Note01--Schema&Table&Constraint&View&Index&Query&Role&User&Trigger

最新推荐文章于 2022-04-12 13:17:40 发布

David_Hernandez

最新推荐文章于 2022-04-12 13:17:40 发布

阅读量459

点赞数

分类专栏： SQL 文章标签：数据库

本文链接：https://blog.csdn.net/kisslotus/article/details/54288958

版权

SQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

模式
定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，例如表，试图，索引等；

 CREATE SCHEMA <模式名> AUTHORIZATION <用户名>

如果没有指定<模式名>，那么<模式名>隐含为<用户名>；
要创建模式，调用该命令的用户必须有DBA权限，或者获得了DBA授予的CREATE SCHEMA权限；

CREATE SCHEMA "S-T" AUTHORIZATION WANG;
CREATE SCHEMA AUTHORIZATION WANG;

在CREATE SCHEMA中可以接受CREATE TABLE, CREATEA VIEW, GRANT 字句；

CREATE SCHEMA TEST AUTHORIZATION ZHANG
  CREATE TABLE TAB1(
    COL1 SMALLINT,
    COL2 INT,
    COL3 CHAR(20),
    COL4 NUMERIC(10，3),
    COL5 DECIMAL(5，2)
  );

删除模式
DROP SCHEMA <模式名> <CASCADE | RESTRICT>
其中，CASCADE和RESTRICT两者必选其一。
选择了CASCADE级联，表示在删除模式的同时把该模式中所有的数据库对象全部一起删除。
选择了RESTRICT限制，表示如果该模式中已经定义了下属的数据库对象（如表，试图等），则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行DROP SCHEMA语句。
DROP SCHEMA ZHANG CASCADE;
删除了模式ZHANG，同时，该模式中已经定义的表TAB1也被删除了。

2. 表

CREATE TABLE Student
(
    Sno char(9) primary key,
    Sname char(20) unique,
    Ssex char(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);
CREATE TABLE Course
(
    Cno char(4) primary key,
    Cname CHAR(40),
    Cpno CHAR(4),
    Ccredit smallint,
    foreign key (Cpno) references Course(Cno)
);
CREATE TABLE SC
(
    Sno char(9),
    Cno char(4),
    Grade smallint,
    primary key (Sno,Cno),
    constraint fk_sc_sno foreign key (Sno) references Student(Sno),
    constraint fk_sc_cno foreign key (Cno) references Course(Cno)
);
alter table <表名>
    [ADD <新列名> <数据类型> [完整性约束]]
    [DROP <完整性约束>]
    [ALTER COLUMN <列名> <数据类型>]
ALTER table Student ADD S_entrance DATE;
ALTER TABLE Student alter column Sage int;
alter table Course ADD unique(Cname);
DROP TABLE <表名> [RESTRICT|CASCADE];
DROP TABLE Student CASCADE;

约束

/*
    SQL 约束
约束用于限制加入表的数据的类型。
可以在创建表时规定约束（通过 CREATE TABLE 语句），
或者在表创建之后也可以（通过 ALTER TABLE 语句）。
我们将主要探讨以下几种约束：
    NOT NULL
    UNIQUE
    PRIMARY KEY
    FOREIGN KEY
    CHECK
    DEFAULT
*/
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
/*MySQL*/
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
);
/*SQL Server / Oracle / MS Access:*/
CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
/*
SQL PRIMARY KEY Constraint on CREATE TABLE
下面的 SQL 在 "Persons" 表创建时在 "Id_P" 列创建 PRIMARY KEY 约束：
MySQL:
*/
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
);
/*
SQL Server / Oracle / MS Access:
*/
CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
/*
如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，
请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
*/
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
);
/*
SQL PRIMARY KEY Constraint on ALTER TABLE
如果在表已存在的情况下为 "Id_P" 列创建 PRIMARY KEY 约束，请使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access:
*/
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P);
/*
如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，
请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
*/
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName);
/*
注释：如果您使用 ALTER TABLE 语句添加主键，
必须把主键列声明为不包含 NULL 值（在表首次创建时）。
撤销 PRIMARY KEY 约束
如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：
MySQL:
*/
ALTER TABLE Persons
DROP PRIMARY KEY;
/*SQL Server / Oracle / MS Access:*/
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID;
/*
SQL FOREIGN KEY Constraint on CREATE TABLE
下面的 SQL 在 "Orders" 表创建时为 "Id_P" 列创建 FOREIGN KEY：
MySQL:
*/
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
);
/*SQL Server / Oracle / MS Access:*/
CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
);
/*
如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，
请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
*/
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
);
/*
SQL FOREIGN KEY Constraint on ALTER TABLE
如果在 "Orders" 表已存在的情况下为 "Id_P" 列创建 FOREIGN KEY 约束，
请使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access:
*/
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P);
/*
如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，
请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
*/
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P);
/*
撤销 FOREIGN KEY 约束
如需撤销 FOREIGN KEY 约束，请使用下面的 SQL：
MySQL:
*/
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders;
/*SQL Server / Oracle / MS Access:*/
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders;
/*
SQL CHECK Constraint on CREATE TABLE
下面的 SQL 在 "Persons" 表创建时为 "Id_P" 列创建 CHECK 约束。
CHECK 约束规定 "Id_P" 列必须只包含大于 0 的整数。
My SQL:
*/
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
);
/*SQL Server / Oracle / MS Access:*/
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
/*
如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，
请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
*/
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
);
/*
SQL CHECK Constraint on ALTER TABLE
如果在表已存在的情况下为 "Id_P" 列创建 CHECK 约束，请使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access:
*/
ALTER TABLE Persons
ADD CHECK (Id_P>0);
/*
如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，
请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access:
*/
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes');
/*
撤销 CHECK 约束
如需撤销 CHECK 约束，请使用下面的 SQL：
SQL Server / Oracle / MS Access:
*/
ALTER TABLE Persons
DROP CONSTRAINT chk_Person;
/*MySQL:*/
ALTER TABLE Persons
DROP CHECK chk_Person;
/*
SQL DEFAULT Constraint on CREATE TABLE
下面的 SQL 在 "Persons" 表创建时为 "City" 列创建 DEFAULT 约束：
My SQL / SQL Server / Oracle / MS Access:
*/
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
);
/*
通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：
*/
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
);
/*
SQL DEFAULT Constraint on ALTER TABLE
如果在表已存在的情况下为 "City" 列创建 DEFAULT 约束，请使用下面的 SQL：
MySQL:
*/
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES';
/*SQL Server / Oracle / MS Access:*/
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES';
/*
撤销 DEFAULT 约束
如需撤销 DEFAULT 约束，请使用下面的 SQL：
MySQL:
*/
ALTER TABLE Persons
ALTER City DROP DEFAULT;
/*SQL Server / Oracle / MS Access:*/
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

