mysql学习笔记（四）

4.1 DQL查询数据（最重点）

4.1.1 DQL

（Data Query LANGUACE:数据查询语言）

• 所有的查询语言都用它 select

• 简单的查询、负载的查询都能做

• 数据库最核心的语言

• 使用频率最高

4.1.2 指定查询字段

select

-- 查询全部的学生 
SELECT * FROM student

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
-- 别名，给结果加一个名字(AS)   可以给字段取别名，也可以给表起别名
SELECT `studentNO` AS 学号,`StudentName` AS 学生姓名 FROM student

-- 函数Concat（a,b）
SELECT CONCAT ('姓名:',StudentName) AS 新名字 FROM student 

一般格式：**SELECT 字段,… FROM 表 **

有的时候，列名字不是那么的见名知意，我们起别名 AS 字段名 as 别名 表名as别名

distinct

作用：去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条

SELECT *FROM result  -- 查询全部的考试成绩
SELECT `studentno` FROM result -- 查询有哪些同学参加了考试
-- 发现重复数据，去重
SELECT DISTINCT `studentno` FROM result

数据库的列（表达式）

SELECT *FROM result  -- 查询全部的考试成绩
SELECT `studentno` FROM result -- 查询有哪些同学参加了考试
-- 发现重复数据，去重
SELECT DISTINCT `studentno` FROM result

SELECT VERSION() -- 查询系统版本（函数）
SELECT 100*3-9 AS 计算结果  -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）
SELECT `studentno`,`studentresult` +1 AS '提分后' FROM result -- 学员成绩+1后查看

数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量······

select 表达式 from 表

4.1.3 where条件子句

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

搜索的条件由一个或多个表达式组成！结果为布尔值

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个都为真，结果为真
or ||	a or b a||b	逻辑或，其中一个为真，则为真
Not !	not a !a	逻辑非,真为假，假为真 ！

尽量使用英文字母

SELECT studentno,`studentresult` FROM result

-- 查询考试成绩在90-100分之间
SELECT studentno,`studentresult` FROM result
WHERE studentresult >=90 AND studentresult<=100

-- and &&
SELECT studentno,`studentresult` FROM result
WHERE studentresult >=90 && studentresult<=100

-- 模糊查询（区间）
SELECT studentno,`studentresult` FROM result
WHERE studentresult BETWEEN 90 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----------------

-- 查询姓刘的同学 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---------------

-- 查询地址为空的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE  address='' OR address IS NULL

-- 查询有出生日期的同学   不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL

4.1.4 联表查询

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT *FROM student
SELECT *FROM result

/*思路：
1、分析需求，分析查询的字段来自那些表（连接查询）
2、确定使用哪种连接查询
    确定交叉点（两张表哪个数据是相同的）
判断的条件：学生表的studentno 和成绩表studentno
*/

-- join（连接的表） on （判断的条件）连接查询
-- where 等值查询

-- inner join
SELECT s.studentno,studentname ,subjectno ,studentresult
FROM student  AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno

-- right join 
SELECT s.studentno,studentname ,subjectno ,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno

-- left join 
SELECT s.studentno,studentname ,subjectno ,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno

-- 查询缺考的同学
SELECT s.studentno,studentname ,subjectno ,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

-- 查询参加考试的同学信息：学号，学生姓名，科目名，分数
 SELECT s.studentno,studentname,subjectname,studentresult
 FROM student AS s
 RIGHT JOIN result AS r
 ON r.studentno = s.studentno
 INNER JOIN `subject` AS sub
 ON r.subjectno = sub.subjectno
 
 -- 我要查询哪些数据 select...
 -- 从那几个表中查 from 表 xxx join 连接的表 on 交叉条件
 -- 假设存在一种多张表查询，先查询两张表在慢慢增加
 
 -- from a left join b   以a为基准
 -- from a right join b  以b为基准

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

自连接

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

