MySQL数据库

MySQL数据库

MySQL数据库

1.数据库前戏

1)存取数据的演变史

阶段一：文本文件
文件路径不固定、数据格式不统一

阶段二：软件开发目录规范
规定了数据应该保存在db目录下
但是数据格式仍未统一

阶段三：数据库服务
统一了存取位置,统一了操作方式
降低学习成本提高开发效率

2)数据库软件应用史

1)单机游戏：数据存储于各个计算机本地信息无法共享

2)网络游戏：数据存储于网络中信息可以共享(数据库服务) 但是必须联网

"""
远程数据库服务有两个问题：
	1.数据安全性问题
	2.服务器负载问题
解决方法：
	【数据库集群】(让多台服务器运行相同的数据库服务)
	既备份了数据又让服务器负载均衡
"""

3)数据库的本质

1)站在底层原理的角度
操作数据的进程(运行在内存中的代码)

2)站在实际应用的角度
可视化操作界面(用于操作进程的界面)

不做特殊说明时提数据库其实都是在指数据库软件
数据库软件本质就是cs架构的程序

4)数据库的分类(重要)

(1)关系型数据库

一般用在数据的永久存储

1)数据的存储方式 有固定的表结构(字段名、字段类型)
    eg：id  name  pwd
    # 关系型数据库存取方式可以看成是表格
    
2)表与表之间可以建立数据库层面的关系
    eg：用户表  豪宅表
    # 只要获取用户表中的某条数据 就可以获取豪宅表中对应的信息

# 常用关系型数据库
1.MySQL      ：开源免费 使用最广 性价比高  ※
2.MariaDB    ：开源免费 跟MySQL是同一个作者 用法相似
3.PostgreSQL ：开源免费 支持二次开发 兼容性高
4.Oracle     ：收费 安全性高但是维护成本也高 银行等重要机关用
5.sqlite     ：小型数据库 主要用于本地测试(django框架自带的数据库)

(2)非关系型数据库

一般用在数据的查询

1)数据的存储方式 没固定的表结构 
    eg：{'name':'jason'}
    # 主要采用k:v键值对形式
    
2)数据之间无法直接建立数据库层面的关系
    # 自己编写代码建立逻辑层面的关系

# 常用非关系型数据库
1.redis   ：目前最火、使用频率最高的缓存数据库  ※
             虽然缓存数据库是基于内存做数据存取,但是也拥有持久化的功能
2.mongoDB ：文档型数据库，最像关系型数据库的非关系型数据库  ※
             主要用在爬虫、大数据等
3.memcache：已经被redis淘汰

2.MySQL数据库简介

1)MySQL版本问题

5.6版本：学习阶段使用
5.7版本：使用频率最高
8.0版本：最新版

# mac建议下载8.0版本
# windows哪个版本都可以

2)下载与安装

1.访问官网  https://www.mysql.com/
2.点击下载  DOWNLOADS
3.最下方选择  MySQL Community (GPL) Downloads »
4.点击  MySQL Community Server
5.点击  Archives
6.选择版本 与 本机系统 (学习建议5.6版本 如：5.6.44)
7.选择64位然后download下载
8.解压安装
	上述方式下载的压缩包里含有服务端和客户端 支持本地操作

3)主要目录介绍

bin文件夹
    mysqld.exe服务端    mysql.exe客户端
data文件夹
    存取数据
my-default.ini
    默认配置文件

3.MySQL基本使用

# 建议以管理员身份启动

1.用cmd命令启动服务端
    cmd>>D:>>cd RuanJian\mysql-5.6.44-winx64\bin>>mysqld
    (Tab自动补全)
	'可能会报错:拷贝关键信息去百度'
    此时该cmd窗口已经启动了mysqld服务端(如果关闭窗口服务端也会停止)
    
2.再打开一个新的cmd窗口启动客户端
	cmd>>D:>>cd RuanJian\mysql-5.6.44-winx64\bin>>mysql
    直接回车会以'游客模式'进入 权限和功能很少
    
3.用管理员登录
    mysql -u用户名 -p密码(eg：mysql -uroot -p密码  直接回车)
    # mysql默认管理员账号 用户名是root 密码是空
    如：
    cmd>>D:>>cd RuanJian\mysql-5.6.44-winx64\bin>>mysql -uroot -p
    
4.退出
    exit  或  quit

4.系统服务制作

1.先把bin目录添加到环境变量中

2.将mysql服务端制作成系统服务(随着计算机的开启而启动 关闭而关闭，不需要每次都打开一个服务端再打开一个客户端)
	1)以管理员身份打开cmd窗口(搜索cmd 右键管理员模式启动)
  	2)管理员cmd中执行系统服务命令 mysqld --install
      (把MySQL添加到系统服务中)
	3)启动服务端
		方式1:右键直接点击启动
		方式2:管理员cmd中执行命令启动 net start mysql
"""
1.查看系统服务的命令
	services.ms
2.如何卸载重新安装：
  1)关闭mysql服务端
	net stop mysql
  2)移除系统服务
	1.先确保服务已经关闭
	2.执行移除命令 mysqld --remove
"""

5.密码相关操作

1.修改密码
	mysqladmin命令
        方式1:  # 直接在cmd中写 不要进入mysql
            mysqladmin -u用户名 -p原密码 password 新密码
				第一次修改
                  mysqladmin -uroot -p password 123
				第二次修改如：
                  mysqladmin -uroot -p123 password 321
                    
        方式2：直接修改存储用户数据的表
        
        方式3：偏门方式(有些版本无法使用):  # 需要先登录
            set password=password('新密码');

2.忘记密码
    方式1：卸载重装
    方式2：把data目录删除 拷贝别人的目录
    方式3：命令方式修改
        1.关闭正常的服务端：
            net stop mysql
        2.以跳过授权表的方式重启服务端(不校验密码)：
            mysqld --skip-grant-tables
        3.再开一个cmd以管理员身份进入：
            mysql -uroot -p
        4.修改mysql.user表数据
            update mysql.user set password=password('123') where Host='localhost' and User='root';
        5.net stop mysql
        6.net start mysql

6.SQL与NoSQL数据库语句

数据库服务端是可以服务读懂中类型的客户端
客户端可以是自己开发的也可以是python代码编写的也可以是java代码编写的

SQL：操作关系型数据库的语言
有时候也表示关系型数据库

NOSQL：操作非关系型数据库的语言
有时候也代表非关系型数据库

7.数据库重要概念

# 以下表示并不精准 仅限于小白了解
库      类似于   文件夹
表      类似于   文件夹里的文件
记录    类似于   文件夹里的文件里的每行数据

1.查看所有的数据库
    show databases;   
2.查看所有的表
    show tables;   
3.查看user表里的所有记录
    select * from mysql.user;

8.基本SQL语句

# 预备知识
1.SQL语句结束符是分号
    ;
2.取消SQL语句的执行(可以直接让他报错重新输入 反正不影响)
    \C

1)针对库的基本SQL语句

1.增
    create database 库名;  # 新建库
2.查
    show databases;  # 查看所有库名字
    show create database 库名;  # 查看指定库的信息
3.改
    alter database 库名 charset='gbk';  # 修改库的编码
4.删
    drop database 库名;  # 删除库

2)针对表的基本SQL语句

'操作表前需先确定库'

select database();  # 查看当前所在的库
use 库名;  # 切换库

1.增
    create table 表名(字段名 字段类型);  # 创建表
    create table t1(id int, name varchar(32));
2.查
    show tables;  # 查看库下所有的表名
    show create table 表名;  # 查看指定表信息
    describe 表名; 或 desc 表名; # 查看指定表中的字段
    """
    跨库查询表：
    desc 库名.表名;
    desc mysql.user;
    """
3.改
    alter table 旧表名 rename 新表名;  # 改表名
4.删
    drop table 表名;  # 删除表

3)针对记录的基本SQL语句

'操作记录前需先确定库和表'

1.增
    insert into 表名 values(数据值1,数据值2..);
    insert into t1 values(1,'jason');
    insert into t1 values(2,'torry'),(3,'jack');
2.查
    select * from 表名;  # 查看表中所有的数据
    '*表示查看所有字段'
    select * from mysql.user\G;
    '如果表中字段较多出现错乱 结尾可以加\G'
    select name,pwd from t1;  # 查看表中name和pwd字段的所有数据
3.改
    update 表名 set 字段名=新数据值 where 筛选条件;  # 修改表中某个条件下的字段名改为新数据值
    update t1 set name='torry' where id=1;
4.删
    delete from 表名;  # 删除表中所有的数据 
    '就会变成一个空表'
    delete from 表名 where 筛选条件  # 按照条件删除数据

9.字符编码与配置文件

\s #查看数据库基本信息(用户、版本号、字符编码、端口号等)
5.6版本字符编码【不统一】 需统一改为utf8
5.6以后的版本不需要 默认就是统一的utf8

1.MySQL文件中 my-default.ini #windows下MySQL默认配置文件，Mac后缀不一样
	再复制一份该文件,重命名为my.ini
