三、使用DQL查询数据

1.DQL语言

DQL( Data Query Language 数据查询语言 )

查询数据库数据 , 如SELECT语句

简单的单表查询或多表的复杂查询和嵌套查询

是数据库语言中最核心,最重要的语句

使用频率最高的语句

-- SELECT语法
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序s
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条

注意 : [ ] 括号代表可选的 , { }括号代表必选得

2.指定查询字段

基础查询

-- 查询所有学生信息,用*代表查询所有，不推荐，效率低
SELECT * FROM student;
-- 查询指定列(学号 , 姓名)
SELECT studentno,studentname FROM student;

别名

给字段或者表起一个新的名字

-- 起别名(as关键词可以省略)
SELECT studentno AS 学号,studentname AS 姓名 FROM student;
-- 为表取别名
SELECT studentno AS 学号,studentname AS 姓名 FROM student AS a;

DISTINCT 去重

-- # 查看哪些同学参加了考试(学号) 去除重复项
SELECT * FROM result; -- 查看考试成绩
SELECT studentno FROM result; -- 查看哪些同学参加了考试
SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项

3.where条件语句

where作用

筛选符合条件的记录

简单查询

-- 查询考试成绩在95以上的同学
SELECT student_no,student_result FROM result
WHERE student_result >= 95;
-- 查询名字为张三的同学
SELECT student_no,student_name FROM student
WHERE student_name = '张三';

模糊查询

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式

/*like 匹配/模糊匹配，会与 % 和 _ 结合使用。
'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的
*/
-- 查询以 张 字段开头的信息。
SELECT student_no,student_name FROM student
WHERE student_name LIKE '张%';
-- 查询包含 张 字段的信息。
SELECT student_no,student_name FROM student
WHERE student_name LIKE '%张%';
-- 查询以 张 字段结尾的信息。
SELECT student_no,student_name FROM student
WHERE student_name LIKE '%张';

IN 操作符允许您在 WHERE 子句中规定多个值

-- 查询姓名为张三,李四,王五的学生学号
SELECT student_no,student_name FROM student
WHERE student_name in ('张三','李四','王五');

NULL 值代表遗漏的未知数据。在查询时不能直接写=NULL ,要用 is null

-- 查询没有填写出生日期的同学
SELECT student_name FROM student
WHERE born_date IS NULL;
-- 查询填写出生日期的同学
SELECT student_name FROM student
WHERE born_date IS NOT NULL;

4.连接查询

连接查询作用

连接查询（JOIN）用于把来自两个或多个表的行结合起来

JOIN 对比

操作符名称	描述
INNER JOIN	如果表中有至少一个匹配，则返回行
LEFT JOIN	即使右表中没有匹配，也从左表中返回所有的行
RIGHT JOIN	即使左表中没有匹配，也从右表中返回所有的行

内连接 inner join

查询两个表中的结果集中的交集

SELECT s.student_no,student_name,subject_no,student_result
FROM student s
INNER JOIN result r
ON r.student_no = s.student_no

左外连接 left join

会读取左边数据表的全部数据，即便右边表无对应数据

SELECT s.student_no,student_name,subject_no,student_result
FROM student s
LEFT JOIN result r
ON r.student_no = s.student_no

右外连接 right join

会读取右边数据表的全部数据，即便左边边表无对应数据

SELECT s.student_no,student_name,subject_no,student_result
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

5.排序和分页

排序：ORDER BY

ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果

-- ORDER BY 语句默认按照ASC升序对记录进行排序。
-- ASC升序，DESC降序
SELECT student_no,student_name
FROM student ORDER BY student_no

分页：LIMIT

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数

-- LIMIT 接受一个或两个数字参数。
-- 检索前 5 个记录行 
SELECT student_no,student_name
FROM student LIMIT 5;
-- 检索记录行 6-15 
SELECT student_no,student_name
FROM student LIMIT 5,10
-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
-- 注意：是先排序后再分页
SELECT s.student_no,student_name,subject_name,student_result
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subject_name='JAVA第一学年'
ORDER BY student_result DESC
LIMIT 0,10

