MySQL命令格式

大二学mysql的时候记录了一部分命令，想着还没发过文章，那就把这个作为第一篇吧！希望大家都能有收获！

有点潦草，过后有时间修改吧！ ——220825

进入cmd

windows+R

↓

输入cmd

显示帮助

\h

\?

显示警告

\W

show warnings;

隐藏警告

\w

进入数据库的步骤

net start mysql

↓

mysql -u root -p 或者 mysql -h localhost -u root -p

退出数据库

\q

exit

quit

刷新权限

flush privileges;

创建数据库

create database 库名;

查看创建的数据库

show create database 库名;

show databases;

修改编码

alter database 库名 default character set 编码 collate 编码_bin;

删除数据库

drop database 库名;

drop database if exists 库名;

显示当前使用的数据库

select database();

创建表

use 所属数据库;

↓

create table 表名

(

字段名1 数据类型 [约束],

字段名2 数据类型 [约束],

……,

字段名n 数据类型 [约束]

);

查看表(分号改为\G格式更好点)

show create table 表名; 

show tables;

describe 表名;

desc 表名;

此处显示的参数值：

FIELD：该表字段名

TYPE：对应字段的数据类型

NULL：表示该列是否可以存储NULL值

KEY：表示该列是否已经编制索引

DEFAULT：表示该列是否有默认值

EXTRA：表示获取到的与给定列相关的附加信息

修改表名称

alter table 旧表名 rename [to] 新表名;

修改表中参数的字段名

alter table 表名 change 旧字段名 新字段名 原数据类型；  （直接改成新数据类型也可以）

修改表中参数的数据类型（同时可以添加约束）

alter table 表名 modify 字段名 新数据类型 [约束类型];

表的约束

主键约束 primary key

非空约束 not null

唯一性约束 unique

默认约束 default 数值（用于设置字段的默认值）

值自动增加 auto_increment

外键约束 foreign key（涉及多表操作，两个及以上）

创建表时加外键格式：(最后一句)

constraint 外键名 foreign key(外键字段名) references 主表名(主表的主键字段名)

创建表后添加外键

alter table add constraint 外键名 foreign key(从表的外键字段名) references 主表名(主表的主键字段名)

删除外键约束

alter table 从表名 drop foreign key 外键名;

添加新参数

alter table 表名 add 新字段名 新数据类型;

删除字段名

alter table 表名 drop 字段名;

修改字段位置

alter table表名 modify 字段名 数据类型 [first/after 字段名];

删除表

drop table 表名;

插入记录

insert into 表名[(字段名列表)]

values (default/null/值1)[, (default/null/值2),…(default/null/值n)];

修改记录

update表名 set 字段名=表达式/值 [where 条件表达式];

删除部分或所有数据

delete [from] 表名 [where 条件表达式];

delete语句和drop语句的区别

delete语句只删除表中数据，drop语句删除表结构

清空所有数据

truncate [table] 表名;

delete语句和truncate语句的区别

(1)delete语句是DML语句；truncate语句是DDL语句

(2)delete语句可以通过指定后面的where子句来删除满足条件的部分记录；truncate语句只能删除表中所有记录，不能加where子句

(3)delete语句清空表后，auto_increment不会恢复为初始值；truncate语句清空表后，auto_increment会被重置为初始值

(4)因为用delete语句删除的内容仍会在日志中保留，而truncate语句删除的则不会，所以truncate语句的执行效率比delete语句高

查询表内容（全内容）

use 所属数据库;

↓

select [all/distinct] */字段名 [ [as] 别名]                         （*表示全部）

from 表名

[where 条件表达式]

[group by 字段名列表 [having 逻辑表达式] ]                    （分组）

[order by 字段名 [asc|desc]]

（asc升序（一般为默认，但一定要加上），desc降序）（可多个字段）

[limit [offset,] n];                                              （限制查询结果的数量）

group by子句和order by子句的区别

group by子句用于对查询结果按指定字段的值进行分组，字段值相同的放在一起，order by子句是对查询结果集中的数据行按照指定字段的值重新排列

where子句的条件表达式中的列表运算符in的使用

select [all/distinct] */字段名 [ [as] 别名]                         （*表示全部）

from 表名 where 字段名 (not) in (‘值1’,’值2’,…,’值n’);

统计查询

select 集合函数 [[as] 别名] from 表名 [where子句];

集合函数

count()、sum()、avg()、max()、min()

where子句和having子句的区别

(1)where子句设置的查询筛选条件在group by子句之前发生作用，并且条件中不可以使用集合函数

(2)having子句设置的查询筛选条件在group by子句之后发生作用，并且条件中可以使用集合函数

交叉连接查询

select 字段名列表 from 表名1 cross join 表名2;

内连接查询（两表连接）

select 字段名列表 from 表名1 [inner] join 表名2 on 表名1.字段名 比较运算符 表名2.字段名;

或者

select 字段名列表 from 表名1,表名2 where 表名1.字段名 比较运算符 表名2.字段名;

内连接查询（三表连接）

select 字段名列表 from 表名1 join 表名2 on 表名1.字段名 比较运算符 表名2.字段名 join 表名3 on 表名3.字段名 比较运算符 表名1或表名2.字段名;