2.清空里面内容 添加字符编码相关配置(百度查看)
  # 注：此处清空内容后会埋下一个坑 下面整型严格模式时会发现
    [mysqld]
      character-set-server=utf8mb4
      collation-server=utf8_general_ci
    [client]
      default-character-set=utf8mb4
    [mysql]
      default-character-set=utf8mb4 
   # utf8mb4能存储表情 功能更强大
   # utf8与utf-8不一样,MySQL只有utf8
   # 如果配置文件涉及到mysqld相关的配置修改,那必须重启服务端才能生效
   # 搜索【服务】,找到MySQL重新启动即可
   # 需注意以上修改编码前的之前的数据编码并未修改

"""
偷懒操作：每次登录还要输root 密码 太麻烦,所以可以在配置文件中的[mysql]下写好用户名和密码,这样以后登录的时候直接打mysql就可以登录
    [mysql]
        user='root'
        password='123'
"""

10.数据库存储引擎(重要)

存储引擎：数据库针对数据采取的多种存取方式
show engines; 查看所有的存储引擎

1)需了解的4个存储常见引擎

1.MyISAM
      1)MySQL 5.5及以前版本默认的存储引擎
      2)存取数据的速度快 功能较少 安全性较低

2.InnoDB  ※
      1)MySQL 5.6开始之后版本默认的存储引擎
      2)存取数据的速度没MyISAM快 支持事物、行锁、外键等功能 安全性较高
        
3.Memory
      1)基于内存的存储引擎
      2)存取数据极快 但是重启数据库,数据立刻丢失(仅用于临时表数据存取)
        
4.BlackHole
      1)黑洞 类似垃圾桶
      2)任何写进去的数据都会立刻丢掉

2)不同存储引擎之间底层文件的区别

MySQL中默认忽略大小写对大小写不敏感

# 1.创建4个表 分别对应4个不同的存储引擎
create table t1(id int) engine = myisam;
create table t2(id int) engine = innodb;
create table t3(id int) engine = memory;
create table t4(id int) engine = blackhole;

# 2.检查MySQL>>data目录>>对应库中的文件发现：各个表中新建的文件不同
1)t1表(MyISAM 存储引擎);
      t1.frm  '表结构'
      t1.MYD  '表数据'
      t1.MYI  '表索引(加快了数据查询)'
2)t2表(InnoDB 存储引擎);
      t2.frm  '表结构'
      t2.ibd  '表数据、表索引(加快了数据查询)'
3)t3表(Memory 存储引擎);
      t3.frm  '表结构'
4)t4表(BlackHole 存储引擎);
      t4.frm  '表结构'
    
# 3.往4个表中插入记录发现：
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

select * from t1; # 正常插入记录
select * from t2; # 正常插入记录
select * from t3; # 重启数据库Memory中的数据丢失
select * from t4; # 无法把数据存入BlackHole中

11.创建表的完整语法

create table 表名(
    字段名1 字段类型(数字) 约束条件，
    字段名2 字段类型(数字) 约束条件,
    字段名3 字段类型(数字) 约束条件
);


 1.字段名和字段类型必须要有(可以不用写多对,但是'至少写一对')
 2.字段类型后的数字 和 约束条件 可以写'也可以不写'
 3.约束条件可以写多个 不过需要'空格隔开'
*4.当多个字段名和字段类型时,最后一对'结尾不能加逗号'！！！

12.MySQL字段基本数据类型

1)整型

类型	所占字节bytes	带负号	不带负号
tinyint(微整型)	1	-128~127	0~255
smallint(小整型)	2	-32768~32767	0~65535
int(整型)	4	-2147483648~2147483647	0~4294967295
bigint(大整型)	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

(1)验证整型默认是否携带正负号

当默认携带正负号时范围则是负数到正数的值

create table t5(id tinyint);  # 创建t5表 类型为微整型
insert into t5 values(-129),(128);  # 插入两条数据 分别超出带正负号的最大、最小范围
select * from t5;  # 查看t5表中所有记录数据

1)'发现结果为-128 和 127' # 由此可得正负号也默认占一个字节
2)发现哪怕超出范围 MySQL会自动填写为两个边界值，不合理(应该直接报错)

(2)移除负号：解决正负号问题

当移除负号后范围则是正数的值

unsigned约束条件之一：不带负号

create table t6(id tinyint unsigned);  # 创建t6表 类型为微整型 不带负号
insert into t6(-1),(256);  # 插入两条数据 分别超出不带负号的最大、最小范围
select * from t6;  # 查看t6表中所有记录数据

1)'发现结果为0 和 255' # 由此可得移除负号后 则不能插入负数 只能插入正数,且范围变成了255
2)发现哪怕超出范围 MySQL会自动填写为两个边界值，不合理(应该直接报错)

(3)严格模式：解决自动填写边界值问题

插入的数据当超过数据类型的范围时应该直接报错,不该自动修改数据！这样会导致我插入的数据失去了本身的意义。

PS: 其实MySQL按理说是会报错的,只是在前面清空配置文件时清掉了

# 方式一：命令临时修改
set session sql_mode = 'strict_trans_tables'; # 在当前客户端有效
set global sql_mode = 'strict_trans_tables'; # 在当前服务端有效

# 方式二：在配置文件中永久修改(建议)
    [mysqld]
        sql_mode='STRICT_TRANS_TABLES'

2)浮点型

float
double
decimal
以上三种'都可以存储浮点型数据' 只是各自的'精确度不一样'

float(20,10)  # 共存储20位数 小数点后面占10位
double(20,10)  # 共存储20位数 小数点后面占10位
decimal(20,10)  # 共存储20位数 小数点后面占10位

(1)验证精确度问题

create table t7(id float(30,20));  # 创建t7表 类型为float 共存储30位数, 小数点后占20位
create table t8(id double(30,20));  # 创建t8表 类型为double 共存储30位数, 小数点后占20位
create table t9(id decimal(30,20));  # 创建t9表 类型为decimal 共存储30位数, 小数点后占20位

insert into t7 values(1.11111111111111111111);
insert into t8 values(1.11111111111111111111);
insert into t9 values(1.11111111111111111111);

select * from t7;  # 发现小数点后到第7位后精确度就不准确了 1.11111116409301760000
select * from t8;  # 发现小数点后到第15位后精确度就不准确了 1.11111111111111120000
select * from t9;  # 发现小数点后到第20位后精确度还很准确 1.11111111111111111111

"""
结论：
1.精确度排名
    float < double < decimal

2.一般float就够平时使用了，如果想追求完美的精确度可以用字符型来代替(如：手机号等)
"""

3)字符型

1)char 定长
     char(4)  
         # 最多存储4个字符 超出范围报错 
         # 不够4个用空格填充至4个
2)varchar 变长
     varchar(4)  
          # 最多存储4个字符 超出范围报错 
          # 不够4个有几个存几个

(1)验证char与varchar长度问题

char_length(字段名) 获取字段存储的数据长度

create table t10(name char(4)); # 创建t10表 类型为char 最大长度为4
create table t11(name varchar(4)); # 创建t11表 类型为varchar 最大长度为4

insert into t10 values('a');
insert into t11 values('b');

select char_length(name) from t10;  # 获取t10表中name字段数据的长度
select char_length(name) from t11;  # 获取t11表中name字段数据的长度
"""
以上结果：
    t10表中长度为1
    t11表中长度为1
    
1.提问：为什么char(4)不够4个用空格填充至4个 获取长度后还是1？
1.回答：因为默认情况下char在存储的时候针对没有满足固定位数的字符确实会自动在后面填充空格,但是【在读取的时候又会自动将填充的空格移除掉】

2.提问：如何取消该移除空格机制？
2.回答：命令临时修改
  set global sql_mode='strict_trans_tables,pad_char_to_full_length';
"""
# 需注意：上述临时配置命令是替换不是新增，所以要把之前配置的超出范围报错的命令也加上！

该取消移除空格机制命令不用添加到配置文件中 临时用一下就好,因为移除的机制挺好用的

(2)char与varchar哪个好

类型	优势	劣势
char	整存整取速度快	浪费存储空间
varchar	节省存储空间	存取数据的速度慢于char

# char与varchar在内存中怎么存数据的：

1)char(4)
    a  bb ccdddd
'占4个位置存放'

2)varchar(4)
1bytes+a1bytes+son1bytes+jack1bytes+tom1bytes+lili
'存储数据都是需要操作报头才能让varchar识别每个字符串多少长度分开处理。这就是varchar比char耗时多的原因'

# 结论：两者使用频率都很高根据实际应用决定 现在默认很多时候是varchar

4)数字的含义

数字在大部分情况下都是用来表示限制存储数据的长度

但是！在整型中是控制最少展示的长度！！

zerofill约束条件，不足的用0填充

# zerofill常用在快递单号中
create table t13(id int(3) zerofill);
insert into t13 values(1),(12345);

select * from t13;
"""
结果为：
001     因为最少展示3位 且不足的用0填充
12345   因为够3位了 所以不需要填充
"""
# 总结：以后涉及到整型字段的定义 类型后面不需要加括号写数字 除非有业务需求必须固定位数(快递单号等)

5)枚举与集合

(1)enum 枚举

多选一

# eg: 性别(男 女 其他)

create table t14(gender enum('male','female','others'));
# 只能选三个中的一个
insert into t14 values('male');

(2)set 集合

多选一或多选多

# eg：爱好(唱 跳 rap)
create table t15(hobby set('sing','jump','rap'));
# 只能输三个中的一个或多个
insert into t15 values('sing'); # 多选一
insert into t15 values('sing,jump'); # 多选多

