1、Database Principles and Applications YAN XING(邢延)（自学篇，必学）Ch6 The Database Language SQLI OutlinelSQL的数据定义功能lSQL的数据控制功能3SQL的数据定义功能的数据定义功能5Functions(功能功能)of SQLlData definition（数据定义）DDL：Data Definition LanguageDefinition，modifications of databases or tableslData manipulation(数据操纵)DML：Data Manipulation
2、LanguageData query：query,statistics,order,etcData updating：inserting,deleting or modifying datalData control（数据控制）DCL：Data Control LanguageGrant(授权)&revoke（收权）Data constraintsSQL数据定义数据定义 lSQL语言使用语言使用数据定义语言数据定义语言（DATA DEFINITION LANGUAGE，简称，简称DDL）实现其数据定义功能。）实现其数据定义功能。操作对象操作对象创建删除修改表表Create tableDrop
3、tableAlter table视图Create viewDrop view索引Create indexDrop index数据库数据库Create databaseDrop databaseAlter database定义数据库定义数据库l定义数据库包括定义数据库名、确定数据库文件及其大小、确认日志文件定义数据库包括定义数据库名、确定数据库文件及其大小、确认日志文件的位置和大小。定义数据库使用的位置和大小。定义数据库使用CREATE DATABASE语句，其语法格式语句，其语法格式为：为：CREATE DATABASE ON PRIMARY(NAME=,FILENAME=,SIZE=,MAX
4、SIZE=,FILEGROWTH=),n LOG ON(NAME=逻辑日志文件名逻辑日志文件名，FILENAME=操作日志文件路径和文件名操作日志文件路径和文件名 ,SIZE=文件长度文件长度),n FOR RESTORE 定义数据库定义数据库数据库定义语句中包括以下数据库定义语句中包括以下4个方面的内容：个方面的内容：l定义数据库名。定义数据库名。l定义数据文件。定义数据文件。在在ON子句中，子句中，PRIMARY短语指明主文件名（短语指明主文件名（.mdf）。）。NAME短语定义逻辑数据文件名。短语定义逻辑数据文件名。FILENAME短语定义物理数据文件的存储短语定义物理数据文件的存储位置
5、和文件名。位置和文件名。SIZE短语定义文件的大小，数据库文件最小为短语定义文件的大小，数据库文件最小为1MB，默认值，默认值为为3MB。MAXSIZE短语定义文件的最大空间。短语定义文件的最大空间。FILEGROWTH说明文件的增说明文件的增长率，默认值为长率，默认值为10%。可以定义多个数据文件，默认第一个为主文件。可以定义多个数据文件，默认第一个为主文件。l定义日志文件。定义日志文件。在在LOG ON子句中，子句中，NAME短语定义逻辑日志文件名。短语定义逻辑日志文件名。FILENAME短语定义日志文件的存储位置和文件名。短语定义日志文件的存储位置和文件名。SIZE短语定义日志文件短语定
6、义日志文件的长度。可以定义多个日志文件。的长度。可以定义多个日志文件。lFOR RESTORE子句。子句。用于重建数据库，该重建的数据库用于数据恢复操作用于重建数据库，该重建的数据库用于数据恢复操作。例例:创建数据库创建数据库“学生选课库学生选课库”，初始大小为，初始大小为4MB。CREATE DATABASE 学生选课库学生选课库ON PRIMARY(NAME=学生选课库学生选课库,FILENAME=C:SQLSERVERDATA学生选课学生选课.mdf,SIZE=4MB,MAXSIZE=6MB,FILEGROWTH=20%)2选择数据库选择数据库:数据库的选择使用USE命令，其语法格式为：
7、USE 3删除数据库删除数据库:删除数据库的语法格式为：DROP DATABASE 例例:将学生选课库删除。DROP DATABASE 学生选课库12Data DefinitionlTable definition（表的定义）13Syntax（句法）（句法）CREATE TABLE （,n ,n）;14Data Type数值型数据SMALLINT短整型INTEGER,INT整型DECIMAL(p,q)十进制数FLOAT浮点型（双字长）字符型数据CHARTER(n),CHAR(n)定长字符型VARCHAR(n)变长字符型特殊数据类型GRAPHIC(n)定长图形字符串VARGRAPHIC(n)变长
8、图形字符串日期时间型DATEYYYY-MM-DDTIMEHH.MM.SSTIMESTAMP日期加时间15Constrain on column(列级完整性的约束条件列级完整性的约束条件)l针对属性值设置的限制条件lNOT NULL，NULL非空，空表示“不详”、“含糊”、“无意义”、“数据丢失”主要属性必须NOT NULLlUNIQUE唯一性约束，不允许出现重复的属性值16lDEFAULT默认值减少数据输入工作量DEFAULTFORlCHECK检查约束，通过约束条件表达式设置约束条件CONSTRAINTCHECK()17Constrain On Table(表级完整性的约束条件表级完整性的约束
9、条件)l涉及到关系中多个列的限制条件lUNIQUE唯一性约束，如列组的值不能重复lPRIMARY KEY定义主码保证主码的唯一性和非空性CONSTRAINTPRIMARY KEY()18lFOREIGN KEY外码和参照表约束定义参照完整性CONSTRAINTFOREIGN KEY()()19ExampleCreate the tables of Student-Course database：Student(sid,name,age,sex,dept)Course(cid,cname,pre_course)Enrolled(sid,cid,grade)CREATE TABLE Student
10、(sid CHAR(5)NOT NULL UNIQUE,name CHAR(8)NOT NULL,age SMALLINT,sex CHAR(2),dept CHAR(20),DEFAULT C1 20 FOR age,CONSTRAINT C2 CHECK(sex IN(male,female);sidnameagesexdept20Student20Create the tables of Student-Course database：Student(sid,name,age,sex,dept)Course(cid,cname,pre_course)Enrolled(sid,cid,gr
11、ade)CREATE TABLE Course(cid CHAR(5)PRIMARY KEY,cname CHAR(20),pre_course CHAR(5),);cidcnamePre_courseCourse21例子例子Create the tables of Student-Course database：Student(sid,name,age,sex,dept)Course(cid,cname,pre_course)Enrolled(sid,cid,grade)CREATE TABLE Enrolled(sid CHAR(5),cid CHAR(5),grade SMALLINT,
12、CONSTRAINT C3 CHECK(grade BETWEEN 0 AND 100),CONSTRAINT C4 PRIMARY KEY(sid,cid),CONSTRAINT C5 FOREIGN KEY(sid)REFERENCES Student(sid),CONSTRAINT C6 FOREIGN KEY(cid)REFERENCES Course(cid);sidcidgrade22Modify Table（修改表）（修改表）ALTER TABLE（ADD(完整性约束,n)DROP MODIFY(,n)）;23lAdd new column（加属性）ALTER TABLE Cou
13、rse ADD hours SMALLINT;cidcnamePre_coursehoursCourse24lModify the name of a column（改属性名）ALTER TABLE Student MODIFY name CHAR(10);注意防止数据丢失sidnameagesexdept20Student25lDelete a column（删除属性）ALTER TABLE Student DROP age;sidnameagesexdept20Student26Delete Table（删除表）（删除表）lDROP TABLE l基本表一旦被删除，表中的数据全部自动删除l
14、基本表一旦被删除，由该表导出的视图（定义）失效，应该也删除28Data Update（数据更新）（数据更新）lInsert data（插入数据）lModify data（修改数据）lDelete data（删除数据）29Insert Datal插入一条记录lINSERT INTO()VALUES(.);INSERTINTO Student VALUES(98010,Michael,20,M,CS);30l插入子查询的结果集lINSERT INTO();如：求每个系学生的平均年龄，并把结果存入数据库。CREATE TABLE dept_AVEage(dept CHAR(20),AVE_age S
15、MALLINT)INSERTINTO dept_AVEageSELECT dept,AVE(ALL age)FROM StudentGROUP BY dept;31Modify DatalUPDATE SET=,=,n WHERE;32Example1lAdd 2 onto the ages of all the studentsUPDATE StudentSET age=age+2;33Example2lModify grade to 1.2*grade for the course named databaseUPDATE EnrolledSET grade=grade*1.2WHERE
16、cid=(SELECT cid FROM Course WHERE cname=Database)34Delete DatalDELETE FROM WHERE;35ExamplelDelete the information of the students in art department and the information of the courses enrolled by them.DELETE FROM Enrolled WHERE sid IN(SELECT sid FROM Student WHERE dept=art);DELETE FROM Student WHERE
17、dept=art;3638Data Controll通过对用户使用权限的限制而保证数据安全的重要措施l一般只有数据库管理员（DBA）有授权资格39l授权(Grant)l收权(Revoke)l拒绝访问(Deny)l一般，DBMS都有专用的GUI工具来进行数据控制授权授权l系统授权系统授权是对指定操作对象的指定操作权限授予指定的用户，其语法格式为是对指定操作对象的指定操作权限授予指定的用户，其语法格式为：GRANT,ON TO|PUBLIC WITH GRANT OPTIONl接受授权的用户可以是一个或多个具体用户，也可以是接受授权的用户可以是一个或多个具体用户，也可以是PUBLIC，即全体用，即
18、全体用户。户。PUBLIC表示所有用户，如果指定了表示所有用户，如果指定了WITH GRANT OPTION子句，则获子句，则获得某种权限的用户还可以把该权限再授予别的用户。如果没有指定得某种权限的用户还可以把该权限再授予别的用户。如果没有指定WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，但不能传子句，则获得某种权限的用户只能使用该权限，但不能传播该权限。播该权限。l例：把对例：把对Student表和表和Course表的全部操作权限权限授予表的全部操作权限权限授予zhao，并允许，并允许zhao将该权限授予他人。将该权限授予他人。GRANT ALL PRIVILE
19、GES ON Student,Course TO zhao WITH GRANT OPTION26拒绝访问拒绝访问l例：例：lDENY SELECT ON dbo.Student,Course TO zhao l若组或角色和它们的成员之间存在权限冲突，对于拒绝若组或角色和它们的成员之间存在权限冲突，对于拒绝访问操作来说，拒绝优先于允许，如组允许，用户拒绝访问操作来说，拒绝优先于允许，如组允许，用户拒绝，则该用户的相关操作被拒绝。，则该用户的相关操作被拒绝。收权收权l向用户授予的权限可以由向用户授予的权限可以由DBA或其他授权者用或其他授权者用REVOKE语句收语句收回，其语法格式为：回，其语法格式为：REVOKE，ON FROM，l例：把用户例：把用户zhao修改成绩的权限收回。修改成绩的权限收回。REVOKE UPDATE(Score)ON SC FROM zhao
