MySQL查询关键字where、group by、having、distinct、order by、limit、redexp正则、多表查询之子查询、连表操作

标签：salary group name 查询 emp 多表 id select

目录

• 操作表的SQL语句补充
• truncate\delete\drop的区别
• 查询关键字之select与from
• 查询关键字之where筛选
• 查询关键字之group by 分组
• 查询关键字之having过滤
• 查询关键字之distinct去重
• 查询关键字之order by排序
• 查询关键字之limit分页
• 查询关键字之regexp正则表达式
• 查询关键字之exists判断
• 多表查询的思路
  • 多表查询之子查询
  • 多表查询连表操作
• 练习题

操作表的SQL语句补充

1.修改表名

普通方法：alter table 表名 rename 新表名;
进阶方法：rename table 表名 to 新表名;

2.查询表

select * from 表名;

3.新增字段

普通方法：alter table 表名 add 字段名 字段类型(数字) 约束条件;

指定字段添加after关键词：alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已经存在的字段;

在表的最前面添加字段first关键字：alter table 表名 add 字段名 字段类型(数字) 约束条件 first;

4.修改字段

修改字段名称和类型：alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件;

只修改字段类型：alter table 表名 modify 字段名 新的字段类型(数字) 约束条件;

5.添加一列、删除一列

alter table users add column(字段  字段类型(数字))   

alter table users drop column(字段 字段类型(数字))

6.删除字段

alter table 表名 drop 字段名;

7.复制表

复制表的结构和记录 (key不会复制:主键key 外键key及索引key都不会复制)
create table new_service select * from service; 
解释：创建一个new_service表 内容为service表里的内容

只拷贝表结构(不包含键)
create table new2_service select * from service where 1=2;  
解释：这里呢是创建一个条件判定为假的条件 1肯定不等于2 所以只复制了表的框架 没有键也没有内容

拷贝表的结构并且包含键
create table new3service like service;
解释：运用到like关键字 使新表的表结构完全一样 但是没有拷贝内容。

truncate\delete\drop的区别

truncate table 和 delete只删除数据(记录)不删除表的结构;

drop语句将删除表的数据(记录)和表结构依赖的约束(constrain)，触发器(trigger),索引(index),保留依赖于该表的存储过程/函数，但是变为invalid状态 .

delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚.

查询关键字之select与from

"""
SQL语句的关键字编写顺序与执行顺序是不一致的!!!
	eg:  select name from emp;
	肯定是先支持from确定表 之后执行select确定字段
编写SQL语句针对select和from可以先写个固定模板
	select * from 表名 其他操作
select后的字段可能是实际的 也可能是通过SQL动态产生的 所以可以先用*占位最后再修改
"""
select
	自定义查询表中字段对应的数据
from
	指定操作的对象(到底是哪张表 也可能是多张)

查询关键字之where筛选

where其实就是对数据进行筛选

实例：

# 1.查询id大于等于3小于等于6的数据
写法一：
mysql> select * from emp where id >=3 and id <=6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name  | sex    | 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 |
|  6 | jack  | female |  18 | 2011-02-11 | teacher | NULL         | 9000.00 |    401 |         1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)
写法二：
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;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | 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 rows in set (0.00 sec)
写法二：
'''MySQL也支持成员运算'''
select * from emp where salary in (20000,18000,17000);

小知识-模糊查询：

模糊查询:关键字 like
		%:匹配任意个数的任意字符
		
		_:匹配单个个数的任意字符

# 3.查询员工姓名中包含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)

# 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)
写法二：
'''根据统计长度来查找'''
mysql> select name,salary from emp where char_length(name)=4;

# 5.查询id小于3或者大于6的数据
写法一：
select * from emp where id < 3 or id > 6;
写法二：取反
select * from emp where id not between 3 and 6;

查询关键字之group by 分组

按照某个指定的条件将单个单个的数据分为一个个的整体

分组之后我们研究的对象应该是以组为单位 不应该再直接获取单个数据项 如果获取了应该直接报错 select后面可以直接填写的字段名只能是分组的依据(其他字段需要借助于一些方法才可以获取)

