mysql 单表查询
程序员文章站
2022-03-20 16:55:46
单表查询 [TOC] 准备数据 只是把查询结果按照自己想要的方式返回,不对数据做修改 简单查询 指定字段查询 select id ,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id from employee; ......
目录
单表查询
准备数据
只是把查询结果按照自己想要的方式返回,不对数据做修改
#准备表和记录 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 #建表和数据准备 create table employee( id int not null unique auto_increment, emp_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 ); #查看表结构 mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | field | type | null | key | default | extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | no | pri | null | auto_increment | | emp_name | varchar(20) | no | | null | | | sex | enum('male','female') | no | | male | | | age | int(3) unsigned | no | | 28 | | | hire_date | date | no | | null | | | post | varchar(50) | yes | | null | | | post_comment | varchar(100) | yes | | null | | | salary | double(15,2) | yes | | null | | | office | int(11) | yes | | null | | | depart_id | int(11) | yes | | null | | +--------------+-----------------------+------+-----+---------+----------------+ #插入数据 #三个部门:教学,销售,运营 insert into employee(emp_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) ; #ps:如果在windows系统中,插入中文字符,select的结果为空白,可以将所有字符编码统一设置成gbk
简单查询
指定字段查询
- select id ,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id from employee;
- select emp_name,salary from employee;
查看所有字段
- select * from employee;
避免重复distinct
- select distinct post from employee;
通过四则运算查询
- select emp_name,salary*12 from employee;
- select emp_name,salary*12 as annual_salary from employee;
- select emp_name,salary*12 annual_salary from employee;
定义显示格式 concat() 函数用于连接字符串
- select concat('姓名:',emp_name,'年薪:',salary*12) as annual_salary from employee;
concat_ws() 第一个参数为分隔符
- select concat_ws(':',emp_name,salary*12) as annual_salary from employee;
- 结合case语句
- select( case when emp_name = 'jingliyang' then emp_name when emp_name = 'alex' then concat(emp_name,'_bigsb') else concat(emp_name, '棒棒哒') end ) as new_name from employee;
比较运算符
大于 > 小于< 等于= 大于等于 >= 小于等于<= 不等于 != 不等于<>
范围筛选 between
between 80 and 100 值在80到100之间
在一个模糊的范围里 between 10000 and 20000 salary 是1w-2w之间的所有人的名字
select emp_name from employee where salary between 10000 and 20000;
多选 in
- 字段名 in (值1,值2,值3)
- select * from employee where salary in (20000,30000,3000,19000,18000,17000);
模糊查询 like
通配符可以是%或**_** %表示任意多字符 _表示一个字符
select * from employee where emp_name like '程%';
逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
逻辑运算 - 条件的拼 (与 and) (或 or) (非 not)
select * from employee where emp_name not in ('程咬金','程咬银','程咬铜');
- select * from employee where emp_name not like ('程%');
-
select emp_name,salary*12 from employee where post='operation' and emp_name like '程%';
- 查看岗位是operation且名字是程开头的员工姓名、年薪
- 查看岗位是operation且名字是程开头的员工姓名、年薪
select * from employee where salary = 20000 or salary = 30000;
select * from employee where emp_name = '丫丫' or emp_name = '丁丁' or emp_name = '星星' or emp_name = '格格'or salary = 20000;
身份运算 - 关于null is null /is not null
查看岗位描述为null的员工信息
select * from employee where post_comment is not null;
正则匹配 regexp
更加细致的匹配的时候使用 select * from 表 where 字段 regexp 正则表达式
select emp_name,salary*12 from employee where post='teacher' and emp_name regexp '^jin.*'
select * from employee where emp_name regexp '^j[a-z]{5}'
分组 group by
#单独使用group by关键字分组 mysql> select post from employee group by post; +--------------+ | post | +--------------+ | operation | | sale | | teacher | | 鸡汤导师 | +--------------+ 4 rows in set (0.02 sec) 注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数 #group by关键字和group_concat()函数一起使用 mysql> select post,group_concat(emp_name) from employee group by post;#按照岗位分组,并查看组内成员名 +--------------+---------------------------------------------------------+ | post | group_concat(emp_name) | +--------------+---------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | sale | 格格,星星,丁丁,丫丫,歪歪 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | | 鸡汤导师 | egon | +--------------+---------------------------------------------------------+ 4 rows in set (0.04 sec) mysql> select post,group_concat(emp_name) as emp_members from employee group by post; +--------------+---------------------------------------------------------+ | post | emp_members | +--------------+---------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | sale | 格格,星星,丁丁,丫丫,歪歪 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | | 鸡汤导师 | egon | +--------------+---------------------------------------------------------+ 4 rows in set (0.00 sec) #group by与聚合函数一起使用 select post,count(id) as count from employee group by post;#按照岗位分组,并查看每个组有多少人 +--------------+-------+ | post | count | +--------------+-------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 鸡汤导师 | 1 | +--------------+-------+ 4 rows in set (0.02 sec)
select * from employee group by post;
分组会把在group by后面的这个字段,也就是post字段中的每一个不同的项都保留下来
并且把值是这一项的的所有,行归为一组,但是只显示第一个分组匹配上值的所有数据,且一般默认倒序显示,数值的默认从小到大排序.
聚合 (把很多行的同一个字段进行一些统计,最终的到一个结果)
#强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组 示例: # count(字段/*) 统计这个字段有多少项 select count(id) from employee; +-----------+ | count(id) | +-----------+ | 18 |#结果 +-----------+ 1 row in set (0.00 sec) select count(*) from employee where depart_id=1; +----------+ | count(*) | +----------+ | 8 |#结果 +----------+ 1 row in set (0.00 sec) # max(字段) 统计这个字段对应的数值的最大值(薪资) select max(salary) from employee; +-------------+ | max(salary) | +-------------+ | 1000000.31 |#结果 +-------------+ 1 row in set (0.03 sec) # min(字段) 统计这个字段对应的数值的最小值(薪资) select min(salary) from employee; +-------------+ | min(salary) | +-------------+ | 1000.37 |#结果 +-------------+ 1 row in set (0.00 sec) # avg(字段) 统计这个字段对应的数值的平均值(薪资) select avg(salary) from employee; +--------------+ | avg(salary) | +--------------+ | 64844.568889 |#结果 +--------------+ 1 row in set (0.00 sec) # sum(字段) 统计这个字段对应的数值的和 mysql> select sum(salary) from employee; +-------------+ | sum(salary) | +-------------+ | 1167202.24 |#结果 +-------------+ 1 row in set (0.00 sec) select sum(salary) from employee where depart_id=3; +-------------+ | sum(salary) | +-------------+ | 84000.13 |#结果 +-------------+ 1 row in set (0.00 sec)
分组+聚合
一 concat()函数 1、功能:将多个字符串连接成一个字符串。 2、语法:concat(str1, str2,...) 返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null。 3、语法:concat(str1, seperator,str2,seperator,...) 返回结果为连接参数产生的字符串并且有分隔符,如果有任何一个参数为null,则返回值为null。 二 concat_ws()函数 1、功能:和concat()一样,将多个字符串连接成一个字符串,但是可以一次性指定分隔符(concat_ws就是concat with separator) 2、语法:concat_ws(separator, str1, str2, ...) 说明:第一个参数指定分隔符。需要注意的是分隔符不能为null,如果为null,则返回结果为null。 三 group_concat()函数 1、功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。 2、语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] ) 说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator是一个字符串值,缺省为一个逗号。
1. 查询岗位名以及岗位包含的所有员工名字 mysql> select post,group_concat(emp_name) from employee group by post; +--------------+---------------------------------------------------------+ | post | group_concat(emp_name) | +--------------+---------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | sale | 格格,星星,丁丁,丫丫,歪歪 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | | 鸡汤导师 | egon | +--------------+---------------------------------------------------------+ 2. 查询岗位名以及各岗位内包含的员工个数 mysql> select post,count(emp_name) from employee group by post ; +--------------+-----------------+ | post | count(emp_name) | +--------------+-----------------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 鸡汤导师 | 1 | +--------------+-----------------+ 3. 查询公司内男员工和女员工的个数 select sex, count(id) from employee group by sex; +--------+-----------+ | sex | count(id) | +--------+-----------+ | male | 10 | | female | 8 | +--------+-----------+ 4. 查询岗位名以及各岗位的平均薪资 select post,avg(salary) from employee group by post; +--------------+---------------+ | post | avg(salary) | +--------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 鸡汤导师 | 7300.330000 | +--------------+---------------+ 5. 查询岗位名以及各岗位的最高薪资 select post,max(salary) from employee group by post; +--------------+-------------+ | post | max(salary) | +--------------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 鸡汤导师 | 7300.33 | +--------------+-------------+ 6. 查询岗位名以及各岗位的最低薪资 select post,min(salary) from employee group by post; +--------------+-------------+ | post | min(salary) | +--------------+-------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | | 鸡汤导师 | 7300.33 | +--------------+-------------+ 7. 查询男员工与男员工的平均薪资,女员工与女员工的平均薪资 select sex,avg(salary) from employee group by sex; +--------+---------------+ | sex | avg(salary) | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+
having过滤
#having与where不一样的地方在于!!!!!! 1. 执行优先级从高到低:where > group by > having 2. where 发生在分组group by之前,因而where中可以有任意字段,但是绝对不能使用聚合函数。 3. having发生在分组group by之后,因而having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
having 条件 # 过滤 组
select count(id) as '人数',post from employee group by post having count(*) > 3; 找出部门人数大于3的部门
select post '均薪资>10000部门' from employee group by post having avg(salay)>10000; 平均薪资大于10000的部门
select * from employee having age>18 年龄大于18岁的
-
order by 查询排序
order by 某一个字段 asc; 默认是升序asc(ascending升序) 从小到大
select emp_name,age from employee order by age; 年龄按照从小到大排序
order by 某一个字段 desc; 指定降序排列desc(descending)从大到小
select emp_name,age from employee order by age desc; 年龄按照从大到小排序
select emp_name,hire_date from employee order by hire_date desc; 入职日期从早到晚排序
order by 第一个字段 asc,第二个字段 desc;指定先根据第一个字段升序排列,在第一个字段相同的情况下,再根据第二个字段排列
select emp_name,age,salary from employee order by age,salary desc;
-
limit 限制查询的记录数
取前n个 limit n == limit 0,n
select emp_name,salary from employee order by salary desc limit 3; 找出薪资最高的三个人,从多到少排序
分页 limit m,n 从m+1开始取n个
员工展示的网页,18个员工,每一页展示5个员工
-
limit n offset m == limit m,n 从m+1开始取n个
- select * from employee order by id desc limit 0,5;
- select * from employee order by id desc limit 5,5;
- select * from employee order by id desc limit 10,5;
- select * from employee order by id desc limit 15,5;
- select * from employee order by id desc limit 0,5;