[大数据]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条件子句

作用：检索数据中符合条件的值

逻辑运算符

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;

模糊查询：比较运算符

?-- ================== 模糊查询 ===========================
?-- 查询姓刘的同学
?-- 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;

自连接

自己的表和自己的表连接，核心：一张表拆成两张一样的表即可

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()）
?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');