30个必备SQL语句 | 小x的博客

type

status

date

slug

summary

📝 主旨内容

数据定义语言（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 ASSELECT department_id,COUNT() ASnum_employeesFROM employeesGROUP 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.namedepartment.name FROM employees JOIN departments ON employees.department_id=departments.department_id; 25.左连接查询： SELECT employees.namedepartments.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%';

🤗 总结归纳

💡

常背常新~