Staff Property For Rent Staff name staff No

笛卡尔乘积运算 Staff × Property. For. Rent Staff name staff. No brach. No property. No p. staff. No type B 01 PA 14 S 07 House S 01 B 01 PL 94 S 07 Flat Mike S 01 B 01 PG 4 S 15 Flat Tom S 07 B 01 PA 14 S 07 House type Tom S 07 B 01 PL 94 S 07 Flat Mike S 01 B 01 Tom S 07 B 01 Mike S 01 Mary S 15 B 03 Mike Property. For. Rent property. No staff. No PA 14 S 07 House Tom S 07 B 01 PG 4 S 15 Flat PL 94 S 07 Flat Mary S 15 B 03 PA 14 S 07 House PG 4 S 15 Flat Mary S 15 B 03 PL 94 S 07 Flat Mary S 15 B 03 PG 4 S 15 Flat 来自两个集合中的行，所有可能的搭配情形； 2

对笛卡尔乘积运算的结果做选择运算 σstaff. No＝p. staff. No(Staff × Property. For. Rent) name staff. No brach. No 所有可能的搭配情形； property. No p. staff. No type Mike S 01 B 01 PA 14 S 07 House Mike S 01 B 01 PL 94 S 07 Flat Mike S 01 B 01 PG 4 S 15 Flat Tom S 07 B 01 PA 14 S 07 House Tom S 07 B 01 PL 94 S 07 Flat Tom S 07 B 01 PG 4 S 15 Flat Mary S 15 B 03 PA 14 S 07 House Mary S 15 B 03 PL 94 S 07 Flat Mary S 15 B 03 PG 4 S 15 Flat 3

再做投影运算，去掉重复的列 Π name, sff. No, branch. No, propert. No, type o(σstaff. No＝p. staff. No(Staff × Property. For. Rent)) name staff. No brach. No property. No p. staff. No type Tom S 07 B 01 PA 14 S 07 House Tom S 07 B 01 PL 94 S 07 Flat Mary S 15 B 03 PG 4 S 15 Flat name staff. No brach. No property. No type Tom S 07 B 01 PA 14 House Tom S 07 B 01 PL 94 Flat Mary S 15 B 03 PG 4 Flat 4

自然联接运算 Staff name staff. No brach. No Mike S 01 B 01 Tom S 07 B 01 Mary S 15 B 03 Staff ⋈ Property. For. Rent property. No staff. No PA 14 S 07 House PA 14 S 07 Tom B 01 House PL 94 S 07 Flat PL 94 S 07 Tom B 01 Flat PG 4 S 15 Mary B 03 Flat property. No type staff. No name 含义：对外键列进行展开 brach. No type 5

