MySQL

目录

数据库概论

系统：运行在硬件基础上，管理其他软件。

数据库：系统的管理存储数据的文件。

集群：同一个业务部署在多个服务器上，应做负载均衡，相当于多个人在一起干同一件事。

分布式：一个业务拆分成多个子业务，部署在多个服务器上，相当于多个人在一起干不同的事。

集群和分布式的区别：

  1. 分布式是把一个大的问题拆分为多个小的问题，并分别解决，最终协同合作。
  2. 集群是在几个服务器上部署相同的应用程序，来分担客户端请求，对问题本身不做分解。

数据库服务端：存放数据库的主机集群。

数据库客户端：可连接数据库的任意客户端。

数据库管理员：DBA(Database Administrator)

库：多表构成一个数据库，本质就是文件夹。

表：多条数据构建一张表，本质就是文件。

记录：存放一条条数据，本质就是文件中一条条数据记录。

字段：表中的一条记录所对应的标题。

数据库分类：

  • 关系与非关系型
    • 关系型：数据库中表与表之间有关系，如MySQL
    • 非关系型：没有表概念，如Redis、MongoDB(介于关系与非关系之间)
  • 内存与硬盘
    • 内存：数据存取效率高，如Redis
    • 硬盘：永久存储，如MySQL、MongoDB
  • SQL与nosql
    • SQL：数据库通过SQL语句操作
    • nosql：数据库是key-value形式（value就是一条记录）

数据库配置

将mysql服务器添加到系统服务,在系统服务中启动mysql, 命令:  mysqld --install
进入系统服务: win+r => services.msc => 找到mysql服务手动启动或关闭

连接数据库:mysql -hlocalhost -P3306 -uroot -p
通过最高权限进入数据库, 要采用root用户进入, 连入本地数据库: mysql -uroot -p

查看mysql版本: select version();
查看当前用户: select user();
查看mysqld下的(当前用户可以操作的)所有数据库: show databases; 

修改密码: mysqladmin -uroot -p旧密码 password "新密码"

统一字符编码：
1.在mysql安装根目录下:创建my.ini (my.cnf)
2.设置配置信息并保存
[mysqld] #服务器配置
port=3306  # 可以修改数据库默认端口(如果数据库端口被其他软件占用)
character-set-server=utf8
collation-server=utf8_general_ci # 排序方式（默认跟编码格式走）
[client] # mysql自己的客户端叫[mysql]，配置[client]即配置了[mysql]，也配置了其他存在方式的客户端，比如Navicat可视化客户端
default-character-set=utf8
3.重启服务

数据库模式

定义：限制的是客户端对服务器操作数据的方式(是否严格)。

sql_mode: 反映数据库的全局变量。

两种模式：

  • no_engine_substitution（代替）：非安全性，默认
  • strict_trans_tables：安全模式
show variables like '%sql_mode%'; #查看当前数据库模式，%：匹配0-n个任意字符，like：模糊查询

# 临时设置为安全模式，服务重启后会被重置
mysql>: set global sql_mode="strict_trans_tables";  # 在root用户登录状态下
# 在设置后，quit断开数据库连接后(服务器不重启)就会进入安全模式

# 安全模式下，非安全模式下sql执行的警告语句，都会抛异常
eg>: create table t1(name char(2));
eg>: insert into t1 values ("ab") # 正常
eg>: insert into t1 values ("owen") # 错误 Data too long for column 'name' at row 1

数据库引擎

定义：驱动数据的方式，给表使用的，不是数据库。

# 5.7以后版本默认都是安全模式
show engines; #查看所有支持的引擎（以行展示）
show engines\G; #查看所有支持的引擎（以列展示——重力加速度垂直向下）
create table t1(id int) engine=innodb; #设置数据库

innodb(默认): 支持事务, 行级锁, 外键，保证安全性。
myisam: 查询效率要优于innodb, 当不需要支持事务, 行级锁, 外键, 可以通过设置myisam来优化数据库。
Memory：表结构是存储在硬盘上的，但是表数据全部存储在内存中，断电消失。
blackhole：往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录。

用户操作

# 为特定数据库分配 操作权限 的用户
mysql>: grant 权限们 on 数据库.表 to 用户名@'主机名' identified by '密码';
# 1）all：所有权限
# 2）oldboy.*：oldboy数据库下所有表
# 3）oldboy@'localhost'：本机可以通过oldboy用户登入
# 4）identified by 'Oldboy123'：密码为Oldboy123
eg>:grant all on db1.* to jzl@'localhost' identified by '123456';

# 1）select,delete,update,insert,drop,alter：指定的权限
# 2）oldboy.*：oldboy数据库下所有表
# 3）oldboy@'%'：任何机器可以通过oldboy用户登入
# 4）identified by 'Oldboy123'：密码为Oldboy123
eg>: grant select,delete,update,insert,drop,alter on oldboy.* to oldboy@'%' identified by 'Oldboy123';

# 撤销权限
mysql>: revoke 权限1,权限2,... on 数据库名.表名 from 用户名@'主机名';
# 禁掉本地oldboy用户对oldboy数据库的所有表的drop权限
eg:> revoke drop on oldboy.* from oldboy@'localhost';

# 删除用户
drop user 用户名@'主机名';

# 退出命令
\c  quit  exit

数据库操作

库操作

create database db1; #创建数据库
create database db2 charset 'gbk'; #创建数据库并自定义编码格式
show databases; #查看所有数据库
show create database db1; #查看数据库详细信息
alter database db2 charset 'utf8'; #修改数据库编码格式
drop database db2; #删除数据库

表操作

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)engine=innodb charset=utf8; #[]为可选参数，用来限制存放数据的规则
use db1; #指定数据库
select database(); #查看当前使用的数据库
create table t1(name char,age int); #创建表（字段 类型）
create table db1.t3(name char(3) not null); #name不能为空(null), 且最长只能存放三个字符
show tables; #查看当前数据库所有表
show create table t1; #查看表详细信息
desc t1; #查看表字段结构信息
drop table t1; #删除表
alter table 旧表 rename 新表; #修改表名

字段操作

alter table 表名 change 旧字段 新字段 类型(长度); #修改字段名
alter table 表名 modify 字段 新类型(长度); #修改字段属性
alter table 表名 add 字段名 类型[(长度) 约束]; #末尾增加字段
eg>: alter table tf1 add z int unsigned;
alter table 表名 add 字段名 类型[(宽度) 约束] first; #首位增加字段
eg>: alter table tf1 add a int unsigned first;
alter table 表名 add 字段名 类型[(宽度) 约束] after 旧字段名;  #某字段后增加字段
eg>: alter table tf1 add xx int unsigned after x;
alter table 表名 drop 字段名;  #删除字段
eg>: alter table tf1 drop a;

记录操作

insert into t1(name,age) values ('aaa',18),('bbb',19); #插入数据记录
select * from t1; #查看表记录
update t1 set age=20 where name='aaa'; #修改表记录
delete from t1 where age>19; #删除表记录

数据类型

整型

tinyint：1字节，取值范围-128~127，默认长度4
smallint：2字节，取值范围 -32768 ~ 32767，默认长度6
mediumint：3字节
int：4字节 -2147483648~2147483647
bigint：8字节

create table tb1(x tinyint,y smallint,z int(6)); #建表
insert into tb1 values(128,32768,32768); #插入数据
select * from tb1; #查看表
+------+-------+-------+
| x    | y     | z     |
+------+-------+-------+
|  127 | 32767 | 32768 |
+------+-------+-------+
# 结论：所有整型变量的长度一般省略不写

浮点型

# 类型
float(M, D)：4字节，3.4E–38～3.4E+38
double(M, D)：8字节，1.7E–308～1.7E+308
decimal(M, D)：所在字节M，D大值基础上+2，其实就是M值+2就是decimal字段所占字节数
# 宽度
(M, D) => M为位数，D为小数位，M要大于等于D
float(255, 30)：精度最低，最常用
double(255, 30)：精度高，占位多
decimal(65, 30)：字符串存，全精度

# 重点：长度与小数位分析
# 报错，总长度M必须大于等于小数位D
mysql>: create table t14 (x decimal(2, 3));
# 能存储 -0.999 ~ 0.999，超长度的小数位会才有四舍五入，0.9994可以存，就是0.999,0.9995不可以存
mysql>: create table t14 (x decimal(3, 3));  # 整数位 3 - 3，所以最大为0
# 能存储 -9.999 ~ 9.999，超长度的小数位会才有四舍五入，9.9994可以存，就是9.999,9.9995不可以存
mysql>: create table t14 (x decimal(4, 3));  # 整数位 4 - 3，所以最大为9
# 能存储 -99.999 ~ 99.999，超长度的小数位会才有四舍五入，99.9994可以存，就是99.999,99.9995不可以存
mysql>: create table t14 (x decimal(5, 3));  # 整数位 5 - 3，所以最大为99

