【数据库】MySQL（3）

DQL查询数据（重点）

查询格式

Date Query Lauguage:数据查询语言
完整的select语法：

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.filed2[as alias2]][,...]]}
FROM table_name [as table_alias]
	[left | right |inner join table_name2] -- 联合查询
	[WHERE ...] -- 指定结果需满足的条件
	[GROUP BY ...] -- 指定结果按照哪几个字段来分组
	[HAVING] -- 过滤分组的记录必须满足的次要条件
	[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
	[LIMIT{[offset,]row_count | row_countOFFSET offset}]; -- 指定查询到记录从哪条到哪条

注意：[ ] 代表可选的，{ }代表必选的。

---

常见用法

去重 distinct

-- 查询哪些同学参加了考试
SELECT DISTINCT `studentno` FROM result;

版本查询

-- 查询版本
SELECT VERSION();

---

where条件子句

检索数据库中符合条件的内容

-- 查询70~89的学生成绩
SELECT `studentno`,`studentresult` FROM result WHERE `studentresult`>=70 AND `studentresult`<90;

模糊查询

运算符	描述
IS NULL	操作数值为空
IS NOT NULL	操作数值为非空
BETWEEN a AND b	在a、b之间
a LIKE b	a匹配b
a IN (b,c···)	a在(b,c···)中

-- 查询姓张的同学
SELECT `studentno`,`studentname` FROM `student` WHERE studentname LIKE '张%';

上述查询语句中 % 是通配符，表示任意个数的任意字符。

匹配符号	解释
%	任意个数通配符
_	一个通配符

联表查询

join对比

inner join 解决问题：

-- 查询所有学生的学号、姓名、科目、成绩
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` 
FROM `student` AS s
INNER JOIN `result` AS r
ON 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`
WHERE `studentresult` IS NOT NULL;

left join解决问题

-- 查询所有学生的学号、姓名、科目、成绩
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno` = r.`studentno`;

on用来连接两个表，where用来限制条件。暂且这么粗浅的理解。

操作	描述
inner join	如果两个表中都匹配，就返回行
left join	只要左表有的匹配，就返回行
right join	只要右表有的匹配，就返回行

---

自连接

一张表自己和自己连接
比如：
父类表

id	内容
2	信息技术
3	软件开发
5	美术设计

子类表

父id	id	内容
2	8	办公信息
3	6	web开发
3	4	数据库
5	7	ps技术

-- 查询父子信息
-- categoryname是内容，categoryid是id，pid是父id
SELECT f.`categoryname` AS 'fname',s.`categoryname` AS 'sname'
FROM `category` AS f,`category` AS s
WHERE f.`categoryid` = s.`pid`;

---

分页和排序

排序： 关键字是 ORDER BY
ASC 是升序，DESC 是倒序

分页： 1.缓解数据库压力 2.提升用户体验
关键字是LIMIT ，跟两个参数，第一个参数是从第几个数据开始显示（从0开始数），第二个参数是一页有多少条数据

---

子查询

本质：在where语句中嵌套一个查询语句，就是将一个查询的结果作为另一个查询的条件。
举个例子：

-- 查询考试名单里的所有学生的名字和身份证号
SELECT `studentname`,`identitycard`
FROM `student`
WHERE `studentno` IN (
	SELECT `studentno` 
	FROM `result`
);

分组过滤

分组的关键字是 GROUP BY ，按组查询
过滤的关键字是 HAVING ，对分组后的结果再次过滤

---

MySQL函数

常用函数

官网提供的函数表

放点特别常用的：

数学运算

方法名	用途
abs()	绝对值
ceiling(num)	向上取整
floor(num)	向下取整
rand()	返回一个0~1的随机数
sign(num)	判断符号，正1，负-1

字符串

方法名	用途
char_length(ch)	字符串长度
concat(ch1,ch2)	合并字符串
insert(ch1,num1,num2,ch2)	ch1的第num1个字符开始，往后数num2个字符，ch2替换
lower(ch)	全小写
upper(ch)	全大写
instr(ch1,ch2)	在ch1里找ch2第一个字符第一次出现的位置
replace(ch1,ch2,ch3)	ch1里的ch2用ch3换
substr(ch,num1,num2)/substring(ch,num1,num2)	ch里的第num1个字符开始，往后截取num2个
reverse(ch)	字符串反转

时间函数

方法名	用途
current_date()/curdate()	获取当前日期
now()	获取当前时间
localtime()	获取本地时间
sysdate()	系统时间

系统函数

方法名	用途
system_user()/user()	数据库当前使用者
version()	获取当前版本

聚合函数

方法名	用途
count()	统计数量
sum()	求和
avg()	平均值
max()	最大值
min()	最小值

注意点：
count(*)和count(1)不会忽略null值的数据，一般来说是会忽略的。

数据库级别的MD5加密

-- UPDATE 表名 SET 字段名=MD5(字段名);
UPDATE `testmd5` SET `pwd`=MD5(`pwd`) WHERE id=1;

MD5()本质是个sql函数
也可以在插入的时候就加密

INSERT INTO `testmd5` VALUES(1,'abc',MD5('123456'));