网站制作费计入哪个科目,wordpress中文文档,阜宁住房和城乡建设局网站,陶瓷网站建设中企动力#x1f320; 作者#xff1a;阿亮joy. #x1f386;专栏#xff1a;《零基础入门MySQL》 #x1f387; 座右铭#xff1a;每个优秀的人都有一段沉默的时光#xff0c;那段时光是付出了很多努力却得不到结果的日子#xff0c;我们把它叫做扎根 目录 #x1f449;函… 作者阿亮joy. 专栏《零基础入门MySQL》 座右铭每个优秀的人都有一段沉默的时光那段时光是付出了很多努力却得不到结果的日子我们把它叫做扎根 目录 函数日期函数字符串函数数学函数其它函数 总结 函数
日期函数 获得年月日
mysql select current_date();
----------------
| current_date() |
----------------
| 2023-05-16 |
----------------
1 row in set (0.00 sec)获得时分秒
mysql select current_time();
----------------
| current_time() |
----------------
| 19:13:44 |
----------------
1 row in set (0.00 sec获得时间戳
mysql select current_timestamp();
---------------------
| current_timestamp() |
---------------------
| 2023-05-16 19:14:25 |
---------------------
1 row in set (0.01 sec)mysql select now();
---------------------
| now() |
---------------------
| 2023-05-16 19:38:20 |
---------------------
1 row in set (0.00 sec)同时使用 current_date 和 current_time 就相当于 current_timestamp
mysql select current_date() 日期, current_time() 时间;
----------------------
| 日期 | 时间 |
----------------------
| 2023-05-16 | 19:16:12 |
----------------------
1 row in set (0.00 sec)在日期的基础上加日期
-- 加上十天
mysql select date_add(2023-5-16, interval 10 day);
----------------------------------------
| date_add(2023-5-16, interval 10 day) |
----------------------------------------
| 2023-05-26 |
----------------------------------------
1 row in set (0.00 sec)-- 加上一年
mysql select date_add(2023-5-16, interval 1 year);
----------------------------------------
| date_add(2023-5-16, interval 1 year) |
----------------------------------------
| 2024-05-16 |
----------------------------------------
1 row in set (0.00 sec)-- 加上一秒
mysql select date_add(2023-5-16, interval 1 second);
------------------------------------------
| date_add(2023-5-16, interval 1 second) |
------------------------------------------
| 2023-05-16 00:00:01 |
------------------------------------------
1 row in set (0.00 sec)在日期的基础上减去时间
-- 减去十天
mysql select date_sub(2023-5-16, interval 10 day);
----------------------------------------
| date_sub(2023-5-16, interval 10 day) |
----------------------------------------
| 2023-05-06 |
----------------------------------------
1 row in set (0.00 sec)-- 减去一年
mysql select date_sub(2023-5-16, interval 1 year);
----------------------------------------
| date_sub(2023-5-16, interval 1 year) |
----------------------------------------
| 2022-05-16 |
----------------------------------------
1 row in set (0.00 sec)-- 减去一秒
mysql select date_sub(2023-5-16, interval 1 second);
------------------------------------------
| date_sub(2023-5-16, interval 1 second) |
------------------------------------------
| 2023-05-15 23:59:59 |
------------------------------------------
1 row in set (0.00 sec)计算两个日期之间相差多少天
mysql select datediff(2023-5-26, 2023-05-06);
-------------------------------------
| datediff(2023-5-26, 2023-05-06) |
-------------------------------------
| 20 |
-------------------------------------
1 row in set (0.00 sec)mysql select datediff(now(), 1949-10-01);
-------------------------------
| datediff(now(), 1949-10-01) |
-------------------------------
| 26890 |
-------------------------------
1 row in set (0.00 sec)获取时间戳中的日期
mysql select date(now());
-------------
| date(now()) |
-------------
| 2023-05-16 |
-------------
1 row in set (0.00 sec)mysql select date(current_timestamp());
---------------------------
| date(current_timestamp()) |
---------------------------
| 2023-05-16 |
---------------------------
1 row in set (0.00 sec)创建生日表
mysql create table birthday(- id int primary key auto_increment,- d date not null,- t timestamp);mysql desc birthday;
-----------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------------------------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| d | date | NO | | NULL | |
| t | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
-----------------------------------------------------------------------------
3 rows in set (0.00 sec)mysql insert into birthday (d) values(1949-10-01);
Query OK, 1 row affected (0.00 sec)mysql insert into birthday (d) values(current_date());
Query OK, 1 row affected (0.01 sec)-- 插入数据时,timestamp类型会默认更新为当前的最新时间
mysql select * from birthday;
-------------------------------------
| id | d | t |
-------------------------------------
| 1 | 1949-10-01 | 2023-05-16 19:30:43 |
| 2 | 2023-05-16 | 2023-05-16 19:30:54 |
-------------------------------------
2 rows in set (0.00 sec)-- 将t字段的类型更改为datetime类型
mysql alter table birthday modify t datetime;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql desc birthday;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| d | date | NO | | NULL | |
| t | datetime | YES | | NULL | |
-----------------------------------------------------
3 rows in set (0.00 sec)mysql select * from birthday;
-------------------------------------
| id | d | t |
-------------------------------------
| 1 | 1949-10-01 | 2023-05-16 19:30:43 |
| 2 | 2023-05-16 | 2023-05-16 19:30:54 |
-------------------------------------
2 rows in set (0.00 sec)mysql insert into birthday (d, t) values(current_date(), current_timestamp());
Query OK, 1 row affected (0.01 sec)mysql select * from birthday;
-------------------------------------
| id | d | t |
-------------------------------------
| 1 | 1949-10-01 | 2023-05-16 19:30:43 |
| 2 | 2023-05-16 | 2023-05-16 19:30:54 |
| 3 | 2023-05-16 | 2023-05-16 19:37:02 |
-------------------------------------
3 rows in set (0.00 sec)创建留言表
mysql create table msg(- id int unsigned primary key auto_increment,- content varchar(100) not null,- sendtime datetime- );
Query OK, 0 rows affected (0.06 sec)mysql desc msg;
----------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------------------
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| content | varchar(100) | NO | | NULL | |
| sendtime | datetime | YES | | NULL | |
----------------------------------------------------------------
3 rows in set (0.00 sec)-- 插入数据
mysql insert into msg (content, sendtime) values(莫道桑榆晚为霞尚漫天, now());
Query OK, 1 row affected (0.01 sec)mysql insert into msg (content, sendtime) values(须知少年凌云志曾许人间第一流, now());
Query OK, 1 row affected (0.00 sec)-- 显示所有留言及留言的时间
mysql select * from msg;
------------------------------------------------------------------------
| id | content | sendtime |
------------------------------------------------------------------------
| 1 | 莫道桑榆晚为霞尚漫天 | 2023-05-16 19:58:10 |
| 2 | 须知少年凌云志曾许人间第一流 | 2023-05-16 19:59:30 |
------------------------------------------------------------------------
2 rows in set (0.00 sec)-- 查询在两分钟内发布的帖子
mysql insert into msg (content, sendtime) values(仰天长笑出门去我辈岂是蓬蒿人, now());
Query OK, 1 row affected (0.01 sec)mysql select * from msg where date_add(sendtime, interval 2 minute) now();
------------------------------------------------------------------------
| id | content | sendtime |
------------------------------------------------------------------------
| 3 | 仰天长笑出门去我辈岂是蓬蒿人 | 2023-05-16 20:03:37 |
------------------------------------------------------------------------
1 row in set (0.00 sec)-- 理解
------------------------------|-----------|-------------|------------------初始时间 now() 初始时间2min字符串函数 查看字符串的字符集
-- 此处字符串的字符集与配置文件的默认字符集有关
mysql select charset(hello world!);
-------------------------
| charset(hello world!) |
-------------------------
| utf8 |
-------------------------
1 row in set (0.00 sec)mysql show variables like character_set%;
------------------------------------------------------
| Variable_name | Value |
------------------------------------------------------
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
------------------------------------------------------
8 rows in set (0.00 sec)获取 emp 表的 ename 和 deptno 列的字符集
mysql select charset(ename) from emp;
----------------
| charset(ename) |
----------------
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
----------------
14 rows in set (0.00 sec)mysql select charset(deptno) from emp;
-----------------
| charset(deptno) |
-----------------
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
-----------------
14 rows in set (0.00 sec)数字能够直接使用二进制来表示不需要再进行编码所以数字的 charset 为 binary而字符串可能需要进行编码其 charset 与配置文件中的 charset 和建表时指定的 charset 有关。 当出现乱码问题时可能是因为存储数据的编码和取出数据的编码是不一样的。出现乱码问题时可能查看 charset 是否一致。 字符串拼接
mysql select concat(hello , world!);
----------------------------
| concat(hello , world!) |
----------------------------
| hello world! |
----------------------------
1 row in set (0.00 sec)-- 尽管是数字也能够直接拼接
mysql select concat(hello , 123, , 3.1415926);
-----------------------------------------
| concat(hello , 123, , 3.1415926) |
-----------------------------------------
| hello 123 3.1415926 |
-----------------------------------------
1 row in set (0.00 sec)显示 exam_result 表中的信息显示格式“XXX的语文XXX分数学XXX分英语XXX分
mysql select concat(name, 的语文, chinese, 分,数学, math, 分,英语, english, 分) 全班成绩 from exam_result;
-------------------------------------------------
| 全班成绩 |
-------------------------------------------------
| 唐三藏的语文67分,数学98分,英语56分 |
| 孙悟空的语文87分,数学78分,英语77分 |
| 猪悟能的语文88分,数学98分,英语90分 |
| 曹孟德的语文82分,数学84分,英语67分 |
| 刘玄德的语文55分,数学85分,英语45分 |
| 孙权的语文70分,数学73分,英语78分 |
| 宋公明的语文75分,数学65分,英语30分 |
-------------------------------------------------
7 rows in set (0.00 sec)求字符串的长度
mysql select length(hello world!);
------------------------
| length(hello world!) |
------------------------
| 12 |
------------------------
1 row in set (0.00 sec)mysql select length(3.1415926);
-------------------
| length(3.1415926) |
-------------------
| 9 |
-------------------
1 row in set (0.00 sec)-- 求出月薪超过两千的人的名字的长度
mysql select ename, sal, length(ename) len from emp where sal2000;
----------------------
| ename | sal | len |
----------------------
| JONES | 2975.00 | 5 |
| BLAKE | 2850.00 | 5 |
| CLARK | 2450.00 | 5 |
| SCOTT | 3000.00 | 5 |
| KING | 5000.00 | 4 |
| FORD | 3000.00 | 4 |
----------------------
6 rows in set (0.00 sec)length 函数返回的是字符串长度以字节为单位。如果是多字节字符则计算多个字节数如果是单字节字符则算作一个字节。比如字母数字算作一个字节中文表示多个字节数与字符集编码有关。
-- utf-8编码的一个汉字占三个字节
mysql select length(你好);
------------------
| length(你好) |
------------------
| 6 |
------------------
1 row in set (0.00 sec)获取字符串的子串
mysql select substring(123456, 3);
------------------------
| substring(123456, 3) |
------------------------
| 3456 |
------------------------
1 row in set (0.00 sec)mysql select substring(123456, 0);
------------------------
| substring(123456, 0) |
------------------------
| |
------------------------
1 row in set (0.00 sec)mysql select substring(123456, 3, 4);
---------------------------
| substring(123456, 3, 4) |
---------------------------
| 3456 |
---------------------------
1 row in set (0.00 sec)mysql select substring(你好世界, 1, 2);
------------------------------------
| substring(你好世界, 1, 2) |
------------------------------------
| 你好 |
------------------------------------
1 row in set (0.00 sec)mysql select substring(你好世界, 1, 4);
------------------------------------
| substring(你好世界, 1, 4) |
------------------------------------
| 你好世 |
------------------------------------
1 row in set (0.00 sec)数据库的字符串下标是从 1 开始的原因是使用数据库的人可能不是程序员只是普通用户。substring 截取子串就是按照一个个字符来截取的而不是按照字节来截取的。substring 的第一个参数是要截取的字符串第二个参数是截取的起始位置第三个参数是截取多少个字符。如果省略第三个参数默认截取到字符串的最后一个字符。 字符串替换
-- xyz是要被替换的字符串,XYZ是用来替换的字符串
-- 如果原字符串中没有要被替换的字符串,则无法完成替换
mysql select replace(abcdxyz1234, xyz, XYZ);
--------------------------------------
| replace(abcdxyz1234, xyz, XYZ) |
--------------------------------------
| abcdXYZ1234 |
--------------------------------------
1 row in set (0.00 sec)-- replace是全部替换,而不是部分替换
mysql select replace(abcdxyz1234xyz, xyz, XYZ);
-----------------------------------------
| replace(abcdxyz1234xyz, xyz, XYZ) |
-----------------------------------------
| abcdXYZ1234XYZ |
-----------------------------------------
1 row in set (0.00 sec)大小写转换
mysql select lcase(ABCD);
---------------
| lcase(ABCD) |
---------------
| abcd |
---------------
1 row in set (0.00 sec)mysql select ucase(abcd);
---------------
| ucase(abcd) |
---------------
| ABCD |
---------------
1 row in set (0.00 sec)以首字母大写的方式显示所有员工的姓名
mysql select concat(ucase(substring(ename, 1, 1)), lcase(substring(ename, 2))) name from emp;
--------
| name |
--------
| Smith |
| Allen |
| Ward |
| Jones |
| Martin |
| Blake |
| Clark |
| Scott |
| King |
| Turner |
| Adams |
| James |
| Ford |
| Miller |
--------
14 rows in set (0.00 sec)查找子串
-- instr返回的是子串第一次出现的位置,没有出现则返回零
-- instr查找子串也是以字符为单位的,而不是以字节为单位
mysql select instr(abcd1234, b);
------------------------
| instr(abcd1234, b) |
------------------------
| 2 |
------------------------
1 row in set (0.00 sec)mysql select instr(abcd1234, 4);
----------------------
| instr(abcd1234, 4) |
----------------------
| 8 |
----------------------
1 row in set (0.00 sec)mysql select instr(abcd1234, e);
------------------------
| instr(abcd1234, e) |
------------------------
| 0 |
------------------------
1 row in set (0.00 sec)找出名字中包含 TH 的员工
-- 使用通配符
mysql select * from emp where ename like %TH%;
-----------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
-----------------------------------------------------------------------
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
-----------------------------------------------------------------------
1 row in set (0.00 sec)-- 内置函数也可以出现在where子句中
mysql select * from emp where instr(ename, TH);
-----------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
-----------------------------------------------------------------------
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
-----------------------------------------------------------------------
1 row in set (0.00 sec)字符串比较
-- 和C语言的strcmp函数一直,一直比较两个字符串中
-- 的字符直到比较出结果或者直到对方到字符串的末尾
-- 字符的大小是按照ASCII码进行比较的
mysql select strcmp(abc, abcd);
-----------------------
| strcmp(abc, abcd) |
-----------------------
| -1 |
-----------------------
1 row in set (0.00 sec)mysql select strcmp(abcd, abcd);
------------------------
| strcmp(abcd, abcd) |
------------------------
| 0 |
------------------------
1 row in set (0.00 sec)mysql select strcmp(abce, abcd);
------------------------
| strcmp(abce, abcd) |
------------------------
| 1 |
------------------------
1 row in set (0.00 sec)-- 找出岗位是CLERK的所有员工
mysql select * from emp where strcmp(job, CLERK) 0;
-------------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
-------------------------------------------------------------------------
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
-------------------------------------------------------------------------
4 rows in set (0.00 sec)从左右截取字符串
mysql select left(abcd, 1);
-----------------
| left(abcd, 1) |
-----------------
| a |
-----------------
1 row in set (0.00 sec)mysql select left(abcd, 3);
-----------------
| left(abcd, 3) |
-----------------
| abc |
-----------------
1 row in set (0.00 sec)mysql select right(abcd, 3);
------------------
| right(abcd, 3) |
------------------
| bcd |
------------------
1 row in set (0.00 sec)mysql select right(abcd, 1);
------------------
| right(abcd, 1) |
------------------
| d |
------------------
1 row in set (0.00 sec)去重空格
-- trim不会去除字符串中间的空格
mysql select trim(abcd 1234) ret;
-----------
| ret |
-----------
| abcd 1234 |
-----------
1 row in set (0.00 sec)-- trim会去除左右两边的空格
mysql select trim( abcd 1234 ) ret;
-----------
| ret |
-----------
| abcd 1234 |
-----------
1 row in set (0.00 sec)-- ltrim只会去除字符串左边的空格
mysql select ltrim( abcd 1234 ) ret;
-------------
| ret |
-------------
| abcd 1234 |
-------------
1 row in set (0.00 sec)-- rtrim只会去除字符串右边的空格
mysql select rtrim( abcd 1234 ) ret;
--------------
| ret |
--------------
| abcd 1234 |
--------------
1 row in set (0.00 sec)数学函数 绝对值
mysql select abs(-10);
----------
| abs(-10) |
----------
| 10 |
----------
1 row in set (0.00 sec)mysql select abs(-3.14);
------------
| abs(-3.14) |
------------
| 3.14 |
------------
1 row in set (0.00 sec)十进制转二进制
mysql select bin(10);
---------
| bin(10) |
---------
| 1010 |
---------
1 row in set (0.00 sec)mysql select bin(-1);
------------------------------------------------------------------
| bin(-1) |
------------------------------------------------------------------
| 1111111111111111111111111111111111111111111111111111111111111111 |
------------------------------------------------------------------
1 row in set (0.00 sec)十进制转十六进制
mysql select hex(10);
---------
| hex(10) |
---------
| A |
---------
1 row in set (0.00 sec)mysql select hex(32);
---------
| hex(32) |
---------
| 20 |
---------
1 row in set (0.00 sec)进制转换
-- 将十进制的10转成二进制
mysql select conv(10, 10, 2);
-----------------
| conv(10, 10, 2) |
-----------------
| 1010 |
-----------------
1 row in set (0.00 sec)-- 将二进制的10转成十进制
mysql select conv(10, 2, 10);
-----------------
| conv(10, 2, 10) |
-----------------
| 2 |
-----------------
1 row in set (0.00 sec)向上取整
mysql select ceiling(3.14);
---------------
| ceiling(3.14) |
---------------
| 4 |
---------------
1 row in set (0.00 sec)mysql select ceiling(-3.14);
----------------
| ceiling(-3.14) |
----------------
| -3 |
----------------
1 row in set (0.00 sec)向下取整
mysql select floor(3.14);
-------------
| floor(3.14) |
-------------
| 3 |
-------------
1 row in set (0.00 sec)mysql select floor(-3.14);
--------------
| floor(-3.14) |
--------------
| -4 |
--------------
1 row in set (0.00 sec)格式化
-- 保留2位小数位数小数四舍五入)
mysql select format(12.3456, 2);
--------------------
| format(12.3456, 2) |
--------------------
| 12.35 |
--------------------
1 row in set (0.00 sec)mysql select format(100, 2);
----------------
| format(100, 2) |
----------------
| 100.00 |
----------------
1 row in set (0.00 sec)随机数
-- rand函数随机产生[0.0, 1.0)之间的数
mysql select rand();
--------------------
| rand() |
--------------------
| 0.6638928788377837 |
--------------------
1 row in set (0.00 sec)mysql select rand();
---------------------
| rand() |
---------------------
| 0.21991669872767916 |
---------------------
1 row in set (0.00 sec)-- 对rand函数进行加减乘除就可以拼出任意区间的随机数
mysql select rand()-rand();
---------------------
| rand()-rand() |
---------------------
| -0.7718694859853661 |
---------------------
1 row in set (0.00 sec)-- 产生[0, 100)之间的随机数
mysql select 100*rand();
------------------
| 100*rand() |
------------------
| 45.6843777053844 |
------------------
1 row in set (0.00 sec)rand 函数可以配合 format 函数随机出整数。
取模
mysql select mod(1, 3);
-----------
| mod(1, 3) |
-----------
| 1 |
-----------
1 row in set (0.00 sec)mysql select mod(-1, 3);
------------
| mod(-1, 3) |
------------
| -1 |
------------
1 row in set (0.00 sec)mysql select mod(3.14, 2);
--------------
| mod(3.14, 2) |
--------------
| 1.14 |
--------------
1 row in set (0.00 sec)注有关负数取模的原则可以自行查询本人就不赘述了。
其它函数
查询当前用户
mysql select user();
----------------
| user() |
----------------
| rootlocalhost |
----------------
1 row in set (0.00 sec)查看 user 表
mysql use mysql;
mysql select * from user;
-- 查看user表结构
mysql desc user;user 函数的实现原理
-- user函数可以通过select加concat函数来实现
mysql select concat(User, , Host) user() from user where Userroot;
----------------
| user() |
----------------
| rootlocalhost |
----------------
1 row in set (0.00 sec)md5 函数
mysql select md5(a);
----------------------------------
| md5(a) |
----------------------------------
| 0cc175b9c0f1b6a831c399e269772661 |
----------------------------------
1 row in set (0.00 sec)mysql select md5(ab);
----------------------------------
| md5(ab) |
----------------------------------
| 187ef4436122d1cc2f40dc2b92f0eba0 |
----------------------------------
1 row in set (0.00 sec)md5 算法对一个字符串进行 md5 摘要摘要后等到一个 32 位的字符串不管原字符串是多长。尽管两个字符串只相差一个字符md5 算法形成的两个摘要相差也很大。md5 算法可以对秘密进行摘要从而起到用户的密码因为很难通过 md5 算法形成的摘要破解出原密码。
-- authentication_string 是密码通过md5算法形成的摘要
mysql select user, host, authentication_string from user;
mysql select user, host, authentication_string from user;
---------------------------------------------------------------------
| user | host | authentication_string |
---------------------------------------------------------------------
| root | localhost | *4A4A4E1077BBA88E4537392A2D56040FF27F1FC1 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
---------------------------------------------------------------------
3 rows in set (0.00 sec)模拟用户注册的场景
-- 在自己的数据库中进行模拟
mysql select database();
------------
| database() |
------------
| my_db |
------------
1 row in set (0.00 sec)-- 创建用户表
mysql create table if not exists user(- id bigint unsigned primary key auto_increment,- name varchar(32) not null,- password char(64) not null,- reg_time datetime not null- );
Query OK, 0 rows affected (0.02 sec)mysql insert into user (name, password, reg_time) values(张三, md5(123456), now());
Query OK, 1 row affected (0.00 sec)mysql select * from user;
-------------------------------------------------------------------
| id | name | password | reg_time |
-------------------------------------------------------------------
| 1 | 张三 | e10adc3949ba59abbe56e057f20f883e | 2023-05-16 23:17:41 |
-------------------------------------------------------------------
1 row in set (0.00 sec)mysql select * from user where md5(123456) password;
-------------------------------------------------------------------
| id | name | password | reg_time |
-------------------------------------------------------------------
| 1 | 张三 | e10adc3949ba59abbe56e057f20f883e | 2023-05-16 23:17:41 |
-------------------------------------------------------------------
1 row in set (0.00 sec)用户表中储存的密码都不是真正用户的密码都是摘要来的因此后端进行用户认证比较的都是摘要。涉及到 md5 算法和密码等私密的信息,无法通过上翻来找到历史输过的 SQL 语句。 password 函数
-- MySQL数据库使用password函数对用户加密
mysql select password(123456);
-------------------------------------------
| password(123456) |
-------------------------------------------
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
-------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql select length(password(123456));
----------------------------
| length(password(123456)) |
----------------------------
| 41 |
----------------------------
1 row in set, 1 warning (0.00 sec)ifnull 函数
-- ifnullval1, val2 如果val1为null,返回val2,否则返回val1的值
-- ifnull就相当于是三目运算符mysql select ifnull(a, b);
------------------
| ifnull(a, b) |
------------------
| a |
------------------
1 row in set (0.00 sec)mysql select ifnull(null, b);
-------------------
| ifnull(null, b) |
-------------------
| b |
-------------------
1 row in set (0.00 sec)mysql select ifnull(a, null);
-------------------
| ifnull(a, null) |
-------------------
| a |
-------------------
1 row in set (0.00 sec)总结 本篇博客主要讲解了日期函数、字符串函数、数学函数、md5 函数、password 函数以及 ifnull 函数等等。以上就是本篇博客的全部内容了如果大家觉得有收获的话可以点个三连支持一下谢谢大家啦❣️
