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

1、3关系数据库标准语言SQL,电子信息工程系 王国庆,内容综述,SQL全称 ：Structured Query Language（结构化查询语言） SQL是关系数据库的标准语言。 SQL语言具有综合统一、高度非过程化、面向集合、简捷易学等优点。 SQL语言可以分为四大部分：数据定义、数据查询、数据操作、数据控制。 SQL语言的数据查询功能非常丰富。,目录,3.1: SQL概述 3.2: 数据定义 定义数据库、基本表、索引。 3.3: 查询 使用SELECT语句实现单表查询、多表查询、嵌套查询、集合查询。 3.4: 数据更新 3.5: 视图 3.6: 数据控制 3.7: 嵌入式SQL,3.1: S

2、QL概述,SQL全称 ：Structured Query Language（结构化查询语言）70年代中期，IBM公司的Sam Jose Research Laboratory（圣约瑟研究实验室）在研制System R关系数据库管理系统中研制并实现了SQL语言。 1986年10月，美国国家标准局ANSI采用SQL作为关系数据库管理系统的标准语言(ANSIX3.135-1986)。1987年国际标准化组织(ISO)通过了这一标准。SQL成为数据库领域的主流语言，很多DBMS产品都支持SQL语言。,SQL的特点：,综合统一 高度非过程化 面向集合的操作方式 操作的对象、结果是元组的集合 以同一种语法

3、结构提供两种使用方式 既是自含式语言，又是嵌入式语言 语言简捷，易学易用 核心命令简捷，接近口语，容易学习，容易使用,SQL的基本概念,SQL包含4个部分： 数据定义语言DDL(Data Definition Language ) 数据查询语言DQL(Data Query Language) 数据操纵语言DML(Data Manipulation Language) 数据控制语言DCL(Data Control Language) SQL支持关系数据库三级模式结构，其中内模式对应于视图、部分基本表，模式对应于基本表，内模式对应于存储文件。,3.2: 数据定义,SQL 的数据定义功能包括：定义表

4、、定义视图、定义索引。 定义表：create table, drop table ,alter table 定义视图：create view, drop view 定义索引：create index, drop index,使用CREATE TABLE 语句,CREATE TABLE 表名(属性列 数据类型 );定义基本表的属性时需要指明各属性的数据类型、长度及 与表相关的完整性约束条件。,Oracle支持的数据类型,Datatype Description VARCHAR2(size) Variable-length character data CHAR(size) Fixed-lengt

5、h character data NUMBER(p,s) Variable-length numeric data DATE Date and time values LONG Variable-length character data up to 2 gigabytes CLOB Single-byte character data up to 4 gigabytes RAW and LONG RAW Raw binary data BLOB Binary data up to 4 gigabytes BFILE Binary data stored in an external file

6、; up to 4 gigabytes,SQL Server2000支持的数据类型,Integers（整型）：bit、int、smallint、bigint、tinyint。 Decimal（小数）：decimal、numeric、float、real。 Money and small money（货币）：Money、small money Datetime and smalldatetime（日期）：Datetime、smalldatetime。 Character Strings（非Unicode字符串）：char、varchar、text。 Unicode Character Strin

7、gs（Unicode字符串）：nchar、nvarchar、ntext。 Binary Strings（二进制串）：binary、varbinary、image。 其它：cursor、sql variant,table,timestamp、uniqueidentifier。,定义完整性约束,NOT NULL 约束,EMP,EMPNO ENAME JOB . COMM DEPTNO 7839 KING PRESIDENT 107698 BLAKE MANAGER 307782 CLARK MANAGER 107566 JONES MANAGER 20.,UNIQUE 约束,DEPT,DEPTNO

8、 DNAME LOC - - -10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON,UNIQUE 约束,定义约束,SQL CREATE TABLE emp(2 empno NUMBER(4),3 ename VARCHAR2(10) NOT NULL,4 job VARCHAR2(9),5 mgr NUMBER(4),6 hiredate DATE,7 sal NUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2) NOT NULL);,SQL

9、CREATE TABLE dept(2 deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13),5 CONSTRAINT dept_dname_uk UNIQUE(dname);,PRIMARY KEY,DEPT,DEPTNO DNAME LOC - - -10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON,PRIMARY KEY,FOREIGN KEY,DEPT,DEPTNO DNAME LOC - - -10 ACCOUNTING

