SQL语句之DML && DQL

Smiling Mr. Rui

于 2024-02-01 16:34:25 发布

阅读量929

点赞数 28

分类专栏： DATABASES 文章标签： sql 数据库大数据

本文链接：https://blog.csdn.net/AXDRXS/article/details/135974574

版权

DATABASES 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

1.1.2 REPLACE与INSERT的区别

2.1.2 SELECT语句中的算数表达式

2.1.10 GROUP CONCAT分组数据合并

前言：

在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;