MySQL数据库

目录

• MySQL数据库
• 1 数据库的了解
  • 1.1 存储数据的演变过程
  • 1.2 数据库的本质
• 2 MySQL数据库简介
  • 2.1 重要概念介绍
  • 2.2 MySQL的安装
  • 2.3 MySQL服务端与客户端位置
    • 注意
  • 2.4 MySQL服务端与客户端的启动
  • 2.5 sql语句初识
  • 2.6 环境变量配置及系统服务制作
    • 2.6.1 环境变量配置
    • 2.6.2 系统服务制作
  • 2.7 设置密码
  • 2.8 破解密码
  • 2.9 统一编码
• 3 基本sql语句
  • 3.1 针对库的增删改查(文件夹)
  • 3.2 针对表的增删改查(文件)
  • 3.3 针对数据的增删改查(一行行数据)
• 4 存储引擎
  • 4.1 存储引擎概念
  • 4.2 MySQL主要存储引擎
  • 4.3 补充: 创建表的完整语法
  • 4.4 补充:表头设置中的宽度
• 5 基本数据类型
  • 5.1 整型
    • 5.1.1 分类
    • 5.1.2 作用
    • 5.1.3 补充: 严格模式
  • 5.2 浮点型
    • 5.2.1 分类
    • 5.2.2 作用
  • 5.3 字符类型
    • 5.3.1 分类
    • 5.3.2 char与varchar对比
  • 5.4 时间类型
    • 5.4.1 分类
  • 5.5 枚举与集合类型
    • 5.5.1 分类
    • 5.5.2 具体使用
• 6 约束条件
  • 6.1 default默认值
  • 6.2 unique唯一
  • 6.3 primary key主键
  • 6.4 auto_increment自增
    • 6.4.1 注意点
    • 6.4.2 id总结
• 7 表与表之间建立关系
  • 7.1 外键
  • 7.2 表关系
  • 7.3 一对多关系
  • 7.4 多对多
  • 7.5 一对一
  • 7.6 总结
• 8 修改表(了解)
• 9 复制表(了解)
• 10 数据筛选
  • 10.1 重要关键字的执行顺序
  • 10.2 where筛选条件
    • 简略版
    • 详细版
  • 10.3 group by分组
    • 10.3.1 简略版
    • 10.3.2 详细版
    • 10.3.3 分组注意事项
    • 10.3.4 having分组之后的筛选条件
  • 10.4 distinct去重
  • 10.5 order by排序
  • 10.6 limit限制展示条数
  • 10.7 正则
• 11 多表查询
  • 11.1 表查询
  • 11.2 子查询
  • 11.3 总结
  • 11.4 补充: 关键字exists(了解)

MySQL数据库

1 数据库的了解

1.1 存储数据的演变过程

• 随意的存到一个文件中、数据格式也是千差万别的,格式完全取决于我们自己

  """
  # 小李
  jason|123|NB
  # 小王
  egon-123-DBJ
  # 小红
  tank~123~hecha
  """
  

• 软件开发目录规范

  限制了存储数据的具体位置

  """
  bin
  conf
  core
  db
  lib
  readme
  """
  

• 不同计算机数据的互通

  """
  将数据的保存部分全部统一起来,全放入服务端的数据库
  任何数据的操作都通过服务端的数据库进行操作
  """
  

1.2 数据库的本质

"""
数据库本质其实就是一款基于网络通信的应用程序
理论上每个人都可以自主开发一款数据库软件 因为它仅仅就是一款基于网络通信的应用程序

这也就意味着数据库软件其实有很多,大致可以分为两类

	关系型数据库
		MySQL、oracle、db2、access、sql server
	非关系型数据库
		redis、mongodb、memcache
"""
# 关系型
	1 数据之间彼此有关系或者约束
    	关联或限制
    2 存储数据的表现形式通常是以表格存储	
    	name     password		hobby
        jason	  123			学习
        egon	  123			女教练
        tank	  123			吃生蚝
        每个字段还会有存储类型的限制
        比如姓名只能存字符串...
# 非关系型
	存储数据通常都是以k,v键值对的形式

2 MySQL数据库简介

"""
任何基于网络通信的应用程序底层用的都是socket

-服务端
	-基于socket通信
	-收发消息
	-SQL语句

-客户端
	-基于socket通信
	-收发消息
	-SQL语句
"""
# MySQL不单单支持MySQL自己的客户端app还支持其他编程语言来充当客户端操作
# 	如何解决不同编程语言沟通mysql的障碍？
    	# 1 让mysql服务端兼容所有的语言(一个人精通多国语言)
        # 2 不同编程语言采用统一的mysql语句(SQL语句)

2.1 重要概念介绍

