SQL语言笔记

基础命令

数据库登录

mysql -uroot -p'password'

参数	含义
-P	指定端口ID，默认3306
-e	直接执行的命令

数据定义语言DDL

库操作

创建数据库

CREATE DATABASE database_name;

删除数据库

DROP DATABASE database_name;

表操作

创建表

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

字段四要素：名称 类型 长度 约束

注：int的长度是隐形的，超出长度会自动增长

数据类型类别	数据类型	说明	示例
数值类型	INT/INTEGER	标准整数类型，通常占用4字节	id INT
	SMALLINT	小整数，通常占用2字节	small_id SMALLINT
	TINYINT	非常小的整数，通常占用1字节	tiny_id TINYINT
	BIGINT	大整数，通常占用8字节	big_id BIGINT
	FLOAT	小数点后精度可变，占用4或8字节	price FLOAT
	DOUBLE PRECISION	双精度浮点数，占用8字节	high_precision DOUBLE PRECISION
	DECIMAL/NUMERIC	用户定义的精度和小数位数，适合财务计算	salary DECIMAL(10, 2)
字符串类型	CHAR(n)	固定长度的字符串，n为字符数	code CHAR(5)
	VARCHAR(n)	可变长度的字符串，n为最大字符数	name VARCHAR(100)
	TEXT	长文本数据	description TEXT
日期和时间类型	DATE	日期，格式为YYYY-MM-DD	birth_date DATE
	TIME	时间，格式为HH:MM:SS	start_time TIME
	DATETIME	日期和时间组合，格式为YYYY-MM-DD HH:MM:SS	created_at DATETIME
	TIMESTAMP	时间戳，记录自1970年1月1日以来的秒数	timestamp TIMESTAMP
二进制类型	BINARY(n)	固定长度的二进制数据	bin_data BINARY(10)
	VARBINARY(n)	可变长度的二进制数据	var_bin_data VARBINARY(10)
	BLOB	大二进制对象	file_data BLOB
其他类型	BOOLEAN	布尔值，通常存储为0（假）或1（真）	is_active BOOLEAN
	ENUM	枚举类型，列出所有可能的值	status ENUM('active', 'inactive', 'pending')
	SET	集合类型，可以存储多个值	permissions SET('read', 'write', 'execute')

约束类型	说明	备注
default	默认值
not null	不能为空
unsigned	无符号
zerofill	自动填充
primary key	主键
auto_increment	自动增加序号
foreign key	外键
unique	唯一但可以为空	比如唯一的用户名

删除表

DROP TABLE table_name;

数据操纵语言DML

SELECT

基本查询

SELECT * FROM 表名 WHERE 条件;

条件	示例	其他
（NOT）BETWEEN…AND…	select * from students where age between 100 and 200;
AND & OR	select * from students where age>500 and sex=’male’;
（NOT）IN	select * from students where age in(‘717’,’760’);
IS （NOT） NULL	select * from students where age is not null;
LIKE	select * from students where name like ‘J%’;	_代表一个字符；%代表任意长度的字符

对查询出的结果处理：

降序：select * from students where name like 'S%' order by age desc;

升序：select * from students where name like 'S%' order by age asc;

限制数量：limit 3。

多表查询

INNER JOIN（内连）

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

LEFT JOIN（左连）

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

RIGHT JOIN（右连）

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

FULL JOIN（笛卡尔积）

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

ps：A left join B 和 B right join A 结果是一样的。

子查询

IN和EXIST，IN是把括号内的作为查询内容，EXIST是判断是否查询成功，有结果就是查询成功。

SELECT column1 FROM table1 WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);

分组

GROUP BY

SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1;

HAVING

SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1
HAVING COUNT(column2) > value;

聚合函数

COUNT

SELECT COUNT(column_name)
FROM table_name;

SUM

SELECT SUM(column_name)
FROM table_name;

AVG

SELECT AVG(column_name)
FROM table_name;

MAX & MIN

SELECT MAX(column_name)
FROM table_name;

SELECT MIN(column_name)
FROM table_name;

INSERT

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

UPDATE

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

DELETE

DELETE FROM table_name
WHERE condition;

数据控制语言DCL

用戶管理

创建用户

CREATE USER user1 IDENTIFIED BY 'password123';  

修改用户密码

root用户改自己密码：

mysqladmin -uroot -p’Abc123…’ password ‘Def123…’

ALTER USER user1 IDENTIFIED BY 'newpassword123'; 
flush privileges;

5.7版本：

set password=password(‘Abc123…’);

权限管理

GRANT privileges ON database.table TO 'username'@'host' [IDENTIFIED BY 'password'];  

• privileges：要授予的权限，例如 SELECT, INSERT, UPDATE, DELETE, ALL 等。
• database.table：指定数据库和表，可以使用通配符 * 表示所有数据库或所有表。
• username：用户名。
• host：用户可以连接的主机地址，可以使用通配符 % 表示任何主机。
• IDENTIFIED BY ‘password’：可选，用于设置或修改用户的密码。

授予用户对所有数据库的所有权限

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password';  

使用revoke on from 取消权限

数据库日志

日志类型	日志作用		文件名
错误日志	启动，停止，关闭失败报错，安装日志位置/var/log/mysqld.log
通用查询日志	所有的查询都记下来。	general_log=1	localhost.log
二进制日志	实现备份，增量备份。只记录改变数据，除了select都记。	使用mysqlbinlog -v 日志文件查看	binlog.000001
中继日志	读取主服务器的binlog，在本地回放。保持一致。
slow log	指导调优，定义某一个查询语句，定义超时时间，通过日志提供调优，建议给开发人员。	slow_query_log=1 long_query_time=3	localhost-slow.log
DDL log	定义语句的日志

事务控制语言TCL

事务控制语言（Transaction Control Language）用于管理数据库中的事务，确保数据的一致性和完整性。以下是一些常用的事务控制命令：

BEGIN 或 START TRANSACTION

作用：开始一个新的事务。

BEGIN;  
或者  
START TRANSACTION;  

COMMIT

作用：提交当前事务，使所有的更改成为永久性的。

COMMIT;  

ROLLBACK

作用：回滚当前事务，撤销所有的更改。

ROLLBACK;  

SAVEPOINT

作用：在事务中创建一个保存点，以便后续可以回滚到这个点。

SAVEPOINT savepoint_name;  

RELEASE SAVEPOINT

作用：删除一个保存点。

RELEASE SAVEPOINT savepoint_name;  

ROLLBACK TO SAVEPOINT

作用：回滚到指定的保存点。

ROLLBACK TO SAVEPOINT savepoint_name;