SQL-知识点

SQL简介

SQL（ Structured Query Language 结构化查询语言 ）是用于访问和操作数据库中的数据的标准数据库编程语言。

• SQL 是关系数据库系统的标准语言。

• SQL 是一种 ANSI（ American National Standards Institute 美国国家标准化组织 ）标准的计算机语言。

 

RDBMS

RDBMS，指关系型数据库管理系统，全称 Relational Database Management System。

 

关系数据库系统

• 关系数据库系统指对应于一个关系模型的所有关系的集合。

• 关系数据库系统建立了关系模型，并用它来处理数据。关系模型在表中将信息与字段关联起来（ 也就是 schemas ），从而存储数据。

 

关系模型

 （1）关系 ( Relation )：一个关系对应着一个二维表，二维表就是关系名。

 （2）元组 ( Tuple )：在二维表中的一行，称为一个元组。

 （3）属性 ( Attribute )：在二维表中的列，称为属性。属性的个数称为关系的元或度，列的值称为属性值。

 （4）( 值 ) 域 ( Domain )：属性值的取值范围为值域。

 （5）分量：每一行对应的列的属性值，即元组中的一个属性值。

 （6）关系模式：在二维表中的行定义，即对关系的描述称为关系模式。一般表示为（ 属性 1，属性 2，......,属性 n ），如老师的关系模型可以表示为教师（ 教师号，姓名，性别，年龄，职称，所在系 ）。

 （7）键 ( 码 ) ：如果在一个关系中存在唯一标识一个实体的一个属性或属性集称为实体的键，即使得在该关系的任何一个关系状态中的两个元组，在该属性上的值的组合都不同。

 （8）候选键 ( 候选码 ) ：若关系中的某一属性的值能唯一标识一个元组如果在关系的一个键中不能移去任何一个属性，否则它就不是这个关系的键，则称这个被指定的候选键为该关系的候选键或者候选码。

 例如下列学生表中 “ 学号 ” 或 “ 图书证号 ” 都能唯一标识一个元组，则 “ 学号 ” 和 “ 图书证号 ” 都能唯一地标识一个元组，则 “ 学号 ” 和 “ 图书证号 ” 都可作为学生关系的候选键。

学号	姓名	性别	年龄	图书证号	所在系
S3001	张明	男	22	B20050101	外语
S3002	李静	女	21	B20050102	外语
S4001	赵丽	女	21	B20050301	管理

而在选课表中，只有属性组 “ 学号 ” 和 “ 课程号 ” 才能唯一地标识一个元组，则候选键为（ 学号，课程号 ）。

学号	课程号
S3001	C1
S3001	C2
S3002	C1
S4001	C3

 （8）主键（ 主码 ）：在一个关系的若干候选键中指定一个用来唯一标识该关系的元组，则称这个被指定的候选键称为主关键字，或简称为主键、关键字、主码。每一个关系都有并且只有一主键，通常用较小的属性组合作为主键。例如学生表，选定“学号”作为数据操作的依据，则“学号”为主键。而在选课表中，主键为（ 学号，课程号 ）。

 （9）主属性和非主属性：关系中包含在任何一个候选键中的属性称为主属性，不包含在任何一个候选键中的属性为非主属性。

 （10）全键或者全码：一个关系模式中的所有属性的集合。

 （11）外键或者外码：关系中的某个属性虽然不是这个关系的主键，或者只是主键的，但它却是另外一个关系的主键时，则称之为外键或者外码。

 （12）超键或者超码：如果在关系的一个键中移去某个属性，它仍然是这个关系的键，则称这样的键为关系的超键或者超码。

 （13）参照关系与被参照关系：是指以外键相互联系的两个关系，可以相互转化。

 

实体与关系

实体是指客观存在并可相互区别的事物，既可以是实际的事物，也可以是抽象的概念或关系。

