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)
版权声明：本博客原创文章欢迎转载，请转载的朋友最好注明出处，谢谢大家。
欢迎大家阅读《mysql 学习记要（五）-字符串、数值处理、日期、查询的逻辑处理、系统相关内容查询、IP地址相关、密码_mysql》,跪求各位点评，by 搞代码
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：mysql 学习记要（五）-字符串、数值处理、日期、查询的逻辑处理、系统相关内容查询、IP地址相关、密码_mysql
转载请注明原文链接：mysql 学习记要（五）-字符串、数值处理、日期、查询的逻辑处理、系统相关内容查询、IP地址相关、密码_mysql
