Mysql 基础知识一

常用函数

数据库表的时间戳字段设计，建议使用 int64 类型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39


# 日期 【format格式说明：%m 月, 数字(01～12)；%c 月, 数字(1～12)】
date_format('2019-12-12 12:22:33', "%Y-%m-%d")  -- 日期格式化
FROM_UNIXTIME('时间戳','%Y-%m-%d %H:%i:%s')   -- 时间戳转换为日期格式
date_add(a.DUE_DAY, INTERVAL 90 day) -- 日期增加90天
UNIX_TIMESTAMP('2015-04-29')  -- 日期转换成时间戳
UNIX_TIMESTAMP()  -- 获取当前时间戳
DATE_SUB(CURDATE(), INTERVAL 15 DAY) -- 最近15天
DATE_SUB(CURDATE(), INTERVAL 1 YEAR) -- 最近1年

# 字符串合并
concat(s1, s2...sn)  --  字符串 s1,s2 等多个字符串合并为一个字符串
concat_ws(x, s1, s2...sn)  -- 同 CONCAT(s1,s2,...) 函数，但是每个字符串直接要加上 x，x 可以是分隔符
group_concat(column)  -- 将某列的所有值合并成一个字符串

# 截取字符串
left(str, length)，即：left(被截取字符串， 截取长度)
right(str, length)，即：right(被截取字符串， 截取长度)
substring(str, pos, length)，即：substring(被截取字符串，从第几位开始截取，截取长度)，length非必填
substring_index(str, delim, count)，即：substring_index(被截取字符串，关键字，关键字出现的次数)
    SELECT SUBSTRING_INDEX('www.yuanrengu.com', '.', 2); -- 截取第二个“.”之前的所有字符，如果关键字不存在，则返回整个字符串
    SELECT SUBSTRING_INDEX('www.yuanrengu.com', '.', -2); -- 截取倒数第二个“.”之后的所有字符

# 计算字段长度
SELECT LENGTH('www中国人民'); -- 长度15『length(): 一个汉字是算三个字符，一个数字或字母算一个字符』
SELECT CHAR_LENGTH('www中国人民'); -- 长度7 『char_length(): 不管汉字还是数字或者是字母都算是一个字符』

find_in_set(s1, s2)  -- 返回在字符串s2中与s1匹配的字符串的位置(等于 0 表示找不到),  SELECT FIND_IN_SET("c", "a,cb,c,d,e");
locate(s1, s) -- 从字符串 s 中获取 s1 的开始位置，位置初始值为1，找不到则返回0
regexp -- 正则匹配，eg：select * from history where data regexp '帐号的使用存在异常|操作太频繁'

format(number, decimal_places)  -- 浮点数格式化, 会出现千分位的浮点数，使用convert/cast不会出现千分位
convert(value, type)/cast(value as 类型)  -- 数据类型转换，可用来获取一个类型的值，并产生另一个类型的值。
eg: convert(1.2456, decimal(10,2)) 或者 cast(1.2456 as decimal(10,2))  -- 保留小数后两位

IFNULL(expr1,expr2) -- 如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。
if(sva=1,"男","女")

# 替换字段内容
UPDATE `goods_file` SET `file` = replace (`file`,'https://xxx.com','https://bbb.com');

常用命令

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44


-- 查询 sql_mode 『sql_mode 会影响 MySQL 支持的 SQL 语法以及它执行的数据验证检查』
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;
SET sql_mode = '';
SET GLOBAL sql_mode = '';

-- 时区操作
show variables like "%time_zone%"; -- 查看时区
> set global time_zone = '+8:00'; -- 修改mysql全局时区为北京时间，即我们所在的东8区
> set time_zone = '+8:00'; -- 修改当前会话时区
> flush privileges; -- 立即生效

SHOW BINLOG EVENTS LIMIT 0, 25; -- 查看 binlog 日志

ALTER TABLE tableName auto_increment=number; -- 设置表自增值

show full processlist; -- 显示所有用户正在运行的线程
show processlist; -- 显示当前用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。

explain/desc select *  from table; -- 查看查询语句性能，即查看执行计划
desc table_name;  -- 查看 table 等结构
show index from table_name;  -- 查看索引信息
create index on table(id(10));  -- 增加前缀索引

-- 查询表所有字段的名称
select COLUMN_NAME from information_schema.COLUMNS where table_name = 'your_table_name';

-- 查询一个表中所有字段的名字和注释
SELECT COLUMN_NAME, COLUMN_COMMENT FROM INFORMATION_SCHEMA.Columns WHERE table_name='provision' AND table_schema='test'

-- 授权root用户新ip可以访问：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.9.76' IDENTIFIED BY 'password' WITH GRANT OPTION;
flush privileges; -- 刷新激活变更操作

