vim /etc/my.cnf
在【mysqld】模块添加：skip-grant-tables
service mysqld restart # 重启配置，然后免密码登陆，use mysql，清空密码，更新配置重启，登陆，设置新密码：
ALTER USER 'root'@'%' IDENTIFIED BY 'root123';
1
2
3
4
5
方法二（第一次登陆）：
grep 'temporary password' /var/log/mysqld.log 找到临时密码
登陆
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123';
1
2
3
4
方式三（5.x版本）：
update user set Host="%" where User="root"; # 可以改Host
1
2
方式四（8.x版本）：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'corona@2020@wuhan';
grant all privileges on *.* to 'root'@'localhost' with grant option; 这个方法只能修改权限，不能改Host
FLUSH PRIVILEGES;
1
2
3
4
忘记密码：
方式一：
vi /etc/my.cnf 在[mysqld]的段中加上一句：skip-grant-tables 保存并且退出vi。
service mysqld restart
mysql -uroot
USE mysql; UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ;
update mysql.user set authentication_string=password('123456a') where user='root';
还原my.cnf配置
service mysqld restart
use mysql
select host,user,authentication_string,plugin from user;
update user set host = "%" where user = "root"; # 更新host的时候不会保留原来的权限
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;
1
2
3
4
5
6
方法二：
RENAME USER 'test'@'172.%' TO 'test'@'%'; 保留原来的权限
1
2
查看连接ip信息：
select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
1
2
创建用户：
示例：
CREATE USER 'czl'@'172.31.%' IDENTIFIED BY 'Czl1234.';
CREATE USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'Czl1234.';
grant all privileges on *.* to 'czl'@'172.31.%'; # 需要grant权限，查user表可看，注意name和domain必须一致
# 8.0之前版本可以通过这个命令创建新的用户+domain
1
2
3
4
5
如何匹配多个网段：
1.update mysql.user set Host=“172.%” where User=‘czl’; # domain是表的一个columns，只能update，不能grant更新
2.再创建一个不同domain相同name的用户：
mysql会根据domain和username来匹配用户，domain和username是联合主键，Duplicate entry '172.%-test' for key 'PRIMARY'（旧版本也这样）
mysql会优先匹配出domain匹配度高的那一条数据，如172.%比%优先，然后使用那条数据的密码和权限配置
旧版本也是根据username+domain来做联合主键，只是grant能否创建新账号的区别
1
2
3
4
旧版本创建方式：
CREATE USER 'grafana'@'%' IDENTIFIED WITH mysql_native_password BY 'Grafana1234.';（这个不变）
或grant all privileges on *.* to 'czl'@'172.31.%';
1
2
3
修改权限：
grant select on *.* to 'czl'@'172.31.%'; # 如果已有该权限，不变，如果没有就更新。范围变化也会更新（范围存放其他表）
# 需要注意username和domain要一致
1
2
3
删除用户：
方法一：
delete from user where user="czl";
FLUSH PRIVILEGES;
1
2
3
方法二：
Drop user 'test'@'%'; # 'test'等价于'test'@'%'
1
2
撤销权限：
revoke super on *.* from 'czl'@localhost; # from
1
2
设置只读：
show global variables like "%read_only%";
set global read_only=1; # 对拥有super权限的账号是不生效的
DATE YYYY-MM-DD
TIME HH-MM-SS
YEAR
DATETIME YYYY-MM-DD HH-MM-SS，Datetime(n)n表示毫秒的精度，固定8个字节。推荐，不存在时区转换问题
TIMESTAMP YYYYMMDD HHMMSS存储1971年到现在的毫秒数（7字节），最大的优点是带有时区属性。需要从毫秒数转换，存在性能问题
create database [if not exists] db_name [character set xxx]
CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;
1
2
3
2.查看数据库
show databases;查看所有数据库
show create database db_name; 查看数据库的创建方式
自带数据库：
information_schema: 信息数据库，记录其他数据库的信息
sys: dba运维需要，一些慢查询sql的记录
performance_schema: 性能
1
2
3
4
5
6
7
3.修改数据库
alter database db_name [character set xxx]
1
2
4.删除数据库
drop database [if exists] db_name;
1
2
5.使用数据库
切换数据库 use db_name; -- 注意：进入到某个数据库后没办法再退回之前状态，但可以通过use进行切换
查看当前使用的数据库 select database();
1
2
3
6.创建表
create table tab_name(
field1 type[完整性约束条件],
field2 type,
...
fieldn type
KEY `id` (`id`) # 圆括号内的id是字段名称，圆括号左侧外面的id是索引名称。
)[character set xxx][ENGINE=innodb\mysaim]; # innodb支持事务、行锁、表锁；mysaim支持表锁
索引约束：
primary key (非空且唯一) :能够唯一区分出当前记录的字段称为主键！
unique
not null
auto_increment 主键字段必须是数字类型。
外键约束：
foreign key */
其他方式：
create table aa(select * from bb) --把数据都复制过来，而外键那些不复制
复制表结构：
create table a like b
部分克隆：
CREATE TABLE A AS SELECT x,x,x,xx FROM B LIMIT 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
7.查看表信息：
desc tab_name 查看表结构
show columns from tab_name 查看表结构 --效果一样
show tables 查看当前数据库中的所有的表
show create table tab_name 查看当前数据库表建表语句
1
2
3
4
5
8.修改表结构：
(1)增加列(字段)
alter table tab_name add [column] 列名 类型［完整性约束条件］［first｜after 字段名］;
alter table users2
add addr varchar(20),
add age int first,
add birth varchar(20) after name;
(2)修改一列类型
alter table tab_name modify 列名 新类型 [完整性约束条件]［first｜after 字段名］;
(3)修改列名（可修改类型）
alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件]［first｜after 字段名］;
(4)删除一列
alter table tab_name drop [column] 列名;
(5)修改表名
rename table 表名 to 新表名;
(6)修该表所用的字符集
alter table student character set utf8;
(7)修改索引主键
先删除再添加
alter table featuredatadb.r_cash_loan_old_user_model_result drop primary key;
alter table featuredatadb.r_cash_loan_old_user_model_result add primary key (`uid`,`cash_loan_id`,`model_version`);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
9.删除表
drop table tab_name;
1
2
10.修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
insert into employee_new values (4,'alvin1','1993-04-20',3000),(5,'alvin2','1995-05-12',5000);
1
2
set插入:
insert ［into］ tab_name set 字段名=值
insert into employee_new set id=12,name="alvin3";
1
2
3
ON DUPLICATE KEY UPDATE的使用：
ON DUPLICATE KEY UPDATE c=VALUES(a)+b
1. 列名b表示原数据库的旧值，VALUES(b)表示引用被新插入的col_name的值
2. 可以使用if语法
on duplicate key update update_time=if(update_time>values(update_time),update_time,values(update_time))
3. 可以使用case when语法
on duplicate key update created_at=case when created_at=values(created_at) then created_at end
# 可以使用其他字段名称
# on duplicate key update level=values(level), update_time=case when level<>values(level) then values(update_time) else update_time end
# 这种写法，先更新了level，那么后面的语法查到的level与values(level)都是update后的同一个level
4. update level=values(level)如果新旧值不一样，才会去更新，才触发触发器
需要注意一下多个update值是否都更新了，如update_time
5. 需要避免的使用情况
a. 最好values括号内不要加新括号，否则如果values后面任意的一个()内再有)的话，会一直匹配到该处的)
b. 如要添加，确保后面的一个()没有子括号
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
UPDATE：
update tab_name set field1=value1,field2=value2,......[where 语句]
1
2
DELETE：
delete from tab_name [where ....]
1
2
SELECT：
查询表达式：
SELECT *|field1,filed2 ... FROM tab_name
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
OFFSET
UNION 去重组合 # UNION ALL 不管重复，上下并接
