自连接:自己的表和自己的表连接,核心:把一张表当成两张表用即可
父类
categoryId | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
categoryId | PID | categoryName |
---|---|---|
4 | 3 | 数据库 |
6 | 3 | web开发 |
7 | 5 | ps技术 |
8 | 2 | 办公信息 |
要求查询结果是:父类和子类相对应
-- ==== 自连接 ===
SELECT c1.`categoryid` AS '父栏目',c1.`categoryName` '父栏目名',c2.`categoryid` '子栏目',c2.`categoryName` '子栏目名'
FROM `category` c1
INNER JOIN `category` c2
ON c2.`pid` = c1.`categoryid`;
连表查询练习
-- === 连表连接联系 ===
-- 1.查询学生的所属年级(学生id,学生姓名,学生年级)
SELECT `studentno` '学生id',`studentname` '学生姓名',`gradeName` '所属年级'
FROM `student` s
LEFT JOIN `grade` g
ON s.`gradeid` = g.`gradeid`;
-- 2.查询科目所属的年级 (subjectno subjectname gradename)
SELECT `subjectno`,`subjectname`,`gradename`
FROM `subject` s
RIGHT JOIN `grade` g
ON s.`gradeid` = g.`gradeid`;
-- 3.查询参加 数据库结构-1 考试的学生 (学号,学生姓名,考试科目,考试成绩)
-- ====1.重命名应该尽可能的清楚 2.要注意有无 ; 在句子末尾
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.5 分页(limit)和排序(order by)
排序(ORDER BY)
-- ===== 分页 limit 和 排序order by =====
-- 排序:1,升序 ASC 2,降序 DESC
-- 语法:order by + 字段名 +排序方式
-- 查询的结果根据成绩降序排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构-1'
ORDER BY `studentresult` DESC
语法:order by + 字段名 + 排序方式
排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构-1'
ORDER BY `studentresult` DESC
LIMIT 0,2
-- 排序 limit (a,x) a是从第几条数据开始,0是第一条数据的位置;x是一次显示数据的条数
-- 第 1 页 (0,5) ,起始值规律:(1-1)*5
-- 第 2 页 (5,5) , (2-1)*5
-- 第 3 页 (10,5) , (3-1)*5
-- 第 n 页 ((n-1)*5,5), (n-1)*5
-- pagesize : 一个页面容纳数据的最大条数
-- 起始值: (n-1)*5
-- 当前页: n;
-- 总的页数: 数据条数除以pagesize向上取整
-- 查询 Java程序设计-1 考试成绩前10的学生 并且分数要大于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 sub.`subjectname` = 'Java程序设计-1' AND `studentresult` >= 80
ORDER BY `studentresult` DESC
LIMIT 0,10
4.6 子查询
子查询在where里面嵌套一个查询语句
-- 分数不小于80分的学生的学号和姓名
-- 方式1 .`result`连表查询
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` >= 80
-- 高等数学-2 分数不小于80分的学生的学号和姓名
-- 方式1 .连表查询
SELECT DISTINCT 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 r.`studentresult` >= 80 AND `subjectname` = '高等数学-2'
-- 方式2 join on 和子查询
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` >= 80 AND `subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等数学-2'
)
-- 方式3 单纯的嵌套子查询
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno` IN (
SELECT `studentno`
FROM `result`
WHERE `studentresult` > 80 AND `subjectno` = (
SELECT `subjectno`
FROM `subject`
WHERE `subjectname` ='高等数学-2'
)
)
-- 查询 C语言-1 前5名同学的成绩的信息(学号,姓名,分数)
-- 方式1:连表查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON `subjectname` = 'C语言-1'
WHERE r.`subjectno` = sub.`subjectno`
ORDER BY `studentresult` DESC
LIMIT 0,5
-- 方式2 嵌套查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `subjectno` = (
SELECT `subjectno`
FROM `subject`
WHERE `subjectname` = 'C语言-1'
)
ORDER BY `studentresult` DESC
LIMIT 0,5
4.7 分组和过滤
-- 查询不同课程的平均分,最高分,最低分
-- 核心:根据不同的课程进行分组计算
SELECT `subjectname`,AVG(`studentresult`) '平均分',MAX(`studentresult`),MIN(`studentresult`),COUNT(`studentresult`)
FROM `result` r
INNER JOIN `subject` s
ON r.`subjectno` = s.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段进行分组
HAVING 平均分>80
4.8 select 小结
SELECT [ALL|DISTINCT]
{|table|[table.field1[as alias1][,table.field2]][,...]]}
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}];--指定查询的记录从哪条到哪条
注意:[]括号代表可选,{}括号代表必选的
5. MySQL的函数
5.1 MySQL的常用函数
-- =========== 常用函数 ===========
-- 数学运算
SELECT ABS(-99) -- 返回99 求绝对值函数
SELECT CEILING(9.1) -- 返回10 作用:向上取整
SELECT FLOOR(9.1) -- 返回9 作用: 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(0) -- 判断一个值的符号,正值返回1,负值返回-1,0返回0
-- 字符串
SELECT CHAR_LENGTH('急急急') -- 返回字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
-- 替换字符串,从1开始计数,第一个数字是开始的位置,第二个数字是替换的长度,最后的是要替换的字符串
SELECT INSERT('今天晚饭要吃什么好呢?',7,5,'大众餐啊')
SELECT LOWER('Swrici') -- 转小写
SELECT UPPER('Swrici') -- 转大写
SELECT INSTR('ILoveYou','you') -- 查询字符在字符串中第一次出现的位置,大小写不敏感
SELECT REPLACE ('我要吃晚餐','晚','大'); -- 替换字符串
-- 截取字符串,第一个数字是位置,第二个数字是截取长度,不写第一个会截取到字符串末尾
SELECT SUBSTR('今天晚上不知道会不会下雨',3,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() -- 获取当前的版本
5.1 聚合函数(这个才是真的常用)
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
AVG() | 求平均 |
max() | 求最大值 |
min() | 求最小值 |
… | … |
-- ======= 聚合函数 =======
-- count(字段) count(*) count(1) 都能统计表中的数据
-- 对于主键使用 count(`主键`)更快一些
SELECT COUNT(`loginpwd`) FROM `student`; -- count(字段),忽略所有的null值
SELECT COUNT(*) FROM `student`; -- count(*),不会忽略null值
SELECT COUNT(1) FROM `student`; -- count(1),不会忽略null值
SELECT SUM(`studentresult`) AS '总和' FROM `result` -- 求总和
SELECT SUM(`studentresult`)/COUNT(*) AS '总和' FROM `result` -- 求平均值
SELECT AVG(`studentresult`) AS '平均分' FROM `result`; -- 求平均值
SELECT MAX(`studentresult`) FROM `result` ; -- 最高分
SELECT MIN(`studentresult`) FROM `result`; -- 最低分
-- 查询不同课程的平均分,最高分,最低分
-- 核心:根据不同的课程进行分组计算
SELECT `subjectname`,AVG(`studentresult`) '平均分',MAX(`studentresult`),MIN(`studentresult`),COUNT(`studentresult`)
FROM `result` r
INNER JOIN `subject` s
ON r.`subjectno` = s.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段进行分组
HAVING 平均分>80
5.3 数据库级别的MD5加密(扩展)
什么是MD5?
主要增强了算法的复杂性和不可逆性
虽然MD5不可逆,但是具体的值的MD5是一样的
-- ========== 数据库md5加密 ===========
CREATE TABLE `testmd5`(
`id` INT(5) NOT NULL ,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8;
INSERT INTO `testmd5`(`id`,`name`,`pwd`) VALUES
(1,'张三','123456'),
(2,'李四','123456'),
(3,'王五','123456'),
(4,'刘六','123456'),
(5,'薛七','123456')
UPDATE `testmd5` SET `pwd` = MD5(`pwd`) WHERE `id`='1';
UPDATE `testmd5` SET `pwd` = MD5(`pwd`) WHERE `id` != 1;
-- 插入时转换
INSERT INTO `testmd5`(`id`,`name`,`pwd`) VALUE (6,'邱八',MD5('12345'))
-- 如何校验, 将用户传递进来的值进行md5加密,然后进行匹配
SELECT * FROM `testmd5` WHERE `name` = '李四' AND `pwd` = MD5('123456')
ps: 数据库本地编程(出发器、存储过程)和数据库集群有冲突