前记

已经好长时间没有记录数据库笔记了，总是被各种各样的时候所牵绕着，总是忙来忙去却不知道忙些什么，想要记录笔记，总是抽不开身，于是愈发浮躁。从今日起，需要屏气凝神，认真完成学习笔记，按时复习，减少精神的内耗，让身心获得平静

SQL语言及其操作

SQL语言的特点：

功能一体化：由三个子语言组成（DDL,DML,DCL）

语言非过程化

交互式和嵌入式使用

标准化和易移植性

简单易学

DDL的操作：

数据库（模式）的建立、删除、修改

数据库日志的建立、删除、修改

数据库表的建立、删除、修改

视图的建立、删除、修改

索引的建立、删除

规则的建立、删除

触发器的建立、修改、删除

默认值的建立、删除

函数Function的建立、修改和删除

定义子语言的关键字以及语法

子语言的关键字有：1.Create 2.Drop 3.Alter

注意：Index Rule Default没有Alter命令

默认值的定义

设立一个全局的缺省值，可以进行多次复制

CREATE  DEFAULT  dft_term  AS  '1' --  定义一个默认值，名字为dft_term,内容为1
        sp_bindefault  dft_term,'ctake.term' -- 将默认值和字段ctake.term绑定
        sp_unbindefault  'ctake.term'          -- 接触绑定
        DROP  DEFAULT  dft_term             -- 删除该默认值

规则的定义

CREATE  RULE  mark_rule  AS @mark between 0 and 100 --定义一个规则，将mark的值规定在0-100
       sp_bindrule mark_rule,'Ctake.mark'   -- 将mark_rule”规则绑定到“Ctake.mark”列，这意味着每当该列的值被插入，更新或删除时，规则将被触发。
       sp_unbindrule  'Ctake.mark'  --mark_rule”规则从“Ctake.mark”列中解除绑定，这意味着规则将不再被调用。
       DROP RULE mark_rule-- 删除该规则

数据库和日志

日志的概念：是数据库故障恢复的重要手段和方法。用于记录对数据库的各种操作及所涉及的相关数据

创建数据库和日志

CREATE database 彩票管理系统
ON PRIMARY 
(
  Name = 彩票管理系统,
  Filename ="D:\sql\彩票\彩票管理系统.mdf", --记录文件的路径和名字
  size= 50MB,                         -- 文件的初始大小
  MaxSize =1000MB,              -- 文件的最大值
  FileGrowth =5MB                -- 文件大小的增量  
)
Log On
(
  Name=彩票管理系统日志,
  Filename="D:\sql\彩票\彩票管理系统.ndf",
  Size=10MB,
  MaxSize=1000MB,
  FileGrowth=5MB
)

修改数据库和日志

1.增加文件

 ALTER Database StudentData 
ADD File                           -- 增加文件的标志
 (Name       = StudentFile3,
  Filename   ='c:\data\StuFile3.ndf',
  Size       = 10MB,
  MaxSize    = 1000MB,
  FileGrowth = 5MB)

2.修改文件

ALTER Database StuData 
MODIFY FILE                         --修改文件
 (Name     = StudentFile1,    -- 修改后的名字
  MaxSize  = 3000MB           --修改后的最大容量
 )

删除数据库和日志

1	Drop Database StuData

数据表的操作

创建数据表

数据表的创建在第三章就已经记录，此处不再阐述

修改数据表

1.修改字段属性

例：修改关系的地址属性

1 2	Alter table department --先声明对具体的表进行操作 alter column adress varchar(50) --alter column是操作关键字

2.增加字段

1 2	Alter table department --先声明对具体的表操作 Add director varchar(10) -- add 是关键字， director是字段的名字

3.添加约束

1 2	alter table department add constraint tele_check check(telephone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

其中 constraint是约束的关键字，tele_check是约束的名字

4.删除约束

1 2	alter table department drop constraint tele_check -- drop 是关键字，tele_check是约束的名字

5.增加唯一约束

1 2	Alter table Student add constraint SName_Unique unique(SName)

constraint 是约束的关键词，SName_Unique是约束的名字，unique是唯一约束的关键字

删除数据表

1	Drop table s

例题

具体代码如下

CREATE TABLE  ESalary
 ( EID char(8) foreign key references Employee(EID)),
   Month char(10) NOT NULL,
      BSalary smallmoney,
      PSalary smallmoney,
      ESalary smallmoney,   
      ASalary as BSalary+PSalary+ESalary，
      Primary key(EID,Month)
    )

需要注意的一点是： ASalary as BSalary+PSalary+ESalary是是T-SQL中计算列的语法，它的作用是动态计算生成一个列的值

视图的操作

为什么要使用视图

