未命名

Size: px

Start display at page:

Download "未命名"

直吴
5 years ago
Views:

1 - MySQL Wing init MySQL MySQL MySQL

2 COALESCE(): NULL : wing 11:23:22> select coalesce(null,null,1,2,3,'test') ; coalesce(null,null,1,2,3,'test') root@localhost : wing 11:39:14> select coalesce(null,'test',null,1,2); coalesce(null,'test',null,1,2) test root@localhost : wing 11:42:54> select coalesce('test',null,1,2); coalesce('test',null,1,2) test GREATEST():, NULL, NULL root@localhost : wing 11:45:24> select greatest(1,2,null,3,4); greatest(1,2,null,3,4) NULL root@localhost : wing 11:45:29> select greatest(1,2,3,4); greatest(1,2,3,4) 4

3 : wing 11:45:33> select greatest(1,2,3,4,'test'); greatest(1,2,3,4,'test') 4 1 row in set, 1 warning (0.00 sec) Warning (Code 1292): Truncated incorrect DOUBLE value: 'test' IN(): IN INTERVAL():,, NULL, -1 root@localhost : wing 11:47:18> select interval(null,1,2,3,4); interval(null,1,2,3,4) root@localhost : wing 11:54:34> select interval(2,1,2,3,4); interval(2,1,2,3,4) 2 ISNULL(): NULL root@localhost : wing 11:54:39> select isnull(null); isnull(null)

4 : wing 11:57:34> select isnull(0); isnull(0) root@localhost : wing 11:57:37> select isnull('test'); isnull('test') 0 LEAST():, NULL, NULL root@localhost : wing 11:57:44> select least(null,1,2,3); least(null,1,2,3) NULL root@localhost : wing 12:00:05> select least(3,2,7); least(3,2,7) root@localhost : wing 12:00:13> select least(3,2,7,'test'); least(3,2,7,'test') 0 1 row in set, 1 warning (0.00 sec) Warning (Code 1292): Truncated incorrect DOUBLE value: 'test' root@localhost : wing 12:00:22> select least(3,2,7,null,'test'); least(3,2,7,null,'test')

5 NULL STRCMP(expr1,expr2) expr1 expr1=expr2 0 expr1>expr2 1 root@localhost : wing 12:08:48> select strcmp('tast','test'); strcmp('tast','test') -1 root@localhost : wing 12:08:52> select strcmp('test','test'); strcmp('test','test') 0 root@localhost : wing 12:08:54> select strcmp('test','tast'); strcmp('test','tast') 1 1. ':=' =,':=', ':=' SQL 2. '=' SET '=' ':=' EXAMPLE

6 # 未赋值成功,= 相当于比较操作 : wing 01:10:02> NULL # 赋值成功 root@localhost : wing 01:25:05> CASE sql CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result...] [ELSE result] END sql CASE WHEN [condition] THEN result [WHEN [condition] THEN result...] [ELSE result] END root@localhost : wing 02:07:15> select * from fun_data; id name MySQL 2 Oracle 3 Python NULL SHELL 5 NULL rows in set (0.00 sec) # CASE WHEN... 语句句实战演练 root@localhost : wing 02:07:14> select case when id is null then 0 else

7 id end from fun_data; case when id is null then 0 else id end rows in set (0.00 sec) # CASE value WHEN... 语句句实战演练 root@localhost : wing 02:08:46> select case name when 'SHELL' then 'LIN UX' else name end from fun_data; case name when 'SHELL' then 'LINUX' else name end MySQL Oracle Python LINUX NULL rows in set (0.00 sec) IF(expr1,expr2,expr3) expr1 TRUE, expr1<>0 expr1<>null, expr2; expr1 FALSE, exor1 is null expr1=0, expr3 IFNULL(expr1,expr2) expr1 NULL, expr2; expr1 NULL, expr1 root@localhost : wing 04:15:44> select ifnull(null,0); ifnull(null,0) 0 root@localhost : wing 04:34:18> select ifnull(3,0);

8 ifnull(3,0) 3 root@localhost : wing 04:34:23> select ifnull('test',0); ifnull('test',0) test root@localhost : wing 04:34:30> select ifnull(0,1); ifnull(0,1) 0 NULLIF(expr1,expr2) expr1=exor2, NULL; expr1<>expr2, expr1 root@localhost : wing 04:37:56> select nullif(null,null); nullif(null,null) NULL root@localhost : wing 04:39:06> select nullif(1024,1024); nullif(1024,1024) NULL root@localhost : wing 04:39:18> select nullif(1024,512); nullif(1024,512)

