MySQL之查询语句的基本操作

一.查询语句的基本操作 1.查询语句的基本操作 - select - from - where:约束条件 - group by:分组 - having：过滤 - distinct：去重 - order by：排序 - limit：限制查询记录的数量 - 聚合函数: count（计数） max（最大值） min（最小值） avg（平均值） sum(求和) 二.单表查询 1、前期表与数据准备 # 创建一张部门表 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 (\'tank\',\'male\',17,\'20170301\',\'张江第一帅形象代言部门\',7300.33,401,1), # 以下是教学部 (\'egon\',\'male\',78,\'20150302\',\'teacher\',1000000.31,401,1), (\'kevin\',\'male\',81,\'20130305\',\'teacher\',8300,401,1), (\'jason\',\'male\',73,\'20140701\',\'teacher\',3500,401,1), (\'owen\',\'male\',28,\'20121101\',\'teacher\',2100,401,1), (\'jerry\',\'female\',18,\'20110211\',\'teacher\',9000,401,1), (\'大饼\',\'male\',18,\'19000301\',\'teacher\',30000,401,1), (\'sean\',\'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); # PS：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk - select * from emp; # 若数据比较多，比较凌乱，可以在表后面+ \G - select * from emp\G - select * from emp; # 若数据比较多，比较凌乱，可以在表后面+ \G - select * from emp\G