6)日期

date		 年月日
datetime	 年月日时分秒
time		 时分秒
year		 年份

create table t16(date date, datetime datetime, time time,year year);
insert into t16 values('2022-11-23','2022-11-23 12:00:00','12:00:00','2022');
select * from t16;

# 结果：
+------------+---------------------+----------+------+
| date       | datetime            | time     | year |
+------------+---------------------+----------+------+
| 2022-11-23 | 2022-11-23 12:00:00 | 12:00:00 | 2022 |
+------------+---------------------+----------+------+

13.MySQL字段约束条件

约束条件：在字段类型的基础上给字段添加额外的限制

#预备知识：

1.# 空的记录为：NULL

2.# 插入数据
create table t1(id int,name varchar(32));

  1)# 默认按照创建表的字段顺序插入记录
  insert into 表名 values(值1,值2);  
  ——————————————————————————————————
  insert into t1 values(1,'jack');

  2)# 也可以自定义字段顺序插入记录
  insert into 表名(字段1,字段2) values(值1,值2);  
  ——————————————————————————————————
  insert into t1(name) values('lili');
  insert into t1(name,id) values('jason',3);

  select * from t1;

  """
  +------+-------+
  | id   | name  |
  +------+-------+
  |    1 | jack  |
  | NULL | lili  |
  |    3 | jason |
  +------+-------+
  """

1)无负号(符号)

unsigned无负号

# 创t2表 给id字段 且约束为无负号
create table t2(id int unsigned); # int整型无负号的范围为：0~4294967295

insert into t2 values(4294967295); # 插入最大范围的值

select * from t2;
"""
+------------+
| id         |
+------------+
| 4294967295 |
+------------+
"""

2)零填充

zerofill不够范围用0填充

# 创t3表 给id字段 且约束不够范围用0填充
create table t3(id int(5) zerofill);

insert into t3 values(1);

select * from t3;
"""
+-------+
| id    |
+-------+
| 00001 |
+-------+
"""

3)非空

not null该字段必须给一个值,不能为空

# 创t4表 给id 和 name字段 且约束name字段不能为空
create table t4(id int,name varchar(32) not null);

insert into t4 (id) values(1); # 发现会报错,因为没有给name插数据
insert into t4 (id,name) values(1,'jason');  # 可插入数据
insert into t4 (name) values('torry');  # 可插入数据

select * from t4;
"""
+------+-------+
| id   | name  |
+------+-------+
|    1 | jason |
| NULL | torry |
+------+-------+
"""

4)默认值

default不插入数据就用默认值

# 创t5表 给id 和 name字段 并约束两个字段为两个默认值
create table t5(id int default 111, name varchar(32) default 'aaa');

insert into t5 (id) values(1);  # 不给name插数据
insert into t5 (name) values('jason');  # 不给id插数据
insert into t5 values(3,'torry');  # 两个都插数据

select * from t5;
"""
+------+-------+
| id   | name  |
+------+-------+
|    1 | aaa   |
|  111 | jason |
|    3 | torry |
+------+-------+
"""

5)唯一值

unique字段中的数据必须是唯一

"""单例唯一"""
# 创t6表 给id字段 且约束数据单例唯一
create table t6(id int unique);
insert into t6 values(1);
insert into t6 values(1);# 报错,因为必须唯一

"""联合唯一"""
# 创t7表 给id和post字段 且约束两个字段数据联合唯一
create  table t7(ip varchar(32),port int,unique(ip,port));
insert into t7 values('192.168.1.1',8080);
insert into t7 values('192.168.1.1',8080); # 报错,因为必须联合唯一
insert into t7 values('192.168.1.1',8000); # 不报错,因为只有合在一起不唯一才报错
select * from t7;
"""
+-------------+------+
| ip          | port |
+-------------+------+
| 192.168.1.1 | 8000 |
| 192.168.1.1 | 8080 |
+-------------+------+
"""

6)主键

primary key给该字段设置为主键

(1)单从约束层面上看主键相当于非空且唯一（not null + unique

# 创t1表 给id与name字段 且约束id为主键(非空且唯一)
create table t1(id int primary key,name varchar(32));

insert into t1(name) values('jason'); # 报错,因为id不能为空且必须唯一
insert into t1(id) values(1); # 不报错 因为name可以为空

# 查看指定表中的字段等信息
desc t1; 
"""
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
"""

(2)InnoDB存储引擎规定一张表必须有且只有一个主键

主键是组织数据的重要条件，且可以加快数据查询速度

📌①.当表中没有主键也没有非空且唯一的字段时：
InnoDB会采用一个隐藏字段作为该表的主键(隐藏意味无法使用)
基于该表的数据查询只能一行一行查询速度很慢

📌②.当表中没有主键但是有非空且唯一的字段时：
InnoDB会自动将从上往下第一个非空且唯一的字段设置成主键

# 验证以上第二点
create table t2(
    id int,
    id1 int not null unique, 
    id2 int not null unique,
    id3 int not null unique
);

# 查看指定表中的字段等信息
desc t2;
"""
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| id1   | int(11) | NO   | PRI | NULL    |       |
| id2   | int(11) | NO   | UNI | NULL    |       |
| id3   | int(11) | NO   | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
"""

(3)每个表都应该有一个id字段,且应该为主键

类似编号等，uid、sid、pid、gid等

7)自增

auth_increment让主键id自增后续添加数据可以不写主键的数据

该约束条件一张表只能出现一次，主要配合主键一起用

# 需先约束主键 再约束自增

# 创t3表 给id和name字段 且约束id为主键且自增
create table t3(id int primary key auto_increment,name varchar(32));

insert into t3(name) values('jason'); # 不报错是因为主键自动自增

select * from t3;
"""
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
+----+-------+
"""

(1)自增的特性

①.自增不会因为删除单条数据或清空所有数据而回退或重置，永远只会往后自增。
如：现在主键id有1 2 3 4 如果删除4或清空所有数据，再插入新的数据来只会从5开始

# 创t4表 给id和name字段 且约束id为主键且自增
create table t4(id int primary key auto_increment,name varchar(32));

insert into t4(name) values('jason'),('torry'),('tom'),('jsck');

# 删除t4表中 id大于2的所有数据
delete from t4 where id>2;

# 插入一条新数据
insert into t4(name) values('lili');

select * from t4;
"""
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | torry |
|  5 | lili  |
+----+-------+
"""
# 以上可以发现新插入的数据id是从5开始的

②.如果插入了更大的数据，之后的自增会按照更大的数往后自增
如：新插入了更大的id：1000 之后自增就是1001开始

(2)重置表中的主键值：格式化表

truncate 格式化表(清空表数据,且重置主键)

# 格式化t4表
truncate t4;

# 给t4表插入一条新数据
insert into t4(name) values('jason')

# 查看主键是否重置
select * from t4;
"""
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
+----+-------+
"""
# 以上发现表中数据不仅被清空了,主键也重置了

8)外键

(1)外键前戏

# 需要一张员工表:
id   name   age   dep_name    dep_desc
1    张三   18      环安      负责安全
2    李四   20      财务      结算薪资
3    王五   19      环安      负责安全

# 以上表的缺陷：
1.表结构不清晰 到底是员工表还是部门表
2.部分字段数据反复填写 浪费存储空间
3.表的扩展性极差 修改部门时多个员工的部门都要改

# 优化操作：分成两个表
【员工表】
id   name   age

【部门表】
id    dep_name    dep_desc

# 但是两个表之间没有任何关系了。员工表中添加一个部门编号(外键)来填写部门表的主键,让两个表有关系
# 
【员工表】
id    name    age    dep_id

【部门表】
id   dep_name   dep_desc

(2)什么是外键字段

外键字段：就是用来记录表与表之间数据的关系

(3)建立外键字段

foreign key建立外键字段

1.外键字段：
foreign key(外键字段) references 被关联表(被关联表主键)

2.级联更新与级联删除：
# 被关联的数据一但变动 关联的数据同步变动
  级联更新：on update cascade
  级联删除：on delete cascade

'以上三个指令是一段话 中间不用加逗号'

"""
扩展:
    在实际工作中 很多时候可能并不会使用外键
        因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
    一般都是自己通过写代码建立逻辑层面的关系 不用外键 
        
   一般在公司表小于100张时属于较少,可以用外键。超过100则自己写SQL
"""

(4)表与表之间数据的四种关系判断

👻一对多

# 针对员工表和部门表判断表数据关系：

    1)先站在员工表的角度
        问:一个员工能否对应多个部门
        答:不可以
    2)再站在部门表的角度
        问:一个部门能否对应多个员工
        答:可以
    结论:一边可以一边不可以 那么表数据关系就是'一对多'
         部门是一 员工是多
        # 针对一对多：外键字段建在多的一方 
        # 【没有多对一】 统一称为【一对多】

1)创建表的时候要先创建'被关联的表'(不放外键字段的)
2)再创建'关联的表'(放外键字段的)
  2.1)先写普通字段
  2.2)再写外键字段
  2.3)添加级联更新、级联删除
    