在我学习视图的时候也是一直存在着这个疑问，视图的作用是什么？

通过上网查询，视图最大的作用就是：当用户对数据库中的一张或者多张表的某些字段的组合感兴趣，而又不想每次键入这些查询时（即每次都使用select对多个表查询），用户就可以定义一个视图，以便解决这个问题。视图中列可以来自于表里的不同列，这些列都是用户所感兴趣的数据列。

视图的创建和修改

通用模板

1
2
3

CREATE/ALTER  VIEW [ [拥有者.]视图名 [ (视图属性名表) ]
  AS   SQL 查询语句
  [ WITH CHECK OPTION ]

1.单表视图的创建

1
2
3

CREATE VIEW 彩票_2021114062(彩票价格,彩票特征码,发行部门编号)   -- 查询彩票种类为双色球的特征码，价格和发行部门的编号
AS
select 价格,特征码,部门编号 from 彩票 where 种类='双色球'

2.多表视图的创建

CREATE VIEW V_IS_S1(Sno,Sname,Grade) AS
SELECT Student.Sno,Sname,Grade FROM Student JOIN SC 
ON Student.Sno=SC.Sno WHERE Sdept=‘信息管理系’ AND 
SC.Cno=‘C001’

该视图含有三个字段(Sno,Sname,Grade),且这三个字段来自不同的表，通过连接并条件筛选后导出满足条件的三个字段所有的值来组成视图

3.在已有的视图上定义新的视图

1 2	CREATE VIEW IS_Student_Sage AS SELECT Sno,Sname,Sage FROM IS_Student WHERE Sage<20

注意：IS_Student是一个视图,在此基础上生成新的视图,新视图的字段和旧视图的字段完全一致

4.定义带表达式的视图

1 2	CREATE VIEW BT_S (Sno,Sname,Sbirth) AS SELECT Sno,Sname,datepart(yyyy,getdate()-Sage FROM Student)

其中datepart(yyyy,getdate()-Sage FROM Student 就是计算出生的月份

Sage FROM Student是一个整体，用来获取当前的年龄

视图的修改

1	DROP VIEW StuCourse

索引的操作

概念：索引是关于数据位置信息的关键字表

目的：快速在磁盘上定位所需的数据，加快查询的速度

结构：索引的建立采用B树

索引分类：聚集索引，非聚集索引，唯一索引

唯一约束：可以在创建表时用（设置主键约束）来创建唯一索引，也可以用（设置唯一约束）来创建唯一索引。

为什么使用索引

对于这个问题，课本上以快速在磁盘上定位所需的数据，加快查询的速度一笔带过，但是具体为什么还是不理解。下面谈谈我的理解

对于新华字典，如果你想查一个字，肯定是先查偏旁部首，再根据偏旁部首所在的页数迅速定位到字所在的页数，再从这一页中查询所需要的字，相比于每一页都去找这个字，索引大大提高了速度。

对于SQL语言也是这样，数据库在执行一条SQL语句的时候，默认的方式是全盘扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

索引的创建

关键字 index

通用模板

1 2	CREATE [ UNIQUE ] [ CLUSTERED \| NONCLUSTERED ] INDEX <索引名> ON < 表名 \| 视图名> ( 列名[ ASC \| DESC ] [ , ... ] )

例如：

1 2	CREATE INDEX StuGraIDx ON SC (Sid) DESC

需注意，一个表只能由一个聚集索引，且在创建表的时候系统默认创建聚集索引，则如果想自定义聚集索引，应该先删除之前的聚集索引

而非聚集索引可以有多个

非聚集索引和聚集索引的区别

数据不按非聚集索引关键字值的顺序排序和存储。

非聚集索引的叶级节点不是存放数据的数据页面

索引例题

1	create clustered index W_id_index on writers(w_id) desc

1	create index Multildx on writers(w_name,w_address) --不表明索引类型，默认是非聚集索引

触发器

概念：数据库触发器是存储在数据库中、根据发生的事件而自动触发执行的一种存储子程序。触发器是由一个事件来启动运行，即触发器是当某个事件发生时自动地隐式运行。

触发器的触发:系统自动在内存中创建Deleted表和inserted表，这两张表是逻辑表，不是存储在数据库中，而是存储在内存中

触发器的创建和修改

模板如下

create trigger trigger_name
on table_name
for [DELETE,INSERT,UPDATE]
AS
  T-SQL语言
GO -- GO的作用单纯就是分割不同的SQL语句

触发器的启动，禁止和删除

启动和禁止

1 2	ALTER TABLE 表名 { ENABLE \| DISABLE} 触发器名称 -- ENABLE 代表启动 DISABLE代表禁止

删除

1	DROP TRIGGER {触发器名} [,...n]

例题

触发器的创建分为显示触发器和动态触发器

显示触发器：

CREATE  TRIGGER  Cls_Delete   ON  Class
  FOR  DELETE  
  AS
     DELETE Student from deleted,Student where 
    Student.CNo=deleted.CNo
Go

动态触发器 (向彩票中插入，如果不是以下的几种就回滚)

CREATE TRIGGER 彩票种类_2021114062 ON 彩票
FOR INSERT
AS 
BEGIN
 if((select 种类 from inserted) NOT IN ('双色球', '大乐透', '3D', '排列三', '重庆时时彩'))
 BEGIN
 print'彩票种类插入错误' 
  ROLLBACK TRANSACTION;
 END
 else
  BEGIN
  print'插入成功'
  COMMIT TRANSACTION;
  END
END
GO

DML

DML 的四个关键字 1.INSERT 2.UPDATE 3.DELETE 4.SELECT

INSERT:这个没什么好说的，就是INSERT INTO TABLE_NAME VALUES

UPDATE

简单的更新

1	UPDATE SC SET Score = Score + 10 -- 将该字段所有的数据的值加10

带有WHERE的更新

1 2	UPDATE authors SET state = 'PC', city = 'Bay City' WHERE state = 'CA' AND city = 'Oakland'

SELECT 子查询的结果进行数据修改

1
2
3

UPDATE SC    SET Score = Score * （1+10%)
   WHERE SC.Sid IN
   （SELECT Sid FROM S WHERE GENDER='F')

