数据库的操作

1、表的修改复制(了解)

2、查询表-拼接操作（重要)

表的修改等操作

    1、修改表名
    alter table 表名 rename 新表名
    2、增加字段
    alter table 表名 add 字段名 字段类型(宽度) 约束条件;
    alter table 表名 add 字段名 字段类型(宽度) 约束条件 first; 将字段直接添加在最前面
    alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;
    3、删除字段
    alter table 表名 drop 字段名;
    4、修改字段
    alter table 表名 modify 字段名 字段类型(宽度) 约束条件；
    alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;

2、表的查询拼接---重要

1）准备表

create table emp(
    id int not null unique auto_increment,
    name varchar(20) not null,
    sex 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,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','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 * from emp\G; # \G可以单独展示每行数据

2）where约束条件

作用 对整体事物的一个删选操作
1、查询 3 <= id <= 6 的数据
select id ,name,age from emp where id>=3 and id<=6;
select id ,name,age 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 id,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 或者 id>6的
select * from emp where id not between 3 and 6;

6、查询薪资不在20000，17000，18000
select * from emp where salary not in (20000,17000,18000);

7、查询岗位描述为空的员工 姓名和岗位名
select name,post from emp where post_comment is NULL;

3）group分组应用练习题

1、按照部门分组
select * from emp group by post;
# 遇到了问题 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'day03.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
5.7 中需要如下修改 
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
分组之后 最下可操作单位应该是租 还不再是组内的单个数据
    set global sql_mode='ONLY_FULL_GROUP_BY';  再设置回去
5.7 严格模式下 分组只能拿到分组的依据
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;
as 可以不写 省略，不推荐 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;   salary age也可以
6、查询分组之后每个部门名称和每个部门下所有员工姓名
    # group_concat 不单单可以支持获取分组之后的其他字段值，还支持拼接操作
    select post,group_concat(name) from emp group by post;
    select post,group_concat(name,'_DSB') from emp group by post; 拼接
    select post,group_concat(name,':',salary) from emp group by post;
    
    # concat 不分组的时候用
    select concat('NAME:',name),concat('SAL:',salary) from emp;

# 补充 as 语法不单单可以给字段起别名 还可以给表起别名
    select emp.id,emp.name from emp;
    select emp.id,emp.name from emp as t1;  当前有效
7、查询每个人的年薪
    select name,salary*12 from emp;

分组注意事项

# 关键字 where 和group by 同时出现的时候group by必须在where的后面
where 先对整体数据进行过滤之后再分组操作
聚合函数只能在分组之后使用
where 筛选不能使用聚合函数
select id,name,age from emp where max(salary) > 3000; 报错

select max(salary) from emp;

# 统计各部门年龄在30以上的员工平均薪资
    先求所有年龄大于30
        select * from emp where age>30;
    再对结果进行分组 不是严格模式可用 5.6
        select * from emp where age>30 group by post;
    两步和一
    select post,avg(salary) from emp where age>30 group by post;

4）having分组之后的筛选条件

having的语法 和 where是一致的
只不过having是在分组之后进行的过滤操作
having是可以直接使用聚合函数的
# 统计各部门年龄在30以上的员工平均工资 并且保留 平均薪资大于10000的部门
select post,avg(salary) from emp
    where age>30
    group by post
    having avg(salary)>10000
    ;

5）distinct 去重

一定要注意，必须是完全一样的数据才可以去重  id 不一样也不行
所以 带id 不能去重 要去掉
select distinct age from emp;

6）order by 排序 默认升序 后面asc可以不写

select * from emp order by salary;
select * from emp order by salary desc; # 降序

select * from emp order by salary desc,salery asc;
# 先按照salary降序排 如果salary相同再按照age升序拍
# 同合计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000 的部门 然后平均薪资降序
    select post,avg(salary) from emp
        where age>10
        group by post
        having avg(salary)>1000
        order by avg(salary) desc
        ;

7）limit 限制条数

select * from emp;
# 针对数据过多的情况 我们通常都是分页处理
select * from emp limit 3;   # 3个一页
select * from emp limit 0,5;  # 第一个参数是起始位
select * from emp limit 5,5;  # 第二个参数是展示位

8）正则

select * from emp where name regexp '^j.*(n|y)$';

9）多表查询

drop table emp; # 删除之前的emp

create table dep(
id int,
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,'运营');

insert into emp(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
select * from dep,emp;   # 结果 笛卡尔积 了解

select * from emp,dep where emp.dep_id = dep.id; # 拼表 和内连接一样
# MySQL开设对应的方法
    inner join  # 内连接
        select * from emp inner join dep on emp.dep_id = dep.id;
        # 只拼接 两张表公有的数据部分
    left join   # 左连接
        select * from emp left join dep on emp.dep_id = dep.id;
        # 左表所有的数据都展示出来 没有对应的项就用NULL
    right join  # 右链接
        # select * from emp  right join dep on emp.dep_id = dep.id;
        # 右表所有的数据都展示出来 没有对应的项就用NULL
    union   # 全连接
        select * from emp left join dep on emp.dep_id = dep.id
        union
        select * from emp  right join dep on emp.dep_id = dep.id;

10）子查询 分步解决

分步骤解决问题
# 1、查询部分是技术或者人力资源的员工信息
    先获取部门的id号
    再去员工表里面筛选出对应的员工
    select id from dep where name='技术' or name='人力资源';

# 表的查询结果可以作为其他表的查询条件
# 也可以通过起别名的方式把它作为一个虚拟表

10）知识点补充

# 查询平均年龄在25岁以上的部门名称
# 联表
    select dep.name from emp inner join dep
        on emp.dep_id = dep.id
        group by dep.name
        having avg(age)>25
        ;
# 子查询
    select name from dep where id in
        (select dep_id from emp group by dep_id
        having avg(age)>25);

# any all in 
any (sql查找结果) 可加判断
in (结果)
# 大于平均年龄的人
select * from emp where age>any(
select avg(age) from emp group by dep_id);

11）exist 存在

select * from dep
where exists (
    select * from emp where emp.dep_id = dep.id
);

# 只返回布尔值
'''
返回True 外层查询语句执行
返回False的时候外层查询语句不在执行