Mysql创建问题

Mysql清除

• 让主键 归0

TRUNCATE TABLE 表名

1. 有外键约束时

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE 表名
SET FOREIGN_KEY_CHECKS=1;

• 只清空数据表，主键不归0

DELETE FROM 表名

Mysql排序

SELECT * FROM 表 ORDER BY 字段 DESC （DESC 降序 ASC 升序）
ORDER BY RAND() 随机排序

Mysql去重

group by [去重复字段表1，.....]

Mysql计算时间差

FROM `p_user_service` WHERE user_id=25 //返回天
SELECT TIMESTAMPDIFF(SECOND, `start_time`, NOW()) FROM `p_user_service` WHERE user_id=25 //返回秒差
SELECT TIMESTAMPDIFF(MINUTE, `start_time`, NOW()) FROM `p_user_service` WHERE user_id=25 //返回分钟差
SELECT TIMESTAMPDIFF(HOUR, `start_time`, NOW()) FROM `p_user_service` WHERE user_id=25 //返回时差
SELECT TIMESTAMPDIFF(DAY, `start_time`, NOW()) FROM `p_user_service` WHERE user_id=25 //返回天数差

Mysql手机号加*处理

CONCAT_WS(
'****',
SUBSTRING(m.member_phone, 1, 4),
SUBSTRING(m.member_phone, 9, 3)
) AS memberPhone,

MybatisPlus

MybatisPlus条件结构器 and中的 or用法

等价于：where 1=1 and（money_state=2 or money_state=3）

QueryWrapper<MemberMoneyRes> queryWrapper = new QueryWrapper<>(); 
        queryWrapper.and(wrapper -> wrapper.eq("money_state",2).or().eq("money_state",3));

Mysql编码不一致问题

mysql报错Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT)

sql语句各个表的默认校队字符集不一致导致的报警。 通过 show create table table_name语句查看各个表的默认校队字符集定位是那张表的原因；

ALTER TABLE `member` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

MySQL Error 2006

导入大批量数据的时候超出了默认允许最大的数据包所以就提示2006 - MySQL server has gone away

max_allowed_packet=300M
wait_timeout=200000
interactive_timeout = 200000

SQLyog 9.63报错号码2058

连接本地MySQL报错：错误号码2058（plugin caching_sha2_password could not be loaded）

【可能原因】MySQL密码加密方式不允许本地访问。

【处理办法】通过命令提示符cmd登录MySQL，然后输入命令。

• 1、打开cmd（win+R），输入：mysql -u root -p
• 2、进入mysql，执行下面语句：ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
• 执行成功后，即可通过SQLyog连接本地MySQL

常用的DDL命令

数据库操作

• 登录数据库：

mysql -uroot -proot

• 创建数据库：

create database test

• 查看所有数据库：

show databases

• 选择数据库并使用：

use test

• 查看所有数据表：

show tables

• 删除数据库：

drop database test

表操作

• 创建表

create table sys_user
(
    user_id      varchar(32)   not null comment '主键ID'
        primary key,
    dept_id      varchar(255)  null comment '部门ID',
    post_ids     varchar(255)  null comment '岗位编号数组',
    user_name    varchar(255)  null comment '用户账号',
    nick_name    varchar(255)  null comment '用户昵称',
    user_type    varchar(255)  null comment '用户类型',
    email        varchar(255)  null comment '邮箱',
    phone_number varchar(255)  null comment '手机号',
    created_time datetime      null comment '创建时间',
)
    comment '系统用户' collate = utf8mb4_unicode_ci;

• 查看表的定义

desc sys_user

• 查看表定义（详细）

show create table sys_user \G

• 删除表

drop table sys_user

• 修改字段

alter table sys_user modify user_name varchar(255)

• 添加表字段

alter table sys_user add column age int(3)

• 删除字段

alter table sys_user drop column age

• 字段改名

alter table sys_user change age age1 int(4)

• 修改表名

alter table sys_user rename sys_user1

数据操纵语句

• 插入记录

#指定字段
insert into sys_user (user_id,user_name,nick_name) values ('1','admin','管理员')
#不指定需要所有的字段都写
insert into sys_user values ('1','admin','管理员')
#批量
insert into sys_user values('1','admin','管理员'),('2','admin2','管理员2')

• 修改记录

update sys_user set nick_name='管理员',user_name='admin' where user_id='1'

• 删除记录

delete from sys_user where user_id='1'

• 查询

select * from sys_user

• 去重查询

select distinct deptno from sys_user

• 条件查询

select * from sys_user where user_id='1'

• 排序和限制

select * from sys_user order by user_id desc limit 2

• 分页查询

select * from sys_user order by user_id desc limit 0,10

• 链接查询

select * from sys_user e left join sys_role d on e.id=d.user_id

• 子查询

select * from sys_user where user_id in (select user_id from sys_role)

数据控制语句

• 授予操作权限(将test数据库中所有表的select和insert权限授予test用户)

grant select,insert on test.* to 'test'@'localhost' identified by '123'

• 查看账号权限

show grants for 'test'@'localhost'

• 收回操作权限

revoke insert on test.* from 'test'@'localhost'

• 授予所有数据库的所有权限

grant all privileges on *.* to 'test'@'localhost'

• 授予所有数据库的所有权限(包括grant)

grant all privileges on *.* to 'test'@'localhost' with grant option

• 授予SUPER PROCESS FILE权限（系统权限不能指定数据库）

grant all privileges on *.* to 'test'@'localhost' with grant option

• 只授予登录权限

grant usage on *.* to 'test'@'localhost'

帐号相关

• 删除账号

drop user 'test'@'localhost'

• 修改自己的密码

set password = password('123')

• 管理员修改他人密码

set password for 'test'@'localhost' = password('123')

字符集相关

• 查看字符集

show variables like 'character%'

• 创建数据库时指定字符集

create database test2 character set utf8

时区相关

• 查看当前时区（UTC为世界统一时间，中国为UTC+8）

show variables like "%time_zone%"

• 修改mysql全局时区为北京时间，即我们所在的东8区

set global time_zone = '+8:00';

• 修改当前会话时区

set time_zone = '+8:00'

• 立即生效

flush privileges