DELETE

单表数据的删除

1.把课程名为MATHS的选课记录从SC中删除。

1
2
3

DELETE  FROM SC 
       WHERE Cid IN ( SELECT Cid  FROM C 
		      WHERE Cname = 'Maths' )

2.把选了C4课程，但小于该课程平均成绩的元组从基本表SC中删除

1
2
3

DELETE  FROM SC    WHERE Cid ='C4'
      AND SCORE < ( SELECT AVG (SCORE)
      FROM SC    WHERE Cid = 'C4' )

其中 AVGS是聚集函数，后面会讲到

多表连接数据删除

1 2	Delete SC From SC, S Where SC.sid=S.sid AND S.name='李舢'

SELECT

对于之前select的认知，往往是查询一个表，即

1	select*from table_name

但是select 的模板完全体是这样的

SELECT 列表  [ INTO  新表名 ]  FROM <源表>
    [WHERE 条件表达式 ]    
    [ GROUP BY 分组表达式 ] 
    [ HAVING  组内数据条件表达式 ] 
    [ ORDER BY  排序表达式 [ ASC | DESC ] ] 
    [ COMPUTE 
     {{AVG|COUNT|MAX|MIN|SUM}(表达式)}[,...n]     
         [ BY expression [ ,...n ] ]]

我们慢慢来展开讲解

select 查询子句

对于这一个只需要记住

1 2	select distinct 字段名 from table_name select 字段名 from table_name

select Into

创建一个新的表，并将查询结果插入表中

1	select *into SC1 FROM SC WHERE CID=

其中 SC1就是新的表名，SC1的字段属性与SC完全一致

FROM 和 WHERE 子句

这个也没什么好说的，记住一些比较操作就可以了

算术比较	Where books.price>=20	比较两个表达式的值
范围的判断	Where books.price [NOT] BETWEEN 10 AND 20 10和20也是满足条件的	表达式的值在/不在给定的范围内
相同模式判断	Where Did [NOT]LIKE ‘D[0-9][0-9]’	字符串与给定模式是否相同
之内的判断	Where Sid IN (select-from-where子句/集合）	某元组值是否在某子句（集合）内
限定比较判断	Where SC.Score >= all/any/some (select-from-where子句）	与子句（集合）的所有/任何/某些结果比较
存在判断	Where [NOT]EXISTS(select-from-where子句）	子句（集合）是否至少存在一个元组

LIKE 模式匹配：

％（匹配任意一串字符)：可以为空字符

_（匹配任意一个字符）：不可以为空字符

[ ]（取其中任意单个字符）

^（非）

例如：将 u_name 为“张三”、“张猫三”、“三脚猫”、“唐三藏”等有“三”的记录全找出来

1	SELECT * FROM [user] WHERE u_name LIKE '%三%'

_:它常用来限定表达式的字符长度语句

例如：只找出“三脚猫”这样 name 为三个字且第一个字是“三”的

1	SELECT * FROM [user] WHERE u_name LIKE '三__'

注意三后面有两个”_”,保证了三个字

[]:指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个

1	SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'

查询张三，李三，王三

^:非，用上面那个例子举例

1	SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'

查询不是张三，李三，王三以外的以三位结尾的

数据库第四章内容梳理

前记