[大数据]SQL汇总

这里总结了在MySQL5.7环境下，用到的SQL用法。主要设计数据查询，数据删除，数据增加和数据修改。其中数据查询是主要的部分。

1.查询数据

1.1查询单列数据

#1.1查询单列数据
#查询所有的学生姓名
SELECT StuName FROM stu_info

结果：

语法：

?通过以上语法即可根据列名查询出相应表中对应列的数据了。

1.2查询多列数据

#1.2查询多列数据
#查询所有的学生姓名和性别

SELECT StuName,StuGender FROM stu_info;

结果：

?语法：

1.3查询所有列数据

#1.3查询所有列数据
#查询stu_info表的所有信息

SELECT * FROM stu_info;

结果：

语法：

2.过滤数据

2.1检索单个值

#2.1检索某列单个值得信息
#查询张三同学的生日信息

SELECT StuBirthday FROM stu_info WHERE StuName='张三'

结果：

语法：

?

2.2范围值检索

#2.2范围值检索
#查询出生在2007到2009年之间的所有学生姓名

SELECT StuName FROM stu_info WHERE StuBirthday BETWEEN '2007-01-01' AND '2009-12-31';

结果：

语法：

2.3空值检查

#2.3空值检索
#查询没有填写出生日期的学生信息

SELECT StuName FROM stu_info WHERE StuBirthday IS NULL;

结果:

语法：

?

2.4多条件组合检索

#2.4多条件组合检索

#查询ClassId为5的女同学姓名
#AND表示多条件同时满足
SELECT StuName FROM stu_info WHERE ClassId=5 AND StuGender='女';


#查询ClassID为1或出生在2006年之前的学生姓名
#OR表示满足多条件中的任何一个
SELECT StuName FROM stu_info WHERE ClassID = 1 OR StuBirthday<'2006-01-01';

结果：

?

语法：

2.5IN操作符

#2.5IN操作符
#查询ClassID为2,3,6的学生姓名
#当条件集合已知时可以通过OR关键字实现

SELECT StuName FROM stu_info WHERE ClassId=2 OR ClassId=3 OR ClassId=6;


#当条件集合未知时可以通过IN关键字实现
#相比较OR关键字实现，IN关键字实现更简洁

SELECT StuName FROM stu_info WHERE ClassId IN (2,3,6);

结果:

?

语法:

2.6NOT操作符

#2.6 NOT操作符
#NOT表示非的意思

#查询学生信息表中填写了生日的学生姓名

SELECT StuName FROM stu_info WHERE StuBirthday IS NOT NULL;

SELECT StuName FROM stu_info WHERE  NOT (StuBirthday IS NULL);

#查询ClassId不是2,3,6的学生姓名

SELECT StuName FROM stu_info WHERE ClassId NOT IN (2,3,6);

SELECT StuName FROM stu_info WHERE NOT (ClassId IN (2,3,6));

结果:

语法:

2.7LIKE操作符

#2.7LIKE操作符

#LIKE表示启动通配模式
#%通配符表示任意长度的任意字符
#查询教师表中姓李的老师

SELECT TeacherName FROM teacher_info WHERE TeacherName LIKE '李%';

#_通配符表示单长度的任意字符
#查询教师表中姓名为两个字的老师

SELECT TeacherName FROM teacher_info WHERE TeacherName LIKE '__';

结果：

语法：

?2.8RLIKE操作符

#RLIKE表示启动正则匹配模式，可以匹配正则表达式
#MySQL中匹配正则表达式时，对英文匹配比较好
#针对中文匹配需要做特殊处理，将中文放置于()中

#[]通配符表示一个长度[]之间的字符
#查询邮箱以字母开头的教师名称
SELECT TeacherName FROM teacher_info WHERE TeacherEmail RLIKE '^[a-z].*';

#[^]通配符表示一个长度非[]之间的字符
#查询邮箱不以字母开头的教师名称

SELECT * FROM teacher_info WHERE TeacherEmail RLIKE '^[^a-z].*';

结果：

? ? ?

语法：

3.排序数据

3.1根据单列排序

#3.1根据单列排序
#根据老师年龄由大到小排列，输出教师名称
#默认排序为升序排列

SELECT TeacherName,TeacherBirthday FROM teacher_info ORDER BY TeacherBirthday;


#通过DESC关键字，改变排序方式为降序排列

SELECT TeacherName,TeacherBirthday FROM teacher_info ORDER BY TeacherBirthday DESC;

结果:

? ? ?

语法:

3.2根据多列排序

#3.2根据多列排序
#当需要根据多列进行排序时，ORDER BY关键字后面可以跟多个列，并指定每列的排列顺序
#根据老师出生年月和TeacherId两个列进行排序，出生年月为降序，TeacherId也是降序排列

SELECT TeacherId,TeacherName,TeacherBirthday FROM teacher_info 
ORDER BY TeacherBirthday DESC, TeacherId DESC;

结果:

语法:

4.创建计算字段

4.1拼接字段

#4.1拼接字段信息
#以教师姓名(性别)的方式显示教师信息
#MySQL通过concat函数将多个字符串拼接成一个字符串

SELECT CONCAT(TeacherName,'(',TeacherGender,')') FROM teacher_info;

SELECT CONCAT(TeacherName,'(',TeacherGender,')') AS teacher FROM teacher_info;

结果:

??

语法:

4.2执行算术计算

#4.2执行算术计算
# +加法，-减法, * 乘法，/除法，%取模

SELECT id,id+2,id-1,id*2,id/4,id%2 FROM score_info;

结果:

语法：

4.3函数使用

MySQL中支持的函数，具体需要查看MySQL手册进行查看。

#4.3函数使用

