数据定义语言（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;

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%';