【MySQL系列笔记】常用SQL

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 常用SQL分为三种类型,分别为DDL,DML和DQL;这三种类型的SQL语句分别用于管理数据库结构、操作数据、以及查询数据,是数据库操作中最常用的语句类型。在后面学习的多表联查中,SQL是分析业务后业务后能否实现的基础,以及后面如何书写动态SQL,以及完成级联查询的关键。

常用SQL分为三种类型,分别为DDL,DML和DQL;这三种类型的SQL语句分别用于管理数据库结构、操作数据、以及查询数据,是数据库操作中最常用的语句类型。

在后面学习的多表联查中,SQL是分析业务后业务后能否实现的基础,以及后面如何书写动态SQL,以及完成级联查询的关键。


分类

全称

说明

DDL

Data Definition Language

数据定义语言,用来定义数据库对象(数据库,表,字段)

DML

Data Manipulation Language

数据操作语言,用来对数据库表中的数据进行增删改

DQL

Data Query Language

数据查询语言,用来查询数据库中表的记录

DCL

Data Control Language

数据控制语言,用来创建数据库用户、控制数据库的访问权限

1. DDL

1.1. 概念:

Data Definition Language,数据定义语言,用来定义数据库对象(数据库,表,字段)

1.2. 格式:

数据库:

查询所有数据库

show databases;

使用数据库

use 数据库名 ;

创建数据库

create database [ if not exists ] 数据库名 ;

删除数据库

drop database [ if exists ] 数据库名 ;

show databases;

use db1;

create database db1;

create schema db1;

create database if not exists db2 ;

drop database db1;

drop database if exits db1;

表:

增加表格

create table 表名(

字段1 字段类型 [ 约束 ] [ comment 字段1注释 ]......

字段n 字段类型 [ 约束 ] [ comment 字段n注释 ] )

删除表格

drop table [ if exists ] 表名;

添加字段

alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

修改字段类型

alter table 表名 modify 字段名 新数据类型(长度);

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型 (长度)

[comment 注释] [约束];

删除字段

alter table 表名 drop column 字段名;

修改表名

rename table 表名 to 新表名;

查询当前数据库所有表

show tables;

查询表结构

desc 表名;

查询建表语句

show create table 表名;

create table user2
(
    id       int primary key auto_increment comment '主键',
    username varchar(20) not null unique comment '登录账号',
    name     varchar(10) not null ,
    age      int,
    gender   char(1) default '男'
);
drop table user;
alter table emp add address varchar(50);  #加地址列
alter table emp drop name;
alter table emp modify username varchar(21);   #改数据类型(长度,约束)
alter table emp change image img varchar(100);  #改字段名并指定新的字段长度
rename table user to user3;
show tables;
desc emp;
show create table emp;


1.3. 约束:

约束

描述

关键字

非空约束

限制该字段值不能为null

not null

唯一约束

保证字段的所有数据都是唯一、不重复的

unique

主键约束

主键是一行数据的唯一标识,要求非空且唯一

primary key (auto_increment自增)

默认约束

保存数据时,如果未指定该字段值,则采用默认值

default

外键约束

让两张表的数据建立连接,保证数据的一致性和完整性

foreign key

无符号约束

没有负值(用于数据类型后)

unsign

外键:

创建表时指定

create table 表名(字段名 数据类型, ... [constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名));

创建表后增加

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

create table emp2
(
    id     int auto_increment
        primary key
        unique,
    emp_id int not null,
    constraint emp2_emp_id_fk
        foreign key (emp_id) references emp (id)
);
alter table dish
    add constraint dish_category_id_fk
        foreign key (category_id) references category (id);

作用:

用于建立表与表之间的关系,以保证数据的完整性和一致性。同时,外键也可以限制数据的删除和修改,以避免对关联表中的数据造成影响。


2. DML

2.1. 概念:

Data Manipulation Language, 数据操作语言,用来对数据库表中的数据进行增删改

2.2. 格式:

指定字段添加数据

insert into 表名 (字段名1, 字段名2) values (值1, 值2);

全部字段添加数据

insert into 表名 values (值1, 值2, ...);

批量添加数据(指定字段)

insert into 表名 (字段名1, 字段名2)

values (值1, 值2), (值1, 值2);

批量添加数据(全部字段)

insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);

