Mysql常用命令



Mysql常用命令


Mysql 各个版本 官方文档

https://dev.mysql.com/doc/refman/


连接本地mysql数据库命令:
> mysql -u root -p
Enter password:******

启动数据库服务
>net start 数据库名;
eg:>net start mysql57;

关闭数据库服务
>net stop 数据库名;
eg：>net stop mysql57;

连接远程数据库
mysql -h [服务器地址] -P [端口号] -u [用户名] -p [用户密码];
eg:>mysql -h 192.168.5.116 -P 3306 -u root -p123456

重启数据库
service mysql restart；

数据库操作

查看数据库服务器中所有的数据库：
>show databases;

查看某个数据库的定义信息：
>show create database 数据库名称;

使用某个数据库:
>use 数据库名;

创建数据库
create database [if not exists] 库名;

删除数据库
drop databases [if exists] 库名;

建数据库通用的写法
drop database if exists 旧库名;
create database 新库名;

表操作 DDL
Data Define Language数据定义语言，主要用来对数据库、表进行一些管理操作。

查看数据库中所有表：
>show tables;

查看某表的定义信息：
>show create table 表名;

查看表结构:
>desc 表名;

建表
create table 表名(
    字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];
约束说明：
not null：标识该字段不能为空
default value：为该字段设置默认值，默认值为value

>drop table IF EXISTS test;
>create table test( a int not null comment '字段a', b int not null default 0 comment '字段b' );

primary key：标识该字段为该表的主键，可以唯一的标识记录，插入重复的会报错(1.在列后2.在所有列定义之后定义),支持多字段作为主键，多个之间用逗号隔开，语法：primary key(字段1,字段2,字段n):

>drop table IF EXISTS test;
>create table test( a int not null comment '字段a', b int not null comment '字段b', primary key(a,b) };

//设置a为主键
>alter table test modify a int not null primary key;
//查询test表的主键
>show index from test;

foreign key：为表中的字段设置外键
语法：foreign key(当前表的列名) references 引用的外键表(外键表中字段名称)

>drop table if exists test1;
>drop table if exists test2;
>create table test1( a int not null comment '字段a' primary key );
>create table test2( b int not null comment '字段b',ts5_a int not null,
     foreign key(ts5_a) references test1(a) );

两张表中需要建立外键关系的字段类型需要一致,要设置外键的字段不能为主键,被引用的字段需要为主键,被插入的值在外键表必须存在.

unique key(uq)：标识该字段的值是唯一的约束，支持一个到多个字段，插入重复的值会报违反唯一约束，会插入失败。（1.跟在字段后，2.所有列定义之后定义）
支持多字段，多个之间用逗号隔开，语法：unique key(字段1,字段2,字段n)

>drop table if exists test;
>create table test( a int not null comment '字段a', b int not null comment '字段b', unique key(a,b));

auto_increment：标识该字段的值自动增长（整数类型，而且为主键）

>drop table if exists test;
>create table test11(a int not null auto_increment primary key comment '字段a',
         b int not null comment '字段b')；

字段a为自动增长，默认值从1开始，每次+1，关于自动增长字段的初始值、步长可以在mysql中进行设置，比如设置初始值为1万，每次增长10,自增长列当前值存储在内存中，数据库每次重启之后，会查询当前表中自增列的最大值作为当前值，如果表数据被清空之后，数据库重启了，自增列的值将从初始值开始.

删除表
drop table [if exists] 表名;

修改表名
alter table 表名 rename [to] 新表名;

表设置备注
alter table 表名 comment '备注信息';

复制表(只复制表结构)
create table 表名 like 被复制的表名;

>create table test1 like test2;
>select * from test1;
>show create table test1;

复制表结构+数据
create table 表名 [as] select 字段,... from 被复制的表 [where 条件];

>create table test1 as select * from tesst2;
>select * from test1;

表中列的管理

添加列
alter table 表名 add column 列名 类型 [列约束];

