文章目录
一、表操作之完整性约束（一）介绍（1）什么是约束条件（2）作用：（3）主要分类及说明：
（二）not null与default（三）unique（1）单列唯一（2）联合唯一
（四）primary key（1）主键是什么（2）**特点：**（3）代码验证
（五）auto_increment（六）foreign key（1）什么是foreign key（2）如何找出两张表之间的关系（3）建立表之间的关系1.一对多或称为多对一2.多对多3.一对一
二、记录相关操作（一）插入数据insert（二）更新数据update（三）删除数据delete（四）**查询数据select**之单表查询（1）单表查询的语法（2）关键字的执行优先级(重点)（3）简单查询（4）where约束1.where字句中可以使用：2.代码示例
（5）分组查询：group by1.什么是分组？为什么要分组2.only_full_group_by3.group by4.聚合函数
（6）having过滤（7）查询排序：order by1.按单列排序2.按多列排序：
（8）限制查询的记录数：limit（9）使用正则表达式查询
一、表操作之完整性约束
（一）介绍
（1）什么是约束条件
约束条件与数据类型的宽度一样，都是可选参数。
（2）作用：
用于保证数据的完整性和一致性。
（3）主要分类及说明：
PRIMARY KEY（PK） # 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY（FK） # 标识该字段为该表的外键
NOT NULL # 标识该字段不能为空
UNIQUE KEY（UK） # 标识该字段的值是唯一的
AUTO_INCREMENT # 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT # 为该字段设置默认值
UNSIGNED # 无符号
ZEROFILL # 使用0填充
1) 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值；
2) 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 # 必须为正值(无符号) 不允许为空 默认是20
3) 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)
（二）not null与default
是否可空，null表示空，非字符串。not null——>不可空；null——>可空。
default是默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值。
# 代码验证
==================not null====================
mysql> create table t1(x int not null); # 设置字段id不为空
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> insert t1 values(); # 不能插入空
"""ERROR 1364 (HY000): Field 'x' doesn't have a default value"""
==================default====================
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
mysql> create table t2(x int not null default 666);
mysql> insert t2 values();
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x | int(11) | NO | | 666 | |
+-------+---------+------+-----+---------+-------+
mysql> select * from t2;
+-----+
| x |
+-----+
| 666 |
+-----+
（三）unique
unique是唯一约束，可以单列唯一，也可以多个条件联合唯一。
（1）单列唯一
mysql> create table t3(name varchar(10) unique);
mysql> insert t3 values("egon");
mysql> insert t3 values("DSB");
mysql> insert t2 values("DSB");
ERROR 1366 (HY000): Incorrect integer value: 'DSB' for column 'x' at row 1
（2）联合唯一
mysql> create table server(
-> id int,
-> name varchar(10),
-> ip varchar(15),
-> port int,
-> unique(ip,port),
-> unique(name)
-> );
mysql> desc server;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | UNI | NULL | |
| ip | varchar(15) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> insert server values (1,"web1","10.10.0.11",8080);
mysql> insert server values (2,"web2","10.10.0.11",8081);
mysql> insert server values(4,"web4","10.10.0.11",8081);
-- ERROR 1062 (23000): Duplicate entry '10.10.0.11-8081' for key 'ip'
mysql> insert server values(3,"网页3","10.10.0.11",8082);
mysql> select * from server;
+------+---------+------------+------+
| id | name | ip | port |
+------+---------+------------+------+
| 1 | web1 | 10.10.0.11 | 8080 |
| 2 | web2 | 10.10.0.11 | 8081 |
| 3 | 网页3 | 10.10.0.11 | 8082 |
+------+---------+------------+------+
（四）primary key
（1）主键是什么
主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表。
（2）特点：
一张innodb表中必须有且只有一个主键，可以：单列做主键，也可以多列做主键（复合/联合主键）。
主键的约束效果是not null + unique。
（3）代码验证
==================单列做主键==================
# 方法一：在某一个字段后用primary key
mysql> create table t6(
-> id int primary key auto_increment, # 主键 # 不指定id，则自动增长
-> name varchar(5)
-> );
mysql> insert t6(name) values
-> ("egon"),
-> ("is"),
-> ("DSB"),
-> ("DDB");
mysql> desc t6;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(5) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
mysql> select * from t6;
+----+------+
| id | name |
+----+------+
| 1 | egon |
| 2 | is | # 尽量不要使用关键字作为表格的内容
| 3 | DSB |
| 4 | DDB |
+----+------+
# 方法二：not null+unique
mysql> create table t6_2(
-> id int not null unique, # 主键
-> name varchar(20) not null unique,
-> comment varchar(100)
-> );
mysql> desc t6_2;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | UNI | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
# 方法三：在所有字段后单独定义primary key
mysql> create table t6_3(
-> id int,
-> name varchar(20),
-> comment varchar(100),
-> constraint pk_name primary key(id) # 创建主键并为其命名pk_name
-> );
mysql> desc t6_3;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
==================多列做主键（了解）==================
mysql> create table t7(
-> id int,
-> name varchar(5),
-> primary key(id,name)
-> );
mysql> desc t7;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(5) | NO | PRI | | |
+-------+------------+------+-----+---------+-------+
（五）auto_increment
约束字段为自动增长，被约束的字段必须同时被key约束。
（六）foreign key
（1）什么是foreign key
foreign key是外键，用于关联多张表，对表进行解耦合，提高查询速度。
1) # 先创建被关联表
mysql> create table dep(
-> id int primary key auto_increment,
-> name varchar(6),
-> comment varchar(60)
-> );
2) # 再创建关联表
mysql> create table emp(
-> id int primary key auto_increment,
-> name varchar(10),
-> gender varchar(5),
-> dep_id int,
-> foreign key(dep_id) references dep(id) on delete cascade on update cascade
-> );
3) # 先往被关联表插入数据
mysql> insert dep(id,name) values
-> (1,"技术部"),
-> (2,"人力资源部"),
-> (3,"销售部");
mysql> desc dep;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(6) | YES | | NULL | |
| comment | varchar(60) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
mysql> select * from dep;
+----+-----------------+---------+
| id | name | comment |
+----+-----------------+---------+
| 1 | 技术部 | NULL |
| 2 | 人力资源部 | NULL |
| 3 | 销售部 | NULL |
+----+-----------------+---------+
4) # 再往关联表插入数据
mysql> insert emp(name,gender,dep_id) values
-> ('egon',"male",1),
-> ('alex1',"male",2),
-> ('alex2',"male",2),
-> ('alex3',"male",2),
-> ('李坦克',"male",3),
-> ('刘飞机',"male",3),
-> ('张火箭',"male",3),
-> ('林子弹',"male",3),
-> ('加特林',"male",3);
mysql> desc emp;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| gender | varchar(5) | YES | | NULL | |
| dep_id | int(11) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+----------------+
mysql> select * from emp;
+----+-----------+--------+--------+
| id | name | gender | dep_id |
+----+-----------+--------+--------+
| 1 | egon | male | 1 |
| 2 | alex1 | male | 2 |
| 3 | alex2 | male | 2 |
| 4 | alex3 | male | 2 |
| 5 | 李坦克 | male | 3 |
| 6 | 刘飞机 | male | 3 |
| 7 | 张火箭 | male | 3 |
| 8 | 林子弹 | male | 3 |
| 9 | 加特林 | male | 3 |
+----+-----------+--------+--------+
（2）如何找出两张表之间的关系
1) 先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是
id）。
2) 再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是
id）。
3) 总结：
如果只有步骤
1成立，则是左表多对一右表；
如果只是步骤
2成立，则是右表多对一左表。
如果步骤
1和
2同时成立，则证明这两张表同时一个双向的多对一，即多对多，需要定义一个这两张表的关系的关系表来专门存放二者的关系。
如果
1和
2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key 右表的基础上，将左表的外键字段设置成unique即可。
（3）建立表之间的关系
1.一对多或称为多对一
三张表：出版社，作者，书
一对多（或多对一）：一个出版社可以出版多本书
关联方式：foreign key
=====================多对一=====================
mysql> create table press(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
mysql> create table book(
-> id int primary key auto_increment,
-> name varchar(20),
-> press_id int not null,
-> foreign key(press_id) references press(id) on delete cascade on update cascade
-> );
mysql> insert press(name) values
-> ('不好听出版社'),
-> ('DSB出版社'),
-> ('XXX出版社');
mysql> insert book(name,press_id) values
-> ('九阳神功',1),
-> ('九阴真经',2),
-> ('九阴白骨爪',2),
-> ('独孤九剑',3),
-> ('降龙十巴掌',2),
-> ('葵花宝典',3);
mysql> select * from press;
+----+--------------------------------+
| id | name |
+----+--------------------------------+
| 1 | 不好听出版社 |
| 2 | DSB出版社 |
| 3 | XXX出版社 |
+----+--------------------------------+
mysql> select * from book;
+----+-----------------+----------+
| id | name | press_id |
+----+-----------------+----------+
| 1 | 九阳神功 | 1 |
| 2 | 九阴真经 | 2 |
| 3 | 九阴白骨爪 | 2 |
| 4 | 独孤九剑 | 3 |
| 5 | 降龙十巴掌 | 2 |
| 6 | 葵花宝典 | 3 |
+----+-----------------+----------+
2.多对多
三张表：出版社，作者信息，数
多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
关联方式：foreign key
+ 一张新的表
=====================多对多
=====================
mysql
> create table author
(
-> id int primary key auto_increment
,
-> name varchar
(20)
-> );
mysql
> create table author2book
(
-> id int not null unique auto_increment
,
-> author_id
int not null
,
-> book_id
int not null
,
-> constraint fk_auther foreign key
(author_id
) references author
(id) on delete cascade on update cascade
,
-> constraint fk_book foreign key
(book_id
) references book
(id) on delete cascade on update cascade
,
-> primary key
(auther_id
,book_id
)
-> );
mysql
> insert into author
(name
) values
('egon'),('alex'),('yuanhao'),('wpq');
1 egon
:
1 九阳神功；
2 九阴真经；
3 九阴白骨爪；
4 独孤九剑；
5 降龙十巴掌；
6 葵花宝典；
2 alex
:
1 九阳神功；
6 葵花宝典；
3 yuanhao
:
4 独孤九剑；
5 降龙十巴掌；
6 葵花宝典；
4 wpq
:
1 九阳神功；
mysql
> insert into author2book
(author_id
,book_id
) values
-> (1,1),
-> (1,2),
-> (1,3),
-> (1,4),
-> (1,5),
-> (1,6),
-> (2,1),
-> (2,6),
-> (3,4),
-> (3,5),
-> (3,6),
-> (4,1);
mysql
> desc author2book
;
+-----------+---------+------+-----+---------+----------------+
| Field
| Type
| Null
| Key
| Default
| Extra
|
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO
| UNI
| NULL
| auto_increment
|
| author_id
| int(11) | NO
| PRI
| NULL
| |
| book_id
| int(11) | NO
| PRI
| NULL
| |
+-----------+---------+------+-----+---------+----------------+
mysql
> select
* from author2book
;
+----+-----------+---------+
| id | author_id
| book_id
|
+----+-----------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 5 |
| 6 | 1 | 6 |
| 7 | 2 | 1 |
| 8 | 2 | 6 |
| 9 | 3 | 4 |
| 10 | 3 | 5 |
| 11 | 3 | 6 |
| 12 | 4 | 1 |
+----+-----------+---------+
3.一对一
两张表：学生表和客户表
一对一：一个学上是一个客户，一个客户有可能变成一个学生，即一对一的关系；
关联方式：foreign key
+unique
# 一定是student来foreign key表customer，这样就保证了：
# 1 学生一定是一个客户，
# 2 客户不一定是学生，但有可能成为一个学生
mysql> create table customer(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> qq varchar(10) not null,
-> phone char(16) not null
-> );
mysql> create table student(
-> id int primary key auto_increment,
-> class_name varchar(20) not null,
-> customer_id int unique, # 该字段一定要是唯一的
-> foreign key(customer_id) references customer(id) on delete cascade on update cascade # 外键的字段一定要保证unique
-> );
# 增加客户
mysql> insert customer(name,qq,phone) values
-> ("x001",'100001','123123123'),
-> ("x002",'100002','123123124'),
-> ("x003",'100003','123123125');
# 增加学生
mysql> insert student(class_name,customer_id) values
-> ("linux",3),
-> ("go",1);
mysql> select * from customer;
+----+------+--------+-----------+
| id | name | qq | phone |
+----+------+--------+-----------+
| 1 | x001 | 100001 | 123123123 |
| 2 | x002 | 100002 | 123123124 |
| 3 | x003 | 100003 | 123123125 |
+----+------+--------+-----------+
mysql> select * from student;
+----+------------+-------------+
| id | class_name | customer_id |
+----+------------+-------------+
| 4 | linux | 3 |
| 5 | go | 1 |
+----+------------+-------------+
二、记录相关操作
（一）插入数据insert
1) 插入完整数据（顺序插入）
语法一：
insert 表名(字段1,字段2,字段3…字段n) values(值1,值2,值3…值n);
语法二：
insert 表名 values(值1,值2,值3…值n);
2) 指定字段插入数据
语法：
insert 表名字段1,字段2,字段3…) values (值1,值2,值3…);
3) 插入多条记录
语法：
insert 表名 values
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4) 插入查询结果
语法：
insert 表名(字段1,字段2,字段3…字段n)
select (字段1,字段2,字段3…字段n) from 表2
where …;
（二）更新数据update
语法：
update 表 set 字段1=值1,字段2=值2 where 条件；
（三）删除数据delete
语法：
delete from 表 where 条件;
（四）查询数据select之单表查询
（1）单表查询的语法
select distinct 字段1,字段2,字段3,... from 表名
where 过滤条件
group by 分组的条件
having 筛选条件
order by 排序字段
limit n; 限制条数
（2）关键字的执行优先级(重点)
# 重点：关键字的执行优先级
1) from # 找到表:from
2) where # 拿着where指定的约束条件，去文件/表中取出一条条记录
3) group by # 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4) having # 将分组的结果进行having过滤
5) select # 执行select
6) distinct # 去重
7) order by # 将结果按条件排序：order by
8) limit # 限制结果的显示条数
（3）简单查询
# 示例表和记录
company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
# 创建表
mysql> create table employee(
-> id int not null unique auto_increment,
-> name varchar(20) not null,
-> sex enum('male','female') not null default 'male',
-> age int(3) unsigned not null default 28,
-> hire_date date not null,
-> post varchar(50),
-> post_comment varchar(100),
-> salary double(15,2),
-> office int,
-> depart_id int
-> );
# 查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| 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 | | 28 | |
| 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 | |
+--------------+-----------------------+------+-----+---------+----------------+
# 插入记录
#三个部门：教学，销售，运营
mysql> insert employee(name,sex,age,hire_date,post,salary,office,depart_id) values
-> ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
-> ('alex','male',78,'20150302','teacher',1000000.31,401,1),
-> ('wupeiqi','male',81,'20130305','teacher',8300,401,1),
-> ('yuanhao','male',73,'20140701','teacher',3500,401,1),
-> ('liwenzhou','male',28,'20121101','teacher',2100,401,1),
-> ('jingliyang','female',18,'20110211','teacher',9000,401,1),
-> ('jinxin','male',18,'19000301','teacher',30000,401,1),
-> ('成龙','male',48,'20101111','teacher',10000,401,1),
->
-> ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
-> ('丫丫','female',38,'20101101','sale',2000.35,402,2),
-> ('丁丁','female',18,'20110312','sale',1000.37,402,2),
-> ('星星','female',18,'20160513','sale',3000.29,402,2),
-> ('格格','female',28,'20170127','sale',4000.33,402,2),
->
-> ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
-> ('程咬金','male',18,'19970312','operation',20000,403,3),
-> ('程咬银','female',18,'20130311','operation',19000,403,3),
-> ('程咬铜','male',18,'20150411','operation',18000,403,3),
-> ('程咬铁','female',18,'20140512','operation',17000,403,3)
-> ;
# 简单查询
# 1) 根据全部字段进行查询
mysql> select id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id from employee;
# 2) 查询全部
mysql> select * from employee;
# 3) 根据部分字段进行查询
mysql> select name,salary from employee;
+------------+------------+
| name | salary |
+------------+------------+
| egon | 7300.33 |
| alex | 1000000.31 |
| wupeiqi | 8300.00 |
| yuanhao | 3500.00 |
| liwenzhou | 2100.00 |
| jingliyang | 9000.00 |
| jinxin | 30000.00 |
| 成龙 | 10000.00 |
| 歪歪 | 3000.13 |
| 丫丫 | 2000.35 |
| 丁丁 | 1000.37 |
| 星星 | 3000.29 |
| 格格 | 4000.33 |
| 张野 | 10000.13 |
| 程咬金 | 20000.00 |
| 程咬银 | 19000.00 |
| 程咬铜 | 18000.00 |
| 程咬铁 | 17000.00 |
+------------+------------+
# 避免重复distinct（针对的是记录）
mysql> select distinct post from employee;
+-----------------------------------------+
| post |
+-----------------------------------------+
| 老男孩驻沙河办事处外交大使 |
| teacher |
| sale |
| operation |
+-----------------------------------------+
# 通过四则运算查询
mysql> select name,salary*12 from employee;
+------------+-------------+
| name | salary*12 |
+------------+-------------+
| egon | 87603.96 |
| alex | 12000003.72 |
| wupeiqi | 99600.00 |
| yuanhao | 42000.00 |
| liwenzhou | 25200.00 |
| jingliyang | 108000.00 |
| jinxin | 360000.00 |
| 成龙 | 120000.00 |
| 歪歪 | 36001.56 |
| 丫丫 | 24004.20 |
| 丁丁 | 12004.44 |
| 星星 | 36003.48 |
| 格格 | 48003.96 |
| 张野 | 120001.56 |
| 程咬金 | 240000.00 |
| 程咬银 | 228000.00 |
| 程咬铜 | 216000.00 |
| 程咬铁 | 204000.00 |
+------------+-------------+
mysql> select name,salary*12 as annual_salary from employee; # 可以给四则运算的字段使用as起别名
+------------+---------------+
| name | annual_salary |
+------------+---------------+
| egon | 87603.96 |
| alex | 12000003.72 |
| wupeiqi | 99600.00 |
| yuanhao | 42000.00 |
| liwenzhou | 25200.00 |
| jingliyang | 108000.00 |
| jinxin | 360000.00 |
| 成龙 | 120000.00 |
| 歪歪 | 36001.56 |
| 丫丫 | 24004.20 |
| 丁丁 | 12004.44 |
| 星星 | 36003.48 |
| 格格 | 48003.96 |
| 张野 | 120001.56 |
| 程咬金 | 240000.00 |
| 程咬银 | 228000.00 |
| 程咬铜 | 216000.00 |
| 程咬铁 | 204000.00 |
+------------+---------------+
mysql> select name,salary*12 annual_salary from employee; # 可以给四则运算的字段省略as起别名
# 定义显示格式
1) concat() 函数用于连接字符串
mysql> select concat('姓名：',name," 年薪：",salary*12) as annual_salary from employee; # 还可以添加其他字符串，如货币符号等等
+-----------------------------------------+
| annual_salary |
+-----------------------------------------+
| 姓名：egon 年薪：87603.96 |
| 姓名：alex 年薪：12000003.72 |
| 姓名：wupeiqi 年薪：99600.00 |
| 姓名：yuanhao 年薪：42000.00 |
| 姓名：liwenzhou 年薪：25200.00 |
| 姓名：jingliyang 年薪：108000.00 |
| 姓名：jinxin 年薪：360000.00 |
| 姓名：成龙 年薪：120000.00 |
| 姓名：歪歪 年薪：36001.56 |
| 姓名：丫丫 年薪：24004.20 |
| 姓名：丁丁 年薪：12004.44 |
| 姓名：星星 年薪：36003.48 |
| 姓名：格格 年薪：48003.96 |
| 姓名：张野 年薪：120001.56 |
| 姓名：程咬金 年薪：240000.00 |
| 姓名：程咬银 年薪：228000.00 |
| 姓名：程咬铜 年薪：216000.00 |
| 姓名：程咬铁 年薪：204000.00 |
+-----------------------------------------+
# 2) concat_ws() 第一个参数为分隔符
# 3) 结合case语句：
select (
case
when name="egon" then
name
when name='alex' then
concat(name,"_dsb")
else
concat(name,"_sb")
end
) as 名字 from employee;
+---------------+
| 名字 |
+---------------+
| egon |
| alex_dsb |
| wupeiqi_sb |
| yuanhao_sb |
| liwenzhou_sb |
| jingliyang_sb |
| jinxin_sb |
| 成龙_sb |
| 歪歪_sb |
| 丫丫_sb |
| 丁丁_sb |
| 星星_sb |
| 格格_sb |
| 张野_sb |
| 程咬金_sb |
| 程咬银_sb |
| 程咬铜_sb |
| 程咬铁_sb |
+---------------+
（4）where约束
1.where字句中可以使用：
1) 比较运算符：> < >= <= !=
2) between 80 and 100 : 值在80到100之间
3) in(80,90,100) : 值是80或90或100
4) like "r%" 或 like "r_"
pattern(模式)可以是 % 或 _
%表示任意多字符
_表示一个字符
5) 逻辑运算符：在多个条件中可以使用逻辑运算符 and or not
2.代码示例
# 1) 单条件查询
select name from employee where post="sale";
# 2) 多条件查询
select name,salary from employee where post="teacher" and salary>10000;
# 3) 关键字between and
select name,salary from employee where salary between 10000 and 20000;
select name,salary from employee where salary not between 10000 and 20000;
# 4) 关键字is null(判断某个字段是否为null不能用等号，需要使用is)
select name,post_comment from employee where post_comment is null;
select name,post_comment from employee where post_comment is not null; # Empty set (0.00 sec)
select name,post_comment from employee where post_comment=""; # 注意！！：""是空字符串，不是null，null是写入的时候什么都没写，空字符串是写入了空字符串。
PS：执行 update employee set post_comment="" where id=2; 在使用查询空字符串就会有结果。
# 5) 关键字in集合查询
select name,salary from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000;
select name,salary from employee where salary in (3000,3500,4000,9000);
select name,salary from employee where salary not in (3000,3500,4000,9000);
# 6) 关键字like模糊查询
通配符"%"
select * from employee where name like "eg%";
通配符"_"
select * from employee where name like "ale_";
（5）分组查询：group by
1.什么是分组？为什么要分组
①首先，分组发生在where之后，即分组是基于where之后的记录进行的；
②分组指的是：将所有的记录按照某个相同的字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等等；
③为何要进行分组？
取每个部门的最高工资；取每个部门的员工数；取男性和女性的数量；
④大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数。
2.only_full_group_by
！！！SQL_MODE设置！！！
# ！！！SQL_MODE设置！！！
# 查看MySQL 5.7默认的sql_mode如下：
mysql> select @@global.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
# ！！！注意
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。
# 设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
# 不设置ONLY_FULL_GROUP_BY时查看分组的数据
mysql> select @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
mysql> select * from employee group by post;
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
# 由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的
mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
mysql> quit # 设置成功后，一定要退出，然后重新登录方可生效
Bye
mysql> use db2
Database changed
mysql> select * from employee group by post;
-- ERROR 1055 (42000): 'db2.employee.id' isn't in GROUP BY # 报错
mysql> select post,count(id) from employee group by post; # 只能查看分组依据和使用聚合函数
+-----------------------------------------+-----------+
| post | count(id) |
+-----------------------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+-----------------------------------------+-----------+
3.group by
# 1) 单独使用group by关键字分组
select post from employee group by post;
注意：我们按照post字段分组，那么select查询字段只能是post，想要获取组内的其他相关信息，需要借助函数。
+-----------------------------------------+
| post |
+-----------------------------------------+
| operation |
| sale |
| teacher |
| 老男孩驻沙河办事处外交大使 |
+-----------------------------------------+
# 2) group by 关键字和group_concat()函数一起使用
select post,group_concat(name) from employee group by post; # 按照岗位分组，并查看组内成员名
select post,group_concat(name) as emp_member from employee group by post;
# 3) group by与聚合函数一起使用
select post,count(id) as count from employee group by post; # 按照岗位分组，并查看每个组有多少人
+-----------------------------------------+-------+
| post | count |
+-----------------------------------------+-------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+-----------------------------------------+-------+
强调：
1) 如果我们用unique的字段作为分组依据，则每一条记录自称一组，这种分组没有任何意义；
2) 多条记录之间的某个字段值相等，该字段通常用来作为分组的依据。
4.聚合函数
强调：聚合函数聚合的是组的内容，若是没有分组，则默认整体是一个组。
# 示例：
select count(*) from employee;
select count(*) from employee where depart_id=1;
select max(salary) from employee;
select min(salary) from employee;
select avg(salary) from employee;
select sum(salary) from employee;
select sum(salary) from employee where depart_id=3;
（6）having过滤
having与where不一样的地方：
# !!!执行优先级从高到低：where > group by > having
# 1) where 发生在分组 group by 之前，因而where中可以有任意字段，但是绝对不能使用聚合函数；
# 2) having 发生在分组 group by 之后，因而having中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数。
# 代码验证
mysql> select @@sql_mode;
+--------------------+
| @@sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
mysql> select * from employee where salary > 100000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
mysql> select * from employee having salary > 100000;
-- ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause
mysql> select post,group_concat(name) from employee group by having salary > 10000; # 错误，分组后无法直接取到salary字段
-- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'having salary > 10000' at line 1
mysql> select post,group_concat(name) from employee group by post having avg(salary) > 10000;
+-----------+---------------------------------------------------------+
| post | group_concat(name) |
+-----------+---------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
+-----------+---------------------------------------------------------+
（7）查询排序：order by
1.按单列排序
# 示例：
select * from employee order by salary; # 默认升序排列
select * from employee order by salary asc; # 升序排列
select * from employee order by salary desc; # 降序排列
2.按多列排序：
主要条件先排序，次要条件在主要条件无法分出先后时参与排序
# 示例：
select * from employee order by age,salary desc; # 年龄升序，薪资降序
select post,avg(salary) as v from employee where sex="male" group by post having avg(salary>3000) order by v; # 1) from 表 employee；2) where指定的约束条件sex="male"； 3) 分组group by post； 4) 将分组的结果进行having avg(salary>3000)过滤； 4) 执行select； 5) 去重(本语句无)； 6) 将结果按条件排序：order by v（即avg(salary) ）； 7) 限制结果的显示条数(本语句无)。
+-----------------------------------------+---------------+
| post | v |
+-----------------------------------------+---------------+
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
| operation | 16000.043333 |
| teacher | 175650.051667 |
+-----------------------------------------+---------------+
（8）限制查询的记录数：limit
# 示例：
select * from employee order by salary desc limit 3; # 默认初始位置为0
select * from employee order by salary desc limit 0,5; # 从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
select * from employee order by salary desc limit 5,5; # 从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
（9）使用正则表达式查询
# 示例：
select * from employee where name regexp "^ale";
select * from employee where name regexp "on$";
select name,id,age,salary from employee where name regexp "程{1}";
+-----------+----+-----+----------+
| name | id | age | salary |
+-----------+----+-----+----------+
| 程咬金 | 15 | 18 | 20000.00 |
| 程咬银 | 16 | 18 | 19000.00 |
| 程咬铜 | 17 | 18 | 18000.00 |
| 程咬铁 | 18 | 18 | 17000.00 |
+-----------+----+-----+----------+
# 小结：对字符串匹配的当时
where name="alex";
where name like "yua%";
where name regexp "on$"; # 以on为结尾的name字段的记录
