Student Sno Sname Ssex Sage Sdept CREATE TABLE
例:创建学生表 Student (Sno, Sname, Ssex, Sage, Sdept) CREATE TABLE Student (Sno CHAR(5) PRIMARY KEY, Sname CHAR(20) NOT NULL UNIQUE, Ssex CHAR(2) CHECK (Ssex IN (‘男’, ‘女’)), Sage SMALLINT, Sdept CHAR(15));
例:创建课程表 Course (Cno, Cname, Cpno, Ccredit) CREATE TABLE Course (Cno CHAR(4) PRIMARY KEY, Cname CHAR(40) NOT NULL, Cpno CHAR(4), Ccredit SMALLINT, FOREIGN KEY (Cpno) REFERENCES Course (Cno)); ))
例:创建学生选课表 SC (Sno, Cno, Grade) CREATE TABLE SC (Sno CHAR(5) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT CHECK (Grade>=0 AND Grade<=100), PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student (Sno), FOREIGN KEY (Cno) REFERENCES Course (Cno));
例: l ALTER TABLE Student ADD Scome DATE; l ALTER TABLE Student ALTER COLUMN Sage INT; l ALTER TABLE Student DROP UNIQUE (Sname);
3. 删除基本表 DROP TABLE <表名>; 例:删除Student表 DROP TABLE Student;
例:为Student表按学号升序建立唯一索引 CREATE UNIQUE INDEX Stusno ON Student (Sno); 例:为Course表按课程号升序建立唯一索引 CREATE UNIQUE INDEX Coucno ON Course (Cno); 例:为SC表按学号升序和课程号降序建立唯一索引 CREATE UNIQUE INDEX SCno ON SC (Sno ASC, Cno DESC);
2. 删除索引 DROP INDEX <索引名>; 例:删除Student表的Stusno索引 DROP INDEX Stusno;
3. 3. 1 单表查询 假设:学生-课程数据库 S (Sno, Sname, Ssex, Sage, Sdept) C (Cno, Cname, Cpno, Ccredit) SC (Sno, Cno, Grade)
Sno Sname Ssex Sage Sdept 95001 李勇 男 20 CS 95002 刘晨 女 19 IS 95003 王名 女 18 MA 95004 张立 男 18 IS Cno Cname Cpno Ccredit Sno Cno Grade 1 数据库 5 4 95001 1 92 2 数学 2 95001 2 85 3 信息系统 1 4 95001 3 88 4 操作系统 6 3 95002 2 90 5 数据结构 7 4 95002 3 80 6 数据处理 PASCAL 7 2 6 4
1. 选择表中若干列(投影运算) 1) 查询指定列 SELECT Sno, Sname FROM S; 2) 查询全部列 SELECT * FROM S; 3) 查询经过计算的值 SELECT Sname, 1996 -Sage FROM S; 1996 -Sage SELECT Sname , ‘Birth Year’, 1996–Sage, ‘Birth Year’ LOWER (Sdept) FROM S; SELECT Sname, ‘Birth Year’ BIRTH, 1996–Sage BIRTHDAY FROM S;
Sno Sname 95001 李勇 95002 刘晨 95003 王名 95004 张立 Sname ‘Birth Year’ 1996 -Sage LOWER (Sdept) 李勇 Birth Year 1976 cs 刘晨 Birth Year 1977 is 王名 Birth Year 1978 ma 张立 Birth Year 1978 is Sname 1996 -Sage Sname Birth BRITHDAY Sno 李勇 1976 李勇 Birth Year 1976 95001 刘晨 1977 刘晨 Birth Year 1977 95001 王名 1978 王名 Birth Year 1978 95001 张立 1978 张立 Birth Year 1978 95002
2. 选择表中的若干元组(选择运算) 1) 消除取值重复行 SELECT DISTINCT Sno FROM SC; DISTINCT 2) 查询满足条件的元组(WHERE) ① 比较大小:<, <=, >, >=, =, <> SELECT Sname FROM S WHERE Sdept = ’CS’; SELECT DISTINCT Sno FROM SC WHERE Grade < 90; ② 确定范围: [NOT] BETWEEN … AND … SELECT * FROM S WHERE Sage BETWEEN 20 AND 23; 23
③ 确定集合: [NOT] IN SELECT * FROM S WHERE Sdept IN (‘MA’, ‘CS’); ④ 字符匹配:[NOT] LIKE ‘<匹配串> ’ ⑤ [ESCAPE ‘ <换码字符> ’] SELECT * FROM S WHERE Sno LIKE ‘ 06001’; SELECT * FROM S WHERE Sname LIKE ‘刘%’; SELECT * FROM S WHERE Sname LIKE ‘_ _阳%’; SELECT * FROM C WHERE Cname LIKE ‘DB_Design’ ESCAPE ‘’; SELECT * FROM C WHERE Cname LIKE ‘DB_%i_ _’ ESCAPE ‘’;
⑤ 涉及空值的查询:IS [NOT] NULL SELECT * FROM SC WHERE Grade IS NULL; ⑥ 多重条件查询:AND 高于 OR SELECT * FROM S WHERE Sdept =‘IS’ AND Sage<19; SELECT * FROM S WHERE Sdept = ‘MA’ OR Sdept = ‘CS’; SELECT * FROM S WHERE Sage >= 20 AND Sage <= 23; 23
3. 对查询结果排序 ORDER BY <列名 > [ASC | DESC] ORDER BY SELECT * FORM SC WHERE Cno=‘ 3’ ORDER BY Grade DESC; SELECT * FORM S ORDER BY Sdept, Sage DESC; Sno Cno Grade 95001 3 88 95002 3 80
4. 使用集函数 COUNT, SUM, AVG, MAX, MIN SELECT COUNT (*) FROM S; SELECT COUNT (DISTINCT Sno) FROM SC; Sno SELECT AVG (Grade) FROM SC WHERE Cno= Grade ‘ 1’; SELECT MAX (Grade) FROM SC WHERE Cno= Grade ‘ 1’;
5. 对查询结果分组 GROUP BY <列名> [HAVING <条件>] SELECT Cno, COUNT (Sno) FROM SC GROUP BY Cno; SELECT Sno FROM SC GROUP BY Sno HAVING COUNT (*) >=3; Cno COUNT (Sno) 1 1 2 2 3 2 Sno 95001
注意: l WHERE作用于表,HAVING作用于组 l 分组后,SELECT语句后的列名只能是分组属性 和集函数 SELECT Cno, COUNT (Sno), Avg (Grade) FROM SC GROUP BY Cno;
3. 3. 2 连接查询 1. 等值与非等值连接查询 SELECT S. *, SC. * FROM S, SC WHERE S. Sno = SC. Sno; S. Sno Sname Ssex Sage Sdept SC. Sno Cno Grade 95001 李勇 男 20 CS 95001 1 92 95001 李勇 男 20 CS 95001 2 85 95001 李勇 男 20 CS 95001 3 88 95002 刘晨 女 19 IS 95002 2 90 95002 刘晨 女 19 IS 95002 3 80
笛卡尔积连接 SELECT S. *, SC. * FROM S, SC; 自然连接 SELECT S. Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM S, SC WHERE S. Sno = SC. Sno; S. Sno Sname Ssex Sage Sdept Cno Grade 95001 李勇 男 20 CS 1 92 95001 李勇 男 20 CS 2 85 95001 李勇 男 20 CS 3 88 95002 刘晨 女 19 IS 2 90 95002 刘晨 女 19 IS 3 80
2. 自身连接 查询每一门课的间接先修课 SELECT F 1. Cno, F 2. Cpno FROM C F 1, C F 2 WHERE F 1. Cpno = F 2. Cno; Cno Cname 1 数据库 2 数学 3 信息系统 1 4 4 操作系统 6 5 数据结构 7 6 数据处理 PASCAL 7 Cpno Ccredit 5 F 1. Cno F 2. Cpno 1 7 3 5 3 4 NULL 4 5 6 7 NULL 4 2 2 6 4
3. 外连接 列出所有学生的基本情况和修课情况 SELECT S. Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM S LEFT [OUTER] JOIN SC ON S. Sno = SC. Sno; ( RIGHT, FULL ---SQL Server) (FROM S, SC WHERE S. Sno =SC. Sno(+); --- Oracle) S. Sno Sname Ssex Sage Sdept Cno Grade 95001 李勇 男 20 CS 2 85 95001 李勇 男 20 CS 1 92 95001 李勇 男 20 CS 3 88 95002 刘晨 女 19 IS 2 90 95002 刘晨 女 19 IS 3 80 95003 王名 女 18 MA NULL 95004 张立 男 18 IS NULL
4. 复合条件连接 例:查询选修 2号课程且成绩在 90分以上的所有学生 SELECT S. Sno, Sname FROM S, SC WHERE S. Sno = SC. Sno AND SC. Cno =‘ 2’ AND Grade >=90; 例:查询每个学生选修的课程名及其成绩 SELECT S. Sno, Sname, Cname, Grade FROM S, C, SC WHERE S. Sno = SC. Sno AND C. Cno = SC. Cno; S. Sno Sname Cname Grade 95002 刘晨 95001 李勇 DB 92 95001 李勇 MA 85 95001 李勇 IS 88 95002 刘晨 MA 90 95002 刘晨 IS 80
1. 带有IN谓词的子查询 例: 查询与‘刘晨’在同一系学习的学生 SELECT Sno, Sname, Sdept FROM S WHERE Sdept IN (SELECT Sdept FROM S WHERE Sname =‘刘晨 ’); 自身连接: SELECT S 1. Sno, S 1. Sname, S 1. Sdept FROM S S 1, S S 2 WHERE S 1. Sdept = S 2. Sdept AND S 2. Sname = ‘刘晨’;
例:查询选修了课程名为‘信息系统’的学生学号和姓名 SELECT Sno, Sname FROM S WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM C WHERE Cname =‘信息系统’)); 连接: SELECT S. Sno, Sname FROM S, C, SC WHERE S. Sno = SC. Sno AND SC. Cno=C. Cno AND Cname= ‘信息系统’;
例:找出每个学生超过他选修课程平均成绩的课程号 SELECT Sno, Cno FROM SC X 1 WHERE Grade > (SELECT AVG (Grade) FROM SC X 2 WHERE X 2. Sno = X 1. Sno); 相关子查询
3. 带有ANY或ALL谓词的子查询 l 子查询返回多值时用,且必须同时使用比较运算符 例:查询其它系中比IS系任一学生年龄小的学生姓名和 年龄 SELECT Sname, Sage FROM S WHERE Sage < ANY (SELECT Sage FROM S WHERE Sdept = ‘IS’) AND Sdept <> ‘IS’ ; SELECT Sname, Sage FROM S WHERE Sage < (SELECT MAX (Sage) FROM S WHERE Sdept = ‘IS’) AND Sdept <> ‘IS’ ;
例:查询其它系中比IS系所有学生年龄都小的学生 姓名和年龄 SELECT Sname, Sage FROM S WHERE Sage < ALL (SELECT Sage FROM S WHERE Sdept = ‘IS’) AND Sdept <> ‘IS’ ; MIN
4. 带有EXISTS谓词的子查询 l 不返回任何数据,只返回true或false,子查询的 目标列表达式通常用 * 例:查询所有选修了1号课程的学生姓名 SELECT Sname FROM S WHERE EXISTS (SELECT * FROM SC WHERE Sno=S. Sno AND Cno=‘ 1’); 相关子查询 SELECT Sname FROM S, SC WHERE S. Sno = SC. Sno AND Cno = ‘ 1’
表示全称量词 (For all) ( x)p转换为¬( x(¬p)) 例:查询选修了全部课程的学生姓名 即查询不存在有课没有选的学生姓名 SELECT Sname FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=S. Sno AND Cno=C. Cno));
例:查询至少选修了学生 95002选修的全部课程的学生 学号 即查询不存在 95002选修课程而他没有选修的学生 SELECT DISTINCT Sno FROM SC X WHERE NOT EXISTS (SELECT * FROM SC Y WHERE Y. Sno = ‘ 95002’ AND NOT EXISTS (SELECT * FROM SC Z WHERE Z. Sno = X. Sno AND Z. Cno = Y. Cno));
3. 3. 4 集合查询 l 实现并UNION 、交INTERSECT 、差MINUS运算 l 涉及多个查询语句,每个查询结果的数据项必须数 目、类型相同 例:查询计算机系及年龄不大于19岁的学生 SELECT * FROM S WHERE Sdept=‘CS’ UNION SELECT * FROM S WHERE Sage<=19; SELECT DISTINCT * FROM S WHERE Sdept=‘CS’ OR Sage <=19;
例:查询选修了课程1或课程2的学生学号 SELECT Sno FROM SC WHERE Cno=‘ 1’ UNION SELECT Sno FROM SC WHERE Cno=‘ 2’; SELECT DISTINCT Sno FROM SC WHERE Cno IN (‘ 1’, ‘ 2’); 例:查询年龄不大于19岁的计算机系学生 交运算 SELECT * FROM S WHERE Sdept=‘CS’ AND Sage <=19;
例:查询同时选修了课程1和2的学生学号 交运算 SELECT Sno FROM SC WHERE Cno = ‘ 1’ AND Sno IN (SELECT Sno FROM SC WHERE Cno = ‘ 2’); 例:查询年龄不大于19岁的非计算机系学生 差运算 SELECT * FROM S WHERE Sdept <> ‘CS’ AND Sage <=19; 例:查询选修了课程1但没选修 2的学生学号 差运算 SELECT Sno FROM SC WHERE Cno = ‘ 1’ AND Sno NOT IN (SELECT Sno FROM SC WHERE Cno = ‘ 2’);
l INSERT INTO S (Sno, Sname, Ssex, Sdept, Sage) VALUES (‘ 95020’, ‘陈冬’, ‘男’, ‘IS’, 18); l INSERT INTO S VALUES (‘ 95020’, ‘陈冬’, ‘男’, 18, ‘IS’); l INSERT INTO SC (Sno, Cno) VALUES (‘ 95020’, ‘ 1’); l INSERT INTO SC VALUES (‘ 95020’, ‘ 1’,NULL);
2. 插入子查询结果 INSERT INTO <表名> [(<属性列 1> [, <属性列 2> …)] 子查询; 例:INSERT INTO D (Sdept, Avgage) SELECT Sdept, AVG (Sage) FROM S GROUP BY Sdept;
3. 4. 3 删除数据 DELETE FROM <表名> [WHERE <条件>]; 1. 删除某一个元组的值 DELETE FROM S WHERE Sno=‘ 95019’; 2. 删除多个元组的值 DELETE FROM SC; 3. 带子查询的删除语言 DELETE FROM SC WHERE ‘CS’= (SELECT Sdept FROM S WHERE Sno= SC. Sno); DELETE FROM SC WHERE Sno IN (SELECT Sno FROM S WHERE Sdept=’CS’);
l 例:建立信息系选修了1号课程的学生视图 CREATE VIEW IS_S 1(Sno, Sname , Grade) AS SELECT S. Sno, Sname, Grade FROM S, SC WHERE Sdept=‘IS’ AND S. Sno= SC. Sno AND Cno=‘ 1’; l 例:建立信息系选修了1号课程且成绩在 90分以上的 学生视图 CREATE VIEW IS_S 2 AS SELECT Sno, Sname, Grade FROM IS_S 1 WHERE Grade >= 90; l 例:建立一个反映学生出生年份的视图 虚列 CREATE VIEW BT_S (Sno, Sname, Sbirth) AS SELECT Sno, Sname, 1996 -Sage FROM S;
l 例:建立一个学生学号及平均成绩的视图 (分组视图 ) CREATE VIEW S_G (Sno, Gavg) AS SELECT Sno, AVG (Grade) FROM SC GROUP BY Sno; 例:建立一个女生视图 CREATE VIEW F_S (stdnum, name, sex, age, dept) AS SELECT * FROM S WHERE Ssex=‘女’; l 慎用 SELECT *,S表结构改变时会出错,可去掉列 说明或改*为列表 2. 删除视图 DROP VIEW <视图名>; 例:DROP VIEW IS_S 1;
3. 5. 2 查询视图 l 查询视图时,DBMS先进行有效性检查,再转为对基 本表的查询,称为视图的消解; 例:SELECT Sno, Sage FROM IS_S WHERE Sage<20; 消解为: SELECT Sno, Sage FROM S WHERE Sdept=‘IS’ AND Sage<20; 例:查询信息系选修了1号课程的学生 SELECT Sno, Sname FROM IS_S, SC WHERE IS_S. Sno= SC. Sno AND SC. Cno=‘ 1’;
例:在S_G视图中查询平均成绩在 90分以上的学生学 号和平均成绩 SELECT * FROM S_G WHERE Gavg>=90; 消解为: 错误! 消解为: 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; 查询条件中尽量避免用视图的特殊列,直接对基表查询!
3. 5. 3 更新视图 1. 修改 UPDATE IS_S SET Sname=‘刘辰’ WHERE Sno=‘ 95002’; 等效为: UPDATE S SET Sname=‘刘辰’ WHERE Sno=‘ 95002’ AND Sdept=‘IS’; 2. 插入 INSERT INTO IS_S VALUES (‘ 95029’, ‘赵新’, 20, ‘IS’);
例: GRANT SELECT ON TABLE S TO U 1; GRANT ALL PRIVILEGES ON TABLE S, C TO U 2, U 3; GRANT SELECT ON TABLE SC TO PUBLIC; GRANT UPDATE (Sno), SELECT ON TABLE S TO U 4; GRANT INSERT ON TABLE SC TO U 5 WITH GRANT OPTION;
2. 收回权限 REVORK <权限> [, <权限>…] ON <对象类型> <对象名> [, …] FROM <用户>[, <用户>…]; 例: REVOKE UPDATE (Sno) ON TABLE S FROM U 4; REVOKE SELECT ON TABLE SC FROM PUBLIC; REVOKE INSERT ON TABLE SC FROM U 5;
1. 说明性语句 EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECALRE SECTION; EXEC SQL END DECALRE SECTION; 2. 数据定义语句 EXEC SQL CREATE TABLE S( Sno CHAR(5) PRIMARY KEY, Sname CHAR(20), Ssex CHAR(2), Sage INT, Sdept CHAR(15)); 不允许使用主变量 EXEC SQL DROP TABLE : tablename; (错误)
例: EXEC SQL SELECT Sno, Sname INTO : Hsno, : Hname FROM S WHERE Sno=: givensno; EXEC SQL SELECT Sno, Cno, Grade INTO : Hsno, : Hcno, : Hgrade : Gradeid FROM SC WHERE Sno=: givensno AND Cno=: givencno;
5. 非CURRENT形式的UPDATE语句 EXEC SQL UPDATE SC SET Grade= Grade +: Raise WHERE Cno=‘ 1’; EXEC SQL UPDATE SC SET Grade= : newgrade WHERE Sno=: givensno; Sageid= 1; EXEC SQL UPDATE S SET Sage= : Raise : Sageid WHERE Sdept='CS‘; 等价:EXEC SQL UPDATE S SET Sage= NULL WHERE Sdept='CS‘;
6. 非CURRENT形式的DELETE语句 EXEC SQL DELETE FROM SC WHERE Sno= (SELECT Sno FROM S WHERE Sname=: stdname); 7. INSERT语句 gradeid= 1; EXEC SQL INSERT INTO SC VALUES (: stdno, : couno, : gradeid); EXEC SQL INSERT INTO SC (Sno, Cno) VALUES (: stdno, : couno);
2. CURRENT形式的UPDATE和DELETE语句 游标使用步骤 ① DECLARE游标 ② EXEC SQL DECLARE <游标名> CURSOR FOR ③ <SELECT语句> FOR UPDATE [OF <列名>]; ② OPEN游标 ③ FETCH游标 ④ 检查是否要修改或删除,若是,执行DELETE或 UPDATE时使用WHERE CURRENT OF <游标名> ⑤ 处理完毕CLOSE游标
例:查询所有的课程信息 EXEC SQL INCLUDE SQLCA; /* 定义SQL通信区 */ EXEC SQL BEGIN DECLARE SECTION; /* 说明主变量 */ CHAR HCno [4]; CHAR HCname [20]; INT HCcredit; EXEC SQL END DECLARE SECTION; main() {EXEC SQL CONNECT TO Test USER sa; /* 连接数据库*/ EXEC SQL DECLARE C 1 CURSOR FOR SELECT Cno, Cname, Ccredit FROM C; /* 定义游标,从表中查询相关信息 */ EXEC SQL OPEN C 1; /* 打开游标*/
例:查询某系全体学生的信息,并按要求处理数据 EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; . . . /* 说明主变量 deptname, HSno, HSname, HSsex, HSage, NEWAge等*/ EXEC SQL END DECLARE SECTION; main() { char yn; printf (“Please input a dept name: ”); scanf (“%s”, &deptname); /* 为主变量deptname赋值 */ EXEC SQL CONNECT TO Test USER sa; EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM S WHERE SDept=: deptname FOR UPDATE OF Sage; /* 定义游标 */
if (yn='y' || yn='Y') /* 需要修改 */ { printf("INPUT NEW AGE: "); scanf("%d", &NEWAge); /* 输入新的年龄值 */ EXEC SQL UPDATE S SET Sage=: NEWAge WHERE CURRENT OF SX; /* 修改当前记录的年龄字段 */ } } EXEC SQL CLOSE SX; /* 关闭游标 */ EXEC SQL COMMIT WORK; /* 提交更新 */ EXEC SQL DISCONNECT ALL; /* 断开数据库*/ }
例:从一个帐户转指定数额的款项到另一个帐户中 CREATE PROCEDURE Trans @In. Ano INT, @Out. Ano INT, @Amount FLOAT AS DECLARE @Deposit FLOAT BEGIN SELECT @Deposit =Total FROM ACCOUNT WHERE Ano= @Out. Ano IF @Deposit < @Amount RETURN UPDATE ACCOUNT SET Total =Total- @Amount WHERE Ano= @Out. Ano UPDATE ACCOUNT SET Total =Total+ @Amount WHERE Ano= @In. Ano END 执行:EXEC Trans 1002, 2005, 1000
例:给定学号,获得该学生成绩,若是C 001课程,成绩加 1, 否则加 2 CREATE PROCEDURE Get_gr @Sn CHAR (4), @Gr INT OUTPUT AS DECLARE @Cn CHAR(4) BEGIN SELECT @Cn=Cno, @Gr=Grade FROM S WHERE Sno = @Sn IF @Cn =’C 001’ SELECT @Gr=@Gr+1 ELSE SELECT @Gr=@Gr+2 END 执行:DECLARE @Gr 1 INT EXEC Get_gr ‘s 001’, @Gr 1 OUTPUT SELECT @Gr 1 或 PRINT @Gr 1
例:为教师表定义完整性规则“教授的 资不得低于 800元,如果低于800元,自动改为 800元 CREATE TRIGGER UPDATE_SAL ON Teacher FOR INSERT, UPDATE AS UPDATE Teacher SET Sal=800 WHERE Tno IN (SELECT Tno FROM inserted I WHERE I. Pos='教授‘ AND I. Sal<800)
- Slides: 98