• 一对一关系：是指表 A 中的一条记录确实在表 B 中有且只有一条相匹配的记录，在一对一关系中，大部分相关信息都在一个表中。

• 一对多关系：是指标 A 中的行可以在表 B 中有许多匹配行，但是表 B 中的行只能在表A中有一个匹配行。

• 多对多关系：是指关系中每个表的行在相关表中具有多个匹配行。在数据库中，多对多关系的建立是依靠第3个表（ 称作连接表 ）实现的，连接表包含相关的两个表的主键列，然后从两个相关表的主键列分别创建与连接表中的匹配行的关系。

 

一些专业术语

• 数据库系统（ DataBase System，DBS ）

• 数据库管理系统（ DataBase Management System，DBMS ）

• 数据库管理员（ DataBase Adminnistrator，DBA ）

• 数据库定义语言（ Data Definition Language，DDL ）

• 数据控制语言（ Data Control Language，DCL ）

• 数据操纵语言（ Data Manipulation Language，DML ）

• 存储过程（ Stored Procedure，SP ）

 

SQL Server数据库组成（ 文件、文件组 ）

• 文件：存储数据库中所有数据和对象（ 如表、存储过程和触发器 ）。

‥ 主要数据文件：存放数据和数据库的初始化信息。每个数据库有且只有一个主要数据文件，默认扩展名是 .mdf。

‥ 次要数据文件：存放除主要数据文件以外的所有数据文件。有些数据库可能没有次要数据文件，也可能有多个次要数据文件，默认扩展名 .ndf。

‥ 事务日志文件：存放用于恢复数据库的所有日志信息。每一个数据库至少有一个事务日志文件，也可以有多个事务日志文件，默认扩展名 .ldf。

• 文件组：数据文件的一种逻辑管理单位，将数据库文件分成不同的文件组，方便于对文件的分配和管理。

‥ 主文件组：包含主要数据文件和任何没有明确指派给其他文件组的文件。系统表的所有页都分配在主文件组中。

‥ 用户定义文件组：主要是在 CREATE DATABASE 或 ALTER DATABASE 语句中，使用 FILEGROUP 关键字指定的文件组。

 

局部变量

• 声明局部变量：

DECLARE @name tye; 

• 为局部变量赋值：

SELECT  @name=key from table_name WHERE  lieming = ''

还可以使用 SET 语句：

set @name=key

 

通配符

通配符	示例
%	"loving%" 可以表示： "loving" "loving you" "loving?"
_	"loving" 可以表示："lovingc"，后面只能接一个字符
[ ]	[0~9]123 表示以 0~9 任意一个字符开头，以 '123' 结尾的字符
[^ ]	[^0~9]123 表示不以 0~5 任意一个字符开头，却以 '123' 结尾的字符

 

SQL的语法

• SELECT DISTINCT（ 选择不同 ）

• 用于仅返回不同的（ different ）值，在一张表内，一列通常包含许多重复的值; 有时你只想列出不同的（ different ）值。

SELECT DISTINCT column1, column2, ...
FROM table_name;

eg：

从"Customers" 表中的 "Country" 列中选择DISTINCT值：

SELECT DISTINCT Country FROM Customers

列出了不同（ distinct ）客户国家的数量：

SELECT COUNT(DISTINCT Country) FROM Customers    
 --Microsoft Access数据库中不支持COUNT( DISTINCT _columnname )

• 修改非空约束

USE db_name;
ALTER TABLE table_name
alter column column_name column_type null|nut null;

eg：

修改 mingri 表中的非空约束：

USE db_Test;
ALTER TABLE mingri
alter column ID int null; 

• SELECT 

主要子句归纳：

[ WITH <common_tableexpression>]----------------指定临时命名的结果集

SELECT select_Jlist [ INTO new_table ]

[ FROM table_source ][ WHERE search_condition]

[ GROUP BY group_by_expression] [ HAVING search_condition]

[ ORDER BY order_expression [ ASC | DESC ] ]

 