SQL简介 v Structured Query Language(SQL) 是访问关系型数据库 的国际标准语言； v SQL 由数据定语言 Data Definition Language (DDL) 和 数据操作语言 Data Manipulation Language (DML)构成. v SQL 是申明性语言 declarative language ，用户用它来只 表明要做什么操作，不表达做的过程，也称作非过程化化语 言(non-procedural）;

SQL 简介(cont. ) v 用户使用Data definition language (DDL) 来只表达如下 操作: – Create, Alter, and Drop table's Schema – Define and Enforce integrity constraints ; – Enforce security restrict; – Implement the simplicity of manipulation； v 用户使用 Data manipulation language (DML) 来只表达 如下操作: – Insert, Update, delete, query, statisitics rows (records) in table(s);

数据库例子 1（房产中介） ü Branch (branch. No, name, mgr. No) ü Staff (staff. No, name, birthday, position, salary, super. No, branch. No) ü Property. For. Rent (property. No, address, rent, owner. No, staff. No, branch. No) ü Host (owner. No, name, address, phone, branch. No) ü Client (client. No, name, address, phone, preferred. Type, max. Rent, branch. No)； ü Viewing (property. No, client. No, view. Date, comments)； ü Lease (lease. No, property. No, client. No, sign. Date, rent, deposit, start. Date, duration); ü Payment (lease. No, pay. Date, account, start. Date, end. Date);

数据库例子 2（ 程公司的项目管理） ü Emp (eno, name, birthday, title, salary, superno, dno) ü Proj (pno, name, budget, dno) ü Dept (dno, dname, mgreno) ü Workson (eno, pno, resp, year, hours)

数据库例子 3（教务管理） ü Student (sno, name, sex, birthday, nation, class. No, phone, dno) ü Course (cno, name, textbook, credit, hours, dno) ü Teacher (tno, name, rank, email, phone, salary, dno) ü department(dno, name, Address, phone, dean_no); ü enroll (sno, cno, semester, class. No, tno, grade)

往一个表中添加一行记录 v Examples: – INSERT INTO branch (branch. No, name) VALUES (‘B 06', ‘Advanced technology'); – INSERT INTO staff VALUES ('E 9', 'S. Smith', DATE’ 1975 -03 -05’, 'SA', 60000, 'E 8', ‘B 01'); 注意： 1）对于被省略掉的字段，其值被设为 Default. 2）主键字段必须赋值； 3) 赋值字段列表不要省；

对一个表，选定行，修改其列的值 l UPDATE staff SET salary = salary*1. 10; l UPDATE staff SET salary = salary*1. 2 WHERE Branch. No = ‘B 01'; l UPDATE staff SET salary = salary + 500 WHERE staff. No = ‘ 2004213'; l UPDATE staff SET phone = '88821974' WHERE staff. No = ‘ 2004213'; – 注意： 1）修改有三种情况：a）一行；b）多行；c）所有行； 2）修改一行时，WHERE条件中一定要指定主键字段； 3) 等号 左边为新值，右边为原有的值；

对一个表，选定行，将其删除 v Examples: DELETE FROM staff; DELETE FROM staff WHERE salary > 9000; DELETE FROM staff WHERE staff. No = ‘ 2004213'; DELETE FROM student WHERE s_no LIKE ’ 2012%’; 注意： 1）删除有三种情况：a）一行；b）多行；c)所有行； 2）删除特定的一行时，WHERE条件中一定要指定主键字段。

课堂练习 Course Student Name student. No sex birthdate Mike 2008043101 male 1990/12/14 Tom Mary Name course. No textbook syllabus database 430601 a introduction 2008043214 female 1992/02/21 operating system 430803 b all 2008043332 female 1988/07/09 java 430317 c section l 往学生表中添加一行（自己） enroll student. No course. No semester grade 2008043332 430803 2010/01 90 2008043101 430317 2009/01 56 2008043101 430317 2008/02 56 2008043332 430007 2004/02 77 2008043332 430601 2000/01 87 2008043101 430317 2002/02 56 l 把课程表中java这门课程的教材 改为 ‘Java essence’; l 把选课表中成绩不及格的记录删 除;

对一个表，查询记录：先选择，再投影 v 综合了关系代数中的选择和投影 SELECT A 1, A 2, … , An //投影: 输出的字段列表，有顺序 FROM R //指定输入表 WHERE condition 选择：查询条件 v SELECT name, salary FROM teacher ； v SELECT phone FROM student WHERE sno ='20180001'; v SELECT name, sno, class. No FROM student WHERE dno = '24’； 注意：查询特定的一行时，WHERE条件中要指定主键字段。

条件类型 v等值查询：null，确定值； SELECT name FROM staff WHERE position ='secretary'; SELECT name FROM student WHERE d_no IS NULL; v范围查询：连续型，离散型； WHERE salary < 10000 AND salary >= 1500; WHERE rank IN ('教授'，'研究员'，'高级 程师'); WHERE birthday >= DATE('1980/01/01'); v模糊查询：LIKE ，两个特意符号，%，_, 转意符 WHERE name LIKE ‘张%'; WHERE name LIKE ‘张 _ _'; WHERE email LIKE ‘rj#_%@hnu. edu. cn’ ESCAPE #;

对输出结果进行排序 因为表是行的集合，因此，输出结果中的行没有顺序概念的，要对 输出结果进行排序，用ORDER BY指明； SELECT name, sno, class. No FROM student WHERE _no = ‘ 24’ ORDER BY class. No, sno； SELECT sno, grade FROM enroll WHERE cno = ‘H 61030008’ AND semester = ‘ 2017/01’ ORDER BY grade DES; 升序：ASC(ascend的缩写)，降序：DES (单词descend的缩写), 默 认为升序。

先查询，再对查询结果进行统计 SELECT * FROM teacher WHERE d_no='24’; SELECT COUNT(*) FROM teacher WHERE dno='24'; SELECT salary FROM teacher WHERE dno='24'; SELECT AVG(salary) FROM teacher WHERE dno='24'; SELECT COUNT(*)，AVG(salary) FROM teacher WHERE d_no='24; COUNT(*) AVG(salary) 128 4875

对输出字段重新命名 SELECT COUNT(*)，AVG(salary) FROM teacher WHERE dno='24'; SELECT COUNT(*) AS num. Teac，AVG(salary) AS avg. Salary FROM teacher WHERE dno='24'; COUNT(*) AVG(salary) 128 4875 num. Teac avg. Salary 128 4875

教师表分组例子 teacher name t_no Birthday rank salary d_no Mike E 62 1 -Jun-81 professor 5000 590 Tom E 63 1 -Aug-82 professor 4500 590 Mary E 64 4 -May-83 associate professor 4000 590 Bill E 72 1 -May-71 assistant professor 8500 590 Susar E 74 22 -Jul-72 associate professor 6400 590 Machel E 78 22 -Jul-72 assistant professor 5800 590 Philip E 77 22 -Jul-72 associate professor 3000 590 Andy E 76 1 -Jul-76 associate professor 5000 v 590

第一步：查询 SELECT rank FROM teacher WHERE dno='24’; rank professor associate professor assistant professor associate professor

第二步：对查询结果进行分组 SELECT rank FROM teacher WHERE dno='24’ GROUP BY rank; rank professor associate professor associate professor assistant professor 三个组

第三步：依次对每一分组进行统计 SELECT rank, COUNT(*) AS num. Rank FROM teacher WHERE dno='24’ GROUP BY rank; rank professor associate professor rank num. Rank professor 2 associate professor 4 assistant professor 2 assistant professor 注意: 输出字段当且仅当允许出现分组 assistant professor 字段，和聚集函数！

第四步：再对分组统计的结果进行筛选 SELECT rank, COUNT(*) AS num. Rank FROM teacher WHERE dno='24’ GROUP BY rank HAVING num. Rank >2; rank num. Rank professor 2 associate professor 4 assistant professor 2 注意: 输出字段当且仅当允许出现分组字段，和聚集函数！

分组统计的例子 SELECT rank, COUNT(*) AS num. Teac, MIN(salary) AS min. Sal, MAX(salary) AS max. Sal, AVG(salary) AS avg. Sal FROM teacher WHERE dno='24' GROUP BY rank HAVING num. Teac >= 2 ORDER BY rank; rank num. Teac min. Sal max. Sal avg. Sal professor 2 5000 9000 7200 associate professor 4 3500 8000 6500 assistant professor 2 3000 5800 5300 lecture 1 3000 7400 5900

笛卡尔乘积运算 branch staff name dept. No address name staff. No d_no position business D 01 72 Street Mike E 62 D 01 clerk Client D 02 E 63 Road Tom E 63 D 02 manager finance D 03 W 35 Street Mary E 64 D 02 account b. name dept. No address business D 01 72 Street Mike business D 01 72 Street business Client D 01 D 02 72 Street E 63 Road Client D 02 Client s. name staff. No d_no position E 62 D 01 clerk Tom E 63 D 02 manager Mary E 64 D 02 account Mike E 62 D 01 clerk E 63 Road Tom E 63 D 02 manager D 02 E 63 Road Mary E 64 D 02 account finance D 03 W 35 Street Mike E 62 D 01 clerk finance D 03 W 35 Street Tom E 63 D 02 manager finance D 03 W 35 Street Mary E 64 D 02 account

自然连接运算 b. name dept. No address business D 01 72 Street Mike business D 01 72 Street business Client D 01 D 02 72 Street E 63 Road Client D 02 Client s. name staff. No d_no position E 62 D 01 clerk Tom E 63 D 02 manager Mary E 64 D 02 account Mike E 62 D 01 clerk E 63 Road Tom E 63 D 02 manager D 02 E 63 Road Mary E 64 D 02 account finance D 03 W 35 Street Mike E 62 D 01 clerk finance D 03 W 35 Street Tom E 63 D 02 manager finance D 03 W 35 Street Mary E 64 D 02 account b. name dept. No address business D 01 72 Street Mike E 62 clerk Client D 02 E 63 Road Tom E 63 manager Client D 02 E 63 Road Mary E 64 account s. name staff. No position

笛卡尔乘积运算，自然联接运算 v 笛卡尔乘积运算 SELECT b. *, s. * FROM Branch AS b , staff AS s; v 自然联接运算： SELECT b. *, s. name，staff. No, position FROM Branch AS b , staff AS s WHERE b. dept. No = s. dept. No;

列出在 2018/01学期既选修了”数据库系统”又选 修了”操作系统”的学生姓名，学号 SELECT DISTINCT s. name, s. sno FROM student AS s, course AS c, enroll AS e WHERE s. sno = e. sno AND c. cno=e. cno AND c. name = '数据库系统' AND e. semester='2018/01'; INTERSECT SELECT DISTINCT s. name, s. sno FROM student AS s, course AS c, enroll AS e WHERE s. sno = e. sno AND c. cno=e. cno AND c. name = '操作系统' AND e. semester='2018/01';

对”数据库系统”和”操作系统”，列出在 2018/01 学期至少选修了其中一门的学生姓名，学号 SELECT DISTINCT s. name, s. sno FROM student AS s, course AS c, enroll AS e WHERE s. sno = e. sno AND c. cno=e. cno AND （c. name = '数据库系统' OR c. name = '操作系统'） AND e. semester='2018/01';

对”数据库系统”和”操作系统”，列出在 2018/01 学期仅只选修了其中一门的学生姓名，学号 SELECT s. name, s. sno INTO tmp. Tbl_1 FROM student AS s, course AS c, enroll AS e WHERE s. sno = e. sno AND c. cno=e. cno AND c. name = '数据库系统' AND e. semester='2018/01'; SELECT s. name, s. sno INTO tmp. Tbl_2 FROM student AS s, course AS c, enroll AS e WHERE s. sno = e. sno AND c. cno=e. cno AND c. name = '操作系统' AND e. semester='2018/01'; tmp. Tbl_1 UNION tmp. Tbl_2 EXCEPT (tmp. Tbl_1 INTERSECT tmp. Tbl_2);

列出在 2018/01学期，信息学院 2016级学生中没 有选修”数据库系统”的学生姓名，学号 SELECT s. name, s. sno FROM student AS s, dept AS d WHERE s. dno = d. dno AND dept. name = '信息学院' AND s. sno LIKE '2016%'; EXCEPT SELECT s. name, s. sno FROM student AS s, course AS c, enroll AS e WHERE s. sno = e. sno AND c. cno=e. cno AND c. name = '数据库系统' AND e. semester='2018/01';

对 2016级学生，求挂科学分达到 21学分的学生 WTITH fail_enroll(s_no, c_no) AS SELECT DISTINCT s_no, c_no FROM enroll WHERE s_no LIKE '2016%' AND score < 60 EXCEPT SELECT DISTINCT s_no, c_no FROM enroll WHERE s_no LIKE '2016%' AND score >= 60; SELECT s_no, SUM(credit) AS sum_fail_credit INTO tb 0 FROM fail_enroll AS e, course AS c WHERE e. c_no = c. c_no GROUP BY s_no HAVING sum_fail_credit >= 21;

列出在 2018年，信息学院每个老师的上课劳酬， 月 资/4/5 *课时 SELECT t 1. name, t 1. tno, salary, SUM(c. hours) AS sum. Hours, sum. Hours*salary /4 /5/ 8 AS income FROM teacher AS t 1, teach AS t 2, course AS c, dept AS d WHERE t 1. dno = d. dno AND t 1. tno = t 2. tno AND t 2. cno = c. cno AND d. name = '信息学院' AND t 2. semester LIKE ‘ 2018% ’ GROUP BY t 1. name, t 1. tno, salary;

列出在 2018年，在信息学院, 教学 作量不达标 的老师清单 SELECT t 1. name, t 1. tno, SUM(c. hours) AS sum. Hours FROM teacher AS t 1, teach AS t 2, course AS c, dept AS d WHERE t 1. dno = d. dno AND t 1. tno = t 2. tno AND t 2. cno = c. cno AND d. name = '信息学院' AND t 2. semester LIKE ‘ 2018% ’ GROUP BY t 1. name, t 1. tno HAVING sum. Hours <140 ORDER BY sum. Hours;

嵌套查询Subqueries (nested queries) v 求高 资老师：其 资等于或高于所有老师的平均 资的2倍 – SELECT name, tno, salary, birthday FROM teacher WHERE salary > (SELECT AVG(salary)*2 FROM teacher); 注意：查询结果是一个表，当只有一行一列时，可看作是一个常 量

交措嵌套Correlated Subqueries 求数据库中同名的老师，列出其姓名， 号，所属学院 SELECT t. name，t. tno, d. name FROM teacher AS t, dept AS d WHERE t. dno= d. dno AND EXISTS (SELECT * FROM teacher AS t 2 WHERE t. name = t 2. name AND t. tno <> t 2. tno);

交措嵌套Correlated Subqueries 求数据库中同名的老师，列出其姓名， 号，所属学院 SELECT t. name，t. tno, d. name FROM teacher AS t, dept AS d WHERE t. dno= d. dno AND EXISTS (SELECT * FROM teacher AS t 2 WHERE t. name = t 2. name AND t. tno <> t 2. tno);

SQL的发展历史 l 1970 - Codd 关系数据模型和关系代数 l 1974 - D. Chamberlin (also at IBM) 定义SEQUEL (Structured English Query Language) l 1976 - SEQUEL/2 被重新命名为 SQL; l Late 1970 s - System R, Oracle, INGRES 实现了SQL. l 1986 - SQL成为 ANSI 标准 l 1987 -SQL成为 ISO 标准 l 1992 - SQL 2 (SQL 92) revision l 1999 - SQL 3 (supports recursion, object-relational) l 2003 - SQL: 2003 l 2011 - SQL: 2011

程项目管理数据库 emp (eno, name, birthday title, salary, superno, dno) proj (pno, name, budget, dno) dept (dno, name, mgrno) workson (eno, pno, duty, year, hours)

课堂测试分析 v 是上司但不是部门经理的员 ： SELECT superno FROM emp WHERE superno IS NOT NULL； superno不是emp表的主键，而且一个员 可充当多人的上司，因此要加 DISTINCT； v 求部门经理： SELECT mgreno FROM dept WHERE mgrno IS NOT NULL； mgrno不是dept的主键，而且一个员 可也可能担当多个部门的经理，因此 要加DISTINCT； v 求出了这些人了(标识字段），他们在emp中是主键，因此把它扩展为所要的 字段name；于是结果再要和emp表做联接运算。 因此SQL语句为： SELECT emp. eno, emp. name FROM （SELECT DISTINCT superno FROM emp WHERE superno IS NOT NULL）AS supervisor, emp WHERE supervisor. Superno= emp. eno EXCEPT SELECT emp. eno, emp. name FROM （SELECT DISTINCT mgrno FROM dept WHERE mgrno IS NOT NULL）AS dept_manager, emp WHERE dept_manager. mgrno = emp. eno；

对位于长沙的宾馆，在 2019都有过预订的 客户 v SELECT DISTINCT guest. No, hotel. No FROM booking WHERE year(date. From)= 2019 AND hotel. No IN (SELECT hotel. No FROM hotel WHERE city ='长沙')； v SELECT guest. No，COUNT（DISTINCT guest. No, hotel. No）AS hotel. Num INTO tb_1 FROM booking WHERE year(date. From)= 2019 AND hotel. No IN (SELECT hotel. No FROM hotel WHERE city =' 长沙') GROUP BY guest. No; v SELECT COUNT(hotel. No) AS hotel. Num INTO tb_2 FROM hotel WHERE city ='长沙'； v SELECT guest. No INTO tb_3 FROM tb_1, tb_2 WHERE tb_1. hotel. Num = tb 2_hotel. Num； v SELECT guest. No, name, city INTO tb_3 FROM guest, tb_3 WHERE tb_3. guest. No =guest. No；

QQ数据库中的表 QQAccount QQNo password nick. Name 673421 abcd 随风 983408 3212 孙悟空 876590 yjm 黑旋风 897866 ding 123 关公 icon mailbox

QQ数据库中的表(cont. ) QQFriend QQNo friend. No nick. Name 673421 876590 小二 673421 897866 凤仙 876590 65456 铁蛋 876590 767890 阿Q

QQ数据库中的表(cont. ) QQChat QQNo Opposite. No date_Time content 673421 893333 2011/03/28 08: 30: 11 Good morning 673421 327177 2011/03/28 08: 30: 11 鬼家伙，去哪？ 897866 983408 2011/03/28 08: 30: 11 有钱借不？ 897866 327177 2011/03/28 08: 30: 11 好想你！！

QQ数据库中的表(cont. ) QQCommunity QQCNo name start. Date creator. No 87392109 数据库系统 2011/03/28 876590 897866 174班 2011/03/28 876590 8900865 长沙语文 2011/03/28 08: 30: 11 673421

QQ数据库中的表(cont. ) CMember QQCNo member. No nickname role 87392109 673421 淘气泡 管理员 87392109 983408 小精灵 创建者 87392109 876590 炫耀者 普通成员