MySQL基础2（外键约束详细讲述、DML（添加、修改、删除）、DQL查询语言（去重、WHERE、联表查询（JOIN）、自连接、排序分页、子查询、过滤分组））

最新推荐文章于 2024-05-24 23:35:22 发布
最新推荐文章于 2024-05-24 23:35:22 发布
阅读量566 收藏 2
点赞数 1
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45677119/article/details/104255103
版权

MySQL基础

MySQL数据管理

外键约束
  1. 什么是外键？
    外键是相对于主键说的，是建立表之间的联系的必须的前提，比如有两张表，student（学生）表和grade（年级）表，student 中 id 是主键，而 gradeid 是依赖于 student 中的 id ，那么 grade 中的 id 就是 student 的外键；我们可以通过外键使两张表进行关联。
  2. 外键的作用是什么？
    使两张表形成关联，外键只能引用外表中的列的值，可以使得两张表关联，保证数据的一致性和实现一些级联操作。
    总结：
    1）为了一张表记录的数据不要太过冗余；
    2）保持数据的一致性、完整性。
  • CREATE TABLE 时：
-- 创建grade表
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

-- 学生表的 gradeid 字段，去引用年级表的 gradeid 
-- 定义外键key 
-- 给这个外键添加约束 （执行引用）  references 引用
CREATE 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(10) NOT NULL COMMENT '学生的年级',   
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',   
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',   
PRIMARY KEY(`id`),   
KEY `FK_gradeid` (`gradeid`),   
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8

可视化查看表可以看到：
在这里插入图片描述

  • ALTER TABLE 时：
