常用SQL语句及进阶

柔情只为你懂 2021-09-28 04:10 476阅读 0赞

### 常用的SQL语句 ###

*   *  常用SQL
     *   *  库操作
         *  表操作
         *  属性增删改查
     *  进阶查询
     *   *  单表查询
         *   *   *  1.SELECT基本语法
                 *  2.WHERE 子句常用的查询条件
                 *  3.带IN的关键字查询
                 *  4.带BETWEEN AND 的范围
                 *  5.带LIKE 的字符串匹配查询
                 *  6.查询空值
                 *  7.对结果集排序
                 *  8.限制数量
                 *  9.聚合函数
                 *  10.分组统计
                 *  11.使用HAVING对分组过滤
                 *  12.综合案例
         *  多表查询
         *   *   *  交叉连接(CROSS JOIN)
                 *  内连接
                 *  外连接
                 *   *  左外连接
                     *  右外连接
                 *  内连接、左外、右外连接的区别
             *  集合运算操作
             *   *  1.并集运算 UNION，UNION ALL
                 *  2.交集运算 INTERSECT
                 *  3.差集运算 EXCEPT；MINUS(oracle)
         *  子查询
         *   *   *  IN 和 NOT IN
                 *  EXISTS 和 NOT EXISTS
                 *  IN 与 EXISTS的区别
         *  其他
         *   *  表的复制
             *   *  表结构的复制
                 *  表结构和内容的复制
             *  日期类处理

> 这里以MySql为SQL语法数据库，其他数据库会注释

## 常用SQL ##

### 库操作 ###

CREATE DATABASE 数据库名;	-- 创建数据库
    SHOW DATABASES;				-- 显示所有的数据库
    DROP DATABASE 数据库名;		-- 删除指定数据库

### 表操作 ###

SHOW TABLES;				-- 显示所有的表
    DESCRIBE 表名;				-- 查看表基本结构
    SHOW CREATE TABLE 表名		-- 查看表详细结构语句
    CREATE TABLE 表名(属性名 数据类型 [完整性约束条件],
    		属性名 数据类型 [完整性约束条件],
    		...
    );							-- 创建表
    
    ALTER TABLE 旧表名 RENAME [TO] 新表名;	-- 修改表名
    
    ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;	--修改表的字段名
    ALTER TABLE 表名 MODIFY (属性名 数据类型)	-- 修改字段的数据理性
    ALTER TABLE 表名 ADD (属性名1 数据类型 [完整性约束条件][FIRST|AFTER 属性名2])	-- 增加字段
    	/* 'FIRST'参数是可选参数,其作用是将新增字段设置为表的第一个字段, 'AFTER 属性名2'参数也是可选参数,其作用是将新增字段添加到'属性名2'所指的字段后。 如果执行的sql语句中没有'FIRST','AFTER 属性名2'参数指定新增字段的位置,新增的字段默认为表的最后一个字段。 */
    ALTER TABLE 表名 DROP (属性名);				-- 删除字段
    ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;		-- 删除表的外键
    
    DROP TABLE 表名;							-- 删除未被关联的表
    	/* 如果删除被其他表关联的父表,最简单的办法: 1.先删除子表,然后再删除父表,但这样可能会影响到字表的其他数据。 2.先删除字表的外键约束,再删除父表。 */

### 属性增删改查 ###

增(插入数据):
    -- 为表的所有字段插入数据
    INSERT INTO 表名 VALUES (value 1, value 2,···);
    INSERT INTO 表名 (属性1,属性2,···,属性n) VALUES (value 1,value 2,···,value n);
    -- 为表的指定字段插入数据
    INSERT INTO 表名 (属性1,属性2,···,属性n) VALUES (value 1,value 2,···,value n);
    -- 同时插入多条数据
    INSERT INTO 表名 [(属性列表)] VALUES (取值列表 1),(取值列表 2),...,(取值列表 n);
    -- 将查询结果插入到表中
    INSERT INTO 表名1 (属性列表 1) SELECT 属性列表 2 FROM 表名2 WHERE 条件表达式;
    	/* 其中,'表名1'参数说明记录是插入到哪个表中; '表名2'表示记录是从哪个表查出来的; '属性列表1'表示为哪些字段赋值; '属性列表2'表示从表中查询出哪些字段的数据; '条件表达式'参数设置了 SELECT 语句的查询条件。 */
    
    删除(删):
    DELETE FROM 表名 [WHERE 条件表达式];
    
    更新(改):
    UPDATE 表名 SET 属性名1 = 取值1,属性名2 = 取值2,...,属性名n = 取值n WHERE 条件表达式
    
    查询(查):
    SELECT 需要查询的属性 FROM 表名 [WHERE 条件表达式]
    SELECT 属性名1 [AS] 别名1, 属性名2 [AS] 别名2 FROM 表名 [WHERE 条件表达式]

