SQL基础语法

最新推荐文章于 2024-03-15 11:32:36 发布

每天都必须加油鸭

最新推荐文章于 2024-03-15 11:32:36 发布

阅读量414

点赞数

分类专栏：数据库文章标签： sql

本文链接：https://blog.csdn.net/buzhidao2333shuosha/article/details/121541335

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

前言
SQL是一种标准化的数据库操作语言，但是各个厂家数据库管理系统的SQL所支持的数据类型和操作不尽相同，例如Oracle、SQL Server、MySQL等。我们这里只介绍SQL语言比较通用且基础的部分，掌握这部分之后，在运用到不同数据库管理系统的语言时，也可以根据手册快速掌握相关语言。

1. 基表和视图

关系又称表，是关系数据库的基本组成单位。在SQL中，表分为两种：
基表（base table），其数据显示的存储在数据库中。
视图（view），它是一种虚表，仅有逻辑定义，可以根据其定义从其他表（包括视图）中导出，但不作为一个表显示地存储在数据库中。

问题一：视图和基表有什么不同？
视图除了在更新操作方面有较大的限制，以及在查询时有个别限制外，可像基表一样，参与各种数据库操作。

问题二：为什么有视图的概念？
1）在同样的基表上，视图可为不同用户提供不同的数据模式，以适应不同的需要；
2）定义不同的视图，也可控制用户对数据的访问，有利于数据库的安全；
3）当基表的模式修改时，通过定义适当的视图，仍可为用户提供修改前的数据模式，避免修改应用程序，从而有利于提高数据的逻辑独立性。

2. SQL基本数据类型

这里列出一些基本的SQL数据类型，所谓“基本”是指在大多数的数据库管理系统中都支持，至于不同数据库系统之间支持数据类型的差异，暂不做讨论。

表一：数字和字符串

数据类型	整数	短整数	十进制数	浮点数	定长字符串	变长字符串
说明符	INT	SMALLINT	DEC(m,n)	FLOAT	CHAR(n)	VARCHAR(n)

注：
1)整数32位，短整数16位，浮点数64位；
2)十进制数中的m表示总位数，n表示小数点后的位数；
3)定长字符串中的n表示长度，变长字符串中的n表示最大长度。

表二：其他类型

数据类型	位串	变长位串	日期	时间	时标
说明符	BIT(n)	BIT VARYING(n)	DATE	TIME	TIMESTAMP

注：
1）位串的n表示长度，变长位串的n表示最大长度；
2）日期格式为"yyyymmdd"，时间格式为"hhmmss"，时标格式为"yyyymmddhhmmssnnnnnn"（ n为微秒）。

3. SQL语言知识

SQL的基础语法可以分为数据定义、查询、数据操纵、视图、数据控制这样几部分。

下面所有的语法将以以下三个基表为基础，这里先放出来备查：
STUDENT表

属性名	SNO	SNAME	SEX	BDATE	HEIGHT
属性含义	学号	学生姓名	性别	出生日期	身高
数据类型	CHAR(7)	VARCHAR(8)	CHAR(2)	DATE	DEC(5,2)

COURSE表

属性名	CNO	LHOUR	CREDIT	SEMESTER
属性含义	课程号	课程时长	学分	学期
数据类型	CHAR(6)	SMALLINT	DEC(5,2)	CHAR(2)

SC表

属性名	SNO	CNO	GRADE
属性含义	学号	课程号	成绩
数据类型	CHAR(7)	CHAR(8)	DEC(4,1)

3.1 SQL数据定义语言（SQL DDL）

1）基表定义

基表的定义按照以下语法进行：

	CREATE TABLE SC(在括号中定义列、主键、外键等);

其中列、主键、外键的定义，以及CHECK语句的详细定义方法如下：

列的定义

	列名 数据类型 默认值\是否可以为NULL,

列名和数据类型是必须的，默认值和是否可以为NULL是可选项。列名最好能体现出属性含义，数据类型在前面已经列出。

默认值设定：

	DEFAULT VALUE

注：VALUE可以是：事先定义的字值、用户标识符、NULL。

此属性不可为NULL:

	NOT NULL

主键的定义

	PRIMARY KEY(列名, ...),

主键只能定义一个，列名可以有一个或多个，列名必须是在表中存在的列。

外键的定义

	FOREIGN KEY(列名)
		REFERENCES 表名
		ON DELETE (RESTRICT\CASCADE\SET NULL)

