目录
③ str_to_date(str,format) 函数的用法
① substring_index(str,delim,count) 解析
5. CONVERT(expr USING transcoding_name)
一、值得系统学习的文章
MySQL视图、索引 ---mysql教程
数据库MySQL详解_mysql数据库_砖业洋__的博客-CSDN博客
【MySQL】数据库基础 ①_你才是臭弟弟的博客-CSDN博客
【MySQL】数据库基础 ②_你才是臭弟弟的博客-CSDN博客
【MySQL】数据库基础 ③_你才是臭弟弟的博客-CSDN博客
MySQL数据库常见面试题总结(超详细)_mysql面试题_Java大数据运动猿的博客-CSDN博客
110道 MySQL面试题及答案 (持续更新)_关于mysql 的面试题_普通网友的博客-CSDN博客
MySQL 常见面试题汇总_mysql场景面试题_石硕页的博客-CSDN博客
经典 55道 MySQL面试题及答案_Firstlucky77的博客-CSDN博客
二、mysql批量造数据
-- 创建一个存储过程,并开始插入数据(循环插入10W条,耗时一分钟多)
drop PROCEDURE if exists test_insert;
CREATE PROCEDURE test_insert()
BEGIN
declare i int;
set i = 1;
while i <= 100000
do
INSERT INTO `student_info`(id, stu_name, sex, birthday_date, phone_number, family_address, created_time)
VALUES (null, i, '男', str_to_date('2023/03/02', '%Y/%m/%d'), '15533365984', '上海市', sysdate());
set i = i + 1;
end while;
END;
call test_insert();
三、mysql常用数据类型
日期和时间类型
MySQL DTAETIME、TIMESTAMP、DATE、TIME、YEAR(日期和时间类型)
扩展:
整数型
MySQL INT、TINYINT、SMALLINT、MEDIUMINT、BIGINT(整数类型)
MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,其属性字段可以添加 AUTO_INCREMENT 自增约束条件。
可见,定义ID主键时,int类型足够了,最大取到了21亿。
小数类型
MySQL FLOAT、DOUBLE、DECIMAL(小数类型)
MySQL 中使用浮点数和定点数来表示小数。
- 浮点类型有两种,分别是单精度浮点数(FLOAT)和双精度浮点数(DOUBLE);定点类型只有一种,就是 DECIMAL。
- 浮点类型和定点类型都可以用
(M, D)
来表示,其中M
称为精度,表示总共的位数;D
称为标度,表示小数的位数。 - 浮点数类型的取值范围为 M(1~255)和 D(1~30,且不能大于 M-2),分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的,FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。
- FLOAT 和 DOUBLE 在不指定精度时,默认会按照实际的精度(由计算机硬件和操作系统决定),DECIMAL 如果不指定精度,默认为(10,0)。
- 提示:不论是定点还是浮点类型,如果用户指定的精度超出精度范围,则会四舍五入进行处理。
- 浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的范围;缺点是会引起精度问题。
- 在 MySQL 中,定点数以字符串形式存储,在对精度要求比较高的时候(如货币、科学数据),使用 DECIMAL 的类型比较好,另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点数时需要注意,并尽量避免做浮点数比较。
- decimal类型是不二选择。
字符串类型
MySQL CHAR、VARCHAR、TEXT、ENUM、SET(字符串类型)
字符串类型用来存储字符串数据,还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较,还可以进行正则表达式的匹配查找。
MySQL 中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。
二进制类型
MySQL BIT、BINARY、VARBINARY、BLOB(二进制类型)
mysql数据类型的选择
1. 时间日期类型的选择
- MySQL 对于不同种类的日期和时间都提供了数据类型,比如 YEAR 和 TIME。如果只需要记录年份,则使用 YEAR 类型即可;如果只记录时间,可以使用 TIME 类型。
- 如果同时需要记录日期和时间,则可以使用 TIMESTAMP 或者 DATETIME 类型。由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,因此存储较大的日期最好使用 DATETIME。
- TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下,当插入一条记录但并没有指定 TIMESTAMP 这个列值时,MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时,使用 TIMESTAMP 是方便的,另外 TIMESTAMP 在空间上比 DATETIME 更有效。
2. 字符串类型的选择
- 字符串类型没有像数字类型列那样的“取值范围",但它们都有长度的概念。如果需要存储的字符串短于 256 个字符,那么可以使用 CHAR、VARCHAR 或 TINYTEXT。如果需要存储更长一点的字符串,则可以选用 VARCHAR 或某种更长的 TEXT 类型。
- 如果某个字符串列用于表示某种固定集合的值,那么可以考虑使用数据类型 ENUM 或 SET。
3. 二进制类型的选择
BLOB 是二进制字符串,TEXT 是非二进制字符串,两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等,而 TEXT 只能存储纯文本文件。
四、mysql常用函数
1. 日期函数
① date_add() 和 addtime()
- 都表示对日期时间进行“加”操作。
② date_sub()用法 & subdate()用法
- 都表示对日期时间进行“减”操作;
- 这两个函数和①的两个函数可以相互转化,即第二个参数为负数正数时可以转化;
- 更多举例请参考dataGrip,鼠标挪上去就会展示示例及解释。
-- TODO date_add()用法 & addtime()用法
SELECT date_add('1997-12-31 23:59:59', INTERVAL 1 DAY); -- 1998-01-01 23:59:59
SELECT date_add('1997-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND); -- 1998-01-01 00:01:00
-- adddate(expr:date, days:int) 第二个参数默认是“天”
SELECT adddate('1998-01-02', 31); -- 1998-01-02
SELECT adddate('1998-01-02', -1); -- 1998-01-01
SELECT adddate('1998-01-02', interval 31 day); -- 1998-02-02
-- adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.
SELECT addtime('1997-12-31 23:59:59.999999', '1 1:1:1.000002'); -- 1998-01-02 01:01:01.000001
SELECT addtime('01:00:00.999999', '02:00:00.999998'); -- 03:00:01.999997
-- TODO date_sub()用法 & subdate()用法
SELECT date_sub('1998-01-02', INTERVAL 31 DAY); -- 1997-12-02
SELECT date_sub('1998-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND); -- 1997-12-30 22:58:59
SELECT subdate('1998-01-02', INTERVAL 31 DAY); -- 1997-12-02
-- The second form allows the use of an integer value for days. In such cases,
-- it is interpreted as the number of days to be subtracted from the date or datetime expression expr.
SELECT subdate('1998-01-02 12:00:00', 31); -- 1997-12-02 12:00:00
-- 减少10分钟
select subdate('1998-01-02 12:00:00', interval 10 hour_minute ); -- 1998-01-02 11:50:00
-- 减少10秒
select subdate('1998-01-02 12:00:00', interval 10 hour_second ); -- 1998-01-02 11:59:50
-- 减少10小时
select subdate('1998-01-02 12:00:00', interval 10 hour ); -- 1998-01-02 02:00:00
③ str_to_date(str,format) 函数的用法
str_to_date函数将str转化为日期型的数据,format表示转化后的格式。
format参数格式:
常用:
- %Y 年
- %m 月
- %d 日
- %H 小时
- %i 分
- %s 秒
大全:
- %a 缩写星期名
- %b 缩写月名
- %c 月,数值
- %D 带有英文前缀的月中的天
- %d 月的天,数值(00-31)
- %e 月的天,数值(0-31)
- %f 微秒
- %H 小时 (00-23)
- %h 小时 (01-12)
- %I 小时 (01-12)
- %i 分钟,数值(00-59)
- %j 年的天 (001-366)
- %k 小时 (0-23)
- %l 小时 (1-12)
- %M 月名
- %m 月,数值(00-12)
- %p AM 或 PM
- %r 时间,12-小时(hh:mm:ss AM 或 PM)
- %S 秒(00-59)
- %s 秒(00-59)
- %T 时间, 24-小时 (hh:mm:ss)
- %U 周 (00-53) 星期日是一周的第一天
- %u 周 (00-53) 星期一是一周的第一天
- %V 周 (01-53) 星期日是一周的第一天,与 %X 使用
- %v 周 (01-53) 星期一是一周的第一天,与 %x 使用
- %W 星期名
- %w 周的天 (0=星期日, 6=星期六)
- %X 年,其中的星期日是周的第一天,4 位,与 %V 使用
- %x 年,其中的星期一是周的第一天,4 位,与 %v 使用
- %Y 年,4 位
- %y 年,2 位
实例:
select str_to_date('2023-03-02', '%Y-%m-%d'); -- 2023-03-02 select str_to_date('2023/03/02', '%Y/%m/%d'); -- 2023-03-02
注意事项:
年月日的位置可以调动,但是一定要注意format格式的大小写!!!
2. 字符串截取
① substring_index(str,delim,count) 解析
- str:要处理的字符串
- delim:分隔符
- count:计数
SQL的substring_index()用法——MySQL字符串截取_小白修炼晋级中的博客-CSDN博客
SELECT substring_index('www.mysql.com', '.', 2); -- www.mysql
SELECT substring_index('www.mysql.com', '.', -2); -- mysql.com
select substring_index(substring_index('www.mysql.com', '.', 2), '.', -1); -- mysql
-- 截取日期中的年份
select substring_index(now(),'-', 1); -- 2023
-- 截取日期中的天数
select substring_index('1998-01-02','-', -1); -- 02
② SUBSTRING截取字符串函数解析
-- 两个参数时,第2个参数表示从第几位开始截取(负数表示从倒数第几位开始截取)
SELECT SUBSTRING('Quadratically',5); -- ratically
SELECT SUBSTRING('Sakila', -3); -- ila
-- 没有逗号,带from的,效果同上
SELECT SUBSTRING('Quadratically' FROM 5); -- ratically
SELECT SUBSTRING('Quadratically' FROM -5); -- cally
-- 三个参数时,第2个参数表示从第几位开始截取(负数表示从倒数第几位开始截取),第3个参数表示截取几位
SELECT SUBSTRING('Quadratically',5,6); -- ratica
SELECT SUBSTRING('Sakila', -5, 3); -- aki
SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -- ki
③ SUBSTR 截取字符串
- SUBSTR 与 SUBSTRING 含义用法完全一样
3. 拼接字符串
-
CONCAT()
-
CONCAT_WS()
SELECT CONCAT('My', 'S', 'QL'); -- MySQL
-- CONCAT() returns NULL if any argument is NULL.
SELECT CONCAT('My', NULL, 'QL'); -- NULL
SELECT CONCAT_WS(',','First name','Second name','Last Name'); -- First name,Second name,Last Name
SELECT CONCAT_WS('','First name','Second name','Last Name'); -- First nameSecond nameLast Name
-- CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.
SELECT CONCAT_WS(',','First name',NULL,'Last Name'); -- First name,Last Name
4. CAST()用法
- SQL的CAST()——转换数据类型_sql cast_小白修炼晋级中的博客-CSDN博客
- SQL中CAST()函数的用法_sql cast用法_三三两的博客-CSDN博客
- Mysql中Cast()函数的用法_Dongguo丶的博客-CSDN博客
SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin; SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);
5. CONVERT(expr USING transcoding_name)
- 作用:将值转换为指定的数据类型或字符集;
- 几乎同cast()函数
- [Mysql] CONVERT函数_mysql convert_山茶花开时。的博客-CSDN博客
- CONVERT()函数_convert函数_牧马人xxx的博客-CSDN博客
6. 大小写转化
-- lcase同lower,都是将字符串转为小写;ucase同upper,都是将字符串转为大写 select lcase('ABcd'),lower('ABcd'),ucase('ABcd'),upper('ABcd');
7. 官网函数大全
官网,以下两个版本不同,可搜索某个函数
MySQL :: MySQL 8.0 Reference Manual :: 12.1 Built-In Function and Operator Reference
MySQL :: MySQL 5.7 Reference Manual :: 12.1 Built-In Function and Operator Reference
五、Oracle函数大全
渠道一:
Oracle Tutorial -> Oracle/PLSQL Functions -> Functions - Category
渠道二:官网更全(见下面的contents标签)
Contents 绝不仅仅是函数哦!!!
渠道三:
六、SQL(这个一定要看哦)
上面这个标签链接中有minus,exist等的用法介绍举例。