9 : wing 04:39:24> select nullif(512,1024); nullif(512,1024) LENGTH() CHAR_LENGTH() BIT_LENGTH() : wing 04:48:06> select char_length('mysql 数据库 '); char_length('mysql 数据库 ') root@localhost : wing 04:48:21> select length('mysql 数据库 '); length('mysql 数据库 ') 14 root@localhost : wing 04:48:25> select bit_length('mysql 数据库 '); bit_length('mysql 数据库 ')

10 CONCAT() : wing 04:50:15> select concat('mysql',' 数据库 '); concat('mysql',' 数据库 ') MySQL 数据库 ELT(expr1,expr2...) expr1 expr2... : wing 05:23:19> select elt(1,'mysql','oracle','python') ; elt(1,'mysql','oracle','python') MySQL root@localhost : wing 05:23:41> select elt(2,'mysql','oracle','python') ; elt(2,'mysql','oracle','python') Oracle root@localhost : wing 05:23:45> select elt(0,'mysql','oracle','python') ; elt(0,'mysql','oracle','python') NULL root@localhost : wing 05:23:49> select elt(5,'mysql','oracle','python') ;

11 elt(5,'mysql','oracle','python') NULL : wing 05:23:54> select elt(,'mysql','oracle','python'); ERROR 1064 (42000): You have an error in your SQL syntax; check the man ual that corresponds to your MySQL server version for the right syntax to use near ''MySQL','Oracle','Python')' at line 1 root@localhost : wing 05:23:59> select elt(null,'mysql','oracle','pytho n'); elt(null,'mysql','oracle','python') NULL FORMAT(X,D),X #,###,###.##,D root@localhost : wing 05:37:31> select format( ,4); format( ,4) 837,485, root@localhost : wing 05:37:48> select format( ); ERROR 1064 (42000): You have an error in your SQL syntax; check the man ual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 root@localhost : wing 05:37:54> select format( ,0); format( ,0) 837,485,938 INSERT(str,x,y,instr) str x y instr

12 ```sql mysql> select * from t; vc vcvcvcvcvc wingwing 2 rows in set (0.00 sec) mysql> select insert(vc,3,5,'fianna') from t; insert(vc,3,5,'fianna') vcfiannacvc wifiannag rows in set (0.04 sec) ``` LCASE() && LOWER() LCASE() LOWER(), root@localhost : wing 05:37:58> select lower('mysql'); lower('mysql') mysql LEFT(str,len) && RIGHT(str,len) str len, str len

13 sql : wing 05:41:08> select left('mysql',1),right('mysql',1); left('mysql',1) RIGHT('MySQL',1) M L LOCATE(substr,str),LOCATE(substr,str,pos) LOCATE(substr,str) substr str LOCATE(substr,str,pos) sunstr str pos POSITION() ```sql root@localhost : wing 05:55:13> select locate('sql','mysqlsql'); locate('sql','mysqlsql') 3 root@localhost : wing 05:55:18> select locate('sql','mysqlsql'); locate('sql','mysqlsql') 3 root@localhost : wing 05:55:26> select locate('sql','mysqlsql',4); locate('sql','mysqlsql',4) ```

