mysql操作

最新推荐文章于 2024-08-17 15:01:06 发布

disui7868

最新推荐文章于 2024-08-17 15:01:06 发布

阅读量483

点赞数

文章标签：数据库人工智能 python

原文链接：http://www.cnblogs.com/SkyOceanchen/p/11594599.html

版权

一、数据库
二、mysql支持的数据类型
三、多表关系
四、查询
五、pymysql：python操作mysql
触发器
存储过程
流程控制

一、数据库

1.什么是数据库**

1.什么是数据库：用来存储数据的仓库，管理数据文件的系统 - 安装某种管理数据的系统 - 管理的对象本质是文件
2.存储数据的位置：在硬盘及内存中存储数据
3.什么是系统：运行在硬件基础上，管理其他软件

4.数据库的组成
库：存放多张表 - 文件夹
表：包含多条相同结构的记录（表结构文件，表数据文件（索引文件）） - 文件
记录：（数据的本体）包含多个key-value键值对的一条数据 - 二进制数据
字段：（数据的描述）描述信息 - 信息本身 == key-value - 二进制数据
例如：
stu
id  name    age     gender
1   Bob     18      男
2   Tom     17      女
...

teacher
...

5. 数据库与文件存储数据区别
  数据库本质也是通过文件来存储数据, 数据库的概念就是系统的管理存储数据的文件

2.数据库介绍

数据库服务器端: 存放数据库的主机集群
数据库客户端: 可以连接数据库的任意客户端
数据库管理员: DBA

# socket套接字的C/S架构应用

3.数据库基本概念（组成）

# 重要性: ***
库: 多表构建一个数据库, 本质就是文件夹
表: 多条相同结构的记录（数据）, 本质就是文件
记录: 包含多个key-value键值对的一条数据， 本质就是文件中一条条数据记录，并且为二进制数据
字段：描述信息 - 信息本身 == key-value - 二进制数据
    (字段)id, name, age, gender...一个老师的数据 => 一条数据记录
例如：
stu
id  name    age     gender
1   Bob     18      男
2   Tom     17      女
...

teacher
...

4.数据库分类**

分类：效率问题（内存>硬盘，非关系>关系，key-value>sql）




# 重要性: ***
 1）关系型数据库：数据库中表与表之间又联系 - mysql
1.有表的概念
2.以表中一条条记录存储数据
 mysql oracle sqlServer access db2


2）非关系型数据库:没有表的概念-redis，mongodb（介于关系与非关系之间）
1.没有表的概念
2.通过key-value键值对方式存储数据
mongodb redis memcache


3）内存与硬盘（内存>硬盘）
    硬盘：数据可以永久存储 - mysql、mongodb
    内存：数据的存取效率极高 - redis、memcache
 
4）sql与nosql
    sql：数据库操作通过sql语句
    nosql：数据库操作就是key-value形式（value就是一条记录）
            stu - {'name': 'Bob', 'age': 18}
            stus - [{'name': 'Bob', 'age': 18}, {'name': 'Tom', 'age': 18}]
            name - 'Jerry'

5.数据库安装

1、解压免安装版本（5.6 - 没有初始密码）
2、配置环境变量：数据库绝对路径下的bin
3、配置服务：
    启动管理员终端：mysqld --install [nmysql]

# 安装server端与client端


# 如何启动server? 如果通过client连接server
管理员的身份运行cmd或者powershell

6.数据库卸载

前提）启动终端，输入 mysql，如果不是提示 不是内部或外部命令，代表已安装数据库

1）直接连入
    i 搜索：服务，检索mysql服务，有
        停止服务，并移除服务 - 启动管理员终端：mysqld --remove
            
    ii 搜索：服务，检索mysql服务，无 （mysql使用命令启动）
        停止服务，启动管理员终端
            tasklist | findstr mysql
            taskkill /pid 进程pid /f
    
2）连接超时
    搜索：服务，检索mysql服务，移除服务即可
    
3）移除环境变量

总结：如果能连入，先停止服务 | 如果添加了服务，就移除服务 | 取消环境变量配置

7.数据库启动与连接**

数据库的连接：主机、端口、账号、密码、数据库

# 前提:配置环境变量

# 了解
1.前往数据库安装路径,bin文件夹下,cmd执行 mysqld 启动mysql服务器端

2.前往数据库安装路径,bin文件夹下,cmd执行 mysql 启动mysql客户端连接服务器端

# 掌握***
# 将mysql服务器添加到系统服务,在系统服务中启动mysql, 命令:  mysqld --install
# 进入系统服务: win+r => services.msc => 找到mysql服务手动启动或关闭

# 连接数据库:mysql -hlocalhost -P3306 -uroot -p
1）游客登陆（不一定能登入，登入了也啥都不能干）
>: mysql

2）账号密码登录
>: mysql -u root -p
再输入密码，没有任何提示，没有密码直接回车

3）连接指定服务器的mysql
>: mysql -h ip地址 -P 端口号 -u 账号 -p
回车后敲入密码
eg：
>: mysql -hlocalhost -P3306 -uroot -p

4）退出数据库
>: quit
>: exit
# 通过最高权限进入数据库, 要采用root用户进入, 连入本地数据库: mysql -uroot -p

8.用户信息查看

1）查看当前登录的用户
mysql>: select user();

2）root权限下可以查看所有用户信息
mysql>: select * from mysql.user;
mysql>: select * from mysql.user \G
mysql>: select user,password,host from mysql.user;

3）root登录下，删除游客（操作后要重启mysql服务）
mysql>: delete from mysql.user where user='';

4）root登录下，修改密码（操作后要重启mysql服务）
mysql>: update mysql.user set password=password('12345678') where host='localhost';

5）没有登录
>: mysqladmin -u用户名 -p旧密码 -h域名 password "新密码"
eg>: mysqladmin -uroot -p12345678 -hlocalhost password "root"

6）root登录下，创建用户
mysql>：grant 权限们 on 数据库名.表名 to 用户名@主机名 identified by '密码';

9.用户操作与管理（重点）（新创建用户）***

创建对指定数据库权限的用户：grant 权限们 on 数据库.表 to 用户名@'主机名' identified by '密码';

# 操作前提：登录root用户

1.重要字段
Host | User | Password

2.新建用户
create user 用户名@主机名 identified by '密码'; # 正确
create user zero@localhost identified by 'zero';


3. 为特定的数据库分配有该数据库 操作权限 的用户
mysql>: grant 权限们 on 数据库.表 to 用户名@'主机名' identified by '密码';
  方法1
# 1）all：所有权限
# 2）oldboy.*：oldboy数据库下所有表
# 3）oldboy@'localhost'：本机可以通过oldboy用户登入
# 4）identified by 'Oldboy123'：密码为Oldboy123
eg>: grant all on oldboy.* to oldboy@'localhost' identified by 'Oldboy123';
    方法2
# 1）select,delete,update,insert,drop,alter：指定的权限
# 2）oldboy.*：oldboy数据库下所有表
# 3）oldboy@'%'：任何机器可以通过oldboy用户登入
# 4）identified by 'Oldboy123'：密码为Oldboy123
eg>: grant select,delete,update,insert,drop,alter on oldboy.* to oldboy@'%' identified by 'Oldboy123';
    
    注：数据库名，表名可以用*替换，代表所有
    注：设置权限时如果没有当前用户，会自动创建用户，提倡使用

    
4. 撤销权限
mysql>: revoke 权限1,权限2,... on 数据库名.表名 from 用户名@'主机名';
# 禁掉本地oldboy用户对oldboy数据库的所有表的drop权限
eg:> revoke drop on oldboy.* from oldboy@'localhost';
    
5.修改密码
set password for 用户名@主机名 = password('新密码');
set password for owen@localhost = password('123');

6. 删除用户
drop user 用户名@'主机名';

10.数据库的密码操作

修改密码: mysqladmin -uroot -p旧密码 password "新密码"

11.数据库的配置（统一字符编码）

# 查看数据库配置信息: \s => 统一编码 => 防止乱码(读取不方便,数据丢失)

# 在mysql安装根目录下:创建my.ini (my.cnf) (命令:type nul>文件名.文件后缀)
# 通过配置文件统一配置的目的：统一管理 服务端(mysqld) 、客户端(client)
#       配置了 mysqld(服务端) 的编码为utf8，那么再创建的数据库，默认编码都采用utf8

# 配置流程
# 1）在mysql安装根目录下，创建配置文件：my.ini
#       mac下配置文件名叫 my.cnf

# 2）设置配置文件内容并保存
[mysqld]  # 服务器配置
port=3306  # 可以修改数据库默认端口(如果数据库端口被其他软件占用)
character-set-server=utf8  # 编码格式
collation-server=utf8_general_ci  # 排序方式（默认跟编码格式走）

[client]  # mysql自己的客户端叫[mysql]，配置[client]即配置了[mysql]，也配置了其他存在方式的客户端，比如Navicat可视化客户端
default-character-set=utf8  # 编码格式

# 3）重启数据库服务

###直接复制粘贴
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci




[mysql]
default-character-set=utf8



