MySQL

DDL(数据定义语言)

创建和查看表

格式

数据类型

数值类型

字符串类型

日期类型

相同点
用途相同：两者都用于存储日期和时间的组合值，通常包括年、月、日、小时、分钟和秒。
格式相同：它们的格式通常是 YYYY-MM-DD HH:MM:SS。
存储相同：在许多数据库系统中，它们都可以存储毫秒级别的精度。

不同点

1. 时间范围：
   Datetime：在 MySQL 中，Datetime 的有效范围是 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。这使得它适用于更广泛的时间范围需求。
   Timestamp：Timestamp 的有效范围是 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC。它的范围受到 UNIX 时间戳的限制（32位的整数）。
2. 时区处理：
   Datetime：Datetime 字段不处理时区。它存储的时间值与时区无关，并且在存储和读取时不会进行时区转换。
   Timestamp：Timestamp 字段会根据服务器的时区设置进行转换。当插入一个 Timestamp 值时，它会先将其转换为 UTC，然后存储。当读取该值时，它会将其转换回服务器的时区。
3. 自动更新：
   Datetime：Datetime 字段不会自动更新。
   Timestamp：Timestamp 字段可以配置为在每次行更新时自动更新（使用 CURRENT_TIMESTAMP 作为默认值并配置 ON UPDATE）。
4. 存储方式：
   Datetime：存储的是字符串形式的日期时间，通常使用8字节存储。
   Timestamp：存储的是从1970年1月1日开始的秒数，即 UNIX 时间戳，通常使用4字节存储。
5. 实际使用

Datetime 通常用于需要存储绝对时间且不涉及时区转换的场景，比如记录事件的发生时间、预约时间等。
Timestamp 更适用于需要记录和比较时间戳的场景，尤其是当数据在不同时区的系统之间传输时，如记录最后修改时间、创建时间等。

修改表结构

修改列
alter table student change dept department VARCHAR(30);

DML(数据操作语言)

数据插入

使用， 可以插入多条数据
INSERT into student (sid,name,gender,age,birth,address) 
VALUES (1001,'李四','男',18,'2001-07-23','南昌'),
(xxx),
(xxx);

数据修改

数据删除

MySQL约束

主键约束

单列主键

pk1是主键名，括号里是列名，pk1可以省略

联合主键

联合主键里的各列任何一个都不能为空

自增长约束

非空约束

唯一约束

NULL和任何值都不相等，NULL和NULL都不相等，所以设置了唯一约束的列是可以同时为NULL的

删除唯一约束

alter table <table name> drop index <唯一约束名>

默认约束

零填充约束

如果插入了123,则会变成0000000123(加了约束后int是10位)

外键约束

create table if not exists dept(
  deptno varchar(20) PRIMARY key,
  name varchar(20)
)
create table if not EXISTS emp(
  eid varchar(20) PRIMARY KEY,
  ename VARCHAR(20),
  age int,
  dept_id VARCHAR(20),
  CONSTRAINT emp_fk FOREIGN key (dept_id) REFERENCES dept(deptno)
)
alter table emp add CONSTRAINT emp_fk FOREIGN key(dept_id) REFERENCES dept(deptno)

如果一个从表拥有两个外键，那么另外两个表为主表，主表里受到从表依赖的数据不能随便删除，但是从表数据可以随便删除

DQL(数据查询语言)

简单查询

运算符

条件查询

排序查询

字段类型必须为数值或者字符串，时间类型排序

聚合查询

分组查询

ep:

select category_id,sum(price) sum from product GROUP BY category_id having sum >= 6000 order by sum desc

分组之后，select的后面只可以跟分组字段(category_id)和聚合操作
筛选条件不能使用where只能使用having

分页查询

分页查询公式:
如果想查询第n页，假设一页有60条，则查询公式为:

select * from product limit (n-1)*60,60
// 查询第n页的前5条
select * from product limit (n-1)*60,5

insert into pruduct2 select category_id, count(*) from product group by category_id

书写顺序和执行顺序

正则表达式

a？匹配0次或一次a

返回值为1or0
可以把REGEXP作为where的条件

以九开头的名称
select * from product WHERE pname REGEXP '^九'
以九结尾
select * from product WHERE pname REGEXP '九$'

select * from product WHERE pname REGEXP '(abab)*'

(abab)序列匹配，直接匹配括号里的字符，可以组合其他符号
(abab)* 出现0次或多次
‘.(abab){2,4}d’,中间至少出现2次至多出现4次

多表操作

多表关系

多表联合查询

交叉连接查询

内连接

内和外表示的是：
"内"指的是只返回两个表中都有的记录，即"内部"共有的部分。任何在连接条件下不匹配的记录将被排除在结果集之外。
"外"指的是除了返回匹配的记录，还包含了一个表中没有匹配到的记录。具体分为左外连接、右外连接和全外连接：