重点： 写SQL语句必须遵循两点: - 书写顺序: # 获取id为 4、5的两条记录 select * from emp where id > 3 and id < 6; - select - from - where - 执行顺序: 比如: 图书管理员，得先找到是哪一个图书馆(哪张表), 再找这本书在图书馆的哪个位置(哪一条记录), 最后查找这个本书中某一页(哪些字段值); select * from emp where id > 3 and id < 6; - from ---> 找到图书馆 - where ---> 找到书的位置 - select ---> 找到书本中的某一页 注意: 必须记住SQL语句的 书写顺序 与 执行顺序(*******); 1.where(约束条件) # PS: 根据执行顺序来书写 SQL语句，一步一步来写; # 1.查询id大于等于3小于等于6的数据(and:与) select * from emp where id >= 3 and id <= 6; mysql> SELECT * FROM EMP WHERE ID > 3 AND ID <= 6; +----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | 4 | jason | 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 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | +----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 3 rows in set (0.00 sec) #可以使用between()and() :两者之间 mysql> select * from emp where id between 3 and 6; +----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | 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 | | 4 | jason | 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 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | +----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 4 rows in set (0.00 sec) # 2.查询薪资是20000或者18000或者17000的数据 # or: 或者 select * from emp where salary=20000 or salary=18000 or salary=17000; # in: 在什么里 select * from emp where salary in (20000, 18000, 17000); mysql> select * from emp where salary = 20000 or salary = 18000 or salary = 17000; +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 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) # 也可以使用in(3,4,5,6) select * from emp where id in(3,4,5,6); +----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | 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 | | 4 | jason | 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 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | +----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 4 rows in set (0.00 sec) # 3.查询员工姓名中包含o字母 的 员工姓名和薪资 # like: 模糊匹配 # %: 匹配0个或多个任意字符(若前后都有%(%o%),查所有带有索引字符的记录,若头有%而尾没有(%o),则查所有尾带有索引字符的记录,若尾有%而头没有(o%),则查所有头带有索引字符的记录,若前后都没有%(o),则只会查询只有索引字符的字符) # _: 匹配一个任意字符 select name, salary from emp where name like "%o%"; +-------+------------+ | name | salary | +-------+------------+ | egon | 1000000.31 | | jason | 3500.00 | | owen | 2100.00 | +-------+------------+ 3 rows in set (0.00 sec) mysql> select name, salary from emp where name like "o"; +------+--------+ | name | salary | +------+--------+ | o | NULL | +------+--------+ 1 row in set (0.00 sec) # 4.查找名字个数为3个的员工 名字 与 薪资 select name, salary from emp where name like "___"; +-----------+----------+ | name | salary | +-----------+----------+ | 程咬金 | 20000.00 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | | 程咬铁 | 17000.00 | +-----------+----------+ 4 rows in set (0.00 sec) # 或者 # char_length(name): 计算名字字符的长度 select name, salary from emp where char_length(name) = 4; mysql> select name, salary from emp where char_length(name) = 3; +-----------+----------+ | name | salary | +-----------+----------+ | 程咬金 | 20000.00 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | | 程咬铁 | 17000.00 | +-----------+----------+ 4 rows in set (0.00 sec) # 5.查询id小于3或者大于6的数据 # not in: 不再什么什么中 select * from emp where id not in (3, 4, 5, 6); select * from emp where id not between 3 and 6; # 6.查询薪资不在20000，18000，17000范围的数据 select * from emp where salary not in (20000, 18000, 17000); # 7.查询岗位描述为空的 员工名 与 岗位名 post_comment # 用等于号无法判定空字段 select name, post from emp where post_comment = null; select * from emp where post_comment = null; Empty set (0.00 sec) # 注意: 针对null的值 需要使用 is select name, post from emp where post_comment is null; select name, post_comment from emp where post_comment is null; +-----------+--------------+ | name | post_comment | +-----------+--------------+ | tank | NULL | | egon | NULL | | kevin | NULL | | jason | NULL | | owen | NULL | | jerry | NULL | | 大饼 | NULL | | sean | NULL | | 歪歪 | NULL | | 丫丫 | NULL | | 丁丁 | NULL | | 星星 | NULL | | 格格 | NULL | | 张野 | NULL | | 程咬金 | NULL | | 程咬银 | NULL | | 程咬铜 | NULL | | 程咬铁 | NULL | | o | NULL | +-----------+--------------+ 19 rows in set (0.00 sec) 2.group by(分组) - 书写顺序: - select - from - where - group by - 执行顺序: - from - where - group by - select # 什么叫分组 # 比如: 一张员工表中有性别字段，可以根据性别分组，一组是男性，一组是女性，或者是根据部门分组，有教学部、销售部等... # 1.根据部门分组 # 非严格模式下可以获取 分组条件post 以外的字段数据 select post, salary from emp group by post; # 设置严格模式: show variables like "%mode%"; # 全局设置: 永久有效 set global sql_mode="strict_trans_tables,only_full_group_by"; """ 设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据， 不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，并且会报错,因为不分组就是对单个元素信息的随意获取 """ mysql> select post, salary from emp group by post; ERROR 1046 (3D000): No database selected # 可以同聚合函数，间接获取其他字段数据 聚合函数: count: 计数 max: 最大值 min: 最小值 avg: 平均值 sum: 求和 group_concat(name): 可以将分组后的 所有名字获取并进行拼接 # 指定以:拼接 select post, group_concat(name) from emp group by post; select post, group_concat(name, \':\') from emp group by post; 2.获取每个 部门 的最高工资 select post,max(salary) from emp group by post; mysql> select post,max(salary) from emp group by post; +-----------------------------------+-------------+ | post | max(salary) | +-----------------------------------+-------------+ | NULL | NULL | | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 张江第一帅形象代言部门 | 7300.33 | +-----------------------------------+-------------+ 5 rows in set (0.00 sec) 补充: as 别名: 可以给字段 加一个 别名 select post as \'部门\', max(salary) as \'薪资\' from emp group by post; # 也可以简写,但是不推荐 select post \'部门\', max(salary) \'薪资\' from emp group by post; mysql> select post as \'部门\', max(salary) as\'薪资\' -> from emp group by post; +-----------------------------------+------------+ | 部门 | 薪资 | +-----------------------------------+------------+ | NULL | NULL | | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 张江第一帅形象代言部门 | 7300.33 | +-----------------------------------+------------+ 5 rows in set (0.00 sec) 3.每个部门的最低工资 select post, min(salary) from emp group by post; 4.每个部门的平均工资 select post, avg(salary) from emp group by post; 5.每个部门的工资总和 select post, sum(salary) from emp group by post; 6.每个部门的员工个数 # count(): 括号中可以填任意非空值 select post, count(salary) from emp group by post; select post, count(post_comment) from emp group by post; 查询岗位名以及各岗位内包含的员工个数 select post, count(id) from emp group by post; 查询公司内男员工和女员工的个数 select sex, count(*) from emp group by sex; 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 select sex, avg(salary) from emp group by sex; .统计各部门年龄在30岁以上的员工平均工资: # 步骤: 先找到表，再找年龄30岁以上，再根据部门分组，最后求平均薪资; select post, avg(salary) from emp where age > 30 group by post;