## 进阶查询 ##

### 单表查询 ###

> SQL语句基本执行顺序:  
> SELECT  
> FROM  
> WHERE  
> GROUP BY  
> HAVING  
> ORDER BY  
> LIMIT

##### 1.SELECT基本语法 #####

SELECT [DISTINCT]属性列表
    	FROM 表名或视图列表
    	[WHERE 条件表达式]
    	[GROUP BY 属性名1 [HAVING 条件表达式2]]
    	[ORDER BY 属性名2 [ASC|DESC]]
    DISTINCT显示结果时 是否剔除重复项;
    '属性列表'参数表示需要查询的字段名;
    '表名和视图列表'参数表示从此处指定的表或者视图中查询数据,表和视图可以有多个;
    '条件表达式1'参数指定查询条件;'属性名1'参数指按该字段中的数据进行分组;
    '条件表达式2'参数表示满足该表达式的数据才能输出;
    '属性名2'参数指按该字段中的数据进行排序,排序方式由 ASC 和 DESC 两个参数指出,
    	ASC 参数表示按升序的顺序进行排序,这是默认参数;DESC 参数表示按降序进行排序。
    如果有 WHERE 子句,就按照'条件表达式1'指定的条件进行查询;如果没有WHERE子句,就查询所有记录;
    如果有 GROUP BY 子句,就按照'属性名1'指定的字段进行分组;
    	如果 GROUP BY 子句后带着 HAVING 关键字,那么只有满足'条件表达式2'中指定的条件的才能够输出。
    	GROUP BY 子句通常和 COUNT()、SUM()等聚合函数一起使用。 
    如果有 ORDER BY 子句,就按照'属性名2'指定的字段进行排序。

##### 2.WHERE 子句常用的查询条件 #####

查询条件						符合或关键字
    	 比较				=、<、<=、>、>=、!=、<>、!>、!<
    	指定范围				BETWEEN AND、NOT BETWEEN AND
    	指定集合						IN、NOT IN
    	匹配字符					  LIKE、NOT LIKE
       是否为空值				    IS NULL、IS NOT NULL
       多个查询条件				      AND、OR

##### 3.带IN的关键字查询 #####

IN 关键字可以判断某个字段的值是否在指定的集合中,
    如果字段的值在集合中,则满足查询条件,该记录将被查询出来;如果不在集合中,则不满足查询条件。
    [NOT] IN(元素 1,元素2,…,元素 n) 
    例:查询学生id为2,3,4的学生信息
    SELECT * FROM student WHERE stuid IN(2,3,4);

##### 4.带BETWEEN AND 的范围 #####

[NOT] BETWEEN 取值1 AND 取值2
    例:查询生日在19970415-19970430之间的学生信息
    SELECT * FROM student WHERE birthday BETWEEN '1997-04-15' AND '1997-04-30';

##### 5.带LIKE 的字符串匹配查询 #####

%:代表多个字符
    _:代表单个字符
    例:查询姓名以x开头的学生信息
    SELECT * FROM student WHERE name LIKE 'x%';
    例:查询姓名以z开头且只有两位长度的学生信息
    SELECT * FROM student WHERE name LIKE 'z_';

##### 6.查询空值 #####

IS [NOT] NULL

##### 7.对结果集排序 #####

SELECT * FROM 表名
    	[WHERE 条件表达式]
    	ORDER BY 字段1 [ASC/DESC] [,字段2 [ASC/DESC]...]  -- 默认为ASC升序排序
    例:查询big_data表中的人员数目且降序排序
    select people_num from big_data DESC

##### 8.限制数量 #####

LIMIT [初始位置], 记录数
    例:查询学生表分数最高的且分数>=60的学生信息。
    select * from student WHERE score>=60 DESC LIMIT 0,10	-- 默认从0开始,取几个

##### 9.聚合函数 #####

