数据库系统概论（第5版）SQL代码

第一章 绪论

1.1 数据库系统概论

1.1.3 数据库系统的特点

使用数据库系统进行学生学籍管理

1.建表：

-- 建立STUDENT表
create table STUDENT
(
    Sno char(8),       --字符串数据类型，固定长度
    Sname char(10),     
    Ssex char(2), 
    Sage smallint,      --精确数值数据类型
    Major char(20)
);
-- create table char等可大写、可小写

-- 建立AWARD表
create table AWARD
(
    Sno char(8),
    Details varchar(2000)   --字符串数据类型，可变长度
);

2.插入信息

/*插入学生的
基本信息*/    --多行注释
insert into STUDENT
(   
    sno,
    sname,
    ssex,
    sage,
    major
)
values
(
    '20101',
    '史玉明',
    '女',
    20,
    '计算机'
)

--插入学生获得的奖励
insert into AWARD
(   
    sno,
    details
)
values
(
    '20101',
    '2011校奖学金，2012国家奖学金'
)

insert into STUDENT(sno,sname,ssex,sage,major)
values('19210','李明虎','男',21,'机械')

insert into STUDENT(sno,sname,ssex,sage,major)
values('19334','张翔','男',21,'化工')

insert into AWARD(sno,details)
values('19210','2012校优秀学生')

3.查询学生信息

select * from STUDENT   --查看STUDENT表里所有数据

select * from AWARD   --查看AWARD表里所有数据

--查看包含奖励信息的所有学生信息
select 
    A.*,
    B.Details
from 
    STUDENT A
left join
    AWARD B
on A.Sno=B.Sno

第三章 关系数据库标准语言SQL

3.3 数据定义

3.3.1 模式的定义与删除

1. 定义模式

在SQL中，模式定义语句如下：

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

在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT子句

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]

【例3.1】为用户WANG定义一个学生-课程模式S-T

CREATE SCHEMA “S-T” AUTHORIZATION WANG;

【例3.2】为用户WANG定义一个模式，不指定<模式名>，<模式名>隐含为<用户名>

CREATE SCHEMA AUTHORIZATION WANG;

【例3.3】为用户ZHANG创建了一个模式TEST，并且在其中定义一个表TAB1

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

1. 删除模式

在SQL中，模式删除语句如下：

DROP SCHEMA <模式名> <CASCADE|RESTRICT>;

CASCADE（级联）：删除模式的同时把该模式中所有的数据库对象全部删除

RESTRICT（限制）：如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。仅当该模式中没有任何下属的对象时才能执行。

【例3.4】删除模式ZHANG，同时该模式中定义的表TAB1也被删除

DROP SCHEMA ZHANG CASCADE;

1. MySQL中定义、删除数据库

定义数据库

CREATE DATABASE 数据库名;

删除数据库

DROP DATABASE 数据库名;

3.3.2 基本表的定义与删除

1. 定义基本表

定义基本表语句如下：

CREATE TABLE <表名>
      (<列名> <数据类型>[ <列级完整性约束条件> ]
      [,<列名> <数据类型>[ <列级完整性约束条件>] ] 
   …
      [,<表级完整性约束条件> ] );

<表名>：所要定义的基本表的名字

<列名>：组成该表的各个属性（列）

<列级完整性约束条件>：涉及相应属性列的完整性约束条件

<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

【例3.5】建立“学生”表Student。学号是主码，姓名取值唯一。

CREATE TABLE Student          
  (Sno   CHAR(9)  PRIMARY KEY,   /* 列级完整性约束条件,Sno是主码*/                  
   Sname CHAR(20) UNIQUE,       /* Sname取唯一值*/
   Ssex  CHAR(2),
   Sage  SMALLINT,
   Sdept CHAR(20)
  ); 

desc Student;-- 创建后查看属性

【例3.6】建立一个“课程”表Course。

CREATE TABLE  Course
  (Cno     CHAR(4)  PRIMARY KEY,    /*列级完整性约束条件，Cno为主码*/
   Cname   CHAR(40) NOT NULL,       /*列级完整性约束条件，Cname不能取空值*/
   Cpno    CHAR(4),                 /*Cpno的含义为先修课*/                        
   Ccredit SMALLINT,
   FOREIGN KEY (Cpno) REFERENCES Course(Cno)  
            /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
  );

