标签：语句 salary 18 emp SQL post where select
SQL语句
一、SQL语句查询关键字
select 查询指定需要查询的字段信息
select * 查询所有字段
select name 查询name字段
select char_length(name) 支持对字段做处理
from 指定需要查询的表信息
from user 查询user表的详细信息
ps:SQL语句中的关键字的执行顺序和编写顺序宝宝巴士一致的，可能会错乱
编写SQL语句有个固定的语句：
select * from 表名 其他操作
select 后面得字段根据需求写入具体的字段，也可以通过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);
三、查询关键字之where筛选
语句：
select 字段名 from 表名 where 查询条件;
作用：where过滤数据，增加约束条件
where在查询数据库的结果返回之前对数据库中的查询条件进行约束
where后面不能写聚合函数
学习更多的内置方法可以通过help + 方法名,进行查看
| 操作符 |
详细说明 |
| benween A and B |
在指定两个值之间 |
| is |
指定数值/检查空值 |
| like |
模糊查询/搜索匹配字段中指定的内容 |
| not like |
模糊查询/搜索匹配字段中除去的内容 |
| and |
where组合/并且 |
| or |
where组合/或者 |
实操：
1.查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and id <= 6;
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;
4.查询员工姓名中包含字母o的员工姓名与薪资
select name,salary from emp where name like '%o%';
select * from emp where name like '%o%';
# 模糊查询：条件不够精确的查询，关键字：like, 模糊查询常用的符号：%(匹配任意个数的任意字符)
5.查询员工姓名是由四个字符组成的员工姓名与其薪资
select * from emp where name like '____';
select * from emp where char_length(name) = 4;
select name,salary from emp where name like '____';
6.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select * from emp where post_comment is null;
代码演示：
1.查询id大于等于3小于等于6的数据
mysql> select * from emp where id >= 3 and id <= 6;
mysql> select * from emp where id between 3 and 6;
2.查询薪资是20000或者18000或者17000的数据
mysql> select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
mysql> select * from emp where salary in (20000,18000,17000);
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | gender | 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.查询id小于3大于6的数据
mysql> select * from emp where id < 3 or id > 6;
mysql> select * from emp where id not between 3 and 6;
4.查询员工姓名中包含字母o的员工姓名与薪资
mysql> select * from emp where name like '%o%';
mysql> select name,salary from emp where name like '%o%';
+-------+------------+
| name | salary |
+-------+------------+
| jason | 7300.33 |
| tom | 1000000.31 |
| tony | 3500.00 |
| owen | 2100.00 |
+-------+------------+
4 rows in set (0.00 sec)
5.查询员工姓名是由四个字符组成的员工姓名与其薪资
mysql> select * from emp where char_length(name) = 4;
mysql> select name,salary from emp where name like '____';
+------+----------+
| name | salary |
+------+----------+
| tony | 3500.00 |
| owen | 2100.00 |
| jack | 9000.00 |
| sank | 10000.00 |
+------+----------+
4 rows in set (0.00 sec)
6.查询岗位描述为空的员工名与岗位名
mysql> select * from emp where post_comment is null;
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 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 |
| 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 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.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 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
四、查询关键字之group by分组
分组的目的是为了更好的统计相关数据
聚合函数：专门用于分组之后的数据统计
eg：
max/min/sum/avg/count 最大值，最小值，求和，平均值，计数
1.将员工数据按照部门分组
select * from emp group by post;
MySQL5.6默认不会报错
set global sql_mode='strict_trans_tables,only_full_group_by'
MySQL5.7及8.0默认都会直接报错
原因是分组之后 select后面默认只能直接填写分组的依据 不能再写其他字段
select post from emp group by post;
select age from emp group by age;
分组之后默认的最小单位就应该是组 而不应该再是组内的单个数据单个字段
2.获取每个部门的最高工资
# 要不要分组我们可以从题目需求分析出来，从关键字分析出来，每个，平均
mysql> select post,max(salary) from emp group by post;
mysql> select post as '部门',max(salary) as '最高薪' from emp group by post;
+-----------------------------+------------+
| 部门 | 最高薪 |
+-----------------------------+------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+------------+
4 rows in set (0.00 sec)
3.一次获取部门薪资相关统计
mysql> select post as '部门',max(salary) as '最高薪',min(salary) as '最低薪',avg(salary) as '平均薪资',sum(salary) as '月支出' from emp group by 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 |
+-----------------------------+------------+-----------+---------------+------------+
4 rows in set (0.00 sec)
4.统计每个部门的人数
mysql> select post as '部门',count(id) as '人数' from emp group by post;
+-----------------------------+--------+
| 部门 | 人数 |
+-----------------------------+--------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 浦东第一帅形象代言 | 1 |
+-----------------------------+--------+
4 rows in set (0.00 sec)
5.统计每个部门的部门名称以及部门下的员工姓名
select post,name from emp group by post;
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,'_nb') from emp group by post;
select post,group_concat('sb_'name,'_nb') from emp group by post;
mysql> select post,group_concat(name,'|',age) from emp group by post;
+-----------------------------+---------------------------------------------------------------+
| post | group_concat(name,'|',age) |
+-----------------------------+---------------------------------------------------------------+
| operation | 程咬铁|18,程咬铜|18,程咬银|18,程咬金|18,僧龙|28 |
| sale | 拉拉|28,乐乐|18,西西|18,呵呵|38,哈哈|48 |
| teacher | sank|48,jenny|18,jack|18,owen|28,tony|73,kevin|81,tom|78 |
| 浦东第一帅形象代言 | jason|18 |
+-----------------------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)
五、查询关键字之having过滤
having与where本质是一样的，都是用来对数据做筛选
区别：
where ---->> 用在分组之前(首次筛选)
having ---->> 用在分组之后(二次筛选)
1.统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
步骤1：先筛选出年龄大于30岁的员工信息
select * from emp where age > 30;
步骤2：对于筛选的数据按照部门分组并统计平均薪资
select post,avg(salary) from emp where age > 30 group by post;
步骤3:针对分组统计之后的结果做二次筛选
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
mysql> select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
1 row in set (0.00 sec)
六、查询关键字之distinct去重
有一模一样的数据才能够去重
select distinct id,age from emp;
select distinct age,post from emp;
mysql> select distinct age,post from emp;
+-----+-----------------------------+
| age | post |
+-----+-----------------------------+
| 18 | 浦东第一帅形象代言 |
| 78 | teacher |
| 81 | teacher |
| 73 | teacher |
| 28 | teacher |
| 18 | teacher |
| 48 | teacher |
| 48 | sale |
| 38 | sale |
| 18 | sale |
| 28 | sale |
| 28 | operation |
| 18 | operation |
+-----+-----------------------------+
13 rows in set (0.00 sec)
七、查询关键字之order by排序
1.单个字段排序
select * from emp order by age; # 默认升序
select * from emp order by age desc; # 降序
2.多个字段排序
select * from emp order by age,salary desc;
3.统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
步骤1：先筛选出年龄大于10岁的员工
select * from emp where age > 10;
步骤2：再对他们按照部门分组统计平均薪资
select post,avg(salary) from emp where age > 10 group by post;
步骤3：对于分组结果进行二次筛选
select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000;
步骤4 ：按照指定字段排序
select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);
mysql> select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary > 1000 order by avg_salary;
+-----------------------------+---------------+
| post | avg_salary |
+-----------------------------+---------------+
| sale | 2600.294000 |
| 浦东第一帅形象代言 | 7300.330000 |
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------------------------+---------------+
4 rows in set (0.00 sec)
八、查询关键字之limit分页
当表中数据特别多的情况下 我们很少会一次性获取所有的数据,所以就需要对数据进行分页处理
select * from emp limit 2,8; # 从第2条开始，往后读取8条
查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
select * from emp order by salary limit 2,5;
mysql> select * from emp order by salary limit 2,5;
+----+--------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | gender | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
+----+--------+--------+-----+------------+---------+--------------+---------+--------+-----------+
5 rows in set (0.00 sec)
九、查询关键字之regexp正则表达式
SQL语句写不习惯的话，可以自己写正则批量查询
8.0之后版本这样写：
select * from emp where name regexp '^j.*?(n|y)$';
5.5版本这样写：
mysql> 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 |
+----+-------+--------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)
十、多表查询
表数据准备
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; 会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
select * from emp,dep where emp.dep_id=dep.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据
标签：语句,
salary,
18,
emp,
SQL,
post,
where,
select
From： https://www.cnblogs.com/zx0524/p/16926043.html