Mysql Database

1. What are the differences between DDL, DML, DCL and TCL in Mysql ?

DDL(Data Definition Language) : 
DDL statements are used to create and modify the structure of tables
  • CREATE : To create new table into database.
  • ALTER :   ALTER TABLE is used to add, delete/drop or modify columns in the existing table.
  • DROP :    This command removes a table from the database
  • TRUNCATE : This will drop and recreate table and reset AUTO_INCREMENT
  • COMMENT :  Add comments to the data dictionary
  • RENAME : RENAME TABLE command will rename the table atomically, which means your table will be locked during the command.
DML(Data Manipulation Language) : 
Used to retrieve and manipulate data in a relational database.
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
DCL(Data Control Language) : 
DCL is about security. Used to controls of the database system. like rights, permissions. 
DCL statement allow you to control who has access to specific object in your database
  • GRANT : It provides the  user's access privileges to the database.
  • REVOKE : This command removes user access rights or privileges to the database objects
TCL(transaction Control Language) :
This is used for transaction within the database.
Note:- DELETE operations can be rolled back (undone), but DROP and TRUNCATE operations cannot be rolled back.
mysql> show databases;

Select your database Or change database.
mysql> use employee_db;
Show all tables in your database.
mysql> show tables;
Create Table
CREATE TABLE employee(  
    employee_id int NOT NULL AUTO_INCREMENT,  
    first_name varchar(100) NOT NULL,  
    last_name  varchar(100) NOT NULL,
    gender  varchar(20) NOT NULL,  
    occupation varchar(35) NOT NULL,  
    age int NOT NULL,  
    PRIMARY KEY (employee_id)  
); 
Describe table. we can use "describe table_name" or "desc table_name"
mysql> desc employee;
mysql> describe employee;

INSERT INTO TABLE
INSERT INTO employee (first_name, last_name, gender, occupation, age) VALUES ('Kamal', 'Deep', 'M', 'engineer', '30');
INSERT INTO employee (first_name, last_name, gender, occupation, age) VALUES ('Sudeep', 'Kumar', 'M', 'Doctor', '35');
INSERT INTO employee (first_name, last_name, gender, occupation, age) VALUES ('Deepti', 'Gupta', 'F', 'Doctor', '28');
INSERT INTO employee (first_name, last_name, gender, occupation, age) VALUES ('Kirti', 'Jain', 'F', 'Doctor', '33');
Select All Data from Table
mysql> select * from employee;

Select selceted columns from Table
mysql> select first_name, gender, occupation from employee;

Select selected columns using where clause
mysql> select first_name, gender, occupation from employee where gender="M";

Select first 2 record where age >=30 : We are using limit here limit has two parameters first is start second is number of records like  "limit start, noOfRecords"
mysql> select first_name, gender, occupation, age from employee where age >="30" limit 0,2;




No comments:

Post a Comment