-- 导出数据库『不用进入mysql命令界面』
mysqldump -u 用户名 -p 数据库名 > 导出的文件名

-- 创建新用户并授权root用户所有权限且所有ip可以访问：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

-- 创建新用户并授权root用户新ip可以访问某个数据库：
GRANT ALL PRIVILEGES ON database_name.* TO 'root'@'192.168.9.76' IDENTIFIED BY 'password' WITH GRANT OPTION;

-- 创建只读用户：
GRANT SELECT ON *.* TO 'db_read'@'%' IDENTIFIED BY 'A1ta939a@Qn*iTls'

json类型的字段查询

查询JSON中的数据用 column->path的形式，其中对象类型path的表示方式 $.path，数组类型的表示方式 $[index];

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


CREATE TABLE muscleape
(
  id       TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category JSON,
  tags     JSON,
  PRIMARY KEY (id)
);

-- 直接插入字符串
INSERT INTO muscleape (category, tags) VALUES ('{"id": 1,"name": "muscleape", "object": {"a": 1, "b": 2}}','[1,2,3]');
-- 使用JSON函数
INSERT INTO muscleape (category, tags) VALUES (JSON_OBJECT("id",2,"name","muscleape_q", "object": {"a": 3, "b": 4}),JSON_ARRAY(1,3,5));
--

-- 查询 json 内容
SELECT
id,
category->'$.id', category->'$.name', category->'$.object.a', , category->'$.object.b'
tags->'$[0]', tags->'$[2]'
FROM muscleape;

-- 修改 JSON 中的某个字段，指定位置替换，JSON_REPLACE(json_doc, path, val[, path, val] ...)
update t1 set basic_info = JSON_REPLACE(basic_info, '$.age', 10) where id =1;
-- 修改数组类型
update t1 set basic_info=JSON_REPLACE(basic_info, "$.material_types", json_array())  where JSON_TYPE(basic_info->"$.material_types")="STRING"

-- 判断 json 中某个字段的数据类型
select JSON_TYPE('[1,2]'); -- ARRAY

-- JSON_VALID 是否有效json格式，判断val是否为有效的json格式，是为1，不是为0。如果参数为NUL，则返回NULL。
SELECT JSON_VALID('{"a": 1}'); -- 1

常用案例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51


-- 查询 mysql 版本号
select version();

-- 将某列的所有值合并成一个字符
select group_concat(test_column) from test;

-- 查询某张表的字段注释
select
COLUMN_NAME 字段名,
column_comment 字段说明,
column_type 字段类型,
column_key 约束 from information_schema.columns
where table_schema = '数据库名'
and table_name = '表名';

-- MySQL UPDATE JOIN 示例，可同时 update 多张表的字段 和 同时 update 多条记录
UPDATE employees
    INNER JOIN merits ON employees.performance = merits.performance
SET
    employees.salary = employees.salary + employees.salary * merits.percentage,
    merits.test = "666";

-- mysql 5.7  自身表排名 update join 示例
UPDATE rank_test
JOIN  (select id, rank from
	(SELECT id, @curRank := @curRank + 1 AS rank
    FROM rank_test,
        (SELECT @curRank := 0) r
        WHERE date="2021-03-30" AND material_type = 22
        ORDER BY  score_delta desc) temp2
        ) temp ON rank_test.id = temp.id
SET rank_test.rank = temp.rank;

-- mysql 8.0  自身表排名 update join 示例
update rank_test
join (SELECT id, rank() over (order by score_delta desc) as rank2
	FROM rank_test
	where date="2021-03-29" and material_type = 0) temp
on rank_test.id = temp.id
set rank_test.rank = temp.rank2;

-- 查询某个数据库内哪些表包含个字段
select tables.table_name, group_concat(column_name separator ","), data_type, table_rows
from information_schema.`TABLES`
join information_schema.columns on tables.table_schema=columns.table_schema and tables.table_name=columns.table_name
where
tables.table_schema="database_name" -- 数据库名
and data_type = 'int' -- 搜索字段数据类型为 int 型
and column_name like '%account_id' -- 搜索字段名
group by tables.table_name
order by table_rows

其他

数据库目录：
- db.opt //存放数据库配置
- table.frm //存放表结构
- table.MYD //存放表数据
- table.MYI //存放表索引
MySQL对InnoDB存储引擎的表进行行级锁定，对MyISAM存储引擎的表进行表级锁定。
浮点数计算存在误差问题，如7.22f-7.0f = 0.21999979; 尽量避免做浮点数比较
mysql终端连接方式：mysql -uroot -p’password' –socket=/var/lib/mysql/mysql.sock

linux 下 mysql 数据库密码修改