视图
视图的作用：
（1）能够简化用户的操作
（2）使用户以多种角度看待同一数据
（3）对重构数据库提供了一定程度的逻辑独立性
（4）能够对机密数据提供安全保护
（5）可以更清晰的表达查询
DBMS执行create view语句的结果只是把视图的定义存入数据字典，并不执行其中的select语句。只是在对视图查询时，才按视图的定义从基本表中将数据查出。

create view is_student as 
    select sno,sname,sage from student where sdept = 'is';
create view is_student as 
    select sno,sname,sage from student where sdept = 'is' with check option;
/*
    with check option 表示对视图进行update，insert，delete操作时要保证更新，
    插入或删除的行满足视图定义中的谓词条件，即子查询中的条件表达式
*/
create view is_s1(sna,sname,grade) as
    select student.sno,sname,grade from student,sc
        where sdept = 'is' and student.sno = sc.sno and sc.cno = '1';
create view is_s2 as
    select sno,sname,grade from is_s1 where grade >= 90;
create view bt_s(sno,sname,sbirth) as
    select sno,sname,2004-sage from student;
create view s_g(sno,gavg) as
    select sno,avg(grade) from sc group by sno;
create view f_student(f_sno,name,sex,age,dept) as
    select * from student where ssex = '女';
drop view is_s1 cascade;
/*
更新视图是指通过insert、delete、update数据
*/
update is_student set sname = '刘辰' where sno = '200215122';
insert into is_student values('200215129','赵新',20);
insert into student(sno,sname,sage,sdept) values('200215129','赵新',20,'IS');
delete from is_student where sno = '200215129';

索引
加快查询速度的有效手段
索引：UNIQUE, CLUSTER
CLUSTER表示要建立的索引是聚簇索引。所谓聚簇索引是指索引的顺序与表中记录的物理顺序一致的索引组织。

create unique index Stusno on Student(Sno);
create unique index Coucno on Course(Cno);
create unique index Scno on SC(Sno ASC,Cno DESC);

删除索引

DROP INDEX <索引名>；
DROP INDEX Stusno;

查询
SELECT 目标列名|表达式
FROM 基本表名或视图序列
[WHERE 行条件表达式]
[GROUP BY 列名序列]
[HAVING 组条件表达式] //按照group之后的分组情况，再提取符合组条件的组
[ORDER BY 列名}[ASC|DESC]];