# 创建从表 给id、dep_name、dep_desc字段 且约束id为主键自增
create table dep1(
        id int primary key auto_increment,
        dep_name varchar(32),
        dep_desc varchar(32)
);
# 创建主表 给id、name、age、dep_id 且约束id为主键自增，给dep_id关联为外键
create table emp1(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int, # 创建一个外键字段
    foreign key(dep_id) references dep1(id) #让dep_id关联dep1表中的id
    on update cascade on delete cascade # 级联更新 级联删除
);

👻多对多

# 针对书籍表和作者表判断表数据关系：

    1)先站在书籍表的角度
        问:一本书能否对应多个作者
        答:可以
    2)再站在作者表的角度
        问:一个作者能否对应多本书
        答:可以
    结论:两边都可以 表数据关系为：'多对多'
         # 针对多对多：外键字段不能建在任意一方,需要单独开设第三张表

1)先把两张表创建出来
2)再创建'第三张表'
  2.1)写上前两张表中要关联的外键字段
  2.2)添加级联更新、级联删除

# 创建书籍表
create table book(
    id int primary key auto_increment, 
    title varchar(32)
);

# 创建作者表
create table author(
    id int primary key auto_increment,
    name varchar(32)
);

# 创建第三张表
create table book2author(
    id int primary key auto_increment, 
    author_id int, # 创建一个外键字段
    foreign key(author_id) references author(id) #让author_id关联author表中的id
    on update cascade on delete cascade,# 级联更新 级联删除
    book_id int, # 创建一个外键字段
    foreign key(book_id) references book(id) #让book_id关联book表中的id
    on update cascade on delete cascade # 级联更新 级联删除
);

👻一对一

一对一与一对多的区别在于一对一被关联字段增加了约束唯一值

# 针对用户表和用户详情表判断表数据关系：

    1)先站在用户表的角度
        问:一个用户能否对应多个用户详情
        答:不可以
    2)再站在用户详情表的角度
        问:一个用户详情能否对应多个用户
        答:不可以
    结论:两边都不可以 表数据关系可能为：'一对一' 或 '没关系'
         # 针对一对一： 外键字段建在任何一张表都可以 建议建在查询频率较高的表中便于后续查询

1)创建表的时候要先创建'被关联的表'(不放外键字段的)
2)再创建'关联的表'(放外键字段的)
  2.1)先写普通字段
  2.2)再写外键字段并约束唯一值
  2.3)添加级联更新、级联删除
    
# 创建从表
create table userinfo(
    id int primary key auto_increment,
    phone bigint
);

# 创建主表
create table user(
    id int primary key auto_increment,
    name varchar(32),
    userinfo_id int unique, # 创建一个外键字段并约束唯一值
    foreign key(userinfo_id) references userinfo(id) #让userinfo_id关联userinfo表中的id
    on update cascade on delete cascade # 级联更新 级联删除
);

👻没关系

以上三种都不是的就是没关系

14.操作表的SQL语句补充

1.修改表名
	alter table 表名 rename 新表名;
2.新增字段
	alter table 表名 add 字段名 字段类型(数字) 约束条件;  # 末尾新增字段
	alter table 表名 add 字段名 字段类型(数字) 约束条件 after 某字段名; # 在某字段名后新增字段
	alter table 表名 add 字段名 字段类型(数字) 约束条件 first; # 在最前面新增字段
    ——————————————————————————————————————————————
    alter table t1 add age int;  # 末尾新增字段
    alter table t1 add age1 int after name; # 在name字段后新增字段
    alter table t1 add id1 int first; # 在最前面新增字段
3.修改字段
	alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件; # 改字段名和字段类型
 	alter table 表名 modify 字段名 新的字段类型(数字) 约束条件; # 只改字段类型
4.删除字段
	alter table 表名 drop 字段名;

15.表查询关键字

# 预备知识
'SQL语句的关键字编写顺序与执行顺序是不一致的'
如：select name from emp;
    先执行from确定表 再执行select确定字段
    
select 
    指定需要查询的字段信息
from 
    指定需要查询的表信息
'建议编写SQL语句针对select和from可以先写模板'
如：select * from 表名 其他操作；
# 等写完再修改*号位置

数据准备：

#【创建表】

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  gender enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);

#【插入记录】
#三个部门：教学，销售，运营

insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

1)where筛选

(1)模糊查询

like
    '%:匹配任意个数的任意字符'
        select * from emp where name like '%o%'; # 匹配名字带o的数据
        
        
    '_:匹配固定个数的字符'
        select * from emp where name like '____'; # 匹配名字是四个字符的数据

# 1.查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and id <= 6;
select *  from emp where id between 3 and 6; 

# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);  # 成员运算

# 3.查询id小于3大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;

# 4.查询员工姓名中包含o字母的员工姓名和薪资
select name,salary from emp where name like '%o%';

# 5.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name)=4;#查询名字为4字符的

# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);

# 7.查询岗位描述为空的员工名与岗位名
'注意：针对null不能用等号，只能用is！！'
select name,post from emp where post_comment is NULL;

2)group by分组

分组：按照指定条件将单个单个数据组成一个整体
分组的目的：为了更好的统计相关数据

# 1.将数据按照部门分组
select * from emp group by post;  # 有问题

"""
MySQL5.6不会报错 MySQL5.7  5.8会报错。

因为：
分组后select后面只能填写分组的依据,不能写其他字段,建议配置文件中加上严格模式
[mysqld]
    sql_mode='STRICT_TRANS_TABLES,only_full_group_by'
    
分组后默认最小单位就应该是组，而不是单个数据单个字段
"""

# 2.聚合函数
'专门用于分组后的数据统计'
max()  最大值     min()  最小值
sum()  求和       count()  计数
avg()  平均

# 3.什么时候需要使用分组
看题目中是否出现：'每个、最大、最小、平均'

# 4.group_concat(分组后不仅可以用来显示除分组外的字段,还可以拼接字符串)
eg: 统计每个部门的部门名称和部门下员工姓名
select post,group_concat(name) from emp group by post;

# 1.按照部门分组,并显示出所有部门
select post from emp group by post;

# 2.获取每个部门最高薪资
select post,max(salary) from emp group by post;
"""
+-----------------------------+-------------+
| post                        | max(salary) |
+-----------------------------+-------------+
| operation                   |    20000.00 |
| sale                        |     4000.33 |
| teacher                     |  1000000.31 |
| 浦东第一帅形象代言          |     7300.33 |
+-----------------------------+-------------+
"""
# 针对结果可以修改字段名称(起别名)
select post,max(salary)as '最高薪资' from emp group by post;
"""
+-----------------------------+--------------+
| post                        | 最高薪资     |
+-----------------------------+--------------+
| operation                   |     20000.00 |
| sale                        |      4000.33 |
| teacher                     |   1000000.31 |
| 浦东第一帅形象代言          |      7300.33 |
+-----------------------------+--------------+
"""
# 获取每个部门最低薪资
select post,min(salary) from emp group by post;
# 获取每个部门平均薪资
select post,avg(salary) from emp group by post;
# 获取每个部门薪资总和
select post,sum(salary) from emp group by post;
# 获取每个部门下的人数
select post,count(id) from emp group by post;

# 3.统计每个部门的部门名称和部门下员工姓名
'group_concat(分组后使用不仅可以用来显示除分组外的字段,还可以拼接字符串)'
select post,group_concat(name) from emp group by post;
"""
+-----------------------------+-----------------------+
| post                        | group_concat(name)    |
+-----------------------------+-----------------------+
| operation                   | 程咬铁,程咬铜..       |
| sale                        | 拉拉,乐乐..           |
| teacher                     | sank,jenny..          |
| 浦东第一帅形象代言          | jason                 |
+-----------------------------+-----------------------+
"""

# 统计每个部门的部门名称和部门下员工姓名、年龄
select post,group_concat(name,age) from emp group by post;
select post,group_concat(name,'|',age) from emp group by post;

3)having过滤

having与where的功能其实差不多都是用来做数据筛选
where用于分组前的筛选(首次筛选)
having用于分组后的筛选(二次筛选)

# 1.统计各部门年龄在30以上的员工平均工资 且保留平均工资大于10000的部门

"""步骤一：先筛选出所有年龄大于30岁的员工数据"""
select * from emp where age>30;
"""步骤二：再对筛选出来的数据按照部门分组统计出平均薪资"""
select post,avg(salary) from emp where age>30 group by post;
"""最终：针对统计出来的结果做二次筛选"""
select post,avg(salary) from emp
    where age>30
    group by post
    having avg(salary)>10000;
"""
+---------+---------------+
| post    | avg(salary)   |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
"""

4)distinct去重

去重的前提是数据必须一模一样。且是看组合后的结果

# 只查年龄字段,且去重
select distinct age from emp;
"""
+-----+
| age |
+-----+
|  18 |
|  78 |
|  81 |
|  73 |
|  38 |
+-----+
"""

# 只查年龄与部门字段，且去重
select distinct age,post from emp;
"""
+-----+-----------------------------+
| age | post                        |
+-----+-----------------------------+
|  18 | 浦东第一帅形象代言          |
|  78 | teacher                     |
|  73 | teacher                     |
|  28 | teacher                     |
|  38 | sale                        |
|  28 | sale                        |
|  28 | operation                   |
|  18 | operation                   |
+-----+-----------------------------+
"""

5)order by排序

"""
order by asc  升序 (asc不写默认为升序)
order by desc 降序
"""

