【MySQL】基础简记

1. SQL语句的分类

• DDL(数据定义语言)：定义、修改、删除数据库和表的结构，不管表里数据

  • CREATE 创建库 / 表
  • ALTER 修改表结构
  • DROP 删除库 / 表
  • TRUNCATE 清空表数据

• DML(数据操纵语言)：操作表里面的实际数据，增删改

  • INSERT 插入数据
  • UPDATE 修改数据
  • DELETE 删除数据

• DQL(数据查询语言)：专门查询读取数据，不会修改任何内容

  • SELECT 查询数据

• DCL(数据控制语言)：管理用户权限、事务

  • GRANT 授予权限
  • REVOKE 撤销权限
  • COMMIT 事务提交
  • ROLLBACK 事务回滚

2. 数据类型和约束

（1）数据类型

• 整数类型：

类型	大小	范围（有符号）	用途
TINYINT	1 字节	-128 ~ 127	状态、性别、小枚举
SMALLINT	2 字节	-32768 ~ 32767	小数量
INT / INTEGER	4 字节	-21 亿～21 亿	主键 ID、常规数字
BIGINT	8 字节	超大范围	大数据 ID、订单号

• 小数类型：

数据类型	名称	特点	精度情况	适用场景	参数说明
FLOAT	单精度浮点数	存储快、占用空间小	存在精度误差	普通小数、温度、距离等非金额数据	无需固定位数定义
DOUBLE	双精度浮点数	精度高于 float，空间略大	仍有精度误差	常规浮点运算、科学计算	无需固定位数定义
DECIMAL(M,D)	定点精准小数	严格十进制存储	无精度丢失，绝对精准	金额、价格、账务、财务数据	M = 数字总位数，D = 小数点后位数

• 字符串类型

类型	最大长度	特点
CHAR(N)	255 字符	固定长度，速度快，浪费空间
VARCHAR(N)	65535 字节	可变长度，最常用
TEXT	65535 字符	长文本，不加索引
MEDIUMTEXT	1600 万字符	文章、富文本
LONGTEXT	4GB	超大文本
ENUM	65535 个值	枚举：' 男 ',' 女'
SET	64 个值	多选集合

• 日期类型

类型	格式	用途
DATE	YYYY-MM-DD	生日、日期
TIME	HH:MM:SS	时长、时间点
DATETIME	YYYY-MM-DD HH:MM:SS	创建时间、更新时间
TIMESTAMP	同上，自动时区转换	跨时区时间
YEAR	YYYY	年份

（2）数据约束

注意：（1）主键：唯一 + 非空；（2）唯一：唯一 + 可空；（3）DEFAULT 只填默认，不限制必填

约束名	关键字	作用	特点	使用场景
主键约束	PRIMARY KEY	唯一标识记录	唯一 + 非空，一张表仅一个，可自增	主键 id
非空约束	NOT NULL	字段必须赋值	不能为空，可重复	用户名、姓名
唯一约束	UNIQUE	字段值不重复	不能重复，允许为空	手机号、邮箱
默认约束	DEFAULT	无赋值自动填充	不填走默认值	状态、性别
检查约束	CHECK	限制数据范围	限定数值 / 格式范围	年龄、分数
外键约束	FOREIGN KEY	关联两张表	保证参照完整性，开发少用	多表关联

3. SQL语句

• 条件查询

条件查询关键字:  where条件查询基础格式: select 字段名 from 表名 where 条件;比较运算符: >  <  >=  <=  !=  <>逻辑运算符: and  or  not范围 查询: 连续范围:between x and y       非连续范围: in(x,y)模糊 查询: 关键字:like   %:0个或者多个字符   _:一个字符非空 判断: 为空: is null    不为空:is not null

• 比较运算符

# 1.比较运算符: >  <  >=  <=  !=  <>-- 需求1: 查询所有'自营'的商品
select * from products where is_self = '自营';
-- 需求2: 查询评分在'9.50'(不含)以上的商品
select * from products where score > 9.50;
-- 需求3: 查询评分在'9.50'(含)以上的商品
select * from products where score >= 9.50;
-- 需求4: 查询价格在999(不含)以下的商品
select * from products where price < 999;
-- 需求5: 查询价格在999(含)以下的商品
select * from products where price <= 999;
-- 需求6: 查询评分不等于9.30的商品
select * from products where score != 9.3;
select * from products where score <> 9.3;

• 逻辑查询

