数据库原理及应用开发技术-SQL3-课件

1、讲义三 关系数据库标准语言SQL1 SQL简介2 SQL定义语言3 SQL操作语言4 SQL查询语言5 SQL的基本语言元素6 SQL的操作符7 SQL表达式8 SQL条件9 SQL函数10 SQL查询与子查询总结讲义三 关系数据库标准语言SQL1 SQL简介3.1 SQL简介Structure Query Language(SQL)的历史1970年,Prof Codd 提出著名的关系数据模型SQL语言由IBM Research Lab首先开发创建System R时使用1979年,关系软件公司(Oracle)首先开发出实用的商业SQL版本SQL 是高级的非过程化编程语言。 3.1 SQL简介S

2、tructure Query Langu3.1 SQL简介SQL标准SQL-86是第一个SQL标准SQL-89、SQL-92(SQL2)、SQL-99(SQL3)关系型商业数据库遵守的查询语言规范各种通行的数据库系统在其实践过程中都对 SQL 规范作了某些编改和扩充 3.1 SQL简介SQL标准3.1 SQL简介SQL的使用直接使用交互式查询工具（MS sql server:Transact-sql）直接键入SQL命令对数据库进行操作由DBMS进行解释嵌入式SQL(Embedded SQL)嵌入到高级编程语言使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力。需要将嵌入的SQL语

3、句转化为宿主语言编译器能处理的语句3.1 SQL简介SQL的使用3.1 SQL简介SQL语句分类数据定义语言（Data Definition）createalterdrop数据操作语言（Data Manipulation）insertupdatedelete数据查询语言select数据控制语言(Data Control)grantrolerevoke3.1 SQL简介SQL语句分类3.5.1 数据类型常用的数据类型数据类型描述CHAR(size)存储最长为n个字节的定长字符串VARCHAR(size)存储最长为n个字节的变长字符串NUMERIC(P,S)存储任何类型的数字DATETIME存储日

4、期时间INT长整形SMALLINT短整形FLOAT(n)浮点数，精度至少为n位数字3.5.1 数据类型常用的数据类型3.1 SQL简介数据定义语句格式1 创建表CREATE TABLE （ 列完整性约束条件， 列完整性约束条件， 列完整性约束条件,）；3.1 SQL简介数据定义语句格式3.1 SQL简介数据定义语句格式2 更改表ALTER TABLE ADD 列完整性约束条件，DROP ，MODIFY ;3 删除表DROP TABLE ;3.1 SQL简介数据定义语句格式3.1 SQL简介数据操作语句格式1 插入操作INSERT INTO (, , )VALUES (, , );3.1 SQL

5、简介数据操作语句格式3.1 SQL简介数据操作语句格式2 修改操作UPDATE SET =, =WHERE ; 3 删除操作DELETEFROM WHERE ;3.1 SQL简介数据操作语句格式3.1 SQL简介数据查询语句：数据查询是数据库的核心操作SELECT语句的格式为：SELECT ALL|DISTINCT 别名 , 别名 FROM 别名 , 别名 WHERE GROUP BY HAVING ORDER BY ASC|DESC, ASC|DESC；3.1 SQL简介数据查询语句：3.1 SQL简介数据控制语句：授权SQL语言用GRANT语句向用户授予操作权限，GRANT语句的一般格式为

6、：GRANT ， ON TO ，WITH GRANT OPTION;3.1 SQL简介数据控制语句：授权3.1 SQL简介数据控制语句：收回权限授予的权限用REVOKE语句收回，REVOKE语句的一般格式为：REVOKE , ON FROM ,;3.1 SQL简介数据控制语句：收回权限3.1 SQL简介数据控制语句：角色创建角色CREATE ROLE ;分配角色：GRANT TO ;3.1 SQL简介数据控制语句：角色3.2 SQL定义语言表、视图是数据库中的主体。SQL提供数据定义语句对这些主体进行管理 SQL的数据定义语句 操作对象操 作 方 式创 建删 除修 改表CREATE TABLE