一个表的外键是另一个表的主键，代码里的表名就是以该列为主键的那个表，即主表。

ON DELETE关键字是一个可选项，它声明了当主表中被引用的主键删除时，为了不破坏引用完整性约束，该怎样处理，这里有三种方式：

a)  RESTRICT：凡是被基表所引用的主键，不得删除，当不设置ON DELETE时，此选项为默认选项；
b)  CASCADE：如主表删除了某一主键，则基表中引用此主键的行也随之被删除；
c) NOT NULL：设置为空，这需要该列不可以有NOT NULL声明。

CHECK语句

	CHECK (条件)

基表定义还提供了可选的CHECK语句，此句子可以说明各列的值应该满足的限制条件，如年龄不能为负等。在设置了这个检查之后，每次插入数据都会进行检查，如果插入使条件为假的数据，则会出现检查约束错误，提示信息如下：

	Error: UNKNOWN_CODE_PLEASE_REPORT: 
	Check constraint 'test_chk_1' is violated.

三张表定义源码：

use test;
CREATE TABLE STUDENT
(SNO CHAR(7) NOT NULL,
SNAME VARCHAR(8) NOT NULL,
SEX CHAR(2) NOT NULL,
BDATE DATE NOT NULL,
HEIGHT DECIMAL(5,2) DEFAULT 000.00,
PRIMARY KEY(SNO)); 

CREATE TABLE COURSE
(CNO CHAR(6) NOT NULL,
 LHOUR SMALLINT NOT NULL,
 CREDIT DECIMAL(1,0) NOT NULL,
 SEMESTER CHAR(2) NOT NULL,
 PRIMARY KEY(CNO));

CREATE TABLE SC
(SNO CHAR(7) NOT NULL,
CNO CHAR(6) NOT NULL,
GRADE DECIMAL(4,1) DEFAULT NULL,
PRIMARY KEY (SNO, CNO),
FOREIGN KEY(SNO)
    REFERENCES STUDENT(SNO)
    ON DELETE CASCADE,
FOREIGN KEY(CNO)
    REFERENCES COURSE(CNO)
    ON DELETE RESTRICT);

2）基表模式的修改

SQL一般提供7种修改基表模式的命令。
a) 增加列

	ALTER TABLE 表名 
		ADD 列的定义;

上述命令中，“列的定义”是指跟创建基表中的列定义相同。

b) 删基表

	DROP TABLE 表名;

SQL未提供删除列的命令。若要删除列，只有与另定义一个新表，并将原表中保留列的内容复制到新表中，然后删除原表。最后还得用重命名（RENAME）命令把新表改名为原表名。

c) 补充定义主键
由于SQL并不要求每个表都定义主键，因此提供了补充定义主键的命令，在需要时定义主键。

	ALTER TABLE(表名）
		ADD PRIMARY KEY(列名1, 列名2, ...);

当然，被定义为主键的列必须满足NOT NULL和唯一性条件。

d) 撤销主键定义
如果定义了主键，系统一般在主键上自动建立索引，并在插入新行时，进行主键唯一性检查。这在插入大批数据时，会严重地影响系统的性能。利用撤销主键命令，可以暂时撤销主键定义，注意，并不是永久撤销。

	ALTER TABLE 表名
		DROP PRIMARY KEY;

d) 补充定义外键

	ALTER TABLE 表名-1
		ADD FOREIGN KEY (列名1, 列名2, ...)
			REFERENCES 表名2
				[ON DELETE {RESTRICT|CASCADE|SET NULL}];

在上述命令中，花括号表示任选三项中的一项；如果不定义，RESTRICT是默认项。（这个语句我实验没成功，报错为Error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row；有兴趣同学可以研究一下，或者等我后面更新）

e) 撤销外键定义
定义了外键，须做引用完整性检查，这在有些情况下会影响系统性能。为此，SQL提供了撤销外键的命令，可以暂时撤销外键。

	ALTER TABLE 表名
		DROP 外键名;

f）定义和撤销别名
别名（alias）的作用有两点：第一，简单的别名替代全名，书写和输入都比较方便；第二，使得个用户对于统一数据对象可以使用不同的命名，用户可以保留自己的命名习惯。定义和撤销别名的语句格式如下。

	CREATE SYNONYM 标识符
		FOR 表创建者.{表名|视图名};
	
	DROP SYNONYM 标识符;

3）索引的建立和撤销

