1
2
3
show databases;
show create database db_name;
show create database db01;

1
2
3
create database if not exists db03
  character set utf8
  collate utf8mb3_bin;

1
drop database if exists db03;

1
2
3
show tables;
show create table user;
desc table_name;

1
2
3
4
5
6
create table table_name (
    field1 datatype,
    field2 datatype
) character set utf8mb4
  collate utf8mb4_general_ci
  engine = InnoDB;

1
id int primary key auto_increment

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 添加列
alter table table_name add column_name datatype;

-- 修改列类型
alter table table_name modify column_name datatype;

-- 修改列名
alter table employee change column old_name new_name datatype;

-- 删除列
alter table table_name drop column column_name;

-- 修改表名
rename table old_table_name to new_table_name;

-- 修改表字符集
alter table table_name character set utf8mb4;

1
drop table tbl_name;

1
2
create table user_bak like user;
insert into user_bak select * from user;

1
2
3
4
5
6
7
create table emp_bak_2025_2_15_13_07 like emp;
insert into emp_bak_2025_2_15_13_07 select distinct * from emp;

delete from emp;
insert into emp select * from emp_bak_2025_2_15_13_07;

drop table emp_bak_2025_2_15_13_07;

1
2
insert into table_name(column1, column2)
values (val1, val2);

1
2
insert into table_name
values (val1, val2, val3);

1
2
3
4
insert into table_name(col1, col2)
values
  (val1, val2),
  (val3, val4);

1
2
3
4
update tab_name
set column1 = val1,
    column2 = val2
where id = 1;

1
2
delete from tbl_name
where id = 1;

1
2
3
4
5
select [distinct] column1, column2
from tbl_name
where condition
order by column1 desc
limit 0, 10;

1
2
3
select *
from emp
order by col1 desc, col2 asc;

1
2
3
4
select deptno, count(*)
from emp
group by deptno
having count(*) > 3;

1
2
3
select *
from emp
limit 20, 10;

1
2
3
select e.ename, m.ename as manager_name
from emp e
join emp m on e.mgr = m.empno;

1
2
3
4
5
6
7
select *
from emp
where emp_id in (
  select distinct id
  from emp
  where sal > 2000
);

1
2
3
4
5
6
7
8
9
-- 查询每个商品类别中价格最高的商品
select goods.goods_name, goods.shop_price
from ecs_goods goods
join (
  select cat_id, max(shop_price) as max_price
  from ecs_goods
  group by cat_id
) temp on goods.cat_id = temp.cat_id
      and goods.shop_price = temp.max_price;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 比 30 号部门所有员工工资都高
select *
from emp t2
where t2.sal > all (
  select t.sal
  from emp t
  where t.deptno = 30
);

-- 比 30 号部门任意一个员工工资高
select *
from emp t2
where t2.sal > any (
  select t.sal
  from emp t
  where t.deptno = 30
);