7、DROP TABLEALTER TABLE视 图CREATE VIEWDROP VIEW3.2 SQL定义语言表、视图是数据库中的主体。SQL提3.2.1 表定义1 表的创建SQL语言使用CREATE TABLE语句定义表，其一般格式如下：CREATE TABLE （ 列级完整性约束条件 ， 列级完整性约束条件 ） ， ）;3.2.1 表定义1 表的创建SQL语言使用CREATE3.2.1 表定义1 表的创建（1）实体完整性例 建立Student表，Sno作为主码。CREATE TABLE Student (Sno CHAR(7) PRIMARY KEY, Sname CHAR(8), Sse

8、x CHAR(2) , Sage SMALLINT, Sdept CHAR(20) );3.2.1 表定义1 表的创建（1）实体完整性3.2.1 表定义1 表的创建（1）实体完整性例 建立SC表，Sno和Cno是主码。CREATE TABLE SC (Sno CHAR(7), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY (Sno,Cno);3.2.1 表定义1 表的创建例 建立SC表，Sno和3.2.1 表定义1 表的创建（2）参照完整性例 建立SC表，Sno和Cno是主码，且Sno和Cno也是外码，分别引用Student表的Sno列和Course表的Cn

9、o列。CREATE TABLE SC (Sno CHAR(7), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY (Sno,Cno),FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno);3.2.1 表定义1 表的创建（2）参照完整性3.2.1 表定义1 表的创建（3）属性值约束非空值限制指定允许的取值范围当往表中插入元组或修改属性的值时，数据库管理系统就检查属性上的限制是否被满足，如果不满足则操作被拒绝执行。3.2.1 表定义1 表的创建（3）属

10、性值约束3.2.1 表定义1 表的创建（3）属性值约束例 当学生的性别是男时，其名字不能以Ms.打头。 CREATE TABLE Student (Sno CHAR(7) PRIMARY KEY, Sname CHAR(8) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20), CHECK (Ssex =女 OR Sname NOT LIKE Ms.% );3.2.1 表定义1 表的创建例 当学生的性别是男时，其3.2.1 表定义2 修改表一般格式为： ALTER TABLE ADD 完整性约束 DROP MODIFY ;3.2.1 表定

11、义2 修改表一般格式为： 3.2.1 表定义2 修改表例 向Student表增加“入学时间”列，其数据类型为日期型。ALTER TABLE Student ADD Scome DATE;不论表中原来是否已有数据，新增加的列一律为空值。例 删除学生姓名不能取空值的约束。ALTER TABLE Student DROP NOT NULL(Sname);3.2.1 表定义2 修改表例 向Student表增加3.2.1 表定义3、删除表当某个表不再需要时，可以使用DROP TABLE语句删除它。其一般格式为：DROP TABLE 例 删除Student表。DROP TABLE Student;3.2.

12、1 表定义3、删除表3.2.2 试图定义1、视图的作用（1）视图能够简化用户的操作（2）视图可以减少冗余数据（3）视图对重构数据库提供了一定程度的逻辑独立性（4）视图能够对机密数据提供安全保护3.2.2 试图定义1、视图的作用3.2.2 试图定义2、视图创建SQL语言用CREATE VIEW命令建立视图，其一般格式为：CREATE VIEW (， )AS WITH CHECK OPTION；3.2.2 试图定义2、视图创建3.2.2 试图定义2 试图创建例 建立计算机系学生的视图。CREATE VIEW Student_CSASSELECT *FROM StudentWHERE Sdept =

13、 计算机;3.2.2 试图定义2 试图创建3.2.2 试图定义 2 试图创建例 建立英语课（1156）成绩单的视图。CREATE VIEW English_Grade(Sno, Sname, Grade)ASSELECT Student.Sno, Sname, GradeFROM Student JOIN SC ON Student.Sno = SC.Sno AND SC.Cno = 1156;3.2.2 试图定义 2 试图创建3.2.2 试图定义2 试图创建例 建立英语课的成绩在80分以上的学生的视图。CREATE VIEW English_Grade_80ASSELECT Sno, Sna

14、me, GradeFROM English_GradeWHERE Grade = 80;3.2.2 试图定义2 试图创建3.2.2 试图定义3 删除视图当不再需要一个视图时，可以删除它，语句格式为：DROP VIEW 例 删除视图Student_CSDROP VIEW Student_CS;3.2.2 试图定义3 删除视图3.3 SQL操作语言1 插入操作2 修改操作3 删除操作3.3 SQL操作语言1 插入操作3.3.1 插入操作1、插入操作插入语句的格式是： INSERTINTO (, )VALUES ( , );3.3.1 插入操作1、插入操作3.3.1 插入操作例 将学生王林的信息插入

15、到Student表中。INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage)VALUES (2000012, 王林, 男, 计算机, 19);INTO子句指定Student表和要赋值的列，VALUES子句对元组的各列赋值。3.3.1 插入操作例 将学生王林的信息插入到Stude3.3.1 插入操作例 在表Course中增加课程离散数学的信息。INSERTINTO Course(Cno,Cname,Cpno,Ccredit)VALUES (1136, 离散数学, NULL, 4);符号NULL的含义是赋予该列的值是空值。3.3.1 插入操作例 在表Cours