# 单排序
# 1.薪资升序、降序排
select * from emp order by salary;  # 升序
select * from emp order by salary asc;  # 升序
select * from emp order by salary desc;  # 降序

# 多排序
# 2.按照年龄升序排，再按薪资降序排
select * from emp order by age,salary desc;

# 3.统计各部门年龄在10岁以上的员工平均薪资，且保留平均薪资大于1000的部门，再对平均薪资进行降序排序
"""步骤一：先筛选所有年龄大于10岁的员工"""
select * from emp where age>10;
"""步骤二：再对他们部门分组统计出平均薪资"""
select post,avg(salary) from emp where age>10 group by post;
"""步骤三：针对分组结果进行二次筛选平均薪资大于1000"""
select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000;
"""最终：针对统计出来的结果做平均薪资降序排序"""
select post,avg(salary) from emp 
    where age>10 
    group by post 
    having avg(salary)>1000
    order by avg(salary) desc;

6)limit分页

# 1.限制只展示3条数据
select * from emp limit 3;

# 2.从第4条开始往后展示3条数据
select * from emp limit 4,3;

# 3.查询工资最高的人的详细信息
# 按照薪资降序排 展示1条信息
select * from emp order by salary desc limit 1;

7)regexp正则表达式

SQL语句的模糊匹配如果用不习惯 也可以自己写正则批量查询
	select * from emp where name regexp '^j.*?(n|y)$';

16.多表查询

数据准备：

# 【创建表】：

create table dep(
  id int primary key auto_increment,
  name varchar(20) 
);

create table emp(
  id int primary key auto_increment,
  name varchar(20),
  sex enum('male','female') not null default 'male',
  age int,
  dep_id int
);

# 【插入数据】：
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

1)多表查询错误语法：笛卡尔积

select * from emp,dep;  会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
select * from emp,dep where emp.dep_id=dep.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据

2)多表查询正确语法

(1)方式一：连表操作

1.inner join    内连接
    #只拼接两边都有的字段数据
  select * from emp inner join dep on emp.dep_id=dep.id;
"""
+----+--------+--------+------+--------+-----+--------------+
| id | name   | sex    | age  | dep_id | id  | name         |
+----+--------+--------+------+--------+-----+--------------+
|  1 | jason  | male   |   18 |    200 | 200 | 技术         |
|  2 | dragon | female |   48 |    201 | 201 | 人力资源     |
|  3 | kevin  | male   |   18 |    201 | 201 | 人力资源     |
|  4 | nick   | male   |   28 |    202 | 202 | 销售         |
|  5 | owen   | male   |   18 |    203 | 203 | 运营         |
+----+--------+--------+------+--------+-----+--------------+
"""

2.left join	    左连接
    #以左表为基准 展示左表所有数据 右表没有对应则NULL填充
  select * from emp left join dep on emp.dep_id=dep.id;  
"""
+----+--------+--------+------+--------+------+--------------+
| id | name   | sex    | age  | dep_id | id   | name         |
+----+--------+--------+------+--------+------+--------------+
|  1 | jason  | male   |   18 |    200 |  200 | 技术         |
|  2 | dragon | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin  | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick   | male   |   28 |    202 |  202 | 销售         |
|  5 | owen   | male   |   18 |    203 |  203 | 运营         |
|  6 | jerry  | female |   18 |    204 | NULL | NULL         |
+----+--------+--------+------+--------+------+--------------+
"""
    
3.right join    右连接
    #以右表为基准 展示右表所有数据 左表没有对应则NULL填充
  select * from emp right join dep on emp.dep_id=dep.id;
"""
+------+--------+--------+------+--------+-----+--------------+
| id   | name   | sex    | age  | dep_id | id  | name         |
+------+--------+--------+------+--------+-----+--------------+
|    1 | jason  | male   |   18 |    200 | 200 | 技术         |
|    2 | dragon | female |   48 |    201 | 201 | 人力资源     |
|    3 | kevin  | male   |   18 |    201 | 201 | 人力资源     |
|    4 | nick   | male   |   28 |    202 | 202 | 销售         |
|    5 | owen   | male   |   18 |    203 | 203 | 运营         |
| NULL | NULL   | NULL   | NULL |   NULL | 205 | 财务         |
+------+--------+--------+------+--------+-----+--------------+
"""

4.union	        全连接
    #左右表全拼一起 没有的数据用NULL填充 一般不用
  select * from emp left join dep on emp.dep_id=dep.id
  union
  select * from emp right join dep on emp.dep_id=dep.id;
"""
+------+--------+--------+------+--------+------+--------------+
| id   | name   | sex    | age  | dep_id | id   | name         |
+------+--------+--------+------+--------+------+--------------+
|    1 | jason  | male   |   18 |    200 |  200 | 技术         |
|    2 | dragon | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin  | male   |   18 |    201 |  201 | 人力资源     |
|    4 | nick   | male   |   28 |    202 |  202 | 销售         |
|    5 | owen   | male   |   18 |    203 |  203 | 运营         |
|    6 | jerry  | female |   18 |    204 | NULL | NULL         |
| NULL | NULL   | NULL   | NULL |   NULL |  205 | 财务         |
+------+--------+--------+------+--------+------+--------------+
"""

(2)方式二：子查询

将一条SQL语句用括号括起来当成另一条SQL语句的查询条件

# 1.获取姓名是jason的部门名称

"""步骤一：根据jason获取部门编号"""
select dep_id from emp where name='jason';
"""步骤二：根据部门编号获取部门名称"""
select name from dep where id=200;

"""总结：合并起来"""
select name from dep where id=(select dep_id from emp where name='jason');
"""
+--------+
| name   |
+--------+
| 技术   |
+--------+
"""

2)知识点额外补充

1.as语法
    # 给字段起别名、起表名
    
2.comment语法
    # 给表、字段添加注释
  create table server(id int) comment '这个server意思是服务器表'
  create table t1(
      id int comment '用户编号',
      name varchar(16) comment '用户名'
    ) comment '用户表';
    """
    查看注释的地方
		show create table 
		use information_schema
    """
    
3.concat与concat_ws语法
    # concat用于分组前额字段拼接操作
    select concat(name,'|',sex) from emp;
    # concat_ws拼接多个字段且中间连接符一致
    select concat_ws('|',name,sex,age,dep_id) from emp;

4.exists语法
    sql1 exists sql2；
    sql2有结果时才会执行sql1 否则不执行sql1，返回空数据
    
5.表相关SQL补充
alter table 表名 rename 新表名;  # 修改表名
alter table 表名 add 字段名 字段类型(数字) 约束条件;  # 添加字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 已有字段;
alter table 表名 add 字段名 字段类型(数字) 约束条件 first; # 修改字段
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
alter table 表名 modify 字段名 新字段类型(数字) 约束条件;
alter table 表名 drop 字段名;  # 删除字段

17.Navicat可视化软件

Navicat可以充当很多数据库软件的户端，其中主要还是针对MySQL。无论这些第三方软件有多花哨，底层都是SQL语句。该软件可以用可视化界面直观看到库、表、内容，可以用鼠标来代替SQL语句提升效率。

1)下载与安装

Navicat官网下载地址：https://www.navicat.com.cn/
正版收费，可以百度破解或淘宝

2)基本使用

1.左边链接处右键可以'新建数据库',字符集选utf8mb4(比utf8功能多),排序规则不要写。
2.左边表右键可以'新建表',主键需要在键的位置按空格(或点击主键),最下面点自动递增。
3.添加完数据后'记得保存'
4.库右键>>逆向数据库到模型 可以只管看到库里的表之间有没有关系
  库右键>>转储SQL文件 可以把数据库转成SQL文件 另一台电脑运行该文件也可以得到一样的库
5.# 如果想建一对一的表，需要在SQL预览上自己添加unique唯一值

6.注释有三种
  --     单行注释
  #      单行注释
  /* */  多行注释

18.多表查询练习题

1.# 查询所有的课程的名称以及对应的任课老师姓名
"""步骤一：确定需要用几张表  (课程表、老师表)"""
"""步骤二：查看每张表的数据"""
"""最终：思考查询思路(由于需要两个字段分别来自两个表,所以是连表操作)"""
select teacher.tname,course.cname from course inner join teacher on course.teacher_id=teacher.tid;

2.# 查询平均成绩大于八十分的同学的姓名和平均成绩
"""步骤一：确定需要用几张表  (成绩表、学生表)"""
"""步骤二：查看每张表的数据"""
"""步骤三：求每个学生的平均成绩 按照学生id分组 求平均成绩大于80的数据"""
select student_id,avg(num)as avg_num from score group by student_id having avg(num)>80;
"""最终：显示出超过平均分80分的学生名字和平均分"""
select student.sname,t1.avg_num from student inner join(select student_id,avg(num)as avg_num from score group by student_id having avg(num)>80)as t1 on student.sid=t1.student_id;

3.# 查询没有报李平老师课的学生姓名
"""步骤一：确定需要用几张表  (学生表、成绩表、课程表、老师表)"""
"""步骤二：查看每张表的数据"""
"""步骤三：在老师表中获取李平老师的tid号 """
select tid from teacher where tname = '李平老师';
"""步骤四：然后去课程表中获取李平老师教授的课程cid"""
select cid from course where teacher_id=(select tid from teacher where tname = '李平老师');
"""步骤五：根据课程cid筛选出所有报了李平老师课程的学生sid"""
select distinct student_id from score where course_id in(select cid from course where teacher_id=(select tid from teacher where tname = '李平老师'));
"""最终：根据学生id去学生表中取反获得没有报李平老师课程的学生名"""
select sname from student where sid not in (select distinct student_id from score where course_id in(select cid from course where teacher_id=(select tid from teacher where tname = '李平老师')));

