什么是数据（Data）
描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机
什么是数据库（DataBase，简称DB）
用来存储数据的仓库
数据库可以在硬盘及内存中存储数据
数据库与文件存储数据区别
数据库本质也是通过文件来存储数据, 数据库的概念就是系统的管理存储数据的文件
什么是数据库管理系统（DataBase Management System 简称DBMS）
一个系统软件---数据库管理系统
如MySQL、Oracle、SQLite、Access、MS SQL Server
mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。
oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
sql server是微软公司的产品，主要应用于大中型企业，如联想、方正等。
数据库服务器端: 存放数据库的主机集群
数据库客户端: 可以连接数据库的任意客户端
数据库管理员: DBA
数据库组成(重点)
数据库服务器：一台计算机（对内存要求比较高）
数据库管理系统：如mysql（是一个软件）
库: 多表构建一个数据库, 本质就是文件夹
表: 多条数据构建一张表, 本质就是文件
记录: 存放一条条数据, （多个字段的信息组成一条记录，即文件中的一行内容）本质就是文件中一条条数据记录
(字段)id, name, age, gender...字段就是表中定义的某一个或多个属性，是有类型的，而没有实际数据。一条记录对应多个字段，并根据多个字段的类型逐一赋值，它是有实际数据的，一条记录的数据项个数取决于你定义的字段的个数。
字段和记录都可以有多个，但是字段是垂直叠加的，而记录是水平叠加。
一个表先有表和字段，才会有记录。
总结：
数据库服务器-：运行数据库管理软件
数据库管理软件：管理-数据库
数据库：即文件夹，用来组织文件/表
表：即文件，用来存放多行内容/多条记录
数据库分类
关系型数据库
1.有表的概念
2.以表中一条条记录存储数据
如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
非关系型数据库
1.没有表的概念
2.通过key-value键值对方式存储数据
mongodb，redis，memcache
MySQL介绍与安装
MySQL是一个关系型数据库管理系统(基于socket编写的C/S架构的软件)
Mysql卸载
"""
前提装过mysql）启动终端，输入 mysql，如果不是提示 不是内部或外部命令，代表已安装数据库
1）直接连入
i 搜索：服务，检索mysql服务，有
停止服务，并移除服务 - 启动管理员终端：mysqld --remove
ii 搜索：服务，检索mysql服务，无 （mysql使用命令启动）
停止服务，启动管理员终端
tasklist | findstr mysql
taskkill /pid 进程pid /f
2）连接超时
搜索：服务，检索mysql服务，移除服务即可
3）移除环境变量
总结：如果能连入，先停止服务 | 如果添加了服务，就移除服务 | 取消环境变量配置
"""
安装
"""
1、解压免安装版本（5.6 - 没有初始密码）
2、配置环境变量：数据库绝对路径下的bin
3、配置服务：
启动管理员终端：mysqld --install [nmysql]
"""
连接数据库(cmd)
1）游客登陆（不一定能登入，登入了也啥都不能干）
>: mysql
2）账号密码登录
>: mysql -u root -p
再输入密码，没有任何提示，没有密码直接回车
3）连接指定服务器的mysql
>: mysql -h ip地址 -P 端口号 -u 账号 -p
回车后敲入密码
eg：
>: mysql -hlocalhost -P3306 -uroot -p
4）退出数据库
>: quit
或>: exit
用户信息查看
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 '密码';
数据库的基本操作
1）查看已有数据库
mysql>：show databases;
2）选择某个数据库
mysql>：use 数据库名
3）查看当前所在数据库
mysql>：select database();
4）创建数据库
mysql>：create database 数据库名 [charset=编码格式];
eg>: create database owen;
eg>: create database zero charset=utf8;
eg>: create database tank;
5）查看创建数据库的详细内容
mysql>：show create database 数据库名;
eg>: show create database owen;
6）删除数据库
mysql>: drop database 数据库名;
eg>: drop database tank;
7)改,修改数据库编码集
mysql>: alter database db1 charset="utf8";
表的基本操作
前提：先选取要操作的数据库
1）查看已有表
mysql>：show tables;
2）创建表
mysql>：create table 表名(字段们);
eg>: create table student(name varchar(16), age int);
eg>: create table teacher(name varchar(16), age int);
3）查看创建表的sql
mysql>：show create table 表名;
eg>: show create table student;
4）查看创建表的结构
mysql>：desc 表名;
5）删除表
mysql>: drop table 表名;
eg>: drop table teacher;
6)# 修改表名
mysql>: alter table 旧表 rename 新表;
# 修改字段名
mysql>: alter table 表名 change 旧字段 新字段 类型(长度);
# 修改字段属性
mysql>: alter table 表名 modify 字段 新类型(长度);
7)修改表的引擎与字符编码
alter table 表名 engine="引擎名" charset="编码名";
8)复制表 *
# 结构
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 旧表名;
注: 会复制表结构+数据, 但不会复制约束条件
9)清空表
truncate 表名;
注：表被重置，自增字段重置
'''
记录的基本操作
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;
#清空表，会重置自增信息
truncate table 表名;
truncate table nt1;
insert into nt1(x, y) values(6, 66);
20190924
数据库配置文件
# 通过配置文件统一配置的目的：统一管理 服务端(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）重启数据库服务
用户权限操作：(重点)
# 为特定的数据库分配有该数据库 操作权限 的用户
mysql>: grant 权限们 on 数据库.表 to 用户名@'主机名' identified by '密码';
eg>: grant all on oldboy.* to oldboy@'localhost' identified by 'Oldboy123';
# 1）all：所有权限
# 2）oldboy.*：oldboy数据库下所有表
# 3）oldboy@'localhost'：本机可以通过oldboy用户登入
# 4）identified by 'Oldboy123'：密码为Oldboy123
eg>: grant select,delete,update,insert,drop,alter on oldboy.* to oldboy@'%' identified by 'Oldboy123';
# 1）select,delete,update,insert,drop,alter：指定的权限
# 2）oldboy.*：oldboy数据库下所有表
# 3）oldboy@'%'：任何机器可以通过oldboy用户登入
# 4）identified by 'Oldboy123'：密码为Oldboy123
# 撤销权限
mysql>: revoke 权限1,权限2,... on 数据库名.表名 from 用户名@'主机名';
# 禁掉本地oldboy用户对oldboy数据库的所有表的drop权限
eg:> revoke drop on oldboy.* from oldboy@'localhost';
# 删除用户
drop user 用户名@'主机名';
创建表的完整语法
# 长度和约束在某些情况下是可以省略的
mysql>: create table 表名 (
属性名1 类型(长度) 约束,
...
属性名n 类型(长度) 约束
) engine=引擎 default charset=utf8;
数据库表的引擎：驱动数据的方式 - 数据库优化
# 前提: 引擎是建表是规定, 提供给表使用的, 不是数据库
# 展示所有引擎
mysql> show engines;
# innodb(默认): 支持事务, 行级锁, 外键
mysql>: create table t11(id int)engine=innodb;
# myisam: 查询效率要优于innodb, 当不需要支持事务, 行级锁, 外键, 可以通过设置myisam来优化数据库
mysql>: create table t12(id int)engine=myisam;
# blackhole：黑洞，存进去的数据都会消失（可以理解不存数据）
mysql>: create table t13(id int)engine=blackhole;
# memory：表结构是存储在硬盘上的，但是表数据全部存储在内存中
mysql>: create table t14(id int)engine=memory;
数据库的安全模式与非安全模式
# 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
mysql支持的数据类型
整型
'''类型
tinyint：1字节，取值范围-128~127，默认长度4
smallint：2字节，取值范围 -32768 ~ 32767，默认长度6
mediumint：3字节
int：4字节 -2147483648~2147483647
bigint：8字节
'''
'''约束
unsigned：无符号
zerofill：0填充
'''
# 建表
mysql>: create table tb1(x tinyint, y smallint, z int(6));
# 插入数据
mysql>: insert into tb1 values(128, 32768, 32768); # 结果：127，32767，32768
# 结论：整型的长度由所占字节(取值范围)决定，可以自定义长度，但是不影响所占字节(取值范围)
# 所有整型变量的长度一般都省略不写
结论>: create table tb1(x tinyint, y smallint, z int);
# 整型约束
mysql>: create table tb2(x tinyint unsigned); # 0~255
mysql>: insert into tb2 values(256), (-1); # 255, 0
# 0填充约束
mysql>: create table tb3(x tinyint unsigned zerofill);
mysql>: insert into tb3 values(10); # 010
浮点型
# 在安全模式下测试浮点型类型
'''类型
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
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
字符串：
'''类型
char：定长，永远采用设置的长度存储数据
varchar：不定长，在设置的长度范围内，变长的存储数据
数据库优化 - 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'
时间类型
'''类型
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
枚举与集合类型
# 枚举与集合：为某一个字段提供选项的 - 枚举只能单选(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', '女', '男,女,哇塞');
约束
"""
primary key：主键，唯一标识，表都会拥有，不设置为默认找第一个 不空，唯一 字段，未标识则创建隐藏字段
foreign key：外键
unique：唯一性数据, 该条字段的值需要保证唯一,不能重复
auto_increment：自增，只能加给key的int类型字段，作为辅助修饰，一个表中只能设置一个自增字段
not null：不为空 - 针对一些字段，如注册时的用户名，出生人的性别等，这些需求下的字段，只不能设置为Null，必须要对其赋值
default：默认值 - 对有默认值意外的字段进行赋值时，有默认值的字段会被赋默认值
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)
);
# 通常自增字段的 自增索引 会被永久记录，想清空表并清空自增索引：
mysql>: truncate 数据库名.表名
20190924
字段操作
create table t2(
id int primary key auto_increment,
x int,
y int
);
insert into t2(x, y) values(10, 20), (100, 200), (1000, 2000);
'''
1.修改字段信息
alter table 表名 modify 字段名 类型[(宽度) 约束];
alter table t2 modify x bigint default 0; # 模式不同, 涉及精度问题
2.修改字段名及信息
alter table 表名 change 旧字段名 新字段名 类型[(宽度) 约束];
alter table t2 change y c char(10) not null; # 模式不同, 涉及类型转换问题
3.添加字段名
# 末尾添加
alter table 表名 add 字段名 类型[(宽度) 约束], ..., add 字段名 类型[(宽度) 约束];
alter table t2 add age int, add gender enum("male", "female", "wasai") default "wasai";
# t头部添加
alter table 表名 add 字段名 类型[(宽度) 约束] first;
# 指定位添加：指定字段后
alter table 表名 add 字段名 类型[(宽度) 约束] after 旧字段名;
alter table t2 add y int after x;
4.删除字段名
alter table 表名 drop 字段名;
alter table t2 drop y;
'''
多表关系
"""
一对一：丈夫-妻子，用户-身份证，作者-作者详情
一对多：部门-员工，班级-学生，书-出版社
多对多：老师-班级，课程-学生，出版社-作者
"""
# 书 - 出版社 - 作者 - 作者详情 外键分布
# 外键是 建立表与表关联 的字段，通常 一个表的外键 是 另一个表的主键（唯一键也可以）
# 一对一：外键在任何一方都可以，此时外键要设置 唯一键
"""
作者(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
id author_id publish_id
1 1 1
2 1 2
3 2 1
4 2 2
"""
外键
# 作者(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
# 重点：外键字段本身可以唯一或不唯一，但是外键关联的字段一定唯一
一对一：无级联关系
# 作者详情(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);
# 修改关联表 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; # 无法删除
# 没有级联关系下：
# 增加：先增加被关联表记录，再增加关联表记录
# 删除：先删除关联表记录，再删除被关联表记录
# 更新：关联与被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定，可以修改)
一对一：有级联关系
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; # 可以删除，将关联表的记录级联删除掉
一对多
# 一对多：外键必须放在多的一方，此时外键值不唯一
# 出版社(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
);
多对多
# 多对多：一定要创建第三张表(关系表)，每一个外键值不唯一，看可以多个外键建立联合唯一
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';
20190925
单表查询
"""
增：
insert [into]
[数据库名.]表名[(字段1[, ..., 字段n])]
values
(数据1[, ..., 数据n])[, ..., (数据1[, ..., 数据n])];
删：
delete from [数据库名.]表名 [条件];
改：
updata [数据库名.]表名 set 字段1=值1[, ..., 字段n=值n] [条件];
查：
select [distinct] 字段1 [as 别名], ..., 字段n [as 别名] from [库名.]表名
[
where 约束条件
group by 分组依据
having 过滤条件
order by 排序的字段
limit 限制显示的条数
];
"""
# 条件：from、where、group by、having、distinct、order by、limit => 层层筛选后的结果
# 注：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，distinct稍有特殊(书写位置)，条件的种类可以不全
# 可以缺失，但不能乱序
# 查表中所有字段用*表示
# 字段可以起别名
# 字段可以直接做运算 select age + 1 'new_age' from emp;
# 分组后的条件均可以使用聚合函数
去重：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, '上海', '陆家嘴', '教学部');
常用函数
"""
拼接：
concat(字段1,...,字段n)：完成字段的拼接
concat_ws(x, 字段1,...,字段n)：完成字段的拼接，x为连接符
大小写：
lower()：小写
upper()：大写
浮点型操作：
ceil()：向上取整
floor()：向下取整
round()：四舍五入
整型：可以直接运算
"""
mysql>: select name,area,port from emp;
mysql>: select name as 姓名, concat(area,'-',port) 地址 from emp; # 上海-浦东
mysql>: select name as 姓名, concat_ws('-',area,port,dep) 信息 from emp; # 上海-浦东-教职部
mysql>: select upper(name) 姓名大写,lower(name) 姓名小写 from emp;
mysql>: select id,salary,ceil(salary)上薪资,floor(salary)下薪资,round(salary)入薪资 from emp;
mysql>: select name 姓名, age 旧年龄, age+1 新年龄 from emp;
条件：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(自定义容器)
逻辑符合：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]'; # 支持[]语法
分组与筛选：group by | having
聚合函数
"""
max()：最大值
min()：最小值
avg()：平均值
sum()：和
count()：记数
group_concat()：组内字段拼接，用来查看组内其他字段
"""
分组查询 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;
# 总结：分组后，查询条件只能为 分组字段 和 聚合函数操作的聚合结果
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);
分组后的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不可以
排序
排序规则
# order by 主排序字段 [asc|desc], 次排序字段1 [asc|desc], ...次排序字段n [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;
限制 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条
连表查询
连接
# 连接：将有联系的多张表通过关联(有联系就行，不一定是外键)字段，进行连接，形参一张大表
# 连表查询：在大表的基础上进行查询，就称之为连表查询
# 将表与表建立连接的方式有四种：内连接、左连接、右连接、全连接
一对多数据准备
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;
联合分组
# 数据来源：在单表emp下
# 联合分组：按多个字段综合结果进行分组
# 按 area与port组合后的结果进行分组，只有组合后的结果还一致，才认为是一组
select group_concat(name),area,port from emp group by area,port;
子查询
# 增：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);
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);
视图：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;
视图的增删改
# 前提：视图的增删改操作可以直接映射给真实表（本质就是对真实表进行操作）
# 视图可以完成增删改，增删改本质是直接对创建视图的真实表进行操作
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;
事务
# 事务：通常一些业务需要多条sql参与，参与的sql会形参一个执行整体，该整体我们就称之为 事务
# 简而言之：事务 - 就是保护多条执行的sql语句
# 比如：转账就是一个事务：从一个用户将资金转出，再将资金转入到另一个用户
""" 事务的四大特性
1.原子性：事务是一组不可分割的单位,要么同时成功,要么同时不成功
2.一致性：事物前后的数据完整性应该保持一致(数据库的完整性：如果数据库在某一时间点下，所有的数据都符合所有的约束，则称数据库为完整性的状态)
3.隔离性：事物的隔离性是指多个用户并发访问数据时，一个用户的事物不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离
4.持久性：持久性是指一个事物一旦被提交，它对数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
"""
# 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;/start transaction;
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()
游标操作
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()
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()
pymysql处理了sql注入
# 什么是sql注入:
# SQL 注入是非常常见的一种网络攻击方式，主要是通过参数来让 mysql 执行 sql 语句时进行预期之外的操作。
# 只要是通过用户输入数据来拼接 sql 语句，就必须在第一时间考虑如何避免 SQL 注入问题
# pymysql 的 execute 支持参数化 sql，通过占位符 %s 配合参数就可以实现 sql 注入问题的避免
# 需要注意的是，不要因为参数是其他类型而换掉 %s，pymysql 的占位符并不是 python 的通用占位符。
# 同时，也不要因为参数是 string 就在 %s 两边加引号，mysql 会自动去处理。
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('登录失败')
"""
sql = 'select * from user where name=%s and password=%s'
row = cursor.execute(sql, (usr, pwd))
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"
索引
# 索引就是 键 - 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)
# )"""
# 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)