SQL-Statements
SQL statements: DDL, DQL, DML, DCL, TCL.
SQL-Structured Query Language is a standard language for storing, manipulating and retrieving data from databases.
SQL statements are categorized into
- DDL (Data Definition Language),
- DQL (Data Query Language),
- DML (Data Manipulation Language),
- DCL (Data Control Language),
- TCL (Transaction Control Language),
Sometimes, Select query which we categorized as DQL may be considered in DML.
DDL
Data Definition Language is used to define database schema. Also, to create and modify the structure of database(db).
Database schema means a skeleton / structure which represents logical view of the entire database.
Create
To create database or its objects (tables, index, functions, view, triggers, stored procedures).
For database : create database db_name;
for table : create table table_name (column_name datatype);
Alter
To modify the structure of already existing table.
Syntax : alter table table_name add column column_name datatype;
Change : To change the name of column, column data type, column constraints.
Syntax : alter table table_name change column column_name datatype constraints;
Modify : To modify existing column data type and constraints.
Syntax : alter table table_name modify column_name datatype constraints;
After : To add new column at specific position in the table.
Syntax : alter table table_name add column_name datatype after column_name;
Drop
To delete an existing db or its objects.
No where clause is used to drop.
Drop command can’t be rollbacked because works directly on data itself.
Syntax : drop database db_name;
Created a dummy database named db_name and deleted using drop.
Syntax : drop table tb_name;
Created a dummy db named as db_name and created a table tb_name then delete by drop table.
Drop table helps to remove a table definition and all the data, indexes, triggers, constraints and permission specifications.
Truncate
To remove all records (row) from the table but the table’s schema wont deleted.
Syntax : truncate table tb_name;
After truncate, use desc truncated_table_name to see the schema. If you need to delete that schema also then use drop table table_name.
Rename
To rename an existing table name.
Syntax : rename table existing_table_name to new_table_name;
DQL
Data Query Language is used to retrieve data from database.
Syntax : select column_name(s) from table_name <where condition> ;
To retrieve all data : select * from table_name <condition>;
To retrieve specific data : select column_name1,column_name2 from table_name <where condition>;
DML
Data Manipulation Language is used to modify the database by inserting, updating, deleting the data.
Insert
Inserting data into the table of a database.
Syntax : insert into table_name (col1, col2,.., coln) values (v1, v2,..,vn);
We can insert values without mentioning the column name of the table.
Syntax : insert into table_name values (v1, v2,…,vn);
Update
To update / modify value of column in the table.
Syntax : update table_name set col1 = v1,…, col_n = vn where condition;
Delete
To remove one or more rows from a table .
Syntax : delete from table_name where condition ;
DCL
Data Control Language is used to give rights and permissions to the users of the database.
We cannot modify our internal marks displayed in the college result portal but our faculty can do. This is because of rights and permission given by the admin of that portal (college exam cell / university authority).
DCL : Grant and Revoke .
Grant
To give access privilege to database.
Syntax : GRANT privilege_name ON object TO user;
- privileges_name : the access rights or privileges granted to the user.
- object : the name of the database object to which permissions are being granted. If we are granting privileges on a table, then table name will be the object.
- user : the name of the user to whom the privileges would be granted.
Privilege
Global : GRANT SELECT ON *.* TO user1@localhost;
user1@localhost can query data from all tables in all database of the current MySQL Server. (to denote all we use * )
Database : GRANT INSERT ON db_name.* TO user1@localhost;
user1@localhost can insert data into all tables in the db_name database.
Table : GRANT DELETE ON db_name.table_name TO user1@localhost;
user1@localhost can delete rows from the table table_name in the database db_name.
Column : GRANT SELECT (column_name(s)), UPDATE (column_name1) ON table_name TO user1@localhost;
user1@localhost can select data from columns column_name(s) and update column_name1 only the column in the table_name table.
Stored routine : GRANT EXECUTE ON PROCEDURE proc_name
TO user1@localhost;
user1@localhost can execute the stored procedure proc_name in the current database.
Proxy : GRANT PROXY ON root TO user2@localhost;
user2@localhost assumes all privileges of root.
Privileges that can be granted are select, insert, delete, index, create, alter, drop, all, update, grant.
Revoke
To revoke some or all of the privileges which have been granted to a user before.
Syntax : REVOKE privileges ON object FROM user;
TCL
Transaction Control Language is used to manage transactions in the database and to manage the changes made to the data in a table by DML statements.
Consider a book without index page and more than 100 pages totally. You are reading the book and to take a pause you are tagging a bookmark. After sometimes you are reading from that bookmark.
So keep this scenario in the mind and consider reading the book as a transaction and pausing, tagging a bookmark are save point. Resuming the reading from the bookmark as commit. But after commit you cant rollback or cant able to read anything. It’s a lol..
TCL : Commit, Rollback, Save point
Syntax:
Start transaction ;
(update / delete / insert) query ;
savepoint savepoint_name;
(update / delete / insert) query ;
rollback to savepoint_name;
commit;
NOTE : before and after rollback, you can use select query to know the difference.
Using select query we can see that followers is 2 for content_id is 13. Now set the save point (Say s).(First table)
Update the followers as 8 for content_id is 13. See the data using select query. (Second table) Before rollback
Now, rollbacked to save point (say s) and then displayed the data present in the table using select query. (third table) After rollback
In third table, followers is 2 for content_id is 13.
Commit the transaction and now the table data shows followers is 2 for content_id is 13.
We updated the value but we rollbacked at that time followers is 2 for content_id is 13. (third table)
TitBits
SQL Designed by : Donald D. Chamberlin & Raymond F. Boyce
Appeared on : 1974.
File Extension : .sql
History
In 1970, Dr. Edgar F. “Ted” Codd of IBM described a relational model for databases and he is known as the father of relational databases.
After learning about the relational model from Edgar F. Codd, SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce.
This version is called SEQUEL (Structured English Query Language) and designed to manipulate and retrieve data stored in IBM’s System R, which was developed from Codd’s ideas.
Chamberlin and Boyce’s first attempt at a relational database language was Square. Due to subscript notation, it was difficult to use. In 1973, they began work on SEQUEL and was later changed to SQL because “SEQUEL” was a trademark of the UK-based company.
After the successful testing IBM developed commercial products based on System R.
Due to usefulness and productivity of the system Relational Software, Inc (now Oracle Corporation) developed their own SQL -based RDBMS and sells to U.S. Navy, Central Intelligence Agency, and other U.S. government agencies.
In 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers.
In 1986, ANSI and ISO standard groups officially adopted the SQL as standard “Database Language SQL” language definition.