字符串

# 类型
char：定长，永远采用设置的长度存储数据
varchar：不定长，在设置的长度范围内，变长的存储数据
# 宽度
char(4)：存 "a" "ab" "abc" "abcd"都采用4个长度，"abcde" 只能存储前4位(安全模式下报错)
varchar(4)：存 "a" "ab" "abc" "abcd"分别采用1,2,3,4个长度存储，"abcde" 只能存储前4位(安全模式下报错)
char按定长存储，如果数据长度变化大，通常更占空间，但是存取数据按固定定长操作，效率高
varchar存储数据时，会先计算要存储数据的长度，动态变长存储数据，所以一般较省空间，但计算是需要耗时的，故效率低
create table ts1 (s1 char(4), s2 varchar(4));
insert into ts1 values('adcde', 'xyzabc');  # 'adcd', 'xyza'

时间

# 类型
year：yyyy(1901/2155)
date：yyyy-MM-dd(1000-01-01/9999-12-31)
time：HH:mm:ss
datetime：yyyy-MM-dd HH:mm:ss(1000-01-01 00:00:00/9999-12-31 23:59:59)
timestamp：yyyy-MM-dd HH:mm:ss(1970-01-01 00:00:00/2038-01-19 ??)
# datetime：8字节，可以为null
# timestamp：4字节，有默认值CURRENT_TIMESTAMP
create table td1 (my_year year, my_date date, my_time time); #建表
insert into td1 values(1666, '8888-8-8', '8:8:8');  # 时间需要在取值访问内
create table td2 (my_datetime datetime, my_timestamp timestamp);   
insert into td2 values('2040-1-1 1:1:1', '2040-1-1 1:1:1');  # 时间需要在取值访问内   
insert into td2(my_datetime) values('2040-1-1 1:1:1');  # timestamp不复制会才有系统当前时间

枚举与集合

# 枚举与集合：为某一个字段提供选项 - 枚举只能单选(1个)，集合可以多选(0-n个),enum、set默认值为NULL
create table tc1(name varchar(20), sex enum('男', '女', '哇塞'), hobbies set('男', '女', '哇塞')); 
insert into tc1 values('ruakei', '哇塞哇塞', '未知');  
# enum、set手动设置默认值 '男' 与 '哇塞'
create table tc2 (name varchar(20), sex enum('男', '女', '哇塞') default '男', hobbies set('男', '女', '哇塞') default '哇塞');
insert into tc2 values('ruakei', '哇塞哇塞', '未知');  
# 对sex、hobbies两个字段赋值错误，系统默认用空字符串填充(非安全模式)，安全模式抛异常
# 如果对出sex、hobbies两个字段外的其他字段进行赋值，这两个字段会才有默认值
insert into tc2 values('ruakei_1', '女', '男,女,哇塞');  
# 注：对set类型的字段进行赋值，用一个字符串，字符串内部用,将多个选项隔开，且不能添加空格等其他额外字符

约束

primary key：主键，是一条记录的唯一标识，不设置为默认找第一个 不空，唯一 字段，未标识则创建隐藏字段。
foreign key：外键
unique：唯一性数据, 确保字段的唯一
auto_increment：自增，只能加给key的int类型字段，最多出现一次
not null：不为空 - 针对一些字段，如注册时的用户名，出生人的性别等，这些需求下的字段，只不能设置为Null，必须要对其赋值
default：默认值 - 对有默认值意外的字段进行赋值时，有默认值的字段会被赋默认值
unsigned：无符号 - 存储的数字从0开始
zerofill：0填充 - 存整数时数据长度小于取值范围长度，会在数字左方用0填充
unique + auto_increment：唯一自增
# y、z在没有赋值情况下，才有默认值，设置值后，采用默认值
create table td1 (x int not null, y int default 0, z int default 100);
# 报错，auto_increment必须设置给 键字段
create table td2 (x int auto_increment);
# 报错，auto_increment必须设置给 int字段
create table td2 (x char(4) auto_increment);
# 报错，auto_increment字段最多出现 1次
create table td2 (x int unique auto_increment, y int unique auto_increment);
# # 正确，主键和唯一键分析,x为主键：没有设置primary key时，第一个 唯一自增键，会自动提升为主键
mysql>: create table td21 (x int unique auto_increment, y int unique);
# y为主键：没有设置primary key时，第一个 唯一自增键，会自动提升为主键
mysql>: create table td22 (x int unique, y int unique auto_increment);
# x为主键：设置了主键就是设置的，主键没设置自增，那自增是可以设置在唯一键上的
mysql>: create table td23 (x int primary key, y int unique auto_increment);
# x为主键：设置了主键就是设置的，主键设置了自增，自增字段只能有一个，所以唯一键不能再设置自增了
mysql>: create table td24 (x int primary key auto_increment, y int unique);
# 默认主键：没有设置主键，也没有 唯一自增键，那系统会默认添加一个 隐式主键（不可见）
mysql>: create table td25 (x int unique, y int unique);
# 联合唯一:ip在port不同时，可以相同，ip不同时port也可以相同，均合法;ip和port都相同时，就是重复数据，不合法
create table tu1 (ip char(16), port int, unique(ip, port));

多表关系

外键 foreign key：建立表与表关联的字段，外键关联的字段一定唯一，用法——foreign key(所在表的外键字段) references 关联表(关联字段);

级联关系：关系字段有连带关系。

  • 级联更新 on update cascade
  • 级联删除 on delete cascade

一对一：外键在任何一方都可以，此时外键要设置唯一键。

一对多：外键必须放在多的一方，此时外键值不唯一。

多对多：一定要创建第三张表（关系表），每一个外键值不唯一，可以多个外键建立联合唯一。

一对一：作者详情VS作者

# 先创建被关联表，再创建关联表
# 作者详情表(author_detail)：id,info,address
create table author_datail(
	id int primary key auto_increment,
    info varchar(256),
    address varchar(256)
);
# 作者表(author)：id,name,sex,age,mobile,detail_id
create table author(
	id int primary key auto_increment,
    name varchar(64) not null,
    mobile char(11) unique not null,
    sex enum('male', 'female') default 'male',
    age int default 0,
    detail_id int unique not null,
    foreign key(detail_id) references author_detail(id)
    on update cascade 
    on delete cascade
);
# 插入数据
insert into author_detail(info,address) values('allen_info','allen_address');
insert into author_detail(info,address) values('tom_info','tom_address');
insert into author(name,mobile,detail_id) values('allen','13344556677', 1);
insert into author(name,sex,mobile,detail_id) values('tom','female','222',2);
# 分析表结构
update author_detail set id=10 where id=1; #修改被关联表，关联表级联修改
update author set id=3 where id=1; #修改关联表，被关联表不变

一对多：书VS出版社

# 出版社(publish)： id，name，address，phone
create table publish(
	id int primary key auto_increment,
    name varchar(64),
    address varchar(256),
    phone char(20)
);

# 书(book)：id，name，price，publish_id, author_id
create table book(
	id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5, 2) default 0,
    publish_id int,  # 一对多的外键不能设置唯一
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade
);

# 增：先增加被关联表(publish)的数据，再增加关联表(book)的数据
mysql>: insert into publish(name, address, phone) values
('人民出版社', '北京', '010-110'),
('西交大出版社', '西安', '010-119'),
('老男孩出版社', '上海', '010-120');

mysql>: insert into book(name, price, publish_id) values
('西游记', 6.66, 1),
('东游记', 8.66, 1),
('python从入门到入土', 2.66, 2),
('轮程序员修养之道', 3.66, 3),
('好好活着', 88.88, 3);
# 没有被关联的字段，插入依旧错误
mysql>: insert into book(name, price, publish_id) values ('打脸之道', 0.3, 4);  # 失败


# 更新：直接更新被关联表的(publish) 主键，关联表(book) 外键 会级联更新
mysql>: update publish set id=10 where id=1;
# 更新：直接更新关联表的(book) 外键，修改的值对应被关联表(publish) 主键 如果存在，可以更新成功，反之失败
mysql>: update book set publish_id=2 where id=4;  # 成功
mysql>: update book set publish_id=1 where id=4;  # 失败

#	删被关联表，关联表会被级联删除
mysql>: delete from publish where id = 2;
#	删关联表，被关联表不会发生变化
mysql>: delete from book where publish_id = 3;

多对多：作者VS出版社

# 作者(author)：id, name, age
create table author(
	id int primary key auto_increment,
    name varchar(64),
    age int unsigned default 0
);

# 出版社(publish)：id, name, address
create table publish(
	id int primary key auto_increment,
    name varchar(64),
    address varchar(256)
);