CREATE TABLE `school`.`category`( 
   `categoryid` INT(3) NOT NULL COMMENT 'id', 
   `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
   `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
   PRIMARY KEY (`categoryid`)
    ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
    
    INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('3', '1', '软件开发');
INSERT INTO `school`.`category` (`categoryid`, `PId`, `categoryname`) VALUES ('5', '1', '美术设计');
INSERT INTO `School`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('4', '3', '数据库'); 
INSERT INTO `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) VALUES ('8', '2', '办公信息');
INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('6', '3', 'web开发'); 
INSERT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('7', '5', 'ps技术');

父类：

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类：

pid	categoryid	categoryname
3	4	数据库
3	6	web开发
5	7	ps技术
2	8	办公信息

操作：查询父类对应子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息:
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

-- 查询学员所属的年级（学号，学生姓名，年纪名称）
SELECT `studentno`,`studentname`,`gradename` 
FROM `student` s
INNER JOIN `grade` g
ON s.`gradeid`=g.`gradeid`

-- 查询科目所属的年级(科目名称，年级名称)
SELECT `subjectname`,`gradename`
FROM `subject` sub
INNER JOIN  `grade` g
ON sub.`gradeid`= g.`gradeid`

-- 查询参加 数据库结构-1 考试的同学信息：学号，学生姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`= r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`= sub.`subjectno`
WHERE `subjectname`='数据库结构-1 '

4.1.5 排序和分页

排序

-- 升序（ASC） 降序（DESC）
-- ORDER BY 通过那个字段排序，怎么排
-- 查询的结果根据成绩降序排序

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`= r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`= sub.`subjectno`
WHERE `subjectname`='数据库结构-1 '
ORDER BY studentresult DESC

分页

-- 为什么要分页：可以缓解数据库压力，给人的体验更好，
-- 分页，每页只显示五条数据
-- 语法：limit 起始值，页面的大小
-- 网页应用：当前页，总的页数，页面的大小
-- limit 0,5      1-5
-- limit 1,5      2-6
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`= r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`= sub.`subjectno`
WHERE `subjectname`='数据库结构-1 '
ORDER BY studentresult DESC
LIMIT  0,5

-- 第一页 ：limit 0,5       (1-1)*5
-- 第二页： limit 5,5       (2-1)*5
-- ·····
-- 第n页：limit 0,5        (n-1)*pagesize,pagesize
-- 【pagesize 页面大小，】
-- 【n当前页】
-- 【（n-1）*pagesize 起始值】
-- 【数据总数/页面大小=总页数】

-- 查询 java第一学年 课程成绩前十名并且分数大于80的学生信息（学号，姓名，课程名，分数）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER 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

一般格式：limit(查询起始下标,pagesize)

4.1.6 子查询

where （这个值是计算出来的）

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

where（select *from）

-- 查询课程为 高等数学-2 且分数不小于80 的同学的学号和姓名
SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-2 ' AND `studentresult`>=80

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

-- 在此基础上，添加一个科目：高等数学-2
SELECT  DISTINCT s.`studentno`,`studentname` 
FROM `student` s
INNER JOIN `result` r
ON s.studentno=r.studentno
WHERE `studentresult`>=80 AND `subjectno`=(
	SELECT `subjectno` FROM `subject`
	WHERE `subjectname`='高等数学-2'
)

-- 改造
SELECT `studentno`,`studentname`FROM `student` WHERE  `studentno`IN(
	SELECT `studentno` FROM `result` WHERE`studentresult`>=80 AND `subjectno`=(
		SELECT `subjectno` FROM`subject`WHERE `subjectname`='高等数学-2'
  )
)

4.1.7 分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：根据不同的课程分组
SELECT `subjectname`,AVG(`studentresult`)AS 平均分,MAX(`studentresult`),MIN(`studentresult`)
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`= sub.`subjectno`
GROUP BY r.`subjectno`-- 通过什么字段来分组
HAVING 平均分>80

4.1.8 select 小结

顺序很重要

select 去重 要查询的字段 from 表（注意：表和字段可以取别名）

xxx join 要连接的表 on 等值判断

where （ 具体的值 子查询语句）

group by （通过哪个字段来分组）

having （过滤分组后的信息，作用和where一样的，位置不同）

order by （通过哪个字段排序【升序降序】）

limit startindex pagesize

4.2 MySQL函数

4.2.1 常用函数

-- 数学运算
SELECT ABS(-8)  -- 绝对值
SELECT CEILING (9.4) -- 向上取整
SELECT FLOOR (9.4)  -- 向下取整
SELECT RAND () -- 返回一个0-1 之间的随机数
SELECT SIGN ()   -- 判断一个数的符号  0返回0，正数返回1，负数返回-1

-- 字符串函数
SELECT CHAR_LENGTH ('坚持就能胜利')  -- 字符串长度
SELECT CONCAT ('我','爱','吃','饭'); -- 拼接字符串
SELECT INSERT('我爱编程',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER ('HelloWorld') -- 小写
SELECT UPPER ('HelloWorld') -- 大写
SELECT INSTR ('HelloWorld','e') -- 返回第一次出现的子串的索引
SELECT REPLACE('坚持就能胜利','坚持','努力') -- 替换字符串
SELECT SUBSTR('坚持就能胜利',4,5) -- 返回固定的子字符串（源字符串，获取的位置，截取的长度）
SELECT REVERSE ('坚持就能胜利')-- 反转

-- 查询姓周的同学,改成邹 
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE `studentname` LIKE '周%'

-- 时间和日期函数（记住）
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 USER()
SELECT VERSION()

4.2.2 聚合函数（常用）

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

-- 都能统计表中的数据（想查询一个表中有多少个记录，就使用count（））
SELECT COUNT(`studentname`) FROM `student` -- Count（指定列），会忽略null值
SELECT COUNT(*) FROM `student`  -- count（*），不会忽略null值，本质：计算行数
SELECT COUNT(1) FROM `result`-- count（1）不会忽略所有的null值


SELECT SUM(`studentresult`) AS 总和 FROM `result`
SELECT AVG(`studentresult`) AS 平均分 FROM `result`
SELECT MAX(`studentresult`) AS 最高分 FROM `result`
SELECT MIN(`studentresult`) AS 最低分 FROM `result`

4.2.3 数据库级别的MD5加密（扩展）

简介：

主要增强算法复杂度和不可逆性。

MD5不可逆，具体的值的MD5是一样的

MD5**网站的原理，背后有一个字典，MD5加密后的值，加密前的值

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

-- 明文密码
INSERT INTO testmd51 VALUES (1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
-- 加密
UPDATE testmd51 SET pwd=MD5(pwd) ;-- 加密全部的密码
UPDATE testmd51 SET pwd=MD5(pwd) WHERE id=1

-- 插入的时候加密
INSERT INTO testmd51 VALUES (4,'zhouliu',MD5('123456'))

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