--创建一个数据库
create database StuDB
GO
use StuDB
GO
--创建班级信息表
create table clsInfo
(
clsno int identity(1,1) primary key,
clsname varchar(20) not null,
clsnum int
)
GO
--插入一条信息
insert into clsinfo(clsname,clsnum) values('百杰八班',16)
--查询信息
select * from clsinfo
select '班级名称'=clsname,'班级人数'=clsnum from clsinfo
select clsname as '班级名称',clsnum as '班级人数' from clsinfo
select clsname '班级名称',clsnum '班级人数' from clsinfo
select clsname 班级名称,clsnum 班级人数 from clsinfo
--删除数据
delete clsinfo
GO
--创建学生信息表
create table StuInfo
(
sno int primary key,
sname varchar(16) not null,
ssex varchar(2),
saddress varchar(30),
sphone int,
sqq varchar(12),
semail varchar(20),
sbirthday datetime,
szzmm varchar(16),
stime datetime,
clsno int references clsinfo(clsno)
)
GO
--插入一条学生的信息
insert into stuinfo(sno,sname,saddress,clsno) values(2005341223,'苏州','成都市',1)
select * from stuinfo
delete stuinfo
--创建教师信息表
create table teainfo
(
tno int identity(1,1) primary key,
tname varchar(20) not null,
ttitle varchar(20) null,
tsex varchar(6),
tphone int
)
GO
--创建课程信息表
create table curinfo
(
cno varchar(5) primary key,
cname varchar(26) not null,
ctno int references teainfo(tno),
ctime int,
cscore int
)
--创建成绩表
create table scinfo
(
sno int references stuinfo(sno),
cno varchar(5) references curinfo(cno),
cscore int,
ctime datetime,
cpeople varchar(10),
constraint pk_studb_sc primary key(sno,cno)
)
GO
--单独的创建一张表,给这个表的主键取名字
create table bbb
(
id int constraint pk_studb_aaa primary key,
aname varchar(15) constraint aa_not not null
)
--查看表对象信息
sp_help stuinfo
--创建学生表
create table students
(
sno int primary key,
sname varchar(10),
ssex varchar(4) CHECK (ssex in('男','女','人妖'))
)
insert into students(sno,sname,ssex) values(2005670,'刘德华','男')
insert into students values(2005671,'芙蓉姐姐','人妖')
insert into students values(2005222,'刘德华','未知')
insert into students values(2005223,'周杰伦','男')
insert into students values(2005225,null,'男')
insert into students(sno,sname) values(2005667,'周星驰')
select * from students
delete students
alter table students alter column sname varchar(10)
alter table students add szip int
--添加check约束到szip列,限制这一列的内容只能是6位数字
ALTER TABLE students ADD CONSTRAINT szip_check CHECK (szip like '[0-9][0-9][0-9][0-9][0-9][0-9]')
GO
insert into students values(2005876,'芙蓉姐姐','人妖',566888)
--添加check约束到ssex列,限制这一列的内容只能是男、女或者人妖
ALTER TABLE students ADD CONSTRAINT ssx_check CHECK (ssex in('男','女','人妖'))
--alter命令修改表的结构
--
增加地址列(新列)
alter table students add sadrress varchar(30)
alter table students add sphone int
alter table students add sdefault int
alter table students add bb int
--增加一个带约束的列
ALTER TABLE students ADD szip int NULL
CONSTRAINT ck_szip check(szip like '[0-9][0-9][0-9][0-9][0-9][0-9]')
ALTER TABLE students ADD sweizhi VARCHAR(20) NULL
CONSTRAINT un_sweizhi UNIQUE
alter table students add aa varchar(10) null
constraint bb_aa default '你好'
--增加约束到已经存在的列
alter table students add constraint un_address unique(sadrress)
GO
alter table students add constraint ck_sphone check(sphone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
GO
alter table students add constraint de_sde default 0 for sdefault
GO
--使用alter方式修改字段的数据类型
alter table students alter column bb varchar(10)
alter table students alter column bb varchar(100)
alter table students alter column bb varchar(100) not null
--使用drop方式删除bb列
sp_help students
alter table students drop column bb
alter table students drop column sphone
alter table students drop constraint ck_sphone
--删除表
drop table students
--删除表的数据
delete students
select * from students
create table a
(
aa int,
bb int
)
alter table a alter column aa int not null
alter table a add constraint pk_name primary key(aa)
--修改表的名称
sp_rename 'a','aaaaa','object'
--插入多行数据
create table ccc
(
aa int,
bb int,
cc int
)
GO
--一次性插入一条数据
insert into ccc values(1,2,3)
insert into ccc values(4,5,6)
insert into ccc values(7,8,9)
select * from ccc
create table ccc2
(
aa int,
bb int,
cc int
)
GO
select * from ccc2
--一次性插入多条语句
insert into ccc2 select * from ccc
--自动创建新表,并插入所有数据
select * into ccc3 from ccc
select * from ccc3
--创建一个自定义的数据类型
sp_addtype ul_tel 'varchar(11)','not null'