文章目录
1. DQL查询数据(重点)
1.1 DQL
(Data Query Language:数据查询语言)
1.2 指定查询字段 SELECT(简单查询)
- select 去重 要查询的字段 from 表(注意:表和字段可以取别名) xxx
- join 要连接的表 on 等值判断
- where(具体的值,子查询语句) 只能有一个,可用and连接
- Group By(通过哪个字段来分组)
- Having(过滤分组后的信息,条件和where是一样的,位置不同) order By(通过那个字段排序)【升序/降序】 limit
- startindex,pagesize
业务层面:
查询,跨表,跨数据库
'查询全部的学生 select 字段 from 表'
SELECT * FROM `student`
'-- 查询指定字段'
SELECT `studentno`,`studentname` FROM `student`
'-- 别名,给结果起一个名字 AS ,可以给字段起别名,也可以给表起别名'
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM `student` AS s
'-- 函数 Concat(a,b)连接字符串'
SELECT CONCAT('姓名:',`studentname`) AS 新名字 FROM `student`
– 语法:SELECT 字段。。。 FROM student
- 有的时候,列名字不是那么见名知意。起别名 AS 字段名 as 别名 表名 as 别名
- 别名
- 函数
1.3 去重 DISTINCT
- 作用:去除SELECT 查询出来的结果中重复的数据,重复的数据只显示一条
'-- 查询全部的考试成绩'
SELECT * FROM `result`
'-- 查询有哪些同学参加了考试'
SELECT `studentno` FROM `result`
'-- 去重'
SELECT DISTINCT `studentno` FROM `result`
1.4 数据库的列(表达式)
'-- 查询系统版本'
SELECT VERSION()
'-- 计算结果'
SELECT 100*3-1 AS 计算结果
'-- 查询自增的步长(变量)' -- 增长的单位
SELECT @@auto_increment_increment
'-- 学员考试成绩 +1查看'
SELECT `studentno`,`studentresult`+1 AS '提分' FROM `result`
- 数据库中的表达式:文本值,列,函数,计算表达式,系统变量…
- 如 100*3-1 是计算表达上
select 表达式 from 表
1.5 where 条件字句
- 作用:检索数据中心符合条件的值
- 搜索的条件由一个或者多个表达式组成! 结果 布尔值
- 逻辑运算符
and &&
or ||
not !
-- and
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=95 AND`studentresult`<=100
-- 模糊查询(区间)
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 95 AND 100
-- c除了1000号学生以外的同学
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000
- 模糊查询
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | A IS NULL | 如果操作符为null,结果为真 |
IS NOT NULL | A IS NOT NULL | 如果操作符不为null,结果为真 |
BETWEEN | A BETWEEN B AND C | 若a在b和c之间,结果为真 |
LIKE | A LIKE B | SQL匹配,如果A匹配B,则结果为真%若干字符,_一个字符 |
IN | A IN(A1,A2,A3…) | 假设a在a1,或者a2.。其中一个值中,结果为真 |
-- 模糊查询(区间)
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 95 AND 100
-- c除了1000号学生以外的同学
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000
-- like 结合 %(代表0到任意个字符) ——(代表只有一个字符)
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%强'
-- ====== in ====
-- 查询1001,1002,1003 的学员
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1001,1002,1003)
-- ========= null not null =========
查询地址为空的学生 NULL ' '
SELECT `studentno`,`studentname` FROM `student`
WHERE address='' OR address IS NULL
-- ====== 查询有出生日期的同学 不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL
1.6 联表查询
- JOIN 对比
- 例1:
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE s.`studentno`=r.`subjectno`
2. 例2:
-- Right join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno`=r.`studentno`
- 显示所有result匹配的值
- 例3:
-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno`=r.`studentno`
- 显示坐标中所有匹配的值
-
例4:
-
例5:
-
例6
操作 | 描述 |
---|---|
Inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使右表中没有匹配 |
- join(连接的表) on(判断条件) 连接查询
- where 等值查询
-- =
-- 多表查询
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN `result` r -- 参加了考试的
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
'-- 我要查询哪些数据 select 。。。
-- 从那几个表中查 FROM 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加·'
1.7 自连接
- 自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
'查询父子信息,把一张表看成两个一模一样的表'
SELECT a.`categoryname` AS '父栏目', b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`
1.8 分页和排序
- 排序 :升序ASC 降序 DESC
- 排序:环节数据库压力,给用户更好的体验,瀑布流
- 语法:
order by
通过哪个字段排序,怎么排
SELECT `studentresult`
FROM `result`
ORDER BY `studentresult` ASC
- 瀑布流:百度图片,图片拉不到底,不断加载
- 语法:limit(查询起始下标,pageSize)
-- 分页,每页只显示五条内容
-- 语法:limit 当前页,页面的大小
SELECT `studentresult`
FROM `result`
ORDER BY `studentresult` ASC
LIMIT 1,5
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 (3-1)*5
-- 第N页 limit 0,5 (n-1)*pageSize,
-- [pageSize:页面大小]
-- [(n-1)*pageSize:起始值]
-- [n:当前页]
-- 【数据总数/页面大小 = 总页数】
例子
1.9 子查询
where(值固定,这个值是计算出来的)
- 本质:在where语句中嵌套一个子查询语句
-- 1.查询 数据库结构-1 的所有考试结果 (学号,科目编号,成绩),降序排列
-- 方式一:使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname`='C语言-1'
ORDER BY `studentresult` DESC
-- 方式二:使用子查询(由里到外)
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = 'C语言-1'
)
ORDER BY `studentresult` DESC
例子:
2.0 函数
2.1 常用函数
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0-1的整数
SELECT SIGN(10) -- 判断一个参数的符号 (正数1,负数-1,0 0)
-- 字符串函数
SELECT CHAR_LENGTH ('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT ('我','爱','你') -- 拼接字符串
SELECT INSERT ('我爱编程hello word',1,2,'超级热爱') -- 从某个位置替换某个长度
SELECT LOWER('I Love You') -- 小写字母
SELECT UPPER('I Love You') -- 大写字母
SELECT INSTR('kuangshen','h') -- 返回第一次出现索引的位置
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的字符
SELECT SUBSTR('坚持就能成功',4,6) -- 返回指定的字符串
SELECT REVERSE('坚持就能成功') -- 反转字符串
-- 时间和日期函数(记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW()) -- 月日时分秒
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系统
SELECT SYSTEM_USER() -- 用户
SELECT VERSION() -- 版本
2.1 聚合函数
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
… |
2.2 分组过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:根据不同的课程分组
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING 平均分>70
2.3 数据库级别的MD5加密
什么是MD5?
主要增强算法复杂度和不可逆性 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` VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
-- 加密
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id = 1
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id = 2
-- 插入时加密
INSERT INTO `testmd5` VALUES(4,'xiaoming',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行MD5加密,然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name` = 'xiaoming' AND pwd = MD5('123456')
3.0 事务
3.1 什么是事务
将一组sql放在一个批次中执行
- 事务原则:ACID 原则 原子性,一致性,隔离性,持久性 (脏读,幻读…)
- 原子性:要么都成功,要么都失败
- 一致性:事务前后都保持一致,1000
- 持久性:事务一旦提交则不可逆,被持久化到数据库中
- 隔离性:多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他操作事务的操作数据所干扰的事务之间要像雾隔离 事务的隔离级别
- 脏读:一个事务读取了另一个事务未提交的数据
- 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同(这个不一定是错误,只是某些场合不对)
- 虚读(幻读):是指一个事务内读取了别的事务插入的数据,导致前后读取的不一致
-- ===========事务 ===========
-- mysql 是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启(默认) */
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内
INSERT xx
INSERT xx
-- 提交: 持久化(成功!)
COMMIT
-- 回滚:回到原来的样子(失败!)
ROLLBACK
-- 事务结束
SET autocommit = 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,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account(`name`,`money`)
VALUES ('A',2000.0),('B', 10000.00)
-- 模拟转账:事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务(一组事务)
UPDATE account SET money=money-500 WHERE `name`='A' -- A-500
UPDATE account SET money=money+500 WHERE `name`='B' -- B+500
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET TRANSACTION = 1; -- 恢复默认值
当未提交事务时,回滚,恢复原来的值
提交事务后,再回滚则不再变化
4.0 索引
1.0
- 主键索引:(primary key) 唯一的标识,主键不可重复,只能有一个列作为主键
- 唯一索引:(unique key)避免重复的列出现,唯一索引可以重复,多个列都可以表示位 唯一索引
- 常规索引:(key/index)默认的,index,key 关键字来设置
- 全文索引:(fullText)再特定的数据库引擎下才有,MyISAM,快速定位数据
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后,增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引(索引名) 列名
ALTER TABLE school.student add FULLTEXT INDEX `studentName`(`studentName`)
-- EXPLAIN 分析sql执行的情况
EXPLAIN select * from student; -- 非全文索引
EXPLAIN select * from student where match(studentName) against('刘');
- 看懂explain网址
1.2 测试索引
DELIMITER $$
-- 写函数之前必须要写,标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT(用户,i),19224305@qq.com,123456789,FLOOR(RAND()*2));
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data() -- 执行此函数 生成一百万条数据
SELECT * FROM app_user WHERE `name` = '用户999'; -- 0.993 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户999';
-- id_表明_字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);-- 创建一个常规索引
SELECT * FROM app_user WHERE `name` = '用户999'; -- 0.001sec
explain select * from app_user where `name`='用户999';
在这里插入图片描述
1.3 索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
- 索引的数据结构:详细
Hash类型的索引
Btree:innoDb的默认数据结构
5. SQL练习
借鉴:https://blog.csdn.net/mrbcy/article/details/68965271