2021-08-04

最新推荐文章于 2023-04-06 10:38:55 发布

JiangHuFengZi

最新推荐文章于 2023-04-06 10:38:55 发布

阅读量206

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/JiangHuFengZi/article/details/119393214

版权

DQL查询语句

分页和排序

排序

-- 排序：升序 ASC ,降序 DESC
-- order by 通过哪个字段排序，怎么排
-- 查询的结果根据，成绩降序，排序
SELECT s.studentNo,studentName,SubjectName,StudentResult -- 查询的四个字段
FROM student s
INSERT JOIN `result` r
ON s.studentno = r.studentno
INSERT JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE SubjectName = '数据结构'
ORDER BY StudentResult DESC -- 降序，升序就写 ASC 即可

分页

-- 为什么要分页？
-- 缓解数据库压力，给人的体验更好

-- 分页，每页只显示五条数据
-- 语法：limit 从第几条数据开始 ，该页面显示几条数据
SELECT s.studentNo,studentName,SubjectName,StudentResult -- 查询的四个字段
FROM student s
INSERT JOIN `result` r
ON s.studentno = r.studentno
INSERT JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE SubjectName = '数据结构'
ORDER BY StudentResult DESC
LIMIT 0,5

-- 第一页  limit 0,5 			   (1-1)*5	
-- 第二页  limit 5,5 			   (2-1)*5
-- 第三页  limit 10,5 			   (3-1)*5
-- 第N页   limit (n-1)*pageSize, pageSize  (n-1)*pageSize, pageSize  
-- [pageSize：页面是显示数据的条数]
-- [总页数 = 数据总数/页面显示大小]

-- 查询Java第一学年课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.studentNo,studentName,SubjectName,StudentResult -- 查询的四个字段
FROM student s
INSERT JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
-- 去除条件不符合的 
WHERE SubjectName = 'Java第一学年' AND StudentResult >80
-- 按成绩排下序
ORDER BY StudentResult DESC
-- 前十名
LIMIT 0,10

子查询

在 where 语句中嵌套一个子查询

where ( select * from )

-- =========== where 子查询 ============

-- 1.查询数据库结构 的所有考试结果（学号，科目编号，成绩），降序排列

-- 方式一：使用连接查询
SELECT `studentNo`,r.`SubjectNo`,`studentResult` -- 这三个字段都在result表中
FROM `result` r
LEFT JOIN `subject` s
ON s.`SubjectNo` = r.`SubjectNo`
WHERE subjectName = '数据库结构'
ORDER BY DESC

-- 方式二：使用子查询(由里及外)
SELECT `studentNo`,`SubjectNo`,`studentResult`
FROM `result` 
WHERE subjectNo = (
	SELECT subjectNo FROM `subject`
	WHERE subjectName = '数据库结构'
)

-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`studentNo`,`studentName`
FROM student s
INNER JOIN result r
ON r.studentNo = s.studentNo
WHERE studentResult >=80

-- 方式二：子查询
SELECT DISTINCT s.`studentNo`,`studentName`
FROM student
WHERE studentNo = (
	SELECT studentNo FROM result
	WHERE studentResult >=80 
)

-- 在这个基础上增加一个科目 ，高等数学（科目名称在 Subjet 表中）

-- 一：
SELECT DISTINCT s.`studentNo`,`studentName`
FROM student s
INNER JOIN result r
ON r.studentNo = s.studentNo
WHERE studentResult >=80 AND `subjectNo` = (
	SELECT subjectNo FROM `subject`
	WHERE `subjectName` = '高等数学'
)

-- 二：
SELECT DISTINCT s.`studentNo`,`studentName`
FROM student
WHERE studentNo = (
	SELECT studentNo FROM result -- 看题中条件是哪个表的内容(这里是studentResult 属于result表)，就要联接哪个表（result表），就查询这两个表公共内容（这里是studentNo）用做联接两个表
	WHERE studentResult >=80 AND subjectNo = (-- 在判断从这个表中查询公共内容的条件（studentResult >=80）
		SELECT subjectNo FROM `subject`
		WHERE subjectName = '高等数学'
	
	)
)



-- 合起来题目就是 查询课程为 高等数学 且分数不小于 80 的同学的学号和姓名

-- 用联接查询
SELECT  s.studentNo ,studentName
FROM student s
INNER JOIN result r
ON s.studentNo = r.studentNo
INNER JOIN `subject` sub
ON sub.subjectNo = r.subjectNo
WHERE subjectName = '高等数学' AND studentResult >=80

