看一眼就能学会的数据库T-SQL语句创建、添加、查询、更新、删除、完整性约束

目录

一、数据库

二、数据库表

三、表中数据的操作

四、完整性约束

五、数据的查询

六、索引

七、视图

---

一、数据库

内容：创建数据库

CREATE DATABASE testbase1

ON
    ( NAME=testbase1_data,               --数据文件名字

      FILENAME='A:\testbase1_dat.mdf' )  --存储路径


LOG ON
    ( NAME=testbase1_log,                --日志文件名字

      FILENAME='A:\testbase1_log.ldf' )  --存储路径

---

 

二、数据库表

内容：

• 创建数据库表
• 增加新的列
• 修改列的属性如数据类型或是否空值NULL
• 删除数据库表

1、创建数据库表

CREATE TABLE C ( CNO CHAR(2),
 
                 CN CHAR(10),

                 CT TINYINT )

 

2、增加新的列

如：向数据库表S中添加列NATIVE

ALTER TABLE S

       ADD NATIVE CHAR(10) NULL

 

3、修改列的属性如数据类型或是否空值NULL

如：修改数据库表S中的列NATIVE的数据类型

ALTER TABLE S

       ALTER COLUMN NETIVE CHAR(40) NULL

 

4、删除数据库表

DROP TABLE T

---

 

三、表中数据的操作

内容：

• 录入数据即插入元组（INSERT INTO）
• 更新元组中某项数据的值（UPDATE）
• 删除元组(DELETE)

1、插入元组（INSERT INTO）

如：向表C中添加一组数据

INSERT INTO C VALUES('C1','程序设计','60')

 

2、更新元组（UPDATE）

如：修改数据库表T中的数据，把教师“王平”的职称PROF改为“副教授”

UPDATE T 

    SET PROF='副教授'    --更新修改PROF的值

    WHERE TN='王平'      --限制条件—只有满足该语句的元组才修改更新值
                          
                         --即只有满足教师名字是‘王平’的一行元组才修改职称PROF为副教授

 

3、删除元组 (DELETE)

如：删除数据库表T中的数据，删除教师“王平”的记录数据。

DELETE 
 
    FROM T            --指定操作对象是表T

    WHERE TN='王平'   --限制条件—只有满足教师名字是‘王平’的一行元组才执行

---

 

四、完整性约束

内容：

• 实体完整性：主键 PRIMARY KEY、UNIQUE(确保列的值是唯一的，无重复的)、IDENTITY（自动编号）
• 参照完整性：外键 FOREIGN KEY
• 用户定义的完整性：不为空 NOT NULL、约束列的值范围 CHECK 、默认值 DEFAULT（未赋值时有效）

1、主键 PRIMARY KEY

如：列级定义（只有一个列属性为主键时）——为表S的列学号SNO设置为主键

CREATE TABLE S ( 
                 SNO CHAR(10) PRIMARY KEY ,   --设置为主键

				 SN  CHAR(5)  NOT NULL    ,   --不允许为空值
)  

如：表级定义（有两个以上列属性为主键时）——为表C的列学号SNO和课程号CNO都设置为主键

CREATE TABLE C ( 
                  SNO CHAR(10) ,

				  CNO CHAR(2)  ,

				  PRIMARY KEY (SNO,CNO)   --主键			
) 

2、外键 FOREIGN KEY

如：课程表C的学号SNO 参照学生表S的学号SNO

CREATE TABLE C ( 
                  SNO CHAR(10) ,

				  FOREIGN KEY (SNO) REFERENCES S (SNO)   --参照完整性

)                 --外键是列SNO，REFERENCES参照学生表S的学号SNO

 

3、不为空 NOT NULL、约束列的值范围 CHECK 、默认值 DEFAULT（未赋值时有效）

CREATE TABLE S(
				SNO  CHAR(10) PRIMARY KEY,  --主键
				SN   CHAR(5)  NOT NULL,     --不允许为空

				SEX  CHAR(5)  NULL CHECK (SEX='男' OR SEX='女'),
                --CHECK约束列的值范围，列性别值只能是男、女

				AGE  TINYINT  NULL CHECK (AGE>=1 AND AGE<=100),
                --CHECK约束列的值范围，列年龄的值范围为1-100

				DEPT CHAR(10) NULL DEFAULT '物联网'
                --DEFAULT 设置默认值为‘物联网’  
)
       

 

4、创建UNIQUE约束、IDENTITY约束

ALTER TABLE C
		ADD UNIQUE (CN)  --值唯一，确保了CN列不会出现重复的值

---

 

五、数据的查询

内容：从表（FROM）中查询满足限制条件（WHERE）的一些列的值，查询出来后进一步处理——有重复的合并GROUP BY、查询结果排序ORDER BY（升序ASC、降序DESC）

查询对象：单表、多表（连接查询）、嵌套查询（将一个查询结果继续作为查询条件查询）、集合查询（两个表的结果做 并操作UNION、交操作INTERSECT、差操作EXCEPT）

案例：

• 查询学生表的所有学生信息、所有男生信息、年龄19岁的所有女生信息。

--从一个表中选择
select *    --*代表选择所有列
from   s      --从表s中选择

