佛山南海网站开发,酒水销售网站,头条站长平台,如何做一个网页卖东西目录
1.整型
1.1参数signed和unsigned
1.2参数zerofill
1.3参数auto_increment
2.数字类型
2.1floor()向下取整
2.2随机函数rand()
2.3重复函数repeat()
3.字符串类型
3.1length()查看字节长度#xff0c;char_length()查看字符长度
3.2字符集
3.2.1查看默认字符…目录
1.整型
1.1参数signed和unsigned
1.2参数zerofill
1.3参数auto_increment
2.数字类型
2.1floor()向下取整
2.2随机函数rand()
2.3重复函数repeat()
3.字符串类型
3.1length()查看字节长度char_length()查看字符长度
3.2字符集
3.2.1查看默认字符集
3.2.2查看已经创建的数据库的字符集
3.2.3修改已经创建的数据库的字符集
3.2.4修改mysql默认字符集
3.2.5两条转换表字符集的语句
3.2.6查看支持的字符集
3.3hex()函数
3.4cast()函数
3.5排序规则collation
3.6对于密码的加密md5()函数
3.7upper()和lower()函数
3.8concat()和concat_ws()函数
3.9lpad()和rpad()函数
4.enumset类型
5.时间类型
5.1日期函数
编辑 5.1.1now()函数
5.1.2now()和sysdate()的区别
5.1.3date_add()和date_sub()函数
5.1.4date_format()函数
6.json类型
6.1json类型相关函数
6.1.1json_extract()函数 6.1.2json_remove()函数 mysql的数据类型有整型、数字类型、字符串类型、日期类型、json类型等主要类型
1.整型 1.1参数signed和unsigned
可以创建有符号(signed)和无符号(unsigned)的整型类型
(rootlocalhost) [(none)] use python;
Database changed
(rootlocalhost) [python] create table z (a int unsigned,b tinyint signed);
Query OK, 0 rows affected (0.58 sec)(rootlocalhost) [python] insert into z values(1,1);
Query OK, 1 row affected (0.13 sec)(rootlocalhost) [python] insert into z values(-1,1);
ERROR 1264 (22003): Out of range value for column a at row 1
(rootlocalhost) [python] insert into z values(1,-1);
Query OK, 1 row affected (0.05 sec)(rootlocalhost) [python] select * from z;
------------
| a | b |
------------
| 1 | 1 |
| 1 | -1 |
------------
2 rows in set (0.00 sec)
1.2参数zerofill
查看创建表ddl有int(10)和tinyint(4)其中10和4代表显示的数值总长度长度不够用0填充
(rootlocalhost) [python] show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a int(10) unsigned DEFAULT NULL,b tinyint(4) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.04 sec)(rootlocalhost) [python] alter table z change column a a int unsigned zerofill;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0(rootlocalhost) [python] select * from z;
------------------
| a | b |
------------------
| 0000000001 | 1 |
| 0000000001 | -1 |
------------------
2 rows in set (0.00 sec)(rootlocalhost) [python] show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a int(10) unsigned zerofill DEFAULT NULL,b tinyint(4) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)可以修改显示的总长度
(rootlocalhost) [python] alter table z change column a a int(4) unsigned zerofill;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0(rootlocalhost) [python] select * from z;
------------
| a | b |
------------
| 0001 | 1 |
| 0001 | -1 |
------------
2 rows in set (0.00 sec)(rootlocalhost) [python] show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a int(4) unsigned zerofill DEFAULT NULL,b tinyint(4) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)
可以正常插入比显示长度更大的值
(rootlocalhost) [python] insert into z values(20000,2);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] select * from z;
-------------
| a | b |
-------------
| 0001 | 1 |
| 0001 | -1 |
| 20000 | 2 |
-------------
3 rows in set (0.00 sec)
1.3参数auto_increment
设置该参数主键自动递增select last_insert_id()查询上一次自增id值是多少表数据量大主键自增字段建议用bigint类型
(rootlocalhost) [python] truncate table z;
Query OK, 0 rows affected (0.13 sec)(rootlocalhost) [python] alter table z change column a a int auto_increment primary key;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0(rootlocalhost) [python] show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a int(11) NOT NULL AUTO_INCREMENT,b tinyint(4) DEFAULT NULL,PRIMARY KEY (a)
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)(rootlocalhost) [python] insert into z values(null,10);
Query OK, 1 row affected (0.01 sec)(rootlocalhost) [python] insert into z values(null,20);
Query OK, 1 row affected (0.01 sec)(rootlocalhost) [python] insert into z(b) values(30);
Query OK, 1 row affected (0.05 sec)(rootlocalhost) [python] insert into z values(40);
ERROR 1136 (21S01): Column count doesnt match value count at row 1
(rootlocalhost) [python] select * from z;
---------
| a | b |
---------
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
---------
3 rows in set (0.00 sec)(rootlocalhost) [python] select last_insert_id();
------------------
| last_insert_id() |
------------------
| 3 |
------------------
1 row in set (0.06 sec) 自增字段值在不重启数据库的情况下不会回溯只会越来越大
(rootlocalhost) [python] delete from z where b30;
Query OK, 1 row affected (0.06 sec)(rootlocalhost) [python] insert into z values(null,30);
Query OK, 1 row affected (0.04 sec)(rootlocalhost) [python] select * from z;
---------
| a | b |
---------
| 1 | 10 |
| 2 | 20 |
| 4 | 30 |
---------
3 rows in set (0.00 sec)
自增字段在删除select last_insert_id()的值后重启数据库会回溯查看ddlAUTO_INCREMENT7
(rootlocalhost) [python] select * from z;
---------
| a | b |
---------
| 1 | 10 |
| 2 | 20 |
| 5 | 40 |
| 6 | 30 |
---------
4 rows in set (0.00 sec)(rootlocalhost) [python] show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a int(11) NOT NULL AUTO_INCREMENT,b tinyint(4) DEFAULT NULL,PRIMARY KEY (a)
) ENGINEInnoDB AUTO_INCREMENT7 DEFAULT CHARSETlatin1
1 row in set (0.00 sec)(rootlocalhost) [python] delete from z where a6;
Query OK, 1 row affected (0.01 sec)(rootlocalhost) [python] delete from z where a5;
Query OK, 1 row affected (0.01 sec)(rootlocalhost) [python] select * from z;
---------
| a | b |
---------
| 1 | 10 |
| 2 | 20 |
---------
2 rows in set (0.00 sec)删除a6、a5后重启数据库查看ddlAUTO_INCREMENT3此时就回溯了再次插入数据a3、a4就回来了线上删除的值就又出现了会乱掉这个问题在mysql8.0得到解决
(rootlocalhost) [python] show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a int(11) NOT NULL AUTO_INCREMENT,b tinyint(4) DEFAULT NULL,PRIMARY KEY (a)
) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETlatin1
1 row in set (0.00 sec)(rootlocalhost) [python] select * from z;
---------
| a | b |
---------
| 1 | 10 |
| 2 | 20 |
---------
2 rows in set (0.00 sec)(rootlocalhost) [python] insert into z values(null,30);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] insert into z values(null,40);
Query OK, 1 row affected (0.01 sec)(rootlocalhost) [python] select * from z;
---------
| a | b |
---------
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
---------
4 rows in set (0.00 sec)(rootlocalhost) [python]
mysql8.0解决自增字段回溯问题AUTO_INCREMENT值持久化
[rootlocalhost ~]# mysql -S/tmp/mysql.sock80 -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.35 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.(rootlocalhost) [(none)] create database zs;
Query OK, 1 row affected (0.42 sec)(rootlocalhost) [(none)] use zs
Database changed
(rootlocalhost) [zs] create table z (a bigint auto_increment primary key,b tinyint);
Query OK, 0 rows affected (1.36 sec)(rootlocalhost) [zs] show create table z\G;
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a bigint NOT NULL AUTO_INCREMENT,b tinyint DEFAULT NULL,PRIMARY KEY (a)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.22 sec)ERROR:
No query specified(rootlocalhost) [zs] insert into z values(null,10);
Query OK, 1 row affected (0.08 sec)(rootlocalhost) [zs] insert into z values(null,20);
Query OK, 1 row affected (0.05 sec)(rootlocalhost) [zs] insert into z values(null,30);
Query OK, 1 row affected (0.05 sec)(rootlocalhost) [zs] insert into z values(null,40);
Query OK, 1 row affected (0.01 sec)(rootlocalhost) [zs] show create table z\G;
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a bigint NOT NULL AUTO_INCREMENT,b tinyint DEFAULT NULL,PRIMARY KEY (a)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)ERROR:
No query specified(rootlocalhost) [zs] select * from z;
---------
| a | b |
---------
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
---------
4 rows in set (0.01 sec)(rootlocalhost) [zs] delete from z where a4;
Query OK, 1 row affected (0.06 sec)(rootlocalhost) [zs] delete from z where a3;
Query OK, 1 row affected (0.05 sec)(rootlocalhost) [zs] select * from z;
---------
| a | b |
---------
| 1 | 10 |
| 2 | 20 |
---------
2 rows in set (0.00 sec)(rootlocalhost) [zs] show create table z\G;
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a bigint NOT NULL AUTO_INCREMENT,b tinyint DEFAULT NULL,PRIMARY KEY (a)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)ERROR:
No query specified(rootlocalhost) [zs] exit
Bye
[rootlocalhost ~]# mysqld_multi stop 80
[rootlocalhost ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld32 is not running
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld80 is not running
[rootlocalhost ~]# mysqld_multi start 80
[rootlocalhost ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld32 is not running
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld80 is running
[rootlocalhost ~]# mysql -S/tmp/mysql.sock80 -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.35 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.(rootlocalhost) [(none)] use zs;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
(rootlocalhost) [zs] show create table z\G;
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a bigint NOT NULL AUTO_INCREMENT,b tinyint DEFAULT NULL,PRIMARY KEY (a)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)ERROR:
No query specified(rootlocalhost) [zs] select * from z;
---------
| a | b |
---------
| 1 | 10 |
| 2 | 20 |
---------
2 rows in set (0.00 sec)(rootlocalhost) [zs] insert into z values(null,30);
Query OK, 1 row affected (0.01 sec)(rootlocalhost) [zs] insert into z values(null,40);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [zs] select * from z;
---------
| a | b |
---------
| 1 | 10 |
| 2 | 20 |
| 5 | 30 |
| 6 | 40 |
---------
4 rows in set (0.00 sec)(rootlocalhost) [zs]
int类型总结
推荐不要使用UNSIGNED范围本质上没有大的改变UNSIGNED可能会有溢出现象发生自增INT类型主键建议使用BIGINT 2.数字类型 一般建议用decimal
2.1floor()向下取整
(rootlocalhost) [(none)] select floor(1.5);
------------
| floor(1.5) |
------------
| 1 |
------------
1 row in set (0.00 sec)(rootlocalhost) [(none)] select floor(1.9);
------------
| floor(1.9) |
------------
| 1 |
------------
1 row in set (0.00 sec)(rootlocalhost) [(none)] select floor(-1.9);
-------------
| floor(-1.9) |
-------------
| -2 |
-------------
1 row in set (0.00 sec)
2.2随机函数rand()
(rootlocalhost) [(none)] select rand() ;
-------------------
| rand() |
-------------------
| 0.590530601880076 |
-------------------
1 row in set (0.00 sec)(rootlocalhost) [(none)] select rand() ;
--------------------
| rand() |
--------------------
| 0.1034116913596277 |
--------------------
1 row in set (0.00 sec)
1~100随机整数
(rootlocalhost) [(none)] select floor(1rand()*99);
--------------------
| floor(1rand()*99) |
--------------------
| 74 |
--------------------
1 row in set (0.00 sec)(rootlocalhost) [(none)] select floor(1rand()*99);
--------------------
| floor(1rand()*99) |
--------------------
| 42 |
--------------------
1 row in set (0.00 sec)(rootlocalhost) [(none)] select floor(1rand()*99);
--------------------
| floor(1rand()*99) |
--------------------
| 85 |
--------------------
1 row in set (0.00 sec)
2.3重复函数repeat()
(rootlocalhost) [(none)] select repeat(1,3);
-------------
| repeat(1,3) |
-------------
| 111 |
-------------
1 row in set (0.00 sec)(rootlocalhost) [(none)] select repeat(32,3);
--------------
| repeat(32,3) |
--------------
| 323232 |
--------------
1 row in set (0.00 sec)(rootlocalhost) [(none)] select repeat(b,5);
---------------
| repeat(b,5) |
---------------
| bbbbb |
---------------
1 row in set (0.00 sec)(rootlocalhost) [(none)] select repeat(ba,4);
----------------
| repeat(ba,4) |
----------------
| babababa |
----------------
1 row in set (0.00 sec)
字符串或数字变长
(rootlocalhost) [mysql] select repeat(a,floor(1rand()*99));
-----------------------------------------------------------------------------------------------------
| repeat(a,floor(1rand()*99)) |
-----------------------------------------------------------------------------------------------------
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
-----------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)(rootlocalhost) [mysql] select repeat(a,floor(1rand()*99));
--------------------------------------------
| repeat(a,floor(1rand()*99)) |
--------------------------------------------
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
--------------------------------------------
1 row in set (0.00 sec)(rootlocalhost) [mysql] select repeat(b1,floor(1rand()*20));
---------------------------------
| repeat(b1,floor(1rand()*20)) |
---------------------------------
| b1b1b1 |
---------------------------------
1 row in set (0.00 sec)(rootlocalhost) [mysql] select repeat(b1,floor(1rand()*20));
---------------------------------
| repeat(b1,floor(1rand()*20)) |
---------------------------------
| b1b1b1b1b1b1b1b1b1 |
---------------------------------
1 row in set (0.00 sec)
3.字符串类型 char(n)和varchar(n)中的n代表字符有字符集
(rootlocalhost) [python] drop table z;
Query OK, 0 rows affected (0.72 sec)(rootlocalhost) [python] create table z (a char(1),b varchar(3),c binary(1),d varbinary(3));
Query OK, 0 rows affected (0.02 sec)(rootlocalhost) [python] insert into z values(a,bbb,c,ddd);
Query OK, 1 row affected (0.01 sec)(rootlocalhost) [python] select * from z;
------------------------
| a | b | c | d |
------------------------
| a | bbb | c | ddd |
------------------------
1 row in set (0.00 sec)(rootlocalhost) [python] show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a char(1) DEFAULT NULL,b varchar(3) DEFAULT NULL,c binary(1) DEFAULT NULL,d varbinary(3) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)(rootlocalhost) [python] insert into z values(紫,bbc,x,ooo);
ERROR 1366 (HY000): Incorrect string value: \xE7\xB4\xAB for column a at row 1
(rootlocalhost) [python] alter table z convert to charset utf8mb4;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0(rootlocalhost) [python] show create table z\G
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a char(1) DEFAULT NULL,b varchar(3) DEFAULT NULL,c binary(1) DEFAULT NULL,d varbinary(3) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4
1 row in set (0.00 sec)(rootlocalhost) [python] insert into z values(紫,bbc,x,ooo);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] insert into z values(x,bbc,紫,ooo);
ERROR 1406 (22001): Data too long for column c at row 1
3.1length()查看字节长度char_length()查看字符长度
(rootlocalhost) [python] select length(紫),char_length(紫);
-----------------------------------
| length(紫) | char_length(紫) |
-----------------------------------
| 3 | 1 |
-----------------------------------
1 row in set (0.00 sec)
3.2字符集
mysql中的字符集最小是在列级别上 3.2.1查看默认字符集
(rootlocalhost) [python] show variables like %character%;
------------------------------------------------------------------------------------------
| Variable_name | Value |
------------------------------------------------------------------------------------------
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.44-linux-glibc2.12-x86_64/share/charsets/ |
------------------------------------------------------------------------------------------
3.2.2查看已经创建的数据库的字符集
(rootlocalhost) [zs] show create database python;
-----------------------------------------------------------------------------
| Database | Create Database |
-----------------------------------------------------------------------------
| python | CREATE DATABASE python /*!40100 DEFAULT CHARACTER SET latin1 */ |
-----------------------------------------------------------------------------
1 row in set (0.00 sec)
3.2.3修改已经创建的数据库的字符集
(rootlocalhost) [python] alter database python charset utf8mb4;
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] show create database python;
------------------------------------------------------------------------------
| Database | Create Database |
------------------------------------------------------------------------------
| python | CREATE DATABASE python /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
------------------------------------------------------------------------------
1 row in set (0.00 sec)
3.2.4修改mysql默认字符集
在my.cnf中加入参数
[mysqld]
character_set_serverutf8mb4
3.2.5两条转换表字符集的语句
alter table t charset utf8mb4;与alter table t convert to charset(或者character set) utf8mb4;的不同前者对已经存在的列不进行字符集转换只对新增列转换后者是对已经存在的列和新增列都转换旧的字符集是新的字符集的子集在生产上要注意转换字符集是要锁表的要小心
(rootlocalhost) [python] drop table t;
Query OK, 0 rows affected (0.01 sec)(rootlocalhost) [python] create table t(a varchar(10),b char(2)) charset gbk;
Query OK, 0 rows affected (0.04 sec)(rootlocalhost) [python] show create table t\G;
*************************** 1. row ***************************Table: t
Create Table: CREATE TABLE t (a varchar(10) DEFAULT NULL,b char(2) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETgbk
1 row in set (0.00 sec)ERROR:
No query specified(rootlocalhost) [python] alter table t charset utf8mb4;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0(rootlocalhost) [python] show create table t\G;
*************************** 1. row ***************************Table: t
Create Table: CREATE TABLE t (a varchar(10) CHARACTER SET gbk DEFAULT NULL,b char(2) CHARACTER SET gbk DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4
1 row in set (0.00 sec)ERROR:
No query specified(rootlocalhost) [python] alter table t add column c varchar(11);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0(rootlocalhost) [python] show create table t\G;
*************************** 1. row ***************************Table: t
Create Table: CREATE TABLE t (a varchar(10) CHARACTER SET gbk DEFAULT NULL,b char(2) CHARACTER SET gbk DEFAULT NULL,c varchar(11) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4
1 row in set (0.00 sec)ERROR:
No query specified(rootlocalhost) [python] alter table t convert to charset utf8mb4;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0(rootlocalhost) [python] show create table t\G;
*************************** 1. row ***************************Table: t
Create Table: CREATE TABLE t (a varchar(10) DEFAULT NULL,b char(2) DEFAULT NULL,c varchar(11) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4
1 row in set (0.00 sec)ERROR:
No query specified3.2.6查看支持的字符集
show charsetMaxlen表示最大的一个字符占用多少个字节
(rootlocalhost) [python] show charset;
------------------------------------------------------------------------
| Charset | Description | Default collation | Maxlen |
------------------------------------------------------------------------
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
------------------------------------------------------------------------
41 rows in set (0.00 sec)
3.3hex()函数
查看字符的16进制
(rootlocalhost) [python] select hex(a);
----------
| hex(a) |
----------
| 61 |
----------
1 row in set (0.00 sec)(rootlocalhost) [python] select hex(ab);
-----------
| hex(ab) |
-----------
| 6162 |
-----------
1 row in set (0.00 sec)(rootlocalhost) [python] select hex(abc);
------------
| hex(abc) |
------------
| 616263 |
------------
1 row in set (0.00 sec)(rootlocalhost) [python] select hex(紫);
------------
| hex(紫) |
------------
| E7B4AB |
------------
1 row in set (0.00 sec)(rootlocalhost) [python] select 0xE7B4AB;
----------
| 0xE7B4AB |
----------
| 紫 |
----------
1 row in set (0.00 sec)(rootlocalhost) [python] insert into t values(aaa,0xE7B4AB,0xE7B4AB);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] select * from t;
------------------
| a | b | c |
------------------
| aaa | 紫 | 紫 |
------------------
1 row in set (0.00 sec)
3.4cast()函数
CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式它包括用AS关键字分隔的源值和目标数据类型。
语法
CAST(value AS datatype)
datatype参数取值
值描述DATE将value转换成YYYY-MM-DD格式DATETIME将value转换成YYYY-MM-DD HHMMSS格式TIME将value转换成HHMMSS格式CHAR将value转换成CHAR(固定长度的字符串)格式SIGNED将value转换成INT(有符号的整数)格式UNSIGNED将value转换成INT(无符号的整数)格式DECIMAL将value转换成FLOAT(浮点数)格式 BINARY将value转换成二进制格式
(rootlocalhost) [python] SELECT CAST(13.6 AS decimal(9,2));
------------------------------
| CAST(13.6 AS decimal(9,2)) |
------------------------------
| 13.60 |
------------------------------
1 row in set (0.00 sec)(rootlocalhost) [python] select cast(123 as char(1));
----------------------
| cast(123 as char(1)) |
----------------------
| 1 |
----------------------
1 row in set, 1 warning (0.00 sec)(rootlocalhost) [python] select cast(123 as char(10));
-----------------------
| cast(123 as char(10)) |
-----------------------
| 123 |
-----------------------
1 row in set (0.00 sec)(rootlocalhost) [python] select cast(a as char(10));
-----------------------
| cast(a as char(10)) |
-----------------------
| a |
-----------------------
1 row in set (0.01 sec)(rootlocalhost) [python] select cast(a as char(10) charset gbk);
-----------------------------------
| cast(a as char(10) charset gbk) |
-----------------------------------
| a |
-----------------------------------
1 row in set (0.00 sec)(rootlocalhost) [python] select hex(cast(a as char(10) charset gbk));
----------------------------------------
| hex(cast(a as char(10) charset gbk)) |
----------------------------------------
| 61 |
----------------------------------------
1 row in set (0.00 sec)(rootlocalhost) [python] select hex(a);
----------
| hex(a) |
----------
| 61 |
----------
1 row in set (0.00 sec)(rootlocalhost) [python] select hex(cast(紫 as char(10) charset gbk));
------------------------------------------
| hex(cast(紫 as char(10) charset gbk)) |
------------------------------------------
| D7CF |
------------------------------------------
1 row in set (0.00 sec)(rootlocalhost) [python] select hex(紫);
------------
| hex(紫) |
------------
| E7B4AB |
------------
1 row in set (0.00 sec)(rootlocalhost) [python] select 0xD7CF;
--------
| 0xD7CF |
--------
| ؏ |
--------
1 row in set (0.00 sec)(rootlocalhost) [python] show create table z\G;
*************************** 1. row ***************************Table: z
Create Table: CREATE TABLE z (a char(1) DEFAULT NULL,b varchar(3) DEFAULT NULL,c binary(1) DEFAULT NULL,d varbinary(3) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4
1 row in set (0.00 sec)ERROR:
No query specified(rootlocalhost) [python] insert into z values(a,0xD7CF,c,0xD7CF);
ERROR 1366 (HY000): Incorrect string value: \xD7\xCF for column b at row 1
(rootlocalhost) [python] insert into z values(a,0xE7B4AB,c,0xD7CF);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] select * from z;
------------------------
| a | b | c | d |
------------------------
| a | bbb | c | ddd |
| 紫 | bbc | x | ooo |
| a | 紫 | c | ؏ |
------------------------
3 rows in set (0.00 sec)
从上面看出varbinary类型不会检测对应的字符是否存在只会去存16进制的这个值varchar类型会去检测16进制的值对应的字符是否存在
3.5排序规则collation (rootlocalhost) [(none)] select a a;
-----------
| a a |
-----------
| 1 |
-----------
1 row in set (0.00 sec)(rootlocalhost) [(none)] select a a ;
---------------
| a a |
---------------
| 1 |
---------------
1 row in set (0.00 sec)(rootlocalhost) [(none)] select a A;
-----------
| a A |
-----------
| 1 |
-----------
1 row in set (0.01 sec)(rootlocalhost) [(none)] select a A ;
---------------
| a A |
---------------
| 1 |
---------------
1 row in set (0.00 sec)(rootlocalhost) [python] drop table t;
Query OK, 0 rows affected (0.02 sec)(rootlocalhost) [python] create table t (a char(1),unique key(a));
Query OK, 0 rows affected (0.02 sec)(rootlocalhost) [python] insert into t values(a);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] insert into t values(A);
ERROR 1062 (23000): Duplicate entry A for key a
(rootlocalhost) [python] insert into t values(a );
ERROR 1062 (23000): Duplicate entry a for key a
(rootlocalhost) [python] insert into t values(A );
ERROR 1062 (23000): Duplicate entry A for key a
(rootlocalhost) [python] select * from t;
------
| a |
------
| a |
------
1 row in set (0.00 sec) show charset看到的有一列是Default collation其中utf8mb4字符集对应的排序规则是utf8mb4_general_cici(case insensitive)表示不区分大小写
3.6对于密码的加密md5()函数
(rootlocalhost) [python] drop table t;
Query OK, 0 rows affected (0.01 sec)(rootlocalhost) [python] create table t(password varchar(128));
Query OK, 0 rows affected (0.01 sec)(rootlocalhost) [python] insert into t values(aaa);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] insert into t values(BBB);
Query OK, 1 row affected (0.01 sec)(rootlocalhost) [python] insert into t values(md5(aaa));
Query OK, 1 row affected (0.01 sec)(rootlocalhost) [python] insert into t values(md5(BBB));
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] select * from t;
----------------------------------
| password |
----------------------------------
| aaa |
| BBB |
| 47bce5c74f589f4867dbd57e9ca9f808 |
| 2bb225f0ba9a58930757a868ed57d9a3 |
----------------------------------
4 rows in set (0.00 sec)(rootlocalhost) [python] insert into t values(md5(concat(aaa,zs)));
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] insert into t values(md5(concat(BBB,zs)));
Query OK, 1 row affected (0.01 sec)(rootlocalhost) [python] select * from t;
----------------------------------
| password |
----------------------------------
| aaa |
| BBB |
| 47bce5c74f589f4867dbd57e9ca9f808 |
| 2bb225f0ba9a58930757a868ed57d9a3 |
| ebf93b07e04f07d5efea104226ce36f5 |
| 5812c79db401f78da7fd2aaf03f9ca97 |
----------------------------------
6 rows in set (0.00 sec)
密码通过加密存储一般来说也不区分大小写
强制密码区分大小写
(rootlocalhost) [python] create table y (password varchar(128) collate utf8mb4_bin,unique key(password));
Query OK, 0 rows affected (0.01 sec)(rootlocalhost) [python] insert into y values(a);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] insert into y values(A);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] select * from y;
----------
| password |
----------
| A |
| a |
----------
2 rows in set (0.00 sec)
3.7upper()和lower()函数
upper()字符串转换成大写lower()字符串转换成小写
(rootlocalhost) [python] select upper(abC);
--------------
| upper(abC) |
--------------
| ABC |
--------------
1 row in set (0.01 sec)(rootlocalhost) [python] select lower(aBC);
--------------
| lower(aBC) |
--------------
| abc |
--------------
1 row in set (0.01 sec)
3.8concat()和concat_ws()函数
concat()直接把字符串拼接起来concat_ws()通过指定的符号把字符串拼接起来
(rootlocalhost) [python] select concat(a,b,ccc,123);
---------------------------
| concat(a,b,ccc,123) |
---------------------------
| abccc123 |
---------------------------
1 row in set (0.00 sec)(rootlocalhost) [python] select concat_ws(!,a,b,ccc,123);
----------------------------------
| concat_ws(!,a,b,ccc,123) |
----------------------------------
| a!b!ccc!123 |
----------------------------------
1 row in set (0.00 sec)
3.9lpad()和rpad()函数
lpad()左填充rpad()右填充
(rootlocalhost) [python] select lpad(aaa,8,!);
-------------------
| lpad(aaa,8,!) |
-------------------
| !!!!!aaa |
-------------------
1 row in set (0.00 sec)(rootlocalhost) [python] select rpad(aaa,8,!);
-------------------
| rpad(aaa,8,!) |
-------------------
| aaa!!!!! |
-------------------
1 row in set (0.00 sec) 4.enumset类型 枚举类型常用于性别等distinct的值不多的情况
(rootlocalhost) [python] drop table y;
Query OK, 0 rows affected (0.01 sec)(rootlocalhost) [python] create table y (sex enum(male,female));
Query OK, 0 rows affected (0.01 sec)(rootlocalhost) [python]
(rootlocalhost) [python] insert into y values(male);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] insert into y values(male11);
ERROR 1265 (01000): Data truncated for column sex at row 1
(rootlocalhost) [python] insert into y values(female);
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [python] insert into y values(zhs);
ERROR 1265 (01000): Data truncated for column sex at row 1
(rootlocalhost) [python] select * from y;
--------
| sex |
--------
| male |
| female |
--------
2 rows in set (0.01 sec)
5.时间类型 常用日期类型datetime和timestamp
5.1日期函数 5.1.1now()函数
unix_timestamp(now())是1970-01-01 00:00:00到现在的秒数
(rootlocalhost) [dbt3] select now();
---------------------
| now() |
---------------------
| 2024-03-21 13:21:05 |
---------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select unix_timestamp(now());
-----------------------
| unix_timestamp(now()) |
-----------------------
| 1710998483 |
-----------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select timestamp(now());
---------------------
| timestamp(now()) |
---------------------
| 2024-03-21 13:21:29 |
---------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] create table z (a datetime,b timestamp);
Query OK, 0 rows affected (0.01 sec)(rootlocalhost) [dbt3] insert into z values(now(),now());
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [dbt3] select * from z;
------------------------------------------
| a | b |
------------------------------------------
| 2024-03-21 13:26:50 | 2024-03-21 13:26:50 |
------------------------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] show variables like time_zone;
-----------------------
| Variable_name | Value |
-----------------------
| time_zone | SYSTEM |
-----------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] set time_zone 0:00;
Query OK, 0 rows affected (0.00 sec)(rootlocalhost) [dbt3] select * from z;
------------------------------------------
| a | b |
------------------------------------------
| 2024-03-21 13:26:50 | 2024-03-21 05:26:50 |
------------------------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select now(10);
ERROR 1426 (42000): Too-big precision 10 specified for now. Maximum is 6.
(rootlocalhost) [dbt3] select now(6);
----------------------------
| now(6) |
----------------------------
| 2024-03-21 05:29:22.432329 |
----------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select now(3);
-------------------------
| now(3) |
-------------------------
| 2024-03-21 05:29:36.677 |
-------------------------
1 row in set (0.00 sec)
5.1.2now()和sysdate()的区别
now()表示该条sql执行开始的时间sysdate()表示执行到该函数的时间
(rootlocalhost) [dbt3] select now(),sysdate(),sleep(4),now(),sysdate();
----------------------------------------------------------------------------------------------
| now() | sysdate() | sleep(4) | now() | sysdate() |
----------------------------------------------------------------------------------------------
| 2024-03-21 05:34:46 | 2024-03-21 05:34:46 | 0 | 2024-03-21 05:34:46 | 2024-03-21 05:34:50 |
----------------------------------------------------------------------------------------------
1 row in set (4.00 sec)(rootlocalhost) [dbt3] select now(6),sysdate(6);
--------------------------------------------------------
| now(6) | sysdate(6) |
--------------------------------------------------------
| 2024-03-21 05:35:58.930067 | 2024-03-21 05:35:58.930234 |
--------------------------------------------------------
1 row in set (0.00 sec)
5.1.3date_add()和date_sub()函数
时间加减函数
(rootlocalhost) [dbt3] set time_zone8:00;
Query OK, 0 rows affected (0.00 sec)(rootlocalhost) [dbt3] select now();
---------------------
| now() |
---------------------
| 2024-03-21 13:41:15 |
---------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select date_add(now(),interval 1 day);
--------------------------------
| date_add(now(),interval 1 day) |
--------------------------------
| 2024-03-22 13:42:18 |
--------------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select date_add(now(),interval -2 day);
---------------------------------
| date_add(now(),interval -2 day) |
---------------------------------
| 2024-03-19 13:42:33 |
---------------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select date_add(now(),interval -2 hour);
----------------------------------
| date_add(now(),interval -2 hour) |
----------------------------------
| 2024-03-21 11:43:06 |
----------------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select date_add(now(),interval -2 minute);
------------------------------------
| date_add(now(),interval -2 minute) |
------------------------------------
| 2024-03-21 13:41:25 |
------------------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select date_sub(now(),interval 2 day);
--------------------------------
| date_sub(now(),interval 2 day) |
--------------------------------
| 2024-03-19 14:45:05 |
--------------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select date_sub(now(),interval -2 day);
---------------------------------
| date_sub(now(),interval -2 day) |
---------------------------------
| 2024-03-23 14:45:10 |
---------------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select date_sub(now(),interval 4 hour);
---------------------------------
| date_sub(now(),interval 4 hour) |
---------------------------------
| 2024-03-21 10:45:42 |
---------------------------------
1 row in set (0.01 sec)
5.1.4date_format()函数
语法
DATE_FORMAT(date,format)
date 参数是合法的日期format 规定日期/时间的输出格式。 可用的格式如下
格式描述%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)%pAM 或 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 位
(rootlocalhost) [dbt3] select date_format(now(),%Y/%m/%s %H.%i.%S);
----------------------------------------
| date_format(now(),%Y/%m/%s %H.%i.%S) |
----------------------------------------
| 2024/03/25 13.49.25 |
----------------------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select date_format(now(),%a);
-------------------------
| date_format(now(),%a) |
-------------------------
| Thu |
-------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select date_format(now(),%b);
-------------------------
| date_format(now(),%b) |
-------------------------
| Mar |
-------------------------
1 row in set (0.00 sec)(rootlocalhost) [dbt3] select date_format(now(),%W);
-------------------------
| date_format(now(),%W) |
-------------------------
| Thursday |
-------------------------
1 row in set (0.00 sec)6.json类型 json类型跟python中的字典类似
(rootlocalhost) [dbt3] create table userjson(uid bigint auto_increment primary key,data json);
Query OK, 0 rows affected (0.01 sec)(rootlocalhost) [dbt3] show create table userjson\G
*************************** 1. row ***************************Table: userjson
Create Table: CREATE TABLE userjson (uid bigint(20) NOT NULL AUTO_INCREMENT,data json DEFAULT NULL,PRIMARY KEY (uid)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4
1 row in set (0.00 sec)(rootlocalhost) [dbt3] INSERT INTO userjson(uid,data)VALUES (NULL,{name:David,address: Shanghai});
Query OK, 1 row affected (0.04 sec)(rootlocalhost) [dbt3] INSERT INTO userjson(uid,data)VALUES (NULL,{name:Jim,passport:E02318883});
Query OK, 1 row affected (0.00 sec)(rootlocalhost) [dbt3] select * from userjson;
-----------------------------------------------
| uid | data |
-----------------------------------------------
| 1 | {name: David, address: Shanghai} |
| 2 | {name: Jim, passport: E02318883} |
-----------------------------------------------
2 rows in set (0.00 sec)
6.1json类型相关函数 6.1.1json_extract()函数
(rootlocalhost) [dbt3] select uid,json_extract(data,$.name) from userjson;
----------------------------------
| uid | json_extract(data,$.name) |
----------------------------------
| 1 | David |
| 2 | Jim |
----------------------------------
2 rows in set (0.00 sec)(rootlocalhost) [dbt3] select uid,json_unquote(json_extract(data,$.name)) from userjson;
------------------------------------------------
| uid | json_unquote(json_extract(data,$.name)) |
------------------------------------------------
| 1 | David |
| 2 | Jim |
------------------------------------------------
2 rows in set (0.00 sec)(rootlocalhost) [dbt3] select uid,data-$.name from userjson;
---------------------
| uid | data-$.name |
---------------------
| 1 | David |
| 2 | Jim |
---------------------
2 rows in set (0.00 sec)(rootlocalhost) [dbt3] select uid,data-$.name from userjson;
----------------------
| uid | data-$.name |
----------------------
| 1 | David |
| 2 | Jim |
----------------------
2 rows in set (0.00 sec)(rootlocalhost) [dbt3] select uid,data-$.passport from userjson;
--------------------------
| uid | data-$.passport |
--------------------------
| 1 | NULL |
| 2 | E02318883 |
--------------------------
2 rows in set (0.00 sec) 6.1.2json_remove()函数
(rootlocalhost) [dbt3] select * from userjson;
-----------------------------------------------
| uid | data |
-----------------------------------------------
| 1 | {name: David, address: Shanghai} |
| 2 | {name: Jim, passport: E02318883} |
-----------------------------------------------
2 rows in set (0.00 sec)(rootlocalhost) [dbt3] select uid,json_remove(data,$.name) from userjson;
---------------------------------
| uid | json_remove(data,$.name) |
---------------------------------
| 1 | {address: Shanghai} |
| 2 | {passport: E02318883} |
---------------------------------
2 rows in set (0.01 sec)(rootlocalhost) [dbt3] select uid,json_remove(data,$.passport) from userjson;
-----------------------------------------------
| uid | json_remove(data,$.passport) |
-----------------------------------------------
| 1 | {name: David, address: Shanghai} |
| 2 | {name: Jim} |
-----------------------------------------------
2 rows in set (0.00 sec)
