数据库原理及应用-第4章-SQL语言

1、数据库原理与应数据库原理与应用用第第4 4章章 SQLSQL语言语言SQL( Structured Query Language)结构化查询语言结构化查询语言 已成为关系数据库的标准语言支持关系系统Oracle、Informix、Sybase、MS SQL、DB2、FoxPro、Access等SQL语言概述DDL(Data Definition Language)数据定义语言数据定义语言 主要定义数据库的逻辑结构：基本表、视图和索引。DML(Data Manipulation Language) 数据操纵语言数据操纵语言 查询和更新(插入、删除和修改) DCL (Data Control La

2、nguage)数据库控制语言数据库控制语言 授权、完整性规则的描述和事务控制。嵌入式嵌入式SQL 规定了SQL语句在宿主语言的程序中使用规则。SQL语言的组成1. 综合统一查询、操纵、定义、控制操作功能一体化语言风格统一，数据操作符统一性2. 高度非过程化用户只需提出“做什么”，而不必指明“怎么做”隐蔽数据存取路径3. 面向集合的操作方式操作的对象和结果都是元组的集合（关系）SQL语言的特点4. 以同一种语法结构提供两种使用方式自含式语言，DBMS中独立使用，是针对DB的所有用户 嵌入式语言，嵌入到宿主语言中使用，针对应用程序员 两种类型的语法结构基本一致5. 语言简洁，易学易用类似于英语的自

3、然语言操作谓词少（核心功能只用9个动词）SQL功能动词 数据查询 数据定义 数据操纵 数据控制SELECTCREATE，DROP，ALTERINSERT，UPDATE，DELETEGRANT，REVOKESQL支持关系数据库的三层模式基本表基本表T1视图视图V1基本表基本表T3基本表基本表T4存储文件存储文件1存储文件存储文件2基本表基本表T2视图视图V2SQL用户用户外模式外模式模式模式内模式内模式 基本表基本表（base table)：数据库中实际独立存在的表（关系）存储在实际的文件中 存储文件存储文件存储基本表的数据 视图（视图（view)是一个虚拟表从基本表或其他视图中导出的表没有实际

4、的存储位置数据库中只存放视图的定义而不存放视图对应的数据，数据仍然存放在导出视图的基本表中Transact-SQL语言 结构化查询语言结构化查询语言SQL(Structured Query Language) SQL语言现在已成为关系型数据库环境下的标准查询语言。 Transact-SQL语言语言微软公司在SQL标准的基础上做了大幅度扩充，并将SQL Server使用的SQL语言称为Transact-SQL语言语言。 Transact-SQL命令格式的约定在Transact-SQL语言的命令格式中， 括起来的内容表示是可选的；，n表示重复前面的内容；括起来表示在实际编写语句时，用相应的内容替代

5、; 括起来表示是必选的；类似A|B的格式：表示A和B只能选择一个，不能同时都选。 系统数据类型系统数据类型 SQL Server预先定义好的，可以直接使用。数据类型1整型数据类型整型数据类型 int（integer）：）：4个字节个字节系统数据类型系统数据类型 SQL Server预先定义好的，可以直接使用。数据类型2 2字符数据类型字符数据类型用来存储各种字母、数字符号和特殊符号。在使用时需要在其前后加上英文单引号或者双引号。 （1）Char：占用1个字节。定义：charchar（n n） n的取值为18000。默认n的值为1（2）Varchar：可以存储长达8000个字符的可变长度字符串，

6、和char类型不同varchar类型根据输入数据的实际长度而变化。其定义形式为：varchar（n） 数据数据定义语言定义语言 DDLDDL(Data Definition Language)(Data Definition Language)DDL包括定义基本表、视图和索引对象定义方式表 tabel create table视图 viewcreate view索引 indexcreate index学生-课程数据库Student(Sno,Sname,Ssex,Sage, Sphone)Course(Cno,Cname)SC(Sno,Cno,Grade)后续示例用到的数据库模式语句格式：cre

7、ate tabel ( 列级完整性约束条件 , 列级完整性约束条件 , );数据数据定义语言定义语言 DDLDDL(Data Definition Language)(Data Definition Language)定义基本表：约束是SQL Server提供的自动保持数据库完整性的一种方法 。约束约束 constraintconstraint主键约束主键约束 primary key主键能够唯一地确定表中的每一条记录，主键不能取空值。主键约束可以保证实体的完整性。 唯一性约束唯一性约束 unique唯一性约束用于指定一个或多个列的组合值具有唯一性，以防止在列中输入重复的值。使用唯一性约束的字段

8、允许为空值允许为空值；一个表中可以允许有多个唯一性约束；可以把唯一性约束定义在多个字段上；约束是SQL Server提供的自动保持数据库完整性的一种方法 。约束约束 constraintconstraint外键约束外键约束 references 外键约束主要用来维护两个表之间数据的一致性。n外键约束提供了字段参照完整性；n外键从句中的字段数目和每个字段指定的数据类型都必须和REFERENCES从句中的字段相匹配；n参考同一表中的字段时，必须只使用REFERENCES子句，不能使用外键子句；建立学生表student，由学号、姓名、性别、年龄、系别5个属性组成.CREATE TABLE stude

9、nt (Sno char(8) primary key, Sname char(20) not null , Ssex char(1) default 男, Sage int check(Sage=0 and Sage =120), Sphone char(15) unique ) 语句格式ALTER TABLE ADD 完整性约束 DROP MODIFY 修改内容增加新列和新的完整性约束条件（ADD子句） 删除指定的完整性约束条件（DROP子句） 修改原有的列定义 （ MODIFY子句）修改基本表 ALTER TABLE查询selectSELECT语句对基本表（关系）和视图（虚表）进行数据查

