MySQL学习记录（五）补充表的一些操作即where,group by,having用法

一、修改表

# MySQL对大小写是不敏感的

"""

1、修改表名

        alter table 表名 rename 新表名;

2、增加字段

        alter table 表名 add 字段名 字段类型（宽度） 约束条件；

        alter table 表名 add 字段名 字段类型（宽度） 约束条件 first；

        # first 将字段直接添加到表的最前面

        alter table 表名 add 字段名 字段类型（宽度） 约束条件 after 字段名；

        # after 字段名 跟在该指定字段的后面

3、删除字段

        alter table 表名 drop 字段名;

4、修改字段

        alter table 表名 modify 字段名 字段类型（宽度）约束条件；

        alter table 表面 change 旧字段名 新字段名 字段类型（宽度）约束条件；

"""

二、复制表

# 其实我们sql语句的结果其实也是一张虚拟表

create table 表名 select * from 旧表;

# 能复制表结构和数据，但不能复制主键、外键、索引.....

# 假设已经创建了一张user表

注意new_user在下面创建前是不存在的 

 # 若复制的是一张空表也可以成功创建，复制它的表结构，只是没有数据

补充小知识：

当你的数据太多显示的很乱时：

select * from emp;(下面是它的结果)

 select * from emp\G;

 会让显示结构好看很多

# 几个重要关键字的执行顺序

# 书写顺序

select id,name from emp where id>3;

# 执行顺序

from ->where->select

三、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 查询员工的姓名中包含字母o的员工姓名和薪资

"""

模糊查询

        like

                % 匹配任意多个字符

                _匹配任意单个字符

"""

select name,salary from emp where name like'%o%';

4 查询员工姓名是由四个字符组成的姓名和薪资

select name,salary from emp where name like'____'# 四个‘_’

select name,salary from emp where char_length(name)=4;

5 查询id小于3或者大于6的数据

select * from emp where id not between 3 and 6;

6 查询薪资不在20000，18000，17000范围的数据

select * from emp where salary not in (20000,18000,17000);

7 查询岗位描述为空的员工姓名和岗位名 针对Null不用=符号 用is

select name,post from emp where post_comment is NULL;

四、group  by分组

"""

分组应用场景

        男女比例

        部门平均薪资

        部门秃头率

        .......

"""

# 按照部门分组

select * from emp group by post;

# 分组操作后，最小操作单位应该是组，而不是组内的单个数据

"""

上述命令在你没有设置严格模式的时候是可以正常执行的（如果设置了严格模式直接报错），返回的是分组之后每个数据的第一条数据，但这是不符号分组的规范的；分组之后不应该考虑单个数据，而应该是以组为操作单位的（分组之后没办法获取单个数据）

"""

set global sel_mode='strict_trans_tables,only_full_group_by';

# 用上面语句设置成严格模式后，分组默认只能拿到分组的依据

select post from emp group by post;

#按照什么分组就只能拿到分组，其它字段不能直接获取，需要借助于一些方法

"""

一般什么时候需要分组呢？

        遇到以下关键子时

                每个 平均 最高 最低

"""

# 1 获取每个部门的最高薪资

select post,max(salary) from emp group by post;

select post as '部门',max(salary) as '最高薪资' from emp group by post;

select post  '部门',max(salary)  '最高薪资' from emp group by post;

# as 字段可以给字段起别名，也可以省略不写，

# 但所不推荐省略，因为省略的语意不明确，容易出错

# 2 获取每个部门的最低薪资

select post,min(salary) from emp group by post;

# 3 获取每个部门的平均薪资

select post,avg(salary) from emp group by post;

# 4 获取每个部门的薪资总和

select post,sum(salary) from emp group by post;

# 5 获取每个部门的人数

select post,count(id) from emp group by post; #这个是最常用的，符合规范

select post,count(salary) from emp group by post; #也可以

# 查询分组之后的部门名称和每个部门下所有的员工姓名

# group_concat 不单单可以支持你获取分组之后的其它字段值，还支持拼接连接操作

select post,group_concat name from emp group by post;# 这种写法是错误的

select post,group_concat(name) from emp group by post;

select post,group_concat(name,'_DSB') from emp group by post;

# 结果会每个名字后面跟_DSB 例如：李明_DSB

select post,(name,'：'，salary) from emp group by post;

# 结果显示：例如：李明:20000

# concat与group_concat类似，在不分组的时候用

select concat('NAME:',name) ,concat('SALARY：',salary) from emp;

# 结果显示：例如：NAME:李明    SALARY：200000

# 补充：as语法不单单可以给字段起别名，还可以跟表起别名

select emp.name,emp.id from emp;

select t1.name,t1.id from emp as t1;

# 查询每个人的年薪（12*salary）

select name,salary*12 from emp;

"""

分组注意事项：

关键子where和group by 同时出现时，group by 必须出现在where的后面

where先对整体过滤操作后再分组操作

聚合函数只能出现再分组之后使用

where筛选条件不能使用聚合函数

"""

select id,name from emp where max(salary)>2000;# 错误写法

select max(salary) from emp;# 不分组，默认整体是一组

# 统计各部门人数在30以上的员工的平均薪资

# 思路

        1、先求所有年龄大于30岁的员工

        select *from emp where age>30;

        2、再对结果进行分组

        select *from emp where age>30 group by post;

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

五、having分组之后的筛选条件

"""

having 的语法跟where是一样的

只不过having是在分组之后进行的过滤操作

即having是可以使用聚合函数的

"""

# 统计个部门年龄在30岁以下的员工工资并且保留平均薪资大于10000的部门

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