场景简介

本实验将提供一台阿里云RDS MySQL数据库。完成本教程操作后，您可以使用数据管理服务DMS（Data Management Service）连接到RDS MySQL实例，然后进行数据表的CRUD操作。

背景知识

本场景主要涉及以下云产品和服务：

• 云数据库RDS

  阿里云关系型数据库RDS（Relational Database Service）是一种安全稳定可靠、高性价比、可弹性伸缩的在线数据库服务。RDS支持什么是RDS MySQL、什么是RDS SQL Server、什么是RDS PostgreSQL和什么是RDS MariaDB引擎，并且提供了容灾、备份、恢复、监控、迁移等方面的全套解决方案，帮助您解决数据库运维的烦恼。

• 数据管理服务DMS

  数据管理服务（Data Management Service，简称DMS）支持MySQL、SQL Server、PostgreSQL、Oracle、MongoDB、Oceanbase等关系型数据库和NoSQL数据库的管理。它是一种集数据管理、结构管理、用户授权、安全审计、数据趋势、数据追踪、BI图表、性能与优化和服务器管理于一体的数据管理服务。用户使用数据管理服务实现易用的数据库和服务器统一管理入口，让数据更安全、管理更高效、数据价值更清晰。

前提条件

云起实验室将在您的账号下开通本次实操资源，资源按量付费，需要您自行承担本次实操的云资源费用。

进入实操前，请确保阿里云账号满足以下条件：

• 已通过实名认证并且账户余额大于100。

• 云资源产生的费用需您自行承担，云起实验室不会向您征收额外费用。

• 所有实验操作将保留至您的账号，请谨慎操作。

• 实操结束后，您可以选择继续付费保留资源，或参考手册自动/手动释放资源。

创建实验资源

1. 在实验页面，勾选我已阅读并同意《阿里云云起实践平台服务协议》和我已授权阿里云云起实践平台创建、读取及释放实操相关资源后，单击开始实操。

2. 创建资源需要5分钟左右的时间，请您耐心等待。

3. 在云产品资源列表，您可以查看本场景涉及的云产品资源信息。

   

数据库启动与连接

1. 在云产品资源列表的云数据库RDS区域，单击管理。

   

2. 在左侧导航栏中，单击账号管理。

   

3. 在用户账号页面，单击创建账号。

   

4. 在创建账号面板中，参考说明配置账号信息，单击确定。

   参数说明：

   • 数据库账号：自定义数据库账号名称，例如test_user。

   • 账号类型：选择高权限账号。

   • 密码：自定义账号密码。

   • 确认密码：再次输入账号密码。

5. 在左侧导航栏中，单击数据库管理。

   

6. 在数据库管理页面。单击创建数据库。

   

7. 在创建数据库面板中，参考说明配置数据库信息，单击创建。

   参数说明：

   • 数据库（DB）名称：输入数据库名称，例如user_db。

   • 支持字符集：默认设为utf8。

   • 备注说明：非必填。用于备注该数据库的相关信息，便于后续数据库管理，最多支持256个字符。

     

8. 在云产品资源列表的云数据库RDS实例区域，单击登录。

   

9. 在登录实例对话框中，填写RDS高权限数据库账号和密码，然后单击登录。

   说明

   如果报错“您暂未获取当前数据库实例的登录权限”，请单击点此申请，并由主账号完成审批。

   

   返回如下页面，表示成功登录RDS实例，进入数据管理服务系统DMS。

   

数据库表操作

本小节主要介绍对数据库表的基本操作，其中包括新增、删除、更新和重命名等。

1. 在SQLConsole窗口，在左侧选择数据库实例>已登录实例>RDS实例，双击目标数据库名称user_db，可以看到自建数据库所有库、表数据已经完成迁移。

   说明

   如果找不到user_db库，请右键单击RDS实例ID，选择刷新/同步数据字典。

   

2. 使用CREATE TABLE语句创建一个数据表。将以下语句复制到SQLConsole页签中，然后单击执行。

   CREATE TABLE `USER` (
     `user_id` int(128) NOT NULL AUTO_INCREMENT,
     `name` varchar(64) NOT NULL,
     `age` int(11) DEFAULT NULL,
     PRIMARY KEY (`user_id`)
   ) ENGINE=InnoDB
   DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

   语句解析：

   • CREATE TABLE `USER`：新表的名字，表名称在关键字CREATE TABLE后给出。

   • `name` varchar(64) NOT NULL,：列名name，类型varchar，长度限制64，此列不能为空。

   • PRIMARY KEY (`user_id`)：设置USER表主键为user_id。

   • `user_id` int(128) NOT NULL AUTO_INCREMENT,：AUTO_INCREMENT通常用于主键，表示主键自增，数值会自动+1。

   执行成功后，单击图标查看创建的数据表。

   

