Section 1.1: Data Definition Language (DDL)

Data Definition Language (DDL) commands are crucial for managing the structure of databases.

1. Database Operations

To start, here are some key commands for database management:

• Logging into the Database:

mysql -uroot -proot

• Creating a Database:

CREATE DATABASE test;

• Viewing All Databases:

SHOW DATABASES;

• Selecting a Database for Use:

USE test;

• Listing All Tables:

SHOW TABLES;

• Deleting a Database:

DROP DATABASE test;

2. Table Operations

Next, we delve into commands for table manipulation:

• Creating a Table:

CREATE TABLE emp (ename VARCHAR(10), hiredate DATE, sal DECIMAL(10,2), deptno INT(2));

CREATE TABLE dept (deptno INT(2), deptname VARCHAR(10));

• Checking Table Structure:

DESC emp;

• Detailed Table Definition:

SHOW CREATE TABLE emp G;

• Dropping a Table:

DROP TABLE emp;

• Modifying Table Fields:

ALTER TABLE emp MODIFY ename VARCHAR(20);

• Adding a Column:

ALTER TABLE emp ADD COLUMN age INT(3);

• Deleting a Column:

ALTER TABLE emp DROP COLUMN age;

• Renaming a Field:

ALTER TABLE emp CHANGE age age1 INT(4);

• Renaming the Table:

ALTER TABLE emp RENAME TO emp12;

Section 1.2: Data Manipulation Language (DML)

Data Manipulation Language (DML) commands are utilized for managing and manipulating data within the database.

1. Inserting Records

• Inserting with Specified Columns:

INSERT INTO emp (ename, hiredate, sal, deptno) VALUES ('Tom', '2023-01-01', '2000', 1);

• Inserting Without Specifying Columns:

INSERT INTO emp VALUES ('Jack', '2023-01-01', '2000', 1);

• Bulk Insertion:

INSERT INTO dept VALUES (1, 'dept1'), (2, 'dept2');

2. Modifying Records

• Updating a Record:

UPDATE emp SET sal = '4000', deptno = 2 WHERE ename = 'Tom';

3. Deleting Records

• Removing a Record:

DELETE FROM emp WHERE ename = 'Tom';

4. Querying Records

• Retrieving All Records:

SELECT * FROM emp;

• Selecting Unique Records:

SELECT DISTINCT deptno FROM emp;

• Conditional Queries:

SELECT * FROM emp WHERE deptno = 1 AND sal < 3000;

• Sorting and Limiting Results:

SELECT * FROM emp ORDER BY deptno DESC LIMIT 2;

• Pagination:

SELECT * FROM emp ORDER BY deptno DESC LIMIT 0, 10;

• Aggregation:

SELECT deptno, COUNT(1) FROM emp GROUP BY deptno HAVING COUNT(1) > 1;

• Join Queries:

SELECT * FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

5. Advanced Queries

• Subqueries:

SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept);

• Union Queries:

SELECT deptno FROM emp UNION SELECT deptno FROM dept;

Section 1.3: Data Control Language (DCL)

Data Control Language (DCL) commands are essential for managing permissions and user accounts.

1. Permission Management

• Granting Permissions:

GRANT SELECT, INSERT ON test.* TO 'test'@'localhost' IDENTIFIED BY '123';

• Viewing Permissions:

SHOW GRANTS FOR 'test'@'localhost';

• Revoking Permissions:

REVOKE INSERT ON test.* FROM 'test'@'localhost';

• Granting All Privileges:

GRANT ALL PRIVILEGES ON . TO 'test'@'localhost';

• Granting Super Privileges:

GRANT SUPER, PROCESS, FILE ON . TO 'test'@'localhost';

• Account Management:

DROP USER 'test'@'localhost';

SET PASSWORD FOR 'test'@'localhost' = PASSWORD('123');

Section 1.4: Additional Commands

1. Character Set Management

• Viewing Character Set:

SHOW VARIABLES LIKE 'character%';

• Creating Database with Specific Character Set:

CREATE DATABASE test2 CHARACTER SET utf8;

2. Time Zone Management

• Viewing Current Time Zone:

SHOW VARIABLES LIKE "%time_zone%";

• Setting Global Time Zone:

SET GLOBAL time_zone = '+8:00';

• Setting Session Time Zone:

SET time_zone = '+8:00';

• Flushing Privileges:

FLUSH PRIVILEGES;