RDS for MySQL的DDL、DML和DQL

2024-05-20 136

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，高可用系列 2核4GB

云数据库 RDS PostgreSQL，高可用系列 2核4GB

简介： 这篇文章介绍了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;

了解表之间的关系： 在执行连表查询之前，你需要了解表之间的关系，包括哪些列用作关联键。通常，表之间的关系由外键（foreign key）来定义，它们用于将一张表中的数据与另一张表中的数据关联起来。
选择适当的连接类型： 连表查询可以使用不同的连接类型，包括内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全连接（FULL JOIN）。你需要根据查询需求选择适当的连接类型，以确保获取正确的数据。
注意性能： 连表查询可能会影响性能，特别是当涉及大量数据时。确保表上有适当的索引可以加速查询，同时避免不必要的连接和复杂的查询条件。
使用别名： 当涉及多个表时，使用表的别名（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;

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

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

RDS for MySQL的DDL、DML和DQL

RDS for MySQL的DDL、DML和DQL

CREATE DATABASE语句

ALTER DATABASE语句

DROP DATABASE语句

CREATE TABLE语句

ALTER TABLE语句

DROP TABLE语句

RENAME TABLE语句

INSERT语句

UPDATE语句

DELETE语句

SELECT语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

RDS for MySQL的DDL、DML和DQL

RDS for MySQL的DDL、DML和DQL

CREATE DATABASE语句

ALTER DATABASE语句

DROP DATABASE语句

CREATE TABLE语句

ALTER TABLE语句

DROP TABLE语句

RENAME TABLE语句

INSERT语句

UPDATE语句

DELETE语句

SELECT语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像