1
2
3
4
5


-> mysql -uroot -p
-> Enter password: 【输入密码】
mysql> use mysql;
mysql> update user set authentication_string=password("test") where user='root';
mysql> flush privileges;

union 对两个结果集进行并集操作，重复数据只显示一次

Union All，对两个结果集进行并集操作，重复数据全部显示
char和varchar使用说明: 根据字符串长度确定，凡是固定长度的字符串或者类似固定长度的字符串一律用char。比如身份证号码，手机号码，银行卡号，MD5，哈希值等这是字符串是固定长度的，毫无疑问用char，还有一类是基本固定长度但是略有出入的，比如中国人的姓名等，一般长度可能是2~5个汉字，这类信息也非常适合用char来存储，只要分配一些略大于通常长度即可。
group by
- GROUP BY X -> 意思是将所有具有相同X字段值的记录放到一个分组里。
- GROUP BY X, Y -> 意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。
having: having 子句在聚合后对组记录进行筛选, HAVING 子句总是包含聚集函数

eg: select payment_channel_id, count(1) as total from bi_payment_channel group by payment_channel_id having total>1
MySql的like语句中的通配符：百分号、下划线和escape

永远不要在MySQL中使用utf8，改用utf8mb4

MySQL的“utf8mb4”是真正的“UTF-8”。“utf8”只支持每个字符最多三个字节，而“utf8mb4”是每个字符最多四个字节。

mysql5.7 默认排序规则：utf8mb4_general_ci
mysql8.0 默认排序规则：utf8mb4_0900_ai_ci

utf8mb4_0900_ai_ci字段如何修改为大小写敏感

utf8mb4_0900_ai_ci, 中间的0900，它对应的是Unicode 9.0的规范，ai表示accent insensitivity，也就是“不区分音调”，而ci表示case insensitivity，也就是“不区分大小写”。

mysql8.0默认排序规则是utf8mb4_0900_ai_ci，对大小写不敏感，如果有些字段对大小写敏感的话，需要改下排序规则，如utf8mb4_0900_as_cs

字符集后缀

mysql charset 和 collation 有多个级别的设置：服务器级、数据库级、表级、列级和连接级。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


# 查看当前数据库各个级别的排序规则
SHOW VARIABLES LIKE 'collation_%';

# 数据库级：修改数据库的默认排序规则
alter database xc  character set utf8mb4 collate utf8mb4_0900_as_cs;

# 表级：修改表数据的字符集
alter table table_name character set utf8mb4 collate utf8mb4_0900_as_cs;

# 列级：修改表内所有字段的字符集
alter table table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

InnoDB 行锁的触发条件是 where 后面带的字段包含索引。若是没有索引的条件下，就会退化为表锁。然后获取所有行后，Mysql 再过滤符合条件的的行并释放锁。这样就降低了并发度，并且性能开销也会很大。

Mysql 性能

MySQL 单表数据不要超过500万行：是经验数值，还是黄金铁律？

该理论已过时，mysql 5.7 及以上版本已经可以支持单表几十亿的数据量了，性能还是不错的。

DDL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39


-- 查看已创建数据库的创建语句
show create database database_name;

-- 创建数据库
CREATE DATABASE `auto_audit` /*!40100 DEFAULT CHARACTER SET utf8 */

-- 创建新表
create table table_name (
    id int(11) NOT NULL AUTO_INCREMENT,
    title text,
    date datetime default NULL,
    timestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 重命名表
ALTER TABLE table_name_old RENAME table_name_new;

-- 删除表
DROP TABLE table_name;

-- 删除数据库
DROP DATABASE database_name;

-- 增加字段
ALTER TABLE student1 ADD teacher_name varchar(20) NOT NULL COMMENT 'test';

-- 编辑字段
ALTER TABLE student1 CHANGE name stu_name varchar(40) NULL;

-- 删除字段
ALTER TABLE student1 DROP teacher_name;

-- 从另一张表获取数据到新表
insert into tableA(fieldA,fieldB) (select fieldA,fieldB from tableB)
ON DUPLICATE KEY UPDATE `fieldA` = VALUES(`fieldA`)

-- 设置自动递增值
alter table tablename auto_increment=num

数据库设计三范式

第一范式1NF

定义：数据库表中的字段都是单一属性的，不可再分。

简单的说，每一个属性都是原子项，不可分割。

1NF是关系模式应具备的最起码的条件，如果数据库设计不能满足第一范式，就不称为关系型数据库。也就是说，只要是关系型数据库，就一定满足第一范式。
第二范式2NF

定义：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖，即符合第二范式。

2NF可以减少插入异常，删除异常和修改异常。
第三范式3NF

定义：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合3NF。

文章目录