sql语法

0.缩写

db  //数据库
tbl //表
fld //字段
typ //类型
_   //用下划线拼接区分不同如 name a/b/c x/y/z 1/2/3
    //我们尽量保证前缀相同

1.基本的sql

①.库操作

show databases;
create database db_name;
use db_name;
drop database db_name;

②.表操作

show tables;
desc tbl_name;
drop table tbl_name;
alter table tbl_name add/modify/change/drop/rename to ...;
show create table tbl_name;      //显示创建这样的表需要的创建语句
create table tbl_name (          //可以用show create table tbl_name;然后对着写
    id int unsigned not null auto_increment comment '用户id',
    username varchar(50) not null unique comment '用户名',
    email varchar(100) not null comment '邮箱',
    password varchar(255) not null comment '加密密码',
    primary key (id)
) engine=innodb default charset=utf8mb4;

③.表数据操作

insert into tbl_name (fld1, fld2, fld3)
values
(fld1_val1, fld2_val1, fld3_val1),
(fld1_val2, fld2_val2, fld3_val2),
...;

update table set fld1=val1, fld2=val2 where 条件;
delete from tbl_name where 条件;
select from where group by having order by limit;

④.用户管理 用户名+ip 构造用户的唯一索引

create user `用户名`@`ip` identified by `密码`;
alter user `用户名`@`ip` identified with mysql_native_password by `密码`;
drop user `用户名`@`ip`;
grant 权限列表 on db_name.tbl_name to `用户名`@`ip`;
revoke 权限列表 on db_name.tbl_name from `用户名`@`ip`;
flush privileges;

2.关键字的执行顺序

3.约束

①.所有约束

②.外键约束的行为

4.多表

①.多表的关系

②.标量子查询

select * from 员工表 where 部门id = (select id from 部门表 where name = '销售部');

③.列子查询

select * from 员工表 where 部门id in (select id from 部门表 where name = '销售部' or name = '市场部');
select * from 员工表 where fld_工资 > all (select fld_工资 from 员工表 where 部门id = (select id from 部门表 where name = '财务部'))
select * from 员工表 where fld_工资 > any (select fld_工资 from 员工表 where 部门id = (select id from 部门表 where name = '财务部'))

③.行子查询

select * from 员工表 where (fld_工资，fld_leader) = (select fld_工资, fld_leader from 员工表 where name='张三丰');

④.表子查询

select e.*, d.* from (select * from 员工表 where fld_入职时间 > '2025-06-18') e left join 部门表 d on e.部门id=d.id

⑤.子查询可以在select/where/from之后

select
  e.name,       -- 查询员工名字
  (select d.fld_部门名 from 部门表 d where e.部门id=d.id) as department_name --查部门名
from
  员工表 e;  -- 员工表为主表

5.笛卡尔积

①.表tbl_a有字段fld_a，表tbl_b有字段fld_b，把fld_a的第一行和fld_b的每一行组合
②.如果是on fld1=fld2的jion条件那么把 fld_a的第一行和fld_b的每一行组合只有等于fld_a第一行的会保留下来
③.左连接是以左边的表为基准查找，右连接是以右边的表为基准查找
  比如tbl_a left join tbl_a， 即使fld_b的所有行都不等于fld_a的第一行，但是因为fld_a是基准表所以第一行会被保留

6.explain

①.id: 查询编号
   体现在多表查询join或者有子查询的时候
   id越大(子查询)会优先执行，结构会传给id小的(父查询)，id相同的由优化器决定哪个先执行
②.select_type: 更多的是描述当前id在多表查询中的位置 所以意义并不是很大

④.partitions: 如果用了分区表，这里显示命中的分区；否则为 NULL

⑤.type: 表的访问类型，效率从高到低排序

 eq_ref 案例
 为什么说eq_ref比ref快都是在join的时候，如果驱动表关联被驱动表的字段不是主键/唯一键 而是普通索引 那就是ref
 在没有join的时候 eq_ref就是const

SELECT *                           //整体的时间复杂度 O(m*log(n)）
FROM orders o                      //orders:驱动表 外层循环，每次提供一行数据  时间复杂度1次遍历 O(m)
JOIN users u ON o.user_id = u.id;  //user:被驱动表 内层循环，针对驱动表的每一行做匹配 时间复杂度 O(log(n))

