勤奋学习的第十三天（2020.7.30）

1.MySQL中的DML

DML：数据库管理语言

1.添加数据：insert

1.指定具体列时添加数据：

insert  into  表名(列名,列名...)  value( , , ...)

这种情况会向表中具体列中添加一条数据，数据内容在value中

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

也可以添加多条数据：

insert  into  表名(列名,列名...)  value( , , ...)，( , , ...)

在添加数据时，自动递增列可以不指定数据，自动递增列不会回撤，不会补齐（只会从该列最大值向上递增），在表staff中，id是自动递增的，不指定就会自动+1

insert into staff(code,name,salary)values('1003','王五',13000),('1004','饿狼',15000)

在添加数据时，not null 列必须要指定数据，非not null的列可以不填入数据

insert into staff(code,name)value('1005','龙卷')

2.不指定具体列时添加数据：

不指定具体添加数据到哪一列时，在value中需要将每一列的数据依次填入

-- 可以不指定列，但是需要将全部列指定数据
insert into staff value(6,'1006','埼玉',20000)

2.删除数据：delete

-- 删除语句delete  可以用where限定要删除列的条件
delete from staff where id=1001;

运行delete代码，会删除符合条件的一整行数据（或多行数据），并不是只删除一项数据

两种删除表中全部数据的方式：

delete from staff;
truncate staff; 

这两种方式会删除表中全部的数据（在数据量大时，truncate的删除效率更高）

3.修改数据/编辑数据：update

update 表名 set 属性=新值 （where ...）

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

同时修改多个数据的值，中间要用逗号“，”隔开

update staff set salary=salary-2000,name='白居易' where id=2

2.DQL 数据库查询语言

1.查询   select

简单的select查询：

select 1;
select now();

2.查询表格

指定列名查询：

从staff表中查询name，salary两列数据

select name,salary from staff

查询全部列：

从staff表中查询全部列（这里星号*代表查询全部列）

select * from staff

3.可以使用as指定别名：

可以使用as指定别名：列，方法，结果，表，视图

select name as `姓名` from staff
select name as a from staff

4.可以使用where限定查询范围

SELECT * FROM staff where id=5

-- 查询id不等于1有以下两种情况：
SELECT*FROM staff WHERE id!=1
SELECT*FROM staff WHERE id<>1
-- 大于小于
select*FROM staff WHERE id>3
SELECT*FROM staff WHERE id>=3

5.对null值的判断

SELECT*FROM staff where salary=null   这种写法是查询不出来的

判断列中的值是否null值    is 

判断不是null值                  is  not

-- SELECT*FROM staff where salary=null   这种写法是查询不出来的
-- 判断列中的值是否null值    is 
SELECT*FROM staff WHERE salary is null;
SELECT*FROM staff WHERE salary <=>null
-- 判断不是null值    is not
SELECT*FROM staff WHERE salary is not null;

6.多条件判断

1.判断id小于5 并且 工资大于等于12000的，此时用and连接两个条件，意为两个条件必须都要达成才能搜索出来

2.查询出工资大于10000或者工资小于13000的，用or连接，意为两个满足一项就可以搜索出来

-- 多条件
SELECT*FROM staff WHERE id<5 and salary>=12000
SELECT*FROM staff WHERE salary>10000 or salary<13000

7. in ，not in

如果用第一句写SQL代码的话太过于繁琐，使用in关键字就可以简化代码，not in也是相同的原理

-- in ,not in
SELECT*FROM staff WHERE id=1 or id=3 or id=5
SELECT*FROM staff WHERE id in(1,3,5)

8.范围性判断

between...and...，可以查询出在这个范围内的数字数据

注意：between 12000 and 14000  这段代码判断的范围，是可以包括12000和14000的

-- 范围性判断(只能做数字性判断)  between...and...
SELECT*FROM staff WHERE salary between 12000 and 14000

9.exists

存在的话就执行前面的查询，不存在就不执行

如果exists（）括号中的语句有返回值，那么就执行前面的select

SELECT*FROM staff WHERE EXISTS(select*from staff where id=1)

 

10.any ，all

any：任意

all：全部

SELECT*FROM staff WHERE salary > any(select 10000,15000)
SELECT*FROM staff WHERE salary > all(select salary FROM staff WHERE id=1)

11.排序    order  by

order by(默认从小到大排序，即asc)

正序asc（从小到大）             倒序desc（从大到小）