4.# 查询没有同时选修物理课程和体育课程的学生姓名(报了两门或都没报的不算)
"""步骤一：确定需要用几张表  (学生表、成绩表、课程表)"""
"""步骤二：查看每张表的数据"""
"""步骤三：在课程表中获取物理和体育课程cid"""
select cid from course where cname in ('物理','体育');
"""步骤四：根据课程id去成绩表中获取物理和体育课的所有信息"""
select * from score where course_id in (select cid from course where cname in ('物理','体育'));
"""步骤五：基于上张表按照学生id分组 统计每个学生报了几门课程 筛选出报了一门的学生id"""
select score.student_id from score where course_id in(select cid from course where cname in('物理','体育'))group by score.student_id having count(score.course_id)=1;
"""最终：根据学生id在学生表中 子查询获取学生姓名"""
select student.sname from student where sid in(select score.student_id from score where course_id in(select cid from course where cname in('物理','体育'))group by score.student_id having count(score.course_id)=1)

5.# 查询挂科超过两门(包括两门)的学生姓名和班级
"""步骤一：确定需要用几张表  (班级表 学生表 成绩表)"""
"""步骤二：查看每张表的数据"""
"""步骤三：筛选出成绩小于60分的数据"""
select * from score where num<60;
"""步骤四：基于上张表按照学生id分组 统计每个学生挂科的次数"""
select student_id,count(course_id) from score where num<60 group by student_id;
"""步骤五：筛选出挂科次数超过两门的学生id"""
select student_id from score where num<60 group by student_id having count(course_id)>=2;
"""步骤六：连接班级表与学生表 基于学生id查找学生姓名和班级"""
select student.sname,class.caption from student inner join class on student.class_id=class.cid where student.sid in (select student_id from score where num<60 group by student_id having count(course_id)>=2);

19.python操作MySQL

pymysql模块
   # pip3 install pymysql

1)基本代码

import pymysql

# 1.连接MySQL服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    db='db1',
    charset='utf8mb4',  # 字符编码
    autocommit=True  # 执行增、删、改操作自动执行conn.commit(必须有,因为mysql中有二次确认机制，设置后会直接修改)
)
# 2.产生游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 把查询出来的数据组成字典，不写默认是元组[{},{}] [(),()]
# 3.编写sql语句
sql = 'select * from teacher;'
# 4.发送给服务端(执行SQL语句)
affect_rows = cursor.execute(sql)  # execute也有返回值,接收的是SQL语句影响的行数
print(affect_rows)
# 5.获取SQL语句执行之后的结果
res = cursor.fetchall()  # 获取结果集中所有数据
print(res)

2)获取命令的执行结果

# 1.获取数据
  cursor.fetchone()  # 获取结果集中第一条数据
  cursor.fetchall()  # 获取结果集中所有数据
  cursor.fetchmany(2)  # 获取结果集中指定2条数据
"""类似于文件光标的概念"""
————————————————————————————————————————————

  cursor.scroll(1,mode='relative') # 基于当前光标位置往后移1位
  cursor.scroll(0,mode='absolute') # 基于数据集开头位置往后移0位

#eg:
    res = cursor.fetchone()  # 获取结果集中第一条数据
    print(res)
    cursor.scroll(0,mode='relative')  # 基于当前光标位置往后移0位
    res1=cursor.fetchone()  # 获取结果集中第一条数据
    print(res1)
    """
    结果：
      {'tid': 1, 'tname': '张磊老师'}
      {'tid': 2, 'tname': '李平老师'}
    """

# 2.增删改查
  autocommit=True  # 针对增删改自动确认
  conn.commit()    # 针对增删改需要二次确认

20.SQL注入问题

1)错误：用户登录SQL语句

# 创建一个表里面放上id、用户名、密码。

import pymysql

# 连接数据库
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db1',
    charset='utf8',
    autocommit=True
)
# 产生游标对象 并把结果组成字典显示
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 获取用户名密码
username = input('username:').strip()
password = input('password:').strip()
# 构造查询SQL语句
sql = f"select * from userinfo where name='%s' and pwd='%s'" %(username,password)
# 执行SQL语句
cursor.execute(sql)
# 获取SQL语句执行后的结果
res = cursor.fetchall()
if res:
    print('登陆成功')
    print(res)
else:
    print('用户名或密码错误')

2)错误结果

# 1.以上代码在登陆时有两个问题：
1)只需要用户名即可登录成功
用户名输入 jason' -- jlksjkfljasf 密码不输就可以登录成功
2)不需要用户名和密码也可以登录成功
用户名输入 xxx' or 1=1 -- sdajfkl 密码不输也可以登录成功

# 2.以上问题的原因：(什么是SQL注入问题)
利用特殊负号的组合产生了特殊含义从而避开了正常的业务逻辑，该问题就叫'SQL注入问题'

# 3.解决措施：
针对SQL注入问题,核心在于手动拼接了关键数据，所以今后涉及拼接时交给execute处理即可。
sql = " select * from userinfo where name=%s and pwd=%s "
cursor.execute(sql, (username, password))

# 4.补充知识：executemany方法
'一次性插入多条数据'
sql="insert into userinfo(name,pwd) values(%s,%s)"
executemany(sql,[('oscar','123'),('lili','123'),('jack','123')...])

3)正确：用户登录SQL语句

import pymysql

# 连接数据库
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db1',
    charset='utf8',
    autocommit=True
)
# 产生游标对象 并把结果组成字典显示
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 获取用户名密码
username = input('username:').strip()
password = input('password:').strip()
# 构造查询SQL语句
sql = "select * from userinfo where name=%s and pwd=%s"
# 执行SQL语句
cursor.execute(sql, (username, password))
# 获取SQL语句执行后的结果
res = cursor.fetchall()
if res:
    print('登陆成功')
    print(res)
else:
    print('用户名或密码错误')

21.视图

1)什么是视图

视图就是通过查询得到一张虚拟表保存下来,下次可以直接使用

2)视图命令

# 创建视图
create view 视图名 as SQL语句;
————————————————————————————————
create view teacher2course as select * from teacher inner join course on teacher.tid=course.teacher_id;

# 删除视图
drop view teacher2course;

3)视图注意事项

①在硬盘中视图只有表结构文件,没有表数据文件
②视图多了会浪费内存空间,且容易分不清哪个是真正的表！尽量少用
③当连表的表修改里面数据时,视图表中的数据也会跟着修改
修改视图表里的数据时，连表的表里的数据也会跟着修改
④视图的表只能用来查询,不能做其他增删改操作

22.触发器

达到某个条件之后自动触发执行
在MySQL中更详细的说明：针对表数据增、删、改操作能够自动触发
主要有六种情况：增前、增后、删前、删后、改前、改后

1)语法结构

# 创建触发器
create trigger 触发器名字 before/after insert/update/delete 
on 操作的表名 for each row begin 自动触发的SQL语句 end

2)触发器的命名规律

# 见名知意
tri_before_insert_t1
tri_after_delete_t2
tri_after_update_t2

3)临时修改SQL语句的结束符

(1)为什么要临时修改SQL语句的结束符？

因为有些操作中需要使用分号,如果不把结束符修改则没办法去执行该操作

(2)如何临时修改SQL语句的结束符

# 最开头临时修改SQL语句结束符为$$
delimiter $$
...
# 用完后不要忘记再改回去
delimiter ;

"""
仅是临时修改：只在当前窗口有效
"""

(3)触发器实际应用

#创建两个表 一个存储所有命令 一个存储执行错误的命令
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') #命令是否执行成功
);

create table errlog (
    id int primary key auto_increment,
    err_cmd char(64),
    err_time datetime
);

#创建一个触发器来判断当新插入cmd的数据为'no'时,则在errlog表中插入该输入
delimiter $$  # 将结束符临时换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if new.success = 'no' then  # 新记录都会被MySQL封装成new对象
        insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);
    end if;
end $$
delimiter ;  # 结束后把结束符改回;


#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
insert into cmd (
    user,
    priv,
    cmd,
    sub_time,
    success
)
values
    ('kevin','0755','ls -l /etc',NOW(),'yes'),
    ('kevin','0755','cat /etc/passwd',NOW(),'no'),
    ('kevin','0755','useradd xxx',NOW(),'no'),
    ('kevin','0755','ps aux',NOW(),'yes');

# 查询errlog表记录
select * from errlog;
# 查看所有的触发器
show triggers;
# 删除触发器
drop trigger tri_after_insert_cmd;

23.事物

1)什么是事物

开启一个事物可以包含多条sql语句，这些sql语句要么同时成功,要么同时失败。这也是事物的原子性特点。

2)事物的作用

保证了数据操作的数据安全性。
如A银行给B银行转账,当A转账成功并扣款后,B断网了没收到转账信息,那这笔钱就不见了。所以为了解决该问题,当A扣钱时B必须加钱,如果没加则回退。

3)事务的四大特性(ACID)