# 作者与出版社关系表：id, author_id, publish_id
create table author_publish(
	id int primary key auto_increment,
    # 关系表一定有多个外键，关联着多张表
    # 关联作者表
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    # 关联出版社表
    publish_id int,
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade,
    # 建立两个字段的联合唯一
    unique(author_id, publish_id)
);

# 注：关系表 关联着 作者 和 出版社 两张表，在表结构上 作者 与 出版社 两表键没有任何关系


# 增：两张被关联表，没有前后关系，但关系表必须在两个表都提供数据后才能进行 关系匹配
mysql>: insert into author(name, age) values('ruakei', 67),('engo', 76),('Lxx', 3);
mysql>: insert into publish(name, address) values('老男孩出版社', '上海'),('小女孩出版社', '北京');

# 操作关系表：
mysql>: insert into author_publish(author_id, publish_id) values(1,1),(1,2),(2,1),(2,2),(3,1);

# 关系表操作：增、删、改，只要两张被关系表有提供对应的操作数据，都可以操作成功，且对两张被关系表没有影响


# 操作两张被关系表：
#		增：不会影响关系表
mysql>: insert into publish(name, address) values('西交大出版社', '西安');
#		改：关系表都会级联更新
mysql>: update publish set id=10 where id=1;
#		删：关系表都会级联删除
mysql>: delete from author where name='ruakei';

单表查询

insert [into] [数据库名.]表名[(字段...)] values (数据1,数据2)...]; #增
delete from [数据库名.]表名 [条件]; #删
update [数据库名.]表名 set 字段1=值1... [条件]; #改
select [distinct] 字段1 [[as] 别名1],...,字段n [[as] 别名n] from [数据库名.]表名 [条件]; #改
条件：from、where、group by、having、distinct、order by、limit => 层层筛选后的结果
注：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，distinct稍有特殊(书写位置)，条件的种类可以不全，以上可缺失，但不能乱序。

去重：distinct

定义：对参与查询的所有字段，整体去重（所查的的全部字段的值都相同，才认为是重复数据）

mysql> create table t1(id int,x int,y int); #建表
mysql> insert into t1 values(1,1,1),(2,1,2),(3,2,2),(4,2,2); #插入数据
mysql> select distinct * from t1; #对全部数据去重
+------+------+------+
| id   | x    | y    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    1 |    2 |
|    3 |    2 |    2 |
|    4 |    2 |    2 |
+------+------+------+
mysql> select distinct x,y from t1;
+------+------+
| x    | y    |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    2 |
+------+------+
mysql> select distinct y from t1;
+------+
| y    |
+------+
|    1 |
|    2 |
+------+

常用函数

拼接：concat() | concat_ws()
大小写：upper() | lower()
浮点型操作：ceil() | floor() | round()
整型：可以直接运算

# 创建职工表
mysql> create table emp(id int not null primary key auto_increment,
                name varchar(10) not null,
                gender enum('男','女','未知') null default '未知',
                age int null default 0,
                salary float null default 0,
                area varchar(20) null default '中国',
                port varchar(20) default '未知',
                dep varchar(20));
# 插入职工数据
mysql> insert into emp values 
	(1, 'yangsir', '男', 42, 10.5, '上海', '浦东', '教职部'),
	(2, 'engo', '男', 38, 9.4, '山东', '济南', '教学部'),
	(3, 'jerry', '女', 30, 3.0, '江苏', '张家港', '教学部'),
	(4, 'tank', '女', 28, 2.4, '广州', '广东', '教学部'),
	(5, 'jiboy', '男', 28, 2.4, '江苏', '苏州', '教学部'),
	(6, 'zero', '男', 18, 8.8, '中国', '黄浦', '咨询部'),
	(7, 'owen', '男', 18, 8.8, '安徽', '宣城', '教学部'),
	(8, 'jason', '男', 28, 9.8, '安徽', '巢湖', '教学部'),
	(9, 'ying', '女', 36, 1.2, '安徽', '芜湖', '咨询部'),
	(10, 'kevin', '男', 36, 5.8, '山东', '济南', '教学部'),
	(11, 'monkey', '女', 28, 1.2, '山东', '青岛', '教职部'),
	(12, 'san', '男', 30, 9.0, '上海', '浦东', '咨询部'),
	(13, 'san1', '男', 30, 6.0, '上海', '浦东', '咨询部'),
	(14, 'san2', '男', 30, 6.0, '上海', '浦西', '教学部'),
	(15, 'ruakei', '女', 67, 2.501, '上海', '陆家嘴', '教学部');
# 拼接：concat()|concat_ws()
mysql> select name as 姓名,concat(area,'-',port) as 地址 from emp;
+---------+-------------+
| 姓名    | 地址        |
+---------+-------------+
| yangsir | 上海-浦东   |
| engo    | 山东-济南   |
| jerry   | 江苏-张家港 |
| tank    | 广州-广东   |
| jiboy   | 江苏-苏州   |
| zero    | 中国-黄浦   |
| owen    | 安徽-宣城   |
| jason   | 安徽-巢湖   |
| ying    | 安徽-芜湖   |
| kevin   | 山东-济南   |
| monkey  | 山东-青岛   |
| san     | 上海-浦东   |
| san1    | 上海-浦东   |
| san2    | 上海-浦西   |
| ruakei  | 上海-陆家嘴 |
+---------+-------------+
mysql> select name 姓名,concat_ws('-',area,port,dep) 信息 from emp;
+---------+--------------------+
| 姓名    | 信息               |
+---------+--------------------+
| yangsir | 上海-浦东-教职部   |
| engo    | 山东-济南-教学部   |
| jerry   | 江苏-张家港-教学部 |
| tank    | 广州-广东-教学部   |
| jiboy   | 江苏-苏州-教学部   |
| zero    | 中国-黄浦-咨询部   |
| owen    | 安徽-宣城-教学部   |
| jason   | 安徽-巢湖-教学部   |
| ying    | 安徽-芜湖-咨询部   |
| kevin   | 山东-济南-教学部   |
| monkey  | 山东-青岛-教职部   |
| san     | 上海-浦东-咨询部   |
| san1    | 上海-浦东-咨询部   |
| san2    | 上海-浦西-教学部   |
| ruakei  | 上海-陆家嘴-教学部 |
+---------+--------------------+
# 大小写 upper()|lower()
mysql> select upper(name) 姓名大写,lower(name) 姓名小写 from emp;
+----------+----------+
| 姓名大写 | 姓名小写 |
+----------+----------+
| YANGSIR  | yangsir  |
| ENGO     | engo     |
| JERRY    | jerry    |
| TANK     | tank     |
| JIBOY    | jiboy    |
| ZERO     | zero     |
| OWEN     | owen     |
| JASON    | jason    |
| YING     | ying     |
| KEVIN    | kevin    |
| MONKEY   | monkey   |
| SAN      | san      |
| SAN1     | san1     |
| SAN2     | san2     |
| RUAKEI   | ruakei   |
+----------+----------+
# 浮点型操作：ceil() | floor() | round()
mysql> select id,salary,ceil(salary)向上取整,floor(salary)向下取整,round(salary)四舍五入 from emp;
+----+--------+----------+----------+----------+
| id | salary | 向上取整 | 向下取整 | 四舍五入 |
+----+--------+----------+----------+----------+
|  1 |   10.5 |       11 |       10 |       10 |
|  2 |    9.4 |       10 |        9 |        9 |
|  3 |      3 |        3 |        3 |        3 |
|  4 |    2.4 |        3 |        2 |        2 |
|  5 |    2.4 |        3 |        2 |        2 |
|  6 |    8.8 |        9 |        8 |        9 |
|  7 |    8.8 |        9 |        8 |        9 |
|  8 |    9.8 |       10 |        9 |       10 |
|  9 |    1.2 |        2 |        1 |        1 |
| 10 |    5.8 |        6 |        5 |        6 |
| 11 |    1.2 |        2 |        1 |        1 |
| 12 |      9 |        9 |        9 |        9 |
| 13 |      6 |        6 |        6 |        6 |
| 14 |      6 |        6 |        6 |        6 |
| 15 |  2.501 |        3 |        2 |        3 |
+----+--------+----------+----------+----------+
# 整型：可以直接运算
mysql> select name 姓名,age 旧年龄,age+1 新年龄 from emp;
+---------+--------+--------+
| 姓名    | 旧年龄 | 新年龄 |
+---------+--------+--------+
| yangsir |     42 |     43 |
| engo    |     38 |     39 |
| jerry   |     30 |     31 |
| tank    |     28 |     29 |
| jiboy   |     28 |     29 |
| zero    |     18 |     19 |
| owen    |     18 |     19 |
| jason   |     28 |     29 |
| ying    |     36 |     37 |
| kevin   |     36 |     37 |
| monkey  |     28 |     29 |
| san     |     30 |     31 |
| san1    |     30 |     31 |
| san2    |     30 |     31 |
| ruakei  |     67 |     68 |
+---------+--------+--------+