索引解决了什么问题？
可以大大提高检索速度，合理设计使用索引的SQL运行速度可能是不设计索引的SQL的几十甚至上百倍。

索引是什么？
索引类似于汉语字典的目录，通过目录我们可以缩小检索范围，快速查找到需要找到的字段。
一个索引是存储的表中一个特定列的值数据结构。索引在表的列上创建。索引是包含一个表中列的值，并且这些值存储在一个数据结构中。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
索引分为单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，需要确保该索引是应用在SQL查询语句的条件（一般作为WHERE子句的条件）。

索引的缺点？
虽然索引大大提高了查询速度，但是也有缺点：第一，降低了更新表的速度，因为在更新表时，不仅要保存数据，还要保存一下索引；第二，建立索引会创建索引文件，占用磁盘空间。

索引的相关语法
建立索引

	CREATE [UNIQUE] INDEX 索引名
		ON 表名(列名1, 列名2, ..., [ASC/DESC]);

代码中的两处中括号内的内容为可选项，当我们在主键上建立索引时，一般都会加UNIQUE，UNIQUE关键字说明这一属性是主键或候补键，这种声明以便在插入时做主键唯一性检查。
列名后的参数说明了索引的排列顺序，ASC表示按升序排列，DESC表示按降序排列。

删除索引

	DROP INDEX 索引名 on 表名;

查看索引

	SHOW INDEX FROM 表名;

3.2 SQL数据操纵语言（SQL DML）

为了更新数据库中的数据，SQL提供了增（insert）、删（delete）、改（update）语句。

1）INSERT语句

插入一条数据

	INDERT INTO 表名(列名1, 列名2，列名3, ...)
		VALUES(数据1，数据2， 数据3，...);

一次插入多条数据（这是MySQL的语法，其他系统有另外的语法）

	INDERT INTO 表名（列名1， 列名2，...）
	VALUES
	(数据1-1，数据1-2，...),
	(数据2-1，数据2-2，...),
	...
	(数据n-1，数据n-2，...);

向含有外键定义的表中插入数据
当表中含有外键的时候，我们向其中插入数据必须先向其父表中插入数据，然而这是很麻烦的，经过搜寻找到的一个最便捷的方法是先关闭对外键约束的检查，在插入完成之后再打开。

	//设为0表示关闭外键约束检查
	SET FOREIGN_KEY_CHECKS=0;
	//设为1表示打开外键约束检查
	SET FOREIGN_KEY_CHECKS=1;

2）DELETE语句

	DELETE FROM 表名
	WHERE 判断条件;

从表中删除元组，要删除的元组要满足WHERE子句后面的条件；如果没有WHERE子句，自删除表中的所有元组，但表仍作为一个空表存在。要删除表，必须使用DROP TABLE语句。

另一种删除语法：

DELETE 表名1 
FROM 表名1，表名2，...
WHERE 判断条件

这种语法的意思是：要从表1删除一点东西，具体删除什么东西看WHERE后的判断条件。

3）UPDATE语句

UPDATE语句用来修改关系中某些元组的属性值。WHERE子句表示需要修改的元组须满足的条件，SET子句表示需要修改的列及其新值。

	UPDATE 表名
	SET 列名=值
	WHERE 条件；

3.3 SQL查询语言（SQL QL）

1）简单查询

	SELECT 列名1，列名2，... FROM 表名
	WHERE 条件;

如果将 ‘列名1，列名2，…’ 替换为*，则是选择所有列。此代码意为从‘表’中选取满足‘条件’的‘列’。

2）复杂查询

在简单查询语句的基础上，有两个地方可使得查询变得复杂：
第一个，SELECT后不仅可以跟列名，也可以是其他形式；
第二个，WHERE后面的条件可以很复杂。

a) SELECT后部分
可以用聚集函数、标量函数、表达式等。聚集函数有AVG（求平均）、MAX（求最大值）、MIN（求最小值）、SUM（求和）、COUNT（计算元素个数）。标量函数如日期、时间函数，数字、字符函数。

在列名变量前加关键字DISTINCT，则在计算时须消除列值中的重复项，即只考虑不同的列值。

b) WHERE后条件
可用表达式以及嵌套子查询等。当使用嵌套子查询时，子查询称为内部查询，包含子查询的查询称为外部查询。常用的条件有BETWEEN, LIKE, IN, EXISTS, ALL, ANY, SOME, IS, NOT等

BETWEEN

	E BETWEEN  E1 AND E2
	//等同于
	E>=E1 AND E<=E2

