你不知道的SQL语言数据库原理

最新推荐文章于 2024-03-22 10:27:23 发布

测试小扎

最新推荐文章于 2024-03-22 10:27:23 发布

阅读量1.2k

点赞数 1

分类专栏：自学编程 python 文章标签： sqlserver 数据库 sql

本文链接：https://blog.csdn.net/m0_57290404/article/details/128018564

版权

自学同时被 3 个专栏收录

177 篇文章 11 订阅

订阅专栏

编程

163 篇文章 13 订阅

订阅专栏

python

95 篇文章 5 订阅

订阅专栏

1、SQL的概述

SQL全称： Structured Query Language，是结构化查询语言，用于访问和处理数据库的标准的计算机语言。 SQL语言1974年由Boyce和Chamberlin提出，并首先在IBM公司研制的关系数据库系统SystemR上实现。
美国国家标准局(ANSI)开始着手制定SQL标准，并在1986年10月公布了最早的SQL标准，扩展的标准版本是1989年发表的SQL-89，之后还有1992年制定的版本SQL-92和1999年ISO发布的版本SQL-99。
SQL标准几经修改和完善，其功能更加强大，但目前很多数据库系统只支持SQL-99的部分特征，而大部分数据库系统都能支持1992年制定的SQL-92。 2、 SQL的特点
具有综合统一性，不同数据库的支持的SQL稍有不同
非过程化语言
语言简捷，用户容易接受
以一种语法结构提供两种使用方式

3、SQL语言的组成

数据定义语言-DDL
数据操纵语言-DML
数据控制语言-DCL
数据查询语言-DQL

SQL语言基础

语法特点

SQL 对关键字的大小写不敏感
SQL语句可以以单行或者多行书写，以分行结束

SQL的注释：

SQL和数据库管理系统的关系 SQL是一种用于操作数据库的语言，SQL适用于所有关系型数据库。

MySQL、Oracle、SQLServer是一个数据库软件，这些数据库软件支持标准SQL，也就是通过SQL可以使用这些软件，不过每一个数据库系统会在标准SQL的基础上扩展自己的SQL语法。

sql的数据定义语言

1.建立数据库

create database 数据库名

2.建立基本表

create table 表名

完整性约束主要有3种子句：主键字句（primary key）、检查子句（check）和外键子句（foreign key）。

定义时使用的基本数据类型如下：

INTEGER：整数(也可写成INT)。
FLOAT(n)：浮点数，精度至少为n位数字。
NUMERIC(p,d)：定点数，由p位数字(不包括符号、小数点)组成，小数点后面有d位数字(也可写成DECIMAL(p,d)或DEC(p,d))。
CHAR(n)：长度为n的定长字符串。
DATETIME：日期时间型。

3.增加一个新列的基本语句

alter table 表名 add 列名类型

4.修改一个新列的基本语句

alter table 表名 alter column 列名新类型

5.删除一个列的语句

alter table 表名 drop column 列名

6.删除基本表

drop table 表名

sql的数据操纵语句

1.直接插入元组值

insert into 表名(列名序列) values (元组值)

或者

insert into 表名(列名序列) (table (元组值),(元组值),...)

2.插入一个查询的结果值

insert into 表名(列名序列) select 查询语句

3.delete删除语句

delete from 表名 [where 条件表达式]

4.update修改语句

update 表名

sql数据查询功能

1.select语句的完整语法

select 目标表的列名或列表达式序列

其中，SELECT子句中的输出项可以是列名，或者由列名、常数和“+”、“-”、“*”、“/"运算符构成的算术表达式,使用DISTINCT选项可保证查询结果集中不存在重复元组。

FROM子句中出现多个基本表或视图时，系统首先执行笛卡尔积操作。

WHERE子句的条件表达式中可以使用的运算符有:比较运算符(<、<=、>、>=、=、<>)和BETWEEN运算符;

逻辑运算符(AND、OR、NOT);

