MySQL数据库
1. 数据库管理
1.1 数据库基本操作
- 查看当前所有数据库：
show databases - 创建数据库：
create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; - 删除数据库：
drop database 数据库名; - 进入数据库：
use 数据库;
1.2 数据表基本操作
- 查看该数据库中的数据表：
show tables; - 创建表结构：
create table 表名(
列名类型，
列名类型，
列名类型
)default charset=utf8;如：
create table tb1(
id int,
name varchar(16)
)default charset=utf8;创建出的数据表如下：
| id | int |
| :--: | :--: |
| | |
| | |
携带参数：
create table tb1(
id int primary key, -- 主键（主键不允许为空，不能重复）
name varchar(16) not null, -- 不允许为空
email varchar(32) null, -- 允许为空（默认）
age int default 3 -- 插入数据时，如果不给默认值，则赋予默认值3
)default charset=utf8;create table tb2(
id int primary key auto_increment not null, -- 主键&&自增&&非空
)default charset=utf8;注意：一个表中只能有一个自增列auto_increment
- 删除表：
drop table 表名; - 清空表（不删除，清空数据）：
delete from 表名;或truncate table 表名; 修改表：
添加列：
alter table 表名 add 列名 类型; alter table 表名 add 列名 类型 DEFAULT 默认值; alter table 表名 add 列名 类型 not null default 默认值; alter table 表名 add 列名 类型 not null primary key auto_increment;删除列：
alter table 表名 drop column 列名；修改列 类型
alter table 表名 modify column 列名 类型;修改列 类型+名称
alter table 表名 change 原列名 新列名 新类型;如：
alter table tb change id nid int not null; alter table tb change id nid int not null default 5; alter table tb change id nid int not null primary key auto_increment; alter table tb change id nid int; -- 允许为空，删除默认值、自增修改列 默认值
alter table 表名 alter 列名 set default 1000;删除列 默认值
alter table 表名 alter 列名 drop default;添加主键
alter table 表名 add primary key 列名;删除主键
alter table 表名 drop primary key;
1.3 常见列类型
int[(m)][unsigned][zerofill]
int 表示有符号，取值范围：-2147483648 ~ 2147483647
int unsigned 表示无符号，取值范围：0 ~ 4294967295
int(5) zerofill 仅用于显示，当不满足5位时，按照左边补0，如：00002；满足时，正常显示
mysql> use test;
Database changed
mysql> create table L1(id int, uid int unsigned, zid int(5) zerofill)default charset=utf8;
Query OK, 0 rows affected, 3 warnings (0.01 sec)
mysql> insert into L1(id,uid,zid) values(1,2,3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into L1(id,uid,zid) values(2147483641,4294967294,300000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from L1;
+------------+------------+--------+
| id | uid | zid |
+------------+------------+--------+
| 1 | 2 | 00003 |
| 2147483641 | 4294967294 | 300000 |
+------------+------------+--------+
2 rows in set (0.00 sec)
mysql> insert into L1(id,uid,zid) values(21474836410,42949672940,300000);
ERROR 1264 (22003): Out of range value for column 'id' at row 1 -- 超出范围报错decimal[(m[,d])] [unsigned] [zerofill]-- 准确的小数值，m是数字总个数（负号不算），d是小数点后个数。m最大值为65，d最大值为30 -- 例如： create table L2( id int not null primary key auto_increment, salary decimal(8,2) )default charset=utf8;mysql> create table L2( -> id int not null primary key auto_increment, -> salary decimal(8,2) -> )default charset=utf8; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> insert into L2(salary) values(1.28); Query OK, 1 row affected (0.00 sec) mysql> insert into L2(salary) values(5.289); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into L2(salary) values(5.282); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into L2(salary) values(512132.28); Query OK, 1 row affected (0.00 sec) mysql> insert into L2(salary) values(512132.283); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from L2; +----+-----------+ | id | salary | +----+-----------+ | 1 | 1.28 | | 2 | 5.29 | | 3 | 5.28 | | 4 | 512132.28 | | 5 | 512132.28 | +----+-----------+ 5 rows in set (0.00 sec) mysql> insert into L2(salary) values(512132123.232183); ERROR 1264 (22003): Out of range value for column 'salary' at row 1char(m)定长字符串，m代表字符串的长度，最多容纳255个字符。
定长：即使内容小于m，也会占用m的长度。如char(5)，数据是：yes，底层也是占用5个字符，而不是yes的3个；如果超出m长度限制，则报错（默认严格模式报错）
注意：默认底层储存是固定的长度（不够则用空格补齐），但是查询数据时，会自动将空白去除。如过想要保存空白，在sql-mode中加入 PAD_CHAR_TO_FULL_LENGTH 即可。
查看模式sql-mode的命令是 ： show variables like 'sql_mode';char一般适用于固定长度的内容
如果在配置文件中加入如下配置，
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"保存并重启，则将MySQL设置为非严格模式，此时超过长度则自动截断（不报错）。
varchar(m)变长字符串，m代表字符串的长度，最多可容纳65535个字符。
注：在utf8中，一个中文占3个字节，则varchar可以容纳 65535/3 个汉字
变长：内容小于m时，会按照真实长度储存；如果超过m的限制则报错
texttext数据用于保存变长的大字符串，可以组躲到65535 (2**16 -1)个字符。
一般情况下，长文本会用text类型。如文章、新闻等。
datetimeYYYY-MM-DD HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59)timestamp
YYYY-MM-DD HH:MM:SS (1970-01-01 00:00:00/2037年)
TIMESTAMP是把客户端插入的时间从当前时区转化为UTC（世界标准时间）。进行存储，查询时，再将其又转化为客户端当前时区进行返回
对于DATATIME，则不做任何改变，原样输入输出
mysql> create table L3( -> id int not null primary key auto_increment, -> dt datetime, -> tt timestamp -> )default charset=utf8; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> insert into L3(dt,tt) values("2025-11-11 11:11:44", "2025-11-11 11:11:44"); Query OK, 1 row affected (0.00 sec) mysql> select * from L3; +----+---------------------+---------------------+ | id | dt | tt | +----+---------------------+---------------------+ | 1 | 2025-11-11 11:11:44 | 2025-11-11 11:11:44 | +----+---------------------+---------------------+ 1 row in set (0.00 sec) mysql> show variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set, 1 warning (0.00 sec) mysql> set time_zone='+0:00'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | | | time_zone | +00:00 | +------------------+--------+ 2 rows in set, 1 warning (0.00 sec) mysql> select * from L3; +----+---------------------+---------------------+ | id | dt | tt | +----+---------------------+---------------------+ | 1 | 2025-11-11 11:11:44 | 2025-11-11 03:11:44 | +----+---------------------+---------------------+ 1 row in set (0.00 sec) mysql>
1.4 所有数据类型
常见数值类型：
类型 大小 范围（有符号） 范围（无符号） 用途 TINYINT 1 Bytes (-128，127) (0，255) 小整数值 SMALLINT 2 Bytes (-32 768，32 767) (0，65 535) 大整数值 MEDIUMINT 3 Bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值 INT或INTEGER 4 Bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 BIGINT 8 Bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 FLOAT 4 Bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值 DOUBLE 8 Bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值 DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值 日期和时间类型
类型 大小 ( bytes) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳 字符串类型
类型 大小 用途 CHAR 0-255 bytes 定长字符串 VARCHAR 0-65535 bytes 变长字符串 TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串 TINYTEXT 0-255 bytes 短文本字符串 BLOB 0-65 535 bytes 二进制形式的长文本数据 TEXT 0-65 535 bytes 长文本数据 MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据 LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295 bytes 极大文本数据
1.5 数据行基本操作
新增数据
insert into 表名 (列名，列名，列名) values(对应列的值，对应列的值，对应列的值);insert into tb1(name,password) values('sb','123123'); insert into tb1(name,password) values('sb','123123'),('nt','123'); insert into tb1 values('sb','123123'),('nt','123'); --如果表中只有两列删除数据
delete from 表名; delete from 表名 where 条件;delete from tb1; delete from tb1 where name="sb"; delete from tb1 where name="sb" and password="123"; delete from tb1 where id>9;修改数据
update 表名 set 列名=值; update 表名 set 列名=值 where 条件;update tb1 set name="sb"; update tb1 set name="sb" where id=1; update tb1 set age=age+1 where id=2; -- 在id=2的地方让age+1 update users set name=concat(name,"123") where id=2; -- concat时用于拼接字符串的函数查询数据
select * from 表名; select 列名,列名,列名 from 表名; select 列名,列名 as 别名,列名 from 表名; select * from 表名 where 条件;select * from tb1; select id,name,age from tb1; select id,name as N,age from tb1; select id,name as N,age,111 from tb1; select * from tb1 where id = 1; select * from tb1 where id > 1; select * from tb1 where id != 1; select * from tb1 where name="sb" and password="123";mysql> select * from tb1; +------+------+------+----------+ | id | name | age | password | +------+------+------+----------+ | 1 | sb | 11 | 123 | | 2 | nt | 14 | 321 | | 3 | zb | 18 | 3221 | +------+------+------+----------+ 3 rows in set (0.00 sec) mysql> select id,name,age from tb1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | sb | 11 | | 2 | nt | 14 | | 3 | zb | 18 | +------+------+------+ 3 rows in set (0.00 sec) mysql> select id,name as N,age from tb1; +------+------+------+ | id | N | age | +------+------+------+ | 1 | sb | 11 | | 2 | nt | 14 | | 3 | zb | 18 | +------+------+------+ 3 rows in set (0.00 sec) mysql> select id,name as N,age,111 from tb1; +------+------+------+-----+ | id | N | age | 111 | +------+------+------+-----+ | 1 | sb | 11 | 111 | | 2 | nt | 14 | 111 | | 3 | zb | 18 | 111 | +------+------+------+-----+ 3 rows in set (0.00 sec) mysql> select * from tb1 where id = 1; +------+------+------+----------+ | id | name | age | password | +------+------+------+----------+ | 1 | sb | 11 | 123 | +------+------+------+----------+ 1 row in set (0.00 sec) mysql> select * from tb1 where id > 1; +------+------+------+----------+ | id | name | age | password | +------+------+------+----------+ | 2 | nt | 14 | 321 | | 3 | zb | 18 | 3221 | +------+------+------+----------+ 2 rows in set (0.00 sec) mysql> select * from tb1 where id != 1; +------+------+------+----------+ | id | name | age | password | +------+------+------+----------+ | 2 | nt | 14 | 321 | | 3 | zb | 18 | 3221 | +------+------+------+----------+ 2 rows in set (0.00 sec) mysql> select * from tb1 where name="sb" and password="123"; +------+------+------+----------+ | id | name | age | password | +------+------+------+----------+ | 1 | sb | 11 | 123 | +------+------+------+----------+ 1 row in set (0.00 sec)
1.6 常见约束
1.6.1 主键约束
关键字：PRIMARY KEY
主键约束是一个列或多个列的组合，其值能唯一标识表中的每一行，方便在RDBMS（关系型数据库）中尽快地找到某一行。
主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。
每个表最多只允许一个主键
当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引
创建主键约束：
create table 表名(
字段名类型PRIMARY KEY
);create table 表名(
字段名类型,
constraint pk1 PRIMARY KEY(要设为主键的字段名)
);创建联合主键：
所谓联合主键，就是这个主键是由一张表中多个字段组成的。
create table 表名(
...
primary key (字段1,字段2,...)
);如1.2的alter命令概述所示，我们可以还在修改表时添加主键
alter table 表名 add primary key(字段名)；如需删除主键约束，则使用
alter table 数据库名 drop primary key;1.6.2 自增约束
关键字：AUTO_INCREMENT
在定义为自增長后，这个主键的值就不再需要用户自行输入数据了，而是由数据库系统自动赋值。
默认情况下，auto_increment的初始值为1，每新增一条记录，字段值+1.
一个表中只能由一个字段使用自增约束，且该字段必须有唯一索引，以避免序号重复
自增约束的字段必须具备NOT NULL属性
自增约束的字段只接受整形,自增约束的最大值受该字段的数据类型约束，超过上限则约束失效
create table 表名(
字段名类型AUTO_INCREMENT
);1.6.3 非空约束
关键字：NOT NULL
非空约束指字段的值不能为空。对于使用了非空约束的字段，如果在添加数据时没有指定值，则系统会报错
create table 表名(
字段名类型NOT NULL
);1.6.4 唯一约束
关键字：UNIQUE
唯一约束指的是所有记录中字段的值不能重复出现。如id字段加上唯一约束后，每个id都是唯一的，不能出现重复的情况。
create table 表名(
字段名类型UNIQUE
);1.6.5 默认约束
关键字：DEFAULT
默认约束用来约束某列的默认值
create table 表名(
字段名类型DEFAULT 默认值
);1.6.6 零填充约束
关键字：ZEROFILL
在插入数据时，当该字段的值长度小于定义的长度时，会在该值的前面补上相应的0
zerofill默认为int(10)
当使用零填充约束时，默认会自动添加unsigned(无符号)属性，使用unsigned属性后，数值范围是原值的2倍。可参考1.4
create table 表名(
字段名类型zerofill
);1.6.7 检查约束
关键字：check
检查约束是用来检查数据表中字段值有效性的一种手段，设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。
create table 表名(
字段名类型check(条件)
);如要在性别字段限定用户填写男、女，则
create table sex(
sex varchar(2)check(sex in ('男','女'));
);2. PyMySQL操作
2.1 PyMySQL基本操作
import pymysql
# 打开数据库连接
conn = pymysql.connect(host='localhost', user='root', password='123456', database='userdb')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = conn.cursor()
# 使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print("Database version : %s " % data)
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
conn.commit()
except:
# 如果发生错误则回滚
conn.rollback()
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > %s" % (1000)
try:
# 执行SQL语句
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# 打印结果
print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
(fname, lname, age, sex, income ))
except:
print ("Error: unable to fetch data")
# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
# 执行SQL语句
cursor.execute(sql)
# 提交到数据库执行
conn.commit()
except:
# 发生错误时回滚
conn.rollback()
# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
# 执行SQL语句
cursor.execute(sql)
# 提交修改
conn.commit()
except:
# 发生错误时回滚
conn.rollback()
# 关闭数据库连接
conn.close()2.2 实例：用户管理系统
首先用mysql终端创建一个数据库和表结构
create database if not exists userdb default charset utf8 collate utf8_general_ci;
use userdb;
create table users(
id int not null primary key auto_increment,
name varchar(32),
password varchar(64)
)default charset=utf8;代码如下
import pymysql
def register():
print("=====用户注册=====")
user = input("请输入用户名：")
passwd = input("请输入密码：")
conn = pymysql.connect(host='localhost', user='root', password='123456', database='userdb')
cursor = conn.cursor()
newUser = f'insert into users(name,password) values("{user}","{passwd}")'
cursor.execute(newUser)
conn.commit()
cursor.close()
conn.close()
print(f"注册成功，你的用户名是{user},你的密码是{passwd}")
def login():
print("=====用户登陆=====")
user = input("请输入用户名：")
passwd = input("请输入密码：")
conn = pymysql.connect(host='localhost', user='root', password='123456', database='userdb')
cursor = conn.cursor()
checkUser = f'select * from users where name="{user}" and password="{passwd}" '
cursor.execute(checkUser)
result = cursor.fetchone()
cursor.close()
conn.close()
if result:
print("登陆成功")
else:
print("登陆失败")
def main():
choice = input("1.注册 \n2.登陆\n请选择：")
if choice == '1':
register()
elif choice == '2':
login()
else:
print("输入有误！")
if __name__ == '__main__':
main()2.3 SQL注入
什么是SQL注入？
如上文的用户管理系统
user = input("请输入用户名：")
passwd = input("请输入密码：")
conn = pymysql.connect(host='localhost', user='root', password='123456', database='userdb')
cursor = conn.cursor()
checkUser = f'select * from users where name="{user}" and password="{passwd}" '
cursor.execute(checkUser)
result = cursor.fetchone()
cursor.close()
conn.close()如果用户在输入user时，输入了” or 1=1 --,这样即使密码不存在，也可以通过验证。
为什么？
因为在SQL拼接时，拼接后结果如下：
-- 原代码
select * from users where name="user" and password="passwd"
-- 拼接后
select * from users where name="” or 1=1 --" and password="passwd"
-- 意为空名字↑ 当1=1时↑ ↑用--注释掉后面的语句 如何避免SQL注入？
以python为例，不要在python中使用字符串格式化，而使用pymysql的execute方法
user = input("请输入用户名：")
passwd = input("请输入密码：")
conn = pymysql.connect(host='localhost', user='root', password='123456', database='userdb')
cursor = conn.cursor()
#newUser = f'insert into users(name,password) values("{user}","{passwd}")' 不要用字符串格式化
#cursor.execute("select * from users where name=%s and password=%s", [user, passwd]) execute列表方法
#cursor.execute("select * from users where name=%(name)s and password=%(passwd)s", {"name":user,"password":passwd}) execute列表方法
result = cursor.fetchone()
print(result)
cursor.close()
conn.close()3. 必备SQL和表关系及授权
3.1 必备SQL语句
先创建如下两张表
create table depart(
id int not null auto_increment primary key,
title varchar(16) not null
)default charset=utf8;
create table info(
id int not null auto_increment primary key,
name varchar(16) not null,
email varchar(32) not null,
age int,
depart_id int
)default charset=utf8;往两张表中插入以下数据
insert into depart(title) values("开发"),("运营"),("销售");
insert into info(name,email,age,depart_id) values("sb1","[email protected]",16,1);
insert into info(name,email,age,depart_id) values("sb2","[email protected]",14,1);
insert into info(name,email,age,depart_id) values("sb3","[email protected]",15,2);
insert into info(name,email,age,depart_id) values("sb4","[email protected]",12,1);
insert into info(name,email,age,depart_id) values("sb5","[email protected]",17,3);
insert into info(name,email,age,depart_id) values("sb6","[email protected]",12,1);
insert into info(name,email,age,depart_id) values("sb7","[email protected]",15,1);mysql> select * from depart;
+----+-------+
| id | title |
+----+-------+
| 1 | 开发 |
| 2 | 运营 |
| 3 | 销售 |
+----+-------+
3 rows in set (0.00 sec)
mysql> select * from info;
+----+------+-------------+------+-----------+
| id | name | email | age | depart_id |
+----+------+-------------+------+-----------+
| 1 | sb1 | [email protected] | 16 | 1 |
| 2 | sb2 | [email protected] | 14 | 1 |
| 3 | sb3 | [email protected] | 15 | 2 |
| 4 | sb4 | [email protected] | 12 | 1 |
| 5 | sb5 | [email protected] | 17 | 3 |
| 6 | sb6 | [email protected] | 12 | 1 |
| 7 | sb7 | [email protected] | 15 | 1 |
+----+------+-------------+------+-----------+
7 rows in set (0.00 sec)3.1.1 条件
根据条件搜索结果
select * from info where age > 15;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id !> 1;
select * from info where between 2 and 4; -- 4>=id>=2
select * from info where name = 'sb1' and age = 16;
select * from info where name = 'sb2' or age = 14;
select * from info where (name = 'sb7' or email="[email protected]") and age=15;
select * from info where id in (1,4,6);
select * from info where not in (1,4,6);
select * from info where id in (select id from depart);
# select * from info where id in (1,2,3);
select * from info where exists (select * from depart where id=5);
# exists (select * from depart where id=5) 查询数据是否存在，如果存在，执行结果，如果不存在，则不搜索
select * from info where not exists (select * from depart where id=5);
select * from (select * from info where id>5) as T where T.age > 10;
# 先找info表中id>5的数做成临时表T，再从中取age>10的值select * from info where id > 10;
select * from info where info.id > 10;
#查询结果相同，通常用第一种，第二种适用于同时查询多表，如：
select * from info,depart where info.id > 10;3.1.2 通配符
一般用于模糊搜索。
% 代指多个字符
_ 代指一个字符
两者能够拼接使用
select * from info where name like "%b%";
select * from info where name like "%b1";
select * from info where email like "%@qq.com";
select * from info where name like "s%1";
select * from info where email like "g123@%";
select * from info where email like "[email protected]"; --email列中的数据都不止一个字符，故搜不到数据
select * from info where email like "[email protected]";
select * from info where email like "[email protected]";
select * from info where email like "[email protected]";注意：数据量大的搜索不建议使用，效率低
3.1.3 映射
想要获取的列。
select * from info;
select id, name from info;
select id, name as NM from info;
select id, name as NM, 123 from info;
select
id,
name,
666 as num,
(select max(id) from depart) as max_id, -- max/min/sum
(select min(id) from depart) as min_id, -- max/min/sum
age
from info;
mysql> select id,name,666 as num, (select max(id) from depart) as max_id, (select min(id) from depart) as min_id,age from info;
+----+------+-----+--------+--------+------+
| id | name | num | max_id | min_id | age |
+----+------+-----+--------+--------+------+
| 1 | sb1 | 666 | 3 | 1 | 16 |
| 2 | sb2 | 666 | 3 | 1 | 14 |
| 3 | sb3 | 666 | 3 | 1 | 15 |
| 4 | sb4 | 666 | 3 | 1 | 12 |
| 5 | sb5 | 666 | 3 | 1 | 17 |
| 6 | sb6 | 666 | 3 | 1 | 12 |
| 7 | sb7 | 666 | 3 | 1 | 15 |
+----+------+-----+--------+--------+------+
7 rows in set (0.00 sec)select
id,
name,
(select title from depart where depart.id=info.id) as x1,
(select title from depart where depart.id=info.depart_id) as x2
from info;
#效率低，用得少，了解即可
mysql> select id,name,(select title from depart where depart.id=info.id) as x1,(select title from depart where depart.id=info.depart_id) as x2 from info;
+----+------+------+------+
| id | name | x1 | x2 |
+----+------+------+------+
| 1 | sb1 | 开发 | 开发 |
| 2 | sb2 | 运营 | 开发 |
| 3 | sb3 | 销售 | 运营 |
| 4 | sb4 | NULL | 开发 |
| 5 | sb5 | NULL | 销售 |
| 6 | sb6 | NULL | 开发 |
| 7 | sb7 | NULL | 开发 |
+----+------+------+------+
7 rows in set (0.00 sec)select
id,
name,
case depart_id when 1 then "第一部门" end v1
-- 选择depart_id且当等于1时创建一个名为v1的列，且等于1的值设为第一部门
from info;
+----+------+----------+
| id | name | v1 |
+----+------+----------+
| 1 | sb1 | 第一部门 |
| 2 | sb2 | 第一部门 |
| 3 | sb3 | NULL |
| 4 | sb4 | 第一部门 |
| 5 | sb5 | NULL |
| 6 | sb6 | 第一部门 |
| 7 | sb7 | 第一部门 |
+----+------+----------+
select
id,
name,
case depart_id when 1 then "第一部门" else "其他" end v2
-- 选择depart_id且当等于1时创建一个名为v1的列，且等于1的值设为第一部门，不等于1则的值设为其他
from info;
+----+------+----------+
| id | name | v2 |
+----+------+----------+
| 1 | sb1 | 第一部门 |
| 2 | sb2 | 第一部门 |
| 3 | sb3 | 其他 |
| 4 | sb4 | 第一部门 |
| 5 | sb5 | 其他 |
| 6 | sb6 | 第一部门 |
| 7 | sb7 | 第一部门 |
+----+------+----------+
select
id,
name,
case depart_id when 1 then "第一部门" when 2 then "第二部门" else "其他" end v3
-- 选择depart_id且当等于1时创建一个名为v1的列，且等于1的值设为第一部门，且等于2的值设为第二部门，不等于1,2则的值设为其他
from info;
+----+------+----------+
| id | name | v3 |
+----+------+----------+
| 1 | sb1 | 第一部门 |
| 2 | sb2 | 第一部门 |
| 3 | sb3 | 第二部门 |
| 4 | sb4 | 第一部门 |
| 5 | sb5 | 其他 |
| 6 | sb6 | 第一部门 |
| 7 | sb7 | 第一部门 |
+----+------+----------+
select
id,
name,
case depart_id when 1 then "第一部门" end v1,
case depart_id when 1 then "第一部门" else "其他" end v2,
case depart_id when 1 then "第一部门" when 2 then "第二部门" else "其他" end v3,
case when age<18 then "少年" end v4,
case when age<18 then "少年" else "成年" end v5,
case when age<18 then "少年" when age<30 then "青年" else "成年" end v6
from info;
+----+------+----------+----------+----------+------+------+------+
| id | name | v1 | v2 | v3 | v4 | v5 | v6 |
+----+------+----------+----------+----------+------+------+------+
| 1 | sb1 | 第一部门 | 第一部门 | 第一部门 | 少年 | 少年 | 少年 |
| 2 | sb2 | 第一部门 | 第一部门 | 第一部门 | 少年 | 少年 | 少年 |
| 3 | sb3 | NULL | 其他 | 第二部门 | 少年 | 少年 | 少年 |
| 4 | sb4 | 第一部门 | 第一部门 | 第一部门 | 少年 | 少年 | 少年 |
| 5 | sb5 | NULL | 其他 | 其他 | 少年 | 少年 | 少年 |
| 6 | sb6 | 第一部门 | 第一部门 | 第一部门 | 少年 | 少年 | 少年 |
| 7 | sb7 | 第一部门 | 第一部门 | 第一部门 | 少年 | 少年 | 少年 |
+----+------+----------+----------+----------+------+------+------+3.1.4 排序
select * from info order by age desc; -- 按照age列倒序排列 从大到小
select * from info order by age asc; -- 顺序从小到大
select * from info order by id desc;
select * from info order by id asc;
select * from info order by age asc,id desc; -- 优先按照age从小到大排序;若age相同则再按照id从大到小排序
select * from info where id>10 order by age asc,id desc;
select * from info where id>6 or name like "%y" order by age asc,id desc;
-- 先拿到结果再进行排序(where后再order by)3.1.5 取部分
select * from info limit 5;-- 取前5条数据
select * from info order by id desc limit 3;-- 先倒序排序，再获取前3条数据
select * from info where id>10 order by id desc limit 3;-- 先取id>10后倒序排序，再获取前3条数据
select * from info limit 3 offset 2;-- 从位置2开始，先后获取前3条数据offset 和 limit 实际应用：
如网站分页：
- 第一页：
select * from info limit 10 offset 0; - 第二页：
select * from info limit 10 offset 10; - 第三页：
select * from info limit 10 offset 20; - 第四页：
select * from info limit 10 offset 30; - ······
3.1.6 分组
select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;
mysql> select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;
+------+---------+---------+-----------+---------+---------+
| age | max(id) | min(id) | count(id) | sum(id) | avg(id) |
+------+---------+---------+-----------+---------+---------+
| 16 | 1 | 1 | 1 | 1 | 1.0000 |
| 14 | 2 | 2 | 1 | 2 | 2.0000 |
| 15 | 7 | 3 | 2 | 10 | 5.0000 |
| 12 | 6 | 4 | 2 | 10 | 5.0000 |
| 17 | 5 | 5 | 1 | 5 | 5.0000 |
+------+---------+---------+-----------+---------+---------+
5 rows in set (0.00 sec)select age,count(1) from info group by age;-- 统计每个年龄的人数
mysql> select age,count(1) from info group by age;
+------+----------+
| age | count(1) |
+------+----------+
| 16 | 1 |
| 14 | 1 |
| 15 | 2 |
| 12 | 2 |
| 17 | 1 |
+------+----------+
5 rows in set (0.00 sec)select depart_id,count(id) from info group by depart_id;
select depart_id,count(id) from info group by depart_id having count(id) > 2;
-- 分组之后，想要对聚合条件继续搜索，要使用having而不是where
mysql> select depart_id,count(id) from info group by depart_id;
+-----------+-----------+
| depart_id | count(id) |
+-----------+-----------+
| 1 | 5 |
| 2 | 1 |
| 3 | 1 |
+-----------+-----------+
3 rows in set (0.00 sec)
mysql> select depart_id,count(id) from info group by depart_id having count(id) > 2;
+-----------+-----------+
| depart_id | count(id) |
+-----------+-----------+
| 1 | 5 |
+-----------+-----------+
1 row in set (0.00 sec)select age,count(id) from info group by age having count(id)>2;
select age,count(id) from info where id>4 group by age having count(id)>2; -- 聚合条件放在having后到目前为止SQL执行顺序：
where
group by
having
order by
limitselect age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
-- 查询表info
-- 条件 id>2
-- 根据age分组
-- 对分组后的数据再根据聚合条件过滤count(id)>1
-- 根据age从大到小排序
-- 获取第1条
mysql> select age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
+------+-----------+
| age | count(id) |
+------+-----------+
| 15 | 2 |
+------+-----------+
1 row in set (0.00 sec)3.1.7 左右连表
多个表可以连接起来进行查询。
如展示用户信息&部门名称：
左外连表
取左边的表的全部，右边的表按条件，符合的显示，不符合则显示null
主表 left outer join 从表 on 主表.x = 从表.idselect * from info left outer join depart on info.depart_id = depart.id;
mysql> select * from info left outer join depart on info.depart_id = depart.id;
+----+------+-------------+------+-----------+------+-------+
| id | name | email | age | depart_id | id | title |
+----+------+-------------+------+-----------+------+-------+
| 1 | sb1 | [email protected] | 16 | 1 | 1 | 开发 |
| 2 | sb2 | [email protected] | 14 | 1 | 1 | 开发 |
| 3 | sb3 | [email protected] | 15 | 2 | 2 | 运营 |
| 4 | sb4 | [email protected] | 12 | 1 | 1 | 开发 |
| 5 | sb5 | [email protected] | 17 | 3 | 3 | 销售 |
| 6 | sb6 | [email protected] | 12 | 1 | 1 | 开发 |
| 7 | sb7 | [email protected] | 15 | 1 | 1 | 开发 |
+----+------+-------------+------+-----------+------+-------+
7 rows in set (0.00 sec)
select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;
mysql> select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;
+----+------+-------------+-------+
| id | name | email | title |
+----+------+-------------+-------+
| 1 | sb1 | [email protected] | 开发 |
| 2 | sb2 | [email protected] | 开发 |
| 3 | sb3 | [email protected] | 运营 |
| 4 | sb4 | [email protected] | 开发 |
| 5 | sb5 | [email protected] | 销售 |
| 6 | sb6 | [email protected] | 开发 |
| 7 | sb7 | [email protected] | 开发 |
+----+------+-------------+-------+
7 rows in set (0.00 sec)右外连表
取右边的表的全部，左边的表按条件，符合的显示，不符合则显示null
从表 right outer join 主表 on 主表.x = 从表.idselect info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;
mysql> select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;
+------+------+-------------+-------+
| id | name | email | title |
+------+------+-------------+-------+
| 7 | sb7 | [email protected] | 开发 |
| 6 | sb6 | [email protected] | 开发 |
| 4 | sb4 | [email protected] | 开发 |
| 2 | sb2 | [email protected] | 开发 |
| 1 | sb1 | [email protected] | 开发 |
| 3 | sb3 | [email protected] | 运营 |
| 5 | sb5 | [email protected] | 销售 |
+------+------+-------------+-------+
8 rows in set (0.00 sec)为了更直观地看到效果，我们在depart表中额外插入一条数据。
insert into depart(title) values("运维");这样一来主从表就有区别：
info主表，就以info的数据为主，depart为辅。
select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id; mysql> select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id; +----+------+-------------+-------+ | id | name | email | title | +----+------+-------------+-------+ | 1 | sb1 | [email protected] | 开发 | | 2 | sb2 | [email protected] | 开发 | | 3 | sb3 | [email protected] | 运营 | | 4 | sb4 | [email protected] | 开发 | | 5 | sb5 | [email protected] | 销售 | | 6 | sb6 | [email protected] | 开发 | | 7 | sb7 | [email protected] | 开发 | +----+------+-------------+-------+ 7 rows in set (0.00 sec)depart主表，就以depart数据为主，info为辅。
mysql> select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id; +------+------+-------------+-------+ | id | name | email | title | +------+------+-------------+-------+ | 7 | sb7 | [email protected] | 开发 | | 6 | sb6 | [email protected] | 开发 | | 4 | sb4 | [email protected] | 开发 | | 2 | sb2 | [email protected] | 开发 | | 1 | sb1 | [email protected] | 开发 | | 3 | sb3 | [email protected] | 运营 | | 5 | sb5 | [email protected] | 销售 | | NULL | NULL | NULL | 运维 | +------+------+-------------+-------+ 8 rows in set (0.00 sec)因为depart为主表，所以即使info中没有关联信息，也照样显示
平日使用建议都使用左外连接即可
select * from info left outer join depart on ...
select * from depart left outer join info on ...连表也可以简写为：
select * from depart left join info on ...总结：左外连表，主表在前；右外连表，主表在后
内连接
也称为等值连接，返回两张表都满足条件的部分
表 inner join 表 on 条件select * from info inner join depart on info.depart_id=depart.id;
mysql> select * from info inner join depart on info.depart_id=depart.id;
+----+------+-------------+------+-----------+----+-------+
| id | name | email | age | depart_id | id | title |
+----+------+-------------+------+-----------+----+-------+
| 1 | sb1 | [email protected] | 16 | 1 | 1 | 开发 |
| 2 | sb2 | [email protected] | 14 | 1 | 1 | 开发 |
| 3 | sb3 | [email protected] | 15 | 2 | 2 | 运营 |
| 4 | sb4 | [email protected] | 12 | 1 | 1 | 开发 |
| 5 | sb5 | [email protected] | 17 | 3 | 3 | 销售 |
| 6 | sb6 | [email protected] | 12 | 1 | 1 | 开发 |
| 7 | sb7 | [email protected] | 15 | 1 | 1 | 开发 |
+----+------+-------------+------+-----------+----+-------+
7 rows in set (0.00 sec)到目前为止SQL执行顺序：
join
on
where
group by
having
order by
limit3.1.8 联合（上下连表）
select id,title from depart
union
select id,name from info;
-- 列数需相同
mysql> select id,title from depart
-> union
-> select id,name from info;
+----+-------+
| id | title |
+----+-------+
| 1 | 开发 |
| 2 | 运营 |
| 3 | 销售 |
| 4 | 运维 |
| 1 | sb1 |
| 2 | sb2 |
| 3 | sb3 |
| 4 | sb4 |
| 5 | sb5 |
| 6 | sb6 |
| 7 | sb7 |
+----+-------+
11 rows in set (0.00 sec)
select id from depart
union
select id from info;
-- 自动去重
mysql> select id from depart
-> union
-> select id from info;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+----+
7 rows in set (0.00 sec)select id from depart
union all
select id from info;
-- 保留所有（不自动去重）
mysql> select id from depart
-> union all
-> select id from info;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+----+
11 rows in set (0.00 sec)
3.2 表关系
在开发项目时，需要根据业务需求去创建很多的表结构，来实现业务逻辑，一般表结构分三类：
- 单表：单独一张表就可以保存信息
- 一对多：需要两张表来存储信息，且两张表存在
一对多或多对一的关系 - 多对多：需要三张表来存储信息，两张单表+关系表，创造出两个单表之间
多对多关系
在开发中往往还会为他们添加一个外键约束，保证某一个列的值必须是其他表中的特定列已存在的值，例如：info.depart_id的值必须是depart.id中已经存在的值。
一对多示例：
create table depart(
id int not null auto_increment primary key,
title varchar(16) not null
)default charset=utf8;
create table info(
id int not null auto_increment primary key,
name varchar(16) not null,
email varchar(32) not null,
age int,
depart_id int not null,
constraint fk_info_depart foreign key (depart_id) references depart(id)
)default charset=utf8;
-- constrain行解释：
constraint fk_info_depart foreign key (depart_id) references depart(id)
-- 约束 命名：外键缩写_当前表_关联表 表示是外键 当前info表中的字段名关联的是另一张表的字段如果表已经创建过了，想要额外增加外键：
alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);如果想要删除外键：
alter table info drop foreign key fk_info_depart;多对多示例：
create table boy(
id int not null auto_increment primary key,
name varchar(16) not null
)default charset=utf8;
create table girl(
id int not null auto_increment primary key,
name varchar(16) not null
)default charset=utf8;
create table boy_girl(
id int not null auto_increment primary key,
boy_id int not null,
girl_id int not null,
constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id),
constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id)
)default charset=utf8;如果表已经创建过了，想要额外增加外键：
alter table boy_girl add constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id);
alter table boy_girl add constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id);如果想要删除外键：
alter table boy_girl drop foreign key fk_boy_girl_boy;
alter table boy_girl drop foreign key fk_boy_girl_girl;3.3 授权
之前我们无论是基于python代码或自带客户端去连接MySQL时，均使用的是root账户，拥有操作数据库的所有权限。
如果有多个程序的数据库都放在同一个MySQL中，如果都给予程序root权限，那么就存在巨大的风险。
如何解决？
MySQL中支持创建用户，并且给账户分配权限。如：
- 只拥有数据库A操作的权限
- 只拥有数据库B中某些表的权限
- 只拥有数据库B中某些表的读权限
- 等等······
3.3.1 用户管理
MySQL的自带库mysql中的user表中存储着所有的用户信息(含账户、权限等)。
mysql> select user,authentication_string,host from mysql.user;
+------------------+------------------------------------------------------------------------+-----------+
| user | authentication_string | host |
+------------------+------------------------------------------------------------------------+-----------+
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
+------------------+------------------------------------------------------------------------+-----------+
4 rows in set (0.00 sec)创建和删除用户
create user '用户名'@'连接者的ip' identified by '密码'; drop user '用户名'@'连接者的ip';-- 允许本机ip登陆 create user [email protected] identified by '123456'; drop user [email protected]; -- 允许127.0.0.x ip段登陆 create user ma1akas@'127.0.0.%' identified by '123456'; drop user ma1akas@'127.0.0.%'; -- 允许所有ip段登陆 create user ma1akas@'%' identified by '123456'; drop user ma1akas@'%'; -- 使用符号需要加上引号，所以一般情况下可以两者都加 create user 'ma1akas'@'%' identified by '123456'; drop user 'ma1akas'@'%';修改用户
rename user '用户名'@'ip地址' to '新用户名'@'新ip地址';rename user [email protected] to ma1akas@localhost; rename user 'ma1akas'@'%' to 'ma1akas'@'localhost';修改密码
set password for '用户名'@'ip地址' = Password('新密码');set password for 'ma1akas'@'%' = Password('123123');
3.3.2 授权管理
创建好用户后，就可以对用户进行授权了。
授权
grant all privileges on *.* tO 'ma1akas'@'localhost'; -- 用户ma1akas拥有所有数据库的所有权限 grant all privileges on day26.* to 'ma1akas'@'localhost'; -- 用户ma1akas拥有数据库day26的所有权限 grant all privileges on day26.info to 'ma1akas'@'localhost'; -- 用户ma1akas拥有数据库day26中info表的所有权限 grant select on day26.info to 'ma1akas'@'localhost';-- 用户ma1akas拥有数据库day26中info表的查询权限 grant select,insert on day26.* tO 'ma1akas'@'localhost'; -- 用户ma1akas拥有数据库day26所有表的查询和插入权限 grant all privileges on day26.* to 'ma1akas'@'localhost'; flush privileges; -- 将数据读取到内存中，从而立即生效。对于权限
all privileges除grant外的所有权限 select仅查权限 select,insert查和插入权限 usage无访问权限 alter使用alter table alter routine使用alter procedure和drop procedure create使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user、drop user、rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile和load data infile grant option 使用grant和revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用对于数据库和表
数据库名.* 数据库中的所有 数据库名.表名 指定数据库中的某张表 数据库名.存储过程名 指定数据库中的存储过程 *.* 所有数据库
查看授权
show grants for '用户'@'ip地址';show grants for 'ma1akas'@'localhost';取消授权
revoke 权限 on 数据库.表 from '用户'@'ip地址';revoke ALL PRIVILEGES on day26.* from 'ma1akas'@'localhost';
3.4 数据库备份
注：数据库备份是在终端下，而不是mysql里进行备份的。
导出整个数据库结构和数据
mysqldump -h ip地址 -u 用户 -p 密码 数据库名 > 你想要备份的路径及文件名.sqlmysqldump -h localhost -uroot -p123456 db_test > d:/test.sql导出单个数据库表结构和数据
mysqldump -h ip地址 -u 用户 -p 密码 数据库名 表名 > 你想要备份的路径及文件名.sqlmysqldump -h localhost -uroot -p123456 db_test table > d:/test_table.sql导出整个数据库结构（不包含数据）
mysqldump -h ip地址 -u 用户 -p 密码 -d 数据库名 > 你想要备份的路径及文件名.sqlmysqldump -h localhost -uroot -p123456 -d db_test > d:/test.sql导出单个数据表结构（不包含数据）
mysqldump -h ip地址 -u 用户 -p 密码 -d 数据库名 表名 > 你想要备份的路径及文件名.sqlmysqldump -h localhost -uroot -p123456 -d db_test table > d:/test_table.sql
4. 索引
索引在数据库中最核心的作用是：提高对表中数据的查询速度。
例如：在含有300w条数据的表中查询，无索引需要700秒，而利用索引可能只需要1秒。
4.1 索引原理
为什么加上索引后速度提升这么大？这是因为索引的底层是基于B+Tree的数据结构存储的。
4.1.1 非聚簇索引(mysiam引擎)
4.1.2 聚簇索引(innodb引擎)
4.2 常见索引
在innodb引擎下，索引底层都是基于B+Tree数据结构存储(聚簇索引)。
- 主键索引：加速查找、不能为空、不能重复。 +联合主键索引
- 唯一索引：加速查找、不能重复。 +联合唯一索引
- 普通索引：加速查找。 +联合索引
4.2.1 主键和联合主键索引
create table 表名(
id int not null auto_increment primary key,
name varchar(32) not null
);
create table 表名(
id int not null auto_increment,
name varchar(32) not null,
primary key(id)
);
create table 表名(
id int not null auto_increment,
name varchar(32) not null,
primary key(列1，列2)-- 如果有多列 称为联合主键(不常用，且myisam引擎支持)
);alter table 表名 add primary key(列名); alter table 表名 drop primary key;注意：删除索引可能会报错，自增列必须定义为键。
alter table 表名 change id id int not null;4.2.2 唯一和联合唯一索引
create table 表名(
id int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
unique ix_name (name),
unique ix_email (email)
);
create table 表名(
id int not null auto_increment primary key,
name varchar(32) not null,
unique (列1，列2)-- 如果有多列，称为联合索引
);create unique index 索引名 on 表名(列名)drop unique index 索引名 on 表名;4.2.3 索引和联合索引
create table 表名(
id int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
index ix_email (email),
index ix_name (name)
);
create table 表名(
id int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
index ix_email (name,email)
);create index 索引名 on 表名(列名);drop index 索引名 on 表名;4.3 操作表
在表中创建索引后，查询时一定要命中索引。
在数据库的表中创建索引后的优缺点如下：
- 优点：查找速度快、约束（唯一、主键、联合唯一）
- 缺点：插入、删除、更新速度慢，因为每次操作都需要调整整个B+Tree的数据结构关系。
所以，在表中不要无节制地创建索引。
以下情况可能导致索引未命中，以表info为例：
类型不一致
select * from info where username = 123; -- 未命中 select * from info where email = 123; -- 未命中 主键索引的话，即使类型不一致也能命中： select * from big where id = "123"; -- 命中使用不等于
select * from info where username != "alex"; -- 未命中 select * from info where email != "[email protected]"; -- 未命中 主键索引的话，即使是不等于也能命中： select * from info where id !=123; -- 命中or，当or条件中有为建立索引地列则索引会失效。
select * from info where id = 123 or password="xx"; -- 未命中 select * from info where username = "alex" or password="xx";-- 未命中 如果在未索引的列后面and一个索引列，则能够命中索引： select * from info where id = 10 or password="xx" and name="xx"-- 命中排序，当根据索引排序时，如果选择的映射不是索引，则不走索引。
select * from info order by name asc; -- 未命中 select * from info order by name desc; -- 未命中 主键无论怎么排序都走索引： select * from info order by id desc; -- 命中like 模糊匹配时
select * from info where name like "%xx"; -- 未命中 select * from info where name like "x%x"; -- 未命中 如果通配符在最后面，则可以命中： select * from info where name like "xx%"; -- 未命中使用函数
select * from info where reverse(name) = "alex"; -- 未命中 如果把函数放在后面，则可以命中; select * from info where name = reverse("alex"); -- 命中最左前缀，如果是联合索引，要遵循最左前缀原则。
即从最左边开始用的，都可以走索引。但如果从中间或右边开始(or)，则不走索引
如果联合索引为：(name,password) name='xx' and password='xx'-- 命中 name='xx' -- 命中 password='xx' -- 未命中 name='xx' or password='xx' -- 未命中
4.4 执行计划
MySQL中提供了执行计划，让你能够预判SQL的执行(仅供参考，不一定完全准确预判)
explain SQL语句;其中比较重要的是type的值，他是SQL性能比较重要的标志，性能从低到高依次：
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
- ALL，全表扫描，数据表从头到尾找一遍。(一般未命中索引，都是会执行权标扫描)
- INDEX，全索引扫描，对索引从头到尾找—遍
- RANGE，对索引列进行范围查找
- INDEX_MERGE，合并索引，使用多个单列索引搜索
- REF，根据索引直接去查找（非键)。
- EQ_REF，连表操作时常见。
- CONST，常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快。
- SYSTEM,系统，表仅有一行（=系统表）。这是const连接类型的一个特例。
5. 函数
5.1 MySQL 字符串函数
| 函数 | 描述 | 实例 |
|---|---|---|
| ASCII(s) | 返回字符串 s 的第一个字符的 ASCII 码。 | 返回 CustomerName 字段第一个字母的 ASCII 码：SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers; |
| CHAR_LENGTH(s) | 返回字符串 s 的字符数 | 返回字符串 RUNOOB 的字符数SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString; |
| CHARACTER_LENGTH(s) | 返回字符串 s 的字符数 | 返回字符串 RUNOOB 的字符数SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString; |
| CONCAT(s1,s2...sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 | 合并多个字符串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString; |
| CONCAT_WS(x, s1,s2...sn) | 同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 | 合并多个字符串，并添加分隔符：SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString; |
| FIELD(s,s1,s2...) | 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置 | 返回字符串 c 在列表值中的位置：SELECT FIELD("c", "a", "b", "c", "d", "e"); |
| FIND_IN_SET(s1,s2) | 返回在字符串s2中与s1匹配的字符串的位置 | 返回字符串 c 在指定字符串中的位置：SELECT FIND_IN_SET("c", "a,b,c,d,e"); |
| FORMAT(x,n) | 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。 | 格式化数字 "#,###.##" 形式：SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56 |
| INSERT(s1,x,len,s2) | 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 | 从字符串第一个位置开始的 6 个字符替换为 runoob：SELECT INSERT("google.com", 1, 6, "runoob"); -- 输出：runoob.com |
| LOCATE(s1,s) | 从字符串 s 中获取 s1 的开始位置 | 获取 b 在字符串 abc 中的位置：SELECT LOCATE('st','myteststring'); -- 5返回字符串 abc 中 b 的位置：SELECT LOCATE('b', 'abc') -- 2 |
| LCASE(s) | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写：SELECT LCASE('RUNOOB') -- runoob |
| LEFT(s,n) | 返回字符串 s 的前 n 个字符 | 返回字符串 runoob 中的前两个字符：SELECT LEFT('runoob',2) -- ru |
| LOWER(s) | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写：SELECT LOWER('RUNOOB') -- runoob |
| LPAD(s1,len,s2) | 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len | 将字符串 xx 填充到 abc 字符串的开始处：SELECT LPAD('abc',5,'xx') -- xxabc |
| LTRIM(s) | 去掉字符串 s 开始处的空格 | 去掉字符串 RUNOOB开始处的空格：SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB |
| MID(s,n,len) | 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len) | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| POSITION(s1 IN s) | 从字符串 s 中获取 s1 的开始位置 | 返回字符串 abc 中 b 的位置：SELECT POSITION('b' in 'abc') -- 2 |
| REPEAT(s,n) | 将字符串 s 重复 n 次 | 将字符串 runoob 重复三次：SELECT REPEAT('runoob',3) -- runoobrunoobrunoob |
| REPLACE(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 | 将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE('abc','a','x') --xbc |
| REVERSE(s) | 将字符串s的顺序反过来 | 将字符串 abc 的顺序反过来：SELECT REVERSE('abc') -- cba |
| RIGHT(s,n) | 返回字符串 s 的后 n 个字符 | 返回字符串 runoob 的后两个字符：SELECT RIGHT('runoob',2) -- ob |
| RPAD(s1,len,s2) | 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len | 将字符串 xx 填充到 abc 字符串的结尾处：SELECT RPAD('abc',5,'xx') -- abcxx |
| RTRIM(s) | 去掉字符串 s 结尾处的空格 | 去掉字符串 RUNOOB 的末尾空格：SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB |
| SPACE(n) | 返回 n 个空格 | 返回 10 个空格：SELECT SPACE(10); |
| STRCMP(s1,s2) | 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 | 比较字符串：SELECT STRCMP("runoob", "runoob"); -- 0 |
| SUBSTR(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| SUBSTRING(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| SUBSTRING_INDEX(s, delimiter, number) | 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。 | SELECT SUBSTRING_INDEX('a*b','*',1) -- a SELECT SUBSTRING_INDEX('a*b','*',-1) -- b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c |
| TRIM(s) | 去掉字符串 s 开始和结尾处的空格 | 去掉字符串 RUNOOB 的首尾空格：SELECT TRIM(' RUNOOB ') AS TrimmedString; |
| UCASE(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写：SELECT UCASE("runoob"); -- RUNOOB |
| UPPER(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写：SELECT UPPER("runoob"); -- RUNOOB |
5.2 MySQL 数字函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| ABS(x) | 返回 x 的绝对值 | 返回 -1 的绝对值：SELECT ABS(-1) -- 返回1 |
| ACOS(x) | 求 x 的反余弦值（单位为弧度），x 为一个数值 | SELECT ACOS(0.25); |
| ASIN(x) | 求反正弦值（单位为弧度），x 为一个数值 | SELECT ASIN(0.25); |
| ATAN(x) | 求反正切值（单位为弧度），x 为一个数值 | SELECT ATAN(2.5); |
| ATAN2(n, m) | 求反正切值（单位为弧度） | SELECT ATAN2(-0.8, 2); |
| AVG(expression) | 返回一个表达式的平均值，expression 是一个字段 | 返回 Products 表中Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products; |
| CEIL(x) | 返回大于或等于 x 的最小整数 | SELECT CEIL(1.5) -- 返回2 |
| CEILING(x) | 返回大于或等于 x 的最小整数 | SELECT CEILING(1.5); -- 返回2 |
| COS(x) | 求余弦值(参数是弧度) | SELECT COS(2); |
| COT(x) | 求余切值(参数是弧度) | SELECT COT(6); |
| COUNT(expression) | 返回查询的记录总数，expression 参数是一个字段或者 * 号 | 返回 Products 表中 products 字段总共有多少条记录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products; |
| DEGREES(x) | 将弧度转换为角度 | SELECT DEGREES(3.1415926535898) -- 180 |
| n DIV m | 整除，n 为被除数，m 为除数 | 计算 10 除于 5：SELECT 10 DIV 5; -- 2 |
| EXP(x) | 返回 e 的 x 次方 | 计算 e 的三次方：SELECT EXP(3) -- 20.085536923188 |
| FLOOR(x) | 返回小于或等于 x 的最大整数 | 小于或等于 1.5 的整数：SELECT FLOOR(1.5) -- 返回1 |
| GREATEST(expr1, expr2, expr3, ...) | 返回列表中的最大值 | 返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); -- 34返回以下字符串列表中的最大值：SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob |
| LEAST(expr1, expr2, expr3, ...) | 返回列表中的最小值 | 返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); -- 3返回以下字符串列表中的最小值：SELECT LEAST("Google", "Runoob", "Apple"); -- Apple |
| LN | 返回数字的自然对数，以 e 为底。 | 返回 2 的自然对数：SELECT LN(2); -- 0.6931471805599453 |
| LOG(x) 或 LOG(base, x) | 返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。 | SELECT LOG(20.085536923188) -- 3 SELECT LOG(2, 4); -- 2 |
| LOG10(x) | 返回以 10 为底的对数 | SELECT LOG10(100) -- 2 |
| LOG2(x) | 返回以 2 为底的对数 | 返回以 2 为底 6 的对数：SELECT LOG2(6); -- 2.584962500721156 |
| MAX(expression) | 返回字段 expression 中的最大值 | 返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products; |
| MIN(expression) | 返回字段 expression 中的最小值 | 返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS MinPrice FROM Products; |
| MOD(x,y) | 返回 x 除以 y 以后的余数 | 5 除于 2 的余数：SELECT MOD(5,2) -- 1 |
| PI() | 返回圆周率(3.141593） | SELECT PI() --3.141593 |
| POW(x,y) | 返回 x 的 y 次方 | 2 的 3 次方：SELECT POW(2,3) -- 8 |
| POWER(x,y) | 返回 x 的 y 次方 | 2 的 3 次方：SELECT POWER(2,3) -- 8 |
| RADIANS(x) | 将角度转换为弧度 | 180 度转换为弧度：SELECT RADIANS(180) -- 3.1415926535898 |
| RAND() | 返回 0 到 1 的随机数 | SELECT RAND() --0.93099315644334 |
| ROUND(x) | 返回离 x 最近的整数 | SELECT ROUND(1.23456) --1 |
| SIGN(x) | 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1 | SELECT SIGN(-10) -- (-1) |
| SIN(x) | 求正弦值(参数是弧度) | SELECT SIN(RADIANS(30)) -- 0.5 |
| SQRT(x) | 返回x的平方根 | 25 的平方根：SELECT SQRT(25) -- 5 |
| SUM(expression) | 返回指定字段的总和 | 计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails; |
| TAN(x) | 求正切值(参数是弧度) | SELECT TAN(1.75); -- -5.52037992250933 |
| TRUNCATE(x,y) | 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） | SELECT TRUNCATE(1.23456,3) -- 1.234 |
5.3 MySQL 日期函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| ADDDATE(d,n) | 计算起始日期 d 加上 n 天的日期 | SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25 |
| ADDTIME(t,n) | n 是一个时间表达式，时间 t 加上时间表达式 n | 加 5 秒：SELECT ADDTIME('2011-11-11 11:11:11', 5); ->2011-11-11 11:11:16 (秒)添加 2 小时, 10 分钟, 5 秒:SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26 |
| CURDATE() | 返回当前日期 | SELECT CURDATE(); -> 2018-09-19 |
| CURRENT_DATE() | 返回当前日期 | SELECT CURRENT_DATE(); -> 2018-09-19 |
| CURRENT_TIME | 返回当前时间 | SELECT CURRENT_TIME(); -> 19:59:02 |
| CURRENT_TIMESTAMP() | 返回当前日期和时间 | SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43 |
| CURTIME() | 返回当前时间 | SELECT CURTIME(); -> 19:59:02 |
| DATE() | 从日期或日期时间表达式中提取日期值 | SELECT DATE("2017-06-15"); -> 2017-06-15 |
| DATEDIFF(d1,d2) | 计算日期 d1->d2 之间相隔的天数 | SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32 |
| DATE_ADD(d，INTERVAL expr type) | 计算起始日期 d 加上一个时间段后的日期，type 值可以是：MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH | SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); -> 2017-06-25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE); -> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR); ->2017-06-15 06:34:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR); ->2017-04-15 |
| DATE_FORMAT(d,f) | 按表达式 f的要求显示日期 d | SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -> 2011-11-11 11:11:11 AM |
| DATE_SUB(date,INTERVAL expr type) | 函数从日期减去指定的时间间隔。 | Orders 表中 OrderDate 字段减去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders |
| DAY(d) | 返回日期值 d 的日期部分 | SELECT DAY("2017-06-15"); -> 15 |
| DAYNAME(d) | 返回日期 d 是星期几，如 Monday,Tuesday | SELECT DAYNAME('2011-11-11 11:11:11') ->Friday |
| DAYOFMONTH(d) | 计算日期 d 是本月的第几天 | SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11 |
| DAYOFWEEK(d) | 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 | SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6 |
| DAYOFYEAR(d) | 计算日期 d 是本年的第几天 | SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315 |
| EXTRACT(type FROM d) | 从日期 d 中获取指定的值，type 指定返回的值。 type可取值为： MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH | SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') -> 11 |
| FROM_DAYS(n) | 计算从 0000 年 1 月 1 日开始 n 天后的日期 | SELECT FROM_DAYS(1111) -> 0003-01-16 |
| HOUR(t) | 返回 t 中的小时值 | SELECT HOUR('1:2:3') -> 1 |
| LAST_DAY(d) | 返回给给定日期的那一月份的最后一天 | SELECT LAST_DAY("2017-06-20"); -> 2017-06-30 |
| LOCALTIME() | 返回当前日期和时间 | SELECT LOCALTIME() -> 2018-09-19 20:57:43 |
| LOCALTIMESTAMP() | 返回当前日期和时间 | SELECT LOCALTIMESTAMP() -> 2018-09-19 20:57:43 |
| MAKEDATE(year, day-of-year) | 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 | SELECT MAKEDATE(2017, 3); -> 2017-01-03 |
| MAKETIME(hour, minute, second) | 组合时间，参数分别为小时、分钟、秒 | SELECT MAKETIME(11, 35, 4); -> 11:35:04 |
| MICROSECOND(date) | 返回日期参数所对应的微秒数 | SELECT MICROSECOND("2017-06-20 09:34:00.000023"); -> 23 |
| MINUTE(t) | 返回 t 中的分钟值 | SELECT MINUTE('1:2:3') -> 2 |
| MONTHNAME(d) | 返回日期当中的月份名称，如 November | SELECT MONTHNAME('2011-11-11 11:11:11') -> November |
| MONTH(d) | 返回日期d中的月份值，1 到 12 | SELECT MONTH('2011-11-11 11:11:11') ->11 |
| NOW() | 返回当前日期和时间 | SELECT NOW() -> 2018-09-19 20:57:43 |
| PERIOD_ADD(period, number) | 为 年-月 组合日期添加一个时段 | SELECT PERIOD_ADD(201703, 5); -> 201708 |
| PERIOD_DIFF(period1, period2) | 返回两个时段之间的月份差值 | SELECT PERIOD_DIFF(201710, 201703); -> 7 |
| QUARTER(d) | 返回日期d是第几季节，返回 1 到 4 | SELECT QUARTER('2011-11-11 11:11:11') -> 4 |
| SECOND(t) | 返回 t 中的秒钟值 | SELECT SECOND('1:2:3') -> 3 |
| SEC_TO_TIME(s) | 将以秒为单位的时间 s 转换为时分秒的格式 | SELECT SEC_TO_TIME(4320) -> 01:12:00 |
| STR_TO_DATE(string, format_mask) | 将字符串转变为日期 | SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10 |
| SUBDATE(d,n) | 日期 d 减去 n 天后的日期 | SELECT SUBDATE('2011-11-11 11:11:11', 1) ->2011-11-10 11:11:11 (默认是天) |
| SUBTIME(t,n) | 时间 t 减去 n 秒的时间 | SELECT SUBTIME('2011-11-11 11:11:11', 5) ->2011-11-11 11:11:06 (秒) |
| SYSDATE() | 返回当前日期和时间 | SELECT SYSDATE() -> 2018-09-19 20:57:43 |
| TIME(expression) | 提取传入表达式的时间部分 | SELECT TIME("19:30:10"); -> 19:30:10 |
| TIME_FORMAT(t,f) | 按表达式 f 的要求显示时间 t | SELECT TIME_FORMAT('11:11:11','%r') 11:11:11 AM |
| TIME_TO_SEC(t) | 将时间 t 转换为秒 | SELECT TIME_TO_SEC('1:12:00') -> 4320 |
| TIMEDIFF(time1, time2) | 计算时间差值 | mysql> SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01 mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', -> '2000:01:01 00:00:00.000001'); -> '-00:00:00.000001' mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001', -> '2008-12-30 01:01:01.000002'); -> '46:58:57.999999' |
| TIMESTAMP(expression, interval) | 单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 | mysql> SELECT TIMESTAMP("2017-07-23", "13:10:11"); -> 2017-07-23 13:10:11 mysql> SELECT TIMESTAMP('2003-12-31'); -> '2003-12-31 00:00:00' mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00'); -> '2004-01-01 00:00:00' |
| TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) | 计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差 | mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少天 -> 89 mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少月 -> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); // 计算两个时间相隔多少年 -> -1 mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); // 计算两个时间相隔多少分钟 -> 128885 |
| TO_DAYS(d) | 计算日期 d 距离 0000 年 1 月 1 日的天数 | SELECT TO_DAYS('0001-01-01 01:01:01') -> 366 |
| WEEK(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 | SELECT WEEK('2011-11-11 11:11:11') -> 45 |
| WEEKDAY(d) | 日期 d 是星期几，0 表示星期一，1 表示星期二 | SELECT WEEKDAY("2017-06-15"); -> 3 |
| WEEKOFYEAR(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 | SELECT WEEKOFYEAR('2011-11-11 11:11:11') -> 45 |
| YEAR(d) | 返回年份 | SELECT YEAR("2017-06-15"); -> 2017 |
| YEARWEEK(date, mode) | 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 | SELECT YEARWEEK("2017-06-15"); -> 201724 |
5.4 MySQL 高级函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| BIN(x) | 返回 x 的二进制编码 | 15 的 2 进制编码:SELECT BIN(15); -- 1111 |
| BINARY(s) | 将字符串 s 转换为二进制字符串 | SELECT BINARY "RUNOOB"; -> RUNOOB |
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END | CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 | SELECT CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END ->1 > 0 |
| CAST(x AS type) | 转换数据类型 | 字符串日期转换为日期：SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29 |
| COALESCE(expr1, expr2, ...., expr_n) | 返回参数中的第一个非空表达式（从左向右） | SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com'); -> runoob.com |
| CONNECTION_ID() | 返回唯一的连接 ID | SELECT CONNECTION_ID(); -> 4292835 |
| CONV(x,f1,f2) | 返回 f1 进制数变成 f2 进制数 | SELECT CONV(15, 10, 2); -> 1111 |
| CONVERT(s USING cs) | 函数将字符串 s 的字符集变成 cs | SELECT CHARSET('ABC') ->utf-8 SELECT CHARSET(CONVERT('ABC' USING gbk)) ->gbk |
| CURRENT_USER() | 返回当前用户 | SELECT CURRENT_USER(); -> guest@% |
| DATABASE() | 返回当前数据库名 | SELECT DATABASE(); -> runoob |
| IF(expr,v1,v2) | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 | SELECT IF(1 > 0,'正确','错误') ->正确 |
| IFNULL(v1,v2) | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 | SELECT IFNULL(null,'Hello Word') ->Hello Word |
| ISNULL(expression) | 判断表达式是否为 NULL | SELECT ISNULL(NULL); ->1 |
| LAST_INSERT_ID() | 返回最近生成的 AUTO_INCREMENT 值 | SELECT LAST_INSERT_ID(); ->6 |
| NULLIF(expr1, expr2) | 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 | SELECT NULLIF(25, 25); -> |
| SESSION_USER() | 返回当前用户 | SELECT SESSION_USER(); -> guest@% |
| SYSTEM_USER() | 返回当前用户 | SELECT SYSTEM_USER(); -> guest@% |
| USER() | 返回当前用户 | SELECT USER(); -> guest@% |
| VERSION() | 返回数据库的版本号 | SELECT VERSION() -> 5.6.34 |
5.5 自定义函数
MySQL也支持自定义函数。
创建函数
delimiter $$-- 用delimiter将终止符由;变成$$ create function fl( i1 int, i2 int ) returns int BEGIN declare num int; declare maxId int; select max(id) from big into maxId; set num = i1 + i2 + maxId; return(num); END $$ delimiter ;-- 用delimiter将终止符变回;执行函数
select f1(11,id)； select f1(11,id),name from dl;删除函数
drop function fl;
6. 存储过程
存储过程，是一个存储在MySQL中的SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。
创建存储过程
delimiter $$ create procedure p1() BEGIN select * from d1; END $$ delimiter ;执行存储过程
call p1();#!/usr/bin/enc python # -*- coding:utf8 -*_ import pymysql conn = pymysql.connect(host='localhost', user='root', password='123456', database='userdb') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.callproc('p1') result = cursor.fetchall() cursor.close() conn.close() print(result)删除存储过程
drop procedure proc_name;
6.1 参数类型
存储过程的参数可以有如下三种：
- in，仅用于传入参数用
- out，仅用于返回值用
- inout，既可传入又可以当做返回值
delimiter $$
create procedure p2(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
BEGIN
DECLARE temp1 int;
DECLARE temp2 int default 0;
set temp1 = 1;
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;
end $$
delimiter ;set @t1 = 4;-- 设置变量t1=4
set @t2 = 0;
CALL p2(1,2,@t1,@t2);
SELECT @t1,@t2;#!/usr/bin/enc python
# -*- coding:utf8 -*_
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='123456', database='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p2',args = (1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p2_0, @_p2_1, @_p2_2, @_p2_3,")
result = cursor.fetchall()
cursor.close()
conn.close()
print(result)
6.2 返回值 & 结果集
delimiter $$
create procedure p3(
in n1 int,
inout n2 int,
out n3 int
)
BEGIN
set n2 = n1 + 100;
set n3 = n2 + n1 + 100;
select * from d1;
END $$
delimiter ;set @t1 = 4;
set @t2 = 0;
CALL p3(1, @t1， @t2);
SELECT @t1,@t2;#!/usr/bin/enc python
# -*- coding:utf8 -*_
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='123456', database='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p3',args = (22, 3, 4))
table = cursor.fetchall() # 得到执行存储过中的结果集
# 获取执行完存储的参数
cursor.execute("select @_p3_0, @_p3_1, @_p3_2")
rets = cursor.fetchall()
cursor.close()
conn.close()
print(table)
print(rets)6.3 事务 & 异常
事务：成功都成功，失败都失败
delimiter $$
create PROCEDURE p4(
out p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;-- 开启事务
DELETE from d1;
insert into tb(name) values('seven');
COMMIT;-- 提交事务
-- SUCCESS
set p_return_code = 0;
END $$
delimiter ;set @ret = 100;
CALL p4(@ret);
SELECT @ret;#!/usr/bin/enc python
# -*- coding:utf8 -*_
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='123456', database='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p4',args = (100))
table = cursor.fetchall()
# 获取执行完存储的参数
cursor.execute("select @_p4_0")
rets = cursor.fetchall()
cursor.close()
conn.close()
print(table)
print(rets)6.4 游标
delimiter $$
create procedure p5()
begin
declare sid int;
declare sname varchar(50);
declare done int default false;
declare my_cursor CURSOR FOR select id,name from d1;-- 创建一个为了查询id,name的游标名为my_cursor
declare continue handler for not found set done = TRUE;
open my_cursor;-- 开启游标
xxoo: LOOP-- 开启一个名为xxoo的循环
fetch my_cursor into sid,sname; -- 查询d1表中的每一行，并赋值给sid，sname
IF done then-- 如果done = TRUE
leave xxoo;-- 离开循环
END IF;
insert into t1(name) values(sname);-- 把从d1获得的值插入给t1
end loop xxoo;
close my_cursor;-- 关闭游标
end $$
delimiter ;call p5();7. 视图
视图其实是一个虚拟表(非真实存在)，本质上是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。
SELECT
*
FROM
(SELECT nid,name FROM tb1 WHERE nid>2) AS A
WHERE
A.name > 'alex';创建视图
create view v1 as select id,name from d1 where id > 1;使用视图
select * from v1;删除视图
drop view v1;修改视图
alter view v1 as SQL语句注意：基于视图只能查询，针对视图不能执行增加、修改、删除。如果源表发生变化，视图表也会发生变化。
8. 触发器
对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器。
-- 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
-- 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
-- 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
-- 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
-- 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
-- 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END删除触发器
DROP TRIGGER 触发器名;示例：
在t1表中插入数据之前，先在t2表中插入一行数据。
delimiter $$ CREATE TRIGGER tri_before_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN IF NEW.name = 'alex' THEN INSERT INTO t2(name) VALUES(NEW.id); END IF; END $$ delimiter ;在t2表中删除数据之后，再在t1表中插入一行数据。
delimiter $$ CREATE TRIGGER tri_after_delete_t1 AFTER DELETE ON t1 FOR EACH ROW BEGIN IF OLD.name = 'alex' THEN INSERT INTO t2(name) VALUES(OLD.id); END IF; END $$ delimiter ;new表示新数据，old表示原来的数据
9. 事务
innodb引擎中支持事务，myisam不支持
事务具有的四大特性(ACID):
原子性(Atomicity)
原子性是指事务包含的所有操作不可分割，要么全都成功，要么全都失败回滚。一致性(Consistency)
执行的前后数据的完整性保持一致。隔离性(Isolation)
一个事务执行的过程中，不应该受到其他事务的干扰。持久性(Durability)
事务一旦结束，数据就持久到数据库
9.1 事务的隔离级别
事务的隔离级别分为以下四个级别
从上到下，代表事务的关联程度越来越稀疏
READ UNCOMMITTED读未提交一个事务可以读取另一个未提交的数据，最低级别，任何情况都无法保证，会造成脏读。
READ COMMITTED读提交一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读。
REPEATABLE READ可重复读就是在开始读取数据（事务开始）时，不再允许修改操作，可避免脏读、不可重复读的发生，但会造成幻读。
SERIALIZABLE序列化是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
MySQL中，默认的隔离级别是可重复读；而Oracle中，默认的隔离级别是读提交。
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED 读未提交 | 是 | 是 | 是 |
| READ COMMITTED 读提交 | 否 | 是 | 是 |
| REPEATABLE READ 可重复读 | 否 | 否 | 是 |
| SERIALIZABLE 序列化 | 否 | 否 | 否 |
脏读
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读
是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
幻读
第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行。
-- 查看隔离级别
show variables like '%isolation%';
-- 设置隔离级别
set session transaction isolation level 级别字符串；
-- 级别字符串 ：READ UNCOMMITTED 、 READ COMMITTED 、 REPEATEABLE READ 、 SERIALIZABLE隔离级别测试：
创建测试数据库isolate与数据表account，并插入数据
create table account(
id int primary key auto_increment,
name varchar(10) not null,
money int
);
insert into account(name,money) values('zhangsan',0),('lisi',1000);读未提交测试
-- 设置MySQL的事务为手动提交（关闭自动提交）
select @@autocommit;
set autocommit = 0;
-- 两个终端都修改隔离级别为读未提交并开启事务
-- 注意 ！ 要两边都修改完事务等级后才能开始事务！！！
set session transaction isolation level READ UNCOMMITTED;
begin;
-- A Client
update account set money = money + 8000 where name = 'zhangsan';
-- B Client
select * from account where name = 'zhangsan';
+----+----------+-------+
| id | name | money |
+----+----------+-------+
| 1 | zhangsan | 8000 |
+----+----------+-------+
1 row in set (0.00 sec)
-- A
rollback；
-- B
select * from account where name = 'zhangsan';
+----+----------+-------+
| id | name | money |
+----+----------+-------+
| 1 | zhangsan | 0 |
+----+----------+-------+
-- A,B
commit;
-- 结论：这种隔离级别会引起脏读，B事务读取到A事务没有提交的数据读提交测试
-- 两个终端都修改隔离级别为读提交并开启事务
set session transaction isolation level READ COMMITTED;
begin;
-- A
update account set money = money - 1000 where name = 'lisi';
-- B
select * from account where name = 'lisi';
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | lisi | 0 |
+----+------+-------+
-- A
commit;
-- B
select * from account where name = 'lisi';
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | lisi | 0 |
+----+------+-------+
commit;
select * from account where name = 'lisi';
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | lisi | 0 |
+----+------+-------+
-- 结论：这种隔离级别会引起不可重复读，B事务在没有提交事务期间，读取到A事务操作数据是不同的可重复读测试
-- 两个终端都修改隔离级别为可重复读并开启事务
set session transaction isolation level REPEATABLE READ;
begin;
-- A
update account set money = money - 999 where name = 'lisi';
-- B
select * from account where name = 'lisi';
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | lisi | 1000 |
+----+------+-------+
-- A
commit;
-- B
select * from account where name = 'lisi';
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | lisi | 1000 |
+----+------+-------+
commit;
select * from account where name = 'lisi';
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | lisi | 1 |
+----+------+-------+
-- 结论：这种隔离级别会引起幻读，B事务在提交之前和提交之后看到的数据不一致序列化测试
-- 两个终端都修改隔离级别序列化并开启事务
set session transaction isolation level SERIALIZABLE;
begin;
-- A
update account set money = money + 1000 where name = 'zhangsan';
-- B
select * from account where name = 'lisi';
-- 这里会被锁住卡住不动，如果超时则抛出异常
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- A
commit;
-- B
select * from account where name = 'zhangsan';
+----+----------+-------+
| id | name | money |
+----+----------+-------+
| 1 | zhangsan | 1000 |
+----+----------+-------+
commit;
-- 结论：这种隔离级别比较安全，但是效率低，A事务操作表时，表会被锁起，B事务不能操作9.2 MySQL操作事务
CREATE TABLE `users`(
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(32) DEFAULT NULL,
`amount` int(11) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;+----+------+--------+
| id | name | amount |
+----+------+--------+
| 1 | 张三 | 10000 |
| 2 | 李四 | 20000 |
| 3 | 王五 | 100 |
+----+------+--------+例如：张三给李四转账100，那就会涉及2个步骤。
- 张三账户减100
- 李四账户加100
这两个步骤必须同时完成才算完成，如果其中一步完成，另一步失败，那么还是回滚到初始状态，也就是要么一起成功，要么一起失败
mysql> select * from users;
+----+------+--------+
| id | name | amount |
+----+------+--------+
| 1 | 张三 | 10000 |
| 2 | 李四 | 20000 |
| 3 | 王五 | 100 |
+----+------+--------+
3 rows in set (0.00 sec)
mysql> begin;-- 开启事务 也可用start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update users set amount=amount-2 where id=1;-- 执行操作
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update users set amount=amount+2 where id=2;-- 执行操作
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;-- 提交事务，如果不想提交想恢复，可以使用 rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users;
+----+------+--------+
| id | name | amount |
+----+------+--------+
| 1 | 张三 | 9998 |
| 2 | 李四 | 20002 |
| 3 | 王五 | 100 |
+----+------+--------+
3 rows in set (0.00 sec)
9.3 PyMySQL 操作事务
#!/usr/bin/enc python
# -*- coding:utf8 -*_
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='123456', database='userdb')
cursor = conn.cursor()
# 开启事务
conn.begin()
try:
cursor.execute("update users set amount=1 where id=1")
int('asdf')
cursor.execute("update tran set amount=2 where id=2")
except Exception as e:
# 回滚
print("回滚")
conn.rollback()
else:
# 提交
print("提交")
conn.commit()
cursor.close()
conn.close()
10. 锁
在用MySQL时，如果同时有很多做更新、插入、删除动作，MySQL怎么确保数据不出错呢？
MySQL中自带了锁的功能，可以帮助我们实现开发过程中遇到的同时处理数据的情况。对于数据库中的锁，从锁的范围来讲有：
- 表级锁，即A操作表时，其他人对整个表都不能操作，等待A操作完后，才能继续。
- 行级锁，即A操作表时，其他人对指定的行数据不能操作，其他行可以操作，等待A操作完后，才能继续。
MYISAM支持表锁，不支持行锁；
InnoDB支持行锁和表锁。
即：在MYISAM下如果要加锁，无论怎么加都是表锁。
而在InnoDB引擎支持下如果是基于索引查询的数据则是行级锁，否则就是表锁。
所以，一般情况下我们会选择使用innodb引擎，并且在搜索时也会使用索引(命中索引)。
接下来的操作都是基于innodb引擎来操作：
CREATE TABLE `l4`(
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(255) DEFAULT NULL,
`amount` int(11) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;+----+------+-------+
| id | name | count |
+----+------+-------+
| 1 | 张三 | 10000 |
| 2 | 李四 | 20000 |
| 3 | 王五 | 100 |
+----+------+-------+在innodb引擎中，update、insert、delete的行为内部都会先申请锁(排他锁)，申请到之后才执行相关操作，最后再释放锁。
所以，在当多个人同时向数据库执行insert、update、delete等操作时，内部加锁后会排队逐一执行。
而select则默认不会申请锁。
如果，你想要让select去申请锁，则需要配合事务+特殊语法来实现。
for update，排它锁，加锁之后，其他不可以读写。begin; select * from l4 where name = "张三" for update;-- name 列不是索引(表锁) commit;begin; select * from l4 where id = 1 for update; -- id 列是索引(行锁)lock in share mode，共享锁，加锁之后，其他可读但不可写。begin; select * from l4 where name = "张三" lock in share mode; -- 假设name列不是索引(表锁) commit;begin; select * from l4 where id=1 lock in share mode;-- id列是索引(行锁) commit;
10.1 排它锁
排它锁（for update），加锁之后，其他事务不可以读写。
应用场景：总共100件商品，每次购买一件需要让商品个数减1.
A : 访问页面查看商品剩余 100
B : 访问页面查看商品剩余 100
此时A、B同时下单，那么他们同时执行SQL:
update goods set count=count-1 where id=3
由于Innodb引擎内部会加锁，所以他们两个即使同一时刻执行，内部也会排序逐步执行。
但是，当商品剩余1个时，就需要注意了。
A : 访问页面查看商品剩余 1
B : 访问页面查看商品剩余 1
此时A、B同时下单，那么他们同时执行sQL:
update goods set count=count-1 where id=3
这样剩余数量就会出现-1，很显然这是不正确的，所以应该怎么办呢?
这种情况下，可以利用排它锁，在更新之前先查询剩余数量，只有数量 >0 才可以购买，所以，下单时应该执行:
begin;
select count from goods where id=3 for update;
-- 获取个数进行判断
if 个数>0;
update goods set count=count-1 where id=3;
else:
-- 已售罄
commit;基于python演示：
#!/usr/bin/enc python
# -*- coding:utf8 -*_
import pymysql
import threading
def task():
conn = pymysql.connect(host='localhost', user='root', password='123456', database='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #让fetch的结果以字典展示。{列名:值}，fetchall则是元组套字典
conn.begin()
cursor.execute("select age from tran where id=2 for update")
result = cursor.fetchone()
current_age = result['age']
if current_age > 0:
cursor.execute("update tran set age=age-1 where id=2")
else:
print("已售罄")
conn.commit()
cursor.close()
conn.close()
def run():
for i in range(5):
t = threading.Thread(target=task)
t.start()
if __name__ == '__main__':
run()10.2 共享锁
共享锁（lock in share mode），可以读，但不允许写。
加锁之后，后续其他事务可以继续读，但不允许写，因为写的默认也会加锁。
没啥用，没啥好说的。