条件：where

比较符号：>  |  <  |  >=  |  <=  |  =  |  !=
区间符号：between 开始 and 结束 |  in(自定义容器)
逻辑符号：and  |  or  |  not
相似符号：like _|%
正则符号：regexp 正则语法

mysql> select * from emp where salary>6; #查找薪资大于6的表
mysql> select * from emp where id%2 != 0; #查找序号为奇数的表
+----+---------+--------+------+--------+------+--------+--------+
| id | name    | gender | age  | salary | area | port   | dep    |
+----+---------+--------+------+--------+------+--------+--------+
|  1 | yangsir | 男     |   42 |   10.5 | 上海 | 浦东   | 教职部 |
|  3 | jerry   | 女     |   30 |      3 | 江苏 | 张家港 | 教学部 |
|  5 | jiboy   | 男     |   28 |    2.4 | 江苏 | 苏州   | 教学部 |
|  7 | owen    | 男     |   18 |    8.8 | 安徽 | 宣城   | 教学部 |
|  9 | ying    | 女     |   36 |    1.2 | 安徽 | 芜湖   | 咨询部 |
| 11 | monkey  | 女     |   28 |    1.2 | 山东 | 青岛   | 教职部 |
| 13 | san1    | 男     |   30 |      6 | 上海 | 浦东   | 咨询部 |
| 15 | ruakei  | 女     |   67 |  2.501 | 上海 | 陆家嘴 | 教学部 |
+----+---------+--------+------+--------+------+--------+--------+
mysql> select * from emp where salary between 6 and 9; #查找薪资6到9的表
+----+------+--------+------+--------+------+------+--------+
| id | name | gender | age  | salary | area | port | dep    |
+----+------+--------+------+--------+------+------+--------+
|  6 | zero | 男     |   18 |    8.8 | 中国 | 黄浦 | 咨询部 |
|  7 | owen | 男     |   18 |    8.8 | 安徽 | 宣城 | 教学部 |
| 12 | san  | 男     |   30 |      9 | 上海 | 浦东 | 咨询部 |
| 13 | san1 | 男     |   30 |      6 | 上海 | 浦东 | 咨询部 |
| 14 | san2 | 男     |   30 |      6 | 上海 | 浦西 | 教学部 |
+----+------+--------+------+--------+------+------+--------+
mysql> select * from emp where id in(1,3,7,20); #查找序列号在括号范围内的表
+----+---------+--------+------+--------+------+--------+--------+
| id | name    | gender | age  | salary | area | port   | dep    |
+----+---------+--------+------+--------+------+--------+--------+
|  1 | yangsir | 男     |   42 |   10.5 | 上海 | 浦东   | 教职部 |
|  3 | jerry   | 女     |   30 |      3 | 江苏 | 张家港 | 教学部 |
|  7 | owen    | 男     |   18 |    8.8 | 安徽 | 宣城   | 教学部 |
+----+---------+--------+------+--------+------+--------+--------+
# _o 某o | __o 某某o | _o% 某o* (*是0~n个任意字符) | %o% *o*
mysql> select * from emp where name like '%o%';
mysql> select * from emp where name like '_o%';  
mysql> select * from emp where name like '___o%';
# sql只支持部分正则语法
mysql> select * from emp where name regexp '.*\d';  # 不支持\d代表数字，认为\d就是普通字符串
mysql> select * from emp where name regexp '.*[0-9]';  # 支持[]语法

分组前：having

# 表象：在没有分组的情况下，where与having结果相同
# 重点：having可以对 聚合结果 进行筛选
mysql>: select * from emp where salary > 5;
mysql>: select * from emp having salary > 5;
mysql>: select * from emp where id in (5, 10, 15, 20);
mysql>: select * from emp having id in (5, 10, 15, 20);

聚合函数

max()：最大值
min()：最小值
avg()：平均值
sum()：和
count()：计数
group_concat()：组内字段拼接，用来查看组内其他字段

分组查询：group by

定义：分组后，查询条件只能为 分组字段 和 聚合函数操作的聚合结果

# 修改my.ini配置重启mysql服务
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# 在sql_mode没有 ONLY_FULL_GROUP_BY 限制下，可以执行，但结果没有意义
# 有 ONLY_FULL_GROUP_BY 限制，报错
mysql> select * from emp group by dep;
+----+---------+--------+------+--------+------+------+--------+
| id | name    | gender | age  | salary | area | port | dep    |
+----+---------+--------+------+--------+------+------+--------+
|  6 | zero    | 男     |   18 |    8.8 | 中国 | 黄浦 | 咨询部 |
|  2 | engo    | 男     |   38 |    9.4 | 山东 | 济南 | 教学部 |
|  1 | yangsir | 男     |   42 |   10.5 | 上海 | 浦东 | 教职部 |
+----+---------+--------+------+--------+------+------+--------+
# 分组后，表中数据考虑范围就不是 单条记录，因为每个分组都包含了多条记录，参照分组字段，对每个分组中的 多条记录 统一处理
# eg: 按部门分组，每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资、组里一共有多少人
# 将多条数据统一处理，这种方式就叫 聚合
# 每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资 都称之为 聚合结果 - 聚合函数操作的结果
mysql> select dep 部门,
			max(salary) 最高薪资,
			min(salary) 最低薪资,
			sum(salary) 总薪资,
			count(gender) 人数 from emp group by dep;
+--------+----------+----------+--------------------+------+
| 部门   | 最高薪资 | 最低薪资 | 总薪资             | 人数 |
+--------+----------+----------+--------------------+------+
| 咨询部 |        9 |      1.2 |  25.00000023841858 |    4 |
| 教学部 |      9.8 |      2.4 |  50.10100030899048 |    9 |
| 教职部 |     10.5 |      1.2 | 11.700000047683716 |    2 |
+--------+----------+----------+--------------------+------+
mysql> select dep 部门,max(age) 最高年龄 from emp group by dep;
+--------+----------+
| 部门   | 最高年龄 |
+--------+----------+
| 咨询部 |       36 |
| 教学部 |       67 |
| 教职部 |       42 |
+--------+----------+

分组后：having

定义：having可以对 聚合结果 再进行筛选，where不可以

mysql> select dep 部门,
	group_concat(name) 成员,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	count(gender) 人数 from emp group by dep having min(salary)<2; #最低薪资小于2
+--------+--------------------+--------------------+-------------------+------+
| 部门   | 成员               | 最低薪资           | 平均薪资          | 人数 |
+--------+--------------------+--------------------+-------------------+------+
| 咨询部 | san1,san,zero,ying | 1.2000000476837158 | 6.250000059604645 |    4 |
| 教职部 | monkey,yangsir     | 1.2000000476837158 | 5.850000023841858 |    2 |
+--------+--------------------+--------------------+-------------------+------+

排序：order by

情况一：未分组状态

# 语法：order by 主排序字段 [asc|desc], 次排序字段1 [asc|desc], ...次排序字段n [asc|desc]
# 按年龄升序
mysql> select * from emp order by age asc;
# 按薪资降序
mysql> select * from emp order by salary desc;
# 按薪资降序，如果相同，再按年龄降序
mysql>: select * from emp order by salary desc, age desc;
# 按龄降序，如果相同，再按薪资降序
mysql>: select * from emp order by age desc, salary desc;

情况二：分组状态

# 最高薪资降序
mysql> select 
	dep 部门,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	count(gender) 人数 from emp group by dep order by 最高薪资 desc;
+--------+----------+----------+-------------------+------+
| 部门   | 最高薪资 | 最低薪资 | 平均薪资          | 人数 |
+--------+----------+----------+-------------------+------+
| 教职部 |     10.5 |      1.2 | 5.850000023841858 |    2 |
| 教学部 |      9.8 |      2.4 | 5.566777812110053 |    9 |
| 咨询部 |        9 |      1.2 | 6.250000059604645 |    4 |
+--------+----------+----------+-------------------+------+

限制:limit

# 语法：limit 条数  |  limit 偏移量,条数
mysql> select name, salary from emp where salary<8 order by salary desc limit 1;
+------+--------+
| name | salary |
+------+--------+
| san1 |      6 |
+------+--------+
mysql> select * from emp limit 5,3;  # 先偏移5条满足条件的记录，再查询3条
+----+-------+--------+------+--------+------+------+--------+
| id | name  | gender | age  | salary | area | port | dep    |
+----+-------+--------+------+--------+------+------+--------+
|  6 | zero  | 男     |   18 |    8.8 | 中国 | 黄浦 | 咨询部 |
|  7 | owen  | 男     |   18 |    8.8 | 安徽 | 宣城 | 教学部 |
|  8 | jason | 男     |   28 |    9.8 | 安徽 | 巢湖 | 教学部 |
+----+-------+--------+------+--------+------+------+--------+

