第十八：SQL命令大全

一.连接到MySQL数据库

1.这会提示输入密码
1.1.连接到默认的 localhost 和 3306 端口
1.2.可以通过-h和-P参数指定主机和端口

mysql -u username -p

二.退出MySQL数据库

EXIT;

三.基本查询

1.选择所有列

SELECT * FROM table_name;

2.选择特定列

SELECT column1, column2 FROM table_name;

3.使用WHERE 子句

SELECT * FROM table_name WHERE condition;

例：SELECT * FROM employees WHERE department = 'Sales';

4.使用AND和OR

SELECT * FROM table_name WHERE condition1 AND condition2;
SELECT * FROM table_name WHERE condition1 OR condition2;

例：SELECT * FROM employees WHERE department = 'Sales' AND hire_date > '2024-01-01';

5.使用IN和NOT IN

SELECT * FROM table_name WHERE column_name IN (value1, value2, ...);
SELECT * FROM table_name WHERE column_name NOT IN (value1, value2, ...);

例：SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');

6.使用LIKE和NOT LIKE

SELECT * FROM table_name WHERE column_name LIKE 'pattern';
SELECT * FROM table_name WHERE column_name NOT LIKE 'pattern';

例：SELECT * FROM employees WHERE name LIKE 'J%'; -- 以 J 开头的所有名字

7.使用BETWEEN和NOT BETWEEN

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT * FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;

例：SELECT * FROM employees WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31';

8.排序结果

SELECT * FROM table_name ORDER BY column_name [ASC|DESC];

例：SELECT * FROM employees ORDER BY hire_date DESC;

9.限制返回行数

SELECT * FROM table_name LIMIT number;

例：SELECT * FROM employees LIMIT 5;

10.限制返回行数并指定偏移量

SELECT * FROM table_name LIMIT offset, number;

例：SELECT * FROM employees LIMIT 5, 10; -- 从第6行开始，返回10行

四.分组函数查询

1.计算总数

SELECT COUNT(column_name) FROM table_name;

2.计算总和

SELECT SUM(column_name) FROM table_name;

3.计算平均值

SELECT AVG(column_name) FROM table_name;

4.计算最大值

SELECT MAX(column_name) FROM table_name;

5.计算最小值

SELECT MIN(column_name) FROM table_name;

6.分组

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

7.分组并筛选

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;

例：SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

五.连接查询

1.内连接（INNER JOIN）

SELECT columns
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

例：SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

2.左连接（LEFT JOIN）

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

例：SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

3.右连接（RIGHT JOIN）

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;

例：SELECT employees.name, departments.department_name 
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

4.全连接(FULL JOIN)MySQL不直接支持FULL JOIN，但可以通过UNION实现

SELECT columns FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column
UNION
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.common_column = table2.common_column;

六.子查询

1.作为SELECT的一部分

SELECT column_name FROM table_name WHERE column_name = (SELECT column_name FROM other_table WHERE condition);

例：SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE department_name = 'Sales');

2.作为FROM的一部分

SELECT * FROM (SELECT column_name FROM table_name WHERE condition) AS alias;

例：SELECT * FROM (SELECT name FROM employees WHERE hire_date > '2024-01-01') AS recent_employees;

七.插入数据

1.插入单行数据

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

2.插入多行数据

INSERT INTO table_name (column1, column2, ...)
VALUES (value1a, value2a, ...), (value1b, value2b, ...), ...;

3.从另一个表插入数据

INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM other_table
WHERE condition;

八.更新数据

1.更新单个字段

UPDATE table_name SET column_name = new_value WHERE condition;

2.更新多个字段

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

九.删除数据

1.删除满足条件的行

DELETE FROM table_name WHERE condition;

2.删除所有行

DELETE FROM table_name;

十.事务管理

1.开始事务

START TRANSACTION;

2.提交事务

COMMIT;

3.回滚事务

ROLLBACK;

十一.索引操作

1.创建索引

CREATE INDEX index_name ON table_name (column_name);

2.删除索引

DROP INDEX index_name ON table_name;

十二.用户管理

1.创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

2.授予权限

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

3.撤销权限

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

4.删除用户

DROP USER 'username'@'host';

十三.备份和恢复

1.备份数据库

mysqldump -u username -p database_name > backup.sql

2.恢复数据库

mysql -u username -p database_name < backup.sql