MySQL单表查询
MySQL单表查询
1.select语句 最简单的select select * from 表; select 字段,... from 表; 重命名字段 select 字段 as 新名字,... from 表; select 字段 新名字,... from 表; 去重 select distinct 字段 from 表; select distinct age,sex from employee; 使用函数 concat concat_ws 四则运算的 select emp_name,salary*12 from employee; 乘法 select emp_name,salary*12 as annual_salary from employee; 使用判断逻辑 case when语句 相当于 if条件判断句 where 筛选所有符合条件的行 比较运算符 > < >= <= <> != 范围 between 10000 and 20000 要1w-2w之间的 in (10000,20000) 只要10000或者20000的 模糊匹配 like % 通配符 表示任意长度的任意内容 _ 通配符 一个字符长度的任意内容 regexp '^a' 'g$' 逻辑运算 not\and\or 查看岗位描述不为NULL的员工信息 is select * from employee where post_comment is not null; 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 select emp_name, age, salary from employee wherepost = 'teacher' and salary not in(10000,9000,30000) 查看岗位是teacher且名字是jin开头的员工姓名、年薪 select emp_name,salary*12 from employee where post = 'teacher' and emp_name like 'jin%'; 分组 group by 根据谁分组,可以求这个组的总人数,最大值,最小值,平均值,求和 但是这个求出来的值只是和分组字段对应 并不和其他任何字段对应,这个时候查出来的所有其他字段都不生效. 聚合函数 count 求个数 max 求最大值 min 求最小值 sum 求和 avg 求平均 SELECT post,emp_name FROM employee GROUP BY post; SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post; having 过滤语句 在having条件中可以使用聚合函数,在where中不行 适合去筛选符合条件的某一组数据,而不是某一行数据 先分组再过滤 : 求平均薪资大于xx的部门,求人数大于xx的性别,求大于xx人的年龄段 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 group by post having count(id) < 2; 排序 order by 默认是升序 asc 降序 desc order by age ,salary desc 优先根据age从小到大排,在age相同的情况下,再根据薪资从大到小排 limit m,n 从m+1项开始,取n项 如果不写m,m默认为0 limit n offset m
单表查询的语法及关键字执行的优先级
from 1.找到表:from
where 2.拿着where指定的约束条件，去文件/表中取出一条条记录
group by 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
select 4.执行select（去重）
distinct
having 5.将分组的结果进行having过滤
order by 6.将结果按条件排序：order by
limit 7.限制结果的显示条数
简单查询
创建表 create table t1( id int not null unique auto_increment, 姓名 varchar(20) not null, 性别 enum('男','女') not null default '男', #大部分是男的 年龄 int(3) unsigned not null default 28, 入职日期 date not null, 部门 varchar(50), 职位描述 varchar(100), 薪资 double(15,2), 部门办公室 int, #一个部门一个屋子 部门编号 int ); 查询表 mysql> desc t1; +-----------------+-------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | 姓名 | varchar(20) | NO | | NULL | | | 性别 | enum('男','女') | NO | | 男 | | | 年龄 | int(3) unsigned | NO | | 28 | | | 入职日期 | date | NO | | NULL | | | 部门 | varchar(50) | YES | | NULL | | | 职位描述 | varchar(100) | YES | | NULL | | | 薪资 | double(15,2) | YES | | NULL | | | 部门办公室 | int(11) | YES | | NULL | | | 部门编号 | int(11) | YES | | NULL | | +-----------------+-------------------+------+-----+---------+----------------+ 10 rows in set (0.01 sec) 插入数据 三个部门 insert into t1(姓名,性别,年龄,入职日期,部门,薪资,部门办公室,部门编号) values ('李三胖','男',18,'20170301','办公室主任',7300.33,401,1), #以下是教学部 ('李狗蛋','男',78,'20150302','老师',1000000.31,401,1), ('老皮','男',81,'20130305','老师',8300,401,1), ('猴子','男',73,'20140701','老师',3500,401,1), ('catdexin','男',28,'20121101','老师',2100,401,1), ('那月','女',18,'20110211','老师',9000,401,1), ('muki','男',18,'19000301','老师',30000,401,1), ('小虫','男',48,'20101111','老师',10000,401,1), ('歪歪','女',48,'20150311','招生办',3000.13,402,2),#以下是销售部门 ('丫丫','女',38,'20101101','招生办',2000.35,402,2), ('丁丁','女',18,'20110312','招生办',1000.37,402,2), ('星星','女',18,'20160513','招生办',3000.29,402,2), ('格格','女',28,'20170127','招生办',4000.33,402,2), ('张鹏','男',28,'20160311','运营',10000.13,403,3), #以下是运营部门 ('shafufu','男',18,'19970312','运营',20000,403,3), ('憨批','女',18,'20130311','运营',19000,403,3), ('瓷锤','男',18,'20150411','运营',18000,403,3), ('王大头','女',18,'20140512','运营',17000,403,3) ; #ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
简单查询 select id,姓名,性别,年龄,入职日期,部门,职位描述,薪资,部门办公室,部门编号 from t1; select * from t1; select 姓名,薪资 from t1; 去重查询 distinct select distinct 部门 from t1; 通过四则运算查询 select 姓名,薪资*12 from t1; select 姓名,薪资*12 as 年薪 from t1; select 姓名,薪资*12 年薪 from t1; 自定义显示格式 concat() 函数用于连接字符串 select concat('员工姓名: ',姓名,' 员工年薪: ',薪资*12) 年薪 from t1; concat_ws() 降低一个参数作为分隔符 select concat_ws('|',姓名,薪资*12) 年薪 from t1; ---组合case语句 select ( case when 姓名 = 'catdexin' then concat(姓名,' 一级棒') when 姓名 = '那月' then concat(姓名,' 二级棒') else concat(姓名,' 是个傻逼') end ) as 新的名字 from t1;
小练习
1 查出所有员工的名字，薪资,格式为 <名字:egon> <薪资:3000> 2 查出所有的岗位（去掉重复） 3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
where约束
where 约束 单条件查询 select 姓名 from t1 where 部门='招生办'; 多条件查询 select 姓名,薪资 from t1 where 部门='老师' and 薪资 > 10000; 关键字between and select 姓名,薪资 from t1 where 薪资 between 10000 and 20000; select 姓名,薪资 from t1 where 薪资 not between 10000 and 20000; 关键字 is null (判断某个字段是否为null不能用等号，要用is) select 姓名,职位描述 from t1 where 职位描述 is null; select 姓名,职位描述 from t1 where 职位描述 is not null; select 姓名,职位描述 from t1 where 职位描述=''; ----注意''是空字符串，不是null ps：执行 update t1 set 职位描述='' where id=2; 再用上条查看，就有结果了 关键字in集合查询 select 姓名,薪资 from t1 where 薪资=3000 or 薪资=3500 or 薪资=4000 or 薪资=9000; select 姓名,薪资 from t1 where 薪资 in (3000,3500,4000,9000); select 姓名,薪资 from t1 where 薪资 not in (3000,3500,4000,9000); 关键字like模糊查询 通配符'%' select * from t1 where 姓名 like 'ca%'; 通配符'_' select * from t1 where 姓名 like '那_';
小练习
1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
tese 1. 查看岗位是老师的员工姓名、年龄 select 姓名,年龄,部门 from t1 where 部门='老师'; 2. 查看岗位是老师且年龄大于30岁的员工姓名、年龄 select 姓名,年龄,部门 from t1 where 部门='老师' and 年龄>30; 3. 查看岗位是老师且薪资在9000-1000范围内的员工姓名、年龄、薪资 select 姓名,年龄,薪资,部门 from t1 where 部门='老师' and 薪资 between 9000 and 10000; 4. 查看岗位描述不为NULL的员工信息 5. 查看岗位是老师且薪资是10000或9000或30000的员工姓名、年龄、薪资 select 姓名,年龄,薪资,部门 from t1 where 部门='老师' and 薪资 in(10000,9000,30000); 6. 查看岗位是老师且薪资不是10000或9000或30000的员工姓名、年龄、薪资 select 姓名,年龄,薪资,部门 from t1 where 部门='老师' and 薪资 not in(10000,9000,30000); 7. 查看岗位是老师且名字是c开头的员工姓名、年薪 select 姓名,年龄,薪资,部门 from t1 where 部门='老师' and 姓名 like 'c%';
group by
单独使用group by关键字分组 select 部门 from t1 group by 部门; 注意 我们按照部门字段分组，那么select查询的字段只能是部门，想要获取组内其他相关信息 需要借助函数 group by关键字和group_concat()函数一起使用 select 部门,group_concat(姓名) from t1 group by 部门; #按照部门分组，并查看组内成员名 select 部门,group_concat(姓名) as 姓名临时 from t1 group by 部门; group by与聚合函数一起使用 select 部门,count(id) as 人数 from t1 group by 部门; #按照部门分组，并查看组内成员人数 强调 如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义 多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
聚合函数
select count(*) from t1; select count(*) from t1 where 部门编号=1; select max(薪资) from t1; select min(薪资) from t1; select avg(薪资) from t1; select sum(薪资) from t1; select sum(薪资) from t1 where 部门编号=3;
小练习
1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
1. 查询岗位名以及岗位包含的所有员工名字 select 部门,group_concat(姓名) from t1 group by 部门; 2. 查询岗位名以及各岗位内包含的员工个数 select 部门,count(id) from t1 group by 部门; 3. 查询公司内男员工和女员工的个数 select 性别,count(id) from t1 group by 性别; 4. 查询岗位名以及各岗位的平均薪资 select 部门,sum(薪资)/count(id) from t1 group by 部门; select 部门,avg(薪资) from t1 group by 部门; 5. 查询岗位名以及各岗位的最高薪资 select 部门,max(薪资) from t1 group by 部门; 6. 查询岗位名以及各岗位的最低薪资 select 部门,min(薪资) from t1 group by 部门; 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 select 性别,avg(薪资) from t1 group by 性别;
having过滤
having过滤 having与where不一样的地方在于 #执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
mysql> select * from t1 where 薪资>100000; +----+-----------+--------+--------+--------------+--------+--------------+------------+-----------------+--------------+ | id | 姓名 | 性别 | 年龄 | 入职日期 | 部门 | 职位描述 | 薪资 | 部门办公室 | 部门编号 | +----+-----------+--------+--------+--------------+--------+--------------+------------+-----------------+--------------+ | 2 | 李狗蛋 | 男 | 78 | 2015-03-02 | 老师 | NULL | 1000000.31 | 401 | 1 | +----+-----------+--------+--------+--------------+--------+--------------+------------+-----------------+--------------+ 1 row in set (0.00 sec) mysql> select 部门,group_concat(姓名) from t1 group by 部门 having 薪资>10000;; ERROR 1054 (42S22): Unknown column '薪资' in 'having clause' mysql> select 部门,group_concat(姓名) from t1 group by 部门 having avg(薪资)>10000; +--------+-----------------------------------------------------+ | 部门 | group_concat(姓名) | +--------+-----------------------------------------------------+ | 老师 | muki,李狗蛋,老皮,猴子,catdexin,那月,小虫 | | 运营 | 张鹏,shafufu,憨批,瓷锤,王大头 | +--------+-----------------------------------------------------+ 2 rows in set (0.00 sec)
小练习
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select 部门,group_concat(姓名),count(id) from t1 group by 部门 having count(id)<2;
3. 查询各岗位平均薪资大于10000的岗位名、平均工资
4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
ORDER BY 查询排序
按单列排序 select * from t1 order by 薪资; select * from t1 order by 薪资 asc; select * from t1 order by 薪资 desc; 按多列排序:先按照年龄排序，如果年纪相同，则按照薪资排序 select * from t1 order by 年龄, 薪资 desc;
小练习
1. 查询所有员工信息，先按照年龄升序排序，如果age相同则按照入职时间降序排序 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
mysql> select * from t1 order by 年龄 asc,hire_date desc; #题目2 mysql> select 部门,avg(薪资) from t1 group by 部门 having avg(薪资) > 10000 order by avg(薪资) asc; +-----------+---------------+ | 部门 | avg(薪资) | +-----------+---------------+ | 运营 | 16800.026000 | | 老师 | 151842.901429 | +-----------+---------------+ #题目3 mysql> select 部门,avg(薪资) from t1 group by 部门 having avg(薪资) > 10000 order by avg(薪资) desc; +-----------+---------------+ | 部门 | avg(薪资) | +-----------+---------------+ | 老师 | 151842.901429 | | 运营 | 16800.026000 | +-----------+---------------+
LIMIT 限制查询的记录数
select * from t1 order by salary desc limit 3; #默认初始位置为0 select * from t1 order by salary desc limit 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条 select * from t1 order by salary desc limit 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
小练习
1.分页显示，每页5条
使用正则表达式查询
select * from t1 where emp_name regexp '^ale'; select * from t1 where emp_name regexp 'on$'; select * from t1 where emp_name regexp 'm{2}'; 小结：对字符串匹配的方式 where emp_name = 'egon'; where emp_name like 'yua%'; where emp_name regexp 'on$';
小练习
1.查看所有员工中名字是jin开头，n或者g结果的员工信息
