7月30日MySQL学习笔记

DML(数据操纵语言)增删改

添加记录

        1.指定列添加数据，主键可以不指定，自动递增

        2.自动递增列不会回撤，不会补齐 从该列最大值递增 没有就从计数器值开始

        3.not null列必须要指定值，可为null可以先不添加

        4.可以不指定所有列，但需要将全部列指定对应数据，包括主键

-- 指定列添加数据
insert into staff(id,code,name,salary) 
value (2,'10002','lisi',9000) 

-- 自动递增列可以不指定
insert into staff(code,name,salary) values 
('10003','wangwu',10000),
('10004','zhaoliu',10001)

-- 自动递增列不会回撤，不会补齐 从该列最大值递增 没有就从计数器值开始如现在id计数到6，删除4，5还是从6开始排

-- not null列必须要指定值，可为null可以先不添加
insert into staff(code,name) 
value ('10005','dufu') 

-- 可以不指定列，但需要将全部列指定对应数据，包括主键
insert into staff value (10,'10010','wangwei',30000)

修改记录

-- 修改/编辑  update
update staff set name='liqingzhao' where id=4

update staff set salary=salary+2000 where name='liqingzhao'

update staff set name='sushi' ,salary=salary-2000 where id=5

删除记录

-- 删除语句  删除数据delete 其他是drop，比如所有结构
delete from staff where id=1001
-- 清空表 
delete from staff; -- 删除每一行数据
truncate staff; -- 清空表（数据量大的情况下快，小就不一定）

DQL(数据查询语言)

        

简单查询

个人缺陷：

1.不等于表示：!=或者<>

2.判断null值  is null     is not null     <=>null

3.in(数据1,数据2, ...)    not in(数据1,数据2, ...) 顾名思义，判断是否在()里有相同的

4.范围性判断 between  num1 and num2 包含边界

5.模糊查询  like  %(任意个数任意字符)  _(有且只有一个)

6.是否存在 exists   not exists 如果exists后成查出来结果，前面就进行查询返回结果，类似于if判断是否运行; exists前后语句没有任何关系;

7.any(sql语句) 与all(sql语句)，any要求满足语句结果的一个满足就行，all要求全部满足；

8.排序 order by   默认正序asc ，倒序desc，每一个数据后都要跟一个asc或desc，没写就是asc

9.union与union all:拼接(合并)查询结果  没必要是一个表也不一定要类型相同   但查询列的数量必须相同;union有去重效果，将相同的结果（一整行的内容）去掉 ，union all  没有去重；

10.去重 distinct  ，写在select后面，将相同的结果（一整行的内容）去掉

11.部分查询limit：(limit n)(查找n个)，(limit start count)(从start(下标)开始查count个) 

12.case when then end，end后面加as可以起名。两种使用方法：

-- 范围判断  
select * ,
case 
when salary <7000 then '薪资较低'
when salary >=7000 and salary <15000 then '薪资正常'
else '薪资较高'
end as '等级'
from staff 

-- 数值匹配，没有就空着
select *,case salary 
when 10000 then '还行'
when 15000 then '还可以'
when 18000 then '挺高'
when 20000 then '哇哦'
when 26000 then '好厉害'
end as '等级'
from staff

总汇：

-- DML  数据管理语言   增删改
-- 新增
-- 指定列添加数据
insert into staff(id,code,name,salary) 
value (2,'10002','lisi',9000) 

-- 自动递增列可以不指定
insert into staff(code,name,salary) values 
('10003','wangwu',10000),
('10004','zhaoliu',10001)

-- 自动递增列不会回撤，不会补齐 从该列最大值递增 没有就从计数器值开始

-- not null列必须要指定值，可为null可以先不添加
insert into staff(code,name) 
value ('10005','dufu') 

-- 可以不指定列，但需要将全部列指定对应数据，包括主键
insert into staff value (10,'10010','wangwei',30000)

-- 删除语句  删除数据delete 其他是drop 
delete from staff where id=1001

-- 修改/编辑  update
update staff set name='liqingzhao' where id=4

update staff set salary=salary+2000 where name='liqingzhao'

update staff set name='sushi' ,salary=salary-2000 where id=5

-- 清空表 
delete from staff; -- 删除每一行数据
truncate staff; -- 清空表（数据量大的情况下快，小就不一定）



-- 查询  MQL 数据查询语言
select 1;
select now();

-- 查询表格
-- 指定列名查询
select name,salary from staff;
-- 可以使用*代替所有的列
select * from staff;

-- 使用as指定别名 列、方法、结果、表、视图等等
select name as 姓名 from staff

