Neekko33's Blog

MySQL速查笔记（一）

2023年01月22日

数据库操作基础

1 - 数据库的操作

1.1 显示数据库

复制代码 
show databases;

1.2 创建数据库

复制代码 
create database [if not exists] `数据库名` [字符编码];

注意：

  1. 如果创建的数据库已存在会报错，所以创建数据库时需要先判断是否存在；
  2. 如果数据库名是关键字和特殊字符会报错，需要在特殊字符、关键字行加上反引号；

1.3 删除数据库

复制代码 
drop database [if exists] 数据库名;

1.4 显示创建数据库的 SQL 语句

复制代码 
show create database 数据库名;

1.5 修改数据库

修改数据库的字符编码：

复制代码 
alter database 数据库名 charset=字符编码;

1.6 选择数据库

复制代码 
use 数据库名;

2 - 表的操作

2.1 显示所有表

复制代码 
show tables;

2.2 创建表

复制代码 
create table [if not exists] 表名(
	字段名 数据类型 [null|not null] [auto_increment] [primary key] [comment]
  字段名 数据类型 [default]
)engine=存储引擎
单词 含义
null not null 空｜非空
default 默认值
auto_increament 自增
primary key 主键
comment 备注
engine 引擎（innodb, myisam, memory）

创建简单的表：

复制代码 
create table stu(
	id int,
  name varchar(30)
);

创建复杂的表：

复制代码 
# 如果带有中文
set names gbk;
# 创建复杂表
create table if not exists teacher(
		id int auto_increment primary key comment '主键',
  	name varchar(20) not null comment '姓名',
  	phone varchar(20) comment '电话号码',
  	address varchar(100) default '地址不详' comment '地址'
)engine=innodb;

提示： 使用数据库名.表名可以给其他数据库创建表

2.3 显示创建表的语句

复制代码 
show create table 表名 [\G];

2.4 查看表结构

复制代码 
desc[ribe] 表名;

2.5 删除表

复制代码 
drop table [if exists] 表1,表2,...;

2.6 修改表

复制代码 
alter table 表名
  1. 添加字段
复制代码 
alter table 表名 add [column] 字段名 数据类型 [位置];

位置：

  • first：在第一位添加
  • after：在 xx 字段后添加
  1. 删除字段
复制代码 
alter table 表名 drop [column] 字段名;
  1. 修改字段（名字和类型）
复制代码 
alter table 表名 change 字段名 新字段名 数据类型...
  1. 修改字段（不改名）
复制代码 
alter table 表名 modify 字段名 字段属性...
  1. 修改引擎
复制代码 
alter table 表名 engine=引擎名称;
  1. 修改表名
复制代码 
alter table 表名 rename to 新表名;

2.7 复制表

复制代码 
create table 表名 select 字段 from 旧表

特点：不能复制父表的主键，只能复制父表的数据

复制代码 
create table 新表 like 旧表

特点：只能复制表结构，不能复制表数据

3 - 数据操作

3.1 插入数据

插入单条数据

复制代码 
insert into 表名 (字段1,字段2,...) values (值1,值2,...);
  1. 可以插入部分字段，但是非空字段必须插入
  2. 自动增长字段不用插入，数据库会自动插入增长的数字
  3. 自动增长列的值插入 null 即可
  4. 插入值的顺序和个数与表字段的顺序和个数一致，插入的字段可以省略
  5. 可以通过 default 插入默认值

插入多条数据

复制代码 
insert into 表名 values (值1,值2,...),(值1,值2,...),...;

3.2 更新数据

复制代码 
update 表名 set 字段=值 [where 条件]

3.3 删除数据

复制代码 
delete from 表名 [where 条件]

3.4 清空表

复制代码 
truncate table 表名

**delete from 表**和**truncate table 表**区别：
delete from 表：遍历表记录，一条一条的删除；
truncate table 表：将原表摧毁，再创建一个同结构的新表，就清空表而言，这种方法效率高；

3.5 查询语句（单表查询）

复制代码 
语法：select [选项] 列名 [form 表名] [where 条件] [order by 排序] [group by 分组] [having 条件][limit 限制]
3.5.1 字段表达式

可以通过as来取别名（as 可以省略）

3.5.2 from 语句

from 后跟数据源，数据源可以放多个，返回笛卡尔积

3.5.3 dual 表

dual 表是一个伪表。在有些特定情况下，没有具体的表的参与，但是为了保证 select 语句的完整又必须要一个表名，这时候就使用伪表。

3.5.4 where 子句

MySQL 支持的运算符：>, <, >=, <=, =, !=, and, or, not
Where 后面跟的是条件语句，返回条件为真的记录

3.5.5 in|not in

多个条件查询可以通过 in 语句 实现：

sql 复制代码 
select * from stu where stuadress in ('北京','上海');
3.5.6 between...and|not between...and

查找某个范围的记录：

sql 复制代码 
select * from stu where stuage between 18 and 20;
3.5.7 is null|is not null

判断是否为空：

sql 复制代码 
select * from stu where ch is null;
3.5.8 聚合函数
  1. sum()：求和
  2. avg()：求平均数
  3. max()：求最大值
  4. min()：求最小值
  5. count()：记录值
3.5.9 通配符
  1. _ [下划线] 表示任意一个字符
  2. % 表示任意字符
