javaweb第八天 mySQL进阶

一 mySQl 中的普通约束

一，主键约束
关键字：主键约束 primary key
特点：①唯一不重复。②不能为null。③每一张表里只能存在一个主键约束。④可以设置联合主键(用两个字段来确定这个主键)

设置主键约束的三种方法
1，在字段后

CREATE   TABLE  p1(
   pid  INT(4) PRIMARY KEY,
   pname  VARCHAR(20));

2，在表格结尾

CREATE   TABLE  p1(
   pid  INT(4) ,
   pname  VARCHAR(20)，
   PRIMARY KEY(pid));

3，创建表格后添加

 ALTER  TABLE  p3 ADD CONSTRAINT   pk_p3    PRIMARY  KEY(pid)

删除主键约束的方法（因为主键约束只有一个直接删除即可）

ALTER  TABLE  p1 DROP  PRIMARY key 

二，主键自增约束
关键字：AUTO_increment 。
特点：①在插入的时候自增长不需要进行插入(i++，也就是每次自增长1)。②一般是针对整型。

三，非空约束
关键字：NOT NULL
特点：加上约束的字段不能为空，但可以重复

四，唯一约束
关键字：UNIQUE
特点：①不能重复。②在表里面可以有多个。③被约束的字段可以为NULL

创建唯一约束的三种写法
1，在字段后

CREATE   TABLE  p1(
   pid  INT(4) UNIQUE,
   pname  VARCHAR(20));

2，在表格结尾

CREATE   TABLE  p1(
   pid  INT(4) ,
   pname  VARCHAR(20)，
   UNIQUE(pid));

3，创建表格后添加

 ALTER  TABLE  p3 ADD CONSTRAINT   u_p3   UNIQUE(pid)

删除唯一约束的方法

ALTER  TABLE  p1 DROP  index pid 

二 mySQl 中的外键约束

一，外键约束的简介
表与表之间存在联系，这种关系我们就称作外键关系
关键字：foreign key

注意点：
1.设置到两张表 ,外键是设置在多的一方
2.主表的主键对应从表的外键
3.主键的大小以及长度必须与外键的相同
4.增加数据的时候，必须先增加主表里的数据 ,删除的时候，需要先删除从表，再删除主表的信息(保证数据的完整性)

设置外键的语法：

CREATE TABLE p1 (
	pid INT(4) PRIMARY KEY,
	pname VARCHAR(20)
);

CREATE TABLE n1 (
	nid INT(4) PRIMARY KEY,
	nname VARCHAR(20),
	pid INT(4)		
);

ALTER TABLE n1 ADD CONSTRAINT fk_pid FOREIGN KEY(pid) REFERENCES p1(pid);

三 表格的查询

设置表要遵循三大范式：

第一大 :每一个列都是一个原子单位: (不能进行再拆分)
第二大: 每一列都是描述的一种事务，每一个字段都只能与主键产生依赖关系
第三大:不能出现传递的依赖（局部依赖） a–>b–>c（错误） 学生编号 <=学生所有院校 <= 学生所在 院校的

一，内连接查询
select * from 表一 inner join 表二 on 条件 = 条件；

select s.studentNo,s.studentName, r.subjectNo,r.studentResult   from result  r   
inner  join student  s   on  s.studentNo=r.studentNo

二，左外连接查询
以左边为大，按照左边的匹配规则，一旦右侧没有数据则显示null
select * from 表一 left join 表二 on 条件 = 条件；

select s.studentNo,s.studentName, r.subjectNo,r.studentResult   from result  r   
left  join student  s   on  s.studentNo=r.studentNo

三，右外连接查询
以右边为大，按照右边的匹配规则，一旦左侧没有数据则显示null
select * from 表一 right join 表二 on 条件 = 条件；

select s.studentNo,s.studentName, r.subjectNo,r.studentResult   from result  r   
right  join student  s   on  s.studentNo=r.studentNo

