MySQL数据库之二、约束与查询

#主键约束：主键约束中主键不能为空
CREATE TABLE student(
stuNo INT PRIMARY KEY,
stuName VARCHAR(20),
age INT,
address VARCHAR(20)
);
INSERT INTO student(stuNo,stuName,age,address) VALUES(1001,‘艾克’,18,‘祖安的租安人’);
INSERT INTO student(stuNo,stuName,age,address) VALUES(1001,‘蒙多’,18,‘祖安’);
报错：> 1062 - Duplicate entry ‘1001’ for key ‘PRIMARY’
INSERT INTO student(stuNo,stuName,age,address) VALUES(NULL,‘坤坤’,18,‘养鸡场’);
报错：> 1048 - Column ‘stuNo’ cannot be null
语法：
CREATE TABLE student(
stuNo INT PRIMARY KEY,
stuName VARCHAR(20)
);

#联合主键
CREATE TABLE student(
stuNo INT,
classId VARCHAR(20),
stuName VARCHAR(20),
age INT,
address VARCHAR(20),
PRIMARY KEY (stuNo,classId)
);
INSERT INTO student(stuNo,classId,stuName,age,address) VALUES(1001,‘JAVA01’,‘艾克’,18,‘养鸡场’);
INSERT INTO student(stuNo,classId,stuName,age,address) VALUES(1002,‘JAVA01’,‘蒙多’,18,‘养鸡场’);
备注：联合主键的两个主键中，可以一个主键相同，但是不能两个主键都相同。

#唯一约束：数据不能重复，可以为null
CREATE TABLE person(
pId INT PRIMARY KEY,
pName VARCHAR(20),
cardNo VARCHAR(18) UNIQUE
);
INSERT INTO person(pId,pName,cardNo)VALUES(201,‘sunny’,1412312311231231);
INSERT INTO person(pId,pName,cardNo)VALUES(202,‘sunny’,NULL);

#自动增长：自动增长不能单独使用，一般需要和主键配合。
CREATE TABLE product(
pId INT PRIMARY KEY auto_increment,
pName VARCHAR(50),
price DECIMAL(8,2),
note VARCHAR(100)
)auto_increment=100;
INSERT INTO product(pName,price,note) VALUES(‘萨尔达荒野之息’,420.15,‘萨尔达天下第一’);
INSERT INTO product(pName,price,note) VALUES(‘只狼’,268,‘TGA2019年度最佳游戏’);
INSERT INTO product(pName,price,note) VALUES(20，‘GTA5’,99.5,‘老催’);
INSERT INTO product(pName,price,note) VALUES(‘英雄联盟’,0,‘真香’);

#外键约束：外键列的数据类型一定要与主键的类型一致
CREATE TABLE score(
id int PRIMARY KEY auto_increment,
course VARCHAR(20),
score DOUBLE(5,2),
stuNo INT,
CONSTRAINT FOREIGN KEY(stuNo) REFERENCES student(stuNo)
)

#连接查询

#笛卡尔积，交叉连接（有很多重复无用的数据）
SELECT * FROM emp,dept;

#交叉
SELECT * FROM emp JOIN dept;

#内连接查询:两个表是平等的，位置可以互换
#内连接的特点：查询结果必须满足条件
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
#表的位置可以任意互换
SELECT * FROM dept e INNER JOIN emp d ON e.deptno=d.deptno;

#外链接（两个表有主次之分）
#左外链接（以左表为主表）
SELECT * FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptno;
#右外链接（以右表为主表）
SELECT * FROM emp RIGHT OUTER JOIN dept on emp.deptno = dept.deptno WHERE emp.empno is NULL;
#子查询
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename=‘JONES’);

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

作业：
1、
创建四张表 分别存储 学生信息 课程信息 分数 讲师信息表 存储相应数据
学生信息表 Student
字段名 字段类型 字段约束 含义
Sno varchar(3) Not null 学员编号
Sname varchar(4) Not null 学员姓名
Ssex varchar(2) Not null 性别
Sbirthday Datetime 生日
Classnum varchar(5) 班级号

课程信息表 course
字段名 字段类型 字段约束 含义
Cno varchar(5) Not null 课程编号
Cname varchar(10) Not null 课程名称
Tno varchar(10) Not null 授课老师编号

成绩表score
字段名 字段类型 字段约束 含义
Sno Varchar(3) Not null 学员编号
Cno Varchar(5) Not null 课程编号
Degree Double(3,1) Not null 分数

讲师表teacher
字段名 字段类型 字段约束 含义
Tno varchar(3) Not null 讲师编号
Tname varchar(4) Not null 讲师姓名
Tsex varchar(2) Not null 讲师性别
Tbirthday Datetime Not null 出生日期
Prof varchar(6) 等级
Depart varchar(10) 所属院系

#查询Student表中的所有记录的Sname、Ssex和Class列
SELECT Sname,Ssex,Class FROM student;
#查询教师所有的单位即不重复的Depart列
SELECT DISTINCT DEPART from teacher;
#查询Student表的所有记录
SELECT * FROM student;
#查询Score表中成绩在60到80之间的所有记录
SELECT * FROM Score WHERE DEGREE BETWEEN 60 and 80;
#查询Score表中成绩为85，86或88的记录
SELECT * FROM Score WHERE DEGREE in(85,86,88);
#查询Student表中“95031”班或性别为“女”的同学记录
SELECT * FROM student WHERE Class = 95031 or Ssex=‘女’;
#以Class降序查询Student表的所有记录
SELECT * FROM student order by class desc;
#以Cno升序、Degree降序查询Score表的所有记录
SELECT * FROM score order by Cno,degree desc;
#查询“95031”班的学生人数
SELECT COUNT(class) FROM student where class=95031;
#查询Score表中的最高分的学生学号和课程号
SELECT Sno,Cno FROM score WHERE degree=(SELECT MAX(degree)FROM score);
#查询‘3-105’号课程的平均分
SELECT cno,avg(degree) FROM score WHERE cno=‘3-105’ group by cno;
#查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
SELECT AVG(degree) FROM score WHERE cno=(SELECT cno FROM score GROUP BY cno HAVING count(cno)>=5)and cno like ‘3%’;
#查询最低分大于70，最高分小于90的Sno列
SELECT sno FROM score GROUP BY sno HAVING min(degree)>70 AND max(degree)<90;
#查询所有学生的Sname、Cno和Degree列
SELECT sname,s.cno,s.degree FROM student,score s WHERE student.sno=s.sno;
#查询所有学生的Sno、Cname和Degree列
SELECT sno,cname,degree FROM course,score WHERE course.cno=score.cno;
#查询所有学生的Sname、Cname和Degree列。
SELECT sname,cname,degree FROM student,course,score WHERE student.sno=score.sno and score.cno=course.cno;
#查询“95033”班所选课程的平均分
SELECT avg(degree) FROM student,score WHERE student.sno=score.sno and class=‘95033’;