数据库原理及应用开发技术-SQL.ppt

讲义三 关系数据库标准语言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）createalterdrop数据操作语言（Data Manipulation）insertupdatedelete数据查询语言select数据控制语言(Data Control)grantrolerevoke,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 删除操作DELETEFROM 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的数据定义语句,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_CSASSELECT*FROM StudentWHERE Sdept=计算机;,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 试图创建例 建立英语课的成绩在80分以上的学生的视图。CREATE VIEW English_Grade_80ASSELECT Sno,Sname,GradeFROM English_GradeWHERE Grade=80;,3.2.2 试图定义,3 删除视图当不再需要一个视图时，可以删除它，语句格式为：DROP VIEW 例 删除视图Student_CSDROP VIEW Student_CS;,3.3 SQL操作语言,1 插入操作2 修改操作3 删除操作,3.3.1 插入操作,1、插入操作插入语句的格式是：INSERTINTO(,)VALUES(,);,3.3.1 插入操作,例 将学生王林的信息插入到Student表中。INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage)VALUES(2000012,王林,男,计算机,19);INTO子句指定Student表和要赋值的列，VALUES子句对元组的各列赋值。,3.3.1 插入操作,例 在表Course中增加课程离散数学的信息。INSERTINTO Course(Cno,Cname,Cpno,Ccredit)VALUES(1136,离散数学,NULL,4);符号NULL的含义是赋予该列的值是空值。,3.3.2 修改操作,2、修改操作修改操作又称为更新操作，语句的一般格式是：UPDATE SET=,=WHERE;,3.3.2 修改操作,例 将学号为2000012的学生的年龄改为18岁。UPDATE StudentSET Sage=18WHERE Sno=2000012;例6 将所有学生的年龄增加1岁。UPDATE StudentSET Sage=Sage+1;,3.3.3 删除操作,3、删除操作 语句的一般格式为：DELETE FROM WHERE;,3.3.3 删除操作,例 删除学号为2000012的学生记录。DELETEFROM StudentWHERE Sno=2000012;例 删除所有的学生选课记录。DELETEFROM 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-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.4 FROM子句,直接使用表名FROM table_name使用相关名您可以赋予表名一个相关名以减少需要键入的字符。您可以在 FROM 子句中指派相关名，方法是在表名之后键入该相关名，如下所示：SELECT d.dept_id,d.dept_name FROM Department dFROM 子句包括链接两个或多个表的表名,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 数值常量,字符常量HelloORACLE09-MAR-98数字常量25+6.340.525e-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)条件中的NULLIf A is:Condition 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 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 exprOrder by expr insert 的values()、update 的set exprSQL表达式的形式简单表达式、复合表达式、CASE表达式Datetime表达式、间隔表达式变量表达式、表达式列表,3.7.1 SQL简单表达式,举例Select Employees.first_name from EmployeesSelect 1 from Employees where first_name=wSelect any string from Employees where,3.7.2 SQL复合表达式,举例(CLARK+SMITH)LEN(MOOSE)*57SQRT(144)+72,3.7.3 CASE表达式,CASE表达式让你可以在SQL中使用IFELSEEND IF 的逻辑ExampleSELECT sno,CASE WHEN grade 85 THEN high WHEN grade 70 THEN low ELSE medium END FROM SC;,3.7.4 间隔表达式,举例SELECT datediff(year,getdate(),scome)from student;SELECT year(getdate()year(scome)from student;,3.7.5 表达式列表,Examples1.Insert into exployees(first_name,last_name)values(Michael,Jordan);2.SELECT*FROM employees WHERE(first_name,last_name)IN(Guy,Himuro),(Karen,Colmenares);,3.8 SQL条件,SQL条件的应用范围Select 的WHERE 和 HAVING 子句Delete 和 update 的 WHERE 子句SQL条件的形式比较条件、逻辑条件、成员条件范围条件、NULL条件、LIKE条件复合条件,3.8.1 比较条件,条件类型Example,=,=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 逻辑条件,逻辑类型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.3 成员条件,类型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.4 范围条件,类型Examplenot between X and y SELECT*FROMemployees WHERE salary BETWEEN 2000 AND 3000;,3.8.5 NULL条件,类型Example IS NOTSELECT last_name NULL FROM employeesWHERE salaryIS NULL;,3.8.6 LIKE条件,举例：SELECT enameFROM empWHERE ename LIKE A_L%ESCAPE;,类型X not LIKE yescape z,3.8.7 复合条件,通过（）、not、and、or 复合各类简单的条件ExampleSelect 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 employees GROUP BY department_id HAVING MIN(salary)5000;,3.10.3 SQL汇总、分组与排序,SQL排序：对查询结果集的记录安装一个多个表达式排序升序：ASC;降序：DESC;默认：升序举例：SELECT*FROM employees ORDER BY first_nameSELECT*FROM employees ORDER BY department_id,first_name desc;,3.10.4 连接：从多个表查询数据,1连接:通过使用来自两个或多个表（或多个视图）的信息，重新创建更大的表。若要使连接有效，您必须组合每个表的正确的列。连接操作通过比较指定的列中的值来组合多个表中的行。,3.10.4 连接：从多个表查询数据,2连接方式:内连接：INNER JOIN外连接：OUTER JOINLEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOIN交叉连接:CROSS JOIN自然连接：NATURAL JOIN,3.10.4 连接：从多个表查询数据,3连接举例:(1)内连接：SELECT e.first_name,d.department_nameFROM employees e JOIN departments dON e.department_id=d.department_id;等价于：SELECT e.first_name,d.department_nameFROM employees e,departments dwhere e.department_id=d.department_id;,3.10.4 连接：从多个表查询数据,3连接举例:(2)外连接：SELECT e.employee_id,d.department_NAME FROM employees e LEFT JOIN department d ON e.department_id=d.department_id,3.10.4 连接：从多个表查询数据,3连接举例:(3)交叉连接：SELECT e.first_name,d.department_nameFROM employees e CROSS JOIN departments d;SELECT e.first_name,d.department_nameFROM employees e,departments d;,3.10.4 连接：从多个表查询数据,3连接举例:(4)自然连接：SELECT e.first_name,d.department_nameFROM employees e NATURAL JOIN departments d;,3.10.5 子查询,简介1.子查询的结构类似于常规查询，并且出现在主查询的 SELECT、FROM、WHERE 或 HAVING 子句中。2.由于单一的表查询无法满足现实系统业务的需要。子查询是满足该查询需求的一种解决方式。,3.10.5 子查询,使用1插入INSERT或CREATE TABLE语句的记录集2确定UPDATE SET子句的查询值3确定SELECT,UPDATE,DELETE语句中的Where、Having子句的值4定义一个表，并在查询中使用,3.10.5 子查询,INSERT INTO语句子查询复制表 employees 的数据到表employees1中INSERT INTO employees1SELECT employee_id,first_name,last_name,salary*1.1FROM employees,3.10.5 子查询,复制表语句子查询select*into employees1 from employees,子查询,UPDATE语句的SET子查询UPDATE employees dSET salary=salary+(SELECT avg(e.salary)FROM employees eWHERE e.department_id=d.department_id);,3.10.5 子查询,Where子句中的子查询SELECT first_name,salaryFROM employeesWHERE salary(SELECT avg(e.salary)from employees e);,3.10.5 子查询,定义一个查询表SELECT a.department_id Department,a.num_emp/b.total_count%_Employees,a.sal_sum/b.total_sal%_SalaryFROM(SELECT department_id,COUNT(*)num_emp,SUM(salary)sal_sumFROM employeesGROUP BY department_id)a,(SELECT COUNT(*)total_count,SUM(salary)total_salFROM employees)b;,总结,SQL是关系数据库的标准语言。从功能上可以划分为DDL（CREATE和DROP）、DML（INSERT、UPDATE、DELETE、SELECT）、DCL（GRANT和REVOKE）。2.查询语句SELECT是SQL中常使用的语句。由SELECT、FROM、WHERE、GROUP BY、HAVING和ORDER BY子句构成。SELECT和FROM子句在每个SQL语句中都必须出现，其它子句可以根据实际情况选用。,总结,3.SELECT语句的基本功能是经过横向和纵向操作，从一个表构造出另外一个新表，这个新表是查询的结果，是一个临时表。4.聚集函数的自变量的值不是单值，而是一个集合。SQL提供的聚集函数有COUNT、MAX、MIN、SUM、AVG。特别要注意的是，除了COUNT(*)函数以外，其它的聚集函数对空值忽略不计。,总结,5.分组是将在分组列上有相同值的元组分配到同一组。分组是聚集函数的作用对象，可以把同一组的所有元组，或者每个元组在某一列上的值在作为聚集函数自变量的值。6.连接操作是一个二元操作符，它将两个表中的元组首尾相连，形成新表的一个元组。连接操作有交叉连接、条件连接和外连接三类。,总结,7.SELECT语句的子句中出现了SELECT语句叫做嵌套查询或子查询。8.子查询可以出现在CREATE、INSERT、UPDATE和DELETE语句中。9.SQL语句可以直接使用也可嵌入到高级编程语言中使用,课堂练习,找出所在部门在“CHIKAGO”,各个不同职位的平均工资，且每个职位的人数在2人以上。,