当前位置： 代码迷 >> 综合 >> Day3:MySQL数据管理
  详细解决方案

Day3:MySQL数据管理

热度：95   发布时间：2023-11-07 03:39:12.0

Day3:MySQL数据管理

  • 一、外键（了解）
  • 1.1 物理外键（数据库级别的外键，不推荐使用）
      • 方式一：
      • 方式二：
  • 二、DML数据操作（重点掌握）
    • 2.1 添加（insert语句）
    • 2.2 修改（update语句）
    • 2.3 删除（delete语句和truncate语句）
  • 三、DQL查询数据（最重点）
    • 3.1 DQL语言
    • 3.2 指定查询字段（Select语句）
    • 3.3 where条件子句（Select语句）
    • 3.4 联表查询（join语句）
    • 3.5 联表查询 -- 自连接
    • 3.6 分页和排序（limit语句和order by语句）
    • 3.7 子查询（嵌套查询）
    • 3.8 分组及过滤（group by语句和having 语句）

一、外键（了解）

1.1 物理外键（数据库级别的外键，不推荐使用）

方式一：

  • 在创建表的时候，增加约束（麻烦，比较复杂）
  • 删除有外键关系的表的时候，必须要先删除引用别人的表（从表，student），再删除被引用的表（主表，grade）
