Mysql学习-day14

Mysql学习-day14

1. 对数据的增删改查

DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）

1.1 指定列增加数据 insert

语法

INSERT INTO table_name (field1, field2,...fieldN) VALUES (value1, value2,...valueN);

向以上的表中添加数据

-- 指定列添加数据
insert into staff(id, code, name, salary) VALUE
(2,'1002','李四',9000);
-- 自动递增列可以不指定数据
insert into staff(code, name, salary) VALUES
('1003', '钱五', 10000),
('1004', '赵六', 5000);
-- 自动递增不会回撤，不会补齐  从该列最大值递增
-- not null列必须要指定数据
insert into staff(code) value
('1007');
-- 可以不指定列，但是需要将全部列指定数据
insert into staff value
(13,'1009', '王维','6000'),
(14,'1010', '王维','6000');

1.2 删除语句 delete

DELETE FROM table_name [WHERE Clause]

-- 删除语句 delete
delete from staff where id = 14;

清空表

-- 清空表
delete from staff; -- 删除掉每一行数据
TRUNCATE staff; -- 清空表，数据量大时性能更好

1.3 修改语句 update

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

-- 修改/编辑 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;

1.4 查询语句 select

select * from 表名 where 筛选条件 group by 分组字段1, 分组字段2 having 分组筛选条件 order by 排序字段1, 排序字段2 limit 起点, 数量

select可以直接查询数字，返回就是这个数字

select 1;

可以查询now()方法来查看当前时间

select now();

查询表格

-- 指定列名查询
select name,salary from staff;
select * from staff;


select * from staff where id = 1;
-- 不等于
select * from staff where id <> 1; -- 不等于也可以用!=
-- 使用as 指定别名 列 方法结果 表 视图
-- 大于小于
select name as '姓名' from staff where salary > 8000;
select name from staff where salary <= 7000;

对null值的判断，在mysql中判断null值不能用=，而是用is或<=>

-- 对null值的判断
#select * from staff where salary = null; 不能用=判断null,可以用<=>
select * from staff where salary <=> null;
select * from staff where salary is null;
select * from staff where salary is not null;

where配合and，or定义多重查询条件

in: 判断属性是否存在

-- 多条件
select name from staff where id < 100 and salary < 10000;
select * from staff where salary <= 7000 or salary > 8000;
-- in    not in
select * from staff where id = 1 or id = 3 or id = 5 or id = 7;
select * from staff where id in (1,3,5,7,9,11);
select * from staff where id not in (1,3,5,7);

between…and…定义取值范围

-- between and
select * from staff where salary between 7000 and 10000;

模糊查询 like %表示任意个数的任意字符 _表示一个字符

-- 模糊查询 like  %(百分号)任意个数任意字符   _(下划线)有且只有一个字符
select * from staff where name like '%张%';
select * from staff where name like '张%';
select * from staff where name like '李_';

是否存在 exists not exists
如果exist中的语句有结果，就执行前面的查询语句，相当于🔒

SELECT * FROM 表名 WHERE EXISTS (SELECT id FROM my WHERE id = 1111)

-- exist中的语句有返回结果，执行前面的查询语句
select * from staff where exists (select * from staff where id = 1);
-- exist中的语句没有返回结果，不执行前面的查询语句
select * from staff where exists (select * from staff where id = 9999);

any all

-- any all
select * from staff where salary > any(select salary from staff where salary < 7000);

select * from staff where salary > all(select salary from staff where salary > 7000 and salary < 10000);

排序 order by

正序 asc(默认) 逆序 desc

-- 排序 order by
select * from staff order by salary;
-- 正序 asc 倒序 desc
select * from staff order by salary asc, code asc;
select * from staff order by salary desc;
-- 默认为asc
select * from staff order by salary, code desc;

拼接(合并)查询结果 union(去重) union all(不去重)

两个查询语句的列的数量必须要相同，数据类型可以不同

-- 两个查询语句的列的数量要相同
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

单个参数时表示查询的个数

两个参数表示从下标位置查询的个数，下标起始是0

-- 部分查询 limit
select * from staff order by salary desc limit 3;
-- 查询第二名到第四名 limit start, count;
select * from staff order by salary desc limit 1,3;

case表达式

使用CASE表达式可以帮助我们解决复杂的查询问题，相当于条件判断的函数，判断每一行是不是满足条件。

CASE WHEN (判断表达式) THEN (表达式)
     WHEN (判断表达式) THEN (表达式)
     WHEN (判断表达式) THEN (表达式)
     ....
     ELSE(表达式)
END;

select *,
case 
when salary < 7000 then '低工资'
when salary >= 7000 and salary < 9000  then '正常薪资'
else '高薪资'
end as '薪资评价'
from staff;


-- 直接匹配
select *, case salary when 6000 then '还行'
when 8000 then '还可以'
when 9000 then '挺高'
when 10000 then 'wow!'
end as '薪资评价'
from staff;

聚合函数

sum求和，avg求平均值，max求最大值，min求最小值

count(*)返回表的总行数，count(column)返回列的总行数 count(1)和count(*)的作用是相同的

-- 分组 group by 聚合函数 将多个数据聚合的函数
-- 最大值
select max(salary) from staff;
-- 最小值
select min(salary) from staff;
-- 平均数
select avg(salary) from staff;
-- 求和
select sum(salary) from staff;
-- 计数
select count(id) from staff;

分组查询 group by

根据一个或多个列对结果集进行分组，在分组的列上我们通常配合聚合函数一起使用

select department from staff group by department;
select * from 
(select department, avg(salary) as 平均薪资 from staff group by department)
as a
where 平均薪资 < 8000;

分组筛选

WHERE 子句只能指定行的条件，而不能指定组的条件,WHERE 先过滤出行，然后 GROUP BY 对行进行分组，HAVING 再对组进行过滤，筛选出我们需要的组

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

2. 连接查询

创建四个表，分别为student，teacher，course，sc

student表

teacher表

course表

sc表

如果我们想要查询老师教学的课程，因为课程名和老师名分别在course表和teacher表上，所以我们需要将这两个表连接起来

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

连接查询分为外连接和内连接
外连接分为左外连接(left join)，右外连接(right join)。全外连接(full join)
左连接以左表为主表，会显示所有的数据，右表为辅表，只会显示和主表有关的数据
右连接与左连接相反
mysql不支持全外连接

2.1 外连接

在表course中添加一行数据，其tid为null

左连接 left join

以左表为主表(teacher)，会显示主表的所有数据，右表为辅表(course)，只显示与主表有关的数据

select t.Tname, c.Cname
from teacher t left join course c
on t.tid = c.tid

右连接 right join

右连接以右表为主表，左表为辅表

select t.Tname, c.Cname
from teacher t right join course c
on t.tid = c.tid

2.2 交叉连接

交叉连接会返回两个表中的每一行数据任意组合的结果

select * from teacher ,course;

2.3 内连接

内连接 inner join也可以写为join

只显示两个表中有对应关系的数据

select *
from teacher as t inner join course as c
on t.tid = c.tid;

2.4 子查询

当一个查询是另一个查询的条件时，称之为子查询。

-- 子查询
select sname from student where sid in(select sid from sc where score < 60);
-- 将子查询当作一个表来处理
-- 子查询会占用运行内存，减少使用
select sname from (select * from student where sid = 01) as b;