My SQL Tutorial
-
Creating and Selecting a Database
-
Creating a Table & Inserting Records
-
Retrieving Information from a Table
-
Selecting All Data
-
Selecting Particular Rows
-
Selecting Particular Columns
-
Sorting Rows
-
Date Calculations
-
Working with NULL Values
-
Pattern Matching
-
-
Counting Rows
-
Using More Than one Table
-
Getting Information About Databases and Tables
-
Using mysql in Batch Mode
-
Examples of Common Queries .
-
The Maximum Value for a Column
-
The Row Holding the Maximum of a Certain Column
-
Maximum of Column per Group
-
The Rows Holding the Group-wise Maximum of a Certain Column
-
Using User-Defined Variables
-
Using Foreign Keys
-
Searching on Two Keys
-
Calculating Visits Per Day
-
Using AUTO_INCREMENT
-
Using MySQL with Apache
Creating Tables
After successfully created database in MySQL, its time to create table and insert records.
Creating Table in My SQL
CREATE TABLE command/statement is use to define a table. Defining a table means specifying its structure such that, columns are named, its data type, size and its constraint. Each table must have at least one column.
Syntax:
CREATE TABLE <table-name>
( <column-name> <data-type> [(<size>)] [constarint],
<column-name> <data-type> [(<size>)] [constarint],
<column-name> <data-type> [(<size>)] [constarint] ...);
mysql> USE school;
Database changed
Note:- Before issuing a CREATE TABLE command, make sure that its parent database has been opened using USE <database name> command.
SHOW TABLES command tells about the list of tables in the current database. A newly created database is empty.
mysql> SHOW TABLES;
Empty set (0.0 sec)
mysql> CREATE TABLE student
-> (admno CHAR(10) PRIMARY KEY,
-> stuname VARCHAR(35) NOT NULL,
-> address VARCHAR(40),
-> mobile VARCHAR(15),
-> class int(2),
-> sec CHAR(2) );
Query OK, 0 rows affected (0.88 sec)
mysql> SHOW TABLES;
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 rows in set (0.00 sec)
Describing Structure of a Table
DESCRIBE table_name statement/command is use to display the structure of table. It give about all the details of table. You can use DESC table_name;
mysql> DESCRIBE student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| admno | char(10) | NO | PRI | NULL | |
| stuname | varchar(35) | NO | | NULL | |
| address | varchar(40) | YES | | NULL | |
| mobile | varchar(15) | YES | | NULL | |
| class | int(2) | YES | | NULL | |
| sec | char(2) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.17 sec)
mysql> DESC student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| admno | char(10) | NO | PRI | NULL | |
| stuname | varchar(35) | NO | | NULL | |
| address | varchar(40) | YES | | NULL | |
| mobile | varchar(15) | YES | | NULL | |
| class | int(2) | YES | | NULL | |
| sec | char(2) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
Inserting Data into a Table
After creating a table, you need a DML command INSERT INTO, to add rows (tuples) to relations or tables.
Syntax :
INSERT INTO <tablename> [<column list>]
VALUES (<value1> , <value2>, ...., <value n>);
For example, to enter a row into student table -
mysql>INSERT INTO student VALUES ('Sch101','Amrit', 'Mahendragarh', '+91-9595959595', 2, 'A');
Note that, the order of values is similar to column order of table, which is given at the time of table creation.
Alternate method -
mysql>INSERT INTO student(admno,stuname,address,mobile,class, sec)
VALUES('Sch101','Amrit','Mahendragarh','+91-9595959595',2, 'A');
In this alternate method, as you ca see, columns name are given after table name. This method has some advantages -
a) You can change the order of values by changing the order of columns name.
b) You can add the records with some column data, by specifying only those column names and their values.
c) The columns are not listed in the INSERT command will have their default value, if it is defied for them, otherwise , NULL value.
Note : If any other column, does not have DEFAULT value and is defined NOT NULL, is skipped in INSERT command, an error message is generated and the row is not added.
Inserting NULL Value-
mysql>INSERT INTO student(admno,stuname,address,mobile,class, sec)
VALUES('Sch102','Tanmay','Mahendragarh',NULL,NULL, 'A');
Inserting Dates:
Dates are by default entered in 'YYYY-MM-DD' format i.e. first 4 digit represent year, next two digit represent month, last two digits represent day, and these all are separated by hypen. All this is enclosed in single quote.
Loading Data into a Table
The LOAD DATA statements are useful for loading records in table with a text file.
this. You could create a text file sample.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), you can use NULL values. To represent these in your text file, use \N (backslash, capital-N). For example, the record for Whistler the bird
would look like this (where the whitespace between values is a single tab character):
SAMPLE.txt
stu105 Rohit Delhi 989898989 12 A
stu106 Shiwani Nagpur 875898698 \N \N
Syntax
LOAD DATA LOCAL INFILE '/path/sample.txt' INTO TABLE student;