MySQL查询关键字

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 |
+----+-------+--------+-----+------------+-----------------------------+--------------+----------+--------+-----------+

操作补充

'concar'
	用于给分组前的字段做拼接操作
 eg：
select concat(name,'|',sex) from emp;

'concat_ws'
	用于拼接多个字段且中间的连接符一致
eg:
select concat_ws('|',name,sex,age) from emp;

'exists'
  后者语句有结果的情况下才会执行前者语句否则不执行 返回空数据
eg: 
    前者sql语句 exists（后者sql语句）

表相关操作补充

alter table 表名 add 字段名 字段类型(数字) 约束条件;            # 添加字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 旧字段名;  # 在旧字段名下添加新字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;        # 最上行添加新字段
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;   # 修改字段与字段类型
alter table 表名 modify 旧字段名 新字段类型(数字) 约束条件；      # 修改 表中字段的类型

alter table 表名 drop 字段名;                           # 删除字段