画布 1
Mysql
  Mysql入门
    密码
      设置密码
        set password = password(" 密码 ")；
      忘记密码的重置方法
        1：修改配置文件,在 [mysqld] 节点下添加 skip-grant-tables=1
        2:重启MySQL，再次登录时，不需要密码直接可以进去net stop mysql57
net start mysql57
        3:重启后，无需密码就可以进入。
        4:进入数据库后执行修改密码命令
          use mysql;
update user set authentication_string = password('新密码'),password_last_changed=now() where user='root';
        5:退出并再次修改配置文件，删除 [mysqld] 节点下的 `skip-grant-tables=1`
          [mysqld]
# skip-grant-tables=1
        6:再次重启mysql服务，以后就可以使用新密码登录了。
    数据库 管理
      查看当前所有的数据库：
        show databases;
      创建数据库：
        create database 数据库名;
        create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
      删除数据库：
        drop database 数据库名;
      进入数据（进入文件）：
        use 数据库;
        进入mysql数据库（文件夹），查看此数据库下的所有表。
          show tables;
      退出：
        exit；
      python代码的操作（本质上和在cmd里面输入的一样）
        import pymysql
# 连接MySQL（socket）
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")
cursor = conn.cursor()
# 1. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))
# 2. 创建数据库（新增、删除、修改）
# 发送指令
cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")
conn.commit()
# 3. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('db3',), ('mysql',), ('performance_schema',), ('sys',))
# 4. 删除数据库
# 发送指令
cursor.execute("drop database db3")
conn.commit()
# 3. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))
# 5. 进入数据库，查看表
# 发送指令
cursor.execute("use mysql")
cursor.execute("show tables")
result = cursor.fetchall()
print(result) # (('columns_priv',), ('db',), ('engine_cost',), ('event',), ('func',), ('general_log',),....
# 关闭连接
cursor.close()
conn.close()
    数据表管理
      进入数据库 `use 数据库;`，查看当前所有表：`show tables;`
      创建表结构
        create table 表名(
列名 类型,
列名 类型,
列名 类型
)default charset=utf8;
        create table tb4(
id int primary key, -- 主键（不允许为空、不能重复）
name varchar(16) not null, -- 不允许为空
email varchar(32) null, -- 允许为空（默认）
age int default 3 -- 插入数据时，如果不给age列设置值，默认值：3
)default charset=utf8;
        主键
          主键一般用于表示当前这条数据的ID编号（类似于人的身份证），需要我们自己来维护一个不重复的值，比较繁琐。所以，在数据库中一般会将主键和自增结合。
            create table tb5(
id int not null auto_increment primary key, -- 不允许为空 & 主键 & 自增
name varchar(16) not null, -- 不允许为空
email varchar(32) null, -- 允许为空（默认）
age int default 3 -- 插入数据时，如果不给age列设置值，默认值：3
)default charset=utf8;
            注意：一个表中只能有一个自增列【自增列，一般都是主键】
      删除表
        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 id int not null default 5;
alter table tb change id id int not null primary key auto_increment;
alter table tb change id id int; -- 允许为空，删除默认值，删除自增。
        修改列 默认值
          ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;
        删除列 默认值
          ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
        添加主键
          alter table 表名 add primary key(列名);
        删除主键
          alter table 表名 drop primary key;
      常见列类型
        int[(m)][unsigned][zerofill]
          int 表示有符号，取值范围：-2147483648 ～ 2147483647
int unsigned 表示无符号，取值范围：0 ～ 4294967295
int(5)zerofill 仅用于显示，当不满足5位时，按照左边补0，例如：00002；满足时，正常显示。
          cmd代码
            mysql> create table L1(id int, uid int unsigned, zid int(5) zerofill) default charset=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into L1(id,uid,zid) values(1,2,3);