desc Course;-- 创建后查看属性

【例3.7】建立一个学生选课表SC。

CREATE TABLE SC
   (Sno CHAR(9), 
    Cno CHAR(4),  
    Grade SMALLINT,
    PRIMARY KEY (Sno,Cno),
                 /* 主码由两个属性构成，必须作为表级完整性进行定义*/
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
                 /* 表级完整性约束条件，Sno是外码，被参照表是Student */
    FOREIGN KEY (Cno)REFERENCES Course(Cno)
                 /* 表级完整性约束条件， Cno是外码，被参照表是Course*/
   ); 

desc SC;-- 创建后查看属性

查看已添加数据表

 show tables;

4.修改基本表

修改基本表语句如下：

ALTER TABLE <表名>
[ADD[COLUMN] <新列名> <数据类型> [完整性约束]]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASCADE| RESTRICT]]
[DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ]]
[ALTER COLUMN <列名><数据类型>];

<表名>是要修改的基本表

ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件

DROP COLUMN子句用于删除表中的列

如果指定了CASCADE短语，则自动删除引用了该列的其他对象

如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列

DROP CONSTRAINT子句用于删除指定的完整性约束条件

ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型

【例3.8】向Student表增加“入学时间”列，其数据类型为日期型。

ALTER TABLE Student ADD S_entrance DATE;

不管基本表中原来是否已有数据，新增加的列一律为空值

【例3.9】将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

ALTER TABLE Student ALTER COLUMN Sage INT;

【例3.10】增加课程名称必须取唯一值的约束条件。

ALTER TABLE Course ADD UNIQUE(Cname);

5.删除基本表

删除基本表语句如下：

DROP TABLE <表名> [RESTRICT| CASCADE];

RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用。如果存在依赖该表的对象，则此表不能被删除。

CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除。

默认为RESTRICT

【例3.11】删除Student表。

DROP TABLE Student CASCADE;

基本表定义被删除，数据被删除；表上建立的索引、视图、触发器等一般也将被删除。

【例3.12】若表上建有视图，选择RESTRICT时表不能删除；选择CASCADE时可以删除表，视图也自动删除。

CREATE VIEW IS_Student             /*Student表上建立视图*/
AS 
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

DROP TABLE Student RESTRICT;       /*删除Student表*/
 --ERROR: cannot drop table Student because other objects depend on it
                           /*系统返回错误信息，存在依赖该表的对象，此表不能被删除*/
DROP TABLE Student CASCADE;        /*删除Student表*/
 --NOTICE: drop cascades to view IS_Student
                           /*系统返回提示信息，此表上的视图也被删除*/
SELECT * FROM IS_Student;
 --ERROR: relation " IS_Student " does not exist 

3.3.3 索引的建立与删除

1. 建立索引

建立索引语句如下：

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> 
ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);

<表名>：要建索引的基本表的名字

索引：可以建立在该表的一列或多列上，各列名之间用逗号分隔

<次序>：指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC

UNIQUE：此索引的每一个索引值只对应唯一的数据记录

CLUSTER：表示要建立的索引是聚簇索引

【例3.13】 为学生-课程数据库中的Student，Course，SC三个表建立索引。Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引

CREATE UNIQUE INDEX  Stusno ON Student(Sno);
CREATE UNIQUE INDEX  Coucno ON Course(Cno);
CREATE UNIQUE INDEX  SCno ON SC(Sno ASC,Cno DESC);

1. 修改索引

修改索引语句如下：

ALTER INDEX <旧索引名> RENAME TO <新索引名>

【例3.14】 将SC表的SCno索引名改为SCSno

ALTER INDEX SCno RENAME TO SCSno;

1. 删除索引

删除索引语句如下：

DROP INDEX <索引名>;

【例3.15】 删除Student表的Stusname索引

DROP INDEX Stusname;

3.4 数据查询

数据查询语句如下：

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句) [AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [ HAVING <条件表达式> ]]
[ORDER BY <列名2> [ ASC|DESC ]];

SELECT子句：指定要显示的属性列

FROM子句ÿ