alter table test add column b int not null default 0 comment '字段b';

修改列
alter table 表名 modify column 列名 新类型 [约束];
或者
alter table 表名 change column 列名 新列名 新类型 [约束];
modify不能修改列名，change可以修改列名

删除列
alter table 表名 drop column 列名;

DML(Data Manipulation Language)数据操作语言，以INSERT、UPDATE、DELETE

三种指令为核心，分别代表插入、更新与删除，是必须要掌握的指令，DML和SQL中的 select 熟称 CRUD（增删改查）。

插入操作
insert into 表名[(字段,字段,...)] values (值,值,...);
如果是字符型或日期类型，值需要用单引号引起来；如果是数值类型，不需要用单引号

insert into 表名 set 字段 = 值,字段 = 值;

批量插入2种方式:
insert into 表名 [(字段,字段,...)] values (值,值,,...),(值,值,...),(值,值,...);
insert into 表 [(字段,字段,...)] 数据来源select语句;
select返回的结果和插入数据的字段数量、顺序、类型需要一致。

单表更新
update 表名 [[as] 别名] set [别名.]字段 = 值,[别名.]字段 = 值 [where条件];

多表更新
update 表1 [[as] 别名1],表名2 [[as] 别名2] set [别名.]字段 = 值,[别名.]字段 = 值 [where条件];

update test1,test2 set test1.a=5,test1.b=6,test2.c1=2 where test1.a=test2.c1;

单表删除
delete [别名] from 表名 [[as] 别名] [where条件];

>delete from test1;//删除test1表所有记录
 >delete test1 from test1;//删除test1表所有记录
 >delete t1 from test1 t1;//有别名的方式，删除test1表所有记录
>delete t1 from test1 t1 where t1.a>100; 有别名的方式删除满足条件的记录

多表删除
delete [别名1],[别名2] from 表1 [[as] 别名1],表2 [[as] 别名2] [where条件];

>delete t1 from test1 t1,test2 t2 where t1.a=t2.c2;//把符合条件的test1表的数据删除
>delete t1,t2 from test1 t1,test2 t2 where t1.a=t2.c2;//把符合条件的test1,test2表数据删除

使用truncate删除
truncate 表名;

drop，truncate，delete区别
drop (删除表)：删除内容和定义，释放空间，简单来说就是把整个表去掉drop语句将删除表的结构被依赖的约       束（constrain），触发器（trigger）索引（index），依赖于该表的存储过程/函数将被保留，但其状态会    变    为：invalid。如果要删除表定义及其数据，请使用 drop table 语句。
truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)，与drop不同的是，只是清    空表数据而已。truncate不能删除具体行数据，要删就要把整个表清空了。
delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，    并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作。

truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）,
truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。

对于由foreign key约束引用的表，不能使用truncate table ，而应使用 不带 where子句的delete语句。由于truncate table 记录在日志中，所以它不能激活触发器。

如果要删除表定义及其数据，请使用 drop table 语句.
删除速度，一般来说: drop> truncate > delete

DQL(Data QueryLanguage)：数据查询语言

基本语法
select 查询的列 from 表名;

查询常量
select 常量值1，常量值2，常量值3；

查询表达式
select 表达式;

查询函数
select 函数;

查询指定的字段
select 字段1,字段2,... from 表名；

查询所有列
select * from 表名;
*表示返回表中所有字段;

列别名
select 列 [as] 别名 from 表名;

>select a 列1,b 列2 from test1;
>select a as 列1,b as 列2 from test1;

表别名
select 别名.字段,别名.* from 表名 [as] 别名;

条件查询语法

条件查询运算符详解（=、<、>、>=、<=、<>、!=）
逻辑查询运算符详解（and、or）
like模糊查询介绍between and查询
in、not in查询
NULL值存在的坑
is null/is not null（NULL值专用查询）
 <=>（安全等于）运算符