CREATE TABLE IF NOT EXISTS `grade` (`gradeid` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年纪id',`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8CREATE TABLE IF NOT EXISTS `student` (`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',`gradeid` INT(4) NOT NULL COMMENT '学生年纪',`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',PRIMARY KEY(`id`),-- 主键设置KEY `FK_gradeid` (`gradeid`), -- 定义外键keyCONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) -- 给这个外键添加约束)ENGINE=INNODB DEFAULT CHARSET=utf8

方式二：

  • 创建表成功后，再添加外键约束

CREATE TABLE IF NOT EXISTS `grade` (`gradeid` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年纪id',`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8CREATE TABLE IF NOT EXISTS `student` (`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',`gradeid` INT(4) NOT NULL COMMENT '学生年纪',`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',PRIMARY KEY(`id`)-- 主键设置)ENGINE=INNODB DEFAULT CHARSET=utf8-- 创建表的时候没有外键关系 ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 被引用的表(字段)
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`);

二、DML数据操作（重点掌握）

DML语言：数据操作语言，有insert（添加）、修改（update）和删除语句。

2.1 添加（insert语句）

  • 字段与字段间用 英文逗号 隔开
  • 字段是可以省略的，但后面的值必须要一一对应，主键也不能少
  • 可同时插入多条数据，values后面的值用 VALUES (),()表示即可
-- 插入语句，由于主键自增，可以省略
-- insert into 表明(字段1,字段2,字段3) values('值1'),('值2'),('值3'),.....)
INSERT INTO `grade`(`gradename`) VALUES ('大四');-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大一'),('大二'),('大三');-- 注意对应关系
INSERT INTO `student`(`name`) VALUES ('张三');
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','aaaa','男');INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('李四','bbbb','男'),('小芳','ccccc','女');

2.2 修改（update语句）

  • 条件：Where 后跟着运算符 （如id = 1,id < 2,id > 3等），运算符返回布尔值，如下表所示：
    在这里插入图片描述
-- 语法：
-- UPDATE 表 SET 列名=新值 WHERE 条件;
UPDATE `student` SET `name`='狂神' WHERE id=1;  -- 指定条件
UPDATE `student` SET `name`='狂神';  -- 不指定条件，会改动所有表
-- 修改多个属性,逗号隔开
UPDATE `student` SET `name`='长江7号',`pwd`='555' WHERE id=1;-- 通过多个条件判断数据：AND OR
UPDATE `student` SET `name`='常会将' WHERE `name`='狂神' AND `pwd`='aaaa';

2.3 删除（delete语句和truncate语句）

删除语句共有两种：delete和truncate

  • 相同点：都能删除数据，都不会删除表结构

  • 不同：
    truncate 重新设置 自增列，计数器归零，并且不会影响事务

  • delete删除的问题（了解），会重启数据库
    InnoDB 自增列会从1开始（存在内存当中，断电即失）
    MyISAM 继续从上一个自增量开始 （存在文档中的，不会丢失）

-- 删除数据
DELETE FROM `student`;  -- 完全删除，不建议操作
DELETE FROM `student` WHERE id=1;-- 完全清空一个数据库表，表的结构和索引约束不会变
TRUNCATE TABLE `student`;-- 测试delete和TRUNCATE的不同
CREATE TABLE `test`(`id` INT(4) NOT NULL AUTO_INCREMENT,`coll` VARCHAR(10) NOT NULL,PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET utf8INSERT INTO `test`(`coll`) VALUES ('123'),('456'),('789');DELETE FROM `test`;  -- 不会影响自增TRUNCATE TABLE `test`;  -- 自增会归0

三、DQL查询数据（最重点）

3.1 DQL语言

（Data Query Language：数据查询语言）

  • 所有的查询操作都用它：Select语句，数据库最核心的语言
  • 简单的查询，复杂的查询都可以完成

3.2 指定查询字段（Select语句）

  • 语法：SELECT 字段，… FROM 表
  • 也可以对列名起别名，用 AS语句（字段名 AS 别名 或 表名 AS 别名）
-- 查询全部 SELECT 字段 FROM `表`
SELECT * FROM `student`;
SELECT * FROM `result`;-- 查询指定字段
SELECT `studentno`,`sex` FROM `student`; -- 使用别名，给结果起一个名字，也可以给表起别名
SELECT `studentno` AS `学号`,`sex` AS `性别` FROM `student` AS s;-- 函数 Concat,查询结果进行拼接
SELECT CONCAT('姓名:',`studentname`) AS `新名字` FROM `student`;

  • 去重语句：distinct

  • 作用：去除SELECT查询出来的结果中重复的数据，只显示一条

-- 查询一下哪些同学有成绩
SELECT * FROM `result`;  -- 查询全部成绩
-- 查询哪些同学参加了考试
SELECT `studentno` FROM `result`;
-- 发现重复数据，去重
SELECT DISTINCT `studentno` FROM result;
  • Select语句还能查询其他表达式
  • 数据库中的表达式有：文本值，列，Null，函数，计算表达式，系统变量…
SELECT VERSION();  -- 查看系统版本号（函数）
SELECT 100*3-1 AS '计算结果'; -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）-- 学院考试成绩 + 1分查看
SELECT `studentno`,`studentresult`+1 AS `提分后` FROM `result`;

3.3 where条件子句（Select语句）

  • 逻辑运算符：and（&&，与）、or（||，或）、Not（!，非）

  • 模糊查询：比较运算符，如下表所示
    在这里插入图片描述

-- 模糊查询，查询姓张的同学
-- like结合，%(代表0到任意个字符) _（一个字符）
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '张%';   -- 查询姓张的
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '张_';   -- 张后边只有1个字的
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '张__';  -- 张后边只有2个字的
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 ('北京朝阳');  -- in里不能用%和_-- null 和 not null
SELECT `studentno`,`studentname` FROM `student` WHERE `address`='' OR `address`=NULL;  -- 查询地址为空的同学
SELECT `studentno`,`studentname` FROM `student` WHERE `borndate` IS NOT NULL;   -- 查询生日不为空的同学

3.4 联表查询（join语句）

  • 内连接：Inner join
    (1) 语法: select 字段 from 表1 inner join 表2 on 连接条件;
    (2) 表示在 笛卡尔积 的基础上加了连接的条件（on 后边的内容）

  • 外连接：Left join和Right join
    左外链接： 使用left join关键字，left join左边的是主表。
    右外连接： 使用right join关键字，right join右边的是主表。

(1) 外连接查询结果为主表中所有记录。如果从表中有和它匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null。

(2) 最终：外连接查询结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录。

-- 联表查询
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）SELECT * FROM `student`;
SELECT * FROM `result`;/* 思路- 分析需求，分析查询的字段来自哪些表（连接查询）- 确定使用那种连接查询？7种- 确定两个表交叉的部分：学生表中的 studentno = 成绩表的 studentno */-- inner join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.`studentno` = r.`studentno`;-- right join 要使用on语句
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno`=r.`studentno`; -- left join 要使用on语句
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno`=r.`studentno`;
  • 组合条件查询：

在连接的结果之后再进行过滤，相当于先获取连接的结果，然后使用where中的条件再对连接结果进行过滤。

语法：select 字段 from 表1 inner join 表2 on 连接条件 where 关联条件;

-- 查询缺考的同学
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` 
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
WHERE `studentresult` IS NULL;

3.5 联表查询 – 自连接

核心：一张表拆为两张一样的表。

这个例子是查询所有父栏目及其下属的子栏目，将category看为两张表，分别命名为a表和b表，这两就拆分为两张一样的表，之后再完成查询。

CREATE TABLE `category`(`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',`pid` INT(10) NOT NULL COMMENT '父id',`categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`) ) ENGINE=INNODB  AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; -- pid代表所属的父类id
INSERT INTO `category` (`categoryid`, `pid`, `categoryname`) 
VALUES ('2','1','信息技术'),
('3','1','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');-- 查询父子信息: 将一张表拆为两张表
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`;

3.6 分页和排序（limit语句和order by语句）

  • 排序：Order By语句，语法Order By 字段 排序方法
  • 升序（ASC） 、降序（DESC）
-- 排序：升序 ASC, 降序 DESC
-- 查询的结果，成绩按照降序排
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
ORDER BY `studentresult` DESC;  -- 升序
  • 分页：Limit 语句，语法： limit 起始页，页面的大小
-- 每页只显示1页数据
-- 语法：limit 起始页，页面的大小
-- 当前页的计算公式：(n-1) * pagesize（pagesize：页面大小，(n-1) * pagesize：起始页，n是当前页，总页数=数据总数/页面大小）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
ORDER BY `studentresult` DESC  -- 降序
LIMIT 0,1;

3.7 子查询（嵌套查询）

  • 在where语句中嵌套一个子查询语句：Where (select * from)
-- 查询'C语言-1'考试的（学生编号、课程编号、学生成绩），并降序排列
-- 方法一：联表查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` AS r
LEFT JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='C语言-1'
ORDER BY `studentresult` DESC-- 方法二：使用子查询（由里及外）
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno`=(SELECT `subjectno` FROM `subject` WHERE `subjectname`='C语言-1'
)
ORDER BY `studentresult` DESC

3.8 分组及过滤（group by语句和having 语句）

  • group by语句： 通过什么字段对select的结果进行分组，语法：GROUP BY 字段名
  • having语句： 对分组后的结果进行过滤，语法：having 过滤条件
-- 查询不同课程的平均分，最高分，最低分
-- 核心：根据不同课程分组
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`) > 75;  -- having 可以对分组后的结果进行过滤

补充： where语句和having语句的差别

  • where不能使用聚合函数，having中可以使用聚合函数。
  • where是一个约束声明,使用where来约束来自数据库的数据，where是在结果返回之前起作用的。
  • having是一个过滤声明，在查询返回结果集以后，对查询结果进行的过滤操作;
  • where子句在聚合前先筛选记录，也就是说作用在group by子句和having子句前，而having子句在聚合后对组记录进行筛选。
  • where和having的执行顺序：where早于group by早于having。
  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.