存储过程

存储过程	用法	举例
sp_addtype	创建用户自定义数据类型	use db_name exec sp_addtype 'type_name','type_type','null/not null/nonull'
sp_help	查看某表的相关信息	exec sp_help 'table_name'
sp_renamedb	修改数据库名称	exec ap_renamedb 'db_name','edit_name'

 

部分命令

BACKUP

     将数据库内容或其事务处理日志备份到存储介质上（ 硬盘或磁带等 ）

语法结构：

Backup Database { database_name |@database_name_var }
To < backup_device >[....n]
[<MIRROR TO clause>][ next-mirror-to ]
[ WITH{ DIFFERENTIAL |<general_WITH_options>-[...n ]}]
[;]

eg：

backup DATABASE db_Test TO disk='backup.bak'

CHECKPOINT

     检查当前工作的数据库中被更改过的数据页或日志页

语法结构：

CHECKPOINT[ checkpoint_duration ]‘’

eg：

USE db_Test;
CHECKPOINT;

DBCC CHECKALLOC

          检查指定数据库的磁盘空间分配结构的一致性

eg：

 DBCC CHECKALLOC('db_Test')

DBCC SHOWCONTIG

     显示指定表的数据和索引的碎片信息

eg：

declare @id int,@indid int;
set @id = OBJECT_ID('tb_Course');
select @indid=index_id
from sys.indexes
where object_id = @id and name = 'PK_tb_Course';
DBCC SHOWCONTIG(@id,@indid);

GOTO

     改变程序执行的流程，使程序跳到标识符指定的程序行再继续往下执行

语法结构：

GOTO 标识符

--标识符需要在其名称后加上一个冒号‘’ : ‘’

eg：

declare @q int;
set @q = 0;
loving:
print @q;
set @q = @q+1;
while @q<=3 goto loving;

GROUPING SETS

IF EXISTS

RAISERROR

     用于在 SQL Server 系统中返回错误信息时同时返回用户指定的信息

READTEXT

     用于读取 text、ntext 或 image 列中的值，从指定的位置开始读取指定的字符数。

READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]

RESTORE

     将数据库或其事务处理日志备份文件由存储介质还原到 SQL Server 系统中

 RESTORE DATABASE {detabase_name | @database_name_var}
[FROM <backup_device>[....n]]
[WITH
　　{
　　　　[ RECOVERY | NORECOVERY | STANDBY = {standby_file_name |@standby_file_name_var }
　　　　]
　　|, <general_WITH_options>[ ,...n ]
　　　　|,<replication_WITH_option>
　　|.<change_data_capture_WITH_option>
　　　　|,<service_broker_WITH options>
　　　　|,<point_in_time_ WITH_options-RESTORE_DATABASE>
　　}[,... n ]
]
*：执行还原操作时，需要将使用的数据库更改为 master 数据库

eg：

RESTORE DATABASE db_Test FROM disk='backup.bak' WITH REPLACE

SHUTDOWN

     立即停止 SQL Server 的执行

语法结构：

SHUTDOWN[ WITH NOWAIT ]
--有NOWAIT时，立即终止所有的用户过程，并对每一现行的事务发生一个回滚后退出 SQL Server
--无NOWAIT时;
--(1) 终止任何用户登录 SQL Server
--(2) 等待尚未完成的T-SQL命令或存储过程执行完毕
--(3) 在每个数据库中执行 CHECKPOINT 命令
--(4) 停止 SQL Server 的执行

eg：

SHUTDOWN WITH NOWAIT

STUFF

WAITFOR

      指定触发器、存储过程或事务执行的时间、时间间隔或事件；还可以用来暂时停止程序 的执行，直到所设定的等待时间已过才继续往下执行。

WAITFOR DELAY<'时间’>ITIME<’时间’>;

