SQL语句汇总

SQL语句汇总

"SQL"是 "Structured Query Language" 即“结构化查询语言”的简称，它是用来管理关系型数据库的。

其包括：数据定义语言（DDL）数据查询语言（DQL）数据操作语言（DML）数据控制语言（DCL）

创建数据库　　　数据修改查询

连接数据库　　　 添加新数据

删除数据库　　　 更改数据

创建表　　　　 删除数据（行）

删除表　　　　 查询操作

复制表　　　　 投影操作（指定列名、组合值、排除重复、返回限定行数）

修改表　　　　　　　　　　　 选择操作（WHERE、LIKE）

数据库完整性　　　　　　　　　 排序操作（ORDER BY）

实体完整性--主键约束

————————————————————————————————————————

聚合函数、分组、子查询及组合查询

聚合函数：统计行数量（COUNT）、获取单个列的合计值（SUM）、计算某个列的平均值（AVG）、计算列的最大值（MAX）、计算列的最小值（MIN）

SQL的执行顺序：

–第一步：执行FROM

–第二步：WHERE条件过滤

–第三步：GROUP BY分组

–第四步：执行SELECT投影列

–第五步：HAVING条件过滤

–第六步：执行ORDER BY 排序

执行列、行计数（count）

返回列合计值（SUM）

返回列平均值（AVG）

返回最大值/最小值（MAX/MIN）

连接查询

————————————————————————————————————————

创建数据库

CREATE DATABASE 数据库名;

数据库名命名规则：

- 第一个字符必须为下列之一：字母、下划线、#及@符号。

- 后续字符由字母、十进制数、下划线、#、$及@符号组成。

- 不能为RDBMS（数据库管理系统）的保留关键字。

- 不允许空格及其他字符。

本文以SQLyog软件为例，创建数据库如下：

CREATE DATABASE test_sql

此图为SQLyog的左侧目录栏，前三个为本地自带数据库。将SQL语句全部选中运行（F8）后，F5刷新目录栏，出现了我们创建的数据库。

连接数据库

USE test_sql;

删除数据库

DROP DATABASE test_sql;

创建表

接下来就开始创建表了，在创建表之前先列出几种常用的数据类型

整数数据类型：

-INT 大小：4字节

-BIGINT 大小：8字节

浮点数据类型：

- FLOAT 大小：4字节精度：7位小数

- DOUBLE 大小：8字节精度：15位小数

字符串数据类型：

- VARCHAR 范围：0-65535

日期数据类型：

- DATE 大小：3字节格式：YYYY

- DATETIME 大小：8字节格式：YYYY-MM-DD

创建表：

CREATE TABLE <表名>
      (<列名> <列的数据类型> [<列的约束>]);

示例：

CREATE TABLE t_student(
            student_name VARCHAR(10),
            student_birthday DATETIME,
            student_phone INT,
            student_score FLOAT);

上表中我们创建了一张学生表，并定义了姓名、生日、电话、得分四列，列名后加上数据类型。其中要注意的是VARCHAR需要在括号内设置字符串的最大长度。

刷新之后就可以看到我们创建的数据库中多了一张表：

选择打开表可以直观的看到内容：

之后我们可以通过SQL语句也可以通过手动为表添加内容。

删除表

DROP TABLE t_student;

复制表

CREATE TABLE copy_student SELECT * FROM t_student;

如此我们便复制了一张名为copy_student的表，它包括t_student表中的内容与结构。注意：复制表的同时表的约束并不能复制过来。

修改表

添加新列：

ALTER TABLE t_student ADD student_address VARCHAR(50);

更改列：

ALTER TABLE t_student CHANGE student_birthday student_age INT;

这里我们把学生生日列改为学生年龄列，CHANGE后第一个为旧列名，第二个为新列名。

删除列：

ALTER TABLE t_student DROP COLUMN student_score;

数据库完整性

