DDL

DDL：[Data Mefinition Language] 数据定义语言
关键点： CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE/DROP INDEX 等
主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
DDL操作是隐性提交的！不能rollback

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    hire_date DATE
);

#添加列
ALTER TABLE employees
ADD COLUMN email VARCHAR(100);

#修改列类型
ALTER TABLE employees
ALTER COLUMN birth_date TYPE TIMESTAMP;

#删除列
ALTER TABLE employees
DROP COLUMN email;

DROP TABLE employees;

CREATE VIEW employee_names AS
SELECT first_name, last_name
FROM employees;

DROP VIEW employee_names;

DML

DML：[Data Manipulation Language]　 数据操纵语言
关键点： INSERT、UPDATE、DELETE
主要是对表数据进行操作，如对表数据进行增、删、改

#插入单行数据
INSERT INTO employees (first_name, last_name, birth_date, hire_date)
VALUES ('John', 'Doe', '1990-01-01', '2020-01-01');

#插入多行数据
INSERT INTO employees (first_name, last_name, birth_date, hire_date)
VALUES 
    ('Jane', 'Doe', '1992-02-02', '2021-02-02'),
    ('Jim', 'Beam', '1985-03-03', '2019-03-03');

#更新单个字段
UPDATE employees
SET hire_date = '2021-01-01'
WHERE employee_id = 1;

#更新多个字段
UPDATE employees
SET first_name = 'Jonathan', last_name = 'Smith'
WHERE employee_id = 2;

#删除符合条件的行
DELETE FROM employees
WHERE employee_id = 3;

#删除所有数据（但保留表结构）
DELETE FROM employees;

DCL

DCL： [Data Control Language] 数据控制语言
关键点： GRANT、REVOKE

主要主要用于控制数据库的访问权限和安全性

#授予用户对表的全部权限
GRANT ALL PRIVILEGES ON TABLE employees TO user_name;

#授予用户对表的特定权限
GRANT SELECT, INSERT, UPDATE ON TABLE employees TO user_name;

#授予角色权限
GRANT SELECT ON TABLE employees TO role_name;

#授予用户对数据库的连接权限
GRANT CONNECT ON DATABASE database_name TO user_name;

#撤销用户对表的特定权限
REVOKE SELECT, INSERT ON TABLE employees FROM user_name;

#撤销用户对表的全部权限
REVOKE ALL PRIVILEGES ON TABLE employees FROM user_name;

#撤销角色权限
REVOKE CONNECT ON DATABASE database_name FROM user_name;

#创建角色
CREATE ROLE role_name;

#授予用户角色
GRANT role_name TO user_name;

#撤销用户角色
REVOKE role_name FROM user_name;

DQL

DQL： [Data Query Language ] 数据查询语言
关键点： SELECT
DQL基本结构是由SELECT语句，FROM语句，WHERE语句组成的查询块：
基本语法：SELECT <字段名表> FROM <表或视图名> WHERE <查询条件>

#查询所有列
SELECT * 
FROM employees;

#查询特定列
SELECT first_name, last_name 
FROM employees;

#查询满足特定条件的行
SELECT first_name, last_name 
FROM employees
WHERE last_name = 'Doe';

#使用比较运算符
SELECT first_name, last_name 
FROM employees
WHERE hire_date > '2020-01-01';

#AND运算符
SELECT first_name, last_name 
FROM employees
WHERE last_name = 'Doe' AND hire_date > '2020-01-01';

#OR运算符
SELECT first_name, last_name 
FROM employees
WHERE last_name = 'Doe' OR hire_date > '2020-01-01';

#NOT运算符
SELECT first_name, last_name 
FROM employees
WHERE NOT (last_name = 'Doe');

#按一个列排序
SELECT first_name, last_name 
FROM employees
ORDER BY last_name ASC;

#按多个列排序
SELECT first_name, last_name, hire_date 
FROM employees
ORDER BY last_name ASC, hire_date DESC;

SELECT first_name, last_name 
FROM employees
ORDER BY hire_date DESC
LIMIT 5 OFFSET 3;

#计数
SELECT COUNT(*) 
FROM employees;

#分组计数
SELECT department_id, COUNT(*) 
FROM employees
GROUP BY department_id;

#其他聚合函数（如SUM, AVG, MAX, MIN）
SELECT department_id, AVG(salary) 
FROM employees
GROUP BY department_id;

#内连接
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

#左连接
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;