数据定义语言（DDL）

1.创建数据库：

CREATE DATABASE example_db;

2.删除数据库：

DROP DATABASE example_db;

3.创建表：

CREATE TABLE employees(
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department_id INT
);

4.删除表：

DROP TABLE employees;

5.添加列：

ALTER TABLE employees ADD email VARCHAR(255);

6.删除列：

ALTER TABLE employees DROP COLUMN email;

7.修改列：

ALTER TABLE employees MODIFY COLUMN name VARCHAR(255);

8.重命名列：

ALTER TABLE employees RENAME COLUMN name TO fullname;

9.创建索引：

CREATE INDEX idx_employee_name ON employees(name);

10.删除索引：

DROP INDEX idx_employee_name ON employees;

11.创建视图：

CREATE VIEW department_summary AS
  SELECT department_id,COUNT(*) AS num_employees    
  FROM employees
  GROUP BY department_id;

12.删除视图：

DROP VIEW department_summary;

13.创建主键：

ALTER TABLE employees ADD PRIMARY KEY(employee_id);

14.删除主键：

ALTER TABLE employees DROP PRIMARY KEY;

数据操作语言（DML）

15.插入数据：

INSERT INTO employees(employee_id,name,age,department_id) VALUES(1,'John Doe',30,5);

16.更新数据：

UPDATE employees SET name='Jane Doe' WHERE employee_id=1;

17.删除数据：

DELETE FROM employees WHERE employee_id=1;

18.查询所有数据：

SELECT * FROM employees;

19.查询特定列：

SELECT name,age FROM employees;

20.条件查询：

SELECT * FROM employees WHERE age>30;

21.限制查询结果数量：

SELECT * FROM employees LIMIT 10;

22.查询排序：

SELECT * FROM employees ORDER BY age DESC;

23.分组统计：

SELECT department_id,COUNT(*) AS num_employees FROM  employees GROUP BY department_id;

24.连接查询:

SELECT employees.name,department.name FROM employees JOIN departments ON employees.department_id=departments.department_id;

25.左连接查询：

SELECT employees.name,departments.name FROM employees LEFT JOIN departments ON employees.department_id=departments.department_id;

26.子查询：

SELECT name FROM employees WHERE department_id IN(SELECT department_id FROM departments WHERE name='IT');

27.计算总和：

SELECT SUM(salary) FROM employees;

28.计算平均值：

SELECT AVG(salary) FROM employees;

29.计算最大值：

SELECT MAX(salary) FROM employees;

30.计算最小值：

SELECT MIN(salary) FROM employees;

31.计数：

SELECT COUNT(*) FROM employees;

32.使用别名：

SELECT COUNT(*) AS total_employees FROM employees;

33.去重分组查询：

SELECT DISTINCT department_id FROM employees;
或
SELECT department_id FROM employees group by department_id;

34.使用条件函数：

SELECT name,CASE WHEN age>=18 THEN 'Adult' ELSE 'Minor' END AS status FROM employees;

35.使用LIKE进行模糊查询：

SELECT * FROM employees WHERE name LIKE 'j%';