Query OK, 1 row affected (0.00 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(214748364100,4294967294,300000);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql>
        tinyint[(m)] [unsigned] [zerofill]
          有符号，取值范围：-128 ～ 127.
无符号，取值范围：0 ～ 255
        bigint[(m)][unsigned][zerofill]
          有符号，取值范围：-9223372036854775808 ～ 9223372036854775807
无符号，取值范围：0 ～ 18446744073709551615
        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;
          cmd代码
            mysql> create table L2(id int not null primary key auto_increment,salary decimal(8,2))default charset=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into L2(salary) values(1.28);
Query OK, 1 row affected (0.01 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(5121321.283);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql>
        FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
          单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数。
        DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
          双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
        char(m)
          讲解
            定长字符串，m代表字符串的长度，最多可容纳255个字符。
定长的体现：即使内容长度小于m，也会占用m长度。例如：char(5)，数据是：yes，底层也会占用5个字符；如果超出m长度限制（默认MySQL是严格模式，所以会报错）。
如果在配置文件中加入如下配置，
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。。
注意：默认底层存储是固定的长度（不够则用空格补齐），但是查询数据时，会自动将空白去除。 如果想要保留空白，在sql-mode中加入 PAD_CHAR_TO_FULL_LENGTH 即可。
查看模式sql-mode，执行命令：show variables like 'sql_mode';
一般适用于：固定长度的内容。
create table L3(
id int not null primary key auto_increment,
name varchar(5),
depart char(3)
)default charset=utf8;
insert into L3(name,depart) values("alexsb","sbalex");
        varchar(m)
          讲解
            变长字符串，m代表字符串的长度，最多可容纳65535个字节。
变长的体现：内容小于m时，会按照真实数据长度存储；如果超出m长度限制（（默认MySQL是严格模式，所以会报错）。
如果在配置文件中加入如下配置，
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。
例如：
create table L3(
id int not null primary key auto_increment,
name varchar(5),
depart char(3)
)default charset=utf8;
          cmd代码
            mysql> create table L3(id int not null primary key auto_increment,name varchar(5),depart char(3))default charset=utf8;
Query OK, 0 rows affected (0.03 sec)
-- 插入多行
mysql> insert into L3(name,depart) values("wu","WU"),("wupei","ALS");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from L3;
+----+-------+--------+
| id | name | depart |
+----+-------+--------+
| 1 | wu | WU |
| 2 | wupei | ALS |
+----+-------+--------+
2 rows in set (0.00 sec)
-- 非严格模式下，不会报错。
mysql> insert into L3(name,depart) values("wupeiqi","ALS");
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into L3(name,depart) values("wupei","ALSB");
ERROR 1406 (22001): Data too long for column 'depart' at row 1
mysql>
-- 如果 sql-mode 中加入了 PAD_CHAR_TO_FULL_LENGTH ，则查询时char时空白会保留。
mysql> select name,length(name),depart,length(depart) from L3;
+-------+--------------+--------+----------------+
| name | length(name) | depart | length(depart) |
+-------+--------------+--------+----------------+
| wu | 2 | WU | 3 |
| wupei | 5 | ALS | 3 |
+-------+--------------+--------+----------------+
4 rows in set (0.00 sec)
mysql>
        text
          讲解
            text数据类型用于保存变长的大字符串，可以最多到65535 (2**16 − 1)个字符。
一般情况下，长文本会用text类型。例如：文章、新闻等。
          代码
            create table L4(
id int not null primary key auto_increment,
title varchar(128),
content text
)default charset=utf8;
        mediumtext
          A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
        longtext
          A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1)
        datetime
          YYYY-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（世界标准时间）进行存储，查询时，将其又转化为客户端当前时区进行返回。
对于DATETIME，不做任何改变，原样输入和输出。
          代码
            mysql> create table L5(
-> id int not null primary key auto_increment,
-> dt datetime,
-> tt timestamp
-> )default charset=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into L5(dt,tt) values("2025-11-11 11:11:44", "2025-11-11 11:11:44");
mysql> select * from L5;
+----+---------------------+---------------------+
| 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 (0.00 sec)
-- “CST”指的是MySQL所在主机的系统时间，是中国标准时间的缩写，China Standard Time UT+8:00
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 | CST |
| time_zone | +00:00 |
+------------------+--------+
2 rows in set (0.01 sec)
mysql> select * from L5;
+----+---------------------+---------------------+
| id | dt | tt |
+----+---------------------+---------------------+
| 1 | 2025-11-11 11:11:44 | 2025-11-11 03:11:44 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
        date
          YYYY-MM-DD（1000-01-01/9999-12-31）
        time
          HH:MM:SS（'-838:59:59'/'838:59:59'）
      python代码操作
        import pymysql
# 连接MySQL
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")
cursor = conn.cursor()
# 1. 创建数据库
"""
cursor.execute("create database db4 default charset utf8 collate utf8_general_ci")
conn.commit()
"""
# 2. 进入数据库、查看数据表
"""
cursor.execute("use db4")
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
"""
# 3. 进入数据库创建表
cursor.execute("use db4")
sql = """
create table L4(
id int not null primary key auto_increment,
title varchar(128),
content text,
ctime datetime
)default charset=utf8;
"""
cursor.execute(sql)
conn.commit()
# 4. 查看数据库中的表
"""
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
"""
# 5. 其他 drop table... 略过
# 关闭连接
cursor.close()
conn.close()
    数据行（修改数据）
      新增数据
        insert into 表名 (列名,列名,列名) values(对应列的值,对应列的值,对应列的值);
        insert into tb1(name,password) values('武沛齐','123123');
insert into tb1(name,password) values('武沛齐','123123'),('alex','123');
insert into tb1 values('武沛齐','123123'),('alex','123'); -- 如果表中只有2列
      删除数据
        delete from 表名;
delete from 表名 where 条件;
        delete from tb1;
delete from tb1 where name="wupeiqi";
delete from tb1 where name="wupeiqi" and password="123";
delete from tb1 where id>9;
      修改数据
        update 表名 set 列名=值;
update 表名 set 列名=值 where 条件;
        update tb1 set name="wupeiqi";
update tb1 set name="wupeiqi" where id=1;
update tb1 set age=age+1; -- 整型
update tb1 set age=age+1 where id=2;
update L3 set name=concat(name,"db");
update L3 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="wupeiqi" and password="123";
      python代码操作
        import pymysql
# 连接MySQL，自动执行 use userdb; -- 进入数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()
# 1.新增（需commit）
"""
cursor.execute("insert into tb1(name,password) values('武沛齐','123123')")
conn.commit()
"""
# 2.删除（需commit）
"""
cursor.execute("delete from tb1 where id=1")
conn.commit()
"""
# 3.修改（需commit)
"""
cursor.execute("update tb1 set name='xx' where id=1")
conn.commit()
"""
# 4.查询
"""
cursor.execute("select * from tb where id>10")
data = cursor.fetchone() # cursor.fetchall()
print(data)
"""
# 关闭连接
cursor.close()
conn.close()
    关于SQL注入
      首先先明确一点： “--”在mysql内为注释
      会出问题的代码
        代码
          import pymysql
# 输入用户名和密码
user = input("请输入用户名：") # ' or 1=1 --
pwd = input("请输入密码：") # 123
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8",db='usersdb')
cursor = conn.cursor()
# 基于字符串格式化来 拼接SQL语句
# sql = "select * from users where name='alex' and password='123'"
# sql = "select * from users where name='' or 1=1 -- ' and password='123'"
sql = "select * from users where name='{}' and password='{}'".format(user, pwd)
cursor.execute(sql)
result = cursor.fetchone()
print(result) # None，不是None
cursor.close()
conn.close()
        以上代码中，如果输入了 ‘ or 1=1 -- ,这样即使用户输入的密码不存在，也会通过验证，甚至会将数据库中的所有数据全部爆出来，出大问题！
        在sql拼接时，拼接的结果为：select * from users where name='' or 1=1 -- ' and password='123' ，这样他--后面的代码就全部被注释掉了，而这里前面又有个or，即只要又一个是真的就全部是真的，而1=1又显然为真，所以就暴库了！！
      正确的代码（用pymysql的execute方法）
        import pymysql
# 输入用户名和密码
user = input("请输入用户名：")
pwd = input("请输入密码：")
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()
cursor.execute("select * from users where name=%s and password=%s", [user, pwd])
# 或
# cursor.execute("select * from users where name=%(n1)s and password=%(n2)s", {"n1": user, 'n2': pwd})
result = cursor.fetchone()
print(result)
cursor.close()
conn.close()
  必备SQL和表关系及授权
    必备SQL语句
      数据图片
      条件
        根据条件搜索结果
        语法
          select * from info where age > 30;
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 id between 2 and 4; -- id大于等于2、且小于等于4
select * from info where name = '武沛齐' and age = 19;
select * from info where name = 'alex' or age = 49;
select * from info where (name = '李杰' or email="pyyu@live.com") and age=49;
select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);
# select * from info where id in (1,2,3);
# exists select * from depart where id=5，去查数据是否存在，如果存在，如果不存在。
select * from info where 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>2) as T where age > 10;
select * from info where info.id > 10;
select * from info where id > 10;
      通配符
        一般用于模糊搜索，数量少，数据量大的搜索。
        语法
          -- %任意个字符
