8.4MySQL串讲

表的约束

• 主键约束
  Primary key
  用于约束表中的一行，作为这一行的标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。主键要求这一行的数据不能有重复且不能为空。
  还有一种特殊的主键——复合主键。主键不仅可以是表中的一列，也可以由表中的两列或多列来共同标识
• 非空约束
  Not null
  听名字就能理解，被非空约束的列，在插入值时必须非空。
  在MySQL中违反非空约束，不会报错，只会有警告
• 唯一约束
  unique
  它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。
  当INSERT语句新插入的数据和已有数据重复的时候，如果有UNIQUE约束，则INSERT失败.
• 默认约束
  Default
  规定，当有DEFAULT约束的列，插入数据为空时该怎么办。
  DEFAULT约束只会在使用INSERT语句时体现出来，INSERT语句中，如果被DEFAULT约束的位置没有值，那么这个位置将会被DEFAULT的值填充
• 外键约束
  Foreign key
  一个表可以有多个外键，每个外键必须REFERENCES(参考)另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值。
  在INSERT时，如果被外键约束的值在参考列中没有对应，则INSERT失败
  空、RESTRICT、NO ACTION（立即检查外键约束） 删除：从表记录不存在时，主表才可以删除。删除从表，主表不变 更新：从表记录不存在时，主表才可以更新。更新从表，主表不变
  CASCADE 删除：删除主表时自动删除从表。删除从表，主表不变 更新：更新主表时自动更新从表。更新从表，主表不变
  SET NULL 删除：删除主表时自动更新从表值为NULL。删除从表，主表不变 更新：更新主表时自动更新从表值为NULL。更新从表，主表不变

主键和外建

关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键
比如
学生表(学号，姓名，性别，班级)
其中每个学生的学号是唯一的，学号就是一个主键
课程表(课程编号,课程名,学分)
其中课程编号是唯一的,课程编号就是一个主键
成绩表(学号,课程号,成绩)
成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以 学号和课程号的属性组是一个主键

成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键

同理 成绩表中的课程号是课程表的外键

表的字段值自动增加

可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。
如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并1为基数递增。
把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。
当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。
当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况，情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。
如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。

多表查询

• 需求分析：现有一个参数需要根据这个参数（这个参数可能只有一个表才有）查询两个表的信息，但是你又不能查询两次数据库，此时就可以使用连表查询。

多表演是过程

新建两个表
表一

image.png

表二

image.png

一、外连接
1、左连接
SQL语句：select * from student left join course on student.ID=course.ID
执行结果

image.png

注：左外连接包含left join左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL).�注：此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。
2、右连接
SOL语句：select * from student right join course on student.ID=course.ID
执行结果：

image.png

3、完全外连接
SQL语句：select * from student full join course on student.ID=course.ID

image.png
mysql不支持外连接，
selectfrom student s left join course c s.id=c.id union selectfrom student s night join course c on s.id=c.id
二、内连接
SQL语句：select * from student inner join course on student.ID=course.ID
执行结果 image.png

inner join 是比较运算符，只返回符合条件的行。�此时相当于：select * from student,course where student.ID=course.ID
三、交叉连接 cross join
1.概念：没有 WHERE 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
SQL语句：select * from student cross join course
执行结果

image.png

如果我们在此时给这条SQL加上WHERE子句的时候比如SQL:select * from student cross join course where student.ID=course.ID 此时将返回符合条件的结果集，结果和inner join所示执行结果一样

当两表为多对多的时候我们该如何建表以及些SQL语句

新建三表

image.png
image.png image.png

一个学生可以选择多门课程，一门课程可以被多个学生选择，因此学生表student和课程表course之间是多对多的关系。
当两表为多对多关系的时候，我们需要建立一个中间表student_course，中间表至少要有两表的主键，当然还可以有别的内容。
SQL语句：select s.Name,C.Cname from student_course as sc left join student as s on s.Sno=sc.Sno left join course as c on c.Cno=sc.Cno
执行结果

image.png

习题练习

image.png

为student表和score表增加记录
向student表插入记录的INSERT语句如下：
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

1.查询student表的所有记录

image.png

2.查询student表的第2条到4条记录

image.png

3.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

image.png

4.student表中查询计算机系和英语系的学生的信息

image.png

5.从student表中查询年龄27~30岁的学生信息

image.png

6.从student表中查询每个院系有多少人

image.png

7.从score表中查询每个科目的最高分

image.png

8.查询李四的考试科目（c_name）和考试成绩（grade）
SOL语句：select name,c_name,grade from student3,score where student3.name='李四' and score.stu_id=student3.id
执行结果：

image.png image.png

9.用连接的方式查询所有学生的信息和考试信息

image.png

10.计算每个学生的总成绩
SQL语句：select name,SUM(grade) from student3,score where score.stu_id=student3.id group by student3.id
执行结果：

image.png

11.计算每个考试科目的平均成绩

image.png

12.查询计算机成绩低于95的学生信息
SQL语句：
select*from student3 where id in (select stu_id from score where c_name='计算机' and grade<95)
执行结果

image.png

13.将计算机考试成绩按从高到低进行排序

image.png

14.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
SQL语句
select student3.Id ,name,sex,dpartment,c_name,grade from student3,score where (Namelike '张%'or name like '王%') AND student3.ID=score.Stu_id
执行结果

image.png