MySQL查询关键字where、group by、having、distinct、order by、limit、redexp正则、多表查询之子查询、连表操作
操作表的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 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)
#5. 查询岗位名以及各岗位的最高薪资
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)
#6. 查询岗位名以及各岗位的最低薪资
mysql> select post,min(salary) from emp group by post;
+-----------------------------+-------------+
| post | min(salary) |
+-----------------------------+-------------+
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)
#7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
mysql> select sex,avg(salary) from emp group by sex;
+--------+---------------+
| sex | avg(salary) |
+--------+---------------+
| male | 110920.077000 |
| female | 7250.183750 |
+--------+---------------+
2 rows in set (0.00 sec)
#8. 统计各部门年龄在30岁以上的员工平均工资
mysql> select post,avg(salary) from emp where age>30 group by post;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| sale | 2500.240000 |
| teacher | 255450.077500 |
+---------+---------------+
2 rows in set (0.00 sec)
#9. 统计各部门年龄在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)
