MySQL查询和函数

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');