[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci




[mysql]
default-character-set=utf8
##########加入免登录
user=root
password=root



[client]
default-character-set=utf8

11.数据库修改(字符编码)信息

#修改字符编码
mysql>:alter database 数据库名 charset=编码格式;

12.库的基本操作

# 前提: 连接上数据库
1.查看mysql版本: 
mysql>select version();
2.查看当前用户: 
mysql>select user();
3.查看mysqld下的(当前用户可以操作的)所有数据库: 
mysql>show databases; 
4.选择数据库
mysql>:use 数据库名；
mysql>use db1;
5.查看当前所在数据库：
mysql>:select database();
6.增,创建数据库
mysql>:create database 数据库名 [charset="编码格式"]；
# 采用默认编码集: 
    mysql>create database db1;  # db1为数据库名
# 自定义编码集: 
    mysql>create database db1 charset="gbk";

7.查,查看数据库全部信息
mysql>:show create database 数据库名；
    # 详细信息: 
mysql>show create database db1;


8.改,修改数据库编码集
mysql>alter database db1 charset="utf8";

9.删, 移除数据库
mysql>:drop database 数据库名；
mysql>drop database db1;
10. 退出
mysql>exit
mysql>quit

13.数据库模式(安全模式和非安全模式)

# sql_mode: 反映数据库的全局变量
# 数据库模式限制的是客户端对服务器操作数据的方式(是否严格)


# mysql 5.7 以后默认都是安全模式

# mysql 5.6 版本

sql_model=no_engine_substitution  # 非安全性，默认
sql_model=strict_trans_tables  # 安全性

# 查看当前数据库模式:
show variables like "%sql_mode%"; # %匹配0~n个任意字符 => 模糊查询

# 临时设置为安全模式，服务重启后会被重置
mysql>: set global sql_mode="strict_trans_tables";  # 在root用户登录状态下
# 在设置后，quit断开数据库连接后(服务器不重启)就会进入安全模式

# 安全模式下，非安全模式下sql执行的警告语句，都会抛异常
eg>: create table t1(name char(2));
eg>: insert into t1 values ("ab") # 正常
eg>: insert into t1 values ("owen") # 错误 Data too long for column 'name' at row 1

14.库的远程连接

15.查看库的文件以及结束进程

16.表的基本操作

前提：先选取要操作的数据库
1）进入指定库
mysql>:use db1;
2）确定当前使用的数据库
mysql>:select database();
3）查看当前数据库已有表
mysql>：show tables;

4）增加，创建表(字段1 类型, ..., 字段n 类型)
mysql>：create table 表名(字段们);
eg>: create table student(name char(16), age int);
eg>: create table t1(name varchar(16), age int);

5）查看创建表的详细信息
mysql>：show create table 表名;
eg>: show create table t1;


4）查看创建表的结构（字段结构信息）
mysql>：desc 表名;
eg>:desc t1;




5）删除表
mysql>: drop table 表名;
eg>: drop table teacher;

17.创建表的完整语法

# 长度和约束在某些情况下是可以省略的
mysql>: create table 表名 (
    属性名1 类型(长度) 约束,
    ...
    属性名n 类型(长度) 约束
) engine=引擎 default charset=utf8;
    
'''
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)engine=innodb charset=utf8;
'''
# []可选参数

举例：

mysql> create database db1;
mysql> use db1;
mysql> create table student(id int primary key auto_increment,name varchar(16) unique,age tinyint unsigned default 0,height float(255,30) unsigned not null,mobile int,unique(name,mobile))engine=myisam charset=utf8;

mysql> desc student;
+--------+------------------------+------+-----+---------+----------------+
| Field  | Type                   | Null | Key | Default | Extra          |
+--------+------------------------+------+-----+---------+----------------+
| id     | int(11)                | NO   | PRI | NULL    | auto_increment |
| name   | varchar(16)            | YES  | UNI | NULL    |                |
| age    | tinyint(3) unsigned    | YES  |     | 0       |                |
| height | float(255,30) unsigned | NO   |     | NULL    |                |
| mobile | int(11)                | YES  |     | NULL    |                |
+--------+------------------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

mysql> show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `age` tinyint(3) unsigned DEFAULT '0',
  `height` float(255,30) unsigned NOT NULL,
  `mobile` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `name_2` (`name`,`mobile`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

18.表的详细操作

1.修改表名
alter table 旧表名 rename 新表名;

2.修改表的引擎与字符编码
alter table 表名 engine="引擎名" charset="编码名";

3.复制表 *

# 结构
create table 新表名 like 旧表名;
eg:1
create table nt like tt; # 将tt的表结构复制到新表nt中, 约束条件一并复制
eg:2
create table nt1 select * from tt where 1=2; # 将tt的表结构复制到新表nt1中, 约束条件不会复制

# 结构+数据
create table 新表名 select * from 旧表名;
注: 会复制表结构+数据, 但不会复制约束条件


4.清空表
truncate 表名;
注：表被重置，自增字段重置


        1 .*#使用truncate清空全部数据主键自增长是从1开始(效率更高)*

        truncate table 表名;
        2 . #清空表数据并且将主键自增长从1开始(1.先清空表数据2.在把表的自增长设置为1)

        delete from 表名;

        alter table 表名 auto_increment=1;

19.表的修改(表名，字段名，字段属性)

1）修改字段属性
mysql>:alter table 表名 modify name char(20);
eg>:alter table t1 modify name char(20);
2）修改字段名(将表t1的属性name修改为usr，并且字段属性改为char(16))
mysql>:alter table t1 change name usr char(16);
3） 修改表名
mysql>:alter table 表名 rename 修改后的表名;
eg>:alter table t1 rename t2;

20.记录（字段）的基本操作

前提：知道具体操作的是那张表
1）查看某个数据库中的某个表的所有记录，如果在对应数据库中，可以直接查找表
mysql>: select * from [数据库名.]表名;
注：*代表查询所有字段

2）给表的所有字段插入数据
mysql>: insert [into] [数据库名.]表名 values (值1,...,值n);
eg：如果给有name和age两个字段的student表插入数据
1条>：insert into student values ('Bob', 18);
多条>：insert into student values ('张三', 18), ('李四', 20);
指定库>：insert owen.student values ('张三', 18), ('李四', 20);

3）根据条件修改指定内容
mysql>: update [数据库名.]表名 set 字段1=新值1, 字段n=新值n where 字段=旧值;
eg:> update student set name='王五', age='100' where name='张三';
注：i) 可以只修改部分字段 ii) 没有条件下，所有记录都会被更新
eg:> update student set name='呵呵';

4）根据条件删除记录
mysql>: delete from [数据库名.]表名 where 条件;
eg:> delete from student where age<30;
    删除字段为空的内容
    mysql>:delete from [数据库名.]表名 where age is NULL;

21.记录(字段)的详细操作

create table tf1(
    id int primary key auto_increment,
    x int,
    y int
);

# 修改字段属性
alter table tf1 modify x char(4) default '';
#修改字段名(将表tf1的属性y修改为m，并且字段属性改为char(4)),加上约束default ''
alter table tf1 change y m char(4) default '';
#移动字段
alter table 表名 modify 字段 类型(长度) 约束 first;
alter table 表名 modify 字段 类型(长度) 约束 after 字段名;

# 增加字段
1）在末尾增加
mysql>: alter table 表名 add 字段名 类型[(长度) 约束];  # 末尾
eg>: alter table tf1 add z int unsigned;

2）在首位增加
mysql>: alter table 表名 add 字段名 类型[(宽度) 约束] first;  # 首位
eg>: alter table tf1 add a int unsigned first;

3）在指定字段后面添加字段
mysql>: alter table 表名 add 字段名 类型[(宽度) 约束] after 旧字段名;  # 某字段后
eg>: alter table tf1 add xx int unsigned after x;
# 删除字段
mysql>: alter table 表名 drop 字段名;  
eg>: alter table tf1 drop a;

#例子：
#创建表
mysql> create table t2(id int primary key auto_increment,x int,y int);
Query OK, 0 rows affected (0.32 sec)
#插入值
mysql> insert into t2(x,y) values(10,20),(100,200),(1000,2000);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0
#查询表结构
mysql> desc t2;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| x     | int(11) | YES  |     | NULL    |                |
| y     | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

#修改字段属性
mysql> alter table t2 modify x bigint default 0;
Query OK, 3 rows affected (0.99 sec)
Records: 3  Duplicates: 0  Warnings: 0

#修改字段名和属性
mysql> alter table t2 change y m char(10) not null;
Query OK, 3 rows affected (0.79 sec)
Records: 3  Duplicates: 0  Warnings: 0
#在末尾添加字段
mysql> alter table t2 add age int,add gender enum("male","female","wasai") default "wasai";
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0
#在开头添加字段
mysql> alter table t2 add name char(16) first;
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0
#在name字段后面添加字段
mysql> alter table t2 add price int after name;
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0
#查询创建表的详细信息
mysql> show create table t2 \G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `name` char(16) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x` bigint(20) DEFAULT '0',
  `m` char(10) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` enum('male','female','wasai') DEFAULT 'wasai',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
#查询操作后的表的结构
mysql> desc t2;
+--------+-------------------------------+------+-----+---------+----------------+
| Field  | Type                          | Null | Key | Default | Extra          |
+--------+-------------------------------+------+-----+---------+----------------+
| name   | char(16)                      | YES  |     | NULL    |                |
| price  | int(11)                       | YES  |     | NULL    |                |
| id     | int(11)                       | NO   | PRI | NULL    | auto_increment |
| x      | bigint(20)                    | YES  |     | 0       |                |
| m      | char(10)                      | NO   |     | NULL    |                |
| age    | int(11)                       | YES  |     | NULL    |                |
| gender | enum('male','female','wasai') | YES  |     | wasai   |                |
+--------+-------------------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
#查询表的内容
mysql> select * from t2;
+------+-------+----+------+------+------+--------+
| name | price | id | x    | m    | age  | gender |
+------+-------+----+------+------+------+--------+
| NULL |  NULL |  1 |   10 | 20   | NULL | wasai  |
| NULL |  NULL |  2 |  100 | 200  | NULL | wasai  |
| NULL |  NULL |  3 | 1000 | 2000 | NULL | wasai  |
+------+-------+----+------+------+------+--------+
3 rows in set (0.00 sec)
#删除name字段
mysql> alter table t2 drop name;
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from t2;
+-------+----+------+------+------+--------+
| price | id | x    | m    | age  | gender |
+-------+----+------+------+------+--------+
|  NULL |  1 |   10 | 20   | NULL | wasai  |
|  NULL |  2 |  100 | 200  | NULL | wasai  |
|  NULL |  3 | 1000 | 2000 | NULL | wasai  |
+-------+----+------+------+------+--------+
3 rows in set (0.00 sec)

22.约束条件

约束条件	解释
primary key	主键，指定该列的值可以唯一地标识该列记录,唯一标识，表都会拥有，不设置为默认找第一个不空，唯一字段，未标识则创建隐藏字段
unique+auto_increment	相当于primary key
foreign key	外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
unique key	唯一约束，唯一数据，该条字段的值需要保证唯一,不能重复
auto_increment	自增，只能加给key的int类型字段，作为辅助修饰，一个表中只能设置一个自增字段，清除自增（先清空表，truncate table 表名;）
not null	非空约束，指定某列不能为空；针对一些字段，如注册时的用户名，出生人的性别等，这些需求下的字段，只不能设置为Null，必须要对其赋值
default	默认值 - 对有默认值意外的字段进行赋值时，有默认值的字段会被赋默认值，没默认值的系统给一个默认值null
unsigned	无符号 - 存储的数字从0开始(主要用在整型上)。
zerofill	0填充 - 存整数时数据长度小于取值范围长度，会在数字左方用0填充，主要用在整型以及浮点型)。

# not null 与 default 限制
# 不能为空，没有默认值的x，必须赋值
# y、z在没有赋值情况下，才有默认值，设置值后，采用默认值
mysql>: create table td1 (x int not null, y int default 0, z int default 100);

# 报错，auto_increment必须设置给 键字段
mysql>: create table td2 (x int auto_increment);
# 报错，auto_increment必须设置给 int字段
mysql>: create table td2 (x char(4) auto_increment);
# 报错，auto_increment字段最多出现 1次
mysql>: create table td2 (x int unique auto_increment, y int unique auto_increment);

# 正确，主键和唯一键分析
# x为主键：没有设置primary key时，第一个 唯一自增键，会自动提升为主键
mysql>: create table td21 (x int unique auto_increment, y int unique);
# y为主键：没有设置primary key时，第一个 唯一自增键，会自动提升为主键
mysql>: create table td22 (x int unique, y int unique auto_increment);
# x为主键：设置了主键就是设置的，主键没设置自增，那自增是可以设置在唯一键上的
mysql>: create table td23 (x int primary key, y int unique auto_increment);
# x为主键：设置了主键就是设置的，主键设置了自增，自增字段只能有一个，所以唯一键不能再设置自增了
mysql>: create table td24 (x int primary key auto_increment, y int unique);
# 默认主键：没有设置主键，也没有 唯一自增键，那系统会默认添加一个 隐式主键（不可见）
mysql>: create table td25 (x int unique, y int unique);

# 唯一键：确保一个字段，数据不能重复
# 主键：是一条记录的唯一标识（可以理解为数据的编号）

# 联合唯一
# ip在port不同时，可以相同，ip不同时port也可以相同，均合法
# ip和port都相同时，就是重复数据，不合法
mysql>: create table tu1 (ip char(16), port int, unique(ip, port));

# 也可以设置成 联合主键，道理同 联合唯一
mysql>: create table tu2 (ip char(16), port int, primary key(ip, port));
# sql可以多行书写
mysql>: 
create table t22(
    ip char(16),
    port int,
    primary key(ip,port)
);



清除自增

1 .*#使用truncate清空全部数据主键自增长是从1开始(效率更高)*

truncate table 表名;
2 . #清空表数据并且将主键自增长从1开始(1.先清空表数据2.在把表的自增长设置为1)

delete from 表名;

alter table 表名 auto_increment=1;

22.1约束例题

例题：创建一个学生student表，有主键id字段，name唯一字段、age字段、height字段、mobile字段

主键为自增字段

name和mobile联合唯一

age和height不可以出现负数

增加记录时，name和height字段必须赋值