set global sql_mode='strict_trans_tables,only_full_group_by';

mysql> use db;
Database changed
mysql> select * from emp group by post;
# ERROR 1055 (42000): 'db.emp.id' isn't in GROUP BY

此时就不可以拿到所有的数据了，是按部门分组的，将来是按照部门管理数据的

我们写SQL是否需要使用分组 可以在题目中得到答案
	每个、平均、最大、最小
	
配合分组常见使用的有聚合函数
	max			最大值
	min 		最小值
	sum 		总和
	count 		计数
	avg			平均

实例：

# 1.每个部门的最高工资
mysql> select post,max(salary) from emp group by post;
+-----------------------------+-------------+
| post                        | max(salary) |
+-----------------------------+-------------+
| operation                   |    20000.00 |
| sale                        |     4000.33 |
| teacher                     |  1000000.31 |
| 浦东第一帅形象代言          |     7300.33 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)

# 2.每个部门的平均工资
mysql> select post,avg(salary) from emp group by post;
+-----------------------------+---------------+
| post                        | avg(salary)   |
+-----------------------------+---------------+
| operation                   |  16800.026000 |
| sale                        |   2600.294000 |
| teacher                     | 151842.901429 |
| 浦东第一帅形象代言          |   7300.330000 |
+-----------------------------+---------------+
4 rows in set (0.00 sec)

# 3.求每个部门的工资总和
mysql> select post,sum(salary) from emp group by post;
+-----------------------------+-------------+
| post                        | sum(salary) |
+-----------------------------+-------------+
| operation                   |    84000.13 |
| sale                        |    13001.47 |
| teacher                     |  1062900.31 |
| 浦东第一帅形象代言          |     7300.33 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)

# 4.求每个部门的人数
mysql> select post,count(id) from emp group by post;
+-----------------------------+-----------+
| post                        | count(id) |
+-----------------------------+-----------+
| operation                   |         5 |
| sale                        |         5 |
| teacher                     |         7 |
| 浦东第一帅形象代言          |         1 |
+-----------------------------+-----------+
4 rows in set (0.00 sec)

小知识1-拼接：

group_concat:用来做分组以后，获取除分组依据以外的一些字段，可以是一个也可以是多个，字段与字段之间可以设置一些分割符号
    
concat 用户分组之前的拼接操作

concat_ws 当多个字段链接符相同的情况下推荐使用

# 5.查询分组之后的部门名称和每个部门下所有的员工姓名
mysql> select post,group_concat(name) from emp group by post;
+-----------------------------+------------------------------------------------+
| post                        | group_concat(name)                             |
+-----------------------------+------------------------------------------------+
| operation                   | 程咬铁,程咬铜,程咬银,程咬金,僧龙               |
| sale                        | 拉拉,乐乐,西西,呵呵,哈哈                       |
| teacher                     | sank,jenny,jack,owen,tony,kevin,tom            |
| 浦东第一帅形象代言          | jason                                          |
+-----------------------------+------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select post,group_concat(name,':',salary) from emp group by post;
+-----------------------------+--------------------------------------------------------------------------------------------------+
| post                        | group_concat(name,':',salary)                                                                    |
+-----------------------------+--------------------------------------------------------------------------------------------------+
| operation                   | 程咬铁:17000.00,程咬铜:18000.00,程咬银:19000.00,程咬金:20000.00,僧龙:10000.13                    |
| sale                        | 拉拉:4000.33,乐乐:3000.29,西西:1000.37,呵呵:2000.35,哈哈:3000.13                                 |
| teacher                     | sank:10000.00,jenny:30000.00,jack:9000.00,owen:2100.00,tony:3500.00,kevin:8300.00,tom:1000000.31 |
| 浦东第一帅形象代言          | jason:7300.33                                                                                    |
+-----------------------------+--------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

小知识2-分组起别名：

分组起别名:
	在查看结果的时候可以给字段起别名
	select post as '部门',max(salary) as '最高薪资' from emp group by post;
省略as：
	select post '部门',max(salary) as '最高薪资' from emp group by post;
	as可以省略但是为了语义更加明确建议不要省略

查询关键字之having过滤