#将老师邮箱全部以大写显示

SELECT TeacherName,TeacherEmail,UPPER(TeacherEmail) FROM teacher_info;

结果:

语法:

5.聚合分组

5.1聚合函数

#5.1聚合函数
#通常MySQL的聚合函数有如下几类
#最大值，最小值，平均值，计数，求和
#聚合函数就是针对一组数据进行相应的计算，最终得出一个结果

#查询成绩表中最大值，最小值，平均值，成绩个数，所有成绩总和

SELECT MAX(score), MIN(score),AVG(score),COUNT(1),SUM(score) FROM score_info;

SELECT MAX(score) AS '最大值', MIN(score) AS '最小值',AVG(score) AS '平均值',
COUNT(1) AS '计数',SUM(score) AS '求和' FROM score_info;

结果:

?

语法:

5.2分组

#5.2分组
#有时候需要根据某一列的内容进行分组，然后根据分组的聚合计算另外一列的值
#这时候就需要使用到分组
#计算每个stu_id的平均成绩

SELECT stu_id, AVG(score) AS '平均分' FROM score_info GROUP BY stu_id;

结果:

语法:

5.3分组过滤

#5.3分组过滤
#有时候需要从分组后的数据中筛选出一部分，这个时候就需要使用分组过滤了

#计算出平均成绩大于80分的stu_id

SELECT stu_id, AVG(score) AS '平均分' FROM score_info GROUP BY stu_id HAVING AVG(score)>80;

结果:

语法:

6.子查询

#6.子查询
#当进行数据筛选的时候需要依据另外一个或多个查询结果时就需要用到子查询

#查询初二(二班)的学生姓名
SELECT
	StuName 
FROM
	stu_info 
WHERE
	ClassId IN ( SELECT ClassId FROM class_info WHERE ClassName = '初二(二班)' )

结果:

语法:

7.表连接

通常一张表里面只存储了部分数据，当需要查询完整的数据时，通常需要查询多个表。
并将多个表中的需要信息组合起来形成自己需要的表。

7.1内连接

#7.1内连接
#内连接只显示满足条件的信息

#显示学生姓名和对应的班级名称

SELECT S.StuName,C.ClassName FROM stu_info AS S INNER JOIN class_info AS C ON S.ClassId = C.ClassId

结果:

语法:

7.2左外连接

#7.2左连接
#当要求部分表中信息全部显示时，就不能使用内连接了，因为内连接只会显示满足连接条件的信息
#左连接是显示左表中所有的信息，而不管其是否满足连接条件

#显示所有学生姓名和对应的班级名称

SELECT S.StuName,C.ClassName FROM stu_info AS S LEFT JOIN class_info AS C ON S.ClassId = C.ClassId

结果:

语法:

7.3右外连接

#7.3右连接
#当要求部分表中信息全部显示时，就不能使用内连接了，因为内连接只会显示满足连接条件的信息
#右连接是显示右表中所有的信息，而不管其是否满足连接条件
#右连接和左连接意思相同，区别在于显示那个方向的表的全部信息

#显示所有班级名称和对应的班级名称

SELECT S.StuName,C.ClassName FROM stu_info AS S RIGHT JOIN class_info AS C ON S.ClassId = C.ClassId

结果:

语法:

8.组合查询

当数据比较大时，有时候会将信息进行分表存储。例如将一个地区的信息存放在一个表中。但是在使用时，需要将所有表的信息综合起来，形成一个数据量大的表。这个就是组合查询。

8.1去重组合

#8.1去重组合

#综合两张班级表显示所有的班级信息

SELECT ClassName FROM class_info
UNION
SELECT ClassName FROM class_info1

结果:

语法:

8.2不去重组合

#8.2不去重组合查询
#在去重组合查询中，会自动去重。
#当需要显示所有信息(不去重)时，需要使用UNION ALL关键字

SELECT ClassName FROM class_info
UNION ALL
SELECT ClassName FROM class_info1

结果:

语法:

9.插入数据

9.1插入单条数据

#9.1插入单条数据

#添加一名学生信息

INSERT INTO stu_info VALUES(10,'李逵','男','2003-06-04',7);

INSERT INTO stu_info(StuID,StuName) VALUES (11,'李旋风');

#查询插入结果
SELECT * FROM stu_info;

结果:

?

语法:

?

9.2插入多条数据

#9.2插入多条记录

#一次添加两名学生信息
INSERT INTO stu_info 
VALUES
(12,'李鬼','男','2001-06-04',8),
(13,'李莫愁','女',NULL,NULL);


#查询插入结果
SELECT * FROM stu_info;

结果：

?

语法:

9.3以查询数据创建新表

#9.3以查询数据创建新表

#创建一个新的表，该表中的内容为学生表中的部分信息
CREATE TABLE stu_info2 AS
SELECT StuName,StuGender FROM stu_info;

#查询插入结果
SELECT * FROM stu_info2;

结果:

语法:

?9.4将检索结果插入数据表中

#?9.4将检索结果插入数据表中

#将stu_info中的学生信息全部复制到stu_info1表中

INSERT INTO stu_info1

SELECT * FROM stu_info;

#查询插入结果
SELECT * FROM stu_info1;

结果:

语法:

?

10.更新数据

#10更新数据

#将李莫愁同学的班级改为9

UPDATE stu_info SET ClassId=9 WHERE StuName='李莫愁';

#查看更新结果
SELECT * FROM stu_info WHERE StuName='李莫愁';

结果：

语法:

11.删除数据

#11删除数据
#删除没有编入班级的学生信息

DELETE FROM stu_info WHERE ClassId IS NULL;

#查询删除结果

SELECT * FROM stu_info;

结果:

语法:

?