10、NEW YORK20 RESEARCH DALLAS.,PRIMARY KEY,EMP,EMPNO ENAME JOB . COMM DEPTNO 7839 KING PRESIDENT 107698 BLAKE MANAGER 30.,FOREIGN KEY,定义约束,SQL CREATE TABLE dept(2 deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13),5 CONSTRAINT dept_dname_uk UNIQUE (dname),6 CONSTRAINT dept_deptno_pk PRIMARY KEY(d

11、eptno);,SQL CREATE TABLE emp(2 empno NUMBER(4),3 ename VARCHAR2(10) NOT NULL,4 job VARCHAR2(9),5 mgr NUMBER(4),6 hiredate DATE,7 sal NUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2) NOT NULL,10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)11 REFERENCES dept (deptno);,练习：,例1：建立一个名为example的基本表,CREA

12、TE TABLE example(A_id smallint identity(1,1) primary key,A_desc varchar(50) not null default new position title not formalized yet, Min_a tinyint not null check (min_a = 10 ), Max_a tinyint not null check (max_a = 250)B as min_a+max_a),练习:,例2：建立一个名为publishers的基本表,CREATE TABLE publishers(pub_id char(

13、4) not null constraint upkcl_pubind primary key check (pub_id in (1389, 0736, 0877,1622,1756) or pub_id like 990-90-9), pub_name varchar(40) null,city varchar(20) null, state char(2) null, country varchar(30) null default(usa) ),练习：,建立一个名为student的基本表,CREATE TABLE student( Sno char(5) not null unique

14、, Sname char(20) unique, Ssex char(1),Sage int, Sdept char(15),使用ALTER TABLE 语句,ALTER TABLE 表名 ADD (属性列 数据类型 DEFAULT 表达式,属性列 数据类型.);,ALTER TABLE 表名 MODIFY (属性列 数据类型 DEFAULT 表达式,属性列 数据类型.);,增加属性列,DEPT30,EMPNO ENAME ANNSAL HIREDATE - - -7698 BLAKE 34200 01-MAY-817654 MARTIN 15000 28-SEP-817499 ALLEN 1

15、9200 20-FEB-817844 TURNER 18000 08-SEP-81 .,DEPT30,EMPNO ENAME ANNSAL HIREDATE - - -7698 BLAKE 34200 01-MAY-817654 MARTIN 15000 28-SEP-817499 ALLEN 19200 20-FEB-817844 TURNER 18000 08-SEP-81 .,JOB,New column,练习:,SQL ALTER TABLE dept302 ADD (job VARCHAR2(9); Table altered.,ALTER TABLE dept30 MODIFY (

16、ename VARCHAR2(15); Table altered.,练习:,SQL ALTER TABLE emp2 ADD CONSTRAINT emp_mgr_fk 3 FOREIGN KEY(mgr) REFERENCES emp(empno); Table altered.,SQL ALTER TABLE emp2 DROP CONSTRAINT emp_mgr_fk; Table altered.,SQL ALTER TABLE dept2 DROP PRIMARY KEY CASCADE; Table altered.,删除DEPT表中的 PRIMARY KEY 约束,同时删除相

17、应 EMP.DEPTNO 列的FOREIGN KEY 约束.,练习:,基于教材59页的关系表，作如下修改： 向student表增加“入学时间”列，数据类型为日期型 将年龄的数据类型改为短整型 删除学生姓名必须取唯一值的约束,使用Drop Table语句,SQL DROP TABLE dept30; Table dropped.,DROP TABLE student,建立和删除索引(1),建立索引是加快查询速度的有效手段。 索引可以建立在表的一列或多列上。 系统在存取数据时会自动选择合适的索引，用户不必选择。 聚集索引(cluster)指索引项的顺序与表中记录的物理顺序一致的索引组织。一个基本上

18、最多只能建立一个聚集索引。 唯一索引(unique)指索引中没一索引值只对应唯一的数据记录。,使用Create Index语句,CREATE INDEX UNIQUE / CLUSTER 索引名ON 表名(属性列.);,3.3: 查询,查询是数据库管理的核心操作。SQL语言提供了select语句进行数据库的查询。 Select语句的一般格式： SELECT ALL|DISTINCT. FROM , . WHERE GROUP BY HAVING ORDER BY ASC|DESC,SELECT语句可以实现:,选择,投影,Table 1,Table 2,Table 1,Table 1,连接,1.

19、单表查询,SELECT DISTINCT *, 目标列表达式 别名,. FROM 表名;,SELECT 指明输出什么列 FROM 指明从哪个表查询,基本的SELECT 语句:,选择全部列,DEPTNO DNAME LOC - - -10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON,SQL SELECT *FROM dept;,选择指定的列,DEPTNO LOC - -10 NEW YORK20 DALLAS30 CHICAGO40 BOSTON,SQL SELECT deptno, locF

20、ROM dept;,例: EMP表,使用算术运算符,SQL SELECT ename, sal, sal+300FROM emp;,ENAME SAL SAL+300 - - - KING 5000 5300 BLAKE 2850 3150 CLARK 2450 2750 JONES 2975 3275 MARTIN 1250 1550 ALLEN 1600 1900 . 14 rows selected.,定义列的别名,SQL SELECT ename AS name, sal salaryFROM emp;,NAME SALARY - - .,SQL SELECT ename “Name“

21、,sal*12 “Annual Salary“FROM emp;,使用字符连接运算符,SQL SELECT ename + job AS “Employees“FROM emp;,Employees - KINGPRESIDENT BLAKEMANAGER CLARKMANAGER JONESMANAGER MARTINSALESMAN ALLENSALESMAN . 14 rows selected.,SQL Server2000使用+进行字符连接运算 ORACLE使用|进行字符连接运算,使用字符连接运算符,Employee Details - KING is a PRESIDENT BLA

22、KE is a MANAGER CLARK is a MANAGER JONES is a MANAGER MARTIN is a SALESMAN . 14 rows selected.,SQL SELECT ename + + is a + +job AS “Employee Details“FROM emp;,消除重复行,SQL SELECT deptnoFROM emp;,DEPTNO -10301020 . 14 rows selected.,默认情况下的选择操作产生了重复行,消除重复行,SQL SELECT DISTINCT deptnoFROM emp;,DEPTNO -1020

23、30,使用DISTINCT参数消除重复行,练习:,如何对SQL Server2000的实例数据库Northwind实现以下查询:1：查询所有订单的详细信息2：查询所有订单的订购日期,Select from orders,Select orderid,orderdate from orders,练习:,3：查询所有订单的订购日期及预计的交货日期（假定通常交货期为30天）,Select orderid,orderdate,orderdate+30 from ordersSelect orderid,orderdate,预计的发货日期, orderdate+30 from ordersSelect

24、orderid,orderdate+30 as 预计的发货日期 from orders,练习：,基于教材59页的关系表创建查询： 查询全体学生的详细信息 查询全体学生的姓名、学号、所在系 查询全体学生的姓名及其出生年份 查询全体学生的姓名及其出生年份，并以“出生年份”为列名显示 查询选修了课程的学生学号,使用SELECT语句实现选择操作,使用WHERE短句来选取元组,SELECT DISTINCT *, 目标列表达式别名, . FROM 表名 WHERE 条件表达式;,WHERE短句跟在FROM短句的后面 WHERE短句中可以使用运算符，包括：关系运算符有、逻辑运算符、及一些特殊的运算符。,使

25、用WHERE短句来选取元组,SQL SELECT ename, job, deptnoFROM empWHERE job=CLERK;,ENAME JOB DEPTNO - - - JAMES CLERK 30 SMITH CLERK 20 ADAMS CLERK 20 MILLER CLERK 10,使用比较运算符,SQL SELECT ename, sal, commFROM empWHERE sal=comm;,ENAME SAL COMM - - - MARTIN 1250 1400,使用 BETWEEN 运算符,ENAME SAL - - MARTIN 1250 TURNER 150

26、0 WARD 1250 ADAMS 1100 MILLER 1300,SQL SELECT ename, salFROM empWHERE sal BETWEEN 1000 AND 1500;,使用 IN 运算符,SQL SELECT empno, ename, sal, mgrFROM empWHERE mgr IN (7902, 7566, 7788);,EMPNO ENAME SAL MGR - - - -7902 FORD 3000 75667369 SMITH 800 79027788 SCOTT 3000 75667876 ADAMS 1100 7788,使用 LIKE 运算符,使

27、用 LIKE 运算符可以进行字符串的匹配. % 代表任意长度（或长度为0）的字符串_ 代表任意单个字符,SQL SELECT enameFROM empWHERE ename LIKE S%;,.使用 ESCAPE 进行转义后，可以查找 “%”或“_”。,SQL SELECT enameFROM empWHERE ename LIKE _A%;,ENAME - JAMES WARD,使用 LIKE 运算符,SQL SELECT enameFROM empWHERE ename LIKE _A% escape ;,使用 IS NULL 运算符,检测是否为空（NULL）值,SQL SELECT e

28、name, mgrFROM empWHERE mgr IS NULL;,ENAME MGR - - KING,使用 AND 运算符,SQL SELECT empno, ename, job, salFROM empWHERE sal=1100AND job=CLERK;,EMPNO ENAME JOB SAL - - - -7876 ADAMS CLERK 11007934 MILLER CLERK 1300,SQL SELECT empno, ename, job, salFROM empWHERE sal=1100OR job=CLERK;,EMPNO ENAME JOB SAL - -

29、- -7839 KING PRESIDENT 50007698 BLAKE MANAGER 28507782 CLARK MANAGER 24507566 JONES MANAGER 29757654 MARTIN SALESMAN 1250 . 14 rows selected.,使用 OR 运算符,SQL SELECT ename, jobFROM empWHERE job NOT IN (CLERK,MANAGER,ANALYST);,ENAME JOB - - KING PRESIDENT MARTIN SALESMAN ALLEN SALESMAN TURNER SALESMAN W

30、ARD SALESMAN,使用 NOT 运算符,在优先顺序上，比较运算符最优先，逻辑运算符中NOT优先级第一，AND其次，OR最不优先。,ENAME JOB SAL - - - KING PRESIDENT 5000 MARTIN SALESMAN 1250 ALLEN SALESMAN 1600 TURNER SALESMAN 1500 WARD SALESMAN 1250,SQL SELECT ename, job, salFROM empWHERE job=SALESMANOR job=PRESIDENTAND sal1500;,可以使用括号来定义优先顺序。,ENAME JOB SAL

31、- - - KING PRESIDENT 5000 ALLEN SALESMAN 1600,SQL SELECT ename, job, salFROM empWHERE (job=SALESMANOR job=PRESIDENT)AND sal1500;,练习：,如何对SQL Server2000的实例数据库Northwind实现以下查询:6：查询所有订单中已经交货的订单的详细信息7：查询所有国家为“UK”的客户的详细信息,Select from orderswhere shippeddatedate(),Select * from customerswhere country=UK,练习：

32、,8：查询所有单价在20到25元之间的产品名称和价格9：查询所有类别为1，2和3的产品的名称和价格,Select productname,unitpricefrom products where unitprice between 20 and 25,Select productname,unitprice from products where categoryid in (1,2,3),练习：,10：查询所有以开头的客户的详细信息11：查询所有客户名第三个字母为的且国家为UK的客户的详细信息,Select * from customers where customerid like A%,

33、Select * from customerswhere customerid like _ _A% and country=UK,练习：,基于教材59页的关系表创建查询： 查询计算机系年龄在20岁以下的全体学生的名单 查询考试成绩不及格的学生的学号 查询年龄在20-23岁之间的学生的姓名、系别、年龄 查询IS、MA、CS系的学生的姓名和性别 查询所有性刘的学生的学号、姓名、性别 查询DB_design课程的课程号和学分 查询已经选修了课程但是成绩缺的学生学号和相应的课程号,ORDER BY 短语,使用 ORDER BY 短语来对查询结果排序 ASC: 升序, 默认 DESC: 降序,SQL

34、SELECT ename, job, deptno, hiredateFROM empORDER BY hiredate;,ENAME JOB DEPTNO HIREDATE - - - - SMITH CLERK 20 17-DEC-80 ALLEN SALESMAN 30 20-FEB-81 . 14 rows selected.,按降序排列结果,SQL SELECT ename, job, deptno, hiredateFROM empORDER BY hiredate DESC;,ENAME JOB DEPTNO HIREDATE - - - - ADAMS CLERK 20 12-

35、JAN-83 SCOTT ANALYST 20 09-DEC-82 MILLER CLERK 10 23-JAN-82 JAMES CLERK 30 03-DEC-81 FORD ANALYST 20 03-DEC-81 KING PRESIDENT 10 17-NOV-81 MARTIN SALESMAN 30 28-SEP-81 . 14 rows selected.,按结果列的别名排序,SQL SELECT empno, ename, sal*12 annsalFROM empORDER BY annsal;,EMPNO ENAME ANNSAL - - -7369 SMITH 9600

36、7900 JAMES 114007876 ADAMS 132007654 MARTIN 150007521 WARD 150007934 MILLER 156007844 TURNER 18000 . 14 rows selected.,按多列排序,SQL SELECT ename, deptno, salFROM empORDER BY deptno, sal DESC;,ENAME DEPTNO SAL - - - KING 10 5000 CLARK 10 2450 MILLER 10 1300 FORD 20 3000 . 14 rows selected.,使用集函数,集函数作用在一

37、组元组上,EMP,“maximum salary in the EMP table”,DEPTNO SAL - -10 245010 500010 130020 80020 110020 300020 300020 297530 160030 285030 125030 95030 150030 1250,MAX(SAL) -5000,使用集函数,SELECT 目标列表达式, 集函数(列名) FROM 表名 WHERE 条件表达式 ORDER BY 列名;,集函数包括: AVG 、COUNT 、 MAX 、MIN 、STDDEV 、SUM 、VARIANCE,AVG 和 SUM适用于数值型数据

38、,AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) - - - -1400 1600 1250 5600,SQL SELECT AVG(sal), MAX(sal),MIN(sal), SUM(sal)FROM empWHERE job LIKE SALES%;,MIN 和 MAX 适用于不同类型的数据,SQL SELECT MIN(hiredate), MAX(hiredate)FROM emp;,MIN(HIRED MAX(HIRED - - 17-DEC-80 12-JAN-83,COUNT 函数用于计数,COUNT(*) -6,SQL SELECT COUNT(*

39、)FROM empWHERE deptno = 30;,SQL SELECT COUNT(comm)FROM empWHERE deptno = 30;,COUNT(COMM) -4,忽略NULL值,创建数据分组,EMP,“EMP表中每一部门的平均工资”,DEPTNO SAL - -10 245010 500010 130020 80020 110020 300020 300020 297530 160030 285030 125030 95030 150030 1250,DEPTNO AVG(SAL)- -10 2916.666720 217530 1566.6667,GROUP BY 短语

40、,SELECT 目标列表达式, 集函数(列名) FROM 表名 WHERE 条件表达式 GROUP BY 分组条件表达式 ORDER BY 列名;,GROUP BY 短句将元组的集合分成了小组。,使用 GROUP BY 短语,GROUP BY 后的列不一定在SELECT中输出，,SQL SELECT AVG(sal)FROM empGROUP BY deptno;,AVG(SAL) - 2916.66672175 1566.6667,使用 GROUP BY 短语,SELECT后的列除非使用了集函数，否则必须包括在GROUP BY短语中,SQL SELECT deptno, AVG(sal)FR

41、OM empGROUP BY deptno;,DEPTNO AVG(SAL) - -10 2916.666720 217530 1566.6667,SQL SELECT deptno, job, sum(sal)FROM empGROUP BY deptno, job;,DEPTNO JOB SUM(SAL) - - -10 CLERK 130010 MANAGER 245010 PRESIDENT 500020 ANALYST 600020 CLERK 1900 . 9 rows selected.,按多列分组,按多列分组,EMP,“EMP表中不同部门不同职位的工资总和”,DEPTNO JO

42、B SAL - - -10 MANAGER 245010 PRESIDENT 500010 CLERK 130020 CLERK 80020 CLERK 110020 ANALYST 300020 ANALYST 300020 MANAGER 297530 SALESMAN 160030 MANAGER 285030 SALESMAN 125030 CLERK 95030 SALESMAN 150030 SALESMAN 1250,JOB SUM(SAL) - - CLERK 1300 MANAGER 2450 PRESIDENT 5000 ANALYST 6000 CLERK 1900 MA

43、NAGER 2975 CLERK 950 MANAGER 2850 SALESMAN 5600,DEPTNO - 10 10 10 20 20 20 30 30 30,对分组的结果进行筛选,“每一部门最高工资大于 $2900”,EMP,DEPTNO SAL - -10 245010 500010 130020 80020 110020 300020 300020 297530 160030 285030 125030 95030 150030 1250,DEPTNO MAX(SAL) - -10 500020 3000,HAVING 短语,使用 HAVING 短语对分组结果进行筛选 HAVING短语专门作用于分组，选择满足条件的分组 WHERE短语作用于整个基本表，选择满足条件的元组，不能用于选择分组。,