SQL server 基本操作

一、数据表的管理与创建

--1创建"学生选课"数据库。 
--使用最简单的语句创建“学生选课”数据库。并切换到该库。 
--（1）创建库 
create database 学生选课

--（2）切换库
use 学生选课

-- 2 使用T-SQL命令创建表。 
--（1）创建 Teacher（教师）表。

create table Teacher
(
    Tno char(3) not null primary key,
    Tname Varchar(10) not null,
    Tsex Char(2),
    Sbirthday Date,
    Prof Varchar(6),
    Depart Varchar(10)

)
-- （2）创建 Student（学生）表。

create table Student
(
    Sno Char(5) not null primary key,
    Sname Varchar(10) not null,
    Ssex Char(2),
    Sage Tinyint
)
--（3）创建 Course（课程）表。

create table Course
(
    Cno Char(3) not null primary key,
    Cname Varchar(20) not null,
    Cpno Char(3),
    Credit Tinyint,
    Semester Tinyint,
    Tno Char(3)

)
--4）创建 SC（选课成绩）表

create table SC
(
    Sno Char(5) not null,
    Cno Char(3) not null,
    Grade int

)

--3 使用T-SQL命令管理表。 
--（1）使用系统存储过程查看课程表Course的详细信息。
sp_help Course

--（2）在课程表Course中删除学期和教工编号列(一条命令删除两列）。
alter table Course
drop column Semester,Tno

--（3）在学生表Student中，增加所在院系列 Sdept，数据类型为Varchar(10)
--允许为空（“允许为空”写NULL）。(自行练习一次添加多列）
alter table Student
add Sdept Varchar(10) null

--（4）在学生表Student中，将年龄列的数据类型改为Smallint
alter table Student
alter column Sage Smallint

--（5）在教师表Teacher中，将所在院系列Depart重命名为Tdept。
sp_rename 'Teacher.Depart','Tdept'

--4 使用T-SQL命令删除表。 
--删除教师表。
drop table Teacher

二、数据完整性

CREATE DATABASE 学生选课 

USE 学生选课 
CREATE TABLE Teacher 
(
    Tno CHAR(3) NOT NULL, 
    Tname VARCHAR(10) NOT NULL, 
    Tsex CHAR(2), 
    Sbirthday DATE, 
    Prof VARCHAR(6), 
    Depart VARCHAR(10) 
) 

--1 创建学生表，表结构如下，在Sno列创建列级主键。

create table Student 
(
    Sno Char(5) NOT NULL primary key, 
    Sname Varchar(10) NOT NULL, 
    Ssex Char(2), 
    Sage Tinyint, 
    Sdept Varchar(10)

 ) 

 --2 修改教师表结构，为Tno列添加主键约束。
alter table Teacher
add primary key(Tno)

--3 创建课程表，结构如下，在Cno列创建表级主键，名为 PK_course_cno 。

CREATE TABLE Course 
(
    Cno Char(3) NOT NULL,
    Cname Varchar(20) NOT NULL, 
    Credit Tinyint, 
    Semester Tinyint, 
    Tno Char(3),
    constraint PK_course_cno primary key(Cno)

 ) 
--4 创建选课表，结构如下，为sno和cno列添加联合主键名PK_SC_snocno，
--Sno列创建表级外键FK_SC_sno,Cno列创建表级外键FK_SC_cno。

create table SC
(
    Sno Char(5) NOT NULL,
    Cno Char(3) NOT NULL,
    Grade int ,
    constraint PK_SC_snocno primary key(Sno,Cno),
    Constraint FK_SC_sno foreign key(Sno) references Student(Sno),
    Constraint FK_SC_Cno foreign key(Cno)references Course(Cno)
 )

 --5 修改课程表，为Cname列添加唯一约束，名UQ_Course_cname。
alter table Course
add constraint UQ_Course_cname unique(cname)

--6 修改教师表，为Tsex列添加检查约束，名为CK_Teacher_tsex，性别取值只能为”男“或”女“。
alter table Teacher
add constraint CK_Teacher_tsex check(Tsex='男'or Tsex='女')

--7 修改学生表，为Sage列添加检查约束，名为CK_Student_sage，年龄取值范围为[12-45]。
alter table Student
add constraint CK_Student_sage check(Sage >= 12 and Sage <=45)

--8 修改课程表，为Credit列添加默认值约束，名为DF_course_credit，默认值为2。
alter table Course
add constraint DF_course_credit default 2 for Credit

--9 删除课程表中的默认值约束。
alter table Course
drop constraint DF_course_credit

--10 修改学生表，禁用Sage列的检查约束。
alter table Student
nocheck constraint CK_Student_sage

--11 修改学生表，启用Sage列的检查约束。

alter table Student
check constraint CK_Student_sage

三、数据操作

IF EXISTS (SELECT name FROM SYS.DATABASES WHERE name='学生选课')
    DROP DATABASE 学生选课
GO
CREATE DATABASE 学生选课
GO
USE 学生选课
-- DROP TABLE SC,course,student
-- DROP TABLE course
GO

--创建教师表
CREATE TABLE Teacher(
Tno CHAR(3) NOT NULL PRIMARY KEY,
Tname VARCHAR(10) NOT NULL,
Tsex CHAR(2),
Sbirthday DATE,
Prof VARCHAR(6),
Tdept VARCHAR(10)
)
GO

--创建学生表
CREATE TABLE Student(
Sno CHAR(5) NOT NULL PRIMARY KEY,
Sname VARCHAR(10) NOT NULL,
Ssex CHAR(2),
Sage TINYINT,
Sdept VARCHAR(10)
)
GO

--创建课程表
CREATE TABLE Course(
Cno CHAR(3) NOT NULL PRIMARY KEY,
Cname VARCHAR(20) NOT NULL,
Cpno CHAR(3),
Credit TINYINT,
Semester TINYINT,
Tno CHAR(3)
)
GO

--创建选课表
CREATE TABLE SC(
Sno CHAR(5) NOT NULL,
Cno CHAR(3) NOT NULL,
Grade INT
)
GO

-- 插入教师表数据
--SP_HELP Teacher
INSERT INTO Teacher 
VALUES ('104','李诚','男','1958/12/2','副教授','计算机系')
,('256','张旭','男','1969/3/12','讲师','数学系')
,('125','王萍','女','1972/5/5','助教','计算机系')
,('331','刘冰','女','1977/8/14','助教','信息系')
GO    
--SELECT * FROM Teacher

-- 插入学生表数据
--SP_HELP Student
INSERT INTO Student 
VALUES('95001','刘超华','男',22,'计算机系')
,('95002','刘晨','女',21,'信息系')
,('95003','王敏','女',20,'数学系')
,('95004','张海','男',23,'数学系')
,('95005','陈平','男',21,'数学系')
,('95006','陈斌斌','男',28,'数学系')
GO
--SELECT * FROM Student

-- 插入课程表数据
--SP_HELP Course
INSERT INTO Course 
VALUES('101','数据库','105',5,4,'125')
,('104','操作系统','106',1,2,'104')
,('105','数据结构','107',1,3,'104')
,('106','数据处理',NULL,1,2,'331')
,('107','C语言','106',3,1,'125')
GO
--SELECT * FROM Course WHERE Cpno IS NULL

-- 插入选课表数据
--SP_HELP SC
INSERT INTO SC 
VALUES('95001','101',87)
,('95001','201',76)
,('95001','301',79)
,('95001','104',80)
,('95001','105',81)
,('95001','106',82)
,('95001','107',67)
,('95002','101',89)
,('95002','201',81)
,('95004','101',83)
,('95004','201',56)
,('95005','101',89)
,('95006','101',54)
,('95006','201',77)
,('95010','101',56)
,('95013','301',80)
,('95013','105',90)
,('95014','201',NULL)
,('95015','201',NULL)
GO
--SELECT * FROM SC

--7.1 数据操作。 
--（1）向教师表插入一条记录：教师编号332，姓名王丽，性别女，出生日期1980年3月4日，职称助教，在信息系工作。

insert into Teacher
values ('332','王丽','女','1980/3/4','助教','信息系')

--（2）向课程表插入一条记录：课程编号108，课程名“Java”，先修课程为空，学分为3，第3学期开课，任课教师为空。
INSERT INTO Course 
VALUES ('108','Java',Null,3,3,Null)

--（3）将教师表中姓名为“王萍”的教师的职称改为“讲师”。
update Teacher
set Prof='讲师'
where Tname='王萍'

-- （4）将课程表中所有第2学期开设的课程的学分加1。
update Course
set Credit=Credit+1
where Semester=2

-- （5）从学生表中删除学号为“95006”的记录。
delete from Student
where Sno='95006'

-- （6）在选课表中删除所有成绩不及格的记录。
delete SC
where Grade<60