目录
前言:
在MySQL数据库中,SQL语句分类有DDL、DCL、DML与DQL,今天我们就来学习一下数据操作语言(DML)以及数据查询语言(DQL)。
一、DML
有关数据表的DML操作,我们主要深入学习三个关键词,分别是insert、delete、update;
INSERT 针对于数据的插入;
DELETE 针对于数据的删除;
UPDATE 针对于数据的修改;
1.1 INSERT语句
INSERT INTO 表名 [(列名1,列名2,....)] VALUES (值1,值2,...);
默认情况下,一条插入命令只针对一行进行影响
INSERT INTO 表名 [(columnName,[columnName...])] VALUES (value[,value....]),(value[,value....]),(value[,value....]).....;
一次性插入多条记录
如果我们为每一列都要指定注入的值,那么表名后面就不需要罗列插入的列名了
INSERT INTO 表名 VALUES (值1,值2,值3,....)
CREATE TABLE `grade` (
`GradeID` int(0) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`GradeName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '年级名称',
PRIMARY KEY (`GradeID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
1.1.1 REPLACE语句
replace语句的语法格式有三种语法格式。
语法格式1:
replace into 表名 [(字段列表)] values (值列表);
语法格式2:
replace [into] 目标表名[(字段列表1) select (字段列表2) from 源表 [where 条件表达式];
语法格式3:
replace [into] 表名 set 字段1=值1, 字段2=值2;
1.1.2 REPLACE与INSERT的区别
replace语句的功能与insert语句的功能基本相同,不同之处在于:使用replace语句向表插入新记录时,如果新记录的主键值或者唯一性约束的字段值与已有记录相同,则已有记录先被删除(注意:已有记录删除时也不能违背外键约束条件),然后再插入新记录。
使用replace的最大好处就是可以将delete和insert合二为一(效果相当于更新),形成一个原子操作,这样就无需将delete操作与insert操作置于事务中了。
1.2 DELETE语句
语法格式:
DELETE (TRUNCATE) FROM 表名 [WHERE 条件];
作用:删除数据,保留表结构,必要时可以回滚,但是如果数据量较大,运行速度不及TRUNCATE
1.2.1 TRUNCATE语句
作用:删除所有数据,保留表结构,不能够回滚。一次全部删除所有数据,速度相对很快
1.2.2 DROP语句
作用:删除数据和表结构,删除速度最快
语法格式:
DROP DATABASE 数据库名;
DROP TABLE 表名;
1.3 UPDATE语句
语法格式:
UPDATE 表名 SET 列名 = 值 [,列名 = 值,列名 = 值,列名 = 值,...] [WHERE 条件];
二、DQL
数据查询语言主要依靠select语句,并且查询包括简单查询、条件查询、分组查询、排序查询、区间查询与i及多表关联查询等等。
2.1 SELECT语句
2.1.1 基础语法与格式:
SELECT {*,列名,函数}
FROM 表名
[WHERE 条件];
说明:
-SELECT检索关键字 *匹配所有列 , 匹配指定列
-FROM 所提供的数据源(表,视图,另一个查询机制反馈的结果)
-WHERE 条件(控制查询的区域)
SELECT *
FROM student; #查询学生表的所有列以及所有行 ====> 学生表的全部信息
SELECT StudentName,Address,Email #查询指定三列的内容所有行 ====> 学生表的姓名 住址 邮箱
FROM student;
SELECT StudentName,Address,Email #查询指定三列的内容指定行 ====> 学生表的姓名 住址 邮箱
FROM student
WHERE sex = 0;
#结论 SELECT 关键字 和 FROM 关键字之间 写的东西 控制的是我们结果的列数
# WHERE 写的东西 控制的是我们结果的行数
#生日在2000-01-01 2010-01-01 的男生 的 姓名
SELECT StudentName
FROM student
WHERE BornDate>'2000-01-01' AND BornDate<'2010-01-01' AND sex = 1;
2.1.2 SELECT语句中的算数表达式
对数值型数据列、变量、常量可以使用算数操作符创建表达式(+ - * /);
对日期型数据列、变量、常量可以使用部分算数操作符创建表达式(+ -);
运算符不仅可以在列和常量之间进行运算,也可以在多列之间进行运算;
SELECT 100+80;
SELECT '300'+80; #只要其中一个是数值类型,而另一个能够转成数值,则自动转换并计算
SELECT 'ABC'+20; #若转换不成功,则将其认为是数字0对待
SELECT 'Hello'+'World';#若转换不成功,则将其认为是数字0对待
SELECT NULL+80; #只要有一个为NULL,则结果为NULL
补充:<==> 安全等于 等价于 = 和 IS 两者的结合;
示例1:查询学号为1001的学生信息
SELECT *
FROM student
WHERE StudentNo <==> 1001; # WHERE StudentNo = 1001;
示例2:查询邮箱为空的学生的信息
SELECT *
FROM student
WHERE Email <==> NULL; # WHERE Email IS NULL;
2.1.3 运算符优先级
1,乘除优先级高于加减
2,同级运算时运算顺序由左到右
3,表达式内使用括号,可以改变优先级的运算顺序
2.1.4 NULL值的使用
String str = null;
String str = "";
null指的是 不可用、未分配的值
null不等于零或空格
任意数据类型都支持null这种表达形式
包括null的任何算数表达式结果都等于空
字符串和null进行连接运算,结果也是空
2.1.5 清除重复的记录
#我想查看学生表的性别
#缺省情况下查询显示所有行,包括重复行
SELECT sex "性别"
FROM student;
#可以使用关键字DISTINCT清除查询记录中的重复数据
SELECT DISTINCT sex "性别"
FROM student;
2.1.6 WHERE语句限制条件
1,WHERE中的字符串或日期格式的内容需要使用单引号进行专门的标识,如StudentName = '张三' 而不能直接StudentName = 张三;
2,字符串内的数据 对大小写是敏感的,如记录中有Louis77@163.com我们在检索时就不能 louis77@163.com;
3,日期值对格式是敏感的,如记录中有2000-01-01我们在检索时就不能2000年01月01日;
#示例1:查询姓名是郭靖的学生信息
SELECT *
FROM student
WHERE StudentName = '郭靖';
#示例2:查询生日是1986-12-31的学生信息
SELECT *
FROM student
WHERE BornDate = '1986-12-31';
#示例3:查询学号是1000的学生信息
SELECT *
FROM student
WHERE StudentNo = 1000;
#WHERE中的比较运算符 < > <= >= != =
#示例4:查询生日在2000-01-01之后的学生信息
SELECT *
FROM student
WHERE BornDate > '2000-01-01';
#WHERE中逻辑运算符 AND OR NOT
#AND需要所有条件都满足
#示例5:查询班级编号是1,并且生日在1980-01-01之后,并且性别是1的学生信息
SELECT *
FROM student
WHERE GradeId = 1 AND BornDate > '1980-01-01' AND sex = 1;
#OR只要满足多条件之一即可
#示例6:查询班级编号是1,或者生日在1980-01-01之后,或者性别是1的学生信息
SELECT *
FROM student
WHERE GradeId = 1 OR BornDate > '1980-01-01' OR sex = 1;
#NOT表示取反
#示例7:查询邮箱不为空的学生的姓名,邮箱地址
SELECT StudentName "姓名",Email "邮箱地址"
FROM student
WHERE Email IS NOT NULL;
#示例8:查询生日在2000-2010之间的学生姓名
SELECT StudentName "姓名"
FROM student
WHERE BornDate >= '2000-01-01' AND BornDate <= '2010-01-01';
#示例9:使用BETWEEN关键字实现范围查询
SELECT StudentName "姓名"
FROM student
WHERE BornDate BETWEEN '2000-01-01' AND '2010-01-01';
#示例10:查询 班级是1或2或3班的学生姓名
SELECT StudentName "姓名"
FROM student
WHERE GradeId = 1 OR GradeId = 3 OR GradeId = 2;
#示例11:使用IN关键字进行匹配
SELECT StudentName "姓名"
FROM student
WHERE GradeId IN (1,2,3);
#LIKE关键字
#该关键字主要用于执行模糊查询,查询条件可以包含文字字符或占位符
#通过%表示匹配0或多个字符
#_表示匹配一个字符
#示例12:查询学生姓名 姓名以周开始,后面字符数量不定
SELECT StudentName "姓名"
FROM student
WHERE StudentName LIKE '李%';
# LIKE '%周' 以周字结束
# LIKE '%周%' 包含周字
# LIKE '周_' 以周开始且后方匹配一个字符
2.1.7 GROUP BY分组查询
GROUP BY 字句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组.
分组的真正含义:把表中列值相同的多条记录,当成是一条记录进行处理,最终也只输出一条记录,分组函数忽略空值;
语法:
SELECT {*,列名,函数}
FROM 表名
[WHERE 基础条件]
[GROUP BY 分组条件]
[HAVING 过滤条件]
分组函数的重要规则:
1、如果 使用了分组函数,或使用了GROUP BY (字段1,字段2,...)执行查询,那么出现在SELECT 列表后的字段 要么必须是聚合函数,要么出现过在GRUOP字句内;
2、GRUOP BY子句的字段可以不出现在SELECT内;
3、使用聚合函数但不使用分组查询时,那么所有的数据会作为一组进行显示;
4、GROUP BY前面的 WHERE 表示 分组前执行的条件过滤;
5、GROUP BY后面的 HAVING表示 分组后执行的条件过滤;
#示例1:统计各班人数
SELECT COUNT(*) "人数",GradeID "班级编号"
FROM student
WHERE sex = 1
GROUP BY (GradeID);
#示例2:统计每个学生的考试总分,平均分,最高分,最低分
SELECT StudentNo "学号", SUM(StudentResult) "总分",AVG(StudentResult) "平均分",MAX(StudentResult) "最高分",MIN(StudentResult) "平最低分"
FROM result
GROUP BY (StudentNo);
#示例3:考试时间在 2012年01月01日后 统计每个学生的考试总分,平均分,最高分,最低分
SELECT StudentNo "学号", SUM(StudentResult) "总分",AVG(StudentResult) "平均分",MAX(StudentResult) "最高分",MIN(StudentResult) "平最低分"
FROM result
WHERE ExamDate >= '2012-01-01'
GROUP BY (StudentNo);
#示例4:考试时间在 2012年01月01日后 统计每个学生的考试总分,平均分,最高分,最低分 过滤掉 总分在650以下的
SELECT StudentNo "学号", SUM(StudentResult) "总分",AVG(StudentResult) "平均分",MAX(StudentResult) "最高分",MIN(StudentResult) "平最低分"
FROM result
WHERE ExamDate >= '2012-01-01'
GROUP BY (StudentNo)
HAVING SUM(StudentResult) >= 650;
2.1.8 ORDER BY 排序查询
SELECT {*,列名,函数}
FROM 表名
[WHERE 基础条件]
[GROUP BY 分组条件]
[HAVING 过滤条件]
[ORDER BY (需要排序的字段) ASC||DESC]; #ASC升序(升序) DESC(降序)
2.1.9 LIMIT区间查询
SELECT {*,列名,函数}
FROM 表名
[WHERE 基础条件]
[GROUP BY 分组条件]
[HAVING 过滤条件]
[ORDER BY (需要排序的字段) ASC||DESC] #ASC升序(升序) DESC(降序)
[LIMIT A,B];
LIMIT 连续区间查询
LIMIT A,B A表示需要查询的行的索引位 B所查询的容量
LIMIT 0,5 第一行---第五
LIMIT 5,5 第六行---第十
LIMIT 10,5 第十一---第十五
分页查询的前置
以baidu热搜为例
>>>>>>>>>>>>> 第一页 LIMIT 0,6
>>>>>>>>>>>>> 第二页 LIMIT 6,6
>>>>>>>>>>>>> 第三页 LIMIT 12,6
>>>>>>>>>>>>> 第四页 LIMIT 18,6
>>>>>>>>>>>>> 第五页 LIMIT 24,6
>>>>>>>>>>>>> 第六页 LIMIT 30,6
>>>>>>>>>>>>> LIMIT (当前页码数-1)*容量,容量
#示例1:求学校学生中 三甲学生的信息
#分析 学生总分 降序排列 区间取前三
SELECT StudentNo "学号",SUM(StudentResult) "总成绩"
FROM result
GROUP BY (StudentNo)
ORDER BY(SUM(StudentResult)) DESC
LIMIT 0,3;
2.1.10 GROUP CONCAT分组数据合并
1、使用GROUP_CONCAT()函数时必须要对数据源进行分组,如果不分组,所有数据都将合并成一行;
2、对结果集排序 查询语句执行的查询结果,数据是按照插入时顺序进行排序;
3、实际上需要按照某列大小值进行拍讯的话,建议只针对于数值或日期通过 ORDER BY函数进行排序;
4、在语句最后也可以通过LIMIT控制容量大小;
#示例1 根据班级进行分组,要求查看各班人数,以及各班学员姓名。
SELECT GradeID "班级编号",COUNT(*) "班级人数",GROUP_CONCAT(StudentName) "学员姓名"
FROM student
GROUP BY(GradeID);
三、多表关联查询
3.1 交叉连接查询
#示例1:查询所有的学生+所有的班级信息
SELECT *
FROM student,grade;
这样查询最终得道的数据有11*7=77条数据
通过笛卡尔积获取的数据,异常过多,无法匹配具体的内容,于是我们需要补充条件以提高查询的精度
3.2 等值连接查询
#示例1:查询所有的学生+所有的班级信息
#语法:SELECT * FROM 表1,表2,... WHERE 表1.列 = 表2.列 [AND...];
SELECT *
FROM student,grade
WHERE student.GradeID = grade.GradeID;
#示例2:查询所有的学生姓名,住址,班级名称
SELECT StudentName "姓名",Address "住址",GradeName "班级名称"
FROM student,grade
WHERE student.GradeID = grade.GradeID;
#更为规范化的写法
SELECT s.StudentName "姓名",s.Address "住址",g.GradeName "班级名称"
FROM student s,grade g
WHERE s.GradeID = g.GradeID;
3.3 内连接查询
内连接查询的本质和等值实际上没有区别,但是内连接可以释放WHERE关键字,使表与表之间关系更加清晰;
#语法
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.列 = 表2.列 [INNER JOIN 表3 ON 关系 .....][WHERE 基础条件];
#示例3:练习查询学生姓名,参考科目,考试时间,考试成绩
SELECT s.StudentName "学生姓名",su.SubjectName "参考科目",r.ExamDate "考试时间",r.StudentResult "考试成绩"
FROM student s INNER JOIN result r ON r.StudentNo = s.StudentNo
INNER JOIN subject su ON r.SubjectNo = su.SubjectNo;
3.4 外连接查询
3.4.1 左外连接
>>>>> LEFT JOIN 获取相交数据+左外关键字以左表的全部数据
SELECT s.StudentName "姓名",s.Address "住址",g.GradeName "班级名称"
FROM grade g LEFT JOIN student s ON s.GradeID = g.GradeID;
3.4.2 右外连接
>>>>> RIGHT JOIN 获取相交数据+右外关键字以右的全部数据
SELECT s.StudentName "姓名",s.Address "住址",g.GradeName "班级名称"
FROM student s RIGHT JOIN grade g ON s.GradeID = g.GradeID;
3.5 自然连接查询
自己与自己形成主外键关系
+------------+-----+-----------------+
| categoryId | pid | categoryName |
+------------+-----+-----------------+
| 2 | 1 | 美术设计 |
| 3 | 1 | 软件开发 |
| 4 | 3 | 数据库基础 |
| 5 | 2 | Photoshop基础 |
| 6 | 2 | 色彩搭配学 |
| 7 | 3 | PHP基础 |
| 8 | 3 | 一起学JAVA |
+------------+-----+-----------------+
假设 1 意味着是根目录
编号为2的美术设计 和编号为3的软件开发 父级都是 1 根目录
编号为3的数据库基础 是软件开发的一部分
SELECT c1.categoryName "父级目录",c2.categoryName "子栏目"
FROM category c1 INNER JOIN category c2 ON c1.categoryId = c2.pid;
3.6 子查询
1,将一个查询语句的结果充当下一个查询语句的条件;
2,核心在于通过小括号以提高优先级别;
3,子查询中可以包含的关键字 IN NOT ALL;
4,子查询中可以包含的运算符 逻辑+算数;
#示例1:查询大一的男生姓名及家庭住址
#1--大一对应的班级编号
SELECT GradeID FROM grade WHERE GradeName = '大一'; ======> 1
#2--以一年级一班对应的班级编号作为线索,去找适配的学生信息
SELECT StudentName "姓名",Address "住址"
FROM student WHERE GradeID = (SELECT GradeID FROM grade WHERE GradeName = '大一');
#3--加入我们的基础条件
SELECT StudentName "姓名",Address "住址"
FROM student WHERE GradeID = (SELECT GradeID FROM grade WHERE GradeName = '大一') AND sex = 1;
#示例2:查询班级名称是大一(学生信息==>学号信息),科目是高等数学-1(科目编号) 的学生的平均分
>>>>需求1 根据班级名 找出班级 编号
SELECT GradeID
FROM grade WHERE GradeName = '大一';
>>>>需求2 根据对应的班级编号 找到适配的学生学号
SELECT StudentNo
FROM student WHERE GradeID = (SELECT GradeID
FROM grade WHERE GradeName = '大一')
>>>>需求3 根据科目名找到对应的科目编号
SELECT subjectNo
FROM subject WHERE subjectName = '高等数学-1';
>>>>编辑最后的命令
SELECT AVG(result.StudentResult)
FROM result
WHERE result.SubjectNo = (SELECT subjectNo
FROM subject WHERE subjectName = '高等数学-1')
AND result.StudentNo IN (SELECT StudentNo
FROM student WHERE GradeID = (SELECT GradeID
FROM grade WHERE GradeName = '大一'));