《数据库学习打卡》第3天(基本表的操作,视图篇)

《数据库学习打卡》第3天

一、基本表的定义、修改、删除

1、定义基本表(Create table <列名>)
Create table <列名>
(
<列名1><数据类型>[列级完整性约束]
<列名2><数据类型>[列级完整性约束]
<列名3><数据类型>[列级完整性约束]
)
①定义一个学生表，其中，学号为主码，姓名必须唯一且非空；
Create table Student
(
Sno Char(10) Primary key,
Sname Char(10) Unique Not Null,
Ssex char(2) check (age = ‘男’ or age = ‘女’) //性别只能取男或女
Sage SMALLINT, //或者数据类型为Int 长整数
Sdept Char(10)
)
②定义一个学生选课表，其中学号参照Student表的Sno，课程号参照Course的Cno；
Create table SC
(
Sno Char(10),
Cno Char(10),
Grade SMALLINT,
Primary key(Sno,Cno), //定义表级约束，设置Sno和Cno为主码
Foreign key(Sno) References Student(Sno),
Foreign key(Cno) References Course(Cno)
外键 (当前表的主码) 参照 被参照表(主码)
)
2、修改基本表(Alter Table <表名>)
Alter Table <表名>
ADD [Column] <列名><数据类型>[完整性约束] //增加一列属性
ADD <表级完整性约束> 例如ADD Unique(Cno) //Cno必须唯一
Drop [Column] <列名><数据类型>[Cascade] [Restrict] //删除一列属性
Drop Constraint <完整性约束名>[Cascade] [Restrict] //删除一个完整性约束，必须带Constraint。
Alter Column <列名><数据类型> //修改一列
注：Drop Column用于删除基本表中的一列， 如果指定了Caseade，则自动删除引用了该列的其他对象， 例如视图，如果指定Restrict，该列如果被其他对象引用，则RDBMS拒绝执行该操作。
3、删除基本表(Drop Table <表名>)
Drop Table <表名> [Caseade][Restrict]
注：Drop Table用于删除整个基本表，如果指定了Caseade子句，则删除时，与该表关联的视图也会被删除，如果指定了Restrict子句，则如果存在其他约束，或触发器，存储过程等，则拒绝执行删除操作。

二、索引的定义和删除

建立索引的作用是提高数据的查询速度，和减少查询操作的时间
1、建立索引
Create [Unique][Cluster] Index <索引名>
On <表名>(<列名1>[次序],<列名2>[次序])
其中，<表名>是基本表的名字，在列名之后加ASC或DESC升序降序，索引可以建立在一列或多列上，中间用英文逗号分隔。

例1：为学生数据库的Student，Course，按升序建立唯一索引，为SC表中的Sno按升序建立唯一索引，Cno按降序建立唯一索引。
①Create Unique Index Stusno On(Sno ASC) //建立升序索引，升序的关键字可以省略
②Create Unique Index Coucno On(Cno ASC)
③Create Unique Index SCno On(Sno ASC,Cno DESC)
2、修改索引
Alter Index<旧索引名> Rename To <新索引名>
Alter Index SCno Rename To SCsno //把SC表的索引名修改成SCsno
3、删除索引
Drop Index<索引名>
Drop Index Stusno //删除表中的学号索引
删除索引时，同时会从数据字典中删除有关该索引的描述

三、视图

1、创建视图：
Create View <视图名>
As <子查询>

例1：建立一个信息系学生的视图Is_Student，要求进行插入、删除，更新操作时有信息系的学生。
Create view Is_Student
As Select Sno,Sname,age from S where Sdept = ‘信息系’
With check option //表示进行增删改操作时会附带where子句中的条件表达式。

例2：建立选修了数据库课程的学生的视图，名称为data_score，包含学号，姓名，成绩。
Create view data_score
As Select S.Sno 学号,Sname 姓名,Grade 成绩 from S,SC,C where S.Sno = SC.Sno And SC.Cno = C.Cno And Cname = ‘数据库’
//建立视图其实除了多了个 Create View <视图名> As 以外，As以后的条件表达式跟Select查询语句一样。

例3：建立信息系选修了1号课程的学生视图(包括学号、姓名、成绩)
Create view IS_S1(Sno,Sname,Grade) //视图名可以自己定义
AS Select **Student.**Sno,Sname,Grade from S,SC
where S.Sno = SC.Sno And
Sdept = ‘IS’ And Cno = ‘1’

例4：将学生的学号，平均成绩定义成一个视图S_G
Create view S_G
AS Select Sno,AVG(Grade) from SC Group by Sno
//定义成绩的同时，需要通过Group by 对学号进行分组

2、删除视图
Drop view <视图名> [CASCADE]

3、查询视图
例3：通过前面建立的IS_Student视图，查询信息系小于20岁的学生信息。
Select * from IS_Student where age<20

例4：查询平均成绩在90分及以上的学生学号和平均成绩
Select Sno,Avg(Grade) from SC Goup by Sno Having Avg(Grade)>=90

4、更新视图
Update <视图名>
As <修改为…>
where <条件表达式>

