1. MySQL数据查询
1.1.1. MySQL数据操作： DML
========================================================
在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括
- 使用INSERT实现数据的插入
- UPDATE实现数据的更新
- 使用DELETE实现数据的删除
- 使用SELECT查询数据以及。
========================================================
本节内容包括：
插入数据 更新数据 删除数据 查询数据
1.1.2. 插入数据insert
语法定义
1. 插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法：
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
1.1.3. 更新数据update
语法
语法：
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
示例：
UPDATE mysql.user SET password=password(‘123’)
where user=’root’ and host=’localhost’;
案例
mysql> select * from student;
+-----+-------+--------+----------+
| sid | sname | gender | class_id |
+-----+-------+--------+----------+
| 1 | 钢蛋 | 女 | 1 |
| 2 | 铁锤 | 女 | 1 |
| 3 | 山炮 | 男 | 2 |
+-----+-------+--------+----------+
3 rows in set (0.00 sec)
mysql>
mysql> update student set sname='钢鸭蛋' where sid='1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+-----+--------+--------+----------+
| sid | sname | gender | class_id |
+-----+--------+--------+----------+
| 1 | 钢鸭蛋 | 女 | 1 |
| 2 | 铁锤 | 女 | 1 |
| 3 | 山炮 | 男 | 2 |
+-----+--------+--------+----------+
3 rows in set (0.00 sec)
1.1.4. 删除数据
语法
语法：
DELETE FROM 表名
WHERE CONITION;
示例：
DELETE FROM mysql.user
WHERE password=’’;
案例
mysql> select * from student;
+-----+--------+--------+----------+
| sid | sname | gender | class_id |
+-----+--------+--------+----------+
| 1 | 钢鸭蛋 | 女 | 1 |
| 2 | 铁锤 | 女 | 1 |
| 3 | 山炮 | 男 | 2 |
+-----+--------+--------+----------+
3 rows in set (0.00 sec)
mysql> delete from student where gender='男';
Query OK, 1 row affected (0.01 sec)
mysql>
mysql>
mysql> select * from student;
+-----+--------+--------+----------+
| sid | sname | gender | class_id |
+-----+--------+--------+----------+
| 1 | 钢鸭蛋 | 女 | 1 |
| 2 | 铁锤 | 女 | 1 |
+-----+--------+--------+----------+
2 rows in set (0.00 sec)
1.1.5. 单表查询
语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
1.1.6. mysql关键字的执行优先级(重点)
重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
查询案例
员工表字段设计
创建表
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 | |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
插入员工表记录
insert into 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)
;
简单查询
#简单查询
SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id
FROM employee;
SELECT * FROM employee;
SELECT name,salary FROM employee;
#避免重复DISTINCT
SELECT DISTINCT post FROM employee;
#通过四则运算查询，查出这个人的年薪
SELECT name, salary*12 FROM employee;
#给结果添加字段别名
SELECT name, salary*12 AS Annual_salary FROM employee;
SELECT name, salary*12 Annual_salary FROM employee;
1.1.7. where约束条件
语法
比较运算符：><>= <= <> !=
between 80 and 100 值在10到20之间
in(80,90,100) 值是10或20或30
like 'oldboy%'
pattern可以是%或_，
%表示任意多字符
_表示一个字符
逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
实战案例
1.查看销售团队的员工信息
mysql> select * from employee where post='sale';
+----+------+--------+-----+------------+------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+--------+-----+------------+------+--------------+---------+--------+-----------+
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
+----+------+--------+-----+------------+------+--------------+---------+--------+-----------+
5 rows in set (0.00 sec)
2.查看所有教师的名字
mysql> select name from employee where post='teacher';
+------------+
| name |
+------------+
| alex |
| wupeiqi |
| yuanhao |
| liwenzhou |
| jingliyang |
| jinxin |
| 成龙 |
+------------+
7 rows in set (0.00 sec)
3.查看月入过关的老师的名字和薪资信息
关键词 where、and
mysql> select name,salary from employee where post='teacher' and salary>10000;
+--------+------------+
| name | salary |
+--------+------------+
| alex | 1000000.31 |
| jinxin | 30000.00 |
+--------+------------+
2 rows in set (0.01 sec)
4.查看薪资在1~2W之间的老师名字和具体薪资信息
关键词where、between and
mysql> select name,salary from employee where salary between 10000 and 20000;
+--------+----------+
| name | salary |
+--------+----------+
| 成龙 | 10000.00 |
| 张野 | 10000.13 |
| 程咬金 | 20000.00 |
| 程咬银 | 19000.00 |
| 程咬铜 | 18000.00 |
| 程咬铁 | 17000.00 |
+--------+----------+
6 rows in set (0.01 sec)
5.查看薪资不在1~2W之间的老师名字和具体薪资信息
关键词 where 、not between and
mysql> select name,salary from employee where salary not between 10000 and 20000;
+------------+------------+
| name | salary |
+------------+------------+
| egon | 7300.33 |
| alex | 1000000.31 |
| wupeiqi | 8300.00 |
| yuanhao | 3500.00 |
| liwenzhou | 2100.00 |
| jingliyang | 9000.00 |
| jinxin | 30000.00 |
| 歪歪 | 3000.13 |
| 丫丫 | 2000.35 |
| 丁丁 | 1000.37 |
| 星星 | 3000.29 |
| 格格 | 4000.33 |
+------------+------------+
12 rows in set (0.00 sec)
6.关键字IN集合查询
关键字 IN 、NOT IN
#方法一：查询员工里，工资在一个集合范围内的名字和待遇信息
mysql> SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
#方法二
mysql> select name,salary from employee where salary in (3000,3500,4000,9000);
#在集合以外的数据
mysql> select name,salary from employee where salary not in (3000,3500,4000,9000);
7.关键字LIKE进行模糊查询
语法
通配符’%’
SELECT * FROM employee
WHERE name LIKE 'eg%';
通配符’_’
SELECT * FROM employee
WHERE name LIKE 'al__';
案例
查询名字以a开头的员工信息
mysql> select * from employee where name like 'a%';
查询年纪在20-30之间的员工信息
select * from employee where age between 20 and 30;
select * from employee where age like '2%' ;
8.关键字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;
SELECT name,post_comment FROM employee
WHERE post_comment=''; 注意''是空字符串，不是null
1.1.8. 案例练习
1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
答案
select name,age from employee where post = 'teacher';
select name,age from employee where post='teacher' and age>30;
select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
select * from employee where post_comment is not null;
select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
select name,salary*12 as annual_salary from employee where post='teacher' and name like 'jin%';
1.1.9. 分组查询：group by
什么是分组？为什么要分组？
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
#3、为何要分组呢？
取每个部门的最高工资 按部门分类
取每个部门的员工数 按部门分类
取男人数和女人数 按性别分组
小窍门：‘每’这个字后面的字段，就是我们分组的依据，也就是group by后面跟着的字段
#4、大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
分组也就是分类，找出大家相同点。。。。。。
例如按照性别分组，分为两类，男和女，然后可以统计，男人有多少，女人有多少
也可以按照职位，入职时间分组，，大家的相同点。。。然后进行分组 归类
1.1.10. group by
知识点如下：
单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
GROUP BY关键字和GROUP_CONCAT()函数一起使用
提示GROUP_CONCAT()函数,group_concat 用在 group 分组的时候，连接多行的字段拼接在一起。
SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
GROUP BY与聚合函数一起使用
select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人
1.1.11. 聚合函数
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
聚合函数：
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
示例：
SELECT COUNT(*) FROM employee; #统计员工信息有多少条
SELECT COUNT(*) FROM employee WHERE depart_id=1; #统计部门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; #求3号部门工资总和
案例
每个部门有多少个员工
技巧：每 字后面，就是分组依据
mysql> select post,count(id) from employee group by post;
+----------------------------+-----------+
| post | count(id) |
+----------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+----------------------------+-----------+
4 rows in set (0.00 sec)
求每个部门最高工资
mysql> select post,max(salary) from employee group by post;
+----------------------------+-------------+
| post | max(salary) |
+----------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+----------------------------+-------------+
4 rows in set (0.00 sec)
求每个部门最低工资
求每个部门平均工资
求每个部门工资和
select post,min(salary) from employee group by post;
select post,avg(salary) from employee group by post;
select post,sum(salary) from employee group by post;
1.1.12. 分组练习题
1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
题1
提示GROUP_CONCAT()函数,group_concat 用在 group 分组的时候，连接多行的字段拼接在一起
mysql> select post,group_concat(name) as member_name from employee group by post;
+----------------------------+-------------------------------------------------------+
| post | member_name |
+----------------------------+-------------------------------------------------------+
| operation | 张野,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 歪歪,丫丫,丁丁,星星,格格 |
| teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 |
| 老男孩驻沙河办事处外交大使 | egon |
+----------------------------+-------------------------------------------------------+
4 rows in set (0.00 sec)
题2
mysql> select post,count(id) from employee group by post;
题3
mysql> select sex,count(id) from employee group by sex;
+--------+-----------+
| sex | count(id) |
+--------+-----------+
| male | 10 |
| female | 8 |
+--------+-----------+
2 rows in set (0.00 sec)
题4
mysql> select post,avg(salary) from employee group by post;
+----------------------------+---------------+
| post | avg(salary) |
+----------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
+----------------------------+---------------+
题5
mysql> select post,max(salary) from employee group by post;
+----------------------------+-------------+
| post | max(salary) |
+----------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+----------------------------+-------------+
4 rows in set (0.00 sec)
题6
mysql> select post,min(salary) from employee group by post;
+-----------------------------------------+-------------+
| post | min(salary) |
+-----------------------------------------+-------------+
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+-----------------------------------------+-------------+
题7
mysql> select sex,avg(salary) from employee group by sex;
+--------+---------------+
| sex | avg(salary) |
+--------+---------------+
| male | 110920.077000 |
| female | 7250.183750 |
+--------+---------------+
2 rows in set (0.00 sec)
1.1.13. having过滤
#！！！执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
示例
查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
备注：
group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。
要返回哪些列，由函数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。
#拆分这个需求，以上信息都来自于员工表
select * from employee;
#想要每个岗位内的信息，也就是要对岗位进行分组
select * from employee group by post
#对岗位分组之后进行过滤，想要岗位内，员工个数小于2的，我们可以先筛选出每个岗位的人数
SQL是： select post,count(id) from employee group by post;
+----------------------------+-----------+
| post | count(id) |
+----------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+----------------------------+-----------+
4 rows in set (0.00 sec)
然后注意having过滤是在分组之后，进行条件过滤，员工个数小于2的员工名字，和个数
select post,group_concat(name),count(id) from employee group by post having count(id)<2;
查询各岗位平均薪资大于10000的岗位名、平均工资
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)
查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary)>10000 and avg(salary) <20000;
1.1.14. 查询排序 order by
单列排序
#按工资从低到高排序，asc
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
#按工资从高到低排序，desc
SELECT * FROM employee ORDER BY salary DESC;
按多列排序
#先age从小到大排序，如果相同，根据工资从小到高排序
select * from employee order by age,salary asc;
排序综合练习
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
解答
mysql> select * from employee order by age,hire_date desc;
mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary);
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.01 sec)
mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;
1.1.15. 限制查询的记录数量limit
示例
#查询所有员工信息，根据薪资降序排序，显示前3名
select * from employee order by salary desc limit 3; #默认起点是0，查3条
#查询所有员工信息，根据薪资升序排序，显示前5名
select * from employee order by salary asc limit 0,5; #默认起始点是0，查5条
select * from employee order by salary desc limit 5,5; #从第五条记录开始，即先出第六6条，向后5条
练习，显示出所有员工信息，每页5条记录
mysql> select * from employee limit 0,5;
mysql> select * from employee limit 5,5;
mysql> select * from employee limit 10,5;