条件查询
select 列名 from 表名 where 列 运算符 值;
数值按照大小比较。字符 按照ASCII码对应的值进行比较，比较时按照 字符 对应的位置一个字符一个字符的比较。

逻辑查询运算符

使用多个条件进行查询的时候，需要使用逻辑查询运算符。
and 多条件都成立
or 多个条件中满足一个

select 列名 from 表名 where 列 like pattern;

pattern中可以包含通配符，有以下通配符：
%：表示匹配任意一个或多个字符
_：表示匹配任意一个字符。

select 列名 from 表名 where 列名 between 值1 and 值2;
这些值可以是数值、文本或者日期，属于一个闭区间查询。

select 列名 from 表名 where 字段 in (值1,值2,值3,值4,...);
in 后面括号中可以包含多个值，对应记录的字段满足in中任意一个都会被返回,in列表的值类型必须一致或兼容,in列表中不支持通配符。

select 列名 from 表名 where 字段 not in (值1,值2,值3,...);

排序查询（order by）

select 字段名 from 表名 [where 条件] order by 字段1 [asc|desc],字段2 [asc|desc],...;
asc|desc表示排序的规则，asc：升序，desc：降序，默认为asc；
支持多个字段进行排序，多字段排序之间用逗号隔开。
按照字段1排序，遇到相同的再按照字段2排序。

limit用来限制select查询返回的行数，常用于分页等操作。
select 列 from 表 limit [offset,] count;
offset：表示偏移量，通俗点讲就是跳过多少行，offset可以省略，默认为0，表示跳过0行；范围：[0,+∞)。
count：跳过offset行 之后 开始取数据，取count行记录；范围：[0,+∞)。
limit中offset和count的值不能用表达式。

用limit实现分页，语法如下:
select 列 from 表 limit (page-1)*pageSize,pageSize;

避免采坑
limit 中不能使用表达式，eg ....... limit 1,1+1;
limit 后面的2个数字不能为负数，eg: ... limit -1;

排序分页存在的坑
排序中存在相同的值时，需要再指定一个排序规则，通过这种排序规则不存在二义性，分页排序时，排序不要有二义性，二义性情况下可能会导致分页结果乱序，可以在后面追加一个主键排序。

null 存在的坑

查询 运算符、like、between and、in、not in对NULL值查询不起效。

is null / is not null (null 值专用查询)
mysql为我们提供了查询空值的语法：IS NULL、IS NOT NULL。

select 列名 from 表名 where 列 is null;

任何值和NULL使用运算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all）比较时，返回值都为NULL，NULL作为布尔值的时候，不为1也不为0。

当IN和NULL比较时，无法查询出为NULL的记录。

当NOT IN 后面有NULL值时，不论什么情况下，整个sql的查询结果都为空。

count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行。

当字段为主键的时候，字段会自动设置为not null。

创建表的时候，尽量设置表的字段不能为空，给字段设置一个默认值。


分组查询

select column,group_function,... from table
[where condition]
group by group_by_expression
[having group_condition];
group_function：聚合函数。
group_by_expression：分组表达式，多个之间用逗号隔开。
group_condition：分组之后对数据进行过滤。
分组中，select后面只能有两种类型的列：出现在group by后的列,或者 使用聚合函数的列.

分组 前 对数据进行筛选，使用where关键字，分组 后 对数据筛选，使用having关键字，having后是可以跟聚合函数的。

where、group by、having、order by、limit这些关键字一起使用时，先后顺序有明确的限制，语法如下：
select 列 from
表名
where [查询条件]
group by [分组表达式]
having [分组过滤条件]
order by [排序条件]
limit [offset,] count;

在写分组查询的时候，最好按照标准的规范来写，select后面出现的 列 必须在group by中 或者 必须使用聚合函数。

子查询

子查询分类
按结果集的行列数不同分为4种
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

