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;