或者

select 字段名列表 from 表名1,表名2, 表名3 where 表名1.字段名 比较运算符 表名2.字段名 and 表名3.字段名 比较运算符 表名1或表名2.字段名;

外连接查询（仅适用于两个表）

select 字段名列表 from 表名1 [left/right/full] join 表名2 on 表名1.字段名 比较运算符 表名2.字段名;

左外连接   左表为主表，右表填充null

右外连接   右表为主表，左表填充null

全外连接   互相填充

自连接查询（注意是一个表出现两次，只是别名不同）

select 字段名列表 from 表名 as 别名1 join 表名 as 别名2 on 别名1.字段名=别名2. 字段名 [where 子句];

子查询---可多层嵌套，不止一层

比较子查询（一般来说，用到all的情况时前面为不等号）

select 字段名 [[as] 别名] from 表名 where 字段名=[any/all](select 字段名 [[as] 别名] from 表名 [where 条件表达式]);

in子查询

select 字段名 [[as] 别名] from 表名 where 字段名 (not) in (select 字段名 [[as] 别名] from 表名 [where 条件表达式]);

exists子查询

select 字段名 [[as] 别名] from 表名 where (not) exists (select 字段名 [[as] 别名] from 表名 [where 条件表达式]);

insert语句中使用子查询（两组字段需一一对应）

insert 表名1 [(字段名列表1)] select 字段名列表2 from 表名2 [where 条件表达式];

update语句中使用子查询

update 表名1 set 字段名=值 where 字段名 (not) in (select 字段名 from 表名2 [where 条件表达式]);

delete语句中使用子查询

delete from 表名1 where 字段名=(select 字段名 from 表名2 [where 条件表达式]);

合并结果集（两组字段需一一对应，并且只能在最后一个select语句有order by子句）

select 字段名列表1 from 表名1 union select字段名列表2 from 表名2;

定义局部变量（没有default为null）

declare 变量名 数据类型 [default 值];

赋值局部变量

set变量名=值;

用户自定义变量

set @变量名[=值];

调用用户自定义变量

select @变量名[1, @变量名2,…, @变量名n];

流程控制语句

if语句

if 逻辑判断语句 then mysql 语句

[else if 逻辑判断语句 then mysql 语句]

[else mysql 语句]

end if;

case语句

case 表达式 when 表达式可能匹配值 then mysql 语句

[when 表达式可能匹配值 then mysql 语句]

[else mysql 语句]

end case;

loop语句

[循环标记名称:] loop mysql 语句 end loop [循环标记名称];

leave语句（执行该语句时会无条件跳出到该标记名称所标识的语句块，即跳出循环体）

leave 循环标记名称:

iterate语句（结束本次循环，转到循环开始语句，进行下一次循环）

iterate循环标记名称:

repeat语句（先执行一次循环语句块再进行条件表达式判断，如果条件表达式结果为true循环结束，否则再重复执行一次循环语句块）

[循环标记名称:] repeat mysql 语句 until 逻辑判断语句 end repeat [循环标记名称];

while语句（首先判断条件表达式，结果为true则继续执行一次循环语句块，执行完再次判断条件表达式，如果条件表达式结果为false直接退出循环）

[循环标记名称:] while 逻辑判断语句 do mysql 语句 end while [循环标记名称];

光标使用步骤：声明→打开→使用

声明光标

declare 光标名 cursor for select 字段名[列表] from 表名:

打开光标

open光标名;

使用光标

fetch光标名 into 变量名[列表]:

关闭光标

close光标名;

创建和调用存储过程（创建时首先要修改结束符，创建完也要把结束符修改回来）

创建无参存储过程

use 所属数据库;

↓

delimiter //

create procedure 存储过程名() [存储特性]

begin mysql语句 end;//

delimiter ;

调用无参存储过程

call 存储过程名();

创建有参存储过程

delimiter //

create procedure 存储过程名([in/out/inout] 参数名 数据类型) [存储特性]

begin mysql语句 end;//

delimiter ;

调用带输入参数in的有参存储过程

set @变量名=’字段名’;

call 存储过程名(变量名);

或者

call 存储过程名(‘字段名’);

调用带输入参数in和输出参数out的有参存储过程

set @变量名=0;

call 存储过程名(‘字段名’,变量名);

显示数据库内存储过程和存储函数列表

select name from mysql.proc where db=’数据库名’;

或者

select routine_name from information_schema.routines where routine_schema=’数据库名’;

显示数据库内所有存储过程和存储函数的名称和详细信息

show procedure status where db=’数据库名’;

查看存储过程的定义语句等信息

show create procedure 数据库.存储过程名;

修改存储过程的特性

alter procedure 存储过程名 [language sql/(not) deterministic/{contains sql/no sql/reads sql data/modifies sql data}/sql security{definer/invoker}];

存储过程特性

contains sql：存储程序不包含读或写数据的语句（如果没指明则为默认）

no sql：存储程序不包含mysql语句

reads sql data：存储程序含读数据的语句

modifies sql data：存储程序含写数据的语句