"""
库 			=====			 文件夹
表		    =====			 文件
记录         =====			文件内一行行的数据

    	name     password		hobby
        jason	  123			学习
        egon	  123			女教练
        tank	  123			吃生蚝
表头				表格的第一行字段
字段				name、password、hobby
"""

2.2 MySQL的安装

"""
在IT界 一般都不会轻易的使用最新版本的软件，因为新版本可能会出现各种问题
(你原本项目跑的好好的 非要画蛇添足更新版本 然后项目奔溃)
小段子:
	更新完没事 那么你还是一个普通员工
	更新完出事 那么你就是一名"烈士"
"""
# MySQL有很多版本(5.6、5.7、8.0) 目前企业里面用的比较多的还是5.6左右

参考网站:https://www.mysql.com/

进入官网后依次点击

-->downloads

-->MySQL Community (GPL) Downloads

-->MySQL Community Server

-->Looking for previous GA versions?

然后在Select Version下选择5.6.48

然后在下方选择对应的兼容性,点击download进行下载

• DOWNLOADS
• MySQL Community (GPL) Downloads »
• MySQL Community Server
• Looking for previous GA versions?
• https://dev.mysql.com/downloads/file/?id=494634

2.3 MySQL服务端与客户端位置

"""
在mysql-5.6.48-winx64\bin下
服务端
mysqld.exe

客户端
mysql.exe

使用cmd命令行窗口打开
"""

注意

"""
在前期配置MySQL的时候 cmd终端尽量以管理员的身份运行

windows+r 输入cmd  进入的是普通用户终端 有一些命令是无法执行的
搜索cmd右键 以管理员身份运行
"""

2.4 MySQL服务端与客户端的启动

• 先切换到mysqld所在的bin目录下，然后输入mysqld即可

• 保留原来的cmd窗口重新打开一个

  """
  常见软件的默认端口号
  	MySQL  3306
  	redis  6379
  	mongodb 27017
  	django  8000
  	flask   5000
  	...
  	
  
  客户端连接服务端完整命令
  mysql -h 127.0.0.1 -P 3306 -uroot -p
  
  root 为初始用户名,-p后输入密码
  MySQL客户端第一次以管理员身份进入是没有密码的 直接回车即可
  """
  

2.5 sql语句初识

"""
1 MySQL中的sql语句是以分号(;)作为结束的标志

2 基本命令
	show databases;  查看所有的库名

3 连接服务端的命令可以简写
	mysql -uroot -p

4 当你输入的命令不对 又不想让服务端执行并返回报错信息 可以用\c取消
	错误命令  \c

5 客户端退出  退出命令加不加分号都可以执行
	quit
	exit

6 当你在连接服务端的时候 发现只输入mysql也能连接
	此时不是管理员身份 而只是一个游客模式
	通过show databases;便能发现可见的库名不全
"""

2.6 环境变量配置及系统服务制作

"""
1 如何查看当前具体进程
	tasklist
	tasklist |findstr mysqld

2 如何杀死具体进程(只有在管理员cmd窗口下才能成功)
	taskkill /F /PID PID号
"""

2.6.1 环境变量配置

"""
每次启动mysqld需要先切到对应的文件路径下才能操作太过繁琐
可以将mysqld所在的文件路径添加到系统环境变量中
"""

2.6.2 系统服务制作

添加环境变量后操作还是相对繁琐, 因为需要同时打开两个cmd窗口

可以将mysql服务端制作成系统服务(开机自启动)

"""
查看当前计算机的运行进程数
	services.msc

将mysql制作成系统服务:
在命令行内输入
	mysqld --install
移除mysql系统服务
	mysqld --remove
"""

2.7 设置密码

"""
mysqladmin -uroot -p原密码 password 新密码
该命令直接在终端输入即可 无需进入客户端

mysqladmin -uroot -p123 password 123456
"""

2.8 破解密码

"""
可以将mysql获取用户名和密码校验的功能看成是一个装饰器
装饰在了客户端请求访问的功能上

我们如果将该装饰器移除 那么mysql服务端就不会校验用户名和密码了
"""
# 破解方法
# 1 先关闭当前mysql服务端
 命令行的方式启动(让mysql跳过用户名密码验证功能)
 mysqld --skip-grant-tables  
# 2 直接以无密码的方式连接
	mysql -uroot -p   直接回车
# 3 修改当前用户的密码
	update mysql.user set password=password(123456) where 		user='root' and host='localhost';
"""
真正存储用户表的密码字段 存储的肯定是密文 
只有用户自己知道明文是什么 其他人都不知道 这样更加的安全
密码比对也只能比对密文
"""
# 4 立刻将修改数据刷到硬盘
	flush privileges;
# 5 关闭当前服务端 然后以正常校验授权表的形式启动

2.9 统一编码