select Sno,Sname from Student;
select Sname,Sno,Sdept from Student;
select * from Student;
/*查询全体学生的姓名及其出生年份*/
select Sname,2016-Sage from Student;
/*查询全体学生的姓名，出生年份和所在的院系，要求用小写字母表示所有系名*/
select Sname,'Year of Birth' Birthday,2016-Sage,lower(Sdept) Department from Student;
/*如果没有指定distinct，则缺省为ALL，即暴力结果表中取值重复的行*/
select distinct Sno from SC;
select Sname from Student where Sdept='CS';
select Sname,Sage from Student where Sage<20;
select distinct Sno from SC where Grade<60;
select Sname,Sdept,Sage from Student where Sage between 20 and 23;
select Sname,Sdept,Sage from Student where Sage not between 20 and 23;
select Sname,Ssex from Student where Sdept in ('CS','MA','IS');
select Sname,Ssex from Student where Sdept not in ('CS','MA','IS');
select * from Student where Sno like '200215121' /*等价于select * from Student where Sno='200215121';*/;
/*%表示代表任意长度；_表示代表单个字符*/
select Sname,Sno,Ssex from Student where Sname like '刘%';
select Sname from Student where Sname like '欧阳_';
select Sname from Student where Sname like '_欧阳%';
select Sname,Sno,Ssex from Student where Sname not like '刘%';
select Cno,Credit from Course where Cname like 'DB\_Design';
select Sno,Cno from SC where Grade IS NULL;
select Sno,Cno from SC where Grade is not null;
select Sname from Student where Sdept='CS' and Sage<20;
select Sname,Ssex from Student where cno='3' order by Grade DESC;
select * from Student order by Sdept, Sage DESC;
/*
    聚合函数：
    count(*) 元组的个数, 
    count(列名) 统计一列中值得个数，
    SUM，
    AVG,
    MAX,
    MIN
*/
select count(*) from Student;
select count(distinct Sno) from SC;
select avg(Grade) from SC where Cno='1';
select max(Grade) from SC where Cno='1';
select sum(Ccredit) from SC, Course where Sno='200215012' and SC.Cno = Course.Cno;
select Cno,count(Sno) from SC group by Cno;
select Sno from SC group by Sno having count(*)>3;
select student.sno,sname,sage,sdept,cno,grade from student,sc where student.sno = sc.sno;
select c1.cno,c2.cpno from course c1,course c2 where c1.cpno = c2.cpno;
/*
    左外连接列出左边关系中的所有元组，右外连接列出右边关系中的所有元组。
*/
select s.sno,sname,ssex,sage,sdept,cno,grade from student left join sc on (student.sno = sc.sno);
select student.sno,sname from student,sc where student.sno = sc.sno and sc.cno = '2' and sc.grade > 90;
select student.sno,sname,cname,grade from student,sc,course where student.sno = sc.sno and sc.cno = course.cno;
select sname from student where sno in(select sno from sc where cno='2');
/*查询与“刘晨”在同一系学习的学生*/
select sno,sname,sdept from student where sdept in (select sdept from student where sname='刘晨');
select s1.sno,s1.sname,s1.sdept from student s1,student s2 where s1.sdept = s2.sdept and s2.sname = '刘晨';
select sno,sname,sdept from student s1 where exists (select * from student s2 where s2.sdept = s1.sdept and s2.sname='刘晨');
/*查询选修了课程名为“信息系统”的学生学号和姓名*/
select sno,sname from student where sno in (select sno from sc where cno in (select cno from course where cname='信息系统'));
select student.sno,sname from student,sc,course where student.sno = sc.sno and sc.cno = course.cno and course.cname = '信息系统';
/*找出每个学生超过他选修课程平均成绩的课程号*/
select sno,cno from sc x where grade >=  (select avg(grade) from sc y where y.sno = x.sno);
/*
> ANY   等价于 >= MIN
> ALL   等价于 > MAX
< ANY   等价于 < MAX
< ALL   等价于 < MIN
>= ANY  等价于 >= MIN
>= ALL  等价于 >= MAX
<= ANY  等价于 <= MIN
<= ALL  等价于 <= MAX
= ANY   
= ALL
!=(或<>)ANY
!=(或<>)ALL
*/
/*查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄*/
select sname,sage from student where sage < any (select sage from student where sdept = 'cs') and sdept <> 'cs';
select sname,sage from student where sage < (select max(sage) from student where sdept = 'cs') and sdept <> 'cs';
/*查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄*/
select sname,sage from student where sage < all (select sage from student where sdept = 'cs') and sdept <> 'cs';
select sname,sage from student where sage < (select min(age) from student where sdept = 'cs') and sdept <> 'cs';
/*查询所有选修了1号课程的学生姓名*/
select sname from student where exists (select * from sc where sc.sno = student.sno and cno = '1');
/*查询没有选修1号课程的学生姓名*/
select sname from student where not exists (select * from sc where sc.sno = student.sno and cno = '1');
/*
查询选修了全部课程的学生姓名
SQL语义：不存在一个学生没有选修所有课程
*/
select sname from student where not exists (
    select * from course where not exists (
        select * from sc where sno = student.sno and cno = course.cno));
