一.查询语句的基本操作
11.查询语句的基本操作 2 - select 3 - from 4 - where:约束条件 5 - group by:分组 6 - having：过滤 7 - distinct：去重 8 - order by：排序 9 - limit：限制查询记录的数量 10 - 聚合函数: count（计数） 11 max（最大值） 12 min（最小值） 13 avg（平均值） 14 sum(求和)
二.单表查询
1、前期表与数据准备
1# 创建一张部门表 2create table emp( 3 id int not null unique auto_increment, 4 name varchar(20) not null, 5 sex enum('male','female') not null default 'male', #大部分是男的 6 age int(3) unsigned not null default 28, 7 hire_date date not null, 8 post varchar(50), 9 post_comment varchar(100), 10 salary double(15,2), 11 office int, # 一个部门一个屋子 12 depart_id int 13); 14 15 16 17# 插入记录 18# 三个部门：教学，销售，运营 19insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values 20('tank','male',17,'20170301','张江第一帅形象代言部门',7300.33,401,1), # 以下是教学部 21('egon','male',78,'20150302','teacher',1000000.31,401,1), 22('kevin','male',81,'20130305','teacher',8300,401,1), 23('jason','male',73,'20140701','teacher',3500,401,1), 24('owen','male',28,'20121101','teacher',2100,401,1), 25('jerry','female',18,'20110211','teacher',9000,401,1), 26('大饼','male',18,'19000301','teacher',30000,401,1), 27('sean','male',48,'20101111','teacher',10000,401,1), 28 29('歪歪','female',48,'20150311','sale',3000.13,402,2),# 以下是销售部门 30('丫丫','female',38,'20101101','sale',2000.35,402,2), 31('丁丁','female',18,'20110312','sale',1000.37,402,2), 32('星星','female',18,'20160513','sale',3000.29,402,2), 33('格格','female',28,'20170127','sale',4000.33,402,2), 34 35('张野','male',28,'20160311','operation',10000.13,403,3), # 以下是运营部门 36('程咬金','male',18,'19970312','operation',20000,403,3), 37('程咬银','female',18,'20130311','operation',19000,403,3), 38('程咬铜','male',18,'20150411','operation',18000,403,3), 39('程咬铁','female',18,'20140512','operation',17000,403,3); 40 41# PS：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk 42 43 44- select * from emp; # 若数据比较多，比较凌乱，可以在表后面+ \G 45- select * from emp\G 46 47 48- select * from emp; # 若数据比较多，比较凌乱，可以在表后面+ \G 49- select * from emp\G
重点：
1 写SQL语句必须遵循两点: 2 - 书写顺序: 3# 获取id为 4、5的两条记录 4select * from emp where id > 3 and id < 6; 5 6 - select 7 - from 8 - where 9 10 - 执行顺序: 11 12 比如: 图书管理员，得先找到是哪一个图书馆(哪张表), 13 再找这本书在图书馆的哪个位置(哪一条记录), 最后查找这个本书中某一页(哪些字段值); 14 15 select * from emp where id > 3 and id < 6; 16 - from ---> 找到图书馆 17 - where ---> 找到书的位置 18 - select ---> 找到书本中的某一页 19 20 注意: 必须记住SQL语句的 书写顺序 与 执行顺序(*******);
1.where(约束条件)
1# PS: 根据执行顺序来书写 SQL语句，一步一步来写; 2 3# 1.查询id大于等于3小于等于6的数据(and:与) 4 5select * from emp where id >= 3 and id <= 6; 6 7mysql> SELECT * FROM EMP WHERE ID > 3 AND ID <= 6; 8+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 9| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | 10+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 11| 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | 12| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | 13| 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | 14+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 153 rows in set (0.00 sec) 16 17 18#可以使用between()and() :两者之间 19 20 21mysql> select * from emp where id between 3 and 6; 22+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 23| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | 24+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 25| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | 26| 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | 27| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | 28| 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | 29+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 304 rows in set (0.00 sec) 31 32# 2.查询薪资是20000或者18000或者17000的数据 33# or: 或者 34select * from emp where salary=20000 or salary=18000 or salary=17000; 35# in: 在什么里 36select * from emp where salary in (20000, 18000, 17000); 37 38mysql> select * from emp where salary = 20000 or salary = 18000 or salary = 17000; 39+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 40| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | 41+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 42| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | 43| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | 44| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | 45+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 463 rows in set (0.00 sec) 47 48# 也可以使用in(3,4,5,6) 49 select * from emp where id in(3,4,5,6); 50+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 51| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | 52+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 53| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | 54| 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | 55| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | 56| 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | 57+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 584 rows in set (0.00 sec) 59 60 61# 3.查询员工姓名中包含o字母 的 员工姓名和薪资 62# like: 模糊匹配 63# %: 匹配0个或多个任意字符(若前后都有%(%o%),查所有带有索引字符的记录,若头有%而尾没有(%o),则查所有尾带有索引字符的记录,若尾有%而头没有(o%),则查所有头带有索引字符的记录,若前后都没有%(o),则只会查询只有索引字符的字符) 64# _: 匹配一个任意字符 65select name, salary from emp where name like "%o%"; 66+-------+------------+ 67| name | salary | 68+-------+------------+ 69| egon | 1000000.31 | 70| jason | 3500.00 | 71| owen | 2100.00 | 72+-------+------------+ 733 rows in set (0.00 sec) 74 75 76mysql> select name, salary from emp where name like "o"; 77+------+--------+ 78| name | salary | 79+------+--------+ 80| o | NULL | 81+------+--------+ 821 row in set (0.00 sec) 83 84 85# 4.查找名字个数为3个的员工 名字 与 薪资 86select name, salary from emp where name like "___"; 87+-----------+----------+ 88| name | salary | 89+-----------+----------+ 90| 程咬金 | 20000.00 | 91| 程咬银 | 19000.00 | 92| 程咬铜 | 18000.00 | 93| 程咬铁 | 17000.00 | 94+-----------+----------+ 954 rows in set (0.00 sec) 96 97# 或者 # char_length(name): 计算名字字符的长度 98select name, salary from emp where char_length(name) = 4; 99 100mysql> select name, salary from emp where char_length(name) = 3; 101+-----------+----------+ 102| name | salary | 103+-----------+----------+ 104| 程咬金 | 20000.00 | 105| 程咬银 | 19000.00 | 106| 程咬铜 | 18000.00 | 107| 程咬铁 | 17000.00 | 108+-----------+----------+ 1094 rows in set (0.00 sec) 110 111 112# 5.查询id小于3或者大于6的数据 113# not in: 不再什么什么中 114select * from emp where id not in (3, 4, 5, 6); 115select * from emp where id not between 3 and 6; 116 117 118# 6.查询薪资不在20000，18000，17000范围的数据 119select * from emp where salary not in (20000, 18000, 17000); 120 121 122 # 7.查询岗位描述为空的 员工名 与 岗位名 post_comment 123# 用等于号无法判定空字段 124select name, post from emp where post_comment = null; 125 126select * from emp where post_comment = null; 127Empty set (0.00 sec) 128 129# 注意: 针对null的值 需要使用 is 130select name, post from emp where post_comment is null; 131 132select name, post_comment from emp where post_comment is null; 133+-----------+--------------+ 134| name | post_comment | 135+-----------+--------------+ 136| tank | NULL | 137| egon | NULL | 138| kevin | NULL | 139| jason | NULL | 140| owen | NULL | 141| jerry | NULL | 142| 大饼 | NULL | 143| sean | NULL | 144| 歪歪 | NULL | 145| 丫丫 | NULL | 146| 丁丁 | NULL | 147| 星星 | NULL | 148| 格格 | NULL | 149| 张野 | NULL | 150| 程咬金 | NULL | 151| 程咬银 | NULL | 152| 程咬铜 | NULL | 153| 程咬铁 | NULL | 154| o | NULL | 155+-----------+--------------+ 15619 rows in set (0.00 sec)
2.group by(分组)
1 - 书写顺序: 2 - select 3 - from 4 - where 5 - group by 6 7 - 执行顺序: 8 - from 9 - where 10 - group by 11 - select 12# 什么叫分组 13# 比如: 一张员工表中有性别字段，可以根据性别分组，一组是男性，一组是女性，或者是根据部门分组，有教学部、销售部等... 14 15 # 1.根据部门分组 16 # 非严格模式下可以获取 分组条件post 以外的字段数据 17select post, salary from emp group by post; 18 19# 设置严格模式: 20show variables like "%mode%"; 21# 全局设置: 永久有效 22set global sql_mode="strict_trans_tables,only_full_group_by"; 23 24 25""" 26设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据， 27不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，并且会报错,因为不分组就是对单个元素信息的随意获取 28""" 29mysql> select post, salary from emp group by post; 30ERROR 1046 (3D000): No database selected 31 32# 可以同聚合函数，间接获取其他字段数据 33 聚合函数: 34 count: 计数 35 max: 最大值 36 min: 最小值 37 avg: 平均值 38 sum: 求和 39 group_concat(name): 可以将分组后的 所有名字获取并进行拼接 40 41# 指定以:拼接 42select post, group_concat(name) from emp group by post; 43select post, group_concat(name, ':') from emp group by post; 44 45 46 2.获取每个 部门 的最高工资 47 select post,max(salary) from emp group by post; 48mysql> select post,max(salary) from emp group by post; 49+-----------------------------------+-------------+ 50| post | max(salary) | 51+-----------------------------------+-------------+ 52| NULL | NULL | 53| operation | 20000.00 | 54| sale | 4000.33 | 55| teacher | 1000000.31 | 56| 张江第一帅形象代言部门 | 7300.33 | 57+-----------------------------------+-------------+ 585 rows in set (0.00 sec) 59 60补充: as 别名: 可以给字段 加一个 别名 61select post as '部门', max(salary) as '薪资' 62from emp group by post; 63 64# 也可以简写,但是不推荐 65select post '部门', max(salary) '薪资' 66from emp group by post; 67 68mysql> select post as '部门', max(salary) as'薪资' 69 -> from emp group by post; 70+-----------------------------------+------------+ 71| 部门 | 薪资 | 72+-----------------------------------+------------+ 73| NULL | NULL | 74| operation | 20000.00 | 75| sale | 4000.33 | 76| teacher | 1000000.31 | 77| 张江第一帅形象代言部门 | 7300.33 | 78+-----------------------------------+------------+ 795 rows in set (0.00 sec) 80 81 823.每个部门的最低工资 83select post, min(salary) from emp group by post; 84 854.每个部门的平均工资 86select post, avg(salary) from emp group by post; 87 885.每个部门的工资总和 89select post, sum(salary) from emp group by post; 90 916.每个部门的员工个数 92# count(): 括号中可以填任意非空值 93select post, count(salary) from emp group by post; 94select post, count(post_comment) from emp group by post; 95 96 97查询岗位名以及各岗位内包含的员工个数 98select post, count(id) from emp group by post; 99查询公司内男员工和女员工的个数 100select sex, count(*) from emp group by sex; 101 102查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 103select sex, avg(salary) from emp group by sex; 104 105.统计各部门年龄在30岁以上的员工平均工资: 106# 步骤: 先找到表，再找年龄30岁以上，再根据部门分组，最后求平均薪资; 107select post, avg(salary) from emp where age > 30 group by post;
总结: 聚合函数，必须跟在group by 后面(执行顺序);
1# 聚合函数: 若没有group by 分组，默认将查出来的数据当做一个分组, 也能使用; 2select max(salary) from emp; 3 4 5 6 # group_concat(name): 可以将分组后的 所有名字获取并进行拼接 7 # 默认以, 拼接 8select post, group_concat(name) from emp group by post; 9 # 指定以:拼接 10select post, group_concat(name, ':') from emp group by post; 11# 12select post, group_concat('Name: ', name) from emp group by post; 13 14 15select post, group_coucat(name) from emp group by post; 16 17mysql> select post, group_concat(name) from emp group by post; 18+-----------------------------------+------------------------------------------------+ 19| post | group_concat(name) | 20+-----------------------------------+------------------------------------------------+ 21| NULL | o | 22| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | 23| sale | 格格,星星,丁丁,丫丫,歪歪 | 24| teacher | sean,大饼,jerry,owen,jason,kevin,egon | 25| 张江第一帅形象代言部门 | tank | 26+-----------------------------------+------------------------------------------------+ 275 rows in set (0.00 sec) 28 29# 给每个部门的员工名字前 + NB_ 30select post, group_concat('NB_', name) from emp group by post; 31# 拼接部门员工名字+薪资 32select post, group_concat(name, ":", salary) from emp group by post;
补充:concat
1# 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法并用 2select concat('Name: ', name) as '名字', concat('Sal: ', salary) as '薪资' from emp; 3 4 5mysql> select concat('name: ', name) as '名字',concat('sal: ',salary) as '薪资' from emp; 6+-----------------+-----------------+ 7| 名字 | 薪资 | 8+-----------------+-----------------+ 9| name: tank | sal: 7300.33 | 10| name: egon | sal: 1000000.31 | 11| name: kevin | sal: 8300.00 | 12| name: jason | sal: 3500.00 | 13| name: owen | sal: 2100.00 | 14| name: jerry | sal: 9000.00 | 15| name: 大饼 | sal: 30000.00 | 16| name: sean | sal: 10000.00 | 17| name: 歪歪 | sal: 3000.13 | 18| name: 丫丫 | sal: 2000.35 | 19| name: 丁丁 | sal: 1000.37 | 20| name: 星星 | sal: 3000.29 | 21| name: 格格 | sal: 4000.33 | 22| name: 张野 | sal: 10000.13 | 23| name: 程咬金 | sal: 20000.00 | 24| name: 程咬银 | sal: 19000.00 | 25| name: 程咬铜 | sal: 18000.00 | 26| name: 程咬铁 | sal: 17000.00 | 27| name: o | NULL | 28+-----------------+-----------------+ 2919 rows in set (0.00 sec)
3.having(过滤)
11.having与where语法一样，只不过having必需要在group by后使用; 22.where 不能使用聚合函数，但having可以; 3 4 - 书写顺序: 5 - select 6 - from 7 - where 8 - group by 9 - having 10 11 - 执行顺序: 12 - from 13 - where 14 - group by 15 - having 16 - select 17 18 1、统计各 部门 年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门; 19select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000; 20mysql> select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000; 21+---------+---------------+ 22| post | avg(salary) | 23+---------+---------------+ 24| teacher | 255450.077500 | 25+---------+---------------+ 261 row in set (0.00 sec) 27
4.distinct(去重)
1# distinct: 去重 2 3- 书写顺序: 4 - select 5 - distinct 6 - from 7 - where 8 - group by 9 - having 10 11执行顺序: 12 - from 13 - where 14 - group by 15 - having 16 - select 17 - distinct 18 19# 注意: 查询的字段值必须是重复的才有效，只要有一个字段值是不重复的就没有效果。 20 21# 若所查字段内有不重复的字段记录就不会去重 22select distinct id, post from emp; 23 24mysql> select distinct id, post from emp; 25+----+-----------------------------------+ 26| id | post | 27+----+-----------------------------------+ 28| 1 | 张江第一帅形象代言部门 | 29| 2 | teacher | 30| 3 | teacher | 31| 4 | teacher | 32| 5 | teacher | 33| 6 | teacher | 34| 7 | teacher | 35| 8 | teacher | 36| 9 | sale | 37| 10 | sale | 38| 11 | sale | 39| 12 | sale | 40| 13 | sale | 41| 14 | operation | 42| 15 | operation | 43| 16 | operation | 44| 17 | operation | 45| 18 | operation | 46| 19 | NULL | 47+----+-----------------------------------+ 4819 rows in set (0.00 sec) 49 50 51 52 53select distinct post from emp; 54 55mysql> select distinct post from emp; 56+-----------------------------------+ 57| post | 58+-----------------------------------+ 59| 张江第一帅形象代言部门 | 60| teacher | 61| sale | 62| operation | 63| NULL | 64+-----------------------------------+ 655 rows in set (0.00 sec) 66
5.order by(排序)
1- 书写顺序: 2 - select 3 - from 4 - where 5 - group by 6 - having 7 - order by 8 9执行顺序: 10 - from 11 - where 12 - group by 13 - having 14 - select 15 - order by # 通过select 查出来的数据再进行排序 16 17# order by 默认升序 18# asc升序 19# desc降序 20 21# 1、根据薪资进行升序 22select name, salary from emp order by salary; # 默认升序 23 24mysql> select name, salary from emp order by salary; 25+-----------+------------+ 26| name | salary | 27+-----------+------------+ 28| o | NULL | 29| 丁丁 | 1000.37 | 30| 丫丫 | 2000.35 | 31| owen | 2100.00 | 32| 歪歪 | 3000.13 | 33| 星星 | 3000.29 | 34| jason | 3500.00 | 35| 格格 | 4000.33 | 36| tank | 7300.33 | 37| kevin | 8300.00 | 38| jerry | 9000.00 | 39| sean | 10000.00 | 40| 张野 | 10000.13 | 41| 程咬铁 | 17000.00 | 42| 程咬铜 | 18000.00 | 43| 程咬银 | 19000.00 | 44| 程咬金 | 20000.00 | 45| 大饼 | 30000.00 | 46| egon | 1000000.31 | 47+-----------+------------+ 4819 rows in set (0.00 sec) 49 50 51 522.select * from emp order by salary asc; # 指定升序 53 543.select name, salary from emp order by salary desc; # 指定降序 55 56 57mysql> select name, salary from emp order by salary desc; 58+-----------+------------+ 59| name | salary | 60+-----------+------------+ 61| egon | 1000000.31 | 62| 大饼 | 30000.00 | 63| 程咬金 | 20000.00 | 64| 程咬银 | 19000.00 | 65| 程咬铜 | 18000.00 | 66| 程咬铁 | 17000.00 | 67| 张野 | 10000.13 | 68| sean | 10000.00 | 69| jerry | 9000.00 | 70| kevin | 8300.00 | 71| tank | 7300.33 | 72| 格格 | 4000.33 | 73| jason | 3500.00 | 74| 星星 | 3000.29 | 75| 歪歪 | 3000.13 | 76| owen | 2100.00 | 77| 丫丫 | 2000.35 | 78| 丁丁 | 1000.37 | 79| o | NULL | 80+-----------+------------+ 8119 rows in set (0.00 sec) 82 83# 先按照age升序，再按照salary降序 84select age, salary from emp order by age asc, salary desc; 85 86 87# 统计 各部门(分组) 年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行升序序 88select post, avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary); 89
9.limit(限制查询记录的数量)
1 书写顺序: 2 - select 3 - from 4 - order by 5 - limit 6 7 执行顺序: 8 - from 9 - select 10 - order by 11 - limit 12 13 14 15# 1、从第一条开始，获取4条记录; 16select * from emp limit 4; 17 18mysql> select * from emp limit 4; 19+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+ 20| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | 21+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+ 22| 1 | tank | male | 17 | 2017-03-01 | 张江第一帅形象代言部门 | NULL | 7300.33 | 401 | 1 | 23| 2 | egon | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | 24| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | 25| 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | 26+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+ 274 rows in set (0.00 sec) 28 29 30 31# 2、limit可以有两个参数, 参数1:是限制的开始位置, 参数2:是从开始位置展示的条数; 32select * from emp limit 0, 4; 33select * from emp limit 4, 4; 34 35 36 37 38mysql> select * from emp limit 4, 4; 39+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 40| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | 41+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 42| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | 43| 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | 44| 7 | 大饼 | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | 45| 8 | sean | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | 46+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 474 rows in set (0.00 sec) 48 49# 3、查询工资最高的人的详细信息 50select * from emp order by salary limit 1; 51 52 53mysql> select * from emp order by salary limit 1; 54+----+------+------+-----+------------+------+--------------+--------+--------+-----------+ 55| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | 56+----+------+------+-----+------------+------+--------------+--------+--------+-----------+ 57| 19 | o | male | 28 | 0000-00-00 | NULL | NULL | NULL | NULL | NULL | 58+----+------+------+-----+------------+------+--------------+--------+--------+-----------+ 591 row in set (0.00 sec)
10.正则
1# 在编程中，凡是看到reg开头的，基本上都是跟正则有关 2 # *: 代表 0 或 多个 3select * from emp where name regexp '^程.*(金|银|铜|铁)$'; 4 5mysql> select * from emp where name regexp '^程.*(金|银|铜|铁)$'; 6+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 7| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | 8+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 9| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | 10| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | 11| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | 12| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | 13+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 144 rows in set (0.00 sec)
三.多表查询
多表查询
-关联查询
-子查询
创建表与插入数据准备
1#建表 2create table dep2( 3id int, 4name varchar(20) 5); 6 7create table emp2( 8id int primary key auto_increment, 9name varchar(20), 10sex enum('male','female') not null default 'male', 11age int, 12dep_id int 13); 14 15#插入数据 16insert into dep2 values 17(200,'技术'), 18(201,'人力资源'), 19(202,'销售'), 20(203,'运营'); 21 22insert into emp2(name,sex,age,dep_id) values 23('tank','male',17,200), 24('egon','female',48,201), 25('kevin','male',38,201), 26('jason','female',28,202), 27('owen','male',18,200), 28('sean','female',18,204); 29 30 31 32mysql> select * from dep2; 33+------+--------------+ 34| id | name | 35+------+--------------+ 36| 200 | 技术 | 37| 201 | 人力资源 | 38| 202 | 销售 | 39| 203 | 运营 | 40+------+--------------+ 414 rows in set (0.00 sec) 42 43 44 45mysql> select * from emp2; 46+----+-------+--------+------+--------+ 47| id | name | sex | age | dep_id | 48+----+-------+--------+------+--------+ 49| 1 | tank | male | 17 | 200 | 50| 2 | egon | female | 48 | 201 | 51| 3 | kevin | male | 38 | 201 | 52| 4 | jason | female | 28 | 202 | 53| 5 | owen | male | 18 | 200 | 54| 6 | sean | female | 18 | 204 | 55+----+-------+--------+------+--------+ 56 57# 指到了如何根据表关系对字段进行拆分，目的是为了更好的管理，表数据都存放在硬盘中，存不是目的，目的是为了取，所以我们将数据从硬盘读到内存中，接下来我们因应该将他们拼成一张表来查询更加合理; 58 59# 注意: 将拆分的表，再拼接到一起进行查询, 可以通过一张表查另一张表的数据;
1.关联查询
1# 左表的一条记录与右表的一条记录都对应一遍称之为 --> "笛卡尔积" PS: 百度科普 2# 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据 3 4 5 6举例:通过之前准备的数据进行多表操作 7 81、查询 员工 以及所在 部门 的信息; 9# 通过where 约束条件 10select * from emp2, dep2 where emp2.dep_id = dep2.id; 11 122、查询 部门 为 技术部 的 员工 及 部门信息 13 14select * from emp2, dep2 where emp2.dep_id = dep2.id and dep2.name = '技术'; 15 16 17联表查询: 18 -内链接:只去两张表有对应关系的记录 19select * from emp2 inner join dep2 on emp2.dep_id = dep2.id; 20 21mysql> select * from emp2 inner join dep2 on emp2.dep_id = dep2.id; 22+----+-------+--------+------+--------+------+--------------+ 23| id | name | sex | age | dep_id | id | name | 24+----+-------+--------+------+--------+------+--------------+ 25| 1 | tank | male | 17 | 200 | 200 | 技术 | 26| 2 | egon | female | 48 | 201 | 201 | 人力资源 | 27| 3 | kevin | male | 38 | 201 | 201 | 人力资源 | 28| 4 | jason | female | 28 | 202 | 202 | 销售 | 29| 5 | owen | male | 18 | 200 | 200 | 技术 | 30+----+-------+--------+------+--------+------+--------------+ 315 rows in set (0.05 sec) 32 332、left join 34# 2、左连接: 在内连接的基础上保留左表没有对应关系的记录(以null补全) 35select * from emp2 left join dep2 on emp2.dep_id = dep2.id; 36 37mysql> select * from emp2 left join dep2 on emp2.dep_id = dep2.id; 38+----+-------+--------+------+--------+------+--------------+ 39| id | name | sex | age | dep_id | id | name | 40+----+-------+--------+------+--------+------+--------------+ 41| 1 | tank | male | 17 | 200 | 200 | 技术 | 42| 5 | owen | male | 18 | 200 | 200 | 技术 | 43| 2 | egon | female | 48 | 201 | 201 | 人力资源 | 44| 3 | kevin | male | 38 | 201 | 201 | 人力资源 | 45| 4 | jason | female | 28 | 202 | 202 | 销售 | 46| 6 | sean | female | 18 | 204 | NULL | NULL | 47+----+-------+--------+------+--------+------+--------------+ 48 49 50# 3、右连接: 在内连接的基础上保留右表没有对应关系的记录 51select * from emp2 right join dep2 on emp2.dep_id = dep2.id; 52 53 54mysql> select * from emp2 right join dep2 on emp2.dep_id = dep2.id; 55+------+-------+--------+------+--------+------+--------------+ 56| id | name | sex | age | dep_id | id | name | 57+------+-------+--------+------+--------+------+--------------+ 58| 1 | tank | male | 17 | 200 | 200 | 技术 | 59| 2 | egon | female | 48 | 201 | 201 | 人力资源 | 60| 3 | kevin | male | 38 | 201 | 201 | 人力资源 | 61| 4 | jason | female | 28 | 202 | 202 | 销售 | 62| 5 | owen | male | 18 | 200 | 200 | 技术 | 63| NULL | NULL | NULL | NULL | NULL | 203 | 运营 | 64+------+-------+--------+------+--------+------+--------------+ 656 rows in set (0.00 sec) 66 67 68- 全连接: union 69# 4、全连接: 在内连接的基础上 保留左、右表没有对应关系的记录 70select * from emp2 left join dep2 on emp2.dep_id = dep2.id 71union 72select * from emp2 right join dep2 on emp2.dep_id = dep2.id;
2.子查询
1# 子查询就是将一个查询语句的结果用括号括起来，当做另一个查询语句的条件去用 2 3# 查询部门是技术或者人力资源的员工信息 4''' 5先获取技术部和人力资源的id号，再去员工表里根据前面的id筛选出符合要求的员工信息; 6''' 7select * from emp2 where dep_id in (select id from dep2 where name='技术' or name='人力资源'); 8 9 # 2.每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询 10 # 查第一张emp表 11# 第四步 12select t1.id, t1.name, t1.hire_date, t2.* 13from 14# 第二步 15# 拼接了 t1（emp） 与 t2（各部门最新入职的员工数据虚拟表）表的数据 16 17emp as t1 18inner join 19 20# 第一步 21# 子查询获取emp表中的 部门名称与最新入职的时间字段值 生成一张虚拟表 22(select post, max(hire_date) as max_date from emp group by post) as t2 23on 24# 若 t1.post 的值与 t2.post 条件成立，则拼接对应的记录; 25t1.post = t2.post 26# 第三步 27where t1.hire_date = t2.max_date;