MySQL：表的增删改查（进阶）

表的增删改查（进阶）十分重要，也较有难度，需多花时间掌握。

 

一、NULL约束：

在加null约束之前，id可以插入null，在加上null约束之后，id不再可以插入null。

 

二、unique约束：

将学号设置为唯一，第一次可以插入学号1，第二次不能再插入。

 

三、default默认键约束：

default：规定没有给列赋值时的默认值。这里的默认值为null。

将名字的默认值修改为“未命名”。 

 仅插入了学号1，没有插入姓名，此时姓名显示“未命名”。

 

四、primary key主键约束：（一张表里只能有一个primary key，not null和unique的结合）

（1） 将学号设置为主键，Null不为空，Key为唯一：

 

（2）自增主键：

将学号设置为自增主键，此后的数据中学号都插入null，但并非真正意义上的空，此后学号会自增，第一次为1，第二次就自增为2。写作null是交给数据库服务器自动分配。

后续分配主键都是根据这个主键来分配的 ，这里王五的学号是10，赵六没有设置值，默认是11。 

 

五、外键：

外键用于关联其他表的主键。（父表约束子表）（class表约束student表）

student表中的classId要出自table表中的classId这一列。

张三的信息可以插入到student表中，但李四却不行，因为200并不包含在class表中的classId那一列中。 

当删除父表中被约束的信息时，一定要确保其未在子表中出现过。

这里103未在子表中出现过，所以可以删除。

101在子表中出现过，所以显示删除失败。 

 

先删子表，再删父表 ：

 

 

六、表的设计：

（1）一对一： 

一个学生只能拥有一个账号，一个账号也只能被一个学生拥有。

（2）一对多：

一个学生只能在一个班级中，一个班级中可以有多个学生。

（3）多对多：

一个学生可以选多门课，一门课也被多个学生选。

先创建一个学生表，包含学生id和姓名，然后插入三个人的数据。

再创建一个课程表，包含课程id和名称，然后插入三门课的数据。

借助一个关联表，表示多对多关系 。

（1,1）=  张三选了语文课   （1,2）=  张三选了数学课

（2,1）=  李四选了语文课   （2,3）=  李四选了英语课

 

七、新增：

将表1中的数据插入到另一个表3中： insert into 表3 select * from 表1;

两张表的数据类型和数量一定要匹配。

 

八、聚合函数：

（1）count（数量）：看看具体有几行

具体的行不会将空计算在内，但统计所有的时候会把空算进去。 

 

 去重的查询：

1号和8号的数学成绩是一样的，去重之后查询数量就少了一个。

（2）sum（总和）：

查询语文成绩的总和：525

（3）avg（平均）：

查询总分的平均分：

（4） max（最大值）：

查询英语的最高分。

（5）min（最小值）：

查询数学70分以上的最低分：

 

九、group by语句： （分组查询）

分别查询程序员、产品经理和老板的平均工资：（通过岗位role来分组）

分组之前：查询每个岗位的平均工资，但排除张三。（where语句，在group by之前）

分组之后：查询每个岗位的平均工资，但是排除平均工资超过2w的。 （having 子句一般写在group by后面）

合二为一：查询每个岗位的平均薪资，排除张三，且保留平均工资2万以下的结果：

 

十、联合查询： （多表查询，笛卡尔积：排列组合）

创建了四张表：班级表、学生表、课名表、分数表

 查询3张表的具体数据

 （1）内连接：

联合学生和成绩两张表查询，条件是学生表的id与成绩表的student_id相同

查询许仙同学的成绩： 

精简版：只查询名字和分数 

查询所有同学的总成绩和姓名（笛卡尔积+聚合函数+分组查询）：

查询所有同学的个人信息：

精简版：

（2）外连接：

新建两张表，score和student，插入数据如下：

笛卡尔积+联合查询（内连接）

 左外连接：（左侧表的每条记录都会出现在最终结果里，如果右侧表不存在，对应列就设为空）

右外连接：（右侧表的每条记录都会出现在最终结果里，如果左侧表不存在，对应列就设为空）

内连接、左外连接、右外连接的区别： 

 

 

 

（3）自连接：（自己和自己笛卡尔积，在同一张表连接自身进行查询）

显示所有“计算机原理”成绩比“java”成绩高的成绩信息：

计算机原理的课程id为3，java的课程id为1，就是要查询课程id3比1高的考试成绩。

精简版：

 

（4）子查询：（嵌入在其他sql语句中的select语句，也叫嵌套查询）

 单行子查询：返回这一行记录的子查询。

查询与“不想毕业”同学的同班同学

 

多行子查询：返回多行记录的子记录：

查询语文或英文课程的考试信息：

（5）合并查询：（合并多个select的执行结果）：

需要使用union关键字，该操作符作用于取得两个结果集的并集。使用该操作符时， 会自动去掉结果集中的重复行。

union：允许把两个不同的表，查询结果合并在一起。合并的两个sql的结果集的列需要匹配，列的个数和类型是要一致的，列名不用一致。

【1】查询课程编号小于3或者课程名为“英文”的信息：（两种实现方式，第一种用了or，第二种用了union）

 

将student1表和student2合并查询，并去除重复的数据。 

 

原文地址：https://blog.csdn.net/gdswsj/article/details/143440536

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！