备案控制台
探索云世界
开发者社区 数据库 文章 正文

一文帮你玩转MySQL表---增，删，查，改（进阶）

2022-10-28 168
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL Serverless，价值2615元额度，1个月
简介： 创建学生表，一个学生对应一个班级，一个班级对应多个学生，id为主键，classes_id为外键，关联班级表id。

1. 数据库约束

1.1 约束类型

· NOT NULL 指示某列不能存储NULL值


· UNIQUE 保证某列必须有唯一的值


· DEFAULT 规定没有给列赋值时的默认值


· PRIMARY KEY ，NOT NULL和UNIQUE的结合，确保某列有唯一标识，有助于更容易更快捷地找到表中的一个特定记录


· FOREIGN KEY 保证一个表中的数据匹配另一个表中的值的参照完整性


· CHECK 保证列中的值符合指定的条件


1.2 NOT NULL约束

创建表时，可以指定某列不为空：

create table student(
    id int NOT NULL, --id不为空
    name varchar(10),
    sex varchar(1),
    age int
);


1.3 UNIQUE 唯一约束

指定id列为唯一的，不重复：

drop table if exists student;
create table student(
    id int UNIQUE, --id为唯一的
    name varchar(10),
    sex varchar(1),
    age int
);


1.4 DEFAULT 默认值约束

指定插入数据时，如果name列为空，则将默认值设为unkown：

drop table if exists student;
create table student(
    id int UNIQUE, 
    name varchar(10) DEFAULT 'unkown',
    sex varchar(1),
    age int
);


1.5 PRIMARY KEY 主键约束

指定id列为主键：

drop table if exists student;
create table student(
    id int PRIMARY KEY, 
    name varchar(10) DEFAULT 'unkown',
    sex varchar(1),
    age int
);


对于整数类型的主键，常搭配自增长auto_increment来使用，插入数据对应字段不给值，使用最大值+1 

id int PRIMARY KEY auto_increment,

1.6 FOREIGN KEY 外键约束

外键用于关联其他表的主键或唯一值


语法：

foreign key (字段名) references 主表(列);


示例：


创建班级表：

-- 创建班级表
drop table if exists classes;
create table classes(
    id int primary key auto_increment,
    name varchar(20)
);


创建学生表，一个学生对应一个班级，一个班级对应多个学生，id为主键，classes_id为外键，关联班级表id：

-- 创建学生表来关联班级表
drop table if exists student;
create table student(
    id int PRIMARY KEY, 
    name varchar(10) DEFAULT 'unkown',
    sex varchar(1),
    classes_id int,
    foreign key (classes_id) references classes(id)
);


1.7 CHECK约束（了解）

MySQL使用时不报错，但忽略该约束：

create table test_user (
    id int,
    name varchar(10),
    sex varchar(1),
    check (sex = '男' or sex = '女')
);


2. 表的设计

三大范式：


一对一：

image.png


一对多：


image.png


多对多：

image.png



创建课程表：

drop table if exists course;
create table course (
    id int primary key auto_increment,
    name varchar(20)
);


创建学生课程中间表，考试成绩表： 

drop table if exists score;
create table score (
    id int primary key auto_increment,
    score decimal(3,1),
    student_id int,
    course_id int,
    foreign key (student_id) references student(id),
    foreign key (course_id) references course(id)
);


3. 新增

插入查询的结果


语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...


示例：


新建一张user表：

create table user (
    id int primary key auto_increment,
    name varchar(10),
    sex varchar(1),
    age int,
    email varchar(20)
);


将学生表的数据复制到user表中：

insert into user (name,sex,age) select name,sex,age from student;


4. 查询

4.1 聚合查询

4.1.1 聚合函数

常见的统计总数，计算平均值等操作，可以使用聚合查询来实现，常见的聚合函数：

函数 说明
COUNT([DISTINCT] expr) 返回查询到的数据的数量
SUM([DISTINCT] expr) 返回查询到的数据的总和
AVG([DISTINCT] expr) 返回查询到的数据的平均值
MAX([DISTINCT] expr) 返回查询到的数据的最大值
MIN([DISTINCT] expr) 返回查询到的数据的最小值


示例：

· COUNT

-- 统计班级有多少个同学
select count(*) from student;
select count(0) from student;


· SUM

-- 统计学生的数学总成绩
select sum(math) from student_score;
-- 统计不及格学生的数学总成绩
select sum(math) from student_score where math<60;

AVG

-- 统计平均总分
select avg(chinese+math+english) from student_score;

· MAX

-- 找出英语的最高成绩
select max(english) from student_score;

· MIN

-- 找出语文的最低成绩
select min(chinese) from student_score;


4.1.2 GROUP BY

select中使用group by子句可以对指定列进行分组查询，需要满足：使用group by 进行分组查询时，select指定的字段必须是“分组依据字段”，其他字段要想出现在select中必须包含在聚合函数中。


语法：

select column1, sum(column2), .. from table group by column1,column3；


案例表：