-- where 指定条件语句
select * from staff where id=2

select * from staff where id<>1 -- 或者!=

-- 大于小于
select * from staff where id>=20
select * from staff where id<20


-- 对null值的判断
select * from staff where salary is null; -- 不能用=
select * from staff where salary is not null;-- 不是not is
select * from staff where salary <=> null;-- 对null值等于判断

-- 多条件 AND  OR
select * from staff where id<100 AND salary>=10000;

select * from staff where salary<5000 or salary>=10000;

-- in    not in 
select * from staff where id in (1,2,4,5);
select * from staff where id not in (1,2,4,5);


-- 范围性判断 between and  包含边界
select * from staff where salary between 5000 and 6000

-- 模糊查询  like  %(任意个数任意字符)  _(有且只有一个)
select * from staff where name like '%a%';
select * from staff where name like 'zhang%';
select * from staff where name like 'zhang_';

-- 是否存在 exists   not exists 如果exists后成查出来结果，前面就进行查询返回结果，类似于if判断是否运行
-- exists前后语句没有任何关系
select * from staff where 
exists (select * from staff where 1=2)

-- any all  两边类型一样
-- any 大于any内查询结果的任意一条
select *  from staff 
where salary > any(select salary from staff where id between 1 and 3)

-- all 大于all内查询结果的所有结果
select *  from staff 
where salary > all(select salary from staff where id between 1 and 3)



-- 排序 order by   默认正序asc  倒序desc
select * from staff ORDER BY salary desc
-- 按照salary正序，相同按code倒序排序
select * from staff order by salary, code desc;

-- 拼接(合并)查询结果  没必要是一个表也不一定要类型相同   但查询列的数量必须相同
select name,salary from staff union 
select code,name from staff

-- union有去重效果，将相同的结果（一整行的内容）去掉  
select name,salary from staff union 
select name,salary from staff

-- union all  没有去重
select name,salary from staff union all
select name,salary from staff

-- 去重 distinct  将相同的结果（一整行的内容）去掉
SELECT distinct salary,name from staff

-- 部分查询  limit   (limit n)(查找n个)   
select * from staff order by salary desc limit 3;
-- 查询第三名到第五名  (limit start count)(从start(下标)开始查count个) 
select * from staff order by salary desc limit 2,3;

-- case when then  
-- 范围判断  end后面加as可以起名
select * ,
case 
when salary <7000 then '薪资较低'
when salary >=7000 and salary <15000 then '薪资正常'
else '薪资较高'
end as '等级'
from staff 

-- 数值匹配，没有就空着
select *,case salary 
when 10000 then '还行'
when 15000 then '还可以'
when 18000 then '挺高'
when 20000 then '哇哦'
when 26000 then '好厉害'
end as '等级'
from staff

聚合函数

        将多个数据聚合成一个数据的函数   一共5种

        聚合函数的null值   笔试题
        -- null不参与运算，count不计数，avg忽略null
        -- 但count(*)或者count(常量)作用一样，都是计算所有行数，包括null计数

-- 最大值
select max(salary) from staff
-- 最小值
select min(salary) from staff
-- 平均数
select avg(salary) from staff
-- 求和
select sum(salary) from staff
-- 求个数
select count(salary) from staff

分组查询

        select只能跟分组的列和聚合函数 不可以带其他列

        执行顺序：where group having

-- 分组查询  只能跟分组的列和聚合函数 不可以带其他列
select department,avg(salary) as '平均薪资',sum(salary) as '薪资总和' 
from staff group by department;

-- 分组筛选having(对分组之后的数据进行筛选)
select department from staff GROUP BY department 
having avg(salary)<50000; 

多表查询

外连接

        主表和附表  显示主表所有数据，没有对应的会补null，附表只会显示和主表有对应关系的数据。

        -- 左外连接(left join)   左主表右附表
        -- 右外连接(right join)  右主表左附表
        -- 全外连接(full join)      默认情况下不支持 可以用左外连接union右外连接实现

内连接

        -- 内连接 inner join 也可写为join
        -- 只显示两表中有对应关系的数据，没有就不显示

交叉连接

        cross join  所有可能都连一遍 ，产生数据量太大，再用where查询性能太差，不常用。

子查询

        跟in(sql语句)当集合用；

        跟from(sql语句) as name 当作表处理，必须加as起别名，暂存在运行内存，尽量少用，影响效率。

-- 子查询
select sname from student where sid 
in (select sid from sc where score<60)
-- 当作表处理，必须加as起别名，暂存在运行内存，尽量少用，影响效率
select sname from(select * from student where sid=01) as b