集合成员运算符(IN、NOT IN);

字符串匹配运算符(LIKE);

谓词(EXISTS、ALL、SOME、UNIQUE);

聚合函数;

空值比较运算符(IS NULL、IS NOT NULL);
集合运算符(UNION、INTERSECT、EXCEPT)等。

1.投影查询

使用SELECT命令可以选择查询表中的任意列。其中，目标表的列名是指要检索的列的名称，可以为一个或多个列。当为多个列时，中间要用“,”分隔。FROM子句中的关系名表指明从什么表中提取数据，如果是从多个表中提取数据，每个表的表名都要写出，表名之间用“,”分隔开。

2.选择查询

选择查询就是指定查询条件，只从源表提取或显示满足该查询条件的记录。其中，WHERE子句的行条件表达式指出记录应满足的条件。选择查询通常使用下列几类运算。

(1)条件表达式的比较运算

在WHERE条件表达式中包含比较运算。

(2)字符串的匹配运算

通常可以利用字符串的匹配运算进行模糊查询。谓词LIKE可以用来进行字符串的匹配运算。

[NOT] LIKE '匹配串' [ESCAPE '匹配串']

其含义是查找指定的属性列值与“匹配串”相匹配的元组。“匹配串”可以是一个完整的字符串，也可以含有通配符%和_。

%(百分号)：代表任意长度(长度可以为0)的字符串。
_(下划线)：代表任意单个字符。

(3)集合的比较运算

在WHERE条件表达式中包含集合成员运算符。

4)逻辑组合运算

在WHERE条件表达式中包含多个条件子句，这些子句通过逻辑运算符组合起来。

3.排序查询

通过在SELECT命令中加入ORDER BY子句来控制选择行的显示顺序。ORDER BY子句可以按升序(默认或ASC)、降序(DESC)排列各行，也可以按多个列来排序。也就是说，ORDER BY子句用于对查询结果进行排序。

ORDER BY子句必须是SELECT命令中的最后一个子句。

4.使用聚合函数

聚合函数实现数据统计等功能，用于对一组值进行计算并返回一个单一的值。聚合函数常与SELECT语句的GROUP BY子句一起使用。常用的聚合函数如下所示。

函败名	功能
AVG	计算一个数值型表达式的平均值
COUNT	计算指定表达式中选择的项数，COUNT(*)统计查询输出的行数
MIN	计算指定表达式中的最小值
MAX	计算指定表达式中的最大值
SUM	计算指定表达式中的数值总和
STDEV	计算指定表达式中所有数据的标准差
STDEVP	计算总体标准差

聚合函数参数的一般格式为:

[ALL|DISTINCT] expr

其中，ALL表示对所有值进行聚合函数运算，它是默认值;DISTINCT指定每个唯一值都被考虑;expr指定进行聚合函数运算的表达式。

5.数据分组

前面的例子中使用了聚合函数，还可以加上GROUP BY子句进行数据分组。通常一个聚合函数的作用范围是满足WHERE子句中指定条件的所有记录。在加上GROUP BY子句后，SELECT命令把查询结果按指定列分成集合组。当一个聚合函数和一个GROUP BY子句一起使用时，聚合函数的范围变成每组的所有记录。换句话说，一个结果是由组成一组的每个记录集合产生的。用HAVING子句定义这些组所必须满足的条件，以便将其包含在结果中。

当WHERE 子句、GROUP BY子句、HAVING子句和聚合函数同时出现在一个查询中时，SELECT命令的执行顺序如下:

①执行WHERE子句，从表中选取行。

②由GROUP BY对选取的行进行分组。

③执行聚合函数。

④执行HAVING子句选取满足条件的分组。

6.表的连接查询

在数据查询中，经常涉及提取两个或多个表的数据，这就需要使用表的连接来实现若干个表数据的联合查询。