select *    --*代表选择所有列
from   s      --从表s中选择
where  SEX='男'   --限制条件：只选择满足性别是男的一行元组才被选出

select *
from   s
where  SEX='女' and AGE=19  --限制条件:性别是女、年龄是19

---

• 查询显示所有学生学号、课程号、成绩。

--从多个表中选择
select STU.SNO, KC.CNO, SCORE.score   
--学生表的学号、课程表的课程号、分数表的成绩
from   STU,KC,SCORE           --3个表：学生、课程、分数
where  STU.SNO=SCORE.SNO and KC.CNO=SCORE.CNO 
--限制条件：学生学号=课程学号，课程课程号=分数课程号

---

• 查询成绩不及格学生的学号、姓名和课程名称。

select STU.SNO, SN, CN
from   STU,KC,score
where  score<60 and STU.SNO=SCORE.SNO and KC.CNO=SCORE.cno 
--限制条件：分数小于60，学生学号=课程学号，课程课程号=分数课程号

---

• 显示数据库前三名的学生学号、姓名。

select top 3 SNO, SN  --top 3代表选择前三个元组
from   STU

---

• 倒序显示数据库学生成绩。升序ASC 、降序DESC

SELECT score       --选择成绩
FROM   SCORE       --从成绩表SCORE中选择
ORDER  BY score DESC   --把分数按降序排

---

• 显示数据库最高成绩、最低成绩和平均成绩。聚集函数（在select语句中使用）（比较的是一列中所有的值）：MAX、MIN、AVG

SELECT MAX(SCORE),MIN(SCORE),AVG(SCORE)   --聚集函数
FROM   SCORE     --从成绩表中选

---

• 查询所有学生的姓名及其出生年份。

SELECT SN,year(GETDATE())-AGE AS 出生年份  
FROM S 

---

• 查询姓李的学生的姓名、学号和性别。

SELECT SN,SNO,SEX
FROM S
WHERE SN LIKE '李%'

---

• 查询名字中第二个字为“明”的男学生的姓名和系名。

SELECT SN AS 姓名,DEPT AS 系名
FROM S
WHERE SN LIKE '_明%' AND SEX='男' 

通配符：_ 和 % 

_表示占一个位置，%表示占无限位置。

---

• 查询信息系、计算机系学生的姓名、系名，结果按系名升序排序，姓名按降序排序。

SELECT SN AS 姓名,DEPT AS 系名
FROM S
WHERE DEPT IN ('信息','计算机')
ORDER BY DEPT ASC,SN DESC

---

• 查询计算机系学生总人数

SELECT COUNT(*) AS 计算机系人数
FROM S
WHERE DEPT ='计算机'

---

 

六、索引

索引意思：简单说就是一个列或多列的外号，别名，当我们找查询该列或一些列时可以直接使用索引来查询。

分类：UNIQUE（每一个索引值对应唯一的数据记录）、CLUSTERED（聚簇索引）、NONCLUSTERED（非聚簇索引）

聚簇索引——相当于数组形式，每个表只能有一个,当创建PRIMARY KEY时，系统会自动在此PRIMARY KEY键上按升序创建一个聚簇索引。

非聚簇索引——相当于指针形式，每个表最多创建259个，创建非聚簇前必须先创建聚簇索引，同一索引最大能包含16个列。

如：为数据库表T在TNO列上按降序创建聚簇索引 IND_TNO。

    CREATE CLUSTERED INDEX IND_TNO   ON   T    (TNO DESC)
  --CREATE 索引类型   索引  索引名字  ON  表名 (列名 升序还是降序) 

如：为数据库表C在CN列上按升序创建唯一索引IND_CN

    CREATE UNIQUE INDEX IND_CN ON  C  (CN ASC)
    --创建   唯一  索引  索引名   表名 (列名 升序)

 如：删除数据库表C中的索引IND_CN

    DROP INDEX C.IND_CN

---

 

七、视图

视图也是表，不过是一个虚表，是从一个或几个基本表导出的表。数据库中只存储视图的定义而不存储视图中的数据，视图也可以被查询、删除，还可以在视图上再定义一个视图。

如：创建一个课程表视图，视图名称是View_CTABLE，其数据来源于两个基本表T、C，包含的数据有来自数据库表T的字段TN和来自数据库表C的字段CN

USE jxsk
GO          --需要写GO
CREATE VIEW View_CTABLE
AS             --子查询
    SELECT TN,CN
	FROM   T,C,TC
    WHERE  T.TNO=TC.TNO AND C.CNO=TC.CNO
GO

如：为视图View_CTABLE增加一个课时字段CT tinyint

USE jxsk
GO 
ALTER VIEW View_CTABLE
AS  
    SELECT TN,CN,CT
	FROM   T,C,TC
	WHERE  T.TNO=TC.TNO AND C.CNO=TC.CNO
GO

如：为视图View_S增加一行数据

INSERT INTO VIEW_S VALUES('古明','男','自动化','S1'

如：对视图View_S执行DELETE语句

USE jxsk
DELETE View_S 
WHERE  SNO='S8'

如:删除视图View_S、View_CTABLE

DROP VIEW View_S
DROP VIEW View_CTABLE

---

——每天八杯水