-- 排序  order by(默认从小到大asc)
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 ,code desc
#SELECT*FROM staff order by salary asc,code desc;这种情况并不会salary与code都是倒序
#因为salary并没有指定排序方式，会默认为正序asc

 12.去重（chong）

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

-- 去重 distinct  对整个查询结果去重
SELECT DISTINCT salary FROM staff 

3.模糊查询

模糊查询：即不给定指定的查询范围，只通过几个字符以及前后的范围进行查询

关键字like

%意为无限个字符

_意为1个字符

select*FROM staff WHERE name LIKE '%白%'
SELECT*FROM staff WHERE name LIKE '饿%'
SELECT*FROM staff WHERE name LIKE '饿_'

4.联合查询     union

联合查询可以理解为：拼接查询结果 / 联合查询结果

联合查询的关键字：union，使用union关键字连接起多个查询

1.联合查询时，两个查询的列数必须相同，但是查询的列的类型可以不同

-- union连接的两个查询，查询的列数必须相同，类型可以不同
SELECT name,salary FROM staff UNION 
SELECT code,name FROM staff

2.union会将重复的结果去除

SELECT name,salary FROM staff UNION SELECT name,salary FROM staff 

此时查询出来的数据的name值和salary值不会有重复

注意：union all 没有去重复效果

-- union all 没有去重复效果
SELECT name,salary FROM staff UNION ALL
SELECT name,salary FROM staff 

此时查询出来的数据的name值和salary值会有重复的值

5.部分查询    limit

1.limit

关键字limit+数字：此时的数字是查询的个数

关键字limit+数字1+数字2：此时的数字1是下标，数字2是查询的个数

-- 查询工资的前三名
SELECT * FROM staff order by salary desc limit 3;
-- 查询工资第三名到第五名  limit start,count(下标为2，查询3个数据)
SELECT*FROM staff ORDER BY salary desc LIMIT 2,3

2.case when then 

case  when  then 是写在select和from中间的，意为新创一个列case，在查询到工资小于14000时会在case中输出‘薪资较低’。

有点类似JavaSe中的switch...case..

SELECT  *,case  when  salary<14000  then '薪资较低' 
when  salary>=12000  and  salary<=20000  then  '薪资正常'
ELSE  '薪资较高'
end
FROM staff ;

SELECT *,case salary when 12000 then '还行'
when 15000 then '中游'
when 18000 then '挺叼'
when 20000 then '哇哦'
when 26000 then '太厉害了'
end as level
from staff

6.分组查询

1.聚合函数

 聚合函数(将多个数据聚合成一个数据的函数)

-- 最大值
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

2.分组查询    group by

对department列的不同值进行分组

在select和from中放分组的列，以及聚合函数，中间用逗号“，”隔开

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

3.分组筛选     having

分组查询中也可以进行筛选
having,是对分组之后的数据再筛选
having后面可以跟聚合函数，where中不能用聚合函数（先执行where再执行having）

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

 4.聚合函数中的null值

聚合函数中的null值
计算时会排除null值，并不会将其当作0运算。count计算数量时会考虑null的情况

SELECT department,avg(salary),count(name) FROM staff group by departmen

7.连接查询 

1.连接查询

连接查询：外连接、内连接
1.外连接：左外连接left join(左表为主表)
              右外连接right join(右表为主表)
              全外连接full join(MySQL不支持全外连接) 

左连接以左表为主表,会显示所有数据,右表为副表,只会显示和主表有对应关系的数据
右连接相反

2.内连接 inner join 也可以写作join
内连接只显示有对应关系的数据

2.新创建了四个表

创建了四个表分别为：student表（学生信息），teacher表（教师信息），course表（课程信息表），sc表（学生成绩表）

 

 

3. 数据是连接的

在teacher表中有tid

在course表中也有tid，可以使用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;

SELECT a.tname,b.cname FROM teacher as a 
right join
course as b on a.tid=b.tid;

 

4.交叉连接   cross join

-- 交叉连接 cross join
SELECT*FROM teacher a,course b where a.tid=b.tid; 

 8.子查询

1.子查询：在一个查询中还有一个查询

SELECT sname FROM student where sid in(
SELECT sid FROM sc where score<60
)

2.可以将子查询当作一个表，再次进行查询

将子查询当作一个表来处理

SELECT sname FROM (SELECT*FROM student where sid=01)as 大香蕉