1.外键(了解)
学生表的 gradeid 字段要去引用年级表的 gradeid
-- 创建年级表
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
-- 创建学生表
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
PRIMARY KEY (`id`), -- 主键
KEY `FK_gradeid` (`gradeid`), -- 定义外键
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) -- 给外键加约束,当前表的 gradeid 绑定到 grade 表的 gradeid 列,并且当前表的外键 gradeid 取名为 FK_gradeid
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
当前表的 gradeid 绑定到 grade 表的 gradeid 列,并且当前表的外键 gradeid 取名为 FK_gradeid。
删除有外键关系的表时,必须先删除引用别人的表(从表),在删除被引用的表(主表)
eg:先删student表,后删grade
However,还可以这样写,建表时没有外键约束,后续添加(一般都这样做)
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
2.DML语言(全部记住)
DML语言:数据操作语言
2.1 添加 insert
-- 表和字段名用``,值VALUES和注释COMMENT用''
INSERT INTO `student` (`gradeid`) VALUES ('大三');
INSERT INTO `student` (`name`,`pwd`,`gradeid`) VALUES ('张三','123456','大三');
INSERT INTO `student` (`gradeid`) VALUES ('大三'),('大一'),('大三');
INSERT INTO `student` (`name`,`sex`,`gradeid`)
VALUES ('张三','male','大三'),('李四','male','大一'),('王五','female','大三');
INSERT INTO `student`
VALUES ('1','张三','male',`大三`),('1','王五','female','大三');
-- 字段可以省略,后面的值不能省,一一对应。
-- 可以插入多条数据。
2.2 修改 update
-- 修改学生姓名,WHERE后接条件
UPDATE `student` SET `name`='张三' WHERE id = 1; -- 修改单个属性
UPDATE `student` SET `name`='张三',`sex`='male' WHERE id = 1; -- 修改多个属性
-- 条件WHERE id(>,<,=,<>或!=不等于,BETWEEN 区间)某个值
UPDATE `student` SET `name`='张三' WHERE id BETWEEN 2 AND 5;
-- 多条件定位数据 AND OR
UPDATE `student` SET `name`='张三' WHERE `name`='张伟' AND `sex`='male';
2.3 删除 delete
DELETE和TRUNCATE相同和不同:
相同:DELETE和DELETE删除,表的结构和索引不会变。
不同:TRUNCATE重新设置自增列,计数器会归零。不会影响事务。(后面讲)
DELETE FROM `student` WHERE id=1;
DELETE FROM `student`
-- TRUNCATE命令清空数据库
TRUNCATE `student`;
3.DQL查询数据(最重点)
DQL语言:数据查询语言(数据库使用频率最高,最核心语言。)
school.sql链接:狂神Msql中 school.sql文件_狂神school.sql-CSDN博客
3.1 指定查询字段
-- SELECT 字段 FROM 表
SELECT * FROM student
SELECT `studentno`,`studentname` FROM student
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student AS 学生
-- 字段名如果不见名知意,用关键词AS给字段和表起别名
-- 字符串拼接函数 CONCAT()
SELECT CONCAT('姓名:',studentname) AS 新名字 FROM student
-- 去重关键字 DISTINCT:重复数据只显示一条
SELECT `studentno` FROM result
SELECT DISTINCT `studentno` FROM result
-- 做计算(所有学生成绩+1)
SELECT `studentno`,`studentresult`+1 AS '提分后' FROM result
SELECT 表达式 FROM 表
数据库中的表达式可以是: 文本值,列,Null , 函数,计算表达式,系统变量…
3.2 where条件子句
作用:检索数据中符合条件的值
WHERE后搜素的条件由一个或多个表达式组成,结果为布尔值。
逻辑查询
- AND
- OR
- NOT
-- BETWEEN...AND...
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult BETWEEN 95 AND 100
-- 与 AND
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult>=95 AND studentresult<=100
-- 或 OR
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult IS NULL OR studentresult<60
-- 非 NOT
SELECT `studentno`,`studentresult` FROM result
WHERE NOT studentno=1000
模糊查询
- IS NULL
- IS NOT NULL
- a BETWEEN b AND c (若a在b和c之间,则为真)
- a LIKE b (SQL匹配,如果a匹配b,则结果为真)
- a3 IN (a1,a2,a3...) (真)
IS (NOT) NULL关键字
-- 查询家庭地址为空的同学
SELECT `studentno`,`studentresult` FROM result
WHERE address='' OR address IS NULL;
-- 查询出生日期不为空的同学
SELECT `studentno`,`studentresult` FROM result
WHERE `borndate` IS NOT NULL;
LIKE关键字
-- %代表0到任意一个字符,_代表一个字符
-- 查询姓刘的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '刘%' -- 刘n(n>=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 '%嘉%'
IN关键字
-- 查询1001,1002,1003号学生
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno IN (1001,1002,1003);
-- 查询住址为北京市东城区、上海市闵行区的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE address IN ('北京市东城区','上海市闵行区');
3.3 联表查询 JOIN ON(重点)
联表查询思路:
① 分析查询的字段来自哪些表
② 确定使用哪种连接查询(7种)
-- 联表查询
-- 格式:
-- SELECT 你想查询的多张表的数据字段
-- FROM 表1 (INNER LEFT RIGHT)JOIN 连接的表2
-- ON 交叉条件
-- WHERE 判断条件
-- tip:
-- ON是取表之前加的判断条件
-- WHERE是取表之后加的判断条件
-- 查询参加考试了的同学(涉及到学号,姓名,科目编号subjectno,分数,分布在两个表)
-- result表:学号,科目编号,分数
-- student表:学号,姓名
-- INNER JOIN
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
-- 注意:使用s表的studentno为s.studentno。不能是`s.studentno`,会报错!!!
FROM student AS s INNER JOIN result AS r -- 取s表和r表的交集
ON s.studentno = r.studentno -- 交集是学号
-- RIGHT JOIN
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student AS s RIGHT JOIN result AS r
-- 以result表为基准,先查询并返回result中所有字段为学号,科目编号,分数的值,再返回student表匹配的值
ON s.studentno = r.studentno -- 找两个表的连接字段
-- LEFT JOIN
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student AS s LEFT JOIN result AS r
-- 以student表为基准,先查询并返回student中所有字段为学号,姓名的值,再返回result表匹配的值
ON s.studentno = r.studentno -- 找两个表的连接字段
操作 | 描述 |
---|---|
Inner join | 如果表中至少有一个匹配,就返回行 |
left join | 先返回左表中所有的值(记录,行),再返回右表中与左表匹配的值 |
right jion | 先返回右表中所有的值(记录,行),再返回左表中与右表匹配的值 |
-- 查询缺考同学信息
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student AS s LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL
-- 查询参加考试同学信息(涉及到学号,姓名,科目名subjectname,分数,分布在三个表)
-- result表:学号,分数,(科目编号subjectno:不需要体现到新标准,只是作为ON连接两表的判断条件)
-- student表:学号,姓名
-- sbject表:科目名subjectname,(科目编号subjectno)
SELECT s.studentno,`studentname`,sub.subjectname,`studentresult`
FROM result AS r LEFT JOIN student AS s
-- 查学生成绩信息,不是所有学生信息,所以以成绩表为基准,因为可能有没参加考试的同学
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub -- 两张表合成一张表后再接一张result表
ON r.subjectno=sub.subjectno -- 找两个表的连接字段
联表查询技巧:
①先判断以那张表为基准,怎么判断以哪张表为基准?
eg:查询缺考同学信息。
以学生表为基准,查询出所有学生成绩,再找出成绩IS NULL的学生。
eg:查询学生成绩信息。(除去没参加考试同学)
以成绩表为基准,因为有的学生可能没参加考试,成绩表里根本没那条记录。
以学生表为基准,查询出所有学生成绩,再找出成绩IS NOT NULL的学生。
②假设存在多张表查询,慢慢来,先查询两张表然后再慢慢增加。
联表查询练习题:
-- 查询学生所属的年级(查询学号、姓名、年级名称)
SELECT `studentno`,`studentname`,`gradename`
FROM student AS s LEFT JOIN grade AS g
ON s.gradeid=g.gradeid
-- 查询科目所属的年级(查询科目名称、年级名称)
SELECT `subjectname`,`gradename`
FROM `subject` AS s LEFT JOIN `grade` AS g
ON s.gradeid=g.gradeid
-- 查询参加了 高等数学-1 考试的同学的信息(查询学生的学号、学生姓名、科目名、分数)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s RIGHT JOIN result AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
3.4 自联接
本质:一张表拆为两张一样的表,自己的表和自己连接,描述父子关系
举例:创建一个category表:
`categoryid`:种类id
`pid`:父类id
`categoryName`:种类名
表中种类和种类间有父子关系:
为了描述这种父子关系,
-- 查询父子信息
SELECT a.`categoryName` AS '父栏' , b.`categoryName` AS '子栏'
FROM `category` AS a , `category` AS b -- 表面从两张表查,实际从一张表查
WHERE a.`categoryid`=b.`pid`
一张表拆为两张一样的表,自己和自己连接,则有:
代替上述树形结构,描述种类间的父子关系。
3.5 分页 LIMIT 和排序 ORDER BY
排序 ORDER BY
升序ASC,降序DESC
-- 例:对所有参加高等数学-1的学生的成绩排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s RIGHT JOIN result AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY studentresult DESC; -- 通过studentresult字段降序地排
分页 LIMIT
为什么分页?因为100万条数据不可能同时展示在一个网页上,缓解数据库压力,给人体验更好。
和分页不同的是瀑布流。瀑布流没有页数,下滑可以无限加载新的数据(文字,图片)。
-- 例:对所有参加高等数学-1的学生的成绩排序(每页只显示五条数据)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s RIGHT JOIN result AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY studentresult DESC
LIMIT 0,5 -- 查询第一页 (第N页的起始下标,每页数据条数)
两个参数:第N页的起始下标n,每页数据条数pagesize
LIMIT 0,5 -- 查询第一页
LIMIT 5,5 -- 查询第二页
LIMIT 10,5 -- 查询第三页
... ...
习题: 查询 Java程序设计-1 课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,课程名称,分数)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s RIGHT JOIN result AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname = 'Java程序设计-1' AND studentresult > 80
ORDER BY studentresult DESC
LIMIT 0,10
3.6 子查询
在WHERE语句中嵌套一个查询语句
WHERE (SELECT * FROM ...)
习题: 查询 Java程序设计-1 的所有考试结果(学号,课程编号,成绩),降序排列
注意:涉及两张表,除成绩表外,题目里的Java程序设计-1还隐含涉及到subject表。
-- 方法一:连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM result AS r INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname = 'Java程序设计-1'
ORDER BY studentresult DESC
-- 方法二:子查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM result
WHERE subjectno = (
SELECT subjectno FROM `subject` -- 查询所有考过 Java程序设计-1 的学生学号
WHERE subjectname = 'Java程序设计-1'
)
习题: 查询 Java程序设计-1 成绩大于80分同学的信息(学号,姓名,分数)
注意:涉及三张表,除学生表、成绩表外,题目里的Java程序设计-1还隐含涉及到subject表。
-- 联表查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM student AS s RIGHT JOIN result AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname` = 'Java程序设计-1' AND `studentresult` > 80
-- 子查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM student AS s RIGHT JOIN result AS r
ON s.`studentno`=r.`studentno`
WHERE `studentresult` > 80 AND `subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = 'Java程序设计-1'
)
3.7 分组 GROUP BY 和过滤 HAVING
例:查询不同课程的平均分大于80,最高分,最低分
核心:根据不同的课程分组(GROUP BY)
SELECT subjectname,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM `result` AS r INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING AVG(studentresult)>80 -- 对分组后的数据进行筛选
注意:WHERE和HAVING都后接条件,但是HAVING加分组之后满足的条件
小结: SELECT语句顺序
INNER JOIN --> ON --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> LIMIT
联表 分组 排序 分页
4. MySQL函数
4.1 一些函数
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回0-1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('2323232') -- 返回字符串长度
SELECT CONCAT('我','233') -- 拼接字符串
-- 查询姓 周 的同学 ,改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'
-- 时间跟日期函数(记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期时分秒
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系统
SELECT USER() -- 系统当前用户(root@localhost)
SELECT VERSION() -- 版本号
4.2 聚合函数(常用)
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
-- COUNT()统计表中的数据
SELECT COUNT(studentname) FROM student; -- COUNT(字段),忽略所有的NULL值
SELECT COUNT(*) FROM student; -- COUNT(*),不忽略NULL值(计算行数,也就是记录数)
SELECT COUNT(1) FROM student; -- 和COUNT(*)没什么区别,查询记录数
-- SUM()
SELECT SUM(`studentresult`) AS 分数总和 FROM result
-- AVG()
SELECT AVG(`studentresult`) AS 平均分 FROM result
-- MAX()
SELECT MAX(`studentresult`) AS 最高分 FROM result
-- MIN()
SELECT MIN(`studentresult`) AS 最低分 FROM result
4.3 MD5加密(拓展)
新创建一张testmd5表
用MD5()函数插入用户信息时加密
INSERT INTO testmd5 VALUES (1,'zhangsan',MD5('123456')),(2,'lisi',MD5('123456')),(3,'wangwu',MD5('123456')),(4,'xiaoming',MD5('123456'))
如何校验:用户传进来的密码进行MD5加密后,对比加密后的值
SELECT * FROM testmd5
WHERE `name`='xiaoming' AND pwd=MD5('123456')