数据库的增删改查，多表联查

最新推荐文章于 2025-04-11 16:56:44 发布

la go go

最新推荐文章于 2025-04-11 16:56:44 发布

阅读量3.1k

点赞数 1

文章标签： sql 数据库 servlet

本文链接：https://blog.youkuaiyun.com/qq_52025132/article/details/126826337

版权

一、新增insert
- 语法
1. insert into 表名（字段名 .... 字段名） values/value(值...值)；【推荐使用】
  1. insert into 表名 values/value(值...值)；
  2. insert into 表名(字段名) values（值）；【不完全插入】
  3. insert into 表名 values/value(值...值),(值...值);【一次插入多条数据】
- 新增的注意事项：
  1. 字符 ‘’“”“你’我”
  2. 日期使用字符串的日期类型（格式）的形式进行书写。【必须写成“2000-02-02”而不是2000-02-02或者2000-2-2（这个相当于数学运算结果为1996，就不是日期了）】。
  3. values【添加多条数据】value【添加一条数据】。
  4. 全字段插入【值必须和字段名前后一一对应，不能少写】
  5. 不完全插入【主键没有自增需要插入数据，如果有非空约束（没有默认值）需要插入数据】
二、两种不推荐使用插入方式

create table student2(

xingmiang VARCHAR(10),

sex VARCHAR(10)

)
- 方式一：先创建插入表，再查询查询表。

insert into student2 select sname ,Ssex from students;

- 方式二:边创建插入表，边查询查询表。

create table student3 select sname,Ssex from students;

三、修改update
- 语法：
  1. update 表名 set 字段名=值，字段名=值；【一般不使用】
  2. update 表名 set 字段名=值，字段名=值 where 子句【条件】；【带条件的修改】
  3. update 表名 set 字段名=值，字段名=值 where 条件子句1 and 条件子句2；【多条件的修改：and和or连接条件子句】

- 修改的注意事项：
  1. 若条件子句是在一个范围则可使用
四、删除delete：【delete不能清除主键自增的序号，索引不能清除】
- 语法：
  1. delete from 表名；【删除整表，一般不使用，删除的是整表的数据，表依然存在】【delete from student4;】
  2. delete from 表名 where 条件子句；【带条件的删除】【delete from student4 where sname="赵雷";】
  3. delete from 表名 where 条件子句 and 条件子句；多条件的删除【and和or连接条件子句】【delete from student4 where sname="小尚" and classid=3;】

五、清空表【将整张表的数据和索引（主键的自增）全部清空，保留表结构】

truncate 表名;
- 语法：
六、delete ，truncate ，drop的区别：【面试题****】
- delete只删除的是数据。属于DMl
- truncate只保留表结构，删除数据和索引包括主键的自增。属于DML
- drop删除的是整个表结构包括数据，索引，主键的自增。属于DDL
七、计算列：MySQL8的新特性
- 语法
  - 字段名 cenerated always as （逻辑语句）virtual

select 'a';

select 123+456;

select sid,sname,birthday,Ssex,classid from student;

select * from student;【先去表中查字段的名称，替换掉 * , 然后再去查对应字段的数据。】

select sid,sname,classid from student;

select sid,sname,birthday,Ssex,classid,"咸阳师范学院" from student;

select * from student where classid=1 and ssex="女";

select * from student where sid>=3 and Sid<=7;

select *from student where sid BETWEEN 3 and 7;

select * from student where birthday < "1990-01-01";

select sname as 'name',sname '学生姓名',sname '姓名' from student;

select distinct ssex from student;

select * from student where sid in (1,5,7,9,52,25,64);

八、用Navicat备份数据库
- 保存：选中数据库->转储SQL文件->选择保存的位置保存文件
- 恢复：创建数据库->运行SQL文件
九、利用控制台备份数据库
- bin->mysqldump.exe
十、查询select【所有的查询一切皆虚拟表】
- 最简单的查询
- 对基本表的查询
- 部分字段的查询(虚拟表)
- 带条件的查询
- 范围性的条件
- - 注意日期【日期小的年龄大】
十一、起别名【三种方式】
1. 字段名 as “”更改后的字段名“
  1. 字段名 “更改后的字段名”
  2. 字段名更改后的字段名
十二、去重distinct【distinct后面的字段要完全相同才会去重】
十三、模糊查询 like

_:一个任意字符【若是两个字符就两个_ _】
- 模糊符号 %：任意多的任意字符
- 模糊分类
  
  insert into student(sname) VALUES("尚"),("小尚"),("尚同学"),("小尚同学"); 【查找所有姓名带尚字的】
  
  select * from student where sname like "%尚%";
  1. 前模糊：%关键字
  2. 后模糊：关键字%
  3. 前后都模糊：%关键字%
十四、 in 表示特定范围的内
十五、查询 null 值
1. select * from student where classid=null; #错误写法
2. select * from student where classid is null;
3. select * from student where birthday is not null;
十六、聚合函数

