SQL语句查询关键字
数据准备
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
gender 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,gender,age,hire_date,post,salary,office,depart_id) values
('Jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','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);
小技巧贴士
select 后的字段名可以先用*占位 最后再回来修改。
*在实际应用情况中很少使用，如果一个表数据量特别大的时候，*就代表查询所有，非常浪费数据库资源。
select 与from
select 就是指定一个查询的字段
from 就是指定一个查询的表
where筛选
当有一个表 我们需要从中查询一些特定条件的数据时，可以使用where 筛选来操作。
1.查询id大于等于3 小于等于6的数据
'支持数学运算符 与 逻辑运算符'
select * from emp where id >=3 and id <= 6;
'英文单词 between(两者之间)'
select * from emp where id between 3 and 6;
====================================================================================
2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
'成员运算符'
select * from emp where salary in (20000,18000,17000);
====================================================================================
3.查询id小于3大于6的数据
select * from emp where id < 3 or id > 6;
select * from emp where id not between 3 and 6;
复习：与或非 or and not
====================================================================================
4. 查询员工姓名中包含字母o的员工姓名与薪资
题目思考：
条件不够精确的查询 我们称之为'模糊查询'
模糊查询的'关键字 like'
模糊查询关键符号
%: 匹配任意个数的任意字符
_:匹配单个个数的任意字符
select * from where name like '%o%';
select name,salary from where name like '%o%';
====================================================================================
5.查询员工姓名是由四个字符组成的员工姓名与其薪资
select * from emp where name like'____';
select * from emp where char_length(name) = 4;
select * from emp where length(name) = 4;
====================================================================================
6.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select * from emp where post_comment=NULL;
不行 '注意：查询字段 只能用is'
select * from emp where post_comment is NULL;
贴士：MySQL中有很多内置方法 可以通过查看帮助手册学习
help 方法名
group by 分组
按照指定的条件把我们想要查询的数据进行分组，方便我们进行查看统计数据
# 为什么需要分组
分组的好处在于可以快速统计出某些数据
eg:最大薪资 平均年龄 最小年龄 总人数
但是我们在查找这些数据分组完成后又需要一些东西进行专门处理。
'聚合函数'：
专门用于分组之后的数据统计
max 统计最大值
min 统计最小值
sum 统计求和
count 统计计数
avg 统计平均值
====================================================================================
按照部门分组：
select * from emp group by post;
====================================================================================
版本问题
MySQL 5.6 默认不会报错
set global sql_mode='strict_trans_tables,only_full_group_by'
5.6以上的版本默认都会直接报错
原因是分组之后 select后面默认只能直接填写分组的字段，不能再写其他字段
原因是分组的目的就是按照分组的条件来管理诸多数据 最小单位应该是分组的依据而不是单个单个的数据
如果是MySQL5.6及以下版本 需要自己手动添加，在配置文件中增加
====================================================================================
1.获取每个部门的最高工资
问题解析：
先对部门进行分组,然后利用max函数查询薪资最高
select post,max(salary) emp group by post;
+-----------------------------+-------------+
| post | max(salary) |
+-----------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
也可以修改字段名 as
select post as '部门',max(salary) as '最高薪资' from emp group by post;
+-----------------------------+--------------+
| 部门 | 最高薪资 |
+-----------------------------+--------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+--------------+
====================================================================================
2.一次获取部门薪资相关统计
select post,max(salary) '最高薪',min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;
+-----------------------------+------------+-----------+---------------+------------+
| post | 最高薪 | 最低薪 | 平均薪资 | 月支出 |
+-----------------------------+------------+-----------+---------------+------------+
| operation | 20000.00 | 10000.13 | 16800.026000 | 84000.13 |
| sale | 4000.33 | 1000.37 | 2600.294000 | 13001.47 |
| teacher | 1000000.31 | 2100.00 | 151842.901429 | 1062900.31 |
| 浦东第一帅形象代言 | 7300.33 | 7300.33 | 7300.330000 | 7300.33 |
+-----------------------------+------------+-----------+---------------+------------+
====================================================================================
3.统计每个部门的人数
select post,count(id) from emp group by post;
+-----------------------------+-----------+
| post | count(id) |
+-----------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 浦东第一帅形象代言 | 1 |
+-----------------------------+-----------+
====================================================================================
4.统计每个部门的部门名称以及部门下的员工姓名
"分组后只能查询该分组的字段名不能填写别的字段名 需要借助 group_concat 方法"
select post,group_concat(name) from emp group by post;
可以在用 符号拼接 也可以+字符，前后都可以
select post,group_concat(name,'|',age) from emp group by post;
select post,group_concat('儿子',name) from emp group by post;
select post,group_concat('儿子-',name,'-菜鸡') from emp group by post;
having 过滤
用来对数据筛查，与where本质是一样的。
where 使用在分组前 having 用在分组后
1.统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
# 先查询各部门年龄在30岁以上的员工平均工资
select post,avg(salary) from emp where age > 30 group by post;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| sale | 2500.240000 |
| teacher | 255450.077500 |
+---------+---------------+
# 接着二次筛选薪资大于10000的数据
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
====================================================================================
'''
针对聚合函数，如果还需要在其他地方作为条件使用，可以起别名
'''
select post,avg(salary) as avg_salary from gjz
where age>30
group by post
having avg_salary > 10000;
distinct去重
数据必须一模一样才能进行去重,
多个字段针对 多个字段的数据组合结果
因id 是独一无二的数据所以不存在重复
# 年龄去重
select distinct age from emp;
# 年龄与部门组合的结果去重
select distinct age,post from emp;
# 年龄与名字与部门组合的结果去重
select distinct age,name,post from emp;
order by 排序
# 排序关键字
order by... asc --- 升序(默认为升序，要升序就不用写)
order by... desc --- 降序(从大到小)
按照单字段排序
select * from emp order by age;
# 结果有相同年龄的话可以对其薪资在进行排序
在年龄相同的情况下对薪资进行排序
select * from emp order by age,salary;
====================================================================================
练习题
统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
# 1.先对各部门进行分组 并筛选大于10岁的员工
mysql> select post,name from emp where age > 10 group by post;
====================================================================================
# 2.统计他们平均工资
select post,name,avg(salary) from emp where age > 10 group by post;
+-----------------------------+--------+---------------+
| post | name | avg(salary) |
+-----------------------------+--------+---------------+
| operation | 僧龙 | 16800.026000 |
| sale | 哈哈 | 2600.294000 |
| teacher | tom | 151842.901429 |
| 浦东第一帅形象代言 | Jason | 7300.330000 |
+-----------------------------+--------+---------------+
====================================================================================
# 3.保留平均工资大于1000的部门，并进行排序
select post,name,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);
+-----------------------------+--------+---------------+
| post | name | avg(salary) |
+-----------------------------+--------+---------------+
| sale | 哈哈 | 2600.294000 |
| 浦东第一帅形象代言 | Jason | 7300.330000 |
| operation | 僧龙 | 16800.026000 |
| teacher | tom | 151842.901429 |
+-----------------------------+--------+---------------+
limit分页
如果我们需要对一张数据很多的表进行查询的话，可以通过limit 进行限制展示数据的条数
select * from emp limit 2;
+----+-------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | gender | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 1 | Jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+-------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
====================================================================================
也可以设置从第几条开始往后查几条数据
select * from emp limit 2,3;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | gender | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
====================================================================================
练习题
查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
+----+------+--------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | gender | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+--------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+------+--------+-----+------------+---------+--------------+------------+--------+-----------+
regexp正则表达式
我们可以不适用模糊查询，直接使用正则查询
使用正则查找名称为J开头 结尾是N或者Y
select * from emp where name regexp '^j.*(n|y)$';
+----+-------+--------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
| id | name | gender | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
| 1 | Jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
+----+-------+--------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
多表查询思路
表数据准备
create table dep(
id int primary key auto_increment,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
select * from emp,dep;
会将两张表数据对应一遍 拼在一起 称为'笛卡尔积'
但是 他把每一个人对应了所有的部门数据，不合理
应该将有关系的对应到一起才合理
如果我们需要查两张表联合在一起的数据但是两张表的字段可能会起冲突，那么我们只需在 字段名前面加上表名.来指定该字段。
select * from emp,dep where emp.dep_id = dep.id;
我们使用筛选 emp的字段dep_id的值与 dep表的id相同 展示。