连表查询

连接：将有联系的多张表通过关联（有联系就行，不一定是外键）字段进行连接，形成一张大表。

连表查询：在大表的基础上进行查询。

表与表连接的方式有四种：内连接、左连接、右连接、全连接

# 一对多数据准备
# 创建部门表
create table dep(
	id int primary key auto_increment,
	name varchar(64),
	work varchar(64));
# 创建员工表
create table emp(id int primary key auto_increment,
                 name varchar(64),
                 salary float,
                 dep_id int);
# 插入部门数据
insert into dep values(1,'市场部','销售'),(2,'教学部','授课'),(3,'后勤部','跑腿');
# 插入员工数据
insert into emp(name,salary,dep_id) values('tank',111.11,2),('nick',222.22,2),('jack',333.33,3),('egon',444.44,1),('jason',555.55,1),('tom',666.66,0);
# 查询每一个部门下的员工们及员工职责
# 1、查谁 2、从哪查，信息量不够连表 3、条件是啥，如果存在分组（不能直接查询的字段聚合处理 | 将要查询的字段添加进分组）
select group_concat(emp.name), work, dep.name from emp right join dep on emp.dep_id=dep.id group by emp.dep_id, work, dep.name;
select group_concat(emp.name) 员工们, max(work) 工作职责, max(dep.name) 部门 from emp right join dep on emp.dep_id=dep.id group by emp.dep_id;
select group_concat(emp.name) 员工们, max(work) 工作职责, dep.name 部门 from emp right join dep on emp.dep_id=dep.id group by dep.name;

笛卡尔积

定义：两张表记录的所有排列组合，数据无意义。

mysql> select * from emp, dep;
+----+-------+--------+--------+----+--------+------+
| id | name  | salary | dep_id | id | name   | work |
+----+-------+--------+--------+----+--------+------+
|  1 | tank  | 111.11 |      2 |  1 | 市场部 | 销售 |
|  1 | tank  | 111.11 |      2 |  2 | 教学部 | 授课 |
|  1 | tank  | 111.11 |      2 |  3 | 后勤部 | 跑腿 |
|  2 | nick  | 222.22 |      2 |  1 | 市场部 | 销售 |
|  2 | nick  | 222.22 |      2 |  2 | 教学部 | 授课 |
|  2 | nick  | 222.22 |      2 |  3 | 后勤部 | 跑腿 |
|  3 | jack  | 333.33 |      3 |  1 | 市场部 | 销售 |
|  3 | jack  | 333.33 |      3 |  2 | 教学部 | 授课 |
|  3 | jack  | 333.33 |      3 |  3 | 后勤部 | 跑腿 |
|  4 | egon  | 444.44 |      1 |  1 | 市场部 | 销售 |
|  4 | egon  | 444.44 |      1 |  2 | 教学部 | 授课 |
|  4 | egon  | 444.44 |      1 |  3 | 后勤部 | 跑腿 |
|  5 | jason | 555.55 |      1 |  1 | 市场部 | 销售 |
|  5 | jason | 555.55 |      1 |  2 | 教学部 | 授课 |
|  5 | jason | 555.55 |      1 |  3 | 后勤部 | 跑腿 |
|  6 | tom   | 666.66 |      0 |  1 | 市场部 | 销售 |
|  6 | tom   | 666.66 |      0 |  2 | 教学部 | 授课 |
|  6 | tom   | 666.66 |      0 |  3 | 后勤部 | 跑腿 |
+----+-------+--------+--------+----+--------+------+

内连接(inner join on)

定义：只保留两张表有关联的数据。

# 语法：from 左表 inner join 右表 on 左表.关联字段=右表.关联字段
mysql> select emp.id,emp.name,emp.salary,dep.name,dep.work from emp inner join dep on emp.dep_id=dep.id order by emp.id;
+----+-------+--------+--------+------+
| id | name  | salary | name   | work |
+----+-------+--------+--------+------+
|  1 | tank  | 111.11 | 教学部 | 授课 |
|  2 | nick  | 222.22 | 教学部 | 授课 |
|  3 | jack  | 333.33 | 后勤部 | 跑腿 |
|  4 | egon  | 444.44 | 市场部 | 销售 |
|  5 | jason | 555.55 | 市场部 | 销售 |
+----+-------+--------+--------+------+

左连接(left join on)

定义：保留左表的全部数据，右表有对应数据直接连表显示，没有对应关系空填充。

# 语法：from 左表 left join 右表 on 左表.关联字段=右表.关联字段
mysql> select emp.id,emp.name,emp.salary,dep.name,dep.work from emp left join dep on emp.dep_id=dep.id order by emp.id;
+----+-------+--------+--------+------+
| id | name  | salary | name   | work |
+----+-------+--------+--------+------+
|  1 | tank  | 111.11 | 教学部 | 授课 |
|  2 | nick  | 222.22 | 教学部 | 授课 |
|  3 | jack  | 333.33 | 后勤部 | 跑腿 |
|  4 | egon  | 444.44 | 市场部 | 销售 |
|  5 | jason | 555.55 | 市场部 | 销售 |
|  6 | tom   | 666.66 | NULL   | NULL |
+----+-------+--------+--------+------+

右连接(right join on)

定义：保留右表的全部数据，左表有对应数据直接连表显示，没有对应关系空填充。

# 语法：from 左表 right join 右表 on 左表.关联字段=右表.关联字段
mysql> select emp.id,emp.name,emp.salary,dep.name,dep.work from emp right join dep on emp.dep_id=dep.id order by emp.id;
+------+-------+--------+--------+------+
| id   | name  | salary | name   | work |
+------+-------+--------+--------+------+
|    1 | tank  | 111.11 | 教学部 | 授课 |
|    2 | nick  | 222.22 | 教学部 | 授课 |
|    3 | jack  | 333.33 | 后勤部 | 跑腿 |
|    4 | egon  | 444.44 | 市场部 | 销售 |
|    5 | jason | 555.55 | 市场部 | 销售 |
+------+-------+--------+--------+------+

全连接(union)

定义：左表右表数据都被保留，彼此有对应关系的正常显示，没有关系的均空填充对方。

# 语法：from 左表 left join 右表 on 左表.关联字段=右表.关联字段 union from 左表 right join 右表 on 左表.关联字段=右表.关联字段
mysql> select emp.id,emp.name,salary,dep.name,work from emp left join dep on emp.dep_id = dep.id union select emp.id,emp.name,salary,dep.name,work from emp right join dep on emp.dep_id = dep.id order by id;
+------+-------+--------+--------+------+
| id   | name  | salary | name   | work |
+------+-------+--------+--------+------+
|    1 | tank  | 111.11 | 教学部 | 授课 |
|    2 | nick  | 222.22 | 教学部 | 授课 |
|    3 | jack  | 333.33 | 后勤部 | 跑腿 |
|    4 | egon  | 444.44 | 市场部 | 销售 |
|    5 | jason | 555.55 | 市场部 | 销售 |
|    6 | tom   | 666.66 | NULL   | NULL |
+------+-------+--------+--------+------+

一对一

# 一对一数据准备
# 创建作者表
mysql> create table author(id int,
                    name varchar(64),
                    detail_id int);
# 创建作者信息表
mysql> create table author_detail(id int,
    					phone varchar(11));
# 插入作者数据
mysql> insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);
# 插入作者信息数据
mysql> insert into author_detail values(1, '13344556677'), (2, '14466779988'), (3, '12344332255');
# 内连
mysql> select author.id,name,phone from author join author_detail on author.detail_id = author_detail.id order by author.id;
+------+------+-------------+
| id   | name | phone       |
+------+------+-------------+
|    1 | Bob  | 13344556677 |
|    2 | Tom  | 14466779988 |
+------+------+-------------+
# 全连
mysql> select author.id,name,phone from author left join author_detail on author.detail_id = author_detail.id union select author.id,name,phone from author right join author_detail on author.detail_id = author_detail.id order by id;
+------+--------+-------------+
| id   | name   | phone       |
+------+--------+-------------+
| NULL | NULL   | 12344332255 |
|    1 | Bob    | 13344556677 |
|    2 | Tom    | 14466779988 |
|    3 | ruakei | NULL        |
+------+--------+-------------+

多对多

# 在一对一基础上，建立作者与书的多对多关系
# 利用之前的作者表与数据
# 创建书表
mysql> create table book(id int,
                  name varchar(64),
                  price decimal(5,2));
