Java Study Point (Java iQ): 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.

COMMIT
ROLLBACK
SAVEPOINT.
SET TRANSACTION:
SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Syntax

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;

2. How to backup a MySQL database ?

Pages

Search

Mysql Database

Related Tutorials

No comments:

Post a Comment