数据操作语言(DML)

SQL常用语句--超实用！！！

数据操作：

1.插入数据(数据需要一一对应)

        INSERT INTO employees (employee_id,name,age,department_id)

        VALUES(1,'John Doe',30,5);

2.更新数据

        UPDATE employees

        SET name = 'Jane Doe'

        WHERE employee_id=1;

3.删除数据

        DELETE FROM employees WHERE employee_id = 1;

4.查询所有数据

        SELETE * FROM employees;

查询操作：

5.查询特定列

        SELETE name,age

        FROM employees;

6.条件查询

        SELETE * 

        FROM employees

        WHERE age>30;

7.限制查询结果数量

        SELETE * 

        FROM employees

        LIMIT 10;

8.查询排序

        SELETE *

        FROM employees

        ORDER BY age DESC;

9.分组统计

        SELECT department_id,COUNT(*) AS num_employees

        FROM employees

        GROUP BY department_id;

10.连接查询

        SELECT employees.name,departments.name

        FROM employees

        JOIN departments

        ON employees.department_id = departments.department_id;

11.左连接查询

        SELECT employees.name,departments.name

        FROM employees

        LEFT JOIN departments

        ON employees.department_id = departments.department_id;

12.子查询(括号内优先执行)

        SELECT name FROM employees

        WHERE department_id

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

计算操作：

13.计算总和

        SELECT SUM(salary) FROM employees;

14.计算平均值

        SELECT AVG(salary) FROM employees;

15.计算最大值

        SELECT MAX(salary) FROM employees;

16.计算最小值

        SELECT MIN(salary) FROM employees;

17.计数

        SELECT COUNT(*) FROM employees;

其他操作：

18.使用别名

        SELECT COUNT(*) AS total_employees

        FROM employees;

19.去重查询

        SELECT DISTINCT department_id FROM employees;

20.使用条件函数

        SELECT name,

        CASE WHEN age >= 18

                THEN 'Adult'

        ELSE 'Minor'

        END AS status

        FROM employees;

21.使用LIKE进行模糊查询

        SELECT * FORM employees

        WHERE name = '%J%';

22.使用BETWEEN查询范围

        SELECT * FROM employees

        WHERE age BETWEEN 25 AND 35;

23.使用IS NULL检查空值

        SELECT * FROM employees

        WHERE department_id IS NULL;

24.使用IS NOT NULL检查非空值

        SELECT * FROM employees

        WHERE department_id IS NOT NULL;