保证数据库的完整性是为了防止垃圾数据的产生，以免影响数据库的执行效率。这里简要说一些，因为毕竟不是理论类的文章，这里主要是整理汇总SQL语句。

分类：

1.实体完整性

　　保证一行数据是有效的

2.域完整性

　　保证一列数据是有效的

3.引用完整性

　　保证引用的编号是有效的

4.用户自定义完整性

　　保证自定义规则

实体完整性--主键约束

PRIMARY KEY

主键列不能为空也不能重复，通常加在表的id列中。

CREATE TABLE t_student(
            student_id INT PRIMARY KEY,
            student_name VARCHAR(10),
            student_birthday DATETIME,
            student_phone INT,
            student_score FLOAT);

实体完整性--唯一约束

UNIQUE

唯一约束是指给定列的值必须唯一，与主键约束不同的是它可以为空。通常加在表中不能重复的信息中，如电话号码。

CREATE TABLE t_student(
            student_id INT PRIMARY KEY,
            student_name VARCHAR(10),
            student_birthday DATETIME,
            student_phone INT UNIQUE,
            student_score FLOAT);

域完整性--非空约束

NOT NULL

非空约束可以加在诸如姓名等列上。

CREATE TABLE t_student(
            student_id INT PRIMARY KEY,
            student_name VARCHAR(10) NOT NULL,
            student_birthday DATETIME,
            student_phone INT UNIQUE,
            student_score FLOAT);

域完整性--默认约束

设定默认值后，可以在添加此列时不指定值,数据库会自动填充设定的默认值。

DEFAULT

现给学生表加入性别列，默认值设为“男”，这样添加新的学生信息时如果没有填写具体的性别均会默认为男性：

CREATE TABLE t_student(
            student_id INT PRIMARY KEY,
            student_name VARCHAR(10) NOT NULL,
            student_sex VARCHAR(5) DEFAULT '男',
            student_birthday DATETIME,
            student_phone INT UNIQUE,
            student_score FLOAT);

引用完整性--外键约束

外键约束是指在外键关联主键上强制加上一个约束，如果违反该约束，则不允许该条数据的修改。

创建主表--班级表：

CREATE TABLE t_class(
              class_id INT PRIMARY KEY,
              class_name VARCHAR(20) UNIQUE NOT NULL);
            )

创建从表--学生表，并设置外键约束：