增加记录时，age未赋值，用0填充，mobile未赋值，用Null填充

create table student(id int primary key auto_increment,name varchar(16) unique,age tinyint unsigned default 0,height float(255,30) unsigned not null,mobile int,unique(name,mobile));

一次性插入一条数据

insert into student(name,age,height,mobile) values('owen',18,180.0,123456789);

再一次性插入五条数据

insert into student(name,age,height,mobile) values('on',18,180.0,123456789),('chen',18,180.0,123456789),('owe',18,180.0,152456752365),('rock',18,180.0,123456789),('nick',18,180.0,123456789);

清空表，并清空主键自增记录

23.数据库表的引擎：驱动数据的方式-数据库优化

前提: 引擎是建表是规定, 提供给表使用的, 不是数据库
1.展示所有引擎
mysql> show engines; 

# 重点: 
mysql> use db1;选择数据库
2. innodb(默认): 支持事务, 行级锁, 外键（安全性比较高，加锁）
mysql>: create table t1(id int)engine=innodb;
3. myisam: 查询效率要优于innodb, 当不需要支持事务, 行级锁, 外键, 可以通过设置myisam来优化数据库（速度比较快，无锁）
mysql>: create table t2(id int)engine=myisam;
4. blackhole：黑洞，存进去的数据都会消失（可以理解不存数据）
mysql>: create table t3(id int)engine=blackhole;

5. memory：表结构是存储在硬盘上的，但是表数据全部存储在内存中，重启清空
mysql>: create table t4(id int)engine=memory;

insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

select * from t1; ...

二、mysql支持的数据类型

#mysql数据库支持存在那些数据
整型、浮点型、字符型、时间类型、枚举类型、集合类型

24.整形

类型	大小	范围（有符号）	范围（无符号）unsigned约束	用途
tinyint	1 字节	(-128，127)	(0，255)	小整数值
smallint	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
mediumint	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
int或integer	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
bigint	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值

'''约束 *
unsigned：无符号
zerofill：0填充
'''
# 不同类型所占字节数不一样, 决定所占空间及存放数据的大小限制
# eg:
create table t8(x tinyint);
insert into t8 values(200);  # 非安全模式存入,值只能到最大值127
select (x) from t8;


'''宽度
1.不能决定整型存放数据的宽度, 超过宽度可以存放, 最终由数据类型所占字节决定
2.如果没有超过宽度,且有zerofill限制, 会用0填充前置位的不足位
3.没有必要规定整型的宽度, 默认设置的宽度就为该整型能存放数据的最大宽度 *
'''
# eg:1
create table t9(x int(5));
insert into t9 values(123456); 
select (x) from t9; # 结果: 123456
insert into t9 values(2147483648); 
select (x) from t9; # 结果: 2147483647
insert into t9 values(10); 
select (x) from t9; # 结果: 10
# eg:2
create table t10(x int(5) unsigned zerofill); # 区域0~4294967295
insert into t10 values(10); 
select x from t10; # 结果: 00010
insert into t10 values(12345678900); 
select x from t10; # 结果: 4294967295

25.浮点型

类型	大小	范围（有符号）	范围（无符号）unsigned约束	用途
float	4 字节 float(255,30)	(-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 字节 double(255,30)	(-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 double(65,30)	依赖于M和D的值	依赖于M和D的值	小数值

# 在安全模式下测试浮点型类型

'''类型
float(M, D)：4字节，3.4E–38～3.4E+38
double(M, D)：8字节，1.7E–308～1.7E+308
decimal(M, D)：所在字节M，D大值基础上+2，其实就是M值+2就是decimal字段所占字节数
'''
'''宽度：
限制存储宽度
(M, D) => M为位数，D为小数位，M要大于等于D（一共M位，小数位是D位）。
float(255, 30)：精度最低，最常用
double(255, 30)：精度高，占位多
decimal(65, 30)：字符串存，全精度
'''

# 建表：
mysql>: create table tb4 (age float(256, 30)); # Display width out of range for column 'age' (max = 255)
mysql>: create table tb5 (age float(255, 31)); # 超出范围，Too big scale 31 specified for column 'age'. Maximum is 30.
mysql>: create table tb5 (age float(65, 30)); # 在合理取值范围
    
mysql>: create table t12 (x float(255, 30));
mysql>: create table t13 (x double(255, 30));
mysql>: create table t14 (x decimal(65, 30));
# 1.111111164093017600000000000000 
mysql>: insert into t12 values(1.11111111111111111119);  
# 1.111111111111111200000000000000
mysql>: insert into t13 values(1.11111111111111111119);
# 1.111111111111111111190000000000
mysql>: insert into t14 values(1.11111111111111111119);

# 重点：长度与小数位分析
# 报错，总长度M必须大于等于小数位D
mysql>: create table t14 (x decimal(2, 3));

# 能存储 -0.999 ~ 0.999，超长度的小数位会才有四舍五入，0.9994可以存，就是0.999,0.9995不可以存
mysql>: create table t14 (x decimal(3, 3));  # 整数位 3 - 3，所以最大为0

# 能存储 -9.999 ~ 9.999，超长度的小数位会才有四舍五入，9.9994可以存，就是9.999,9.9995不可以存
mysql>: create table t14 (x decimal(4, 3));  # 整数位 4 - 3，所以最大为9

# 能存储 -99.999 ~ 99.999，超长度的小数位会才有四舍五入，99.9994可以存，就是99.999,99.9995不可以存
mysql>: create table t14 (x decimal(5, 3));  # 整数位 5 - 3，所以最大为99

举例验证：

26.字符串：数据库优化-char效率要高于varchar

类型	大小	用途
char(常用)	0-255字节	定长字符串
varchar（常用）	0-65535 字节	变长字符串
tinyblob	0-255字节	不超过 255 个字符的二进制字符串
tinytext	0-255字节	短文本字符串
blob	0-65 535字节	二进制形式的长文本数据
text	0-65 535字节	长文本数据
mediumblob	0-16 777 215字节	二进制形式的中等长度文本数据
mediumtext	0-16 777 215字节	中等长度文本数据
longblob	0-4 294 967 295字节	二进制形式的极大文本数据
longtext	0-4 294 967 295字节	极大文本数据

'''类型
char：定长，永远采用设置的长度存储数据
varchar：不定长，在设置的长度范围内，变长的存储数据
'''
'''宽度
限制存储宽度
char(4)：存 "a" "ab" "abc" "abcd"都采用4个长度，"abcde" 只能存储前4位(安全模式下报错)
varchar(4)：存 "a" "ab" "abc" "abcd"分别采用1,2,3,4个长度存储，"abcde" 只能存储前4位(安全模式下报错)

char就按定长存储，如果数据长度变化大，通常更占空间，但是存取数据按固定定长操作，效率高
varchar存储数据时，会先计算要存储数据的长度，动态变长存储数据，所以一般较省空间，但是计算是需要耗时的，所以效率低

varchar计算出的数据长度信息也是需要开辟空间来存储，存储在数据头（数据开始前）中，也需要额外消耗1~2个字节
所以如果数据都是固定长度，或是小范围波动，char相比就不会更占空间，且效率高
'''

# 建表：
mysql>: create table ts1 (s1 char(4), s2 varchar(4));
mysql>: insert into ts1 values('adcde', 'xyzabc');  # 'adcd', 'xyza'

27.时间类型

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 (字节)	范围	格式	用途
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	年份值
datatime（年月日，小时分钟秒）	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	混合日期和时间值，时间戳

'''类型
year：yyyy(1901/2155)
date：yyyy-MM-dd(1000-01-01/9999-12-31)
time：HH:mm:ss
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/2038-01-19 ??)
'''

# 建表：
mysql>: create table td1 (my_year year, my_date date, my_time time);
mysql>: insert into td1 values(1666, '8888-8-8', '8:8:8');  # 时间需要在取值访问内

mysql>: create table td2 (my_datetime datetime, my_timestamp timestamp);   
mysql>: insert into td2 values('2040-1-1 1:1:1', '2040-1-1 1:1:1');  # 时间需要在取值访问内   
mysql>: insert into td2(my_datetime) values('2040-1-1 1:1:1');  # timestamp不复制会才有系统当前时间

# datetime：8字节，可以为null
# timestamp：4字节，有默认值CURRENT_TIMESTAMP

举例：year,date,time

举例:timestamp

mysql> insert into td4 values (19700101080001);
Query OK, 1 row affected (0.00 sec)

mysql> select * from td4;
+---------------------+
| t1                  |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
row in set (0.00 sec)
# timestamp时间的下限是19700101080001
mysql> insert into td4 values (19700101080000);
ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1

mysql> insert into td4 values ('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
# timestamp时间的上限是2038-01-19 11:14:07
mysql> insert into td4 values ('2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1
mysql> 

#添加一列 默认值是'0000-00-00 00:00:00'
mysql> alter table td4 add id2 timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table td4 \G;
*************************** 1. row ***************************
       Table: td4
Create Table: CREATE TABLE `td4` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.00 sec)

ERROR: 
No query specified

# 手动修改新的列默认值为当前时间
mysql> alter table td4 modify id2 timestamp default current_timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table td4 \G;
*************************** 1. row ***************************
       Table: td4
Create Table: CREATE TABLE `td4` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.00 sec)

ERROR: 
No query specified

mysql> insert into td4 values (null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from td4;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2018-09-21 14:56:50 | 0000-00-00 00:00:00 |
| 2018-09-21 14:59:31 | 2018-09-21 14:59:31 |
+---------------------+---------------------+
rows in set (0.00 sec)

举例：datetime

28.枚举与集合

enum中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。enum只允许从值集合中选取单个值，而不能一次取多个值。

set和enum非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。

类型	大小	用途
enum	对1-255个成员的枚举需要1个字节存储; 对于255-65535个成员，需要2个字节存储; 最多允许65535个成员。	单选：选择性别
set	1-8个成员的集合，占1个字节 9-16个成员的集合，占2个字节 17-24个成员的集合，占3个字节 25-32个成员的集合，占4个字节 33-64个成员的集合，占8个字节	多选：兴趣爱好

# 枚举与集合：为某一个字段提供选项的 - 枚举只能单选(1个)，集合可以多选(0-n个)

# 建表
# enum、set默认值为NULL
mysql>: create table tc1 (name varchar(20), sex enum('男', '女', '哇塞'), hobbies set('男', '女', '哇塞'));
mysql>: insert into tc1 values('ruakei', '哇塞哇塞', '未知');  
    
# enum、set手动设置默认值 '男' 与 '哇塞'
mysql>: create table tc2 (name varchar(20), sex enum('男', '女', '哇塞') default '男', hobbies set('男', '女', '哇塞') default '哇塞');
mysql>: insert into tc2 values('ruakei', '哇塞哇塞', '未知');  
mysql>: insert into tc2(name) values('ruakei');
    
# 对sex、hobbies两个字段赋值错误，系统默认用空字符串填充(非安全模式)，安全模式抛异常
# 如果对出sex、hobbies两个字段外的其他字段进行赋值，这两个字段会才有默认值

# 注：对set类型的字段进行赋值，用一个字符串，字符串内部用,将多个选项隔开，且不能添加空格等其他额外字符
mysql>: insert into tc2 values('ruakei_1', '女', '男,女,哇塞'); 
    
    
    
    
 结果：

mysql> select * from tc2;
+----------+------+----------------+
| name     | sex  | hobbies        |
+----------+------+----------------+
| ruakei   | 男   | 哇塞           |
| ruakei   | 男   | 哇塞           |
| ruakei_1 | 女   | 男,女,哇塞     |
+----------+------+----------------+
3 rows in set (0.03 sec)

三、多表关系

社会中存储需要可以构建成表的数据, 它们形成的表,往往之间存储某种或某些社会关系,

mysql数据库建立表结构就是社会中产生的各种数据, 分门别类管理

但mysql建立的(代码层次的)表之间, 同样需要处理表与表之间的关系

形成了多对一 | 多对多 | 一对一三种关系

"""
一对一：丈夫-妻子，用户-身份证，作者-作者详情
一对多：部门-员工，班级-学生，书-出版社
多对多：老师-班级，课程-学生，出版社-作者
"""

# 书 - 出版社 - 作者 - 作者详情 外键分布
# 外键是 建立表与表关联 的字段，通常 一个表的外键 是 另一个表的主键（唯一键也可以）

# 一对一：外键在任何一方都可以，此时外键要设置 唯一键
"""
作者(author)：id，name，sex，age，mobile
作者详情(author_detail)： id，info，address，author_id
----------------------------------------------------
作者(author)：id，name，sex，age，mobile, detail_id
1 Tom 1
2 Bom 2
3 Bob 3

作者详情(author_detail)： id，info，address
1 Tom_info
2 Bom_info
"""

# 一对多：外键必须放在多的一方，此时外键值不唯一
"""
书(book)：id，name，price，publish_id
1 西游记 1
2 东游记 2
3 西厢记 1
4 流浪记 1

出版社(publish)： id，name，address，phone
1 老奶奶出版社
2 小奶奶出版社
"""

# 多对多：一定要创建第三张表(关系表)，每一个外键值不唯一，看可以多个外键建立联合唯一
"""
作者(author)：id, name, age
出版社(publish)：id, name, address
作者与出版社关系表：
id      author_id       publish_id
1           1               1
2           1               2
3           2               1
4           2               2
"""

29.约束条件中的外键

重点：外键字段本身可以唯一或不唯一，但是外键关联的字段一定唯一
# 作者(author)：id，name，sex，age，mobile, detail_id
# 作者详情(author_detail)： id，info，address

# 1、外键的 字段名 可以自定义(名字随意)，通常命名规范(关联表_关联字段)

# 2、外键要通过 foreign key 语法建立表与表之间的关联

# 3、foreign key(所在表的外键字段) references 关联表(关联字段)
# eg>：foreign key(detail_id) references author_detail(id)

# 4、级联关系
#   级联更新 on update cascade
#   级联删除 on delete cascade

30.一对一：无级联关系

# 作者详情(author_detail)： id，info，address
create table author_detail(
    id int primary key auto_increment,
    info varchar(256),
    address varchar(256)
);


# 作者表id，name，sex，age，mobile, detail_id
create table author(
    id int primary key auto_increment,
    name varchar(64) not null,
    mobile char(11) unique not null,
    sex enum('男', '女') default '男',
    age int default 0,
    detail_id int unique not null,
    foreign key(detail_id) references author_detail(id)
);

# 必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据
mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address');
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
mysql>: insert into author_detail(info,address) values('Bob_info','Bob_address');
mysql>: insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);
###############################################################################
mysql> select * from author_detail;
+----+----------+-------------+
| id | info     | address     |
+----+----------+-------------+
|  1 | TOM_info | TOM_address |
|  2 | Bob_info | Bob_address |
+----+----------+-------------+
2 rows in set (0.00 sec)

mysql> select * from author;
+----+------+-------------+------+------+-----------+
| id | name | mobile      | sex  | age  | detail_id |
+----+------+-------------+------+------+-----------+
|  1 | Tom  | 13344556677 | 男   |    0 |         1 |
|  2 | Bob  | 15666882233 | 男   |    0 |         2 |
+----+------+-------------+------+------+-----------+
2 rows in set (0.00 sec)
###############################################################################
# 修改关联表 author
mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据，就可以修改
# 删除关联表 author
mysql>: delete from author where detail_id=3;  # 直接删除

# 修改被关联表 author_detail
mysql>: update author_detail set id=10 where id=1;  # 无法修改
# 删除被关联表 author_detail
mysql>: delete from author_detail where id=1;  # 无法删除

# 没有级联关系下：
# 增加：先增加被关联表记录，再增加关联表记录
# 删除：先删除关联表记录，再删除被关联表记录
# 更新：关联与被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定，可以修改)

31.一对一：有级联关系

建表规则：
未存放外键的表被依赖，称之为左表（被关联表）；存放外键的表示依赖表，称之为右表（关联表）；先操作被关联表再操作关联表
mysql>: drop table author;
mysql>: drop table author_detail;


# 作者详情(author_detail)： id，info，address
create table author_detail(
    id int primary key auto_increment,
    info varchar(256),
    address varchar(256)
);

# 作者表id，name，sex，age，mobile, detail_id
create table author(
    id int primary key auto_increment,
    name varchar(64) not null,
    mobile char(11) unique not null,
    sex enum('男', '女') default '男',
    age int default 0,
    detail_id int unique not null,
    foreign key(detail_id) references author_detail(id)
    on update cascade 
    on delete cascade
);



# 必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);  # 错误
mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address');
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
mysql>: insert into author_detail(info,address) values('Bob_info','Bob_address');
mysql>: insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);

# 修改关联表 author
mysql>: update author set detail_id=3 where detail_id=2;  # 失败，3详情不存在
mysql>: update author set detail_id=1 where detail_id=2;  # 失败，1详情已被关联
mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据，就可以修改
# 删除关联表 author
mysql>: delete from author where detail_id=3;  # 直接删除

# 修改被关联表 author_detail
mysql>: update author_detail set id=10 where id=1;  # 级联修改，同步关系关联表外键

# 删除被关联表 author_detail
mysql>: delete from author where detail_id=10;  # 可以删除对被关联表无影响
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 10);
mysql>: delete from author_detail where id=10;  # 可以删除，将关联表的记录级联删除掉

