CLASSIFICATION OF SQL STATEMENTS

1.DDL-DATA DEFINITION LANGUAGE

 create
1.create database
2.create table
 alter (use to update the column)
1.add-adding new column
2.modify-modifying datatype
3.drop-removing a column
4.change-old name to new name of column
 show
1.show databases
2.show tables
 drop
1.drop database
2.drop table
 truncate (use to delete the rows)
 rename (use to change the table name)
 use (use to select the database)
2.DML-DATA MANIPULATION LANGUAGE
 insert (inserting values into tables)
 update (used along with set keyword)
 delete (delete rows in a table)
3.DQL-DATA QUERY LANGUAGE (select command)
4.DCL-DATA CONTROL LANGUAGE
 grant(access)
 revoke (withdraw user’s access)
5.TCL-TRANSACTION CONTROL LANGUAGE
 commit- (commits a transaction)
 rollback- (rollbacks a transaction in case of any error
occurs)
 savepoint-(sets a save point within a transaction)
mysql> /*DDL COMMANDS*/
Create and Show command used for both database and tables
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| vollyball |
+--------------------+
mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> use school;

Database changed
mysql> create table student(rollno tinyint not null primary
key,name varchar(15) not null unique,gender char(1),marks
decimal,DOB date,mob bigint,stream varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

+------------------+

| Tables_in_school |

+------------------+

| student |

+------------------+

1 row in set (0.00 sec)

To check the fields in table desc or describe is used

mysql> desc student;
+--------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------+------+-----+---------+-------+

| rollno | tinyint(4) | NO | PRI | NULL | |

| name | varchar(15) | NO | UNI | NULL | |

| gender | char(1) | YES | | NULL | |

| marks | decimal(10,0) | YES | | NULL | |

| DOB | date | YES | | NULL | |

| mob | bigint(20) | YES | | NULL | |

| stream | varchar(20) | YES | | NULL | |

+--------+---------------+------+-----+---------+-------+

7 rows in set (0.02 sec)

mysql> describe student;

+--------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------+------+-----+---------+-------+
| rollno | tinyint(4) | NO | PRI | NULL | |

| name | varchar(15) | NO | UNI | NULL | |

| gender | char(1) | YES | | NULL | |

| marks | decimal(10,0) | YES | | NULL | |

| DOB | date | YES | | NULL | |

| mob | bigint(20) | YES | | NULL | |

| stream | varchar(20) | YES | | NULL | |

+--------+---------------+------+-----+---------+-------+

7 rows in set (0.05 sec)

To delete table and database

mysql> drop table ball;
Query OK, 0 rows affected (0.01 sec)

mysql> drop database vollyball;

Query OK, 0 rows affected (0.02 sec)

USE OF ALTER COMMANDS

To update the columns
Adding extra column, alter and add is used
mysql> alter table student add city char(5) default "hosur";
Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+--------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------+------+-----+---------+-------+

| rollno | tinyint(4) | NO | PRI | NULL | |

| name | varchar(15) | NO | UNI | NULL | |

| gender | char(1) | YES | | NULL | |

| marks | decimal(10,0) | YES | | NULL | |

| DOB | date | YES | | NULL | |

| mob | bigint(20) | YES | | NULL | |

| stream | varchar(20) | YES | | NULL | |

| city | char(5) | YES | | hosur | |

+--------+---------------+------+-----+---------+-------+

8 rows in set (0.02 sec)

To change the column name

Changing from old name to new name, alter and change is used
mysql> alter table student change city place varchar(5);
Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe student;

+--------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------+------+-----+---------+-------+

| rollno | tinyint(4) | NO | PRI | NULL | |

| name | varchar(15) | NO | UNI | NULL | |

| gender | char(1) | YES | | NULL | |

| marks | decimal(10,0) | YES | | NULL | |

| DOB | date | YES | | NULL | |

| mob | bigint(20) | YES | | NULL | |

| stream | varchar(20) | YES | | NULL | |

| place | varchar(5) | YES | | NULL | |

+--------+---------------+------+-----+---------+-------+8 rows in set (0.02 sec)

To Change the datatype of an existing column

modify the datatype of a column, alter and modify is used
mysql> alter table student modify place char(5);
Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe student;

+--------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------+------+-----+---------+-------+

| rollno | tinyint(4) | NO | PRI | NULL | |

| name | varchar(15) | NO | UNI | NULL | |

| gender | char(1) | YES | | NULL | |

| marks | decimal(10,0) | YES | | NULL | |

| DOB | date | YES | | NULL | |

| mob | bigint(20) | YES | | NULL | |

| stream | varchar(20) | YES | | NULL | |

| place | char(5) | YES | | NULL | |

+--------+---------------+------+-----+---------+-------+

8 rows in set (0.00 sec)

Removing a column
To delete a particular column name, alter and drop is used
mysql> alter table student drop place;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+--------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------+------+-----+---------+-------+

| rollno | tinyint(4) | NO | PRI | NULL | |

| name | varchar(15) | NO | UNI | NULL | |

| gender | char(1) | YES | | NULL | |

| marks | decimal(10,0) | YES | | NULL | |

| DOB | date | YES | | NULL | |

| mob | bigint(20) | YES | | NULL | |

| stream | varchar(20) | YES | | NULL | |

+--------+---------------+------+-----+---------+-------+

7 rows in set (0.01 sec)

ADDING AND DELETING PRIMARY KEY

mysql> alter table student drop primary key;
Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+--------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------+------+-----+---------+-------+

| rollno | tinyint(4) | NO | | NULL | |

| name | varchar(15) | NO | PRI | NULL | |

| gender | char(1) | YES | | NULL | |

| marks | decimal(10,0) | YES | | NULL | |

| DOB | date | YES | | NULL | |

| mob | bigint(20) | YES | | NULL | |

| stream | varchar(20) | YES | | NULL | |

+--------+---------------+------+-----+---------+-------+

7 rows in set (0.01 sec)

mysql> alter table student add primary key(rollno);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+--------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------+------+-----+---------+-------+
| rollno | tinyint(4) | NO | PRI | NULL | |

| name | varchar(15) | NO | UNI | NULL | |

| gender | char(1) | YES | | NULL | |

| marks | decimal(10,0) | YES | | NULL | |

| DOB | date | YES | | NULL | |

| mob | bigint(20) | YES | | NULL | |

| stream | varchar(20) | YES | | NULL | |

+--------+---------------+------+-----+---------+-------+

7 rows in set (0.03 sec)

mysql> alter table student rename stud;

Query OK, 0 rows affected (0.02 sec)

mysql> /*DML COMMANDS*/

Insert values into the table
a) Inserting values to all the columns without mentioning
the column name
mysql> insert into student values(1,"Raj Kumar","M",93,"2000-
11-17",9586774748,"Science");
Query OK, 1 row affected (0.02 sec)

b) Multiple row insertions

