MySql常用命令解锁与知识补充 +跑数据

后续会按时间线更新。

查看数据库、查看表字段

• 查看数据库：show databases;
• 查看有哪些表：show tables;
• 库里表太多，可以模糊查：
  show tables like "%keyword%"
• 查看表结构、字段信息：
  a) desc 表名;
  b) show columns from 表名;
  c) decsribe 表名;
  d) show create table 表名; 可以查询创建表的语句。
  abc三种，展示结果一致。比如一个学生表，查看结果会如下：
• 设置编码：1set names utf8;

Filed	Type	Null	Key	Default	Extra
student_id	bigint(20) unsigned	NO	PRI	NULL	auto_increment
time	int(10) unsigned	YES	MUL	0
…	…	…	…	…	…
key（PRI、UNI、MUL）：

• PRI主键约束
  - UNI唯一约束
  - MUL可以重复

查看内容

• 查看表前5行：select * from table_name limit 5;
• 表中某个字段有哪些值：select distinct(字段名) from table_name;

select 要返回的字段或者表达式
from 表
where 行级过滤
group by 分组，仅在按组计算聚集（count、sum、avg...）时使用
having 组级过滤
order by 排序

插入数据insert into

insert into 表名(列名1，列名2，列名3) values(值1，值2，值3);
insert into 表名(列名1，列名2，列名3) values(值1，值2，值3) on duplicate key update 列名=值1, 列名2=值2, 列名3=值3;
如果写入全部字段：
insert into 表名(值1，值2，值3，值4...)...; 注意和表中字段顺序对应。

补充：注意写on duplicate key update ：
这是mysql特有的语法。
含义：当insert插入的数据已经存在时，就执行update。
而只写insert into 表名(列名1，列名2，列名3) values(值1，值2，值3) ，当数据已经存在的时候，还会再写入，导致重复数据。

注意：什么叫数据已经存在？
“如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句。”
unique key好像不建议设置，会影响性能？？

更新数据update

 `UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]`

当返回值为0时，表示数据修改失败； 当返回值为1时，表示数据修改成功。

删除数据 尽量加标识

建议用逻辑标记删除，避免物理删除。
不建议用delete，delete会影响性能。

delete物理删除既不能释放磁盘空间，而且会产生大量的碎片，导致索引频繁分裂，影响SQL执行计划的稳定性；
同时在碎片回收时，会耗用大量的CPU，磁盘空间，影响表上正常的DML操作。
在业务代码层面，应该做逻辑标记删除，避免物理删除；为了实现数据归档需求，可以用采用MySQL分区表特性来实现，都是DDL操作，没有碎片产生。

创建表

示例：创建一张学生表
自增id ，学生姓名，学生年龄，身体素质打分，该条数据修改时间
随便写了几个字段

定义示例：
create table `student`(
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto increment id',
 `name` varchar(10) NOT NULL COMMENT 'student name',
 `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'age',
 `score` decimal(4,4) DEFAULT '0.0000' COMMENT 'weight',
 `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'alter time',
 `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '1:ok 0:deleted',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='student table'

id	name	age	score	mtime	status
1	刘雯	20	0.95	2021-06-10 18:05:04	0

变更数据库表

新增字段

给学生表新增score列，且新增在age列之后

给学生表新增score列，且新增在age列之后:
alter table student add column score decimal(4,2) NOT NULL default '0' comment 'weight' after age;
column可省略

修改字段类型

 ALTER  TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型 新类型长度  新默认值  新注释;
 alter table student modify score decimal(4,2) NOT NULL DEFAULT '0' comment 'weight';

修改索引

对于mysql5.7以前的版本：

ALTER TABLE tbl_name DROP INDEX old_index_name
ALTER TABLE tbl_name ADD INDEX new_index_name(column_name)

跑数据常用命令

mysql -h $hostname -P ${port} -p${password} -u${username} -A ${db}  -e "${select_sql}" > dataname
hostname或ip
eg.

---

知识方面

1、主键（primary key）、唯一键（unique key）区别

主键（primary key）、唯一键（unique key）区别：

	保证唯一性	是否允许为空	一个表中可以有多个	能否组合
primary key	√	X	至多一个	√
unique key	√	√	可以有多个	√
能否组合的意思是：可以由多个字段组成。
一个表主键或者唯一键好像只能二选一？？？to do。

unique key主要是用来防止数据插入时候重复的。
定义了unique约束的字段不能包含重复值，可以为一个或多个字段定义unique约束。
一个表只能有一个主键，但是可以有好多个UNIQUE，而且UNIQUE可以为NULL值，如员工的电话号码一般就用UNIQUE，因为电话号码肯定是唯一的，但是有的员工可能没有电话。

2、not null

not null : 给字段加not null约束，意思是该字段不能为空。
建议：建表的时候，各字段定义的时候写上not null约束。

3、数据类型

3.1 整型 (int 、tinyint、smallint、bigint)

数据类型	tinyint	smallint	int	bigint
存储大小	占1字节	2字节	4字节	8字节
建表时候一般	tinyint(3)	smallint(5)	int(10)	bigint(20)
unsigned下整型范围	0-255	0-65535	0-4294967296	0-…
tinyint(3)显示的最小长度是3字符。

3.2 带小数型（float double decimal）

|数据类型| float | double |decimal(M,D)|
|–|–|–|–|–|
|存储大小 |占4字节 | 8字节 |M字节，|
| 建表时候一般 | | | |
|示例| | |…|
float(20,2) 意思：一共20位，小数部分占2位，整数部分最多占18位。

3.3 日期和时间类型

数据类型	字节数	示例
DATETIME	8个字节
DATE	3个字节
TIME	3个字节
YEAR	3个字节
TIMESTAMP	4个字节	2021-06-10 18:05:04

3.4 字符串类型

数据类型	字节数	示例
varchar(m)		iphone
char(m)	m个字节