一.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)

查询数据时不可能一直使用到主键，也有可能会用到其它字段，这个时候就可以根据情况给其它字段设置辅助索引

叶子节点存放的是数据对应的主键值，先按照辅助索引拿到数据的主键值，之后还是需要去主键的聚集索引里面查询数据。