3. 使用ALTER TABLE语句更新数据表。将以下语句复制到SQLConsole页签中，然后单击执行。

   在年龄age列后面，为USER表新增一列性别sex，0代表女，1代表男。

   ALTER TABLE user ADD COLUMN sex tinyint(1) NOT NULL COMMENT 'sex,woman:0,man:1' AFTER `age`;

4. 使用RENAME TABLE语句对数据表重命名。将以下语句复制到SQLConsole页签中，然后单击执行。

   修改表名user为student。

   RENAME TABLE USER TO student;

   语句格式：

   RENAME TABLE table_name_a TO table_name_b;

5. 使用DROP TABLE语句删除数据表。将以下语句复制到SQLConsole页签中，然后单击执行。

   说明

   此操作不能撤销，请谨慎操作。

   删除学生表。

   DROP TABLE student;

   语句格式：

   DROP TABLE table_name;

数据操作

本小节主要为大家介绍MySQL中常用的数据查询、删除、更新、插入等基本操作。

1. 使用CREATE TABLE语句创建一个数据表。将以下语句复制到SQLConsole页签中，然后单击执行。

   CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `name` varchar(32) DEFAULT NULL COMMENT 'name',
    `age` int(11) DEFAULT NULL COMMENT 'age',
    `address` varchar(32) DEFAULT NULL COMMENT 'address',
    `sex` tinyint(1) DEFAULT NULL COMMENT 'sex,woman:0,man:1',
    PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8

   执行成功后，单击图标查看创建的数据表。

   

2. 插入数据。

   插入语句说明：

   • table_name(col_name,...)中的字段名列表必须和values(expr,...)字段值列表一致。

   • 如果数据是字符型，必须使用单引号或者双引号。

   • 被AUTO_INCREMENT标记的自动递增的主键，在插入数据时，可以不设置值。

   将以下SQL语句复制到SQLConsole页签中，然后单击执行。

   INSERT INTO USER
          (name, age, address, sex)
   VALUES
          ('zhangsan', 21, 'jiangxi', 0),
          ('lisi', 22, 'hubei', 0),
          ('wangwu', 23, 'hunan', 0),
          ('lilei', 24, 'henan', 1),
          ('hanmeimei', 25, 'hebei', 1),
          ('xiaoming', 26, 'shandong', 1),
          ('xiaoli', 27, 'shanxi', 1) ;

   

3. 查询数据。

   查询语句格式：

   SELECT col_name,...
     FROM table_name
    WHERE where_condition
    GROUP BY col_name,...
   HAVING where_condition
    ORDER BY col_name,...
    LIMIT offset,row_count

   将以下SQL语句复制到SQLConsole页签中，然后单击执行。

   SELECT id,name,age,address,sex FROM user;

   

4. 更新数据。

   更新语句格式：

   UPDATE table_name SET col_name=expr,... WHERE where_condition

   将以下SQL语句复制到SQLConsole页签中，然后单击执行。

   UPDATE `user` SET `age` = 28,`address` = 'sichuan' WHERE `name` = 'xiaoming' ;

   

5. 删除数据。

   删除语句格式：

   DELETE FROM table_name WHERE where_condition

   将以下SQL语句复制到SQLConsole页签中，然后单击执行。

   DELETE FROM `user` WHERE `name` = 'xiaoming' ;

   

函数操作

本小节主要为大家介绍MySQL中常用的内置函数。

1. 使用CREATE TABLE语句创建一个数据表。将以下语句复制到SQLConsole页签中，然后单击执行。

   CREATE TABLE student_score(sid INT PRIMARY KEY NOT NULL, sname VARCHAR(30), sage INT, ssex VARCHAR(8), score INT(11));
   insert into `student_score`(`sid`, `sname`, `sage`, `ssex`,`score`)
   values (1001, 'xiaohua', 17,'0', 75),
    (1002, 'xiaohong', 18,'0', 80),
    (1003, 'wangwu', 18,'1', 90),
    (1004, 'lisi', 17,'1', 68),
    (1005, 'zhangsan', 19,'1', 73),
    (1006, 'xiaohei', 19,'1', 100),
    (1007, 'xiaoma', 20,'0', 77),
    (1008, 'xiaoli', 17,'1', 82),
    (1009, 'xiaobai', 19,'0', 88),
    (1010, 'wentong', 18,'0', 53);

