数据库创建

发表于 | 分类于 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
use Students;
drop table Student;
drop table Course;
drop table SC;
create table Student
(
sno char(8) not null primary key,
Sname char(15) null,
Ssex char(4) null,
Sage int null,
Sdept char(10) null
)

create table Course
(
Cno char(6) not null,
Cname char(10),
Cpno int null,
Ccredit int null,
primary key(cno)
)

go 
create table SC
(
Sno char(8) not null primary key,
Cno int null,
Grade int  null
)

insert into Student values('19130201','丁鹏','男',20,'Software')
insert into Student values('19130202','王韵婷','女',20,'Software')
insert into Student values('19130203','尹嘉琪','男',18,'Software')
insert into Student values('19130204','卢冬冬','男',20,'Software')
insert into Student values('19130205','史逸凡','男',19,'Software')

insert into Course values('1','数据库',5,4)
insert into Course values('2','数学',null,2)
insert into Course values('3','信息系统',1,4)
insert into Course values('4','操作系统',6,3)
insert into Course values('5','数据结构',7,5)
insert into Course values('6','数据处理',null,2)
insert into Course values('7','PASCAL语言',6,4)

insert into SC values('19130201',1,99)
insert into SC values('19130202',5,95)
insert into SC values('19130203',3,100)
insert into SC values('19130205',1,93)
insert into SC values('19130204',5,92)

select Sno as '学号',Sname as '姓名',Ssex as '性别',Sage as'年龄',Sdept as '所在系' from Student
select Cno'课程号',Cname'课程名',Cpno'先行课',Ccredit'学分' from Course
select * from SC

update Student set Sage=22 where sno='19130202'
delete from Student where sno='19130205'

///////////////////////////////////////////////////////

select * from student
alter table student add phone char(15) null,qq varchar(12)  //给表增加一个列
alter table student alter column phone int null //更改列的类型
execute sp_rename 'student.phone','phnum','column' //修改列名
alter table student drop column qq //删除表中的类
alter table student add constraint sex check (sex in('男'，'女'))//给列增加一个check约束
alter table student add constraint pk_student_sno primary key(sno)  //构建主键
alter table student alter column sno char(9) not null
alter table student drop constraint pk_student_sno //删除主键
create table spe(id char(6) primary kry,name char(10) null) //创建表格
sp_rename 'spe','special' //修改表名
1
2
3
4
5
6
7
8
9
10
11
12

select * from student
alter table student add phone char(15) null,qq varchar(12)  //给表增加一个列
alter table student alter column phone int null //更改列的类型
execute sp_rename 'student.phone','phnum','column' //修改列名
alter table student drop column qq //删除表中的类
alter table student add constraint sex check (sex in('男'，'女'))//给列增加一个check约束
alter table student add constraint pk_student_sno primary key(sno)  //构建主键
alter table student alter column sno char(9) not null
alter table student drop constraint pk_student_sno //删除主键
create table spe(id char(6) primary kry,name char(10) null) //创建表格
sp_rename 'spe','special' //修改表名

##1 建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
create database Students
on
primary(name=Students,
   filename='D:\test\test.mdf',
size=4mb,
maxsize=10mb,
filegrowth=2mb
  )
log on
(name=Studentslog,
filename='D:\test\testlog.ldf',
size=1mb,
maxsize=5mb,
filegrowth=1mb
)

use Students;


create table Student
(
sno char(8) not null primary key,
Sname char(15) null,
Ssex char(4) null,
Sage int null,
Sdept char(10) null
)


create table Course
(
Cno char(6) not null,
Cname char(10),
Cpno char(6),
Ccredit int null,
primary key(cno),
)

go 
create table SC
(
Sno char(8) not null foreign key references Student(sno),
Cno char(6) not null foreign key references course(cno),
primary key(Sno,Cno),
Grade int  null
)

insert into Student values('19130201','丁鹏','男',20,'Software')
insert into Student values('19130202','王韵婷','女',20,'Software')
insert into Student values('19130203','尹嘉琪','男',18,'Software')
insert into Student values('19130204','卢冬冬','男',20,'Software')
insert into Student values('19130205','史逸凡','男',19,'Software')

insert into Course values('1','数据库',5,4)
insert into Course values('2','数学',null,2)
insert into Course values('3','信息系统',1,4)
insert into Course values('4','操作系统',6,3)
insert into Course values('5','数据结构',7,5)
insert into Course values('6','数据处理',null,2)
insert into Course values('7','PASCAL语言',6,4)

insert into SC values('19130201',1,99)
insert into SC values('19130202',5,95)
insert into SC values('19130203',3,100)
insert into SC values('19130205',1,93)
insert into SC values('19130204',5,92)

alter table course add  foreign key(cpno) references course(cno)

select * from Course;
select * from SC;
select * from Student;