RDS for MySQL的DDL、DML和DQL

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 这篇文章介绍了MySQL中处理数据的几种主要操作:DDL(Data Definition Language),DML(Data Manipulation Language)和DQL(Data Query Language)。首先讲解了如何使用CREATE DATABASE, ALTER DATABASE和DROP DATABASE语句来创建、修改和删除数据库。

RDS for MySQL的DDL、DML和DQL

CREATE DATABASE语句

老王:咱们商城不是要上一批书籍吗?创建一个goods库来存这些书籍信息吧!

CREATE DATABASE IF NOT EXISTS `goods` CHARACTER SET utf8 COLLATE utf8_general_ci;
  • 当创建的数据库本身存在而且没有写明IF NOT EXISTS子句时,则创建数据库的语句会报错。
  • create_option子句指明创建的数据库的属性。
  • CHARACTER SET属性指明此数据库的默认字符集。
  • COLLATE属性指明此数据库的默认排序规则。
  • 在数据文件所在目录创建一个自己的文件目录,用来包含后续创建的表文件。

ALTER DATABASE语句

小明:老王,库我已创建好了,我听说utf8不支持表情符号?

老王:那你可以使用ALTER DATABASE语句来修改库的字符集为utf8mb4。

ALTER DATABASE `goods` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

DROP DATABASE语句

小明:老王,那如果库名创建错了或者业务变更,需要修改库名怎么操作呢?

老王:目前MySQL不支持通过SQL修改库名,需要DROP DATABASE语句把老库删掉重建。

DROP DATABASE IF EXISTS `goods`;

注意:DROP DATABASE 删掉库,库中的表也会删掉!若库中有表且有用,则需先建新库把表移过去,再删老库。

老王:库的操作你已经掌握了,接下来开始对表的操作吧!

CREATE TABLE语句

老王:在MySQL中,使用CREATE TABLE语句来创建表。你需要指定表名和列的定义,包括列名、数据类型以及约束等,结合实际存储和上一课学习的数据类型选取合适的。创建一个book_types表:

