mysql常用sql_mysql常用sql

## mysql常用sql

## 1. 导入与导出

```

-- 导出指定表

mysqldump -h192.168.1.1 -uroot -P3306 -p --single-transaction --databases database1 --tables table1 table2 > table1_table2.sql

-- 导出指定表的部分数据

mysqldump -uroot -p --single-transaction --databases database --tables table1 --where="id < 2" > partTable.sql

-- 导出指定数据库

mysqldump -h192.168.1.1 -uroot -P3306 -p --single-transaction --databases database1 > database1.sql

-- 导出全部数据库

mysqldump -h192.168.1.1 -uroot -P3306 -p --single-transaction --all-databases > all.sql

```

## 2. 索引处理

-- 创建唯一索引

CREATE UNIQUE INDEX `uni_idx` ON `database1`.`table1` (`column1`) USING HASH;

-- 创建普通索引

ALTER TABLE `table1` ADD INDEX `index1`(`column1`);

## 3. 数据传输

-- 将某表的某几个字段导入到另一个表，两个表的结构可以不一样

INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;

-- 表结构一样,全表复制

INSERT INTO 目标表 SELECT * FROM 来源表;

-- 通过建表的方式,临时存储数据

CREATE TABLE `tmp`(SELECT `column1`,`column2` FROM `source_table` WHERE `name` = '张三')

## 4. merge表创建

`gt_huiz_1`和`gt_huiz_2` 是基于 `myisam`引擎的， 创建`gt_huiz`表用于联合两张表，方便查询。

`gt_huiz_1`和`gt_huiz`的建表语句是差不多的，但是`gt_huiz`里面是没有包含索引信息的，其它都一样。

CREATE TABLE `gt_huiz` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`md5` varchar(32) DEFAULT '' COMMENT 'md5',

`status` int(11) DEFAULT '0' COMMENT '状态 0-可用 其他-不可用',

`has_md5` int(11) DEFAULT '0' COMMENT '是否有MD5 0-没有 1-有',

`gmt_created` timestamp NULL DEFAULT '2000-01-01 00:00:00' COMMENT '创建时间',

`gmt_modified` timestamp NULL DEFAULT '2000-01-01 00:00:00' ON UPDATE CURRENT_TIMESTAMP,

INDEX (`id`)

) ENGINE=MERGE UNION=(gt_huiz_1,gt_huiz_2) DEFAULT CHARSET=utf8;

合成的 `gt_huiz` 可以直接查询到两张表的数据。

## 5. 数据查询

-- 筛选出column1 字段 不为纯数字的数据 (为0则表示为纯数字)

select * from ddd where (`column1` REGEXP '[^0-9.]') = 1

## 6. 表结构修改

-- 添加字段(指定顺序)

alter table `table1` add column `source_file_name` varchar(64) default '' comment '导入文件名称' after `mobile_city`;

## 7.添加只读权限

```

-- version 5.7.19-log

GRANT SELECT ON database_name.* TO 'user_name'@'%' IDENTIFIED BY "yourpassword";

```

## 8. 查看所有账号

```

-- version 5.7.19-log

select user from mysql.user;

```

## 9. 添加账号并授权

```

-- version 5.7.19-log

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

flush privileges;

```

## 10. 修改账号密码

```

-- version 5.7.19-log

update user set authentication_string = password('123456') where user = 'root';

```