数据库查询之单表查询
目录
单表查询的语法以及关键字执行的优先级
单表查询语句
select distinct 字段, 字段2... from 表名
where 条件
group by field
having 筛选
order by field
limit 限制条数
关键字执行的优先级
from # 找到表
where # 利用where后的指定约束条件, 去文件/表中取出一条记录
group by # 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
select # 执行
distinct # 去重
having # 将分组的结果进行having过滤
order by # 将结果按条件排序
limit # 限制结果的显示条数
简单查询
1. 简单查询
select id, name, age from t1; # 根据字段查询
select * from t1; # 查询所有字段
2. 避免重复 (distinct)
select distinct post from t1;
3. 通过四则运算查询
select name, salary*5 from t1; # 显示薪水的5倍
select name, salary*5 as s from t1; # 以别名的方式显示薪水 (字段 as 别名)
select name, salary*5 s from t1; # 以别名的方式显示薪水
4. 定义显示格式
CONCAT() 函数用于连接字符串
select concat('姓名: ', name, ' 年薪: ', salary) as 临时字段名 from t1;
CONCAT_WS() 第一个参数为分隔符
select concat_ws(':', name, salary) as 临时字段名 from t1;
结合CASE语句
select
(
case # 判断开始的标志
when name = 'zhansan' then # if 语句
name # 输出符合条件的值
when name = 'lisi' then # elif 语句
concat(name, '_cool')
else # else 语句
concat(name, 'cool')
end # 判断结束的标志
) as new_name from t1;
注意: 当使用 concat 或 concat_ws 连接字符串是最好设置别名, 否则就会拿语句作为字段名
设置别名是的字段名: message
不设置别名的字段名: concat_ws(':', name, salary)
字段显示: (姓名: zhansan 年薪: 87603.96) 或 (zhansan : 36501.65)
where (约束)
where字句中可以使用:
1. 比较运算符：> < >= <= <> !=
2. between ... and ... # 值在80到100之间
3. in(80,90,100) # 值是80或90或100
4. like 'e%'
% 表示任意多字符
_ 表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
1. 单条件查询
select name from t1 where id=5;
2. 多条件查询
select name, salary from t1 where post='teacher' and salary>10000;
3. 关键字 between... and...
select name, salary from t1 where salary between 9000 and 15000;
select name, salary from t1 where salary not between 9000 and 15000;
4. 关键字 is null (判断某个字段是否为 NULL不能用等号, 需要用 is)
select name, post_comment from t1 where post_comment IS NULL;
select name, post_comment from t1 where post_comment IS NOT NULL;
# 注意post_comment='', ''是空字符串, 不是null
5. 关键字 in 集合查询
select name, salary from t1 where salary IN (3000, 4000, 5000, 9000);
select name, salary from t1 where salary NOT IN (3000, 4000, 5000, 9000);
6. 关键字 like 模糊查询
1) 通配符 '%'
select * from t1 where name like 'zhang%';
2) 通配符 '_'
select * from t1 where name like '张_';
group by (分组)
1. 单独使用 GROUP BY 关键字进行分组
select post from t1 group by post;
注意: 按照post字段分组, select查询字段只能是post, 想获取组内其他信息, 要借助函数.
2. GROUP BY 关键字和 GROUP_CONCAT() 函数一起使用.
select post, group_concat(name) from t1 group by post; # 按岗位分组, 并查看组内名字.
select post, group_concat(name) as name from t1 group by post;
3. GROUP BY 和聚合函数一起使用
select post, count(id) as count from t1 group by post; # 按岗位分组, 并查看人数
注意:
如果用 unique 的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
聚合函数
# 聚合函数聚合的是组的内容, 若没有分组, 则默认一组.
1. 统计计数: COUNT()
select count(*) from t1;
select count(*) from t1 where depart_id=1;
2. 最大值: MAX()
select max(salary) from t1;
3. 最小值: min()
select min(salary) from t1;
4. 平均值: AVG()
select avg(salary) from t1;
5. 求和: SUM()
select sum(salary) from t1;
select sum(salary) from t1 where depart_id=2;
having (过滤)
# 执行优先级从高到低: where > group by > having
1. where 发生在分组 group by之前, 因而 where中可以有任意字段, 但绝不能使用聚合函数.
2. Having 发生在 group by之后, 因而 Having中可使用分组字段, 无法直接取其他字段, 可使用聚合函数.
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post, GROUP_CONCAT(name) as name, count(id) as total from t1
GROUP BY post HAVING COUNT(id) > 5;
2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post, AVG(salary) as avg from t1 group by post HAVING AVG(salary)>10000;
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post, avg(salary) as avg from t5
GROUP BY post HAVING AVG(salary)>10000 and AVG(salary)<20000;
order by (查询排序)
1. 按单列排序 (降序: desc 升序: asc)
select * from t1 order by salary; # 默认升序
select * from t1 order by salary desc; # 降序
2. 按多列排序: 按照字段顺序先后排序, 先按年纪排序, 如果年纪相同, 则按照薪资排序.
select * from t1 order by age, salary desc;
limit (限制)
1. 限制查询的记录数: limit 值1(查询起始位置, 默认为0), 值2(查询条数)
1) 按照薪资查询前五条数据
select * from t1 order by salary desc limit 5; # 默认初始位置为0
select * from t1 order by salary desc limit 0, 5;
2) 从第5条开始，即先查询出第6条，然后包含这一条在内往后查5条
select * from t1 order by salary desc limit 5, 5;
正则表达式查询
SELECT * FROM t1 WHERE name REGEXP '^li';
SELECT * FROM t1 WHERE name REGEXP 'si$';
SELECT * FROM t1 WHERE name REGEXP 'm{2}';
小结：对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';
测试数据
company.employee
员工id id int
姓名 name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
# 创建表
create table t1(
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
);
# 查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| 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(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('zhangsan','male',78,'20150302','teacher',1000000.31,401,1),
('lisi','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);
