MySQL学习笔记2

DML 数据管理语言  增删改

新增 insert

#指定列添加数据

insert into staff(id,code,name,salary) VALUE(2,'1002','李四',9000);

#自动递增列可以不指定数据

insert into staff(code,name,salary) values('1003','王五',10000),('1004','赵六',11000);

#自动递增列不会回撤，不会补齐。 从该列最大值递增

#notnull列必须要指定数据

insert into staff(code,name) value('1008','杜甫');

#可以不指定列，但是需要将全部列指定数据

insert into staff value(6,'1009','王维','3000');

删除数据delete  

delete from staff where id=1001;

修改/编辑 UPDATE

UPDATE staff set name='李清照' where id=4;

update staff set salary=salary+2000 where name ='李清照';

UPDATE staff set name='郑板桥',salary=salary-2000 where id = 5;

清空表 truncate

DELETE from staff;#删除每一行数据

truncate staff;#清空表  性能更快一点

查询  MQL

select 1;#查询1

SELECT new();#查询当前时间

#查询表格

#指定列名查询

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;#不等于1

SELECT * from staff where id <> 1;#不等于1

#大于小于

select * from staff where id>20;

select * from staff where id>=20;

select * from staff where id<20;

select * from staff where id<=20;

#对null值的判断

select * from staff where salary is null;#null不能用=

select * from staff where salary is not null;

#等于null

select * from staff where salary <=> null;

#多条件

select * from staff where id<100 and salary>10000;

select * from staff where id<100 or salary>10000;

#in     /     not in

select * from staff where id=1 or id=3 or id=5;

select * from staff where id in(1,3,5);

# between   and 只能做数值性的判断

select * from staff where salary BETWEEN 5000 and 6000;

#like 模糊查询   %任意个数任意字符  _有且只有一个字符

select * from staff where name like '%李%';

#查询姓张的人

select * from staff where name like '张%';

#是否存在 exists

select * from staff where exists(select *from staff where 1=2);

#any大于任意一个 ALL大于全部

select * from staff where salary>any(select 25);

select * from staff where salary>all(select 25);

#排序 order by

#默认为正序

select * from staff order by salary;

#正序asc  倒叙desc

select * from staff order by salary asc;

select * from staff ORDER BY salary desc;

select * from staff order by salary asc,code desc;

#拼接（合并）查询结果 UNION

select name,salary from staff union select code,name from staff;

#union 会有一个去重的效果会将重复的结果去除

select name,salary from staff union select name,name from staff;

#union all 不会去重

select name,salary from staff union all select name,name from staff;

#去重 distinct 对整个查询结果去重

select distinct salary,name from staff;

#部分查询 limit

select * from staff order by salary desc limit 3;

#查询第三名到第5名 limit start,count;

select * from staff order by salary desc limit 2,3;

#case when then

select * ,case when salary <10000 then '薪资较低'when salary>=10000 and salary<20000 then '薪资正常'else '薪资较高' end  from staff;



select * ,case salary when 12000 then '还行' WHEN 15000 then '还可以' WHEN 18000 then '挺高' when 800000 then '不是人' end from staff;

#分组  聚合函数 只有五个 将多个数据构造成一个的函数

#最大值  max

select max(salary) from staff;

#最小值  min

select min(salary) from staff;

#计算平均数 avg 会排除null

select avg(salary) from staff;

#求和 sun()

select sum(salary) from staff;

#求个数  count

select count(salary) from staff;

#分组查询

select department,avg(salary) as 平均薪资,sum(salary) as 薪资总和 from staff group by department;

#分组当中进行筛选

#having 对分组之后的数据再筛选

select department from staff group by department having avg(salary)<40000;

#NULL经常出笔试题  avg 会排除null count会排除null，count(*)会计算null值

#连接查询

#老师的姓名以及教授的课程

#分别在teacher Tname   course cname

#teacher.tid = course.tid

select tname,cname from teacher left join course on teacher.tid = course.tid;

#as 起别名

select a.tname,b.cname from teacher as a left join course as b on a.TId=b.tid;

#链接查询

#外连接  左外连接 left join

#        右外连接 right join

#        全外连接 full join

#内连接  inner join 也可以写为join

#只显示有对应关系的数据

#左连接以左表为主表，会显示所有的数据，右表为附表，只会显示和主表有关系的数据，右连接相反。

#交叉链接 Cross join 很少使用

select * from teacher a,course b where a.TId=b.TId;

#子查询 在一个查询里面又有一个查询

select sname from student where sid in (

select sid from sc where score<60;

)

#将子查询当作表进行查询

select sname from (SELECT * from student where sid =01)as i;