3、MySQL数据管理
3.1、外键
如果公共关键字在一个关系表中是主关键字,那么这个公共关键字被称为另一个关系表的外键。外键表示了两个关系表之间的相关联系。以另一个关系表的外键作主关键字的表被称为主表,具有此外键的表被称为主表的从表。
在实际操作中,将一个表的值放入第二个表来表示关联,所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时,第二个表中保存这些值的属性称为外键(foreign key)。
外键作用
保持数据一致性,完整性,主要目的是控制存储在外键表中的数据,约束。使两张表形成关联,外键只能引用外表中的列的值或使用空值。
方式一:在创建表的时候,增加约束,比较麻烦
CREATE TABLE `grade`(
`grade_id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`grade_name` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的grade_id字段,需要引用年级表的grade_id
-- 定义外键key
-- 给外键添加约束(执行这个引用) references
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 '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`grade_id` INT(10) NOT NULL COMMENT '学生年级',
PRIMARY KEY(`id`),
-- 定义外键
KEY `Fk_grade_id` (`grade_id`),
-- 添加约束
CONSTRAINT `Fk_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8复制
删除具有外键引用关系表的时候,必须先删除引用其它表的表(从表),再删除被引用的表(主表)
方式二:创建表成功后,添加外键约束
CREATE TABLE `grade`(
`grade_id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`grade_name` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的grade_id字段,需要引用年级表的grade_id
-- 定义外键key
-- 给外键添加约束(执行这个引用) references
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 '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`grade_id` INT(10) NOT NULL COMMENT '学生年级',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 创建表时没有添加外键
ALTER TABLE `student` ADD CONSTRAINT `Fk_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`grade_id`)
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的字段) REFERENCES 引用表(外键字段)复制
上述两种方式是物理外键,是数据库级别的外键,不建议使用,会造成数据库过多引用引起一些不必要的问题
推荐:
数据库就只是单纯的表,通过行列存放数据(行:数据,列:字段属性)
如果多张表有引用关系(使用外键),通过程序(比如Java代码)去实现
删除外键
ALTER TABLE student DROP FOREIGN KEY FK_grade_id
-- 执行完上述语句后,索引还在,还需要删除索引
-- 索引是创建外键时自动生成的
ALTER TABLE student DROP INDEX FK_grade_id复制
3.2、DML语言
通过DML语句对数据库中数据进行一系列的操作
insert
update
delete
3.3、添加
insert
-- 插入语句
-- 在写插入语句时,一定要保证数据和字段一一对应,即表名后的括号对应values后括号,一个字段属性 可对应多个values值,相当于插入多条数据
INSERT INTO `grade`(`grade_name`) VALUES('一年级') -- 插入一条数据
INSERT INTO `grade`(`grade_name`) VALUES('一年级'),('二年级') -- 插入多条数据
INSERT INTO `student`(`name`) VALUES('小明')
INSERT INTO `student`(`name`,`sex`,`age`) VALUES('小红','男','18'),('小雨','男','17'),('小芳','女','19')
INSERT INTO `student` VALUES(5,'张三','男','2000-01-23','西安','10001@qq.com','一年级',21)复制
语法:insert into 表名(字段名1,字段名2,字符名3...) values('值1'),('值2'),('值3')...
注意:
字段和字段直接使用英文逗号隔开
字段可以省略,但后面的值必须一一对应,不能少
可以同时插入多条数据,即values后面的值,用括号分隔每一条数据
3.4、修改
update
-- 修改学生名字(通过条件)
UPDATE `student` SET `name`='李四' WHERE id = 5
-- 不指定条件的情况下,会改动所有表!!!
-- 修改多个属性,逗号隔开
UPDATE `student` SET `name` = '小丽', `sex` = '女' WHERE id = 5
UPDATE `student` SET `name` = '小丽', `sex` = '女' WHERE id BETWEEN 1 AND 2复制
条件:where 子句 通过运算符来判断执行 比如:id=3,id>3...
运算符返回 布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 5=6 | false | |
<>或!= | 不等于 | 5<>6 | true |
> | |||
< | |||
>= | |||
<= | |||
BETWEEN ... AND ... | [2,5] | ||
AND | && | 5>1 and 1>2 | false |
OR | || | 5>1 or 1>2 | true |
语法: UPDATE 表名 SET 字段 = value WHERE 条件
注意:
字段是列类型,操作时尽量戴上 ``
筛选的条件,如果没有指定,会修改表中所有数据,极其危险!!!
多个设置的字段属性,使用英文逗号隔开
value,是一个字段对应的具体值,也可以是一个变量(通常是时间),变量如下修改
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE id BETWEEN 1 AND 2
复制
3.5、删除
delete命令
语法:delete from 表名 where 条件
-- 删除数据表(避免使用)
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student` WHERE id=1复制
TRUNCATE命令
作用:清空一个数据库表,表的结构和索引约束不会变
-- 清空student表
TRUNCATE `student`复制
delete和TRUNCATE区别
相同点:都能删除数据,不会删除表结构
不同:
TRUNCATE 删除后自增列的 计数器会归零
TRUNCATE 不会影响事务
-- 测试delete和TRUNCATE区别
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`course` VARCHAR(50) NOT NULL,
PRIMARY KEY (`ID`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test`(`course`) VALUES("数据结构"),('操作系统'),('计算机网络'),('计算机组成原理')
DELETE FROM `TEST` -- 不会影响自增
TRUNCATE TABLE `TEST` -- 自增会归零复制
DELETE删除表后,如果重启数据库:
如果数据库表是InnoDB类型,自增列会从1开始。(数据存在内存中,数据库重启后丢失自动重新开始)
如果数据库表是MyiSAM类型,会从上一个自增量开始。(数据存在文件中,不会丢失)
创建一个school数据库
-- 创建一个school数据库
CREATE DATABASE school
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入学生数据 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','小丽',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','小丹',0,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233'),
(1002,'123456','小明',1,4,'13800002223','广东深圳','1990-1-2','text112@qq.com','123456199001011234'),
(1003,'123456','小王',1,2,'13800002224','广东深圳','1990-1-3','text113@qq.com','123456199001011235'),
(1004,'123456','小芳',0,1,'13800002225','广东深圳','1990-1-4','text114@qq.com','123456199001011236'),
(1005,'123456','小兰',0,3,'13800002226','广东深圳','1990-1-5','text115@qq.com','123456199001011237');
-- 插入成绩数据
INSERT INTO `score`(`studentno`,`subjectno`,`examdate`,`studentresult`) VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58),
(1000,6,'2013-11-15 16:00:00',60),
(1000,7,'2013-11-16 16:00:00',61),
(1000,8,'2013-11-17 16:00:00',62),
(1000,9,'2013-11-18 16:00:00',63),
(1000,10,'2013-11-19 16:00:00',64),
(1000,11,'2013-11-20 16:00:00',65),
(1000,12,'2013-11-21 16:00:00',66),
(1000,13,'2013-11-22 16:00:00',67),
(1000,14,'2013-11-23 16:00:00',68),
(1000,15,'2013-11-24 16:00:00',69),
(1000,16,'2013-11-25 16:00:00',70),
(1000,17,'2013-11-26 16:00:00',71);
INSERT INTO `score`(`studentno`,`subjectno`,`examdate`,`studentresult`) VALUES
(1001,1,'2013-11-11 16:00:00',85),
(1002,1,'2013-11-11 16:00:00',85),
(1003,1,'2013-11-11 16:00:00',85),
(1004,1,'2013-11-11 16:00:00',85),
(1005,1,'2013-11-12 16:00:00',85),
(1005,5,'2013-11-13 16:00:00',85),
(1005,8,'2013-11-14 16:00:00',85)
-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);复制
4、DQL查询数据(极其重点)
4.1、DQL
Data Query Language:数据查询语言
所有查询操作都用 Select
数据库最核心、最重要的语句
使用频率最高
-- select基本语法
select [ALL | DISTINCT]
{* | TABLE.* | [table.filed1 [as xxx][, table.filed1 [as xxx]][,...]}
from table_name1 [as xxx] -- 表和字段可以起别名
[left | right | inner join table_name2] -- 联合查询
[where ...] -- 指令查询条件
[group by ...] -- 指定查询结果按照哪个字段分组
[having ...] -- 过滤分组需要满足的条件,(条件和where一样,只是位置不同)
[order by ...] -- 指定查询记录由一个或多个条件排序
[limit ...] -- 分页,指定查询记录从哪条到哪条为一页复制
4.2、指定查询字段
-- 查询全部学生
SELECT * FROM student
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
-- 别名(给查询结果起一个名字) AS 可以给字段、表分别起名
SELECT `studentno` AS 学号, `studentname` AS 学生姓名 FROM student AS a
-- 函数 Concat(a,b)
SELECT CONCAT('姓名:',studentname) AS concat拼接 FROM student复制
语法:SELECT 字段,... FROM 表
有时列名字不是那么见名知意,可以使用 AS 起别名:字段名 AS 别名 表名 AS 别名
去重复:distinct
作用:去除select查询结果中重复数据,重复数据只显示一条
-- 查询学生所有成绩
SELECT * FROM score
-- 查询哪些同学(通过学号)参加考试
SELECT `studentno` FROM score
-- 一个学号出现多次,去重复
SELECT DISTINCT `studentno` FROM score复制
数据库的列
-- 查看系统版本
SELECT VERSION()
-- 计算(表达式)
SELECT 100*3-1 AS 计算结果
-- 查询自增步长(变量)
SELECT @@auto_increment_increment
-- 学生考试成绩 +1 分 并查看
SELECT `studentno`,`studentresult`+1 AS 加分 FROM score复制
数据库中的表达式:文本值、列、null、函数、计算表达式、系统变量等
select 表达式
from 表
4.3、where条件子句
作用:检索数据中符合条件的值
搜索条件由一个或多个表达式组成,结果是布尔值。表达式结果为true才能查询到
利用与或非(&& || !=)来组合多个表达式
SELECT `studentno`, `studentresult` FROM score
WHERE studentresult>=90 AND studentresult<=100
-- AND &&
SELECT `studentno`, `studentresult` FROM score
WHERE studentresult>=90 && studentresult<=100
-- 区间查询
SELECT `studentno`, `studentresult` FROM score
WHERE studentresult BETWEEN 80 AND 100
-- 除了1000号学生以外的同学的成绩
SELECT `studentno`, `studentresult` FROM score
WHERE studentno!=1000
-- != 或 not
SELECT `studentno`, `studentresult` FROM score
WHERE NOT studentno=1000
-- || or
SELECT `studentno`, `studentresult` FROM score
WHERE studentno=1001 OR studentno=1002复制
模糊查询
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为NULL,结果为true |
IS NOT NULL | a is not null | 如果操作符不为NULL,结果为true |
BETWEEN | a between b and c | 若a在b和c之间,结果为true |
LIKE | a like b | SQL匹配,如果a匹配b,结果为true |
IN | a in (a1,a2,a3...) | 假设a属于括号中的某一个值,结果为true |
-- -----------模糊查询------------
-- 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
-- 查询 地址为空 或 地址为null的学生
SELECT `studentno`, `studentname` FROM `student`
WHERE `address` IS NULL OR `address`=''
-- 为空
SELECT `studentno`, `studentname` FROM `student`
WHERE `borndate` IS NULL
-- 不为空
SELECT `studentno`, `studentname` FROM `student`
WHERE `borndate` IS NOT NULL复制
4.4、联表查询
-- -----------联表查询-----------
-- 查询参加了考试的同学(学号,姓名,科目编号,成绩)
SELECT * FROM student
SELECT * FROM score
/* 分析
1、需求:有哪些字段需要查询,分别来自哪个表
2、使用哪种 连接查询?7种
确定交叉点:这两个表中哪些数据时相同的
判断条件:学生表中 `studentno` = 成绩表中 `studentno`
*/
-- join (连接的表) on (判断条件) 连接查询
-- where 等值查询
-- INNER JOIN
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `score` AS sc
ON s.studentno = sc.studentno
-- Right Join
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
RIGHT JOIN `score` AS sc
ON s.studentno = sc.studentno
-- Left Join
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `score` AS sc
ON s.studentno = sc.studentno复制
操作 | 描述 | |
---|---|---|
inner join | 如果表中至少有一个匹配,就返回行 | |
left join | 会从左表中返回所有的值,即使右表中没有匹配 | |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
-- 查询缺考学生
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `score` AS sc
ON s.studentno = sc.studentno
WHERE `studentresult` IS NULL
-- 查询参加了考试的同学(学号,姓名,科目名,成绩)
/* 分析
1、需求:有哪些字段需要查询,分别来自哪个表,student,score,subject(连接查询)
2、使用哪种 连接查询?7种
确定交叉点:这两个表中哪些数据时相同的
判断条件:学生表中 `studentno` = 成绩表中 `studentno`
*/
SELECT s.studentno,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
LEFT JOIN `score` AS sc
ON s.studentno = sc.studentno
INNER JOIN `subject` sub
ON sc.subjectno = sub.subjectno
-- 需要查询哪些数据
-- 从哪几个表中查询 FROM 表 join 连接的表 on(通过哪个相同的字段连接) 交叉条件
-- 假设存在多张表查询,先查询两张表,再逐步递增
-- from a left join b
-- from a right join b复制
查询实例
-- 查询学生所属年级(学号,学生姓名,年级名称)
SELECT `studentno`, `studentname`, `gradename`
FROM student AS s
INNER JOIN grade AS g
ON s.gradeid = g.gradeid
-- 查询科目所属年级
SELECT `subjectname`,`gradename`
FROM `subject` AS sub
INNER JOIN grade AS g
ON sub.`gradeid` = g.`gradeid`
-- 查询了参加 数据库结构-1 考试的同学信息:学号,学生姓名,科目名,分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN score AS sco
ON s.`studentno` = sco.`studentno`
INNER JOIN `subject` AS sub
ON sco.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构-1'复制
自连接
当前表和当前表连接。核心:将一张表拆分为两张表
-- 创建自连接数据库表
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 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
操作:查询父类对应的子类关系
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
-- 操作:查询父类对应的子类
SELECT a.`categoryname` AS '父栏目', b.`categoryname` AS '子栏目'
FROM category AS a, category AS b
WHERE a.`categoryid` = b.`pid` -- 通过categoryid和pid表明父类和子类的关系复制
4.5、排序和分页
排序
-- 排序:升序:ASC 降序:DESC
-- ORDER BY 通过哪个字段排序,怎么排
-- 查询结果根据成绩排序(降序)
-- 查询了参加 高等数学-1 考试的同学信息:学号,学生姓名,科目名,分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN score AS sco
ON s.`studentno` = sco.`studentno`
INNER JOIN `subject` AS sub
ON sco.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` ASC复制
分页
-- 100万条数据
-- 针对数据量大时。如何缓解数据库压力,同时给客户端更好的体验。采用分页 (或瀑布流)
-- 分页,每页只显示2条数据
-- 语法:limit 查询起始下标,页面大小
-- LIMIT 0,2 1~2
-- LIMIT 1,2 2~3
-- LIMIT 6,2 7~8
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN score AS sco
ON s.`studentno` = sco.`studentno`
INNER JOIN `subject` AS sub
ON sco.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` ASC
LIMIT 0,2
-- 第一页 limit 0,2 (1-1)* 5
-- 第二页 limit 2,2 (2-1)* 5
-- ...
-- 第N页 limit 0,2 (n-1)* pagesize
-- pagesize: 页面大小
-- (n-1)* pagesize:起始数值
-- n: 当前页面
-- 数据总数/页面大小 = 总页数
-- 思考:查询 高等数学-1 课程成绩排名前三的学生,且分数要大于80的学生信息(学号,姓名,课程名称,分数)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN score AS sc
ON s.`studentno`=sc.`studentno`
INNER JOIN `subject` AS sub
ON sc.`subjectno`=sub.`subjectno`
WHERE `subjectname` = '高等数学-1' AND `studentresult`>=80
ORDER BY `studentresult` DESC
LIMIT 0,3复制
4.6、子查询
-- --------------- where 子查询 ---------------------
-- 1、查询 高等数学-1 的所有考试结果(学号,科目编号,成绩),降序排
-- 方式一:使用连接查询
SELECT `studentno`,sc.`subjectno`,`studentresult`
FROM score AS sc
INNER JOIN `subject` AS sub
ON sc.`subjectno`=sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` DESC
-- 方式二、使用子查询(由里到外执行)
-- 子查询 高等数学-1 的学生学号
SELECT `studentno`,`subjectno`,`studentresult`
FROM score
WHERE `subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等数学-1'
)
ORDER BY `studentresult` DESC
-- 查询课程为 高等数学-1 分数不小于80分的学生的学号和姓名
SELECT s.`studentno`,`studentname`
FROM student AS s
INNER JOIN score AS sc
ON s.`studentno`=sc.`studentno`
INNER JOIN `subject` AS sub
ON sc.`subjectno`=sub.`subjectno`
WHERE `subjectname` = '高等数学-1' AND `studentresult`>=80
-- 查询 高等数学-1 不小于80分的学生的学号和姓名
SELECT DISTINCT s.`studentno`,`studentname`
FROM student AS s
INNER JOIN score AS sc
ON s.`studentno` = sc.`studentno`
WHERE `studentresult`>=80
-- 在这基础上增加科目 高等数学-1
-- 子查询 高等数学-1 的编号
SELECT DISTINCT s.`studentno`,`studentname`
FROM student AS s
INNER JOIN score AS sc
ON s.`studentno` = sc.`studentno`
WHERE `studentresult`>=80 AND `subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等数学-1'
)
-- 另一种方式
SELECT `studentno`,`studentname` FROM student
WHERE `studentno` IN (
SELECT `studentno` FROM score
WHERE `studentresult`>=80 AND `subjectno` = (
SELECT `subjectno`FROM `subject`
WHERE `subjectname` = '高等数学-1'
)
)
-- 练习:使用子查询 查询 高等数学-1 前三名的同学成绩信息(学号,姓名,成绩)
SELECT DISTINCT s.`studentno`,`studentname`,`studentresult`
FROM student AS s
INNER JOIN score AS sc
ON s.`studentno` = sc.`studentno`
WHERE `subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等数学-1'
)
ORDER BY `studentresult` DESC
LIMIT 0,3复制
4.7、分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于60
-- 根据不同的课程分组
SELECT `subjectname`, AVG(`studentresult`) AS 平均分, MAX(`studentresult`) AS 最高分, MIN(`studentresult`) AS 最低分
FROM score AS sc
INNER JOIN `subject` AS sub
ON sc.`subjectno` = sub.`subjectno`
GROUP BY sc.`subjectno` -- 通过哪个字段分组
HAVING 平均分>60
ORDER BY 平均分 DESC -- 降序排序复制