10、询它不改变数据本身。查询主要有：单表查询连接查询嵌套查询集合查询SELECT语句一般格式：SELECT ALL|DISTINCT ,FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESCSELECT 指明要检索的数据的列 FROM指明从哪(几)个表中进行检索 WHERE指明返回数据必须满足的标准 GROUP BY 指明返回的列数据通过某些条件来形成组 HAVING 指明返回的集合必须满足的标准，它必须与GROUP BY配合使用 ORDER BY 指明返回的行的排序规则 单表查询1、选择表中的若干列（投影）SELECT2、选择表中的若干行（选取）WHERE3

11、、对查询结果排序 ORDER BY4、集函数查询5、对查询结果分组 GROUP BY选择表中的若干列1）查询指定的列1. 查询全体学生的学号与姓名 SELECT Sno, Sname FROM Student2. 查询学生姓名、学号和性别 SELECT Sname, Sno, Ssex FROM Student 2）查询全部列1. 查询全体学生的详细记录 SELECT * FROM Student 2. 显示指定字段输出次序SELECT Sno, Sphone, Sname, Sage, Ssex FROM Student3）查询经过计算的值1. 查询全体学生的姓名及其出生年份 SELECT

12、Sname,2012-Sage FROM Student4）列标题使用别名1.查询全体学生的姓名、出生年份，并使用别名 SELECT Sname Name, 2012-Sage AS Birthday FROM Student1）消除重复的行：DISTINCT1. 查询所有选修过课的学生的学号 SELECT Sno FROM SC 等价于： SELECT ALL Sno FROM SC结果中保留了重复的行 SELECT DISTINCT Sno FROM SC结果中消除了重复的行选择表中的若干行2）查询满足条件的元组通过WHERE子句实现常用的查询条件查询条件谓 词比较=, , =, =, !

13、=, , !, !, NOT+上述比较符确定范围BETWEEN AND， NOT BETWEEN AND确定集合IN， NOT IN字符匹配LIKE，NOT LIKE空值IS NULL， IS NOT NULL多重条件AND，OR（1）比较大小：比较运算符1. 查询计算机系全体学生的名单SELECT Sname FROM Student WHERE Sdept=CS2. 查询年龄小于20岁学生SELECT * FROM Student WHERE Sage=203. 查询考试成绩不及格学生的学号SELECT DISTINCT Sno FROM SC WHERE Grade=60 AND Gra

14、de=752. 查询成绩不在60至75之间的学生SELECT * FROM SC WHERE Grade NOT BETWEEN 60 AND 75(3)确定集合（NOT）IN1. 查询选修了课程C1或C2或C3的学号SELECT DISTINCT Sno FROM SC WHERE Cno IN (C1,C2,C3)或：SELECT DISTINCT Sno FROM Sc WHERE Cno=C1 OR Cno=C2 OR Cno=C32. 查询既不是计算机系，也不是数学系的学生SELECT * FROM Student WHERE Sdept NOT IN (CS,MA)(4)字符匹配（

15、模糊查询）（NOT）LIKE语法格式： NOT LIKE ESCAPE含义：查询指定的属性列值与相匹配的元组。 通配符 ：%(百分号)：代表任意长度的字符串 _(下划线)：代表任意单个字符ESCAPE短语，对通配符进行转义1. 查询计算机系(CS)的学生SELECT * FROM Student WHERE Sdept LIKE CS或：SELECT * FROM Student WHERE Sdept = CS2. 查询姓赵的学生姓名、学号和性别SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 赵%3. 查询第二个字符为o的课程SELEC

16、T * FROM Course WHERE Cname LIKE _o%4. 查询以DB_开头，且倒数第2个字符为i的课程SELECT * FROM Course WHERE Cname LIKE DB_%i_ ESCAPE （5）涉及空值的查询：IS （NOT）NULL1. 查询缺少成绩的学生学号和相应课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NULL2. 查询有成绩的学生学号和相应课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL（6）多重条件查询：AND/OR1. 查询课程号为C2且成绩高于8

17、5分以上的学生SELECT * FROM SC WHERE Cno=C2 ANDGrade852. 查询选修C1或C2且不低于70分的学生 SELECT * FROM SC WHERE (Cno=C1 OR Cno=C2) ANDGrade=70AND优先级高于OR指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果。1.查询选修了C1的学生，成绩按降序排列 SELECT * FROM SC WHERE Cno=C1 ORDER BY Grade DESC降序排列，成绩为空的元组最先显示升序排列，成绩为空的元组最后显示2. 查询全体学生情况，查询结果按所在系升序排列，对同

18、一系中的学生按年龄降序排列 SELECT * FROM Student ORDER BY Sdept, Sage DESC对查询结果排序 ORDER BYCOUNT (DISTINCT|ALL *)统计元组个数，用DISTINCT消除重复行COUNT (DISTINCT|ALL ) 统计列值计个数，用DISTINCT消除重复行SUM (DISTINCT|ALL ) 按列计算值的总和，对数值有效AVG (DISTINCT|ALL ) 按列计算平均值，对数值有效 MAX (DISTINCT|ALL )求一列值中最大值MIN (DISTINCT|ALL )求一列值中最小值 集函数查询1. 求计算机系（CS）学生的平均年龄 SELECT AVG(Sage) FROM Student WHERE Sdept=CS2. 求95003学生的总分和平均分SELECT SUM(Grade), AVG(Grade)FROM SC WHERE Sno=950033. 求计算机系的学生总数SELECT COUNT(*) FROM Student WHERE Sdept=CS 或： SELECT COUNT(Sno) FROM Student WHERE Sdept=CS4. 查询共有几个系SELE