mysql> insert into student values(2,"Deep Singh","M",98,"1996-
08-22",8988886577,"Commerce"),(3,"Ankit Sharma","M",76,"2000-
02-02",null,"Science"),(4,"Radhika Gupta","F",78,"1999-12-
03",9818675444,"Humanitites");
Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into student values(5,"Payal Goel","F",82,"1998-

04-1",9845639990,"Vocational"),(6,"DikshaSharma","F",82,"1999-
127",9897666650,"Humanities"),(7,"Gurpreet Kaur","F",65,"2000-
01-04",7575757575,"Science"),(8,"Akshay Dureja","M",90,"1997-
05-05",9560567890,"Commerce");
Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

To add the default value in the column

mysql> alter table student add place char(10) default "hosur";
Query OK, 8 rows affected (0.02 sec)

Records: 8 Duplicates: 0 Warnings: 0

c) Inserting values to the specific columns

mysql>insert into student(rollno,name,gender,marks,DOB,stream)
values(9,"Shreya Ananad","F",70,"1999-10-08","Vocational");
Query OK, 1 row affected (0.02 sec)

mysql>insert into student(rollno,name,gender,marks,DOB,mob,stream)

values(10,"Prateek Mittal","M",75,"2000-12-25",9999999675,"Science");
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;

+--------+----------------+--------+-------+------------+------------+-------------+-------+

| rollno | name | gender | marks | DOB | mob | stream | place |

+--------+----------------+--------+-------+------------+------------+-------------+-------+

| 1 | Raj Kumar | M | 93 | 2000-11-17 | 9586774748 | Science | hosur |

| 2 | Deep Singh | M | 98 | 1996-08-22 | 8988886577 | Commerce | hosur |

| 3 | Ankit Sharma | M | 76 | 2000-02-02 | NULL | Science | hosur |

| 4 | Radhika Gupta | F | 78 | 1999-12-03 | 9818675444 | Humanitites | hosur |

| 5 | Payal Goel | F | 82 | 1998-04-21 | 9845639990 | Vocational | hosur |

| 6 | Diksha Sharma | F | 82 | 1999-12-17 | 9897666650 | Humanities | hosur |

| 7 | Gurpreet Kaur | F | 65 | 2000-01-04 | 7575757575 | Science | hosur |

| 8 | Akshay Dureja | M | 90 | 1997-05-05 | 9560567890 | Commerce | hosur |

| 9 | Shreya Ananad | F | 70 | 1999-10-08 | NULL | Vocational | hosur |

| 10 | Prateek Mittal | M | 75 | 2000-12-25 | 9999999675 | Science | hosur |

+--------+----------------+--------+-------+------------+------------+-------------+-------+

10 rows in set (0.00 sec)

Updating data in the table

mysql> update student set place="Delhi" where rollno=10;
Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update student set mob=9176889845,place="Delhi" where

rollno=9;
Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from student;

+--------+----------------+--------+-------+------------+------------+-------------+-------+

| rollno | name | gender | marks | DOB | mob | stream | place |

+--------+----------------+--------+-------+------------+------------+-------------+-------+

| 1 | Raj Kumar | M | 93 | 2000-11-17 | 9586774748 | Science | hosur |

| 2 | Deep Singh | M | 98 | 1996-08-22 | 8988886577 | Commerce | hosur |

| 3 | Ankit Sharma | M | 76 | 2000-02-02 | NULL | Science | hosur |

| 4 | Radhika Gupta | F | 78 | 1999-12-03 | 9818675444 | Humanitites | hosur |

| 5 | Payal Goel | F | 82 | 1998-04-21 | 9845639990 | Vocational | hosur |

| 6 | Diksha Sharma | F | 82 | 1999-12-17 | 9897666650 | Humanities | hosur |

| 7 | Gurpreet Kaur | F | 65 | 2000-01-04 | 7575757575 | Science | hosur |

| 8 | Akshay Dureja | M | 90 | 1997-05-05 | 9560567890 | Commerce | hosur |

| 9 | Shreya Ananad | F | 70 | 1999-10-08 | 9176889845 | Vocational | Delhi |

| 10 | Prateek Mittal | M | 75 | 2000-12-25 | 9999999675 | Science | Delhi |

+--------+----------------+--------+-------+------------+------------+-------------+-------+

10 rows in set (0.00 sec)

Removing data from the table

mysql> delete from student;
mysql> truncate from student;