1、T-SQL语句
- 在SQL Server数据库中，操作数据库的SQL语句包括以下几个方面：
数据定义语句
DDL——数据定义语句，用于对数据库以及数据在库中的对象进行创建、删除、修改等操作。如前所述，数据库对象主要包括表、默认约束、规则、视图、触发器、存储过程。
数据操纵语句
DML用于从操纵数据库中的各种对象，检索和修改数据。
数据控制语句
DCL用于安全管理，确定哪些用户可以查看或修改数据库中的数据。
2、界面创建数据库与维护数据库
- 首先要明确，能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。
- 创建数据库必须确定数据库名、所有者（即创建数据库的用户）、数据库大小（初始大小、最大容量、是否允许增长及增长方式）和存储数据库的文件。
- 对于新创建的数据库，系统默认数据文件初始代小为8MB，最大大小不受限制（仅受磁盘空间的限制），允许数据库自动增长，增量为64MB。默认日志文件初始代小为8MB，最大大小不限制，允许日志文件自动增长，增长方式按照10%比例增长。
- 举例：
创建数据库PXSCJ，数据文件和日志文件的属性按默认值设置，创建过程如下：
对象资源管理器——右击数据库——新建数据库——常规
5、 数据库的修改和删除
数据库创建成功之后，数据文件名和日志文件名就不能改变。对已存在的数据库可以进行的修改包括：
- 增加或删除数据库文件
- 改变数据库文件的大小和增长方式
- 改变日志文件的大小和增长方式
- 增加或删除日志文件
- 增加或删除文件组
- 重命名数据库
6、举例：
下面已修改PXSCJ为例：
右击数据库PXSCJ——属性，出现9个选项卡：
在这里面可以进行上述操作。
3、命令创建数据库
- 除了使用SSNS的图形界面创建数据库之外，还可以使用Transact-SQL命令（称为命令方式）来创建数据库。与图形界面化相比，命令方式更灵活。
- 创建数据库的模板语句为：
create database 数据库名
on [primary]
(
name='数据文件的逻辑名称',
filename='文件的路径和文件名',
size=文件的初始大小,/*以MB为单位*/
maxsize=文件的最大容量|unlimited,
filegroup=文件的每次增长量
)
log on
(
name='事务日志文件的逻辑名称',
filename='文件的路径和文件名',
size=文件的初始大小,/*以MB为单位*/
maxsize=文件的最大容量|unlimited,
filegrowth=文件的每次增长量
)
/*1、用primary关键字显式的指定后面的数据文件属于主文件组
2、on后面括号里的文件是主数据文件(.mdf)、辅数据文件(.ndf)，如果有多个，以逗号隔开
3、filename指的是文件物理名
4、name与filename是不可省略的，其他属性省略均采用默认值
5、涉及到的文件的增长量，单位为MB或%
6、log on后面跟的是事务日志文件
7、关键字unlimited表示无限增长
8、size的单位可以省略不*/
- 例题：创建名为TEST的数据库，有三个数据文件：
主数据文件：初始大小为10MB，最大容量为60MB，按2MB增长，文件名为TEST_data1；
辅数据文件：大小为10MB，最大大小不限，按10%增长，文件名为TEST_data2；
事务日志文件：文件名为TEST_data3，其他属性为默认值。
SQL语句如下：
create database TEST
on primary
(
name='TEST_data1',
filename='G:\SQL server2016\ziwenjian\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_data1.mdf',
size=10MB,
maxsize=60MB,
filegrowth=2MB
),
(
name='TEST_data2',
filename='G:\SQL server2016\ziwenjian\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_data2.ndf',
size=10MB,
maxsize=unlimited,
filegrowth=10%
)
log on
(
name='TEST_data3',
filename='G:\SQL server2016\ziwenjian\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_data3.ldf'
)
执行结果为：
4、命令维护数据库
- 新增文件组和文件的语法格式：
/*新增文件组*/
Alter database 数据库名
Add Filegroup 新文件组名
/*新增文件*/
Alter database 数据库名
Add File
(
name='数据文件的逻辑名称',
filename='文件的路径和文件名',
size=文件的初始大小,/*以MB为单位*/
maxsize=文件的最大容量|unlimited,
filegroup=文件的每次增长量
)
- 操作数据库时注意：
1、一条Alter database语句智只能新增一个文件，不管是数据文件还是日志文件
2、如果是数据文件，只能是辅数据文件，因为一个数据库只能存在一个主数据文件 - 例题：
在TEST数据库中新增newgroup文件组，新增文件名为TEST_data4.ndf。
/*新增文件组*/
Alter database TEST
Add Filegroup newgroup
Alter database TEST
/*新增文件*/
Add File
(
name='TEST_data4',
filename='G:\SQL server2016\ziwenjian\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_data4.ndf'
)
- 删除文件组和文件的语法格式：
Alter database 数据库名
Remove Filegroup 文件组名
Alter database 数据库名
/*删除文件*/
Remove File 文件名
- 在进行删除操作的时候应该注意：删除文件组前必须保证其中没有文件
- 例题：删除上述新建的文件组和文件
Alter database TEST
Remove Filegroup newgroup
Alter database TEST
/*删除文件*/
Remove File TEST_data2
- 修改数据库的文件或事务日志文件的容量的语法格式：
Alter Database 数据库名
Modify file
(
name='数据文件的逻辑名称',
size=文件的初始大小,/*以MB为单位*/
maxsize=文件的最大容量|unlimited,/*可以省略*/
filegroup=文件的每次增长量/*可以省略*/
)
- 例题：
将数据库TEST的数据文件TEST_data3的容量8MB->20MB:
Alter database TEST
Modify file
(
name='TEST_data2',
size=20MB
)
- 修改数据库名称的语法格式：
修改数据库的语句分为2种：使用Alter Database语句、调用系统内置存储过程Sp_renamedb
Alter Database 数据库名
Modify name=新数据库名
Sp_renamedb 旧数据库名,新数据库名
- 举例：将数据库TEST改名为NEWTEST
法一：
Alter database TEST
Modify name=NewTest
法二：
Sp_renamedb TEST,NewTest
- 删除数据库的语法格式
Drop Database 数据库名
- 举例：删除数据库NewTest
Drop Database NewTest
5、数据表的设计（主要是物理设计）
-
数据表的设计包括：
-
确定字段的属性名
-
选取字段的数据类型
-
设置数据完整性约束
-
表名和属性名：
-
选取字段的数据类型：
6、数据表的创建
- 使用SSMS图形界面化创建表：
- 标识列：标识列能够自动生成行号，行号是按照指定的标识增量和标识种子排序，如：标识增量和标识种子都为1，代表初始行号为1，增量为1的自然数。
- 使用标识列的时候应该注意：
- 一个表只能创建一个标识列
- 如果创建标识列没有指定标识增量和标识种子，将采用的默认值是（1，1）
- 标识列中的数据类型只能使用整型当中的bigint、int、smallint、tinyint，定点小数类型decimal、numeric也可使用，但是不允许出现小数值。
- 手动添加标识列，在规范标识选项中设计标识：
- 使用SQL语句创建数据库：
- 数据类型中只有char、nchar、varchar、nvchar数据类型必须指明长度，其他类型不用
- decimal(p,s)和numeric(p,s)数据类型必须指明p(精度)和s(小数位数)
- 标识列：使用identity(标识种子、标识增量语法)定义
- NULL表示允许为空，可以省略；NOT NULL表示不允许为空
书写SQL语句时应注意：
- 标点符号必须使用英文标点符号
- 在执行语句前，先要检查当前数据库是否为需要操作的数据库，否则表就建到其他数据库了。
7、数据完整性约束
- 数据完整性：
由于数据库中的数据是从外界输入的，数据的输入由于种种原因会发生输入无效信息或错误信息，数据的完整性是为了保证数据是否符合规定而提出的。 - 数据完整性约束的分类：
- 如何实现数据完整性？
可以通过为表的字段设置约束来保证表中数据完整性
- 约束包括6种：
- 主键约束：
要求主键列数据唯一，并且不允许为空，主键约束的命名推荐使用“Pk_表名”命名格式。
- 用SQL语句给SCC数据库的Student学生表设置主键Sno：
法1：
*在创建表时设置主键，使用列级约束
Use SCC/*指定数据库SCC*/
create table Student
(
Sno nvarchar(15) Primary key,
Sname nvarchar(10),
Sex nchar(1),
Birth date,
ClassNo nvarchar(10)
)
法2：
*在创建表时设置主键，使用表级约束
create table Student
(
Sno nvarchar(15),
Sname nvarchar(10),
Sex nchar(1),
Birth date,
ClassNo nvarchar(10),
Constraint Pk_Student Primary key(Sno)
)
法3：
*在修改表时，设置主键
Alter table Student
Add Constraint Pk_Student Primary Key(Sno)
- 删除Student表中的主键Pk_Student
Alter table Student
Drop Constraint Pk_Student
- 外键约束：
1、用于实现表与表之间的参照完整性，使二维表所描述的实体建立联系。注意：外键可以理解为两张二维表含义上的公共字段，它们的字段名各不相同，但它们的数据类型一定相同。
2、如果这个公共字段在一张表上是主键，或主键组之一，就把这张表叫做主键表，反之则为外键表。
3、类似的，外键约束的命名推荐使用“Fk_表名”命名格式。
- 在SCC中创建外键约束，关联Student表与Class表，实现2张表的参照完整性。
法1：
*在创建表时设置主键，使用列级约束
Use SCC/*指定数据库SCC*/
create table Student
(
Sno nvarchar(15) Primary key,
Sname nvarchar(10),
Sex nchar(1),
Birth date,
ClassNo nvarchar(10) Foreign Key References Class(ClassNo)
/*Class是主键表名，ClassNo是主键字段名*/
)
法2：
*在创建表时设置主键，使用表级约束
create table Student
(
Sno nvarchar(15),
Sname nvarchar(10),
Sex nchar(1),
Birth date,
ClassNo nvarchar(10),
Constraint Fk_Student_Class foreign Key(ClassNo) references Class(ClassNo)
)
法3：
*在修改表时，设置主键
Alter table Student
Add Constraint Fk_Student_Class Foreign Key(ClassNo) references Class(ClassNo)
建立外键约束后，会对以下操作产生影响：
1、对外键表操作的约束：
当向外建表中插入数据时，外键的值必须与主键表的主键值相同，更显外键表的外键值时，同样需要满足该条件。
2、对主键更新操作的约束：
当存在更新的时候，可以更新主键表中主键的值，同时外键表中所有关联的记录外键值会被更新。
3、对主键表删除操作的约束：
删除主键值，外键表中的记录也会被删除。
- 删除SCC数据库中的外键约束Fk_Student_Class：
Alter table Student
Drop Constraint Fk_Student_Class
- 非空约束：
来指定某个字段的值是否允许为空。注意：取空值和值为0或空字符串是不同的，也就是在字段的属性中添加NULL或Not Null
- 检查约束：
1、检查约束是用来限制列数据的有效范围，当对约束的列数据进行添加或修改时，SQL Server会自动检查列数据的有效性，从而保证数据库中用户定义完整性。
2、在设置检查约束时，可以使用逻辑表达式表示数据的有效范围，检查约束的命名规则是Ck_字段名。
3、检查约束默认强制用于insert、update,当给Sex属性设置数据时，必须要满足设置好的约束，否则会出现警告错误。
- 为Student学生表中的Sex性别字段创建字段检查约束，使得性别字段的数据值只能是‘男’或‘女’
法1：
*在创建表时设置主键，使用列级约束
Use SCC/*指定数据库SCC*/
create table Student
(
Sno nvarchar(15) Not Null Primary key,
Sname nvarchar(10) Not Null,
Sex nchar(1) Not Null check(Sex='男' or Sex='女'),
Birth date,
ClassNo nvarchar(10) Foreign Key References Class(ClassNo)
/*Class是主键表名，ClassNo是主键字段名*/
)
法2：
*在创建表时设置主键，使用表级约束
create table Student
(
Sno nvarchar(15) Not Null Primary Key,
Sname nvarchar(10) Not Null,
Sex nchar(1) Not Null,
Birth date,
ClassNo nvarchar(10) Not Null Foreign Key References Class(ClassNo),
Constraint Ck_Sex check(Sex='男' or Sex='女')
)
法3：
*在修改表时，设置主键
Alter table Student
Add Constraint Ck_Sex check(Sex='男' or Sex='女')
法4：
*使用图形界面化：右击表——设计——右击字段——选择check约束——表达式中输入：Sex='男' or Sex='女'——修改约束名称为Ck_Sex
- 唯一约束：
1、唯一约束应用于表中的非主键列，用于指定一个或多个字段的组合的值具有唯一性，以防止在字段中输入重复的值，如身份证号码列。
2、唯一约束的命名规则为UQ_字段名。
3、唯一约束与主键约束的区别：
- 一张表可以设置多个唯一约束，但是主键约束只能设置一个，因为它是表中字段的唯一标识
- 设置了唯一约束的列值必须唯一，但允许有一个空值NULL；而设置了主键约束的列值必须唯一，而且不允许为空
- 将Department院部表中的Dname院部名称字段，设置为唯一约束，不允许该字段重复出现的数据值
法1：
*在创建表时设置主键，使用列级约束
Use SCC/*指定数据库SCC*/
create table Department
(
Dno nvarchar(30) Not Null Primary Key,
Dname nvarchar(30) Not Null Unique
)
法2：
*在创建表时设置主键，使用表级约束
create table Student
(
Dno nvarchar(30) Not Null Primary Key,
Dname nvarchar(30) Not Null,
Constraint UQ_Dname Unique(Dname)
)
法3：
*在修改表时，设置主键
Alter table Student
Add Constraint UQ_Dname Unique(Dname)
法4：
*使用图形界面化：右击表——设计——右击字段——索引与键——添加——常规、唯一键——选择标识列Dname——修改标识名称为UQ_Dname
- 默认值约束：
默认值约束是指在表记录添加后用户没有输入字段时，该字段值是由系统自动提供
- 将Course课程表中的Teacher任课教师字段，设置默认值约束为“待定”
法1：
*在创建表时设置主键，使用列级约束
Use SCC/*指定数据库SCC*/
create table Course
(
Cno nvarchar(10) Not Null,
Cname nvarchar(30) Not Null,
Teacher navchar(10) Not Null default '待定',
Credit numeric(4,1) Not Null,
LimitNum int Not Null Check(LimitNum>0),
CourseHour int
)
法2：
*在修改表时，设置主键
Alter table Course
Add Column Teacher Set default '待定'
法3：
*使用图形界面化：与上类似
8、数据表的修改
- 修改数据表结构：
1、修改表的名称，修改表的列名以及表中列的数据类型；所谓的修改数据表，指的是：修改数据库中已经存在的数据表结构
2、表结构的修改与表内容的修改是不一样的，要注意区分
3、修改表的结构主要包括5个方面：修改表名、修改字段名、修改字段的数据类型、添加字段、删除字段
4、使用SSMS或SQL语句修改表结构
- 使用SQL语句修改表结构：
1、修改表名：
数据库中，不同的数据表是通过表名区分的，要修改数据表的名称时，通常要用到Sp_name存储过程，具体语法为： Execute Sp_rename 旧表名，新表名；
/*将SCC字段中的Stu表名改为Stu1表：*/
Execute Sp_rename Stu,Stu1;
2、修改列名：
数据库中，列是通过列名来区分的；语法格式为：Sp_rename ‘表名.旧列名’，‘新列名’；
/*将表Student中的name字段改为username*/
Execute Sp_rename 'Student.name','username';
3、修改列的数据类型：
语法格式为：Alter table 表名 Alter Column 列名 数据类型；
/*将表Student中的username字段的数据类型由varchar(20)改为varchar(50)*/
Alter table Student Alter Column username varchar(50);
4、添加列：
语法格式为：Alter table 表名 Add 列名 列数据类型
/*将数据表Score中添加一个char(5)类型的字段*/
use SCC
Alter table Score Add gender char(5)
5、删除列：
语法格式为：Alter table 表名 Drop Column 列名；
/*删除数据表Score表中的gender字段*/
use SCC
Alter table Score
Drop Column gender
- 注意：
1、在Alter table语句中，一次只能包含Alter、Column、Add、Drop中的一条语句
2、使用Alter Column语句，一次只能修改一列属性，要想修改多个列的属性，必须写多条Alter table语句
3、这里加一道练习题，实际操作时遇到的问题与理论是有一定差异的
习题1：
习题2：
- 我在做作业的时候出现了一个问题，同时设置一张表中的主键和外键时出现了问题，后来发现因为该表中还没有定下主键，就先定下外键。因此，要想使得一张表中有主键和外键，必须先创建主键之后再使用修改表的方式设置外键。
- 修改的过程如下：
结果为：
习题3：
这是我的Goods数据库：
写入SQL语句之后发现：
语句执行失败；分析是因为要添加的记录所在的表中，Goodno属性的数据类型为char(4)，这与将要添加的记录的字段长度为5不相符，所以必须修改该属性的字段长度；
分析是因为该属性具有主键约束，做法是：在修改字段长度之前必须先解除约束，然后修改长度，之后添加约束，最后插入记录。
删除主键约束：
修改属性数据类型：
添加主键约束：
插入记录：
插入记录完成
9、表数据的增删改
- 添加数据
上述模板语句中，前者是insert语句指定所有的字段名；后者是不指定字段名。
- 更新数据
是指对表中存在的记录进行修改，比如某个同学改了名字，就需要对其记录信息中的name字段值进行修改，使用update语句进行更新记录。
update 表名
Set 字段名1=值1 [字段名2=值2,……][where 条件表达式]
where语句指定更新数据需满足条件，更新一条或几条记录使用where约束，如果没有它，将会对表中所有的记录的指定字段进行更新
- 删除数据
delete from 表名[where 条件表达式]
- 还有一种删除表中所有记录的方法，它的语法简单，本质与前者一样：
truncate [table] 表名
- 同样，图形界面化的方式很简单，与上面类似。
转载：https://blog.csdn.net/weixin_45082647/article/details/104658847