CREATE TABLE t_student(
            student_id INT PRIMARY KEY,
            s_c_id INT REFERENCES t_class(class_id),
            student_name VARCHAR(10) NOT NULL,
            student_sex VARCHAR(5) DEFAULT '男',
            student_birthday DATETIME,
            student_phone INT UNIQUE,
            student_score FLOAT
            CONSTRAINT FOREIGN KEY(s_c_id) REFERENCES t_class(class_id);

红色标识的就是创建外键约束的方法，个人认为也是SQL语句中最难记的。

数据修改查询

添加新数据

INSERT INTO <表名> (<列名列表>) VALUES (<值列表>)

如：

INSERT INTO t_student (student_id,student_name,student_age,student_sex) VALUES (1,'大毛',18,'男');

其中列名可以省略，省略之后要求插入的值必须与列一一对应：

INSERT INTO t_student VALUES (2,'王二',20,'男');

多行数据添加：

INSERT INTO t_student VALUES (3,'张三',22,'男'),
                 (4,'李四',17,'女'),
                 (5,'王五',23,'男');

更改数据

UPDATE 表名 SET 列1=新值1,列2=新值2 WHERE 过滤条件

假如要修改李四的年龄为21岁

UPDATE t_student SET student_age=21 WHERE student_name='李四';

注：修改多个列的值时用逗号隔开。要想设置某一列的值为空，只需让<列名>=NULL 即可。WHERE表示过滤条件。

删除数据（行）

DELETE FROM 表名 WHERE 过滤条件

现要删除20到22岁的学生信息：

DELETE FROM t_student WHERE student_age BETWEEN 20 AND 22;

WHERE的判断条件之后会更详细的介绍。

删除除了DELETE还有一种方法TRUNCATE，写法：

TRUNCATE TABLE 表名

二者区别在于：

DELETE会记录日志，意味着删除后的数据还可以恢复，但是效率低。TRUNCATE不会记录日志，删除后的数据不能恢复，但是效率高。需要注意的是，TRUNCATE不能用于有外键约束引用的表。

查询操作

分类：

–投影操作

　　指定查询结果中能显示哪些列

–选择操作

　　指定哪些行出现在结果中

–排序操作

　　指定查询的结果以什么样的顺序显示

投影操作（指定列名、组合值、排除重复、返回限定行数）

SELECT 列1,列2 FROM 表名

多个列中间用逗号隔开，如果选择所有列可以用*号简写。

还是此表：

现在只想要查看姓名和年龄列：

SELECT student_name,student_age FROM t_student;

注意这里不是把其他列删除了，而是只显示我们想看见的部分。

SELECT CONCAT(student_name,'——',student_age) '组合值' FROM t_student;

CONCAT，可以将列与列之间用想要的符号连接起来：

排除重复——DISTINCT

现给原表加入一班级列：

按照之前方法查询班级列得到：

但是我们只想查看具体有哪些班级，这里就需要用到去重，也就是DISTINCT。

SELECT DISTINCT student_class FROM t_student;

返回限定行数的查询——LIMIT

LIMIT后面参数为1或2个：

LIMIT N 表示从第一行开始返回N行结果，LIMIT i,N 表示从第i+1行开始返回N行结果。

例：

SELECT * FROM t_student LIMIT 3;

SELECT * FROM t_student LIMIT 2,3;

*注：LIMIT很重要，它是之后做数据表格分页的关键。

选择操作（WHERE、关系运算符=-*/、选择范围BETWEEN、定义集合IN或NOT IN、模糊查询LIKE）

分为单条件选择与多条件选择

单条件选择标准结构：

SELECT 列1, 列2 FROM 表名 WHERE 列3 = 值

关系运算符包括：> >= < <= = !=

多条件选择标准结构：

SELECT 列A, 列B FROM 表 WHERE 条件1 (AND或者OR) 条件2

其中AND表示并且，OR表示或者。

选择范围——BETWEEN

如：

SELECT * FROM t_student WHERE student_age BETWEEN 20 AND 23;

BETWEEN后的值为从下限到上限。

定义集合——IN或NOT IN

现在想查看年龄为17、20、23的学生信息：

SELECT * FROM t_student WHERE student_age IN (17,20,23);

反之NOT IN就是选择不包括在集合里的学生信息。

模糊查询——LIKE

为了更好的解释模糊查询，这里重新建张表：

这里姓王的兄弟们躺枪...别介意。

首先先说下占位符与通配符：

占位符 "_"，表示任何单个字符。

通配符 "%"，表示包含零或多个字符。

下面就来用模糊查询逐一选中我们想要的行。

名字只有两个字的：

SELECT * FROM t_student WHERE student_name LIKE '__';

这里可能看不清，引号里实际是两个占位符。

所有姓王的：

SELECT * FROM t_student WHERE student_name LIKE '王%';

最后一个字是“王”的：

SELECT * FROM t_student WHERE student_name LIKE '%王';

只要是名字中有“王”字的：

SELECT * FROM t_student WHERE student_name LIKE '%王%';

这下模糊查询就很明白了吧，当然还有其他组合，大家可以自己尝试。

处理空值数据：

判断条件不能用列名=NULL，而是要用IS NULL或IS NOT NULL。

标准写法：

SELECT * FROM t_student WHERE 性别 IS NULL

排序操作（ORDER BY）

使用ORDER BY时，列名上指定ASC或DESC。ASC表示正序，DESC表示倒序。如果不指定则默认为正序。

按年龄排：

SELECT * FROM t_student ORDER BY student_age ASC;

SELECT * FROM t_student ORDER BY student_age DESC;

最后一定要注意！

基本查询SQL的执行顺序：

1.执行FROM 2.WHERE条件过滤 3.SELECT投影 4.ORDER BY排序

执行列、行计数（count）

首先，创建数据表如下：

标准格式

SELECT COUNT(<计数规范>) FROM <表名>

其中，计数规范包括：

- * :计数所有选择的行，包括NULL值；

- ALL 列名：计数指定列的所有非空值行，如果不写，默认为ALL；

- DISTINCT 列名：计数指定列的唯一非空值行。

例，计算班里共有多少学生：

SELECT COUNT(*) FROM t_student;

也可加入筛选条件，如求女学生数目：

SELECT COUNT(*) FROM t_student WHERE student_sex='女';

如果要计算班级数目，就需要用到DISTINCT：

SELECT COUNT(DISTINCT student_class) FROM t_student;

DISTINCT即去重，如果不加DISTINCT则结果为表行数——5。

返回列合计值（SUM）：

注：sum只要ALL与DISTINCT两种计数规范，无*。

计算学生年龄之和：

SELECT SUM(student_age) FROM t_student;

返回列平均值（AVG）

计算学生平均年龄：

SELECT AVG(student_age)FROM t_student;

返回最大值/最小值（MAX/MIN）

求年龄最大的学生信息（最小值同理）：

SELECT MAX(student_age) FROM t_student;

注：这里只能求出最大年龄，要想显示年龄最大的学生全部信息，需要用到之后的子查询。

数据分组（GROUP BY)

