1.使用 T-SQL 语句创建数据库
☠ 1.1在 Query Analyzer 工具下建立学生选课数据库。
♞ (1)打开SQL Server 2012
✾ (2)在SQL Server Management Studio 窗口中,单击工具栏上的新建查询按钮“新建查询”,打开查询编辑器
❋(3)在查询编辑器中输入创建选课数据库的T-SQL脚本代码
CREATE DATABASE 选课
ON PRIMARY
(
NAME = 选课_data,
FILENAME = 'E:\data\选课_data.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10%
)
LOG ON
(
NAME = 选课_log,
FILENAME = 'E:\data\选课_log.ldf',
SIZE = 5MB,
MAXSIZE = 20MB,
FILEGROWTH=5%
)
GO
♝ (4)单击工具栏“分析”按钮,语法无错误后,单击"执行"按钮,运行结果如下图所示
☂ (5)刷新“对象资源管理器”的“数据库”结点,就可以看到选课数据库了,如下图所示:
☠ 1.2定义基本表
❥ 数据库中各表的物理结构和要求如下
☃1.2.1学生表
① 主键:sno
② 索引:sname(升序)
③ check 约束:年龄大于 18
④ default 约束:性别默认为男
⑤ 更改学生表的结构,取消姓名不允许为空的约束
在查询编辑器中输入创建学生表的代码:
USE 选课
GO
CREATE TABLE 学生表(
学号 char(10) PRIMARY KEY,
姓名 char(8) NOT NULL ,
性别 char(2) NOT NULL DEFAULT '男',
年龄 smallint CHECK(年龄>18),
所在院系 char(30),
联系电话 char(13)
)
Go
--② 索引:姓名(升序)
CREATE INDEX IC ON 学生表(姓名 ASC)
GO
--⑤更改学生表的结构,取消姓名不允许为空的约束。
ALTER TABLE 学生表
ALTER COLUMN 姓名
char(8) NULL
GO
还是和创建数据库时的操作一样,“分析” → “执行” → “刷新”,就会看到就会看到新创建的学生表。
☆1.2.2课程表
① 主键:cno
② 索引: cno (升序)+ccredit(降序)
③ 更改课程表的结构,增加属性列教师 ctech(类型是 char)
在查询编辑器中输入创建课程表的代码:
CREATE TABLE 课程表(
课程编号 char(10) PRIMARY KEY,
课程名称 char(16) NOT NULL,
学分 smallint NOT NULL,
先行课 char(10)
)
GO
--②索引: 学号(升序)+学分(降序)
CREATE INDEX IC ON 课程表(课程编号,学分 DESC)
GO
--③ 更改课程表的结构,增加属性列教师 ctech(类型是 char )
ALTER TABLE 课程表
ADD 教师 char(5)
GO
还是和创建数据库时的操作一样,“分析” → “执行” → “刷新”,就会看到就会看到新创建的课程表。
❦1.2.3选课表
主键:sno+cno
在查询编辑器中输入创建选课表的代码:
CREATE TABLE 选课表
(
学号 char(10),
课程编号 char(10),
成绩 smallint,
PRIMARY KEY(学号,课程编号),
FOREIGN KEY(学号) REFERENCES 学生表(学号),
FOREIGN KEY(课程编号) REFERENCES 课程表(课程编号)
)
还是和创建数据库时的操作一样,“分析” → “执行” → “刷新”,就会看到就会看到新创建的选课表。
☠ 1.3使用 T-SQL 实现学生选课数据库的如下操作
❆1.3.1向学生选课数据库中的每个基本表中录入若干条记录
--学生表的录入*/
GO
INSERT INTO 学生表
VALUES
('110001','王华',DEFAULT,'20','院1','1111'),
('110002','张二',DEFAULT,'21','院2','1112'),
('110003','张三',DEFAULT,'22','院3','1113'),
('110004','张四',DEFAULT,'21','院4','1112'),
('110005','李萍',DEFAULT,'21','院5','1112'),
('110006','张五',DEFAULT,'21','管理学院','1112'),
('110007','张飞',DEFAULT,'21','院6','1112'),
('110008','张八',DEFAULT,'21','管理学院','1112'),
('110009','张九',DEFAULT,'21','管理学院','1112'),
('140001','小明',DEFAULT,'21','院7','1112')
GO
SELECT *
FROM 学生表
GO
-- 课程表的录入
GO
INSERT INTO 课程表
VALUES
('1001','JAVA_1','3','1002','李1'),
('1002','JAVA_2','1','1003','李2'),
('1003','数据库原理与应用','2','1005','李3'),
('1004','Python1','1','1003','李4'),
('1005','JAVA','2','1007','李5'),
('1006','JAVA_3','1','1005','李6'),
('1007','JAVA','2','1006','李7'),
('1008','JAVA','2','1007','李8'),
('1009','JAVA_6','1','1008','李9'),
('1010','JAVA','5','1009','李0')
GO
SELECT *
FROM 课程表
--选课表的录入
GO
INSERT INTO 选课表
VALUES
('110002','1001','10'),
('110002','1005','15'),
('110001','1003','60'), /*王华*/
('110005','1003','18'),
('110007','1008','60'),
('110008','1001','80'),
('110009','1006','60'),
('110002','1006','75'),
('110005','1007','40'),
('110004','1009','100')
GO
SELECT *
FROM 选课表
GO
♛1.3.2对学生选课数据库的数据增加、数据删除和数据修改操作。
⚘(1)查询每个学生的选课门数和平均成绩。
SELECT 学号 ,COUNT(*) AS 选课门数,AVG(成绩) AS 平均成绩
FROM 选课表
GROUP BY 学号 HAVING COUNT(*)>0 AND AVG(成绩)>0
GO
⚘(2)将数据库原理与应用课程的学分修改为 4。
GO
UPDATE 课程表
SET 学分='4'
WHERE 课程名称='数据库原理与应用'
GO
SELECT *
FROM 课程表
WHERE 课程编号='1003'
⚘(3)将姓名为“王华”的学生选修数据库原理及应用课程的成绩增加 5 分。
UPDATE 选课表
SET 选课表.成绩=成绩-5
FROM 学生表 ,选课表
WHERE 学生表.学号=选课表.学号 AND 姓名='王华'
GO
SELECT 学生表.学号,姓名,课程表.课程编号,课程名称,成绩
FROM (选课表 INNER JOIN 学生表 ON 选课表.学号=学生表.学号)
INNER JOIN 课程表 ON 选课表.课程编号=课程表.课程编号
AND 姓名='王华'
⚘(4)删除选课表中成绩低于 40 分的记录。
USE 选课
DELETE 选课表
FROM 选课表
WHERE 选课表.成绩 < 40
SELECT *
FROM 选课表
⚘(5)删除所有选修课程“JAVA”的选课记录。
DELETE 选课表
FROM 选课表,课程表
WHERE 选课表.课程编号=课程表.课程编号 AND 课程名称='JAVA'
SELECT *
FROM 选课表
⚘(6)将管理学院全体学生的成绩置为空。
UPDATE 选课表
SET 成绩 = NULL
WHERE 学号 IN(
SELECT 学号
FROM 学生表
WHERE
所在院系='管理学院'
)
SELECT 学生表.学号,姓名,课程编号,所在院系,成绩
FROM 选课表 INNER JOIN 学生表 ON 选课表.学号=学生表.学号
AND 所在院系='管理学院'
⚘(7)删除学生李萍的所有选课记录。
DELETE 选课表
FROM 选课表 ,学生表
WHERE 选课表.学号=学生表.学号 AND 姓名='李萍'
SELECT *
FROM 选课表