# 插入书数据
mysql> insert into book values(1, 'python', 3.66), (2, 'Linux', 2.66), (3, 'Go', 4.66);
+------+--------+-------+
| id   | name   | price |
+------+--------+-------+
|    1 | python |  3.66 |
|    2 | Linux  |  2.66 |
|    3 | Go     |  4.66 |
+------+--------+-------+
# 创建 作者与书 的关系表
mysql> create table author_book(id int,
                                author_id int,
                                book_id int);
# 插入作者与书的关联数据
mysql> insert into author_book values(1,1,1),(2,1,2),(3,2,2),(4,2,3),(5,3,1),(6,3,3);
+------+-----------+---------+
| id   | author_id | book_id |
+------+-----------+---------+
|    1 |         1 |       1 |
|    2 |         1 |       2 |
|    3 |         2 |       2 |
|    4 |         2 |       3 |
|    5 |         3 |       1 |
|    6 |         3 |       3 |
+------+-----------+---------+
# 将有关联的表一一建立连接，查询所有自己所需字段
# 书与关联表（作者与书）建立内连接
mysql> select * from book join author_book on book.id=author_book.book_id;
+------+--------+-------+------+-----------+---------+
| id   | name   | price | id   | author_id | book_id |
+------+--------+-------+------+-----------+---------+
|    1 | python |  3.66 |    1 |         1 |       1 |
|    2 | Linux  |  2.66 |    2 |         1 |       2 |
|    2 | Linux  |  2.66 |    3 |         2 |       2 |
|    3 | Go     |  4.66 |    4 |         2 |       3 |
|    1 | python |  3.66 |    5 |         3 |       1 |
|    3 | Go     |  4.66 |    6 |         3 |       3 |
+------+--------+-------+------+-----------+---------+
# 书与关联表（作者与书）建立内连接后，再与作者建立内连接
mysql> select * from book 
join author_book on book.id = author_book.book_id
join author on author_book.author_id = author.id;
+------+--------+-------+------+-----------+---------+------+--------+-----------+
| id   | name   | price | id   | author_id | book_id | id   | name   | detail_id |
+------+--------+-------+------+-----------+---------+------+--------+-----------+
|    1 | python |  3.66 |    1 |         1 |       1 |    1 | Bob    |         1 |
|    2 | Linux  |  2.66 |    2 |         1 |       2 |    1 | Bob    |         1 |
|    2 | Linux  |  2.66 |    3 |         2 |       2 |    2 | Tom    |         2 |
|    3 | Go     |  4.66 |    4 |         2 |       3 |    2 | Tom    |         2 |
|    1 | python |  3.66 |    5 |         3 |       1 |    3 | ruakei |         0 |
|    3 | Go     |  4.66 |    6 |         3 |       3 |    3 | ruakei |         0 |
+------+--------+-------+------+-----------+---------+------+--------+-----------+
# 书与关联表（作者与书）建立内连接后，再与作者建立内连接，最后与作者详情表建立内连接
mysql> select * from book 
join author_book on book.id = author_book.book_id
join author on author_book.author_id = author.id
join author_detail on author.detail_id = author_detail.id;
+------+--------+-------+------+-----------+---------+------+------+-----------+------+-------------+
| id   | name   | price | id   | author_id | book_id | id   | name | detail_id | id   | phone       |
+------+--------+-------+------+-----------+---------+------+------+-----------+------+-------------+
|    1 | python |  3.66 |    1 |         1 |       1 |    1 | Bob  |         1 |    1 | 13344556677 |
|    2 | Linux  |  2.66 |    2 |         1 |       2 |    1 | Bob  |         1 |    1 | 13344556677 |
|    2 | Linux  |  2.66 |    3 |         2 |       2 |    2 | Tom  |         2 |    2 | 14466779988 |
|    3 | Go     |  4.66 |    4 |         2 |       3 |    2 | Tom  |         2 |    2 | 14466779988 |
+------+--------+-------+------+-----------+---------+------+------+-----------+------+-------------+
# 书与关联表（作者与书）建立内连接后，再与作者建立内连接，最后与作者详情表建立左连接且提取所需数据
mysql> select book.name, book.price, author.name, author_detail.phone from book 
join author_book on book.id = author_book.book_id
join author on author_book.author_id = author.id
left join author_detail on author.detail_id = author_detail.id;
+--------+-------+--------+-------------+
| name   | price | name   | phone       |
+--------+-------+--------+-------------+
| python |  3.66 | Bob    | 13344556677 |
| Linux  |  2.66 | Bob    | 13344556677 |
| Linux  |  2.66 | Tom    | 14466779988 |
| Go     |  4.66 | Tom    | 14466779988 |
| python |  3.66 | ruakei | NULL        |
| Go     |  4.66 | ruakei | NULL        |
+--------+-------+--------+-------------+

1、单表查询
	增删改查的完整语法
select distinct 字段 from 表 where   group by   having   order by   limit  

比较：> < =
区间：between and  |  in  |  not in
逻辑: and  or  not
相似：like  _%
正则：regexp

聚合函数：group_concat()、max()

having：可以对 聚合函数 结果进行筛选，不能使用 聚合函数 别名
order by：分组后对 聚合函数 进行排序，能使用 聚合函数 别名
limit：条数 | 偏移,条数


2、多表查询
内连接：from emp inner join dep on emp.dep_id = dep.id  只保存两表有对应关系的记录
左连接：from emp left join dep on emp.dep_id = dep.id   左表记录全部保存，右边没有对应记录空填充
右连接：from emp right join dep on emp.dep_id = dep.id  右表记录全部保存，左边没有对应记录空填充
全连接：
from emp left join dep on emp.dep_id = dep.id
union
from emp right join dep on emp.dep_id = dep.id

联合分组

# 数据来源：在单表emp下
# 联合分组：按多个字段综合结果进行分组
# 按 area与port组合后的结果进行分组，只有组合后的结果还一致，才认为是一组
select group_concat(name),area,port from emp group by area,port;

子查询

# 定义：将一条查询sql的结果作为另一条sql的条件
# 增：insert into 表 select子查询
# 删：delete from 表 条件是select子查询(表不能与delete表相同)
# 查：select 字段 from 表 条件是select子查询
# 改：update 表 set 字段=值 条件是select子查询(表不能与update表相同)

# 数据来源：在单表emp下
# 思考：每个部门最高薪资的那个人所有信息
# 子查询的sql
select dep, max(salary) from emp group by dep;
# 子查询 - 查
select * from emp where (dep, salary) in (select dep, max(salary) from emp group by dep);

# 将子查询转换为一张表
# 创建一个存子查询数据的一张表
create table t1(dep_name varchar(64), max_salary decimal(5,2));
# 子查询 - 增
insert into t1 select dep, max(salary) from emp group by dep;
# 需求
select name, dep_name, salary 
from emp join t1 
on emp.dep=t1.dep_name and emp.salary=t1.max_salary;

# 子查询 - 改（update更新的表不能 与 子查询select的表同表）
# 每个部门最大薪资+1
update t1 set max_salary=max_salary+1;
# 给t1额外增加一个新部门
insert into t1 values ('打杂部', 100);
# 子查询 - 改
update t1 set max_salary=max_salary+1 where dep_name in (select distinct dep from emp);
# 错误：update更新的表 与 子查询select的表 相同
update t1 set max_salary=max_salary+1 where dep_name in (select distinct dep_name from t1);

# 子查询 - 删
delete from t1 where dep_name in (select distinct dep from emp);
# 错误: delete删除的表 与 子查询select的表 相同
delete from t1 where dep_name in (select distinct dep_name from t1);

all与any：区间修饰条件

# 语法规则
# where id in (1, 2, 3) => id是1或2或3
# where id not in (1, 2, 3) => id不是1，2，3
# where salary < all(3, 6, 9) => salary必须小于所有情况（小于最小）
# where salary > all(3, 6, 9) => salary必须大于所有情况（大于最大）
# where salary < any(3, 6, 9) => salary只要小于一种情况（小于最大）
# where salary > any(3, 6, 9) => salary只要大于一种情况（大于最小）
in < > ()
# 案例
select * from emp where salary < all(select salary from emp where id>11);

视图：view

定义：存在内存中的临时表。
要点：
	1.视图是select语句操作的结果形成的表，故创建依赖select语句。
	2.支持对数据的增删改查，本质对真实表的操作
	3.不允许对视图表的字段做修改

需求：对视图进行增删改查

# 数据依赖：单表emp
# 创建视图
mysql> create view 视图名[(别名们)] as select 语句;
eg> create view v1 as select dep, max(salary) from emp group by dep;
# 创建或替换视图
mysql> create or replace 视图名[(别名们)] as select 语句;
mysql> alter 视图名[(别名们)] as select 语句;
eg> create or replace view v1(dep_name, max_salary) as select dep, max(salary) from emp group by dep;
eg> alter view v1(name, salary) as select dep, max(salary) from emp group by dep;
# 删除视图
mysql> drop view 视图名
eg> drop view v1;
# 视图可以作为正常表完成连表查询
select name, dep_name, salary 
from emp join v1 
on emp.dep=v1.dep_name and emp.salary=v1.max_salary;