SUM		求和
    AVG		求平均值
    MAX/MIN		求最大/最小
    COUNT	计数
    例:求学生的平均成绩
    SELECT AVG(score) FROM student

##### 10.分组统计 #####

分组统计是使用 GROUP BY 关键字将查询结果按照某个字段或多个字段进行分组。
    分组时,指定字段中值相等的被认为是一组，在查询结果中的体现是指定字段相等的情况下只保留一条记录。
    SELECT <字段列表> [聚合函数]
    	FROM 表名
    	[WHERE 条件表达式]
    	[GROUP BY <字段列表>]
    例:查询学科的平均分
    SELECT AVG(score) avg FROM score GROUP BY subid

##### 11.使用HAVING对分组过滤 #####

对分组后的结果再进行条件过滤,则不能使用WHERE语句。WHERE只是对分组前的数据进行过滤。
    SELECT <字段列表> [聚合函数]
    	FROM 表名
    	[WHERE 条件表达式]
    	[GROUP BY <字段列表>]
    	HAVING 条件

##### 12.综合案例 #####

查询所有的学生的平均分 降序(及格) 取前五名
    SELECT student.stuid,name,AVG(score) AS avg
    	FROM score,student
    	WHERE student.stuid = score.stuid
    	GROUP BY stuid		-- 分组的意义是去重复
    	HAVING score>=60
    	ORDER BY score DESC, stuid ASC
    	LIMIT 0,5

### 多表查询 ###

> 多表连接图解。  
> ![sql-joins][]

##### 交叉连接(CROSS JOIN) #####

> 交叉连接又称**笛卡儿积**:笛卡尔乘积是指在数学中两个集合 X 和 Y 的笛卡尓积（Cartesian product），又称直积，表示 为 X × Y，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一个成员。  
> 假设集合 A=\{a, b\}，集合 B=\{0, 1, 2\}，则两个集合的笛卡尔积为\{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)\}

SELECT * FROM TABLE_A CROSS JOIN TABLE_B [WHERE 条件]
    SELECT * FROM TABLE_A JOIN TABLE_B [WHERE 条件]
    SELECT * FROM TABLE_A, TABLE_B [WHERE 条件]

交叉连接图：  
![笛卡儿积][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTYyMDkw_size_16_color_FFFFFF_t_70]

##### 内连接 #####

> 内连接：又叫等值连接，只返回两个表中连接字段相等的行。  
> ![innerjoin][]

SELECT * FROM TABLE_A INNER JOIN TABLE_B ON 连接条件 [WHERE 条件]
    	-- 连接条件通常是在两个表中具有相同意义的列。

> **注意**：通常情况下使用交叉连接都要配合 WHERE 对连接后的迪卡尔积进行过滤，  
> 而**内连接**可以使用 **ON** 设置连接条件，在对数据进行拼接时，  
> 根据连接条件决定如何将两个表或多个表的数据进行关联，  
> 省去了无用记录的关联拼接。因此**内连接的效率要比交叉连接高**！

举个例子:获取姓名为xzy的学生的所有成绩
    SELECT student.stuid, name, subid, score
    	FROM student INNER JOIN score		-- 内连接
    	ON student.stuid = score.stuid		-- 连接条件
    	WHERE student.name = 'xzy'

##### 外连接 #####

> 外连接与内连接不同，内连接时，根据连接条件**只保留两个表中有对应数据的记录**;  
> 而外连接时,当一个表中记录在另一个表中**没有对应记录**时,会生成一条与 **NULL** 值对应的记录.  
> 外连接根据数据保留表的不同,又分为**左外连接**和**右外连接**。  
> **左外连接**时,保留**左表中的所有数据**,**右外连接**时,保留**右表的所有数据**。

###### 左外连接 ######

> 左外连接使用 **LEFT JOIN** 连接两表，连接时**左表为主表**，左表中的每条记录必定出现在结果集中，而在右表中没有对应的记录，将以 NULL 值进行填充。

SELECT * 
    	FROM 表名1 LEFT [OUTER] JOIN 表名2  -- 表名1为主表
    	ON 连接条件
    	[WHERE 条件表达式]

![外连接][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTYyMDkw_size_16_color_FFFFFF_t_70 1]

###### 右外连接 ######

> 右外连接与左外连接相似，不同的是**右表为主表**，右表中的每条记录必定出现在结果集中，而在左 表中没有对应的记录，将以 NULL 值进行填充.