16、e中增加课程离散3.3.2 修改操作2、修改操作修改操作又称为更新操作，语句的一般格式是：UPDATE SET =, =WHERE ;3.3.2 修改操作2、修改操作3.3.2 修改操作例 将学号为2000012的学生的年龄改为18岁。UPDATE StudentSET Sage=18WHERE Sno=2000012;例6 将所有学生的年龄增加1岁。UPDATE StudentSET Sage= Sage+1;3.3.2 修改操作例 将学号为2000012的学生的3.3.3 删除操作3、删除操作 语句的一般格式为： DELETE FROM WHERE ;3.3.3 删除操作3、删除操作3.3

17、.3 删除操作例 删除学号为2000012的学生记录。DELETEFROM StudentWHERE Sno=2000012;例 删除所有的学生选课记录。DELETEFROM SC;3.3.3 删除操作例 删除学号为2000012的学生记3.4 SQL查询语音1.查询基本结构概述2.查询子句概述3.SELECT 列表4.FROM子句5.WHERE子句3.4 SQL查询语音1.查询基本结构概述3.4.1 查询基本结构概述查询基本结构概述：SELECT 列表：指定列FROM 指定表WHERE指定行语义：Select 子句中的属性列表，对上述结果作投影（ ）操作。From 子句：对指定关系，作笛卡儿

18、积（）Where 子句：进行选择（）运算，找出符合条件的元组。3.4.1 查询基本结构概述查询基本结构概述：3.4.2 查询子句概述SELECT 子句指定您要检索的列。FROM 子句指定从中请求列的表。在所有从表中检索数据的查询中都需要该子句。ON 子句指定 FROM 子句中的表将如何进行连接。该子句只用于多表查询。WHERE 子句指定表中您要查看的行。GROUP BY 子句用于集合数据。HAVING 子句指定要在其上收集集合数据的行。ORDER BY 子句对结果集中的行进行排序。3.4.2 查询子句概述SELECT 子句指定您要检索的列。3.4.3 SELECT 列表1从表中选择所有列SEL

19、ECT distinct * FROM table-expression2从表中选择特定的列SELECT dept_id, dept_name FROM department3重命名查询结果中的列SELECTcolumn-nameASaliasSELECTcolumn-namealias4查询结果中显示字符串select the firstname is as “ ”,first_name from employees;5计算 SELECT 列表中的值SELECT first_name, salary - 10 AS salary after tax FROM employees; 3.4.3