按子查询出现在主查询中的不同位置分
select后面：仅仅支持标量子查询。
from后面：支持表子查询。
where或having后面：支持标量子查询（单列单行）、列子查询（单列多行）、行子查询（多列多行）
exists后面（即相关子查询）：表子查询（多行、多列）

where和having后面的子查询
子查询放在小括号内。
子查询一般放在条件的右侧。
标量子查询，一般搭配着 单行 操作符使用，多行操作符  >、<、>=、<=、=、<>、!=
列子查询，一般搭配着多行操作符使用

子查询的执行 优先于 主查询执行，因为主查询的条件用到了子查询的结果。

mysql中的 in、any、some、all 子查询关键词之一。
in：in常用于where表达式中，其作用是查询某个范围内的数据;
any和some一样： 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的 任何 一个数据。
all：可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的 所有数据。

a>som(10,20,30)，a大于子查询中任意一个即可，a大于子查询中最小值即可
a>all(10,20,30)，a大于子查询中所有值

sql中笛卡尔积语法

select 字段 from 表1,表2[,表N];
或者
select 字段 from 表1 join 表2 [join 表N];

内连接
select 字段 from 表1 inner join 表2 on 连接条件;
或者
select 字段 from 表1 join 表2 on 连接条件;
或者
select 字段 from 表1,表2 [where 关联条件];

内连接建议使用第3种语法，简洁：
select 字段 from 表名1,表名2 [where 关联条件];

外连接分为2种：
左外链接：使用left join关键字，left join左边的是主表。
右外连接：使用right join关键字，right join右边的是主表。

select 列 from 主表 left join 从表 on 连接条件;
先使用 内连接 获取连接结果，然后再 使用where 对连接结果进行过滤。

select 列 from 从表 right join 主表 on 连接条件;


聚合函数

max   查询指定列的最大值
min    查询指定列的最小值
count 统计查询结果的行数
sum  求和，返回指定列的总和
avg   求平均值，返回指定列数据的平均值

视图view

创建视图
create view 视图名 as 查询语句;

create view v_1 as select name,age from test;
select * from v_1 where age=20;//使用视图

select 字段 from 视图 [where 条件];

修改视图
create or replace view 视图名 as 查询语句;
或者
alter view 视图名 as  查询语句;

删除视图
drop view 视图名1 [,视图名2] [,视图名n];

查看视图结构
desc 视图名称;
show create view 视图名称;

系统变量

查看系统所有变量
show [global | session] 变量名;

查看全局变量
show global variables;

查看会话变量
show session variables;
show variables;

流程控制语句

if函数
if(条件表达式,值1,值2);
当参数1为true的时候，返回值1，否则返回值2。

if 条件语句1 then 语句1;
elseif 条件语句2 then 语句2;
...
else 语句n;
end if;
只能使用在begin end之间。

case
case 表达式
when 值1 then 结果1或者语句1（如果是语句需要加分号）
when 值2 then 结果2或者语句2
...
else 结果n或者语句n
end [case] （如果是放在begin end之间需要加case，如果在select后则不需要）
eg:SELECT id,(CASE sex WHEN 1 THEN '男' ELSE '女' END) ,name FROM test;
SELECT id,(CASE sex WHEN 1 then '男' WHEN 2 then '女' END) ,name FROM test;

while循环
[标签:]while 循环条件 do
循环体
end while [标签];

repeat循环
[标签:]repeat
循环体;
until 结束循环的条件 end repeat [标签];

loop循环
[标签:]loop
循环体;
end loop [标签];

结束本次循环
iterate 循环标签;

退出循环
leave 循环标签;

游标

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行遍历数据的能力。
游标只能在存储过程和函数中使用

一个begin end中只能声明一个游标

声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标
open 游标名称;

遍历游标
fetch 游标名称 into 变量列表;

关闭游标
close 游标名称;

索引

是依靠某些数据结构和算法来组织数据，最终引导用户快速检索出所需要的数据。
通过数据结构和算法来对原始的数据进行一些有效的组织，通过这些有效的组织，可以引导使用者对原始数据进行快速检索。

