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;
小明:子查询看起来容易理解多了,那我是不是以后直接用子查询就可以了呢?
老王:子查询在这种简单查询场景下可读性确实好,但是随着表关系复杂度增加,多层子查询嵌套可读性就会下降,而且在处理大数据量时,性能可能开销会更大。连表查询和子查询各有其用武之地,你应该根据具体的查询需求来选择合适的方法。在实际应用中,通常需要权衡查询的性能、可读性和灵活性,并选择最合适的查询方式。