mysql 学习记录（五）--字符串、数值处理、日期、查询的逻辑处理、系统相关内容查询、IP地址相关、密码

1.字符串函数
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select concat('aaa','bbb','ccc'),concat('aaa',null);
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc                 | NULL               |
+---------------------------+--------------------+
1 row in set (0.00 sec)

mysql> select insert('beijing200&you',12,3,'me');
+------------------------------------+
| insert('beijing200&you',12,3,'me') |
+------------------------------------+
| beijing200&me                      |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select lower('BEIJING2008'),UPPER('beijing2008');
+----------------------+----------------------+
| lower('BEIJING2008') | UPPER('beijing2008') |
+----------------------+----------------------+
| beijing2008          | BEIJING2008          |
+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> select left('beijing2008',7),left('beijing',null),right('beijing2008',4);
+-----------------------+----------------------+------------------------+
| left('beijing2008',7) | left('beijing',null) | right('beijing2008',4) |
+-----------------------+----------------------+------------------------+
| beijing               | NULL                 | 2008                   |
+-----------------------+----------------------+------------------------+
1 row in set (0.00 sec)

mysql> select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
+---------------------------+---------------------------+
| lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |
+---------------------------+---------------------------+
| beijingbeijingbe2008      | beijing2008200820082      |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

mysql> select ltrim('  lbeijing'),rtrim('beijing!           ');
+---------------------+------------------------------+
| ltrim('  lbeijing') | rtrim('beijing!           ') |
+---------------------+------------------------------+
| lbeijing            | beijing!                     |
+---------------------+------------------------------+
1 row in set (0.00 sec)

mysql> select repeat('mysql  ',3);
+-----------------------+
| repeat('mysql  ',3)   |
+-----------------------+
| mysql  mysql  mysql   |
+-----------------------+
1 row in set (0.01 sec)

mysql> select replace('beijing_2010','_2010','2008');
+----------------------------------------+
| replace('beijing_2010','_2010','2008') |
+----------------------------------------+
| beijing2008                            |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> select trim('          $ beijing2008 $        ');
+-------------------------------------------+
| trim('          $ beijing2008 $        ') |
+-------------------------------------------+
| $ beijing2008 $                           |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select substring('beijing2008',8,4),substring('beijing2008',1,7);
+------------------------------+------------------------------+
| substring('beijing2008',8,4) | substring('beijing2008',1,7) |
+------------------------------+------------------------------+
| 2008                         | beijing                      |
+------------------------------+------------------------------+
1 row in set (0.00 sec)

2.数值处理函数
mysql> select abs(-0.8),abs(0.8);
+-----------+----------+
| abs(-0.8) | abs(0.8) |
+-----------+----------+
|       0.8 |      0.8 |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select ceil(-9.21),ceil(95.21355);
+-------------+----------------+
| ceil(-9.21) | ceil(95.21355) |
+-------------+----------------+
|          -9 |             96 |
+-------------+----------------+
1 row in set (0.00 sec)

mysql> select floor(-0.8),floor(0.32805);
+-------------+----------------+
| floor(-0.8) | floor(0.32805) |
+-------------+----------------+
|          -1 |              0 |
+-------------+----------------+
1 row in set (0.00 sec)

mysql> select mod(15,10),mod(1,11),mod(null,10);
+------------+-----------+--------------+
| mod(15,10) | mod(1,11) | mod(null,10) |
+------------+-----------+--------------+
|          5 |         1 |         NULL |
+------------+-----------+--------------+
1 row in set (0.00 sec)

mysql> select rand(),rand(),rand();
+--------------------+--------------------+--------------------+
| rand()             | rand()             | rand()             |
+--------------------+--------------------+--------------------+
| 0.7249892304884169 | 0.4090333165685025 | 0.8701989221109068 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> select ceil(100*rand()),ceil(100*rand());
+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+
|               13 |                1 |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> select round(1.1),round(1.1,2),round(1,2);
+------------+--------------+------------+
| round(1.1) | round(1.1,2) | round(1,2) |
+------------+--------------+------------+
|          1 |         1.10 |          1 |
+------------+--------------+------------+
1 row in set (0.00 sec)

mysql> select round(1.1),round(1.1,2),round(1.235251,2);
+------------+--------------+-------------------+
| round(1.1) | round(1.1,2) | round(1.235251,2) |
+------------+--------------+-------------------+
|          1 |         1.10 |              1.24 |
+------------+--------------+-------------------+
1 row in set (0.00 sec)

mysql> select round(1.1),round(1.1,2),round(1.2635251,2);
+------------+--------------+--------------------+
| round(1.1) | round(1.1,2) | round(1.2635251,2) |
+------------+--------------+--------------------+
|          1 |         1.10 |               1.26 |
+------------+--------------+--------------------+
1 row in set (0.00 sec)

mysql> select round(1.2356234234,2),truncate(1.2384235235,2);
+-----------------------+--------------------------+
| round(1.2356234234,2) | truncate(1.2384235235,2) |
+-----------------------+--------------------------+
|                  1.24 |                     1.23 |
+-----------------------+--------------------------+
1 row in set (0.01 sec)

3.日期函数
mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2015-10-02 |
+------------+
1 row in set (0.01 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 09:40:21  |
+-----------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-10-02 09:41:40 |
+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1443804114 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1443804114);
+---------------------------+
| from_unixtime(1443804114) |
+---------------------------+
| 2015-10-02 09:41:54       |
+---------------------------+
1 row in set (0.00 sec)

mysql> select week(now()),year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
|          39 |        2015 |
+-------------+-------------+
1 row in set (0.00 sec)