create table emp (
    id int primary key auto_increment, 
    name varchar(10),
    role varchar(10) comment '角色',   
    salary decimal(10,2) comment '薪资'
);
insert into emp (name,role,salary) values
('小王','员工',3000.50),
('小贺','老板',200000.00),
('小张','秘书',15000),
('小方','保洁员',3000),
('小乔','员工',4500.20),
('小李','员工',5000.28);


查询每个角色的最高工资，最低工资和平均工资：

select role,max(salary),min(salary),avg(salary) from emp group by role;


结果：

image.png


4.1.3 HAVING

GROUP BY子句进行分组后，如果要对分组后的结果进行条件过滤不能使用WHERE，要使用HAVING语句。


示例：显示平均工资低于4000的角色，和他的平均工资：

select role,avg(salary) from emp group by role having avg(salary)<4000;


结果：

image.png


4.2 联合查询

先将后续用到的表和数据给出：班级表，学生表，课程表，分数表

drop table if exists classes;
create table classes (
    id int primary key auto_increment,
    name varchar(20)
);
insert into classes (name) values
('计算机1班'),
('自动化2班'),
('机械3班');
drop table if exists student;
create table student (
    id int primary key auto_increment,
    name varchar(10),
    classes_id int,
    foreign key (classes_id) references classes(id)
);
insert into student (name,classes_id) values
('小花',2),
('小张',1),
('小贺',1),
('小方',3),
('小乔',3);
drop table if exists course;
create table course (
    id int primary key auto_increment,
    name varchar(20)
);
insert into course (name) values
('java程序设计'),
('大学英语'),
('高等数学'),
('数据结构'),
('工程制图');
drop table if exists score;
create table score (
    id int primary key auto_increment,
    score decimal(3,1),
    student_id int,
    course_id int,
    foreign key (student_id) references student(id),
    foreign key (course_id) references course(id)
);
insert into score (score,student_id,course_id) values
-- 小花
(98.5,1,3),(80,1,5),
-- 小张
(99,2,1),(95,2,2),(96,2,3),(90,2,4),(93,2,5), 
-- 小贺
(85,3,1),(86,3,2),(86,3,3),(95,3,4), 
-- 小方
(70,4,3),(65,4,5),
-- 小乔
(65,5,5),(67,5,3),(68,5,2);


实际的开发中，数据来自不同的表，这时候需要多张表联合查询，多表查询是对多张表的数据取笛卡尔积。


笛卡尔积：

image.png



注意：关联查询可以对关联表使用别名


4.2.1 内连接

语法：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;


示例：查询“小张”同学的成绩：

select
    stu.id,
    stu.name,
    sco.score
from
    student stu
    join score sco on stu.id = sco.student_id
    and stu.name='小张';


结果：

image.png


示例：查询所有同学的总成绩，及同学的个人信息：

select
    stu.id,
    stu.name,
    sum(sco.score)
from
    student stu
    join score sco on stu.id = sco.student_id
group by
    stu.id;


结果：


image.png

示例：查询所有同学的成绩，及同学的个人信息：

select
    stu.id,
    stu.name,
    sco.score,
    sco.course_id,
    cou.name
from
    student stu
    join score sco on stu.id = sco.student_id
    join course cou on sco.course_id = cou.id;


结果：

image.png


4.2.2 外连接

外连接分为左外连接和右外连接，如果联合查询，左侧的表完全显示就是左外连接，右侧的表完全显示就是右外连接。


语法：

-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;


4.2.3 自连接

自连接是指在同一张表连接自身进行查询


示例：显示所有“大学英语”比“高等数学”成绩高的信息：

select
    stu.*,
    s1.score 大学英语,
    s2.score 高等数学
from
    score s1
    join score s2 on s1.student_id = s2.student_id
    join student stu on s1.student_id = stu.id
    join course c1 on s1.course_id = c1.id
    join course c2 on s2.course_id = c2.id
    and s1.score > s2.score
    and c1.name = '大学英语'
    and c2.name = '高等数学';


结果：

image.png


4.2.4 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询


· 单行子查询：返回一行记录的子查询


示例：查询与“小张”在同一个班的同学

select
    *
from
    student
where
    classes_id = (
        select
            classes_id
        from
            student
        where
            name = '小张'
    );


结果：

image.png


· 多行子查询：返回多行记录的子查询


示例：查询“高等数学”或“大学英语”课程的成绩信息


1. [NOT] IN 关键字

-- 使用IN
select * from score where course_id in (
    select id from course where name='大学英语' or name='高等数学'
);
-- 使用NOT IN
select * from score where course_id not in (
    select id from course where name != '高等数学' and name != '大学英语'
);


2. [NOT] EXISTS 关键字

-- 使用exists
select
    *
from
    score sco
where
    exists (
        select
            sco.id
        from
            course cou
        where
            (
                name = '高等数学'
                or name = '大学英语'
            )
            and cou.id = sco.course_id
    );
-- 使用not exists
select
    *
from
    score sco