/*
查询至少选修了学生200215122选修的全部课程的学号
SQL语义：不存在这样的课程y, 学生200215122选修了y，而学生x没有选
*/
select distinct sno from sc x where not exists (
    select * from sc y where y.sno='200215122' and not exists (
        select * from sc z where z.sno = x.sno and z.cno = y.cno));
/*查询计算机科学系的学生及年龄不大于19岁的学生*/
select * from student where sdept = 'cs' union select * from student where sage <=19;
/*查询选修了课程1或者课程2的学生*/
select sno from sc where cno = '1' union select sno from sc where cno = '2';
/*查询计算机科学系的学生与年龄不大于19岁的学生的交集*/
select * from student where sdept = 'cs' intersect select * from student where sage <=19;
/*查询既选修了课程1又选修了课程2的学生*/
select sno from sc where cno = '1' intersect select sno from sc where cno = '2';
select sno from sc where cno = '1' and sno in (select sno from sc where cno = '2');
/*查询计算机科学系的学生与年龄不大于19岁的学生的差集*/
select * from student where sdept = 'cs' except select * from student where sage <=19;
select * from student where sdept = 'cs' and sage > 19;
7. 角色、用户、触发器
grant select on table student to u1;
grant all privileges on table student,course to u2,u3;
grant all privileges on table student to pulbic;
grant update(sno),select on table student to u4;
grant insert on table sc to u5 with grant option;
grant insert on table sc to u6 with grant option;
grant insert on table sc to u7;
revoke update(sno)  on table student from u4;
revoke select on table sc from public;
revoke insert on table sc from u5 cascade;
/*
    新创建的数据库用户有三种权限：CONNECT, RESOURCE, DBA
    CONNECT用户：不能创建新用户，不能创建模式，也不能创建基本表，只能登陆数据库。
    RESOURCE用户：只能创建基本表和视图，成为新创建对象的属主；只拥有CREATE TABLE权限；
    DBA用户：系统中的超级用户；
*/
create role r1;
grant select,update,insert on table student to r1;
grant r1 to lotus1,lotus2,lotus3;
revoke r1 from lotus1;
grant delete on table student to r1;
revoke delete on table student from r1;
/*
当发生不一致时，可以采用以下的策略加以处理；
    1. 拒绝（NO ACTION）执行：不允许该操作执行。该策略一般设置为默认策略。
    2. 级联（CASCADE）操作：当删除或修改被参照表（student）的一个元组造成了与参照表（sc）的不一致，
        则删除或修改参照表中的所有造成不一致的元组。
*/
create table sc (
    sno char(9) not null,
    cno char(9) not null,
    grade smallint,
    primary key (sno,cno),
    foreign key (sno) references student(sno)
        /*当删除student表中的元组时，级联删除sc表中相应地元组*/
        on delete cascade
        /*当更新student表中的sno时，级联更新sc表中相应地元组*/
        on update cascade,
    foreign key (cno) references course(cno)
        /*当删除course表中的元组造成了与sc表不一致时拒绝删除*/
        on delete no action
        /*当更新course表中的cno时，级联更新sc表中相应地元组*/
        on update cascade
);
create table sc(
    sno char(9) not null,
    cno char(4) not null,
    grade smallint not null,
    primary key(sno,cno)
);
create table dept(
    deptno numeric(2),
    dname char(9) unique,
    location char(10),
    primary key(deptno)
);
create table student(
    sno char(9) primary key,
    sname char(8) not null,
    ssex char(2) check (sex in ('男','女')),
    sage smallint,
    sdept char(20)
);
create table sc(
    sno char(9) not null,
    cno char(4) not null,
    grade smallint check (grade >= 0 and grade <= 100),
    primary key (sno,cno),
    foreign key (sno) references student(sno),
    foreign key (cno) references course(cno)
);
/*当性别为男时，其名字不能以Ms.打头*/
create table student(
    sno char(9) not null,
    sname char(8) not null,
    ssex char(2),
    sage smallint,
    sdept char(20),
    primary key (sno),
    check (ssex = '女' or sname not like 'Ms.%')
);
create trigger insert_or_update_sal before insert or update on teacher
    for each row
    as begin
        if (new.job='教授') and (new.sal < 4000) then
            new.sal:= 4000;
        end if;
    end;
create trigger insert_sal
    after insert on teacher FOR EACH ROW
    as begin
        insert into sal_log values(new.eno,new.sal,current_user,current_timestamp);
    end;
create trigger update_sal
    after update on teacher for each row
    as begin
        if (new.sal<>old.sal) then
            insert into sal_log values(new.eno,new.sal,current_user, current_timestamp);
        end if;
    end;
drop trigger insert_sal on teacher;