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