分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80的
-- 核心：（根据不同的课程分组）

SELECT  subjectName , AVG(`subjectResult`),MAX(subjectResult),MIN(subjectResult)
FROM `result` r
INNER JOIN `subject` s
ON r.subjetNo = s.subjetNo
GROUP BY r.subjetNo  -- 通过什么字段来分组
HAVING subjectResult>80 -- 平均分大于八十

Select 小结

在这里插入图片描述

MySQL函数

常用函数

-- ============ 常用函数 ==============

-- 数学运算
SELECT ABS(-5) -- 绝对值 结果为5
SELECT CEILING(9.4) -- 向上取整 结果为10
SELECT FLOOR(9.5)   -- 向下取整 结果为9
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(5) -- 判断一个数的符号  0返回0  负数返回-1 正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('sfdasdafsda')  -- 字符串长度 结果为11
SELECT CONCAT('我','和','你')  -- 拼接字符串
SELECT INSERT('我爱编程',1,2,'超级热爱') -- 插入，替换 （从某个位置替换开始替换某个长度），结果为：超级热爱编程
SELECT LOWER('WEFAFsdf') -- 小写字母 结果为：wefafsdf
SELECT UPPER('sdfihisda') -- 大写字母 结果为：SDFIHISDA
SELECT INSTR('sdfahsdsfjijhgo','f') -- 返回字符串i第一次出现的索引 结果为11
SELECT REPLACE('坚持就能成功','坚持','努力') -- 将指定的字符串改为指定的字符串  结果为 努力就能成功
SELECT SUBSTR('坚持就能成功',4,6)  -- 返回指定的子字符串（源字符串，截取的位置，截取的长度） 从指定的索引开始到指定的位置结束 结果为：能成功
SELECT REVERSE('sadfsdafsaihfd')  -- 反转字符串

-- 查询姓王的同学，将姓改为魏
SELECT `id`,REPLACE(`name`,'王','魏') FROM student
WHERE `name` LIKE '王%'


-- 时间和日期函数 （记住） 
SELECT CURRENT_DATE() -- 获取当前日期 结果为：2021-08-04
SELECT CURDATE() -- 获取当前日期	
SELECT NOW() -- 获取当前时间 2021-08-04 10：56：48
SELECT LOCALTIME() -- 本地时间 2021-08-04 10：57：41
SELECT SYSDATE() -- 系统时间 2021-08-04 10：58：38

SELECT YEAR(NOW()) -- 获取时间中的年份
SELECT MONTH(NOW()) -- 月份
SELECT DAY(NOW())   -- 天数
SELECT HOUR(NOW())  -- 小时数
SELECT MINUTE(NOW()) -- 分钟
SELECT SECOND(NOW()) -- 秒数

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

聚合函数（常用）

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
…	…

-- ========== 聚合函数 ============
-- 都能够统计表中的数据有多少条
-- 想查询一个表中有多少条记录，就使用 count 即可
SELECT COUNT(`email`) FROM student -- count(指定列)，会忽略所有的 null 值

SELECT COUNT(*) FROM student  -- count（*） 不会忽略 null 值 ，本质 计算行数
SELECT COUNT(1) FROM student  -- count（1） 不会忽略 null 值 ，本质 计算行数

SELECT SUM(`gradeid`) AS 总和 FROM student  -- 求和
SELECT AVG(`gradeid`) AS 总和 FROM student  -- 求平均分
SELECT MAX(`gradeid`) AS 总和 FROM student  -- 求最高分
SELECT MIN(`gradeid`) AS 总和 FROM student  -- 求最低分

数据库级别的MD5加密（拓展)

MD5主要增强算法复杂度和不可逆性。
MD5不可逆，但具体的MD5的值是一样的

-- ======= 测试MD5 加密 ========