-- and: 并且  or:或者  not:取反-- 需求1: 查询自营商品中所有价格大于2000的商品信息
select * from products where is_self = '自营' and price > 2000;
-- 需求2: 查询商品评分在9.0(含)-9.5(含)之间的商品信息
select * from products where score >= 9 and score <= 9.5;
-- 需求3: 查询商品价格在1000(含)到3000(含)之间的商品信息
select * from products where price >= 1000 and price <= 3000;
-- 需求4: 查询价格是999或者2199或者2399的商品
select * from products where price = 999 or price = 2199 or price = 2399;
-- 需求5: 查询商品是'华为Mate50'或者'荣耀80'的商品
select * from products where name = '华为Mate50' or name = '荣耀80';
-- 需求6: 查询商品不是自营的商品
select * from products where not is_self = '自营';
select * from products where  is_self = '非自营';
-- 需求7: 查询商品不在1000到3000之间的商品
select * from products where not (price >= 1000 and price <= 3000);
select * from products where  price < 1000 or price > 3000;

• 范围查询

-- 需求1: 查询商品价格在1000(含)到3000(含)之间的商品信息
select * from products where  price BETWEEN 1000 and 3000;
-- 注意: 以下语法是错误的
select * from products where 1000 <= price <= 3000;
-- 需求2: 查询商品不在1000到3000之间的商品
select * from products where  price not BETWEEN 1000 and 3000;
-- 需求3: 查询价格是999或者2199或者2399的商品
select * from products where price in(999,2199,2399);
-- 需求4: 查询商品是'华为Mate50'或者'荣耀80'的商品
select * from products where name in('华为Mate50','荣耀80');

• 模糊查询

-- 关键字: like   符号 %:任意多个字符  _:任意1个字符-- 需求1: 查询商品名称以'华'开头的商品信息
select * from products where name like '华%';
-- 需求2: 查询商品名称以'华'开头并且8个字符的商品信息
select * from products where name like '华_______';
-- 需求3: 查询商品名称以'66'结尾商品信息
select * from products where name like '%66';
-- 需求4: 查询商品名称中包含'兰'字的商品信息
select * from products where name like '%兰%';
-- 需求5: 查询商品名称中第3个字是'兰'字的商品信息
select * from products where name like '__兰%';

• 非空判断

/*
null在sql中代表空的,没有任何意义的意思
如果数据中有空字符串'',字符串'null',一定要注意,他们和sql中的null不是一回事!!!
*/
-- 需求1:查询未评分的商品信息
select * from products where score is null;
-- 注意: 以下方式是错误的
select * from products where score = null;
select * from products where score = '';
select * from products where score = 'null';
select * from products where score is 'null';-- 为了方便演示null和'','null'的区别,可以插入部分测试数据
insert into products(name,price) values('拯救者Y9000','9999');
insert into products(name,price) values('null','99');
insert into products(name,price) values('',0);-- 需求2:查询商品名称是'null'的商品信息
select * from products where name = 'null';
-- 需求3:查询商品名称是''的商品信息
select * from products where name = '';

• 排序查询

排序查询关键字: order by排序查询基础格式: select 字段名 from 表名 order by 排序字段名 asc|desc;asc : 升序(默认)desc: 降序排序查询进阶格式: select 字段名 from 表名 order by 排序字段1名 asc|desc , 排序字段2名 asc|desc;注意: 如果order by后跟多个排序字段,先按照前面的字段排序,如果有相同值的情况再按照后面的排序规则排序

-- 示例1：查询所有商品，并按照评分从高到低进行排序
SELECT * FROM products ORDER BY score DESC;-- 示例2：查询所有商品，先按照评分从高到低进行排序，评分相同的再按照价格从低到高排序
SELECT * FROM products ORDER BY score DESC, price;

• 聚合函数

聚合函数: 又叫统计函数,也叫分组函数常用聚合函数:  sum()  count()  avg()   max()  min()聚合查询基础格式: select 聚合函数(字段名) from 表名;    注意: 此处没有分组默认整个表就是一个大的分组注意: 聚合函数(字段名)会自动忽略null值,以后统计个数一般用count(*)统计因为它不会忽略null值

