Mysql常用sql

Mysql常用sql

查

表所有字段信息

SHOW FULL COLUMNS FROM `db`.`table_name`

查询表在哪个数据库

SELECT table_schema FROM information_schema.TABLES WHERE table_name = 'table_name';

查找字段在哪个数据库和表中有使用到

select table_schema,table_name from information_schema.columns where column_name = 'column_name'

增

表新增字段

ALTER TABLE `db`.`table_name`
	ADD `column` smallint(6) unsigned zerofill default '0' COMMENT '无符号、填充零',
    ADD `column` mediumint default NULL COMMENT '备注',
    ADD `column` int(11) NOT NULL COMMENT '备注',
    ADD `column` bigint default NULL COMMENT '备注',
    ADD `column` char(10) NOT NULL COMMENT '备注';

类型	字节/8bit	2^*	有符号	无符号
tinyint	1	2^8	0~255	-128~127
smallint	2	2^16	0~65536	-32768~32767
mediumint	3	2^24	0~16777216	0~16777215
int	4	2^32	0~4294967296	-2147483648~2147483647
bigint	8	2^64	0~((2^64)-1)	-(2^64)/2 ~ (2^64)/2-1

• int(6)括号里的数字

  • 括号里的数字表示的是最大显示宽
  • 用于需要 填充零 zerofill 时，位数不足这填充零至6位，位数超过则显示完整显示

• varchar(10)括号里的数字

  • 括号里的数字表示的是字符长度，最大字节65535 可变长度，可取 0-65535
  • 可变长度，实际存入的字符数 = 实际字符数 + 1个字节

• char(10) 括号里的数字

  • 括号里的数字表示的是字符长度，最大字节数255 定长类型，可取 0-255
  • 固定长度，但存储位数小于定长，它的右边填充空格以达到指定长度
  • 当检索到char值时，尾部的空格被删除掉 (性别 密码)

表新增索引

ALTER TABLE `db`.`table_name`
	ADD PRIMARY KEY (`column`),
	ADD UNIQUE KEY `index_name` (`column`),
	ADD FULLTEXT KEY `index_name` (`column`),
	 (`column`),
	ADD INDEX `index_name` (`column1`,`column2`,`column3`);

• PRIMARY (主键索引)
• UNIQUE（唯一索引）
• FULLTEXT（全文索引）
• INDEX（普通索引）

批量查询-插入

改

修改字段信息

ALTER TABLE `db`.`table_name`
	MODIFY `column` int(11) default NULL COMMENT '备注';

删

删除字段

ALTER TABLE `db`.`table_name`
	DROP COLUMN `column`,
	DROP COLUMN `column`;

删除索引

ALTER TABLE `db`.`table_name`
	DROP KEY `index_name`,
	DROP INDEX `index_name`;
	
DROP INDEX `index_name` ON `db`.`table_name`

删除表数据

TRUNCATE TABLE `table_name`;
DELETE FROM `table_name`;

• truncate 是整体删除，delete是逐条删除，所以在删除整个表数据时，truncate比delete快得多

• truncate不写进服务器的log， delete会写进服务器的log

• truncate删除之后，标识列，自增字段，索引重新归1，而delete删除之后，再次添加数据， 会重新会从删掉的索引之后自增。

删除表

DROP TABLE `table_name`;
DROP TABLE IF EXISTS `table_name`;

Explain

id

表示执行优先级

• 每个select都会对应一个id
• id值越大，执行优先级越高
• 相同的id值，从上往下依次执行

select_type

• simple：简单查询，不包含子查询和union
• primary：复杂查询的最外层查询
• subquary：包含在select中不包含在from的子查询
• derived：包含在from中的子查询，mysql会把这些数据放到临时表中，也称为派生查询
• union：在union中第二个和随后的select

table

表示explain正在访问哪张表

type

表示关系类型或者访问类型，即mysql如何查找表中的行

• NULL：mysql在优化阶段，分解查询语句，在执行阶段不用访问索引树或者表的查询类型
• system：是const的特例，当表中只有一条数据时的const查询是system
• const：mysql对查询的某部分优化并将其转化为一个常量，用于primary key与常量比较时，表最多返回一条记录。
• eq_ref：primary key 或者unque key索引的所有部分被引用，最多只会返回一条记录，简单的select查询不会出现这种type
• ref：不实用唯一索引，使用普通索引或者唯一索引的前缀部分，索引和某个值比较，可能会返回多行
• range：范围扫描，通常出现在in between
• index：扫描全索引就能拿到结果，一般扫描的是二级索引，这种扫描不会从根节点开始快速查找，而是直接对二级索引的子节点遍历扫描。速率是比较慢的，这种查询一般为使用为覆盖索引，二级索引一般比较小，比all快一些
• all：全表扫描，扫描聚簇索引的所有字节点，一般这种情况是需要优化的

执行效率：system > const > eq_ref > ref > range > index > all

possible_keys

显示可能用到哪些索引

key

查询过程中使用到的索引

key_len

查询中使用索引的长度

ref

显示在key列记录的索引中，表查找时所用到的列或常量，常见的有 const 常量，字段名

rows

每张表有多少行被优化器查询，所需读取的行数越少越好

Extra

额外信息，常见的重要信息如下：

• useing index：使用覆盖索引
• using where：使用where语句来处理结果，并且查询的列未被索引覆盖
• using index condition:查询的列不完全被索引覆盖
• useing temporary：mysql需要创建一张临时表来处理查询，这种情况一般是需要优化的，一般使用覆盖索引来优化
• using filesort:将用外部排序而非索引排序，数据较小时在内存排序，较大时使用文件排序。这种情况通常需要优化