SQL中数据可以按列名分组，搭配聚合函数十分实用。

例，统计每个班的人数:

SELECT student_class,COUNT(ALL student_name) AS 总人数 FROM t_student GROUP BY (student_class);

AS为定义别名，别名的使用在组合及联接查询时会有很好的效果，之后再说。

分组中也可以加入筛选条件WHERE，不过这里一定要注意的是，执行顺序为：WHERE过滤→分组→聚合函数。牢记！

统计每个班上20岁以上的学生人数：

SELECT student_class,COUNT(student_name) AS 总人数 FROM t_student WHERE student_age >20 GROUP BY (student_class);

过滤条件HAVING

之前说了分组操作、聚合函数、WHERE过滤的执行顺序，那如果我们希望在聚合之后执行过滤条件怎么办？

例，我们想查询平均年龄在20岁以上的班级

能用下面的语句吗？

SELECT student_class, AVG(student_age) FROM t_student WHERE AVG(student_age)>20 GROUP BY student_class;

结果会出错。正因为聚合函数在WHERE之后执行，所以这里在WHERE判断条件里加入聚合函数是做不到的。

这里使用HAIVING即可完成：

SELECT student_class,AVG(student_age) AS 平均年龄 FROM t_student GROUP BY (student_class) HAVING AVG(student_age)>20;

这里再啰嗦一句

SQL的执行顺序：

–第一步：执行FROM

–第二步：WHERE条件过滤

–第三步：GROUP BY分组

–第四步：执行SELECT投影列

–第五步：HAVING条件过滤

–第六步：执行ORDER BY 排序

子查询：

为什么要子查询？

现有一数据表如下：

根据之前的知识我们可以查出每门科目的最高分，但是要想查出取得最高分的学生信息就做不到了。这时就需要用到子查询来取得完整的信息。

什么是子查询？子查询就是嵌套在主查询中的查询。

子查询可以嵌套在主查询中所有位置，包括SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。

但并不是每个位置嵌套子查询都是有意义并实用的，这里对几种有实际意义的子查询进行说明。

现有表两张：一张学生表、一张班表。id相关联

在SELECT中嵌套

学生信息和班级名称位于不同的表中，要在同一张表中查出学生的学号、姓名、班级名称：