视图的增删改

# 前提：视图的增删改操作可以直接映射给真实表（本质就是对真实表进行操作）

# 视图可以完成增删改，增删改本质是直接对创建视图的真实表进行操作
create or replace view v2 as select id,name,age,salary from emp;
update v2 set salary=salary+1 where id=1;
delete from v2 where id=1;

create or replace view v3 as select * from emp;
insert into v3 values(1, 'yangsir', '男', 66, 1.11, '上海', '那噶的', '教职部');

# 总结：操作视图，会影响真实表，反之也会影响
update emp set salary=salary+1 where id=1;

事务

# 定义：多条整体执行的sql语句，如转账就是一个事务，从一个用户将资金转出，再将资金转入到另一个用户
# 四大特性：
# 1.原子性：事务不可分割,要么同时成功,要么同时不成功
# 2.一致性：事物前后的数据完整性应该保持一致
# 3.隔离性：innodb自带行级锁，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据相互隔离
# 4.持久性：一个事物一旦被提交，对数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

需求：如何使用事务

# 创建银行表
create table bank(id int,
                  name varchar(16),
                  money decimal(65, 2));
# 插入银行数据
insert into bank values(1, 'Tom', 10), (2, "Bob", 10);
# 假设没有事务支持情况下，Tom的钱就丢了
update bank set money=money-1 where name='Tom';
update bank set money=money+1 where name='ruakei';

# 将两条sql看做事务处理
update bank set money=money-1 where name='Tom';
update bank set money=money+1 where name='ruakei';
# 确认无误，提交事务
commit;
# 确认有误，回滚
rollback;

pymysql模块

# 用途：对数据库进行增删改查
# pymysql连接数据库的必要参数：主机host(默认)、端口port(默认)、用户名user、密码passwd、数据库db.
# 操作步骤：
    # 1.建立数据库连接对象 conn，可设置自动提交事务autocommit=True,增删改查自动映射到数据库中
    # 2.通过 conn 创建操作sql的 游标对象
    # 3.编写sql交给 cursor 执行
    # 4.如果是查询，通过 cursor对象 获取结果
    # 5.游标操作
    # 6.操作完毕，端口操作与连接

需求：如何对数据库进行增删改查？

import pymysql
from pymysql.cursors import DictCursor
# 1.建立数据库连接对象
conn = pymysql.connect(user='root', passwd='', db='db1')
# 2.通过连接对象创建操作sql的游标对象，游标不设置参数，查询的结果就是数据元组，数据没有标识性，设置DictCursor，查询的结果是字典，key是表的字段
cursor = conn.cursor(DictCursor)
# 3.编写sql交给游标对象去执行
# 创建表
sql1 = 'create table t2(id int,x int,y int)'
cursor.execute(sql1)
# 增
# 方式一：
sql2 = 'insert into t2 value (1,10,100),(2,20,200),(3,30,300)'
cursor.execute(sql2)
conn.commit()
# 方式二：
sql3 = 'insert into t2 value (%s,%s,%s)'
cursor.execute(sql3, (4, 40, 400))  # 插入单条数据
cursor.executemany(sql3, [(5, 50, 500), (6, 60, 600)])  # 插入多条数据
conn.commit()
# 改
sql4 = 'update t2 set y=666 where id=2'
cursor.execute(sql4)
conn.commit()
# 删
sql5 = 'delete from t2 where id=%s'
cursor.execute(sql5, 4)
conn.commit()
# 查
# 4.若是查询，通过游标对象获取结果
# fetchone() 偏移一条取出，fetchmany(n) 偏移n条取出，fetchall() 偏移剩余全部
sql6 = 'select * from t1'
row = cursor.execute(sql6)  # 返回值是受影响的行
if row:
    r1 = cursor.fetchmany(5)
    print(r1)  # 以列表形式返回
    # 5.操作游标
    cursor.scroll(-3, 'relative')  # relative相对偏移，游标在当前位置进行上下偏移，向上偏移3位
    r2 = cursor.fetchone()
    print(r2)
    cursor.scroll(0, 'absolute')  # absolute绝对偏移，游标重置，从头开始偏移
    r3 = cursor.fetchone()
    print(r3)
# 6.操作完毕，断开游标与连接
cursor.close()
conn.close()

pymysql事务

# 需求：转账案例
import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='root', passwd='', db='db1')
cursor = conn.cursor(DictCursor)
try:
    sql1 = 'update t1 set money=money-1000 where name="tank"'
    cursor.execute(sql1)
    sql2 = 'update t1 set money=money+1000 where name="fuck"'
    cursor.execute(sql2)
except:
    print('转账异常')
    conn.rollback()  # 回滚
else:
    conn.commit()  # 提交事务
    print('转账成功')

sql注入问题

# 定义：通过书写sql，包含注释相关的特殊字符，让原有的sql执行顺序发生改变，从而改变执行的sql。从而绕过原有的sql安全认证，达到对数据库攻击的目的
# 没有处理sql注入的写法
import pymysql
conn=pymysql.connect(user='root',passwd='',db='user_info')
cursor=conn.cursor(pymysql.cursors.DictCursor)
user=input('>>>:')
pwd=input('>>>:')
sql='select * from user_data where user="%s" and password="%s" '%(user,pwd)
row=cursor.execute(sql)
print(row) #打印行数
if row:
    print('登录成功')
else:
    print('登录失败')
# 知道用户名时：user=allen" #
# 不知道用户名时：user=" or 2=2 #

# 如何处理sql注入问题：pymysql自带解决方法，如下：
import pymysql
conn=pymysql.connect(user='root',passwd='',db='user_info')
cursor=conn.cursor(pymysql.cursors.DictCursor)
user=input('>>>:')
pwd=input('>>>:')
sql='select * from user_data where user=%s and password=%s '
row=cursor.execute(sql,(user,pwd))
print(row) #打印行数
if row:
    print('登录成功')
else:
    print('登录失败')
# 总结：自己拼接参数一定有sql注入（除非自行处理特殊字符问题），将数据的占位填充交给pymysql

索引原理

# 索引就是键-key，给表创建键后，该表不仅会形成表结构、表数据，还有键的B+结构图。
# 键的结构图是需要维护的，在数据完成增删改操作时，只要影响到有键的字段，结构图都要维护一次，故创建键后一定会降低增删改的效率。
# 键可以极大的加快查询速度，而实际开发中，几乎业务都和查有关系。
# 建立键的方式：主键、外键、唯一键、index

# 需求：有无索引的查询速度比较
import pymysql
import random
import time
from pymysql.cursors import DictCursor
# 创建连接对象
conn=pymysql.connect(user='root',passwd='',db='index_data')
# 创建游标对象
cursor=conn.cursor(DictCursor)
# # 创建表
# sql1='create table t1(id int primary key auto_increment,x int,y int)'
# # 执行sql语句
# cursor.execute(sql1)
# sql2='create table t2(id int primary key auto_increment,x int,y int,index(x))'
# cursor.execute(sql2)
# 插入1w条数据
# for i in range(1,10001):
#     x=i
#     y=random.randint(1,10000)
#     cursor.execute('insert into t1(x,y) value(%s,%s)',(x,y))
#     cursor.execute('insert into t2(x,y) value(%s,%s)',(x,y))
# conn.commit()

start_time=time.time()
sql1='select * from t1 where id=3967'
cursor.execute(sql1)
end_time=time.time()
print(f'查询t1表id值所需时间：{end_time-start_time}')

start_time=time.time()
sql2='select * from t1 where x=3967'
cursor.execute(sql2)
end_time=time.time()
print(f'查询t1表x值所需时间：{end_time-start_time}')

start_time=time.time()
sql3='select * from t2 where x=3967'
cursor.execute(sql3)
end_time=time.time()
print(f'查询t2表x值所需时间：{end_time-start_time}')
# 总结:含有索引的表2中x查询速度明显高于没有索引的表1中的x

练习题

  • 详细解释下列mysql执行语句的每个参数与参数值的含义？

    mysql -hlocalhost -P3306 -uroot -proot
答：
    mysql：客户端指令
    mysqld：服务端指令
    mysqladmin：管理员指令
    -h：本机地址
    -P：端口号
    -u：用户名
    -p：密码

  • 创建数据库

    # db1：采用默认编码
create database db1;
# db2：采用gbk编码
create database db2 charset='gbk';
# db3：采用utf-8编码
create database db3 charset='utf8';
# 将db2数据库编码修改为utf8编码格式
alter database db2 charset='utf8';
# 删除db3数据库
drop database db3;

  • 创建表操作

    # 在db1数据库内，为其添加一个t1表，表有两个字段（id int,name char）
