MySQL常用命令

 

备份表
create table km_team_member_20201221 like km_team_member;
insert into km_team_member_20201221 select * from km_team_member;

添加索引

ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）

//普通索引
alter table table_name add index index_name (column_list);
//唯一索引
alter table table_name add unique (column_list);
//主键索引
alter table table_name add primary key (column_list) ;

 

常用操作

查看进程：show PROCESSLIST （可以看有没有慢查询、超时和死锁）

查看数据库所在路径：SHOW VARIABLES LIKE 'datadir'
查看数据库支持的引擎：SHOW ENGINES

mysql缓存：
https://blog.csdn.net/weixin_39626613/article/details/113121854
https://blog.csdn.net/yabingshi_tech/article/details/49494487
查询优化：
https://www.cnblogs.com/firstdream/p/5899383.html


create table km_team_member_20201221 like km_team_member;
insert into km_team_member_20201221 select * from km_team_member;
rename table 旧表名 to 新表名;
alter table 旧表名 rename [as] 新表名


1、 添加PRIMARY KEY（主键索引） 
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
 
2.添加UNIQUE(唯一索引) 
mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
 
3.添加INDEX(普通索引) 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 
 
4.添加FULLTEXT(全文索引) 
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
 
5.添加多列索引 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

1、修改字段
alter table access_log modify column `duration` int(11) NOT NULL DEFAULT '0' COMMENT '请求耗时';

2、修改备注
alter table km_materials modify column `bitrate` int(11) DEFAULT '0' COMMENT '视频码率(总码率)';
导入数据表：
nohup  mysqldump  -h 127.0.0.1 -uroot -p123456 xunmall  <  user.sql  2>&1  &

导入导出
mysql命令：导入
source /home/xunmall.sql  (要先use 数据库)
linux命令：导入
nouhp mysqldump -h 127.0.0.1 -uroot -p123456 ugdesk < xunmall.sql 2>&1  & exit

# nohup npm run dev >/dev/null 2>&1 & exit    （不记录日志）
导出：
　　mysqldump -h 127.0.0.1 -uugdesk -D ugdesk -p123456 数据库名 表名 > /tmp/ug_stats_materila_hourly

1.sql 
函数会阻止索引
5.7，虚拟列
查询语句可以正常使用虚拟列作为索引。
通过虚拟列的方式，即满足了查询性能，也不会有之前那个解决方案的潜在麻烦。
虚拟列不存储在数据行中，但虚拟列的元数据信息会存在于相关系统表中，对虚拟列的添加或者删除只会涉及这些系统表，不会导致数据表的重建，所以效率很高。
需要注意，不能建立虚拟列和真实列的联合索引
给IP和账号设置数据库权限

grant all on *.* to root@'192.168.20.49';
grant select on *.* to root@192.168.20.49 ; -- dba 可以查询 MySQL 中所有数据库中的表。 
grant all    on *.* to root@192.168.20.49 ; -- dba 可以管理 MySQL 中的所有数据库 


以上不行就下面
GRANT ALL PRIVILEGES ON *.* TO 'aitang'@'192.168.100.142' IDENTIFIED BY '123' WITH GRANT OPTION;
FLUSH PRIVILEGES;
PHPstudy升级MySQL到5.7.17

1、复制新下载的文件夹内容到MySQL，并新建data文件夹
2、把MySQL/bin 加入到系统变量
3、mysqld --initialize 然后 mysqld --install
4、sc delete mysql (删除旧的服务，否则不能启动)
5、net start mysql 启动新服务 （4/5顺序可调换）
6、此时密码错误，（要跳过表权限，修改密码，重启服务）
group_contact 用法

用于分组查询 https://www.cnblogs.com/hanybblog/p/6125792.html

分组查询时在select语句中可用的group方法：
1、group_concat(column1, "分割符号") //分隔符可省略，默认逗号


高级SQL查询

1、 
    date格式的条件选择  
      SELECT * FROM fa_front_project WHERE dayofweek(updatetime) = 2;   ### 
    时间格式化，两位小数，平均值
      SELECT DATE_FORMAT(QX_D_DATE,'%Y-%m-%d') as new_time,id,format(AVG(QX_D_T_CR),2) as t FROM t_ir_qxdtcr_m WHERE QX_D_DATE < '2018-12-17' AND QX_D_DATE > '2017-11-17' AND QXZ_STN_ID = 54113 GROUP BY new_time;
2、 MySQL函数
    数字函数
      随机函数 round()  
      格式化小数format(x,d)
      截取函数truncate()函数会将小数部分2位以后的值直接舍去。  
      类型转换函数  convert(expr,type)，用法：select convert(salary,decimal(10,2)); convert()函数会对小数部分进行四舍五入操作，
    时间格式函数：  
      DATE_FORMAT(QX_D_DATE,'%Y-%m-%d') 把'2008-08-08 22:23:01' 转成 2008-08-09  
3、替换某个表中某个字段的某个字符
    update 表名
    set 字段名=REPLACE (字段名,'原来的值','要修改的值')
4、 批量更新
    UPDATE fa_front_project
    SET edge_json = CASE id
    WHEN 1 THEN
        101
    WHEN 2 THEN
    102
    ELSE
        999999
    END CASE;

5、FIND_IN_SET 查找

6、一张表中两个字段互换
SET SQL_SAFE_UPDATES = 0;

update temp3 as a, temp3 as b set a.test1=b.test3, a.test3=b.test1 where a.id=b.id and a.test4=1 and b.test4=1;

SET SQL_SAFE_UPDATES = 1;
索引

    索引数量不宜太多，可以联合索引，遵从最左前缀，遇到范围查找不适用后面的索引 
haproxy实现mysql双主高可用的详细过程

https://blog.csdn.net/bbwangj/article/details/82969078