• mysql默认的配置文件

  """
  mysql-5.6.48-winx64文件夹下有文件: my-default.ini 
  
  ini结尾的一般都是配置文件
  
  程序启动会先加载配置文件中的配置之后才真正的启动
  """
  [mysqld]  # 一旦服务端启动立刻加载下面的配置
  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
  [mysql]  # 一旦客户端启动立刻加载下面的配置
  ...
  [client]  # 一旦其他客户端启动立刻加载下面的配置
  ...
  
  # 需要新建一个my.ini的配置文件
  
  # 修改配置文件后一定要重启服务才能生效
  
  # 统一编码的配置 无需掌握 直接拷贝即可
  
  # 偷懒 将管理员的用户名和密码也添加到配置文件中
  [mysqld]
  character-set-server=utf8
  collation-server=utf8_general_ci
  [client]
  default-character-set=utf8
  [mysql]
  user="root"
  password=123456
  default-character-set=utf8
  

3 基本sql语句

大部分程序的业务逻辑其实都是增删改查

3.1 针对库的增删改查(文件夹)

# 增
create database db1;
create database db2 charset='gbk';
# 查
show databases;  # 查所有
show create database db1;  # 查单个
# 改
alter database db2 charset='utf8';
# 删
drop database db2;

3.2 针对表的增删改查(文件)

"""
在操作表(文件)的时候 需要指定所在的库(文件夹)
"""
# 查看当前所在的库的名字
select database();
# 切换库
use db1; 

# 增
create table t1(id int,name char(4));
# 查
show tables;  # 查看当前库下面所有的表名
show create table t1;
describe t1;  # 支持简写 desc t1;
# 改
alter table t1 modify name char(16);
# 删
drop table t1;

"""
create table db2.t1(id int);  也可以用绝对路径的形式操作不同的库
"""

3.3 针对数据的增删改查(一行行数据)

"""
一定要先有库 有表 最后才能操作记录
"""
# 增
insert into t1 values(1,'jason');
insert into t1 values(1,'jason'),(2,'egon'),(3,'tank');
# 查
select * from t1;  # 该命令当数据量特别大的时候不建议使用
select name from t1;
# 改
update t1 set name='DSB' where id > 1;
# 删
delete from t1 where id > 1;
delete from t1 where name='jason';
# 将表所有的数据清空
delete from t1;

4 存储引擎

4.1 存储引擎概念

日常生活中文件格式有很多中，并且针对不同的文件格式会有对应不同存储方式和处理机制(txt,pdf,word,mp4...)

针对不同的数据应该有对应的不同的处理机制来存储

存储引擎就是不同的处理机制

4.2 MySQL主要存储引擎

• Innodb

  是MySQL5.5版本及之后默认的存储引擎

  存储数据更加的安全

• myisam

  Indexed Sequential Access Method (有索引的顺序访问方法)

  是MySQL5.5版本之前默认的存储引擎

  由索引访问,速度要比Innodb更快

  但是现在人更加注重的是数据的安全

• memory

  内存引擎(数据全部存放在内存中) 断电数据丢失

• blackhole

  无论存什么，都立刻消失

`

存储引擎	生成文件1	生成文件2	生成文件3
InnoDB	t.frm(框架)	t.ibd(数据)
MyISAM	t.frm(框架)	t.MYD(数据)	t.MYI(索引)
MEMORY	t.frm(框架)
BLACKHOLE	t.frm(框架)

`

memory的数据部分存放在内存中,不会生成文件

blackhole不保存数据部分