14 LPAD(str,n,pad) pad str n ```sql mysql> select * from t; vc vc wing rows in set (0.01 sec) mysql> select lpad(vc,5,'pad') from t; lpad(vc,5,'pad') padvc pwing rows in set (0.00 sec) ``` RPAD(str,n,pad) pad str n ```sql mysql> select * from t; vc vc wing rows in set (0.01 sec) mysql> select rpad(vc,5,'mysql') from t; rpad(vc,5,'mysql')

15 vcmys wingm rows in set (0.00 sec) ##### LTRIM() && RTRIM() && TRIM() LTRIM() 返回删除左边空格的字符串串 RTRIM() 返回删除右边空格的字符串串 TRIM() 返回删除左右空格的字符串串 ```sql root@localhost : wing 06:12:24> select ltrim(' MySQL '); ltrim(' MySQL ') MySQL root@localhost : wing 06:12:31> select rtrim(' MySQL '); rtrim(' MySQL ') MySQL root@localhost : wing 06:12:34> select trim(' MySQL '); trim(' MySQL ') MySQL repeat(str,count) count str ```sql root@localhost : wing 06:04:49> select repeat('mysql',2); repeat('mysql',2) MySQLMySQL

16 : wing 06:04:56> select repeat(null,2); repeat(null,2) NULL 1 row in set (0.00 sec ``` replace(str,from_str,to_str) str from_str to_str root@localhost : wing 06:07:13> Select replace(' 'MySQL'); replace(' reverse(str) str root@localhost : wing 06:08:26> select reverse('mysql'); reverse('mysql') LQSyM SPACE(n) n root@localhost : wing 06:12:38> select space(6);

17 space(6) : wing 06:15:52> select space(100); space(100) SUBSTRING(str,m,n) str m n ```sql mysql> select * from t; vc vcvcvcvcvc wingwing 2 rows in set (0.00 sec) mysql> select substring(vc,3,5) from t; substring(vc,3,5) vcvcv ngwin 2 rows in set (0.00 sec) ``` UCASE() && UPPER()

18 str : wing 06:16:11> select UCASE('python'),UPPER('python'); UCASE('python') UPPER('python') PYTHON PYTHON CEIL() & CEILING(), : wing 04:18:06> select ceil(9.99); ceil(9.99) 10 : wing 04:18:20> select ceiling(9.99); ceiling(9.99) : wing 04:18:24> select ceil(null); ceil(null) NULL : wing 04:18:29> select ceiling(null); ceiling(null) NULL

19 : wing 04:18:32> select ceil(0); ceil(0) root@localhost : wing 04:18:36> select ceiling(-9.99); ceiling(-9.99) -9 CRC32(), 32,, root@localhost : wing 04:21:43> select crc32('mysql'); crc32('mysql') root@localhost : wing 04:23:41> select crc32('mysql'); crc32('mysql') FLOOR() root@localhost : wing 04:41:32> select floor(1.23); floor(1.23) 1

20 : wing 04:41:39> select floor(0); floor(0) root@localhost : wing 04:41:42> select floor(null); floor(null) NULL root@localhost : wing 04:41:47> select floor(-1.23); floor(-1.23) POW(x,y) && POWER(x,y), x y root@localhost : wing 04:49:54> select power(2,4); power(2,4) 16 RAND() && RAND(N) 1., RAND() SQL RAND_SEED1 RAND_SEED2, RAND(), binlog_format=statement, RAND() ; 2. RAND() [0,1) ;

21 3. RAND(N), N, binlog RAND_SEED1 RAND_SEED2, ; root@localhost : wing 05:08:51> select * from rand_data; id rows in set (0.00 sec) # 在如下示例例中可观察到,rand() 的函数产生的随机数随机改变,rand(n) 的函数产生的随机数是确定值 root@localhost : wing 05:08:58> select id, rand() from rand_data; id rand() rows in set (0.00 sec) root@localhost : wing 05:09:08> select id, rand(1) from rand_data; id rand(1) rows in set (0.00 sec) root@localhost : wing 05:09:14> select id, rand() from rand_data; id rand() rows in set (0.00 sec) root@localhost : wing 05:09:21> select id, rand(1) from rand_data; id rand(1)

22 rows in set (0.00 sec) 1., # 获得 7 到 12 之间的随机整数 root@localhost : wing 05:43:44> select floor(7+rand()*5); floor(7+rand()*5) 7 root@localhost : wing 05:43:50> select floor(7+rand()*5); floor(7+rand()*5) 9 ROUND(X),ROUND(X,D) X D, D, 0 root@localhost : wing 05:50:05> select round(2.34); round(2.34) 2 root@localhost : wing 05:50:08> select round(2.78); round(2.78) 3 root@localhost : wing 05:50:11> select round(2.34,1); round(2.34,1)

23 : wing 05:50:17> select round(2.78,1); round(2.78,1) : wing 05:50:24> select round(2.5,0); round(2.5,0) SIGN(),-1,0 0,1 root@localhost : wing 05:52:04> select sign(-123); sign(-123) -1 root@localhost : wing 05:57:51> select sign(0); sign(0) root@localhost : wing 05:57:54> select sign(123); sign(123)

24 SQRT() : wing 09:18:43> select sqrt(16); sqrt(16) root@localhost : wing 09:18:49> select sqrt(-16); sqrt(-16) NULL root@localhost : wing 09:18:51> select sqrt(null); sqrt(null) NULL TRUNCATE(X,D) X D, root@localhost : wing 09:20:48> select truncate(1.7699,2); truncate(1.7699,2) ADDDATE()

25 ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days) : wing 10:16:38> select adddate(now(),interval 31 day); adddate(now(),interval 31 day) :16: : wing 10:16:44> select adddate(now(),31); adddate(now(),31) :16:50 ADDTIME(expr1,expr2) expr1 expr2, : wing 10:40:21> select now(),addtime(now(),'1 1:1:1'); now() addtime(now(),'1 1:1:1') :40: :41: CONVERT_TZ(dt,from_tz,to_tz) dt from_tz to_tz,,dt from_tz : wing 10:47:37> select now(),convert_tz(now(),'+08:00', '+00:00'),convert_tz(now(),'+02:00','+00:00'),convert_tz(now(),'+02:00','-02:00'); now() convert_tz(now(),'+08:00','+00:00') convert_t z(now(),'+02:00','+00:00') convert_tz(now(),'+02:00','-02:00') :49: :49: :49: :49:44