SELECT * 
    	FROM TABLE_A RIGHT [OUTER] JOIN TABLE_B		--TABLE_B为主表 
    	ON 连接条件
    	[WHERE 条件表达式]

##### 内连接、左外、右外连接的区别 #####

> |-----| — |------|  
> | A1 | C | B1 |  
> |-----| — |------|  
> 内连接 得到的结果是公有数据C  
> 左外连接 得到的是 A1+C  
> 右外连接 得到的是 B1+C

#### 集合运算操作 ####

##### 1.并集运算 UNION，UNION ALL #####

> UNION 会排除重复项，UNION ALL 不会排除重复项

select name from table1 where id = '1'
    UNION
    select name from table2 where id = '5'
    order by name desc

##### 2.交集运算 INTERSECT #####

SELECT name from t1  
INTERSECT  
SELECT name from t2;

##### 3.差集运算 EXCEPT；MINUS(oracle) #####

SELECT name from t1  
MINUS  
SELECT name from t2;

### 子查询 ###

> 当一个查询需要用到另一个查询的结果时,在查询语句中可以出现多层嵌套查询。  
> 这种查询语句叫做**子查询**。

例:
    SELECT *
    	FROM student
    	WHERE birthday >=		-- 单值比较子查询
    		(						-- 括号代表一个整体，子查询的结果作为一个整体
    		SELECT birthday FROM student
    			WHERE 'name' = 'Jay'
    		)

> 批量比较查询:  
> = ALL 等于子查询结果中的所有的值;  
> <= ALL 大于等于子查询结果中的所有值;  
> <= ANY 大于等于子查询结果中的任意值;  
> < ANY 小于子查询结果中的任意值;  
> ···

##### IN 和 NOT IN #####

> 在嵌套子查询中，***子查询的结果如果是一个集合***，此时就不能直接使用比较运算符和结果进行比较了，应用**IN**和**NOT IN**。

例:查询参加考试的学生信息
    SELECT * FROM student
    	WHERE stuid IN (			-- 获取stuid集合作为外部查询的条件
    		SELECT stuid FROM score
    	)

##### EXISTS 和 NOT EXISTS #####

> EXISTS和NOT EXISTS也是判断是否存在，与IN类似，但是效率比IN高。

> **使用EXISTS 和 NOT EXISTS时：**  
> 首先取*外层查询表*的*第一个记录*，拿这个记录与内层查询相关的属性值去**参与**内层查询的求解，若内层查询的 WHERE 子句返回真值，则将这个记录放入结果集；  
> 然后再取外层查询表的下一条记录；重复上述过程，知道外层表处理完成为止。

例:查询已考试的科目
    SELECT * FROM subject
     WHERE EXISTS (
     	SELECT 1			-- 提高效率
     		FROM score
     		WHERE score.subid = subject.subid
     )

##### IN 与 EXISTS的区别 #####

> IN：先执行子查询，返回一个*集合*，然后再将**子查询的结果作为外层查询的条件**进行过滤。  
> EXISTS：先执行外层查询，再将*外层查询的每一条记录作为条件*进行子查询，子查询的**返回值是一个TRUE或一个FALSE**，因此一般情况下子查询中直接使用SELECT 1提高效率。

### 其他 ###

#### 表的复制 ####

##### 表结构的复制 #####

-- mysql中
    CREATE TABLE new_table SELECT * FROM old_table WHERE 1=2; -- 方法一
    CREATE TABLE new_table LIKE old_table ; -- 方法二

##### 表结构和内容的复制 #####

-- mysql中
    CREATE TABLE new_table SELECT * FROM old_table; -- 创建新表并复制旧表数据
    INSERT INTO 新表 SELECT * FROM 旧表;  -- 表结构需要相同
    INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表; -- 表结构不同的话

#### 日期类处理 ####

now()
    DATE_FORMAT(Now(),'%Y-%m-%d')

[sql-joins]: /images/20210923/3c8c4da928f74cb499d110f3a48b15ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTYyMDkw_size_16_color_FFFFFF_t_70]: /images/20210923/463f4e30a9394ae9bfce1ccf76dce110.png
[innerjoin]: /images/20210923/2e58d59ef73745bba61784659c8a4eae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTYyMDkw_size_16_color_FFFFFF_t_70 1]: /images/20210923/55c15489d78b4a6da8d51a5f32ef01e3.png