show databases; --显示数据库
create database **;  -- 创建数据库

use name_of_databse; --使用数据库
select database(); --显示正在使用的数据库
drop database name_of_database; --删除数据库

SHOW TABLES; --显示数据表
CREATE TABLE 表名(字段名 数据类型);
DESC 表名; -- 显示表结构
SHOW CREATE TABLE 表名 --显示建表语句
ALTER TABLE 表名 ADD/MODIFY/CHANGE/DROP/RENAME TO --添加/修改类型/修改名称&类型/删除/修改名称
Drop table 表名;

INSERT INTO 表名(字段1，字段2)VALUES (值1，值2);
INSERT INTO 表名 VALUES (值1， 值2);
INSERT INTO 表名 (字段1，字段2) VALUES (值1，值2),(值1，值2);
INSERT INTO 表名 VALUES (值1，值2),(值1，值2);
                     
UPDATE name_of_table SET col1=value1, col2=value2 where **;
delete from name_of_table where **;

select col from name_of_table;
select col as alias from ;
select distinct col from name_of_table;

select * from tablename where col is null;
select * from tablename where col is not null;
&& and
between and
select * from name where col in(1,2,3);
select * from name from col like '__';

select count(col) from name_of_table;

select col form naem where group by 分组字段 having

select count(*) from emp group by gender;
select count(*) as addc from emp where age < 45 group by workaddress having addc>=3;

select cols from name_of_table order by col1 method, col2;

select * from emp order by age asc; 升
select * from emp order by age desc; 降
select * from emp order by age asc, ent desc;

select cols from name_of_table LIMIT start,number;

select 字段 from 表1， 表2 where --隐式
select * from emp e inner join dept d on e.id==d.id

select * from emp e left join dept d on e.id=d.id
right join

select * from table1 t1 left join table2 t2 on t1.id=t2.id;

select 字段 from 表a
union all
select 字段 from 表b
union去重
字段需要保持一致

select * fromm t1 where col1=(select col1 from t2);

--标量子查询
select * from emp where detp_id = (select id from dept where name="ss")

--列子查询
--in not in any some all
select * from emp where detp_id in (select id from ....)

--行子查询
select * from emp where (col1, col2) = (select col1, col2 from emp2 where ..)

--表子查询
select * from emp where (col1, col2) in (select col1, col2 from emp2 where ..)

select @@autocommit; --查询
set @@autocommit=0 --设置 0关闭事务

commit;

rollback  
start transaction;--开启事务
commit;
rollabck

create table ss{
}engine = INNODB;

create index index_name on table_name (col1, col2);
show index from table_name;
drop index index_name from table_name;

show global status like 'com_______' --查看增删改查次数

-- 慢查询日志 默认不开启 10秒

-- profile
show profiles; ---查看sql语句的耗时情况
show profile for query id; --查看sql在各个阶段的耗时

--explain执行计划
explain select ....
--type possible_keys kes key_len

create index idx_ on table_(col(n));

select * from tb, (select id from tb_b order by id limit * *) a where a.id = b.id

create or replace view view_n as select 字段 from table_n where **
show create view stu_v_1
select * from
alter view 视图名称 as select 字段 --修改视图
drop view 视图名称 --删除视图

create procedure 存储过程名称
begin
--SQL语句
end;

CAll 名称 --调用

drop procedure

delimitor

show session| global variables；
show variables like ''
select @@session|global.系统变量名
set

-- 赋值
set @var_name = 
set @var_name:=
select @var_name := exp
select count(*) into @var_name from table_name --

select @var_name --查看

declare 变量名 变量类型

set 变量名=值
同上

if exp then
...
elseif exp2 then
...
else
...
end if；

create procedure name(in/out/inout var_name 类型)

case case_value
when .. then..
end case

whlie 条件 do
SQL
end while

repeat 
	SQL
	util
end repeat

[begin label]loop
	SQL
end loop[end lable]

leave lable
iterate label

declare 游标名称 cursor for 查询语句
open 游标
fetch ** into **；
close 游标

declare handler_action hander for condi

action:
- continue
- exit

con
- 状态码

create function 名称（参数）
returns type c
begin
	SQL
	return
end;

create trigger name
before/after insert/update/de;ete
on table for each row
begin
	S
end

-- 
show triggers
drop trigger trigger_name

flush tables with read lock;
unlock tables;

--不加锁的一致性数据备份
single-transaction

表共享读
表共享写
-- 表共享读锁
locak tables 表名 read/wirte
unlock tables

读锁不会阻塞读，但会阻塞其他客户端的写，自己也不能写

写锁当前能读能写，其他客户端不能读不能写