mysql> select hour(curtime()),minute(curtime());
+-----------------+-------------------+
| hour(curtime()) | minute(curtime()) |
+-----------------+-------------------+
|               9 |                42 |
+-----------------+-------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-10-02 09:42:53 |
+---------------------+
1 row in set (0.00 sec)

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| October          |
+------------------+
1 row in set (0.01 sec)

mysql> select date_format(now(),'%M,%D,%Y');
+-------------------------------+
| date_format(now(),'%M,%D,%Y') |
+-------------------------------+
| October,2nd,2015              |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select now() current;
+---------------------+
| current             |
+---------------------+
| 2015-10-02 09:44:33 |
+---------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),INTERVAL '1_2' year_month);
+-------------------------------------------+
| date_add(now(),INTERVAL '1_2' year_month) |
+-------------------------------------------+
| 2016-12-02 09:44:52                       |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),INTERVAL 31 day) ;
+---------------------------------+
| date_add(now(),INTERVAL 31 day) |
+---------------------------------+
| 2015-11-02 09:45:44             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),INTERVAL 31 day) as after31days;
+---------------------+
| after31days         |
+---------------------+
| 2015-11-02 09:45:55 |
+---------------------+
1 row in set (0.01 sec)

mysql> select date_add(now(),interval '1_2' year_month) as after_oneyear_twomonth;
+------------------------+
| after_oneyear_twomonth |
+------------------------+
| 2016-12-02 09:46:27    |
+------------------------+
1 row in set (0.01 sec)

mysql> select datediff('2008-08-08',now());
+------------------------------+
| datediff('2008-08-08',now()) |
+------------------------------+
|                        -2611 |
+------------------------------+
1 row in set (0.00 sec)

4.查询的逻辑处理
mysql> use test1;
Database changed
mysql> create table salary(
    -> userid int,
    -> salart decimal(9,2));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into salary values
    -> (1,1000),
    -> (2,2000),
    -> (3,3000),
    -> (4,4000),
    -> (5,5000),
    -> (6,6000),
    -> (7,7000),
    -> (1,null);
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from salary;
+--------+---------+
| userid | salart  |
+--------+---------+
|      1 | 1000.00 |
|      2 | 2000.00 |
|      3 | 3000.00 |
|      4 | 4000.00 |
|      5 | 5000.00 |
|      6 | 6000.00 |
|      7 | 7000.00 |
|      1 |    NULL |
+--------+---------+
8 rows in set (0.00 sec)

mysql> select if(salary>2000,'high','low') from salary;
+------------------------------+
| if(salary>2000,'high','low') |
+------------------------------+
| low                          |
| low                          |
| high                         |
| high                         |
| high                         |
| high                         |
| high                         |
| low                          |
+------------------------------+
8 rows in set (0.00 sec)

mysql> select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
|          1000.00 |
|          2000.00 |
|          3000.00 |
|          4000.00 |
|          5000.00 |
|          6000.00 |
|          7000.00 |
|             0.00 |
+------------------+
8 rows in set (0.00 sec)

mysql> select case when salary<=2000 then 'low' else 'high' end from salary;
+---------------------------------------------------+
| case when salary<=2000 then 'low' else 'high' end |
+---------------------------------------------------+
| low                                               |
| low                                               |
| high                                              |
| high                                              |
| high                                              |
| high                                              |
| high                                              |
| high                                              |
+---------------------------------------------------+
8 rows in set (0.00 sec)

mysql> select case salary when 1000 then 'low'
    -> when 2000 then 'mid'
    ->  else 'high' end as level from salary;
+-------+
| level |
+-------+
| low   |
| mid   |
| high  |
| high  |
| high  |
| high  |
| high  |
| high  |
+-------+
8 rows in set (0.01 sec)

5.mysql系统相关内容查询
mysql> select database();
+------------+
| database() |
+------------+
| test1      |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.44-log |
+------------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

6.IP地址相关查询
mysql> select inet_aton('192.168.1.1');
+--------------------------+
| inet_aton('192.168.1.1') |
+--------------------------+
|               3232235777 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(323235777);
+----------------------+
| inet_ntoa(323235777) |
+----------------------+
| 19.68.47.193         |
+----------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(3232235777);
+-----------------------+
| inet_ntoa(3232235777) |
+-----------------------+
| 192.168.1.1           |
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+------+
| col  |
+------+
| a,b  |
| a,d  |
| a,b  |
| a,c  |
| a    |
+------+
5 rows in set (0.00 sec)

mysql> alter table t rename t_oldtable;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t( ip varchar( 20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t(ip) values ('192.168.1.1'), ('192.168.1.3'), ('192.168.1.6'), ('192.168.1.10'), ('192.168.1.20'), ('192.168.1.30');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t;
+--------------+
| ipaddress    |
+--------------+
| 192.168.1.1  |
| 192.168.1.3  |
| 192.168.1.6  |
| 192.168.1.10 |
| 192.168.1.20 |
| 192.168.1.30 |
+--------------+
6 rows in set (0.00 sec)

mysql> select * from t where ip>='192.168.1.3' and ip<='192.168.1.20';
Empty set (0.00 sec)

mysql> select * from t where ip>='192.168.1.3' and ip<='192.168.1.20';
Empty set (0.00 sec)

mysql> select * from t where  inet_aton(ip)>=inet_aton('192.168.1.3') and  inet_aton(ip)<=inet_aton('192.168.1.20');
+--------------+
| ip           |
+--------------+
| 192.168.1.3  |
| 192.168.1.6  |
| 192.168.1.10 |
| 192.168.1.20 |
+--------------+
4 rows in set (0.01 sec)

7.密码相关函数
mysql> select password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select md5('123456');
+----------------------------------+
| md5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)