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

1. 1.字符串函数  
2. mysql> use test1;  
3. 
   
4.   
5. Database changed  
6. mysql> select concat('aaa','bbb','ccc'),concat('aaa',null);  
7. +---------------------------+--------------------+  
8. | concat('aaa','bbb','ccc') | concat('aaa',null) |  
9. +---------------------------+--------------------+  
10. | aaabbbccc                 | NULL               |  
11. +---------------------------+--------------------+   
12.   
13. mysql> select insert('beijing200&you',12,3,'me');  
14. +------------------------------------+  
15. | insert('beijing200&you',12,3,'me') |  
16. +------------------------------------+  
17. | beijing200&me                      |  
18. +------------------------------------+   
19.   
20. mysql> select lower('BEIJING2008'),UPPER('beijing2008');  
21. +----------------------+----------------------+  
22. | lower('BEIJING2008') | UPPER('beijing2008') |  
23. +----------------------+----------------------+  
24. | beijing2008          | BEIJING2008          |  
25. +----------------------+----------------------+  
26.   
27. mysql> select left('beijing2008',7),left('beijing',null),right('beijing2008',4);  
28. +-----------------------+----------------------+------------------------+  
29. | left('beijing2008',7) | left('beijing',null) | right('beijing2008',4) |  
30. +-----------------------+----------------------+------------------------+  
31. | beijing               | NULL                 | 2008                   |  
32. +-----------------------+----------------------+------------------------+    
33.   
34. mysql> select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');  
35. +---------------------------+---------------------------+  
36. | lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |  
37. +---------------------------+---------------------------+  
38. | beijingbeijingbe2008      | beijing2008200820082      |  
39. +---------------------------+---------------------------+  
40.   
41. mysql> select ltrim('  lbeijing'),rtrim('beijing!           ');  
42. +---------------------+------------------------------+  
43. | ltrim('  lbeijing') | rtrim('beijing!           ') |  
44. +---------------------+------------------------------+  
45. | lbeijing            | beijing!                     |  
46. +---------------------+------------------------------+  
47.   
48. mysql> select repeat('mysql  ',3);  
49. +-----------------------+  
50. | repeat('mysql  ',3)   |  
51. +-----------------------+  
52. | mysql  mysql  mysql   |  
53. +-----------------------+  
54.   
55. mysql> select replace('beijing_2010','_2010','2008');  
56. +----------------------------------------+  
57. | replace('beijing_2010','_2010','2008') |  
58. +----------------------------------------+  
59. | beijing2008                            |  
60. +----------------------------------------+  
61.   
62. mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');  
63. +-----------------+-----------------+-----------------+  
64. | strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |  
65. +-----------------+-----------------+-----------------+  
66. |              -1 |               0 |               1 |  
67. +-----------------+-----------------+-----------------+  
68.   
69. mysql> select trim('          $ beijing2008 $        ');  
70. +-------------------------------------------+  
71. | trim('          $ beijing2008 $        ') |  
72. +-------------------------------------------+  
73. | $ beijing2008 $                           |  
74. +-------------------------------------------+  
75.   
76. mysql> select substring('beijing2008',8,4),substring('beijing2008',1,7);  
77. +------------------------------+------------------------------+  
78. | substring('beijing2008',8,4) | substring('beijing2008',1,7) |  
79. +------------------------------+------------------------------+  
80. | 2008                         | beijing                      |  
81. +------------------------------+------------------------------+  
82.   
83. 2.数值处理函数  
84. mysql> select abs(-0.8),abs(0.8);  
85. +-----------+----------+  
86. | abs(-0.8) | abs(0.8) |  
87. +-----------+----------+  
88. |       0.8 |      0.8 |  
89. +-----------+----------+   
90.   
91. mysql> select ceil(-9.21),ceil(95.21355);  
92. +-------------+----------------+  
93. | ceil(-9.21) | ceil(95.21355) |  
94. +-------------+----------------+  
95. |          -9 |             96 |  
96. +-------------+----------------+  
97.   
98. mysql> select floor(-0.8),floor(0.32805);  
99. +-------------+----------------+  
100. | floor(-0.8) | floor(0.32805) |  
101. +-------------+----------------+  
102. |          -1 |              0 |  
103. +-------------+----------------+  
104.   
105. mysql> select mod(15,10),mod(1,11),mod(null,10);  
106. +------------+-----------+--------------+  
107. | mod(15,10) | mod(1,11) | mod(null,10) |  
108. +------------+-----------+--------------+  
109. |          5 |         1 |         NULL |  
110. +------------+-----------+--------------+  
111.   
112. mysql> select rand(),rand(),rand();  
113. +--------------------+--------------------+--------------------+  
114. | rand()             | rand()             | rand()             |  
115. +--------------------+--------------------+--------------------+  
116. | 0.7249892304884169 | 0.4090333165685025 | 0.8701989221109068 |  
117. +--------------------+--------------------+--------------------+  
118.   
119. mysql> select ceil(100*rand()),ceil(100*rand());  
120. +------------------+------------------+  
121. | ceil(100*rand()) | ceil(100*rand()) |  
122. +------------------+------------------+  
123. |               13 |                1 |  
124. +------------------+------------------+  
125.   
126. mysql> select round(1.1),round(1.1,2),round(1,2);  
127. +------------+--------------+------------+  
128. | round(1.1) | round(1.1,2) | round(1,2) |  
129. +------------+--------------+------------+  
130. |          1 |         1.10 |          1 |  
131. +------------+--------------+------------+  
132.   
133. mysql> select round(1.1),round(1.1,2),round(1.235251,2);  
134. +------------+--------------+-------------------+  
135. | round(1.1) | round(1.1,2) | round(1.235251,2) |  
136. +------------+--------------+-------------------+  
137. |          1 |         1.10 |              1.24 |  
138. +------------+--------------+-------------------+  
139.   
140. mysql> select round(1.1),round(1.1,2),round(1.2635251,2);  
141. +------------+--------------+--------------------+  
142. | round(1.1) | round(1.1,2) | round(1.2635251,2) |  
143. +------------+--------------+--------------------+  
144. |          1 |         1.10 |               1.26 |  
145. +------------+--------------+--------------------+  
146.   
147. mysql> select round(1.2356234234,2),truncate(1.2384235235,2);  
148. +-----------------------+--------------------------+  
149. | round(1.2356234234,2) | truncate(1.2384235235,2) |  
150. +-----------------------+--------------------------+  
151. |                  1.24 |                     1.23 |  
152. +-----------------------+--------------------------+  
153.   
154. 3.日期函数  
155. mysql> select curdate();  
156. +------------+  
157. | curdate()  |  
158. +------------+  
159. | 2015-10-02 |  
160. +------------+  
161.   
162. mysql> select curtime();  
163. +-----------+  
164. | curtime() |  
165. +-----------+  
166. | 09:40:21  |  
167. +-----------+  
168.   
169. mysql> select now();  
170. +---------------------+  
171. | now()               |  
172. +---------------------+  
173. | 2015-10-02 09:41:40 |  
174. +---------------------+  
175.   
176. mysql> select unix_timestamp(now());  
177. +-----------------------+  
178. | unix_timestamp(now()) |  
179. +-----------------------+  
180. |            1443804114 |  
181. +-----------------------+  
182.   
183. mysql> select from_unixtime(1443804114);  
184. +---------------------------+  
185. | from_unixtime(1443804114) |  
186. +---------------------------+  
187. | 2015-10-02 09:41:54       |  
188. +---------------------------+  
189.   
190. mysql> select week(now()),year(now());  
191. +-------------+-------------+  
192. | week(now()) | year(now()) |  
193. +-------------+-------------+  
194. |          39 |        2015 |  
195. +-------------+-------------+  
196.   
197. mysql> select hour(curtime()),minute(curtime());  
198. +-----------------+-------------------+  
199. | hour(curtime()) | minute(curtime()) |  
200. +-----------------+-------------------+  
201. |               9 |                42 |  
202. +-----------------+-------------------+  
203.   
204. mysql> select now();  
205. +---------------------+  
206. | now()               |  
207. +---------------------+  
208. | 2015-10-02 09:42:53 |  
209. +---------------------+  
210.   
211. mysql> select monthname(now());  
212. +------------------+  
213. | monthname(now()) |  
214. +------------------+  
215. | October          |  
216. +------------------+  
217.   
218. mysql> select date_format(now(),'%M,%D,%Y');  
219. +-------------------------------+  
220. | date_format(now(),'%M,%D,%Y') |  
221. +-------------------------------+  
222. | October,2nd,2015              |  
223. +-------------------------------+   
224.   
225. mysql> select now() current;  
226. +---------------------+  
227. | current             |  
228. +---------------------+  
229. | 2015-10-02 09:44:33 |  
230. +---------------------+  
231.   
232. mysql> select date_add(now(),INTERVAL '1_2' year_month);  
233. +-------------------------------------------+  
234. | date_add(now(),INTERVAL '1_2' year_month) |  
235. +-------------------------------------------+  
236. | 2016-12-02 09:44:52                       |  
237. +-------------------------------------------+  
238.   
239. mysql> select date_add(now(),INTERVAL 31 day) ;  
240. +---------------------------------+  
241. | date_add(now(),INTERVAL 31 day) |  
242. +---------------------------------+  
243. | 2015-11-02 09:45:44             |  
244. +---------------------------------+  
245.   
246. mysql> select date_add(now(),INTERVAL 31 day) as after31days;  
247. +---------------------+  
248. | after31days         |  
249. +---------------------+  
250. | 2015-11-02 09:45:55 |  
251. +---------------------+  
252.   
253. mysql> select date_add(now(),interval '1_2' year_month) as after_oneyear_twomonth;  
254. +------------------------+  
255. | after_oneyear_twomonth |  
256. +------------------------+  
257. | 2016-12-02 09:46:27    |  
258. +------------------------+  
259.   
260. mysql> select datediff('2008-08-08',now());  
261. +------------------------------+  
262. | datediff('2008-08-08',now()) |  
263. +------------------------------+  
264. |                        -2611 |  
265. +------------------------------+  
266.   
267. 4.查询的逻辑处理  
268. mysql> use test1;  
269. Database changed  
270. mysql> create table salary(  
271.     -> userid int,  
272.     -> salart decimal(9,2));  
273.   
274. mysql> insert into salary values  
275.     -> (1,1000),  
276.     -> (2,2000),  
277.     -> (3,3000),  
278.     -> (4,4000),  
279.     -> (5,5000),  
280.     -> (6,6000),  
281.     -> (7,7000),  
282.     -> (1,null);  
283.   
284. mysql> select * from salary;  
285. +--------+---------+  
286. | userid | salart  |  
287. +--------+---------+  
288. |      1 | 1000.00 |  
289. |      2 | 2000.00 |  
290. |      3 | 3000.00 |  
291. |      4 | 4000.00 |  
292. |      5 | 5000.00 |  
293. |      6 | 6000.00 |  
294. |      7 | 7000.00 |  
295. |      1 |    NULL |  
296. +--------+---------+   
297.   
298. mysql> select if(salary>2000,'high','low') from salary;  
299. +------------------------------+  
300. | if(salary>2000,'high','low') |  
301. +------------------------------+  
302. | low                          |  
303. | low                          |  
304. | high                         |  
305. | high                         |  
306. | high                         |  
307. | high                         |  
308. | high                         |  
309. | low                          |  
310. +------------------------------+   
311.   
312. mysql> select ifnull(salary,0) from salary;  
313. +------------------+  
314. | ifnull(salary,0) |  
315. +------------------+  
316. |          1000.00 |  
317. |          2000.00 |  
318. |          3000.00 |  
319. |          4000.00 |  
320. |          5000.00 |  
321. |          6000.00 |  
322. |          7000.00 |  
323. |             0.00 |  
324. +------------------+  
325.   
326. mysql> select case when salary<=2000 then 'low' else 'high' end from salary;  
327. +---------------------------------------------------+  
328. | case when salary<=2000 then 'low' else 'high' end |  
329. +---------------------------------------------------+  
330. | low                                               |  
331. | low                                               |  
332. | high                                              |  
333. | high                                              |  
334. | high                                              |  
335. | high                                              |  
336. | high                                              |  
337. | high                                              |  
338. +---------------------------------------------------+  
339.   
340. mysql> select case salary when 1000 then 'low'  
341.     -> when 2000 then 'mid'  
342.     ->  else 'high' end as level from salary;  
343. +-------+  
344. | level |  
345. +-------+  
346. | low   |  
347. | mid   |  
348. | high  |  
349. | high  |  
350. | high  |  
351. | high  |  
352. | high  |  
353. | high  |  
354. +-------+   
355.   
356. 5.mysql系统相关内容查询  
357. mysql> select database();  
358. +------------+  
359. | database() |  
360. +------------+  
361. | test1      |  
362. +------------+  
363.   
364. mysql> select version();  
365. +------------+  
366. | version()  |  
367. +------------+  
368. | 5.5.44-log |  
369. +------------+    
370.   
371. mysql> select user();  
372. +----------------+  
373. | user()         |  
374. +----------------+  
375. | root@localhost |  
376. +----------------+  
377.   
378. 6.IP地址相关查询  
379. mysql> select inet_aton('192.168.1.1');  
380. +--------------------------+  
381. | inet_aton('192.168.1.1') |  
382. +--------------------------+  
383. |               3232235777 |  
384. +--------------------------+  
385.   
386. mysql> select inet_ntoa(323235777);  
387. +----------------------+  
388. | inet_ntoa(323235777) |  
389. +----------------------+  
390. | 19.68.47.193         |  
391. +----------------------+  
392.   
393. mysql> select inet_ntoa(3232235777);  
394. +-----------------------+  
395. | inet_ntoa(3232235777) |  
396. +-----------------------+  
397. | 192.168.1.1           |  
398. +-----------------------+  
399.   
400. mysql> select * from t;  
401. +------+  
402. | col  |  
403. +------+  
404. | a,b  |  
405. | a,d  |  
406. | a,b  |  
407. | a,c  |  
408. | a    |  
409. +------+  
410.   
411. mysql> alter table t rename t_oldtable;  
412.   
413. mysql> create table t( ip varchar( 20));  
414.   
415. 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');  
416.   
417. mysql> select * from t;  
418. +--------------+  
419. | ipaddress    |  
420. +--------------+  
421. | 192.168.1.1  |  
422. | 192.168.1.3  |  
423. | 192.168.1.6  |  
424. | 192.168.1.10 |  
425. | 192.168.1.20 |  
426. | 192.168.1.30 |  
427. +--------------+  
428.   
429. mysql> select * from t where ip>='192.168.1.3' and ip<='192.168.1.20';   
430.   
431. mysql> select * from t where ip>='192.168.1.3' and ip<='192.168.1.20';   
432.   
433. mysql> select * from t where  inet_aton(ip)>=inet_aton('192.168.1.3') and  inet_aton(ip)<=inet_aton('192.168.1.20');  
434. +--------------+  
435. | ip           |  
436. +--------------+  
437. | 192.168.1.3  |  
438. | 192.168.1.6  |  
439. | 192.168.1.10 |  
440. | 192.168.1.20 |  
441. +--------------+  
442.   
443. 7.密码相关函数  
444. mysql> select password('123456');  
445. +-------------------------------------------+  
446. | password('123456')                        |  
447. +-------------------------------------------+  
448. | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |  
449. +-------------------------------------------+  
450.   
451. mysql> select md5('123456');  
452. +----------------------------------+  
453. | md5('123456')                    |  
454. +----------------------------------+  
455. | e10adc3949ba59abbe56e057f20f883e |  
456. +----------------------------------+  
457.