A：原子性
事务中的各项操作是不可分割的整体 要么同时成功要么同时失败
C：一致性
一个事物执行完,另一个事物必须保持一致,不能你成功我失败
I ：隔离性
多个事物之间彼此互不干扰
D：持久性
一个事物一但提交，对数据库的改变就是永久的，不能有其他修改操作

4)事物案例

创建表：

create table user(
id int primary key auto_increment,
name char(32),
balance int);

插入记录：

insert into user(name,balance) values('jason',1000),('kevin',1000),('torry',1000);

事物操作：

# 开启事物操作(在修改操作前)
start transaction;
# 修改操作
update user set balance=900 where name='jason';  # 买家支付100元
update user set balance=1010 where name='kevin';  # 中介收走10元
update user set balance=1090 where name='torry';  # 卖家收走90元

# rollback;  # 可回退到修改操作前的状态
# 如果用了rollback后需要重新修改操作,则需要再开启事物操作

commit; # 只要没执行commit提交操作,数据都没有真正刷新到硬盘中

5)事物相关关键字

start transaction   # 开启事物操作
rollback            # 可回退到修改操作前的状态
commit              # 提交确认事物
savepoint           # 节点 类似存档,结合回退用,回退到该节点

6)事物的隔离级别 ※

在SQL标准中定义了四种隔离级别，每种级别都规定了一个事务中所做的修改。
InnoDB支持所有隔离级别

set transaction isolation level 级别

1.read uncommitted(未提交读)
    事务中的修改即便没提交,对其他事物也都是可见的。事物可以读取未提交的数据,这一现象也叫'脏读'。
    【'脏读'】简单理解为开启事物修改数据后没有提交确认时,又有另一个事物来获取数据，获取到的就是内存里的数据而不是本应该在硬盘里的真实数据。
    
2.read committed(提交读 或 不可重复读)  # 大多数据库默认隔离级别
    一个事物从开始一直到提交之前所作的任何修改对其他事务都是不可见的，也叫'不可重复读'
    简单理解就是开启事物修改数据后没有提交确认时，又有另一个事物来获取数据，获取到的是修改前硬盘里的数据。

3.repeatable read(可重复度) # MySQL默认隔离级别
    能够解决'脏读'问题，但无法解决'幻读'问题
    【'幻读'】简单理解为当某个事物在读取某个范围内的记录时,另一个事物又在该范围内插入了新记录，当之前的事物再次读取该范围的记录会产生'幻行'。
    【'幻行'】简单理解为假如事物1对一个表中的5条数据做查询3条记录操作,同时事物2过来把其中的1条数据删掉了，事物1就只能查询2条。
    InnoDB和XtraDB通过'多版本并发控制'(MVCC)及'间隙锁策略'可以解决幻读的问题。

4.serializable(可串行读)
    强制多个事物串行执行(排队执行)。由于效率太低所以很少用。

7)了解知识：MVCC多版本并发控制

MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新  serializable:所有的行都加锁)

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
    一个列保存了行的创建时间
    一个列保存了行的过期时间(或删除时间)  # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

例如：
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
    username    create_version    delete_version
    jason             1					
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
    username      create_version     delete_version
    jason                1                2
    jason01              2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
    username    create_version    delete_version
    jason01          2                   3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
   1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
   2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""

24.存储过程

存储过程：类似于python中的自定义函数，可以自己写一些方法后续可以反复调用

1)类似无参函数

# 无参函数
delimiter $$
create procedure p1()
begin
	select * from cmd;
end $$
delimiter ;


# 调用
call p1(); # 今后调用就相当于执行了查看cmd表的命令

2)类似于有参函数

# 有参函数
delimiter $$
create procedure p2(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示既可以传入也可以被返回出去
)
begin
    select * from cmd where id > m and id < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;

# 针对res需要先提前定义
set @num=10;  定义一个变量
select @num;  查看变量对应的值 #10
call p1(1,5,@num);  调用
select @num;  查看变量变成了什么 #0

3)存储过程命令

# 查看存储过程具体信息
	show create procedure pro1;
# 查看所有存储过程
	show procedure status;
# 删除存储过程
	drop procedure pro1;

4)了解知识：存储过程其他

# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！

# 1、直接在mysql中调用
set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10);  # 报错
call p1(2,4,@res);  

# 查看结果
select @res;  # 执行成功，@res变量值发生了变化

# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')

25.函数

函数：类似于python中的内置函数，不用自己写只能在sql语句中使用

help 函数名可以查看帮助信息

# 1.移除指定字符
Trim   # 移除所有空格
Ltrim  # 移除左边空格
Ttrim  # 移除右边空格
——————————————————————
select trim('  abv  ');  # 去掉空格
"""
+------------------+
| trim('    abc ') |
+------------------+
| abc              |
+------------------+
"""

# 2.大小写转换
Lower  # 转小写
Upper  # 转大写
——————————————————————
select upper('abc');  # 转大写
"""
+--------------+
| upper('abc') |
+--------------+
| ABC          |
+--------------+
"""

# 3.获取左右起始指定个数字符
Left    # 左
Right   # 右
——————————————————————
select left('abcde',2);  # 切割左边两个字符
"""
+-----------------+
| left('abcde',2) |
+-----------------+
| ab              |
+-----------------+
"""

# 4.返回读音相似的值(对英文有效)
Soundex  # 返回一个同音字符串
——————————————————————
select * from user where soundex(name)=soundex('jason');
"""
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | jason |     800 |
|  4 | joson |    1000 |
|  5 | jsson |    2000 |
+----+-------+---------+
"""
eg：
  客户表中有一个顾客登记的用户名为J.Lee
  如果输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的数据
select * from t1 where soundex(name)=soundex('J.Lee');

# 5.日期格式：date_format
"""在MySQL中表示时间的格式尽量采用：2011-11-11 格式"""
CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');
    
# 统计出不同年月下有多少条数据
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');#把时间字段截取成年月格式并按照年月分组
"""
+-------------------------------+-----------+
| date_format(sub_time,'%Y-%m') | count(id) |
+-------------------------------+-----------+
| 2015-03                       |         2 |
| 2016-07                       |         4 |
| 2017-03                       |         3 |
+-------------------------------+-----------+
"""

日期额外了解：
1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数 
	adddate	增加一个日期 
	addtime	增加一个时间
	datediff计算两个日期差值

26.流程控制

# 分支结构
declare i int default 0;
if i = 1 then
	select 1;
elseif i = 2 then
	select 2;
else
	select 7;
end if;
——————————————————————————
# 循环结构
declare num int;
set num = 0;
while num < 10 do
	select num;
	set num = num + 1;
end while;

27.索引

1)什么是索引

索引就像一本书的目录,可以更快找到自己想要的内容。让获取的数据更有目的性,从而提高数据库检索数据的性能。其实就是为了加快对数据的查找

索引在MySQL中也叫做键，是存储引擎用于快速找到记录的一种数据结构
primary key 主键：非空且唯一
unique key 唯一键：唯一
index key 索引键：无约束条件
①.以上三种键在数据查询时使用都可以加快数据查询速度
②.primary key和unique key除了可以加快查询,还自带约束条件。
而index key只能加快数据查询,没其他约束条件(一般额外加的都是索引键)
③.外键不属于索引键的范围,是用来建立关系的。与加快查询无关

2)索引加快查询的本质

#假如有以下表字段 里面有很多数据：
id int primary key auto_increment, # 主键
name varchar(32) unique, # 唯一键
province varchar(32)
age int
phone bigint

# 不用索引查找 相当于一页一页的去找 很慢
select name from userinfo where phone=18818888888;  # 一页页的翻
# 用索引查找 相当于按照目录去找 很快
select name from userinfo where id=99999;  # 按照目录确定页数找

索引可以加快数据查询 但是会'降低增删的速度'
通常频繁使用某些字段查询数据 '为了提升速度可以给该字段建立索引'

2)索引相关概念

1.聚集索引(primary key)
	指的是表的主键,innodb引擎规定一张表必须要有主键
	特点：叶子结点放的一条条完整的记录
    
2.辅助索引(unique,index)
	查询数据的时候不可能都是用id作为筛选条件,也可能会用name，pwd等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引
	特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(辅助索引中并不存放任何数据,只有要查找的主键值,根据主键值去主目录中才可以拿到真正的数据)
	数据查找 如果一开始使用的是辅助索引 那么还需要使用聚焦索引才可以获取到真实数据
    
3.覆盖索引
	只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
	select name from user where name='jason';
    
4.非覆盖索引
	虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
	select age from user where name='jason';

3)索引数据结构

索引底层其实是树结构>>>:树是计算机底层的数据结构
 
树有很多中类型
	二叉树、b树、b+树、B*树......

二叉树
	二叉树里面还可以细分成很多领域 我们简单的了解即可 
  	二叉意味着每个节点最大只能分两个子节点
B树
	所有的节点都可以存放完整的数据
B+\*树
	只有叶子节点才会存放真正的数据 其他节点只存放索引数据
 	B+叶子节点增加了指向其他叶子节点的指针
  	B*叶子节点和枝节点都有指向其他节点的指针

辅助索引在查询数据的时候最会还是需要借助于聚集索引
	辅助索引叶子节点存放的是数据的主键值

有时候就算采用索引字段查询数据 也可能不会走索引!!!
	最好能记三个左右的特殊情况

