常用的MySQL指令汇总分享

#显示库

show databases;

#切换库

use [库名];

#显示表

show tables;

#查询表内容

select [列名] from [表] where [条件];

#查看表结构

desc [表名];

#创建表

create table [表名]([列名] [类型], [列名] [类型])charset=utf8;

#显示创建表的语句

show create table [表名];

#insert添加，不是全部列添加，剩余列自增

insert into [表名] ([列名],[列名]) values([列值],[列值]);

#添加所有列的简写，需要写上全部值，并对应

insert into [表名] values([列值],[列值]);

#一次插入多行

insert into [表名] values([列值],[列值]),([列值],[列值]);

#update更改，不加where限制全部更改

update [表名] set [列1] = [新值], [列2] = [新值] where [列3] = [列值];

#删除行，不加where全部删除

delete from [表名] where [列名] = [列值];

#查询表的所有行所有行

select * from [表名];

#查询一行

select * from [表名] where [列名] = [列值];

#查询某几行的某几行

select [列名],[列名] from [表名] where [列名] = [列值];

#select 查询模型

#取出部分列叫作投影运算

#两个列做运算叫作广义投影

#查询全部和0行，当where后面为真才会执行

select * from [表] where 1;

select * from [表] where 0;

#列还可以运算

select age+1 from [表];

select floor(age) from [表];

#取出某个集合内的行

select * from [表] where [列名] in ([列值],[列值]);

#取出两个值之间的行

select * from [表] where [列名] between [值] and [值];

#取出不属于两个值之间的行

select * from [表] where [列名] != [值] and [列名] != [值];

select * from [表] where [列名] where [列名] not in([列值],[列值]);

#取出两个范围的之内的行

select * from [表] where ([列名] between [值] and [值]) or ([列名] between [值] and [值]);

select * from [表] where ([列名] > [值] and [列名] <[值]) or ([列名] > [值] and [列名] <[值]);

#取出以某个字符串开头的行

select * from [表] where [列名] like "[字符串]%";

#取出指定长度但以字符串开头的行

select * from [表] where [列名] like "[字符串][剩下长度个数的_]";

#取出不以某个字符串开头的行

select * from [表] where [列名] notlike "[字符串]%";

#查询null

select * from [表] where [列名] is null;

#取别名

select age - num as test from [表];

#count()计算行数

select count(*) from [表];

#avg() 求平均数

select avg(age) from [表];

#sum() 求总和

select sum(age) from [表];

#min() 求最小

select min(age) from [表];

#max() 求最大

select max(age) from [表];

#group分组

#按某列属性分组求最大值,即对具有相同列属性的行进行处理

selec max(age) from [表] group by [列];

#where和having的区别

#where只能筛选原有的列，不能筛选别名

#having可以筛选别名,因为他是在内存结果集上操作的

#命令顺序

#from >> where >> group by >> having >> order by >> limit

#order by 排序

#正序

select * from [表] order by [列] asc;

#倒序

select * from [表] order by [列] desc;

#先升序再降序

selecr * from [表] order by [列] asc , [另一列] desc;

#limit一般和order by合用

#limit [跳过条数],[输出多少条]

#取出前三条

select * from [表] order by [列] desc limit 0,3;

#取出前三到前五

select * from [表] order by [列] desc limit 2,3;

#子查询和链接查询

#where型子查询

select [列1],[列2],[列3] from [表] where [列1]=(select [列1] from [表] order by goods_id desc limit 1);

select [列1],[列2],[列3] from [表] where [列1] in (select [列1] from [表] order by [列]);

#from型子查询，需要对子表取别名，内部排序不起作用

select [列1],[列2],[列3] from (select [列1],[列2],[列3] from [表]) as [别名]group by [列] ;

#exists子句查询

#当需要查询两张表的都有的列，并具有相同的列值的行时

select * from [表1] where exists (select * from [表2] where [表1].[列]=[表2].[列]);

#inner join查询,当两张表有相同的列名时

select [表1].[列1],[表1].[列2],[表2].[列1],[表2].[列2] from [表1] inner join [表2] on [表1].[列1]=[表2].[列1];

select [表1].[列1],[表1].[列2],[表2].[列1],[表2].[列2] from [表1] inner join [表2] on [表1].[列1]=[表2].[列1] where [条件];

#左连接和右链接查询

#左连接，以左表为准，右边没有被链接的不显示

select [表1].[列1],[表1].[列2],[表2].[列1],[表2].[列2] from [表1] left join [表2] on [表1].[列1]=[表2].[列1];