-- 当我们的student表已经存在时，添加外键约束
CREATE 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(10) 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`);

和上面结果一样。

  • 注意：
    加入外键约束后，没删除student表前，是无法删除grade表的；
    一般也不推荐使用外键约束，阿里巴巴的Java规范下是：强制不得使用外键，一切外键在应用层解决，原因就是在DELETE或者UPDATE时都要考虑外键约束，导致开发变得痛苦很多！
DML语言

DML：数据操纵语言（Data Manipulation Language），它可以实现对数据库的基本操作。

开始我们创建一个school数据库，里面包含了以下的表，接下来的操作都是在此数据库里进行的
在这里插入图片描述

  1. 添加（INSERT）
    语法：INSERT INTO 表名([字段名1,字段2,字段3,...]) VALUES ('值1'),('值2'),('值3'，....)
-- 给上面的student表插入数据
INSERT INTO `student`(`name`) VALUES ('张三')
INSERT INTO `studnet` VALUES('李四') -- 如果不写表的字段，他就会一一匹配，这条语句就会报错！
-- 插入多个字段 
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','123456','男')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('李四','123456','男'),('王五','aaaaaa','男')
  • 注意：
    1）字段和字段之间使用英文逗号隔开；
    2）字段是可以省略的，但是后面的值必须要要一一对应，不能缺少数据；
    3）可以同时插入多条数据，VALUES 后面的值，需要使用逗号隔开。
  1. 修改（UPDATE）
    语法：UPDATE 表名 SET colnum_name = value,[colnum_name = value,....] WHERE [条 件]
-- 修改student表name字段
UPDATE `student` SET `name`='小明' WHERE id = 1;
-- 不指定条件的情况下，会改动所有表 
UPDATE `student` SET `name`='小红';
-- 修改多个属性,逗号隔开 
UPDATE `student` SET `name`='小强',`email`='123456@qq.com' WHERE id = 1;
  • 注意：
    1）WHERE 条件子句 ：id 等于某个值，或大于某个值，表示在某个区间内修改；
    2）colnum_name 是数据库的列，尽量带上`` ；
    3）条件，筛选的条件，如果没有指定，则会修改所有的列；
    4）value，是一个具体的值，也可以是一个变量；
    5）多个设置的属性之间，使用英文逗号隔开。

通过多个条件定位数据

UPDATE `student` SET `name`='小强' WHERE `name`='张三' AND sex='男'
  1. 删除（DELETE）
    语法：DELETE FROM 表名 [WHERE 条件]
-- 删除数据 （这样写，会全部删除） 
DELETE FROM `student`;
-- 删除指定数据 
DELETE FROM `student` WHERE id = 1;

完全清空一个数据库表（TRUNCATE）

--清空 student 表 
TRUNCATE `student`;

DELETE 和 TRUNCATE：
相同点：都能删除数据，都不会删除表结构
不同点： DELETE ，不会影响自增；TRUNCATE ，重新设置自增列，计数器会归零 。

DQL语言

DQL：数据查询语言（Data Query Language，所有的查询操作都用它，数据库中最核心的语言，最重要的语句，使用频率最高的语句。

  1. 指定查询字段
-- 查询全部的学生  SELECT 字段 FROM 表 
SELECT * FROM student
-- 查询指定字段 
SELECT `StudentNo`,`StudentName` FROM student
-- 给查询结果赋值一个新的名字  AS  可以给字段起别名，也可以给表起别名 
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s
-- 函数  Concat（a，b），给查询结果拼接一个字符串 
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student

有的时候，列名字不是那么的见名知意，所以我们赋值一个新的名字，语法：字段名 AS 别名 表名 AS 别名

  1. 去重 （DISTINCT）
    去除SELECT 查询出来的结果中重复的数据，重复的数据只显示一条
SELECT DISTINCT `StudentNo` FROM result
  1. 数据库的列（表达式）
-- 查询系统版本 （函数）
SELECT VERSION()  
-- 用来计算  （表达式） 
SELECT 100*3-1 AS 计算的结果为  
-- 查询自增的步长  (变量)
SELECT @@auto_increment_increment 
-- 给student表的考试成绩都 + 1分 
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result
  1. WHERE条件子句
    用来检索数据中 符合条件 的值
    MySQL官网的函数和运算符说明：点击这里

简单说几个：

符号描述语法
<> 或 !=不等于id <> 3、id != 3
BETWEEN … and …在某个范围内
AND、&&逻辑与a and b 、 a&&b
OR逻辑或a or b
Not ！逻辑非not a ！a
IS NULL如果操作符为 NUll, 结果为真a is null
IS NOT NULL如果操作符不为 null，结果为真a is not null
BETWEEN若a 在 b 和c 之间，则结果为真a between b and c
LIKESQL 匹配，如果a匹配b，则结果为真a like b
Ina在a1，或者a2…. 其中的某一个值中，结果为真a in (a1,a2,a3….)
-- 查询考试成绩在 95~100 分之间 
SELECT studentNo,`StudentResult` FROM result 
WHERE StudentResult>=95 AND StudentResult<=100
-- 模糊查询（区间） 
SELECT studentNo,`StudentResult` FROM result 
WHERE StudentResult BETWEEN 95 AND 100
-- 查询学号不为1000的学生成绩 
SELECT studentNo,`StudentResult` FROM result 
WHERE studentNo!=1000;
SELECT studentNo,`StudentResult` FROM result 
WHERE NOT studentNo = 1000

模糊查询：

-- 查询姓张的同学 
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName 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 '%坤%'
-- 查询 1001,1002,1003号学生
SELECT `StudentNo`,`StudentName` FROM `student` 
WHERE StudentNo IN (1001,1002,1003);
-- 查询地址为北京的学生 
SELECT `StudentNo`,`StudentName` FROM `student` 
WHERE `Address` IN ('北京');
-- 查询地址为空的学生 null  '' 
SELECT `StudentNo`,`StudentName` FROM `student` 
WHERE address=''  OR address IS NULL
-- 查询有出生日期的同学，不为空 
SELECT `StudentNo`,`StudentName` FROM `student` 
WHERE `BornDate` IS NOT NULL
-- 查询没有登记出生日期的同学，为空 
SELECT `StudentNo`,`StudentName` FROM `student` 
WHERE `BornDate` IS NULL
  • 注意：
    WHERE IN()查询时，IN()里面是具体的值，如果地址是北京市海淀区，当你输入WHERE Address IN ('北京')，结果是查询不到的，只能采用WHERE Address LIKE '%北京%'。
  1. 联表查询（JOIN）
    LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法
    在这里插入图片描述
  • 思路：
    1）分析需求，分析查询的字段来自哪些表？并进行连接查询；
    2）确定使用上图的哪种连接查询？
    3）确定交叉点（这两个表中哪个数据是相同的；
    4）判断的条件
-- INNER JOIN
SELECT s.studentNO,studentName,SubjectNo,StudentResult 
FROM student AS s 
INNER JOIN result AS r 
ON s.studentNO = r.studentNO
-- Right JOIN 
SELECT s.studentNO,studentName,SubjectNo,StudentResult 
FROM student s 
RIGHT JOIN result r 
ON s.studentNO = r.studentNO
-- Left JOIN
SELECT s.studentNO,studentName,SubjectNo,StudentResult 
FROM student s 
LEFT JOIN result r 
ON s.studentNO = r.studentNO
  • 注意：
JOIN描述
INNER JOIN如果表中有至少一个匹配，则返回行
LEFT JOIN即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN即使左表中没有匹配，也从右表返回所有的行
  • 总结：
    联表查询思路：
    1）首先确定要查询哪些数据：SELECT ...
    2）从哪几个表中查 FROM 表 XXX JOIN 连接的表 on 交叉条件
    3）假设存在一种多张表查询，先查询两张表，然后再慢慢增加。
  1. 自连接
    自己的表和自己的表连接，将一张表拆为两张一样的表即可

假设一张表：

categoryidpidcategoryName
21水果
31蔬菜
43土豆
51肉类
63青菜
75猪肉
82西瓜

拆分这张表：
表1：

categoryidcategoryName
2水果
3蔬菜
5肉类

表二：

pidcategoryidcategoryName
34土豆
28西瓜
36青菜
57猪肉

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

父类子类
水果西瓜
蔬菜土豆、青菜
肉类猪肉
-- 通过别名将一张表看作两个一摸一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目' 
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`
  1. 排序和分页
  • 排序（ORDER BY）