在一个查询中，当需要对两个或多个表连接时，可以指定连接列，在WHERE子句中给出连接条件，在FROM子句中指定要连接的表，其格式如下:

SELECE 列名1,列名2,...

对于连接的多个表通常存在公共列，为了区别是哪个表中的列，在连接条件中通过表名前缀指定连接列，由此来区别连接列所在的表。

在SQL Server中，连接分为内连接(INNER JOIN)和外连接(OUTER JOIN)两种类型。

1)内连接

内连接是最基本的连接方式，使用INNER JOIN关键字，不使用该关键字时默认均为内连接。内连接又包括等值连接、非等值连接和自连接。

(1)等值连接

指表之间通过“等于”关系连接起来，产生一个连接临时表，然后对该临时表进行处理后生成最终结果。

(2)非等值连接

指表之间的连接关系不是“等于”，而是其他关系。通过指定的非等值关系将两个表连接起来，产生一个连接临时表，然后对该临时表进行处理后生成最终结果。

(3)自连接

在数据查询中有时需要将同一个表进行连接，这种连接称之为自连接，进行自连接就如同两个分开的表一样，可以把一个表的某行与同一表中的另一行连接起来。

2)外连接

仅当至少有一个同属于两表的行符合连接条件时，内连接才返回行，所以内连接消除与另一个表中的任何行不匹配的行。而外连接会返回FROM子句中提到的至少一个表或视图的所有行，只要这些行符合任何WHERE或HAVING搜索条件。将检索通过左外连接引用的左表的所有行，以及通过右外连接引用的右表的所有行。全外连接中两个表的所有行都将反回。

外连接又分为左外连接、右外连接和全外连接，其关键字如下:

LEFT OUTER JOIN或LEFT JOIN(左外连接)。
RIGHT OUTER JOIN或RIGHT JOIN(右外连接)。
FULL OUTER JOIN或FULL JOIN(全外连接)。

(1)左外连接

左外连接简称为左连接，其结果包括第一个命名表(“左”表，出现在JOIN子句的最左边)中的所有行，不包括右表中的不匹配行。

(2)右外连接

右外连接简称为右连接，其结果中包括第二个命名表(“右”表，出现在JOIN子句的最右边)中的所有行，不包括左表中的不匹配行。

(3)全外连接

若要通过在连接结果中包括不匹配的行即保留不匹配信息，可以使用全外连接。SQL Server 2005提供全外连接运算符FULL OUTER JOIN，不管另一个表是否有匹配的值，此运算符都包括两个表中的所有行。

7.子查询

1)一般子查询

当一个查询是另一个查询的条件时，换言之，当从表中选取数据行的条件依赖于该表本身或其他表的联合信息时，需要使用子查询来实现。子查询可以使用几个简单命令构造功能强大的复合命令。子查询最常用于SELECT语句的WHERE子句中。

一个子查询的返回值为多行时，需要使用ANY、ALL、IN和NOT IN等选项，它们与查询条件一起构造返回一组值的子查询。

2)相关子查询

每个子查询仅执行一次，返回的值为主查询的WHERE子句所用。在有的查询中，子查询不只执行一次。

3)带EXISTS测试的子查询

在子查询中还可以使用EXISTS，它一般用在WHERE子句中，其后紧跟一个SELECT子查询，从而构成一个条件。

EXISTS代表存在量词∃。带有EXISTS谓词的子查询不返回任何数据，只返回逻辑真值和逻辑假值。当子查询的结果不为空集时返回逻辑真值，否则返回逻辑假值。

8.查询结果的并、交、差运算

可以使用以下运算符将两个SELECT查询语句的结果进行相应运算:

UNION:并.

INTERSECT:交.

EXCEPT:差.

sql数据控制语句

1.授予权限

SQL语句采用GRANT语句向用户授予操作权限，一般格式如下：

GRANT 权限 ON 对象 TO 用户 [WITH GRANT OPTION]

2.收回权限

