2关系数据库标准语言--SQL.ppt

1、关系数据库标准语言-SQL,李静,SQL语言的产生发展,1974 IBM 1986 美国国家标准局 美国标准 1987 国际标准化组织ANSI 1992 SQL2 1999 SQL3,SQL语言的特点,SQL是高度非过程化的语言（指出要干什么） SQL是采取面向集合操作方式的语言 SQL具有一种语法，两种使用方式(交互式和嵌入式) 符合人的思维习惯，结构简单，易学易用,SQL语言的功能,数据定义（Create，Alter，Drop） 数据查询（Select） 单表查询 多表查询 附加功能 数据更新 数据插入（Insert） 数据删除（Delete） 数据修改（Update） 数据控制（Gran

2、t，Commit，Rollback）,SQL是一种标准，但是,SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。 不幸地是，存在着很多不同版本的 SQL 语言，但是为了与 ANSI 标准相兼容，它们必须以相似的方式共同地来支持一些主要的关键词（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等）。 除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的私有扩展！,SQL扩展语言,T-SQL PL/SQL,数据定义,数据类型 SmallINT 短整数类型 2个字节存储 INT 整数类型 4个字节存储 REAL 实数类型 4个字节存储 C

3、HAR(n) 长度为n的字符类型 nCHAR(n) 长度为n的字符类型（Unicode） VARCHAR(n) 最大长度为n的变长字符类型 DATETIME 日期时间类型 BLOB 二进制数,具体数据库的数据类型,数值型,二进制型,数据定义,基本表的创建 CREATE TABLE 例子：,CREATE TABLE Persons( Id int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ),CREATE TABLE Persons ( Id int NOT NU

4、LL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ),CREATE TABLE Persons ( Id int NOT NULL Unique, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ),CREATE TABLE Persons ( Id_P int NOT NULL PRIMARY KEY,

5、 LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ),数据定义,基本表的更改 ALTER 在表S中添加一个新的属性Sex ALTER TABLE S ADD Sex (CHAR(6),数据定义,基本表的取消 DROP 取消基本表S DROP TABLE S,数据定义,创建索引 CREATE INDEX 在表S建立一个学号升序排列的索引X CREATE INDEX X on S(Si) 默认是升序ASC，指定是降序DESC,数据定义,取消索引 DROP

6、INDEX 取消S上的索引X DROP INDEX X,单表查询,数据查询的表定义说明,建立如下关系数据库，由三个关系组成 学生表：S(Si,Sn,Sex,Sd,Sa)； Si为学号， Sn为学生姓名， Sex为学生性别， Sd为学生系别， Sa为学生年龄 课程表：C(Ci,Cn)； Ci为课程编号， Cn为课程名 学生与课程关系表：SC(Si,Ci,G)； G为学生成绩,数据查询-单表查询,单表查询(不带条件的查询) 查询学生表S所有的列 SELECT * FROM S;,查询所有学生的姓名 SELECT Sn FROM S;,查询所有学生的学号与年龄 SELECT Si,Sa FROM S

7、;,创建列的别名,查询所有学生的学号与年龄 SELECT Si as “ID”,Sa as “age” FROM S;,问题： 再运行一次 SELECT * FROM S;,问题？,注意： 1、列的别名不会改变表的列名 2、别名最好加引号 3、as也可以创建表的别名 4、as也可以创建派生列名,消除重复的行,例子：请显示学生所在的系名,如果用： Select Sd from S；,请显示学生所在的系名（不重复） SELECT DISTINCT Sd FROM S;,排序,Order by(ASC/ DESC) 按年龄升序排列学生学号、姓名、年龄 SELECT Si,Sn,Sa FROM S O

8、RDER BY Sa ASC;,问题： SELECT Si,Sn,Sa FROM S ORDER BY Sa;,数据查询-单表查询,带条件的查询 查询计算机系所有学生的姓名与年龄 SELECT Sn,Sa FROM S WHERE Sd=计算机系;,例子,查询所有成绩不及格的学生的学号 SELECT Si FROM SC WHERE G60; 查询所有计算机系的女生的学号 SELECT Si FROM S WHERE sex=女 AND Sd=计算机系; 查询学生学号，条件是计算机系的或者是女生 SELECT Si FROM S WHERE sex=女 OR Sd=计算机系;,例子,查询年龄在

9、18-21岁的学生的姓名 SELECT Sn FROM S WHERE Sa Between 18 and 21; 查询年龄不在18-21岁的学生的姓名 SELECT Sn FROM S WHERE Sa NOT Between 18 and 21;,练习,查询计算机系年龄在20岁以下的学生姓名,s,数据查询-单表查询,集合查询 查询信息系(IS)，数学系(MA)和计算机系(CS)的学生姓名和年龄 SELECT Sn FROM S WHERE Sd IN (IS ,MA,CS);,数据查询-单表查询,相似查询 LIKE 查询姓名以张开头的学生姓名及系别 SELECT Sn,Sd FROM S

10、WHERE Sn LIKE 张%； 通配字符%(*)可以与任意长的字符匹配 通配字符_(?)可以与单个字符匹配,练习,查询姓名以A开头，且第3个字符必须为P的学生姓名 SELECT SN FROM S WHERE Sn LIKE A_P%；,多表查询,数据查询多表查询,学生表S,数据查询多表查询,课程表C,数据查询多表查询,成绩表SC,数据查询多表查询,连接查询 查询选修课程号为C1的所有学生的学号和姓名 需要几张表？ SELECT S.Si, S.Sn FROM S, SC WHERE S.Si=SC.Si AND SC.Ci=C1 由于Si在S和SC两张表中都会出现，引用时需要加上表名称,

11、数据查询多表查询,查询修读课程名为数据库的所有学生姓名 涉及到3张表的查询 SELECT S.Sn FROM S, C,SC WHERE S.Si=SC.Si AND SC.Ci=C.Ci AND C.Cn=数据库,练习,查询至少有一门成绩不及格的学生姓名,数据查询多表查询,嵌套查询 查询选修课程号为C1的所有学生的姓名 SELECT S.Sn FROM S WHERE S.Si IN (SELECT SC.Si FROM SC WHERE SC.Ci=C1) IN 表示属于,内连接,内连接查询与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种： 1、等值连接：在连接条件

12、中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。 2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括、=、!。 3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，而它连接的列名字相同。,例子,列出S中选修了课程的学生信息： SELECT S .* FROM S AS a INNER JOIN SC AS b ON a.Si=b.Si,sql中INNER JOIN和WHERE有区别吗?,SELECT S.Si, S.Sn FROM S,SC WHERE S.Si=SC.Si AND

13、SC.Ci=C1,SELECT S.Si, S.Sn FROM S INNER JOIN SC on S.Si=SC.Si AND SC.Ci=C1,查询选修课程号为C1的所有学生的学号和姓名,sql中INNER JOIN和WHERE有区别吗?,区别不大，可以达到相同效果。 前者是显式连接，后者是隐式连接。 不过，使用INNER JOIN的显示连接方式更适合数据库新的语言规范，已经逐渐替代使用where字句的隐式连接方式。,混合方式 SELECT S.Si, S.Sn FROM S INNER JOIN SC ON S.Si=SC.Si WHERE SC.Ci=C1,外连接,分为左外连接（ L

14、EFT JOIN ）、右外连接（ RIGHT JOIN ）和全外连接（ FULL OUTER JOIN ） 内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。,例子,如下面使用左外连接将选修信息和学生信息连接起来： SELECT a.*,b.* FROM S as a LEFT JOIN SC as b ON a.Si=b.Si 下面使用全外连接将SC表中的所有选修学生以及S表中的所有学生显示： SELE

15、CT a.*,b.* FROM S as a FULL OUTER JOIN SC as b ON a.Si=b.Si,交叉连接,交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 例，S表中有5个学生，而C表中有3个选修课程，则下列交叉连接检索到的记录数将等于5*3=15行。 SELECT Si,Sn FROM S CROSS JOIN C ORDER BY Si,统计计算,数据查询-统计计算,查询中的统计计算 COUNT：集合中元素个数的统计 SUM：集合中元素的和 A

16、VG：集合中元素平均值 MAX：集合中最大的元素 MIN：集合中最小的元素 后面4项只能用在元素为数值型时,数据查询-统计计算,给出全体学生数 SELECT COUNT(*) FROM S; 给出学生S1所修读的课程数目 SELECT COUNT(*) FROM SC WHERE Si=S1;,派生列,给出全体学生平均分 SELECT AVG(G) FROM SC; 给出学号为001的学生各科总分 SELECT SUM(G) FROM SC WHERE Si=001; 给出课程C1的最高分 SELECT MAX(G) FROM SC WHERE Ci=C1;,练习,给出学生李红各科平均分。 给

17、出全校姓李的学生总人数。,算数运算,例子,把学生的C1这门课程成绩全部加上5分 SELECT G+5 FROM SC WHERE Ci=C1; SELECT G+5 AS 成绩 FROM SC WHERE Ci=C1;,常用函数,SubString,作用：提取子串 SubString(string，from start for length) 例子：把课程编号分隔为字母部分和数字部分 SELECT SubString(Ci,1) AS 字母, SubString(Ci,2) AS 数字 FROM C;,Character_Length,作用：得到字符串的长度 Character_Length(

18、string) 例子：把学生名字和名字长度显示出来 SELECT Sn，Character_Length (Sn) AS Len FROM S;,Current_Date()和Current_Time(),作用：得到当前日期和时间 例子：列出当前日期 SELECT Current_Date() AS 日期;,Access里用的函数 是： Date（） Time（） Now（）,数据查询分类功能,分类功能 对集合元组分组，还可以设置逻辑条件 GROUP BY HAVING,SC,给出每个学生 的平均成绩,给出学生 的平均成绩,给出每个学生的平均成绩 SELECT Si,AVG(G) FROM S

19、C GROUP BY Si;,数据查询分类功能,给出各门选修课程的学生数 SELECT Ci,COUNT(Si) FROM SC GROUP BY Ci;,SC,数据查询分类功能+约束条件,给出超过2个学生所选修课程的学生数 SELECT Ci,COUNT(Si) FROM SC GROUP BY Ci HAVING COUNT(Si)2;,SC,练习,给出每个学生修读课程的门数 SELECT Si,COUNT(Ci) FROM SC GROUP BY Si; SELECT Si,COUNT(Ci) AS 修读课程数 FROM SC GROUP BY Si;,练习,按平均分降序给出所有课程都及

20、格的每个学生的平均成绩 SELECT Si,AVG(G) FROM SC GROUP BY Si HAVING MIN(G)=60 ORDER BY AVG(G) DESC;,数据更新-数据删除,数据删除 DELETE 删除学生王明的记录 DELETE FROM S WHERE Sn=王明;,练习,删除学生王明的所有成绩 DELETE FROM SC WHERE Si=(SELECT Si FROM S WHERE Sn=王明);,数据更新数据插入,数据插入 INSERT INTO 将一个学生新记录(Si:S35,Sn:Tom,Sa:20,Sd:CS)插入到表S中 INSERT INTO S

21、VALUES(S35, Tom, 20, CS);,数据更新数据插入,在SC中插入记录（ S23,C10 ） INSERT INTO SC(Si,Ci) VALUES(S23, C10); 注意：属性G没有出现在插入语句中，表示新记录在其上取空值,数据更新数据修改,数据修改 UPDATE SET 修改单个元组的值 将学号为S16的学生系别改为CS UPDATE S SET Sd=CS WHERE Si=S16;,数据更新数据修改,修改多个元组的值 将数学系的学生年龄增加一岁 UPDATE S SET Sa=Sa+1 WHERE Sd=MA;,数据控制授予权限,授予权限 Grant to 对基本表的操作权限 查询（Select）、插入（Insert）、修改（Upd