`

"""
# 查看所有的存储引擎
show engines;

# 不同的存储引擎在存储表的时候 异同点
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=blackhole;
create table t4(id int) engine=memory;

# 存数据
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
"""

4.3 补充: 创建表的完整语法

# 语法
create table 表名(
	字段名1 类型(宽度) 约束条件1 约束条件2,
    字段名2 类型(宽度) 约束条件,
    字段名3 类型(宽度) 约束条件
)


# 注意点
1 在同一张表中字段名不能重复
2 宽度和约束条件是可选的(可写可不写) 而字段名和字段类型是必须的
	约束条件写的话 也支持写多个
    字段名1 类型(宽度) 约束条件1 约束条件2...,
	create table t5(id);  报错
3 最后一个表头设置后面不能有逗号
	create table t6(id int,name char,);   报错

    
# 部分约束条件
    
not null # 不能插入null
unsigned # 无符号

4.4 补充:表头设置中的宽度

# 宽度: 一般情况下指的是对存储数据的限制
	create table t7(name char);  # 默认宽度是1,即只能存一个字符
    insert into t7 values('jason');
    insert into t7 values(null);  # 关键字NULL,和python中的None类似

    
# 针对不同的版本会出现不同的效果

# 5.6版本默认没有开启严格模式 规定只能存一个字符,但是给了多个字符，那么Mysql会自动截取部分
# 5.7版本及以上或者开启了严格模式 那么就不能超长，一旦超出范围立刻报错 Data too long ...


"""严格模式到底开不开呢？"""
# 应该开
# MySQL5.7之后的版本默认都是开启严格模式的

# 使用数据库的准则:
# 能尽量少的让数据库干活就尽量少 不要给数据库增加额外的压力
# 分工明确,数据的条件判断,不应该由数据库完成


# 约束条件 null        not null(不能插入null)
create table t8(id int,name char not null);


"""
宽度和约束条件是什么关系
	宽度是用来限制数据的存储
	约束条件是在宽度的基础之上增加的额外的约束
"""

5 基本数据类型

sql语句中的基本的数据类型

有整型,浮点型,字符类型,时间类型,枚举及集合类型

5.1 整型

5.1.1 分类

tinyint

smallint

mediumint

bigint

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

---

5.1.2 作用

存储年龄、等级、id、号码等等

"""
以TINYINT 
	是否有符号
		默认情况下是带符号的
	超出会如何
		超出限制只存最大可接受值
"""
create table t9(id tinyint);
insert into t9 values(-129),(256);

# 约束条件之unsigned 无符号
create table t10(id tinyint unsigned);


create table t11(id int);
# int默认也是带符号的  
# 整型默认情况下都是带有符号的

# 针对整型 括号内的宽度到底是干嘛的
create table t12(id int(8));
insert into t12 values(123456789);

"""
特例:只有整型括号里面的数字不是表示限制位数
id int(8)
	如果数字没有超出8位 那么默认用空格填充至8位
	如果数字超出了8位 那么有几位就存几位(但是还是要遵守最大范围)
"""
create table t13(id int(8) unsigned zerofill);
# 用0填充至8位

# 总结:
针对整型字段 括号内无需指定宽度 因为它默认的宽度以及足够显示所有的数据了

5.1.3 补充: 严格模式

# 如何查看严格模式
show variables like "%mode";
# 匹配所有以mode为结尾的variables
# sql_mode中可以看是否为严格模式

模糊匹配/查询
	关键字 like
		%:匹配任意多个字符	(类似于正则相关的概念)
        _:匹配任意单个字符

            
# 修改严格模式
	set session  只在当前窗口有效
    set global   全局有效
    
    set global sql_mode = 'STRICT_TRANS_TABLES';

    
# 修改完之后 重新进入服务端即可

5.2 浮点型

5.2.1 分类

FLOAT、DOUBLE、DECIMAL

5.2.2 作用

存储身高、体重、薪资等

# 存储限制
float(255,30)  # 总共255位 小数部分占30位
double(255,30)  # 总共255位 小数部分占30位
decimal(65,30)  # 总共65位 小数部分占30位

# 精确度验证
create table t15(id float(255,30));
create table t16(id double(255,30));
create table t17(id decimal(65,30));
"""你们在前期不要给我用反向键 所有的命令全部手敲！！！增加熟练度"""

insert into t15 values(1.111111111111111111111111111111);
insert into t16 values(1.111111111111111111111111111111);
insert into t17 values(1.111111111111111111111111111111);

float < double < decimal
# 要结合实际应用场景 三者都能使用
# 多出的第一位会四舍五入,其他忽略
# 少的用0补全小数位
# 大小上限为表中大小

5.3 字符类型

tip: 输入数字时会自动转换成字符,不报错

5.3.1 分类

"""
char
	定长
	char(4)	 数据超过四个字符直接报错 不够四个字符空格补全
varchar
	变长
	varchar(4)  数据超过四个字符直接报错 不够有几个存几个
"""

create table t18(name char(4));
create table t19(name varchar(4));

insert into t18 values('a');
insert into t19 values('a');

# 介绍一个小方法 char_length统计字段长度
select char_length(name) from t18;
select char_length(name) from t19;


"""
首先可以肯定的是 char硬盘上存的绝对是真正的数据 带有空格的
但是在显示的时候MySQL会自动将多余的空格剔除
"""


# 再次修改sql_mode 让MySQL不要做自动剔除操作
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

5.3.2 char与varchar对比

"""
char
	缺点:浪费空间
	优点:存取都很简单
		直接按照固定的字符存取数据即可
		jason egon alex wusir tank 
		存按照五个字符存 取也直接按照五个字符取
		
varchar
	优点:节省空间
	缺点:存取较为麻烦
		1bytes+jason 1bytes+egon 1bytes+alex 1bytes+tank 
		
		存的时候需要制作报头
		取的时候也需要先读取报头 之后才能读取真实数据
		
以前基本上都是用的char 其实现在用varchar的也挺多
"""

补充:
    进来公司之后你完全不需要考虑字段类型和字段名
    因为产品经理给你发的邮件上已经全部指明了

5.4 时间类型

5.4.1 分类

date: 年月日 2020-5-4

datetime:年月日时分秒 2020-5-4 11:11:11

time: 时分秒 11:11:11

year: 年 2020

create table student(
	id int,
    name varchar(16),
    born_year year,
    birth date,
    study_time time,
    reg_time datetime
);
insert into student values(1,'egon','1880','1880-11-11','11:11:11','2020-11-11 11:11:11');

5.5 枚举与集合类型

5.5.1 分类

"""
枚举(enum)  多选一(必须选择一个)
集合(set)   多选多(可以选0或1个或任意多个)
"""

5.5.2 具体使用

create table user(
	id int,
    name char(16),
    gender enum('male','female','others')
);
insert into user values(1,'jason','male');  正常
insert into user values(2,'egon','xxxxooo');  报错
# 枚举字段 后期在存数据的时候只能从枚举里面选择一个存储 


create table teacher(
	id int,
    name char(16),
    gender enum('male','female','others'),
    hobby set('read','DBJ','hecha')
);
insert into teacher values(1,'jason','male','read');  正常
insert into teacher values(2,'egon','female','DBJ,hecha');  正常
insert into teacher values(3,'tank','others','生蚝'); 报错
# 集合可以只写一个  但是不能写没有列举的

6 约束条件

6.1 default默认值

# 补充知识点  插入数据的时候可以指定字段
create table t1(
	id int,
    name char(16)
);
insert into t1(name,id) values('jason',1);

create table t2(
	id int,
    name char(16),
    gender enum('male','female','others') default 'male'
);
insert into t2(id,name) values(1,'jason');
insert into t2 values(2,'egon','female');

6.2 unique唯一

唯一可以分为单列唯一和联合唯一

单列唯一表示一个字段单独不重复,如id.不能出现两个一样的id

`

