一、数据库创建管理
1.1使用该当前数据库
1.2 创建数据库
1.3 修改数据库
ALTER DATABASE [要修改的数据库名(逻辑名称)]
ADD LOG FILE --添加一个日志文件
(
NAME='WebShop_log1',
FILENAME='E:\data\WebShop_log1.ldf'
)
ALTER DATABASE [要修改的数据库名]
MODIFY FILE --修改文件(数据文件、日志文件)
(
NAME='WebShop_data', --NAME='WebShop_log'
MAXSIZE=500 --修改上限
) |
1.4 删除数据文件和日志文件
语法:
alter database 数据库名称
remove file 数据文件或日志文件的逻辑文件名
操作:删除数据库db1中的数据文件data4和日志文件log2
alter
database
db1
remove data4
alter database db1
remove log2 |
1.5 增加文件组
语法:
alter database 数据库名
add filegroup 文件组名
操作:在数据库db1中增加一个g2文件组
1.6 重命名文件组
语法:
alter database 数据库名
modify filegroup 文件组名
name=新文件组名
操作:将数据库db1中的文件组g2更名为g3
alter database db1 modify filegroup g2 name=g3 |
1.7 删除文件组
语法:
alter database 数据库名称
remove filegroup 文件组名
操作:删除数据库db1的文件组g3
alter database db1 remove filegroup g3 |
1.8 修改数据库名称
语法:
alter database 数据库名
modify name = 新数据库名
操作:将数据库db1的名字修改为gl
alter database db1 modify name = gl |
1.9 删除数据库
语法:
Drop database 数据库名 [,……n]
USE master
GO
DROP DATABASE WebShop |
2 创建基本表
2.1 表约束
添加约束:
--添加unique约束
alter table 表名
add constraint 约束名 unique (规格说明)
--删除原来的约束需要知道约束名
--用sp_helpconstraint取得约束名
exec sp_helpconstraint '表名'
--执行以后constraint_type列应该有一个是check on column 数量
--看看constraint_name是什么,然后用以下语句删除
alter table 表名
drop constraint 约束名
--添加新的check约束
alter table 表名 with nocheck
add constraint 约束名 check(数量 between 1 and 1000) |
类型:
主键(PRIMARY KEY)约束
惟一(UNIQUE)约束
外键(FOREIGN KEY)约束
检查(CHECK)约束
说明:非空和默认值也可看成是约束。
创建表约束的方法:新建表时,在单列后创建约束或者在所有列之后,再创建约束;如果表已存在,只能通过修改表,添加约束。
语法:
create table 表名
(字段名 类型[(长度)] [,……n])
操作:
--创建一个学生表
CREATE TABLE Student
(
sno CHAR(7) PRIMARY KEY, --学号是主键
sname NCHAR(5) NOT NULL, --名字不能为空
id CHAR(18) UNIQUE, --Id 唯一
sex NCHAR(1) DEFAULT 'MAN', -- 性别默认为MAN
sage TINYINT CHECK (sage >= 15 AND sage <= 40) --年龄必须在~40之间
)
--创建一个课程表
CREATE TABLE Course
(
cno CHAR(6) PRIMARY KEY,
cname NVARCHAR(20) NOT NULL,
credit NUMERIC(3,1) CHECK (credit > 0),
senester TINYINT
)
CREATE TABLE SC
(
sno CHAR(7) NOT NULL,
cno CHAR(6) NOT NULL,
grade TINYINT,
PRIMARY KEY (sno, cno), --主键约束
FOREIGN KEY (sno) REFERENCES Student (sno),--添加外键
FOREIGN KEY (cno) REFERENCES Course (cno) --添加外键
) |
2.2 PRIMARY KEY
主键约束的作用:
1.不允许输入重复的值
2.不能取空值 (当主键是由多个属性组成时:某一属性上的数据可以重复,但其组合必须是惟一的;每个属性的值都不能为空。)
3.一个表上只能有一个主键。
2.3 UNIQUE
惟一性约束的作用:保证列中不会出现重复的数据。
--添加unique约束
alter table 表名
add constraint 约束名 unique (规格说明) |
主键约束与惟一性约束的区别:
1.一个表中只能定义一个主键约束,但可以定义多个惟一约束。
2.定义了惟一约束的列数据可以为空值,而定义了主键约束的列数据不能为空值。
2.4 FOREIGN KEY
外间约束的作用:
用于建立和强制两个表间的关联,限制外键的取值必须是主表的主键值。
2.5 CHECK
检查约束的作用:
1.用来限制列上可以接受的数据值
2.使用逻辑表达式来判断数据合法性
2.6 DEFAULT
默认约束的作用:当列值未确定且该列又不能为空时,可由系统自动为该列添加一个值
2.7 添加主键约束
操作:在学生情况表student中,添加“sno”的主键约束,主键约束命名为pk_student
如果表不存在
--在学生情况表student中,添加“sno”的主键约束,主键约束命名为pk_student
create table student
( sno char (6),
sname char (8),
ssex bit ,
sphone char(11)
constraint pk_student
primary key (sno)
) |
如果表已存在
alter table student
add constraint pk_student
primary key (sno) |
操作:在student表中,创建“sphone”字段惟一性约束,并且将约束命名为uq_sphone
--在student表中,创建“sphone”字段惟一性约束,并且将约束命名为uq_sphone
alter table student
add constraint uq_sphone
unique (sphone) |
2.8创建外键约束
CREATE TABLE SC
(
sno CHAR(7) NOT NULL,
cno CHAR(6) NOT NULL,
grade TINYINT,
PRIMARY KEY (sno, cno), --主键约束
FOREIGN KEY (sno) REFERENCES Student (sno),--添加外键
FOREIGN KEY (cno) REFERENCES Course (cno) --添加外键
) |
2.9 创建检查约束
操作1:在student表中,设置名为ck_student的检查约束,该约束限制“性别”为man或woman
--在student表中,设置名为ck_student的检查约束,该约束限制“性别”为man或woman
alter table student
add constraint ck_student
check (sex=man or sex=woman) |
操作2:在学生与课程表sc中,添加名ck_sc j的检查约束,该约束限制“成绩”在0到100之间
--在学生与课程表sc中,添加名ck_sc j的检查约束,该约束限制“成绩”在到之间
alter table sc
add constraint ck_sc
check (grade>=0 and grade<=100) |
2.10 设置默认约束
操作1:新建表时添加
--创建一个学生表
CREATE TABLE Student
(
sno CHAR(7) PRIMARY KEY, --学号是主键
sname NCHAR(5) NOT NULL, --名字不能为空
id CHAR(18) UNIQUE, --Id 唯一
sex NCHAR(1) DEFAULT 'MAN', -- 性别默认为MAN
sage TINYINT CHECK (sage >= 15 AND sage <= 40) --年龄必须在~40之间
) |
操作2:修改已有的表
alter table student
add constraint df_student
default 'man' for sex |
2.11 删除约束
--删除约束
alter table student drop constraint pk_xsqk_xh
alter table student drop constraint uq_xsqk_dh
alter table student drop constraint ck_xsqk _xb
alter table student drop constraint ck_xsqk _xh
alter table student drop constraint df_xsqk_xb |
3 修改基本表
3.1 增加列
语法:
alter table 表名
add <列定义>[<列约束>][ ,……n ]
关于“标识列”:
每个表中都可以有一个标识列,其作用是由系统自动生成能标识表中每一行数据的惟一序列值。(其实可以理解为行号)
“标识列”定义格式:
identity [ (seed, increment) ]
说明:seed为初始值,increment为增长的步长。意思就是你从几开始增长,每次增长几。省略时,初始值为1,步长为1.
操作:在student表中,增加三列
--在student表中,增加三列
alter table student
add address char(10) constraint df_xsqk_jg default 'shanghai' ,
email varchar(30),
number int identity
go |
3.2 修改列
语法:
alter table 表名
alter column 列名 新类型[(长度[,小数位数])]
操作1:将sc表的grade列的数据类型修改为numeric(4,1)
--将sc表的grade列的数据类型修改为numeric(4,1)
alter table sc
alter column grade numeric(4,1) |
操作2:将sc表的grade列的数据类型修改为int
--将sc表的grade列的数据类型修改为int
alter table sc
alter column grade int |
注意:
1.不能修改text、image、ntext、gimestamp类型的列;
2.不能修改类型是varchar、nvarchar、varbinary的列的数据类型,但可增加其长度。
3.不能修改是主键、外键列的类型,但可增加其长度;
4.不能修改包含索引、有默认值、检查约束和惟一性约束列的类型,但可增加其长度。
5.不能修改用列表达式定义或被引用在列表达式中的列。
6.不能修改复制列。
3.3 修改表中的数据
语法:
update 表名
set {列名 = 表达式 | null | default } [ , … n ] )
[ where 逻辑表达式 ]
操作:将sc表中的课程号为101的成绩不及格的学生的成绩都加上10分
--将sc表中的课程号为的成绩不及格的学生的成绩都加上分
u
pdate
sc
set grade = grade + 10
where ( cno = '101' and grade < 60) |
4删除基本表
4.1 删除列
注意:
若列上有约束,所以应先删除该约束后,再删除该列。
语法:
alter table 表名
drop column 列名
操作:
--先删除表中的约束
Alter table student drop constraint df_xsqk_jg
--再删除表中的列
Alter table student drop column address,email,number |
4.2 删除行
语法:
delete [from]表名
[ where 逻辑表达式]
操作:删除student表中姓名为zhangsan的数据记录
--删除student表中姓名为zhangsan的数据记录
delete from student where name = 'zhangsan' |
4.3 重命名数据表
语法:
exec sp_rename '表名','新表名'
操作:将student表重命名为rename_student
--将student表重命名为rename_student
exec sp_rename 'student','rename_student' |
4.4 删除数据表
注意:如果要删除的表T1是其他表T2的参照表,即如果T2有外键约束,参照了T1的数据项,则不能删除。需要先取消T2表中的外键约束载删除T1,或者先删除T2表再删除T1表。
语法:
drop table 表名[ ,……n ]
操作:已知sc表设置了外键约束,参照了表student和表course,现在要删除student表和course表。
--已知sc表设置了外键约束,参照了表student和表course,现在要删除student表和course表。
Drop table sc
go
Drop table student ,course |
5向表中插入数据
5.1 插入单行数据
语法:
insert [ into ] 表名 [ (字段名列表) ] values (字段值列表)
操作:已知student有以下数据项:SNO,SNAME,SAGE,SEX,SAGE,SDEPT,现向student表中插入数据
--已知student有以下数据项:SNO,SNAME,SAGE,SEX,SAGE,SDEPT,现向student表中插入数据
[sql] view plain copy print?在CODE上查看代码片派生到我的代码片
INSERT INTO Student(SNO,SNAME,SAGE,SDEPT) VALUES ('132','ZHANGSAN',23,'ASD')
INSERT INTO Student VALUES ('1234','ZHANGSAN','143','MAN',22,'YI',) |
注意:字符型、日期型数据要用单引号括起来。
5.2 插入多行数据
语法:
insert [ into ] 目的表名 [ ( 字段列表) ]
select [ 源表名. ] 列名 [ , …n ] from 源表名 [ , …n ]
[ where 逻辑表达式]
操作:将sc表中的成绩不及格的记录,插入到nopass表中,或者完全写出一 一对应的列名。参考语法说明
--将sc表中的成绩不及格的记录,插入到nopass表中,或者完全写出一一对应的列名。参考语法说明
insert into nopass
select * from sc
where grade<60 |
注意:
1.查询的值与列名按顺序对应,要求值类型与列数据类型一致。
2.对语句中无值对应的列名赋NULL。
3.如果没有指明列名表,则新插入记录的值的顺序必须与表中列的顺序一致,且每一列均有值(可为空)
6索引
6.1 创建索引
语法:
create [ unique ] [ clustered | nonclustered ] index 索引名
on { 表名 | 视图名 } ( 列名 [ asc | desc ] [ , ...n ] )
注意:
1.一个表中只能创建1个聚集索引。(由于系统已自动在主键上创建了聚集索引,所以用户不能再创建,除非先删除已有的索引,重新创建)
2.一个表中可以创建若干个非聚集索引。
操作:在kc表中,重新创建名为“ix_kcm”的索引,使其成为惟一性的非聚集索引
--在kc表中,重新创建名为“ix_kcm”的索引,使其成为惟一性的非聚集索引
create unique index ix_kcm
on kc ( 课程名 desc)
with drop_existing --删除已存在的索引,创建新的索引 |
6.2 删索引
语法:
drop index {表名 . | 视图名 . } 索引名 [ , …n ]
注意:
SQL Server系统自动建立的索引不能用drop index删除,只能用alter table语句中的drop constraint子句来解除加在该字段上的主键约束或惟一性约束,这些约束一解除,相关的索引也就被删除了。
6.3 查看索引
语法:
[exec] sp_helpindex {表名 | 视图名 }