外连接

左外连接（LEFT JOIN）：
返回左表中的所有记录，即使右表中没有匹配的记录。对于右表中没有匹配的记录，其结果集中的字段会包含NULL。

右外连接（RIGHT JOIN）：
返回右表中的所有记录，即使左表中没有匹配的记录。对于左表中没有匹配的记录，其结果集中的字段会包含NULL。

全外连接（FULL JOIN）：
返回两个表中的所有记录。如果在任一表中没有匹配的记录，其结果集中的字段会包含NULL。


union将两个结果去重，union all则是将两个结果拼在一起

子查询

最后一个需要补充 on t1.deptno=t2.dept_id

标量子查询（Scalar Subquery）：
返回单个值（单行单列）。
常用于SELECT、WHERE、HAVING和ORDER BY子句中。

SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

行子查询（Row Subquery）：
返回单行多列。
可以用于与行比较的情况。

SELECT employee_name, salary, department_id
FROM employees
WHERE (salary, department_id) = (SELECT MAX(salary), department_id FROM employees);

表子查询（Table Subquery）：
返回多行多列（通常用于IN、EXISTS子句中，或作为临时表）。
可以用于多种上下文中，如FROM子句中的临时表。

SELECT employee_name, salary, department_id
FROM employees
WHERE (department_id, salary) IN (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);

子查询关键字

如上级字段，其manager_id必须是本表主键里的eid里的某个存在的值

函数

聚合函数

select group_concat(emp_name separator ';') from emp;

select department,group_concat(emp_name order by salary desc separator ';') from emp group by department;

数学函数

字符串函数

日期函数

控制流函数

额外查询出多一个列

窗口函数

序号函数

求部门里薪资排在前三的员工

开窗聚合函数

sum进行累加

如果没有使用order by排序，则默认把分组内所有数据进行sum操作，即c1值为部门薪资和.
这段语句其实是使用了如下默认语句

unbounded preceding是指直到开头行，到当前行current row,进行累加

将当前行上方三行的值加到当前行（上三行包括当前行）

向上三行到最后一行（该组内的最后一行）

当前行加到最后一行

分布函数

第一个显示小于等于当前行的salary的行数/总记录数
第二个显示小于等于当前行的salary的组内的行数/组内总行数

分布装置

前后函数

头尾函数

是到目前为止，因为按照入职日期排序了

其他

如果按照时间排序了，就是截止到当前行

视图

创建

create or replace view view1
as 
select ename,job from emp

创建完视图之后，将视图当做表一样操作它

修改

更新

其他操作

存储过程

也可以使用//充当结束符

变量

局部变量

用户变量

系统变量

参数传递

流程控制

IF


CASE

LEAVE&ITERATE

WHILE


标签是可选项

REPEAT

LOOP

游标

直到游标把所有行都取完，然后报错（产生异常），loop才结束

句柄

游标取空的错误码为1329

案例

存储函数

触发器

创建

NEW & OLD

索引

索引种类

索引操作

普通索引

创建


查看



删除

唯一索引

创建

主键索引

使用show index from table才能看见

组合索引

全文索引

最好是表中有了数据之后再去创建全文索引

空间索引

索引原理

HASH

二叉树

二叉排序树

B树

索引特点

存储引擎

事务

回滚之后撤销提交的结果

特性

隔离级别

读未提交：（脏读）

不可重复读（读已提交）：

可重复读：（幻读）

可重复读：事务开启时，不再允许修改操作，但是其他事务可以插入和删除

锁

表锁

加完之后只能读，不能修改，且只能读该表，不能读其他的表

行锁

日志

错误日志

二进制日志

查询日志

修改myini文件

慢查询日志

优化

查看SQL执行频率

定位低效率SQL

Explain分析执行计划

id

select_type

type

其他指标

key_len的长度

Show profile分析

trace分析优化器

用mysql终端查询

索引优化

避免索引失效

全值匹配

查询条件字段和索引字段完全匹配

最左前缀法则

第一第二个查询使用到了索引，因为使用了name
第三个没用到name，因为是从左到右扫描索引
第四个只使用了name，因为跳过了status

其他原则

第一个address没使用到索引

数据中较多北京市，不使用索引
较少西安市，使用索引

大批量插入数据

通过load向表加载数据时，保证主键有序可以提高执行效率

如果数据某一列已经是唯一的，那么可以关闭唯一索引，插入后再开启，可以提高执行效率

SQL优化

insert

order by

子查询

尽量使用多表查询

limit

1.

2.

JDBC

优化结果集

增删改

SQL注入

pymysql