InnoDB中的索引
主键索引（聚集索引）、辅助索引（非聚集索引）。
主键索引：每个表只有一个主键索引，叶子节点同时保存了主键的值也数据记录。
辅助索引：叶子节点保存了索引字段的值以及主键的值。

mysql中页是innodb中存储数据的基本单位，也是mysql中管理数据的最小单位，和磁盘交互的时候都是以页来进行的，默认是16kb，mysql中采用b+树存储数据，页相当于b+树中的一个节点。b+树中叶子页之间用双向链表连接的，能够实现范围查找，页内部的记录之间是采用单向链表连接的，方便访问下一条记录，为了加快页内部记录的查询，对页内记录上加了个有序的稀疏索引，叫页目录（page directory）

mysql中的索引用到了b+树，链表，二分法查找，做到了快速定位目标数据，快速范围查找。

索引分类分为聚集索引和非聚集索引。


```

//查看表索引

 SHOW INDEX FROM tbl_name;

```


权限操作

mysql识别用户身份的方式是：用户名+主机

查看mysql中所有用户
用户信息在mysql.user表中，如下：
>use mysql;
>select user,host from user;

创建用户(所有的用户信息都在一张user表里)
语法:
create user 用户名[@主机名] [identified by '密码'];
主机名默认值为%，表示这个用户可以从 任何主机 连接mysql服务器
密码可以省略，表示无密码登录

>create user test1;
>select user,host from user;

create user 'test2'@'localhost' identified by '123';
test2的主机为localhost表示本机，此用户只能登陆本机的mysql

create user 'test3'@'%' identified by '123';
test3可以从任何机器连接到mysql服务器

create user 'test4'@'192.168.11.%' identified by '123';
test4可以从192.168.11段的机器连接mysql

修改密码【3种方式】
1)通过管理员修改密码
SET PASSWORD FOR '用户名'@'主机' = PASSWORD('密码');

2)create user 用户名[@主机名] [identified by '密码'];
set password = password('密码');

3)通过修改mysql.user表修改密码
use mysql;
update user set authentication_string = password('321') where user = 'test1' and host = '%';
flush privileges;//刷新

给用户授权
grant privileges ON database.table TO 'username'[@'host'] [with grant option]
priveleges (权限列表)，可以是all，表示所有权限，也可以是select、update等权限，多个权限之间用逗号分开。
ON 用来指定 权限针 对哪些库和表，格式为 数据库.表名 ，点号前面用来指定 数据库名，点号后面用来指定 表名，. 表示所有数据库 所有表。
TO 表示将权限赋予某个用户, 格式为username@host，@前面为用户名，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。
WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。

grant all on *.* to 'test1'@‘%’;
给test1授权可以操作所有库所有权限，相当于dba

grant select on seata.* to 'test1'@'%';
test1可以对seata库中所有的表执行select

grant select,update on seata.* to 'test1'@'%';
test1可以对seata库中所有的表执行select、update

grant select(user,host) on mysql.user to 'test1'@'localhost';
test1用户只能查询mysql.user表的user,host字段

查看用户有哪些权限
show grants for '用户名'[@'主机']
主机可以省略，默认值为%，示例:
> show grants for 'test1'@'localhost';

撤销用户的权限,语法:
revoke privileges ON database.table FROM '用户名'[@'主机'];

删除用户【2种方式】
方式1：
drop user '用户名'[@'主机'];
eg:>drop user test1@localhost;
>drop user test2;

方式2：
通过删除mysql.user表数据的方式删除
eg:>delete from user where user='用户名' and host='主机';
>flush privileges;

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,214评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,307评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,543评论 0赞 341
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,221评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,224评论 5赞 371
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,007评论 1赞 284
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,313评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,956评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,441评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,925评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,018评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,685评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,234评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,240评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,464评论 1赞 261
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,467评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,762评论 2赞 345