1. 数据库服务器操作命令
启动数据库:net start mysql80 (注释:windows命令)
停止数据库:net stop mysql80 (注释:windows命令)
重启数据库:systemctl restart mysql;(重启后全局变量失效)
连接数据库:mysql -h localhost -P 3306 -u root -p (注释:windows命令)
备份数据库:mysqldump[-h{ip}] -uroot -p1234 数据库名 > d:/数据库名.sql(windows命令)
退出数据库:exit (注释:mysql命令,下记略)
2. DDL数据库操作命令
查询所有数据库:show databases; //显示所有数据名
切换当前数据库:use {数据库名}; // 将数据库切换到指定数据库
查询当前数据库:select database(); // 显示当前数据库名
创建数据库:careate database [if not exists] {数据库名} [default charset 字符集]
删除数据库:drop database {数据库名}
3. DDL表操作
查询所有表:show tables; // 显示当前数据库所有表名
查询表结构:desc {表名}; // 显示表字段 属性 约束
查询建表语句:show create table {表名};
创建表:create table {表名} ( {字段名} {类型} [comment 注释], … ) [comment=表注释];
修改表_添加字段:alter table {表名} add {字段名} {类型} [comment 注释 约束];
修改表_修改类型:alter table {表名} modify {字段名} {类型} [comment 注释 约束];
修改表_修改字段:alter table {表名} change {原字段} {新字段} {新类型} [comment 约束];
修改表_删除字段:alter table {表名} drop {字段名};
修改表_修改表名:alter table {表名} rename to {新表名};
重新建表:truncate table {表名}; // 删除后重新建表(删数据比delete快,需要有改表权限)
删除表:drop table [if exists] {表名};
4. DML数据操作(增删改)
单条插入_指定字段:insert into {表名} (字段, 字段…) values (值, 值…);
单条插入_全部字段:insert into {表名} values (值, 值…);
批量插入_指定字段:insert into {表名} (字段, 字段…) values(字段, 字段),(字段, 字段)…;
批量插入_全部字段:insert into {表名} values(字段, 字段),(字段, 字段)…;
修改数据:update {表名} set {字段}={值},{字段}={值}… [where 条件];
删除数据:delete from {表名} [where 条件];
5. DQL数据操作(查询)
查询:select 字段… from {表名} [where 条件] [group by 字段…] [having 字段] order by {字段} asc, {字段} desc limit 下标值件数值;
去重查询:select distinct 字段… from {表名} [where 条件]…; // 据说去重比分组快
条件运算符: =; !=; ; >; =; <=; beturn and ; in ; like ; is null; no in; not like; is not null;
逻辑运算符:and ; or ; !
关联查询:inner join; left [outer] join; right [outer] join; full outer join;
联合查询:union; union all;
嵌套查询(子查询):
标量子查询:select * from {表1} where{字段} = (select {字段}from {表2});
行子查询:select * from {表1} where{字段, 字段} = (select {字段, 字段}from {表2});
列子查询:select *from {表1} where{字段} [in,any,some,all] (select {字段}from {表2});
表子查询:select * from {表1} where{字段, 字段} in (select {字段, 字段}from {表2});
判空函数: if (表达式, 字段1, 字段2) // 如果表达式成立则字段1,否则字段2
判空函数: ifnull (字段1, 字段2) // 如果字段1是null则字段2,否则字段1
case when {表达式} then {字段1} else {字段2} end;
case {字段1} when {值1} then {} when {值2} then {} else {} end;
6. DCL数据访问权限(用户权限)
查询用户:select * from user;
创建用户:create user ‘{用户名}’@'{主机名}’ identified {密码}; // 主机 ‘localhost’本地访问权限,’%’全部机器访问权限
修改密码:alter user ‘{用户名}’@'{主机名}’ identified with mysql_native_password by {新密码};
删除用户:drop user ‘{用户名}’@'{主机名}’;
查询用户权限:show grants for ‘{用户名}’@'{主机名}’;
授权用户权限_数据库下所有表:grant {权限}, {权限}… on {数据库.表名} to ‘{用户名}’@'{主机名}’;
授权用户权限_所有数据的表:grant{权限}, {权限}… on *.* to ‘{用户名}’@'{主机名}’;
撤销权限:revoke{权限}, {权限}… on {数据库.表名} from'{用户名}’@'{主机名}’;
7. 约束
建表约束_字段指定:create table {表名} (字段 类型 primary key, 字段 类型 not null, 字段 类型 unique, 字段 类型 check(条件), 字段 类型 defult);
建表约束_外键:create table {表名} (… constraint 外键名 foreign key (字段) 主表名(字段) );
追加外键约束:alter table {表名} add constraint {约束名} {约束} (字段) referenes 主表名(字段);
删除外键约束:alter table {表名} drop foreign key 外键约束名;
外键更新删除行为:no action 不允许删除更新,restrict 不允许删除更新,cascade 同时删除更新子表记录,set null 子表设置成 null
设置外键更新删除行为:alter table {表名} add constraint {约束名} {约束} (字段) referenes主表名(字段) on update cascade on delete set null; // 主表更新子表同事更新。主表删除子表设置null
8. 事务(原子性,一致性,隔离性,持久性)
查询事务提交方式:select @@autocommint; // 1自动提交 0手动提交
设置事务提交方式:set @@autocommint = 0; // 改为手动提交
开启事务:start transaction; // 或者begin
提交事务:commit; 回滚事务:rollback;
隔离级别:read uncommited(读未提交)有脏读 不可重复读 幻读问题, read commited(读已提交)有不可重复读 幻读问题, reteatable read(可重复读)有幻读问题, serializable(串行化)三个问题都没有,执行效率低
查看事务隔离级别:select @@transaction_isolation;
设置事务隔离级别:set [session | global] transaction level {隔离级别}; // session 当前窗口生效 global所有窗口生效
9. 索引
创建索引:create [unique fulltext] index {索引名} on 表名(字段..);
查看索引:show index from {表名};
删除索引:drop index {索引名} on {表名};
提示用指定索引查询:select * from 表 use index(索引名) where 条件;
提示不用指定索引查询:select * from 表 ignore index(索引名) where 条件;
强制使用索引查询:select * from 表 force index(索引名) where 条件;
10. 视图
创建视图:create view 视图名 as select * from 表;
查询视图:select * from 视图名; // 与查询表一致
查询视图结构:show create view 视图名; // 与查询表结构一致
替换视图:create or replaceview 视图名 as select * from 表;
修改视图:alter view 视图名 as select * from 表;
删除视图:drop view 视图名;
创建视图检查选项:create view 视图名 as select * from 表 wherewith cascaded check option;
create view 视图名 as select * from 表 where 条件with local check option;
11. 存储过程
查看系统变量:show [global session] variables;
查看系统变量值:select @@[global session] {系统变量名};
设置系统变量:set @@[global session].{系统变量名} = 值; set [global session] {系统变量名} = 值;
设置自定义变量:set @变量名 = 值; set @变量名 := 值; select @变量名 := 值; // 无需声明
查询自定义变量:select @变量名, @变量名;
声明局部变量:declare 变量名 类型; // 可以default 指定默认值
设置局部变量:set 变量名 = 值; set 变量名 := 值; select 字段 into 变量 from 表;
创建存储过程:create procedure {存储过程名} (参数列表) begin … end;
创建带参存储过程:create procedure{存储过程名} (in 参数类型, out 返回值 类型) begin … end;
条件判断: if 条件 then … elseif 条件 then … else … end if;
条件判断:case 值 when 值 then … else … end case; case when 条件 then… else … end case;
循环:while 条件 do … end while; repeat … until 条件;
循环:[循环名:] loop … end loop [循环名]; // leave 循环名(退出) iterate 循环名(跳过)
调用存储过程:call {存储过程名}(参数列表); call{存储过程名}(参数, @自定义变量);
查询存储过程:select * from infomation_schema.routines where routine_schema = {数据库};
查询存储过程创建语句:show create procedure {存储过程名};
删除存储过程:drop procedure {存储过程名};
声明游标: declare 游标名 cursor for 查询语句;
打开游标:open 游标名;
获取当前游标记录:fetch 游标名 into 变量 [, 变量…]; // 多条记录需要循环调用
关闭游标:close 游标名;
12. 存储函数
创建存储函数:create function 存储函数名(参数名 参数类型) // 无需指定参数默认in
returns 返回值类型 begin … return …; end;
调用存储函数:select 存储函数名;
13. 触发器
创建触发器:create trigger 触发器名;
before insert / after insert / before update / after update / before delete / after delete //选一个
on 表名 for each row // 行级触发器
begin … end;
查看触发器:show triggers;
删除触发器:drop trigger [schema名.]触发器名;
14. 锁
加全局锁:flush tables with read lock;
释放全局锁:unlocak tables;
加表共享读锁(读锁):lock tables 表名[,表名…] read; // 任何人只能读不能写
加表独占写锁(写锁):lock tables 表名[,表名…] write; // 自己读写,其他人不能读写
释放表锁:unlock tables;
加查询共享锁:select … lock in share mode;
加查询排他锁:select … for update;