32.一对多

# 一对多：外键必须放在多的一方，此时外键值不唯一

# 出版社(publish)： id，name，address，phone
create table publish(
    id int primary key auto_increment,
    name varchar(64),
    address varchar(256),
    phone char(20)
);

# 书(book)：id，name，price，publish_id, author_id
create table book(
    id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5, 2) default 0,
    publish_id int,  # 一对多的外键不能设置唯一
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade
);

# 增：先增加被关联表(publish)的数据，再增加关联表(book)的数据
mysql>: insert into publish(name, address, phone) values
('人民出版社', '北京', '010-110'),
('西交大出版社', '西安', '010-119'),
('老男孩出版社', '上海', '010-120');

mysql>: insert into book(name, price, publish_id) values
('西游记', 6.66, 1),
('东游记', 8.66, 1),
('python从入门到入土', 2.66, 2),
('轮程序员修养之道', 3.66, 3),
('好好活着', 88.88, 3);
# 没有被关联的字段，插入依旧错误
mysql>: insert into book(name, price, publish_id) values ('打脸之道', 0.3, 4);  # 失败


# 更新：直接更新被关联表的(publish) 主键，关联表(book) 外键 会级联更新
mysql>: update publish set id=10 where id=1;
# 更新：直接更新关联表的(book) 外键，修改的值对应被关联表(publish) 主键 如果存在，可以更新成功，反之失败
mysql>: update book set publish_id=2 where id=4;  # 成功
mysql>: update book set publish_id=1 where id=4;  # 失败


# 删：
#   删被关联表，关联表会被级联删除
mysql>: delete from publish where id = 2;

#   删关联表，被关联表不会发生变化
mysql>: delete from book where publish_id = 3;

# 假设：书与作者也是 一对多 关系，一个作者可以出版多本书
create table book(
    id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5, 2) default 0,
    publish_id int,  # 一对多的外键不能设置唯一
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade
    
    # 建立与作者 一对多 的外键关联
    author_id int,  
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);

33.多对多

# 多对多：一定要创建第三张表(关系表)，每一个外键值不唯一，看可以多个外键建立联合唯一

mysql>: drop table author;
mysql>: drop table author_detail;
mysql>: drop table book;
mysql>: drop table publish;

# 作者(author)：id, name, age
create table author(
    id int primary key auto_increment,
    name varchar(64),
    age int unsigned default 0
);

# 出版社(publish)：id, name, address
create table publish(
    id int primary key auto_increment,
    name varchar(64),
    address varchar(256)
);

# 作者与出版社关系表：id, author_id, publish_id
create table author_publish(
    id int primary key auto_increment,
    # 关系表一定有多个外键，关联着多张表
    # 关联作者表
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    # 关联出版社表
    publish_id int,
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade,
    # 建立两个字段的联合唯一
    unique(author_id, publish_id)
);

# 注：关系表 关联着 作者 和 出版社 两张表，在表结构上 作者 与 出版社 两表键没有任何关系


# 增：两张被关联表，没有前后关系，但关系表必须在两个表都提供数据后才能进行 关系匹配
mysql>: insert into author(name, age) values('ruakei', 67),('engo', 76),('Lxx', 3);
mysql>: insert into publish(name, address) values('老男孩出版社', '上海'),('小女孩出版社', '北京');

# 操作关系表：
mysql>: insert into author_publish(author_id, publish_id) values(1,1),(1,2),(2,1),(2,2),(3,1);

# 关系表操作：增、删、改，只要两张被关系表有提供对应的操作数据，都可以操作成功，且对两张被关系表没有影响


# 操作两张被关系表：
#       增：不会影响关系表
mysql>: insert into publish(name, address) values('西交大出版社', '西安');
#       改：关系表都会级联更新
mysql>: update publish set id=10 where id=1;
#       删：关系表都会级联删除
mysql>: delete from author where name='ruakei';

多表关系小练习

1、创建一个stu表，字段有：自增主键id，不为空姓名，默认值性别(枚举类型)，无限制身高
create table stu(
    id int primary key auto_increment,
    name varchar(256) not null,
    gender enum('男','女') default '男',
    height float(5,2) unsigned
);
2、为stu表依次插入以下三条数据

   i）插入一条包含id，name，gender，height四个信息的数据
insert into stu values(1,'tom','男',180.88);

   ii）插入一条name，gender，height三个信息的数据
insert into stu(name,gender,height) values('bob','女',155.55);
   iii）插入一条只有name信息的数据
insert into stu(name) values('rock');

mysql> select * from stu;
+----+------+--------+--------+
| id | name | gender | height |
+----+------+--------+--------+
|  1 | tom  | 男     | 180.88 |
|  2 | bob  | 女     | 155.55 |
|  3 | rock | 男     |   NULL |
+----+------+--------+--------+

3、创建一张有姓名、年龄的teacher表，在最后添加工资字段，在姓名后添加id主键字段
mysql> create table teacher(
    name varchar(256),
    id int primary key auto_increment,
    age int,
    sarlay float(5,2)
);
mysql> desc teacher;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| name   | varchar(256) | YES  |     | NULL    |                |
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| age    | int(11)      | YES  |     | NULL    |                |
| sarlay | float(5,2)   | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

4、思考：将4中id字段移到到表的最前方，形成最终字段顺序为id、姓名、年龄、工资
mysql> alter table teacher modify id int first;
mysql> desc teacher;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   | PRI | 0       |       |
| name   | varchar(256) | YES  |     | NULL    |       |
| age    | int(11)      | YES  |     | NULL    |       |
| sarlay | float(5,2)   | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
5、完成 学生表 与 国家表 的 多对一 表关系的创建，并完成数据测试
mysql> create table country(
         id int primary key auto_increment,
         name char(16)
     );

mysql> create table student(
         id int primary key auto_increment,
         name varchar(256) not null,
         gender enum('男','女') default '男',
         country_id int,
         foreign key(country_id) references country(id)
         on update cascade
         on delete cascade
     );