create table db1.t1(id int,name char(6));
# 在db2数据库内，为其添加一个t2表，表有三个字段（name char,age int,phone int）
create table db2.t2(name char(6),age int,phone int);
#在db2数据库内，为db1数据库添加表tt1，字段可以自定义
create table db1.tt1(id int,addr char(256));
# 修改db1数据库t1表的name字段char类型长度为20，db2数据库t2表的age字段int类型长度为3
alter table db1.t1 modify name char(20);
alter table db2.t2 modify age int(3);
# 删除tt1表
drop table db1.tt1;
# 列举四种查询表的方式
show * from t1; 
desc t1; 
show create table t1; 
show tables;

  • 记录操作

    # 为t1一次性插入三条数据
insert into t1 values(1,'tank'),(2,'jack'),(3,'fuck');
# 为t2分三次插入三条数据
insert into t2 values('aaa',11,11111);
insert into t2 values('bbb',22,22222);
insert into t2 values('ccc',33,33333);
# 分别显示t1、t2表下的所有数据
show * from t1;
show * from t2;
# t1表下根据id条件更改name名字
update t1 set name='allen' where id=1;
# t2表下根据name删除一条记录
delete from t2 where name='aaa';

  • 综合操作

    # 用mysqladmin命令将密码从admin修改为root
mysqladmin -uroot -p123456 password 'admin'
# 创建一个school数据库，字符编码设置成utf8
create database school charset='uft8';
# 在school数据库下创建stu表，表字段分别为name char,age int,sex char
create table school.stu(name char,age int,sex char);
# 用show命令展示stu表
show create table school.stu;
# 更改stu表中sex字段名为char(5)的gender新字段
alter table stu change sex gender char(5);
# 创建一个引擎为myisam的表t1，有x，y两个int类型字段
create table t1(x int,y int) engine=myisam;
# 创建一个user表，有一个is_delete字段，字段值表示用户是否被删除，1表示已删除，0表示未删除
create table user(is_delete enum(0,1));
# 创建一个t3表，id为主键，x为唯一键，y，z为联合唯一键，类型都是int类型
create table t3(id int primary key,
                x int unique,
                y int,
                z int,
                unique(y,z));

  • 创建一个学生student表，条件如下：

    # 有主键id字段，name唯一字段、age字段、height字段、mobile字段
# 主键为自增字段
# name和mobile联合唯一
# age和height不可以出现负数
# 增加记录时，name和height字段必须赋值
# 增加记录时，age未赋值，用0填充，mobile未赋值，用Null填充
# 清空表，并清空主键自增记录
mysql> create table student(id int primary key auto_increment,
                            name char(64) unique not null,
                            age int unsigned default 0,
                            height decimal(5,2) unsigned not null,
                            mobile char(11) default Null,
                            unique(name,mobile));
mysql> truncate student;

  • 创建一个stu表,字段有：自增主键id，不为空姓名，默认值性别(枚举类型)，无限制身高

    # 创建stu表
create table stu(id int primary key auto_increment,
                 name char(64) not null,
                 gender enum('male','female') default 'male',
                 height decimal(5,2) unsigned);
# 插入一条包含id,name,gender,height四个信息的数据
insert into stu values(1,'allen','male',170.00);
# 插入一条name,gender,height三个信息的数据
insert into stu(name,gender,height) values('tank','female',160.00);
# 插入一条只有name信息的数据
insert into stu(name) values('oven');
# 查看表记录
mysql> select * from stu;
+----+-------+--------+--------+
| id | name  | gender | height |
+----+-------+--------+--------+
|  1 | allen | male   | 170.00 |
|  2 | tank  | female | 160.00 |
|  3 | oven  | male   |   NULL |
+----+-------+--------+--------+

  
* 创建一张有姓名、年龄的teacher表

  ```mysql
  # 创建teacher表
  create table teacher(name char(64),age int);
  # 在最后添加工资字段
  alter table teacher add salary decimal(10,2);
  # 在姓名后添加id主键字段
  alter table teacher add id int primary key after name;
  # 将id字段移到表的最前方，形成顺序为id、name、age、salary
  alter table teacher modify id int first;
  # 查看表结构
  mysql> desc teacher;
  +--------+---------------+------+-----+---------+-------+
  | Field  | Type          | Null | Key | Default | Extra |
  +--------+---------------+------+-----+---------+-------+
  | id     | int(11)       | NO   | PRI | 0       |       |
  | name   | char(64)      | YES  |     | NULL    |       |
  | age    | int(11)       | YES  |     | NULL    |       |
  | salary | decimal(10,2) | YES  |     | NULL    |       |
  +--------+---------------+------+-----+---------+-------+

  • 练习表关系

    # 完成学生表与国家表的多对一表关系的创建
# 完成学生表与课程表的多对多表关系的创建
# 完成学生表与学生简介表的一对一表关系的创建
# 将学生表、国家表、课程表、学生简介表四张表放在一起考虑表关系
# 创建国家表
create table country(in int,
                     name varchar(64));
# 创建学生表
create table student(id int name varchar(64),
                     country_id int,
                     foreign key(country_id) references country(id) 
                     on update cascade on delete cascade);
# 创建学生简介表
create table student_info(id int,
                          info varchar(256),
                          student_id int unique,
                          foreign key(student_id) references student(id) 
                          on update cascade on delete cascade);
# 创建课程表
create table course(id int,
                    name varchar(64));
# 创建学生课程关系表
create table student_course(id int,
                            student_id int,
                            course_id int,
                            foreign key(student_id) references student(id)
                            on update cascade on delete cascade,
                            foreign key(course_id) references course(id)
                            on update cascade on delete cascade)；

  • 查询练习

    # 创建职工表
mysql> create table emp(id int not null primary key auto_increment,
                name varchar(10) not null,
                gender enum('男','女','未知') null default '未知',
                age int null default 0,
                salary float null default 0,
                area varchar(20) null default '中国',
                port varchar(20) default '未知',
                dep varchar(20));
# 插入职工数据
mysql> insert into emp values 
	(1, 'yangsir', '男', 42, 10.5, '上海', '浦东', '教职部'),
	(2, 'engo', '男', 38, 9.4, '山东', '济南', '教学部'),
	(3, 'jerry', '女', 30, 3.0, '江苏', '张家港', '教学部'),
	(4, 'tank', '女', 28, 2.4, '广州', '广东', '教学部'),
	(5, 'jiboy', '男', 28, 2.4, '江苏', '苏州', '教学部'),
	(6, 'zero', '男', 18, 8.8, '中国', '黄浦', '咨询部'),
	(7, 'owen', '男', 18, 8.8, '安徽', '宣城', '教学部'),
	(8, 'jason', '男', 28, 9.8, '安徽', '巢湖', '教学部'),
	(9, 'ying', '女', 36, 1.2, '安徽', '芜湖', '咨询部'),
	(10, 'kevin', '男', 36, 5.8, '山东', '济南', '教学部'),
	(11, 'monkey', '女', 28, 1.2, '山东', '青岛', '教职部'),
	(12, 'san', '男', 30, 9.0, '上海', '浦东', '咨询部'),
	(13, 'san1', '男', 30, 6.0, '上海', '浦东', '咨询部'),
	(14, 'san2', '男', 30, 6.0, '上海', '浦西', '教学部'),
	(15, 'ruakei', '女', 67, 2.501, '上海', '陆家嘴', '教学部');
# 查询教学部山东人的平均薪资
select avg(salary) 平均薪资 from emp where dep='教学部' and area='山东';
# 查询姓名中包含英文字母n且居住在上海的人的所有信息
select * from emp where name like '%n%' and area='上海';
# 查询姓名中包含英文字母n但不包含数字的人的所有信息
select * from emp where name like '%n%' and not name regexp '.*[0-9].*';
# 查询各部门的平均年龄并降序排序
select dep 部门,avg(age) 平均年龄 from emp group by dep order by avg(age) desc;
# 查询各部门中年级最大的人的姓名与居住地(户籍+区域)
select max(age), dep from emp group by dep; #查询各部门年龄最大的人
select name, concat_ws('-', area, port) from emp 
where (age, dep) in (('36', '咨询部'),('38', '教学部'),('42', '教职部')); #举例说明
select name, concat_ws('-', area, port) from emp 
where (age, dep) in (select max(age), dep from emp group by dep);
# 查询不同年龄层次平均薪资大于5，组中工资最高者的姓名与薪资
select age, max(salary) from emp group by age having avg(salary) > 5;
select name, salary from emp where (age, salary) in (select age, max(salary) from emp group by age having avg(salary) > 5);

posted on 2019-09-27 10:42  岱宗夫  阅读(167)  评论(0)  编辑  收藏  举报

导航