一、常用命令

1.修改密码

select user, host, authentication_string from mysql.user; # 查看用户
alter user 'root'@'localhost' identified by '123456'; # 修改密码

2.创建表

USE `xxx-db`;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(15) NOT NULL DEFAULT '' COMMENT '登录账号',
  `type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1普通账号 2VIP账号',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1正常 2禁用',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `is_delete` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1未删除 2已删除',
  `add_time` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' COMMENT '添加时间',
  `delete_time` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' COMMENT '删除时间',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_account` (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账号表'

3.表操作

alter table `user` rename `user_bak`; # 修改表名
truncate table `user`; # 清除表
alter table `user` engine=innodb; # 更改引擎
show create table `user`; # 显示创建表的sql

4.字段操作

alter table `user` add `status` int not null default 1 comment "状态值" after `name`; # 添加字段
alter table `user` modify `content` varchar(300) not null default '' comment "备注"; # 修改字段属性
alter table `user` change `name` `nickname` varchar(30) not null default '' comment '名称'; # 修改字段名称
alter table `user` drop age; # 删除字段

5.索引操作

show index from `user`; # 查看索引
show keys from `user`; # 查看索引

alter table `user` add primary key (`column`); # 添加主键索引 
alter table `user` add unique (`column`); # 添加唯一索引
alter table `user` add fulltext (`column`); # 添加全文索引 
alter table `user` add index idx_name (`column` ); # 添加普通索引 
alter table `user` add index idx_name (`column1`, `column2`, `column3`); # 添加联合索引

alter table table_name drop index idx_name ; # 删除索引
alter table table_name drop primary key; # 删除主键

6.增删改查

select * from `user` where `id`=1; # 查询

insert into `user` values('chenqi','m', 29); # 添加
insert into `user` (name, sex) values ('abby', 'f'),('joseph', 'm');

update `user` set name='lise',age=18 where `id`=1; # 修改
delete from `user` where `id`>10; # 删除

insert into `goods` (`title`,`image`) select `title`,`image` from `goods`; # 复制表
insert into `goods` (`title`,`image`) select `title`,`image` from `goods` where id=10;

7.批量更新不同数据

$sql = 'UPDATE `user` SET `age` =  CASE `id`';

$ids = [];
foreach ($arr as $v) {
     $ids[] = $v['id'];
     $sql .= ' when ' . $v['id'] . ' THEN ' . mt_rand(1, 100);
}
$ids = implode(',', $ids);

$sql = ' END  WHERE `id` IN ($ids)';

# 如果报错，Could not execute JDBC batch update
# 修改 mysql 配置文件 my.cnf 中的 max_allowed_packet = 5M（若没有参数则添加），原因 mysql 默认通讯数据包大小为 1M，因发送 sql 语句过长造成执行失败。

8.if 条件更新数据

UPDATE `user`
SET `type` = (
CASE 
  WHEN (width/2 - height) < -10 THEN 2
  WHEN (width/2 - height) > -10 AND (width/2 - height) < 10 THEN 3
  WHEN (width/2 - height) > 10 THEN 1
END)
WHERE id >10;

9.锁机制

# 乐观锁：一般通过在数据库表增加数字类型 version 字段，当读取数据时，将 version 字段值一同读出，数据每更新一次，对此 version+1。
# 当提交更新时，将当前版本与第一次取出来的 version 值进行比对，如果表中当前版本值与第一次取出来的 version 值相等，则予以更新，否则认为是过期数据，返回更新失败。
select `id`,`version` from `user` where `id`=1; # 假设查询结果 version=5
update table `user` set `name`='luis', `version`=version+1 and where id=1 and version=5; # 乐观锁

# 共享锁：又称读锁（lock in share mode），允许其他事务加共享锁读取，但是不允许其他事务加入排他锁或修改操作。例如：select，上锁之后，另一个线程只可以读，不可以修改。
# lock in share mode 比较危险，如果其他事务同时加了这种锁，导致当前事务无法进行更新，进而阻塞出现死锁。
update table `user` set `name`='luis' where `id`=1 lock in share mode; # 共享锁

# 排他锁：又称写锁（for update），不允许其他事务加共享锁或排他锁，更加不允许其他事务修改加锁的行。例如：update，insert，delete，上锁之后，另一个线程不可以读和修改。
# select for update 不会产生的死锁，因为一个事务加了这种锁，其他事务除了正常（不加锁）读取操作，其他操作如更改操作、加共享锁读或排他锁读都不可以。
update table `user` set `name`='luis' where `id`=1 for update; # 排他锁

# 悲观锁（共享锁、排他锁）仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。
# 若一个线程for update执行锁住某行数据，其他线程读取的时候，sql里没有for update，则可以正常读取。

// 添加锁
RedisUtil::lock('order_add', 10);

Db::beginTransaction();
try {
    // for update 放在 begin和commit之间
    $goods = Db::table('goods')->where('id', 5)->select('id')->lockForUpdate()->first(); 

    // 其他业务代码

    Db::commit();
} catch (Throwable $e) {
    Db::rollback();
    $this->rollbackError($e);
} finally {
    // 释放锁
    RedisUtil::release('order_add');
}

# 在InnoDB存储引擎中，默认的行锁等待超时时间是由系统变量innodb_lock_wait_timeout控制的。该变量的默认值50秒。这意味着如果一个事务在等待锁超过50秒后仍未获取到锁，将抛出一个锁等待超时的错误。
# 获取当前的innodb_lock_wait_timeout值
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
# 修改innodb_lock_wait_timeout值为60秒
SET GLOBAL innodb_lock_wait_timeout = 60;

10.批量修改表名

USE `information_schema`;

SET @old_prefix = 'dyzj_jq_';
SET @new_prefix = 'dymz_';
SET @table_schema = 'stkj23_dymz';

SELECT CONCAT('ALTER TABLE ', table_name, ' RENAME TO ', @new_prefix, SUBSTRING(table_name FROM LENGTH(@old_prefix)+1), ';') AS rename_sql
FROM information_schema.tables 
WHERE table_schema = @table_schema AND table_name LIKE CONCAT(@old_prefix,'%');

# 复制命令在窗口执行
USE `stkj23_dymz`;
ALTER TABLE dyzj_jq_admin RENAME TO dymz_admin; 
ALTER TABLE dyzj_jq_admin_item RENAME TO dymz_admin_item;                          
......

11.命令行导入数据

mysql -uroot -p123456
show VARIABLES like 'character%'; # 将所有字符集改为 utf8
set character_set_results=utf8;

source ./xxx.sql;

二、常见配置

1.查看mysql配置

SHOW GLOBAL VARIABLES LIKE '%timeout';
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'com_kill';
SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet';

2.分组查询 group

# 对于 group by 查询语句，group by 前可以用 where，其后只能使用 having。
# 如果是 mysql5.7 版本，对于 GROUP BY 聚合操作，SELECT 中的列必须在 group by 后面出现或者这个字段出现在聚合函数里面，否则就会报错。
# 分组查询 group，去除 ONLY_FULL_GROUP_BY

SELECT @@GLOBAL.sql_mode;
SET GLOBAL sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

3.datetime 类型字段

# 数据库时间字段为 datetime 类型，默认值：1000-01-01 00:00:00
# datetime 默认值，去除 NO_ZERO_IN_DATE,NO_ZERO_DATE
# 分组查询 group，去除 ONLY_FULL_GROUP_BY

SELECT @@GLOBAL.sql_mode;
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';