select * from info where name like "%沛%";
select * from info where name like "%沛";
select * from info where email like "%@live.com";
select * from info where name like "武%齐";
select * from info where name like "k%y";
select * from info where email like "wupeiqi%";
-- _ 匹配一个字符
select * from info where email like "_@live.com";
select * from info where email like "_upeiqi@live.com";
select * from info where email like "__peiqi@live.com";
select * from info where email like "__peiqi_live.co_";
      映射
        用于获取想要的列，并且可以用as来改名字
        语法
          select * from info;
select id, name from info;
select id, name as NM from info;
select id, name as NM, 123 from info;
注意：少写select * ,按照自己需求，要哪几列写几列。
select
id,
name,
666 as num,
( select max(id) from depart ) as mid, -- max/min/sum
( select min(id) from depart) as nid, -- max/min/sum
age
from info;
select
id,
name,
( select title from depart where depart.id=info.depart_id) as x1
from info;
# 注意：效率很低
select
id,
name,
( select title from depart where depart.id=info.depart_id) as x1,
( select title from depart where depart.id=info.id) as x2
from info;
select
id,
name,
case depart_id when 1 then "第1部门" end v1
from info;
select
id,
name,
case depart_id when 1 then "第1部门" else "其他" end v2
from info;
select
id,
name,
case depart_id when 1 then "第1部门" end v1,
case depart_id when 1 then "第1部门" else "其他" end v2,
case depart_id when 1 then "第1部门" when 2 then "第2部门" 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;
        case
          case后面放搜索的列表，或者case后面不放东西，将要搜索的东西放在我很里面。 when+then是一个小的搜索语法，可以写多个并列，else就是不包括于上面的所有的数据，如果没有这句，那么那些没有被匹配到的数据就会返回null，像python里面的if/elif/else，最后的end就是搜索出来的这列的名字。
      排序
        用于排列
        语法
          select * from info order by age desc; -- 倒序
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;
      取部分
        一般要用于获取部分数据
        语法
          select * from info limit 5; -- 获取前5条数据
select * from info order by id desc limit 3; -- 先排序，再获取前3条数据
select * from info where id > 4 order by id desc limit 3; -- 先排序，再获取前3条数据
select * from info limit 3 offset 2; -- 从位置2开始，向后获取前3数据 (2不取)
      分组
        将某些具有 某些共同特点 的数据放在一起
        语法
          select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age; #avg平均 sum求和 count统计有几个
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; #不能用where
select age,name from info group by age; -- 不建议（有错）
select * from info where id in (select max(id) from info group by age);
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后面
        having
          这个语句在上面有体现，这个是对聚合后的列（上面是count（id））再一次进行分组！
        目前为止语句执行的顺序
          到目前为止SQL执行顺序：
where
group by
having
order by
limit
          select 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条
      左右连表
        多个表可以连接起来进行查询
        左连表
          主表 left outer join 从表 on 主表.x = 从表.id
          示例
            select * from info left outer join depart on info.depart_id = depart.id;
select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;
        右连表
          从表 right outer join 主表 on 主表.x = 从表.id
          示例
            select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;
        主从关系
          为了更加直接的查看效果，我们分别在 depart 表 和 info 中额外插入一条数据。
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;
            --------+------------------+-------+
