数据操作
数据操作
1.插入数据
1. 插入完整数据（顺序插入） 语法一： insert into 表名(字段1,字段2,字段3…字段n) values(值1,值2,值3…值n); 例如：insert into student(id,name,sex)values( （1,'david','male'）, (2,'jeney','female')); 语法二： insert into 表名 values (值1,值2,值3…值n); 例如：insert into student values( （1,'david','male'）, (2,'jeney','female')); 2. 插入查询结果 语法： insert into 表名(字段1,字段2,字段3…字段n) select (字段1,字段2,字段3…字段n) from 表2 where, group by,having,order by,limit;
2.更新数据
语法： UPDATE 表名 SET 字段1=值1, 字段2=值2, WHERE CONDITION; 示例： UPDATE mysql.user SET password=password(‘123’) where user=’root’ and host=’localhost’;
3.删除数据
语法： delete from 表名 where conition; 示例： delete from mysql.user where password=’’;
4.单表查询
- 单表查询语法
select 字段1，字段2，distinct 字段3...... from 表名 where 约束条件 group by 分组的字段 having 过滤条件 order by 排序字段 limit 限制条件
- 关键字的执行优先级
重点：关键字的执行优先级 from where group by having select distinct order by limit
先找到表:from
有where就依照其指定的约束条件去表中取出一条条记录，没有where就执行下面的关键字
有group by按照其分组依据取出一条条记录，没有group by，整体作为一组，并继续往下执行
将分组结果进行having过滤，可以没有
执行select
执行distinct去重
将结果按条件排序：order by，可以没有
限制结果的显示条数：limit，可以没有
- 简单查询
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 -> ); Query OK, 0 rows affected (2.03 sec) 插入记录 ysql> 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) -> ; Query OK, 18 rows affected (0.06 sec) Records: 18 Duplicates: 0 Warnings: 0 mysql> select * from employee; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 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 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec)
mysql> select -> id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id -> from employee; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 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 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) mysql> select * from employee; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 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 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) 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 | +------------+------------+ 18 rows in set (0.00 sec) #去重distinct mysql> select distinct post from employee; +-----------------------------------------+ | post | +-----------------------------------------+ | 老男孩驻沙河办事处外交大使 | | teacher | | sale | | operation | +-----------------------------------------+ 4 rows in set (0.00 sec) #通过四则运算查询 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 | +------------+-------------+ 18 rows in set (0.00 sec) mysql> select name, salary*12 as Annual_salary from employee; +------------+---------------+ | 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 | +------------+---------------+ 18 rows in set (0.00 sec) mysql> select name, salary*12 Annual_salary from employee; +------------+---------------+ | 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 | +------------+---------------+ 18 rows in set (0.00 sec) #定义显示格式 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 | +---------------------------------------+ 18 rows in set (0.00 sec) concat_ws() 第一个参数为分隔符 mysql> select concat_ws(':',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 | +----------------------+ 18 rows in set (0.00 sec)
- where约束
where字句中可以使用： 1） 比较运算符：> < >= <= <>（不等于，一般不用） != 2） between 80 and 100 值在10到20之间 3） in(80,90,100) 值是10或20或30 4） like 'egon%' pattern可以是%或_， %表示任意多字符 _表示一个字符 5） 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
1）单条件查询 mysql> select name from employee -> where post='sale'; +--------+ | name | +--------+ | 歪歪 | | 丫丫 | | 丁丁 | | 星星 | | 格格 | +--------+ 5 rows in set (1.81 sec) 2）多条件查询 mysql> select id,name from employee where id >=3 and id < 5; +----+---------+ | id | name | +----+---------+ | 3 | wupeiqi | | 4 | yuanhao | +----+---------+ 2 rows in set (1.04 sec) mysql> select id,name from employee where id <=3 or id >= 10; +----+-----------+ | id | name | +----+-----------+ | 1 | egon | | 2 | alex | | 3 | wupeiqi | | 10 | 丫丫 | | 11 | 丁丁 | | 12 | 星星 | | 13 | 格格 | | 14 | 张野 | | 15 | 程咬金 | | 16 | 程咬银 | | 17 | 程咬铜 | | 18 | 程咬铁 | +----+-----------+ 12 rows in set (0.00 sec) mysql> select id,name from employee where id between 3 and 5; +----+-----------+ | id | name | +----+-----------+ | 3 | wupeiqi | | 4 | yuanhao | | 5 | liwenzhou | +----+-----------+ 3 rows in set (1.82 sec) mysql> select id,name from employee where id >=3 and id <= 5; +----+-----------+ | id | name | +----+-----------+ | 3 | wupeiqi | | 4 | yuanhao | | 5 | liwenzhou | +----+-----------+ 3 rows in set (0.00 sec) mysql> select id,name,age from employee -> where age = 60 or age = 70 or age = 80 or age = 18; +----+------------+-----+ | id | name | age | +----+------------+-----+ | 1 | egon | 18 | | 6 | jingliyang | 18 | | 7 | jinxin | 18 | | 11 | 丁丁 | 18 | | 12 | 星星 | 18 | | 15 | 程咬金 | 18 | | 16 | 程咬银 | 18 | | 17 | 程咬铜 | 18 | | 18 | 程咬铁 | 18 | +----+------------+-----+ 9 rows in set (0.00 sec) mysql> select id,name,age from employee -> where age not in (60,70,80,18,'aaa'); +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | alex | 78 | | 3 | wupeiqi | 81 | | 4 | yuanhao | 73 | | 5 | liwenzhou | 28 | | 8 | 成龙 | 48 | | 9 | 歪歪 | 48 | | 10 | 丫丫 | 38 | | 13 | 格格 | 28 | | 14 | 张野 | 28 | +----+-----------+-----+ 9 rows in set, 9 warnings (0.00 sec) 3）模糊查询 mysql> select id,name,age from employee -> where name like 'e%'; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | egon | 18 | +----+------+-----+ 1 row in set (0.04 sec) mysql>mysql> select id,name,age from employee -> where name not like '___'; +----+------------+-----+ | id | name | age | +----+------------+-----+ | 1 | egon | 18 | | 2 | alex | 78 | | 3 | wupeiqi | 81 | | 4 | yuanhao | 73 | | 5 | liwenzhou | 28 | | 6 | jingliyang | 18 | | 7 | jinxin | 18 | | 8 | 成龙 | 48 | | 9 | 歪歪 | 48 | | 10 | 丫丫 | 38 | | 11 | 丁丁 | 18 | | 12 | 星星 | 18 | | 13 | 格格 | 28 | | 14 | 张野 | 28 | +----+------------+-----+ 14 rows in set (0.00 sec) #null并不是空字符串 mysql> select * from employee where not post_comment is null; Empty set (0.00 sec)
1) 查看岗位是teacher的员工姓名、年龄 mysql> select name,age from employee where post='teacher'; +------------+-----+ | name | age | +------------+-----+ | alex | 78 | | wupeiqi | 81 | | yuanhao | 73 | | liwenzhou | 28 | | jingliyang | 18 | | jinxin | 18 | | 成龙 | 48 | +------------+-----+ 7 rows in set (0.00 sec) 2) 查看岗位是teacher且年龄大于30岁的员工姓名、年龄 mysql> select name,age from employee where post='teacher' and age > 30; +---------+-----+ | name | age | +---------+-----+ | alex | 78 | | wupeiqi | 81 | | yuanhao | 73 | | 成龙 | 48 | +---------+-----+ 4 rows in set (0.03 sec) 3) 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资 mysql> select name,age,salary from employee -> where post='teacher' and salary between 9000 and 10000; +------------+-----+----------+ | name | age | salary | +------------+-----+----------+ | jingliyang | 18 | 9000.00 | | 成龙 | 48 | 10000.00 | +------------+-----+----------+ 2 rows in set (0.00 sec) 4) 查看岗位描述不为NULL的员工信息 mysql> select * from employee where not post_comment is null; Empty set (0.00 sec) 5)查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 mysql> select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000); +------------+-----+----------+ | name | age | salary | +------------+-----+----------+ | jingliyang | 18 | 9000.00 | | jinxin | 18 | 30000.00 | | 成龙 | 48 | 10000.00 | +------------+-----+----------+ 3 rows in set (0.00 sec) 6)查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 mysql> select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000); +-----------+-----+------------+ | name | age | salary | +-----------+-----+------------+ | alex | 78 | 1000000.31 | | wupeiqi | 81 | 8300.00 | | yuanhao | 73 | 3500.00 | | liwenzhou | 28 | 2100.00 | +-----------+-----+------------+ 4 rows in set (0.00 sec) 7)查看岗位是teacher且名字是jin开头的员工姓名、年薪 mysql> select name,salary*12 from employee where post='teacher' and name like 'jin%'; +------------+-----------+ | name | salary*12 | +------------+-----------+ | jingliyang | 108000.00 | | jinxin | 360000.00 | +------------+-----------+ 2 rows in set (0.00 sec)
- 分组group by
1）分组之后，select只能查看到分组的字段，要想查组内内容 不能直接查看，需要借助于聚合函数max，min，avg，sum，count 2）分组的目的是为类以组为单位来处理记录，而不是处理单独的记录 3）如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义 多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
统计：每个部门的员工数 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 count(id) from employee; +-----------+ | count(id) | +-----------+ | 18 | +-----------+ 1 row in set (0.00 sec) 查看teacher部门的员工数 mysql> select count(id) from employee where post='teacher'; +-----------+ | count(id) | +-----------+ | 7 | +-----------+ 1 row 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.04 sec) 查看每个部门的最低工资 mysql> select post,min(salary) from employee group by post; +-----------------------------------------+-------------+ | post | min(salary) | +-----------------------------------------+-------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+-------------+ 4 rows in set (0.00 sec) 查看每个部门的平均工资 mysql> select post,avg(salary) from employee group by post; +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 老男孩驻沙河办事处外交大使 | 7300.330000 | +-----------------------------------------+---------------+ 4 rows in set (0.03 sec) 查看每个部门的总工资 mysql> select post,sum(salary) from employee group by post; +-----------------------------------------+-------------+ | post | sum(salary) | +-----------------------------------------+-------------+ | operation | 84000.13 | | sale | 13001.47 | | teacher | 1062900.31 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+-------------+ 4 rows in set (0.00 sec) 查看每个部门的人数 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,group_concat(name) from employee group by post; +-----------------------------------------+---------------------------------------------------------+ | post | group_concat(name) | +-----------------------------------------+---------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | sale | 格格,星星,丁丁,丫丫,歪歪 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | | 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+---------------------------------------------------------+ 4 rows in set (0.00 sec) 查看每个部门每个人的工资分布 mysql> select post,group_concat('薪资',salary) from employee group by post; +-----------------------------------------+--------------------------------------------------------------------------------------------------------+ | post | group_concat('薪资',salary) | +-----------------------------------------+--------------------------------------------------------------------------------------------------------+ | operation | 薪资17000.00,薪资18000.00,薪资19000.00,薪资20000.00,薪资10000.13 | | sale | 薪资4000.33,薪资3000.29,薪资1000.37,薪资2000.35,薪资3000.13 | | teacher | 薪资10000.00,薪资30000.00,薪资9000.00,薪资2100.00,薪资3500.00,薪资8300.00,薪资1000000.31 | | 老男孩驻沙河办事处外交大使 | 薪资7300.33 | +-----------------------------------------+--------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) 设置分组模式为only_full_group_by mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye 查看分组模式 mysql> select @@global.sql_mode; +--------------------+ | @@global.sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ 1 row in set (0.00 sec) mysql> select * from employee group by post; ERROR 1055 (42000): 'day44.employee.id' isn't in GROUP BY mysql> select post from employee group by post; +-----------------------------------------+ | post | +-----------------------------------------+ | operation | | sale | | teacher | | 老男孩驻沙河办事处外交大使 | +-----------------------------------------+ 4 rows in set (0.00 sec) mysql> select post,name from employee group by post; ERROR 1055 (42000): 'day44.employee.name' isn't in GROUP BY
1）查询岗位名以及岗位包含的所有员工名字 mysql> select post,group_concat(name) from employee group by post; +-----------------------------------------+---------------------------------------------------------+ | post | group_concat(name) | +-----------------------------------------+---------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | sale | 格格,星星,丁丁,丫丫,歪歪 | | teacher | 成龙， ,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | | 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+---------------------------------------------------------+ 4 rows in set (0.00 sec) 2）查询岗位名以及各岗位内包含的员工个数 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) 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 | +-----------------------------------------+---------------+ 4 rows in set (0.00 sec) 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 | +-----------------------------------------+-------------+ 4 rows in set (0.00 sec) 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)
- having过滤
错误：having是在分组之后的，意味着，只能用分组的字段或者聚合函数作为过滤条件 mysql> select post from employee -> group by post -> having salary > 10000; ERROR 1054 (42S22): Unknown column 'salary' in 'having clause' mysql> select * from employee -> having salary > 10000; ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause mysql> select * from employee -> having count(id) > 10000; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 取出员工数大于3的部门 mysql> select post from employee group by post having count(id) > 3; +-----------+ | post | +-----------+ | operation | | sale | | teacher | +-----------+ 3 rows in set (0.00 sec) 取出id是大于10的,员工数大于3的部门 mysql> select post from employee where id > 10 group by post having count(id) > 3; +-----------+ | post | +-----------+ | operation | +-----------+ 1 row in set (0.00 sec)
1）查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 mysql> select post,group_concat(name),count(id) from employee group by post having count(id) < 2; +-----------------------------------------+--------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+--------------------+-----------+ | 老男孩驻沙河办事处外交大使 | egon | 1 | +-----------------------------------------+--------------------+-----------+ 1 row in set (0.00 sec) 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) 3）查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+ 1 row in set (0.00 sec)
- order by排序
默认升序 mysql> select * from employee order by age asc\G; *************************** 1. row *************************** id: 1 name: egon sex: male age: 18 hire_date: 2017-03-01 post: 老男孩驻沙河办事处外交大使 post_comment: NULL salary: 7300.33 office: 401 depart_id: 1 *************************** 2. row *************************** id: 17 name: 程咬铜 sex: male age: 18 hire_date: 2015-04-11 post: operation post_comment: NULL salary: 18000.00 office: 403 depart_id: 3 *************************** 3. row *************************** id: 16 name: 程咬银 sex: female age: 18 hire_date: 2013-03-11 post: operation post_comment: NULL salary: 19000.00 office: 403 depart_id: 3 *************************** 4. row *************************** id: 15 name: 程咬金 sex: male age: 18 hire_date: 1997-03-12 post: operation post_comment: NULL salary: 20000.00 office: 403 depart_id: 3 *************************** 5. row *************************** id: 12 name: 星星 sex: female age: 18 hire_date: 2016-05-13 post: sale post_comment: NULL salary: 3000.29 office: 402 depart_id: 2 *************************** 6. row *************************** id: 11 name: 丁丁 sex: female age: 18 hire_date: 2011-03-12 post: sale post_comment: NULL salary: 1000.37 office: 402 depart_id: 2 *************************** 7. row *************************** id: 18 name: 程咬铁 sex: female age: 18 hire_date: 2014-05-12 post: operation post_comment: NULL salary: 17000.00 office: 403 depart_id: 3 *************************** 8. row *************************** id: 7 name: jinxin sex: male age: 18 hire_date: 1900-03-01 post: teacher post_comment: NULL salary: 30000.00 office: 401 depart_id: 1 *************************** 9. row *************************** id: 6 name: jingliyang sex: female age: 18 hire_date: 2011-02-11 post: teacher post_comment: NULL salary: 9000.00 office: 401 depart_id: 1 *************************** 10. row *************************** id: 13 name: 格格 sex: female age: 28 hire_date: 2017-01-27 post: sale post_comment: NULL salary: 4000.33 office: 402 depart_id: 2 *************************** 11. row *************************** id: 14 name: 张野 sex: male age: 28 hire_date: 2016-03-11 post: operation post_comment: NULL salary: 10000.13 office: 403 depart_id: 3 *************************** 12. row *************************** id: 5 name: liwenzhou sex: male age: 28 hire_date: 2012-11-01 post: teacher post_comment: NULL salary: 2100.00 office: 401 depart_id: 1 *************************** 13. row *************************** id: 10 name: 丫丫 sex: female age: 38 hire_date: 2010-11-01 post: sale post_comment: NULL salary: 2000.35 office: 402 depart_id: 2 *************************** 14. row *************************** id: 9 name: 歪歪 sex: female age: 48 hire_date: 2015-03-11 post: sale post_comment: NULL salary: 3000.13 office: 402 depart_id: 2 *************************** 15. row *************************** id: 8 name: 成龙 sex: male age: 48 hire_date: 2010-11-11 post: teacher post_comment: NULL salary: 10000.00 office: 401 depart_id: 1 *************************** 16. row *************************** id: 4 name: yuanhao sex: male age: 73 hire_date: 2014-07-01 post: teacher post_comment: NULL salary: 3500.00 office: 401 depart_id: 1 *************************** 17. row *************************** id: 2 name: alex sex: male age: 78 hire_date: 2015-03-02 post: teacher post_comment: NULL salary: 1000000.31 office: 401 depart_id: 1 *************************** 18. row *************************** id: 3 name: wupeiqi sex: male age: 81 hire_date: 2013-03-05 post: teacher post_comment: NULL salary: 8300.00 office: 401 depart_id: 1 18 rows in set (0.00 sec) mysql> select * from employee order by age\G; *************************** 1. row *************************** id: 1 name: egon sex: male age: 18 hire_date: 2017-03-01 post: 老男孩驻沙河办事处外交大使 post_comment: NULL salary: 7300.33 office: 401 depart_id: 1 *************************** 2. row *************************** id: 17 name: 程咬铜 sex: male age: 18 hire_date: 2015-04-11 post: operation post_comment: NULL salary: 18000.00 office: 403 depart_id: 3 *************************** 3. row *************************** id: 16 name: 程咬银 sex: female age: 18 hire_date: 2013-03-11 post: operation post_comment: NULL salary: 19000.00 office: 403 depart_id: 3 *************************** 4. row *************************** id: 15 name: 程咬金 sex: male age: 18 hire_date: 1997-03-12 post: operation post_comment: NULL salary: 20000.00 office: 403 depart_id: 3 *************************** 5. row *************************** id: 12 name: 星星 sex: female age: 18 hire_date: 2016-05-13 post: sale post_comment: NULL salary: 3000.29 office: 402 depart_id: 2 *************************** 6. row *************************** id: 11 name: 丁丁 sex: female age: 18 hire_date: 2011-03-12 post: sale post_comment: NULL salary: 1000.37 office: 402 depart_id: 2 *************************** 7. row *************************** id: 18 name: 程咬铁 sex: female age: 18 hire_date: 2014-05-12 post: operation post_comment: NULL salary: 17000.00 office: 403 depart_id: 3 *************************** 8. row *************************** id: 7 name: jinxin sex: male age: 18 hire_date: 1900-03-01 post: teacher post_comment: NULL salary: 30000.00 office: 401 depart_id: 1 *************************** 9. row *************************** id: 6 name: jingliyang sex: female age: 18 hire_date: 2011-02-11 post: teacher post_comment: NULL salary: 9000.00 office: 401 depart_id: 1 *************************** 10. row *************************** id: 13 name: 格格 sex: female age: 28 hire_date: 2017-01-27 post: sale post_comment: NULL salary: 4000.33 office: 402 depart_id: 2 *************************** 11. row *************************** id: 14 name: 张野 sex: male age: 28 hire_date: 2016-03-11 post: operation post_comment: NULL salary: 10000.13 office: 403 depart_id: 3 *************************** 12. row *************************** id: 5 name: liwenzhou sex: male age: 28 hire_date: 2012-11-01 post: teacher post_comment: NULL salary: 2100.00 office: 401 depart_id: 1 *************************** 13. row *************************** id: 10 name: 丫丫 sex: female age: 38 hire_date: 2010-11-01 post: sale post_comment: NULL salary: 2000.35 office: 402 depart_id: 2 *************************** 14. row *************************** id: 9 name: 歪歪 sex: female age: 48 hire_date: 2015-03-11 post: sale post_comment: NULL salary: 3000.13 office: 402 depart_id: 2 *************************** 15. row *************************** id: 8 name: 成龙 sex: male age: 48 hire_date: 2010-11-11 post: teacher post_comment: NULL salary: 10000.00 office: 401 depart_id: 1 *************************** 16. row *************************** id: 4 name: yuanhao sex: male age: 73 hire_date: 2014-07-01 post: teacher post_comment: NULL salary: 3500.00 office: 401 depart_id: 1 *************************** 17. row *************************** id: 2 name: alex sex: male age: 78 hire_date: 2015-03-02 post: teacher post_comment: NULL salary: 1000000.31 office: 401 depart_id: 1 *************************** 18. row *************************** id: 3 name: wupeiqi sex: male age: 81 hire_date: 2013-03-05 post: teacher post_comment: NULL salary: 8300.00 office: 401 depart_id: 1 18 rows in set (0.00 sec) 降序 mysql> select * from employee order by age desc\G; *************************** 1. row *************************** id: 3 name: wupeiqi sex: male age: 81 hire_date: 2013-03-05 post: teacher post_comment: NULL salary: 8300.00 office: 401 depart_id: 1 *************************** 2. row *************************** id: 2 name: alex sex: male age: 78 hire_date: 2015-03-02 post: teacher post_comment: NULL salary: 1000000.31 office: 401 depart_id: 1 *************************** 3. row *************************** id: 4 name: yuanhao sex: male age: 73 hire_date: 2014-07-01 post: teacher post_comment: NULL salary: 3500.00 office: 401 depart_id: 1 *************************** 4. row *************************** id: 9 name: 歪歪 sex: female age: 48 hire_date: 2015-03-11 post: sale post_comment: NULL salary: 3000.13 office: 402 depart_id: 2 *************************** 5. row *************************** id: 8 name: 成龙 sex: male age: 48 hire_date: 2010-11-11 post: teacher post_comment: NULL salary: 10000.00 office: 401 depart_id: 1 *************************** 6. row *************************** id: 10 name: 丫丫 sex: female age: 38 hire_date: 2010-11-01 post: sale post_comment: NULL salary: 2000.35 office: 402 depart_id: 2 *************************** 7. row *************************** id: 14 name: 张野 sex: male age: 28 hire_date: 2016-03-11 post: operation post_comment: NULL salary: 10000.13 office: 403 depart_id: 3 *************************** 8. row *************************** id: 13 name: 格格 sex: female age: 28 hire_date: 2017-01-27 post: sale post_comment: NULL salary: 4000.33 office: 402 depart_id: 2 *************************** 9. row *************************** id: 5 name: liwenzhou sex: male age: 28 hire_date: 2012-11-01 post: teacher post_comment: NULL salary: 2100.00 office: 401 depart_id: 1 *************************** 10. row *************************** id: 17 name: 程咬铜 sex: male age: 18 hire_date: 2015-04-11 post: operation post_comment: NULL salary: 18000.00 office: 403 depart_id: 3 *************************** 11. row *************************** id: 15 name: 程咬金 sex: male age: 18 hire_date: 1997-03-12 post: operation post_comment: NULL salary: 20000.00 office: 403 depart_id: 3 *************************** 12. row *************************** id: 16 name: 程咬银 sex: female age: 18 hire_date: 2013-03-11 post: operation post_comment: NULL salary: 19000.00 office: 403 depart_id: 3 *************************** 13. row *************************** id: 12 name: 星星 sex: female age: 18 hire_date: 2016-05-13 post: sale post_comment: NULL salary: 3000.29 office: 402 depart_id: 2 *************************** 14. row *************************** id: 11 name: 丁丁 sex: female age: 18 hire_date: 2011-03-12 post: sale post_comment: NULL salary: 1000.37 office: 402 depart_id: 2 *************************** 15. row *************************** id: 1 name: egon sex: male age: 18 hire_date: 2017-03-01 post: 老男孩驻沙河办事处外交大使 post_comment: NULL salary: 7300.33 office: 401 depart_id: 1 *************************** 16. row *************************** id: 7 name: jinxin sex: male age: 18 hire_date: 1900-03-01 post: teacher post_comment: NULL salary: 30000.00 office: 401 depart_id: 1 *************************** 17. row *************************** id: 6 name: jingliyang sex: female age: 18 hire_date: 2011-02-11 post: teacher post_comment: NULL salary: 9000.00 office: 401 depart_id: 1 *************************** 18. row *************************** id: 18 name: 程咬铁 sex: female age: 18 hire_date: 2014-05-12 post: operation post_comment: NULL salary: 17000.00 office: 403 depart_id: 3 18 rows in set (0.00 sec) 先按照age升序排，如果age相同，则id降序排序 mysql> select * from employee order by age asc,id desc\G; *************************** 1. row *************************** id: 18 name: 程咬铁 sex: female age: 18 hire_date: 2014-05-12 post: operation post_comment: NULL salary: 17000.00 office: 403 depart_id: 3 *************************** 2. row *************************** id: 17 name: 程咬铜 sex: male age: 18 hire_date: 2015-04-11 post: operation post_comment: NULL salary: 18000.00 office: 403 depart_id: 3 *************************** 3. row *************************** id: 16 name: 程咬银 sex: female age: 18 hire_date: 2013-03-11 post: operation post_comment: NULL salary: 19000.00 office: 403 depart_id: 3 *************************** 4. row *************************** id: 15 name: 程咬金 sex: male age: 18 hire_date: 1997-03-12 post: operation post_comment: NULL salary: 20000.00 office: 403 depart_id: 3 *************************** 5. row *************************** id: 12 name: 星星 sex: female age: 18 hire_date: 2016-05-13 post: sale post_comment: NULL salary: 3000.29 office: 402 depart_id: 2 *************************** 6. row *************************** id: 11 name: 丁丁 sex: female age: 18 hire_date: 2011-03-12 post: sale post_comment: NULL salary: 1000.37 office: 402 depart_id: 2 *************************** 7. row *************************** id: 7 name: jinxin sex: male age: 18 hire_date: 1900-03-01 post: teacher post_comment: NULL salary: 30000.00 office: 401 depart_id: 1 *************************** 8. row *************************** id: 6 name: jingliyang sex: female age: 18 hire_date: 2011-02-11 post: teacher post_comment: NULL salary: 9000.00 office: 401 depart_id: 1 *************************** 9. row *************************** id: 1 name: egon sex: male age: 18 hire_date: 2017-03-01 post: 老男孩驻沙河办事处外交大使 post_comment: NULL salary: 7300.33 office: 401 depart_id: 1 *************************** 10. row *************************** id: 14 name: 张野 sex: male age: 28 hire_date: 2016-03-11 post: operation post_comment: NULL salary: 10000.13 office: 403 depart_id: 3 *************************** 11. row *************************** id: 13 name: 格格 sex: female age: 28 hire_date: 2017-01-27 post: sale post_comment: NULL salary: 4000.33 office: 402 depart_id: 2 *************************** 12. row *************************** id: 5 name: liwenzhou sex: male age: 28 hire_date: 2012-11-01 post: teacher post_comment: NULL salary: 2100.00 office: 401 depart_id: 1 *************************** 13. row *************************** id: 10 name: 丫丫 sex: female age: 38 hire_date: 2010-11-01 post: sale post_comment: NULL salary: 2000.35 office: 402 depart_id: 2 *************************** 14. row *************************** id: 9 name: 歪歪 sex: female age: 48 hire_date: 2015-03-11 post: sale post_comment: NULL salary: 3000.13 office: 402 depart_id: 2 *************************** 15. row *************************** id: 8 name: 成龙 sex: male age: 48 hire_date: 2010-11-11 post: teacher post_comment: NULL salary: 10000.00 office: 401 depart_id: 1 *************************** 16. row *************************** id: 4 name: yuanhao sex: male age: 73 hire_date: 2014-07-01 post: teacher post_comment: NULL salary: 3500.00 office: 401 depart_id: 1 *************************** 17. row *************************** id: 2 name: alex sex: male age: 78 hire_date: 2015-03-02 post: teacher post_comment: NULL salary: 1000000.31 office: 401 depart_id: 1 *************************** 18. row *************************** id: 3 name: wupeiqi sex: male age: 81 hire_date: 2013-03-05 post: teacher post_comment: NULL salary: 8300.00 office: 401 depart_id: 1 18 rows in set (0.00 sec) 查看每个部门的最高工资并将最高工资按升序排列 mysql> select post,max(salary) from employee group by post order by max(salary); +-----------------------------------------+-------------+ | post | max(salary) | +-----------------------------------------+-------------+ | sale | 4000.33 | | 老男孩驻沙河办事处外交大使 | 7300.33 | | operation | 20000.00 | | teacher | 1000000.31 | +-----------------------------------------+-------------+ 4 rows in set (0.00 sec) 取出平均工资>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) 错误：执行having时，select还没有执行 mysql> select post,avg(salary) as x from employee group by post having x > 10000; ERROR 1463 (42000): Non-grouping field 'x' is used in HAVING clause 取出平均工资>10000的部门以及它的平均工资，并将平均工资降序排列 mysql> select post,avg(salary) as x from employee -> group by post -> having avg(salary) > 10000 -> order by x desc -> ; +-----------+---------------+ | post | x | +-----------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | +-----------+---------------+ 2 rows in set (0.00 sec) mysql> select post,avg(salary) as x from employee -> group by post -> having avg(salary) > 10000 -> order by avg(salary) desc -> ; +-----------+---------------+ | post | x | +-----------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | +-----------+---------------+ 2 rows in set (0.00 sec) 错误： mysql> select post,avg(salary) as x from employee -> group by post -> having avg(salary) > 10000 -> order by post -> ; +-----------+---------------+ | post | x | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ 2 rows in set (0.00 sec) mysql> select post,avg(salary) as x from employee -> group by post -> having avg(salary) > 10000 -> order by x -> ; +-----------+---------------+ | post | x | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ 2 rows in set (0.00 sec)
- limit限制条数:总共计数数，30123；一页只能显示，30
mysql> select * from employee limit 3\G; *************************** 1. row *************************** id: 1 name: egon sex: male age: 18 hire_date: 2017-03-01 post: 老男孩驻沙河办事处外交大使 post_comment: NULL salary: 7300.33 office: 401 depart_id: 1 *************************** 2. row *************************** id: 2 name: alex sex: male age: 78 hire_date: 2015-03-02 post: teacher post_comment: NULL salary: 1000000.31 office: 401 depart_id: 1 *************************** 3. row *************************** id: 3 name: wupeiqi sex: male age: 81 hire_date: 2013-03-05 post: teacher post_comment: NULL salary: 8300.00 office: 401 depart_id: 1 3 rows in set (0.00 sec) 取出工资最高的那个员工信息 mysql> select * from employee order by salary desc limit 1\G; *************************** 1. row *************************** id: 2 name: alex sex: male age: 78 hire_date: 2015-03-02 post: teacher post_comment: NULL salary: 1000000.31 office: 401 depart_id: 1 1 row in set (0.00 sec) mysql> select * from employee limit 0,3\G; *************************** 1. row *************************** id: 1 name: egon sex: male age: 18 hire_date: 2017-03-01 post: 老男孩驻沙河办事处外交大使 post_comment: NULL salary: 7300.33 office: 401 depart_id: 1 *************************** 2. row *************************** id: 2 name: alex sex: male age: 78 hire_date: 2015-03-02 post: teacher post_comment: NULL salary: 1000000.31 office: 401 depart_id: 1 *************************** 3. row *************************** id: 3 name: wupeiqi sex: male age: 81 hire_date: 2013-03-05 post: teacher post_comment: NULL salary: 8300.00 office: 401 depart_id: 1 3 rows in set (0.00 sec) mysql> select * from employee limit 3,3\G; *************************** 1. row *************************** id: 4 name: yuanhao sex: male age: 73 hire_date: 2014-07-01 post: teacher post_comment: NULL salary: 3500.00 office: 401 depart_id: 1 *************************** 2. row *************************** id: 5 name: liwenzhou sex: male age: 28 hire_date: 2012-11-01 post: teacher post_comment: NULL salary: 2100.00 office: 401 depart_id: 1 *************************** 3. row *************************** id: 6 name: jingliyang sex: female age: 18 hire_date: 2011-02-11 post: teacher post_comment: NULL salary: 9000.00 office: 401 depart_id: 1 3 rows in set (0.00 sec) mysql> select * from employee limit 6,3\G; *************************** 1. row *************************** id: 7 name: jinxin sex: male age: 18 hire_date: 1900-03-01 post: teacher post_comment: NULL salary: 30000.00 office: 401 depart_id: 1 *************************** 2. row *************************** id: 8 name: 成龙 sex: male age: 48 hire_date: 2010-11-11 post: teacher post_comment: NULL salary: 10000.00 office: 401 depart_id: 1 *************************** 3. row *************************** id: 9 name: 歪歪 sex: female age: 48 hire_date: 2015-03-11 post: sale post_comment: NULL salary: 3000.13 office: 402 depart_id: 2 3 rows in set (0.00 sec)
- 使用正则表达式
mysql> select * from employee where name regexp '^e.*n$'\G; *************************** 1. row *************************** id: 1 name: egon sex: male age: 18 hire_date: 2017-03-01 post: 老男孩驻沙河办事处外交大使 post_comment: NULL salary: 7300.33 office: 401 depart_id: 1 1 row in set (0.00 sec)
5.多表查询
- 准备表
建表 create table department( id int, name varchar(20) ); mysql> create table employee( -> id int primary key auto_increment, -> name varchar(20), -> sex enum('male','female') not null default 'male', -> age int, -> dep_id int -> ); Query OK, 0 rows affected (0.25 sec) 插入数据 mysql> insert into department values -> (200,'技术'), -> (201,'人力资源'), -> (202,'销售'), -> (203,'运营'); Query OK, 4 rows affected (0.88 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into employee(name,sex,age,dep_id) values -> ('egon','male',18,200), -> ('alex','female',48,201), -> ('wupeiqi','male',38,201), -> ('yuanhao','female',28,202), -> ('liwenzhou','male',18,200), -> ('jingliyang','female',18,204) -> ; Query OK, 6 rows affected (0.08 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.00 sec) mysql> select * from employee; +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec)
- 交叉连接：不使用任何匹配条件，生成笛卡尔积
mysql> select * from employee,department; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 1 | egon | male | 18 | 200 | 201 | 人力资源 | | 1 | egon | male | 18 | 200 | 202 | 销售 | | 1 | egon | male | 18 | 200 | 203 | 运营 | | 2 | alex | female | 48 | 201 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 2 | alex | female | 48 | 201 | 202 | 销售 | | 2 | alex | female | 48 | 201 | 203 | 运营 | | 3 | wupeiqi | male | 38 | 201 | 200 | 技术 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 销售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 运营 | | 4 | yuanhao | female | 28 | 202 | 200 | 技术 | | 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 4 | yuanhao | female | 28 | 202 | 203 | 运营 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 | | 5 | liwenzhou | male | 18 | 200 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 203 | 运营 | | 6 | jingliyang | female | 18 | 204 | 200 | 技术 | | 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 | | 6 | jingliyang | female | 18 | 204 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | 203 | 运营 | +----+------------+--------+------+--------+------+--------------+ 24 rows in set (0.00 sec)
- 内连接：只连接匹配的行
内连接语法 select 字段列表 from 表1 inner join 表2 on 表1.字段=表2.字段
找出两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果 mysql> select employee.id,employee.name,employee.age,employee.sex,
department.name from employee inner join department
on employee.dep_id=department.id; +----+-----------+------+--------+--------------+ | id | name | age | sex | name | +----+-----------+------+--------+--------------+ | 1 | egon | 18 | male | 技术 | | 2 | alex | 48 | female | 人力资源 | | 3 | wupeiqi | 38 | male | 人力资源 | | 4 | yuanhao | 28 | female | 销售 | | 5 | liwenzhou | 18 | male | 技术 | +----+-----------+------+--------+--------------+ 5 rows in set (0.00 sec) 等同于 mysql> select employee.id,employee.name,employee.age,employee.sex,
department.name from employee,department where employee.dep_id=department.id; +----+-----------+------+--------+--------------+ | id | name | age | sex | name | +----+-----------+------+--------+--------------+ | 1 | egon | 18 | male | 技术 | | 2 | alex | 48 | female | 人力资源 | | 3 | wupeiqi | 38 | male | 人力资源 | | 4 | yuanhao | 28 | female | 销售 | | 5 | liwenzhou | 18 | male | 技术 | +----+-----------+------+--------+--------------+ 5 rows in set (0.00 sec)
- 左连接：优先显示左表的全部记录
左连接语法 select 字段列表 from 表1 left join 表2 on 表1.字段=表2.字段
以左表为准，找出所有员工的信息，包括没有部门的员工 在内连接的基础上增加左表有右表没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from
employee left join department on employee.dep_id=department.id; +----+------------+--------------+ | id | name | depart_name | +----+------------+--------------+ | 1 | egon | 技术 | | 5 | liwenzhou | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 6 | jingliyang | NULL | +----+------------+--------------+ 6 rows in set (0.00 sec)
- 右连接
右连接语法 select 字段列表 from 表1 right join 表2 on 表1.字段=表2.字段
以右表为准，找出所有部门的信息，包括没有员工的部门 在内连接的基础上增加右表有左表没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from
employee right join department on employee.dep_id=department.id; +------+-----------+--------------+ | id | name | depart_name | +------+-----------+--------------+ | 1 | egon | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 5 | liwenzhou | 技术 | | NULL | NULL | 运营 | +------+-----------+--------------+ 6 rows in set (0.00 sec)
- 全外连接：显示左右两个表全部记录
全外连接：在内连接的基础上增加左表有右表没有的和右表有左表没有的结果 在pymysql中不支持全外连接 full join 可以使用下列方式间接实现全外连接 mysql> select * from employee left join department on employee.dep_id = department.id -> union -> select * from employee right join department on employee.dep_id = department.id -> ; 结果： +------+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+------------+--------+------+--------+------+--------------+ 7 rows in set (0.00 sec) union与union all的区别：union会去掉相同的记录
- 符合条件的连接查询
#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出公司所有部门中年龄大于25岁的员工 ysql> select employee.name,employee.age from employee,department -> where employee.dep_id = department.id -> and age > 25; +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | | yuanhao | 28 | +---------+------+ 3 rows in set (0.00 sec) #示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示 mysql> select employee.id,employee.name,employee.age,department.name from employee,department -> where employee.dep_id = department.id -> and age > 25 -> order by age asc; +----+---------+------+--------------+ | id | name | age | name | +----+---------+------+--------------+ | 4 | yuanhao | 28 | 销售 | | 3 | wupeiqi | 38 | 人力资源 | | 2 | alex | 48 | 人力资源 | +----+---------+------+--------------+ 3 rows in set (0.00 sec)
- 关键字的优先级
select distinct 字段1，字段2，...... from 左表 inner/left/right join 右表 on 连接条件 where 约束条件 group by 分组字段 having 过滤条件 order by 排序字段 limit 限制条件 ps：Vn代表虚拟表 1、先找到两张表，生成笛卡尔积，得到V1 2、按照on后的条件得到两张的共同部分，得到V2 3、如果是left join，则在V2的基础上保留左表的记录，得到V3 4、where 5、group by 6、having 7、select（distinct） 8、order by 9、limit
- 子查询
带in关键字的子查询 #查询employee表，但dep_id必须在department表中出现过 mysql> select * from employee -> where dep_id in -> (select id from department); +----+-----------+--------+------+--------+ | id | name | sex | age | dep_id | +----+-----------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | +----+-----------+--------+------+--------+ 5 rows in set (0.00 sec)
带比较运算符的子查询 比较运算符：=、!=、>、>=、<、<=、<> 查询平均年龄在25岁以上的部门名 mysql> select id,name from department -> where id in -> (select dep_id from employee group by dep_id having avg(age) > 25); +------+--------------+ | id | name | +------+--------------+ | 201 | 人力资源 | | 202 | 销售 | +------+--------------+ 2 rows in set (0.00 sec) 查看技术部员工姓名 mysql> select name from employee -> where dep_id in -> (select id from department where name='技术'); +-----------+ | name | +-----------+ | egon | | liwenzhou | +-----------+ 2 rows in set (0.00 sec) 查看不足1人的部门名 mysql> select name from department -> where id not in -> (select dep_id from employee group by dep_id having count(id) >=1); +--------+ | name | +--------+ | 运营 | +--------+ 1 row in set (0.00 sec)
带exists关键字的子查询 EXISTS关字键字表示存在； 在使用EXISTS关键字时，内层查询语句不返回查询的记录， 而是返回一个真假值：True或False； 当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询 department表中存在dept_id=200，Ture mysql> select * from employee -> where exists -> (select id from department where id=200); +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec) department表中存在dept_id=205，False mysql> select * from employee -> where exists -> (select id from department where id=205); Empty set (0.00 sec)
浙公网安备 33010602011771号