where与having的功能其实是一样的 都是用来筛选数据

只不过where用于分组之前的筛选 而having用于分组之后的筛选

为了人为的区分 所以叫where是筛选 having是过滤

实例：

# 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000
select * from emp where age > 30; 获取到的是所有年龄大于30的员工数据

mysql> select post,group_concat(name) from emp where age>30 group by post;

mysql> select post,avg(salary) from emp where age>30 group by post;

mysql> select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;

关键字的使用顺序：where--group by--having

查询关键字之distinct去重

去重的前提是存在一摸一样的数据，如果针对于主键肯定无法去重
关键字：distinct

实例：

1、针对于主键没有效果：
mysql> select distinct id,age from emp;
+----+-----+
| id | age |
+----+-----+
|  1 |  18 |
|  2 |  78 |
|  3 |  81 |
|  4 |  73 |
|  5 |  28 |
|  6 |  18 |
|  7 |  18 |
|  8 |  48 |
|  9 |  48 |
| 10 |  38 |
| 11 |  18 |
| 12 |  18 |
| 13 |  28 |
| 14 |  28 |
| 15 |  18 |
| 16 |  18 |
| 17 |  18 |
| 18 |  18 |
+----+-----+
18 rows in set (0.00 sec)
   
mysql> select distinct id,distinct age from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct age from emp' at line 1

2、查看有几个年龄段的员工：
mysql> select distinct age from emp;
+-----+
| age |
+-----+
|  18 |
|  78 |
|  81 |
|  73 |
|  28 |
|  48 |
|  38 |
+-----+
7 rows in set (0.00 sec)

查询关键字之order by排序

"""
关键字：  
	关键字需要用在指定字段之后
	asc升序(默认)、desc降序 
"""

select * from emp order by salary asc; #默认升序排
select * from emp order by salary desc; #降序排

select * from emp order by age desc; #降序排

#先按照age降序排，在年轻相同的情况下再按照薪资升序排,可以自己定义是升序还是降序
select * from emp order by age desc,salary asc; 

# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
mysql> select post,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分页

在查看的数据太多情况下，使用分页来限制每次查看数据的数量。

关键字： limit

用法：limit 数字
1、limit后只跟一个数字：从头开始展示多少行
	select * from emp limit 5;    # 从头开始展示5行
	
2、limit后跟两个数字：第一个数字为起始位，第二个数字为从起始位开始展示多少行
	select * from emp limit 5,5;   # 从第5行开始展示5行
	

实例：

# 求薪资最高的员工所有数据
分析：可以使用order by使薪资降序排序
	 在使用limit 1 展示一行
	 
mysql> select * from emp order by salary desc limit 1;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | tom  | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)

查询关键字之regexp正则表达式

正则表达式：使用一些符号的组合产生一些特殊的含义，然后去字符串中筛选出符合条件的数据

关键字：regexp

实例：

# 取指定员工信息
mysql> select * from emp where name regexp '^j.*(n|y)$';
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
| id | name  | sex  | 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)
解释：取员工姓名为：开头为j结尾为n或者y中间是任意字符的所有信息

查询关键字之exists判断

定义：exists关键字表示存在,在使用在使用exists关键字时，内层查询语句不返回查询的记录，而是返回一个真假值，True或False。 

当返回为True时，外层查询语句将进行查询、
返回值为False时，外层查询语句不进行查询。

用法：sql语句 exists(判断语句) 
判断语句为True则执行sql语句 如果返回为False则不执行.

实例：

select * from emp where exists (select id from emp where id > 3);
解释：括号内判断语句为True有结果则会执行exists前的sql语句

select * from emp where exists (select id from emp where id > 20);
解释：括号内判断语句为False没有结果则不会执行exists前的sql语句

多表查询的思路

子查询
	将一张表的查询结果括号括起来当做另外一条SQL语句的条件
        eg:类似以日常生活中解决问题的方式
			第一步干什么
			第二步基于第一步的结果在做操作 ...
               
连表操作
	先将所有涉及到结果的表全部拼接到一起形成一张大表 然后从大表中查询数据

多表查询之子查询

其实就是分布操作 将一张表的查询结果当作另外一条sql语句的查询条件