* 主键常量都是一样的
- count（）：统计总数【不统计null】
  1. * 【select count(*) from student;】
  2. 字段名：主键【select count(sid) from student;】
  3. 常量【select count(sid) from student;】
- -- 聚合数值
  - sum（）：总和【select sum(score) from sc;】
  - max（）：最大值【select MAX(score) from sc;】
  - min（）：最小值【select min(score) from sc;】
  - avg（）：平均值【select avg(score) from sc;】
找到男同学有多少人【select count(*) from student where Ssex="男";】
1号学生的总成绩是多少【select sum(score) from sc where Sid=1;】
2号同学的最高分是多少【select MAX(score) from sc where Sid=2;】
3号课程最差成绩是多少分【select min(score) from sc where Cid=3;】
4号学生的平均分【select avg(score) from sc where Sid=4;】
列出成绩的总次数，总成绩，最高分，最低分，平均分：【select COUNT(score) 总次数,SUM(score) 总成绩,MAX(score) 最高分,MIN(score) 最低分,avg(score) 平均分 from sc;】

十七、分组 ***** （重点，难点，不好学，必须会，要精通）
- select count(*) from student where Ssex="男";
- select count(*) from student where Ssex="女";
- select Ssex,count(*) from student group by Ssex;
- 每个班的学生人数【select classid,COUNT(*) from student GROUP BY classid;】
- 每个学生的平均分【select sid,avg(score) from sc GROUP BY sid;】
- 每个班的最高分最低分【select Cid,MAX(score), MIN(score) FROM sc GROUP BY Cid;】
- 每个学生的总成绩：总成绩大于200分。【select sid,SUM(score) from sc GROUP BY sid having SUM(score)>200 ;】
  - - [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where SUM(score)>200' at line 1
    - [Err] 1111 - Invalid use of group function
  - 注意：
    1. where后面的筛选是针对表中的每一条数据进行的
    2. having 筛选【对分组之后的数据进行筛选】
十八、having和where的区别【面试题*****】

1. 1. having对分组之后的数据进行筛选。不能单独出现，必须和group by 一起出现。group by可以单独出现。
  2. where后面的筛选是针对表中的每一条数据进行的。

-- 每个学生的单科成绩不低于60，总成绩：总成绩大于200分。

select sid,SUM(score) from sc WHERE score>60 GROUP BY sid having SUM(score)>200 ;

十九、排序 order by
- 升序
  - select * from sc order by sid ; #默认是升序
  - select * from sc order by sid asc; #asc升序
- 降序
  - select * from sc order by sid desc; #desc降序
- 后面可以跟多个字段进行排序，先写的优先排列
  - select * from sc order by score asc ,sid desc,cid asc ;

二十、 limit 分页【两种方式】

select * from student limit 0,3;  #1

select * from student limit 3,3;  #2

select * from student limit 6,3;  #3

select * from student limit (页码-1)*步长,3;  #1
- 方式一： limit 数字获取多少个为一页【相当于第二种方式的简写】
- 方式二： limit 数字(位置),数字（步长）

SELECT tid,tname,

case tsex

when 1 then "男"

when 0 then "女"

else "double"

end "性别"

from teacher ;

二十一、MySQL8的特性： select * from student limit 3 offset 1; #跳过第一个数组步长为3 。
二十二、多表联查（重点）
- 非等值联查（笛卡尔积）【select * from student,class;】
- 等值联查【内联两种，外联两种（外联主要分清主从表）】
  - 内联查询（两种查询各有优缺）
    1. 方式一：班级和学生内联查询【select * from student,class where student.classid=class.classid; #方式一：一次查询，适合表多但是表示的数据少的】
select * from student,class,sc,course,teacher where student.classid=class.classid and sc.Cid=student.classid and course.Tid=teacher.Tid and course.Cid=sc.Cid;【把五张表数据关联起来查询】

2.方式二：INNER JOIN ON 内联【select * from student INNER JOIN class on student.classid=class.classid;# 方式二，多次查询拼接，适合数据多表少的】
- 2.右外联 RIGHT JOIN ON【select * from class RIGHT JOIN student on student.classid=class.classid;】
  - 外联查询（分清楚主表和从表【主表left左 right 右】）
    
    所有的学生信息和对应的班级信息【select * from student LEFT JOIN class on student.classid=class.classid;】
    1. 左外联 LEFT JOIN ON
【select sc.Sid,sname,birthday,Ssex,classid from teacher

   INNER JOIN  course on teacher.Tid=course.Tid

    INNER JOIN  sc on course.Cid=sc.Cid

    INNER JOIN student on student.sid=sc.sid

    WHERE Tname="张三";】
- 练习1：找到张三老师课程的学生吗，显示学生信息
                                  select Sid,sname,birthday,Ssex,classid from teacher

                                  where teacher.Tid=course.Tid

                                  and course.Cid=sc.Cid

                                  and student.sid=sc.sid

                                  and Tname="张三";
select teacher.*,avg(score) from teacher

INNER JOIN course on teacher.Tid=course.Tid

INNER JOIN sc on sc.Cid=course.Cid

GROUP BY teacher.Tid

ORDER BY avg(score) ;
- 练习2：统计每个老师所带的学生的平均分，老师信息和平均成绩【新获取数据在处理数据】
select class.* FROM class LEFT JOIN student on student.classid=class.classid

WHERE student.Sid is null;
- 练习3：获取没有学生的班级信息
select student.* from student left join class on student.classid=class.classid

where class.classid is null;
- 练习4：获取没有班级的学生信息
二十三、union 查询两个集合的并集
- 数据类型不同是可以进行合并的
- 两个集合的列数要一致。
- 表头是第一个集合的信息【字段起别名必须在第一个集合】
- 去除重复
select sname 姓名,Ssex 性别,classid 其他 from student

UNION

select tname,tsex,temail from teacher;
- 练习5：获取没有班级的学生和学生的班级
select student.sname from student left join class on student.classid=class.classid

where class.classid is null

UNION

select class.classname FROM class LEFT JOIN student on student.classid=class.classid

WHERE student.Sid is null;

select * from student left join class on student.classid=class.classid

UNION

select * FROM student RIGHT JOIN class on student.classid=class.classid
select * from student left join class on student.classid=class.classid

UNION all

select * FROM student RIGHT JOIN class on student.classid=class.classid
- 获得有重复的就是 union all
二十四、子查询（难点，面试用）【所有的子查询都需要小括号括起来】

select student.* from student ORDER BY sid DESC LIMIT 0,1;

select student.* from student where sid=(select max(sid) from student );
- 练习1：查询id最大的学生（使用排序和分页实现）
查询大于五人的班级的名称和人数（不使用子查询）使用form

select * from class

LEFT JOIN (SELECT classid,count(*) 人数 from student GROUP BY classid) t on class.classid=t.classid

where 人数 > 5;

select * from teacher where EXISTS (select * from student where Ssex="男")
select * from student INNER JOIN student  on student.classid=class.classid

WHERE sid in (select max(sid) from student

GROUP BY classid);

select * from student WHERE sid in (select MAX(sid) from student GROUP BY classid)

select * from student WHERE  sid not in (

    select sid from  sc where cid=(

            select  Cid  from course where  tid=(

                    select tid from teacher where tname="张三")

    )

)

select class.*,COUNT(*) from class LEFT JOIN student on class.classid=student.classid GROUP BY class.classid HAVING COUNT(*)>5;

select classid,count(classid) from student  GROUP BY classid HAVING COUNT(classid)>5;
- 练习2：查询每个班下id最大的学生的id
- 练习3 找到没上过张三老师的课的学生
- 练习4：查询大于五人的班级的名称和人数（不使用子查询）
- 一、from子查询【一定得起别名】
- 二、exist 子查询子句有数据，父句执行，子句么有数据，父句不执行
- 练习:5：如果学生表中有男同学则查询出所有的老师
- 三、any some ,all
- 练习6：查询出一班比二班成绩高的学生的信息
  
  SELECT * from student INNER JOIN sc on student.Sid=sc.Sid WHERE classid=1 and score>(SELECT MAX(score) from student INNER JOIN sc on student.sid=sc.Sid where classid=2)【经典写法】
  
  SELECT * from student INNER JOIN sc on student.Sid=sc.Sid WHERE classid=1 and score>any (SELECT score from student INNER JOIN sc on student.sid=sc.Sid where classid=2)
  - 使用any=some
    - 使用all
SELECT * from student INNER JOIN sc on student.Sid=sc.Sid WHERE classid=1 and score>all (SELECT score from student INNER JOIN sc on student.sid=sc.Sid where classid=2)

-- expr1 条件

-- expr2 条件成立的值

-- expr3 条件不成立的值

expr1 字段

expr2 默认值
二十五、IF(expr1,expr2,expr3)
- 练习7：select Tid,Tname,IF(tsex=1,"男","女"),Tbirthday from teacher
五、IFNULL(expr1,expr2) 相当于给字段设置默认值
- 练习8：select sid,sname,IFNULL(birthday,"2022-8-27") 生日 from student
二十六、简单 CASE when then END【else可以不写】
- 练习9：teacher表中1代表男，0代表女显示表数据
二十七、搜索case

select student.sid,sname,birthday,Ssex,classid,

case

when score>90 then "A"

when score>80 then "B"

when score>70 then "C"

when score>60 then "D"

when score<60 then "不及格"

else "found"

end "成绩等级"

from sc INNER JOIN student on sc.sid=student.sid;
- 练习10：成绩用ABCD等级表示【链表查询】
  
  select Sid,Cid,
  
  case
  
  when score>=90 then "A"
  
  when score>=80 then "B"
  
  when score>=70 then "C"
  
  when score>=60 then "D"
  
  when score<60 then "不及格"
  
  else "found"
  
  end "成绩等级"
  
  from sc;
  
  select * from sc;
  - 【单表查询】