CREATE TABLE `testmd5`(
   `id` INT(4) NOT NULL,
   `name` VARCHAR(20) NOT NULL,
   `pwd` VARCHAR(50) NOT NULL,
   PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO testmd5 
VALUE(1,'zhangsan','123456'),
(2,'shaan','1234'),
(3,'ghanan','136456'),
(4,'hzhagsan','12465456'),
(5,'zcvnangsan','124556'),
(6,'zhngcxvsn','12346'),
(7,'zngsncds','13456'),
(8,'dshangan','12346'),
(9,'dshnsa','12356'),
(10,'zdhngsan','1223456'),
(11,'zagdfsan','1234456'),
(12,'zhanssdfhn','12863456'),
(13,'zhagafgdn','123678456'),
(14,'zagssyren','12348756')

-- 加密
UPDATE testmd5 SET pwd = MD5(pwd) WHERE id = 1; -- 加密指定
UPDATE testmd5 SET pwd = MD5(pwd) -- 加密全部

-- 在执行插入的时候就加密
INSERT INTO testmd5 
VALUE(15,'lisi',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name` = 'lisi' AND pwd = MD5('123456')

事物

什么是事务

要么都成功，要么都失败

==================

1. SQL执行 A  给 B 转账    A 最开始有1000  A 转200 给 B   B  最开始有 200
2. SQL执行 B 收到 A 的钱  A  要变成 800  B  要变成 400

==================

将一组SQL 放在一个批次中去执行，（即让它们，要么都成功，要么都失败，不能一条成功，一条失败，）

INNODB 是支持事务的

事务原则：ACID原则原子性，一致性，隔离性，持久性（脏读，幻读…）

原子性（Atomicity）

要么都成功，要么都失败。

一致性（Consistency）

事务前后的数据完整性要保证一致。

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其它事务的操作数据所干扰，事务之间要隔离。

持久性（Durability）

事务一旦提交则不可逆，被持久化到数据库中！

隔离所导致的一些问题

脏读

值一个事务读取了另外一个事务未提交的数据。

不可重复读

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读（幻读）

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

执行事务

-- =============== 事务 =================

-- MySQL 是默认开启事务自动提交的
 SET autocomimt = 0 -- 关闭
 
 SET autocomimt = 1 -- 开启

-- === 事务执行的完整过程 ===

-- 手动处理事务
 SET autocomimt = 0 -- 关闭自动提交


-- 事务开始
START TRANSACTION -- 标记一个事务的开始，从这个之后的SQL，都在同一个事务内



COMMIT -- 提交：持久化 （执行成功就提交）

ROLLBACK -- 回滚：回到原来的样子 （执行失败就回滚）


-- 事务结束
SET autocomimt = 1 -- 开启自动提交


-- 了解即可
SAVEPOINT 保存点名 -- 保存点  设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

案例:

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci

USE shop

CREATE TABLE `account` (
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL,
    `money` DECIMAL(9,2) NOT NULL, -- 钱定义时就用 decimal类型
    PRIMARY KEY (id)
)ENGINE = INNODB DEFAULT CHARSET =utf8

DROP TABLE account

INSERT INTO `account`(`name`,`money`)
VALUES('A',2000.0),('B',10000.0)


-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务 （一组事务）
-- 下面两条语句就是一组事务
UPDATE account SET money = money - 500  WHERE `name` = 'A' -- A减五百
UPDATE account SET money = money + 500  WHERE `name` = 'B' -- A减五百

COMMIT; -- 提交事务，事务一旦提交就被持久化了，回滚无效
ROLLBACK; -- 回滚

SET autocommit = 1; -- 恢复默认值

索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（UNIQE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引（KEY / INDEX）
- 默认的，可以用 index ，或key 关键字来设置
全文索引（FullText）
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据

基础：

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 增加一个索引 （索引名） 列名
ALTER TABLE school.`student` ADD FULLTEXT INDEX `name` (`name`)

-- EXPLAIN 分析sql执行的状况

EXPLAIN SELECT * FROM student -- 常规索引，非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`name`) AGAINST ('王六')

测试索引

-- 测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT'用户昵称',
`eamil` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL DEFAULT '123456' COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表';


-- 插入100万数据
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
BEGIN
 DECLARE num INT DEFAULT 10000000;
 DECLARE i INT DEFAULT 0;
 WHILE i < num DO
  INSERT INTO app_user(`name`,`email`app_user``,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
  SET i = i + 1;
 END WHILE;
 RETURN i;
END $$

SELECT mock_data();

SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.923sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.875sec

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

-- id _表名_字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

-- 添加索引后 查数据
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.001sec

没加索引前：

在这里插入图片描述

加了索引后：

在这里插入图片描述

总结：

索引不是越多越好（至少你得 500万条数据后才考虑加索引）
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上。

JiangHuFengZi

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2021-08-04

DQL查询语句分页和排序排序-- 排序：升序 ASC ,降序 DESC-- order by 通过哪个字段排序，怎么排-- 查询的结果根据，成绩降序，排序SELECT s.studentNo,studentName,SubjectName,StudentResult -- 查询的四个字段FROM student sINSERT JOIN `result` rON s.studentno = r.studentnoINSERT JOIN `subject` subON r.subject
复制链接

扫一扫