导图社区 MySQL基础
- 491
- 22
- 9
- 举报
MySQL基础
小白必看《MySQL基础》，注释内含大量解释，主要内容有数据库概念、MySQL数据库、SQL概述、SQL命令。无他唯手熟尔！
提示: 本内容由社区用户上传并分享。平台不对内容的真实性、合法性、知识产权归属及是否侵害第三方权利进行事前审核或保证。本内容可能包含受版权保护的图片、字体或其他第三方素材，使用前请自行确认授权范围。
- mysql
- MySQL基础
- 相似推荐
- 大纲
MySQL基础
数据库概念
数据库：存储和管理数据的仓库
数据库分类
数据库概念
数据库：存储和管理数据的仓库
数据库分类
关系型数据库
MySQL
非关系型数据库
Redis、HBase
数据库特点
持久化存储
读写速度极高
保证数据的有效性
类型和约束保证数据有效性
MySQL数据库
MySQL服务器安装和使用
Linux镜像已经安装MySQL服务端和客户端，直接可以使用
MySQL客户端安装和使用
命令行客户端
登录
mysql -uroot -p123456 -h192.168.88.100
退出
exit
quit
ctrl + d
Navicat
DataGrip
SQL概述
结构化查询语言
所有关系型数据库通用 语言 (普通话）
分类
DDL 数据定义语言
建库，建表，删库，删表，查看所有库，查看所有表，查看表结构，修改表结构等。 create，alter，drop，show
DML 数据操作语言
增，删，改 数据 insert, delete, update
DCL 数据库控制语言
授权，安全，创建用户，设置密码等
DQL 数据查询语言
select
语法
关键字忽略大小写（建议大写）
可以写成单行或者多行，必须以`;` 结尾
可以使用使用缩进或者空格提高可读性
可以使用 # - /* */ 注释
类型
数字类型
整数
int
小数
double ...
文本类型
char
定长：char(10) 这个字段一定占10个字符
varchar
变长：varchar(10) 实际长度看数据，最大是10个字符
text
时间类型
date
time
datetime
约束
primary key 主键约束
1.唯一 2.非空 3.一张表只有一个主键 主键作用：唯一标识一行记录。
auto_increment 自增约束:
1.对键设置自增 (一般设置主键自增) 2.对int类型设置自增 一般配合主键设置自增约束。
not null 非空约束
设置该字段不能为空。 主键默认是非空的，所以设置了主键，不需要设置非空。
unique 唯一约束
1.该字段内容必须唯一 2.可以为null 3.一张表可以设置多个唯一约束 唯一约束 也叫唯一键。可以设置自增。
default 默认约束
如果没有设置当前字段值，那么使用默认值
SQL命令
DDL 数据定义语言
create alter show drop
库基本操作
show databases;
create database 库名 charset=utf8;
use 库名;
select database();
drop database 库名;
show create database 库名;
表基本操作
show tables;
create table 表名( 字段名 类型(长度) [约束], .....);
desc 表名;
alter table 表名 add 字段 类型(长度) [约束];
alter table 表名 change 旧字段名 新字段名 类型(长度) [约束];
alter table 表名 drop 字段名;
drop table 表名;
show create table 表名;
rename table 旧表名 to 新表名;
DML 数据操作语言
insert
insert into 表名 values(值1, ...);
全部列插入。values中(值)的个数跟表字段个数一致.
insert into 表名(字段1, ...) values(值1, ...)
部分列插入, values中(值）个数和前面的字段个数一致;
insert into 表名 values(值1,...),(值1,...)
多行全列插入
insert into 表名(字段1,...) values(值1,...),(值1,...)
多行部分列插入
update
update 表名 set 字段名=值;
该字段所有行都修改成该值;
update 表名 set 字段名=值 where 条件;
该字段满足条件的行被修改成值;
delete
delete from 表名;
逐行删除所记录
truncate 表名;
删除表，重新创建表。
delete from 表名 where 条件;
满足条件的删除
逻辑删除：修改数据
DQL 数据查询语言
查询格式
select * from product;
select pname,price from product;
设置别名： as
select p.pname,p.price from product as p;
select pname as pn, price as pi from product;
as可以省略
去重: distinct
select distinct price from product;
distinct:去重 去除重复的行（整行的所有字段都相同，才去重）
查询表达式
select pname,price+10 from product;
条件查询
运算符
比较运算符: >, <, >=, <=, =, !=, <>
范围查询: 连续范围: between ... and ... 非连续范围: in (100, 200)
模糊查询: like "%_" : %（0个或者多个任意字符) _ （一个任意字符）
空值判断: is null, is not null,
逻辑运算符: and : 条件1 and 条件2 (条件1和条件2同时满足） or ：条件1 or 条件2 （条件1或者条件2 只要满足一个） not ：取反
select * from product where price > 800;
select * from product where price between 200 and 800;
select * from product where price in (200, 800);
select * from product where pname like "%霸%";
select * from product where pname like "_想%";
select * from product where category_id is null;
select * from product where category_id is
select * from product where price >= 200 and price <= 800;
排序查询
order by 字段 asc|desc
order by 字段名: 按照字段名的值排序 asc : 升序(默认) desc : 降序
order多列排序
ORDER BY 字段名 DESC, 字段名 ASC
聚合查询 （聚合函数不能嵌套，可以使用with as分开查）
count
记数用，统计指定列非空数据的行数
sum
统计指定列非空数据的和 注意：如果指定列不是数字类型，结果为0
max
统计指定列非空数据的最大值
min
统计指定列非空数据的最小值
avg
统计指定列非空数据的平均值 注意：如果指定列不是数字类型，结果为0
对查询结果进行自定义格式返回的函数
保留指定位数的小数
ROUND（具有返回值的语句，数值）
ROUND（...，2）将比率四舍五入到小数点后两位
分组查询
group by 字段 having 条件
group by 字段 : 按照字段的值相同进行分组. having 条件 : 对分组后的数据进行过滤. having 和 where 的区别: 1. having 对分组后的数据进行过滤. where 对分组前的数据进行过滤. 2. having 后可以使用 分组函数. where 后不可以使用 分组函数
分页查询
limit m, n
limit 起始索引，显示行数 起始索引：默认是0，从0开始 分页显示：公式 起始索引 = (当前页数 - 1) * 每页显示的行数
表复制
复制旧表结构及内容到新表（1步）
create table 新表 select * from 旧表;
完整复制出一张表（2步）
1. 复制表结构
create table 新表 like 旧表
2. 复制内容
insert into 新表 select * from 旧表
外键
一对多关系
一表：班级 多表：学生 一表：客户 多表：订单 一表：部门 多表：员工 表与表的关系: 一对一 ： 人 对 身份证 一对多 ：班级 对 学员 多对多 ：学员 对 课程 学员表 课程表 选课表 主键 主键 两个外键 1 01 1 -> 01 2 02 1 -> 02 3 4
多表新建一个字段(外键字段)存储另一张表主键
外键约束
1.对 从（多）表进行 增改 ，需要查询主表，验证是否可以操作
2.对 主（一）表进行删除时，需要查询从表，验证是否可以删除
外键约束使用
1.创建表时设置外键约束
CREATE TABLE products( pid VARCHAR(32) PRIMARY KEY , pname VARCHAR(50), price INT, flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架 category_id VARCHAR(32), FOREIGN KEY (category_id) REFERENCES category (cid) );
2.修改表结构设置外键约束
alter table product add FOREIGN KEY (category_id) REFERENCES category (cid);
3.删除外键约束
show create table product； alter table product drop foreign key product_idfk_1;
帮助
help 命令
多表查询
交叉连接查询
select * from A, B
内连接查询
隐式内连接查询
select * from A, B where 条件
显式内连接查询
select * from A [inner] join B on 条件
外连接查询
左外连接
select * from A left [outer] join B on 条件
右外连接
select * from A right [outer] join B on 条件
子查询
一条完整的查询，嵌入到主查询中，充当条件、数据源、表等。
标量子查询
子查询返回一行一列数据
select * from products where price>(select avg(price) from products));
行子查询
子查询返回一行数据
select * from products where price in (查询返回一行数据);
列子查询
子查询返回一列数据
select * from products where category_id in (select cid from category where cname="服饰" or cname="家电");
表子查询
子查询返回一张表
select p.*
合并查询 union
合并两表数据并去重
union
SELECT * FROM BOOKS UNION SELECT * FROM MOVIES
注：列数必须相同，列名不必相同
合并两表数据
union all
SELECT * FROM BOOKS UNION ALL SELECT * FROM MOVIES
视图 view
视图（VIEW）是⼀条SQL语句，不占用储存空间，是虚拟表 每次查询视图时，都会查询视图下的⼀个或多个表来再次获取数据
创建视图
create view 视图名 as select语句
CREATE VIEW aaa AS SELECT * FROM bbb
检查视图
check table 视图名
删除视图
drop view 视图名
修改视图
ALTER VIEW <视图名> AS <SELECT语句>
CREATE VIEW aaa AS SELECT id, name FROM bbb
REPLACE VIEW aaa AS SELECT id, name FROM bbb
索引 index
作用: 快速检索数据（提高查询效率）
副作用：增删改查效率降低
索引结构
BTree

B+Tree

普通索引
创建：
create index 索引名 on 表名(列名);
alter table 表名 add index 索引名(列名);
查看:
show index from products;
使用:
查询时，使用该字段进行过滤。就会自动使用索引.
select * from products where price=200;
删除:
drop index my_index on products;
alter table products drop my_index;
唯一索引
创建
create unique index my_index on category(cname(20));
查看
show index from category;
删除
drop index my_index on category;
窗口函数 over() 本身也是聚合函数
把源数据变成一块一块窗口处理
窗口函数 over()
源数据后面加一列显示开窗函数结果
select *, avg(salary) over() from employee;
利用分组字段来规定窗口大小
over(partition by 分组字段)
select *, avg(salary) over(partition by deptid) from employee;
对窗口内的数据进行排序
over(partition by 分组字段 order by 排序字段)
select *, rank() over(partition by deptid order by salary) from employee;
排序函数
row_number(列名) 会返回唯一行号
rank(列名) 会返回每一行的序号(等级)，若并列则存在跳跃
RANK() OVER (ORDER BY ...)
dense_rank(列名) 会返回每一行的序号(等级)，并列不跳跃
DANSE_RANK() OVER (ORDER BY ...)
ntile(数字) 对排序后的数据进行等比分组，分成X组，多出来的数据会放到前面的组
SELECT id, country, city, rating, NTILE(2) OVER(PARTITION BY country ORDER BY opening_day) FROM store; 
NTILE(3) OVER (ORDER BY ...)
将over结果用于进一步计算
SELECT *， AVG(列1) OVER(), 列2 - AVG(列1) OVER() as difference FROM 表名;
自定义窗口框架（Window frames）
以每行方式定义窗口大小
rows
以每行方式定义窗口大小(允许并行)
range
between 上限 and 下限
BETWEEN ... AND ... 的意思是在... 之间
定义窗口相对大小
当前行到上限无限制
rows unbounded preceding 简写
rows between unbounded preceding and current row
当前行到下限无限制
rows between current row and unbounded following
当前行到往上的第 n 行
rows 3 preceding 简写
当前行到往上的第 n 行
rows between current row and 3 following
仅当前行
rows current row
注意事项
range 一般不与 n PRECEDING 或 n FOLLOWING 一起使用，因为窗口大小很可能不固定
默认的window frames
如果在over()中指定了order by子句，则会默认添加 range unbounded preceding 作为窗口框架
分析函数 （分析函数只引用窗口中的单个行）
当无返回值时，显示null空值
返回指定x列当前行的下一行
lead(x)
SELECT name, opened, LEAD(name) OVER(ORDER BY opened) FROM website; 
返回指定x列当前行的下n行
lead(x,n)
SELECT name, opened, LEAD(opened,2) OVER(ORDER BY opened) FROM website;
返回指定x列当前行的下n行，空值时默认为z
lead(x,n,z)
lead(字段名,偏移量,默认值)
返回指定x列当前行的上一行
lag(x)

返回指定x列当前行的上n行
lag(x,y)
返回指定x列当前行的上n行，空值时默认为z
lag(x,n,z)
lag(字段名,偏移量,默认值)
返回指定x列的第一个值
first_value(x)
可以不写范围
返回指定x列的最后一个值
last_value(x)
要写范围 rows|range between unbounded preceding and unbounded following
返回指定x列的第n个值
nth_value(x,n)
要写范围 rows|range between unbounded preceding and unbounded following
窗口函数 避坑指南
不能使用窗口函数的情况
窗口函数只能出现在 SELECT 和 ORDER BY 的子句中
如果（WHERE，GROUP BY，HAVING）的子句需要窗口函数，请使用子查询，在子查询中使用窗口函数，因为SQL执行顺序决定了窗口函数在上述三者之后执行
在ORDER BY中使用窗口函数

窗口函数与GROUP BY一起使用

此时窗口函数只能处理分组后的结果，而不是原始的表数据
利用GROUP BY计算环比：
SELECT ended, SUM(views) AS `sum`, LAG(SUM(views)) OVER(ORDER BY ended) AS previous_day, SUM(views) - LAG(SUM(views)) OVER(ORDER BY ended) AS delta FROM auction GROUP BY ended ORDER BY ended;
对GROUP BY分组后的数据使用 PARTITION BY

我们可以对GROUPY BY分组后的数据进一步分组（`PARTITION BY`） ，再次强调，使用`GROUP BY` 之后使用窗口函数，只能处理分组之后的数据，而不是处理原始数据
select 执行流程：
select 执行顺序： from -> where -> group by -> 聚合函数 -> having -> 窗口函数 -> select -> distinct -> union -> order by -> offset -> limit ...
自定义分组 case when
【目的】想添加一个新列，从而对数据进行自定义分组，可以在`SELECT`子句中使用`CASE
【过程】创建了一个新列， 通过 `CASE WHEN` 语句来对这一列赋值
【语法】 case when 条件1 then 返回值1 when 条件2 then 返回值2 ... else 返回值3 end as 别名
如果没有匹配条件，且没有`ELSE`子句，则`CASE WHEN`返回`NULL`
自定义分组并统计每组数据数量：
select count (case when 条件 then 返回值 end) as 别名1 count (case when 条件 then 返回值 end) as 别名2 from 表名
公共表表达式 CTE
【语法】 with 公共表名 as (select语句) select ... from 公共表名
其实就是给一个查询语句指定别名，搞成临时表，后面可以接着对临时表进行查询
【多层聚合】 with 公共表名1 as (select语句)， 公共表名2 as (select ... from 公共表1)， 公共表名3 as (select ... from 公共表2) select ... from 公共表3
【注意】前面with子句定义的查询，可以被后面的with子句使用。但with子句的内部不能嵌套with语句
数据报表例子
分组对比
按行对比
【比如】 1. 先使用with公共表 2. 然后对公共表进行查询得到最终结果

按列对比
【比如】 1. 使用case when 2. 再使用case when

多行多列对比
【比如】 1. 先使用group by #对行进行分组，进行多行对比 2. 然后使用case when #对某列数据进行自定义分组并返回值到新列，进行多列对比 3. 再使用case when #对某列数据进行自定义分组并返回值到新列，进行多列对比

占比对比
【比如】 1. 先使用with公共表 2. 然后对公共表进行查询得到最终结果