1
2
3
4
5
6
7
8
-- 查询各科成绩完全和“宋江”一样的学生
select *
from student s
where (s.chinese, s.math, s.english) = (
  select chinese, math, english
  from student
  where name = '宋江'
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 多列子查询
select *
from emp
where (deptno, sal) in (
  select deptno, max(sal)
  from emp
  group by deptno
);

-- 子查询作为临时表
select emp.*
from emp
join (
  select deptno, max(sal) as max_sal
  from emp
  group by deptno
) temp on temp.deptno = emp.deptno
      and temp.max_sal = emp.sal;

1
2
3
4
5
6
7
8
9
select if(score >= 60, 'pass', 'fail') as result
from student;

select case
         when sal >= 10000 then 'high'
         when sal >= 5000 then 'middle'
         else 'low'
       end as sal_level
from emp;

1
2
3
4
5
6
7
8
9
10
create table student (
    id bigint primary key,
    name varchar(64) not null
);

create table score (
    student_id bigint,
    course_id bigint,
    primary key (student_id, course_id)
);

1
foreign key (stu_id) references tbl_father(id)

1
2
gender varchar(8) check (gender in ('m', 'f')),
sal double check (sal > 1000 and sal < 2000)

1
2
3
4
5
6
-- 查询太慢
select * from emp where empno = 1234567; -- 2s

-- 使用索引优化
create index empno_index on emp(empno);
select * from emp where empno = 1234567; -- 51ms，提升约 400 倍

1
2
select * from emp where ename = 'HQfdaS'; -- 2s -> 55ms
create index enameno_index on emp(ename);

1
drop index empno_index on emp;

1
2
3
4
create index index_name on table_name(col_name);
create unique index index_name on table_name(col_name);
alter table tbl_name add index index_name (col_name);
alter table tbl_name add primary key (col_name);

1
2
3
drop index index_name on tbl_name;
alter table tbl_name drop index index_name;
alter table tbl_name drop primary key;

1
2
3
show indexes from tbl_name;
show keys from tbl_name;
desc tbl_name;

1
select * from emp where empno = 1;

1
2
3
4
5
6
7
8
9
10
11
12
start transaction;

update account set balance = balance - 100 where id = 1;
savepoint after_debit;

update account set balance = balance + 100 where id = 2;

-- 回退到保存点
rollback to after_debit;

-- 提交事务
commit;

1
select @@transaction_isolation;

1
2
3
4
set transaction isolation level read uncommitted;
set transaction isolation level read committed;
set transaction isolation level repeatable read;
set transaction isolation level serializable;

1
2
3
4
begin;
select * from orders where user_id = 100 for update;
update orders set status = 'paid' where user_id = 100;
commit;

1
在mysql命令行内执行: source 备份的sql文件名.sql

1
2
3
create table t4 (
    id tinyint unsigned
);

1
2
3
4
5
6
7
create table t14 (
    birthday date,
    job_time datetime,
    login_time timestamp not null
        default current_timestamp
        on update current_timestamp
);

1
alter table tbl_name engine = engine_name;

1
show engines;

1
2
create view view_name as
select ...;

1
2
3
4
5
-- 视图在使用上类似一张表
show tables;

-- 查看创建视图时的 SQL
show create view emp_view;

1
drop view view_name;

1
create user '用户名'@'登录ip' identified by '密码';

1
drop user '用户名'@'允许登录ip';

1
2
3
4
5
6
-- 修改自己的密码
set password = password('密码');

-- 修改他人的密码
set password for '用户名'@'登录ip' = password('密码');
alter user '用户名'@'登录ip' identified by '新密码';

1
create user 'xxx'@'192.168.1.%' identified by '密码';

1
grant 权限列表 on 库.表名 to '用户名'@'登录ip' [identified by '密码'];

1
2
3
grant select on db_name.table_name to 'app'@'%';
grant select, delete, create on db_name.* to 'app'@'%';
grant all privileges on *.* to 'admin'@'%';

1
revoke 权限列表 on 库.对象 from '用户名'@'登录ip';

1
flush privileges;

1
2
3
4
5
6
7
set @a = 10;
set @b = 10.5;
set @b = @a;

select count(*) from emp into @a;
select max(sal), min(sal) from emp into @a, @b;
select @a, @b;

1
declare var1, var2 int default 0;

1
2
3
4
5
6
7
8
9
delimiter $$

create function avg_math_score()
returns float
begin
    return (select avg(math) from student);
end $$

delimiter ;

1
2
3
4
select avg_math_score();
show function status like 'avg_math_score';
show create function avg_math_score;
drop function avg_math_score;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create function condition_demo(i int)
returns varchar(20)
begin
    declare result varchar(20);
    if i = 1 then
        set result = 'result is 1';
    elseif i = 2 then
        set result = 'result is 2';
    elseif i = 3 then
        set result = 'result is 3';
    else
        set result = 'invalid!';
    end if;
    return result;
end;

1
2
3
4
5
6
7
8
9
10
11
create function sum_all(n int unsigned)
returns int
begin
    declare sum int default 0;
    declare i int unsigned default 1;
    while i <= n do
        set sum = sum + i;
        set i = i + 1;
    end while;
    return sum;
end;

1
2
3
4
5
6
7
8
9
10
11
create function sum_all2(n int unsigned)
returns int
begin
    declare sum int default 0;
    declare i int default 1;
    repeat
        set sum = i + sum;
        set i = i + 1;
    until i > n end repeat;
    return sum;
end;

1
2
3
4
5
6
7
8
9
10
11
12
13
create function sum_all3(n int unsigned)
returns int
begin
    declare sum int default 0;
    declare i int default 1;
    loop
        if i > n then
            return sum;
        end if;
        set sum = i + sum;
        set i = i + 1;
    end loop;
end;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create function sum_all4(n int unsigned)
returns int
begin
    declare sum int default 0;
    declare i int default 1;
    flag:
    loop
        if i > n then
            leave flag;
        end if;
        set sum = i + sum;
        set i = i + 1;
    end loop;
    return sum;
end;

1
2
3
4
5
6
7
8
9
10
create procedure tbl_emp_operation(
    eno mediumint unsigned,
    ena varchar(20)
)
begin
    select * from emp;
    insert into emp(empno, ename, hiredate, sal)
    values (eno, ena, current_date(), 0);
    select * from emp;
end;

1
2
3
4
call tbl_emp_operation(10086, 'motherfather');
show procedure status like 'tbl_emp_operation';
show create procedure tbl_emp_operation;
drop procedure tbl_emp_operation;

1
2
3
4
5
6
7
8
create trigger trigger_name
{before | after}
{insert | delete | update}
on tbl_name
for each row
begin
    -- 触发器内容
end;

1
2
3
4
5
6
7
8
9
10
11
12
13
create trigger trigger_for_stu
before insert
on student
for each row
begin
    if new.english < 60 then
        set new.english = 60;
    elseif new.english < 80 then
        set new.english = 80;
    else
        set new.english = 99;
    end if;
end;

1
2
3
show triggers;
show create trigger trigger_for_stu;
drop trigger trigger_for_stu;

1
2
3
4
5
6
7
8
9
10
create event event_name on schedule
{
  at 某个确定时间 |
  every 期望的时间间隔 [starts 开始日期和时间] [ends 结束日期和时间]
}
do
begin
    insert into student(id, name, chinese, english, math)
    values (12, 'caiqingsong', 100, 0, 100);
end;

1
2
3
4
5
6
7
create event insert_student_event on schedule
at '2025-02-16 15:20:00'
do
begin
    insert into student(id, name, chinese, english, math)
    values (12, 'caiqingsong', 100, 0, 100);
end;

1
2
3
show events;
show create event event_name;
drop event event_name;

1
set global event_scheduler = on;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create procedure cursor_demo()
begin
    declare temp_empno mediumint unsigned;
    declare temp_ename varchar(30);
    declare record_len int;
    declare i int default 1;

    declare emp_record_cursor cursor for
        select empno, ename from emp;

    open emp_record_cursor;
    set record_len = (select count(*) from emp);

    while i <= record_len do
        fetch emp_record_cursor into temp_empno, temp_ename;
        select temp_ename, temp_empno;
        set i = i + 1;
    end while;

    close emp_record_cursor;
end;

1
2
3
4
declare emp_record_cursor cursor for select empno, ename from emp;
open emp_record_cursor;
fetch emp_record_cursor into temp_empno, temp_ename;
close emp_record_cursor;

1
2
3
4
5
# /etc/my.cnf
#开启慢查询
slow_query_log=1
#设置慢查日志时间为2s,SQL执行时间超过2s就会记录这个慢查询
long_query_time=2

1
desc select * from emp where empno = 100002;

1
2
3
4
5
6
7
# id是主键, name是普通索引
# 覆盖索引,因为主键是聚集索引
select * from tbl_user where id = 1
# 覆盖索引 (name是二级索引, 数据有name(索引)和主键id)
select id,name from tbl_user where name = 'alice'
#非覆盖索引, 要在聚集索引中再次查找-->回表操作
select id,name gender from tbl_user where name = 'alice'

1
2
3
4
# 耗时2s
select * from emp limit 7000000,10;
# 耗时5ms
select * from emp limit 0,10;

1
2
3
4
select e.*
from emp e,
     (select empno from emp order by empno limit 7000000,10) a
where e.empno = a.empno;