⑥.possible_key: 优化器判断”可能”会用到的索引
⑦.key: 实际使用的索引
⑧.key_len: 使用的索引长度(估计) (单位:字节)
     精度是以字段为单位:只会出现使用这个字段或者没有使用这个字段不会说用了0.5个该字段
     实际使用的联合索引字段要用EXPLAIN FORMAT=JSON 查看used_key_parts字段
     例如有abc字段的联合索引，如果查找条件是a=1&b>=1&c=1那么就只会用到ab两个字段
     联合索引在遇到第一个范围查询(>, <, >=, BETWEEN, LIKE 非前缀) 后就“停止向后匹配”了
⑨.rows: mysql预估的扫描行数
⑩.filtered: 预估扫描行里面有%多少个点会通过WHERE过滤条件 越大越好
⑩.Extra: 附加信息，例如是否使用索引、是否临时表、文件排序等

7.索引

①.

②.

③.

④.

⑤.

⑥.

⑦.告诉mysql用哪个索引

//use index:    建议mysql使用哪个索引
//ignore index: 告诉mysql忽略哪个索引
//force index:  强制mysql使用哪个索引
select * from tbl_user user index(idx_user_pro) where profession='计算机'

⑧.索引设计原则

1.针对数据量大，且查询频繁的表建立索引
2.针对查询条件where/order by/group by的操作字段建索引
3.选择选择性高的字段作为索引，尽量使用唯一索引(选择性=1)
4.如果是字符型字段，字符比较长，可以选择前缀索引
5.尽量使用联合索引，索引覆盖可以避免回表
6.索引数量越多，增删改的效率就会降低
7.如果字段不为null时，需要用not null来约束，否则该字段作为索引时会多出一个bit来标识是否为null

8.优化

①.insert优化
  如果插入的数据过多可以选择load(注:load也要按主键顺序插入)，语法如下

mysql --local-infile -u -p
set global local_infile=1;
load data local infile '/data/sql.log' into `tbl_name` fields terminated by ',' lines terminated by '\n';

②.order by优化
  尽量使用索引覆盖，并且order by的顺序和联合索引顺序一致

type:ALL             //扫描全表
type:index:          //索引覆盖
Extra:Using filesort //在缓冲区完成排序 如果不可避免使用这个方式 可以调节排序缓冲区的大小sort_buffer_size(默认256k)
                     //如果缓冲区不够大，会涉及磁盘排序，效率会更低
Extra:Using index    //通过索引直接返回有序的数据

③.group by优化
  尽量按索引顺序group by，如果没有索引或者没有按索引顺序group会使用临时表 (Extra: Using temporary)
④.limit优化
  limit 2000000, 10 mysql需要排序前面的2000000条数据最后返回2000000-2000010的记录
  可以记录前200w的最大主键id，查询: WHERE id > id_max_200w ORDER BY id LIMIT 10;
  如果查找条件是联合索引，那就得是索引覆盖
⑤.count优化
  MyISAM引擎把有多少行这个统计数据记录在磁盘了，执行count(*)的时间复杂度是O(1)
  InnoDB的几种count方法效率从低到高

⑥.update优化
  InnoDB的行锁是基于索引加的锁，不是直接锁住数据行本身。
  如果不使用索引(或索引失效) 导致全表扫描，那么InnoDB无法定位具体行，就会退化为加表锁，以保证一致性。

9.触发器

①.概念
  行级触发器: 修改了多少行就会触发多少次
  语句触发器: 一条sql语句不管修改了多少行数据，只会触发一次 (注:mysql不支持)

  关键字old: 触发器能获取到修改之前的行数据
  关键字new: 触发器能获取到修改之后的行数据

②.语法 (在users表被update时记录操作内容)

create trigger trg_users_update
after update on tbl_users
for each row
begin
  insert into tbl_users_mod_log (
    act, id_old, name_old, age_old, id_new, name_new, age_new
  )
  values (
    'update',
    old.id, old.name, old.age,
    new.id, new.name, new.age
  );
end;