26 CURDATE() & CURRENT_DATE & CURRENT_DATE() : wing 10:56:47> select curdate(); curdate() CURRENT_TIME([fsp]) & CURTIMR() : wing 10:56:51> select current_time(6); current_time(6) :57: CURRENT_TIMESTAMP([fsp]) & CURRENT_TIMESTAMP & NOW() + root@localhost : wing 10:57:23> select current_timestamp(6); current_timestamp(6) :57: DATE(expr) expr

27 : wing 10:59:53> select date(now()); date(now()) DATEDIFF(expr1,expr2) expr1-expr2 root@localhost : wing 10:59:58> select datediff(now(),' '); datediff(now(),' ') DATE_ADD() & DATE_SUB() date DATE_ADD(date,INTERVAL expr unit) date DATE_SUB(date,INTERVAL expr unit) root@localhost : wing 11:08:07> select date_add(' :00:00', interval 5 day),date_sub(' :00:00',interval 5 day); date_add(' :00:00', interval 5 day) date_sub(' :00:00',interval 5 day) :00: :00: root@localhost : wing 11:11:32> select date_add(' :00:00', interval '5 2' day_hour),date_sub(' :00:00',interval '5 2'

28 day_hour); date_add(' :00:00', interval '5 2' day_hour) date_sub(' :00:00',interval '5 2' day_hour) :00: :00: : wing 11:11:40> select date_add(' :00:00', interval '5 2:2:2' day_second),date_sub(' :00:00',interval '5 2:2:2' day_second); date_add(' :00:00', interval '5 2:2:2' day_second) date _sub(' :00:00',interval '5 2:2:2' day_second) :02: :57: DATE_FORMAT(date,format) format format, : wing 11:22:35> select date_format(' ','%w %M %Y'); date_format(' ','%w %M %Y') Sunday January DAYNAME(date)

29 : wing 11:26:16> select dayname(' '); dayname(' ') Sunday DAYOFMONTH() & DAYOFWEEK & DAYOFYEAR() DAYOFMONTH() DAYOFWEEK(), DAYOFYEAR() root@localhost : wing 11:33:22> select dayofmonth(' '); dayofmonth(' ') 14 root@localhost : wing 11:34:30> select dayofweek(' '); dayofweek(' ') root@localhost : wing 11:35:41> select dayofyear(' '); dayofyear(' ') from_days(n) N, root@localhost : wing 12:01:34> SELECT FROM_DAYS(365);

30 FROM_DAYS(365) # from_days 从这个日期开始记起, 从 N=366 开始有效 root@localhost : wing 12:01:36> SELECT FROM_DAYS(366); FROM_DAYS(366) root@localhost : wing 12:01:39> SELECT FROM_DAYS(367); FROM_DAYS(367) root@localhost : wing 12:02:35> SELECT FROM_DAYS(3670); FROM_DAYS(3670) from_unixtime() FROM_UNIXTIME(unix_timestamp) FROM_UNIXTIME(unix_timestamp,format) root@localhost : wing 12:05:32> select from_unixtime(0); from_unixtime(0) :00:00

31 : wing 12:05:37> select from_unixtime(125); from_unixtime(125) :02:05 root@localhost : wing 12:05:48> select from_unixtime(125,'%w'); from_unixtime(125,'%w') Thursday get_format() root@localhost : wing 01:17:58> SELECT DATE_FORMAT(' ',GET_FOR MAT(DATE,'EUR')); DATE_FORMAT(' ',GET_FORMAT(DATE,'EUR')) # get_format() 函数不不可单独使用 root@localhost : wing 01:18:20> select GET_format(' ','EUR'); ERROR 1064 (42000): You have an error in your SQL syntax; check the man ual that corresponds to your MySQL server version for the right syntax to use near '' ','EUR')' at line 1 root@localhost : wing 01:19:41> SELECT DATE_FORMAT(' :00:0 0',GET_FORMAT(DATE,'EUR')); DATE_FORMAT(' :00:00',GET_FORMAT(DATE,'EUR'))

