1、DQL查询数据库(重点)
1.1 DQL
(Data QUery LANGUAGE:数据查询语言)
- 所有的查询操作都用它
- 简单的查询,复杂的查询它都能做
- 数据库中最核心的语言
- 使用频率最高的语句
1.2 指定查询字段
-- 查询全部的学生 SELECT 字段 FROM 表
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名,给结果取一个名字 AS 可以给字段起别名,也可以给表取别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s
-- 函数 Concat(a,b),查询结果为 姓名:XXX
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
去重 distinct
作用:去除SELECT查询出来的结果中重复的数据,重复的数据只显示一条
-- 查询有哪些同学参加考试
SELECT * FROM result; -- 查询全部的考试成绩
SELECT `StudentNo` FROM result; -- 查询有哪些同学参加了考试
SELECT DISTINCT `studentNo` FROM result; -- 去除重复数据
数据库的列(表达式)
SELECT VERSION() -- 查询系统版本(函数)
SELECT 100*3-100 AS 计算结果; -- 用来计算(表达式)
SELECT @@auto_increment_increment; -- 查询自增的步长
-- 学员考试成绩+1分查看
SELECT `StudentNO`,`StudentResult`+1 AS '加分后' FROM result;
1.3 Where条件子句
作用:检索数据中符合条件的值
逻辑运算符
运算符 | 语法 | 描述 |
and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
or || | a or b a||b | 逻辑或,其中一个为真,则结果为真 |
Not ! | not a ! a | 逻辑非,真为假,假为真! |
MySQL中尽量使用英文字母
-- ======================== where ==================================
SELECT `StudentNo`,`StudentResult` FROM result;
-- 查询考试成绩在95~100之间的学生
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
-- 模糊查询(区间)
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult` BETWEEN 95 AND 100;
-- 除了1000号学生之外的同学的成绩
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNO`!=1000;
-- NOT
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `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....其中的某一个值中,结果为真 |
-- ================== 模糊查询 ===========================
-- 查询姓刘的同学
-- like结合 %(代表0到任意个字符) _(一个字符)
SELECT `StudentNO`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%';
-- 查询姓刘的同学,名字后面只有一个字的
SELECT `StudentNO`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘_';
-- 查询姓刘的同学,名字后面只有两个字的
SELECT `StudentNO`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘__';
-- 查询名字中间有嘉字的同学
SELECT `StudentNO`,`StudentName` FROM `student`
WHERE StudentName LIKE '%嘉%';
-- ======= in(具体的一个或者多个值) ========
-- 查询 1001,1002,1003号学员
SELECT `StudentNO`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- 查询在北京的学生
SELECT `StudentNO`,`StudentName` FROM `student`
WHERE `Address` IN ('安徽','河南洛阳');
-- ====== 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;
-- 查询没有出生日期的同学
SELECT `StudentNO`,`StudentName` FROM `student`
WHERE `BornDate` IS NULL;
1.4联表查询
- ================= 联表查询 join =================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result
/*思路
1.分析需求,分析查询的字段来自那些表,(连接查询)
2.确定使用那种连接查询
确定交叉点(这两个表中那些数据是相同的)
判断的条件:学生表中 studentNO = 成绩表中 studentNo
*/
SELECT s.StudengNO,StudentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.StudentNO = r.StudentNO;
-- Right Join
SELECT s.StudengNO,StudentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.StudentNO = r.StudentNO;
-- Left Join
SELECT s.StudengNO,StudentName,SubjectNo,StudentResult
FROM student AS s
Left JOIN result AS r
ON s.StudentNO = r.StudentNO;
操作 | 描述 |
Inner join | 如果表中有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
自连接
自己的表和自己的表连接,核心:一张表拆成两张一样的表即可
1.5分页和排序
排序
- 排序:升序 ASC , 降序 DESC
- ORDER BY 通过哪个字段排序,怎么排
- 语法 查询的结果 ORDER BY 需要进行排序的字段 ASC/DESC
分页
- 分页作用:缓解数据库的压力,增强用户体验
- 语法:Limit 起始值, 页面的大小 -- 第一页 limit 0,5 (1-1)*5 -- 第二页 limit 5,5 (2-1)*5 -- 第三页 limit 10,5 (3-1)*5 -- 第n页 limit (n-1)*PageSize,PageSize
4.6子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
where(select * from......)
4.7分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于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 平均分>80
2.函数
2.1聚合函数
函数名称 | 描述 |
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
...... | ...... |
-- ===========聚合函数=========
-- 都能够统计表中的数据(想查询一个表中有多少个数据,就使用COUNT())
SELECT COUNT(`BornDate`) FROM student; -- 会忽略所有的null值
SELECT COUNT(`BornDate`) FROM student; -- 不会忽略null值,本质计算行数
SELECT COUNT(`BornDate`) FROM student; -- 不会忽略null值,本质计算行数
5.3数据库级别的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,'张三','123456789'),(2,'李四','123456789'),(3,'王五','123456789')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1;
UPDATE testmd5 SET pwd=MD5(pwd);
-- 插入时加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('1234567'));
-- 如何校验:将用户传递进来的密码,进行MD5加密,然后比对加密后的值
SELECT * FROM testmd5 WHERE `name` = '小明' AND pwd = MD5('1234567');