删除数据

delete from 表名 [ where 条件 ];

修改数据

update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where 条件 ] ;

-- 插入单条数据片段
insert into user (name, username, gender)
values ('李四', 'lisi', 1);
-- 插入单条数据
insert into user (id, username, name, ages, gender)
values (3, 'wangwu', '王五', 20, '男');
insert into user
values (4, 'zhaoliu', '赵六', 33, '1');
-- 插入多条数据
insert into user
values (5, 'hu', 'hu', 33, '1'),
       (6, 'hu', 'hu', 33, '1');
-- 修改数据
update user
set username = 'zhangsan'
where id = '5';
-- 删除数据
delete
from user
where id = 6;
delete
from user
where id in (4, 5);

2.3. 注意事项:

  • 修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
  • DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
  • DELETE 语句不能删除某一个字段的值(如果要操作,可以使用UPDATE,将该字段的值置为NULL)。

3. DQL

3.1. 概念:

Data Query Language(数据查询语言),用来查询数据库表中的记录。

关键字:SELECT

3.2. 格式:

select
  字段列表
from
  表名列表
where
  条件列表
group by
  分组字段列表
having
  分组后条件列表
order by
  排序字段列表
limit
  分页参数
select *
from emp
where name like '%张%'
  and gender = 1
  and entrydate between '2000-01-01' and '2010-01-01'
order by entrydate desc
limit 10;
select if(gender = 1, '男性员工', '女性员工'), count(gender)
from emp
group by gender;
select case job
           when 1 then '班主任'
           when 2 then '讲师'
           when 3 then '教研主管'
           when 4 then '学工主管'
           else '无'
           end,
       count(job)
from emp
group by job;

3.3. 分类:

基本查询

条件查询(where)

分组查询(group by)

排序查询(order by)

分页查询(limit)

3.4. 注意事项:

  1. null值不参与所有聚合函数运算。
  2. 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
  3. 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
  4. 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。

where与having区别

  1. 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
  2. 判断条件不同:where不能对聚合函数进行判断,而having可以。

4. 多表设计

4.1. 概念:

项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:一对多,多对多,一对一。

4.2. 分类:

一对一

关系:

多用于单表拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升操作效率

实现:

在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)

案例:

用户 与 身份证信息 的关系

用户基本信息表(tb_user)

用户身份信息表(tb_user_card)

一对多

关系:

一张父表对应多个子表,每个部门有多名员工

实现:

在数据库表中多的一方,添加字段,来关联一的一方的主键。

案例:

部门与员工的关系

员工表(tb_emp)

部门表(tb_dept)

多对多

关系:

一个学生可以选修多门课程,一门课程也可以供多个学生选择

实现:

建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

案例:

学生 与 课程的关系

学生表(tb_student)

课程表(tb_course)

学生课程关系表(tb_student_course)


5. 多表查询

5.1. 分类:

内连接查询

外连接查询

子查询

-- ============================= 内连接 ==========================
-- A.查询员工的姓名, 及所属的部门名称 (隐式内连接实现)
select tb_emp.name, tb_dept.name
from tb_emp,
     tb_dept
where tb_emp.dept_id = tb_dept.id;
-- B.查询员工的姓名, 及所属的部门名称 (显式内连接实现)
select tb_emp.name, tb_dept.name
from tb_emp join tb_dept on tb_emp.dept_id = tb_dept.id;
         
select tb_emp.name, tb_dept.name
from tb_emp
         inner join tb_dept on tb_emp.dept_id = tb_dept.id;
-- =============================== 外连接 ============================
-- A.查询员工表所有员工的姓名, 和对应的部门名称 (左外连接)
select tb_emp.name , tb_dept.name 
from tb_emp
         left outer join tb_dept on tb_emp.dept_id = tb_dept.id;
         
select tb_emp.name , tb_dept.name 
from tb_emp
         left join tb_dept on tb_emp.dept_id = tb_dept.id;
-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
select tb_dept.name as 部门, tb_emp.name as 员工
from tb_dept
         right join tb_emp on tb_dept.id = tb_emp.dept_id;
-- ========================= 子查询 ================================
-- 标量子查询
-- A.查询 "教研部" 的所有员工信息
select *
from tb_emp
where dept_id = (select id
                 from tb_dept
                 where name = '教研部');