32 HOUR(time), 23 : wing 01:23:51> select now(); now() :24:00 : wing 01:24:00> select hour(now()); hour(now()) 13 : wing 01:24:27> select hour('356:24:24'); hour('356:24:24') 356 LAST_DAY(date) date : wing 01:48:06> select last_day(' '); last_day(' ') : wing 01:48:23> select last_day(' '); last_day(' ')

33 MAKEDATE() & MAKETIME() MAKEDATE(year,dayofyear) year,dayofyear MAKETIME(hour,minute,second) : wing 01:48:29> select makedate(2015,35); makedate(2015,35) : wing 01:52:37> select maketime(13,25,25); maketime(13,25,25) :25: MICROSECOND() & MINUTE() & MONTH() & SECOND() & TIME(),,,, MONTHNAME(date) date NOW() + now() root@localhost : wing 01:56:49> select now(),sleep(2),now(); now() sleep(2) now() :57: :57: row in set (2.00 sec) SEC_TO_TIME(seconds)

34 seconds : wing 01:57:02> select sec_to_time(125); sec_to_time(125) :02: STR_TO_DATE(str,format) str format : wing 02:01:40> select str_to_date('25,01,2015','%y,%m, %d'); str_to_date('25,01,2015','%y,%m,%d') row in set, 1 warning (0.00 sec) Warning (Code 1292): Truncated incorrect date value: '25,01,2015' root@localhost : wing 02:01:49> select str_to_date('2015,01,25','%y,%m, %d'); str_to_date('2015,01,25','%y,%m,%d') root@localhost : wing 02:02:04> select str_to_date('2015,1,2.5','%y,%m, %d'); str_to_date('2015,1,2.5','%y,%m,%d') row in set, 1 warning (0.00 sec) SUBDATE() & SUBTIME() SUBDATE(),

35 SUBDATE(date,INTERVAL expr unit) SUBDATE(expr,days) SUBTIME(), SUBTIME(expr1,expr2) : wing 02:08:20> select subdate(now(),5),subdate(now(),i nterval 5 hour),subtime(now(),' :00:00'); subdate(now(),5) subdate(now(),interval 5 hour) subtime(now(), ' :00:00') :08: :08:33 NULL SYSDATE([fsp]) SYSDATE() --sysdate-is-now,sysdate() now(),, # 以下均在未使用 --sysdate-is-now 时 root@localhost : wing 02:13:49> select now(),sysdate(),sleep(2),now(),s ysdate(); now() sysdate() sleep(2) now() sysdate() :14: :14: : 14: :14: row in set (2.00 sec) # 如上可见,now() 值是相同的, 因为 now() 是指语句句开始执行行时的时间值,sysdate() 是不不同的, 因为 sysdate() 指的是执行行 sysdate 时的时间值

36 TIMEDIFF(expr1,expr2) expr1-expr2 : wing 02:20:02> select timediff(time(now()),'08:55:00') ; timediff(time(now()),'08:55:00') :25: TIMESTAMP() & TIMESTAMPADD() & TIMESTAMPDIFF() TO_DAYS() & TO_SECONDS() TO_DAYS(), TO_SECONDS(), root@localhost : wing 02:25:47> select to_days(' '); to_days(' ') 1 root@localhost : wing 02:25:53> select to_days(' '); to_days(' ') root@localhost : wing 02:26:02> select to_seconds(' '); to_seconds(' ') UNIX_TIMESTAMP([date])

37 unix timestamp ' :00:00'UTC : wing 02:28:22> select unix_timestamp(' :00 :00'); unix_timestamp(' :00:00') UTC_DATE & UTC_DATE() & UTC_TIME & UTC_TIME() & UTC_TIMESTAMP & UTC_TIMESTAMP() UTC : wing 02:31:31> select utc_date(); utc_date() : wing 02:32:51> select utc_timestamp(); utc_timestamp() :32:57 : wing 02:32:57> select now(),utc_timestamp(); now() utc_timestamp() :33: :33: : wing 02:33:10> select utc_timestamp; utc_timestamp :34:10 : wing 02:34:10> select time(now()),utc_time;

38 time(now()) utc_time :35:58 06:35: WEEK(date[,mode]) date mode, : wing 02:35:58> select week(date(now())); week(date(now())) 34 WEEKDAY(date) date (0 1...) : wing 02:38:15> select weekday(date(now())); weekday(date(now()))

四川省普通高等学校

四川省普通高等学校四川省普通高等学校计算机应用知识和能力等级考试考试大纲 (2013 年试行版 ) 四川省教育厅计算机等级考试中心 2013 年 1 月目录一级考试大纲 1 二级考试大纲 6 程序设计公共基础知识 6 BASIC 语言程序设计 (Visual Basic) 9