#增加数据
mysql> insert into country(name) values('中国');
mysql> insert into student(name,gender,country_id) values('Bob','男',1),('Tom','男',1),('Cob','男',1),('Tob','女',1);



# 更新数据

# 可以对被关联表进行更改


mysql> update country set id=10 where id=1;

mysql> update student set id=1 where id=10;


# 不能对外键直接更改
#错误
mysql> update student set country_id=1 where country_id=10;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)



#删除数据

mysql> delete from student where id=1;

mysql> select * from student;
+----+------+--------+------------+
| id | name | gender | country_id |
+----+------+--------+------------+
|  2 | Tom  | 男     |         10 |
|  3 | Cob  | 男     |         10 |
|  4 | Tob  | 女     |         10 |
+----+------+--------+------------+

mysql> select * from country;
+----+--------+
| id | name   |
+----+--------+
| 10 | 中国   |
+----+--------+

mysql> delete from country where id=10;

mysql> select * from student;
Empty set (0.00 sec)

mysql> select * from country;
Empty set (0.00 sec)

6、完成 学生表 与 课程表 的 多对多 表关系的创建，并完成数据测试
 create table student(
         id int primary key auto_increment,
         name varchar(256) not null,
         gender enum('男','女') default '男'
     );
create table course(
    id int primary key auto_increment,
    name char(16)
);
create table student_course(
    id int primary key auto_increment,
    student_id int not null,
    foreign key(student_id) references student(id)
    on update cascade
    on delete cascade,
    course_id int not null,
    foreign key(course_id) references course(id)
    on update cascade
    on delete cascade
);





# 增加数据

mysql> insert into student(name,gender) values('Bob','男'),('Tom','男'),('Cob','男'),('Tob','女');

mysql> insert into course(name) values('数学'),('python'),('linux');

mysql> insert into student_course(student_id,course_id) values(1,1),(1,2),(1,3),(2,2),(2,3),(3,3);


# 增加
mysql> insert into student(name,gender) values('Aob','男');

#改

mysql> update student set id=10 where id=1;

mysql> update course set id=20 where id=1;



#删

mysql> delete from student where id=10;


mysql> select * from student_course;
+----+------------+-----------+
| id | student_id | course_id |
+----+------------+-----------+
|  4 |          2 |         2 |
|  5 |          2 |         3 |
|  6 |          3 |         3 |
+----+------------+-----------+

mysql> select * from student;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  2 | Tom  | 男     |
|  3 | Cob  | 男     |
|  4 | Tob  | 女     |
|  5 | Aob  | 男     |
+----+------+--------+

mysql> select * from course;
+----+--------+
| id | name   |
+----+--------+
|  2 | python |
|  3 | linux  |
| 20 | 数学   |
+----+--------+

7、完成 学生表 与 学生简介表 的 一对一 表关系的创建，并完成数据测试
create table student_info(
    id int primary key auto_increment,
    info varchar(256),
    address varchar(256)
);
create table student(
    id int primary key auto_increment,
    name varchar(256) not null,
    gender enum('男','女') default '男',
    info_id int not null,
    foreign key(info_id) references student_info(id)
    on update cascade
    on delete cascade
 );
 #创建后




################增加数据
mysql> insert into student_info(info,address) values('BOb_info','Bob_address'),('Tom_info','Tom_address');

mysql> insert into student(name,gender,info_id) values('Bob','男',1),('Tom','男',2);


### 修改数据
mysql> update student set id=10 where id=1;
mysql> update student_info set id=10 where id=1;
mysql> select * from student;
+----+------+--------+---------+
| id | name | gender | info_id |
+----+------+--------+---------+
|  2 | Tom  | 男     |       2 |
| 10 | Bob  | 男     |      10 |
+----+------+--------+---------+

8、将 学生表、国家表、课程表、学生简介表 四个表放在一起考虑表关系，并完成数据的增删测试
 #国家表
 create table country(
     id int primary key auto_increment,
     name char(16)
 );
 
#学生简介
create table student_info(
    id int primary key auto_increment,
    info varchar(256),
    address varchar(256)
);

#学生表
create table student(
    id int primary key auto_increment,
    name varchar(256) not null,
    gender enum('男','女') default '男',
    info_id int not null,
    foreign key(info_id) references student_info(id)
    on update cascade
    on delete cascade,
    country_id int not null,
    foreign key(country_id) references country(id)
    on update cascade
    on delete cascade   
 ); 
 
 
#课程表 
 create table course(
    id int primary key auto_increment,
    name char(16)
);

#多对多学生和课程
create table student_course(
    id int primary key auto_increment,
    student_id int not null,
    foreign key(student_id) references student(id)
    on update cascade
    on delete cascade,
    course_id int not null,
    foreign key(course_id) references course(id)
    on update cascade
    on delete cascade
);


#增加

mysql> insert into country(name) values('中国');

mysql> insert into student_info(info,address) values('BOb_info','Bob_address'),('Tom_info','Tom_address');

mysql> insert into student(name,gender,info_id,country_id) values('Bob','男',1,1),('Tom','男',1,1);

mysql> insert into course(name) values('数学'),('python'),('linux');

mysql> insert into student_course(student_id,course_id) values(1,1),(1,2),(1,3),(2,2),(2,3);


#删除
mysql> delete from course where id=1;

mysql> delete from student where id=1;

mysql> select * from student;
+----+------+--------+---------+------------+
| id | name | gender | info_id | country_id |
+----+------+--------+---------+------------+
|  2 | Tom  | 男     |       1 |          1 |
+----+------+--------+---------+------------+


mysql> select * from student_info;
+----+----------+-------------+
| id | info     | address     |
+----+----------+-------------+
|  1 | BOb_info | Bob_address |
|  2 | Tom_info | Tom_address |
+----+----------+-------------+

mysql> select * from course;
+----+--------+
| id | name   |
+----+--------+
|  2 | python |
|  3 | linux  |
+----+--------+

mysql> select * from student_course;
+----+------------+-----------+
| id | student_id | course_id |
+----+------------+-----------+
|  4 |          2 |         2 |
|  5 |          2 |         3 |
+----+------------+-----------+

四、查询

34.查询语法

select [distinct] 字段1 [[as] 别名], ..., 字段n [[as] 别名] from [库名.]表名
                    [
                    where 约束条件
                    group by 分组依据
                    having 过滤条件
                    order by 排序的字段
                    limit 限制显示的条数
                    ];
注：
1.查表中所有字段用*表示
2.条件的书写规则严格按照语法顺序书写，可以缺省，但不可以错序
3.约束条件的流程：from -> where -> group by -> having -> distinct -> order by -> limit
4.字段可以起别名
5.字段可以直接做运算 select age + 1 'new_age' from emp;
6.分组后的条件均可以使用聚合函数

                        
3.
def from():
    return "查询的文件"
def where(file):
    return "条件筛选后的结果"
def group_by(res):
    return "分组后的结果"
def having(res):
    return "再次过滤后的结果"
def distinct(res):
    return "去重后的结果"
def order_by(res):
    return "排序后的结果"
def limit(res):
    return "限制条数后的结果"

def select(from=from, where=null, ..., limit=null):
    file = from()
    res = where(file) if where else file
    res = group_by(res) if group_by else res
    ...
    res = limit(res) if limit else res
    
    return res
select(where=where, group_by=group_by)

35.单表查询

sql中几乎所有的操作都是单表查询，多表也会连成单边，临时表也会创建视图。

select distinct 字段 from 表 where group by having order by limit

"""
增：
insert [into] 
    [数据库名.]表名[(字段1[, ..., 字段n])] 
values 
    (数据1[, ..., 数据n])[, ..., (数据1[, ..., 数据n])];

删：
delete from [数据库名.]表名 [条件];

改：
updata [数据库名.]表名 set 字段1=值1[, ..., 字段n=值n] [条件];

查：
select [distinct] 字段1 [[as] 别名1],...,字段n [[as] 别名n] from [数据库名.]表名 [条件];
"""

# 条件：from、where、group by、having、distinct、order by、limit => 层层筛选后的结果
# 注：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，distinct稍有特殊(书写位置)，条件的种类可以不全
# 可以缺失，但不能乱序

35.1.去重：distinct

mysql>: 
create table t1(
    id int,
    x int,
    y int
);

mysql>: insert into t1 values(1, 1, 1), (2, 1, 2), (3, 2, 2), (4, 2, 2);

mysql>: select distinct * from t1;  # 全部数据

mysql>: select distinct x, y from t1;  # 结果 1,1  1,2  2,2

mysql>: select distinct y from t1;  # 结果 1  2

# 总结：distinct对参与查询的所有字段，整体去重(所查的全部字段的值都相同，才认为是重复数据)

数据准备

CREATE TABLE `emp`  ( 
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `gender` enum('男','女','未知') NULL DEFAULT '未知',
  `age` int(0) NULL DEFAULT 0,
  `salary` float NULL DEFAULT 0,
  `area` varchar(20) NULL DEFAULT '中国',
  `port` varchar(20) DEFAULT '未知',
  `dep` varchar(20),
  PRIMARY KEY (`id`)
);

INSERT INTO `emp` VALUES 
    (1, 'yangsir', '男', 42, 10.5, '上海', '浦东', '教职部'),
    (2, 'engo', '男', 38, 9.4, '山东', '济南', '教学部'),
    (3, 'jerry', '女', 30, 3.0, '江苏', '张家港', '教学部'),
    (4, 'tank', '女', 28, 2.4, '广州', '广东', '教学部'),
    (5, 'jiboy', '男', 28, 2.4, '江苏', '苏州', '教学部'),
    (6, 'zero', '男', 18, 8.8, '中国', '黄浦', '咨询部'),
    (7, 'owen', '男', 18, 8.8, '安徽', '宣城', '教学部'),
    (8, 'jason', '男', 28, 9.8, '安徽', '巢湖', '教学部'),
    (9, 'ying', '女', 36, 1.2, '安徽', '芜湖', '咨询部'),
    (10, 'kevin', '男', 36, 5.8, '山东', '济南', '教学部'),
    (11, 'monkey', '女', 28, 1.2, '山东', '青岛', '教职部'),
    (12, 'san', '男', 30, 9.0, '上海', '浦东', '咨询部'),
    (13, 'san1', '男', 30, 6.0, '上海', '浦东', '咨询部'),
    (14, 'san2', '男', 30, 6.0, '上海', '浦西', '教学部'),
    (15, 'ruakei', '女', 67, 2.501, '上海', '陆家嘴', '教学部');

35.2常用函数

拼接：
    concat(字段1,...,字段n)：完成字段的拼接
    concat_ws(x, 字段1,...,字段n)：完成字段的拼接，x为连接符


大小写：
    lower()：小写
    upper()：大写
浮点型操作：
    ceil()：向上取整
    floor()：向下取整
    round()：四舍五入
整型：可以直接运算



mysql>: select name,area,port from emp;

mysql> select name,area,port from emp limit 3;
+---------+--------+-----------+
| name    | area   | port      |
+---------+--------+-----------+
| yangsir | 上海   | 浦东      |
| engo    | 山东   | 济南      |
| jerry   | 江苏   | 张家港    |
+---------+--------+-----------+

mysql>: select name as 姓名, concat(area,'-',port) 地址 from emp;  # 上海-浦东

mysql> select name as 姓名,concat(area,'-',port) 地址 from emp limit 3;
+---------+------------------+
| 姓名    | 地址             |
+---------+------------------+
| yangsir | 上海-浦东        |
| engo    | 山东-济南        |
| jerry   | 江苏-张家港      |
+---------+------------------+

mysql>: select name as 姓名, concat_ws('-',area,port,dep) 信息 from emp;  # 上海-浦东-教职部

