数据库原理及应用开发技术_SQL.pptVIP

讲义三 关系数据库标准语言SQL 1 SQL简介 2 SQL定义语言 3 SQL操作语言 4 SQL查询语言 5 SQL的基本语言元素 6 SQL的操作符 7 SQL表达式 8 SQL条件 9 SQL函数 10 SQL查询与子查询 总结 3.1 SQL简介 Structure Query Language(SQL)的历史 1970年,Prof Codd 提出著名的关系数据模型 SQL语言由IBM Research Lab首先开发创建 System R时使用 1979年,关系软件公司(Oracle)首先开发出实 用的商业SQL版本 SQL 是高级的非过程化编程语言。 3.1 SQL简介 SQL标准 SQL-86是第一个SQL标准 SQL-89、SQL-92(SQL2)、SQL-99(SQL3) 关系型商业数据库遵守的查询语言规范 各种通行的数据库系统在其实践过程中都对 SQL 规范作了某些编改和扩充 3.1 SQL简介 SQL的使用 直接使用 交互式查询工具（MS sql server:Transact-sql） 直接键入SQL命令对数据库进行操作 由DBMS进行解释 嵌入式SQL(Embedded SQL) 嵌入到高级编程语言 使应用程序充分利用SQL访问数据库的能力、宿主 语言的过程处理能力。 需要将嵌入的SQL语句转化为宿主语言编译器能处 理的语句 3.1 SQL简介 SQL语句分类 数据定义语言（Data Definition） create alter drop 数据操作语言（Data Manipulation） insert update delete 数据查询语言 select 数据控制语言(Data Control) grant role revoke 3.5.1 数据类型 常用的数据类型 数据类型描述 CHAR(size)存储最长为n个字节的定长字符串 VARCHAR(size)存储最长为n个字节的变长字符串 NUMERIC(P,S)存储任何类型的数字 DATETIME存储日期时间 INT长整形 SMALLINT短整形 FLOAT(n)浮点数，精度至少为n位数字 3.1 SQL简介 数据定义语句格式 1 创建表 CREATE TABLE （ 列完整性约束条件， 列完整性约束条件， 列完整性约束条件, ）； 3.1 SQL简介 数据定义语句格式 2 更改表 ALTER TABLE ADD 列完整性约束条件 ， DROP ， MODIFY ; 3 删除表 DROP TABLE ; 3.1 SQL简介 数据操作语句格式 1 插入操作 INSERT INTO (, , ) VALUES (, , ); 3.1 SQL简介 数据操作语句格式 2 修改操作 UPDATE SET =, = WHERE ; 3 删除操作 DELETE FROM WHERE ; 3.1 SQL简介 数据查询语句： 数据查询是数据库的核心操作 SELECT语句的格式为： SELECT ALL|DISTINCT 别名 , 别名 FROM 别名 , 别名 WHERE GROUP BY HAVING ORDER BY ASC|DESC, ASC|DESC； 3.1 SQL简介 数据控制语句：授权 SQL语言用GRANT语句向用户授予操作权限， GRANT语句的一般格式为： GRANT ， ON TO ， WITH GRANT OPTION; 3.1 SQL简介 数据控制语句：收回权限 授予的权限用REVOKE语句收回，REVOKE语句的 一般格式为： REVOKE , ON FROM ,; 3.1 SQL简介 数据控制语句：角色 创建角色 CREATE ROLE ; 分配角色： GRANT TO ; 3.2 SQL定义语言 表、视图是数据库中的主体。SQL提供数据定 义语句对这些主体进行管理 SQL的数据定义语句 操作对对象操 作 方 式 创创 建删删 除修 改 表CREATE TABLE DROP TABLE ALTER TABLE 视视 图图CREATE VIEW DROP VIEW 3.2.1 表定义 1 表的创建 SQL语言使用CREATE TABLE语句定义表，其一 般格式如下： CREATE TABLE （ 列级完整性约束条件 ， 列级完整性约束条件 ） ， ）; 3.2.1 表定义 1 表的创建 （1）实体完整性 例 建立Student表，Sno作为主码。 CREATE TABLE Student (Sno CHAR(7) PRIMARY KEY, Sname CHAR(8), Ssex CHAR(2) , Sage SMALLINT, Sdept CHAR(20) ); 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 表的创建 （2）参照完整性 例 建立SC表，Sno和Cno是主码，且Sno和Cno也是外码，分别引 用Student表的Sno列和Course表的Cno列。 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 表的创建 （3）属性值约束 非空值限制 指定允许的取值范围 当往表中插入元组或修改属性的值时， 数据库管理系统就检查属性上的限制是否 被满足，如果不满足则操作被拒绝执行。 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 表定义 2 修改表 一般格式为： ALTER TABLE ADD 完整性约束 DROP MODIFY ; 3.2.1 表定义 2 修改表 例 向Student表增加“入学时间”列，其数据类 型为日期型。 ALTER TABLE Student ADD Scome DATE; 不论表中原来是否已有数据，新增加的列一律 为空值。 例 删除学生姓名不能取空值的约束。 ALTER TABLE Student DROP NOT NULL(Sname); 3.2.1 表定义 3、删除表 当某个表不再需要时，可以使用DROP TABLE语句删除它。其一般格式为： DROP TABLE 例 删除Student表。 DROP TABLE Student; 3.2.2 试图定义 1、视图的作用 （1）视图能够简化用户的操作 （2）视图可以减少冗余数据 （3）视图对重构数据库提供了一定程度的逻 辑独立性 （4）视图能够对机密数据提供安全保护 3.2.2 试图定义 2、视图创建 SQL语言用CREATE VIEW命令建立视 图，其一般格式为： CREATE VIEW (， ) AS WITH CHECK OPTION； 3.2.2 试图定义 2 试图创建 例 建立计算机系学生的视图。 CREATE VIEW Student_CS AS SELECT *SELECT * FROM StudentFROM Student WHERE Sdept = WHERE Sdept = 计算机计算机 ; ; 3.2.2 试图定义 2 试图创建 例 建立英语课（1156）成绩单的视图。 CREATE VIEW English_Grade(Sno, Sname, Grade) AS SELECT Student.Sno, Sname, GradeSELECT Student.Sno, Sname, Grade FROM Student JOIN SC ON FROM Student JOIN SC ON Student.Sno = SC.Sno AND Student.Sno = SC.Sno AND SC.Cno = 1156; SC.Cno = 1156; 3.2.2 试图定义 2 试图创建 例 建立英语课的成绩在80分以上的学生 的视图。 CREATE VIEW English_Grade_80 AS SELECT Sno, Sname, GradeSELECT Sno, Sname, Grade FROM English_GradeFROM English_Grade WHERE Grade = 80;WHERE Grade = 80; 3.2.2 试图定义 3 删除视图 当不再需要一个视图时，可以删除它， 语句格式为： DROP VIEW 例 删除视图Student_CS DROP VIEW Student_CS; 3.3 SQL操作语言 1 插入操作 2 修改操作 3 删除操作 3.3.1 插入操作 1、插入操作 插入语句的格式是： INSERT INTO (, ) VALUES ( , ); 3.3.1 插入操作 例 将学生王林的信息插入到Student表中 。 INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES (2000012, 王林, 男, 计算机, 19); INTO子句指定Student表和要赋值的列 ，VALUES子句对元组的各列赋值。 3.3.1 插入操作 例 在表Course中增加课程离散数学的信 息。 INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES (1136, 离散数学, NULL, 4); 符号NULL的含义是赋予该列的值是空 值。 3.3.2 修改操作 2、修改操作 修改操作又称为更新操作，语句的一般 格式是： UPDATE SET =, = WHERE ; 3.3.2 修改操作 例 将学号为2000012的学生的年龄改为18 岁。 UPDATE Student SET Sage=18 WHERE Sno=2000012; 例6 将所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1; 3.3.3 删除操作 3、删除操作 语句的一般格式为： DELETE FROM WHERE ; 3.3.3 删除操作 例 删除学号为2000012的学生记录。 DELETE FROM Student WHERE Sno=2000012; 例 删除所有的学生选课记录。 DELETE FROM SC; 3.4 SQL查询语音 1.查询基本结构概述 2.查询子句概述 3.SELECT 列表 4.FROM子句 5.WHERE子句 3.4.1 查询基本结构概述 查询基本结构概述： SELECT 列表：指定列 FROM 指定表 WHERE 指定行 语义： Select 子句中的属性列表，对上述结果作投 影（ ）操作。 From 子句：对指定关系，作笛卡儿积（） Where 子句：进行选择（）运算，找出符合 条件的元组。 3.4.2 查询子句概述 SELECT 子句指定您要检索的列。 FROM 子句指定从中请求列的表。在所有从表中检索数据 的查询中都需要该子句。 ON 子句指定 FROM 子句中的表将如何进行连接。该子句 只用于多表查询。 WHERE 子句指定表中您要查看的行。 GROUP BY 子句用于集合数据。 HAVING 子句指定要在其上收集集合数据的行。 ORDER BY 子句对结果集中的行进行排序。 3.4.3 SELECT 列表 1从表中选择所有列 SELECT distinct * FROM table-expression 2从表中选择特定的列 SELECT dept_id, dept_name FROM department 3重命名查询结果中的列 SELECT column-name AS alias SELECT column-name alias 4查询结果中显示字符串 select the firstname is as “ ”,first_name from employees; 5计算 SELECT 列表中的值 SELECT first_name, salary - 10 AS “ salary after tax“ FROM employees; 3.4.4 FROM子句 直接使用表名 FROM table_name 使用相关名 您可以赋予表名一个相关名以减少需要键入的字符。 您可以在 FROM 子句中指派相关名，方法是在表名之 后键入该相关名，如下所示： SELECT d.dept_id, d.dept_name FROM Department d FROM 子句包括链接两个或多个表的表名 3.4.5 WHERE 子句 比较 WHERE salary 50000 范围 WHERE salary BETWEEN 40000 AND 60000 列表 WHERE station IN ( beijing, shanghai, jinan) 字符匹配 WHERE phone LIKE 415% 未知值 WHERE first_name is not NULL 组合 WHERE salary 50000 AND first_name like A% 3.5 SQL的基本语言元素 数据类型 数值常量 数据格式化 空值 3.5.1 数据类型 数据类型自动转换规则 INSERTUPDATE语句中,数据值按照字段的类型进行转 换 SELECT语句中,字段值按照定义的变量类型转换 比较字符数值与数字数值时,将字符数值转换为数字 比较字符数值与日期数值时,将字符数值转换为日期 赋值语句中,一般将等号右边的变量转换为左边的数据 类型 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.2 数值常量 字符常量 Hello ORACLE 09-MAR-98 数字常量 25 +6.34 0.5 25e-03 -1 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.4 NULL(空) Null:表明记录的字段中不包含任何值(空) 目前将等同于Null （ORACLE) 条件中的NULL If A is: Condition Evaluates to: 10 a IS NULL FALSE 10 a IS NOT NULL TRUE NULLa IS NULL TRUE NULL a IS NOT NULL FALSE 10 a = NULL UNKNOWN 10 a != NULL UNKNOWN 3.6 SQL的操作符 操作符介绍 一元操作符 二元操作符 多元操作符 算术运算符 连接运算符 组合操作符 3.6.1 算数运算符 运算符举例 + - SELECT * FROM WHERE quantity = -1; SELECT hire_date FROM employees WHERE convert(int,GETDATE() - hire_date,1) 365; * / UPDATE employees SET salary = salary * 1.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); SELECT col1+col2+col3+col4 “Concatenation“ FROM tab1; - Abcdef ghijkl 3.6.3 组合操作符 将两个或多个查询的结果组合到一个结果 集中 操作符 返回结果 UNION 组合查询的结果（不重复） UNION ALL 组合查询的全部结果 INTERSECT 组合查询的共有结果 MINUS对第一个查询取唯一记录， 对共有记录不返回 3.6.3 组合操作符 举例： create table a1(t1 char(1),t2 char(2); create table a2(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.7 SQL表达式 SQL表达式的应用范围 Select expr、where expr、having expr Order by expr insert 的values() 、update 的set expr SQL表达式的形式 简单表达式、复合表达式、CASE表达式 Datetime表达式、间隔表达式 变量表达式、表达式列表 3.7.1 SQL简单表达式 举例 Select Employees.first_name from Employees Select 1 from Employees where first_name = w Select any string from Employees where 3.7.2 SQL复合表达式 举例 (CLARK + SMITH) LEN(MOOSE) * 57 SQRT(144) + 72 3.7.3 CASE表达式 CASE表达式让你可以在SQL中使用 IFELSEEND IF 的逻辑 Example SELECT 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 salary = ALL ( 1400, 3000); 3.8.2 逻辑条件 逻辑类型Example NOTSELECT * 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.3 成员条件 类型Example INSELECT * 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 employees WHERE job_id NOT IN (PU_CLERK, SH_CLERK); 3.8.4 范围条件 类型Example not between X and y SELECT * FROM employees WHERE salary BETWEEN 2000 AND 3000; 3.8.5 NULL条件 类型Example IS NOTSELECT last_name NULL FROM employees WHERE salary IS NULL; 3.8.6 LIKE条件 举例： SELECT ename FROM emp WHERE ename LIKE A_L% ESCAPE ; 类型 X not LIKE y escape z 3.8.7 复合条件 通过（）、not 、and 、or 复合各类简单 的条件 Example Select first_name from employees Where first_name like m% and (salary = 2000 or department_id in (10,20); 3.9 功能函数 内部函数 单行函数 聚集函数 用户自定义函数 3.9 功能函数 常用的单行函数 数值函数 abs、sqrt、round、mod 字符函数 lower、upper、len、trim、substr 日期函数 Day,month,year,getdate,weekday,today 转换函数 Convert、cast 3.9 功能函数 常用的聚集函数 求平均：AVG 总计：COUNT 最大值：MAX 最小值：MIN 求总和：SUM 3.10 SQL查询和子查询 1.SQL查询举例 2.SQL子查询 子查询简介 子查询的作用 WHERE子句中的子查询 FROM子句中的子查询 HAVING子句中的子查询 3.10.1 SQL汇总、分组与排序 SQL汇总： 应用了集合函数的每一组行生成一个值 举例： SELECT avg(salary + 1) FROM SELECT count(distinct id) FROM SELECT sum(salary) FROM SELECT max(salary) FROM 3.10.2 SQL汇总、分组与排序 SQL分组： GROUP BY 字句，将表的输出划分为若干个组 举例： SELECT employee_id,avg(salary) FROM employees GROUP BY employee_id; 3.10.2 SQL汇总、分组与排序 WHERE子句与HAVING子句： Where 决定哪些元组被选择参加运算，作用于关系中 的元组。 Having 决定哪些分组符合要求，作用于分组。 聚合函数的条件关系必须用Having，Where中不应出 现聚合函数。 举例： SELECT department_id, MIN(salary), MAX (salary) FROM emp