20、 SELECT 列表1从表中选择所有列3.4.4 FROM子句直接使用表名FROM table_name使用相关名您可以赋予表名一个相关名以减少需要键入的字符。您可以在 FROM 子句中指派相关名，方法是在表名之后键入该相关名，如下所示：SELECT d.dept_id, d.dept_name FROM Department dFROM 子句包括链接两个或多个表的表名3.4.4 FROM子句直接使用表名3.4.5 WHERE 子句比较WHERE salary 50000 范围WHERE salary BETWEEN 40000 AND 60000 列表WHERE station IN ( b

21、eijing, shanghai, jinan) 字符匹配 WHERE phone LIKE 415% 未知值WHERE first_name is not NULL组合WHERE salary 50000 AND first_name like A% 3.4.5 WHERE 子句比较3.5 SQL的基本语言元素数据类型数值常量数据格式化空值3.5 SQL的基本语言元素数据类型3.5.1 数据类型数据类型自动转换规则INSERTUPDATE语句中,数据值按照字段的类型进行转换SELECT语句中,字段值按照定义的变量类型转换比较字符数值与数字数值时,将字符数值转换为数字比较字符数值与日期数值时,

22、将字符数值转换为日期赋值语句中,一般将等号右边的变量转换为左边的数据类型3.5.1 数据类型数据类型自动转换规则3.5.1 数据类型字符自动转换SELECT salary + 10 FROM employees;SELECT last_name FROM employees WHERE employee_id = 200;SELECT last_name FROM employees WHERE hiredate = 1982-01-23;3.5.1 数据类型字符自动转换3.5.2 数值常量字符常量HelloORACLE09-MAR-98数字常量25+6.340.525e-03-13.5.2

23、数值常量字符常量3.5.3 数据格式化字符串格式化CAST ( expression AS data_type ) CONVERT ( data_type ( length ) , expression , style )日期格式化字符串转日期 Select cast(2009-01-01 as datetime) 日期格式转换 Select CONVERT(varchar(100), GETDATE(), 0)3.5.3 数据格式化字符串格式化3.5.4 NULL(空)Null:表明记录的字段中不包含任何值(空)目前将等同于Null （ORACLE)条件中的NULLIf A is: Cond

24、ition Evaluates to:10 a IS NULL FALSE10 a IS NOT NULL TRUENULLa IS NULL TRUENULL a IS NOT NULL FALSE10 a = NULL UNKNOWN10 a != NULL UNKNOWN3.5.4 NULL(空)Null:表明记录的字段中不包含任3.6 SQL的操作符操作符介绍一元操作符二元操作符多元操作符算术运算符连接运算符组合操作符3.6 SQL的操作符操作符介绍3.6.1 算数运算符运算符举例+ -SELECT * FROM WHERE quantity = -1; SELECT hire_dat

25、e FROM employees WHERE convert(int,GETDATE() - hire_date,1) 365;* / UPDATE employees SET salary = salary * 1.1;3.6.1 算数运算符3.6.2 连接运算符连接运算符:+Example:CREATE TABLE tab1 (col1 VARCHAR(6), col2 CHAR(6),col3 VARCHAR(6), col4 CHAR(6) );INSERT INTO tab1 (col1, col2, col3, col4) VALUES (abc, def, ghi, jkl);S