where
    not exists (
        select
            sco.id
        from
            course cou
        where
            (
                name != '高等数学'
                and name != '大学英语'
            )
            and cou.id = sco.course_id
    );


· 在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个 子查询当作一个临时表来使用


注意：临时表必须起别名，临时表也可以当作一张虚拟表来关联查询


示例：查询所有比自动化2班平均分高的成绩信息

select
    *
from
    score sco,
(
        select
            avg(sco.score) score
        from
            score sco
            join student stu on sco.student_id = stu.id
            join classes cls on stu.classes_id = cls.id
        where
            cls.name = '自动化2班'
    ) tmp
where
    sco.score > tmp.score;


结果：

image.png


4.2.5 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。


· UNION


该操作符取得两个结果集的并集，会自动去掉结果集中的重复行


示例：查询id小于3，或者名字为“高等数学”的课程

select * from course where id < 3
union
select * from course where name = '高等数学';


结果：

image.png


· UNION ALL


该操作符取两个结果集的并集，但是不会去掉结果集中的重复行


示例：查询id小于3，或者名字为“java程序设计”的课程

select * from course where id < 3
union all
select * from course where name = 'java程序设计';


结果：

image.png



文章标签：
云数据库 RDS MySQL 版
Java
SQL
关系型数据库
MySQL
数据库
存储
关键词：
云数据库 RDS MySQL 版表
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
游客7sp3zq27pysty
目录
相关文章
一只牛博
|
21天前
|
关系型数据库 MySQL 数据库
数据安全之路：深入了解MySQL的行锁与表锁机制
数据安全之路：深入了解MySQL的行锁与表锁机制
一只牛博
19 1
起飞的风筝
|
Oracle 关系型数据库 MySQL
【mysql】—— 表的内连和外连
【mysql】—— 表的内连和外连
起飞的风筝
37 0
起飞的风筝
|
2月前
|
存储 关系型数据库 MySQL
【mysql】—— 表的增删改查
【mysql】—— 表的增删改查
起飞的风筝
41 0
哈茶真的c
|
1月前
|
存储 SQL 关系型数据库
【MySQL】4. 表的操作
【MySQL】4. 表的操作
哈茶真的c
22 0
哈茶真的c
|
1月前
|
存储 关系型数据库 MySQL
【MySQL】6. 表的约束
【MySQL】6. 表的约束
哈茶真的c
18 0
技术蜜糖罐
|
29天前
|
存储 关系型数据库 MySQL
MySQL数据库性能大揭秘：表设计优化的高效策略（优化数据类型、增加冗余字段、拆分表以及使用非空约束）
MySQL数据库性能大揭秘：表设计优化的高效策略（优化数据类型、增加冗余字段、拆分表以及使用非空约束）
技术蜜糖罐
36 0
技术蜜糖罐
|
29天前
|
缓存 关系型数据库 MySQL
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
技术蜜糖罐
39 0
你的小笔记本YY
|
1天前
|
SQL 存储 关系型数据库
【MySQL】DDL的表操作详解：创建＆查询＆修改＆删除
【MySQL】DDL的表操作详解：创建＆查询＆修改＆删除
你的小笔记本YY
8 0
讓丄帝愛伱
|
2天前
|
存储 SQL 关系型数据库
mysql查询数据库表大小怎么操作
mysql查询数据库表大小怎么操作
讓丄帝愛伱
11 0
xin在这
|
2天前
|
运维 DataWorks 关系型数据库
DataWorks产品使用合集之DataWorks还有就是对于mysql中的表已经存在数据了，第一次全量后面增量同步的步骤如何解决
DataWorks作为一站式的数据开发与治理平台，提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案，帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述，涵盖数据处理的各个环节。
xin在这
17 2

热门文章

最新文章

  • 1
    MySQL全局库表查询准确定位字段
  • 2
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 3
    基于 java + Springboot + vue +mysql 大学生实习管理系统（含源码）
  • 4
    mysql性能优化-慢查询分析、优化索引和配置
  • 5
    CentOS 7 下使用yum安装MySQL5.7.20 最简单 图文详解
  • 6
    MySQL 8教程：详解如何更改和重置用户密码
  • 7
    centos7安装mysql-带网盘安装包
  • 8
    MySQL学习手册（第一部分）
  • 9
    高性能 MySQL 第四版（GPT 重译）（三）(1)
  • 10
    mysql用户权限设置
  • 1
    连接和管理RDS
    30
  • 2
    购买和初始化阿里云RDS
    28
  • 3
    rds教程
    27
  • 4
    TiDB与MySQL、PostgreSQL等数据库的比较分析
    126
  • 5
    OBCP实践 - 迁移 MySQL 数据到 OceanBase 集群
    42
  • 6
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    41
  • 7
    TIDB和MySQL的区别
    183
  • 8
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    67
  • 9
    [MySQL]事务原理之redo log,undo log
    46
  • 10
    [MySQL]SQL优化之索引的使用规则
    36

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 极致弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • 如何通过RDS MySQL数据库内网访问
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考