数据库中的基本操作总结

来源：互联网发布：ubuntu命令行模式编辑：程序博客网时间：2024/06/09 23:28

一、数据库创建管理

1.1使用该当前数据库

-- 使用该数据库

USE [数据库名]

1.2 创建数据库

CREATE DATABASE [新建数据库名]

ON --数据文件

(

NAME='WebShop_data', --数据库逻辑名称

FILENAME='E:\data\WebShop_data.mdf', --数据库物理名称

SIZE=5, --初始大小，单位MB

MAXSIZE=100, --增长上线，单位MB

FILEGROWTH=2 --增长量，单位MB、%

)

LOG ON --日志文件

(

NAME='WebShop_log',

FILENAME='E:\data\WebShop_log.ldf',

SIZE=2,

MAXSIZE=500,

FILEGROWTH=10%

)

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文件组

alter database db1

add filegroup 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

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

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表中的课程号为的成绩不及格的学生的成绩都加上分

update 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

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 {表名 | 视图名 }

1 0