| id | name | email | title |
+----+--------+------------------+-------+
| 1 | 武沛齐 | wupeiqi@live.com | 开发 |
| 2 | 于超 | pyyu@live.com | 开发 |
| 4 | tony | tony@live.com | 开发 |
| 6 | james | james@live.com | 开发 |
| 7 | 李杰 | lijie@live.com | 开发 |
| 3 | alex | alex@live.com | 运营 |
| 5 | kelly | kelly@live.com | 销售 |
+----+--------+------------------+-------+
            on后面的条件是不分前后的，也就是两个东西放在等号的前面或者后面都可以生效。
          depart主表，，就以depart数据为主，info为辅。
            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 |
+------+--------+------------------+-------+
| 1 | 武沛齐 | wupeiqi@live.com | 开发 |
| 2 | 于超 | pyyu@live.com | 开发 |
| 3 | alex | alex@live.com | 运营 |
| 4 | tony | tony@live.com | 开发 |
| 5 | kelly | kelly@live.com | 销售 |
| 6 | james | james@live.com | 开发 |
| 7 | 李杰 | lijie@live.com | 开发 |
| NULL | NULL | NULL | 运维 |
+------+--------+------------------+-------+
        简写
          select * from depart left join info on ....
        内连接
          -- 内连接: 表 inner join 表 on 条件
select * from info inner join depart on info.depart_id=depart.id;
+----+-----------+------------------+------+-----------+----+--------+
| id | name | email | age | depart_id | id | title |
+----+-----------+------------------+------+-----------+----+--------+
| 1 | 武沛齐 | wupeiqi@live.com | 19 | 1 | 1 | 开发 |
| 2 | 于超 | pyyu@live.com | 49 | 1 | 1 | 开发 |
| 3 | alex | alex@live.com | 9 | 2 | 2 | 运营 |
| 4 | tony | tony@live.com | 29 | 1 | 1 | 开发 |
| 5 | kelly | kelly@live.com | 99 | 3 | 3 | 销售 |
| 6 | james | james@live.com | 49 | 1 | 1 | 开发 |
| 7 | 李杰 | lijie@live.com | 49 | 1 | 1 | 开发 |
+----+-----------+------------------+------+-----------+----+--------+
        到目前为止SQL执行顺序：
          到目前为止SQL执行顺序：
join
on
where
group by
having
order by
limit
      联合
        列数需相同
          select id,title from depart
union
select id,name from info;
select id,title from depart
union
select email,name from info;
-- 列数需相同
        自动去重
          select id from depart
union
select id from info;
-- 自动去重
        保留所有
          select id from depart
union all
select id from info;
-- 保留所有
    表关系
      单表
        单表，单独一张表就可以将信息保存。
      一对多
        一对多，需要两张表来存储信息，且两张表存在 `一对多` 或 `多对一`关系。
      多对多
        多对多，需要三张表来存储信息，两张单表 + 关系表，创造出两个单表之间`多对多关系`。
        示例
          代码
            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 info drop foreign key fk_boy_girl_boy;
