Ch3SQL语言01.

数据库原理与应用第三章SQL语言2011年3月学习目标本章要求：拿握对教据库的基本操作，并了解数据库管理糸统的基 本功能；熟练拿握SQL查询语句，并运用SQL语句兜成对数据库的 操作本章重6:简单查询、连揍查询、嵌套子查询、聚合查询SQL教据良义语言SQL教据更新语言本章难点、：连接查询、嵌套子查询、聚合查询内容概要3. 1SQL概述3. 2简单查询3. 3连接查询3. 4嵌冬孑番询3. 5聚合查询3. 6集合查询3. 7SQL 杳询一般格式3. 8SQL 数据是义语言3. 9SQL 救据更新语言3. 10视图本章小结3. 1 SQL概述3.1.1 SQL发展3.1.2 SQL特点 3.1.3 SQL查询基本概念3. 1. 1 SQL发展SQL语言是关糸数据库的标准语言，其经坊的发展阶段如下：SQL-86:第一个SQL标准.也称为SQL-1SQL-92：也称为SQL-2SQL-99:护展了适归、紐发.而向对象技术，也称为SQL-3SQL-2003:也称为SQL-4ISO/IEC 9075-1 : Framework CSQL/FrameworkJ ；ISO/IEC 9075-2：Foundation fSQLVFoundation J ；ISO/IEC 9075-3：Call Level Interface fSQL/CLU ;ISO/IEC 9075-4: Persistent Stored Modules (SQL/PSM 丿；ISO/IEC 9075-9: Management of External Data fSQL/MEDj ；ISO/IEC 9075-10：Object Language Bindings fSQL/OLBJ；ISO/IEC 9075-1 I : Information and Definition Schemas (SQLVSchemataJ；ISO/IEC 9075-1 3: Java Routines and Types Using the Java Programming LanguagefSQL7JRT)；ISO/IEC 9075-14：XML-Related Specifications fSQL/XMLJ ；3. 1. 1 SQL发展许多数据库厂商都支持SQL-92的绝丸多数标准，以及SQL-99和SQL2003的部分标准， 并对SQL语言进行了 护展， 并赋予SQL不同的名字Oracle：PL/SQLSybase. Microsoft SQL Server: Transact-SQLSQL语言由4部分组成救据定义语言DDL：定义教据库的逆辑结构救据操纵语言DML：对数据库的数煽进行检索和更新救据控制语言DCL：对教据库的对象进行授权、用户维护 、完整性规则定义和事务定义其他：嵌入式SQL语言和动态SQL语言，规定了SQL语言在 猪主语言中使用的规则。3e 1 2SQL特点综合统一SQL语言集数据定义语言DDL,数据掾纵语言DML和数据控制语TDCL的功能于一体，语言凤格统一，可以独立完成数据库生命 周期中的全部活动。数据结构的单一性决龙数据操作符的单一性，无论是实体还是实 体诃的联糸P均采用冋一种操作轩需度非过程化非关糸数据库模型的数据操纵语言是面向过程的语言： 完成禁项捺作请求时必须指走存取路径SQL语言进行数据操作，用户只需提出“做什么”,而不必描朗“念么做”面向集合的襟作方式SQL语言釆用集合操作方式，其操作对象、查找结果都是元组的 集合；非关糸数据模型采用的是面向诃录的操作方式，操作对象是一条 记录3e 1 2SQL特点同一种语出结杨提供两种彼用芳无 SQL 诸言阮是窗含式语言.又是丧入式语言，且蛊两种不同的使用方式 下.SQLvS T 的语法结构基本上是一玫的。作为自令式涪言，能够歿立的用于联机交互的使用方式.用户可以衣终 鶏織盘上应接线入 SQL令令对数据库进行捺作作为获人丸為言，SQL 语句能够渕入刊嘉级语言程序中，供程序员沒计 程序时使用。语言简洁，易学易用SQL 语言的动词SQL 功能动词数据定义CREATE, DROP, ALTER数据查询SELECT数据操纵INSERT, UPDATE DELETE数据控制GRANT, REVOKE3. 1. 3 SQL查询基本概念SQL语t支持关系数据库管理系统的三级模式结构，其中外模式对应视图和部分基本衣，模式对应基本表 ,内模式对应存储丈件3. 1. 3 SQL查询基本概念基本恚数据库中独立存在的表称为基本表；在SQL中一个关糸对应一个基本表，一个（或多个丿基本奁对应 一个存儲文件；一个表可以带若干素引，素引也存放在存儲文件 中。视图视图是指从一个或几个基本表（或视图丿中导出的表，是虚表， 只存放祝图的走义而不存放对应的数摒查询表查询表是查询结皋对应的表存赭文件存緖文件是指数据库中存放关糸的杨理文件，其逻辑结构组成了 关糸数据库的内棋无，其扬理结皋对用户是透朗的。3. 8 SQL数据定义语言数据库中的关系集合必须由数据；t义语t-DDL义 ,包括：教据库模式、关纟模式、每个属性的值城、完整性约束、毎个关糸的素引集合和关糸的杨理存储 结构等。 SQL数据定义语言包括数据库的定义、修改和刪除基本表的走义、修改和刪除视图的走义、修改和刪除素引的皮义、修改和刪除3. 8 SQL数据定义语言 SQL数据定义撫作对象创建修改刪除数据库CREATE DATABASEALTER DATABASEDROP DATABASE表CREATETABLEALTERTABLEDROPTABLE视图CREATEVIEWALTER VIEWDROPVIEW索引CREATE INDEXDROP INDEX3. 8 1数据库的定义数据库与其对象之问的关糸3. 8. 1数据库的定义数据库的创建数据文件（data filej:存赭数据的文件可志文件flog filej:存赭可志的文件与该教据库相关的福述信息会存入列糸统的数据字典中在走义数据库的肘候，必须定义数据库的名字，送辑设备 名和炀理文件名。定义数据库操作的语法CREATE DATABASE ON ,. .n,.nLOG ON,.n其中： := PRIMARY (NAME=,数据逻辑设备3. 8. 1数据库的定义FILENAME =4,SIZE = .MAXSIZE = | UNLIMITED .FILEGROWTH =),.n := Fl LEG RO UP ,.n 3. 8. 1数据库的定义其中:彼创 建的数据库的名字，满足如下要求长度可以从I30,第一个字符必须是字母，或下划线或字符衣祈字符后的字苻可以是号母、救字或者前面规则中提到的步符9右称中不能有空格数据库的大小可以彼护展或者收缩ON :指定数据库中数据的该盘文件。其中it据文件，他们用逸号来隔需，用来之义主Cprimaryj送辑 役备中的数据文件；除了主送辑设务外，用户还可以定义 用户的送辑设备和相关用户丈件。3 & 1数据库的定义PRIMARY指走关联的vfilespec 列表走义主丈件。 主丈件组包含所有救据 库糸统表。还包含所有未指派给用户丈件组的对象。主丈件组的 第一个filespec条M成为主丈件，该丈件包含数据库的逻輯起 点及其糸统表。一个数堀库只能有一个主丈件。如黑没冇指走PRIMARY,叩么CREATE DATABASE语旬中列出的 第一个文件柠成为主丈件。LOG ON指定救据库目志的该盘丈件（日志丈件丿，其中vfilespec指ti志 丈件。如果没有指症.LOGON,糸统将动创建单个的可志丈件3. 8. 1数据库的定义例3.1建立学生成绩救据库ScoreDB CREATE DATABASE ScoreDB ON(NAME=ScoreDB,FILENAME二e:SQLDatabaseScoreDB.mdf,SIZE=2,MAXSIZE=IO,FILEGROWTH=I)3. 8. 1数据库的定义 LOG ON(NAM E=ScoreLog,FILENAME二e:SQLDatabaseScoreLog.ldf,SIZE=I,MAXSIZE=5,FILEGROWTH=I)3. 8. 1数据库的定义例3.2建立一个复杂的数据库M ylempDBCREATE DATABASE MylmpDBONPRIM AR -主股备(NAME=_empDev, FILENAME=td:empData7empDev.mdf,SIZE=5,FILEGROWTH=2),FILEGROUPTempHisDev,第一个用户沒等，仅包含I个文件(NAME= TempHisDev I,FILENAME=4d:TempDatalempHisDev|.mdf,SIZE=IO,FILEGROWTH=5),3. 8. 1数据库的定义 FILEGROUPTempBakDev2f第二个用户没备，包舍 3 个丈件 (NAME=TempBakDev 11FILENAME 三 d:TempDataTempBakDev l.mdf, SIZE=5tFILEGROWTH=2).FILEGROUPTempBakDev2.(NAME=TempBakDev2,FILENAME-d:TempDataTempBakDev2.mdfSIZE=5tFILEGROWTH=2),FILEGROUPTempBakDev3,(NAME=TempBakDev3,FILENAME 二 dTempDataTempBakDev3mdf：SIZE=5tFILEGROWTH=2).3. 8. 1数据库的定义 LOG ON(NAM E=Score Log,FILENAME二e:SQLDatabaseVfempLogDev I .Idf,SIZE=5,FILEGROWTH=I)(NAME=ScoreLog,FILENAME二e:SQLDatabaseScoreLog.ldf,SIZE=5,FILEGROWTH=2)3. 8. 1数据库的定义 LOG ON(NAM E=ScoreLog,FILENAME二e:SQLDatabaseScoreLog.ldf,SIZE=I,MAXSIZE=5,FILEGROWTH=I)3. 8. 1数据库的定义救据库的修改修叱数据库捺作的语法ALTER DATABASE ADD FILEvespecnTO FILEGROUP|ADD LOG FILEv 他 spec,.n|REMOVE FILE|ADD FILEGROUP|REMOVE FILEGROUP|MODIFY FILE|MODIFY FILEGROUP其中： :指走枚修改的救据库的名字；ADD FILE：指走添加列救据库中的故垢丈件；3. 8. 1数据库的定义TO FILEGROUP:指走文件潦加刊 丈件组名为 的丈件俎；3. 8. 1数据库的定义 ADD LOG FILE：指走添加到数据库中的可志文件； REMOVE FILE：从数据库糸统表中刪除该丈件，并杨 理刪除该文件； ADD FILEGROUP：指査海加到数据库的丈件组； :文件组的名称； REMOVE FILEGROUP：从数据库中刪除该丈件组，并 刪除在这个文件组中的丈件； MODIFYFILE：指定要修改的丈件，包含该丈件的名 称、大小、增长量和最丸家量。卜说朗：一次只可以修改其中的一个选项。3. 8. 1数据库白勺定义M3.2ALTER DATABASE MyfempDBMODIFY FILE(NAME=7empHisDevI,SIZE=20MB)3. & 1数据库白勺定义数据库的刪除刪除教据库肘，糸统会同肘从纟统的数据字典中将该数据 库的檔述一起刪除，有的数据库糸统还会自动刪除与教据 库相关联的杨理文件。 DROP DATABASE 3. & 2基本表的定义创建了数据库后，就可以在数据库中遣尤基本慕。通.过将基木 表与送辑沒务相关联，使得一个基本表可以放在一个杨理文件 上，也可以放在多个肠理文件上。SQL中的基本数据类型是：整型：int(4B)、smallint(2B), tinyinc(lB)；实型：float. real(4B).；字 符型：char(n). varchar(n). text；二 进制 型：binary(n)、varbinary(n). image；逻辑型：b,只能取0和I,不允许为空；货币型：money(8B, 4住小数).small money(4B, 2住小数)；肘间型：datetime(4B, I 753-01-01开始)smalldatetime(4B,1900-01 -01开始3. 8. 2基本表的定义创直基本表CREATETABLEdatabase_name. owner .| owner. table name裹七( I column_namc AS computcd_column_cxprcssion一列名AS丈义计 算列 做询表达式| := CONSTRAINT constraint namc | PRIMARY KEY | UNIQUE ,.n )ON filcgroup | DEFAULT杆对象放在粘定的11辑设务上，缺不主设备TEXTIMAGE_ON filegroup | DEFAULT 建仪：不柠帀户对兔建立应主匡轿役备上，主逻轿设备用来存放糸统做据。其中： := column namc data type COLLATE DEFAULT constantexpression | IDENTITY ( seed increment) NOT FOR REPUCATION ROWGUIDCOL .n 3. & 2基本表的定义v column_constraint := CONSTRAINT constraint_name NULL | NOT NULL一| PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT1| FOREIGN KEY REFERENCES rentable ( ref_column ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( logical_express ion )3. 8. 2基本表的定义 := CONSTRAINT constraint_name PRIMARY KEY | UNIQUE一 CLUSTERED | NONCLUSTERED ( column ASC | DESC t.n ) WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT | FOREIGN KEY ( column t.n )REFERENCES rentable ( ref.column t.n ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( search_conditions )3. 8. 2基本表的定义落用兜整性约柬主码约束：PRIMARY KEY难一性约束：UNIQUE非空值约東：NOT NULL（属于表结构，更改肘用ALTER TABLEALTERCOLUMN更改丿参照完整性约束PRIMARY KE丫与UNIQUE的区别？3. & 2基本表的定义例3.3建立ScoreDB中的4張表 CREATE TABLE Class(char(6)varchar(30)varchar(30) smallintdefault 0 tinyintCONSTRAINT CoursePKPRIMARY3. & 2基本表的定义CREATE TABLE CoursP (courseNochar(3)NOT NULL,courseNamevarchar(30)NOT NULL,creditHournumeric(l) default 0NULL,courseHourtinyint default 0NULL,priorCoursechar(3)NULL,CONSTRAINT CoursePK PRIMARY KEY(courseNo),CONSTRAINT CourseFK FOREIGN KEY(priorCourse)REFERENCE Course(courseNo)classNoclassNamcinstitutegradeclassNumNOTNULL,NOTNULL,NOTNULL,NOTNULL,NULL,KEY(classNo)3. 8. 2基本表的定义 CREATE TABLE Score(studentNo char(7)NOT NULL,courseNo char(3)NOT NULL,score numeric(5,1)default 0NOT NULLCHECK(score BETWEEN 0.0 AND 100.0),CONSTRAINT CoursePK PRIMARYKEY(studentNo,courseNo),CONSTRAINT CourseFK FOREIGN KEY(courseNo)REFERENCE Course(courseNo),CONSTRAINT CourseFK FOREIGN KEY(studentNo)REFERENCE Student(studentNo)3. & 2基本表的定义 CREATETABLE Stiidpnt (studentNochar(7)NOT NULL,stude ntZamevarchar(20)NOT NULL,sexchar(2)NULL,birthdaydatetimeNULL,nativevarchar(20)NULL,nationvarchar(30) defaultz汉族NULL,classNochar(6)NULL,CONSTRAINT StudentPK PRIMARY KEY(studentNo),CONSTRAINT StudentFKFOREIGN KEY(classNo)REFERENCE Class(classNo)说朗：以上4張表都缺苟了 OZfilegroup_name,数据存 菽在主 逻辑役务 上。3. & 2基本表的定义基本表的修孜 ALTER TABLE table ALTER COLUMN column_name new_data_typc ( precision . scale )数毎类型必须要有COLLATE v collation_name NULL | NOT NULL| ADD | DROP ROWGUIDCOL | ADD | column_name AS computed_column_expression . n | WITH CHECK | WITH NOCHECK ADD n | DROP CONSTRAINT constraintname | COLUMN column ,.n | CHECK | NOCHECK CONSTRAINT ALL | constraintname n | ENABLE | DISABLE TRIGGER ALL | triggername n 3. & 2基本表的定义 := column name data type DEFAULT constanjexpression WITHVALUES | IDENTITY ( seed , increment) NOT FOR REPLICATION ROWGUIDCOL COLLATE v column constraint .n 3. & 2基本表的定义v column_constraint :=CONSTRAINT constraint_name NULL | NOT NULLi | PRIMARY KEY | UNIQUECLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filcgroup | DEFAULT | FOREIGN KEYREFERENCES refutable ( ref_column )ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION (logical_expression ) 3 8. 2基本表的定义 :=CONSTRAINT con$crainc_name PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED ( column ,.n ) WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT J| FOREIGN KEY(column ,.n )REFERENCES ref cable ( ref column .n ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NOACTION NOT FOR REPLICATION | DEFAULT con$canc_cxpre$ionFOR column WITH VALUES | CHECK NOT FOR REPLICATION (search_conditions )3. 8. 2基本表的定义基本表的修改增加列ALTERTABLE ADD 剧除列ALTERTABLE DROP COLUMN 增加约束ALTERTABLE ADD CONSTRAINT 刪除约束ALTERTABLE DROP 修改列的数据类型ALTERTABLE ALTER COLUMN 3. 8. 2基本表的定义例3.4在ScoreDB_i&库中为Student裹增加一列ALTER TABLE StudentADD address varchar(50)例3.习在ScoreDB数据库中为Course表的CourseZame列 修改教 据类型ALTER TABLE CourseALTER COLUMN courseName varchar(40)例3.6$.ScoreDBL4库中为Class表增加唯一约束ALTERTABLE ClassADD CONSTRAINT UcName UNIQUE(className)例3.7在ScoreQB数据库中为Student表刪 除一列ALTER TABLE StudentDROP COLUMN address3. 8. 2基本表的定义卜基本表的刪除DROPTABLE DROPTABLE不能用于除去由FOREIGN KEY约束引用的表。 必须丸除去引用的FOREIGN KEY约束或引用的表。表所有者可以除去任何数据库内的表。 除去表肘， 表上的规 则或默认值将鮮除绑定，任何与表关联的约束或紐发彖将自 动除去。妇果重拚创建表，必须重新绑走适当的规则和默认值,重新创建任何紐发器并添加必要的约束。在糸统表上不能使用DROPTABLE语句。3. 8. 3索弓I的定义建立秦引是加快查询速度的有枚手段建立素引DBA或表的厲主（即建立表的人丿垠据需要建立有些DBMS如sql server2000会自动建立以下列上的素引 PRIMARY KEYUNIQUE维护素引DBMSij动完成使用素引DBMS自动选择是否使用素引以及使用哪些素引3. & 3索引的定义语句格式CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_nameON table | view ( column ASC | DESC . .n )WITH M.n ON filegroup v index_option := RADNDEX| FILLFACTOR = fillfactor | IGNORE_DUP_KEY |DROP_EXISTING | STATISTICS_NORECOMPUTE |SORTN_TEMPDBON table | view 指走要建素9的基本表或视图老字秦引可以建立怎该表的一刊或多列上.各刊毛之问用适号分隔用ASC | DESC 指走索引值的排列次序升序：ASC,吟序：DESC。缺省值: ASCUNIQUE 表朗此素引的每一个崇引值只对应难一的做据诃录CLUSTERED 表示要建立的索引是聚建崇引3. 8. 3索弓I的定义唯一值索引对于己含重复值的属性列不能建UNIQUE素引对荼个列建立UNIQUE索引后，插入新诃录时DBMS会自动检查新记录在该列上是否取了重复值。这相多于增加了一个UNIQUE约束3. & 3索引的定义聚簇素引建立聚簇崇引后，基表中数揚也需要按指走的聚 簇属性值的升序或睁序存放。也印聚簇奈引的崇 别项顺序与农中询录的畅理顺序一玫例3.8CREATE CLUSTERED INDEX sName ONStude nt(studentName)Student 裹的 Sname (姓名丿列上建立一个聚氛索弓 I,而且 Student中的记录将按 MSnameA 的升序存枚是否能成功？3. & 3索引的定义卷一个基本急上最多只能盘立一个聚簇素引聚簇素引的用途：对于禁些类型的姿询，可以提嵩查询效 率聚簇素引的适用范禹很少对基表进行增酬捺作很少对其中的雯长列进行修改操作3. & 3索引的定义例3旬衣Class表中按所属学院建立一个非聚集索引In stituteldx CREATE NONCLUSTERED INDEX Instituteldx ON Class(institute)卜例3.10在Student表中，首丸按班级编号的升序，然后 按出生可期的吟序建立一个非聚集索引ClassBirthldx CREATE NONCLUSTERED INDEX ClassBirthldx ON Student(classNo,birthday desc)3. & 3索引的定义刪除奈?iDROP INDEXv 表名.v 素引名；刪除素引肘，糸统会从救据字典中刪去有关该素引的福述。例3.1 I刪 除素引Instituteldx DROP INDEX Class.lnstituteldx