--其中，“时间”必须为 DATETIME 类型的数据，如“11:15:27”, 但不能包括日期。各关键字含义如下。
--DELAY：用来设定等待的时间，最多可达 24 小时。
--TIME：用来设定等待结束的时间点。

eg：

waitfor delay '00:00:03';
print '祝你生日快乐！';

waitfor time '10:33';
print '《新三国演义》开始了';

WRITETEXT

      允许对数据类型为 text，ntext 或 image 的列进行交互式更新

语法结构：

WRITETEXT { table.column text_ptr }
[ WITH LOG ] { data }

 

注意

 • left join , right join , inner join , full join 之间的区别

 left join , right join , inner join , full join 之间的区别

• SQL 对大小写不敏感：SELECT 与 select 是相同的。

• 一些数据库系统需要在每个 SQL 语句的末尾使用分号，所以在平时学习练习中，在语句末尾都最好加上分号

• 注释符为“--”

• 单引号与双引号：

‥ 在标准 SQL 中，字符串使用的是单引号。

‥ 如果字符串本身也包括单引号，则使用两个单引号（注意，不是双引号，字符串中的双引号不需要另外转义）。

‥ 但在其它的数据库中可能存在对 SQL 的扩展，比如在 MySQL 中允许使用单引号和双引号两种。

• 针对当打开 SQL Server时所出现的sql语句中对象无效的问题：

‥ 解决办法：执行语句  exec sp_msforeachtable "sp_changeobjectowner '?','dbo'"

• 针对 DROP ：

‥ 当删除中文名数据库时，对于中文名需要使用双引号（""）

• 当 NOT、AND 和 OR 出现在同一表达式中时，优先级是：NOT、AND、OR。

• 自增长：identity(1,1)

 

SQL语句优化经验

       SQL SERVER 中有一个“查询分析优化器”，它可以计算出 where 子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。

       在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数（ SARG ），那么就称之为可优化的，并且可以利用索引快速获得所需数据。

       SARG 的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的 AND 连接。形式如下：  

  列名   操作符   <常数   或   变量>     或    <常数   或   变量>   操作符列名  

       列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。

       如果一个表达式不能满足 SARG 的形式，那它就无法限制搜索的范围了，也就是 SQL SERVER 必须对每一行都判断它是否满足 WHERE 子句中的所有条件。所以一个索引对于不满足 SARG 形式的表达式来说是无用的。

 • Like 语句是否属于 SARG 取决于所使用的通配符的类型，因为通配符 % 在字符串的开通使得索引无法使用

 • or 会引起全表扫描  

 • 非操作符、函数引起的不满足 SARG 形式的语句 

        不满足 SARG 形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、<>、!<、!>、NOT   EXISTS、NOT IN、NOT LIKE等，另外还有其他函数。

         有些表达式，如：WHERE 价格 * 2 > 5000，SQL SERVER 也会认为是 SARG，SQL SERVER 会将此式转化为：WHERE 价格 > 2500 / 2 。但我们不推荐这样使用，因为有时 SQL SERVER 不能保证这种转化与原始表达式是完全等价的。  

 • IN 的作用相当与 OR

 • 尽量少用 NOT

 • exists 和 in 的执行效率是一样的

 • 用函数 charindex() 和前面加通配符 % 的 LIKE 执行效率一样

 • union 并不绝对比 or 的执行效率高

　　在通常情况下，用 union 比用 or 的效率要高的多；但如果 or 两边的查询列是一样的话，那么用 union 则反倒和用 or 的执行速度差很多，虽然这里 union 扫描的是索引，而 or 扫描的是全表。

 • 字段提取要按照 “ 需多少、提多少 ” 的原则，避免 “ select   * ”

 • count ( * ) 不比 count ( 字段 ) 慢

 • order by 按 聚集索引 列排序效率最高

 • 高效的 TOP （ ORACLE中却没有，这不能说不是一个遗憾，虽然在ORACLE中可以用其他方法，如：rownumber 来解决。 ）