mysql常用命令及字段类型说明

mysql相关

# 查看版本
select version();

# 查看状态：
show status;

# 查看参数配置：
show variables;

# 查看指定参数：
show variables like 'slow%';

# 临时修改参数值：
set global max_connect_errors=200;
# mysql重启后失效，需要永久修改编辑/etc/my.cnf配置文件

# 查看消息队列：
show processlist;
show full processlist;
# 使用full可以将查询结果info字段中正在执行的sql语句显示完整

# 查看当前用户：
select user();

库相关

# 显示所有库
show databases;

# 查看当前所在数据库
select database();

# 创建数据库
create database [db_name] character set utf-8 collate utf8_general_ci;;

# 删除数据库
drop database [db_name];

# 切换数据库
use [db_name];

表相关

# 显示库中的表：
show tables;

# 查看表字段：
desc [table_name];

# 查看建表语句(有序显示)
show create table [table_name]\G
# (表格显示)
show create table [table_name];

# 创建表：
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(10) ,
  `name` varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 自增属性
AUTO_INCREMENT 

# 修改自增起始值
alter table [table_name] AUTO_INCREMENT=100;

# 删除表
drop table [table_name];

# 修改表名
alter table [table_name] rename [table_name2];

# 增加字段
alter table [table_name] add `[column_name]` [datatype];

# 删除字段
alter table [table_name] drop column `[column_name]`;

# 修改字段
alert table [table_name] change `[column_name]` `[column_name]` [datatype]

# 增加数据
insert into [table_name] (`[column_name]`, `[column_name2]`) values (0 ,'abc');

# 增加数据，根据主键判断，如果不存在则新增，如果存在则忽略
insert ignore into [table_name] (`[column_name]`, `[column_name2]`) values (0 ,'abc');

# 删除数据
delete from [table_name] where `[column_name]`=1;

# 修改数据
update [table_name] set `[column_name]`=2 where `[column_name]`=1;

# 累加/减数据
update [table_name] set `[column_name]`=`[column_name]`+(1|-1) where `[column_name]`=1;

# UNSIGNED 属性 累减数据
update [table_name] set `[column_name]`= `[column_name]` + case when `[column_name]` > 0 then -1 else 0 end where `[column_name]` in (1, 2);
// 单个字段更新累减
update [table_name] set `[column_name]`=`[column_name]`-1 where `[column_name1]`=1 and `[column_name]`>0

# 查询数据
select `[column_name]`, `[column_name2]`|* from [table_name] where `[column_name]`=2 
    group by `[column_name]` 
    having `[column_name]`>1         --针对group by之后再次筛选
    order by `[column_name]` asc|desc 
    limit [start_num], [end_num];

# 存在则更新，不存在则插入
insert into [table_name] (`name`) values (1, 'andy'), (2, 'baby')
on duplicate key update `name`='baby';

# 先删后增
replace into [table_name] (`name`) values (1, 'andy'), (2, 'baby');

索引

# 创建索引
create `[index_name]` on `[table_name]` (`[column_name]`);
create `[index_name]` on `[table_name]` (`[column_name]`, `[column_name]`);
alter `[table_name]` add index `[index_name]` (`[column_name]`);
alter `[table_name]` add primary key (`[column_name]`);
alter `[table_name]` add unique index `[index_name]` (`[column_name]`);

# 删除索引
drop index `[index_name]` on `[table_name]`;
alter `[table_name]` drop index `[index_name]`;
alter `[table_name]` drop primary key;

# 修改索引
alter `[table_name]` drop index `[index_name]` add index `[index_name]` (`[column_name`]);

表结构属性

常用数据类型

数字数据类型

日期与时间数据类型

文本数据类型

空间数据类型

字段属性

主键 primary key

# 主键用于唯一标识每一条记录（每个人都有自己唯一的身份证）
# 每一张表只能有一个主键。
# 因为唯一标识，所以主键字段的数据不能为空，并且主键字段的数据值不能重复
# 主键也是一种索引，可以提高查找速率。

创建/修改
craete table [table_name] (id int PRIMARY KEY);
create table [table_name] (id int, PRIMARY KEY(id));
alter table [table_name] modify `id` int(10) primary key;
删除
alter table [table_name] drop primary key;

唯一键 unique key

# 唯一键的功能与主键有点类型，但不同的是主键只能有一个，唯一键可以有多个，而且唯一键的字段的数据允许为空。
# 唯一键可以约束字段，使得字段的数据不能重复
# 如果唯一键同时也有not null,并且表中没有主键的话，在desc查看表结构中会显示成主键
# 如果唯一键也不允许为空，那么功能与主键相同

唯一键的定义方法可以参考主键的。

删除
alter table [table_name] drop index [键名];

自增长 auto_increment

# 自增长的功能是可以使某个字段的数据随着记录的插入而进行增长（不给这个字段插入数据的情况下）
# 自增长的前提是这个字段必须是一个“索引”，比如主键、唯一键
# 自增长的前提这个字段的数据类型是一个数值型的，（如果给了float,也不会增长成小数，而仅仅是整数）
# 一个表只能有一个自增长。
# 只有不给值，或者给null的情况下，才能正确自增长；如果某一次自增长失败了，那么下一次会从当前字段的最大值开始继续自增长。

定义
create table [table_name] (id int primary key auto_increment);
修改 (只能改大，不能变小)
alter table [table_name] auto_increment=值;

默认值 default

# 默认值的功能是当我们不给一个字段赋值的时候，使用默认值作为数据
create table [table_name] (
    id int primary key auto_increment,
    age int default `0`
)

字段描述 comment

# 字段描述是用来描述字段的，能在查看数据表创建语句的时候显示出来（不会再select结果中显示出来）
create table [table_name] (
    id int primary key auto_increment,
    age int default `0` COMMENT '年龄字段'
)

备份相关

# 备份库
$ mysqldump -uroot -p [db_name] > /tmp/[db_name]_bak.sql

# 恢复库
$ mysql -uroot -p [db_name] < /tmp/[db_name]_bak.sql

# 备份表
$ mysqldump -uroot -p [db_name] [table_name] > /tmp/[db_name]_[table_name]_bak.sql

# 恢复表
$ mysql -uroot -p [db_name] < /tmp/[db_name]_[table_name]_bak.sql

# 备份所有库
$ mysqldump -uroot -p -A > /tmp/dbs_all_bak.sql

# 备份库的表结构
$ mysqldump -uroot -p -d [db_name] > /tmp/[db_name]_bak.sql

# 备份库中某表的机构
$ mysqldump -uroot -p -d [db_name] [table_name] > /tmp/[db_name]_[table_name]_bak.sql