day47-约束条件续，多表查询
create table emp( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #插入记录 #三个部门：教学，销售，运营 insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部 ('tom','male',78,'20150302','teacher',1000000.31,401,1), ('kevin','male',81,'20130305','teacher',8300,401,1), ('tony','male',73,'20140701','teacher',3500,401,1), ('owen','male',28,'20121101','teacher',2100,401,1), ('jack','female',18,'20110211','teacher',9000,401,1), ('jenny','male',18,'19000301','teacher',30000,401,1), ('sank','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); # 当表字段特别多 展示的时候错乱 可以使用\G分行展示 select * from emp\G; # 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成GBK
# 书写顺序 select id,name from emp where id > 3; # 执行顺序 from where select """ 虽然执行顺序和书写顺序不一致 你在写sql语句的时候可能不知道怎么写 你就按照书写顺序的方式写sql select * 先用*号占位 之后去补全后面的sql语句 最后将*号替换后你想要的具体字段 明天会一直使用 这里先理解 """
# 作用:是对整体数据的一个筛选操作 # 1.查询id大于等于3小于等于6的数据 select id,name,age from emp where id>=3 and id<=6; select id,name from emp where id between 3 and 6; 两者等价
mysql> select id,name,age from emp where id>=3 and id<=6;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 3 | kevin | 81 |
| 4 | tony | 73 |
| 5 | owen | 28 |
| 6 | jack | 18 |
+----+-------+-----+
4 rows in set (0.00 sec)
mysql> select id,name,age from emp where id between 3 and 6;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 3 | kevin | 81 |
| 4 | tony | 73 |
| 5 | owen | 28 |
| 6 | jack | 18 |
+----+-------+-----+
4 rows in set (0.00 sec)
mysql>
# 2.查询薪资是20000或者18000或者17000的数据 select * from emp where salary=20000 or salary=18000 or salary=17000; select * from emp where salary in (20000,18000,17000);
mysql> select * from emp where salary=20000 or salary=17000 or salary= 18000;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.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 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
3 rows in set (0.00 sec)
mysql> select * from emp where salary in (17000,18000,20000);
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.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 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
3 rows in set (0.00 sec)
mysql>
# 3.查询员工姓名中包含字母o的员工的姓名和薪资 """ 模糊查询 like % 匹配任意多个字符 _ 匹配任意单个字符 """ select name,salary from emp where name like '%o%';
mysql> select * from emp where name like '%o%';
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)
mysql>
# 4.查询员工姓名是由四个字符组成的 姓名和薪资 char_length() _ select name,salary from emp where name like '____'; select name,salary from emp where char_length(name) = 4;
mysql> select name,salary from emp where name like '____';
+------+----------+
| name | salary |
+------+----------+
| tony | 3500.00 |
| owen | 2100.00 |
| jack | 9000.00 |
| sank | 10000.00 |
+------+----------+
4 rows in set (0.00 sec)
mysql> select * from emp where char_length(name)=4;
+----+------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
+----+------+--------+-----+------------+---------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)
mysql>
# 5.查询id小于3或者id大于6的数据 select * from emp where id not between 3 and 6;
mysql> select * from emp where id not between 4 and 6;
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | sank | 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 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
15 rows in set (0.00 sec)
mysql>
# 6.查询薪资不在20000,18000,17000范围的数据 select * from emp where salary not in (20000,18000,17000);
mysql> select * from emp where salary not in (17000,18000,20000);
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | sank | 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 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
15 rows in set (0.00 sec)
mysql>
# 7.查询岗位描述为空的员工姓名和岗位名 针对null不用等号 用is select name,post from emp where post_comment = NULL; select name,post from emp where post_comment is NULL;
mysql> select name,post from emp where post_comment =NULL;
Empty set (0.00 sec)
mysql> select name,post from emp where post_comment is NULL;
+-----------+-----------------------------+
| name | post |
+-----------+-----------------------------+
| jason | 张江第一帅形象代言 |
| tom | teacher |
| kevin | teacher |
| tony | teacher |
| owen | teacher |
| jack | teacher |
| jenny | teacher |
| sank | teacher |
| 哈哈 | sale |
| 呵呵 | sale |
| 西西 | sale |
| 乐乐 | sale |
| 拉拉 | sale |
| 僧龙 | operation |
| 程咬金 | operation |
| 程咬银 | operation |
| 程咬铜 | operation |
| 程咬铁 | operation |
+-----------+-----------------------------+
18 rows in set (0.00 sec)
mysql>
# 分组实际应用场景 分组应用场景非常的多 男女比例 部门平均薪资 部门秃头率 国家之间数据统计 # 1 按照部门分组 select * from emp group by post; mysql> select * from emp 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 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 | +----+--------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ 4 rows in set (0.00 sec) mysql> """ 分组之后 最小可操作单位应该是组 还不再是组内的单个数据 上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据) 如果设置了严格模式 那么上述命令会直接报错 """ set global sql_mode = 'strict_trans_tables,only_full_group_by'; mysql> set global sql_mode ='strict_trans_tables,only_full_group_by'; Query OK, 0 rows affected (0.00 sec) 设置严格模式之后 分组 默认只能拿到分组的依据 select post from emp group by post; 按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数) mysql> select name from emp group by post; ERROR 1055 (42000): 'day47.emp.name' isn't in GROUP BY mysql> """ 什么时候需要分组啊？？？ 关键字 每个 平均 最高 最低 聚合函数 max min sum count avg """ # 1.获取每个部门的最高薪资 select post,max(salary) from emp group by post; mysql> select post,max(salary) from emp group by post; +-----------------------------+-------------+ | post | max(salary) | +-----------------------------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 张江第一帅形象代言 | 7300.33 | +-----------------------------+-------------+ 4 rows in set (0.00 sec) mysql> select post as '部门',max(salary) as '最高薪资' from emp group by post; mysql> select post as '部门',max(salary) as '最高薪资' from emp group by post;; +-----------------------------+--------------+ | 部门 | 最高薪资 | +-----------------------------+--------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 张江第一帅形象代言 | 7300.33 | +-----------------------------+--------------+ 4 rows in set (0.00 sec) mysql> select post '部门',max(salary) '最高薪资' from emp group by post; # as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱 # 2.获取每个部门的最低薪资 select post,min(salary) from emp group by post; mysql> select post,min(salary) from emp 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> # 3.获取每个部门的平均薪资 select post,avg(salary) from emp group by post; mysql> select post,avg(salary) from emp group by post; +-----------------------------+---------------+ | post | avg(salary) | +-----------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 张江第一帅形象代言 | 7300.330000 | +-----------------------------+---------------+ 4 rows in set (0.00 sec) mysql> # 4.获取每个部门的工资总和 select post,sum(salary) from emp group by post; # 5.获取每个部门的人数 select post,count(id) from emp group by post; # 常用 符合逻辑 mysql> select post,count(id) from emp group by post; +-----------------------------+-----------+ | post | count(id) | +-----------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 张江第一帅形象代言 | 1 | +-----------------------------+-----------+ 4 rows in set (0.00 sec) mysql> select post,count(salary) from emp group by post; select post,count(age) from emp group by post; select post,count(post_comment) from emp group by post; null不行 # 6.查询分组之后的部门名称和每个部门下所有的员工姓名 # group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作 select post,group_concat(name) from emp group by post; mysql> select post,group_concat(name) from emp group by post; +-----------------------------+------------------------------------------------+ | post | group_concat(name) | +-----------------------------+------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙 | | sale | 拉拉,乐乐,西西,呵呵,哈哈 | | teacher | sank,jenny,jack,owen,tony,kevin,tom | | 张江第一帅形象代言 | jason | +-----------------------------+------------------------------------------------+ 4 rows in set (0.00 sec) select post,group_concat(name,'_DSB') from emp group by post; mysql> select post,group_concat(name,'_dsb') from emp group by post; +-----------------------------+--------------------------------------------------------------------+ | post | group_concat(name,'_dsb') | +-----------------------------+--------------------------------------------------------------------+ | operation | 程咬铁_dsb,程咬铜_dsb,程咬银_dsb,程咬金_dsb,僧龙_dsb | | sale | 拉拉_dsb,乐乐_dsb,西西_dsb,呵呵_dsb,哈哈_dsb | | teacher | sank_dsb,jenny_dsb,jack_dsb,owen_dsb,tony_dsb,kevin_dsb,tom_dsb | | 张江第一帅形象代言 | jason_dsb | +-----------------------------+--------------------------------------------------------------------+ 4 rows in set (0.00 sec) select post,group_concat(name,':',salary) from emp group by post; # concat不分组的时候用 select concat('NAME:',name),concat('SAL:',salary) from emp; mysql> select concat('名字：',name),concat('薪资：',salary) from emp; +--------------------------+----------------------------+ | concat('名字：',name) | concat('薪资：',salary) | +--------------------------+----------------------------+ | 名字：jason | 薪资：7300.33 | | 名字：tom | 薪资：1000000.31 | | 名字：kevin | 薪资：8300.00 | | 名字：tony | 薪资：3500.00 | | 名字：owen | 薪资：2100.00 | | 名字：jack | 薪资：9000.00 | | 名字：jenny | 薪资：30000.00 | | 名字：sank | 薪资：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) # 补充 as语法不单单可以给字段起别名 还可以给表临时起别名 select emp.id,emp.name from emp; select emp.id,emp.name from emp as t1; 报错 select t1.id,t1.name from emp as t1; # 查询每个人的年薪 12薪 select name,salary*12 from emp; mysql> select name,salary*12 as '年薪'from emp; +-----------+-------------+ | name | 年薪 | +-----------+-------------+ | jason | 87603.96 | | tom | 12000003.72 | | kevin | 99600.00 | | tony | 42000.00 | | owen | 25200.00 | | jack | 108000.00 | | jenny | 360000.00 | | sank | 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和group by同时出现的时候group by必须在where的后面 where先对整体数据进行过滤之后再分组操作 where筛选条件不能使用聚合函数 select id,name,age from emp where max(salary) > 3000; select max(salary) from emp; # 不分组 默认整体就是一组 # 统计各部门年龄在30岁以上的员工平均薪资 1 先求所有年龄大于30岁的员工 select * from emp where age>30; 2 再对结果进行分组 select * from emp where age>30 group by post; select post,avg(salary) from emp where age>30 group by post;
""" having的语法根where是一致的 只不过having是在分组之后进行的过滤操作 即having是可以直接使用聚合函数的 """ # 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门 select post,avg(salary) from emp where age>30 group by post having avg(salary) > 10000 ; mysql> select post, avg(salary) from emp where age>30 group by post having avg(salary)>10000; +---------+---------------+ | post | avg(salary) | +---------+---------------+ | teacher | 255450.077500 | +---------+---------------+ 1 row in set (0.00 sec) mysql>
""" 一定要注意 必须是完全一样的数据才可以去重！！！ 一定不要将逐渐忽视了 有逐渐存在的情况下 是不可能去重的 [ {'id':1,'name':'jason','age':18}, {'id':2,'name':'jason','age':18}, {'id':3,'name':'egon','age':18} ] ORM 对象关系映射 让不懂SQL语句的人也能够非常牛逼的操作数据库 表 类 一条条的数据 对象 字段对应的值 对象的属性 你再写类 就意味着在创建表 用类生成对象 就意味着再创建数据 对象点属性 就是在获取数据字段对应的值 目的就是减轻python程序员的压力 只需要会python面向对象的知识点就可以操作MySQL """ select distinct id,age from emp; select distinct age from emp; mysql> select distinct id,age from emp; +----+-----+ | id | age | +----+-----+ | 1 | 18 | | 2 | 78 | | 3 | 81 | | 4 | 73 | | 5 | 28 | | 6 | 18 | | 7 | 18 | | 8 | 48 | | 9 | 48 | | 10 | 38 | | 11 | 18 | | 12 | 18 | | 13 | 28 | | 14 | 28 | | 15 | 18 | | 16 | 18 | | 17 | 18 | | 18 | 18 | +----+-----+ 18 rows in set (0.00 sec) mysql> select distinct age from emp; +-----+ | age | +-----+ | 18 | | 78 | | 81 | | 73 | | 28 | | 48 | | 38 | +-----+ 7 rows in set (0.00 sec) mysql>
select * from emp order by salary; select * from emp order by salary asc; select * from emp order by salary desc; mysql> select * from emp order by salary; +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 | | 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) mysql> select * from emp order by salary asc; +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 | | 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) mysql> select *from emp order by salary desc; +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 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 | | 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 | | 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) mysql> """ order by默认是升序 asc 该asc可以省略不写 也可以修改为降序 desc """ select * from emp order by age desc,salary asc; mysql> select *from emp order by age desc,salary; +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 | | 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | +----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) mysql> # 先按照age降序排 如果碰到age相同 则再按照salary升序排 # 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序 select post,avg(salary) from emp where age>10 group by post having avg(salary) > 1000 order by avg(salary) desc ; mysql> select post,avg(salary) from emp -> where age >10 -> group by post -> having avg(salary) >1000 -> order by avg(salary) desc; +-----------------------------+---------------+ | post | avg(salary) | +-----------------------------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | | 张江第一帅形象代言 | 7300.330000 | | sale | 2600.294000 | +-----------------------------+---------------+ 4 rows in set (0.00 sec) mysql>
select * from emp; """针对数据过多的情况 我们通常都是做分页处理""" select * from emp limit 3; # 只展示三条数据 mysql> select * from emp limit 3; +----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 | | 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ 3 rows in set (0.00 sec) select * from emp limit 0,5; select * from emp limit 5,5; mysql> select * from emp limit 0,5; +----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 | | 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ 5 rows in set (0.00 sec) mysql> select * from emp limit 5,5; +----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | sank | 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 | +----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec) mysql> 第一个参数是起始位置 第二个参数是展示条数
select * from emp where name regexp '^j.*(n|y)$'; 查找emp表中名字符合 j开头，n或者y结尾的 信息 mysql> select * from emp where name regexp '^j.*(n|y)$'; +----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+ | 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 | | 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | +----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+ 2 rows in set (0.00 sec) mysql>
前期表准备
#建表 create table dep( id int, name varchar(20) ); create table emp( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into dep values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into emp(name,sex,age,dep_id) values ('jason','male',18,200), ('egon','female',48,201), ('kevin','male',18,201), ('nick','male',28,202), ('owen','male',18,203), ('jerry','female',18,204);
select * from dep,emp; # 结果 笛卡尔积 """ 了解即可 不知道也没关系 """ select * from emp,dep where emp.dep_id = dep.id; """ MySQL也知道 你在后面查询数据过程中 肯定会经常用到拼表操作 所以特地给你开设了对应的方法 inner join 内连接 left join 左连接 right join 右连接 union 全连接 """ # inner join 内连接 select * from emp inner join dep on emp.dep_id = dep.id; # 只拼接两张表中公有的数据部分 # left join 左连接 select * from emp left join dep on emp.dep_id = dep.id; # 左表所有的数据都展示出来 没有对应的项就用NULL # right join 右连接 select * from emp right join dep on emp.dep_id = dep.id; # 右表所有的数据都展示出来 没有对应的项就用NULL # union 全连接 左右两表所有的数据都展示出来 select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id;
""" 子查询就是我们平时解决问题的思路 分步骤解决问题 第一步 第二步 ... 将一个查询语句的结果当做另外一个查询语句的条件去用 """ # 查询部门是技术或者人力资源的员工信息 1 先获取部门的id号 2 再去员工表里面筛选出对应的员工 select id from dep where name='技术' or name = '人力资源'; select name from emp where dep_id in (200,201); select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');
总结
表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一个张虚拟表根其他表关联
"""
多表查询就两种方式
先拼接表再查询
子查询 一步一步来
"""
