MySql操作大全

我不是女神ヾ 2022-05-21 02:21 205阅读 0赞

**1、Grant赋权**

\#大数据库迁移

SET GLOBAL log\_bin\_trust\_function\_creators = 1;

\#远程访问开放

grant all privileges on \*.\* to root@'%' identified by '123456';

grant all on uclass3.\* to muser@'192.168.0.60' identified by "123456";

grant all on \*.\* to muser@'192.168.0.60' identified by "123456";

grant select,insert,update,delete on uclass3.\* to muser@'192.168.0.60' identified by "123456";

**2、引擎切换**

select concat('ALTER TABLE ',TABLE\_name,' ENGINE = innodb;') from information\_schema.TABLES where TABLE\_SCHEMA='scpt1435';

**3、索引创建、导出**

select CONCAT('create index ',INDEX\_NAME,' ON ',TABLE\_NAME,'(',GROUP\_CONCAT(COLUMN\_NAME),');')

from information\_schema.statistics where TABLE\_SCHEMA='scpt\_online\_140319' and INDEX\_NAME!='PRIMARY' and TABLE\_NAME!='sys\_user\_library'

GROUP BY INDEX\_NAME;

**4、数据库备份**

**--hex-blob**

使用十六进制格式导出二进制字符串字段。

mysqldump  -uroot -p --all-databases --hex-blob

**--routines, -R**

导出存储过程以及自定义函数。

mysqldump  -uroot -p --host=localhost --all-databases --routines

mysql mysqldump 只导出表结构 不导出数据
    
    复制代码 代码如下:
    
    mysqldump --opt -d 数据库名 -u root -p > xxx.sql 
    
    备份数据库 
    
    复制代码 代码如下:
    
    #mysqldump　数据库名　>数据库备份名 
    
    #mysqldump　-A　-u用户名　-p密码　数据库名>数据库备份名 
    
    #mysqldump　-d　-A　--add-drop-table　-uroot　-p　>xxx.sql 
    
    1.导出结构不导出数据 
    
    复制代码 代码如下:
    
    mysqldump　--opt　-d　数据库名　-u　root　-p　>　xxx.sql　　 
    
    2.导出数据不导出结构 
    
    复制代码 代码如下:
    
    mysqldump　-t　数据库名　-uroot　-p　>　xxx.sql　 
    
    3.导出数据和表结构 
    
    复制代码 代码如下:
    
    mysqldump　数据库名　-uroot　-p　>　xxx.sql　 
    
    4.导出特定表的结构 
    
    复制代码 代码如下:
    
    mysqldump　-uroot　-p　-B　数据库名　--table　表名　>　xxx.sql　　 
    
    导入数据： 
    
    　　由于mysqldump导出的是完整的SQL语句，所以用mysql客户程序很容易就能把数据导入了： 
    
    复制代码 代码如下:
    
    #mysql　数据库名　<　文件名 
    
    #source　/tmp/xxx.sql

**5、慢查询设置**

**6、分区表**

**7、数据库主从**

CHANGE MASTER TO

MASTER\_HOST='192.168.0.61',

MASTER\_USER='muser',

MASTER\_PASSWORD='123456',

MASTER\_PORT=3306,

MASTER\_LOG\_FILE='mysql-bin.000001',

MASTER\_LOG\_POS=98,

MASTER\_CONNECT\_RETRY=10;

MYSQL主从同步的作用

(1) 数据分布

(2) 负载平衡(load balancing)

(3) 备份

(4) 高可用性(high availability)和容错

**8、存储过程**

**9、数据库初始化及清库脚本编写**

select concat('DELETE FROM  ',TABLE\_name,';') from information\_schema.TABLES where TABLE\_SCHEMA='scpt\_res\_sp';

**10、垂直分库优缺点赏析**

**11、数据库引擎修改**

select concat('ALTER TABLE ',TABLE\_name,' ENGINE = innodb;') from information\_schema.TABLES where TABLE\_SCHEMA='scpt1435';

select concat('DELETE FROM  ',TABLE\_name,';') from information\_schema.TABLES where TABLE\_SCHEMA='scpt\_res\_sp';

**12、查询库各表空间大小**

> [查看MySQL数据库大小][MySQL]
> 
> 1、进去指定schema 数据库（存放了其他的数据库的信息）
> 
> use information\_schema
> 
> 2、查询所有数据的大小
> 
> select concat(round(sum(DATA\_LENGTH/1024/1024),2),'MB') as data  from TABLES
> 
> 3、查看指定数据库的大小
> 
> 比如说 数据库apoyl
> 
> select concat(round(sum(DATA\_LENGTH/1024/1024),2),'MB') as data  from TABLES where table\_schema='apoyl';
> 
> 4、查看指定数据库的表的大小
> 
> 比如说 数据库apoyl 中apoyl\_test表
> 
> select concat(round(sum(DATA\_LENGTH/1024/1024),2),'MB') as data  from TABLES where table\_schema='apoyl' and table\_name='apoyl\_test';
> 
> 例子：
> 
> select table\_name, concat(round(sum(DATA\_LENGTH/1024/1024),2),'MB') as data  from 
> 
> TABLES where table\_schema='tc' GROUP BY TABLE\_NAME;

**13、MySQL---使用GROUP\_CONCAT后limit无效**

SELECT GROUP\_CONCAT(t.id) from (SELECT id from md.entity\_field where domain\_entity\_id=1370 limit 60,20)t;

**14、MySQL实现笛卡尔积**

SELECT count(*) from (
    SELECT * from price_store pss 
    where pss.tenant_id=11068  and pss.factor_dm_bo_name is not  null
    and pss.price_subject_id not in(SELECT DISTINCT pss1.price_subject_id from price_store pss1 where pss1.tenant_id=11068 and pss1.factor_dm_bo_name is not null and pss1.price_subject_id is not null ) 
    )pss_temp
    cross join store ps  where ps.tenant_id=11068;

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbXBsZW11cnJpbmE_size_16_color_FFFFFF_t_70][]

15、mysql列转行

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbXBsZW11cnJpbmE_size_16_color_FFFFFF_t_70 1][]

[MySQL]: http://www.cnblogs.com/flysoft/archive/2011/11/15/2250057.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbXBsZW11cnJpbmE_size_16_color_FFFFFF_t_70]: /images/20220521/b0ede25a36b84b58a538125564630cfa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbXBsZW11cnJpbmE_size_16_color_FFFFFF_t_70 1]: /images/20220521/4ef3afc004204a2295be47e6f615b2b3.png