6.子查询

在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句

嵌套查询可由多个子查询组成,求解的方式是由里及外

子查询返回的结果一般都是集合,故而建议使用IN关键字

-- 查询 数据库结构-1,数据库结构-2 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT student_no,r.subject_no,student_result
FROM result r
INNER JOIN `subject` sub
ON r.`subject_no`=sub.`subject_no`
WHERE subject_name in ('数据库结构-1','数据库结构-2')
ORDER BY student_result DESC;
-- 方法二:使用子查询(执行顺序:由里及外)
SELECT student_no,r.subject_no,student_result
FROM result
WHERE subject_no in (
SELECT subject_no FROM `subject`
WHERE subject_name in ('数据库结构-1','数据库结构-2')
)
ORDER BY student_result DESC;

四、MySQL函数

1.常用函数

字符串函数

-- CONCAT(s1,s2...sn) 多个字符串合并为一个字符串
SELECT CONCAT('中','国'); -- 输出:中国
-- 从字符串第一个位置开始的 6 个字符替换为 ABC：
SELECT INSERT("mysql.com", 1, 6, "ABC"); -- 输出:ABCcom
-- 等等等

数字函数

-- 绝对值
SELECT ABS(-8);
-- 随机数,返回一个0-1之间的随机数
SELECT RAND();
-- 等等等

日期函数

-- 获取当前日期和时间
sELECT NOW();
-- 计算起始日期 d 加上 n 天的日期
SELECT ADDDATE("2022-03-02", INTERVAL 10 DAY);-- 输出:2022-03-12

2.聚合函数

函数名称	描述
COUNT()	返回满足Select条件的记录总和数，如 select count() 【不建议使用，效率低】
SUM()	返回数字字段或表达式列作统计，返回一列的总和。
AVG()	通常为数值字段或表达列作统计，返回一列的平均值

MAX()	可以为数值字段，字符字段或表达式列作统计，返回最大的值。
MIN()	可以为数值字段，字符字段或表达式列作统计，返回最小的值。

-- 查询学生表中的所有数量
SELECT COUNT(student_name) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student; /*推荐*/
-- 查询成绩的总和，平均分，最高分，最低分
SELECT SUM(student_result) AS 总和 FROM result;
SELECT AVG(student_result) AS 平均分 FROM result;
SELECT MAX(student_result) AS 最高分 FROM result;
SELECT MIN(student_result) AS 最低分 FROM result;

3.分组函数

GROUP BY 语句根据一个或多个列对结果集进行分组

-- 查询不同课程的平均分,最高分,最低分
SELECT subject_name,AVG(student_result) AS 平均分,MAX(student_result) AS 最高
分,MIN(student_result) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subject_no = s.subject_no
GROUP BY r.subject_no  /*group by写在where后面，分组需要跟聚合函数*/

HAVING 可以让我们筛选分组后的各组数据

HAVING 要在 GROUP BY 后面，因为是先分组再筛选

-- 查询不同课程的平均分,最高分,最低分后筛选最高分在80分以上的课程
SELECT subject_name,AVG(student_result) AS 平均分,MAX(student_result) AS 最高分,MIN(student_result) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subject_no = s.subject_no
GROUP BY r.subject_no
HAVING 最高分>80;

五、事务

1.事务概述

什么是事务

MySQL 事务主要用于处理操作量大，复杂度高的数据

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行

ACID

一般来说，事务是必须满足4个条件（ACID）：

原子性（Atomicity，或称不可分割性）：要么全部完成，要么全部不完成。

一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。

隔离性（Isolation，又称独立性）：防止多个事务并发执行时由于交叉执行而导致数据的不一致。

持久性（Durability）：事务处理结束后，对数据的修改就是永久的。

2.事务实现

用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

直接用 SET 来改变 MySQL 的自动提交模式

SET AUTOCOMMIT=0 ：禁止自动提交

SET AUTOCOMMIT=1 ：开启自动提交

