3-数据库基本dml操作
一.where过滤条件
1.select name,salary from emp where name like "____";
select name,salary from emp where char_length(name) = 4;
2.select * from emp where id not between 3 and 6;
3.select * from emp where salary not in (20000,18000,30000);
4.select name,post from emp where post_comment is NULL;
二.group by 分组：
1.select * from emp group by post;
分组之后，最小可操作单位应该是组，而不再是组内的单个数据；分组之后没办法直接获取组内单个数据。
set global sql_mode = "strict_trans_tables,only_full_group_by"
设置严格模式后，分组只能拿到分组的数据，其它字段不能直接获取，需要借助于一些方法(聚合函数)
select post from emp group by post;
2.select post,max(salary) from emp group by post;
select post as ’部门‘,max(salary) as '最高薪资' from emp group by post;
select post,min(salary) from emp group by post;
select post,avg(salary) from emp group by post;
select post,sum(salary) from emp group by post;
select post,count(id) from emp group by post;
3.
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;
concat_ws：如果多个字段之间的连接符号是相同的情况下，可以直接使用concat_ws来完成
select concat_ws(':',name,age,sex) from emp;
4.select name,salary*12 from emp;
5.select post,avg(salary) from emp where age > 30 group by post;
分组注意事项：
# 1.关键字where和grouo by同时出现的时候group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
select id,name,age from emp where max(salary) > 3000; # 报错
三.having分组之后的筛选条件
having的语法跟where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
四.distinct去重
一定要注意，必须是完全一样的数据才可以去重
一定不要将主键忽视了，有主键存在的情况下，是不可能去重的
select distinct id,age from emp; # id是主键，不能去重
select distinct age from emp;
五.order by排序
order by默认是升序asc，asc可以省略不写；也可以改为desc降序
select * from emp order by salary;
select * from emp order by salary asc;
select * from emp oder by salary desc;
select * from emp order by age desc，salary asc; 先按照age降序排，碰到相同age，再按照salary升序排
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000 order by avg(salary) desc;
六.limit限制展示条数
针对数据过多的情况，我们通常都是做分页处理
select * from emp limit 3; # 只展示三条数据
select * from emp limit 0,5; # 第一个参数是起始位置，第二个参数是展示条数
select * from emp limit 5,5;
七.多表查询
1.连表查询
inner join：内连接，只拼接两张表中共有的数据部分(交集)
select * from emp inner join dep on emp.dep_id = dep.id;
left join：左连接，左表作为主表所有的数据都展示出来，没有对应的项就用NULL
select * from emp left join dep on emp.dep_id = dep.id;
right join：右连接，右表作为主表所有数据都展示出来，没有对应的项就用NULL
select * from emp right join dep on emp.dep_id = dep.id;
union（full join）：全连接，左右两表所有的数据都展示出来
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;
2.子查询：将一个查询语句的结果当做另外一个查询语句的条件去用。分步骤查询，步骤1，步骤2
select name from emp where dep_id in(
select id from dep where name='技术' or name='人力资源'
);
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;
); # 子查询
3.关键字exists(了解)
只返回布尔值True False
返回True的时候外层查询语句执行，返回False的时候外层查询语句不执行
select * from emp where exists(select id from dep where id > 3); 有值
select * from emp where exists(select id from dep where id > 300); 无值
总结：表的查询结果可以作为其它表的查询条件，也可以通过起别名的方式把它作为一张虚拟表和其它表关联。多表查询两种方式1.先拼接表再查询 2.子查询一步一步来
八.navicat破解版安装：https://www.cr173.com/soft/126934.html
九.触发器
1.在满足对表数据进行增，删，改的情况下，自动触发的功能。
使用触发器可以帮助我们实现监控，日志...
触发器可以在六种情况下自动触发”增前增后“，”删前删后“，”改前改后“
2.基本语法结构：
create trigger 触发器的名字 before/after insert/update/delete on 表名
for each row
begin
sql语句
end
3.具体使用，针对触发器的名字，我们通常需要做到见名知意
针对增：
create trigger tri_before_insert_t1 before insert on t1
for each row
begin
sql 语句
end
create trigger tri_after_insert_t1 after insert on t1
for each row
begin
sql 语句
end
ps:修改mysql默认的语句结束符，只作用于当前窗口
delimiter $$ 将默认的结束符号由;改为$$
4.案例
create table cmd(
id int primary key auto_increment,
user char(32),
priv char(10),
cmd char(64),
sub_time datetime, # 提交时间
success enum('yes','no') # no代表执行失败
);
create table errlog(
id int primary key auto_increment,
err_cmd char(64),
err_time datetime
);
当cmd表中的记录success字段是no那么触及触发器执行errlog表中插入数据
delimiter $$
create trigger tri_after_insert_cmd after insert on cmd
for each row
begin
if NEW.success = 'no' then
insert into errlog(err_cmd,err_time)
values(NEW.cmd,sub_time);
end if;
end $$
delimiter ;
删除触发器：drop trigger tri_after_insert_cmd;
十.事务
事务相关的关键字
# 1.开启事务
start transaction;
# 2.回滚(回到事务执行之前的状态)
rollback;
# 3.确认(确认之后就无法回滚了)
commit;
""""模拟转账功能“”“”“”“
create table user(
id int primary key auto_increment,
name char(16),
balance int
);
insert into user values(1,'jason',1000),(2,'egon',1000),(3,'tank',1000);
# 1）先开启事务
start transaction;
# 2）多条sql语句
update user set balance=900 where name = 'jason';
update user set balance=1010 where name = 'egon';
update user set balance=1090 where name = 'tank';
# 3）回滚
rollback; # 回滚到事务执行前的状态
# 4）提交
commit; # 刷入内存，无法回滚
十一：存储过程
基本使用:
create procedure 存储过程的名字(形参1，形参2，...)
begin
sql代码
end;
# 调用
call 存储过程的名字();
案例：
delimiter $$
create procedure p1(
in m int, # 只进不出，m不能返回出去
in n int,
out res int # 该形参可以返回出去
)
begin
select name from user where id > m and id < n;
set res = 666; # 将res变量修改 用来标识当前的存储过程代码确实执行了
end $$
delimiter ;
# 针对形参res，不能直接传数据，应该传一个变量名
# 定义变量
set @ret = 10;
# 查看变量对应的值
select @ret;
# 调用存储过程：call p1(1,10,@ret);
十二.索引
索引：就是一种数据结构，类似于书的目录，意味着以后在查询数据时应该先找目录再找数据，而不是一页一页地翻书，从而提升查询速度降低io操作
索引在mysql中也叫”键“，是存储引擎用于快速查找记录地一种数据结构
primary key，unique key，index key
前面两种key除了加速查询速度还各自具有约束条件，index key没有任何约束条件，只是加速查询数据
1）聚集索引(primary key)
聚集索引指的就是主键
innodb只有两个文件，直接将主键存放在了idb表中
myisam三个文件，单独将索引存在一个文件
2）辅助索引(unique，index)
查询数据时不可能一直使用到主键，也有可能会用到其它字段，这个时候就可以根据情况给其它字段设置辅助索引
叶子节点存放的是数据对应的主键值，先按照辅助索引拿到数据的主键值，之后还是需要去主键的聚集索引里面查询数据。