-- 升序 ASC ， 降序DESC 
ORDER BY StudentResult ASC、DESC
  • 分页（LIMIT）
    缓解数据库压力，给人的体验更好
    语法： LIMIT (查询起始下标，pageSize)
第一页  LIMIT 0,5      （1-1）*5 
第二页  LIMIT 5,5      （2-1）*5 
第三页  LIMIT 10,5     （3-1）*5 
第N页   LIMIT 0,5      （n-1）* pageSize，pageSize 
pageSize：页面大小
(n-1)* pageSize:起始值
n ：当前页 
数据总数/页面大小 = 总页数
  1. 子查询
    在where语句中嵌套一个子查询语句，查询高数考试学生的成绩、学号，成绩降序排序
-- 方式一： 使用连接查询 
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult` 
FROM `result` r 
INNER JOIN `subject` sub 
ON r.SubjectNo = sub.SubjectNo 
WHERE SubjectName = '高数' 
ORDER BY StudentResult DESC

-- 方式二： 使用子查询（由里及外） -- 查询所有数据库结构-1 的学生学号 
SELECT `StudentNo`,`SubjectNo`,`StudentResult` 
FROM `result` WHERE SubjectNo = (      
SELECT SubjectNo FROM `subject`       
WHERE SubjectName = '高数' 
) ORDER BY StudentResult DESC
  1. 过滤分组
-- 通过什么字段来分组
GROUP BY xxx  
-- 过滤分组后的信息，需要满足的条件，次要条件
HAVING xxx

//下篇再见…谢谢

Tigirs
关注
专栏目录
MySql 【DDL, DML， DQL， 约束（主键，外键）】
weixin_41431242的博客
09-20 851
-------------------------------------------------------------------------------------- SQL语句： DDL 对数据库，表，列进行操作 create drop alter show 数据库操作 crub 创建（增）create * 创建数据库： create database 库名称; * 创建数据库，判断不存在，再创建： create database if ...
DML语言（数据库操作语言）和DQL语言（数据库查询语言）
weixin_57012447的博客
09-07 650
DML语言（数据库操作语言）和DQL语言（数据库查询语言）
MySQL删除表的时候忽略外键约束的简单实现
12-16
删除表不是特别常用，特别是对于存在外键关联的表，删除更得小心。但是在开发过程中，发现Schema设计的有问题而且要删除现有的数据库中所有的表来重新创建也是常有的事情；另外在测试的时候，也有需要重新创建数据库的所有表。当然很多自动化工具也可以做这样的事情。 删除表的时候有时会遇到这样的错误消息： ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails 这是因为你尝试删除的表中的字段被用作了其他表的外键，因此在删除这个表（父表）之前必须先删除具有外键的表（子表）。也就是说，
SQL语言分类DDL、DML、DQL、TCL、DCL
Duke的博客
12-10 8291
关系型数据库的SQL语句都可以分为4大类：1. DDL（数据定义语言）2. DML（数据操纵语言）3. DQL（数据查询语言）4. TCL（事务处理语言）5. DCL（数据控制语言）
mysql删除外键约束
Ai_Answer_zr的博客
05-17 5714
1.通过数据库创建表的语句查看外键约束名 SHOW CREATE TABLE movie -- 建表语句如下 CREATE TABLE `movie` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) DEFAULT NULL, `filename` VARCHAR(255) DEFAULT NULL, `videoname` VARCHAR(255) DEFAULT NULL, `categoryid` INT(1
MySQL删除外键约束
天高任鸟飞
07-12 8084
在开发中，根据需要解除两个表之间的关联，需要删除外键约束 Alter table 表名 drop foreign key 外建名；删除student表中外键约束可以看出，表student中的外键已经删除
Mysql基础篇（一）之DDL、DML、DQL、DCL
yun3k_com的博客
06-29 743
一. SQL语句分类 SQL语句，根据其功能，主要分为四类：DDL、DML、DQL、DCL。 DDL Data Definition Language 数据定义语言，用来定义数据对象(数据库，表，字段) DML Data Manipulation Language 数据操作语言，用来对数据库表中的数据进行增删改 DQL Data Query Language DCL Data Control Language （数据控制语言），用来管理数据库用户、控制数据库的访问权限。
数据库-DML（数据操作语言）和DQL（数据查询语言）
南劲竹的博客
09-19 1992
MySQL的存储引擎 什么是存储引擎？ 关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式。有的表简单，有的表复杂，有的表根本不用来存储任何长期的数据，有的表读取时非常快，但是插入数据时很差；而我们在实际开发过程中，就可能需要各种各样的表，不同的表，就意味着存储不同类型的数据，数据的处理上也会存在着差异，那么。对于MySQL来说，它提...
mysql_(DDL,DML,DQL,子查询,多表设计)
SDWESCES的博客
09-30 370
文章目录1. mysql数据库1.1 数据库的操作语言1.2 DDL1.2.1 创建数据库1.2.2 删除数据库1.2.3 修改数据库 1. mysql数据库 1.1 数据库的操作语言 增 删 改 查 mysql(数据库软件名字) sqlyog(客户端功能) sql语言(结构化查询语言) ● 结构化查询语言(Structured Query Language)简称SQL，是一种特殊 目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以 及查询、更新和管理关系数据库系统. SQL优点： ① 不是某
MySQL删除外键约束问题。
热门推荐
中年人
05-26 1万+
当我们在一个表中添加字段约束的时候： ALTER TABLE product ADD CONSTRAINT product_fk FOREIGN KEY(category_id) REFERENCES category(id); 会认为我的外键约束名称为product_fk, 然后当我们想删除时： ALTER TABLE product DROP FOREIGN KEY product_...
Mysql外键约束的创建与删除
侯小啾技术博客
02-20 7888
在Mysql中使用外键，可以有效地将多个表格的数据“联动”起来，打破独立状态，互相影响。本文简要介绍Mysql外键约束的创建与删除。
MySQL--删除表的外键约束，简单易懂。
nuhao的博客
11-07 4966
我们曾在第一章中讲过外键的作用，以及如何创建一个表的外键。建立了外键我们就建立起了两张表的关联关系，那如果我想删除主表呢？为了确保数据库的正确性，我们必须先解除两个表之间的关联关系，那就是删除外键约束啦！让我们先来看看删除外键约束的语法规则。举个例子： 我们先建立了一个主表。然后建立一个有外键约束的子表。不见了，外键约束删除成功！上述语句成功执行后，在表。的外键约束，外键字段为。
mysql删除外键约束的探究
weixin_37627774的博客
07-23 272
如何彻底删除外键约束添加外键删除外键参考链接 今天就跟修改表这个操作干上了，虽然用的并不多，但是平时自己做实验的时候还是很有用滴。比如说这个添加和删除外键。 添加外键 分为两步: 1. 添加相应列 ALTER TABLE goods ADD stuid INT; 2. 添加约束 ALTER TABLE goods ADD CONSTRAINT fk_stu_goo FOREIGN KEY(stuid) REFERENCES stuinfo(id); - 附加创建goods表的语句 DROP TAB
MySQL中如何删除有外键约束的数据?
Luke Ewin的博客
10-18 2726
MySQL中如何删除有外键约束的数据？阅读本博文你将会了解到如何删除有外键约束的数据。
SQL语句分类DDL、DML、DQL、DCL语句
红目香薰
05-29 1212
DDL（Data Ddefinition Language） 数据定义语言，用来定义数据库对象：库、表、列 代表性关键字：CREATE,DROP,ALTER 对逻辑结构等有操作的语句，其中包括表结构，视图和索引。 MySQL复习资料(二)——MySQL-DDL语句 DML（Data Manipulation Language）数据操作语言，用来定义数据库记录 代表性关键字:INSERT,UPDATE,DELETE MySQL复习资料(三)——MySQL-DML语句 DQL （Data
mysql外键约束删除数据问题
jiangyunlovec的专栏
08-29 4085
背景 在删除有外键的数据库表经常会出现错误，提示有外键约束 解决办法：    1：常规 按照提示的消息一条一条找过去，找到最小的一条，然后倒着删除如: mysql&gt; delete from base_operation where id='4028db8144881f0e01448830a3070020'; ERROR 1451 (23000): Cannot delete or...
删除MySQL中所有表的外键
最新发布
哈
05-24 454
原理查询schema中所有外键名称然后拼接生成删除语句。
MySQL基础教程：DDL/DML/DQL/DCL全面解析
本文档是关于MySQL的基本语法和常用SQL语句的全面总结，涵盖了DDL（数据库定义语言）、DML（数据库操作语言）、DQL（数据库查询语言）和DCL（数据库控制语言）。主要知识点包括数据库的管理、表的操作、数据的增删改...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值