LIKE
用于字符串的比较。在比较时已用了两个具有特殊意义的符号：’_’ 表示任意单字符，’%'表示包括长不为零的任意长字符。例如

	'CS-110' LIKE 'CS%'
	'EE-122' LIKE 'EE-_ _ _'
	以上两句结果均为真。

IN
在子查询返回的结果中检查是否存在某个特定值，如果存在则为真，只有为真时才执行外部查询。

EXISTS
表示一个子查询块的结果是否存在。如果查询块的查询结果非空，则EXISTS（子查询块）为真，否则为假。只有为真是才执行外部查询。使用此关键字时，子查询不返回查询结果，只返回一个真假值。

ALL
使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。

ANY (SOME)
使用ANY关键字时，只要满足内层查询语句返回的结果中的任意一个，就可以通过该条件来执行外层查询语句。SOME关键字和ANY关键字是一样的功能。

IS
由于NULL不是值，涉及NULL的比较条件不得使用"="等比较符，而只能使用IS NULL 或者 IS NOT NULL。

3）ORDER BY子句

	ORDER BY 列名1，列名2，... ASC\DESC；
	//列名后加空格，再接ASC\DESC

ORDER BY子句可对查询结果按子句中指定的列的值排序。列可以用列名表示，也可以用在SELECT子句中出现的序号表示。后者书写起来比较简便，特别当选择的列是聚集函数表达式时，由于没有列名，只有用序号表示。

ASC表示升序，DESC表示降序，默认升序。

如果ORDER BY后有多个列名，则首先按第一列名排序，然后对于具有相同第一列值的各行，再按第二列名排序，原则上可以如此继续下去。

4）GROUP BY 子句

	GROUP BY 列名1，列名2， ...
	HAVING 条件

GROUP BY子句将表按列的值分组，列的值相同的分在一组。如果GROUP BY后有多个列名，则先按第一列名分组，再按第二列名在组中分组，原则上可以一直分下去，直到在所有基本组中，GROUP BY子句所指定的列都具有相同的值，HAVING后的条件是选择基本组的条件。

GROUP BY子句和聚合查询
GROUP BY子句常与聚集函数连用，此时聚集函数以基本组为计算对象。加了GROUP BY子句之后，SELECT子句所取的值必须在基本组中是唯一的，即只能是GTOUP BY子句所指明的列或聚集函数。若无GROUP BY子句，则聚集函数以整个表为计算对象，此时SELECT子句只能取聚集函数，而不能取某一列。
以这个例子来理解：

--查看SC表的内容
SELECT * FROM SC;
--以CS-110班为例
SELECT * FROM SC
WHERE CNO = 'CS-110';
--计算CS开头课程成绩最大值、最小值、平均值
SELECT CNO, MAX(GRADE), MIN(GRADE), AVG(GRADE)
FROM SC
WHERE CNO LIKE 'CS%'
GROUP BY CNO;

结果：
在这里插入图片描述
上图是自左向右三个查询的结果（SNO-学号，CNO-课程，GRADE-得分）。从结果很容易能够理解我们要做的事情：计算CS开头课程成绩最大值、最小值、平均值。但是做到这个事情是离不开GROUP BY语句的，是它为我们的聚合函数MAX、MIN、AVG指明了操作对象，即以CNO分组，分别计算，所以我们计算出来的是每个CS课程的平均值。

如果删除GROUP BY语句会怎么样呢？
这里要关注我们SELECT后的列名，我们看到列名列表里有CNO。
情况一：把CNO和GROUP BY语句一起删掉。这时是不会出问题的，计算结果如下：
请添加图片描述
这是所有CS课程学生的成绩的最大值、最小值、平均值。这说明，当我们去掉GROUP BY语句之后，我们聚集函数的计算对象已经成了满足WHERE条件的整个表了，不再是以CS课程为单位。
情况二：保留列名CNO，只删除GROUP BY语句。这时会报错：
Error: ER_MIX_OF_GROUP_FUNC_AND_FIELDS: In aggregated query without GROUP BY
这个错误的含义为：没有GROUP BY语句的聚合查询。

为什么会产生这个错误呢？
从情况一我们已经看到，当删除GROUP BY语句之后，我们聚合函数的操作对象已经变成了整个满足WHERE语句的对象，那么很容易想到，我们的MAX、MIN、AVG函数都只能得到一个值，也就是只会产生一行。而我们知道，CNO是不止一个的，那我们可以简单的将计算出的MAX、MIN、AVG值粘贴到每个CNO后面而产生一个表返回吗？显然是不能的，因为这样得到的表语义是有问题的。所以系统认为是咱们忘了加GROUP BY语句，提示了这个错误。

