20170401- T-SQL 语句实训作业

实验目标: 进一步熟悉 T-SQL 语句
实验人: ZhangSiyang
实验内容: 完成老师给出十四道题,详细见下

目录

1. 创建名为stuDB的数据库…

题目要求:

创建名为stuDB的数据库(存储位置D:\sql),包含两个数据文件和一个日志文件:
第一个数据文件stuDB_data1初始大小为50MB,最大不受限制,按20%增长;
第二个数据文件stuDB_data2初始大小为50MB,最大200MB,按10MB增长;
日志文件stuDB_log1初始大小为10MB,最大100MB,按10MB增长。

代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create database stuDB
on ( name = stuDB_data1,
filename = 'd:\sql\stuDB_data1.mdf',
size = 50mb,
filegrowth = 10%,
maxsize = unlimited ),
( name = stuDB_data2,
filename = 'd:\sql\stuDB_data2.mdf',
size = 50mb,
maxsize =200mb,
filegrowth = 10mb)
log on
( name = stuDB_log1,
filename = 'd:\sql\stuDB_log1.ldf',
size = 10mb,
maxsize = 100mb,
filegrowth = 10mb );

2. 为数据库更名

题目要求

为数据库stuDB更名为studentDB

代码

1
2
alter database stuDB
modify name = studentDB;

3. 创建表的同时定义约束

问题要求

在studentDB数据库中创建如下两个数据表并定义约束:
(1)系信息表(系号,系名,系主任)
  其中:系号(字符型,2个字符,不能为空,主键)
        系名(字符型,最长30字符,不能为空)
        系主任(字符型, 8个字符)    
(2)学生信息表(学号,姓名,性别,出生日期,身份证号,系号)
  其中:学号(字符型,7个字符,主键)
        姓名(字符型,8个字符,不能为空)
        性别(字符型,2个字符,只能是"男"或"女",默认值为"男")
        出生日期(日期型)
        身份证号(字符型,18个字符,必须是18位,身份证号唯一)
        系号(字符型,2个字符,外键) 

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
use studentDB
create table 系信息
(
系号 char(2) not null primary key,
系名 varchar(30) not null,
系主任 char(8)
)
create table 学生信息
(
学号 char(7) primary key,
姓名 char(8) not null,
性别 char(2) check (性别= '男'or 性别= '女') default '男', --汉子等串要用单引号包起来用
出生日期 datetime,
身份证号 char(18) unique check (len(身份证号) = 18), --check (len(身份证号) = 18)
系号 char(2) foreign key
references 系信息(系号)
)

4. 为已有表添加列,并添加约束

题目要求

为学生信息表添加”入学成绩”列,并定义该列的数据取值必须在0到100之间

代码

1
2
3
use studentDB
alter table 学生信息
add 入学成绩 int check(入学成绩>=0 and 入学成绩<=100);

5. 删除学生信息表的”入学成绩”列

代码

1
2
3
use studentDB
alter table 学生信息
drop column 入学成绩;

6. 修改已有列的属性

题目要求

修改系信息表的”系名”列最长50个字符。

代码

1
2
3
use studentDB
alter table 系信息
alter column 系名 varchar(50);

7. 删除某表某列上的约束

题目要求

删除学生信息表的”性别”列的默认值约束

这道题看起来很唬人,其实知道约束名称直接删除就好了,不用理会在哪个表里
删除约束需要表名称[可用exec sp_helpconstraint查询]

代码

1
2
3
use studentDB
alter table 学生信息
drop constraint DF__学生信息__性别__3A81B327; --根据实际情况而改变,所以……最好一开始就定义约束的名字

8. 为某一列添加 Check 型约束,并指定名称

题目要求

为学生信息表的”性别”列定义默认值约束df_xb,定义该列默认值为”男”

代码

1
2
3
use studentDB
alter table 学生信息
add constraint df_xb default '男' for 性别;

9. 向表中插入数据

题目要求

为两个表中插入如下测试数据:
系信息表:
    系号    系名        系主任
    01    计算机系    张三丰
    02    外语系        NULL
    03    管理系        刘光荣
学生信息表:
    学号    姓名    性别    出生日期         身份证号        系号
    1601001    李丽    女    1990-11-22     130601199001010211         01
    1601002    赵明    男    1990-01-01     130601199011010212         01
    1602001    赵丽红    女    1991-12-11     130601199012010213         02

代码

1
2
3
4
5
6
7
8
9
use studentDB
insert into 系信息(系号,系名,系主任)
values (01,'计算机系','张三丰'),
(02,'外语系','NULL'),
(03,'管理系','刘光荣')
insert into 学生信息(学号,姓名,性别,出生日期,身份证号,系号)
values ('1601001','李丽','女','1990-11-22','130601199001010211',01),
('1601002','赵明','男','1990-01-01','130601199011010212',01),
('1602001','赵丽红','女','1991-12-11','130601199012010213',02)

10. 元组属性值的改变

题目要求

将学生信息表中学号为1601002的学生姓名改为”赵明明”,性别改为”女”

代码

1
2
3
4
use studentDB
update 学生信息
set 姓名 = '赵明明', 性别 = '女'
where 学号 = '1601002';

11. 为已有表添加一列,并给已有行添加值

题目要求

为系信息表添加”备注”列,且将所有系的该列的取值都设置为”无”

代码

1
2
3
use studentDB
alter table 系信息
add 备注 varchar(1026);

12. 问答:关于删除数据时要注意的

题目

(1)删除系信息表中系号为”01”的系。
(2)删除系信息表中系号为”03”的系。
 请问:在删除数据时你遇到的问题是什么?总结到的知识点有哪些?

回答

T-SQL 执行时不会字符串转为数字进行计算,因此,题目中的要求无法满足–废话,当然不会转成数字了,串是串,数是数。怎么能胡来;

进行删除时要确认好值的类型,要做到完全匹配。这样写,便可以删除了【约束等特殊情况除外】

这道题考察的也可能是单引号的使用

相关代码

1
2
3
4
5
delete from 系信息
where 系号 = '01' or 系号 = '03';
delete from 系信息
where 系号 = 1 or 系号 = 3;

13. 问答:删除表是的顺序重要吗?

题目:

依次删除以上创建的系信息表和学生信息表。若颠倒删除两个表的顺序会怎样?为什么?

回答

–正序删除提示
—-消息 3726,级别 16,状态 1,第 159 行
—-无法删除对象 ‘系信息’,因为该对象正由一个 FOREIGN KEY 约束引用。
–逆序删除提示:
—-命令已成功完成。

这时”references 系信息(系号)”引用了不存在的表,当然要出错。
–更基层原因:T-SQL 语句按行解释,一行一行得来

14. 问答:两个有关联的表,创建他们需要注意先后顺序吗?

题目

重新创建以上两个表。若颠倒创建两个表的顺序会怎样?为什么?

回答

–消息 1767,级别 16,状态 0,第 45 行
–外键 ‘FK学生信息系号__267ABA7A’ 引用了无效的表 ‘系信息’。
–消息 1750,级别 16,状态 1,第 45 行
–无法创建约束或索引。请参阅前面的错误。

–原因:这时”references 系信息(系号)”引用了不存在的表,当然要出错。
–更基层原因:T-SQL 语句按行解释,一行一行得来

另外的收获:

  1. 删除数据库要先跳转到别的库中进行,不然会一直提示正在使用的
  2. 什么时候要用 table 关键字,什么时候不能用真是令人搞不懂