MySQL基础知识复习

mysql基础知识复习

一、数据类型

• 整数类型

数据类型	字节数
INT	4
BIGINT	8
…

• 浮点类型和定点类型

数据类型	字节数
FLOAT	4
DOUBLE	8
…

• 字符串类型

数据类型	字节数
VARCHAR(N) 可变长度的	根据具体的存储数值长度决定
CHAR(N) 不可变长的	N

• 文本类型(用于表示大量文本数据)

数据类型	字节数
TEXT	0-65535
…

• 日期与时间类型

数据类型	字节数	格式
YEAR	1	YYYY
DATE	4	YYYY-MM-DD
TIME	3	HH:MM:SS
DATETIME	8	YYYY-MM-DD HH:MM:SS
…

• 二进制类型（存储图片等数据）

数据类型	存储范围(字节)
BLOB	0-65535
…

二、数据库的基本操作

• 创建数据库

create database 数据库名称;

• 删除数据库

drop database 数据库名称;

• 查看所有数据库

show databases;

• 切换数据库

use 数据库名称;

三、数据表的基本操作

• 创建数据表

create table 表名称(
	字段1 类型,
    字段2 类型,
    ..
)

• 查看所有表

show tables;

• 查看表结构

desc 表名称;

• 修改数据表

alter table 表名称 rename to 修改后名称;					 --修改表名称
alter table 表名称 change 字段名称 修改后字段名称 类型;			--修改字段名称和类型
alter table	表名称 modify 字段名称 类型;						--修改字段类型
alter table 表名称 add 添加字段 类型;						--添加字段
alter table 表名称 drop 删除的字段;

• 删除数据表

drop table 表名称;

四、数据表的约束

约束	作用
PRIMARY KEY	主键约束（加上auto_increment表示自动增长）
FOREIGN KEY	外键约束
NOT NULL	非空约束
UNIQUE	唯一约束
DEFAULT	默认值约束

五、DML 数据操作语言

1，添加数据

insert into 表名称(字段1,字段2,...) values(值1,值2,...);
insert into 表名称(字段1,字段2,...) values(值1,值2,...),(值1,值2,...),...;		--同时添加多条数据

2，更新数据

update 表 set 字段1 = 值1,字段2 = 值2,... where 条件字段 = 条件值;
update 表 set 字段1 = 值1,...; 										--无条件地修改所有记录的值

3，删除数据

delete from 表 where[条件];
delete from 表;				--删除表中所有数据
--------------
truncate 表;					--清空表中所有数据

4，基础查询

• 简单查询(略)

select distinct * from 表;		--distinct用于过滤相同的记录

• 条件查询

  • IN 是否在集合中

  select * from 表 where 字段 in (v1,v2,...);
  

  • BETWEEN AND 是否某个范围内

  select * from 表 where 字段 BETWEEN v1 AND v2;
  

  • AND
  • OR
  • LIKE

  select * from 表 where 字段 LIKE '%aaa';		--通配符'%'表示任意长度的字符串
  select * from 表 where 字段 LIKE '_aaa';		--通配符'_'表示单个任意字符
  

  • LIMIT

  select * from 表 limit 结果数量;				--限制查询结果的记录数量
  

  • GROUP BY
  • WHERE和HAVING

  这两个关键字都用于表示过滤条件。

  当过滤条件中有聚合函数时，必须使用HAVING；

  当过滤条件中没有聚合函数时，WHERE和HAVING都可以使用，但是推荐使用WHERE，因为WHERE的效率更高；

  HAVING通常都使用在GROUP BY后面；

  #推荐写法：
  select 字段1,max(字段2),...
  from 表1
  where 条件表达式1
  group by 字段3
  having 有关聚合函数的条件表达式;
  
  #不推荐写法：
  select 字段1,max(字段2),...
  from 表1
  group by 字段3
  having 有关聚合函数的条件表达式 and 条件表达式1;
  

5，多表查询

数据准备

student表：

teacher表：

5.1、笛卡尔积

多表查询的时候如果没有条件限制，各表中的每条数据都会和其他表的每条数据匹配一遍。这样的查询结果集就是产生了笛卡尔积，这种结果集一般没什么意义。

select * from student join teacher;

结果集（3*7）：

5.2、内连接

合并多个表的行，但结果集中只包含符合连接条件的的数据。

select * from student s join teacher t on s.teacher_id = t.id;

结果集：

5.3、左外连接

结果集中除了包含内连接的记录外，还包含左表（在left join关键字左边的表）中不满足条件的所有记录。

先添加点数据：

INSERT INTO student(name,teacher_id) VALUES("小秦",10),("小玄",11),("小迪",12);
INSERT INTO teacher(name) VALUES("秦老师"),("珍老师"),("奥老师");

SELECT * FROM student s LEFT JOIN teacher t ON s.teacher_id = t.id;

结果集：

5.4、右外连接

结果集中除了包含内连接的记录外，还包含右表（在left join关键字右边的表）中不满足条件的所有记录。

SELECT * FROM student s right JOIN teacher t ON s.teacher_id = t.id;

结果集：

5.5、其他连接1

SELECT s.id stu_id,s.`name` stu_name,t.`name` tea_name
FROM student s 
LEFT JOIN teacher t 
ON s.teacher_id = t.id 
WHERE t.`name` IS NULL;

结果集：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zMZNEIjL-1672567841034)(https://gitee.com/lu-yunji/image/raw/master/image-20230101133250801.png)]

5.6、其他连接2

SELECT t.id tea_id,t.`name` tea_name,s.id stu_id,s.`name` stu_name
FROM student s 
RIGHT JOIN teacher t 
ON s.teacher_id = t.id
WHERE s.id IS NULL;

结果集：

5.7、其他连接3（满外连接）

SELECT s.id stu_id,s.`name` stu_name,t.`name` tea_name,t.id tea_id
FROM student s 
LEFT JOIN teacher t 
ON s.teacher_id = t.id 
WHERE t.`name` IS NULL
UNION ALL
SELECT s.id stu_id,s.`name` stu_name,t.id tea_id,t.`name` tea_name FROM student s right JOIN teacher t ON s.teacher_id = t.id;

结果集：

5.8、其他连接4

SELECT t.id tea_id,t.`name` tea_name,s.id stu_id,s.`name` stu_name
FROM student s 
RIGHT JOIN teacher t 
ON s.teacher_id = t.id
WHERE s.id IS NULL
UNION ALL
SELECT s.id stu_id,s.`name` stu_name,t.id tea_id,t.`name` tea_name
FROM student s 
LEFT JOIN teacher t 
ON s.teacher_id = t.id 
WHERE t.`name` IS NULL;

结果集：

6、子查询

查询套查询，内层查询的结果集作为外层查询的条件参数。

重点在于根据实际业务灵活使用。

六、sql的执行流程

编写顺序

sql92语法：

select ...#可能包含聚合函数
from ...
where 多表连接的条件 and 过滤条件(不包含聚合函数)
group by ...
having 过滤条件(包含聚合函数)
order by ...(asc/desc)
limit ...

sql99语法

select ...#可能包含聚合函数
from ... (left/right) join ... on 多表连接的条件
(left/right) join ... on 多表连接的条件
...
where 过滤条件(不包含聚合函数)
group by ...
having 过滤条件(包含聚合函数)
order by ...(asc/desc)
limit ...

执行顺序

执行顺序：from、on、left/right join、where、group by、having、select、distinct、order by、limit

1，先执行from，如果有多个表，底层会先产生有笛卡尔积的结果集；

2，然后执行on进行连接条件的过滤；

3，然后再执行left/right join；

…(后面略，就是跟图上画的一样)