删除约束注意：
网上说是 ALTER TABLE 表名 DROP CONSTRAINT 约束名;
这里的CONSTRAINT 是指primary key,foreign key,unique,等实际的约束，删除的时候需要用对应的替换(注意:唯一性约束用 INDEX)
1 USE work;
2
3 /*创建选修数据表*/
4 DROP TABLE IF EXISTS sc;
5 CREATE TABLE sc(
6 sno CHAR(8) COMMENT '学号',
7 cno CHAR(3) COMMENT '课程号',
8 grade SMALLINT COMMENT '成绩'
9 );
10
11
12 /*创建学生数据表*/
13 DROP TABLE IF EXISTS student;
14 CREATE TABLE student(
15 sno CHAR(8) COMMENT'学号',
16 sname VARCHAR(20) COMMENT '姓名',
17 sgender CHAR(2) COMMENT '性别',
18 sage INT COMMENT '年龄',
19 sdept VARCHAR(30) COMMENT '所系',
20 sedat VARCHAR(30) COMMENT '入学日期'
21 );
22
23 /*创建课程数据表*/
24 DROP TABLE IF EXISTS course;
25 CREATE TABLE course(
26 cno CHAR(3) COMMENT '课程号',
27 cname VARCHAR(30) COMMENT '课程名',
28 ccredit SMALLINT COMMENT '学分'
29 );
30
31
32 /*插入数据*/
33 INSERT INTO student(sno,sname,sgender,sage,sdept,sedat)
34 VALUES
35 ('20131001','王力','男',19,'计算机','2013/9/11'),
36 ('20131002','张红','女',20,'计算机','2013/9/11'),
37 ('20132001','孙国庆','男',18,'信息','2013/9/11');
38 INSERT INTO course(cno,cname,ccredit)
39 VALUES
40 ('101','数据结构',4),
41 ('102','数据库',3),
42 ('103','C语言程序设计',6);
43 INSERT INTO sc(sno,cno,grade)
44 VALUES
45 ('20131001','101','90'),
46 ('20131001','102','76'),
47 ('20131001','103','88'),
48 ('20131002','101','56');
49
50 /*修改表结构*/
51 ALTER TABLE student ADD sphone char(11);
52 ALTER TABLE student MODIFY COLUMN sphone char(20);
53 ALTER TABLE student DROP COLUMN sphone;
54
70
71 /*对数据表添加修改约束*/
72 -- 添加主键约束
73 ALTER TABLE student ADD CONSTRAINT pk_student PRIMARY KEY(sno);
74 ALTER TABLE course ADD CONSTRAINT pk_course PRIMARY KEY(cno);
75 ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY(sno,cno);
76
77 -- 当主表中的数据删除，从表的对应行也删除
78 -- 添加外键约束
79 ALTER TABLE sc ADD CONSTRAINT fk_student_sc FOREIGN KEY(sno) REFERENCES student(sno) ON DELETE CASCADE;
80 ALTER TABLE sc ADD CONSTRAINT fk_course_sc FOREIGN KEY(cno) REFERENCES course(cno) ON DELETE CASCADE;
81
82 -- 添加非空约束
83 ALTER TABLE student MODIFY COLUMN sname VARCHAR(20) NOT NULL;
84 ALTER TABLE course MODIFY COLUMN cname VARCHAR(30) NOT NULL;
85
86 -- 添加唯一约束
87 -- ALTER TABLE course ADD UNIQUE(cname);
88 ALTER TABLE course ADD CONSTRAINT u_cname UNIQUE(cname);
89
90 -- 添加CHECK约束
91 ALTER TABLE student ADD CONSTRAINT ck_sgender CHECK(sgender in('男','女'));
92 ALTER TABLE student ADD CONSTRAINT ck_sage CHECK(sage BETWEEN 15 AND 35);
93
94 /*删除刚刚添加的约束*/
95
96 -- 先删除外键约束才能删除主表的主键约束
97 ALTER TABLE sc DROP FOREIGN KEY fk_student_sc;
98 ALTER TABLE sc DROP FOREIGN KEY fk_course_sc;
99
100 ALTER TABLE student DROP PRIMARY KEY;
101 ALTER TABLE course DROP PRIMARY KEY;
102 ALTER TABLE sc DROP PRIMARY KEY;
103
104 ALTER TABLE student MODIFY COLUMN sname VARCHAR(20) NULL;
105 ALTER TABLE course MODIFY COLUMN cname VARCHAR(30) NULL;
106
107 ALTER TABLE course DROP INDEX u_cname;
108 -- 不能删除，报错ALTER TABLE course DROP INDEX(cname);
109
110 -- mysql数据库不支持check约束，添加也无效，删除更不支持
111 /*ALTER TABLE student DROP CHECK ck_sgender;
112 ALTER TABLE student DROP CHECK ck_sage;*/
113
114
115
116 SELECT * FROM student;
117 SELECT * FROM course;
118 SELECT * FROM sc;
--------------------------------DEMO2-------------------------------
USE work;
-- 查询全体学生的详细信息
SELECT * FROM student;
-- 检索全体学生的学号、姓名
SELECT sno,sname FROM student;
-- 线索已选课程的课程号,要求显示的课程号不重复
SELECT DISTINCT(cno) FROM sc;
-- 查询全体学生的姓名及出生日期
-- SELECT YEAR(CURDATE())- sage AS 出生年份 FROM student;
-- 查询成绩大于80分的学生的学号、课程号及成绩
SELECT sno,cno,grade FROM sc WHERE grade > 80;
-- 查询成绩介于70~80分的学生的学号、课程号及成绩
SELECT sno,cno,grade FROM sc WHERE grade BETWEEN 70 AND 80;
-- 查询选修了课程号为"101",且成绩大于80分的学生的学号
SELECT sno FROM sc WHERE cno = 101 AND grade > 80;
-- 查询"数据结构" 和 "C语言程序设计"课程的详细信息
SELECT * FROM course WHERE cname = '数据结构' OR cname = 'C语言程序设计';
-- 检索姓王的同学的详细信息(like，模糊查询,'%'代表任意0个或多个字符)
SELECT * FROM student WHERE sname like '王%';
-- 检索名字中第二个字是"力"或"历"的学生的详细信息('_'代表任意单个字符)
SELECT * FROM student WHERE sname like '_力%' OR '_历%';
-- 查询全体学生的姓名及其年龄,并按学生的年龄降序排列(升序ASC,降序DESC)
SELECT sname,sage FROM student ORDER BY sage DESC;
-- 查询学生总人数
SELECT COUNT(sno) FROM student;
-- 计算选修'101'号课程的学生的平均成绩、最高分和最低分
SELECT MAX(grade) AS 最高分,MIN(grade) AS 最低分,AVG(grade) AS 平均成绩 FROM sc WHERE cno = '101';
-- 汇总总分大于200分的学生的学号及总成绩
SELECT sno AS 学号,SUM(grade) AS 总成绩 FROM sc GROUP BY sno HAVING SUM(grade) > 200;
-- 统计各个课程的选课人数
SELECT cno AS 课程号,COUNT(*) AS 选课人数 FROM sc GROUP BY cno;
-- 检索多于2名学生选修的并以1结尾的课程号和平均成绩
-- 数据量不够，我自己改成了至少1人选修的以1开头的课程号和平均成绩，
SELECT cno AS 课程号, AVG(grade) AS 平均成绩 FROM sc WHERE cno like '1%' GROUP BY cno HAVING COUNT(cno) > 0;
--------------------------------DEMO3-------------------------------
-- 检索所有学生的选课信息，包括学号、姓名、课程号、课程名和成绩
SELECT
s.sno AS 学号 ,
s.sname AS 姓名,
c.cno AS 课程号,
c.cname AS 课程名,
sc.grade AS 成绩
FROM
Student s,
Course c,
SC
WHERE
s.sno = sc.sno
AND c.cno = sc.cno;
-- 查询选修了"C语言程序设计"的学生学号和姓名。
SELECT
s.sno AS 学号,
s.sname AS 姓名
FROM
Student s,
Course c,
SC
WHERE
s.sno = sc.sno
AND c.cno = sc.cno
AND c.cname = 'C语言程序设计';
-- 查询与"张红"在同一个系的学生学号、姓名和所属系。
SELECT
sno AS 学号,
sname AS 姓名,
sdept AS 系
FROM
student
WHERE
sdept = (
SELECT
sdept
FROM
student
WHERE
sname = '张红'
);
-- 查询其它系中比信息系的所有学生年龄都大的学生的学号、姓名和年龄
SELECT
sno AS 学号,
sname AS 姓名,
sage AS 年龄
FROM
student
WHERE
sage > (
SELECT
max(sage)
FROM
student
WHERE
sdept = '信息'
)
AND sdept <> '信息';
-- 查询比"王力"年纪大的男学生信息
SELECT
*
FROM
student
WHERE
sage > (
SELECT
sage
FROM
student
WHERE
sname = '王力'
)
AND sgender = '男';
-- 查询每个学生选修课程的学号和课程号和成绩，要求该选修课程的成绩超过他选修课程的平均成绩
SELECT
sc.sno AS 学号,
sc.cno AS 课程号,
sc.grade AS 成绩
FROM
(
SELECT
AVG(grade) avg,
sno
FROM
SC
GROUP BY
sno
) av,
sc
WHERE
av.sno = sc.sno
AND grade >= av.avg;
-- 检索最高分与最低分之差大于10分的学生的学号、最高分和最低分
SELECT DISTINCT
sc.sno AS 学号,
m.max AS 最高分,
m.min AS 最低分
FROM
(
SELECT
MIN(grade) min,
MAX(grade) max,
sno
FROM
SC
GROUP BY
sno
) m,
sc
WHERE
sc.sno = m.sno
AND (m.max - m.min) > 10;
-- 检索选修2门以上课程的学生的学号和总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来.
SELECT
sc.sno AS 学号,
SUM(grade) AS 总成绩
FROM
(
SELECT
sno,
count(cno) c
FROM
sc
GROUP BY
sno
HAVING
COUNT(*) > 2
) cn,
sc
WHERE
cn.sno = sc.sno
AND grade >= 60
GROUP BY
sc.sno
ORDER BY
总成绩 DESC;
-- 查询选修了全部课程的学生学号和姓名
-- 第一步:查询出成绩表中的学号和对应选课数量 SELECT sno,COUNT(cno) FROM sc GROUP BY sno;
-- 第二步:查询出课程表中所有课程的数量 SELECT count(cno) FROM course;
-- 查询到 选课数量 和 所有课程的数量 相等(代表选修了全部课程) 的学生的学号，和在student表中与该学号相同学号的 学生姓名就行了
SELECT
a.sno AS 学号,
student.sname AS 姓名
FROM
(
SELECT
sno,
COUNT(cno) count
FROM
sc
GROUP BY
sno
) a,
student
WHERE
a.count = (SELECT COUNT(cno) FROM course)
AND a.sno = student.sno;
-- 查询至少选修了学生"20132001"选修的全部课程的学生的学号和姓名
SELECT DISTINCT
scx.sno AS 学号,
sname AS 姓名
FROM
sc scx,
student
WHERE
NOT EXISTS (
SELECT
*
FROM
sc scy
WHERE
scy.sno = '20132001'
AND NOT EXISTS (
SELECT
*
FROM
sc scz
WHERE
scz.sno = scx.sno
AND scz.cno = scy.cno
)
)
AND scx.sno = student.sno;
视图与索引
-- 创建视图v1,该视图包含计算机系的学生信息
CREATE VIEW v1(学号,姓名,性别,年龄,系别,入学日期) AS SELECT * FROM student WHERE sdept = '计算机';
-- 创建视图v2,该视图包含成绩及格同学的学号、所以选课程课课程号和成绩
CREATE VIEW v2(学号,课程号,成绩) AS SELECT * FROM sc WHERE grade >= 60;
-- 创建视图v3,该视图包含学号、姓名和平均成绩
CREATE VIEW v3(学号,姓名,平均成绩) AS SELECT student.sno,student.sname,AVG(sc.grade) FROM student,sc WHERE student.sno = sc.sno GROUP BY student.sno;
-- 利用v1视图定义一个包含计算机系学生的学号及其所学课程的课程号和成绩的视图
CREATE VIEW v4(学号,课程号,成绩) AS SELECT v1.`学号`,sc.cno,sc.grade FROM v1,sc WHERE sc.sno = v1.`学号`;
-- 查询计算机系中年龄大于19岁的学生信息
SELECT * FROM v1 WHERE v1.`年龄` > 19;
-- 查询平均成绩在85分以上的学生的学号、姓名和平均成绩
SELECT * FROM v3 WHERE v3.`平均成绩` > 85;
-- 利用v1视图向学生数据表中插入一条记录('20121011','李余力','男',17,'2013/9/11')
INSERT INTO v1(学号,姓名,性别,年龄,入学日期) VALUES('20121011','李余力','男',17,'2013/9/11');
-- 对视图v3进行更新操作，将平均成绩提高5分。观察结果，并分析原因
UPDATE v3 SET 平均成绩 = 平均成绩 + 5; -- 报错:[Err] 1288 - The target table v3 of the UPDATE is not updatable
-- 原因:若视图导出时包含有分组和聚合操作，则不允许对这个视图执行更新操作。
-- 为sc表的sno和cno列建普通索引
ALTER TABLE sc ADD INDEX index_sno_cno(sno,cno);
-- 为student表的sno列建聚簇索引
-- 为course表的cname列建唯一索引
CREATE UNIQUE INDEX UK_course_cname ON course(cname);