联合唯一表示指定的多个字段不能完全相同,如ip与端口port,

ip相同,端口不同

或端口相同,ip不同

都可以接受,唯独不能接受的是端口和ip都相同,这时就能使用联合唯一

# 单列唯一
create table t3(
	id int unique,
    name char(16)
);
insert into t3 values(1,'jason'),(1,'egon');
insert into t3 values(1,'jason'),(2,'egon');

# 联合唯一
"""
ip和port
单个都可以重复 但是加载一起必须是唯一的
"""
create table t4(
	id int,
    ip char(16),
    port int,
    unique(ip,port)
);
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.1',8081);
insert into t4 values(3,'127.0.0.2',8080);
insert into t4 values(4,'127.0.0.1',8080);  报错

6.3 primary key主键

primary key的作用相当于unique + not null + 提升查询效率

但是一个表中有且只能有一个primary key,它可以用来提升查询的效率

如果不设置primary key会自动搜索一个unique + not null的字段名(自上而下搜索),升级为primary key

如果不设置且无unique + not null的字段名,则生成一个隐藏的primary key,这时无法提升查询效率

"""
1.单单从约束效果上来看primary key等价于not null + unique
非空且唯一！！！
"""
create table t5(id int primary key);
insert into t5 values(null);   # 报错
insert into t5 values(1),(1);  # 报错
insert into t5 values(1),(2); 

"""
2.它除了有约束效果之外 它还是Innodb存储引擎组织数据的依据
Innodb存储引擎在创建表的时候必须要有primary key
因为它类似于书的目录 能够帮助提升查询效率并且也是建表的依据
"""
# 1 一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键
create table t6(
	id int,
    name char(16),
    age int not null unique,
    addr char(32) not null unique
);

# 2 如果表中没有主键也没有其他任何的非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它 就无法提升查询速度

# 3 一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键
# 单个字段主键
create table t5(
    id int primary key
	name char(16)
);



# 联合主键(多个字段联合起来作为表的主键 本质还是一个主键)
# 一个表中有且只能有一个primary key,本质还是一个主键
create table t7(
    ip char(16),
    port int,
    primary key(ip,port)
);

"""
也意味着 以后我们在创建表的时候id字段一定要加primary key
"""

6.4 auto_increment自增

auto_increment通常都是加在主键上的,

只能给键加, 不能给普通字段加

# 当编号特别多的时候 人为的去维护太麻烦
create table t8(
	id int primary key auto_increment,
    name char(16)
);
insert into t8(name) values('jason'),('egon'),('kevin');