-- 使用set语句来改变自动提交模式
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION
-- 提交一个事务给数据库
COMMIT
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

3.事务测试示例

/*
需求：小明转张三转账500
*/
-- 创建数据库
CREATE TABLE `account` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL,
    `money` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`money`)
VALUES('小明',1000.00),('张三',1500.00);
-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点
UPDATE account SET money=money-500 WHERE `name`='小明';
UPDATE account SET money=money+500 WHERE `name`='张三';
COMMIT; -- 提交事务
--  rollback;
SET autocommit = 1; -- 恢复自动提交

六、索引

1.索引介绍

索引概念

索引相当于字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字

索引的优点

提高查询速度

可以加速表和表之间的连接 , 实现表与表之间的参照完整性

使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间

全文检索字段进行搜索优化

索引的缺点

会降低更新表的速度，对表进行INSERT、UPDATE和DELETE操作时，不仅要保存数据，还要保存一下索引文件

2.索引分类

主键索引 (Primary Key)

唯一索引 (Unique)

常规索引 (Index)

全文索引 (FullText)

3.主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

最常见的索引类型

确保数据记录的唯一性

确定特定数据记录在数据库中的位置

4.唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别：

主键索引只能有一个

唯一索引可能有多个

CREATE TABLE `Grade`(
`grade_id` INT(11) AUTO_INCREMENT PRIMARYKEY,
`grade_name` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE KEY `grade_id` (`grade_id`)
)

5.常规索引

作用 : 快速定位特定数据

注意 :

index 和 key 关键字都可以设置常规索引

应加在查询找条件的字段

不宜添加太多常规索引,影响数据的插入,删除和修改操作

-- 创建表时添加 
CREATE TABLEresult( ... INDEX/KEYind(student_no,student_no) ) 
-- 创建表后添加
ALTER TABLEresultADD INDEXind(student_no,student_no`);

6.全文索引

说明：

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；

MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

作用 : 快速定位特定数据

注意 :

只能用于MyISAM类型的数据表

只能用于CHAR , VARCHAR , TEXT数据列类型

适合大型数据集

示例：

ALTER TABLE `student` ADD FULLTEXT INDEX `student_name` (`student_name`);

7.删除索引

-- 删除索引：DROP INDEX 索引名 ON 表名字;
-- 删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;

8.查看表索引

-- 显示索引信息:
SHOW INDEX FROM student;

9.索引使用准则

索引不是越多越好

不要对经常变动的数据加索引

小数据量的表建议不要加索引

索引一般应加在查找条件的字段

10.索引的数据结构

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

关于索引的本质参考文章：MySQL索引背后的数据结构及算法原理

后记

本文下接：MySQL初级之【3.数据库用户管理、备份与设计】

Java全栈学习路线可参考：【Java全栈学习路线】最全的Java学习路线及知识清单，Java自学方向指引，内含最全Java全栈学习技术清单~

MySQL初级之【2.最全数据库相关操作，CRUD】（二）

三、使用DQL查询数据

1.DQL语言

2.指定查询字段

3.where条件语句

4.连接查询

5.排序和分页

6.子查询

四、MySQL函数

1.常用函数

2.聚合函数

3.分组函数

五、事务

1.事务概述

2.事务实现

3.事务测试示例

六、索引

1.索引介绍

2.索引分类

3.主键索引

4.唯一索引

5.常规索引

6.全文索引

7.删除索引

8.查看表索引

9.索引使用准则

10.索引的数据结构

后记

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL初级之【2.最全数据库相关操作，CRUD】（二）

三、使用DQL查询数据

1.DQL语言

2.指定查询字段

3.where条件语句

4.连接查询

5.排序和分页

6.子查询

四、MySQL函数

1.常用函数

2.聚合函数

3.分组函数

五、事务

1.事务概述

2.事务实现

3.事务测试示例

六、索引

1.索引介绍

2.索引分类

3.主键索引

4.唯一索引

5.常规索引

6.全文索引

7.删除索引

8.查看表索引

9.索引使用准则

10.索引的数据结构

后记

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像