CREATE TABLE IF NOT EXISTS `book_types` (
  `type_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '书籍类型ID',
  `book_type` varchar(32) NOT NULL DEFAULT '' COMMENT '书籍类型',
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • book_types: 表示被创建的表名,默认在当前数据库下创建此表。
  • IF NOT EXISTS: 表示当相同的表名存在时,则不执行此创建语句,避免语句执行错误。
  • NOT NULL:表示需要对此字段明确数值,或者要有默认值,否则报错。
  • DEFAULT: 表示设置字段的默认值。
  • AUTO_INCREMENT:表示该字段为自增,默认是从1开始,一个表中只容许有一个,且该字段必须有key属性,不能含有DEFAULT属性,且插入负值会被当成很大的正数。
  • PRIMARY KEY:表示该字段为主键,主键字段必须唯一,必须非空,一个表中只能有一个主键,主键可以包含一个或多个字段。

小明:书籍种类表我已经创建好了,书籍信息怎么存呢?

老王:创建一张名为book的表来存储书籍信息

CREATE TABLE IF NOT EXISTS `book` (
  `book_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '书籍ID',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '书籍名称',
  `price` decimal(10,2) unsigned zerofill NOT NULL COMMENT '单价',
  `author` varchar(16) NOT NULL DEFAULT '' COMMENT '作家名称',
  `publishing_house` varchar(32) NOT NULL DEFAULT '' COMMENT '出版社',
  `type_id` int unsigned NOT NULL COMMENT '书籍类型ID',
  PRIMARY KEY (`book_id`),
  KEY `fk_typeid` (`type_id`),
  CONSTRAINT `fk_typeid` FOREIGN KEY (`type_id`) REFERENCES `book_types` (`type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='书籍信息';
  • CONSTRAINT:表示为主键、唯一键、外键等约束条件命名。
  • KEY:表示索引字段。
  • FOREIGN KEY:表示该字段为外键字段。

ALTER TABLE语句

小明:我还需要存出版日期,但是book表没有这个字段信息。

老王:这个时候就需要用到ALTER TABLE语句来增加字段了。

ALTER TABLE `book` ADD COLUMN `publishing_date` date NOT NULL COMMENT '出版日期' AFTER `publishing_house`;
  • publishing_date:增加的字段名字,后面跟字段属性。
  • AFTER:在某个字段后面增加。

老王:你还可以使用DROP TABLE语句把表删了,重新创建。

DROP TABLE语句

老王:假如表里有数据,则数据也就被删了,所以使用该语句需要谨慎操作。

DROP TABLE IF EXISTS `book`;

RENAME TABLE语句

小明:我想把表名改成books,是不是也需要把表删掉重建呢?

老王:用RENAME TABLE语句就可以,很方便。

RENAME TABLE `book` TO `books`;

老王:表的操作你已经掌握了,接下来开始增、删、改、查数据吧!

INSERT语句

老王:把咱们新上的那批书籍插入books吧!

-- ----------------------------
-- 书籍类型数据
-- ----------------------------
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (1, '计算机');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (2, '历史');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (3, '小说');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (4, '科幻');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (5, '随笔');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (6, '心理学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (7, '科学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (8, '传记');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (9, '励志');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (10, '悬疑');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (11, '哲学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (12, '语言学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (13, '人工智能');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (14, '数据分析');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (15, '数据挖掘');
-- ----------------------------
-- 书籍信息数据
-- ----------------------------
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (1, 'Python编程从入门到精通', 00000049.90, '张三', '图灵出版社', '2022-01-15', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (2, '数据结构与算法分析', 00000035.00, '李四', '清华大学出版社', '2021-09-30', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (3, '人类简史', 00000025.50, '尤瓦尔·赫拉利', '中信出版社', '2020-03-12', 2);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (4, '活着', 00000018.00, '余华', '作家出版社', '2019-05-08', 3);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (5, '白夜行', 00000022.50, '东野圭吾', '南海出版公司', '2021-11-20', 3);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (6, 'Java编程思想', 00000059.00, 'Bruce Eckel', '机械工业出版社', '2020-07-10', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (7, '三体', 00000028.80, '刘慈欣', '重庆出版社', '2018-12-18', 4);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (8, '围城', 00000015.50, '钱钟书', '人民文学出版社', '2017-10-22', 3);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (9, 'JavaScript权威指南', 00000042.50, 'David Flanagan', '电子工业出版社', '2022-02-28', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (10, '活法', 00000019.00, '梁文道', '江苏文艺出版社', '2019-09-10', 5);
  • VALUES后括号内为插入数据。
  • books表名后括号内为字段名称,两个小括号一一对应。

UPDATE语句

小明:老王,刘慈欣的《三体》出版日期需要修改一下,怎么办?

老王:用UPDATE语句即可,通过WHERE来修改符合条件的数据。

UPDATE `books` SET `publishing_date`='2016-12-18' WHERE `name`='三体' AND `author`='刘慈欣';

注意:如果不加WHERE条件,那表中所有书的出版日期都会改为2016-12-18。

DELETE语句

小明:老王,梁文道的《活法》需要下架,怎么办?

老王:用DELETE语句即可,通过WHERE来删除符合条件的数据。

DELETE FROM `books` WHERE `name`='活法' AND `author`='梁文道';

注意:如果不加WHERE条件,那表中的所有数据将被一行一行地删除。

老王:小明啊,数据的增、删、改你已经掌握了,接下来你可以根据需求去查询数据了!

SELECT语句

老王:先用一个最简单的查询语句查看书籍信息。

SELECT * FROM `books`;

老王:但是这个语句会将表中所有数据查出来,如果表中数据很多,将耗费大量资源甚至引发事故因此我们一般会用LIMIT子句加以限制。

SELECT * FROM `books` LIMIT 10;

说明: LIMIT子句后可以带两位>0的整数,第一位代表offset,第二位代表取多少行;也可以是一位>0的整数,代表取多少行。

小明:我只需要查看书名,是不是用书名的字段`name`像下面这样就可以了?

SELECT `name` FROM `books` LIMIT 10;

老王:是的,为了方便查看,你还可以用AS取个别名,如下:

SELECT `name` AS '书名' FROM `books` LIMIT 10;

小明:那如果我只想看刘慈欣的小说是不是就可以像前面学的加WHERE条件,就可以了呢?

SELECT `name` AS '书名' 
FROM `books` 
  WHERE `author`='刘慈欣'
LIMIT 10;

老王:小王你很棒啊!都学会举一反三了,真是厉害了。刚业务同学说,需要看看每类型书的最高价格,可以用下面这个语句:

SELECT `type_id`, MAX(`price`) 
FROM `books` 
GROUP BY `type_id`
LIMIT 10;

说明:GROUP BY子句代表分组,通常和聚合函数:最大值MAX、最小值MIN、平均值AVG、个数COUNT、求和SUM 配合使用。

小明:那如果要看最高价格高于25元以上类型的书籍是不是就可以用WHERE子句呢?

老王:在MySQL中,分组求最大值,再根据最大值过滤符合条件的就需要用HAVING子句,你刚才的需求用如下语句实现

SELECT `type_id`, MAX(`price`) 
FROM `books` 
GROUP BY `type_id`
  HAVING MAX(`price`) > 25
LIMIT 10;

小明:业务方需要在此基础上根据每种书最高价格,再由高到低排个序。

老王:排序就要用ORDER BY子句了,如下:

SELECT `type_id`, MAX(`price`) 
FROM `books` 
GROUP BY `type_id`
  HAVING MAX(`price`) > 25
ORDER BY MAX(`price`) DESC
LIMIT 10;

说明:默认是升序排列(ASC),指定DESC按照降序排列。

小明:老王,有个问题哈,我们查出来的类型显示都是数字,我还得去看表结构才知道是什么类型。

老王:咱们的书籍类型放在book_types表里面的,需要直接看到类型名称,就需要用到连表查询。

SELECT `bt`.`book_type`,MAX( `b`.`price` ) 
FROM `books` AS `b`
  LEFT JOIN `book_types` AS `bt` ON `b`.`type_id` = `bt`.`type_id` 
GROUP BY `b`.`type_id` 
HAVING MAX( `b`.`price` ) > 25 
ORDER BY MAX( `b`.`price` ) DESC 
LIMIT 10;
  1. 了解表之间的关系: 在执行连表查询之前,你需要了解表之间的关系,包括哪些列用作关联键。通常,表之间的关系由外键(foreign key)来定义,它们用于将一张表中的数据与另一张表中的数据关联起来。
  2. 选择适当的连接类型: 连表查询可以使用不同的连接类型,包括内连接(INNER JOIN)、左连接(LEFT JOIN)、右连接(RIGHT JOIN)和全连接(FULL JOIN)。你需要根据查询需求选择适当的连接类型,以确保获取正确的数据。
  3. 注意性能: 连表查询可能会影响性能,特别是当涉及大量数据时。确保表上有适当的索引可以加速查询,同时避免不必要的连接和复杂的查询条件。
  4. 使用别名: 当涉及多个表时,使用表的别名(alias)可以使查询更可读。别名是对表的简短引用,使查询语句更加清晰。

小明:这连表查询语句看起来很简洁,但我不是很理解。

老王:通常子查询的方式可以简化复杂JOIN表连接查询,提高了语句的可读性。

SELECT `book_type`, MAX(`price`)
FROM `books`
WHERE `type_id` IN (
    SELECT `type_id`
    FROM `books`
    WHERE `price` > 25
)
GROUP BY `book_type`
ORDER BY MAX(`price`) DESC
LIMIT 10;

小明:子查询看起来容易理解多了,那我是不是以后直接用子查询就可以了呢?

老王:子查询在这种简单查询场景下可读性确实好,但是随着表关系复杂度增加,多层子查询嵌套可读性就会下降,而且在处理大数据量时,性能可能开销会更大。连表查询和子查询各有其用武之地,你应该根据具体的查询需求来选择合适的方法。在实际应用中,通常需要权衡查询的性能、可读性和灵活性,并选择最合适的查询方式。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
17天前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
|
18天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
60 2
|
17天前
|
SQL 关系型数据库 MySQL
|
2月前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
399 15
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
1月前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
61 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
1月前
|
Java 关系型数据库 MySQL
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
这篇文章是关于如何使用Spring Boot框架通过JdbcTemplate操作MySQL数据库的教程。
25 0
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
|
1月前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
100 4
|
1月前
|
数据可视化 关系型数据库 MySQL
【IDEA】配置mysql环境并创建mysql数据库
【IDEA】配置mysql环境并创建mysql数据库
94 0
|
3月前
|
SQL 存储 关系型数据库
"MySQL增列必锁表?揭秘InnoDB在线DDL,让你的数据库操作飞一般,性能无忧!"
【8月更文挑战第11天】在数据库领域,MySQL凭借其稳定高效的表现深受开发者喜爱。对于是否会在给数据表添加列时锁表的问题,MySQL的行为受版本、存储引擎等因素影响。从5.6版起,InnoDB支持在线DDL,可在改动表结构时保持表的可访问性,避免长时间锁表。而MyISAM等则需锁表完成操作。例如,在使用InnoDB的表上运行`ALTER TABLE users ADD COLUMN email VARCHAR(255);`时,通常不会完全锁表。虽然在线DDL提高了灵活性,但复杂操作或大表变更仍可能暂时影响性能。因此,进行结构变更前应评估其影响并择机执行。
71 6
|
3月前
|
SQL 关系型数据库 MySQL
MySQL】-DQL(基本、条件、分组、排序、分页)详细版
通过这些查询方法,你可以高效地检索、分析和组织MySQL数据库中的数据,以满足各种应用需求。实践中,理解这些SQL语句的基础知识以及它们如何组合起来进行复杂的数据操作是至关重要的。
45 1