# 注意auto_increment通常都是加在主键上的 不能给普通字段加
create table t9(
	id int primary key auto_increment,
    name char(16),
    cid int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

6.4.1 注意点

delete 删除表中数据后 主键的自增不会停止

delete from t1  删除表中数据后 主键的自增不会停止

truncate t1  清空表数据并且重置主键

6.4.2 id总结

"""
在创建表的id(数据的唯一标识id、uid、sid)字段的时候使用
id int primary key auto_increment
"""

7 表与表之间建立关系

"""
定义一张员工表 表中有很多字段
id password name gender dep_name dep_desc
"""
# 1 该表的组织结构不是很清晰(可忽视)
# 2 浪费硬盘空间(可忽视)
# 3 数据的扩展性极差(无法忽视的)
# 4 由于数据都存在一起,读取速度慢

# 如何优化？
# 上述问题就类似于将所有的代码都写在了一个py文件中
# 将员工表拆分  员工表和部门表

7.1 外键

foreign key

"""
外键就是用来帮助我们建立表与表之间关系的
foreign key
"""

7.2 表关系

"""
表与表之间最多只有四种关系
	一对多关系
	多对多关系
	一对一关系
	没有关系
"""

7.3 一对多关系

外键字段建在 一对多中 多的那一方

外键

"""
判断表与表之间关系的时候 前期不熟悉的情况下 一定要按照我给你的建议
换位思考  分别站在两张表的角度考虑

员工表与部门表为例
	先站在员工表
		思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)
			不能！！！
			(不能直接得出结论 一定要两张表都考虑完全)
	再站在部门表
		思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)
			能！！！
	得出结论
		员工表与部门表示单向的一对多
		所以表关系就是一对多
"""
foreign key
	1 一对多表关系   外键字段建在多的一方
    2 在创建表的时候 一定要先建被关联表 
    3 在录入数据的时候 也必须先录入被关联表
# SQL语句建立表关系
create table dep(
	id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

create table emp(
	id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);


# 修改dep表里面的id字段
update dep set id=200 where id=2;  不行
# 删除dep表里面的数据
delete from dep;  不行

# 1 先删除教学部对应的员工数据 之后再删除部门
	操作太过繁琐
    
# 2 真正做到数据之间有关系
	更新就同步更新
    删除就同步删除
"""
级联更新   >>>   同步更新
级联删除   >>>   同步删除
"""
create table dep(
	id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

create table emp(
	id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

7.4 多对多

一对多 + 一对多 + 关系表

"""
图书表和作者表
"""


create table book(
	id int primary key auto_increment,
    title varchar(32),
    price int,
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);


create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    book_id int,
    foreign key(book_id) references book(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);

"""
按照上述的方式创建 一个都别想成功！！！
其实我们只是想记录书籍和作者的关系
针对多对多字段表关系 不能在两张原有的表中创建外键
需要你单独再开设一张 专门用来存储两张表数据之间的关系
"""

create table book(
	id int primary key auto_increment,
    title varchar(32),
    price int
);


create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int
);


create table book2author(
	id int primary key auto_increment,
    author_id int,
    book_id int,
    foreign key(author_id) references author(id) 
    on update cascade  # 同步更新
    on delete cascade,  # 同步删除
    foreign key(book_id) references book(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);

7.5 一对一

unique + 外键

"""
id name age addr phone hobby email........
如果一个表的字段特别多 每次查询又不是所有的字段都能用得到
将表一分为二  
	用户表
		用户表
			id name age
		用户详情表
			id addr phone hobby email........
	
	站在用户表
		一个用户能否对应多个用户详情   不能！！！
	站在详情表
		一个详情能否属于多个用户      不能！！！
	结论:单向的一对多都不成立 那么这个时候两者之间的表关系
		就是一对一
		或者没有关系(好判断)

客户表和学生表
	在你们报名之前你们是客户端
	报名之后是学生(期间有一些客户不会报名)
"""

# 一对一 外键字段建在任意一方都可以 但是推荐建在查询频率比较高的表,从表,数据少的表中
create table authordetail(
	id int primary key auto_increment,
    phone int,
    addr varchar(64)
);
create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int unique,
    foreign key(authordetail_id) references authordetail(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
)

7.6 总结

"""
表关系的建立需要用到foreign key
	一对多
		外键字段建在多的一方
	多对多
		自己开设第三张存储
	一对一
		建在任意一方都可以 但是推荐建在查询频率较高的表中

判断表之间关系的方式
	换位思考！！！
		员工与部门		一对多
	
		图书与作者		多对多
	
		作者与作者详情		一对一
"""

8 修改表(了解)

# MySQL对大小写是不敏感的
"""
1 修改表名
	alter table 表名 rename 新表名;

2 增加字段
	alter table 表名 add 字段名 字段类型(宽度)  约束条件;
	alter table 表名 add 字段名 字段类型(宽度)  约束条件 first;
	alter table 表名 add 字段名 字段类型(宽度)  约束条件 after 字段名;

3 删除字段
	alter table 表名 drop 字段名;

4 修改字段
	alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
	
	alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
	
"""

9 复制表(了解)

"""
我们sql语句查询的结果其实也是一张虚拟表
"""
create table 表名 select * from 旧表;  不能复制主键 外键 ...

create table new_dep2 select * from dep where id>3;

10 数据筛选

练习使用的表

create table emp(
  id int not null unique auto_increment,
  name varchar(20) not null,
  sex 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,sex,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);


# 当表字段特别多 展示的时候错乱 可以使用\G分行展示
select * from emp\G;

# 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成GBK

10.1 重要关键字的执行顺序

# 书写顺序
select id,name from emp where id > 3;
# 执行顺序
from
where
select

"""
	先写select * from 表名 where 条件
	之后再补全sql语句,将*号替换成具体的字段
"""

10.2 where筛选条件

简略版

where id>=3 and id<=6

where id between 3 and 6

where salary=20000 or salary=18000 or salary=17000

where salary in (20000,18000,17000)

where name like '%o%'
	%  匹配任意多个字符
	_  匹配任意单个字符

where name like '____'

where char_length(name) = 4

where id not between 3 and 6

where salary not in (20000,18000,17000)


# 对于null不能用等号 要用is
where post_comment is null

详细版

# 作用:是对整体数据的一个筛选操作
# 1.查询id大于等于3小于等于6的数据
select id,name,age from emp where id>=3 and id<=6;
select id,name 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.查询员工姓名中包含字母o的员工的姓名和薪资
"""
模糊查询
	like
		%  匹配任意多个字符
		_  匹配任意单个字符
"""
select name,salary from emp where name like '%o%';

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

# 5.查询id小于3或者id大于6的数据
select * from emp where id not between 3 and 6;

# 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 = NULL;
select name,post from emp where post_comment is NULL;

10.3 group by分组

10.3.1 简略版

聚合函数
	max
	min
	sum
	count
	avg
	


select post as '部门',max(salary) as '最高薪资' from emp group by post;
# 改名

select post,avg(salary) from emp group by post;
# |post|平均工资|		的表

select post,group_concat(name,':',salary) from emp group by post;
# |post|name:salary|  的表

select concat('NAME:',name),concat('SAL:',salary) from emp;
# |NAME:name|SAL:salary|的表

select t1.id,t1.name from emp as t1;
# 注意select后的是t1.id,不再是emp.id,因为已经临时改名


# 关键字where和group by同时出现的时候group by必须在where的后面
# 聚合函数必须在分组后才能使用,而where在分组之前执行,所以不能在where条件中使用
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
        ;
# 筛选出年龄大于30岁的,然后按post分组,保留平均薪资大于10000的post组,显示post和平均薪资

10.3.2 详细版

# 分组实际应用场景  分组应用场景非常的多
	男女比例
    部门平均薪资
    部门秃头率
    国家之间数据统计

# 1	按照部门分组
select * from emp group by post;
"""
分组之后 最小可操作单位应该是组 还不再是组内的单个数据
	上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据)
	如果设置了严格模式 那么上述命令会直接报错 
	ERROR 1055 (42000): 'day47.emp.id' isn't in GROUP BY
"""

set global sql_mode = 'strict_trans_tables,only_full_group_by';
设置之后  分组 之后默认只能拿到分组的依据,无法直接获取其他数据


select post from emp group by post;  
按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数)


"""
什么时候需要分组啊？？？
	关键字 
		每个 平均 最高 最低 
	
	聚合函数
		max
		min
		sum
		count
		avg
"""
# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
select post '部门',max(salary) '最高薪资' from emp group by post;
# as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱

# 2.获取每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.获取每个部门的工资总和
select post,sum(salary) from emp group by post;
# 5.获取每个部门的人数
select post,count(id) from emp group by post;  # 常用 符合逻辑
select post,count(salary) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post;  null不行

# 6.查询分组之后的部门名称和每个部门下所有的员工姓名 
# group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
# concat不分组的时候用 
select concat('NAME:',name),concat('SAL:',salary) from emp;

# 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp;  
select emp.id,emp.name from emp as t1;   报错
select t1.id,t1.name from emp as t1;

# 查询每个人的年薪  12薪
select name,salary*12 from emp;

10.3.3 分组注意事项

# 关键字where和group by同时出现的时候group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
select id,name,age from emp where max(salary) > 3000;

select max(salary) from emp;  # 不分组 默认整体就是一组

# 统计各部门年龄在30岁以上的员工平均薪资
	1 先求所有年龄大于30岁的员工
    	select * from emp where age>30;
    2 再对结果进行分组
     	select * from emp where age>30 group by post;
    
    select post,avg(salary) from emp where age>30 group by post;

10.3.4 having分组之后的筛选条件

"""
having的语法与where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
"""

# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp 
		where age>30 
    	group by post
        having avg(salary) > 10000
        ;

10.4 distinct去重

"""
一定要注意 必须是完全一样的数据才可以去重！！！
一定不要将逐渐忽视了 有逐渐存在的情况下 是不可能去重的
[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]
ORM  对象关系映射   让不懂SQL语句的人也能够非常牛逼的操作数据库
表								类
一条条的数据						对象
字段对应的值						对象的属性

你再写类 就意味着在创建表
用类生成对象 就意味着再创建数据
对象点属性 就是在获取数据字段对应的值
目的就是减轻python程序员的压力 只需要会python面向对象的知识点就可以操作MySQL
"""
select distinct id,age from emp;		# 无法去重,因为id不同
select distinct age from emp;

10.5 order by排序

select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc;
"""
order by默认是升序  asc 该asc可以省略不写
也可以修改为降序     desc
"""
select * from emp order by age desc,salary asc;
# 先按照age降序排  如果碰到age相同 则再按照salary升序排

# 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序
	select post,avg(salary) from emp 
		where age>10 
    	group by post
        having avg(salary) > 1000
        order by avg(salary) desc
        ;

10.6 limit限制展示条数

select * from emp;
"""针对数据过多的情况 我们通常都是做分页处理"""
select * from emp limit 3;  # 只展示三条数据

select * from emp limit 0,5;		# 1 2 3 4 5
select * from emp limit 5,5;		# 6 7 8 9 10
# 第一个参数是起始位置
# 第二个参数是展示条数

10.7 正则

select * from emp where name regexp '^j.*(n|y)$';
# 筛选以j开头,中间任意位任意字符,以n或者y结尾 的名字 的所有信息


"""
正则是一门独立的语言
在python中如果你想使用正则需要借助于re模块
	面试题
		1.re模块中常用的方法
			findall:分组优先展示
				j.*(n|y)
				不会展示所有正则表达式匹配到的内容,只展示n或者y,
				如findall('j.*(n|y)','jasonjerryjfasgdsgwkaef')不是得到jason,jerry,而是n,y
				而仅仅展示括号内正则表达式匹配到的内容
				
			如果想取消分组优先就findall('j.*(?:n|y)','jasonjerryjfasgdsgwkaef')
			
			
				
			match: 从头匹配,没分组优先		 成功返回,失败None  相当于search+^
			
			如findall('j.*(n|y)','jasonjerryjfasgdsgwkaef')
			group后得到jason
			如findall('j.*(n|y)','ajasonjerryjfasgdsgwkaef')
			得到None
			
			
			
			search:从整体匹配,没分组优先	  	整体中遇到第一个匹配成功的就返回(用group得到),失败返回None
			如findall('j.*(n|y)','ajasonjerryjfasgdsgwkaef')
			group后得到jason
			
			
			
		2.贪婪匹配与非贪婪匹配
			正则表达式默认都是贪婪匹配的
			将贪婪变成非贪婪只需要在正则表达式后面加?
			.*  贪婪
			.*? 非贪婪
"""

11 多表查询

前期表准备

#建表
create table dep(
id int,
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,'运营');

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

11.1 表查询

select * from dep,emp;  # 结果   笛卡尔积

"""
了解即可
假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}
"""


select * from emp,dep where emp.dep_id = dep.id;

"""
MySQL开设了对应的方法 进行拼表操作
	inner join  内连接
	left join   左连接
	right join  右连接
	union		全连接
"""
# inner join  内连接
select * from emp inner join dep on emp.dep_id = dep.id;
# 只拼接两张表中公有的数据部分

# left join   左连接
select * from emp left join dep on emp.dep_id = dep.id;
# 左表所有的数据都展示出来 没有对应的项就用NULL

# right join  右连接
select * from emp right join dep on emp.dep_id = dep.id;
# 右表所有的数据都展示出来 没有对应的项就用NULL

# union		全连接  左右两表所有的数据都展示出来
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;

11.2 子查询

"""
子查询就是我们平时解决问题的思路
	分步骤解决问题
		第一步
		第二步
		...
将一个查询语句的结果当做另外一个查询语句的条件去用
"""
# 查询部门是技术或者人力资源的员工信息
	1 先获取部门的id号
    2 再去员工表里面筛选出对应的员工
    select id from dep where name='技术' or name = '人力资源';
    
    select name from emp where dep_id in (200,201);
    
    
    select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');

11.3 总结

# 表的查询结果可以作为其他表的查询条件
# 也可以通过起别名的方式把它作为一个张虚拟表根其他表关联

"""
多表查询就两种方式
	1 先拼接表再查询
	
	2 子查询 一步一步查询
"""

11.4 补充: 关键字exists(了解)

只返回布尔值 True False
返回True的时候外层查询语句执行
返回False的时候外层查询语句不再执行
select * from emp where exists 
	(select id from dep where id>3);
        
select * from emp where exists 
    (select id from dep where id>300);