mysql> select name as 姓名,concat_ws('-',area,port,dep) 地址 from emp limit 3;
+---------+----------------------------+
| 姓名    | 地址                       |
+---------+----------------------------+
| yangsir | 上海-浦东-教职部           |
| engo    | 山东-济南-教学部           |
| jerry   | 江苏-张家港-教学部         |
+---------+----------------------------+
3 rows in set (0.03 sec)
mysql>: select upper(name) 姓名大写,lower(name) 姓名小写 from emp;

mysql> select upper(name) 大写姓名,lower(name) 小写姓名 from emp limit 3;
+--------------+--------------+
| 大写姓名     | 小写姓名     |
+--------------+--------------+
| YANGSIR      | yangsir      |
| ENGO         | engo         |
| JERRY        | jerry        |
+--------------+--------------+

mysql>: select id,salary,ceil(salary)上薪资,floor(salary)下薪资,round(salary)入薪资 from emp;

mysql> select salary,ceil(salary) 上薪资,floor(salary) 下薪资,round(salary) 四舍五入薪资 from emp limit 3;
+--------+-----------+-----------+--------------------+
| salary | 上薪资    | 下薪资    | 四舍五入薪资       |
+--------+-----------+-----------+--------------------+
|   10.5 |        11 |        10 |                 10 |
|    9.4 |        10 |         9 |                  9 |
|      3 |         3 |         3 |                  3 |
+--------+-----------+-----------+--------------------+

mysql>: select name 姓名, age 旧年龄, age+1 新年龄 from emp;

mysql> select name 姓名,age 旧年龄,age+1 新年龄 from emp limit 3;
+---------+-----------+-----------+
| 姓名    | 旧年龄    | 新年龄    |
+---------+-----------+-----------+
| yangsir |        42 |        43 |
| engo    |        38 |        39 |
| jerry   |        30 |        31 |
+---------+-----------+-----------+

35.3条件：where

# 多条件协调操作导入：where 奇数 [group by 部门 having 平均薪资] order by [平均]薪资 limit 1

mysql>: select * from emp where id<5 limit 1;  # 正常
mysql>: select * from emp limit 1 where id<5;  # 异常，条件乱序

# 判断规则
"""
比较符合：>  |  <  |  >=  |  <=  |  =  |  !=
区间符合：between 开始 and 结束 |  in(自定义容器)
    between 10 and 20：10~20
    in(10, 20, 30)：10或20或30
逻辑符合：and  |  or  |  not
相似符合：like _|%，模糊匹配字符串，_表示一个字符，%表示任意字符
正则符合：regexp 正则语法
"""
mysql>: select * from emp where salary>5;
mysql>: select * from emp where id%2=0;

mysql>: select * from emp where salary between 6 and 9;

mysql>: select * from emp where id in(1, 3, 7, 20);

# _o 某o | __o 某某o | _o% 某o* (*是0~n个任意字符) | %o% *o*
mysql>: select * from emp where name like '%o%';
mysql>: select * from emp where name like '_o%';  
mysql>: select * from emp where name like '___o%';

# sql只支持部分正则语法
mysql>: select * from emp where name regexp '.*\d';  # 不支持\d代表数字，认为\d就是普通字符串
mysql>: select * from emp where name regexp '.*[0-9]';  # 支持[]语法

35.4where条件之正则匹配

# why: like完成模糊匹配, 但功能局限, 可以模糊个数, 但不能模糊类型, 正则可以完成类型及个数的模糊匹配
'''
语法：字段 regexp '正则表达式'
注：只支持部分正则语法
'''
# 完成需求:
select * from emp where name regexp '.*[0-9]+.*';

35.5分组与筛选：group by | having

35.5.1 where与having区别

# 表象：在没有分组的情况下，where与having结果相同
# 重点：having可以对 聚合结果 进行筛选
mysql>: select * from emp where salary > 5;
mysql>: select * from emp having salary > 5;

mysql>: select * from emp where id in (5, 10, 15, 20);
mysql>: select * from emp having id in (5, 10, 15, 20);

35.5.2聚合函数

"""
max()：最大值
min()：最小值
avg()：平均值
sum()：和
count()：记数
group_concat()：组内字段拼接，用来查看组内其他字段
"""

35.5.3如何分组之解决分组中思考题的过程

# res = select max(salary) '最高薪资', gender from emp where dep='教学部' group by gender;
# select name from emp where (salary 跟 res作比较)
# 一个查询依赖于另一个查询的结果 => 一个查询的结果作为另外一个查询的条件 => 子查询

35.5.4分组查询 group by

'''
分组：根据字段相同值形成不同的类别，不明确分组其实整个表就为一个默认大组
原因：把以值共性得到的类别作为考虑单位，不再关系单条记录，而且一组记录

结果：只能考虑组内多条数据的聚会结果（聚合函数结果），分组的字段同样是聚合结果，如：组内的最大最小值
'''
# 修改my.ini配置重启mysql服务
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 在sql_mode没有 ONLY_FULL_GROUP_BY 限制下，可以执行，但结果没有意义
# 有 ONLY_FULL_GROUP_BY 限制，报错
mysql>: select * from emp group by dep;

# 分组后，表中数据考虑范围就不是 单条记录，因为每个分组都包含了多条记录，参照分组字段，对每个分组中的 多条记录 统一处理
# eg: 按部门分组，每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资、组里一共有多少人

# 将多条数据统一处理，这种方式就叫 聚合
# 每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资 都称之为 聚合结果 - 聚合函数操作的结果
# 注：参与分组的字段，也归于 聚合结果

mysql>: 
select 
    dep 部门,
    group_concat(name) 成员,
    max(salary) 最高薪资,
    min(salary) 最低薪资,
    avg(salary) 平均薪资,
    sum(salary) 总薪资,
    count(gender) 人数
from emp group by dep;

mysql>: select 
    dep 部门,
    max(age) 最高年龄
from emp group by dep;

# 总结：分组后，查询条件只能为 分组字段 和 聚合函数操作的聚合结果

35.5.5分组后的having

mysql>: 
select 
    dep 部门,
    group_concat(name) 成员,
    max(salary) 最高薪资,
    min(salary) 最低薪资,
    avg(salary) 平均薪资,
    sum(salary) 总薪资,
    count(gender) 人数
from emp group by dep;

# 最低薪资小于2
mysql>:
select 
    dep 部门,
    group_concat(name) 成员,
    max(salary) 最高薪资,
    min(salary) 最低薪资,
    avg(salary) 平均薪资,
    sum(salary) 总薪资,
    count(gender) 人数
from emp group by dep having min(salary)<2;

# having可以对 聚合结果 再进行筛选，where不可以

36.排序order by

排序规则

# order by 主排序字段 [asc|desc], 次排序字段1 [asc|desc], ...次排序字段n [asc|desc]



升序：order by 字段名  asc
降序：order by 字段名 desc
多个排序条件：order by 字段名 asc,字段名 desc

未分组状态下

mysql>: select * from emp;

# 按年龄升序
mysql>: select * from emp order by age asc;
# 按薪资降序
mysql>: select * from emp order by salary desc;

# 按薪资降序，如果相同，再按年龄降序
mysql>: select * from emp order by salary desc, age desc;
# 按龄降序，如果相同，再按薪资降序
mysql>: select * from emp order by age desc, salary desc;

分组状态下

mysql>:
select 
    dep 部门,
    group_concat(name) 成员,
    max(salary) 最高薪资,
    min(salary) 最低薪资,
    avg(salary) 平均薪资,
    sum(salary) 总薪资,
    count(gender) 人数
from emp group by dep;

# 最高薪资降序
mysql:
select 
    dep 部门,
    group_concat(name) 成员,
    max(salary) 最高薪资,
    min(salary) 最低薪资,
    avg(salary) 平均薪资,
    sum(salary) 总薪资,
    count(gender) 人数
from emp group by dep
order by 最高薪资 desc;

37.限制 limit

# 语法：limit 条数  |  limit 偏移量,条数
mysql>: select name, salary from emp where salary<8 order by salary desc limit 1;

mysql>: select * from emp limit 5,3;  # 先偏移5条满足条件的记录，再查询3条

38.连表查询

连接

# 连接：将有联系的多张表通过关联(有联系就行，不一定是外键)字段，进行连接，形参一张大表
# 连表查询：在大表的基础上进行查询，就称之为连表查询

# 将表与表建立连接的方式有四种：内连接、左连接、右连接、全连接

一对多数据准备

mysql>: create database db3;
mysql>: use db3;

mysql>: 
create table dep(
    id int primary key auto_increment,
    name varchar(16),
    work varchar(16)
);
create table emp(
    id int primary key auto_increment,
    name varchar(16),
    salary float,
    dep_id int
);
insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课'), (3, '管理部', '开车');
insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1),('yingjie', 1.2, 0);

笛卡尔积

# 笛卡尔积: 集合 X{a, b} * Y{o, p, q} => Z{{a, o}, {a, p}, {a, q}, {b, o}, {b, p}, {b, q}}

mysql>: select * from emp, dep;

# 总结：是两张表 记录的所有排列组合，数据没有利用价值


注意: 同时查询两张表形成新的表,可以称之为虚拟表, 原表与表之间可能存在重复字段, 同时使用时需要明确所属表,必要时还需明确所属数据库

内连接

# 关键字：inner join on
# 语法：from A表 inner join B表 on A表.关联字段=B表.关联字段

mysql>: 
select 
    emp.id,emp.name,salary,dep.name,work 
from emp inner join dep on emp.dep_id = dep.id 
order by emp.id;

# 总结：只保留两个表有关联的数据

左连接

# 关键字：left join on
# 语法：from 左表 left join 右表 on 左表.关联字段=右表.关联字段

mysql>: 
select 
    emp.id,emp.name,salary,dep.name,work 
from emp left join dep on emp.dep_id = dep.id 
order by emp.id;

# 总结：保留左表的全部数据，右表有对应数据直接连表显示，没有对应关系空填充

右连接

# 关键字：right join on
# 语法：from A表 right join B表 on A表.关联字段=B表关联字段

mysql>: 
select 
    emp.id,emp.name,salary,dep.name,work 
from emp right join dep on emp.dep_id = dep.id 
order by emp.id;

# 总结：保留右表的全部数据，左表有对应数据直接连表显示，没有对应关系空填充

左右可以相互转化

mysql>: 
select 
    emp.id,emp.name,salary,dep.name,work 
from emp right join dep on emp.dep_id = dep.id 
order by emp.id;

mysql>: 
select 
    emp.id,emp.name,salary,dep.name,work 
from dep left join emp on emp.dep_id = dep.id 
order by emp.id;

# 总结：更换一下左右表的位置，相对应更换左右连接关键字，结果相同

全连接

mysql>: 
select 
    emp.id,emp.name,salary,dep.name,work 
from emp left join dep on emp.dep_id = dep.id 

union

select 
    emp.id,emp.name,salary,dep.name,work 
from emp right join dep on emp.dep_id = dep.id 

order by id;

# 总结：左表右表数据都被保留，彼此有对应关系正常显示，彼此没有对应关系均空填充对方

一对一与一对多情况一致

# 创建一对一 作者与作者详情 表
create table author(
    id int,
    name varchar(64),
    detail_id int
);
create table author_detail(
    id int,
    phone varchar(11)
);
# 填充数据
insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);
insert into author_detail values(1, '13344556677'), (2, '14466779988'), (3, '12344332255');

# 内连
select author.id,name,phone from author join author_detail on author.detail_id = author_detail.id order by author.id;

# 全连
select author.id,name,phone from author left join author_detail on author.detail_id = author_detail.id
union
select author.id,name,phone from author right join author_detail on author.detail_id = author_detail.id
order by id;

多对多：两表两表建立连接

# 在一对一基础上，建立 作者与书 的多对多关系关系

