网站空间提供,广告手机网站制作,西安知名的集团门户网站建设企业,电商运营教程目录 一#xff0c;SQL server的数据类型
1#xff0c;基本数据类型
2#xff0c;自定义数据类型 二#xff0c;使用T-SQL创建表
1#xff0c;数据完整性的分类
2#xff0c;约束的类型
3#xff0c;创建表时创建约束
4#xff0c;任务
5#xff0c;由任务编写…目录 一SQL server的数据类型
1基本数据类型
2自定义数据类型 二使用T-SQL创建表
1数据完整性的分类
2约束的类型
3创建表时创建约束
4任务
5由任务编写的SQL语句
三使用T-SQL管理表
1创表时添加约束名
1添加约束名
2 修改由任务编写的SQL语句 添加约束名
2使用ALTER TABLE修改表
1从表中删除列
2向表中添加列
3修改表中列的长度
4对指定表添加默认约束 3使用DROP TABLE删除表 通过前一篇SQL server学习02-使用T-SQL创建数据库-CSDN博客的学习
我已经创建好了数据库接下来开始使用创好的库创表。首先了解表的概念
表是数据关系模型中表示实体的方式 表是数据库中用来组织和存储数据具有行列结构的数据库对象数据库中的数据都存储在表中。表由行和列组成。 值得注意的是一张表就是一个关系。 表有行和列关系有记录和字段。表有表名每个关系都有一个关系名。 行称为记录是组织数据的单位每行都是一条独立的数据记录。 列称为字段主要描述数据的属性。
如下就是常见的一张excel二维表。 表分为普通表和系统表。
普通表由用户创建用户定义的表也称为标准表用来存储数据库应用系统中的数据。系统表由系统创建存储了有关数据库服务器的配置数据库设置用户和数据库对象的描述等系统信息用户不能创建。
在同一个数据库中表名不能重复在同一张表中列名不允许相同但在不同的表中列名可以相同。
【ps同一张表列名重复无意义。由于不同表之间可能存在联系因此会有相同列名。】 一SQL server的数据类型 在创建表之前需要考虑要创建的表包含哪些内容例如一张表包含
哪些列每列都是什么数据类型
可以看到在创表时数据类型很关键。SQL server提供了基本数据类型和自定义数据类型。
先来学习基本数据类型也称系统数据类型。
1基本数据类型
基本数据类型是系统本身就自带的可以直接拿来用如下 2自定义数据类型
自定义数据类型不是系统自带的基本数据类型但用户需要以SQL server系统数据类型为基础创建。
当多张表中的列要存储相同类型的数据时往往要确保这些列具有完全相同的数据类型长度和为空性数据的值是否允许为空 可以通过用户自定义数据类型来实现。例如
电话的长度都为11非空且唯一。 如果某个系统要求电话这一列的长度为11个字符那么有效的电话内容可以是“12345678912”或“98765432121”但“123”或“123456”就不符合这个要求。 身份证的长度为18非空且唯一。
下面通过SSMS创建一个名为char30的自定义数据类型要求基于char系统数据类型大小为30个字符。 鼠标右击“用户定义数据类型”子节点可以看到之前的自定义数据类型char30已被排列在“用户自定义数据类型”列表中 二使用T-SQL创建表
不管是在MySQL中还是在SQL server中创库创表都是一样的关键字CREATE如果创库后面接DATABASE 创表后面则接TABLE 。 在SQL server中使用T-SQL语句创表的语法如下
CREATE TABLE 数据表名
(列名 数据类型[列级完整性约束条件]
[,...n]
[,表级完整性约束条件]
) 可以看到在上述语法总“约束条件”出现了两次。约束时SQL server提供的自动保持数据库完整性的一种方法。之所以存在约束条件是因为需要保持数据库的完整性即数据完整性。 用户在向表格中添加数据时有些数据的内容可能与实际情况不相符例如
年龄1000岁 没有人能够活到1千岁性别好 性别没有“好”要么是“男”或“女”要么是美国的97 种性别中的一个身份证号12 身份证号必须是18位极少数是15位邮箱123 邮箱格式写错
可以看到这些数据都是不可靠的为了能够保证数据的准确可靠就需要对数据表进行数据完整性设置。
1数据完整性的分类 实体完整性行完整性 指表的每一行在表中是唯一的实体。一般用主键约束 PRIMARY KEY。 例如学生表中的每一行代表表中唯一的一名学生不会有两行或两行以上的记录来表示同一个学生太浪费空间没必要。 域完整性列完整性 指列满足特定的数据类型和约束。可以使用检查约束或非空约束。 例如成绩表中成绩这一列的值限定在[0,100]学生表中性别只能为“男”或“女”年龄非空。 参照完整性引用完整性 指表和表之间的字段指是有关联的特殊情况是产生在同一张表的不同字段值之间参照表中的外键值必须存在于被参照表中的主键值中这句话的意思就是你要参照我的数据那么我的外键列在你的表中必须是主键列。使用外键约束。 例如成绩表中的学号这一列外键的值必须存在于学生表中学号这一列主键的值中。 用户定义的完整性 指某一具体的应用必须满足的语义要求或用户实际的业务规则。较少使用 接下来了解SQL server中的约束类型。
2约束的类型
分为
检查约束CHECK 指定某列可取值的集合或范围用于实现域完整性。一张表可以有多个检查约束。 在设置检查约束时需要加上逻辑表达式即CHECK(逻辑表达式) 默认约束DEFAULT 若在表中定义了默认值约束用户在插入新的数据行时如果没有为该列指定数据那么系统会将默认值赋值给该列。 主键约束PRIMARY KEY 主键是表中某列或多个列的组合可以唯一确定一条记录。可以保证实体完整性。每张表只能有一个主键主键不能为空即如果一个字段为主键就必须同时设置为非空约束。 外键约束FOREIGN 表与表之间是有关系的有两张表分别为表A和表B两张表中有相同的列m假设列m在表A中是主键或具有唯一约束则列m在表B中为外键需要使用外键约束。语法如下 --表A中
CREATE TABLE A(
列名m 数据类型 约束条件为主键PRIMARY KEY,
)--表B中
CREATE TABLE B(
列名m 数据类型,
FOREIGN KEY (m) REFERENCES A(m)
) 此时表A称为主表或被参考表表B为从表或参考表。存在相互联系的两种表中谁有主键谁就是主表。 外键表示一张表中的列于另外一张表中列的引用关系以保证不同表中数据的一致性。用于实现参照完整性。唯一约束UNIQUE 用于指定表中某列或多个列的组合值具有唯一性确保在非主键列中输入非重复值。可以保证实体的完整性。虽然这点和主键约束很像但是使用唯一约束的字段允许为空null并且只能有一个该字段值为空即如果有一条记录的该字段为空那么其他记录的该字段必须有值并且这些值必须是唯一的。在一张表中唯一约束可有多个而主键约束只能有一个。 例如现在我有一张表的数据如下sphone设置了唯一约束 snosphone1153xxxxxxxx2null3189xxxxxxxx如果再试图插入另一条记录如果sphone也为空就会违反唯一约束从而导致插入失败。 非空约束NOT NULL 指定表中的某些列必须有具体值可以实现域完整性。
知道了数据完整性和约束条件这两个概念就可以在创建表的同时创建约束也可以在修改表时创建约束。
为了方便后期删除指定约束有些时候在创建约束的时候可以给当前的约束指定一个约束名字约束名删除表中指定约束时可以使用指定约束的约束名来进行删除该约束。 constraint拘束。
添加表中约束的语法如下
ALTER TABLE 表名
ADD CONSTRAINT 约束名 约束条件(表中需要加指定约束的列名)
删除表中约束时使用关键字DROP如下
ALTER TABLE 表名
DROP CONSTRAINT 约束名;
一般不用自己写使用默认的约束名就行。
3创建表时创建约束
语法格式如下
CREATE TABLE 数据表名从表
(
列名 数据类型,
.
.
.
FOREIGN KEY (列名) REFERENCES 主表名(列名)
)
4任务
在StuScore数据库中根据需要还需要创建系部表班级表和用户表。使用T-SQL语句按照下表所示的表结构创建所需的三张表。以下是数据库中各张表的结构 注意点
学生表不是第一张先创的表从图中可以看到学生表有一个班级编号外键classid表示在学生表students和班级表classes之间班级表是主表学生表是从表。因此要先将创建班级表再创建学生表。根据之前所学的约束条件上面的建表就会很简单使用默认的约束条件。约束条件 主键 PRIMARY KY 检查 CHECK(gender男 or gender女) 外键 FOREIGN KEY 唯一 UNIQUE 上述表中唯一特别的就是成绩表score中的sno和cno都是主键约束通过之前的学习我知道了一张表只能有一个主键约束。
为什么成绩表score中会有两个主键 答因为每个学生可以选修多门课所以成绩表中的学生学号sno就会有重复这意味着仅使用sno作为主键无法保证数据的实体完整性因为它不能唯一标识每一条成绩记录。然而每个学生在同一门课程中只会有一条成绩记录因此sno和cno的组合可以唯一标识每一条成绩记录。通过将这两个字段合并为一个复合主键能够有效地保证数据的实体完整性。 “复合主键”就如上面的情况一张表只能有一个主键但是有些时候需要多个字段同时为一个主键这种时候就需要使用到复合主键。
值得注意的是因为我是事先看表结构来创表的因此我知道要用到复合主键sno和cno先不加任何约束之后再一起添加主键约束。
如果不小心将学号设置成了主键就需要先使用如下语句删掉原有的主键再添加复合主键进去。
--删除约束
ALTER TABLE 表名
DROP CONSTRAINT 约束名 -- 添加复合主键约束
ALTER TABLE 表名
ADD CONSTRAINT 约束名 PRIMARY KEY(列名1,列名2) 上面这张表主要是用于连接数据库对应数据库的sa用户及密码。 如果后面做项目的话会用到。
按照生活常识
没有系部的成立就没有班级。学生达到一定人数才会开设课程。没有学生考试就不会有成绩。
可以看到首先应该创建的是系部表再是班级表接着就是学生表课程表成绩表。
编写的sql语句如下
5由任务编写的SQL语句
USE Stuscore --之前创建的数据库
----系部表
-- 检查并删除表 dept
IF EXISTS (SELECT * FROM sys.objects WHERE name dept AND type U)DROP TABLE dept
CREATE TABLE dept(deptno char(5) NOT NULL PRIMARY KEY,dname nchar(10) NOT NULL,dean nchar(4),phone char(8) UNIQUE
);--班级表
-- 检查并删除表 classes
IF EXISTS (SELECT * FROM sys.objects WHERE name classes AND type U)DROP TABLE classes;
CREATE TABLE classes(classid char(6) NOT NULL PRIMARY KEY,speciality nchar(12) NOT NULL,deptno char(5) NOT NULL, counselor nchar(4),FOREIGN KEY (deptno) REFERENCES dept(deptno)
);--学生表
-- 检查并删除表 students
IF EXISTS (SELECT * FROM sys.objects WHERE name students AND type U)DROP TABLE students;
CREATE TABLE students
(sno char(8) NOT NULL PRIMARY KEY,sname nchar(4) NOT NULL,gender nchar(1) CHECK(gender男 or gender女),classid char(6) NOT NULL, birthday date,phone char(13) UNIQUE,FOREIGN KEY (classid) REFERENCES classes(classid)
)--课程表
-- 检查并删除表 courses
IF EXISTS (SELECT * FROM sys.objects WHERE name courses AND type U)DROP TABLE courses;
CREATE TABLE courses
(cno char(10) NOT NULL PRIMARY KEY,cname nvarchar(20) NOT NULL,period int NOT NULL,credit tinyint NOT NULL, type nchar(5) NOT NULL
)--成绩表
-- 检查并删除表 score
IF EXISTS (SELECT * FROM sys.objects WHERE name score AND type U)DROP TABLE score;
CREATE TABLE score
(sno char(8) NOT NULL,cno char(10) NOT NULL,CONSTRAINT PK_score_sno_cno PRIMARY KEY(sno, cno),grade tinyint CHECK(grade0 and grade100),FOREIGN KEY (sno) REFERENCES students(sno), FOREIGN KEY (cno) REFERENCES courses(cno)
)--用户表
-- 检查并删除表 users
IF EXISTS (SELECT * FROM sys.objects WHERE name users AND type U)DROP TABLE users;
CREATE TABLE users(login char(10) NOT NULL PRIMARY KEY,username nchar(5) NOT NULL, pwd char(10) NOT NULL
)-- 检查是表中的字段是否完整
SELECT * FROM dept
SELECT * FROM classes
SELECT * FROM students
SELECT * FROM score
SELECT * FROM users 三使用T-SQL管理表
1创表时添加约束名
这里以系部表dept为例。
1添加约束名
系部表中的deptno为主键约束如果不添加主键约束名的语句就如下编写
deptno char(5) NOT NULL PRIMARY KEY,
但如果想要给主键加上约束名就需要先写好列deptno不急着添加主键约束需要再写一行语句给列deptno添加主键约束及主键约束名 deptno char(5) NOT NULL,CONSTRAINT PK_dept_deptno PRIMARY KEY(deptno), 为什么需要给约束添加上约束名主要是因为随着时间的推移有些约束条件需要改变比方说成绩表中的成绩grade现在的约束是0~100分但是满分可能会变成150分即约束为0~150。
如果想要在不删除表格的前提下修改该约束就需要通过约束名来修改该约束条件。
如果不给约束条件加上约束名系统会为该约束条件分配名字但由系统给的约束名字会变。
使用如下语句查看系统给的约束名这里是dept表查看主键PK
SELECT name FROM sys.key_constraints WHERE parent_object_id OBJECT_ID(dept) AND type PK;先不手动添加约束名dept表的deptno列约束名就会随着程序的每一次运行而改变如下
第一次执行下面的语句 再次执行下面的语句 仔细看会发现表示主键约束的关键字PK及表名dept不变但是后面的约束名会变。 如果想要通过约束名来删除该约束无疑是天方夜谭。因此建议只要含有主键约束的列最好都加上对应的约束名。约束名可以写成如下形式
约束条件单词首位字母大写_数据表名_被添加约束的列名
手动添加约束名之后再去查看约束名就不会随着程序的运行而改变 2 修改由任务编写的SQL语句 添加约束名
--USE Stuscore --之前创建的数据库----系部表
-- 检查并删除表 dept
IF EXISTS (SELECT * FROM sys.objects WHERE name dept AND type U)DROP TABLE dept
CREATE TABLE dept(dno char(5) NOT NULL CONSTRAINT PK_dept_dno PRIMARY KEY(dno), --dno系编号 dne nchar(100) NOT NULL, --dne系名 dept nameddn nchar(100), --ddn系主任 dept deandpe char(11), --dpe系联系电话 dept phoneCONSTRAINT CK_deptno_dpn UNIQUE(dpe)
)--班级表
-- 检查并删除表 classes
IF EXISTS (SELECT * FROM sys.objects WHERE name classes AND type U)DROP TABLE classes;
CREATE TABLE classes(cno char(6) NOT NULLCONSTRAINT PK_classes_cno PRIMARY KEY(cno), --cno班级编号cne nchar(100) NOT NULL, --cne班级名 classes namedno char(5) NOT NULL, --dno系编号ccr nchar(100), --班级辅导员 classes counsellorCONSTRAINT FK_dept_dno FOREIGN KEY (dno) REFERENCES dept(dno)
);--学生表
-- 检查并删除表 students
IF EXISTS (SELECT * FROM sys.objects WHERE name students AND type U)DROP TABLE students;
CREATE TABLE students
(sno char(8) NOT NULL CONSTRAINT PK_students_sno PRIMARY KEY(sno), --sno学生编号sne nchar(100) NOT NULL, --sne 学生名 students namessx nchar(6)CONSTRAINT CK_students_ssx CHECK(ssxmale or ssxfemale), --ssx学生性别 students sexcno char(6) NOT NULL, --cno班级编号sbd date, --sbt学生出生日期 students birth dayspe char(11) CONSTRAINT UQ_students_spe UNIQUE(spe), --spe学生联系方式 students phonesnn nvarchar(100) CONSTRAINT DF_students_snn DEFAULT China, --snn学生国籍 students nationCONSTRAINT FK_students_cno FOREIGN KEY (cno) REFERENCES classes(cno)
);--课程表
-- 检查并删除表 courses
IF EXISTS (SELECT * FROM sys.objects WHERE name courses AND type U)DROP TABLE courses;
CREATE TABLE courses
(cno char(10) NOT NULLCONSTRAINT PK_courses_cno PRIMARY KEY(cno), --cno课程编号 cne nvarchar(100) NOT NULL, --cne 课程名 courses namecpd int NOT NULL, --cpd课程学时 courses periodcct tinyint NOT NULL, --cct 课程学分 courses creditcte nchar(20) NOT NULLCONSTRAINT CK_courses_type CHECK(cteoptional_course or cterequired_course) --cte课程类型 courses type
)--成绩表
-- 检查并删除表 score
IF EXISTS (SELECT * FROM sys.objects WHERE name score AND type U)DROP TABLE score;
CREATE TABLE score
(sno char(8) NOT NULL, cno char(10) NOT NULL,CONSTRAINT PK_score_sno_cno PRIMARY KEY(sno, cno),get tinyint, --get 成绩得分CONSTRAINT CK_score_get CHECK(get0 and get100),CONSTRAINT FK_score_sno FOREIGN KEY (sno) REFERENCES students(sno), CONSTRAINT FK_score_cno FOREIGN KEY (cno) REFERENCES courses(cno)
)--用户表
-- 检查并删除表 users
IF EXISTS (SELECT * FROM sys.objects WHERE name users AND type U)DROP TABLE users;
CREATE TABLE users(uln char(100) NOT NULL CONSTRAINT PK_users_uln PRIMARY KEY(uln), --uln 用户登录 users loginune nchar(100) NOT NULL, --une 用户名 users name upd char(10) NOT NULL -- upd 用户登录密码 users password
)-- 检查是表中的字段是否完整
--SELECT * FROM users
SELECT * FROM studentsSELECT * FROM deptSELECT * FROM classesSELECT * FROM score 2使用ALTER TABLE修改表
有些时候已经创好表点击执行之后会存在少列少约束等问题。这些问题都不用担心能够在创建好数据表后添加。
可以使用ALTER TABLE... DROP COLUMN...删除列ALTER...ADD...添加列。
1从表中删除列
语法如下
ALTER TABLE 数据表名 DROP COLUMN 要删除的列名无任何约束条件 需要注意的是删除的列必须没有任何约束这里以用户表users为例我现在删除主键列login
可以看到因为有约束条件-主键约束所以该列无法删除。
但是如果我想要删除没有任何约束的列pwd是完全可以删除的如下 2向表中添加列 语法如下
ALTER TABLE 数据表名 ADD 新列的名字 新列的数据类型
依旧可以使用ALTER TABLE关键字将删除的列pwd再添加回来 3修改表中列的长度
语法如下
ALTER TABLE 数据表明 COLUMN 列名(新的长度)
以上面的用户表users为例将列pwd的长度由原来的10变成20可以如下编写语句
ALTER TABLE users ALTER COLUMN pwd char(20) 如果想要查看修改的长度是否生效可以使用如下语句查看列的长度
--查看列的长度
SELECT column_name AS 列名, data_type AS 数据类型, character_maximum_length AS 数据长度
FROM information_schema.columns
WHERE table_name users AND column_name pwd; 如上长度修改成功。
4对指定表添加默认约束 这里以学生表students为例向学生表中添加新列nation国籍 nvarchar(10)默认值为“中国”。 ALTER TABLE studentsADD nation nvarchar(10) CONSTRAINT DF_students_nation DEFAULT(中国) 通过上图可以无法直观的感受到默认值的优势后面插入数据的时候会更直观。
ALTER TABLE courses ADD CONSTRAINT DF_courses_cte DEFAULT required_course FOR cte
INSERT INTO dept(dno,dne,ddn,dpe)
VALUES(1,Computer,QiangguoGong,1531111111)INSERT INTO classes(cno,cne,ccr,dno)
VALUES(1,Computer24,JianjunSong,1)INSERT INTO students(sno,sne,ssx,sbd,spe,cno)
VALUES(1,zs,male,2000.1.1,18911111111,1)INSERT INTO courses(cno,cne,cpd,cct)
VALUES(1,SQLserver DataBase Application principle,96,6)INSERT INTO users(uln,une,upd)
VALUES(SQL_identity,sa,123456) 3使用DROP TABLE删除表 能够创建表自然也就能够删除表。删表的语法如下
DROP TABLE 数据表名 有问题请在评论区留言或者是私信我回复时间不超过1天。