select [表1].[列1],[表1].[列2],[表2].[列1],[表2].[列2] from [表1] left join [表2] on [表1].[列1]=[表2].[列1] where [条件];

#右链接，以右表为准

select [表1].[列1],[表1].[列2],[表2].[列1],[表2].[列2] from [表1] right join [表2] on [表1].[列1]=[表2].[列1];

select [表1].[列1],[表1].[列2],[表2].[列1],[表2].[列2] from [表1] right join [表2] on [表1].[列1]=[表2].[列1] where [条件];

#多次链接

select [表1].[列1],[表1].[列2],[表2别名1].[列1],[表2别名2].[列2] from [表1] right join [表2] as [表2别名1] on [表1].[列1]=[表2别名1].[列1] right join [表2] as [表2别名2] on [表1].[列1]=[表2别名2].[列2];

#union查询，将两个查询结果合并,当两个值一样时会取其中之一,取出各表的列数要相同，不能使用order by,取出的列名以第一个表为准

select * from [表1] union select * from [表2];

#如果不想只取1个

#union all查询

select * from [表1] union all select * from [表2];

#取出的结果可以排序

(select * from [表1]) union all (select * from [表2]) order by [列];

#创建表

creat table [表名](

[列名] [类型] [主键] [是否自增],

[列名] [类型]

)charset=utf8;

#添加列

alter table [表名] add [列名] [类型] [有无符号];

alter table [表名] add [列名] [类型] [有无符号] after [列];

alter table [表名] add [列名] [类型] [有无符号] before [列];

alter table [表名] add [列名] [类型] [有无符号] first;

#删除列

alter table [表名] drop [列名];

#删除表

drop table [表名];

#删除视图

drop view [视图名];

#删除库

drop database

#修改列属性

alter table [表名] modify [列名] [新属性];

#修改列名和属性

alter table [表名] change [列名] [新列名] [新属性];

#暂时建表语句

show create table [表名];

#修改表名

rename table [表名] to [表名];

#清空表数据，但记住之前的表结构

delete from [表名];

#清空表数据，但不记住之前的表结构

truncate [表名]

#创建视图

create view [视图名] as select [列名], [列名], [列名] from [表名];

#视图被修改，原表也会被修改

#原表被修改，视图也会被修改

#显示表状态

show table status \G

show table status where name=[表名] \G

#显示警告

show warnings;

#主键，不能重复

#声明键索引后才可以自增

creat table [表名](

[列名] [类型] auto_increment,

[列名] [类型],

key [自己起的键名]([要设为键的列])

)charset=utf8;

#列的默认值

creat table [表名](

[列名] [类型] [not null/null] default [默认值]

)charset=utf8;

#列类型大致分为3类

#数值型

#字符串型 char定长 varchar不定长

#日期型 时间戳 timestamp 定义这个类型之后，插入数据会自动生成插入的时间

#可选参数

#zerofill 零填充 整形规定范围只能在声明零填充时才会起效

#unsigned 无符号

#显示字符集

show variables like '%charact%';

#将客户端 返回结果 命令行一次性改成gbk

set names gbk;

#索引，方便查找，类似书的目录，提高查询速度

#key 普通索引，可以重复

creat table [表名](

[列名] [类型] auto_increment,

[列名] [类型],

key ([要设为键的列])

)charset=utf8;

#unique key, 不可以重复

creat table [表名](

[列名] [类型] auto_increment,

[列名] [类型],

unique key ([要设为键的列])

)charset=utf8;

#主键索引，primary key

#全文索引，fulltext，中文环境下，全文索引无效

#多列索引

creat table [表名](

[列名] [类型] auto_increment,

[列名] [类型],

key ([要设为键的列],[要设为键的列])

)charset=utf8;

#展示索引

#show index from [表名]

#显示查询用到的索引

#explain select * from [表] where [列名] = [列值]

#冗余索引，给一个列添加多个索引

creat table [表名](

[列名] [类型] auto_increment,

[列名] [类型],

key ([要设为键的列]),

key ([要设为键的列])

)charset=utf8;

#索引操作

#显示索引

show index from [表名];

#删除索引

alter table [表名] drop index [索引名];

drop index [索引名] on [表名];

#添加索引

alter table [表名] add index [索引名]([列名]);

#添加主键索引不需要起名字

alter table [表名] add primary key ([列名]);

#删除主键

alter table [表名] drop primary key;

#事务的特性

#隔离性 原子性 一致性 持久性

#开启事务，开启事务后的操作预保留结果，但是在commit之前不会影响表

start transaction

#提交事务

commit;

#不需要事务修改的结果

rollback;