模式
create schema ch3;
drop schema ch3;
create schema ch3;
use ch3;
show tables;
基本表
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
show tables;
desc student;
show create table student;
drop table student;
INSERT INTO `student` VALUES ('200215121', '李勇', '男', '20', 'CS');
INSERT INTO `student` VALUES ('200215122', '刘晨', '女', '19', 'CS');
INSERT INTO `student` VALUES ('200215123', '王敏', '女', '18', 'MA');
INSERT INTO `student` VALUES ('200215125', '张立', '男', '19', 'IS');
delete from student;
INSERT INTO `student` VALUES ('200215121', '李勇', '男', '20', 'CS'),
('200215122', '刘晨', '女', '19', 'CS'),
('200215123', '王敏', '女', '18', 'MA'),
('200215125', '张立', '男', '19', 'IS');
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
INSERT INTO `course` VALUES ('1', '数据库', '5', '4');
INSERT INTO `course` VALUES ('2', '数学', null, '2');
INSERT INTO `course` VALUES ('3', '信息系统', '1', '4');
INSERT INTO `course` VALUES ('4', '操作系统', '6', '3');
INSERT INTO `course` VALUES ('5', '数据结构', '7', '4');
INSERT INTO `course` VALUES ('6', '数据处理', null, '2');
INSERT INTO `course` VALUES ('7', 'PASCAL语言', '6', '4');
delete from course;
delete from course
where Cpno is not null;
delete from course;
INSERT INTO `course` VALUES ('2', '数学', null, '2');
INSERT INTO `course` VALUES ('6', '数据处理', null, '2');
INSERT INTO `course` VALUES ('7', 'PASCAL语言', '6', '4');
INSERT INTO `course` VALUES ('4', '操作系统', '6', '3');
INSERT INTO `course` VALUES ('5', '数据结构', '7', '4');
INSERT INTO `course` VALUES ('1', '数据库', '5', '4');
INSERT INTO `course` VALUES ('3', '信息系统', '1', '4');
INSERT INTO `course` VALUES ('2', '数学', null, '2'),
('6', '数据处理', null, '2'),
('7', 'PASCAL语言', '6', '4'),
('4', '操作
系统', '6', '3'),
('5', '数据结构', '7', '4'),
('1', '数据库', '5', '4'),
('3', '信息系统', '1', '4');
delete from course where cno='3';
delete from course where cno='1';
delete from course where cno='5';
delete from course where cno='4';
delete from course where cno='7';
delete from course where cno='6';
delete from course where cno='2';
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
INSERT INTO `sc` VALUES ('200215121', '1', '92');
INSERT INTO `sc` VALUES ('200215121', '2', '85');
INSERT INTO `sc` VALUES ('200215121', '3', '88');
INSERT INTO `sc` VALUES ('200215122', '2', '90');
INSERT INTO `sc` VALUES ('200215122', '3', '80');
alter table student add [column] S_entrance Date;
alter table student modify [column] S_entrance int;
alter table student drop [column] S_entrance;
alter table course add unique(Cname);
alter table course drop key Cname;
索引
create index sc_index on sc(sno, cno);
create index sc_index2 on sc(sno, cno);
explain select * from sc where sno='200215121';
drop index sc_index on sc;
drop index sc_index2 on sc;
单表查询
查询指定列
ex1:
SELECT Sno, Sname
FROM Student;
ex2:
SELECT Sname, Sno, Sdept
FROM Student;
查询所有列
ex3:
SELECT *
FROM Student;
SELECT Sno, Sname, Ssex, Sage, Sdept
FROM Student;
查询经过计算的值
ex4:
SELECT Sname, 2004-Sage
FROM Student;
SELECT 10*100;
SELECT sin(100);
SELECT rand(100);
SELECT rand();
ex5:
SELECT Sname, 'Year of Birth', 2004-Sage, LOWER(Sdept)
FROM Student;
SELECT Sname NAME, 'Year of Birth' BIRTH, 2004-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT
FROM Student;
SELECT Sname 姓名, 'Year of Birth' 出生年, 2004-Sage 出生日期, LOWER(Sdept) 院系
FROM Student;
消除取值重复的行
ex6:
SELECT Sno
FROM SC;
SELECT DISTINCT Sno
FROM SC;
SELECT ALL Sno
FROM SC;
查询满足条件的元组
比较大小
ex7:
SELECT Sname
FROM Student
WHERE Sdept='CS';
SELECT Sname
FROM Student
WHERE Sdept='cs';
ex8:
SELECT Sname, Sage
FROM Student
WHERE Sage<20;
ex9:
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;
SELECT DISTINCT Sno
FROM SC
WHERE Grade<90;
SELECT Sno
FROM SC
WHERE Grade<90;
确定范围
ex10:
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
ex11:
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
确定集
合
ex12:
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS', 'MA', 'IS');
ex13:
SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN ('CS', 'MA', 'IS');
字符串
匹配
ex14:
SELECT *
FROM Student
WHERE Sno LIKE '200215121';
SELECT *
FROM Student
WHERE Sno='200215121';
ex15:
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%';
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘_';
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘__';
ex16:
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳__';
ex17:
SELECT Sname, Sno
FROM Student
WHERE Sname LIKE '__阳%';
SELECT Sname, Sno
FROM Student
WHERE Sname LIKE '_立%';
ex18:
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';
ex19:
insert into course (cno, cname, ccredit) values (8, 'DB_Design', 3);
SELECT Cno, Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
SELECT Cno, Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\\';
SELECT Cno, Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design';
与上面的一个结果是一样的
SELECT Cno, Ccredit
FROM Course
WHERE Cname LIKE 'DBt_Design' ESCAPE 't';
与上面的结果是一样的
SELECT Cno, Ccredit
FROM Course
WHERE Cname LIKE 'DBt_Design';
结果为空集
ex20:
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i__' ESCAPE '\\';
涉及空值的查询
ex21:
SELECT Sno, Cno
FROM SC
WHERE Grade is NULL;
select cno, cpno from course where cpno is null;
select cno, cpno from course where cpno = null;
ex22:
SELECT Sno, Cno
FROM SC
WHERE Grade is NOT NULL;
select cno, cpno from course where cpno is not null;
select cno, cpno from course where cpno != null;
多重条件查询
ex23:
SELECT Sname
FROM Student
WHERE Sdept='CS' AND Sage<20;
SELECT Sname, Ssex
FROM Student
WHERE Sdept='CS' OR Sdept='MA' OR Sdept='IS';
ORDER BY 子句
ex24:
SELECT Sno, Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;
SELECT Sno, Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade;
SELECT Sno, Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade ASC;
ex25:
SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;
SELECT *
FROM Student
ORDER BY Sdept, Sage ASC;
聚集函数:
ex26:
SELECT COUNT(*)
FROM Student;
ex27:
SELECT COUNT(DISTINCT Sno)
FRO
M SC;
SELECT COUNT(Sno)
FROM SC;
SELECT COUNT(ALL Sno)
FROM SC;
ex28:
SELECT AVG(Grade)
FROM SC
WHERE Cno='2';
ex29:
SELECT MAX(Grade)
FROM SC
WHERE Cno='2';
ex30:
SELECT SUM(Ccredit)
FROM SC, Course
WHERE Sno='200215
121' AND https://www.doczj.com/doc/d42307352.html,o=https://www.doczj.com/doc/d42307352.html,o;
GROUP BY 子句
ex31:
SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;
ex32:
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>1;
连接查询
等值与非等值查询
ex33:
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno=SC.Sno;
ex34:
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC
WHERE Student.Sno=SC.Sno;
自身连接
ex35:
SELECT https://www.doczj.com/doc/d42307352.html,o, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno=https://www.doczj.com/doc/d42307352.html,o;
外连接
ex36:
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT JOIN SC
ON (Student.Sno=SC.Sno);
复合条件连接
ex37:
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno AND https://www.doczj.com/doc/d42307352.html,o='2' AND SC.Grade>90;
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno AND https://www.doczj.com/doc/d42307352.html,o='1' AND SC.Grade>90;
ex38:
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno=SC.Sno AND https://www.doczj.com/doc/d42307352.html,o=https://www.doczj.com/doc/d42307352.html,o;
嵌套查询
带有谓词 IN 的子查询
ex39:
SELECT Sdept
FROM Student
WHERE Sname='刘晨';
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept='CS';
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname='刘晨')
;
SELECT S1.Sno, S1.Sname, S1.Sdept
FROM Student S1, Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨';
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept=
(SELECT Sdept
FROM Student
WHERE Sname='刘晨'
);
ex40:
SELECT Sno, Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno IN
(SELECT Cno
FROM Course
WHERE Cname = '信息系统'
)
);
SELECT Student.Sno, Sname
FROM Student, SC, Course
WHERE Student.Sno=SC.Sno AND
https://www.doczj.com/doc/d42307352.html,o=https://www.doczj.com/doc/d42307352.html,o AND
https://www.doczj.com/doc/d42307352.html,ame='信息系统';
带有比较运算符的
子查询
ex41:
SELECT Sno, Cno
FROM SC x
WHERE Grade >= (
SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno
);
带有 ANY (SOME) 或 ALL 谓词的子查询
ex42:
SELECT Sname, Sage
FROM Student
WHERE Sage < ANY (
SELECT Sage
FROM Student
WHERE Sdept='CS'
)
AND Sdept <> 'CS';
SELECT Sname, Sage
FROM Student
WHERE Sage < SOME (
SELECT Sage
FROM Student
WHERE Sdept='CS'
)
AND Sdept <> 'CS';
SELECT Sname, Sage
FROM Student
WHERE Sage < (
SELECT MAX(Sage)
FROM Student
WHERE Sdept='CS'
)
AND Sdept <> 'CS';
ex43:
SELECT Sname, Sage
FROM Student
WHERE Sage < ALL (
SELECT Sage
FROM Student
WHERE Sdept='CS'
)
AND Sdept <> 'CS';
SELECT Sname, Sage
FROM Student
WHERE Sage < (
SELECT MIN(Sage)
FROM Student
WHERE Sdept='CS'
)
AND
Sdept <> 'CS';
带有 EXISTS 谓词的子查询
ex44:
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1');
ex45:
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1');
SELECT Sno, Sname, Sdept
FROM Student S1
WHERE EXISTS
(SELECT *
FROM Student S2
WHERE S2.Sdept=S1.Sdept AND
S2.Sname='刘晨');
ex46:
SELECT Sname
FROM Student
WHERE NOT EXISTS
(
SELECT *
FROM Course
WHERE NOT EXISTS
(
SELECT *
FROM SC
WHERE Sno=Student.Sno
AND Cno=https://www.doczj.com/doc/d42307352.html,o
)
);
ex47:
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(
SELECT *
FROM SC SCY
WHERE SCY.Sno='200215122' AND
NOT EXISTS
(
SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno
AND https://www.doczj.com/doc/d42307352.html,o=https://www.doczj.com/doc/d42307352.html,o
)
);
集合查询
ex48:
SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
ex49:
SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';
ex50:
SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;
ex51:
SELECT Sno
FROM SC
WHERE Cno='1'
INTERSECT
SELECT Sno
FROM SC
WHERE Cno='2';
SELECT Sno
FROM SC
WHERE Cno='1' AND Sno IN
(
SELECT Sno
FROM SC
WHERE Cno='2';
);
ex52:
SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage<=19;
SELECT *
FROM Student
WHERE Sdept='CS' AND Sage>19;
3.5 数据更新
插入数据:
插入元组
ex1:
INSERT
INTO Student(Sno, Sname, Ssex, Sdept, Sage)
VALUES ('200215128', '陈冬', '男', 'IS', 18);
ex2:
INSERT
INTO Student
VALUES ('200215126', '张成民', '男', 18, 'CS');
ex3:
INSERT
INTO SC(Sno, Cno)
VALUES ('200215128', '1');
INSERT
INTO SC
VALUES ('200215128', '1', NULL);
插入子查询结果
ex4:
CREATE TABLE Dept_age
(Sdept CHAR(15),
Avg_age SMALLINT);
INSERT
INTO Dept_age(Sdept, Avg_age)
(
SELECT Sdept, AVG(Sage)
FROM Student
GROUP BY Sdept
);
修改数据:
修改某一个元组的值:
ex5:
UPDATE Student
SET Sage=22
WHERE Sno='200215121';
修改多个元组的值
ex6:
UPDATE Student
SET Sage=Sage+1;
带子查询的修改语句
ex7:
UPDATE SC
SET Grade=0
WHERE 'CS'=
(
SELECT Sdept
FROM Student
WHERE Student.Sno=SC.Sno
);
删除数据:
删除某一个元组的值
ex8:
DELETE
FROM Student
WHERE Sno='200215128';
删除多个元组的值
ex9:
DELETE
FROM SC;
带子查询的删除语句
ex10:
DELETE
FROM SC
WHERE '
CS'=
(
SELECT Sdept
FROM Student
WHERE
Student.Sno=SC.Sno
);
3.6 视图
建立视图:
ex1:
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept='IS';
ex2:
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
MySQL 中!
CREATE OR REPLACE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
ALTER VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
ex3:
CREATE VIEW IS_S1(Sno, Sname, Grade)
AS
SELECT Student.Sno, Sname, Grade
FROM Student, SC
WHERE Sdept='IS' AND
Student.Sno=SC.Sno AND
https://www.doczj.com/doc/d42307352.html,o='1';
ex4:
CREATE VIEW IS_S2
AS
SELECT Sno, Sname, Grade
FROM IS_S1
WHERE Grade >= 90;
ex5:
CREATE VIEW BT_S(Sno, Sname, Sbirth)
AS
SELECT Sno, Sname, 2004-Sage
FROM Student;
ex6:
CREATE VIEW S_G(Sno, Gavg)
AS
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno;
ex7:
CREATE VIEW F_Student(F_Sno, name, sex, age, dept)
AS
SELECT *
FROM Student
WHERE Ssex='女';
删除视图:
ex8:
DROP VIEW BT_S;
DROP VIEW IS_S1;
DROP VIEW IS_S1 CASCADE;
查询视图
ex9:
SELECT Sno, Sage
FROM IS_Student
WHERE Sage<20;
SELECT Sno, Sage
FROM Student
WHERE Sdept='IS' AND Sage<20;
ex10:
SELECT IS_Student.Sno, Sname
FROM IS_Student, SC
WHERE IS_Student.Sno=SC.Sno AND https://www.doczj.com/doc/d42307352.html,o='1';
ex11:
SELECT *
FROM S_G
WHERE Gavg>=90;
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno;
SELECT Sno, AVG(Grade)
FROM SC
WHERE AVG(Grade) >= 90
GROUP BY Sno;
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
更新视图:
ex12:
使用与更新基本表数据一样的语法
UPDATE IS_Student
SET Sname='刘辰'
WHERE Sno='200215122';
UPDATE Student
SET Sname='刘辰'
WHERE Sno='200215122' AND Sdept='IS';
ex13:
INSERT
INTO IS_Student
VALUES ('200215129', '赵新', 20);
INSERT
INTO Student (Sno, Sname, Sage, Sdept)
VALUES ('200215129', '赵新', 20, 'IS');
ex14:
DELETE
FROM IS_Student
WHERE Sno='200215129';
DELETE
FROM Student
WHERE Sno='200215129'AND Sdept='IS';