通过以上例子的分析，相信大家应该能够明白GROUP BY对聚合函数作用对象的影响，以及为什么他们常常配对使用了。

5）包含 UNION 的查询

UNION即并运算，是SQL中常用的一种集合运算。一些SQL库还提供了交或差运算。
在进行集合运算时，把关系看成元组的集合。两个关系必须并兼容，即参与集合运算的关系（表）必须具有相等的数目，且对应的属性域（列名）相同。UNION也可以推广到多于两个关系的UNION运算，在此情况下，SQL规定，须用圆括号把多元UNION运算变成多次二元UNION运算。
在做UNION运算的时候，必须消除结果中的重复项。如果想发现重复项，就必须做一次排序。如果结果很大，排序的开销是很大的，此时使用UNION运算，要慎重考虑。如果允许，可以在UNION后加ALL，则不消除结果中的重复项。

以下为一个例子：

SELECT SNO FROM STUDENT
WHERE YEAR(BDATE) = 1983
UNION
SELECT SNO FROM SC
WHERE CNO LIKE 'EE%';

3.4 SQL中的视图

1）基本视图

视图是由基表或其他视图导出的虚表，只在数据目录中保留其逻辑定义，而不作为一个表存储在数据库中。当视图参与数据库操作时，在简单情况下，可以通过修改查询条件，把对视图的查询转换成对基表的查询。但并不是所有情况下都可以如此处理。在不能这样处理时，系统须按视图定义生成一个临时表，在查询处理完毕后，此临时表不再保留。

如果视图所用的属性都是已定义的，则在视图名后一般不必附上列名。但当通过函数或表达式定义视图的属性时，由于没有现成的列名，必须在视图名后的列名表中为其定义新的列名。

对于视图的查询语句原则上与对基表的查询语句相同，但出于性能和实用性的考虑，一般规定不得使用UNION，ORDER BY等语法成分来定义视图。

视图的更新是一个非常复杂的问题，因为视图元组到基表元组的映射关系不一定存在，所以通常情况下视图是不可以更新的，并不是说真的就无法更新，多数情况是一种偏保守的规定，为了数据库的安全。

视图创建

CREATE VIEW 视图名(列名1, 列名2, ...)
	AS SELECT 列名1, 列名2, ...
		FROM 基表1, 基表2, ...
		WHERE 判断条件;

视图删除

DROP VIEW 视图名;

2）临时视图

用CREATE VIEW语句所定义的视图须记入数据目录中，可供反复使用，直到使用DROP VIEW语句撤销为止。在有些较复杂的查询中，可将查询中相对独立的部分作为查询的中间结果，定义为临时视图。
临时视图在功能上与普通视图一样，但仅用于附在临时视图定义后的查询语句中。该查询语句结束后，临时视图也随之小时，不需要用DROP VIEW语句撤销。临时视图的定义如下：

	WITH 临时视图名（列名1，列名2，...）AS
	（(SELECT子查询）
		UNION
		(SELECT子查询))
		
	 SELECT查询;

以上语法有这样几点要注意：第一，AS后面的视图定义部分是在圆括号中的；第二，子查询是用圆括号括起来的；第三，临时视图定义与附在其后的查询语句是一体，所以最终的分号是在最后的那个查询之后。

3.5 SQL数据控制语言（SQL DCL）

数据控制是指数据库安全，如访问权限等内容，用的比较少，暂时不做介绍（2021.11.26），用到时再补充。

5. SQL语法练习

纸上谈兵，终究不美。在编程路上，我们必须牢记卖油翁的那句“唯手熟尔”。只是知道理论，不加以实践，任何语言都是难以掌握的。下面我列出了找到的可以练习SQL语言的几个网站，请收好，勤加练习，一起成长！

官方练习网站（官方，免费）：https://sqlzoo.net/wiki/SELECT_basics/zh

练习资源网站（进阶需付费，不是我的，不是广告）：http://xuesql.cn/lesson/select_queries_introduction

一个在线平台：http://sqlfiddle.com/

LeetCode题库也有SQL题：https://leetcode-cn.com/

最简单的方式：本地安装MySQL即可在控制台操作，配合vscode服用更佳。这里有vscode配置使用MySQL的总结。