SELECT s.student_id,s.student_name,(SELECT class_name FROM t_class c WHERE c.class_id=s.class_id) FROM t_student s GROUP BY s.student_id;

* 首先这条SQL语句用到了别名，写法为在FORM的表名后加上某个字符比如FROM t_student s，这样在之后调用t_student的某一列时就可以用s.student_id来强调此列来源于对应别名的那张表。

别名在子查询及联接查询中的应用有着很好效果，当两张表有相同列名或者为了加强可读性，给表加上不同的别名，就能很好的区分哪些列属于哪张表。

还有种情况就是在子查询或联接查询时，主查询及子查询均为对同一张表进行操作，为主、子查询中的表加上不同的别名能够很好的区分哪些列的操作是在主查询中进行的，哪些列的操作是在子查询中进行的，下文会有实例说明。

接下来回到上面的SQL语句中，可以看出本条子查询的嵌套是在SELECT位置（括号括起来的部分），它与学号、学生姓名以逗号分隔开并列在SELECT位置，也就是说它是我们想要查出的一列，

子查询中查出的是，班级表中的班级id与学生表中的班级id相同的行，注意 WHERE c.class_id=s.class_id 这里就是别名用法的一个很好的体现，区分开了两张表中同样列名的列。

结果：

最后的GROUP BY可以理解为对重复行的去重，如果不加：

在WHERE中嵌套

现要查出C语言成绩最高的学生的信息：

SELECT * FROM t_student WHERE student_subject='C语言' AND student_score>=ALL (SELECT student_score FROM t_student WHERE student_subject='C语言') ;

结果：

这里出现了一个ALL，其为子查询运算符

分类：

–ALL运算符

　　和子查询的结果逐一比较，必须全部满足时表达式的值才为真。

–ANY运算符

　　和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真。

–EXISTS/NOT EXISTS运算符

　　EXISTS判断子查询是否存在数据，如果存在则表达式为真，反之为假。NOT EXISTS相反。

在子查询或相关查询中，要求出某个列的最大值，通常都是用ALL来比较，大意为比其他行都要大的值即为最大值。

要查出C语言成绩比李四高的学生的信息：

SELECT * FROM t_student WHERE student_subject='C语言' AND student_score >(
SELECT student_score FROM t_student WHERE student_name='李四' AND student_subject='C语言');

通过上面两例，应该可以明白子查询在WHERE中嵌套的作用。通过子查询中返回的列值来作为比较对象，在WHERE中运用不同的比较运算符来对其进行比较，从而得到结果。

现在我们回到最开始的问题，怎么查出每门课最高成绩的学生的信息：

SELECT * FROM t_student s1 WHERE s1.student_score >= ALL(SELECT s2.student_score FROM t_student s2 WHERE s1.`student_subject`=s2.student_subject);

这里就是上文提到的别名的第二种用法，主、子查询对同一张表操作，区分开位于内外表中相同的列名。

结果：

子查询的分类

–相关子查询

　　执行依赖于外部查询的数据。

　　外部查询返回一行，子查询就执行一次。

–非相关子查询

　　独立于外部查询的子查询。

　　子查询总共执行一次，执行完毕后后将值传递给外部查询。

上文提到的例子中，第一个例子求学生对应班级名的即为相关子查询，其中WHERE c.class_id=s.class_id 即为相关条件。其他的例子均只对一张表进行操作，为非相关子查询。

需要注意的是相关子查询主查询执行一回，子查询就执行一回，十分耗费时间，尤其是当数据多的时候。

组合查询（UNION）

通过UNION运算符来将两张表纵向联接，基本方式为：

SELECT 列1 , 列2 FROM 表1
UNION
SELECT 列3 , 列4 FROM 表2;

UNION ALL为保留重复行：

SELECT 列1 , 列2 FROM 表1
UNION ALL
SELECT 列3 , 列4 FROM 表2;