3.5.10 模糊查询（like）

在学生表中查询姓张的学生：

复制代码 
select * from stu where stuname like '张_'
3.5.11 order by 排序
  • ASC：升序【默认】
  • DESC：降序
  • 多列排序：
复制代码 
# 年龄升序，成绩降序
select *,(ch+math) as '总分' from stu order by stuage asc,(ch+math) desc;
3.5.12 group by [分组查询]

将查询的结果分组，分组查询目的在于统计数据

复制代码 
# 按照性别分组，显示每组的平均年龄
select avg(stuage) as '平均年龄',stusex from stu group by stusex;

注意：

  1. 如果是分组查询，查询字段必须是分组字段和聚合函数；
  2. 查询字段是普通字段，只取分组的第一个值；
  3. 可以通过group_concat()函数将同一组的值连接起来显示
  4. 分组以后结构默认按照升序排列，可以使用 desc 实现降序排列（直接加 desc）

多列分组：

复制代码 
# 按照性别和地区分组，显示每组的平均年龄
select stuaddress,stusex,avg(stuage) as '平均年龄'
3.5.13 having 条件

where 和 having 的区别：
where 是对原始数据进行筛选，having 是对结果集进行筛选

3.5.14 limit

语法：limit 起始位置，显示长度
注意：

  1. 起始位置可以不写，默认从 0 开始
  2. limit 可以在 update 和 delete 语句中使用
3.5.15 查询语句中的选项

查询语句中的选项有两个：

  1. all：显示所有数据【默认】
  2. distinct：去除结果集中重复的数据

3.6 查询语句（多表查询）

3.6.1 内连接【inner join】
复制代码 
# 语法一：
select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段;
# 语法二：
select 列名 from 表1，表2 where 表1.公共字段=表2.公共字段;

注意：如果要显示公共字段，必须要指定表名

3.6.2 左外连接 【left join】

以左边的表为标准，如果右边的表没有对应的记录，就以 null 填充结果

复制代码 
select 列名 from 表1 left join 表2 on 表1.公共字段=表2.公共字段;
3.6.3 右外连接 【right join】

以右边的表为标准，如果左边的表没有对应的记录，就以 null 填充结果

复制代码 
select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段;
3.6.4 交叉连接 【cross join】
  1. 如果没有连接表达式返回的是笛卡尔积
  2. 如果有连接表达式等价于内连接
3.6.5 自然连接 【natural】

自动的判断连接条件，它是通过同名字段来判断
自然连接分为：

  1. 自然内连接：natural join
  2. 自然左外连接：natural left join
  3. 自然右外连接：natural right join

自然连接结论：

  1. 表连接通过同名的字段来连接
  2. 如果没有同名的字段返回笛卡尔积
  3. 会对结果进行整理，整理的规则如下
    • 连接字段保留一个
    • 连接字段放在最前面
3.6.6 using()
  1. 同来指定连接字段
  2. using()也会对连接字段进行整理，整理规则与自然连接相同

3.7 子查询

3.7.1 只有一个返回值

语法：

复制代码 
select 语句 where 条件 (select ... from 表)
  1. 外面的查询称为父查询，括号中的查询称为子查询
  2. 子查询为父查询提供查询条件
  3. =只适用于子查询返回一个值的情况
3.7.2 in|not in 子查询

用于子查询返回多个值的情况

3.7.3 exists|not exists
复制代码 
# 如果有学生笔试成绩大于80就显示所有学生
select * from stuinfo where exists(select * from stumarks where writtenexam>=80);
3.7.4 子查询分类
  1. 标量子查询：子查询返回的结果就一个
  2. 列子查询：子查询返回的结果是一个列表
  3. 行子查询：子查询返回的结果返回一行
  • 例题：查询成绩最高的男生和女生
sql 复制代码 
select stuname,stusex,ch from stu where (stusex,ch) in (select stusex,max(ch) from stu group by stusex);
  1. 表子查询：子查询返回的结果当成一个表
  • 注意：如果把子查询当成表来看待，必须取一个别名

3.8 union

3.8.1 union 的使用

作用：将多个 select 语句结果集纵向联合起来

复制代码 
select 语句 union [选项] select 语句 union [选项] select 语句
3.8.2 union 的选项
  1. all：显示所有数据
  2. distinct：去除重复的数据【默认】
3.8.3 union 的注意事项
  1. union 两边的 select 语句的字段个数必须一致
  2. union 两边的 select 语句的字段名可以不一致，最终按第一个 select 语句的字段名
  3. union 两边的 select 语句中的数据类型可以不一致

4 - SQL 分类

DDL（Data Definition Language）数据库定义语言 CREATE、ALTER、DROP
DML（Data Manipulation Language）数据操纵语言 SELECT、UPDATE、INSERT、DELETE
DCL（Data Control Language）数据库控制语言，是用来设置或更改数据库用户或角色权限的语句

5 - 数据表的文件介绍

  • 一个数据库对应一个文件夹
  • 一个表对应一个或多个文件
    • myisam：一个表对应三个文件（表结构，表数据，表索引）
    • innodb：一个表对应一个表结构文件

6 - 字符集

字符集：字符在保存和传输时对应的二进制编码合集。

复制代码 
set names 字符集编码
相关文章

MySQL速查笔记（二）