可以使用REVOKE命令撤销已授予用户的某些权限，REVOKE的使用语法与GRANT类似。

例如，使用命令撤销用户User1的create table语句权限：

revoke create table to User1

视图

视图是从一个或几个基本表(或视图)导出的表，它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中，在对视图进行查询时要执行对应的SELECT语句。

若一个视图是从单个基本表导出来的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图。

1.视图的作用

视图能够简化用户的操作。
视图使用户能以多种角度看待同一数据。
视图对于数据库的重构提供了一定程度的逻辑独立性。
视图能够对机密数据提供安全保护。

2.视图的更新操作的限制

一个视图对应一个SELECT语句，SQL中规定允许进行更新操作的视图的SELECT语句必须满足以下规则:

FROM子句只能包含一个关系R，不能再有其他关系。

WHERE子句在子查询中不能使用关系R。

SELECT (而非SELECT DISTINCT)中的属性列表必须包含足够多的属性，以保证对该视图进行元组插入时，能够用null或默认值来填充所有其他不属于该视图的属性。

视图上的一个插入操作可以直接应用到基本关系R，仅有的差别是这里视图SELECT子句中的属性是提供值的属性。

3.创建和删除视图

(1)创建视图

create view 视图名 (列名序列) as select 查询语句

(2)删除视图

drop view 视图名

索引

1.索引的作用

提高查询速度；
提高连接、ORDER BY和GROUP BY执行的速度；
查询优化过程依赖索引；
强制实施行的唯一性。

2. SQL Server中索引类型

SQL Server提供了3种类型的索引：聚集索引、非聚集索引和唯一性索引。

聚集索引(CLUSTERED)：在聚集索引中，数据表中记录的物理顺序与索引顺序相同，即索引顺序决定了表中记录行的存储顺序，因为记录行是经过排序的，所以每个表只能有一个聚集索引。

非聚集索引(NONCLUSTERED)：一个数据表中只能有一个聚集索引，而表中的每一列上都可以建立自己的非聚集索引。

唯一性索引：聚集索引和非聚集索引是按照索引的结构划分的。按照索引实现的功能还可以划分为唯一性索引和非唯一性索引。唯一性索引表示表中任何两个记录的索引值都不相同，与表的主键类似。它可以确保索引列不包含重复的值。在多列唯一性索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。

在创建主键和唯一性约束的列上会自动创建唯一性意引。如果表中没有创建提示其他的聚集索引，则在表的主键列上自动创建聚集索引。

3.创建索引

create [UNIQUE] [CLUSTERED|NONCLUSTERED] index 索引名称

4.删除索引

drop index 表名.索引

嵌入式SQL

1.SQL的运行环境

SQL的运行环境有交互式SQL和嵌入式SQL两种。

交互式SQL是在某个DBMS中运行的，如前面的所有SQL语句均是在SQLServer数据库管理系统中直接运行的。
嵌入式SQL是将SQL语句嵌入到宿主语言程序(如C程序)中运行的。

嵌入式SQL有两种处理方式:一种是扩充宿主语言的编译程序，使之能处理SQL语句:另一种是采用预处理方式，其处理包含嵌套SQL语句的过程。

2.嵌入式SQL的使用规定（在宿主语言程序中使用SQL）

所有SQL语句必须加上前缀和结束标志，作为与宿主语言语句的区分；
共享变量必须由宿主语言的程序定义，并用SQL的DECLARE语句声明；

SQL语句中引用共享变量时，必须加冒号“：”前缀，使其区别于数据库中的变量(在SQLServer中规定共享变量名以@开头)。

3.嵌入式SQL的使用技术

包括不涉及游标和涉及游标的两种使用方法如果嵌入到宿主语言程序中的是SQL的数据更新语句，或查询结果为单个元组的SELECT语句，则不需使用游标。如果SELECT语句的查询结果是多个元组，必须利用游标将多个元组逐个传送给宿主语言程序进行处理。

python大数据方向