实例：

# 查询jason的部门名称
1.先获取Jason的部门编号
	select dep_id from emp1 where name = 'jason';
2.根据部门编号获取名称
	select name from dep1 where id = 200;
结合起来：
    mysql> select name from dep1 where id = (select dep_id from emp1 where name = 'jason');
    +--------+
    | name   |
    +--------+
    | 技术   |
    +--------+
    1 row in set (0.00 sec)

多表查询连表操作

1、from 后可跟多个表用逗号隔开，使两张表拼接
	eg ：from emp1,dep1-----这个现象称为笛卡儿积

2、在涉及到多表操作的时候 为了避免表字段重复
	需要在字段名的前面加上表名限制

实例：

select emp1.name,dep1.name from emp1,dep1 where emp1.dep_id=dep1.id;

但是这种写法效率太低不建议写

'''专门的语法：'''
inner join		内连接
	只拼接两边都有的字段数据
left join		左连接
	以左表为基准 展示所有的数据 没有对应则NULL填充
right join		右连接
	以右表为基准 展示所有的数据 没有对应则NULL填充
union		   全连接
	select * from emp1 left join dep1 on emp1.dep_id = dep1.id
   	union
	select * from emp1 right join dep1 on emp1.dep_id = dep1.id;

练习题

完成下列练习题
#1. 查询岗位名以及岗位包含的所有员工名字
mysql> select dep1.name,emp1.name from emp1 right join dep1 on  emp1.dep_id = dep1.id;
+--------------+--------+
| name         | name   |
+--------------+--------+
| 技术         | jason  |
| 人力资源     | dragon |
| 人力资源     | kevin  |
| 销售         | nick   |
| 运营         | owen   |
| 安保         | NULL   |
+--------------+--------+
#2. 查询岗位名以及各岗位内包含的员工个数
mysql> select dep1.name,count(emp1.name) from emp1 right join dep1 on  emp1.dep_id = dep1.id group by dep1.name;
+--------------+------------------+
| name         | count(emp1.name) |
+--------------+------------------+
| 人力资源     |                2 |
| 安保         |                0 |
| 技术         |                1 |
| 运营         |                1 |
| 销售         |                1 |
+--------------+------------------+
5 rows in set (0.00 sec)
#3. 查询公司内男员工和女员工的个数
mysql> select gender as '性别',count(1) as '数量' from emp1 group by gender;
+--------+--------+
| 性别   | 数量   |
+--------+--------+
| male   |      4 |
| female |      2 |
+--------+--------+
2 rows in set (0.00 sec)
#4. 查询岗位名以及各岗位的平均薪资
mysql> select dep.name,avg(emp.salary) from emp right join dep on  emp.office = dep.id group by dep.name;
+-----------+-----------------+
| name      | avg(emp.salary) |
+-----------+-----------------+
| 教学部    |   133775.080000 |
| 运维部    |    16800.026000 |
| 销售部    |     2600.294000 |
+-----------+-----------------+
3 rows in set (0.00 sec)
#5. 查询岗位名以及各岗位的最高薪资
mysql> select dep.name,max(emp.salary) from emp right join dep on  emp.office = dep.id group by dep.name;
+-----------+-----------------+
| name      | max(emp.salary) |
+-----------+-----------------+
| 教学部    |      1000000.31 |
| 运维部    |        20000.00 |
| 销售部    |         4000.33 |
+-----------+-----------------+
3 rows in set (0.00 sec)
#6. 查询岗位名以及各岗位的最低薪资
mysql> select dep.name,min(emp.salary) from emp right join dep on  emp.office = dep.id group by dep.name;
+-----------+-----------------+
| name      | min(emp.salary) |
+-----------+-----------------+
| 教学部    |         2100.00 |
| 运维部    |        10000.13 |
| 销售部    |         1000.37 |
+-----------+-----------------+
3 rows in set (0.00 sec)
#7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
#8. 统计各部门年龄在30岁以上的员工平均工资
#9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

标签：salary,group,name,查询,emp,多表,id,select
From： https://www.cnblogs.com/zxr1002/p/16596886.html