例5：将信息系学生视图IS_Student中学号为2012的学生姓名修改为“刘辰”
Update IS_Student<视图名>
As Sname = ‘刘辰’
where Sno = ‘2012’
等同于
Update Student
As Sname = ‘刘辰’
where Sdept = ‘信息系’ And Sno = ‘2012’

四、基本表的更新

数据更新
定义：数据更新有3种：向表中添加若干行数据，修改表中的数据，删除表中若干行数据。
1.基本表的插入操作
Insert Into <表名>(列1，列2，列3…)
Values(‘值1’,‘值2’,‘值3’)
例1：插入“95031”班学号为30、姓名为“郑和”的学生记录
Insert Into Student(Sno,Sname,Class)
Values(‘30’,‘郑和’,‘95031’)

2.基本表的修改操作
Update <表名>
Set <列名>=<表达式>
where <条件>
例2：将计算机系的所有学生的成绩设为0
Update SC
//注意：涉及到修改成绩，那么被修改的对象只能是在SC表中
Set Grade = 0
where Sno In(Select Sno from Student where Sdept = ‘计算机’)
例3：把学生王萍的名字改成王平
Update Student
Set Sname = ‘王平’
where Sname = '王萍

设有关系 EMP（ ENO，ENAME ，SALARY ，DNO ），其中各属性的含义依次为职
工号、 姓名、工资和所在部门号， 以及关系 DEPT（DNO ，DNAME ，MANAGER ），
其中各属性含义依次为部门号、部门名称、部门经理的职工号。

带子查询的修改语句
例3：把’销售部’工资低于600的职工工资上调10%
Update Emp
Set Salary = Salary * 10%
//一定需要将工资*10%后再复制给Salary工资。
where Eno In (2分)
(Select Eno from Emp,Dept where Emp.Dno = Dept.Dno
And Dname = ‘销售部’ And Salary<600)

3.基本表的删除操作
说明：Delete语句的功能是从表中删除满足where子句条件的元组，也就是说，Delete删除的是表中的数据，而不是表的定义。
Delete from <表名>
where <条件>
例 ：删除学生表中所有学生的数据
Delete from Student //删除学生表中所有的元组
例4：删除学号为2012的学生的记录
Delete from Student
where Sno = ‘2012’ //把学号为’2012’的学生整条记录删除
例5：删除所有学生的选课记录。
Delete from SC //使SC表成了空表，SC表中的所有元组都删除
带子查询的删除语句
例6：删除计算机科学系所有学生的选课记录SC
Delete from SC ②删除对应学号的学生的选课记录
//注意：涉及到选课记录，那么被删除的对象只能是在SC表中
where Sno In //①从S表中查询出系部为计算机的学生的学号
(Select Sno from S where Sdept = ‘计算机’)

四、授权：授予与收回

1.Grant(授予权限)的一般格式：
Grant<权限1>,<权限2>
On <对象类型> <对象名>
To <用户名1>[,<角色名2>]
With Grant Opation //指定该条子句后，则获得权限的用户可以将自己的权限授予给他人。
All Privileges //表示所有权限
Public //表示所有用户

**例1：**把查询学生表和修改学号的权限赋予给U1
Grant Selecf,Update(Sno)
On Table Student
To U1
对属性列授权时必须明确指出相应的属性名，例如Update(学号)。

**例2：**把学生表的所有权限(增删改查)赋予给用户U2，U2还可以将权限授予给其他人
Grant Privileges
On Table Student
To U2
With Grant Opation

2.Revoke(收回权限)的一般格式：
Revoke <权限1>,<权限2>
On <对象类型> <对象名>
From <用户名1>,<用户名2>
All Privileges //表示所有权限
Public //表示所有用户

**例1：**收回所有用户对学生表的查询权限及姓名的修改权限
Revoke Select,Update(Sname)
On Table Student
From Public
对属性列收回权限时必须明确指出相应的属性名，例如Update(学号)。

**例2：**收回用户U3对学生表的所有权限
Revoke Privileges
On Table Student
From U3

2、创建数据库模式的权限
Grant和Revoke用于对用户授予或收回对数据的操作权限，而对创建数据库模式一类的数据库对象的授权由数据库管理员在创建用户时实现。
创建数据库模式(schema)的语法：
Create User [With][Connect | Resource | DBA]
1.Create User 时没有指定命令，则默认为Connect权限，拥有该权限的用户只能登陆数据库，由DBA向其授予一定权限。
2.拥有Resource权限的用户可以创建基本表和视图，但不能创建新的模式和创建新的用户。
3.用于DBA权限的用户是系统中的超级用户，可以创建新的用户、模式、基本表、视图等，DBA拥有对所有数据库对象的存取权限。
3、数据库角色
数据库角色是被命名的一组与数据库操作相关的权限，**角色是权限的集合,**角色也就是用于保存权限的一个对象。
1.首先创建角色
Create Role R1(角色名)
2.给角色授予学生表的查询，插入，修改权限：
Grant Select,Insert,Update
On Tbale Student
To R1(角色名)
3.将一个角色权限授予其他的角色或用户
把角色权限授予给用户王平，张明。
Grant R1
To 王平,张明(用户名)

4.角色权限的收回
将角色R1的查询权限从Student表中收回。
Revoke Select
On Student
From R1(角色名)

五、完整性约束条件的定义

Constraint