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语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像