26、ELECT col1+col2+col3+col4 Concatenation FROM tab1;-Abcdef ghijkl3.6.2 连接运算符连接运算符:+3.6.3 组合操作符将两个或多个查询的结果组合到一个结果集中操作符 返回结果UNION 组合查询的结果（不重复）UNION ALL 组合查询的全部结果INTERSECT 组合查询的共有结果MINUS对第一个查询取唯一记录，对共有记录不返回3.6.3 组合操作符将两个或多个查询的结果组合到一个结果集3.6.3 组合操作符举例：create table a1(t1 char(1),t2 char(2);create table a2(

27、t01 char(1),t02 char(2);insert into a1(t1,t2) values(1,11);insert into a1(t1,t2) values(2,22);insert into a2(t01,t02) values(2,22);insert into a2(t01,t02) values(3,33);select t01,t02 from a2 Union select t1,t2 from a1;3.6.3 组合操作符举例：3.7 SQL表达式SQL表达式的应用范围Select expr、where expr、having exprOrder by expr

28、 insert 的values() 、update 的set exprSQL表达式的形式简单表达式、复合表达式、CASE表达式Datetime表达式、间隔表达式变量表达式、表达式列表3.7 SQL表达式SQL表达式的应用范围3.7.1 SQL简单表达式举例Select Employees.first_name from EmployeesSelect 1 from Employees where first_name = wSelect any string from Employees where 3.7.1 SQL简单表达式举例3.7.2 SQL复合表达式举例(CLARK + SMITH)

29、LEN(MOOSE) * 57SQRT(144) + 723.7.2 SQL复合表达式举例3.7.3 CASE表达式CASE表达式让你可以在SQL中使用IFELSEEND IF 的逻辑ExampleSELECT sno,CASE WHEN grade 85 THEN high WHEN grade ,=,= 2500;ANY,SOMESELECT * FROM employees WHERE salary = ANY (SELECT salary FROM employees WHERE department_id = 30);ALLSELECT * FROM employees WHERE

30、salary = ALL ( 1400, 3000);3.8.1 比较条件 条件类型Example3.8.2 逻辑条件逻辑类型ExampleNOTSELECT * FROM employees WHERE NOT (job_id IS NULL);SELECT * FROM employees WHERE NOT (salary BETWEEN 1000 AND 2000); ANDORSELECT * FROM employees WHERE job_id = PU_CLERK ANDOR department_id = 30;3.8.2 逻辑条件逻辑类型Example3.8.3 成员条件类

31、型ExampleINSELECT * FROM employees WHERE job_id IN (PU_CLERK,SH_CLERK);SELECT * FROM employees WHERE salary IN (SELECT salary FROM employees WHERE department_id =30); NOT INSELECT * FROM employeesWHERE job_id NOT IN(PU_CLERK, SH_CLERK);3.8.3 成员条件类型Example3.8.4 范围条件类型Examplenot between X and y SELECT

32、* FROMemployees WHERE salary BETWEEN 2000 AND 3000;3.8.4 范围条件类型Example3.8.5 NULL条件类型Example IS NOTSELECT last_name NULL FROM employeesWHERE salaryIS NULL; 3.8.5 NULL条件类型Example3.8.6 LIKE条件举例：SELECT enameFROM empWHERE ename LIKE A_L%ESCAPE ;类型X not LIKE yescape z3.8.6 LIKE条件举例：类型3.8.7 复合条件通过（）、not 、a

33、nd 、or 复合各类简单的条件ExampleSelect first_name from employees Where first_name like m% and (salary = 2000 or department_id in (10,20);3.8.7 复合条件通过（）、not 、and 、or 复合3.9 功能函数内部函数单行函数聚集函数用户自定义函数3.9 功能函数内部函数3.9 功能函数常用的单行函数数值函数abs、sqrt、round、mod字符函数lower、upper、len、trim、substr日期函数Day,month,year,getdate,weekday,

34、today转换函数Convert、cast3.9 功能函数常用的单行函数3.9 功能函数常用的聚集函数求平均：AVG总计：COUNT最大值：MAX最小值：MIN求总和：SUM3.9 功能函数常用的聚集函数3.10 SQL查询和子查询1.SQL查询举例2.SQL子查询子查询简介子查询的作用WHERE子句中的子查询FROM子句中的子查询HAVING子句中的子查询3.10 SQL查询和子查询1.SQL查询举例3.10.1 SQL汇总、分组与排序SQL汇总：应用了集合函数的每一组行生成一个值举例：SELECT avg(salary + 1) FROM SELECT count(distinct id)

35、 FROM SELECT sum(salary) FROM SELECT max(salary) FROM 3.10.1 SQL汇总、分组与排序SQL汇总：3.10.2 SQL汇总、分组与排序SQL分组：GROUP BY 字句，将表的输出划分为若干个组举例：SELECT employee_id,avg(salary) FROM employees GROUP BY employee_id;3.10.2 SQL汇总、分组与排序SQL分组：3.10.2 SQL汇总、分组与排序WHERE子句与HAVING子句：Where 决定哪些元组被选择参加运算，作用于关系中的元组。Having 决定哪些分组符合要求，作用于分组。聚合函数的条件关系必须用Having，Where中不应出现聚合函数。举例：SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY departme