# 利用之前的作者表
create table author(
    id int,
    name varchar(64),
    detail_id int
);
insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);

# 创建新的书表
create table book(
    id int,
    name varchar(64),
    price decimal(5,2)
);
insert into book values(1, 'python', 3.66), (2, 'Linux', 2.66), (3, 'Go', 4.66);

# 创建 作者与书 的关系表
create table author_book(
    id int,
    author_id int,
    book_id int
);
# 数据：author-book：1-1,2  2-2,3  3-1,3
insert into author_book values(1,1,1),(2,1,2),(3,2,2),(4,2,3),(5,3,1),(6,3,3);

# 将有关联的表一一建立连接，查询所以自己所需字段
select book.name, book.price, author.name, author_detail.phone from book 
join author_book on book.id = author_book.book_id
join author on author_book.author_id = author.id
left join author_detail on author.detail_id = author_detail.id;

表查询练习

1、查询教学部山东人的平均薪资

select avg(salary) from emp 
    where area="山东" 
    group by dep 
    having dep="教学部";

select avg(salary) from emp
    where dep="教学部" 
    group by area
    having area="山东";

select avg(salary) from emp
    where dep="教学部" and area="山东";

2、查询姓名中包含英文字母n并且居住在上海的人的所有信息

select * from emp
    where name like "%n%" and area="上海";

3、查询姓名中包含英文字母n但不包含数字的人的所有信息

select * from emp 
    where  name like "%n%"  
    and name not  regexp '.\*[0-9].\*';

4、查看各部门的平均年龄并升序排序

select dep, avg(salary) '平均薪资' from emp 
    group by dep 
    order by avg(salary) asc;

5、查询各部门中年纪最大的人的姓名与居住地（户籍+区域）

select name, concat(area, '-', port), dep from emp 
    where (age, dep) in 
    (
        select max(age), dep from emp 
        group by dep
    );

6、查询不同年龄层次平均薪资大于5w组中工资最高者的姓名与薪资

select name, salary  from emp 
    where (salary, age) in 
    (
        select  max(salary), age from emp 
        group by age 
        having avg(salary) > 5
    );

7、查询每一个部门下的员工们及员工职责

select max(dep.name), max(dep.work), group_concat(emp.name) from emp right join dep on emp.dep_id = dep.id group by dep_id;

39.联合分组

# 数据来源：在单表emp下

# 联合分组：按多个字段综合结果进行分组

# 按 area与port组合后的结果进行分组，只有组合后的结果还一致，才认为是一组
select group_concat(name),area,port from emp group by area,port;

40.子查询

查询结果辅助另外一条sql的增删改查。

如果查询结果为一条

where 字段 = （select 字段 from 表名）

如果查询结果为多条

where 字段 in （select 字段 from 表名）

where 字段 > all（select 字段 from 表名）

where 字段 > any（select 字段 from 表名）

# 增：insert into 表 select子查询
# 删：delete from 表 条件是select子查询(表不能与delete表相同)
# 查：select 字段 from 表 条件是select子查询
# 改：update 表 set 字段=值 条件是select子查询(表不能与update表相同)

# 数据来源：在单表emp下

# 子查询：将一条查询sql的结果作为另一条sql的条件

# 思考：每个部门最高薪资的那个人所有信息

# 子查询的sql
select dep, max(salary) from emp group by dep;
# 子查询 - 查
select * from emp where (dep, salary) in (select dep, max(salary) from emp group by dep);

# 将子查询转换为一张表
# 创建一个存子查询数据的一张表
create table t1(dep_name varchar(64), max_salary decimal(5,2));
# 子查询 - 增
insert into t1 select dep, max(salary) from emp group by dep;
# 需求
select name, dep_name, salary 
from emp join t1 
on emp.dep=t1.dep_name and emp.salary=t1.max_salary;

# 子查询 - 改（update更新的表不能 与 子查询select的表同表）
# 每个部门最大薪资+1
update t1 set max_salary=max_salary+1;
# 给t1额外增加一个新部门
insert into t1 values ('打杂部', 100);
# 子查询 - 改
update t1 set max_salary=max_salary+1 where dep_name in (select distinct dep from emp);
# 错误：update更新的表 与 子查询select的表 相同
update t1 set max_salary=max_salary+1 where dep_name in (select distinct dep_name from t1);

# 子查询 - 删
delete from t1 where dep_name in (select distinct dep from emp);
# 错误: delete删除的表 与 子查询select的表 相同
delete from t1 where dep_name in (select distinct dep_name from t1);

41.all与any：区间修饰条件

# 语法规则
# where id in (1, 2, 3) => id是1或2或3
# where id not in (1, 2, 3) => id不是1，2，3
# where salary < all(3, 6, 9) => salary必须小于所有情况（小于最小）
# where salary > all(3, 6, 9) => salary必须大于所有情况（大于最大）
# where salary < any(3, 6, 9) => salary只要小于一种情况（小于最大）
# where salary > any(3, 6, 9) => salary只要大于一种情况（大于最小）
in < > ()
# 案例
select * from emp where salary < all(select salary from emp where id>11);

42.视图：view

视图与真实表共享数据，视图和真实表的数据会相互映射，

视图的表结构只要存在真实表的数据只要存在，视图就可以使用。

# 数据依赖：单表emp
"""
1）视图是存在内存中的临时表
2）视图的创建依赖select语句，所有就是select语句操作的结果形参的表
3）视图支持对数据的增删查改 ?
4）视图不允许对视图表的字段做修改
5）视图不仅支持创建，也支持更新与删除
"""
# 语法
# 创建视图
mysql>: create view 视图名[(别名们)] as select 语句;
eg>: create view v1 as select dep, max(salary) from emp group by dep;

# 创建或替换视图
mysql>: create or replace 视图名[(别名们)] as select 语句;
mysql>: alter 视图名[(别名们)] as select 语句;
eg>: create or replace view v1(dep_name, max_salary) as select dep, max(salary) from emp group by dep;
eg>: alter view v1(name, salary) as select dep, max(salary) from emp group by dep;

# 删除视图
mysql>: drop view 视图名
eg>: drop view v1;

# 视图可以作为正常表完成连表查询
select name, dep_name, salary 
from emp join v1 
on emp.dep=v1.dep_name and emp.salary=v1.max_salary;

43.视图的增删改

# 前提：视图的增删改操作可以直接映射给真实表（本质就是对真实表进行操作）

# 视图可以完成增删改，增删改本质是直接对创建视图的真实表进行操作
create or replace view v2 as select id,name,age,salary from emp;
update v2 set salary=salary+1 where id=1;
delete from v2 where id=1;

create or replace view v3 as select * from emp;
insert into v3 values(1, 'yangsir', '男', 66, 1.11, '上海', '那噶的', '教职部');

# 总结：操作视图，会影响真实表，反之也会影响
update emp set salary=salary+1 where id=1;

44.事务

满足事物的四大特性就是事物。mysql语句自动启动事物，每一句都是事物的执行过程。

# 事务：通常一些业务需要多条sql参与，参与的sql会形参一个执行整体，该整体我们就称之为 事务
# 简而言之：事务 - 就是保护多条执行的sql语句
# 比如：转账就是一个事务：从一个用户将资金转出，再将资金转入到另一个用户

事务的使用：
start transaction; --开启事物,在这条语句之后的sql将处在同一事务,并不会立即修改数据库
commit;--提交事务,让这个事物中的sql立即执行数据的操作,
rollback;--回滚事务,取消这个事物,这个事物不会对数据库中的数据产生任何影响

""" 事务的四大特性
1.原子性：事务是一组不可分割的单位,要么同时成功,要么同时不成功
2.一致性：事物前后的数据完整性应该保持一致(数据库的完整性：如果数据库在某一时间点下，所有的数据都符合所有的约束，则称数据库为完整性的状态)
3.隔离性：事物的隔离性是指多个用户并发访问数据时，一个用户的事物不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离
4.持久性：持久性是指一个事物一旦被提交，它对数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
"""




事务的用户隔离级别:
数据库使用者可以控制数据库工作在哪个级别下,就可与防止不同的隔离性问题
read uncommitted --不做任何隔离,可能脏读,幻读
read committed --可以防止脏读,不能防止不可重复读,和幻读, 
Repeatable read --可以防止脏读,不可重复读,不能防止幻读
Serializable --数据库运行在串行化实现,所有问题都没有,就是性能低

修改隔离级别:
select @@tx_isolation;--查询当前级别 
set[session|global] transaction isolation level ....;修改级别
实例:
set global transaction isolation level Repeatable read;
注：修改后重新连接服务器生效





'''
what：事务是逻辑上的一组操作，要么都成功，要么都失败
why：很多时候一个数据操作，不是一个sql语句就完成的，可能有很多个sql语句，如果部分sql执行成功而部分sql执行失败将导致数据错乱
eg：转账 => 转入转出均成功，才能认为操作成功


'''