alter table info drop foreign key fk_boy_girl_girl;
      外键约束
        保证某一个列的值必须是其他表中的特定列已存在的值，例如：`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;
        额外增加外键
          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 database luffy default charset utf8 collate utf8_general_ci;
use luffy;
create table info(
id int not null auto_increment primary key,
username varchar(16) not null,
mobile char(11) not null,
password varchar(64) not null
)default charset=utf8;
create table course(
id int not null auto_increment primary key,
title varchar(16) not null
)default charset=utf8;
create table module(
id int not null auto_increment primary key,
title varchar(16) not null,
course_id int not null,
constraint fk_module_course foreign key (course_id) references course(id)
)default charset=utf8;
create table day(
id int not null auto_increment primary key,
title varchar(16) not null,
module_id int not null,
constraint fk_day_module foreign key (module_id) references module(id)
)default charset=utf8;
create table video(
id int not null auto_increment primary key,
title varchar(16) not null,
day_id int not null,
constraint fk_video_day foreign key (day_id) references day(id)
)default charset=utf8;
create table module_record(
id int not null auto_increment primary key,
user_id int not null,
module_id int not null,
constraint fk_user_id foreign key module_record(user_id) references info(id),
constraint fk_module_id foreign key module_record(module_id) references module(id)
)default charset=utf8;
    授权
      用户管理
        在MySQL的默认数据库 `mysql` 中的 `user` 表中存储着所有的账户信息（含账户、权限等）。
        创建和删除用户
          create user '用户名'@'连接者的IP地址' identified by '密码';
          实例
            create user wupeiqi1@127.0.0.1 identified by 'root123';
drop user wupeiqi1@127.0.0.1;
create user wupeiqi2@'127.0.0.%' identified by 'root123';
drop user wupeiqi2@'127.0.0.%';
create user wupeiqi3@'%' identified by 'root123';
drop user wupeiqi3@'%';
create user 'wupeiqi4'@'%' identified by 'root123';
drop user 'wupeiqi4'@'%';
        修改用户
          rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
          实例
            rename user wupeiqi1@127.0.0.1 to wupeiqi1@localhost;
rename user 'wupeiqi1'@'127.0.0.1' to 'wupeiqi1'@'localhost';
        修改密码
          set password for '用户名'@'IP地址' = Password('新密码')
          实例
            set password for 'wupeiqi4'@'%' = Password('123123');
      授权管理
        授权
          grant 权限 on 数据库.表 to '用户'@'IP地址'
          代码示例
            grant all privileges on *.* TO 'wupeiqi'@'localhost'; -- 用户wupeiqi拥有所有数据库的所有权限
grant all privileges on day26.* TO 'wupeiqi'@'localhost'; -- 用户wupeiqi拥有数据库day26的所有权限
grant all privileges on day26.info TO 'wupeiqi'@'localhost'; -- 用户wupeiqi拥有数据库day26中info表的所有权限
grant select on day26.info TO 'wupeiqi'@'localhost'; -- 用户wupeiqi拥有数据库day26中info表的查询权限
grant select,insert on day26.* TO 'wupeiqi'@'localhost'; -- 用户wupeiqi拥有数据库day26所有表的查询和插入权限
grant all privileges on day26db.* to 'wupeiqi4'@'%';
注意：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 'wupeiqi'@'localhost';
show grants for 'wupeiqi4'@'%';
        取消授权
          revoke 权限 on 数据库.表 from '用户'@'IP地址'
          示例
            revoke ALL PRIVILEGES on day26.* from 'wupeiqi'@'localhost';
revoke ALL PRIVILEGES on day26db.* from 'wupeiqi4'@'%';
注意：flush privileges; -- 将数据读取到内存中，从而立即生效。
  索引 函数 存储过程
    索引
      作用：加速查找
      索引原理
        图片演示
        索引的底层是基于B+Tree的数据结构存储的
        非聚簇索引（mysiam引擎）
          代码
            create table 表名(
id int not null auto_increment primary key,
name varchar(32) not null,
age int
)engine=myisam default charset=utf8;
          图片演示
          存的是内存地址
        聚簇索引（innodb引擎）（最常用）
          代码
            create table 表名(
id int not null auto_increment primary key,
name varchar(32) not null,
age int
)engine=innodb default charset=utf8;
          图片
          数据就直接存在相应的位置了，存的不是内存地址
        上面两种方式在mysql文件存储中的体现
          root@192 userdb # pwd
/usr/local/mysql/data/userdb
root@192 userdb # ls -l
total 1412928
-rw-r----- 1 _mysql _mysql 8684 May 15 22:51 big.frm，表结构。
-rw-r----- 1 _mysql _mysql 717225984 May 15 22:51 big.ibd，数据和索引结构。
-rw-r----- 1 _mysql _mysql 8588 May 16 11:38 goods.frm
-rw-r----- 1 _mysql _mysql 98304 May 16 11:39 goods.ibd
-rw-r----- 1 _mysql _mysql 8586 May 26 10:57 t2.frm，表结构
-rw-r----- 1 _mysql _mysql 0 May 26 10:57 t2.MYD，数据
-rw-r----- 1 _mysql _mysql 1024 May 26 10:57 t2.MYI，索引结构
      常见索引
        常见的索引类型
          - 主键索引：加速查找、不能为空、不能重复。 + 联合主键索引
- 唯一索引：加速查找、不能重复。 + 联合唯一索引
- 普通索引：加速查找。 + 联合索引
        主键和联合主键索引
          代码
            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;
          注意：删除索引时可能会报错，自增列必须定义为键。
            ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
          避免该问题的方法
            alter table 表 change id id int not null; （改变该列的一些参数）
            create table t7(
id int not null,
name varchar(32) not null,
primary key(id)
);
alter table t6 drop primary key;
        唯一和联合唯一索引
          代码
            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,
name varchar(32) not null,
unique (列1,列2) -- 如果有多列，称为联合唯一索引。
);
            create unique index 索引名 on 表名(列名);
            drop unique index 索引名 on 表名;
        索引和联合索引
          代码
            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 表名;
        上面三种综合在一起看
          CREATE TABLE `big` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`email` varchar(64) DEFAULT NULL,
`password` varchar(64) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`), -- 主键索引
UNIQUE KEY `big_unique_email` (`email`), -- 唯一索引
index `ix_name_pwd` (`name`,`password`) -- 联合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8
      操作表
        一般情况
          一般情况下，我们针对只要通过索引列去搜搜都可以 `命中` 索引（通过索引结构加速查找）。
          代码
            select * from big where id = 5;
select * from big where id > 5;
select * from big where email = "wupeiqi@live.com";
select * from big where name = "武沛齐";
select * from big where name = "kelly" and password="ffsijfs";
...
        特殊情况
          类型不一致
            select * from big where name = 123; -- 未命中
select * from big where email = 123; -- 未命中
特殊的主键：
select * from big where id = "123"; -- 命中
          使用不等于
            select * from big where name != "武沛齐"; -- 未命中
select * from big where email != "wupeiqi@live.com"; -- 未命中
特殊的主键：
select * from big where id != 123; -- 命中
          or，当or条件中有未建立索引的列才失效
            select * from big where id = 123 or password="xx"; -- 未命中
select * from big where name = "wupeiqi" or password="xx"; -- 未命中
特别的：
select * from big where id = 10 or password="xx" and name="xx"; -- 命中
          排序，当根据索引排序时候，选择的映射如果不是索引，则不走索引
            select * from big order by name asc; -- 未命中
select * from big order by name desc; -- 未命中
特别的主键：
select * from big order by id desc; -- 命中
          like，模糊匹配时
            select * from big where name like "%u-12-19999"; -- 未命中
select * from big where name like "_u-12-19999"; -- 未命中
select * from big where name like "wu-%-10"; -- 未命中
特别的：
select * from big where name like "wu-1111-%"; -- 命中
select * from big where name like "wuw-%"; -- 命中
          使用函数
            select * from big where reverse(name) = "wupeiqi"; -- 未命中
