基础

三类语句：

1、DDL：定义库、表、索引等结构

2、DML：插入、更新、删除数据

3、DQL：查询数据

高频查询先练熟：连接、分组、分页、日期统计。

1
2
3
4
SELECT sc.SId, AVG(sc.score) AS avgscore
FROM sc
GROUP BY sc.SId
HAVING AVG(sc.score) >= 60;

常用操作

高频操作：

1、连接数据库

2、导入 SQL 文件

3、日期范围查询

4、表结构和字段类型

5、基础运维命令

常见问题：

1、mysql 命令找不到，通常是环境变量没有指到 bin。

2、通过 source 导入 SQL 文件时，路径分隔符写错。

3、建库、切库、建表这些操作会做，但对 DDL、DML、DQL 的边界没有清楚认知。

4、JDBC 导大字段时报 Packet for query is too large，通常是 max_allowed_packet 太小。

1
2
3
create database school;
use school;
show tables;

Packet for query is too large 常见处理：

1
2
SHOW VARIABLES LIKE '%max_allowed_packet%';
set global max_allowed_packet = 1024*1024*10;

如果只是临时改，MySQL 重启后会失效；要永久生效，需要在 my.ini 或对应配置文件里加上：

1
max_allowed_packet=10485760

查询

高频模式：

1、分组聚合与 HAVING

2、子查询与关联查询

3、分页、排序、去重

4、exists、in、left join 的场景差异

5、日期维度统计

1
2
3
4
select sc.SId, AVG(sc.score) as avgscore
from sc
group by sc.SId
having AVG(sc.score) >= 60;

练习时至少把下面几类写顺：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select *
from sc
where sc.sid not in (
  select sid
  from sc
  where cid = '01'
);

select *
from student
where exists (
  select 1
  from sc
  where sc.sid = student.sid
);

select sc.sid, sum(sc.score) as total_score, count(sc.cid) as course_count
from sc
group by sc.sid;

exists 更偏存在性判断，in 更偏集合匹配，复杂查询先判断谁做驱动表。

分页和条件分级也很常用：

1
2
3
4
5
6
7
8
9
10
select s.sid,
       s.score,
       case
         when s.score >= 90 then 'A'
         when s.score >= 80 then 'B'
         when s.score >= 70 then 'C'
         when s.score >= 60 then 'D'
         else 'E'
       end as level_name
from sc s;

时间查询模板：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select * from create_time where TO_DAYS(create_time) = TO_DAYS(NOW());

select count(1)
from kunyao_shop_order
where is_payed = 1
  and TO_DAYS(NOW()) - TO_DAYS(create_time) = 1;

SELECT *
FROM wx_fund_record
WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d'), 1) = YEARWEEK(now(), 1);

SELECT *
FROM wx_fund_record
WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

最近 7 天趋势一般先造日期序列，再左关联业务表，用 IFNULL 补 0。

时间

高频统计：

1、查当天数据

2、查昨天或过去几天的数据

3、查本周、本月数据

4、查一段时间内每天的趋势数据

timestamp、datetime、字符集、排序规则、在线 DDL 都要知道。

时间差计算：

1
2
3
4
select to_days(now()) - to_days('20200827');
select datediff(now(), '20200827');
select timestampdiff(week, '2020-09-29', '2020-10-04');
select timestampdiff(day, '2020-10-01', '2020-10-07');

补几条基础判断：

1、字符集决定存什么，排序规则决定怎么比较和排序

2、timestamp 更省空间，但和时区相关

3、datetime 存字面值，范围更大，业务时间更常用

事务

事务先记 3 个并发问题：

1、脏读：读到别的事务未提交的数据

2、不可重复读：同一事务里两次读取同一行，结果不一致

3、幻读：同一事务里两次按条件查询，结果集条数不一致

常见隔离级别：

1、READ UNCOMMITTED

2、READ COMMITTED

3、REPEATABLE READ

4、SERIALIZABLE

InnoDB 默认是 REPEATABLE READ，隔离更强，但并发下还是要结合锁和 SQL 场景看。

查看和设置隔离级别：

1
2
3
4
5
6
7
8
SELECT @@transaction_isolation;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
SELECT *
FROM student
WHERE sid = '01';

SERIALIZABLE 隔离效果最好，但吞吐最差，排查并发问题时先确认业务到底要不要上到这一档。

数据库常用操作速查

连接和导入

1
2
3
4
5
mysql -u root -p
show databases;
use databaseName;
show tables;
source d:/demo.sql;

函数

1
2
3
4
5
6
SELECT *
FROM user
WHERE IF(id = 1, count < 10, count > 500);

SELECT GROUP_CONCAT(ENIN_NAME)
FROM sys_c_en_entityinfo;

变更和归档

1
ALTER TABLE table_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
1
2
3
4
5
6
CREATE TABLE order_info_tmp LIKE order_info;

INSERT INTO order_info_tmp
SELECT *
FROM order_info
WHERE create_time >= '2021-01-01 00:00:00';

归档步骤：

1、先建结构一致的中间表

2、按时间或主键范围把保留数据插进去

3、确认无误后再做清理或迁移

Oracle

旧项目常见内容：

1、用户与权限操作

2、imp/dmp 导入导出

3、序列与自增主键实现

4、基础函数和模糊查询

按月统计和自增主键是最常见的两类：

1
2
3
4
select to_char(t.create_time, 'yyyy-mm') 月份, count(*) 数量
from table_name t
group by to_char(t.create_time, 'yyyy-mm')
order by 月份;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table mall_roles (
  id number primary key not null,
  role_name varchar2(32)
);

create sequence mall_roles_id
minvalue 1
start with 1
increment by 1;

create or replace trigger mall_roles_tg_insertId
before insert on mall_roles
for each row
begin
  select mall_roles_id.nextval into :new.id from dual;
end;

如果是 Oracle 分页或分析函数，也要能看懂 row_number() over(order by ...) 这类写法。

Oracle 老库里还经常会碰到几类典型报错：

1、ORA-00918: 未明确定义列，通常是联表查询时列名重复，select 里没有带表别名。

1
2
3
select a.name, b.name
from a
left join b on a.flag = b.flag;

2、ORA-01451，建表或改字段时把主键列错误地设成允许为空，去掉可空约束后再提交。

3、ORA-01658 或 ORA-01659，本质上是表空间不足，要先确认表空间大小、使用情况和数据文件路径。

排查表空间时常用：

1
2
3
4
5
6
7
8
9
10
select tablespace_name, sum(bytes) / 1024 / 1024
from dba_data_files
group by tablespace_name;

select segment_type, owner, sum(bytes) / 1024 / 1024
from dba_segments
where tablespace_name = 'XITONG'
group by segment_type, owner;

select * from dba_data_files;

扩容方式一般有三种：

1
2
3
4
5
alter tablespace YWGOV add datafile 'F:\SOFT-INSTALL-SOFT\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\HOUTAI1.ORA' size 100M;

ALTER DATABASE DATAFILE 'F:\SOFT-INSTALL-SOFT\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\HOUTAI.ORA' RESIZE 1024M;

ALTER DATABASE DATAFILE 'F:\SOFT-INSTALL-SOFT\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\HOUTAI.ORA' AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;

后续

继续看：

1、索引与执行计划

2、事务隔离与锁机制

3、MySQL 存储引擎与底层实现