mysql数据库与python

那个萝卜在飘 2021-01-05 17:10:24
Python 数据库 Mysql 数据


数据库

参考网站

知乎

#数据
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:\windows\system32>F:
F:\>cd F:\app\mysql-5.6.45-winx64\bin
F:\app\mysql-5.6.45-winx64\bin>
F:\app\mysql-5.6.45-winx64\bin>mysqld
4.使用Windows+r再运行一个CMD窗口,启动mysql客户端
C:\Users\17575>F:
F:\>cd F:\app\mysql-5.6.45-winx64\bin
F:\app\mysql-5.6.45-winx64\bin>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:\windows\system32>mysqld
#启动客户端
C:\windows\system32>mysql -uroot -p
Enter password:

mysql开机自启动

#将mysqld制作成系统服务
C:\windows\system32>mysqld --install
Service successfully installed.
#此时mysql就可以开机自启动了,service.msc图形化可以设置mysql的状态
#mysql启动
1.CMD命令行启动
F:\app\mysql-5.6.45-winx64\bin>net stop mysql
F:\app\mysql-5.6.45-winx64\bin>net start mysql
2.Windows+r,输入services.msc,使用图形化启动

修改mysql数据库root密码

#实例,先确认,再修改
C:\windows\system32>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:\windows\system32> tasklist |findstr mysqld
mysqld.exe 27428 Services 0 93,272 K
C:\windows\system32> taskkill /F /PID 27428
成功: 已终止 PID 为 27428 的进程。
2.跳过用户名和密码的验证功能,启动服务端(跳过授权表)
C:\windows\system32> mysqld --skip-grant-tables
3.以管理员的身份再开启一个CMD窗口,使用mysql客户端连接mysql服务端
C:\windows\system32> mysql -uroot
4.修改管理员密码
mysql> update mysql.user set password=password(1) where user='root' and host='localhost';
5.关闭服务端,再以检验密码的方式启动(正常启动就行)
F:\app\mysql-5.6.45-winx64\bin>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> \r #重连数据库
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)

参考网站

#数据库软件之所以可以操作数据库,原理是因为该软件把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语句,点击运行即可

练习题

#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没有任何约束功能,只会加速查询
版权声明
本文为[那个萝卜在飘]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/syy1757528181/p/14215069.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云