# 注意: 别名不建议用中文,以下仅仅为了演示
-- 示例1：统计当前商品一共有多少件
SELECT count(id) FROM products;
SELECT count(*) FROM products;-- 示例2：对商品评分列进行计数、求最大、求最小、求和、求平均
SELECTCOUNT(score) AS cnt,MAX(score) AS max_score,MIN(score) AS min_score,SUM(score) AS total_score,AVG(score) AS avg_score
FROM products;-- 示例3：统计所有非自营商品评分的平均值
SELECTis_self,AVG(score)
FROMproducts
WHEREis_self = '非自营';

• 分组查询

分组查询关键字: group by分组查询基础格式: select 分组字段名,聚合函数(字段名) from 表名 group by 分组字段名;注意: select后的字段名要么在group by后面出现过,要么写到聚合函数中,否则报错...sql_mode=only_full_group_by分组查询进阶格式: select 分组字段名,聚合函数(字段名) from 表名 [where 非聚合条件] group by 分组字段名 [having 聚合条件];where和having的区别? 书写顺序: where在group by 前,having在group by后执行顺序: where在分组前,having在分组后分组函数: where后不能跟聚合条件,只能跟非聚合条件,having后可以使用聚合条件,也可以使用非聚合条件(不建议)应用场景: 建议大多数过滤数据都采用where,只有当遇到聚合条件的时候再使用having使用别名: where后不能使用别名,having后可以使用别名

-- 示例1：统计每个分类的商品数量
SELECTcategory_id,-- ② 再聚合：对每一组的id进行count计数COUNT(id) AS cnt
FROM products
-- ① 先分组：按照category_id进行分组
GROUP BY category_id;-- 示例2：统计每个分类中自营和非自营商品的数量
SELECTcategory_id,is_self,COUNT(id) AS cnt
FROM products
GROUP BY category_id, is_self;-- 示例3：统计每个分类商品的平均价格，并筛选出平均价格低于1000的分类
SELECT category_id,-- 再聚合AVG(price)
FROM products
-- 先分组
GROUP BY category_id
-- 对分组聚合的结果进行筛选
HAVING AVG(price) < 1000;

• limit查询

分页查询关键字: limit分页查询基础格式: select 字段名 from 表名 limit x,y;x: 起始索引,默认从0开始     x = (页数-1)*yy: 本次查询的条数注意: limit能完成topN需求,但是不能考虑到并列情况,此问题可以使用后期学习的开窗函数解决

- 示例1：获取所有商品中，价格最高的商品信息
SELECT*
FROM products
ORDER BY price DESC
LIMIT 1; -- LIMIT 0, 1;-- 示例2：将商品数据按照价格从低到高排序，然后获取第2页内容（每页3条）
SELECT*
FROM products
ORDER BY price
LIMIT 3, 3;-- 示例3：当分页展示的数据不存在时，不报错，只不过查询不到任何数据
SELECT * FROM products LIMIT 20, 10;

• 多表查询

连接类型	关键字	作用	通俗理解
内连接	INNER JOIN	查询两表交集	两边都有的数据
左外连接	LEFT JOIN	左表全部 + 右表匹配数据	左表全显示，右表没有显示 null
右外连接	RIGHT JOIN	右表全部 + 左表匹配数据	右表全显示，左表没有显示 null
全外连接	MySQL 无	两表所有数据	并集（MySQL 用左 + 右合并实现）

• 内连接

内连接关键字: inner join ... on显式内连接格式: select * from 左表 inner join 右表 on 关联条件;隐式内连接格式:  select * from 左表 , 右表 where 关联条件;注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表

• 左 / 右外连接

左外连接关键字: left outer join ... on
左外连接格式: select * from 左表 left outer join 右表 on 关联条件;右外连接关键字: right outer join ... on
左外连接格式: select * from 左表 right outer join 右表 on 关联条件;注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表

• 全外连接

注意: mysql中没有full outer join on这个关键字,所以不能用它来完成全外连接!

mysql中实现全外连接方式: 先查询左外连接和右外连接的结果,然后用union或者union all来实现!!!
union : 默认去重
union all: 不去重

• 自连接查询

自连接查询: 两个表进行关联时，如果左表和右边是同一张表，这就是自关联。注意: 自连接必须起别名!

• 子查询

子查询: 在一个SELECT语句中嵌入了另外一个SELECT语句那么被嵌入的 SELECT 语句称之为子查询语句注意: 子查询是辅助主查询的。

4. SQL执行顺序

书写顺序: SELECT -> DISTINCT -> 聚合函数 -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT
执行顺序: FROM -> WHERE -> GROUP BY -> 聚合函数 -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT