常用SQL语句及进阶

这里以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

多表查询

多表连接图解。

交叉连接(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 条件]

交叉连接图：

内连接

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

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 条件表达式]

右外连接

右外连接与左外连接相似，不同的是右表为主表，右表中的每条记录必定出现在结果集中，而在左 表中没有对应的记录，将以 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')