deterministic：输入不变的情况下输出是否确定

sql security{definer/invoker}：指明谁有权限执行存储过程

definer：只有定义者能执行存储过程

invoker：调用者可以执行存储过程

删除存储过程

drop procedure [if exists] 存储过程名;

存储过程和存储函数的联系和区别

(1)联系：结构上相似，都是由sql语句和过程式语句组成的代码段，都可以被别的应用程序或sql语句所调用

(2)区别：存储过程需要输出参数处理结果和call语句进行调用，存储函数必须使用return语句返回结果

创建和调用存储函数（创建时首先要修改结束符，创建完也要把结束符修改回来）

创建无参存储函数

use 所属数据库;

↓

delimiter //

create function 存储函数名() returns 数据类型 [存储特性]

begin mysql语句 end;//

delimiter ;

调用无参存储函数

set @变量名=存储函数名();

select @变量名;

创建有参存储函数

delimiter //

create function 存储函数名(参数 数据类型) returns 数据类型 [存储特性]

begin mysql语句 end;//

delimiter ;

调用无参存储函数

set @变量名1=值;

set @变量名2=存储函数名(@变量名1);

select @变量名2;

查看存储函数的定义语句等信息

show create function 数据库.存储函数名;

删除存储函数

drop function [if exists] 存储函数名;

启动事务

start transaction;

提交事务

commit;

完成事务全过程

use 所属数据库;

↓

start transaction;

mysql 语句;

commit;

回滚事务

rollback;

设置隔离级别

set [session/global] transaction isolation level {read uncommitted/read committed/repeatable read/serializable};

隔离级别

read uncommitted：读未提交

read committed：读提交

repeatable read：可重复读

serializable：可串行化

创建表时创建索引

use 所属数据库;

↓

create table 表名

(

字段名1 数据类型 [约束],

字段名2 数据类型 [约束],

……,

字段名n 数据类型 [unique/fulltext/spatial] index/key [别名](字段名[(长度)]) [asc/desc]

)[engine=myisam];                                              （长度指检索出的量）

索引类型

index：普通索引，基本索引类型，允许在定义索引的字段插入重复值或空值，该索引可创建在任何数据类型中

unique：唯一索引，定义索引的字段的值必须唯一，但允许有空值，如果是在多个字段上建立的组合索引，则字段值的组合必须唯一，在创建主键和唯一约束的字段上会自动创建唯一索引，主键索引是特殊的唯一索引，不允许有空值

fulltext：全文索引，允许在定义索引的字段上插入重复值和空值，只能创建在char，varchar和text类型的字段上

spatial：空间索引，只能在空间数据类型（geometry，point，linestring，polygon）上创建，且创建索引的字段必须声明为not null

单列索引：在表中单个字段上创建的索引，可以是任何索引，只要保证该索引只对应表中的一个字段即可

多列索引：在表中多个字段上创建的索引，只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用

查看索引的字段信息

explain select * from 表名 [where 条件表达式];

查看索引的定义语句等信息

show create table 表名\G

在已经存在的表上创建索引

create [unique/fulltext/spatial] index 索引名 on 表名(字段名[(长度)]) [asc/desc];

或者

alter table 表名 add [unique/fulltext/spatial] index 索引名(字段名[(长度)]) [asc/desc];

删除索引

drop index 索引名 on 表名;

或者

alter table 表名 drop index 索引名;

定义视图

create [or replace] [algorithm={undefined/merge/temptable}] view 视图名 [(字段名列表)] as select 语句 [with [cascaded/local] check option];

相关参数

or place：当具有同名视图时，将用新创建的视图覆盖原始图

algorithm：为视图选择的算法

undefined：默认算法，倾向于merge

merge：将引用视图的语句和视图的定义合并起来，使得视图定义的某一部分取代语句的对应部分

temptable：视图的结果将被置于临时表中，然后使用它执行语句

with check option：对于可更新视图，给定该子句来防止插入或更新行，除非select语句中的where子句为真

cascaded：默认，对该视图相关的所有视图和基表进行检查

local：限制check option仅作用在定义的视图上

查看视图的字段信息

describe 视图名;

或者

desc 视图名;

查看视图的定义信息

show table status like ’视图名’;

查看视图的定义语句、字符编码以及视图中记录的行数

show create view 视图名;

修改视图          （对于create和replace，视图存在时修改，视图不存在时会创建视图）

alter/replace/create [algorithm={undefined/merge/temptable}] view 视图名 [(字段名列表)] as select 语句 [with [cascaded/local] check option];

删除视图

drop view [if exists] 视图名[列表];

向视图插入记录

insert into 视图名[(字段名列表)]

values (default/null/值1)[, (default/null/值2),…(default/null/值n)];

修改视图的记录

update视图名 set 字段名=表达式/值 [where 条件表达式];

删除视图的部分或所有数据

delete [from] 视图名 [where 条件表达式];

创建触发器

use 所属数据库;

↓

delimiter //

create trigger 触发器名 after/before insert/update/delete on 表名 for each row

begin sql语句; 触发程序 end;//

delimiter ;

删除触发器

drop trigger 触发器名;