RDS for MySQL的DDL、DML和DQL

本文涉及的产品
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;

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

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
13小时前
|
关系型数据库 MySQL 数据库
【MySQL】MySQL数据库的delete from table和truncate table之间的区别
【MySQL】MySQL数据库的delete from table和truncate table之间的区别
11 1
|
19小时前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第三篇(MySQL性能优化)
MySQL数据库进阶第三篇(MySQL性能优化)
|
1天前
|
关系型数据库 MySQL API
实时计算 Flink版操作报错合集之同步MySQL数据到另一个MySQL数据库,第一次同步后源表数据发生变化时目标表没有相应更新,且Web UI中看不到运行的任务,该怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
4天前
|
SQL 关系型数据库 MySQL
MySQL周内训参照2、DDL与DML语句
MySQL周内训参照2、DDL与DML语句
7 1
|
4天前
|
SQL 存储 关系型数据库
MySQL存储过程——Baidu Comate智能代码助手添加20条DML语句——测试索引效果
MySQL存储过程——Baidu Comate智能代码助手添加20条DML语句——测试索引效果
5 0
|
4天前
|
SQL 关系型数据库 MySQL
MySQL数据库子查询练习——DDL与DML语句(包括引入视频)
MySQL数据库子查询练习——DDL与DML语句(包括引入视频)
7 1
|
4天前
|
SQL 安全 关系型数据库
MySQL DML语句insert全表数据添加语句以及注意事项
MySQL DML语句insert全表数据添加语句以及注意事项
11 0
|
5天前
|
SQL 关系型数据库 MySQL
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)
|
12天前
|
关系型数据库 MySQL Linux
MySQL数据库——MySQL8.0.26-Linux版安装
MySQL数据库——MySQL8.0.26-Linux版安装
52 1
|
12天前
|
存储 安全 关系型数据库