数据库
参考网站
知乎
#数据
1.不同的人把相同的数据随意的存到一个文件中，数据的格式不一定相同
2.软件开发目录规范
规定了数据存放的位置，db目录
3.将数据保存到一个公共的地方，查找数据的时候就来这个地方查找
#数据库之mysql
数据库本质上就是一款基于网络通信的cs架构的应用软件
任何基于网络通信的软件，底层使用的都是socket
MySQL不单单支持MySQL客户端来操作，也支持其他编程语言使用代码直接操作，为了解决语言的沟通问题，数据库制定了统一的语言--SQL语句
mysql其实就是一款远程操作文件的软件，这一点与git、mvn类似
#服务端
基于socket通信
收发消息
SQL语句（公共的标准）
#客户端（自带的客户端、第三方软件）
基于socket通信
收发消息
SQL语句（公共的标准）
#数据管理系统DBMS
#数据库的分类
关系型数据库,数据与数据之间有关联、限制
MySQL、oracle、SQLserver、sqllite、db2
#关系型数据库通常都是表结构（Excel），使用关系型数据库之前需要确定表结构、字段
非关系型数据库
redis、mongodb(文档型数据库)、memcache
#通常以键值对的方式存储数据
数据库的安装与基本使用
#可以把mysql看成是一款支持远程操作文件的软件
库 相当于文件夹
表 相当于文件
记录 文件内的一行行的数据
表头：表格的第一行数据
字段：字段名+字段类型
#表头由字段组成
#mysql中的注释
1.#号
2.--
下载mysql
#mysql的下载
官网下载社区版（包括客户端、服务端），解压
#服务端
mysqld
#客户端
mysql
#启动mysql
1.切换到bin目录
2.执行mysqld、mysql
启动mysql(mysqld)
#启动mysql
#mysql下载好之后，对于文件的修改、执行，最好使用管理员身份
Windows+r 启动的是普通用户，不是管理员
#以管理员启动CMD（命令提示符）的方法
1.在开始栏找到命令提示符
2.鼠标右键以管理员身份运行
3.输入命令，启动mysqld（mysql服务端）
C:windowssystem32>F:
F:>cd F:appmysql-5.6.45-winx64in
F:appmysql-5.6.45-winx64in>
F:appmysql-5.6.45-winx64in>mysqld
4.使用Windows+r再运行一个CMD窗口，启动mysql客户端
C:Users17575>F:
F:>cd F:appmysql-5.6.45-winx64in
F:appmysql-5.6.45-winx64in>mysql -h127.0.0.1 -P3306 -uroot -p
Enter password: #mysql没有初始密码
mysql>
进程
#查看进程
F:>tasklist |findstr mysqld
mysqld.exe 13496 Console 4 454,476 K
#杀死进程（必须以管理员身份运行）
F:>taskkill /F /PID 13496
成功: 已终止 PID 为 13496 的进程。
环境变量
#环境变量
将启动文件所在的路径添加到环境变量中
1.鼠标右键此电脑,属性
2.高级系统设置，环境变量
3.双击系统变量中的Path，新建'要添加的环境变量路径'即可
4.重启mysql服务端、CMD终端
#启动服务端
C:windowssystem32>mysqld
#启动客户端
C:windowssystem32>mysql -uroot -p
Enter password:
mysql开机自启动
#将mysqld制作成系统服务
C:windowssystem32>mysqld --install
Service successfully installed.
#此时mysql就可以开机自启动了,service.msc图形化可以设置mysql的状态
#mysql启动
1.CMD命令行启动
F:appmysql-5.6.45-winx64in>net stop mysql
F:appmysql-5.6.45-winx64in>net start mysql
2.Windows+r，输入services.msc,使用图形化启动
修改mysql数据库root密码
#实例，先确认，再修改
C:windowssystem32>mysqladmin -uroot -p password 123
Enter password:
Warning: Using a password on the command line interface can be insecure.
#修改密码格式
mysqladmin -uroot -p原密码 password 新密码;
update mysql.user set password=password(123) where user='root' and host='localhost';
'破解'mysql的管理员密码
#'破解'
1.先将已经启动的服务端停掉
C:windowssystem32> tasklist |findstr mysqld
mysqld.exe 27428 Services 0 93,272 K
C:windowssystem32> taskkill /F /PID 27428
成功: 已终止 PID 为 27428 的进程。
2.跳过用户名和密码的验证功能，启动服务端（跳过授权表）
C:windowssystem32> mysqld --skip-grant-tables
3.以管理员的身份再开启一个CMD窗口，使用mysql客户端连接mysql服务端
C:windowssystem32> mysql -uroot
4.修改管理员密码
mysql> update mysql.user set password=password(1) where user='root' and host='localhost';
5.关闭服务端，再以检验密码的方式启动（正常启动就行）
F:appmysql-5.6.45-winx64in>mysql -uroot -p
Enter password: *
配置文件
#s查看mysql服务端的简介
mysql> s
--------------
mysql Ver 14.14 Distrib 5.6.45, for Win64 (x86_64)
Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.6.45 MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 1 hour 1 min 17 sec
Threads: 1 Questions: 5 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.001
#Windows中，配置文件的后缀通常都是.ini结尾的（给人看的）
#mysql自带的配置文件不要修改，但是你可以自己新建一个配置文件my.ini，mysql服务端在启动的时候就会加载my.ini文件内的配置
#修改了配置文件，一定要重启服务，这样才能让配置的修改生效，但是配置文件中，客户端配置的修改不重启服务也能生效
#my.ini
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client] #第三方客户端
default-character-set=utf8
[mysql] #mysql自带的客户端，每运行一次SQL语句就会运行配置文件中对应的配置
default-character-set=utf8
#重新查看mysql配置
mysql> s
--------------
mysql Ver 14.14 Distrib 5.6.45, for Win64 (x86_64)
Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.6.45 MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 19 sec
Threads: 1 Questions: 5 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.263
--------------
数据库的增删改查
修改数据库名参考
delete与truncate的区别
#库
增
create database db_name;
查
show databases;
show create database db_name;
show engines; #查看数据库存储引擎，mysql存储引擎默认为innodb
show variables like 'default_storage_engine'; #正在使用的存储引擎
set default_storage_engine=myisam; #修改mysql数据库的存储引擎
改
#数据库名不能直接修改
alter database db_name charset 'gbk' #修改库的字符集
删
drop database db_name;
#表
#创建表的时候，要进入到指定的库下，use db_name;
#查看当前所在的库，select database();
增
create table table_name(id int,name char(4))
查
show tables;
show create table table_name;
desc table_name; #查看表结构，等效于describe
describe table_name;
改
alter table table_name rename new_table_name; #修改表名
alter table table_name change old_field_name new_field_name varchar(4)
alter table table_name modify name char(32); #修改字段的属性
删
drop table table_name;
#数据
增
insert table_name value(1,'syy');
insert table_name value(2,'ayy'),(3,'byy');
insert table_name(name) value('xyy');
insert table_name(id,name) value(5,'Xyy');
查
select id,name from table_name;
select * from table_name where id=5;
select * from table_name where id =1 and name='syy';
select * from table_name where id =1 or id=2;
select * from table_name where not id =1; #不包含NULL
改
update table_name set id=4 where name='xyy'; #默认不区分大小写(Windows)
update table_name set id=1,name='xxx' where id=1;
删
delete from table_name where id=4 and name='xyy';
delete from table_name; #清空表
truncate table_name; #清空表
存储引擎
#存储引擎
不同的数据应该有不同的处理机制，这样的处理机制就是存储引擎工作的机制
#查看mysql支持的存储引擎
mysql> show engines;
#mysql存储引擎
innodb mysql默认的存储引擎（5.1版本之后）
myisam 老版本存储引擎（5.1版本之前）
memory 数据存储在内存中，是一个内存引擎
blackhole 无论存什么都会消失（黑洞）
#innodb存储引擎的特点
1.支持事务
2.支持行级锁，处理数据更加安全
3.支持外键
#myisam存储引擎的特点
1.查询数据更快
#验证不同存储引擎的特点
mysql> create table t1(id int) engine=innodb;
mysql> create table t2(id int) engine=myisam;
mysql> create table t3(id int) engine=blackhole;
mysql> create table t4(id int) engine=memory;
#不同的表就对应了不同的文件（不同的存储引擎，一个表对应的文件个数不同）
1.innodb，一个表有两个文件，table_name.frm,该文件存储表结构，table_name.ibd,该文件存储表中的真实数据
2.myisam，一个表有三个文件，table_name.frm,该文件存储表结构,table_name.MYD,该文件存储表中的真实数据,table_name.MYI,该文件存储表的索引
3.blackhole,一个表有一个文件，table_name.frm,该文件存储表结构，数据不需要存储到文件，所以没有table_name.ibd文件
4.memory,一个表有一个文件，table_name.frm,该文件存储表结构,数据存储到内存，不需要存储到文件，所以没有table_name.ibd文件
#向不同的表中插入数据
mysql> insert t1 value(1);
mysql> insert t2 value(2);
mysql> insert t3 value(3); #数据立即消失
mysql> insert t4 value(4); #服务端重启，数据消失
创建表的完整语法
字符串char
#
create table table_name(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意
1.字段名和字段类型是必须的，中括号内的参数都是可选参数
2.同一张表中字段名不能重复，字段的类型是可以重复的
3.创建表的SQL语句中，最后一个字段类型的后面不能加逗号
#'类型'约束的是数据的存储类型，'约束条件'是数据的额外限制
#mysql和Windows一样，对大小写不敏感
#宽度
宽度是字段的属性，是对数据的'个数'限制
#例
字符宽度与数据插入
mysql> create table test(name char);
mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name | char(1) | YES | | NULL | | #默认宽度为1
+-------+---------+------+-----+---------+-------+
mysql> insert test value('syy');
mysql> select * from test;
+------+
| name |
+------+
| s | #只能存储第一个字符，或者直接报错（数据库严格模式下）
+------+
#数据库的严格模式(默认是关闭的)
#开启的话，上例就会直接报错
#关闭的话，上例中数据库会先截取字符，再插入数据
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #关闭
#例2
设置字段非空
mysql> select * from test;
+------+
| name |
+------+
| s |
+------+
mysql> insert test value(NULL); #NULL为特殊字符
mysql> select * from test;
+------+
| name |
+------+
| s |
| NULL |
+------+
mysql> alter table test modify name char not null;
mysql> select * from test;
+------+
| name |
+------+
| s |
| |
+------+
#char后面的数字是用来限制插入字符串的长度的
mysql> create database test2;
mysql> create table test2.test(name char(4));
mysql> desc test2.test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name | char(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> insert test2.test value('hahaha');
mysql> select * from test2.test;
+------+
| name |
+------+
| haha |
+------+
字符串varchar
#varchar(4)
char表示字符串的数据类型，后面的数字表示最大存储字符的个数，如果插入字符少了的话，那么就用空格填充，如果插入字符多了的话
严格模式开启，直接报错
严格模式关闭，先截取再插入
varchar表示字符串的数据类型，后面的数字表示最大存储字符的个数，如果插入字符少了的话，那么就有几个存几个，如果插入字符多了的话
严格模式开启，直接报错
严格模式关闭，先截取再插入
#例
mysql> create table test1(name char(4));
mysql> create table test2(name varchar(4));
mysql> insert test1 value('syyha');
ERROR 1406 (22001): Data too long for column 'name' at row 1 #严格模式，直接报错
mysql> insert test2 value('syyha');
ERROR 1406 (22001): Data too long for column 'name' at row 1 #严格模式，直接报错
mysql> insert test1 value('ha');
mysql> insert test2 value('ha');
mysql> select * from test1,test2;
+------+------+
| name | name |
+------+------+
| ha | ha |
+------+------+
#使用关键字char_length(),统计某列字符长度
#mysql在存储char类型字段的时候，硬盘上确确实实存的是固定长度的数据，但是取出来的时候，mysql会自动将填充的空格去掉，所以该函数统计的字符长度并不包括插入数据的时候填充的空格
mysql> select char_length(name) from test1;
+-------------------+
| char_length(name) |
+-------------------+
| 2 |
+-------------------+
mysql> select char_length(name) from test2;
+-------------------+
| char_length(name) |
+-------------------+
| 2 |
+-------------------+
#设置sql_mode,修改字符统计的方法
mysql> set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
mysql>
#重连数据库
mysql> show variables like '%mode%'; #查看配置是否生效
+----------------------------+---------------------------------------------+
| Variable_name | Value |
+----------------------------+---------------------------------------------+
| binlogging_impossible_mode | IGNORE_ERROR |
| block_encryption_mode | aes-128-ecb |
| gtid_mode | OFF |
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| pseudo_slave_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH |
+----------------------------+---------------------------------------------+
#重新使用关键字char_length(),统计某列字符长度
mysql> select char_length(name) from test1;
+-------------------+
| char_length(name) |
+-------------------+
| 4 | #说明插入字符不足，char会填充
+-------------------+
mysql> select char_length(name) from test2;
+-------------------+
| char_length(name) |
+-------------------+
| 2 | #说明插入字符不足，varchar不会填充
+-------------------+
#char与varchar的区别
char定长
1.浪费空间
2.存取速度快（按固定长度取即可）
varchar变长（#推荐）
1.比较节省空间
2.存取速度慢（存的时候需要给数据加一个报头，取的时候需要先查看数据对应的报头，再取出指定长度的数据）
整型
#TINYINT
范围是(-128,127),默认有负数，超出范围只会插入最大值或最小值
#测试TINYINT的范围
mysql> create table test(id tinyint);
mysql> desc test;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | tinyint(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
mysql> insert test value(-128),(127);
mysql> insert test value(-129),(128); #超出范围，插入最大值或最小值
mysql> select * from test;
+------+
| id |
+------+
| -128 |
| 127 |
| -128 |
| 127 |
+------+
#使用关键字unsigned修改tinyint范围,(0,255)
mysql> alter table test modify id TINYINT unsigned;
mysql> select * from test;
+------+
| id |
+------+
| 0 |
| 127 |
| 0 |
| 127 |
+------+
mysql> insert test value(-1),(256);
mysql> select * from test;
+------+
| id |
+------+
| 0 |
| 127 |
| 0 |
| 127 |
| 0 |
| 255 |
+------+
#INT
范围是(-2147483648,2147483647),默认有负数，超出范围只会插入最大值或最小值
#测试INT范围
mysql> create table test(id int);
mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | | #默认为11，满足一般使用
+-------+---------+------+-----+---------+-------+
mysql> insert test value(-1111111111111111),(1111111111111111111111);
mysql> select * from test;
+-------------+
| id |
+-------------+
| -2147483648 |
| 2147483647 |
+-------------+
#使用关键字unsigned修改tinyint范围,(0,4294967295)
mysql> alter table test modify id int unsigned;
mysql> insert test value(-1111111111111111),(11111111111111111111111111111111);
mysql> select * from test;
+------------+
| id |
+------------+
| 0 |
| 2147483647 |
| 0 |
| 4294967295 |
+------------+
#整型后面的数字不是用来限制存储数据的长度的，而是用来控制展示数据的长度/位数的
#int后面的数字大于或等于8位，那么插入的数字中，有几位数字就直接存几位数字
#int后面的数字小于8位的话，插入的数字位数不足的话，默认以空格填充，插入的数字等于或大于的话，直接存储
mysql> alter table test modify id int(3);
mysql> desc test;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | int(3) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
mysql> insert test value(1234);
mysql> select * from test;
+-------+
| id |
+-------+
| 1234 |
+-------+
#使用关键字zerofill,修改数据类型int的数字填充方式（用0填充，无论int后面的数字是多少）
mysql> create table test(id int(8) zerofill);
mysql> insert test value(1),(12345678);
mysql> select * from test;
+----------+
| id |
+----------+
| 00000001 |
| 12345678 |
+----------+
数据库sql严格模式
#模糊匹配
like
%,匹配任多个个字符
_,匹配任意一个字符
#查看mysql配置中，变量名含有mode的变量名
mysql> show variables like '%mode%';
+----------------------------+------------------------+
| Variable_name | Value |
+----------------------------+------------------------+
| binlogging_impossible_mode | IGNORE_ERROR |
| block_encryption_mode | aes-128-ecb |
| gtid_mode | OFF |
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| pseudo_slave_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | NO_ENGINE_SUBSTITUTION |
+----------------------------+------------------------+
+----------------------------+---------------------------------------------+
| Variable_name | Value |
+----------------------------+---------------------------------------------+
| binlogging_impossible_mode | IGNORE_ERROR |
| block_encryption_mode | aes-128-ecb |
| gtid_mode | OFF |
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| pseudo_slave_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH |
+----------------------------+---------------------------------------------+
#临时设置严格模式 ，立即生效
set session sql_mode='STRICT_TRANS_TABLES';
#永久设置严格模式，需要重新进入mysql客户端才能生效
set global sql_mode='STRICT_TRANS_TABLES';
#STRICT_TRANS_TABLES，SQL开启严格模式，SQL不会对数据再有剪切、默认等动作
#PAD_CHAR_TO_FULL_LENGTH，验证length()函数在统计char和varchar上的区别
#only_full_group_by，group_by使用SQL严格模式
浮点型
#浮点型
type(最大所有位数,最大小数位数)
float(255,30)
double(255,30)
decimal(65,30)
#区别
插入数据的精确度：float < double < decimal
#例
mysql> create table test1(id float(255,30));
mysql> create table test2(id double(255,30));
mysql> create table test3(id decimal(65,30));
mysql> insert test1 value(1.012345678901234567890123456789);
mysql> insert test2 value(1.012345678901234567890123456789);
mysql> insert test3 value(1.012345678901234567890123456789);
mysql> select * from test1,test2,test3;
1.012345671653747600000000000000 |
1.012345678901234600000000000000 |
1.012345678901234567890123456789 |
#通常情况下，会将数字先转化为字符串，然后再存储数据库上，这样就不需要考虑浮点型的精度问题了
日期类型
#date,年月日
#datetime,年月日时分秒
#year,年
#time,时分秒
#例
create table student(
id int,
name char(4),
born_year year,
birth date,
study_time time,
reg_time datetime
);
mysql> desc student;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(4) | YES | | NULL | |
| born_year | year(4) | YES | | NULL | |
| birth | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| reg_time | datetime | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
insert student values(1,'syy','2019','2019-12-21','11:11:00','2019-12-21 11:11:00');
mysql> select * from student;
+------+------+-----------+------------+------------+---------------------+
| id | name | born_year | birth | study_time | reg_time |
+------+------+-----------+------------+------------+---------------------+
| 1 | syy | 2019 | 2019-12-21 | 11:11:00 | 2019-12-21 11:11:00 |
+------+------+-----------+------------+------------+---------------------+
枚举类型
#枚举类型
多选一
#例
create table user(
id int,
name char(16),
gender enum('male','female','others')
);
desc user;
+--------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | YES | | NULL | |
| gender | enum('male','female','others') | YES | | NULL | |
+--------+--------------------------------+------+-----+---------+-------+
insert user value(1,'syy','male');
select * from user;
+------+------------------+--------+
| id | name | gender |
+------+------------------+--------+
| 1 | syy | male |
+------+------------------+--------+
集合类型
#集合类型
多选多
#例
create table teacher(
id int,
name char(16),
gender enum('male','female','others'),
hobby set('read','sleep','sanna','eat')
);
desc teacher;
+--------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | YES | | NULL | |
| gender | enum('male','female','others') | YES | | NULL | |
| hobby | set('read','sleep','sanna','eat') | YES | | NULL | |
+--------+-----------------------------------+------+-----+---------+-------+
insert teacher value(1,'syy','male','read,eat'); #集合也可以只选一个
mysql> select * from teacher;
+------+------------------+--------+----------+
| id | name | gender | hobby |
+------+------------------+--------+----------+
| 1 | syy | male | read,eat |
+------+------------------+--------+----------+
约束条件
#约束条件
not null 非空（数据不能为空）
default 给某个数据设置默认值
unique 单列唯一（该列数据不能重复）
联合唯一（某一条数据的两列不能重复）
primary key 主键，非空且唯一，innodb存储引擎规定：一个表必须有一个主键，且一个表最多只能有一个主键，当没有指定主键的时候：
1.将表中非空且唯一的字段，自动升级主键
2.如果表中没有非空且唯一的字段的话，innodb存储引擎会自动添加一个隐藏的主键字段
#通常每张表里面都应该有一个id字段，并设置该字段为主键
#联合主键，多个字段联合起来作为表的一个主键，本质上还是一个主键
auto_increment 自增，不能单独使用，必须先设置键再设置自增
#例，非空
mysql> create table t1(id int not null);
mysql> insert t1 value(1),(null);
ERROR 1048 (23000): Column 'id' cannot be null
mysql> select * from t1;
Empty set (0.00 sec) #插入失败
#例，默认值
mysql> create table t2(id int default 999);
mysql> insert t2 value();
mysql> select * from t2;
+------+
| id |
+------+
| 999 |
+------+
#例，单列唯一
mysql> create table t3(id int unique);
mysql> insert t3 value(1),(1);
ERROR 1062 (23000): Duplicate（重复） entry '1' for key 'id
#例，联合唯一
mysql> create table t4(ip char(16),port int,unique(ip,port));
mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ip | char(16) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> insert t4 value('10.0.0.1',8080);
mysql> insert t4 value('10.0.0.1',8081);
mysql> insert t4 value('10.0.0.2',8080);
mysql> insert t4 value('10.0.0.1',8080);
ERROR 1062 (23000): Duplicate entry '10.0.0.1 -8080' for key 'ip'
#例，单列主键
mysql> create table t5(id int primary key);
mysql> desc t5;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> insert t5 value(1);
mysql> insert t5 value(null);
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert t5 value(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
#例，升级主键
mysql> create table t6(id int not null unique,name varchar(4));
mysql> desc t6;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
#例，联合主键
mysql> create table t7(ip varchar(16),port int,primary key(ip,port));
mysql> desc t7;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ip | varchar(16) | NO | PRI | | |
| port | int(11) | NO | PRI | 0 | |
+-------+-------------+------+-----+---------+-------+
#例，自增
#主键设置自增
mysql> create table t8(id int primary key auto_increment,name varchar(4));
mysql> desc t8;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(4) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
mysql> insert t8(name) value('syy'),('yyy');
mysql> select * from t8;
+----+------+
| id | name |
+----+------+
| 1 | syy |
| 2 | yyy |
+----+------+
mysql> delete from t8; #清空表数据，不会重置主键
mysql> insert t8(name) value('zyy');
mysql> select * from t8;
+----+------+
| id | name |
+----+------+
| 3 | zyy |
+----+------+
mysql> truncate t8; #清空表数据和表结构，会重置主键
mysql> insert t8(name) value('xyy');
mysql> select * from t8;
+----+------+
| id | name |
+----+------+
| 1 | xyy |
+----+------+
#唯一键设置自增
mysql> create table t9(id int unique auto_increment);
mysql> desc t9;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
mysql> insert t9 value(),(),();
mysql> select * from t9;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
表与表之间的关系（外键）
#员工表
id emp_name emp_gender dep_name dep_desc
1 jason male 外交部 形象代言人
2 egon female 教学部 教书育人
3 tank male 教学部 教书育人
4 jerry female 教学部 教书育人
5 kevin male 技术部 技术能力有限
#所有信息都记录在一张表所带来的的问题
1.表的结构不清晰
2.重复数据较多，代码冗余，浪费硬盘空间
3.表的扩展性差
#拆表，员工表
id emp_name emp_gender dep_id
1 jason male 1
2 egon female 2
3 tank male 2
4 jerry female 2
5 kevin male 3
#拆表，部门表
id dep_name dep_desc
1 外交部 形象代言人
2 教学部 教书育人
3 技术部 技术能力有限
#判断表与表之间的关系
使用换位思考的方法
数据的多对一，既是一对多，通常只说一对多
查找表之间的关系，一定要站在两个表的角度考虑
#员工到部门是多对一，部门到员工不是多对一，员工和部门是单向的多对一，单向的多对一/一对多统称一对多关系（等于多对一表关系）
两表之间的关系字段，称为外键字段，一对多的外键字段建在多的一方，多对多的外键字段建在第三张表，一对一的外键字段建在任意一方都可以，但是推荐建在查询频率较高的一方
#外键，foreign key，外键约束
外键就是两张表在代码层面上真正的关联
#在创建外键的时候，必须先创建被关联表，再创建关联表（即外键表）
#外键虽然能建立表关系，但同时也会增加数据相关的约束，造成被关联表的数据无法直接被修改
#外键通常都设置级联更新、级联删除
#表与表之间的关系只有三种，所以外键有三种
一对一，图书与出版社
一对多，部署与作者
多对多，作者与作者详情
#表关系的判断方法
判断表A的一条数据能否对应表B的多条数据
1.能
判断表B的一条数据能否对应表A的多条数据
1.能，两表之间的关系是'多对多'
2.不能，两表之间的关系是'一对多'
2.不能
判断表B的一条数据能否对应表A的一条数据
1.能，两表之间的关系是'一对一'
2.不能，两表之间没有关系
一对多
#创建外键
#先创建被关联表
mysql> create table dep(id int primary key auto_increment,dep_name varchar(32),dep_desc varchar(128));
#再创建关联表
mysql> create table emp(id int primary key auto_increment,emp_name varchar(64),emp_gender enum('male','female'),dep_id int,foreign key(dep_id) references dep(id));
mysql> desc emp;
+------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| emp_name | varchar(64) | YES | | NULL | |
| emp_gender | enum('male','female') | YES | | NULL | |
| dep_id | int(11) | YES | MUL | NULL | |
+------------+-----------------------+------+-----+---------+----------------+
mysql> desc dep;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| dep_name | varchar(32) | YES | | NULL | |
| dep_desc | varchar(128) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
#先插入被关联表数据
mysql> insert dep(dep_name,dep_desc) values('外交部','搞外交'),('教学部','教书育人'),('技术部','技术能力有限');
#再插入关联表数据
mysql> insert emp(emp_name,dep_id) values('jason',1),('egon',2),('tank',2),('kevin',3);
mysql> select * from emp;
+----+----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+----------+------------+--------+
| 1 | jason | NULL | 1 |
| 2 | egon | NULL | 2 |
| 3 | tank | NULL | 2 |
| 4 | kevin | NULL | 3 |
+----+----------+------------+--------+
mysql> select * from dep;
+----+-----------+--------------------+
| id | dep_name | dep_desc |
+----+-----------+--------------------+
| 1 | 外交部 | 搞外交 |
| 2 | 教学部 | 教书育人 |
| 3 | 技术部 | 技术能力有限 |
+----+-----------+--------------------+
#修改表数据
mysql> update dep set id=100 where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
mysql> update emp set dep_id=100 where id=1;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
#删除表数据
mysql> delete from dep where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
#删除表
mysql> drop table emp;
mysql> drop table dep;
#外键的级联更新、级联删除
#先创建被关联表
mysql> create table dep(id int primary key auto_increment,dep_name varchar(32),dep_desc varchar(128));
#再创建关联表
mysql> create table emp(id int primary key auto_increment,emp_name varchar(64),emp_gender enum('male','female'),dep_id int,foreign key(dep_id) references dep(id) on update cascade on delete cascade);
#先插入被关联表数据
mysql> insert dep(dep_name,dep_desc) values('外交部','搞外交'),('教学部','教书育人'),('技术部','技术能力有限');
#再插入关联表数据
mysql> insert emp(emp_name,dep_id) values('jason',1),('egon',2),('tank',2),('kevin',3);
mysql> select * from emp;
+----+----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+----------+------------+--------+
| 1 | jason | NULL | 1 |
| 2 | egon | NULL | 2 |
| 3 | tank | NULL | 2 |
| 4 | kevin | NULL | 3 |
+----+----------+------------+--------+
mysql> select * from dep;
+----+-----------+--------------------+
| id | dep_name | dep_desc |
+----+-----------+--------------------+
| 1 | 外交部 | 搞外交 |
| 2 | 教学部 | 教书育人 |
| 3 | 技术部 | 技术能力有限 |
+----+-----------+--------------------+
#修改表数据
mysql> update dep set id=200 where id=3;
mysql> select * from dep;
+-----+-----------+--------------------+
| id | dep_name | dep_desc |
+-----+-----------+--------------------+
| 1 | 外交部 | 搞外交 |
| 2 | 教学部 | 教书育人 |
| 200 | 技术部 | 技术能力有限 |
+-----+-----------+--------------------+
mysql> select * from emp;
+----+----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+----------+------------+--------+
| 1 | jason | NULL | 1 |
| 2 | egon | NULL | 2 |
| 3 | tank | NULL | 2 |
| 4 | kevin | NULL | 200 |
+----+----------+------------+--------+
#删除表数据
mysql> delete from dep where id=2;
mysql> select * from dep;
+-----+-----------+--------------------+
| id | dep_name | dep_desc |
+-----+-----------+--------------------+
| 1 | 外交部 | 搞外交 |
| 200 | 技术部 | 技术能力有限 |
+-----+-----------+--------------------+
mysql> select * from emp;
+----+----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+----------+------------+--------+
| 1 | jason | NULL | 1 |
| 4 | kevin | NULL | 200 |
+----+----------+------------+--------+
多对多
#book
id title price desc
1 jpm 888.88 不可描述
2 python 22222 变成屌丝
3 聊斋 999.99 魔幻世界
#author
id name age
1 jason 18
2 egon 66
#书籍和作者的关系是多对一，作者和书籍的关系也是多对一，两张表之间是多对多的关系
多对多关系的建立，必须手动创建第三张表，用来专门记录两张表之间的关系
#先创建两张普通的表，不设置外键
mysql> create table book(id int primary key auto_increment,title varchar(32),price int);
mysql> create table author(id int primary key auto_increment,name varchar(32),age int);
#再创建第三张表，设置外键
mysql> create table book_author(id int primary key auto_increment,book_id int,foreign key(book_id) references book(id) on update cascade on delete cascade,author_id int,foreign key(author_id) references author(id) on update cascade on delete cascade);
#查看表结构
mysql> desc book;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(32) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
mysql> desc author;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
mysql> desc book_author;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| book_id | int(11) | YES | MUL | NULL | |
| author_id | int(11) | YES | MUL | NULL | |
+-----------+---------+------+-----+---------+----------------+
#插入数据
mysql> insert book(title,price) values('jpm',199),('聊斋',299),('鸡神',999);
mysql> insert author(name,age) values('jason',18),('tank',88);
mysql> insert book_author(book_id,author_id) values(1,1),(1,2),(2,1),(3,1),(3,2);
mysql> select * from book;
+----+-----------+-------+
| id | title | price |
+----+-----------+-------+
| 1 | jpm | 199 |
| 2 | 聊斋 | 299 |
| 3 | 鸡神 | 999 |
+----+-----------+-------+
mysql> select * from author;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | jason | 18 |
| 2 | tank | 88 |
+----+-------+------+
mysql> select * from book_author;
+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
+----+---------+-----------+
#删除表数据
mysql> delete from author where id=1;
mysql> select * from book;
+----+-----------+-------+
| id | title | price |
+----+-----------+-------+
| 1 | jpm | 199 |
| 2 | 聊斋 | 299 |
| 3 | 鸡神 | 999 |
+----+-----------+-------+
mysql> select * from author;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | tank | 88 |
+----+------+------+
mysql> select * from book_author;
+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
| 2 | 1 | 2 |
| 5 | 3 | 2 |
+----+---------+-----------+
一对一
#一对一
一个表中的数据，跟另外一张表中的数据是一一对应的
#一对一表关系跟一对多表关系的语法结构完全一致，只不过需要给外键字段在加一个unique唯一性约束即可
#一对一应用场景1
#当表特别大的时候可以考虑拆分表,比如qq界面的用户名和其他信息,作者与作者详情
author
id name age authordetail_id
1 xxx 18 1
authordetail
phone addr
111 xxx
#一对一应用场景2
#招生咨询和学生
id name qq course
1 小林 111 python
2 小红 222 C
3 小芳 333 python
4 小黄 444 java
id name course
1 小林 python
2 小芳 python
#例，场景1代码实现
#先创建被关联表
mysql> create table authordetail(id int primary key auto_increment,phone int,addr char(255));
#再创建外键表
mysql> create table author(id int primary key auto_increment,name char(4),age int,authordetail_id int unique,foreign key(authordetail_id) references authordetail(id) on update cascade on delete cascade);
#查看表结构
mysql> desc author;
+-----------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(4) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| authordetail_id | int(11) | YES | UNI | NULL | |
+-----------------+---------+------+-----+---------+----------------+
mysql> desc authordetail;
+-------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| phone | int(11) | YES | | NULL | |
| addr | char(255) | YES | | NULL | |
+-------+-----------+------+-----+---------+----------------+
#插入表数据，先插入被关联表，再插入外键表
mysql> insert authordetail(phone,addr) values(110,'china'),(120,'USA'),(130,'Ac');
mysql> insert author(name,age,authordetail_id) values('jaso',18,1),('egon',88,2);
mysql> select * from author;
+----+------+------+-----------------+
| id | name | age | authordetail_id |
+----+------+------+-----------------+
| 1 | jaso | 18 | 1 |
| 2 | egon | 88 | 2 |
+----+------+------+-----------------+
mysql> select * from authordetail;
| id | phone | addr
| 1 | 110 | china
| 2 | 120 | USA
| 3 | 130 | Ac
SQL语句补充
alter
#修改表名
alter table 表名 rename 新表名;
#增加字段
alter table 表名 add 字段名 数据类型 [约束条件],
add 字段名 数据类型 [约束条件];
alter table 表名 add 字段名 数据类型 [约束条件] first;
alter table 表名 add 字段名 数据类型 [约束条件] after 字段名;
#删除字段
alter table 表名 drop 字段名;
#修改字段
#modify只能修改字段类型约束条件，不能修改字段名，但是change都可以
alter table 表名 modify 字段名 数据类型 [约束条件];
alter table 表名 change 旧字段名 新字段名 数据类型 [约束条件];
#SQL语句后面加上G，可以将要查看的表中的数据竖向排列
复制表
#使用select查询，结果也是一张表，可以看成虚拟表
#复制表
#注意，该方法只会复制表结构和表数据，不会复制主键、唯一键、外键、索引
create table 新表名 select * from 旧表名;
#只复制表结构
select * from 旧表名 where 1=2; #因为条件为假，所以查不到任何表数据
create table 新表名 select * from 旧表名 where 1=2;
create table 新表名 like 旧表名;
单表查询
where子句
#SQL语句的执行顺序
#客户端发送SQL语句到mysql服务端
mysql> select id,name from emp where id>3 and id<6;
#服务端执行SQL语句
from #从哪张表拿数据
where #筛选数据，一条一条的
group by #指定分组，默认一个表就是一组
having #筛选数据，一条一条的
select #筛选数据，一个字段一个字段的
distinct #去重
#返回结果给客户端
#创建表
mysql> 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 18,
hire_date date not null, #雇佣日期
post varchar(50), #岗位
post_comment varchar(100), #岗位描述
salary double(15,2), #薪水
office int, #部门
depart_id int #id
);
mysql> desc emp;
+--------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 18 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------+------+-----+---------+----------------+
#插入数据
#ps，如果在Windows系统中，插入中文字符，select的结果为空白的话，可以将所有字符编码统一设置为'gbk'
insert emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('aa','male',77,'20120101','teacher',10.100,401,1), #教学部
('bb','male',66,'20120201','teacher',9.99,401,1),
('cc','female',55,'20120301','teacher',8.98,401,1),
('dd','male',44,'20120401','teacher',7.97,401,1),
('ee','male',33,'20120501','teacher',6.96,401,1),
('ff','female',22,'20120601','teacher',5.95,401,1),
('gg','male',18,'20100101','sale',4.94,402,2), #销售部
('hh','male',19,'20100201','sale',3.93,402,2),
('ii','male',20,'20100301','sale',2.92,402,2),
('jj','male',21,'20100401','sale',1.91,402,2),
('kk','male',22,'20100501','sale',0.90,402,2),
('ll','male',23,'20200101','operation',4.89,403,3), #运营部
('mm','male',24,'20200201','operation',3.88,403,3),
('nn','male',25,'20200301','operation',2.87,403,3),
('oo','male',26,'20200401','operation',1.86,403,3),
('pp','male',27,'20200501','operation',0.85,403,3);
#1.查询id大于等于3小于等于6的数据
mysql> select * from emp where id >=3 and id <=6;
mysql> select * from emp where id between 3 and 6;
#2.查询薪资是2.92或者1.91或者0.90的数据
mysql> select * from emp where salary=2.92 or salary=1.91 or salary=0.90;
mysql> select * from emp where salary in (2.87,1.86,0.85);
#3.查询员工姓名中，包含o字母的员工姓名和薪资
mysql> select name,salary from emp where name like '%o%';
#4.查询员工姓名是由两个字符组成的员工姓名和薪资
mysql> select name,salary from emp where name like '__';
mysql> select name,salary from emp where char_length(name)=4;
#5.查询id小于3或者大于6的数据
mysql> select * from emp where id<3 or id>6;
mysql> select * from emp where id not between 3 and 6;
#6.查询薪资不是2.92或者1.91或者0.90的数据
mysql> select * from emp where salary not in (2.87,1.86,0.85);
#7.查询岗位描述为空的员工与岗位名（针对null不能用等号，只能用is）
mysql> select name,post from emp where post_comment = NULL; #错
mysql> select name,post from emp where post_comment is NULL;
mysql> select name,post from emp where post_comment is not NULL;
group by
#group by，分组
#分组之后，查询数据的最小单位是组，不会再默认展示组内的单条数据
#mysql中，分组之后只能查看分组的字段信息，无法直接获取其他的字段信息，但是可以通过其他的方法(聚合函数)间接的获取
#聚合函数有：max()、min()、avg()、sum()、count()、group_concat()
#聚合函数只能在SQL语句group by分组之后使用
#如果SQL没有分组，那么默认该表内的所有数据就是一组，所以仍可以使用所有的聚合函数（max,min,avg,sum,count,group_caoncat）
#1.按部门分组
mysql> select * from emp group by post;
#如果设置SQL严格模式，该语句报错，不设置的话不报错
#使用post对数据分组，默认显示每组的第一条数据
+----+------+----+-----------+--------------+--------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+----+-----------+--------------+--------+--------+-----------+
| 12 | ll | male | 23 | 2020-01-01 | operation | NULL | 4.89 | 403 | 3 |
| 7 | gg | male | 18 | 2010-01-01 | sale | NULL | 4.94 | 402 | 2 |
| 1 | aa | male | 77 | 2012-01-01 | teacher | NULL | 10.10 | 401 | 1 |
+----+------+----+-----------+--------------+--------+--------+-----------+
#查看SQL严格模式是否开启
mysql> show variables like '%mode%';
#设置SQL严格模式
mysql> set session sql_mode='STRICT_TRANS_TABLES,only_full_group_by';
mysql> set global sql_mode='STRICT_TRANS_TABLES,only_full_group_by';
#直接报错，这个时候只能查看分组字段数据
mysql> select * from emp group by post;
ERROR 1055 (42000): 'z6.emp.id' isn't in GROUP BY
mysql> select post from emp group by post;
+-----------+
| post |
+-----------+
| operation |
| sale |
| teacher |
+-----------+
#2.获取每个部门的最高薪资
mysql> select post,max(salary) from emp group by post;
+-----------+-------------+
| post | max(salary) |
+-----------+-------------+
| operation | 4.89 |
| sale | 4.94 |
| teacher | 10.10 |
+-----------+-------------+
#使用关键字as,给虚拟表字段起别名（as可以可以省略）
mysql> select post as '部门',max(salary) as '最高薪资' from emp group by post;
mysql> select post '部门',max(salary) '最高薪资' from emp group by post;
+-----------+--------------+
| 部门 | 最高薪资 |
+-----------+--------------+
| operation | 4.89 |
| sale | 4.94 |
| teacher | 10.10 |
+-----------+--------------+
#获取每个部门的最低薪资
mysql> select post as '部门',min(salary) as '最低薪资' from emp group by post;
+-----------+--------------+
| 部门 | 最低薪资 |
+-----------+--------------+
| operation | 0.85 |
| sale | 0.90 |
| teacher | 5.95 |
+-----------+--------------+
#获取每个部门的平均薪资
mysql> select post as '部门',avg(salary) as '平均薪资' from emp group by post;
+-----------+--------------+
| 部门 | 平均薪资 |
+-----------+--------------+
| operation | 2.870000 |
| sale | 2.920000 |
| teacher | 8.325000 |
+-----------+--------------+
#获取每个部门的薪资总和
mysql> select post as '部门',sum(salary) as '薪资总和' from emp group by post;
+-----------+--------------+
| 部门 | 薪资总和 |
+-----------+--------------+
| operation | 14.35 |
| sale | 14.60 |
| teacher | 49.95 |
#获取每个部门的人数
#在使用关键字count，统计分组内个数的时候，填写任意非空字段都可以完成计数，但是要使用唯一标识数据的字段（非空字段）
mysql> select post as '部门',count(id) as '总人数' from emp group by post;
+-----------+-----------+
| 部门 | 总人数 |
+-----------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 6 |
+-----------+-----------+
#3.查询分组之后的部门名称和每个部门下所有的名字
#group_concat(分组之后用)，不仅可以用来显示指定字段的数据，还有拼接字符串的作用
mysql> select post,group_concat(name) from emp group by post;
+-----------+--------------------+
| post | group_concat(name) |
+-----------+--------------------+
| operation | pp,oo,nn,mm,ll |
| sale | kk,jj,ii,hh,gg |
| teacher | ff,ee,dd,cc,bb,aa |
+-----------+--------------------+
#拼接字符串
mysql> select post,group_concat(name,'_DSB') from emp group by post;
+-----------+-------------------------------------------+
| post | group_concat(name,'_DSB') |
+-----------+-------------------------------------------+
| operation | pp_DSB,oo_DSB,nn_DSB,mm_DSB,ll_DSB |
| sale | kk_DSB,jj_DSB,ii_DSB,hh_DSB,gg_DSB |
| teacher | ff_DSB,ee_DSB,dd_DSB,cc_DSB,bb_DSB,aa_DSB |
+-----------+-------------------------------------------+
mysql> select post,group_concat(name,':',salary) from emp group by post;
+-----------+--------------------------------------------------+
| post | group_concat(name,':',salary) |
+-----------+--------------------------------------------------+
| operation | pp:0.85,oo:1.86,nn:2.87,mm:3.88,ll:4.89 |
| sale | kk:0.90,jj:1.91,ii:2.92,hh:3.93,gg:4.94 |
| teacher | ff:5.95,ee:6.96,dd:7.97,cc:8.98,bb:9.99,aa:10.10 |
+-----------+--------------------------------------------------+
#4.concat(不分组时用)，拼接字符串达到更好的显示效果
mysql> select name as 姓名,salary as 薪资 from emp;
+--------+--------+
| 姓名 | 薪资 |
+--------+--------+
| aa | 10.10 |
| bb | 9.99 |
| cc | 8.98 |
| dd | 7.97 |
| ee | 6.96 |
| ff | 5.95 |
| gg | 4.94 |
| hh | 3.93 |
| ii | 2.92 |
| jj | 1.91 |
| kk | 0.90 |
| ll | 4.89 |
| mm | 3.88 |
| nn | 2.87 |
| oo | 1.86 |
| pp | 0.85 |
+--------+--------+
mysql> select concat('name: ',name) as 姓名,concat('SAL: ',salary) as 薪资 from emp;
+----------+------------+
| 姓名 | 薪资 |
+----------+------------+
| name: aa | SAL: 10.10 |
| name: bb | SAL: 9.99 |
| name: cc | SAL: 8.98 |
| name: dd | SAL: 7.97 |
| name: ee | SAL: 6.96 |
| name: ff | SAL: 5.95 |
| name: gg | SAL: 4.94 |
| name: hh | SAL: 3.93 |
| name: ii | SAL: 2.92 |
| name: jj | SAL: 1.91 |
| name: kk | SAL: 0.90 |
| name: ll | SAL: 4.89 |
| name: mm | SAL: 3.88 |
| name: nn | SAL: 2.87 |
| name: oo | SAL: 1.86 |
| name: pp | SAL: 0.85 |
+----------+------------+
#5.concat_ws(分组只有用),使用指定符号拼接数据
mysql> select concat_ws(':',name,sex,age) from emp2;
+-----------------------------+
| concat_ws(':',name,sex,age) |
+-----------------------------+
| jason:male:18 |
| egon:female:48 |
| kevin:male:38 |
| nick:female:28 |
| jerry:female:18 |
+-----------------------------+
#补充
#as既可以给字段起别名，也可以给表起别名
mysql> select emp.id,emp.name from emp as t1;
ERROR 1054 (42S22): Unknown column 'emp.id' in 'field list'
mysql> select t1.id,t1.name from emp as t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
| 6 | ff |
| 7 | gg |
| 8 | hh |
| 9 | ii |
| 10 | jj |
| 11 | kk |
| 12 | ll |
| 13 | mm |
| 14 | nn |
| 15 | oo |
| 16 | pp |
+----+------+
#查询之四则运算
mysql> select name,salary*13 from emp;
+------+-----------+
| name | salary*13 |
+------+-----------+
| aa | 131.30 |
| bb | 129.87 |
| cc | 116.74 |
| dd | 103.61 |
| ee | 90.48 |
| ff | 77.35 |
| gg | 64.22 |
| hh | 51.09 |
| ii | 37.96 |
| jj | 24.83 |
| kk | 11.70 |
| ll | 63.57 |
| mm | 50.44 |
| nn | 37.31 |
| oo | 24.18 |
| pp | 11.05 |
+------+-----------+
#group by可以与where一起使用
mysql> select post,group_concat(salary) from emp where id <5 group by post;
+---------+----------------------+
| post | group_concat(salary) |
+---------+----------------------+
| teacher | 10.10,9.99,8.98,7.97 |
+---------+----------------------+
#聚合函数只能在group by之后使用，因为post不是分组依据，所以报错
mysql> select post,max(salary) from emp;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
#因为整个表默认就是一组，所以仍可以使用所有聚合函数
mysql> select max(salary) from emp;
+-------------+
| max(salary) |
+-------------+
| 10.10 |
+-------------+
练习题
#刚开始查询表，一定要按照最基本的步骤
1.先确定是哪张表
2.再确定这张表有没有限制条件（where）
3.再确定是否需要分类（xx下，xx中，xx内，xx包含的，各xx）
4.最后再确定需要什么字段对应的信息
#1.查询岗位名以及岗位包含的所有员工的名字
mysql> select post,group_concat(name) from emp group by post;
+-----------+--------------------+
| post | group_concat(name) |
+-----------+--------------------+
| operation | pp,oo,nn,mm,ll |
| sale | kk,jj,ii,hh,gg |
| teacher | ff,ee,dd,cc,bb,aa |
+-----------+--------------------+
#2.查询岗位名以及各岗位内包含的员工的个数
mysql> select post,count(id) from emp group by post;
+-----------+-----------+
| post | count(id) |
+-----------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 6 |
+-----------+-----------+
#3.查询公司内男员工和女员工的个数
mysql> select sex,count(id) from emp group by sex;
+--------+-----------+
| sex | count(id) |
+--------+-----------+
| male | 14 |
| female | 2 |
+--------+-----------+
#4.查询岗位名以及各岗位的平均薪资
mysql> select post,avg(salary) from emp group by post;
+-----------+-------------+
| post | avg(salary) |
+-----------+-------------+
| operation | 2.870000 |
| sale | 2.920000 |
| teacher | 8.325000 |
+-----------+-------------+
#5.查询岗位名以及各岗位的最高薪资
mysql> select post,max(salary) from emp group by post;
+-----------+-------------+
| post | max(salary) |
+-----------+-------------+
| operation | 4.89 |
| sale | 4.94 |
| teacher | 10.10 |
+-----------+-------------+
#6.查询岗位名以及各岗位的最低薪资
mysql> select post,min(salary) from emp group by post;
+-----------+-------------+
| post | min(salary) |
+-----------+-------------+
| operation | 0.85 |
| sale | 0.90 |
| teacher | 5.95 |
+-----------+-------------+
#7.查询男员工和女员工的平均薪资，女员工与女员工的平均薪资
mysql> select sex,avg(salary) from emp group by sex;
+--------+-------------+
| sex | avg(salary) |
+--------+-------------+
| male | 4.569286 |
| female | 7.465000 |
+--------+-------------+
#8.查询各部门年龄在30岁以上的员工的平均工资
mysql> select post,avg(salary) from emp where age >= 30 group by post;
+---------+-------------+
| post | avg(salary) |
+---------+-------------+
| teacher | 8.800000 |
+---------+-------------+
having
#having
having跟where的作用是一模一样的，都是用来筛选数据的
where在简单的SQL语句中可以直接使用，但是不能在复杂的SQL语句中使用，如group by之后
如果想要在group by之后，再次对数据进行筛选，只能使用关键字having
having只能在group by之后使用
1.查询各部门年龄在30岁以上的员工的平均工资,并且保留平均薪资大于8的部门
mysql> insert emp(name,sex,age,hire_date,post,salary,office,depart_id) values('xx','female',31,'20120101','other',1,401,10);
mysql> select post,avg(salary) from emp where age >= 30 group by post;
+---------+-------------+
| post | avg(salary) |
+---------+-------------+
| other | 1.000000 |
| teacher | 8.800000 |
+---------+-------------+
2 rows in set (0.00 sec)
mysql> select post,avg(salary) from emp where age >= 30 group by post having avg(salary)>8;
+---------+-------------+
| post | avg(salary) |
+---------+-------------+
| teacher | 8.800000 |
+---------+-------------+
distinct
#distinct去重
两条数据必须是一模一样，才能使用关键字distinct去重
对重复展示的数据进行去重，保留一条
distinct必须紧跟在select之后，鉴于SQL语句的执行步骤
#例
mysql> select sex from emp;
+--------+
| sex |
+--------+
| male |
| male |
| female |
| male |
| male |
| female |
| male |
| male |
| male |
| male |
| male |
| male |
| male |
| male |
| male |
| male |
| female |
+--------+
17 rows in set (0.00 sec)
mysql> select distinct sex from emp;
+--------+
| sex |
+--------+
| male |
| female |
+--------+
order by
#order by，排序
order by 升序，默认，asc
order by desc 降序,desc
#例
#升序
mysql> select id,name,salary from emp order by salary;
mysql> select id,name,salary from emp order by salary asc;
+----+------+--------+
| id | name | salary |
+----+------+--------+
| 16 | pp | 0.85 |
| 11 | kk | 0.90 |
| 19 | xx | 1.00 |
| 15 | oo | 1.86 |
| 10 | jj | 1.91 |
| 14 | nn | 2.87 |
| 9 | ii | 2.92 |
| 13 | mm | 3.88 |
| 8 | hh | 3.93 |
| 12 | ll | 4.89 |
| 7 | gg | 4.94 |
| 6 | ff | 5.95 |
| 5 | ee | 6.96 |
| 4 | dd | 7.97 |
| 3 | cc | 8.98 |
| 2 | bb | 9.99 |
| 1 | aa | 10.10 |
+----+------+--------+
#降序
mysql> select id,name,salary from emp order by salary desc;
+----+------+--------+
| id | name | salary |
+----+------+--------+
| 1 | aa | 10.10 |
| 2 | bb | 9.99 |
| 3 | cc | 8.98 |
| 4 | dd | 7.97 |
| 5 | ee | 6.96 |
| 6 | ff | 5.95 |
| 7 | gg | 4.94 |
| 12 | ll | 4.89 |
| 8 | hh | 3.93 |
| 13 | mm | 3.88 |
| 9 | ii | 2.92 |
| 14 | nn | 2.87 |
| 10 | jj | 1.91 |
| 15 | oo | 1.86 |
| 19 | xx | 1.00 |
| 11 | kk | 0.90 |
| 16 | pp | 0.85 |
+----+------+--------+
#先比较age字段(升序)，对于age字段相同的数据，再比较salary字段(升序)
mysql> select id,name,age,salary from emp order by age,salary;
+----+------+-----+--------+
| id | name | age | salary |
+----+------+-----+--------+
| 7 | gg | 18 | 4.94 |
| 8 | hh | 19 | 3.93 |
| 9 | ii | 20 | 2.92 |
| 10 | jj | 21 | 1.91 |
| 11 | kk | 22 | 0.90 | #
| 6 | ff | 22 | 5.95 |
| 12 | ll | 23 | 4.89 |
| 13 | mm | 24 | 3.88 |
| 14 | nn | 25 | 2.87 |
| 15 | oo | 26 | 1.86 |
| 16 | pp | 27 | 0.85 |
| 19 | xx | 31 | 1.00 |
| 5 | ee | 33 | 6.96 |
| 4 | dd | 44 | 7.97 |
| 3 | cc | 55 | 8.98 |
| 2 | bb | 66 | 9.99 |
| 1 | aa | 77 | 10.10 |
+----+------+-----+--------+
#先比较age字段(升序)，对于age字段相同的数据，再比较salary字段(降序)
mysql> select id,name,age,salary from emp order by age asc,salary desc;
+----+------+-----+--------+
| id | name | age | salary |
+----+------+-----+--------+
| 7 | gg | 18 | 4.94 |
| 8 | hh | 19 | 3.93 |
| 9 | ii | 20 | 2.92 |
| 10 | jj | 21 | 1.91 |
| 6 | ff | 22 | 5.95 | #
| 11 | kk | 22 | 0.90 |
| 12 | ll | 23 | 4.89 |
| 13 | mm | 24 | 3.88 |
| 14 | nn | 25 | 2.87 |
| 15 | oo | 26 | 1.86 |
| 16 | pp | 27 | 0.85 |
| 19 | xx | 31 | 1.00 |
| 5 | ee | 33 | 6.96 |
| 4 | dd | 44 | 7.97 |
| 3 | cc | 55 | 8.98 |
| 2 | bb | 66 | 9.99 |
| 1 | aa | 77 | 10.10 |
+----+------+-----+--------+
#例，统计各部门年龄在10岁以上的员工的平均工资，并且保留平均工资大于0.9的部门，然后对平均工资进行排序
mysql> select post,avg(salary) from emp where age >10 group by post having avg(salary)>0.9 order by avg(salary);
+-----------+-------------+
| post | avg(salary) |
+-----------+-------------+
| other | 1.000000 |
| operation | 2.870000 |
| sale | 2.920000 |
| teacher | 8.325000 |
+-----------+-------------+
limit
#limit
#限制展示数据的条数
#使用场景，分页
#例
mysql> select id,name from emp limit 5;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
+----+------+
#limit(x,y),x表示起始位置（不包括起始位置），y表示要展示数据的条数，
mysql> select id,name from emp limit 0,2;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
mysql> select id,name from emp limit 2,2;
+----+------+
| id | name |
+----+------+
| 3 | cc |
| 4 | dd |
+----+------+
#查询工资最高的人的详细信息
mysql> select * from emp order by salary limit 1;
+----+------+----+-----------+--------------+--------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+----+-----------+--------------+--------+--------+-----------+
| 16 | pp | male | 27 | 2020-05-01 | operation | NULL | 0.85 | 403 | 3 |
+----+------+----+-----------+--------------+--------+--------+-----------+
正则
#在编程中，只要看到一reg开头的单词，基本上都是跟正则有关
#SQL语句中使用正则，要使用关键字regexp声明
mysql> select * from emp where name regexp '^a.*(a|z)$';
+----+------+------+--------+--------------+--------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+-----+---------+--------------+--------+--------+-----------+
| 1 | aa | male | 77 | 2012-01-01 | teacher | NULL | 10.10 | 401 | 1 |
+----+------+------+--------+--------------+--------+--------+-----------+
多表查询
#创建多个表
mysql> create table dep(
id int,
name varchar(20)
);
mysql> 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
);
#插入数据
mysql> insert dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
mysql> insert emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',38,201),
('nick','female',28,202),
('jerry','female',18,204);
#表查询分为两类
1.联表查询
2.子查询
#手动联表查询
#笛卡尔积效果
mysql> select * from emp,dep;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 1 | jason | male | 18 | 200 | 201 | 人力资源 |
| 1 | jason | male | 18 | 200 | 202 | 销售 |
| 1 | jason | male | 18 | 200 | 203 | 运营 |
| 2 | egon | female | 48 | 201 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 2 | egon | female | 48 | 201 | 202 | 销售 |
| 2 | egon | female | 48 | 201 | 203 | 运营 |
| 3 | kevin | male | 38 | 201 | 200 | 技术 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 202 | 销售 |
| 3 | kevin | male | 38 | 201 | 203 | 运营 |
| 4 | nick | female | 28 | 202 | 200 | 技术 |
| 4 | nick | female | 28 | 202 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 4 | nick | female | 28 | 202 | 203 | 运营 |
| 5 | jerry | female | 18 | 204 | 200 | 技术 |
| 5 | jerry | female | 18 | 204 | 201 | 人力资源 |
| 5 | jerry | female | 18 | 204 | 202 | 销售 |
| 5 | jerry | female | 18 | 204 | 203 | 运营 |
+----+-------+--------+------+--------+------+--------------+
#联表查询中，无论是select后面的字段，还是where后面的字段，都要使用'表名.字段名'的格式
mysql> select * from emp,dep where emp.dep_id=dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
+----+-------+--------+------+--------+------+--------------+
#查询部门名为技术的员工信息
mysql> select * from emp,dep where emp.dep_id=dep.id and dep.name='技术';
+----+-------+------+------+--------+------+--------+
| id | name | sex | age | dep_id | id | name |
+----+-------+------+------+--------+------+--------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
+----+-------+------+------+--------+------+--------+
联表查询
#专门联表的方法
#1.内连接（inner join）,只取两张表有对应关系的记录(没有对应关系的两表数据直接剔除)
mysql> 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 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
+----+-------+--------+------+--------+------+--------------+
mysql> select * from emp inner join dep on emp.dep_id=dep.id where dep.name='技术';
+----+-------+------+------+--------+------+--------+
| id | name | sex | age | dep_id | id | name |
+----+-------+------+------+--------+------+--------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
+----+-------+------+------+--------+------+--------+
#2.左连接（left join），在内连接的基础上，保留左表没有对应关系的数据
mysql> 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 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 5 | jerry | female | 18 | 204 | NULL | NULL | #
+----+-------+--------+------+--------+------+--------------+
#3.右连接（right join），在内连接的基础上，保留右表没有对应关系的数据
mysql> 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 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 | #
+------+-------+--------+------+--------+------+--------------+
#4.全连接（union），在内连接的基础上，保留左、右表没有对应关系的数据
mysql> 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 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 5 | jerry | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+-------+--------+------+--------+------+--------------+
#这四种联表的方法以及现象，是对两表原始数据的合并，本质就是两表合为一表，后面可以对这个虚拟表直接操作
子查询
#子查询
将一张表的查询结果，作为另一张表的查询条件，这样的SQL语句称为子查询
#as
1.可以给表起别名
2.可以给虚拟表起别名
3.可以给字段起别名
#例1，员工Jason所在的部门？子查询如下
mysql> select * from emp;
+----+-------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-------+--------+------+--------+
| 1 | jason | male | 18 | 200 | #200
| 2 | egon | female | 48 | 201 |
| 3 | kevin | male | 38 | 201 |
| 4 | nick | female | 28 | 202 |
| 5 | jerry | female | 18 | 204 |
+----+-------+--------+------+--------+
mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 | #可以看到Jason所在部门为'技术部'
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
#子查询完整语句
mysql> select name from dep where id=(select dep_id from emp where name='jason');
+--------+
| name |
+--------+
| 技术 |
+--------+
#例2，查询每个部门最新入职的员工（先查询每个部门最先入职的员工，再按部门对应上连表查询）
select t1.id,t1.name,t1.hire_data,t1.post,t2.* from emp as t1
inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on
t1.post=t2.post
where t1.hire_date=t2.max_date;
#规律
表查询的结果，可以作为其他表的查询条件，也可以通过起别名的方式把他作为一个虚拟表，去跟其他的表做关联查询
#例3，查询平均年龄在25岁以上的部门名
#联表
mysql> select dep.name,avg(age)
from emp
inner join dep
on
emp.dep_id=dep.id
group by dep.name
having avg(age)>25;
+--------------+----------+
| name | avg(age) |
+--------------+----------+
| 人力资源 | 43.0000 |
| 销售 | 28.0000 |
+--------------+----------+
#子查询
mysql> select name from dep where id in
(select dep_id from emp group by dep_id having avg(age)>25);
+--------------+
| name |
+--------------+
| 人力资源 |
| 销售 |
+--------------+
exists(了解)
#关键字exists表示存在
在使用关键字exists时，内层查询语句不返回查询的数据，而是返回一个真假值True/False
当返回True时，外层查询语句将进行查询
当返回False时，外层查询语句不进行查询
外层查询和内存查询，除了exists之外，并没有直接的代码联系
#例
#子查询为True，则查询
mysql> select dep_id from emp where exists(select id from dep where id>201);
+--------+
| dep_id |
+--------+
| 200 |
| 201 |
| 201 |
| 202 |
| 200 |
+--------+
#子查询为False，则不查询
mysql> select dep_id from emp where exists(select id from dep where id>2011);
Empty set (0.00 sec)
Navicat
navicat下载
参考网站
navicat使用
#数据库软件之所以可以操作数据库，原理是因为该软件把UI操作转化对应的SQL语句，发送到mysql服务端执行，再返回结果到该软件
#ORM,对象关系映射
把一个复杂的功能映射成一个简单的接口，在UI界面展示
能够让一个不会SQL语句的程序员，简单快速的操作mysql
类 >>> 表
对象 >>> 表中的一条数据
对象获取属性 >>> 表中的某个字段对应的一条数据
#navicat这款软件是很强大的
它可以连接多个版本的数据库，这一点打开navicat点击左上角的'连接'就可以看到了
连接本地的mysql之后，默认的连接名为'localhost_3306'
双击'localhost_3306',相当于 show databases;
双击'库',相当于 use database_name;
双击'表',相当于 show tables;
双击'table_name'，相当于 select * from table_name;
单击'表',鼠标右键选择'设计表'，相当于，desc table_name;
单击'localhost_3306',鼠标右键选择'新建数据库'，指定库名、字符集（utf8mb4支持存储表情）
双击新建的库，选择'表'，鼠标右键选择'新建表'，指定表名、类型、约束条件...等（设置键即主键：空格键、左键，设置或取消，记得主键一定要设置自增）
#保存
表结构的修改需要手动保存，表数据的修改、外键的创建自动保存（界面左下角如果有√，修改数据之后记得点一下）
#逆向数据库到模型
可以更好的展示表之间的关系
单击库，右键选择'逆向数据库到模型'
#模型
可以更方便的处理表之间的关系
#转储
可以在不同的mysql数据库之间快速的传递'库数据'
单击库，右键选择'转储SQL文件'(记得刷新'表')
#SQL
在navicat也可以写SQL语句
选择库，点击查询，编辑SQL语句，点击运行即可
navicat练习
练习题
#1.查询所有的课程的名称，以及对应的任课老师姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
#2.查询平均成绩大于80分的同学的姓名和平均成绩
#先联表，再分组
SELECT
student.sname,
AVG( score.num )
FROM
student
INNER JOIN score ON student.sid = score.sid
GROUP BY
student.sname
HAVING
AVG( score.num )> 80;
#先分组，再联表
SELECT
student.sname,
t1.f1
FROM
student
INNER JOIN ( SELECT student_id, AVG( num ) AS f1 FROM score GROUP BY student_id HAVING AVG( num )> 80 ) AS t1 ON student.sid = t1.student_id;
#3.查询没有报李平老师课的学生姓名
#报李平老师课的学生（子查询）
SELECT
sname AS '李平老师的学生'
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' )));
#没有报李平老师课的学生（子查询）
SELECT
sname AS '不是李平老师的学生'
FROM
student
WHERE
sid NOT IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' )));
#报李平老师课的学生（联表查询）
SELECT
student.sname
FROM
student
INNER JOIN (
SELECT DISTINCT
student_id
FROM
score
INNER JOIN ( SELECT course.cid FROM course INNER JOIN teacher ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' ) AS t1 ON score.course_id = t1.cid
) AS t2 ON student.sid = t2.student_id;
#没有报李平老师课的学生（联表查询）
SELECT
sname AS '不是李平老师的学生'
FROM
student
WHERE
sname NOT IN (
SELECT
student.sname
FROM
student
INNER JOIN (
SELECT DISTINCT
student_id
FROM
score
INNER JOIN ( SELECT course.cid FROM course INNER JOIN teacher ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' ) AS t1 ON score.course_id = t1.cid
) AS t2 ON student.sid = t2.student_id
);
#4.查询没有同时选修物理课程和体育课程的学生姓名
#查询至少选修物理课程和体育一门课程的学生姓名
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
score.student_id
FROM
course
INNER JOIN score ON course.cid = score.course_id
WHERE
course.cname IN ( '物理', '体育' ));
#查询没有只选修物理课程和体育一门课程的学生姓名（通过count，区分种类）
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
cname IN ( '物理', '体育' ))
GROUP BY
student_id
HAVING
COUNT( course_id )= 1
);
#5.查询挂科超过两门（包括两门）的学生姓名和班级
SELECT
t1.sname,
class.caption
FROM
class
INNER JOIN (
SELECT
*
FROM
student
WHERE
sid = ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( num ) > 1 )) AS t1 ON class.cid = t1.class_id;
#总结
select ...as...
from table_name ...
inner join ... on ...=...
where ...=...
group by ...
having ...;
#where后面的等号后面只能跟一个确定的值，on后面的等号的后面可以跟多个值
#使用as给虚拟表起别名，在多表联查中的作用很重要，可以让多张表中的数据在一张表中展示指定的字段（as前面的虚拟表必须加括号）
#要使用虚拟表中的聚合函数字段，必须使用as给虚拟表中的聚合函数字段起别名
python操作mysql
#python操作mysql需要使用pymysql模块
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '1',
database = 'test3',
charset = 'utf8' #不要加-
)
#生成一个游标对象，以字典的形式返回表数据，即{'字段':'数据'}
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = 'select * from test3.t3'
#执行传入的SQL语句，有返回值，返回值为执行SQL语句得到的数据的条数
cursor.execute(sql)
#迭代器，返回值为一条表数据
print(cursor.fetchone())
print(cursor.fetchone())
# cursor.scroll(1,'absolute') #移动光标，相对于起始位置向后移动几位
# cursor.scroll(-1,'relative') #移动光标，相对于当前位置向后移动几位
#一次性的获取所有的表数据，在列表中
print(cursor.fetchall())
{'id': 1, 'age': 18}
{'id': 2, 'age': 19}
[{'id': 3, 'age': 20}]
SQL注入问题
#SQL注入问题
SQL注入问题就是利用注释等具有特殊意义的符号，来完成一些骚操作
禁止用户输入含有特殊符号的用户名和密码，可以有效的防止SQL注入问题
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '1',
database = 'test3',
charset = 'utf8' #不要加-
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
while type:
username = input('请输入用户名>>>: ').strip()
password = input('请输入用户密码>>>: ').strip()
sql = "select * from user where name='%s' and password='%s'"%(username,password)
res = cursor.execute(sql)
if res:
print(cursor.fetchall())
else:
print('用户名或密码输入错误')
请输入用户名>>>: jason' -- s
请输入用户密码>>>:
[{'id': 1, 'name': 'jason', 'password': '123'}]
请输入用户名>>>: s' or 1=1 -- s
请输入用户密码>>>:
[{'id': 1, 'name': 'jason', 'password': '123'}, {'id': 2, 'name': 'egon', 'password': '123'}, {'id': 3, 'name': 'tank', 'password': '123'}, {'id': 4, 'name': 'kevin', 'password': '123'}]
#SQL注入问题的解决方法
使用python代码来操作mysql，为了避免SQL注入的问题，关键的数据（用户名、密码等），不能手动拼接SQL语句，要使用excute()来拼接
excute(sql,(arg1,arg2)),会自动识别sql语句中的%s，自动替换
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '1',
database = 'test3',
charset = 'utf8' #不要加-
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
while type:
username = input('请输入用户名>>>: ').strip()
password = input('请输入用户密码>>>: ').strip()
sql = "select * from user where name=%s and password=%s" #注意格式
res = cursor.execute(sql,(username,password))
if res:
print(cursor.fetchall())
else:
print('用户名或密码输入错误')
python操作mysql之增删改
#python操作mysql之增删改的操作，必须在代码的结尾加上commit()
#手动commit
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '1',
database = 'test3',
charset = 'utf8' #不要加-
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# sql = "insert user(name,password) values('jerry','123')"
# sql = 'update user set name="jasonhh" where id="1"'
sql = "delete from user where id='6142'"
cursor.execute(sql)
conn.commit()
#设置自动commit
#配置自动commit之后，python操作mysql之增删改的操作都不需要再手动commit
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '1',
database = 'test3',
charset = 'utf8', #不要加-
autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# sql = "insert user(name,password) values('jerry','123')"
# sql = 'update user set name="jasonhh" where id="1"'
sql = "delete from user where id='6142'"
cursor.execute(sql)
视图
#1.什么是视图
视图就是通过查询的得到一张虚拟表，然后保存下来，下次直接使用即可，这个表就叫做视图
#视图对应的文件只有一个table_name.frm，里面记录这视图的表结构，视图的数据还是来源于原来的表
#不要改动视图中的数据，因为可能会影响到其他的表（#不能改）
#2.为什么要使用视图
如果需要重复的使用一张虚拟表，使用了视图的话可以不用频繁的查询
#3.怎么使用视图
create VIEW teacher_course as SELECT * FROM teacher INNER JOIN course on teacher.tid=course.teacher_id;
show tables;
select * from teacher_course;
#工作中一般不使用视图来写业务逻辑的原因
1.无法对视图进行'增删改'，容易造成业务逻辑混乱
2.视图文件占用磁盘空间
触发器
#触发器
当你对指定表的数据进行'增删改'的时候，条件满足则自动触发触发器的运行
#mysql默认提示符
可以使用关键字delimiter,修改mysql默认提示符
delimiter $$
使用关键字delimiter修改mysql默认提示符的行为，只对'当前窗口有效'
#根据触发器与SQL语句的作用先后，触发器分为6种
1.增前
2.增后
3.删前
4.删后
5.改前
6.改后
#触发器语法结构
delimiter $$
create trigger tri_before/after_insert/delete/update_table_name
after/before
insert/delete/update on
table_name
for each row
begin
sql语句;
end$$
delimiter ;
#例
#再向cmd表插入数据之后，如果success字段为no，则向errorlog表插入记录
#创建表cmd
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR ( 32 ),
priv CHAR ( 10 ),
cmd CHAR ( 64 ),
sub_time datetime, #cmd提交时间
success enum ( 'yes', 'no' )); #0表示cmd执行失败
#创建表errlog
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR ( 64 ),
err_time datetime );
#创建触发器（条件触发器）
delimiter $$
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
IF
NEW.success = 'no' THEN
INSERT errlog ( err_cmd, err_time )
VALUES
( NEW.cmd, NEW.sub_time );
END IF;
END $$
delimiter ;
#向cmd表插入数据
INSERT cmd ( USER, priv, cmd, sub_time, success )
VALUES
( 'syy', '0751', 'ls -l /etc', NOW(), 'yes' ),
( 'syy', '0751', 'cat /etc/password', NOW(), 'no' ),
( 'ee', '0755', 'useradd xxx', NOW(), 'no' ),
( 'ee', '0755', 'ps aux', NOW(), 'yes' );
#查看errlog表中的记录
SELECT * from errlog;
1 cat /etc/password 2020-12-28 09:47:02
2 useradd xxx 2020-12-28 09:47:02
#删除触发器
drop TRIGGER tri_after_insert_cmd;
#mysql中，查看触发器的两种方法
1.show TRIGGERS;
2.select * from information_schema.`TRIGGERS`;
事务
#事务
事务包含多条SQL语句，这些SQL语句要么同时成功，要么全部失败（只要有一台SQL语句失败 ）
#事务的四大特性：ACID
A：原子性
一个事务是一个不可分割的工作单位，事务中包含的多个操作，要么都做，要么都不做
C：一致性
事务必须是使数据库从一个一致状态编导另一个一致状态，一致性与原子性是密切相关的
I：隔离性
一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰
D：持久性
持久性也称永久性，指一个事务一旦提交它对数据库中的数据就应该是永久性的，接下来的其他操作或故障不应该对其有任何影响
#开启事务
start transaction
#事务回滚
rollback
#永久性更改
commit
#开启事务之后，只要没有执行commit操作，数据就没有真正刷新到硬盘
#commit的时候，会检测事务操作是否完整，不完整的话会回到上一个状态，如果完整就执行commit操作，刷新数据到硬盘，更新内存中的数据
#例
CREATE TABLE USER (
id INT PRIMARY KEY auto_increment,
NAME CHAR ( 32 ),
balance INT
);
INSERT USER(NAME, balance) VALUES
( 'syy', 1000 ),
( 'egon', 1000 ),
( 'jason', 1000 );
#修改数据之前先开启事务操作
start transaction;
#修改数据
update user set balance=900 where name='syy'; #买东西支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='jason'; #卖家拿走90元
#查看数据
select * from user;
1 syy 900
2 egon 1010
3 jason 1090
#回滚操作（没有commit，也没有rollback的话，自动commit）
rollback;
#验证数据
select * from user;
1 syy 900
2 egon 1010
3 jason 1090
#python代码解释事务
try:
update user set balance=900 where name='syy';
update user set balance=1010 where name='egon';
update user set balance=1090 where name='jason';
except 异常:
rollback;
else:
commit;
存储过程
#存储过程
存储过程就类似于python中的自定义函数，内部封装了操作数据库的SQL语句，后续想要实现相应的操作，主需要调用存储过程即可
#存储过程在哪个库下面创建，就只能在对应的库下面才能使用（可以在别的库下使用：call test.p_name(m,n,@args);）
#1.创建'无参''存储过程'
delimiter $$
create proceduer p_name()
begin
select * from user;
end $$
delimiter ;
#调用存储过程
call p1();
#2.创建'有参''存储过程'
delimiter $$
create proceduer p_name(
in m int, #不能被返回
in n int,
out res int #可以被返回
#inout xxx int #既可以进，又可以出
)
begin
select * from user;
end $$
delimiter ;
#例
#res类似于标志位，用来标识存储器是否执行成功
delimiter $$
create proceduer p1(
in m int,
in n int,
out res int
)
begin
select tname from teacher where tid > m and tid < n;
set res=0;
end $$
delimiter ;
set @res=10; #设置变量
SELECT @res; #查看变量
CALL p1(1,5,@res); #调用有参'存储过程'
SELECT @res; #再次查看变量
#使用pymysql使用存储过程
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '1',
database = 'test',
charset = 'utf8', #不要加-
autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
#调用存储过程，相当于navicate中的：call p1(1,5,@res)
cursor.callproc('p1',(1,5,10)) #内部自动用变量名存储了对应的值（@_存储过程名_索引值：@_p1_0=1,@_p1_1=5,@_p1_2=10）
print(cursor.fetchall())
cursor.execute('selete @_p1_0')
print(cursor.fetchall())
cursor.execute('selete @_p1_1')
print(cursor.fetchall())
cursor.execute('selete @_p1_2')
print(cursor.fetchall())
内置函数
参考网站
#
#例
create table blog(id int primary key auto_increment,name char(32),sub_time datetime);
insert blog(name,sub_time) values
('第一篇','2020-11-11 11:11:11'),
('第二篇','2020-11-11 11:11:12'),
('第三篇','2020-11-11 11:11:13');
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) |
2020-11 2 |
流程控制
#if条件语句
delimiter //
create procedure proc_if()
begin
declare i int default 0;
if i=1 then
select 1;
elseif i=2 then
select 2;
else
select 7;
end //
delimiter ;
#while循环
delimiter //
create procedure proc_while()
begin
declare num int;
set num=0;
while num < 10 DO
select num;
set num = num + 1;
end while;
end //
delimiter ;
索引
#索引
数据是存在于硬盘上的，拿查询数据不可避免的需要进行IO操作
索引在mysql中也叫做键，是存储引擎用于快速找到记录的一种数据结构
#索引的本质：通过不断的缩小数据的范围，来筛选出想要的结果，同时把随机的事件变成顺序的事件，总而言之，通过索引机制，我们总是可以用同一种查找方式来锁定数据
innodb存储引擎中，表索引和表数据在同一个文件中
在表中有大数据的前提下，创建索引的速度会很慢，索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
#索引的种类
1.主键索引 primary key
2.唯一键索引 unique key
3.普通索引 index key
#注意，外键（foreign key）不是用来加速查询用的
#上面三种key，前两种除了有加速查询的效果之外，还有额外的约束条件（主键索引：非空且唯一，唯一键索引：唯一），而index key没有任何约束功能，只会加速查询