sqlserver的一些基本语法总结

sqq varchar(12),
semail varchar(20),
sbirthday datetime,
szzmm varchar(16),
stime datetime,
clsno int references clsinfo(clsno)
)
GO
--插入一条学生的信息
(
sno int primary key,
sname varchar(10),
ssex varchar(4) CHECK (ssex in('男','女','人妖'))
)
insert into students(sno,sname,ssex) values(2005670,'刘德华','男')
--创建一个自定义的数据类型
sp_addtype ul_tel 'varchar(11)','not null'
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,
insert into students values(2005671,'芙蓉姐姐','人妖')
insert into students values(2005222,'刘德华','未知')
insert into students values(2005223,'周杰伦','男')
insert into students values(2005225,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
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
--添加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)
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,
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
cname varchar(26) not null,
ctno int references teainfo(tno),
ctime int,
cscore int
)
--创建成绩表
create table scinfo
(
sno int references stuinfo(sno),
select clsname as '班级名称',clsnum as '班级人数' from clsinfo
select clsname '班级名称',clsnum '班级人数' from clsinfo
select clsname 班级名称,clsnum 班级人数 from clsinfo
--删除数据
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
alter table a add constraint pk_name primary key(aa)
--修改表的名称
sp_rename 'a','aaaaa','object'
--插入多行数据
create table ccc
(
aa int,
bb int,
cc int
)
tname varchar(20) not null,
ttitle varchar(20) null,
tsex varchar(6),
tphone int
)
GO
--创建课程信息表
create table curinfo
(
cno varchar(5) primary key,
alter table students add aa varchar(10) null
constraint bb_aa default '你好'
--增加约束到已经存在的列
alter table students add constraint un_address unique(sadrress)
--删除表
drop table students
--删除表的数据
delete students
select * from students
create table a
(
aa int,
bb int
)
alter table a alter column aa int not null
delete clsinfo
GO
--创建学生信息表
create table StuInfo
(
sno int primary key,
sname varchar(16) not null,
ssex varchar(2),
saddress varchar(30),
sphone int,
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
clsnum int
)
GO
--插入一条信息
insert into clsinfo(clsname,clsnum) values('百杰八班',16)
--查询信息
select * from clsinfo
select '班级名称'=clsname,'班级人数'=clsnum from clsinfo
--添加check约束到ssex列,限制这一列的内容只能是男、女或者人妖
ALTER TABLE students ADD CONSTRAINT ssx_check CHECK (ssex in('男','女','人妖'))
--alter命令修改表的结构
--增加地址列(新列)
alter table students add sadrress varchar(30)
--创建一个数据库
create database StuDB
GO
use StuDB
GO
--创建班级信息表
create table clsInfo
(
clsno int identity(1,1) primary key,
clsname varchar(20) not null,
bb int,
cc int
)