2. AVG()函数。

   计算score字段的平均数，将以下语句复制到SQLConsole页签中，然后单击执行。

   SELECT avg(score) FROM student_score;

   执行结果如下。

   

3. COUNT()函数。

   计算student_score表中的行数，将以下语句复制到SQLConsole页签中，然后单击执行。

   SELECT COUNT(*) FROM student_score;

   执行结果如下。

   

4. MAX()函数。

   计算score字段中的最大值，然后单击执行。

   SELECT MAX(score) FROM student_score;

   执行结果如下。

   

5. MIN()函数。

   计算score字段中的最小值，然后单击执行。

   SELECT MIN(score) FROM student_score;

   执行结果如下。

   

6. SUM()函数。

   计算score字段的总和，然后单击执行。

   SELECT SUM(score) FROM student_score;

   执行结果如下。

   

组合查询

本小节主要介绍如何使用GROUP BY、HAVING和ORDER BY等进行分组查询。

1. 使用CREATE TABLE语句创建数据表。将以下语句复制到SQLConsole页签中，然后单击执行。

   CREATE TABLE `student` (
    `sid` int(11) NOT NULL,
    `sname` varchar(30) DEFAULT NULL,
    `sage` int(11) DEFAULT NULL,
    `ssex` varchar(8) DEFAULT NULL,
    PRIMARY KEY (`sid`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   insert into `student`(`sid`,`sname`,`sage`,`ssex`) values
   (1005,'xiaohua',19,'0'),
   (1004,'xiaohong',18,'0'),
   (1003,'wangwu',18,'1'),
   (1002,'lisi',17,'1'),
   (1001,'zhangsan',18,'1');
   CREATE TABLE `sc` (
    `sid` int(11) NOT NULL,
    `cid` int(11) NOT NULL,
    `score` int(11) DEFAULT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   insert into `sc`(`sid`,`cid`,`score`) values
   (1001,101,75),
   (1001,102,85),
   (1002,101,65),
   (1002,102,95),
   (1003,101,65),
   (1003,102,95),
   (1004,101,80),
   (1004,102,80),
   (1005,101,75),
   (1005,102,85);
   CREATE TABLE `course` (
    `cid` int(11) NOT NULL,
    `cname` varchar(30) DEFAULT NULL,
    `tid` int(11) DEFAULT NULL,
    PRIMARY KEY (`cid`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   insert into `course`(`cid`,`cname`,`tid`) values
   (102,'yuwen',2),
   (101,'shuxue',1);
   CREATE TABLE `teacher` (
    `tid` int(11) NOT NULL,
    `tname` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`tid`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   insert into `teacher`(`tid`,`tname`) values
   (2,'liulaoshi'),
   (1,'wanglaoshi');

   执行成功后，单击图标查看创建的数据表。

   

3. 查询所有同学的学号和成绩。 将以下SQL语句复制到SQLConsole页签中，然后单击执行。

   # 使用WHERE s. sid = sc.sid 来消除笛卡尔积。
   SELECT s.sid ,s.sname ,c.score FROM student AS s ,sc AS c WHERE s.sid = c.sid ;

   执行结果如下。

   

4. 查询语文成绩在80以上同学。 将以下SQL语句复制到SQLConsole页签中，然后单击执行。

   SELECT a.score,
          b.cname,
          s.sname
     FROM sc as a,
          course AS b,
          student AS s
    WHERE a.cid= b.cid
      AND s.sid= a.sid
      AND a.cid= 102
      AND a.score> 80

   执行结果如下。

   

5. 查询语文成绩比数学成绩高的同学。 将以下SQL语句复制到SQLConsole页签中，然后单击执行。

   SELECT a.sid,
          a.score,
          s.sname
     FROM(
   SELECT sid, score
     FROM sc
    WHERE cid= "102") a,
          (
   SELECT sid, score
     FROM sc
    WHERE cid= "101") b,
          student as s
    WHERE a.score > b.score
      AND a.sid= b.sid
      AND s.sid= a.sid

   执行结果如下。

   

清理资源

• 在完成实验后，如果无需继续使用资源，选择不保留资源，单击结束实操。在结束实操对话框中，单击确定。

  

• 在完成实验后，如果需要继续使用资源，选择付费保留资源，单击结束实操。在结束实操对话框中，单击确定。请随时关注账户扣费情况，避免发生欠费。