代码语言：javascript复制♥ 文章声明 ♥ 该系列文章部分文字描述,参考于以下文献,化繁为简. 《MySQL5.7从入门到精通》 - 刘增杰
MariaDB [lyshark]> select Name,char_length(Name) from lyshark; -- 求字符串长度
------------ -------------------
| Name | char_length(Name) |
------------ -------------------
| apple | 5 |
| apricot | 7 |
| blackberry | 10 |
------------ -------------------
17 rows in set (0.00 sec)
MariaDB [lyshark]> select concat("{ID: ",Gid,", Name: ",Name,"}") from lyshark;
-----------------------------------------
| concat("{ID: ",Gid,", Name: ",Name,"}") |
-----------------------------------------
| {ID: 101, Name: apple} |
| {ID: 103, Name: apricot} |
| {ID: 101, Name: blackberry} |
| {ID: 104, Name: berry} |
-----------------------------------------
17 rows in set (0.00 sec)
-- 如果为空则输出null
MariaDB [lyshark]> select concat("{ID: ",Gid,", Name: ",ifnull(Name,"NULL"),"}") from lyshark;
-- 以，作为连接，将两个值串起来。
MariaDB [lyshark]> select concat_ws(',',"Name: ","lyshark");
-----------------------------------
| concat_ws(',',"Name: ","lyshark") |
-----------------------------------
| Name: ,lyshark |
-----------------------------------
1 row in set (0.00 sec)
-- 字符串替换，从第7个字符串开始替换，向后替换10个。
MariaDB [lyshark]> select insert('hello world',7,10,'lyshark');
--------------------------------------
| insert('hello world',7,10,'lyshark') |
--------------------------------------
| hello lyshark |
--------------------------------------
1 row in set (0.00 sec)
-- 返回str字符串中第一个出现substr字符串的位置
MariaDB [lyshark]> select instr('hello lyshark','lyshark');
----------------------------------
| instr('hello lyshark','lyshark') |
----------------------------------
| 7 |
----------------------------------
1 row in set (0.00 sec)
-- 字符串截断
MariaDB [lyshark]> select left('hello lyshark',3);
-------------------------
| left('hello lyshark',3) |
-------------------------
| hel |
-------------------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select right('hello lyshark',3);
--------------------------
| right('hello lyshark',3) |
--------------------------
| ark |
--------------------------
1 row in set (0.00 sec)
-- 返回str字符串的byte字节长度
MariaDB [lyshark]> select length('aaaaafasdfasd');
-------------------------
| length('aaaaafasdfasd') |
-------------------------
| 13 |
-------------------------
1 row in set (0.00 sec)
-- 返回str字符串中第一次出现substr字符串的位置，如果没有则返回null
MariaDB [lyshark]> select locate('lys','hello lyshark');
-------------------------------
| locate('lys','hello lyshark') |
-------------------------------
| 7 |
-------------------------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select locate('bar','foobarbar',5);
-----------------------------
| locate('bar','foobarbar',5) |
-----------------------------
| 7 |
-----------------------------
1 row in set (0.00 sec)
-- 字符串大小写转换
MariaDB [lyshark]> select lower('ABD');
--------------
| lower('ABD') |
--------------
| abd |
--------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select upper('abc');
--------------
| upper('abc') |
--------------
| ABC |
--------------
1 row in set (0.00 sec)
-- 去掉左右两边的空格
MariaDB [lyshark]> select ltrim(' abc dde eee ');
-----------------------------
| ltrim(' abc dde eee ') |
-----------------------------
| abc dde eee |
-----------------------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select rtrim(' abc dde eee ');
-----------------------------
| rtrim(' abc dde eee ') |
-----------------------------
| abc dde eee |
-----------------------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select trim(' abc dde eee ');
----------------------------
| trim(' abc dde eee ') |
----------------------------
| abc dde eee |
----------------------------
1 row in set (0.00 sec)-- 替换字符串中的字符
MariaDB [lyshark]> select replace('hello world world','world','lyshark');
------------------------------------------------
| replace('hello world world','world','lyshark') |
------------------------------------------------
| hello lyshark lyshark |
------------------------------------------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select substring('sublyshark',3,2);
-----------------------------
| substring('sublyshark',3,2) |
-----------------------------
| bl |
-----------------------------
1 row in set (0.00 sec)
-- 字符串截取，联系
MariaDB [lyshark]> insert into if_test(name,sex) values("zhang san",1);
MariaDB [lyshark]> insert into if_test(name,sex) values("li si",1);
MariaDB [lyshark]> insert into if_test(name,sex) values("wang wu",1);
MariaDB [lyshark]> select name,locate(' ',name) from if_test;
----------- ------------------
| name | locate(' ',name) |
----------- ------------------
| lyshark | 0 |
| admin | 0 |
| luxi | 0 |
| zhang san | 6 |
| li si | 3 |
| wang wu | 5 |
----------- ------------------
6 rows in set (0.00 sec)
MariaDB [lyshark]> select name,substring(name,locate(' ',name)) from if_test;
----------- ----------------------------------
| name | substring(name,locate(' ',name)) |
----------- ----------------------------------
| zhang san | san |
| li si | si |
| wang wu | wu |
----------- ----------------------------------
6 rows in set (0.00 sec)
MariaDB [lyshark]> select name,substring(name,1,locate(' ',name) 1) from if_test;
----------- ------------------------------------
| name | substring(name,1,locate(' ',name)) |
----------- ------------------------------------
| zhang san | zhang |
| li si | li |
| wang wu | wang |
----------- ------------------------------------
6 rows in set (0.00 sec)MariaDB [lyshark]> select * from lyshark order by rand(); -- 随机输出数据
MariaDB [lyshark]> select floor(rand()*10); -- 取出随机整数
MariaDB [lyshark]> select round(10.25);
-- 时区转换
MariaDB [lyshark]> select CONVERT_TZ('2009-12-11 12:00:00',' 00:00',' 10:00');
-----------------------------------------------------
| CONVERT_TZ('2009-12-11 12:00:00',' 00:00',' 10:00') |
-----------------------------------------------------
| 2009-12-11 22:00:00 |
-----------------------------------------------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select curdate();
------------
| curdate() |
------------
| 2020-07-02 |
------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select current_date();
----------------
| current_date() |
----------------
| 2020-07-02 |
----------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select now();
---------------------
| now() |
---------------------
| 2020-07-02 07:30:26 |
---------------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select sysdate();
---------------------
| sysdate() |
---------------------
| 2020-07-02 07:30:54 |
---------------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select current_time();
----------------
| current_time() |
----------------
| 07:31:12 |
----------------
1 row in set (0.00 sec)
-- 获取差异时间
MariaDB [lyshark]> select datediff('2020-12-25','2020-11-22');
-------------------------------------
| datediff('2020-12-25','2020-11-22') |
-------------------------------------
| 33 |
-------------------------------------
1 row in set (0.00 sec)
-- 时间增加减少
MariaDB [lyshark]> select date_add('2020-11-24',INTERVAL -100 SECOND); // 减少100秒
MariaDB [lyshark]> select date_add('2020-11-24',INTERVAL 100 SECOND); // 增加100秒
MariaDB [lyshark]> select date_add('2020-11-24',INTERVAL '12:22' MINUTE_SECOND);
-------------------------------------------------------
| date_add('2020-11-24',INTERVAL '12:22' MINUTE_SECOND) |
-------------------------------------------------------
| 2020-11-24 00:12:22 |
-------------------------------------------------------
1 row in set (0.00 sec)
SECOND/DAY/WEEK/HOUR/
-- 日期格式转换
MariaDB [lyshark]> select date_format(now(),'%Y:%m:%d');
-------------------------------
| date_format(now(),'%Y:%m:%d') |
-------------------------------
| 2020:07:02 |
-------------------------------
1 row in set (0.00 sec)
MariaDB [lyshark]> select dayofmonth('2020-06-24'); 返回date中当前月份是第几天
MariaDB [lyshark]> select dayname('2020-06-24'); 返回date中是星期几
MariaDB [lyshark]> select dayofweek('2020-06-24'); 返回date时间是星期几
MariaDB [lyshark]> select extract(YEAR from '2009-12-21'); 获取年份
MariaDB [lyshark]> select extract(YEAR_MONTH from '2009-12-21'); 获取年月
MariaDB [lyshark]> select last_day('2015-12-12'); 获取当月最后一天
MariaDB [lyshark]> select unix_timestamp(); unix时间戳
MariaDB [lyshark]> select from_unixtime(unix_timestamp()); 将时间戳转换为时间字符集
1.设置my.ini
代码语言：javascript复制[mysqld]
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
default-character-set=utf8
MariaDB [lyshark]> select * from information_schema.character_sets; 查询所支持的字符集
MariaDB [lyshark]> show character set like 'utf8%';
MariaDB [lyshark]> show variables like 'character_set%';
set global character_set_client=utf8;
set global character_set_connection=utf8;
set global character_set_database=utf8;
set global character_set_results=utf8;
set global character_set_server=utf8;
MariaDB [lyshark]> alter database lyshark default character set utf8 collate utf8_general_ci;
MariaDB [lyshark]> alter table lyshark.user convert to character set utf8 collate utf8_general_ci;表分区
表分区是将⼀个表的数据按照⼀定的规则⽔平划分为不同的逻辑块，并分别进⾏物理存储， 这个规则就叫做分区函数
代码语言：javascript复制DROP TABLE IF EXISTS `emept`;
create table emept
(
u_id int not null,
u_name varchar(30),
store_id int not null
)
PARTITION BY RANGE(store_id)
(PARTITION p0 values less than(6),PARTITION p1 values less than(11),
PARTITION p2 values less than(16),PARTITION p3 values less than(21) );
insert into emept values(1,'aaaa',1);
insert into emept values(2,'bbbb',2);
insert into emept values(3,'aaaa',3);
insert into emept values(3,'aaaa',8);
insert into emept values(3,'aaaa',19);
explain select * from emept where store_id=1;
MariaDB [lyshark]> create table abc(name varchar(20)) charset utf8mb4;
