MYSQL PART II TABLES

Creating tables :
Once you have selected the database, we can start creating tables. The CREATE statement is used to create a table in MySQL with constraint. A Constraint is restriction to the behavior of a variable.
The Create syntax is

CREATE TABLE tableName
( fieldName1 dataType(size) [NULL NOT NULL]
fieldName2 dataType(size) [NULL NOT NULL] );
If NULL is specified, the field is allowed to be left empty. If NOT NULL is specified, the field must be given a value. In the absence of either a NULL or NOT NULL, NULL is assumed.
The below example query will help you in creating table:
CREATE TABLE student
( studID INT(5),
name VARCHAR(30),
);
The above query will create the table student with fields ID and Name.
PRIMARY KEY :
A PRIMARY KEY is a field in a table that uniquely identifies a record. This attribute is used to define the field name to create a primary key.
Example :
fieldName INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
The PRIMARY KEY is specified after defining the fields in the below example:
CREATE TABLE student
( studID INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(30),
PRIMARY KEY(studID)
);
We can also create a compound primary key. A compound primary key is where more than one field is used to uniquely identify a record.
Lets create a table for holding student details in a class.
mysql> create table student(studid int(10), name varchar(20), address varchar(40), phone int(10));
Query OK, 0 rows affected (0.05 sec)
Desc table :
We can examine the structure of a table using the DESCRIPTION or DESC statement. The following query describes the structure of the student table.
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+-------------+------+-----+---------+-------+
studid int(10) YES NULL
name varchar(20) YES NULL
address varchar(40) YES NULL
phone int(10) YES NULL
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
We can also use the SHOW FIELDS FROM statement to display the same structure.
mysql> SHOW FIELDS FROM student;
Listing Tables :
We can list all the tables in the database using SHOW TABLES query. The following query will list the tables in the current database.
mysql> show tables;
+--------------------+
Tables_in_sample
+--------------------+
student
+--------------------+
1 row in set (0.00 sec)
Deleting tables :
The DROP statement is used to delete one or more tables completely from a database.
The syntax is
DROP TABLE tbl_name
The following example deletes the student table.
mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)
This query will permanently remove or delete the table student.
DROP TABLE query drops all fields in the table and deletes the table. Once the DROP TABLE statement is used, we cannot use that table. So, we should be careful with this statement.
Renaming tables :
The RENAME statement is used to rename one or more tables in a database.
The syntax is
RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
The following example query renames the student table as class table.
mysql> rename table student to class;
Query OK, 0 rows affected (0.00 sec)
Now we can view the table whether the name is changed by the following query.
mysql> show tables;
+--------------------+
Tables_in_sample
+--------------------+
class
+--------------------+
1 row in set (0.00 sec)
If the query renames more than one table, renaming operations are done from left to right.
We can also swap two table names. Let us assume tmp table which does not exists.
Example :
RENAME TABLE emp1 TO tmp,
emp2 TO emp1,
tmp TO emp2;
We can also use RENAME TABLE to move a table from one database to another.
Example :
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
ALTER TABLE :
ALTER TABLE is used to change the structure of an existing table. We can add or delete columns, change the type of existing columns, or rename columns or the table itself. We can also change the comment for the table and type of the table.
The Syntax is
ALTER TABLE tbl_name alter_specification [, alter_specification] ...
The below table will describe the alter specification :
Alter Specification Description
Rename Rename a Table name
Add Add a new column, key, index
Add First Add a column First
Add After Add a column After
Drop Drop a column, Index, key
Change Change a column name
Change Type Change a column type
Modify Modify a column type
Renaming a Table :
We can also RENAME the table using ALTER TABLE. The following example query renames the table student to class.
mysql> ALTER TABLE student RENAME class;
The above query will change the table name.
Adding a column to a table :
The ADD COLUMN modifier is used to add a column to a table. The following example query adds a field called marks to the student table.
mysql> ALTER TABLE student ADD COLUMN marks INT(10);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+-------------+------+-----+---------+-------+
studid int(10) YES NULL
name varchar(20) YES NULL
address varchar(40) YES NULL
phone int(10) YES NULL
marks int(10) YES NULL
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Next we move to the alterations in displaying the tables.
Add a column First :
We can position the field using FIRST and AFTER modifiers. The following example query will place the new field as the first field in the table.
mysql> ALTER TABLE student ADD COLUMN marks INT(10) FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+-------------+------+-----+---------+-------+
marks int(10) YES NULL
studid int(10) YES NULL
name varchar(20) YES NULL
address varchar(40) YES NULL
phone int(10) YES NULL
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Add a column After :
We can also place the new field next to any of the field. The following example query will place the new field immediately after the field name.
mysql> ALTER TABLE student ADD COLUMN marks INT(10) AFTER names;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+-------------+------+-----+---------+-------+
studid int(10) YES NULL
name varchar(20) YES NULL
marks int(10) YES NULL
address varchar(40) YES NULL
phone int(10) YES NULL
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Next we can see how to delete and change a field.
Delete a column :
The DROP COLUMN is used to delete a column from the table.
The syntax is
ALTER TABLE tbl_name DROP col_name;
The following query drops the field marks.
mysql> ALTER TABLE student DROP COLUMN marks;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+-------------+------+-----+---------+-------+
studid int(10) YES NULL
name varchar(20) YES NULL
address varchar(40) YES NULL
phone int(10) YES NULL
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Change a column name :
When we modify a column, we have to specify the attribute of the column again. The following example renames the name field to stud_name in the student table.
mysql> ALTER TABLE student CHANGE name stud_name VARCHAR(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+-----------+-------------+------+-----+---------+-------+
studid int(10) YES NULL
stud_name varchar(20) YES NULL
Counting Rows :
COUNT(*) counts the number of rows in a table.
The syntax is
SELECT COUNT(*) from tbl_name;
Example :
mysql> select count(*) from student;
+----------+
count(*)
+----------+
5
+----------+
1 row in set (0.00 sec)
The above query will list the number of rows in the student table.
After inserting datas into the table, we probably want to check the datas are stored correctly. To do so, we use the SELECT statement.
The Select syntax is
SELECT what_to_select from tbl_name;
To view all the data from the table, we use the below query.
mysql> select * from student;
+--------+---------+-------+------------------+---------+
studid name marks address phone
+--------+---------+-------+------------------+---------+
1 steve 100 5th cross street 2456987
2 david 98 welling street 547896
3 michael 75 edinburgh 2598234
4 jack 82 victoria street 2436821
5 anne 100 downing street 2634821
6 steve 75 downing street 2874698
7 anne 80 edinburgh 2569843
8 mille 98 victoria street 1236547
+--------+---------+-------+------------------+---------+
8 rows in set (0.00 sec)
The above example query will list the complete details of the student table. Here * will select all the columns from the table.
LIMIT :
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. It takes one or two numeric arguments, which must both be non-negative integer constants.
Lets see an example query for SELECT LIMIT statement.
mysql> select * from student limit 2,5;
+--------+---------+-------+-----------------+---------+
studid name marks address phone
+--------+---------+-------+-----------------+---------+
3 michael 75 edinburgh 2598234
4 jack 82 victoria street 2436821
5 anne 100 downing street 2634821
6 steve 75 downing street 2874698
7 anne 80 edinburgh 2569843
+--------+---------+-------+-----------------+---------+
5 rows in set (0.00 sec)
Here the first argument of th query specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. Therefore it retrieves the rows from 3 - 7 from the student table.
We can also return the values from the beginning of the result set by specifying the number of rows in a single argument as follows.
mysql> select * from student limit 5;
+--------+---------+-------+------------------+---------+
studid name marks address phone
+--------+---------+-------+------------------+---------+
1 steve 100 5th cross street 2456987
2 david 98 welling street 547896
3 michael 75 edinburgh 2598234
4 jack 82 victoria street 2436821
5 anne 100 downing street 2634821
+--------+---------+-------+------------------+---------+
5 rows in set (0.08 sec)
The above query will retrieve the first five rows of the student table.
We can select a particular column to display, regretting the entire rows. Suppose you want to see the name of the students alone we can use the below query.
mysql> select name from student;
+---------+
name
+---------+
steve
david
michael
jack
anne
steve
anne
mille
+---------+
8 rows in set (0.27 sec)
We can also select multiple columns, separated by commas as given in the below query.
mysql> select name, marks from student;
+---------+-------+
name marks
+---------+-------+
steve 100
david 98
michael 75
jack 82
anne 100
steve 75
anne 80
mille 98
+---------+-------+
8 rows in set (0.03 sec)
In the above query, we have selected both name and marks from the table student
In the preceding examples, the query for selected rows are displayed in no particular order. We can also select the rows to display in an ordered format using ORDER BY Clause.
The following example query will sort the rows in an ascending order based on the marks.
mysql> select name, marks from student order by marks;
+---------+-------+
name marks
+---------+-------+
michael 75
steve 75
anne 80
jack 82
david 98
mille 98
steve 100
anne 100
+---------+-------+
8 rows in set (0.03 sec)
We can also sort the orders in descending order. In the below example query the marks are sorted in descending order.
mysql> select name, marks from student order by marks desc;
+---------+-------+
name marks
+---------+-------+
steve 100
anne 100
david 98
mille 98
jack 82
anne 80
michael 75
steve 75
+---------+-------+
8 rows in set (0.00 sec)
Next we can see how to sort the multiple columns.
We can also sort multiple columns in different directions as given in the below query.
mysql> select name, marks, address from student order by name,
marks desc;
+---------+-------+------------------+
name marks address
+---------+-------+------------------+
anne 100 downing street
anne 80 edinburgh
david 98 welling street
jack 82 victoria street
michael 75 edinburgh
mille 98 victoria street
steve 100 5th cross street
steve 75 downing street
+---------+-------+------------------+
8 rows in set (0.00 sec)
Here we have selected three columns name, marks and address. In this query we have sorted the column name alone in ascending order and we have additionally mentioned marks in descending order.
So if there are same names, the highest mark will be taken as the first priority. In the above example query, there are 2 anne, so the anne with highest mark will be displayed first
We can select a particular row using the WHERE clause statement. We can also check any condition using WHERE clause or keyword. Where condition comes handy when we come across a big table having huge volume of data but we might want to see only small number of rows satisfying a condition.
The select where syntax is
SELECT what_to_select FROM tbl_name WHERE conditions_to_satisfy;
Here the conditions_to_satisfy specifies one or more conditions that rows must satisfy to qualify for retrieval.
Let's see an example query for retrieving a single student data using the WHERE clause.
mysql> select * from student where name = 'jack';
+--------+------+-------+-----------------+---------+
studid name marks address phone
+--------+------+-------+-----------------+---------+
4 jack 82 victoria street 2436821
+--------+------+-------+-----------------+---------+
1 row in set (0.00 sec)
Here in the above example query we have retrieved a single student details. Suppose if we want to retrieve the student's details who have secured more than 90 marks, we can use the below query.
mysql> select * from student where marks > 90;
+--------+-------+-------+------------------+---------+
studid name marks address phone
+--------+-------+-------+------------------+---------+
1 steve 100 5th cross street 2456987
2 david 98 welling street 547896
5 anne 100 downing street 2634821
8 mille 98 victoria street 1236547
+--------+-------+-------+------------------+---------+
4 rows in set (0.05 sec)
Sometimes we may need to look for the table with a certain matching character. In MySQL we use LIKE or NOT LIKE operator for comparison. In MySQL the patterns are case-insensitive by default.
Let us consider an example query to display the student names starting with the letter M.
mysql> select * from student where name like 'm%';
+--------+---------+-------+-----------------+---------+
studid name marks address phone
+--------+---------+-------+-----------------+---------+
3 michael 75 edinburgh 2598234
8 mille 98 victoria street 1236547
+--------+---------+-------+-----------------+---------+
2 rows in set (0.01 sec)
In the above example query, it will list all the names that starts with the letter M from the table student.
The following example query will list the names that ends with letter e.
mysql> select * from student where name like '%e';
+--------+-------+-------+------------------+---------+
studid name marks address phone
+--------+-------+-------+------------------+---------+
1 steve 100 5th cross street 2456987
5 anne 100 downing street 2634821
6 steve 75 downing street 2874698
7 anne 80 edinburgh 2569843
8 mille 98 victoria street 1236547
+--------+-------+-------+------------------+---------+
5 rows in set (0.00 sec)
We can also list the names that contains a specific letter anywhere. The following example query will list the names that contains "a".
mysql> select * from student where name like '%a%';
+--------+---------+-------+-----------------+---------+
studid name marks address phone
+--------+---------+-------+-----------------+---------+
2 david 98 welling street 547896
3 michael 75 edinburgh 2598234
4 jack 82 victoria street 2436821
5 anne 100 downing street 2634821
7 anne 80 edinburgh 2569843
+--------+---------+-------+-----------------+---------+
5 rows in set (0.00 sec)
Suppose if we want to find the names that contain exactly five characters, we use a special character "_"(underscore). The following query will list all the five letter names from the table student.
mysql> select * from student where name like '_____';
+--------+-------+-------+------------------+---------+
studid name marks address phone
+--------+-------+-------+------------------+---------+
1 steve 100 5th cross street 2456987
2 david 98 welling street 547896
6 steve 75 downing street 2874698
8 mille 98 victoria street 1236547
+--------+-------+-------+------------------+---------+
4 rows in set (0.00 sec)
Group By :
The Group by clause is used to display the rows and columns grouped by selective columns. It can be used to perform the aggregate functions, such as count().
The following example query will list the name of the student and also count the repeative names using Group By clause in the select statement.
mysql> select name, count(name) from student group by name;
+-------+-------------+
name count(name)
+-------+-------------+
anne 2
david 1
jack 1
mille 1
steve 2
+-------+-------------+
5 rows in set (0.03 sec)
The below query will display the name and sum of marks of the student using groupby clause.
mysql> select name,sum(marks),count(*) from students group by name;
+----------+------------+----------+
name sum(marks) count(*)
+----------+------------+----------+
anne 175 2
maichael 82 1
mike 182 2
rock 100 1
steve 175 2
+----------+------------+----------+
5 rows in set (0.00 sec)