特别的：
select * from big where name = reverse("wupeiqi"); -- 命中
          最左前缀，如果是联合索引，要遵循最左前缀原则
            如果联合索引为：(name,password)
name and password -- 命中
name -- 命中
password -- 未命中
name or password -- 未命中
      执行计划
        标准代码
          explain + SQL语句;
        图片演示
        type类型细讲
          其中比较重要的是 type，他他SQL性能比较重要的标志，性能从低到高依次：`all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const`
          ALL，全表扫描，数据表从头到尾找一遍。(一般未命中索引，都是会执行权标扫描)
            select * from big;
特别的：如果有limit，则找到之后就不在继续向下扫描.
select * from big limit 1;
          INDEX，全索引扫描，对索引从头到尾找一遍
            explain select id from big;
explain select name from big;
          RANGE，对索引列进行范围查找
            explain select * from big where id > 10;
explain select * from big where id in (11,22,33);
explain select * from big where id between 10 and 20;
explain select * from big where name > "wupeiqi" ;
          INDEX_MERGE，合并索引，使用多个单列索引搜索
            explain select * from big where id = 10 or name="武沛齐";
          REF，根据 索引 直接去查找（非键）
            select * from big where name = '武沛齐';
          EQ_REF，连表操作时常见
            explain select big.name,users.id from big left join users on big.age = users.id;
          CONST，常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快
            explain select * from big where id=11; -- 主键
explain select * from big where email="w-11-0@qq.com"; -- 唯一索引
          SYSTEM，系统，表仅有一行(=系统表)。这是const联接类型的一个特例
            explain select * from (select * from big where id=1 limit 1) as A;
        其他列
          代码/讲解
            id，查询顺序标识
z，查询类型
SIMPLE 简单查询
PRIMARY 最外层查询
SUBQUERY 映射为子查询
DERIVED 子查询
UNION 联合
UNION RESULT 使用联合的结果
...
table，正在访问的表名
partitions，涉及的分区（MySQL支持将数据划分到不同的idb文件中，详单与数据的拆分）。 一个特别大的文件拆分成多个小文件（分区）。
possible_keys，查询涉及到的字段上若存在索引，则该索引将被列出，即：可能使用的索引。
key，显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。例如：有索引但未命中，则possible_keys显示、key则显示NULL。
key_len，表示索引字段的最大可能长度。(类型字节长度 + 变长2 + 可空1)，例如：key_len=195，类型varchar(64)，195=64*3+2+1
ref，连表时显示的关联信息。例如：A和B连表，显示连表的字段信息。
rows，估计读取的数据行数（只是预估值）
explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c";
explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c" limit 1;
filtered，返回结果的行占需要读到的行的百分比。
explain select * from big where id=1; -- 100，只读了一个1行，返回结果也是1行。
explain select * from big where password="27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3"; -- 10，读取了10行，返回了1行。
注意：密码27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3在第10行
extra，该列包含MySQL解决查询的详细信息。
“Using index”
此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
“Using where”
这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
“Using temporary”
这意味着mysql在对查询结果排序时会使用一个临时表。
“Using filesort”
这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
“Range checked for each record(index map: N)”
这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
    函数
      部分函数列表：
        CHAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。
CONCAT(str1,str2,...)
字符串拼接
如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
字符串拼接（自定义连接符）
CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
CONV(N,from_base,to_base)
进制转换
例如：
SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示
FORMAT(X,D)
将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
例如：
SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSERT(str,pos,len,newstr)
在str的指定位置插入字符串
pos：要替换位置其实位置
len：替换的长度
newstr：新字符串
特别的：
如果pos超过原字符串长度，则返回原字符串
如果len超过原字符串长度，则由新字符串完全替换
INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。
LEFT(str,len)
返回字符串str 从开始的len位置的子序列字符。
LOWER(str)
变小写
UPPER(str)
变大写
LTRIM(str)
返回字符串 str ，其引导空格字符被删除。
RTRIM(str)
返回字符串 str ，结尾空格字符被删去。
SUBSTRING(str,pos,len)
获取字符串子序列
LOCATE(substr,str,pos)
获取子序列索引位置
REPEAT(str,count)
返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
若 count <= 0,则返回一个空字符串。
若str 或 count 为 NULL，则返回 NULL 。
REPLACE(str,from_str,to_str)
返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
返回字符串 str ，顺序和字符顺序相反。
RIGHT(str,len)
从字符串str 开始，返回从后边开始len个字符组成的子序列
SPACE(N)
返回一个由N空格组成的字符串。
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
      创建函数
        delimiter $$
