暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

MySQL02:MySQL数据管理及查询

Hu说编程 2021-07-12
267

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=6false
<>或!=不等于5<>6true
>


<


>=


<=


BETWEEN ... AND ...
[2,5]
AND&&5>1 and 1>2false
OR||5>1 or 1>2true

语法: 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 NULLa is null如果操作符为NULL,结果为true
IS NOT NULLa is not null如果操作符不为NULL,结果为true
BETWEENa between b and c若a在b和c之间,结果为true
LIKEa like bSQL匹配,如果a匹配b,结果为true
INa 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技术');
 
复制

父类

categoryidcategoryname
2信息技术
3软件开发
5美术设计

子类

pidcategoryidcategoryname
34数据库
28办公信息
36web开发
57ps技术

操作:查询父类对应的子类关系

父类子类
信息技术办公信息
软件开发数据库
软件开发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 -- 降序排序
复制


文章转载自Hu说编程,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论