sql 复习笔记（一）

1.创建数据库

create database STU
on primary
(
name ='STU',
filename ='E:\study\SQL\STU\STU.mdf',
size =5mb,
maxsize =20mb,//不设置的话文件可以一直增大到磁盘容量值
filegrowth =1
)
log on
(
name ='STU_log',
filename ='E:\study\SQL\STU\STU_log.ldf',
size =1mb,
maxsize =5mb,
filegrowth =1%
)

2.创建表

go
create table S
(
sno char(9) not null
constraint pk_sno primary key clustered,//这里添加主键，记得前面那句没有逗号
sname char(8) not null,
sex char(2) null,
age smallint null,
sdept varchar(50) null
)
go
create table C
(
cno char(4) not null,
cname varchar(50) not null,
cdept varchar(50) null,
Tname char(8),
primary key(cno) //添加主键的另一种方法
)
go
create table SC
(
sno char(9) not null,
cno char(4) not null,
grades real null, //real也是浮点数类型
foreign key(sno) references S(sno),//外键可以这样子添加
foreign key(cno) references C(cno)
)

3.修改表

use STU
go
alter table S
alter column sex char(1) null//修改列属性

go
alter table S
drop column sex
alter table s
add column birthday date//先删后增

use STU
go
alter table S
drop constraint PK_sno//删除约束，当我删除主键约束是，因为SC引用了sno作为外键，所以得先找出外键名称，然后再删除

select name  
from  sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id 
where f.parent_object_id=object_id('SC')//找出SC的外键
alter table SC
Drop constraint FK__SC__sno__060DEAE8 //删除外键
alter table S
Drop constraint PK_sno//再删除主键
use STU
go
alter table S
add constraint PK_sno primary key(sno)//增加主键

4.表中数据的维护

use STU
go
insert into S//插入数据
values('20170002','张明明','23','信息安全','男')
go
update S
set sex = '女'//更新数据
where sno = '20170002'
go
delete from S//删除表数据
where sname ='张明明'