-- B.查询在 "方东白" 入职之后的员工信息
select *
from tb_emp
where entrydate > (select entrydate
                   from tb_emp
                   where name = '方东白');
-- 列子查询
-- A.查询 "教研部" 和 "咨询部" 的所有员工信息
select *
from tb_emp
where dept_id in (select id
                  from tb_dept
                  where name in ('教研部', '咨询部'));
-- 行子查询
-- A.查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息;
select *
from tb_emp
where (entrydate, job) = (select entrydate, job
                          from tb_emp
                          where name = '韦一笑');
-- 表子查询
-- A.查询入职日期是 "2006-01-01" 之后的员工姓名 , 及其部门信息
select tb_emp.name, tb_dept.name
from tb_emp,
     tb_dept
where entrydate > '2006-01-01'
and tb_emp.dept_id = tb_dept.id;
select tb_emp2.name,tb_dept.name
from (select * from tb_emp where entrydate > '2006-01-01') as tb_emp2
left join tb_dept on tb_emp2.dept_id = tb_dept.id;
-- 1.查询价格低于 10元 的菜品的名称、价格 及其 菜品的分类名称
select d.name, d.price, c.name
from dish d,
     category c
where price < 10 && d.category_id = c.id;
-- 2.查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来)
select dish.name, dish.price, c.name
from dish,
     category c
where price >= 10 && price <= 50
  and dish.category_id = c.id
  and dish.status = 1;
-- 3.查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格
select c.name, max(d.price)
from dish d,
     category c
where d.category_id = c.id
group by c.name;
select c.name, d2.max
from (select category_id, max(price) max
      from dish
      group by category_id) d2,
     category c
where d2.category_id = c.id;
-- 4.查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3的 分类名称
select c.name, count(d.status)
from category c,
     dish d
where d.category_id = c.id && d.status = 1
group by c.name
having count(d.status) >= 3;
select category_id, count(*)
from dish
where status = 1
group by category_id
having count(*) >= 3;
select c.name ,d.count
from (select category_id, count(*) count
      from dish
      where status = 1
      group by category_id
      having count >= 3) d,
     category c
where d.category_id = c.id;
-- 5.查询出 "商务套餐A" 中包含了哪些菜品 (展示出套餐名称、价格, 包含的菜品名称、价格、份数)
select s.name, s.price, d.name
from setmeal s,
     dish d,
     setmeal_dish sd
where s.name = '商务套餐A'
  and sd.setmeal_id = s.id
  and sd.dish_id = d.id;
-- 6.查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格)
select dish.name, price
from dish
where price < (select avg(price) from dish);

5.2. 注意事项:

  1. 外连接可以查出null值
  2. 外键可以为null

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3天前
|
SQL 关系型数据库 MySQL
go 通过sql操作mysql
go 通过sql操作mysql
11 1
|
3天前
|
存储 SQL 关系型数据库
MySQL游标的创建与使用——Baidu Comate全文SQL-AI生成
MySQL游标的创建与使用——Baidu Comate全文SQL-AI生成
4 0
|
3天前
|
SQL 关系型数据库 MySQL
MySQL学习必备SQL_DDL_DML_DQL
MySQL学习必备SQL_DDL_DML_DQL
2 0
|
5天前
|
SQL 关系型数据库 MySQL
mysqldiff - Golang 针对 MySQL 数据库表结构的差异 SQL 工具
Golang 针对 MySQL 数据库表结构的差异 SQL 工具。https://github.com/camry/mysqldiff
38 7
|
9天前
|
分布式计算 DataWorks 关系型数据库
MaxCompute产品使用合集之DataWorks是否支持通过SQL方式在MaxCompute中查询数据,并通过数据集成服务将查询结果同步至MySQL数据库
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
SQL 数据库管理
常用Sql整理笔记
一、多行结果转换为一行,用逗号隔开。 mssql代码如下: 点击打开 -- 多行 select tid from typeinfo where pid=4 -- 一行 select STUFF((Select ','+Convert(varchar(50),tid) FROM ty...
873 0
|
7天前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
1月前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
156 1
|
25天前
|
SQL 存储 搜索推荐
SQL server增删改查(1)
SQL server增删改查(1)
106 0

相关产品

  • 云数据库 RDS MySQL 版