上传网站空间,flash布局 的优秀网站,可视化编程软件,全国工程信息网文章目录运算符算术运算符比较运算符逻辑运算符MySQL运算符数字函数字符串函数日期时间函数条件函数系统信息函数加密函数其他常用函数MySQL索引索引的概念索引的分类创建索引创建表时创建索引在已经存在的表上创建索引删除索引MySQL图形化管理工具PHPMyAdminSQLyog运算符
算术…
文章目录运算符算术运算符比较运算符逻辑运算符MySQL运算符数字函数字符串函数日期时间函数条件函数系统信息函数加密函数其他常用函数MySQL索引索引的概念索引的分类创建索引创建表时创建索引在已经存在的表上创建索引删除索引MySQL图形化管理工具PHPMyAdminSQLyog运算符
算术运算符 1加、减、乘、除
mysql SELECT 11,1-1,2*4,3/8;
-----------------------
| 11 | 1-1 | 2*4 | 3/8 |
-----------------------
| 2 | 0 | 8 | 0.3750 |
-----------------------
1 row in set (0.09 sec)
2被除数是0时返回NULL
mysql SELECT 3/0;
------
| 3/0 |
------
| NULL |
------
1 row in set (0.00 sec)mysql SELECT 4 DIV 2;
---------
| 4 DIV 2 |
---------
| 2 |
---------
1 row in set (0.00 sec)
3取余数
mysql SELECT 3%8;
------
| 3%8 |
------
| 3 |
------
1 row in set (0.00 sec)mysql SELECT 3 MOD 8;
---------
| 3 MOD 8 |
---------
| 3 |
---------
1 row in set (0.00 sec)
4 SQL的字符串转换功能
mysql SELECT 13MAIZI;
------------
| 13MAIZI |
------------
| 4 |
------------
1 row in set, 1 warning (0.03 sec)
5和NULL进行算数运算结果仍未NULL
mysql SELECT 1NULL;
--------
| 1NULL |
--------
| NULL |
--------
1 row in set (0.00 sec)mysql SELECT NULLNULL;
-----------
| NULLNULL |
-----------
| NULL |
-----------
1 row in set (0.00 sec)
比较运算符 1, 比较字符串或数值是否相等数值可直接和等值的字符串进行比较
mysql SELECT 11;
-----
| 11 |
-----
| 1 |
-----
1 row in set (0.00 sec)mysql SELECT 11,11;
------------
| 11 | 11 |
------------
| 1 | 1 |
------------
1 row in set (0.00 sec)mysql SELECT 11,11,12;
-----------------
| 11 | 11 | 12 |
-----------------
| 1 | 1 | 0 |
-----------------
1 row in set (0.00 sec)mysql SELECT username,usernameking FROM student;
---------------------------
| username | usernameking |
---------------------------
| king | 1 |
| king1 | 0 |
| king2 | 0 |
| king3 | 0 |
| king4 | 0 |
| king5 | 0 |
| king6 | 0 |
| king7 | 0 |
| king8 | 0 |
---------------------------
9 rows in set (0.00 sec)mysql SELECT username,username!king FROM student;
----------------------------
| username | username!king |
----------------------------
| king | 0 |
| king1 | 1 |
| king2 | 1 |
| king3 | 1 |
| king4 | 1 |
| king5 | 1 |
| king6 | 1 |
| king7 | 1 |
| king8 | 1 |
----------------------------
9 rows in set (0.00 sec)
2和NULL比较是否相等时要用而不是
mysql SELECT id,username,age,sex,sexNULL FROM cms_user;
-----------------------------------------
| id | username | age | sex | sexNULL |
-----------------------------------------
| 1 | 张三 | 21 | 男 | 0 |
| 2 | 张三丰 | 31 | 女 | 0 |
| 3 | 章子怡 | 43 | 男 | 0 |
| 4 | long | 41 | 女 | 0 |
| 5 | ring | 9 | 男 | 0 |
| 6 | queen | 77 | 女 | 0 |
| 8 | blek | 85 | 女 | 0 |
| 9 | rose | 9 | 男 | 0 |
| 10 | lily | 39 | 女 | 0 |
| 11 | john | 72 | 保密 | 0 |
| 12 | test1 | NULL | 保密 | 0 |
| 13 | TEST2 | 18 | NULL | 1 |
| 14 | lll | 18 | NULL | 1 |
| 15 | ttt | 18 | NULL | 1 |
| 16 | ooo | 18 | NULL | 1 |
-----------------------------------------
15 rows in set (0.00 sec)
3, , ,
mysql SELECT id,username,score,score70 FROM student;
--------------------------------
| id | username | score | score70 |
--------------------------------
| 1 | king | 95 | 1 |
| 2 | king1 | 35 | 0 |
| 3 | king2 | 45 | 0 |
| 4 | king3 | 55 | 0 |
| 5 | king4 | 65 | 0 |
| 6 | king5 | 75 | 1 |
| 7 | king6 | 80 | 1 |
| 8 | king7 | 90 | 1 |
| 9 | king8 | 25 | 0 |
--------------------------------
9 rows in set (0.00 sec)
4IS NULL, IS NOT NULL
mysql SELECT id,username,age,age IS NULL FROM cms_user;
----------------------------------
| id | username | age | age IS NULL |
----------------------------------
| 1 | 张三 | 21 | 0 |
| 2 | 张三丰 | 31 | 0 |
| 3 | 章子怡 | 43 | 0 |
| 4 | long | 41 | 0 |
| 5 | ring | 9 | 0 |
| 6 | queen | 77 | 0 |
| 8 | blek | 85 | 0 |
| 9 | rose | 9 | 0 |
| 10 | lily | 39 | 0 |
| 11 | john | 72 | 0 |
| 12 | test1 | NULL | 1 |
| 13 | TEST2 | 18 | 0 |
| 14 | lll | 18 | 0 |
| 15 | ttt | 18 | 0 |
| 16 | ooo | 18 | 0 |
----------------------------------
15 rows in set (0.00 sec)mysql SELECT id,username,age,age IS NOT NULL FROM cms_user;
--------------------------------------
| id | username | age | age IS NOT NULL |
--------------------------------------
| 1 | 张三 | 21 | 1 |
| 2 | 张三丰 | 31 | 1 |
| 3 | 章子怡 | 43 | 1 |
| 4 | long | 41 | 1 |
| 5 | ring | 9 | 1 |
| 6 | queen | 77 | 1 |
| 8 | blek | 85 | 1 |
| 9 | rose | 9 | 1 |
| 10 | lily | 39 | 1 |
| 11 | john | 72 | 1 |
| 12 | test1 | NULL | 0 |
| 13 | TEST2 | 18 | 1 |
| 14 | lll | 18 | 1 |
| 15 | ttt | 18 | 1 |
| 16 | ooo | 18 | 1 |
--------------------------------------
15 rows in set (0.00 sec)
5BETWEEN…AND…
mysql SELECT id,username,age,age BETWEEN 10 AND 30 FROM cms_user;
--------------------------------------------
| id | username | age | age BETWEEN 10 AND 30 |
--------------------------------------------
| 1 | 张三 | 21 | 1 |
| 2 | 张三丰 | 31 | 0 |
| 3 | 章子怡 | 43 | 0 |
| 4 | long | 41 | 0 |
| 5 | ring | 9 | 0 |
| 6 | queen | 77 | 0 |
| 8 | blek | 85 | 0 |
| 9 | rose | 9 | 0 |
| 10 | lily | 39 | 0 |
| 11 | john | 72 | 0 |
| 12 | test1 | NULL | NULL |
| 13 | TEST2 | 18 | 1 |
| 14 | lll | 18 | 1 |
| 15 | ttt | 18 | 1 |
| 16 | ooo | 18 | 1 |
--------------------------------------------
15 rows in set (0.02 sec)
6IN
mysql SELECT id,username,age,age IN(21,31,41,51) FROM cms_user;
------------------------------------------
| id | username | age | age IN(21,31,41,51) |
------------------------------------------
| 1 | 张三 | 21 | 1 |
| 2 | 张三丰 | 31 | 1 |
| 3 | 章子怡 | 43 | 0 |
| 4 | long | 41 | 1 |
| 5 | ring | 9 | 0 |
| 6 | queen | 77 | 0 |
| 8 | blek | 85 | 0 |
| 9 | rose | 9 | 0 |
| 10 | lily | 39 | 0 |
| 11 | john | 72 | 0 |
| 12 | test1 | NULL | NULL |
| 13 | TEST2 | 18 | 0 |
| 14 | lll | 18 | 0 |
| 15 | ttt | 18 | 0 |
| 16 | ooo | 18 | 0 |
------------------------------------------
15 rows in set (0.00 sec)mysql SELECT 1 IN (1,2,3);
--------------
| 1 IN (1,2,3) |
--------------
| 1 |
--------------
1 row in set (0.00 sec)mysql SELECT 11 IN (1,2,3);
---------------
| 11 IN (1,2,3) |
---------------
| 0 |
---------------
1 row in set (0.00 sec)
7LIKE
mysql SELECT s LIKE _;
--------------
| s LIKE _ |
--------------
| 1 |
--------------
1 row in set (0.00 sec)mysql SELECT sD LIKE _;
---------------
| sD LIKE _ |
---------------
| 0 |
---------------
1 row in set (0.00 sec)mysql SELECT id,username,username LIKE ____ FROM cms_user;
-------------------------------------
| id | username | username LIKE ____ |
-------------------------------------
| 8 | blek | 1 |
| 11 | john | 1 |
| 10 | lily | 1 |
| 14 | lll | 0 |
| 4 | long | 1 |
| 16 | ooo | 0 |
| 6 | queen | 0 |
| 5 | ring | 1 |
| 9 | rose | 1 |
| 12 | test1 | 0 |
| 13 | TEST2 | 0 |
| 15 | ttt | 0 |
| 1 | 张三 | 0 |
| 2 | 张三丰 | 0 |
| 3 | 章子怡 | 0 |
-------------------------------------
15 rows in set (0.00 sec)
8REGEXP
mysql SELECT id,username,username REGEXP ^t FROM cms_user;
-------------------------------------
| id | username | username REGEXP ^t |
-------------------------------------
| 8 | blek | 0 |
| 11 | john | 0 |
| 10 | lily | 0 |
| 14 | lll | 0 |
| 4 | long | 0 |
| 16 | ooo | 0 |
| 6 | queen | 0 |
| 5 | ring | 0 |
| 9 | rose | 0 |
| 12 | test1 | 1 |
| 13 | TEST2 | 1 |
| 15 | ttt | 1 |
| 1 | 张三 | 0 |
| 2 | 张三丰 | 0 |
| 3 | 章子怡 | 0 |
-------------------------------------
15 rows in set (0.00 sec)
逻辑运算符
1AND||OR和NULL运算时结果为NULL但真值和NULL取或时结果为真
mysql SELECT 22;
------
| 22 |
------
| 1 |
------
1 row in set (0.00 sec)mysql SELECT 22,20;
------------
| 22 | 20 |
------------
| 1 | 0 |
------------
1 row in set (0.00 sec)mysql SELECT 22,20,2NULL,1||1,1||0,1||NULL,0||NULL;
---------------------------------------------------
| 22 | 20 | 2NULL | 1||1 | 1||0 | 1||NULL | 0||NULL |
---------------------------------------------------
| 1 | 0 | NULL | 1 | 1 | 1 | NULL |
---------------------------------------------------
1 row in set (0.00 sec)
2NOT
mysql SELECT NULL1;
---------
| NULL1 |
---------
| NULL |
---------
1 row in set (0.00 sec)mysql SELECT !1,!0,!NULL;
---------------
| !1 | !0 | !NULL |
---------------
| 0 | 1 | NULL |
---------------
1 row in set (0.00 sec)
3XOR
mysql SELECT 1XOR0,0XOR1,1XOR1,0XOR0;
ERROR 1054 (42S22): Unknown column 1XOR0 in field list
mysql SELECT 1 XOR 0,0 XOR 1 ,1 XOR 1,0 XOR 0;
------------------------------------
| 1 XOR 0 | 0 XOR 1 | 1 XOR 1 | 0 XOR 0 |
------------------------------------
| 1 | 1 | 0 | 0 |
------------------------------------
1 row in set (0.00 sec)
MySQL运算符
数字函数 1CEIL()进一取整
mysql SELECT CEIL(1.2),CEILING(1.2);
-------------------------
| CEIL(1.2) | CEILING(1.2) |
-------------------------
| 2 | 2 |
-------------------------
1 row in set (0.00 sec)mysql SELECT *FROM test4;
------------------
| num1 | num2 | num3 |
------------------
| 3.14 | 3.14 | 3.14 |
| 3.25 | 3.25 | 3.25 |
------------------
2 rows in set (0.07 sec)mysql SELECT num1,CEIL(num2),CEILING(num3) FROM test4;
---------------------------------
| num1 | CEIL(num2) | CEILING(num3) |
---------------------------------
| 3.14 | 4 | 4 |
| 3.25 | 4 | 4 |
---------------------------------
2 rows in set (0.00 sec)
2FLOOR()舍一取整
mysql SELECT FLOOR(3.14);
-------------
| FLOOR(3.14) |
-------------
| 3 |
-------------
1 row in set (0.00 sec)
3MOD取余数去模
mysql SELECT MOD(3,8);
----------
| MOD(3,8) |
----------
| 3 |
----------
1 row in set (0.00 sec)
4POWER()幂运算
mysql SELECT POW(2,3),POWER(3,3);
----------------------
| POW(2,3) | POWER(3,3) |
----------------------
| 8 | 27 |
----------------------
1 row in set (0.06 sec)
5ROUND()四舍五入
mysql SELECT ROUND(3.14567,2);
------------------
| ROUND(3.14567,2) |
------------------
| 3.15 |
------------------
1 row in set (0.02 sec)
6TRUNCATE()数字截取
mysql SELECT TRUNCATE(3.14567,2);
---------------------
| TRUNCATE(3.14567,2) |
---------------------
| 3.14 |
---------------------
1 row in set (0.00 sec)
7ABS()取绝对值
mysql SELECT ABS(-12);
----------
| ABS(-12) |
----------
| 12 |
----------
1 row in set (0.00 sec)
8PI()圆周率
mysql SELECT PI();
----------
| PI() |
----------
| 3.141593 |
----------
1 row in set (0.01 sec)
9RAND()和RAND(x)返回0~1之间的随机数RAND(x)x相同时返回的随机数相同
mysql SELECT RAND();
--------------------
| RAND() |
--------------------
| 0.6808652986594153 |
--------------------
1 row in set (0.00 sec)mysql SELECT RAND();
--------------------
| RAND() |
--------------------
| 0.7984468916416605 |
--------------------
1 row in set (0.00 sec)mysql SELECT RAND(1);
---------------------
| RAND(1) |
---------------------
| 0.40540353712197724 |
---------------------
1 row in set (0.00 sec)mysql SELECT RAND(1);
---------------------
| RAND(1) |
---------------------
| 0.40540353712197724 |
---------------------
1 row in set (0.00 sec)
10SIGN(x)返回x的符号x为负数、0、正数分别返回-1、0、1
mysql SELECT SIGN(12),SIGN(0),SIGN(-12);
------------------------------
| SIGN(12) | SIGN(0) | SIGN(-12) |
------------------------------
| 1 | 0 | -1 |
------------------------------
1 row in set (0.00 sec)
11EXP(x)计算e的几次方
mysql SELECT EXP(3);
--------------------
| EXP(3) |
--------------------
| 20.085536923187668 |
--------------------
1 row in set (0.03 sec)
字符串函数 1CHAR_LENGTH(S)返回字符串的字符数LENGTH返回字符串长度
mysql SELECT CHAR_LENGTH(maizi),LENGTH(maizi);
---------------------------------------
| CHAR_LENGTH(maizi) | LENGTH(maizi) |
---------------------------------------
| 5 | 5 |
---------------------------------------
1 row in set (0.00 sec)mysql SELECT CHAR_LENGTH(啊),LENGTH(啊);
-----------------------------------
| CHAR_LENGTH(啊) | LENGTH(啊) |
-----------------------------------
| 1 | 3 |
-----------------------------------
1 row in set (0.00 sec)
2CONTACT(S1, S2…)将字符串合并为一个字符串和NULL连接时结果为NULL
mysql SELECT CONCAT(HELLO,WORLD);
-------------------------
| CONCAT(HELLO,WORLD) |
-------------------------
| HELLOWORLD |
-------------------------
1 row in set (0.02 sec)mysql USE cms;
Database changed
mysql SELECT * FROM student;
---------------------
| id | username | score |
---------------------
| 1 | king | 95 |
| 2 | king1 | 35 |
| 3 | king2 | 45 |
| 4 | king3 | 55 |
| 5 | king4 | 65 |
| 6 | king5 | 75 |
| 7 | king6 | 80 |
| 8 | king7 | 90 |
| 9 | king8 | 25 |
---------------------
9 rows in set (0.00 sec)mysql SELECT id,CONCAT(username,_)FROM student;
--------------------------
| id | CONCAT(username,_) |
--------------------------
| 1 | king_ |
| 2 | king1_ |
| 3 | king2_ |
| 4 | king3_ |
| 5 | king4_ |
| 6 | king5_ |
| 7 | king6_ |
| 8 | king7_ |
| 9 | king8_ |
--------------------------
9 rows in set (0.00 sec)mysql SELECT CONCAT(a,b,null);
----------------------
| CONCAT(a,b,null) |
----------------------
| NULL |
----------------------
1 row in set (0.00 sec)
3CONTACT_WS(X, S1, S2…)将制定分隔符连接字符串当X为NULL时连接结果为NULL当S1或S2…为NULL时当做空字符处理
mysql SELECT CONCAT_WS(^_^,a,b,c);
------------------------------
| CONCAT_WS(^_^,a,b,c) |
------------------------------
| a^_^b^_^c |
------------------------------
1 row in set (0.00 sec)mysql SELECT CONCAT_WS(,a,b,c);
---------------------------
| CONCAT_WS(,a,b,c) |
---------------------------
| abc |
---------------------------
1 row in set (0.00 sec)mysql SELECT CONCAT_WS(NULL,a,b,c);
-----------------------------
| CONCAT_WS(NULL,a,b,c) |
-----------------------------
| NULL |
-----------------------------
1 row in set (0.00 sec)mysql SELECT CONCAT_WS(^_^,a,b,c,NULL);
-----------------------------------
| CONCAT_WS(^_^,a,b,c,NULL) |
-----------------------------------
| a^_^b^_^c |
-----------------------------------
1 row in set (0.00 sec)
4UPPER(S)/UCASE(S)将字符转换为大写
mysql SELECT UPPER(this is a test),UCASE(this is a test);
--------------------------------------------------
| UPPER(this is a test) | UCASE(this is a test) |
--------------------------------------------------
| THIS IS A TEST | THIS IS A TEST |
--------------------------------------------------
1 row in set (0.00 sec)5LOWER(S)/LCASE(S)将字符转换为小写
mysql SELECT LOWER(HELLO WORLD),LCASE(HELLO WORLD);
--------------------------------------------
| LOWER(HELLO WORLD) | LCASE(HELLO WORLD) |
--------------------------------------------
| hello world | hello world |
--------------------------------------------
1 row in set (0.00 sec)6LEFT(S, N)/RIGHT(S, N)返回字符串的前/后n个字符
mysql SELECT LEFT(ABCDEF,2),RIGHT(ABCDEF,2);
-------------------------------------
| LEFT(ABCDEF,2) | RIGHT(ABCDEF,2) |
-------------------------------------
| AB | EF |
-------------------------------------
1 row in set (0.01 sec)7LPAD(S1, LEN, S2)/ RPAD(S1, LEN, S2)将字符串S1用S2填充到指定的LEN
mysql SELECT LPAD(A,5,?),RPAD(A,5,!);
----------------------------------
| LPAD(A,5,?) | RPAD(A,5,!) |
----------------------------------
| ????A | A!!!! |
----------------------------------
1 row in set (0.04 sec)8LTRIN(S)/ RTRIM(S)/ TRIM(S)去掉字符串中的空格
mysql SELECT CONCAT(_,TRIM( ABC ),_),CONCAT(_,LTRIM( ABC ),_),CONCAT(_,RTRIM( ABC ),_);
-----------------------------------------------------------------------------------------------
| CONCAT(_,TRIM( ABC ),_) | CONCAT(_,LTRIM( ABC ),_) | CONCAT(_,RTRIM( ABC ),_) |
-----------------------------------------------------------------------------------------------
| _ABC_ | _ABC _ | _ ABC_ |
-----------------------------------------------------------------------------------------------
1 row in set (0.00 sec)9TRIM(S1 FROM S)去掉字符串s中开始处和结尾处的字符串s1
mysql SELECT TRIM(A FROM ABCBCA);
-------------------------
| TRIM(A FROM ABCBCA) |
-------------------------
| BCBC |
-------------------------
1 row in set (0.00 sec)10REPEAT(S,N)重复字符串指定次数
mysql SELECT REPEAT(H,5);
---------------
| REPEAT(H,5) |
---------------
| HHHHH |
---------------
1 row in set (0.00 sec)11SPACE(N)返回N个空格
mysql SELECT CONCAT(_,SPACE(5),_);
--------------------------
| CONCAT(_,SPACE(5),_) |
--------------------------
| _ _ |
--------------------------
1 row in set (0.00 sec)12REPLACE(S,S1,S2)在字符串中搜索S1替换成S2区分大小写若未搜索到则返回原字符串
mysql SELECT REPLACE(ABCBCA,A,_);
---------------------------
| REPLACE(ABCBCA,A,_) |
---------------------------
| _BCBC_ |
---------------------------
1 row in set (0.00 sec)mysql SELECT REPLACE(ABCBCA,a,_);
---------------------------
| REPLACE(ABCBCA,a,_) |
---------------------------
| ABCBCA |
---------------------------
1 row in set (0.00 sec)13STRCMP(S1, S2)比较字符串不区分大小写分别返回10-1
mysql SELECT STRCMP(A,A),STRCMP(A,a),STRCMP(B,A);
---------------------------------------------------
| STRCMP(A,A) | STRCMP(A,a) | STRCMP(B,A) |
---------------------------------------------------
| 0 | 0 | 1 |
---------------------------------------------------
1 row in set (0.02 sec)mysql SELECT STRCMP(A,A),STRCMP(A,a),STRCMP(A,B);
---------------------------------------------------
| STRCMP(A,A) | STRCMP(A,a) | STRCMP(A,B) |
---------------------------------------------------
| 0 | 0 | -1 |
---------------------------------------------------
1 row in set (0.00 sec)14SUBSTRING(S, N, LEN)截取字符串
mysql SELECT SUBSTRING(ABCDEF,2,2);
-------------------------
| SUBSTRING(ABCDEF,2,2) |
-------------------------
| BC |
-------------------------
1 row in set (0.00 sec)15REVERSE(S)反转字符串
mysql SELECT REVERSE(ABC);
----------------
| REVERSE(ABC) |
----------------
| CBA |
----------------
1 row in set (0.00 sec)16ELT(N, S1, S2)返回指定位置的字符串
mysql SELECT ELT(2,A,B,C);
--------------------
| ELT(2,A,B,C) |
--------------------
| B |
--------------------
1 row in set (0.00 sec)日期时间函数 1CURDATE(), CURRENT_DATE()返回当前日期
mysql SELECT CURDATE(),CURRENT_DATE();
----------------------------
| CURDATE() | CURRENT_DATE() |
----------------------------
| 2020-03-27 | 2020-03-27 |
----------------------------
1 row in set (0.04 sec)2CURTIME(), CURRENT_TIME()返回当前时间
mysql SELECT CURTIME(),CURRENT_TIME();
---------------------------
| CURTIME() | CURRENT_TIME() |
---------------------------
| 15:59:32 | 15:59:32 |
---------------------------
1 row in set (0.00 sec)3NOW()返回当前日期和时间
mysql SELECT NOW();
---------------------
| NOW() |
---------------------
| 2020-03-27 15:59:37 |
---------------------
1 row in set (0.00 sec)4MONTH(D)返回当前日期中月份的值
mysql SELECT MONTH(2015-1-3);
-------------------
| MONTH(2015-3-27) |
-------------------
| 3 |
-------------------
1 row in set (0.00 sec)mysql SELECT MONTH(NOW());
--------------
| MONTH(NOW()) |
--------------
| 3 |
--------------
1 row in set (0.00 sec)5MONTHNAME(D)返回日期中月份名称
mysql SELECT MONTHNAME(NOW());
------------------
| MONTHNAME(NOW()) |
------------------
| March |
------------------
1 row in set (0.09 sec)6DAYNAME(D)返回日期是几
mysql SELECT DAYNAME(NOW());
----------------
| DAYNAME(NOW()) |
----------------
| Friday |
----------------
1 row in set (0.02 sec)7DAYOFWEEK(D)返回一周内的第几天1代表星期日
mysql SELECT DAYOFWEEK(NOW());
------------------
| DAYOFWEEK(NOW()) |
------------------
| 6 |
------------------
1 row in set (0.00 sec)8WEEKDAY(D)返回日期是星期几0代表星期一
mysql SELECT WEEKDAY(NOW());
----------------
| WEEKDAY(NOW()) |
----------------
| 6 |
----------------
1 row in set (0.00 sec)9WEEK(D)一年中的第几个星期
mysql SELECT WEEK(NOW());
-------------
| WEEK(NOW()) |
-------------
| 14 |
-------------
1 row in set (0.00 sec)10YEAR(D)返回年份值
mysql SELECT YEAR(NOW());
-------------
| YEAR(NOW()) |
-------------
| 2020 |
-------------
1 row in set (0.00 sec)11HOUR(T)返回小时值
mysql SELECT HOUR(NOW());
-------------
| HOUR(NOW()) |
-------------
| 16 |
-------------
1 row in set (0.00 sec)12MINUTE(T)返回分钟值
mysql SELECT MINUTE(NOW());
---------------
| MINUTE(NOW()) |
---------------
| 1 |
---------------
1 row in set (0.00 sec)13SECOND(T)返回秒数
mysql SELECT SECOND(NOW());
---------------
| SECOND(NOW()) |
---------------
| 49 |
---------------
1 row in set (0.00 sec)14DATEDIFF(D1, D2)计算量日期之间相隔的天数
mysql SELECT DATEDIFF(CURRENT_DATE(),1990-1-1);
-------------------------------------
| DATEDIFF(CURRENT_DATE(),1990-1-1) |
-------------------------------------
| 9133 |
-------------------------------------
1 row in set (0.02 sec)条件函数 1IF(EXPR, V1, V2)如果表达式EXPR成立则返回结果V1否则返回V2
mysql USE cms;
Database changed
mysql SELECT * FROM student;
---------------------
| id | username | score |
---------------------
| 1 | king | 95 |
| 2 | king1 | 35 |
| 3 | king2 | 45 |
| 4 | king3 | 55 |
| 5 | king4 | 65 |
| 6 | king5 | 75 |
| 7 | king6 | 80 |
| 8 | king7 | 90 |
| 9 | king8 | 25 |
---------------------
9 rows in set (0.00 sec)mysql SELECT id,username,score,IF(score60,及格,不及格)FROM student;
---------------------------------------------------------
| id | username | score | IF(score60,及格,不及格) |
---------------------------------------------------------
| 1 | king | 95 | 及格 |
| 2 | king1 | 35 | 不及格 |
| 3 | king2 | 45 | 不及格 |
| 4 | king3 | 55 | 不及格 |
| 5 | king4 | 65 | 及格 |
| 6 | king5 | 75 | 及格 |
| 7 | king6 | 80 | 及格 |
| 8 | king7 | 90 | 及格 |
| 9 | king8 | 25 | 不及格 |
---------------------------------------------------------
9 rows in set (0.03 sec)
2IFNULL(V1,V2)如果V1不为空就显示V1的值否则显示V2的值
mysql SELECT id,username,age,IFNULL(age,100)FROM cms_user;
----------------------------------------
| id | username | age | IFNULL(age,100) |
----------------------------------------
| 1 | 张三 | 21 | 21 |
| 2 | 张三丰 | 31 | 31 |
| 3 | 章子怡 | 43 | 43 |
| 4 | long | 41 | 41 |
| 5 | ring | 9 | 9 |
| 6 | queen | 77 | 77 |
| 8 | blek | 85 | 85 |
| 9 | rose | 9 | 9 |
| 10 | lily | 39 | 39 |
| 11 | john | 72 | 72 |
| 12 | test1 | NULL | 100 |
| 13 | TEST2 | 18 | 18 |
| 14 | lll | 18 | 18 |
| 15 | ttt | 18 | 18 |
| 16 | ooo | 18 | 18 |
----------------------------------------
15 rows in set (0.00 sec)3CASE WHEN exp1 THEN vi [WHEN exp2 THEN v2] [ELSE vn] END
mysql SELECT id,username,score, CASE WHEN score60 THEN 不错 WHEN score60 THEN 刚及格 ELSE 没及格 END FROM student;
-----------------------------------------------------------------------------------------------------------
| id | username | score | CASE WHEN score60 THEN 不错 WHEN score60 THEN 刚及格 ELSE 没及格 END |
-----------------------------------------------------------------------------------------------------------
| 1 | king | 95 | 不错 |
| 2 | king1 | 35 | 没及格 |
| 3 | king2 | 45 | 没及格 |
| 4 | king3 | 55 | 没及格 |
| 5 | king4 | 65 | 不错 |
| 6 | king5 | 75 | 不错 |
| 7 | king6 | 80 | 不错 |
| 8 | king7 | 90 | 不错 |
| 9 | king8 | 25 | 没及格 |
-----------------------------------------------------------------------------------------------------------
9 rows in set (0.00 sec)系统信息函数 加密函数 1MD5(str)信息摘要算法
mysql SELECT MD5(ADMIN);
----------------------------------
| MD5(ADMIN) |
----------------------------------
| 73acd9a5972130b75066c82595a1fae3 |
----------------------------------
1 row in set (0.07 sec)
mysql SELECT LENGTH(MD5(ADMIN));
----------------------
| LENGTH(MD5(ADMIN)) |
----------------------
| 32 |
----------------------
1 row in set (0.00 sec)
2PASSWORD(str)密码算法例如对用户设置密码
mysql SELECT PASSWORD(root),PASSWORD(king);
--------------------------------------------------------------------------------------
| PASSWORD(root) | PASSWORD(king) |
--------------------------------------------------------------------------------------
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | *0C6F8A2CE8ABFD18609CCE4CDFAB3C15DAD20718 |
--------------------------------------------------------------------------------------
1 row in set (0.02 sec)3ENCODE(str, pwd_str)加密结果是一二进制数必须使用BLOB类型保存字段 4DECODE(crypt_str, pwd_str)通过对ENCODE加密后的内容进行解密
其他常用函数 1FORMAT(x, n)将数字x进行格式化并将x保留到小数点后n为
mysql SELECT FORMAT(3.14567,2);
-------------------
| FORMAT(3.14567,2) |
-------------------
| 3.15 |
-------------------
1 row in set (0.00 sec)2ASCII(s)返回字符串的第一个字符的ASCII码
mysql SELECT ASCII(abc);
--------------
| ASCII(abc) |
--------------
| 97 |
--------------
1 row in set (0.02 sec)3BIN(x)返回x的二进制编码HEX(x)返回x的十六进制编码OCT(x)返回x的八进制编码
mysql SELECT BIN(5),HEX(5),OCT(5);
------------------------
| BIN(5) | HEX(5) | OCT(5) |
------------------------
| 101 | 5 | 5 |
------------------------
1 row in set (0.02 sec)4CONV(x, f1, f2)将x从f1进制数编程f2进制数
mysql SELECT CONV(5,10,2);
--------------
| CONV(5,10,2) |
--------------
| 101 |
--------------
1 row in set (0.00 sec)mysql SELECT CONV(35,10,2);
---------------
| CONV(35,10,2) |
---------------
| 100011 |
---------------
1 row in set (0.00 sec)mysql SELECT CONV(35,10,8);
---------------
| CONV(35,10,8) |
---------------
| 43 |
---------------
1 row in set (0.00 sec)mysql SELECT CONV(35,10,16);
----------------
| CONV(35,10,16) |
----------------
| 23 |
----------------
1 row in set (0.00 sec)5INET_ATON(IP)将IP地址转换为数字
mysql SELECT INET_ATON(127.0.0.1);
------------------------
| INET_ATON(127.0.0.1) |
------------------------
| 2130706433 |
------------------------
1 row in set (0.06 sec)6INET_NTOA(n)将数字转换成IP地址
mysql SELECT INET_NTOA(2130706433);
-----------------------
| INET_NTOA(2130706433) |
-----------------------
| 127.0.0.1 |
-----------------------
1 row in set (0.02 sec)7get_LOCT(name, time)定义锁
mysql SELECT GET_LOCK(KING,10);
---------------------
| GET_LOCK(KING,10) |
---------------------
| 1 |
---------------------
1 row in set (0.05 sec)8IS_FREE_LOCK(name)判断锁是否正在使用0表示正在使用1表示未在使用解锁或新建立锁都能使锁脱离被使用的状态
mysql SELECT IS_FREE_LOCK(KING);
----------------------
| IS_FREE_LOCK(KING) |
----------------------
| 0 |
----------------------
1 row in set (0.02 sec)mysql SELECT RELEASE_LOCK(KING);
----------------------
| RELEASE_LOCK(KING) |
----------------------
| 1 |
----------------------
1 row in set (0.00 sec)mysql SELECT IS_FREE_LOCK(KING);
----------------------
| IS_FREE_LOCK(KING) |
----------------------
| 1 |
----------------------
1 row in set (0.00 sec)mysql SELECT GET_LOCK(MAIZI,10);
----------------------
| GET_LOCK(MAIZI,10) |
----------------------
| 1 |
----------------------
1 row in set (0.00 sec)mysql SELECT IS_FREE_LOCK(MAIZI);
-----------------------
| IS_FREE_LOCK(MAIZI) |
-----------------------
| 0 |
-----------------------
1 row in set (0.00 sec)mysql SELECT GET_LOCK(AB,5);
------------------
| GET_LOCK(AB,5) |
------------------
| 1 |
------------------
1 row in set (0.00 sec)mysql SELECT IS_FREE_LOCK(MAIZI);
-----------------------
| IS_FREE_LOCK(MAIZI) |
-----------------------
| 1 |
-----------------------
1 row in set (0.00 sec)9RELSASE_LOCK(name)解锁
mysql SELECT RELEASE_LOCK(KING);
----------------------
| RELEASE_LOCK(KING) |
----------------------
| 1 |
----------------------
1 row in set (0.00 sec)mysql SELECT IS_FREE_LOCK(KING);
----------------------
| IS_FREE_LOCK(KING) |
----------------------
| 1 |
----------------------
1 row in set (0.00 sec)MySQL索引
索引的概念
1索引由数据库中的一列或多列组合而成其作用是提高对表中数据的查询速度 2索引的优点是可以提高检索数据的速度 3索引的缺点是创建和维护索引需要耗费时间 4索引可以提高查询速度会减慢写入速度
索引的分类
1普通索引 2唯一索引 3全文索引 只有是字符类型且全是英文名时可使用 4单列索引 5多列索引 6空间索引
创建索引
创建表时创建索引
CREATE TABLE tbl_name( 字段名称 字段类型 [完整性约束条件], …, [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名称] (字段名称) ) 1创建普通索引
mysql CREATE TABLE test4(- id TINYINT UNSIGNED,- username VARCHAR(20),- INDEX in_id(id),- KEY in_username(username)- );
Query OK, 0 rows affected (0.42 sec)mysql SHOW CREATE TABLE test4;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| test4 | CREATE TABLE test4 (id tinyint(3) unsigned DEFAULT NULL,username varchar(20) DEFAULT NULL,KEY in_id (id),KEY in_username (username)
) ENGINEInnoDB DEFAULT CHARSETutf8 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.03 sec)2创建唯一索引
mysql CREATE TABLE test5(- id TINYINT UNSIGNED AUTO_INCREMENT KEY,- username VARCHAR(20) NOT NULL UNIQUE,- card CHAR(18) NOT NULL,- UNIQUE KEY uni_card(card)- );
Query OK, 0 rows affected (0.47 sec)mysql SHOW CREATE TABLE test5;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| test5 | CREATE TABLE test5 (id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,username varchar(20) NOT NULL,card char(18) NOT NULL,PRIMARY KEY (id),UNIQUE KEY username (username),UNIQUE KEY uni_card (card)
) ENGINEInnoDB DEFAULT CHARSETutf8 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)3创建全文索引
mysql CREATE TABLE test6(- id TINYINT UNSIGNED AUTO_INCREMENT KEY,- username VARCHAR(20) NOT NULL UNIQUE,- userDesc VARCHAR(20) NOT NULL,- FULLTEXT INDEX full_userDesc(userDesc)- );
Query OK, 0 rows affected (2.09 sec)mysql SHOW CREATE TABLE test6;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| test6 | CREATE TABLE test6 (id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,username varchar(20) NOT NULL,userDesc varchar(20) NOT NULL,PRIMARY KEY (id),UNIQUE KEY username (username),FULLTEXT KEY full_userDesc (userDesc)
) ENGINEInnoDB DEFAULT CHARSETutf8 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)4创建单列索引
mysql CREATE TABLE test7(- id TINYINT UNSIGNED AUTO_INCREMENT KEY,- test1 VARCHAR(20) NOT NULL,- test2 VARCHAR(20) NOT NULL,- test3 VARCHAR(20) NOT NULL,- test4 VARCHAR(20) NOT NULL,- INDEX in_test1(test1)- );
Query OK, 0 rows affected (0.38 sec)mysql SHOW CREATE TABLE test7;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| test7 | CREATE TABLE test7 (id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,test1 varchar(20) NOT NULL,test2 varchar(20) NOT NULL,test3 varchar(20) NOT NULL,test4 varchar(20) NOT NULL,PRIMARY KEY (id),KEY in_test1 (test1)
) ENGINEInnoDB DEFAULT CHARSETutf8 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.02 sec)5创建多列索引
mysql CREATE TABLE test8(- id TINYINT UNSIGNED AUTO_INCREMENT KEY,- test1 VARCHAR(20) NOT NULL,- test2 VARCHAR(20) NOT NULL,- test3 VARCHAR(20) NOT NULL,- test4 VARCHAR(20) NOT NULL,- INDEX mul_t1_t2_t3(test1,test2,test3)- );
Query OK, 0 rows affected (0.27 sec)mysql SHOW CREATE TABLE test8;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| test8 | CREATE TABLE test8 (id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,test1 varchar(20) NOT NULL,test2 varchar(20) NOT NULL,test3 varchar(20) NOT NULL,test4 varchar(20) NOT NULL,PRIMARY KEY (id),KEY mul_t1_t2_t3 (test1,test2,test3)
) ENGINEInnoDB DEFAULT CHARSETutf8 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql DESC test8;
----------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------------------
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| test1 | varchar(20) | NO | MUL | NULL | |
| test2 | varchar(20) | NO | | NULL | |
| test3 | varchar(20) | NO | | NULL | |
| test4 | varchar(20) | NO | | NULL | |
----------------------------------------------------------------
5 rows in set (0.08 sec)6创建空间索引只能给空间变量创建空间索引
mysql CREATE TABLE test10(- id TINYINT UNSIGNED AUTO_INCREMENT KEY,- test GEOMETRY NOT NULL,- SPATIAL INDEX spa_test(test)- )ENGINEMyISAM;
Query OK, 0 rows affected (0.06 sec)mysql SHOW CREATE TABLE test10;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| test10 | CREATE TABLE test10 (id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,test geometry NOT NULL,PRIMARY KEY (id),SPATIAL KEY spa_test (test)
) ENGINEMyISAM DEFAULT CHARSETutf8 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.09 sec)在已经存在的表上创建索引
【1】CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名称 ON 表名 {字段名称 [(长度)] [ASC|DESC]} 1普通索引
CREATE INDEX in_id ON test4(id)2唯一索引
CREATE UNIQUE INDEX uni_username ON test5(username);3全文索引 4单列索引 5多列索引 6空间索引
CREATE SPATIAL INDEX spa_test ON test10(test);【2】ALTER TABLE tbl_name ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名称(字段名称[(长度)] [ASC|DESC]) 1普通索引
ALTER TABLE test4 ADD INDEX in_username(username)2唯一索引
ALTER TABLE test5 ADD UNIQUE INDEX uni_card(card);3全文索引
CREATE FULLTEXT INDEX full_userDesc ON test6(userDesc);4单列索引 5多列索引
ALTER TABLE test8 ADD INDEX mul_ti_t2_t3(test1,test2,test3);6空间索引
删除索引
【1】DROP INDEX 索引名称 ON tbl_name
DROP INDEX full_userDesc ON test6;【2】ALTER tbl_name 表名 DROP INDEX 索引名称
ALTER TABLE test8 DROP INDEX mul_t1_t2_t3;MySQL图形化管理工具
PHPMyAdmin
PHPMyAdmin是一个用PHP编写的软件工具可以通过web方式控制和操作MySQL数据库
SQLyog
【1】是业界注明的Webyog公司出品的一款简介高效功能强大的MySQL数据库管理工具 【2】特点 1基于C和MySQLQPI编程 2方便快捷的数据库同步与数据库结构同步工具 3易用的数据库数据备份与还原功能4支持导入与导出XML、HTML、CSV等多种格式的数据 5直接运行批量SQL脚本文件速度极快 6新版本更适合增加了强大的数据迁移