'''

# mysql中事务的执行
create table bank(
    id int,
    name varchar(16),
    money decimal(65, 2)
);
insert into bank values(1, 'Tom', 10), (2, "Bob", 10);

# 假设出现以下执行情况

# 没有事务支持情况下，Tom的钱就丢了
update bank set money=money-1 where name='Tom';
update bank set money=money+1 where name='ruakei';




# 将两条sql看做事务处理
# 开启事务
begin;
update bank set money=money-1 where name='Tom';
update bank set money=money+1 where name='ruakei';
# 确认无误，提交事务
commit;
# 确认有误，回滚
rollback;

五、pymysql：python操作mysql

安装

>: pip3 install pymysql

增删改查

# 选取操作的模块 pymysql

# pymysql连接数据库的必要参数：主机、端口、用户名、密码、数据库
# 注：pymysql不能提供创建数据库的服务，数据库要提前创建
import pymysql

# 1）建立数据库连接对象 conn
# 2）通过 conn 创建操作sql的 游标对象
# 3）编写sql交给 cursor 执行
# 4）如果是查询，通过 cursor对象 获取结果
# 5）操作完毕，端口操作与连接


# 1）建立数据库连接对象 conn
conn = pymysql.connect(user='root', passwd='root', database='oldboy')
# conn = pymysql.connect(user='root', passwd='root', database='oldboy', autocommit=True)

# 2）通过 conn 创建操作sql的 游标对象
# 注：游标不设置参数，查询的结果就是数据元组，数据没有标识性
# 设置pymysql.cursors.DictCursor，查询的结果是字典，key是表的字段
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 3）编写sql交给 cursor 执行

创建表

# 创建表
sql1 = 'create table t1(id int, x int, y int)'
cursor.execute(sql1)

增

sql2 = 'insert into t1 values(%s, %s, %s)'

# 增1
cursor.execute(sql2, (1, 10, 100))
cursor.execute(sql2, (2, 20, 200))
# 重点：在创建conn对象时，不设置autocommit，默认开启事务，增删改操作不会直接映射到数据库中，
# 需要执行 conn.commit() 动作
conn.commit()

# 增多
cursor.executemany(sql2, [(3, 30, 300), (4, 40, 400)])
conn.commit()

删

sql3 = 'delete from t1 where id=%s'
cursor.execute(sql3, 4)
conn.commit()

改

sql4 = 'update t1 set y=666 where id=2'
cursor.execute(sql4)
conn.commit()

查

sql5 = 'select * from t1'
row = cursor.execute(sql5)  # 返回值是受影响的行
print(row)

# 4）如果是查询，通过 cursor对象 获取结果
# fetchone() 偏移一条取出，fetchmany(n) 偏移n条取出，fetchall() 偏移剩余全部
r1 = cursor.fetchone()
print(r1)
r2 = cursor.fetchone()
print(r2)
r3 = cursor.fetchmany(1)
print(r3)
r4 = cursor.fetchall()
print(r4)

# 5）操作完毕，端口操作与连接
cursor.close()
conn.close()

46.游标操作

import pymysql
from pymysql.cursors import DictCursor

# 1）建立数据库连接对象 conn
conn = pymysql.connect(user='root', passwd='root', db='oldboy')
# 2）通过 conn 创建操作sql的 游标对象
cursor = conn.cursor(DictCursor)
# 3）编写sql交给 cursor 执行
sql = 'select * from t1'
# 4）如果是查询，通过 cursor对象 获取结果
row = cursor.execute(sql)
if row:
    r1 = cursor.fetchmany(2)
    print(r1)

    # 操作游标
    # cursor.scroll(0, 'absolute')  # absolute绝对偏移，游标重置，从头开始偏移
    cursor.scroll(-2, 'relative')  # relative相对偏移，游标在当前位置进行左右偏移

    r2 = cursor.fetchone()
    print(r2)

# 5）操作完毕，端口操作与连接
cursor.close()
conn.close()

47.pymysql事务

import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='root', passwd='root', db='oldboy')
cursor = conn.cursor(DictCursor)

try:
    sql = 'create table t2(id int, name char(4), money int)'
    row = cursor.execute(sql)
    print(row)
except:
    print('表已创建')
    pass

# 空表才插入
row = cursor.execute('select * from t2')
if not row:
    sql = 'insert into t2 values(%s,%s,%s)'
    row = cursor.executemany(sql, [(1, 'tom', 10), (2, 'Bob', 10)])
    conn.commit()


# 可能会出现异常的sql
"""
try:
    sql1 = 'update t2 set money=money-1 where name="tom"'
    cursor.execute(sql1)
    sql2 = 'update t2 set moneys=money+1 where name="Bob"'
    cursor.execute(sql2)
except:
    print('转账执行异常')
    conn.rollback()
else:
    print('转账成功')
    conn.commit()
"""

try:
    sql1 = 'update t2 set money=money-1 where name="tom"'
    r1 = cursor.execute(sql1)
    sql2 = 'update t2 set money=money+1 where name="ruakei"'  # 转入的人不存在
    r2 = cursor.execute(sql2)
except:
    print('转账执行异常')
    conn.rollback()
else:
    print('转账没有异常')
    if r1 == 1 and r2 == 1:
        print('转账成功')
        conn.commit()
    else:
        conn.rollback()

48.sql注入问题和解决

import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='root', passwd='root', db='oldboy')
cursor = conn.cursor(DictCursor)

try:
    sql = 'create table user(id int, name char(4), password char(6))'
    row = cursor.execute(sql)
    print(row)
except:
    print('表已创建')
    pass

# 空表才插入
row = cursor.execute('select * from user')
if not row:
    sql = 'insert into user values(%s,%s,%s)'
    row = cursor.executemany(sql, [(1, 'tom', '123'), (2, 'bob', 'abc')])
    conn.commit()



# 用户登录
usr = input('usr: ')
pwd = input('pwd: ')

# 自己拼接参数一定有sql注入，将数据的占位填充交给pymysql

"""
sql = 'select * from user where name="%s" and password="%s"' % (usr, pwd)
row = cursor.execute(sql)
if row:
    print('登录成功')
else:
    print('登录失败')
"""

# 知道用户名时
# 输入用户时：
#   tom => select * from user where name="tom" and password="%s"
#   tom" # => select * from user where name="tom" #" and password="%s"

# 不自定义用户名时
#   " or 1=1 # => select * from user where name="" or 1=1 #" and password="%s"


解决注入问题

sql = 'select * from user where name=%s and password=%s'
row = cursor.execute(sql, (usr, pwd))
if row:
    print('登录成功')
else:
    print('登录失败')

49.索引

# 索引就是 键 - key

"""
1）键 是添加给数据库表的 字段 的
2）给表创建 键 后，该表不仅会形参 表结构、表数据，还有 键的B+结构图
3）键的结构图是需要维护的，在数据完成增、删、改操作时，只要影响到有键的字段，结构图都要维护一次
    所以创建键后一定会降低 增、删、改 的效率
4）键可以极大的加快查询速度(开发需求中，几乎业务都和查有关系)
5）建立键的方式：主键、外键、唯一键、index
"""

import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='root', passwd='root', db='oldboy')
cursor = conn.cursor(DictCursor)

# 创建两张表
# sql1 = """create table a1(
#     id int primary key auto_increment,
#     x int,
#     y int
# )"""
# cursor.execute(sql1)
# sql2 = """create table a2(
#     id int primary key auto_increment,
#     x int,
#     y int,
#     index(x)###############索引，对x的索引，增加索引，提高查询率。
# )"""
# cursor.execute(sql2)

# 每个表插入5000条数据
# import random
# for i in range(1, 5001):
#     x = i
#     y = random.randint(1, 5000)
#     cursor.execute('insert into a1(x, y) values(%s, %s)', (x, y))
#     cursor.execute('insert into a2(x, y) values(%s, %s)', (x, y))
#
# conn.commit()

import time
# a1的x、a1的id、a2的x
b_time = time.time()
sql = 'select * from a1 where id=4975'
cursor.execute(sql)
e_time = time.time()
print(e_time - b_time)

b_time = time.time()
sql = 'select * from a1 where x=4975'
cursor.execute(sql)
e_time = time.time()
print(e_time - b_time)

b_time = time.time()
sql = 'select * from a2 where x=4975'
cursor.execute(sql)
e_time = time.time()
print(e_time - b_time)

触发器

'''
what：在表发生数据更新时，会自动触发的功能称之为触发器
why：当一个表在发生数据更新时，需要去完成一些操作，可以为具体数据更新的方式添加触发器

语法：
delimiter //
create trigger 触发器名 before|after insert|update|delete on 表名 for each row
begin 
    需要触发执行的sql代码们
end //
delimiter ;

# 触发器名: t1_before_insert_tri

注：delimiter是用来修改sql的语句结束标识符

删除触发器：drop trigger 触发器名;
'''

# cmd表
create table cmd (
    id int primary key auto_increment,
    user char(32),
    priv char(10),
    cmd char (64),
    sub_time datetime, # 提交时间
    success enum ('yes', 'no') # 0代表执行失败
);
# 错误日志表
create table errlog (
    id int primary key auto_increment,
    err_cmd char(64),
    err_time datetime
);
# 创建触发器
delimiter //
create trigger trigger1 after insert on cmd for each row
begin
# new就是cmd当前插入的那条记录(对象)
if new.success = "no" then
    insert into errlog values(null, new.cmd, new.sub_time);
end if;
end //
delimiter ;

# 往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
insert into cmd(user, priv, cmd, sub_time, success) values
    ('egon', '0765', 'ls -l /etc', now(), 'yes'),
    ('jerry', '0852', 'cat /etc/passwd', now(), 'no'),
    ('kevin', '0867', 'useradd xxx', now(), 'no'),
    ('owen', '0912', 'ps aux', now(), 'yes');
# 查看cmd数据信息
select * from cmd;
# 查看错误日志表中的记录是否有自动插入
select * from errlog;

存储过程

'''
what：用于完成指定功能的sql语句块，类似于Python中的函数
why：将能指定功能的sql语句块建立成存储过程，不仅将sql语句逻辑化了，更是功能化了，那我们要完成相同的事，只需要重复使用建立的存储过程，不就需要再重复书写sql语句了
# 总结: 存储过程可以让sql语句具有 复用性, 从而提高开发效率

语法：
delimiter //
create procedure 存储过程名(
    输入输出类型1 参数名1 参数类型1(宽度), 
    ... ,
    输入输出类型n 参数名n 参数类型n(宽度)
)
begin
sql语句块
end //
delimiter ;
注：
1.输入输出类型：in | out | inout
2.call 存储过程名(实参们)来调用存储过程

案例：
set @res = null; # 定义空值变量, 用来接收存储过程的执行结果
delimiter //
create procedure user_info(in b int, in l int, out res char(20))
begin
select * from emp limit b, l;
set res = 'success';
end //
delimiter ;
call user_info(2, 3, @res); # 调用存储过程, 传入相应的实参
select @res; # 查看存储过程的执行结果

变量的使用：
1.赋值变量：set @变量名 = 变量值
2.使用变量：@变量名 | select @变量名
3.删除变量：set @变量名 = null

三种开发方式：
1. 业务逻辑 + 存储过程：高执行与开发效率，低耦合 | 不易移植，人员成本高
2. 业务逻辑 + 原生sql：人员成本低 | 开发难度大
3. 业务逻辑 + ORM：高开发效率，对象化操作数据库，可移植 | 性能消耗加大，多表联查、复杂条件会复制化ORM

存储过程的操作：
1.查看
select routine_name, routine_type from information_schema.routines where routine_schema='数据库名';

eg: select routine_name, routine_type from information_schema.routines where routine_schema='db2';

2.删除
drop procedure [if exists] 数据库名.存储过程名
'''

delimiter //
create procedure send_money( out p_return_code char(20) )
begin 
    # 异常处理
    declare exit handler for sqlexception 
    begin 
        # error 
        set p_return_code = '错误异常'; 
        rollback; 
    end; 
    # exit 也可以换成continue 表示发送异常时继续执行
    declare exit handler for sqlwarning 
    begin 
        # warning 
        set p_return_code = '警告异常'; 
        rollback; 
    end; 

    start transaction;
    update account set money = money - 1000 where id = 1;
    update account set money = moneys + 1000 where id = 2; # moneys字段导致异常
    commit; 

    # success 
    set p_return_code = '转账成功'; # 代表执行成功
end //
delimiter ;

# 在mysql中调用存储过程
set @res=null;
call send_money(@res);
select @res;

流程控制

if语句的使用

第一种 if:
"""
if 条件 then
语句;
end if;
"""
第二种 if elseif
"""
if 条件  then
语句1;
elseif 条件 then
语句2;
else 语句3;
end if;
"""

案例:编写过程实现输入一个整数type 范围 1 - 2 输出 type=1 or type=2 or type=other;

delimiter //
create procedure showType(in type int,out result char(20))
begin
if type = 1 then 
set result = "type = 1";
elseif type = 2 then 
set result = "type = 2";
else 
set result = "type = other";
end if;
end //
delimiter ;

set @res=null;
call showType(100, @res);
select @res;

CASE 语句

大体意思与Swtich一样的你给我一个值我对它进行选择然后执行匹配上的语句
语法:

create procedure caseTest(in type int)
begin
CASE type 
when 1  then select "type = 1";
when 2  then select "type = 2";
else select "type = other";
end case;
end

定义变量

declare 变量名类型 default 值;
例如: declare i int default 0;

WHILE循环

循环输出10次hello mysql
create procedure showHello()
begin 
declare i int default 0;
while  i < 10 do
select "hello mysql";
set i  = i + 1;
end while;
end

LOOP循环的

没有条件需要自己定义结束语句
语法:

输出十次hello mysql;
create procedure showloop()
begin 
declare i int default 0;
aloop: LOOP
select "hello loop";
set i = i + 1;
if i > 9 then leave aloop;
end if;
end LOOP aloop;
end

REPEAT循环

#类似do while
#输出10次hello repeat
create procedure showRepeat()
begin
declare i int default 0;
repeat
select "hello repeat";
set i = i + 1;
until i > 9
end repeat;
end

#输出0-100之间的奇数
create procedure showjishu()
begin
declare i int default 0;
aloop: loop
set i = i + 1;
if i >= 101 then leave aloop; end if;
if i % 2 = 0 then iterate aloop; end if;
select i;
end loop aloop;
end

转载于:https://www.cnblogs.com/SkyOceanchen/p/11594599.html