组合查询并不是太实用，所以这里只是简单提一下，不举出例子了。

上文说过相关子查询不推荐使用，组合查询又用的少之又少，那需要关联的多张表我们怎么做？

这就是下一篇博文要详细说明的SQL的重点表联接、联接查询。而此篇博文目的是为了对嵌套查询、别名的用法等等打下基础，毕竟只是写法变了，思路还是相似的。

连接查询

相关子查询效率低下，那我们怎么能将不同表的信息一起查询出来呢？这就需要用到表联接。

和之前的UNION组合查询不同，UNION是将不同的表组合起来，也就是纵向联接，说白了就是竖着拼起来。

而表联接是通过笛卡尔乘积将表进行横向联接，所谓的通过笛卡尔乘积简单说就是两表的行依次相联再相加。要想更详细的理解可以百度下，毕竟本文主要是汇总SQL语句。

现在有如下两张表：

这是当初老师布置的一份作业，我偷个懒就不改数据了。不过把这些真神级人物的大名贴出来做“实验”总觉得心里有很虚，更何况大部分都是IT业的。如有什么不敬我先道个歉，别跟我一般见识。

好了，扯远了。怎么联接这两张表呢？标准写法：

SELECT * FROM t_student JOIN t_class

结果这里只截一小部分图，因为笛卡尔乘积后的行数等于两张表的行数乘积，实在太多了。

这里就可以理解表联接的原理了，依次相连再相加。当然其中很多是无效行，为了去除无效的行我们就要用到外键来进行约束。学生表中的_fk与班级表中的_infor相关联：

SELECT * FROM t_student s JOIN t_class c ON s._fk=c._infor;

结果：

这里通过外键的匹配我们就得到了一张完美的联接之后的表，它可以看做一张新表，想要任何数据均可以从此表中查询，这就是表联接的强大之处。

表联接的分类：

内联接：

内联接是指两个表中某一行相关的列值匹配时，这一行才会出现在表中。就像上例中s._fk与c._infor相同时才会出行该行，其他的行剔除。

语法为INNER JOIN 其中INNER可以省略。

内联接的简写：

SELECT * FROM t_student s,t_class c WHERE c._infor = s._fk

* 此写法也是我们用的最多的。

外联接：

分为左外联接与右处联接。

外联接是指不管有没有匹配，被定义了外联接的表数据都要出现在结果中。比如左外联接，那么在JOIN左边的表就被定义为外联接，那么此表中所有数据都会出现在查询结果中。

注意班级表中的四班是没有学生的，所以在内联接之后理所当然的被剔除了。现在以外联接做示例：

SELECT * FROM t_student s RIGHT JOIN t_class c ON s._fk=c._infor;

上面SQL中表t_class在写在JOIN的右边，所以我们用RIGHT JOIN来进行外联接。

最下面多了一行四班的信息

例如我们想查出还没有学生录入的班级信息：

SELECT c._id,c._cname,c._code FROM t_student s RIGHT JOIN t_class c ON s._fk=c._infor WHERE s._id IS NULL;

这就是外联接的用法，通常用在我们想要的数据匹配不上时。

自联接：

自联接属于内联接或外联接的一种特例，自联接所联接的表均是来自同一张，用法个人感觉还是比较巧妙的。

现有一表如下：

表中，6个人均属于某公司的员工。区别是李四为张三和王五的领导，张八为赵六和孙七的领导。leader_id与work_id相关联。

现在可以通过自联接巧妙的将一张表分为员工部分和领导部分：

SELECT w.work_name,l.work_name 领导姓名 FROM t_emp w,t_emp l WHERE w.leader_id=l.work_id;

注意别名的用法

结果：

posted @ 2023-03-09 10:49 小小小白丶阅读(91) 评论(0) 编辑收藏举报

刷新页面返回顶部

Xiaoxiao-bai

SQL语句汇总

聚合函数、分组、子查询及组合查询

公告