4)慢查询优化

explain select * from 表名 where 条件;

常见的索引扫描类型:
1）index
2）range(最少要到达的级别)
3）ref
4）eq_ref
5）const
6）system
7）null

从上到下，性能从最差到最好，我们认为至少要达到range级别

28.额外了解知识点

1)测试索引

准备

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢


"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉

2)联合索引

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快

总结:上面这些操作，你感兴趣可以敲一敲，不感兴趣你就可以不用敲了，权当看个乐呵。理论掌握了就行了

慢查询日志

设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化！

3)全文检索

MySQL的全文检索功能MYISAM存储引擎支持而InnoDB存储引擎不支持
一般在创建表的时候启用全文检索功能
create table t1(
	id int primary key auto_increment,
  content text
	fulltext(content)
)engine=MyISAM;

# match括号内的值必须是fulltext括号中定义的(单个或者多个)
select content from t1 where match(content) against('jason')
'''上述语句可以用like实现但是查询出来的结果顺序不同 全文检索会以文本匹配的良好程度排序数据再返回效果更佳'''

# 查询扩展
select note_text from productnotes where Math(note_text) Against('jason' with query expansion);
"""
返回除jason外以及其他jason所在行相关文本内容行数据
eg:
	jason is handsome and cool,every one want to be cool,tony want to be more handsome;
	二三句虽然没有jason关键字 但是含有jason所在行的cool和handsome
"""

# 布尔文本搜索
即使没有定义fulltext也可以使用，但是这种方式非常缓慢性能低下
select note_text from productnotes where Match(note_text) Against('jason' in boolean mode);

# 注意事项
1.三个及三个以下字符的词视为短词，全文检索直接忽略且从索引中排除
2.MySQL自身自带一个非用词列表，表内词默认均被忽略(可以修改该列表)
3.出现频率高于50%的词自动作为非用词忽略，该规则不适用于布尔搜索
4.针对待搜索的文本内容不能少于三行，否则检索不返回任何结果
5.单引号默认忽略

4)插入数据

数据库经常被多个用户访问，insert操作可能会很耗时(特别是有很多索引需要更新的时候)而且还可能降低等待处理的select语句性能
如果数据检索是最重要的(一般都是)，则可以通过在insert与into之间添加关键字low_priority指示MySQL降低insert语句优先级
	insert low_priority  into 
  
insert还可以将一条select语句的结果插入表中即数据导入:insert select
eg:想从custnew表中合并数据到customers表中
  insert into customers(contact,email) select contact,email from custnew;

5)更新数据

如果使用update语句更新多列值，并且在更新这些列中的一列或者多列出现一个错误会导致整个update操作被取消，如果想发生错误也能继续执行没有错误的更新操作可以采用
	update ignore custmoers ...
  """
  update ignore  set name='jason1',id='a' where id=1;
  	name字段正常修改
  update set name='jason2',id='h' where id=1;
  	全部更新失败
  """

6)删除数据

delete语句从表中删除数据，甚至可以是所有数据但是不会删除表本身
并且如果想从表中删除所有的行不要使用delete可以使用truncate速度更快并且会重置主键值(实际是删除原来的表并重新创建一个表而不是逐行删除表中的数据)

7)主键

查看当前表主键自增到的值(表当前主键值减一)
	select last_insert_id();

8)外键

MySQL存储引擎可以混用，但是外键不能跨引擎即使用一个引擎的表不能引用具有使用不同引擎表的外键

9)重命名表

rename关键字可以修改一个或者多个表名
	rename table customer1 to customer2;
  rename table back_cust to b_cust,
  						 back_cust1 to b_cust1,
   						 back_cust2 to b_cust2;

10)事务

MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT

事务处理中有几个关键词汇会反复出现
  事务(transaction)
  回退(rollback)
  提交(commit)
  保留点(savepoint)
		为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
    创建占位符可以使用savepoint
    	savepoint sp01;
    回退到占位符地址
    	rollback to sp01;
    # 保留点在执行rollback或者commit之后自动释放

11)安全管理

1.创建用户
	create user 用户名 identified by '密码';
 	"""修改密码"""
  	set password for 用户名 = Password('新密码');
    set password = Password('新密码');  # 针对当前登录用户
2.重命名
	rename user 新用户名 to 旧用户名; 
3.删除用户
	drop user 用户名;
4.查看用户访问权限
	show grants for 用户名;
5.授予访问权限
	grant select on db1.* to 用户名;
  # 授予用户对db1数据库下所有表使用select权限
6.撤销权限
	revoke select on db1.* from 用户名;
"""
整个服务器
	grant all/revoke all
整个数据库
	on db.*
特定的表
	on db.t1
"""

12)隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
	set transaction isolation level 级别

1.read uncommitted(未提交读)
	事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
	大多数数据库系统默认的隔离级别
  一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读)		# MySQL默认隔离级别
	能够解决"脏读"问题，但是无法解决"幻读"
  所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
	强制事务串行执行，很少使用该级别

13)锁

读锁(共享锁)
	多个用户同一时刻可以同时读取同一个资源互不干扰
写锁(排他锁)
	一个写锁会阻塞其他的写锁和读锁
死锁
	1.多个事务试图以不同的顺序锁定资源时就可能会产生死锁
  2.多个事务同时锁定同一个资源时也会产生死锁
	# Innodb通过将持有最少行级排他锁的事务回滚

14)事务日志

事务日志可以帮助提高事务的效率 
	存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
  事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要次哦按的多个地方移动磁头所以采用事务日志的方式相对来说要快的多
  事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘

15)MVCC多版本控制

MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新  serializable:所有的行都加锁)

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
	一个列保存了行的创建时间
  一个列保存了行的过期时间(或删除时间)  # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

例如
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
    username		create_version		delete_version
    jason						1					
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
    username		create_version		delete_version
    jason						1									2
    jason01					2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
		username		create_version		delete_version
    jason01					2									 3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
   1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
   2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""

16)转换表的引擎

主要有三种方式，并各有优缺点!
# 1.alter table
	alter table t1 engine=InnoDB;
  """
  	适用于任何存储引擎 但是需要执行很长时间 MySQL会按行将数据从原表赋值到一张新的表中，在复制期间可能会消耗系统所有的IO能力，同时原表会加读锁
  """
# 2.导入导出
	"""
	使用mysqldump工具将数据导出到文件，然后修改文件中相应的SQL语句
		1.引擎选项
		2.表名
	""" 	
# 3.insert ... select
	"""
	综合了第一种方案的高效和第二种方案的安全
		1.先创建一张新的表
		2.利用insert ... select语法导数据
	数据量不大这样做非常合适 数据量大可以考虑分批处理 针对每一段数据执行事务提交操作避免产生过多的undo
	"""
  ps:上述操作可以使用pt-online-schema-change(基于facebook的在线schema变更技术)工具，简单方便的执行上述过程

posted @ 2022-11-22 14:57 oreox 阅读(240) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· django框架

· Flask框架

· MySQL 数据库

· MySQL数据库

阅读排行：
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理了，记录一下
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通

oreox

MySQL数据库

MySQL数据库

1.数据库前戏

1)存取数据的演变史

2)数据库软件应用史

3)数据库的本质

4)数据库的分类(重要)

(1)关系型数据库

(2)非关系型数据库

2.MySQL数据库简介

1)MySQL版本问题

2)下载与安装

3)主要目录介绍

3.MySQL基本使用

4.系统服务制作

5.密码相关操作

6.SQL与NoSQL数据库语句

7.数据库重要概念

8.基本SQL语句

1)针对库的基本SQL语句

2)针对表的基本SQL语句

3)针对记录的基本SQL语句

9.字符编码与配置文件

10.数据库存储引擎(重要)

1)需了解的4个存储常见引擎

2)不同存储引擎之间底层文件的区别

11.创建表的完整语法

12.MySQL字段基本数据类型

1)整型

(1)验证整型默认是否携带正负号

(2)移除负号：解决正负号问题

(3)严格模式：解决自动填写边界值问题

2)浮点型

(1)验证精确度问题

3)字符型

(1)验证char与varchar长度问题

(2)char与varchar哪个好

4)数字的含义

5)枚举与集合

(1)enum 枚举

(2)set 集合

6)日期

13.MySQL字段约束条件

1)无负号(符号)

2)零填充

3)非空

4)默认值

5)唯一值

6)主键

(1)单从约束层面上看 主键相当于非空 且 唯一（not null + unique

(2)InnoDB存储引擎规定一张表必须有且只有一个主键

(3)每个表都应该有一个id字段,且应该为主键

7)自增

(1)自增的特性

(2)重置表中的主键值：格式化表

8)外键

(1)外键前戏

(2)什么是外键字段

(3)建立外键字段

(4)表与表之间数据的四种关系判断

👻一对多

👻多对多

👻一对一

👻没关系​

14.操作表的SQL语句补充

15.表查询关键字

1)where筛选

(1)模糊查询

2)group by分组

3)having过滤

4)distinct去重

5)order by排序

6)limit分页

7)regexp正则表达式

16.多表查询

1)多表查询错误语法：笛卡尔积

2)多表查询正确语法

(1)方式一：连表操作

(2)方式二：子查询

(1)单从约束层面上看主键相当于非空且唯一（not null + unique

👻没关系