四，子查询方式

SELECT s.StudentName FROM student s WHERE s.StudentNo
IN(SELECT r.StudentNo FROM result r WHERE r.StudentResult = 60 AND r.SubjectNo IN
(SELECT sub.SubjectNo FROM `subject` sub WHERE sub.SubjectName LIKE "jAVA%"))

四 练习

表格内容

# 查询《数据库结构-1》的所有考试结果，并按成绩由高到低排列
SELECT s.StudentNo,s.StudentName,r.StudentResult,sub.SubjectName FROM student s INNER JOIN result r ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub ON sub.SubjectNo = r.SubjectNo WHERE sub.SubjectName="数据库结构-1" 
ORDER BY r.StudentResult DESC

#查询所有课程《数据库结构-2》的考试成绩，并按照由高到低的显示，同时把该成绩对应的学生的学号、姓名打印出来
SELECT sub.SubjectName,r.StudentResult,s.StudentName,s.StudentNo FROM student s INNER JOIN result r ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub ON sub.SubjectNo = r.SubjectNo WHERE sub.SubjectName="数据库结构-2"
ORDER BY r.StudentResult DESC 

#查询《C语言-1》的前5名学生成绩信息
SELECT sub.SubjectName,r.StudentResult,s.studentName,s.StudentNo FROM student s INNER JOIN result r ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub ON sub.SubjectNo = r.SubjectNo WHERE sub.SubjectName="C语言-1" LIMIT 0,5

#按照不同的课程，分别算出其平均分、最高分和最低分，对于低于60分平均分的不予显示
SELECT sub.SubjectName,AVG(r.StudentResult),MAX(r.StudentResult),MIN(r.StudentResult)  FROM student s INNER JOIN result r ON s.StudentNo = r.StudentNo 
INNER JOIN `subject` sub ON sub.SubjectNo = r.SubjectNo GROUP BY sub.SubjectName 
HAVING AVG(r.StudentResult)>60

/*查询密码长度不足6位的学员信息(学员编号、姓名、密码、年级名称)*/
SELECT s.StudentNo,s.StudentName,s.LoginPwd,s.GradeId FROM student s WHERE LENGTH(s.LoginPwd) < 6

/*查找郑环环所有考试的成绩（科目名称、考试成绩）*/
SELECT s.StudentName,sub.SubjectName,r.StudentResult FROM student s INNER JOIN result r ON r.StudentNo = s.StudentNo
INNER JOIN `subject` sub ON sub.SubjectNo = r.SubjectNo WHERE s.StudentName = "郑环环"

/*使用表连接方式查询所有U1年级学员学号、姓名及年级名称*/
SELECT s.GradeId,s.StudentNo,s.StudentName FROM student s WHERE s.GradeId= 1

/*使用表连接方式查询“Java”课程至少一次考试刚好等于60分的学生姓名*/
SELECT s.StudentNo,s.StudentName,r.StudentResult,sub.SubjectName FROM student s INNER JOIN result r ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub ON sub.SubjectNo = r.SubjectNo WHERE sub.SubjectName LIKE "JAVA%" AND r.StudentResult = 60

/*使用子查询方式查询“Java”课程至少一次考试刚好等于60分的学生*/
SELECT s.StudentName FROM student s WHERE s.StudentNo
IN(SELECT r.StudentNo FROM result r WHERE r.StudentResult = 60 AND r.SubjectNo IN
(SELECT sub.SubjectNo FROM `subject` sub WHERE sub.SubjectName LIKE "jAVA%"))

/*使用子查询方式+表连接方式混合查询“Java”课程至少一次考试刚好等于60分的学生*/
SELECT s.StudentName FROM student s INNER JOIN result r ON 
r.StudentNo = s.StudentNo WHERE r.SubjectNo
IN(SELECT sub.SubjectNo FROM `subject` sub WHERE sub.SubjectName LIKE "JAVA%" AND r.StudentResult = 60)