create function f1(
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 ;
      执行函数
        select f1(11,22);
select f1(11,id),name from d1;
      删除函数
        drop function f1;
    存储过程
      定义
        存储过程，是一个存储在MySQL中的SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。
        图片
      创建存储过程
        delimiter $$
create procedure p1()
BEGIN
select * from d1;
END $$
delimiter ;
      执行存储过程
        call p1();
      删除存储过程
        drop procedure proc_name;
      python代码
        #!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1')
result = cursor.fetchall()
cursor.close()
conn.close()
print(result)
      存储过程的参数（三种）
        - 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;
set @t2 = 0;
CALL p2 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
          python代码
            #!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='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()
# {"@_p2_0":11 }
cursor.close()
conn.close()
print(result)
      返回值 & 结果集
        代码
          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;
          python代码
            #!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='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)
      事务 & 异常
        定义
          事务，成功就都成功，失败就都失败。
        代码
          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;
          python代码
            #!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p4',args=(100))
# 获取执行完存储的参数
cursor.execute("select @_p4_0")
rets = cursor.fetchall()
cursor.close()
conn.close()
print(table)
print(rets)
      游标
        代码
          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;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open my_cursor;
xxoo: LOOP
fetch my_cursor into sid,sname;
IF done then
leave xxoo;
END IF;
insert into t1(name) values(sname);
end loop xxoo;
close my_cursor;
end $$
delimiter ;
          call p5();
    视图
      定义
        视图其实是一个虚拟表（非真实存在），其本质是【根据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;
-- select * from (select id,name from d1 where id > 1) as v1;
      删除视图
        drop view v1;
      修改视图
        alter view v1 as SQL语句
      注意：
        基于视图只能查询，针对视图不能执行 增加、修改、删除。 如果源表发生变化，视图表也会发生变化。
    触发器
      图片
      定义
        对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器。
      代码
        # 插入前
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 tri_after_insert_tb1;
      示例代码
        在 t1 表中插入数据之前，先在 t2 表中插入一行数据。
          delimiter $$
CREATE TRIGGER tri_before_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
-- NEW.id NEW.name NEW.email
-- INSERT INTO t2 (name) VALUES();
IF NEW.name = 'alex' THEN
INSERT INTO t2 (name) VALUES(NEW.id);
END IF;
END $$
delimiter ;
          insert into t1(id,name,email)values(1,"alex","xxx@qq.com")
        在t1表中删除数据之后，再在t2表中插入一行数据。
          delimiter $$
CREATE TRIGGER tri_after_insert_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表示原来的数据。
  python操作mysql
    事务
      innodb引擎中支持事务，myisam不支持。
      四大特性
        原子性（Atomicity）
          原子性是指事务包含的所有操作不可分割，要么全部成功，要么全部失败回滚。
        一致性（Consistency）
          执行的前后数据的完整性保持一致。
        隔离性（Isolation）
          一个事务执行的过程中,不应该受到其他事务的干扰。
        持久性（Durability）
          事务一旦结束,数据就持久到数据库
      MySQL客户端代码
        mysql> select * from users;
+----+---------+---------+
| id | name | amount |
+----+---------+---------+
| 1 | wupeiqi | 5 |
| 2 | alex | 6 |
+----+---------+---------+
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 | wupeiqi | 3 |
| 2 | ale x | 8 |
+----+---------+---------+
3 rows in set (0.00 sec)
        mysql> select * from users;
+----+---------+---------+
| id | name | amount |
+----+---------+---------+
| 1 | wupeiqi | 3 |
| 2 | ale x | 8 |
+----+---------+---------+
3 rows in set (0.00 sec)
mysql> begin; -- 开启事务
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> rollback; -- 事务回滚（回到原来的状态）
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users;
+----+---------+---------+
| id | name | amount |
+----+---------+---------+
| 1 | wupeiqi | 3 |
| 2 | ale x | 8 |
+----+---------+---------+
3 rows in set (0.00 sec)
      python代码
        import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='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()
    锁
      概念介绍
        出现的原因
          MySQL中自带了锁的功能，可以帮助我们实现开发过程中遇到的同时处理数据的情况。
        - 表级锁，即A操作表时，其他人对整个表都不能操作，等待A操作完之后，才能继续。
        - 行级锁，即A操作表时，其他人对指定的行数据不能操作，其他行可以操作，等待A操作完之后，才能继续。
        使用范围
          MYISAM支持表锁，不支持行锁；
InnoDB引擎支持行锁和表锁。
即：在MYISAM下如果要加锁，无论怎么加都会是表锁。
在InnoDB引擎支持下如果是基于索引查询的数据则是行级锁，否则就是表锁。
      实操
        正常情况
          在innodb引擎中，update、insert、delete的行为内部都会先申请锁（排它锁），申请到之后才执行相关操作，最后再释放锁。
所以，当多个人同时像数据库执行：insert、update、delete等操作时，内部加锁后会排队逐一执行。
        select
          select默认不会申请锁
          让其可以申请锁的办法
            如果，你想要让select去申请锁，则需要配合 事务 + 特殊语法来实现。
            for update
              排它锁，加锁之后，其他不可以读写
              代码
                begin;
select * from L1 where name="武沛齐" for update; -- name列不是索引（表锁）
commit;
                begin; -- 或者 start transaction;
select * from L1 where id=1 for update; -- id列是索引（行锁）
commit;
            lock in share mode
              共享锁，加锁之后，其他可读但不可写（用的我较少）
              代码
                begin;
select * from L1 where name="武沛齐" lock in share mode; -- 假设name列不是索引（表锁）
commit;
                begin; -- 或者 start transaction;
select * from L1 where id=1 lock in share mode; -- id列是索引（行锁）
commit;
      排它锁
        排它锁（ `for update`），加锁之后，其他事务不可以读写。
        应用代码
          情景
            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; -- start transaction;
select count from goods where id=3 for update;
-- 获取个数进行判断
if 个数>0:
update goods set count=count-1 where id=3;
else:
-- 已售罄
commit;
          代码
            import pymysql
import threading
def task():
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor(pymysql.cursors.DictCursor)
# cursor = conn.cursor()
# 开启事务
conn.begin()
cursor.execute("select id,age from tran where id=2 for update")
# fetchall ( {"id":1,"age":10},{"id":2,"age":10}, ) ((1,10),(2,10))
# {"id":1,"age":10} (1,10)
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()
      共享锁（用的较少）
        共享锁（ `lock in share mode`），可以读，但不允许写。
        官方文档中的应用
          Suppose that you want to insert a new row into a table `child`, and make sure that the child row has a parent row in table `parent`. Your application code can ensure referential integrity throughout this sequence of operations.
First, use a consistent read to query the table `PARENT` and verify that the parent row exists. Can you safely insert the child row to table `CHILD`? No, because some other session could delete the parent row in the moment between your `SELECT` and your `INSERT`, without you being aware of it.
To avoid this potential issue, perform the [`SELECT`](https://dev.mysql.com/doc/refman/5.7/en/select.html) using `LOCK IN SHARE MODE`:
```
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
```
After the `LOCK IN SHARE MODE` query returns the parent `'Jones'`, you can safely add the child record to the `CHILD` table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the `PARENT` table waits until you are finished, that is, until the data in all tables is in a consistent state.
    数据库连接池
      图片
      代码
        import threading
import pymysql
from dbutils.pooled_db import PooledDB
MYSQL_DB_POOL = PooledDB(
creator=pymysql, # 使用链接数据库的模块
maxconnections=5, # 连接池允许的最大连接数，0和None表示不限制连接数
mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
maxcached=3, # 链接池中最多闲置的链接，0和None不限制
blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
ping=0,
# ping MySQL服务端，检查是否服务可用。
# 如：0 = None = never, 1 = default = whenever it is requested,
# 2 = when a cursor is created, 4 = when a query is executed, 7 = always
host='127.0.0.1',
port=3306,
user='root',
password='root123',
database='userdb',
charset='utf8'
)
def task():
# 去连接池获取一个连接
conn = MYSQL_DB_POOL.connection()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute('select sleep(2)')
result = cursor.fetchall()
print(result)
cursor.close()
# 将连接交换给连接池
conn.close()
def run():
for i in range(10):
t = threading.Thread(target=task)
t.start()
if __name__ == '__main__':
run()
    SQL工具类
      基于数据库连接池开发一个公共的SQL操作类，方便以后操作数据库。
      单例和方法
        代码
          创建的方法
            # db.py
import pymysql
from dbutils.pooled_db import PooledDB
class DBHelper(object):
def __init__(self):
# TODO 此处配置，可以去配置文件中读取。
self.pool = PooledDB(
creator=pymysql, # 使用链接数据库的模块
maxconnections=5, # 连接池允许的最大连接数，0和None表示不限制连接数
mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
maxcached=3, # 链接池中最多闲置的链接，0和None不限制
blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
ping=0,
# ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
host='127.0.0.1',
port=3306,
user='root',
password='root123',
database='userdb',
charset='utf8'
)
def get_conn_cursor(self):
conn = self.pool.connection()
cursor = conn.cursor(pymysql.cursors.DictCursor)
return conn, cursor
def close_conn_cursor(self, *args):
for item in args:
item.close()
def exec(self, sql, **kwargs):
conn, cursor = self.get_conn_cursor()
cursor.execute(sql, kwargs)
conn.commit()
self.close_conn_cursor(conn, cursor)
def fetch_one(self, sql, **kwargs):
conn, cursor = self.get_conn_cursor()
cursor.execute(sql, kwargs)
result = cursor.fetchone()
self.close_conn_cursor(conn, cursor)
return result
def fetch_all(self, sql, **kwargs):
conn, cursor = self.get_conn_cursor()
cursor.execute(sql, kwargs)
result = cursor.fetchall()
self.close_conn_cursor(conn, cursor)
return result
db = DBHelper()
          导入上面的方法
            from db import db
db.exec("insert into d1(name) values(%(name)s)", name="武沛齐666")
ret = db.fetch_one("select * from d1")
print(ret)
ret = db.fetch_one("select * from d1 where id=%(nid)s", nid=3)
print(ret)
ret = db.fetch_all("select * from d1")
print(ret)
ret = db.fetch_all("select * from d1 where id>%(nid)s", nid=2)
print(ret)
      上下文管理
        代码
          创建的方法
            # db_context.py
import threading
import pymysql
from dbutils.pooled_db import PooledDB
POOL = PooledDB(
creator=pymysql, # 使用链接数据库的模块
maxconnections=5, # 连接池允许的最大连接数，0和None表示不限制连接数
mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
maxcached=3, # 链接池中最多闲置的链接，0和None不限制
blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
ping=0,
host='127.0.0.1',
port=3306,
user='root',
password='root123',
database='userdb',
charset='utf8'
)
class Connect(object):
def __init__(self):
self.conn = conn = POOL.connection()
self.cursor = conn.cursor(pymysql.cursors.DictCursor)
def __enter__(self):
return self
def __exit__(self, exc_type, exc_val, exc_tb):
self.cursor.close()
self.conn.close()
def exec(self, sql, **kwargs):
self.cursor.execute(sql, kwargs)
self.conn.commit()
def fetch_one(self, sql, **kwargs):
self.cursor.execute(sql, kwargs)
result = self.cursor.fetchone()
return result
def fetch_all(self, sql, **kwargs):
self.cursor.execute(sql, kwargs)
result = self.cursor.fetchall()
return result
          导入上面的方法
            from db_context import Connect
with Connect() as obj:
# print(obj.conn)
# print(obj.cursor)
ret = obj.fetch_one("select * from d1")
print(ret)
ret = obj.fetch_one("select * from d1 where id=%(id)s", id=3)
print(ret)