mysql 学习记要（五）-字符串、数值处理、日期、查询的逻辑处理、系统相关内容查询、IP地址相关、密码
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)
版权声明：本博客原创文章欢迎转载，请转载的朋友最好注明出处，谢谢大家。