SQL的数据定义和数据控制功能.ppt

Database Principles and Applications YAN XING(邢延 ) （自学篇，必学） Ch6 The Database Language SQL I Outline SQL的数据定义功能 SQL的数据控制功能 3 SQL的数据定义功能 5 Functions(功能 ) of SQL Data definition（数据定义） DDL： Data Definition Language Definition， modifications of databases or tables Data manipulation(数据操纵 ) DML： Data Manipulation Language Data query： query, statistics, order, etc Data updating： inserting, deleting or modifying data Data control（数据控制） DCL： Data Control Language Grant(授权 ) 14 Data Type 数值型数据 SMALLINT 短整型 INTEGER, INT 整型 DECIMAL(p,q) 十进制数 FLOAT 浮点型（双字长） 字符型数据 CHARTER(n), CHAR(n) 定长字符型 VARCHAR(n) 变长字符型 特殊数据类型 GRAPHIC(n) 定长图形字符串 VARGRAPHIC(n) 变长图形字符串 日期时间型 DATE YYYY-MM-DD TIME HH.MM.SS TIMESTAMP 日期加时间 15 Constrain on column (列级完整性的约束条件 ) 针对属性值设置的限制条件 NOT NULL， NULL 非空，空 表示 “ 不详 ” 、 “ 含糊 ” 、 “ 无意义 ” 、 “ 数据丢失 ” 主要属性必须 NOT NULL UNIQUE 唯一性约束，不允许出现重复的属性值 16 DEFAULT 默认值 减少数据输入工作量 DEFAULTFOR CHECK 检查约束，通过约束条件表达式设置约束条件 CONSTRAINTCHECK() 17 Constrain On Table (表级完整性的约束条件 ) 涉及到关系中多个列的限制条件 UNIQUE 唯一性约束，如列组的值不能重复 PRIMARY KEY 定义主码 保证主码的唯一性和非空性 CONSTRAINTPRIMARY KEY () 18 FOREIGN KEY 外码和参照表约束 定义参照完整性 CONSTRAINTFOREIGN KEY () () 19 Example Create the tables of Student-Course database： Student(sid, name, age, sex, dept) Course(cid, cname, pre_course) Enrolled(sid, cid, grade) CREATE TABLE Student(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) ); sid name age sex dept 20 Student 20 Create the tables of Student-Course database： Student(sid, name, age, sex, dept) Course(cid, cname, pre_course) Enrolled(sid, cid, grade) CREATE TABLE Course(cid CHAR(5) PRIMARY KEY, cname CHAR(20), pre_course CHAR(5), ); cid cname Pre_course Course 21 例子 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, 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) ); sid cid grade 22 Modify Table（修改表） ALTER TABLE （ ADD(完整性约束 , n) DROP MODIFY(, n) ） ; 23 Add new column（加属性） ALTER TABLE Course ADD hours SMALLINT; cid cname Pre_course hours Course 24 Modify the name of a column（改属性名） ALTER TABLE Student MODIFY name CHAR(10); 注意防止数据丢失 sid name age sex dept 20 Student 25 Delete a column（删除属性） ALTER TABLE Student DROP age; sid name age sex dept 20 Student 26 Delete Table（删除表） DROP TABLE 基本表一旦被删除，表中的数据全部自动删除 基本表一旦被删除，由该表导出的视图（定义） 失效，应该也删除 28 Data Update（数据更新） Insert data（插入数据） Modify data（修改数据） Delete data（删除数据） 29 Insert Data 插入一条记录 INSERT INTO ( ) VALUES(. ); INSERT INTO Student VALUES(98010,Michael,20,M,CS); 30 插入子查询的结果集 INSERT INTO ( ) ; 如： 求每个系学生的平均年龄，并把结果存入数据库。 CREATE TABLE dept_AVEage(dept CHAR(20), AVE_age SMALLINT) INSERT INTO dept_AVEage SELECT dept, AVE(ALL age) FROM Student GROUP BY dept; 31 Modify Data UPDATE SET=,=, n WHERE; 32 Example1 Add 2 onto the ages of all the students UPDATE Student SET age=age+2; 33 Example2 Modify grade to 1.2*grade for the course named database UPDATE Enrolled SET grade= grade*1.2 WHERE cid=(SELECT cid FROM Course WHERE cname=Database) 34 Delete Data DELETE FROM WHERE; 35 Example Delete 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 dept=art; 36 38 Data Control 通过对用户使用权限的限制而保证数据安全的重 要措施 一般只有数据库管理员（ DBA） 有授权资格 39 授权 (Grant) 收权 (Revoke) 拒绝访问 (Deny) 一般， DBMS都有专用的 GUI工具来进行数据控 制 授权 系统授权 是对指定操作对象的指定操作权限授予指定的用户，其语法格式为 ： GRANT , ON TO | PUBLIC WITH GRANT OPTION 接受授权的用户可以是一个或多个具体用户，也可以是 PUBLIC，即全体用 户。 PUBLIC表示所有用户，如果指定了 WITH GRANT OPTION子句，则 获得某种权限的用户还可以把该权限再授予别的用户。如果没有指定 WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，但不能传 播该权限。 例：把对 Student表和 Course表的全部操作权限权限授予 zhao，并允许 zhao将该权限授予他人。 GRANT ALL PRIVILEGES ON Student,Course TO zhao WITH GRANT OPTION26 拒绝访问 例： DENY SELECT ON dbo.Student,Course TO zhao 若组或角色和它们的成员之间存在权限冲突，对于拒绝 访问操作来说，拒绝优先于允许，如组允许，用户拒绝 ，则该用户的相关操作被拒绝。 收权 向用户授予的权限可以由 DBA或其他授权者用 REVOKE语句收 回，其语法格式为： REVOKE ， ON FROM ， 例：把用户 zhao修改成绩的权限收回。 REVOKE UPDATE (Score) ON SC FROM zhao