数据库SQL练习1-CREATE/DROP/ALTER_第1关:create/alter/drop 数据库

首先对SQL 语句简单的总结
1.SQL的动词：

2.数据定义：

（1）定义模式格式如下：CREAT SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]

（2）删除模式格式如下：DROP SCHEMA <模式名> <CASCADE|RESTRICT>
CASCADE(级联)：删除模式的同时把该模式中所有的数据库对象全部删除。
RESTRICT(限制)：仅当该模式下没有任何下属的对象时才能执行，如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。
（3）基本表的定义格式如下：CREATE TABLE <表名>
(<列名> <数据类型>[ <列级完整性约束条件> ]
[,<列名> <数据类型>[ <列级完整性约束条件>] ]
…
[,<表级完整性约束条件> ] );
<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。
(4)修改基本表的格式：ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;
ADD用于增加新列、新的列级/表级完整型约束条件
DROP COLUMN用于删除表中列
DROP CONSTRAINT用于删除指定的完整型约束条件
ALTER COLUMN用于修改原有的列定义，包括列名和数据类型
（5）删除基本表格式：DROP TABLE <表名>［RESTRICT| CASCADE］;
3.在定义表的属性时需要指明其数据类型的长度。

经常用VARCHAR(n),它用于最大长度为n的变长字符串，当长度小于n时，申请的存储空间为实际的长度，节省了空间。
4.模式和表之间的关系：每一个表都属于某一个模式/架构，一个模式可以包含多个基本表、视图等对象。
服务器对象->数据库->架构->对象->表、视图
下面是针对以上SQL语句的练习

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

在定义之前首先要检查数据库中是否存在用户WANG，如果不存在需要先创建该用户。由于我的数据库中没有，所以需要先创建用户WANG。
下面是创建用户WANG的过程：可参考以下链接：数据库使用SQL语句新建数据集及新建用户WANG
前提工作完成后，输入下面的语句后点击【执行】：

CREATE SCHEMA "S-T" AUTHORIZATION WANG;
1

结果显示以下界面：

在执行之前可以先点击【√】，检查一下是否有语法错误，在没有语法错误的前提下再做修改。
这是由于当前运行的数据库是master,应该把默认的数据库改成数据库SCHOOL，在SCHOOL数据库中才有用户WANG。

【例3.2】CREATE SCHEMA AUTHORIZATION WANG;
该语句没有指定<模式名>，<模式名>隐含为<用户名>。

CREATE SCHEMA AUTHORIZATION WANG;
1

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

首先说明，要想在自己的命名空间下建表，要么在创建模式语句中同时创建表TEST.TAB1，要么在表名中明显的给出模式名TEST.TAB3，要么设置所属的格式（DBMS中先设置模式，再建表），不然就会成为默认条件dbo.TAB2。
（1）

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

这是在TEST模式下建立的表TAB1，在建立模式之后紧跟着建新表，满足题目的要求。
（2）但是如果不加说明是在哪个模式下进行的，就会默认为在dbo的模式下建表，如下面的语句：

CREATE TABLE TAB2(COL1 SMALLINT,
		          COL2 INT,
			      COL3 CHAR(20),
				  COL4 NUMERIC(10,3),
				  COL5 DECIMAL(5,2)
				  );
1
2
3
4
5
6

产生的效果如下：

SQL server 中，默认用户为 dbo，在没有创建模式的情况下，默认的模式名为dbo，所以表名为dbo.*。
（3）也可以采用下面的方式，在已经建立了模式的条件下建立新表，对建立的表名用模式加以约束。

CREATE TABLE TEST.TAB3( COL1 SMALLINT
					   );
1
2

产生的效果如下：

【例3.4】DROP SCHEMA TEST CASCADE;
（1）删除模式TEST，同时该模式下定义的表也会被删除，

DROP SCHEMA TEST CASCADE;
1

会出现语法错误，说明：SQL Server不支持在DROP SCHEMA使用CASCADE
(2)可以先删除该模式下的基本表，在删除模式。（由于前面建立了三个基本表，所以三个表都要删除）

 DROP TABLE TEST.TAB1
 DROP TABLE dbo.TAB2
 DROP TABLE TEST.TAB3
DROP SCHEMA TEST;
1
2
3
4

【例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)
                     );
1
2
3
4
5
6

注意：需要在当前数据库的当前模式下执行，主属性不能为空。

【例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*/
                    );
1
2
3
4
5
6

本例说明参照表和被参照表可以是同一个表，如果是不同的表，要在同一个模式下面。
【例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*/
                );
1
2
3
4
5
6
7

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

ALTER TABLE Student ADD S_entrance DATE;
1

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

ALTER TABLE Student ALTER COLUMN SAGE INT;
1

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

ALTER TABLE Course ADD UNIQUE(Cname);
1

【例3.11】删除Student表。
(1)

DROP TABLE Student CASCADE;
1

这样写会出现语法错误，SQL Server 不支持使用CASCADE。
(2)需要先将SC表删除，在删除Student表，因为SC表中有外码，参照表Student.
基本表定义被删除，数据被删除,表上建立的索引、视图、触发器等一般也将被删除

DROP TABLE SC
DROP TABLE Student;
1
2

【例3.12】若表上建有视图，使用RESTRICT时表不能删除;使用CASCADE时可以删除表，视图也自动删除。
创建基于Student的视图IS_Student：

CREATE VIEW IS_Student      
    AS 
	    SELECT SNO,SNAME,SAGE
	    FROM  Student
    	    WHERE SDEPT='IS';
1
2
3
4
5

删除表时：
（1）限制

DROP TABLE Student RESTRICT; 
1

出现语法错误，该表下面有视图，使用RESTRICT不能全部删除，拒绝该删除语句的执行。
（2）级联
由于SQL Server不能使用，可以先将Student表下的视图IS_Student删除后，在删除Student 表。

DROP VIEW IS_Student
DROP TABLE Student ;
1
2

注意：1.在输入语句时，将之前输入的语句要先删除，否则就会再执行一遍之前的命令而出现错误。

2.在语句没写完之前不要有分号，否则会出现语法错误。

3.不要重复点击执行按钮，会因为已经存在了这个对象而出现错误。

4.SQL Server 删除时不能使用CASCADE,可以一步一步的倒着挨个删除。

5.在创建或删除一个表或模式后，到相应的级别点击刷新之后才能修改。