数据库SQL基础与实践-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_44292782/article/details/121406924

数据库知识回顾

select … as …, … as …, … as …, …
—> 窗口函数 over (partition by … order by …) —> 排名 / TopN
—> row_number / rank / dense_rank / lead / first_value / cume_dist
from t1, t2, …
[from t1 … join t2 on …]
where … and … or … —> like / regexp
group by …, … —> 聚合函数（max / min / avg / sum / count / stddev_pop / var_pop）
having … and … or …
order by … desc, … asc
limit … offset …

关系型数据库
理论基础：关系代数（关系运算、集合论、一阶谓词逻辑）

具体表象：用二维表组织数据
- 行（row）：记录（record） —> tuple —> 行数（cardinality）
~ 主键（primary key）
外键（foreign key）
- 列（column）：字段（field） —> attribute —> 列数（degree）

编程语言：SQL —> Structured Query Language —> 结构化查询语言
- 数据定义语言：create / drop / alter
- 数据操作语言：insert / delete / update
- 数据查询语言：select
select … as …, … as …, … as …, …
—> 窗口函数 over (partition by … order by …) —> 排名 / TopN
—> row_number / rank / dense_rank / lead / first_value / cume_dist
from t1, t2, …
[from t1 … join t2 on …]
where … and … or … —> like / regexp
group by …, … —> 聚合函数（max / min / avg / sum / count / stddev_pop / var_pop）
having … and … or …
order by … desc, … asc
limit … offset …
- 数据控制语言：grant / revoke
创建二维表
- 语法：create table tb_xxx (…) engine=innodb default charset utf8mb4;
- 数据类型的选择：
? data types —> ? int
~ 整数：int (integer) / bigint —> unsigned / primary key —> auto_increment
int —> -2^31 ~ 2^31-1 / 0 ~ 2^32-1
bigint —> -2^63 ~ 2^63 - 1 / 0 ~ 2^64-1
boolean —> tinyint —> 0 / 1
~ 小数：float（不推荐） / double（推荐）/ decimal
经验：涉及到钱的业务一律不使用小数，都用整数表示
~ 字符串：char / varchar（推荐使用）/ —> clob（text / longtext）—> 放文件路径或者资源的URL
字符集 —> utf8mb4 —> 排序规则 —> 默认
和字符串相关的函数：
- char_length / length / concat / format / left / right / substring / trim / ucase/ lcase
~ 日期时间：year / date / time / datetime / timestamp（不推荐）
和日期时间相关的函数：
- adddate / subdate / curdate / now / datediff / year / quarter / month / day / hour / minute / second / weekday
~ 二进制：blob （blob / longblob）—> 不推荐这么做！！！
~ 弱结构：json —> 灵活性
- JSON对象：{“key1”: “value1”, “key2”: “value2”, …}
- JSON数组：[elem1, elem2, …]
~ json_unquote(json_extract(col_name, ‘ $key1')) as alias_name ~ col_name ->> '$ .key1’ as alias_name
~ elem member of (col_name->’ $json_contains(col_name->'$ ’, ‘[e1, e2, …]’)
~ json_overlaps(col_name->’$’, ‘[e1, e2, …]’)
~ MySQL特有的类型：set / enum
- 约束条件
~ not null —> 非空约束
~ default —> 默认值约束
~ check —> 检查约束
~ primary key —> 主键约束
~ foreign key —> 外键约束
~ unique —> 唯一约束
- MySQL5.5+ 默认的引擎就是 innodb —> 底层数据结构 —> engine=innodb
~ 外键
~ 事务（把多个操作视为不可分割的原子性操作，要么全做，要么全不做）—> transaction —> 一致性
~ 行级锁（增删改的操作不需要锁住整个表，只需要锁住对应的行，能支持高并发）
删除和修改表
- drop table …;
- alter table … add column …;
- alter table … drop column …;
- alter table … modify column …;
- alter table … change column …;
- alter table … add constraint …;
- alter table … drop constraint …;
ER图和表关系
- 实体关系图 —> 设计表
~ 矩形框：实体 —> 表
~ 椭圆框：属性 —> 字段
~ 菱形框：关系
- 一对一：主键一对一 / 多对一特例（推荐）
- 一对多/多对一：在多的一方添加外键列
- 多对多：通过中间表将多对多转换成两个一对多关系

练习：
创建两张表，建议一对一关系
个人信息表（自动编号、姓名、性别、生日）
身份证表（自动编号、身份证号、有效期、发证机关）

create table tb_person_info
(
person_id bigint unsigned not null auto_increment comment ‘编号’,
person_name varchar(50) not null comment ‘姓名’,
person_sex boolean not null default 1 comment ‘性别’,
person_birth date not null comment ‘出生日期’,
primary key (person_id)
) engine=innodb comment=‘个人信息表’;

create table tb_idcard
(
card_id bigint unsigned not null auto_increment comment ‘编号’,
card_no varchar(18) not null comment ‘身份证号码’,
expire_date date not null comment ‘有效期’,
police_station varchar(50) not null comment ‘发证机关’,
person_id bigint unsigned not null comment ‘所有者’,
primary key (card_id),
constraint fk_idcard_person_id foreign key (person_id) references tb_person_info (person_id),
constraint uk_idcard_person_id unique (person_id)
) engine=innodb comment=‘身份证表’;

DML
insert
- insert into … values (…, …, …); —> 不推荐
- insert into … (…, …, …) values (…, …, …);
- insert into … (…, …, …) values (…), (…), (…);
- insert into … select … from …;

delete
- delete from … where key=…;
- 要点：
~ 删除操作一定要慎重的执行，一定是带条件的删除；
很多产品里面的删除操作，其实都不是真正的删除，可能是一个更新或者数据转移的操作

如果配置了bin-log，可以通过日志进行误删除的恢复（可以通过专业工具）
- 如果要删除全表而且不走日志 —> truncate table …;

update
- update … set …, … where key=…;
- show variables like ‘sql_safe_updates’;
- select @@sql_safe_updates;
- set @@sql_safe_updates=on; —> 不能够不带条件更新
- set @@sql_safe_updates=off; —> 不带条件更新全表

6.DQL

语法：
select … as …, … as …, … as …, …
—> 窗口函数 over (partition by … order by …) —> 排名 / TopN
—> row_number / rank / dense_rank / lead / first_value / cume_dist
from t1, t2, …
[from t1 … join t2 on …]
where … and … or … —> like / regexp
group by …, … —> 聚合函数（max / min / avg / sum / count / stddev_pop / var_pop）
having … and … or …
order by … desc, … asc
limit … offset … —> MySQL方言

表达式：
+ - * / %
函数

谓词：
= / <> / > / < / >= / <= / between … and …
like / regexp —> % _
in / not in
exists / not exists
is null / is not null

数据筛选
- where —> 分组之前的数据筛选
- having —> 分组以后的数据筛选（这里可以使用分组聚合的结果）

数据透视 —> 分组聚合（先将数据分成若干组，在组内使用聚合函数）
数据按某个维度拆解 —> 定位数据异常到底是什么引起的
根据 A 统计 B —> 根据性别统计男女学生人数 / 根据学号统计每个学生的平均成绩

嵌套查询 —> 把一个查询的结果作为另外一个查询的一部分来使用
派生表：把查询的结果当做表来使用

连接查询 —> 如果要查询的字段来自于多张表
如果没有连接数据的条件 —> 笛卡尔积
内连接
自然连接 —> 外键 / 同名列 —> 笛卡尔积
外连接（左外连接 / 右外连接）

7. DCL

创建用户
create user …@… identified by …;
drop user …;

授权
grant select on xxx.* to …;
grant all privileges on . to … with grant option;

召回权限
revoke select on xxx.* from …;

Python程序接入MySQL

~三方库：

- mysqlclient —> import MySQLdb
- pymysql —> import pymysql —> pip install pymysql
步骤：
1. 创建连接：connect(host, port, user, password, database, charset) —> Connection
  try-except-else:
  1. 获取游标：Connection --> cursor() —> Cursor
  2. 发送SQL：Cursor —> execute(’…’, (…)) / executemany()
2. 两种情况：
  增删改：Connection —> commit() / rollback()
  
  查询：Cursor —> fetchone() / fetchmany(size) / fetchall()
  finally:
  1. 关闭连接：Connection --> close()