|
Mysql一些維護命令 --------------------------------------------------------- -------------------使用mysql客戶端程序------------------- ---------------------------------------------------------
-------------------建立MySQL用戶帳戶-------------------
--登錄mysql mysql -h hostname -u username -p password; mysql --host=localhost --user=root --password
--創(chuàng)建賬戶并授予數(shù)據(jù)庫訪問權限 grant all on dbname.* to 'username'@'hostname' identified by 'password';
--查看MySQL幫助 mysql --help
-------------------創(chuàng)建數(shù)據(jù)庫表和樣表-------------------
--創(chuàng)建數(shù)據(jù)庫 create database dbname;
--切換當前使用數(shù)據(jù)庫 use dbname;
--創(chuàng)建表 create table limbs (thing varchar(20), legs int, arms int);
--插入數(shù)據(jù) insert into limbs(thing, legs, arms) values('human', 2, 2); insert into limbs(thing, legs, arms) values('insect', 6, 0); insert into limbs(thing, legs, arms) values('squid', 0, 10); insert into limbs(thing, legs, arms) values('octopus', 0, 8); insert into limbs(thing, legs, arms) values('fish', 0, 0); insert into limbs(thing, legs, arms) values('centipede', 100, 0); insert into limbs(thing, legs, arms) values('table', 4, 0); insert into limbs(thing, legs, arms) values('armchair', 4, 2); insert into limbs(thing, legs, arms) values('phonograph', 0, 1); insert into limbs(thing, legs, arms) values('tripod', 3, 0); insert into limbs(thing, legs, arms) values('Peg Leg Pete', 1, 2); insert into limbs(thing, legs, arms) values('space alien', null, null);
--查詢數(shù)據(jù) select * from limbs;
-------------------啟動和停止MySQL-------------------
--備份數(shù)據(jù)庫 mysqldump -h localhost -u username -p daname > dbname.sql mysqldump -h localhost -u cbuser -p cookbook > cookbook.sql
mysqldump -h srchost -uusername -ppassword srcdbname | mysql -hdsthost -uusername -p dstdbname mysqldump -h192.168.62.68 -uroot -p** text | mysql -h127.0.0.1 -uroot -p test mysqldump -h192.168.71.137 -uroot -p** love | mysql -h127.0.0.1 -uroot -p love
--shutdown數(shù)據(jù)庫服務器 mysqladmin -p -u root shutdown
--退出MySQL quit; exit;
--操作系統(tǒng)服務方式啟動停止mysql(vista\win7管理員方式啟動cmd) net start mysql net stop mysql
--操作系統(tǒng)服務方式啟動停止mysql(linux管理員方式啟動cmd) service mysqld start service mysqld stop
-------------------使用可選項文件來指定連接參數(shù)-------------------
--通用客戶端程序連接選項 [client] host=localhost user=cbuser password=cbpass
--mysql特定選項 [mysql] skip-auto-rehash
--查看從選項文件讀取哪些選項 mysql --print-defaults
--檢查從選項文件讀取了什么值 my_print_defaults client mysql
-------------------保護選項文件以阻止其他用戶讀取------------------- chmod 60 .my.cnf chmod go-rwx .my.cnf
-------------------發(fā)起sql語句-------------------
--分號；結束 select * from tables;
--\g結束 select * from table\g
-------------------取消一條部分輸入的語句-------------------
--\c取消 select * from tables where fun \c
--/* */注釋 select * /* this is comments*/ from tables where fun \c
-------------------重復和編輯sql語句------------------- up 向上翻動語句的歷史 down 向下翻動語句的歷史 left 行內(nèi)左移 right 行內(nèi)右移 backspace 刪除前一個字符
-------------------自動完成數(shù)據(jù)庫和表名-------------------
-------------------讓mysql從文件中讀取語句----------------- --cmd從sql文件中讀取 mysql dbname < filename mysql cookbook < limbs.sql
--mysql會話內(nèi)從sql文件讀取 source filename \. filename
source testscore.sql \. testscore.sql
--測試.sql文件中調(diào)用腳本文件可嵌套層數(shù) create table counter (depth int); insert into counter set depth = 0;
--loop.sql文件內(nèi)容 update counter set depth = depth + 1; select depth from counter; source loop.sql;
--cmd調(diào)用loop.sql文件 mysql cookbook < loop.sql
--mysql會話調(diào)用loop.sql文件 source loop.sql \. loop.sql
--cmd查看系統(tǒng)錯誤號描述 perror 24
-------------------讓mysql從其他程序讀取語句----------------- --cmd從pipe（管道）讀取語句 type filename | mysql dbname /*cmd*/ cat filename | mysql dbname /*shell*/
type limbs.sql | mysql cookbook cat limbs.sql | mysql cookbook
--結合mysqldump和mysql拷貝一個數(shù)據(jù)庫到另外一個mysql服務器 mysqldump dbname | mysql -h some.other.host.com other_dbname mysqldump cookbook | mysql -h other.host cookbook
--使用程序生成sql語句發(fā)送到mysql generate-test-data | mysql cookbook
-------------------一行輸入所有sql-----------------
--cmd一行輸入sql mysql -e "statement1" dbname -u username -p password mysql -e "select count(*) from limbs" cookbook -uroot -p password
--cmd一行輸入多個sql mysql -e "statement1; statement2" dbname -u username -p password mysql -e "select count(*) from limbs;,select now()" cookbook -uroot -p password
-------------------預防查詢輸出超出屏幕范圍-----------------
--為mysql設置分頁顯示程序 mysql --pager=/usr/bin/less mysql --pager /*已經(jīng)設置環(huán)境變量pager*/
--mysql命令行開啟關閉分頁功能 \p /*開啟分頁功能*/ \p /usr/bin/less \n /*關閉分頁功能*/
-------------------發(fā)送查詢輸出到文件或程序-----------------
--交互模式下表格顯示結果集數(shù)據(jù) select * from limbs
--非交互模式下制表符顯示結果集數(shù)據(jù) echo select * from limbs | mysql cookbook
--將mysql輸出保存到文件 mysql dbname > outputfile statement1; statement2; exit
mysql cookbook > d:\result.txt select * from limbs; select * from testscore; exit
--從指定文件讀入sql，將結果保存到指定文件 mysql dbname < inputfile > outfile mysql cookbook < test.txt > d:\result.txt
--從指定文件讀入sql，將結果發(fā)送到另一程序 mysql dbname < inputfile | mail paul mysql dbname < test.txt | more
mysql cookbook < test.txt | find "alien" /*cmd*/ mysql cookbook < test.txt | grep "alien" /*linux*/
-------------------選擇表格或制表符定界的查詢輸出格式----------------- mysql -t dbname < inputfile mysql -t cookbook < test.txt
mysql -t dbname < inputfile | program mysql -t cookbook < test.txt | find "a"
-------------------指定任意的輸出列分隔符----------------- mysql cookbook < test.sql | sed -e "s/TAB/:/g" > result.txt mysql cookbook < test.sql | tr " TAB" ":" > result.txt mysql cookbook < test.sql | tr "\011" ":" > result.txt
-------------------生成xml或html----------------- mysql -H -e "statement" dbname > outfile mysql -X -e "statement" dbname > outfile
mysql -H -e "select * from limbs" cookbook > demohtml.txt mysql -X -e "select * from limbs" cookbook > demoxml.txt
-------------------使長輸出行更具有可讀性-----------------
--mysql命令行垂直顯示結果集 show full columns from tables; show full columns from tables\g show full columns from tables\G /*局部控制垂直顯示方式*/
--cmd命令行垂直顯示結果集 mysql -E /*全局控制垂直顯示方式*/
-------------------控制mysql的繁冗級別----------------- echo select now() | mysql /*cmd*/ echo select now() | mysql -v /*cmd*/ echo select now() | mysql -vv /*cmd*/ echo select now() | mysql -vvv /*cmd*/
echo "select now()" | mysql /*linux*/ echo "select now()" | mysql -v /*linux*/ echo "select now()" | mysql -vv /*linux*/ echo "select now()" | mysql -vvv /*linux*/
-------------------記錄交互式的mysql會話-----------------
--cmd命令行記錄交互日志到指定文件 mysql -tee=outfile dbname mysql -tee=tmp.out cookbook
--\T開啟交互日志功能、\t關閉日志交互功能
-------------------以之前執(zhí)行的語句創(chuàng)建mysql腳本-----------------
--使用mysql執(zhí)行語句歷史文件.mysql_history(位于用戶home目錄下) cd; cat .mysql_history
-------------------在sql語句中使用用戶自定義的變量-----------------
--將select列值賦予變量 select @class_id := id from t_classes where id = 2; delete from t_student where classesid = @class_id; delete from t_classes where id = @class_id;
--將表達式至賦予變量 select @max_limbs := max(arms+legs) from limbs;
--將含有auto_increment列的表插入新行之后的last_insert_id()的結果賦給變量 select @last_id := last_insert_id();
--如果查詢返回值含有多行，最后一行的值賦予變量 select @name := thing from limbs where legs = 0; select @name;
--如果語句沒有返回任何行，變量保持先前值，如果先前沒有賦值，則為null select @name2 := thing from limbs where legs < 0;
--使用set顯式為某個變量指定特定值 set @sum = 4 + 7; select @sum;
set @sum2 := 3 + 2; select @sum2;
--使用set將一個select結果賦給變量 set @max_limbs = (select max(arms+legs) from limbs);
--變量名不區(qū)分大小寫 set @x = 1, @X = 2; select @x, @X;
-------------------為查詢輸出行計數(shù)----------------- --linux命令行--skip-column-names結合cat -n mysql --skip-column-names -e "select thing, arms from limbs" cookbook | cat -n
--mysql命令行使用自定義變量 set @n = 0; select @n := @n + 1 as rownum, thing, arms, legs from limbs;
-------------------將mysql用作計算器----------------- select (17 + 24) / sqrt(64); select "ABC" = "abc"; select "ABC" = "abcd";
set @daily_room_charge = 100.00; set @num_of_nights = 3; set @tax_percent = 8; set @total_room_charge = @daily_room_charge * @num_of_nights; set @tax = (@total_room_charge * @tax_percent) / 100; set @total = @total_room_charge + @tax; select @total;
-------------------在unix下寫shell腳本-----------------
--shell腳本查看mysql服務器正常運行時間 #!/bin/sh # mysql_uptime.sh - report server uptime in seconds mysql --skip-column-names -B -e "SHOW /*!50002 GLOBAL */ STATUS LIKE 'Uptime'"
#!/bin/sh # mysql_uptime2.sh - report server uptime mysql -e STATUS | grep "^Uptime"
#!/bin/sh # mysql_uptime3.sh - report server uptime echo STATUS | mysql | grep "^Uptime"
--mysql命令行查看mysql服務器正常運行時間 status
--win環(huán)境下安裝類unix命令行環(huán)境 cygnus uwin
--------------------------------------------------------- ---------------------從表中查詢數(shù)據(jù)---------------------- ---------------------------------------------------------
--查詢數(shù)據(jù)庫版本、當前會話默認數(shù)據(jù)庫名 select version(), database();
--創(chuàng)建表 CREATE TABLE mail ( t DATETIME, # when message was sent srcuser CHAR(8), # sender (source user and host) srchost CHAR(20), dstuser CHAR(8), # recipient (destination user and host) dsthost CHAR(20), size BIGINT, # message size in bytes INDEX (t) );
--插入數(shù)據(jù) INSERT INTO mail (t,srchost,srcuser,dsthost,dstuser,size) VALUES ('2006-05-11 10:15:08','saturn','barb','mars','tricia',58274), ('2006-05-12 12:48:13','mars','tricia','venus','gene',194925), ('2006-05-12 15:02:49','mars','phil','saturn','phil',1048), ('2006-05-13 13:59:18','saturn','barb','venus','tricia',271), ('2006-05-14 09:31:37','venus','gene','mars','barb',2291), ('2006-05-14 11:52:17','mars','phil','saturn','tricia',5781), ('2006-05-14 14:42:21','venus','barb','venus','barb',98151), ('2006-05-14 17:03:01','saturn','tricia','venus','phil',2394482), ('2006-05-15 07:17:48','mars','gene','saturn','gene',3824), ('2006-05-15 08:50:57','venus','phil','venus','phil',978), ('2006-05-15 10:25:52','mars','gene','saturn','tricia',998532), ('2006-05-15 17:35:31','saturn','gene','mars','gene',3856), ('2006-05-16 09:00:28','venus','gene','mars','barb',613), ('2006-05-16 23:04:19','venus','phil','venus','barb',10294), ('2006-05-17 12:49:23','mars','phil','saturn','tricia',873), ('2006-05-19 22:21:51','saturn','gene','venus','gene',23992) ;
----------------指定查詢列/從指定列中查詢----------------- select * from mail; select t, srchost, srcuser, dsthost, dstuser, size from mail; select t, srcuser, srchost, size from mail;
------------------------指定查詢行------------------------- select t, srcuser, srchost from mail where srchost = "venus"; select t, srcuser, srchost from mail where srchost like 's%'; select * from mail where srcuser = 'barb' and dstuser = 'tricia';
---------------------格式化顯示查詢結果---------------------- select t, srcuser, size from mail; select concat(monthname(t), ' ', dayofmonth(t), ', ', year(t)), srcuser, size from mail; select date_format(t, '%M %e, %Y'), srcuser, size from mail;
select date_format(t, '%M %e, %Y') as 'Date of message', srcuser as 'Message sendr', size as 'Number of bytes' from mail;
select '1+1+1' as 'The epression', 1+1+1 as 'The result';
select 1 as 'integer';
---------------------合并多列來構建復合值---------------------- select date_format(t, '%M %e, %Y') as date_sent, concat(srcuser, '@', srchost) as sender, concat(dstuser, '@', dsthost) as recipient, size from mail;
---------------------where表達式中的列別名---------------------- select t, srcuser, dstuser, size/1024 as kilobytes from mail where kilobytes > 500;
select t, srcuser, dstuser, size/1024 as kilobytes from mail where size/1024 > 500;
---------------------調(diào)試比較表達式---------------------- select * from mail where srcuser < 'c' and size > 500; select srcuser, srcuser < 'c', size, size > 500 from mail; select srcuser, srcuser < 'c', size, size > 500 from mail where srcuser < 'c' and size > 500;
---------------------使查詢結果唯一化--------------------- select srcuser from mail; select distinct srcuser from mail; select distinct year(t), month(t), dayofmonth(t) from mail; select count(distinct srcuser) from mail;
----------------------如何處理null值-----------------------
--創(chuàng)建表 CREATE TABLE taxpayer ( name CHAR(20), id CHAR(20) );
--插入數(shù)據(jù) INSERT INTO taxpayer (name,id) VALUES ('bernina','198-48'); INSERT INTO taxpayer (name,id) VALUES ('bertha',NULL); INSERT INTO taxpayer (name,id) VALUES ('ben',NULL); INSERT INTO taxpayer (name,id) VALUES ('bill','475-83');
select * from taxpayer; select * from taxpayer where id = null; select * from taxpayer where id != null; select * from taxpayer where id is null; select * from taxpayer where id is not null; select null = null, null <=> null;
select if(id is null, "unknown", id) as 'id' from taxpayer; select name, ifnull(id, 'unknown') as 'id' from taxpayer;
----------------------結果集排序----------------------- select * from mail where size > 1000000 order by size; select * from mail where dstuser = 'tricia' order by srchost, srcuser; select * from mail where size > 50000 order by size desc;
----------------------使用視圖簡化查詢----------------------- select date_format(t, '') as date_sent, concat(srcuser, '@', srchost) as sender, concat(dstuser, '@', dsthost) as recipient, size from mail;
create view mail_view as select date_format(t, '%M %e, %Y') as date_sent, concat(srcuser, '@', srchost) as sender, concat(dstuser, '@', dsthost) as recipient, size from mail;
select date_sent, sender, size from mail_view where size > 100000 order by size;
----------------------多表查詢-----------------------
--創(chuàng)建表 CREATE TABLE profile_contact ( profile_id INT UNSIGNED NOT NULL, # ID from profile table service CHAR(20) NOT NULL, # messaging service name contact_name CHAR(25) NOT NULL, # name to use for contacting person INDEX (profile_id) );
--插入數(shù)據(jù) INSERT INTO profile_contact VALUES (1, 'AIM', 'user1-aimid'), (1, 'MSN', 'user1-msnid'), (2, 'AIM', 'user2-aimid'), (2, 'MSN', 'user2-msnid'), (2, 'Yahoo', 'user2-yahooid'), (4, 'Yahoo', 'user4-yahooid') ;
select * from profile_contact order by profile_id, service;
select id, name, service, contact_name from profile inner join profile_contact on id = profile_id;
select * from profile_contact where profile_id = (select id from profile where name = 'Mort');
----------------------從查詢結果集頭或尾取出部分行----------------------- select * from profile; select * from profile limit 1; select * from profile limit 5; select * from profile order by birth limit 1; select * from profile order by birth desc limit 1;
select name, date_format(birth, '%m-%d') as birthday from profile order by birthday limit 1;
----------------------在結果集頭中間選取部分行----------------------- select * from profile order by birth limit 2, 1; select * from profile order by birth desc limit 2, 1;
select count(*) from profile; select * from profile order by name desc limit 0, 4; select * from profile order by name desc limit 4, 4; select * from profile order by name desc limit 8, 4;
select sql_calc_found_rows * from profile order by id limit 4; select found_rows();
----------------------選擇合適的limit參數(shù)-----------------------
--創(chuàng)建表 CREATE TABLE al_winner ( name CHAR(30), wins INT );
--初始化數(shù)據(jù) al_winner-2001.txt Mulder, Mark 21 Clemens, Roger 20 Moyer, Jamie 20 Garcia, Freddy 18 Hudson, Tim 18 Abbott, Paul 17 Mays, Joe 17 Mussina, Mike 17 Sabathia, C.C. 17 Zito, Barry 17 Buehrle, Mark 16 Milton, Eric 15 Pettitte, Andy 15 Radke, Brad 15 Sele, Aaron 15
--導入數(shù)據(jù)數(shù)據(jù) load data local infile 'al_winner-2001.txt' into table al_winner;
set @n = 0; set @val = 0; select @n:=if(@val=wins, @n, @n+1) as rank,name, @val:=wins as wins from al_winner order by wins desc, name;
selectname, wins from al_winner order by wins desc, name;
select name, wins from al_winner order by wins desc, name limit 4;
select name, wins from al_winner order by wins desc, name limit 3, 1;
select name, wins from al_winner where wins >= 18 order by wins desc, name;
select name, wins from al_winner where wins >= ( select wins from al_winner order by wins desc, name limit 3, 1 ) order by wins desc, name;
select distinct wins from al_winner order by wins desc, name limit 3, 1;
select name, wins from al_winner where wins >= 17 order by wins desc, name;
select name, wins from al_winner where wins >= ( select distinct wins from al_winner order by wins desc, name limit 3, 1 ) order by wins desc, name;
----------------------當limit需要“錯誤”的排列順序時做什么-----------------------
select name, birth from profile order by birth desc limit 4; select count(*) from profle; select name, birth from profile order by birth limit 6, 4;
select name, birth from ( select name, birth from profile order by birth desc limit 4 ) as t order by birth;
----------------------從表達式中計算limit值-----------------------
--limit只允許數(shù)字作為其參數(shù)，不能使用表達式 select * from profile limit 5+5; select * from profile limit @skip_count, @show_count;
--------------------------------------------------------- --------------------------表關聯(lián)------------------------- ---------------------------------------------------------
----------------------克隆表-----------------------------
--僅僅克隆表結構(不克隆源表外鍵定義) create table new_table like original_table; create table mail2 like mail; select * from mail2;
--轉移表數(shù)據(jù) insert into new_table select * from original_table; insert into mail2 select * from mail where srcuser = 'barb'; select * from mail2;
----------------------將查詢結果保存到表中-----------------------------
--如果目的表已經(jīng)存在 insert into dsttbl(i, s) select val, name from src_tal; insert into dst_tbl select * from src_tbl; /*目的表與源表結構相同，拷貝全部數(shù)據(jù)*/ insert into dst_tbl select * from src_tbl where val > 1000 and name like 'A%'; /*目的表與源表結構相同，拷貝部分數(shù)據(jù)*/ insert into dst_tbl(i, s) select count(*), name from src_tbl group by name; /*目的表與基于源表進行統(tǒng)計的結果結構相同，將源表統(tǒng)計結果插入目的表*/
--如果目的表不存在 create table dst_tbl select * from src_tbl; create table dst_tbl select * from src_tbl where 0; /*僅克隆表結構，不克隆約束、鍵等，不包含數(shù)據(jù)*/ create table dst_tbl select b, c from src_tbl; /*依照源表部分列建表*/ create table dst_tbl select c, b, a from src_tbl; /*改變源表列定義順序（假設源表為a、b、c）*/ create table dst_tbl (id int not null auto_increment), primary key(id)) select a, b, c from src_tbl; /*在create table子句中插入列定義，此列不存在于源表中*/ /* create table dst_tbl ( id int not null auto_increment), primary key(id) ) select a, b, c from src_tbl; */
create table hoho ( id int not null auto_increment, primary key (id) ) select * from limbs;
create table dst_tbl select inv_no, sum(unit_cost * quantity) as total_cost from src_tbl group by inv_no; /*在create table子句中為表達式賦予別名，此列不存在于源表中*/ /* create table dst_tbl select inv_no, sum(unit_cost * quantity) as total_cost from src_tbl group by inv_no; */
create table dst_tbl (primary key(id), index(state, city)) select * from src_tbl; /*根據(jù)源表定義目的表主鍵、索引，假設源表主鍵為id，state、city列上有索引*/
/*間接復制列屬性與默認值*/ create table dst_tbl (primary key(id)) select * from src_tbl; alter table dst_tbl modify id int unsigned not null auto_increment;
----------------------使用臨時表-----------------------------
--普通建表 create temporary table tbl_name(...)
--克隆表 create temporary table new_table like original_table;
--根據(jù)查詢結果建表 create temporary table tbl_name select ...;
--創(chuàng)建與已存在的表同名的臨時表，暫時屏蔽非臨時表 create temporary table mail select * from mail; select count(*) from mail; delete from mail; select count(*) from mail; drop table mail; select count(*) from mail; drop temporary table if exists tbl_name;
----------------------檢查或改變某個表的存儲引擎-----------------------------
--InnoDB BDB MyISAM --檢測information_schema select engine from information_schema.tables where table_schema = 'cookbook' and table_name = 'mail'; show table status\G show table status like 'mail'\G show create table mail\G
--改變表引擎 alter table mail engine = InnoDB; alter table mail engine = BDB; alter table mail engine = MyISAM;
--生成唯一的表名 drop table if exists tbl_name; select connection_id();
--------------------------------------------------------- ----------------------與字符串共舞----------------------- ---------------------------------------------------------
----------------------字符串屬性-------------------------
--查看系統(tǒng)支持字符集 show character set;
--查看字節(jié)長度、字符長度 set @s = convert('abc' using ucs2); select length(@s), char_length(@s);
set @s = convert('abc' using utf8); select length(@s), char_length(@s);
--查看collation show collation;
--查看特定字符集collation show collation like 'latin1%';
--collation排序 create table t (c char(3) character set latin1); insert into t (c) values('AAA'), ('bbb'), ('aaa'), ('BBB'); select c from t;
--大小寫不敏感 select c from t order by c collate latin1_swedish_ci;
--大小寫敏感 select c from t order by c collate latin1_general_cs;
--二進制數(shù)值排序 select c from t order by c collate latin1_bin;
create table t(c char(2) character set utf8); insert into t(c) values('cg'), ('ch'), ('ci'), ('lk'), ('ll'), ('lm'); select c from t order by c collate utf8_general_ci; select c from t order by c collate utf8_spanish2_ci;
----------------------選擇字符串數(shù)據(jù)類型-------------------------
----------------------------------------------------- 二進制數(shù)據(jù)類型 非二進制數(shù)據(jù)類型 最大長度 binary char 255 varbinary varchar 65535 tinyblob tinytext 255 blob text 65535 mediumblob mediumtext 16777215 longblog longtext 4294967295 -----------------------------------------------------
--char與varchar處理尾部空格的差異之處 create table t (c1 char(10), c2 varchar(10)); insert into t (c1, c2) values('abc ', 'abc '); select c1, c2, char_length(c1), char_length(c2) from t;
--指定列character set與collation create table mytbl ( utf8data varchar(100) character set utf8 collate utf8_danish_ci, sjisdata varchar(100) character set sjis collate sjis_japanese_ci );
--character-set-server --collation-server
----------------------正確設置客戶端連接的字符集-------------------------
--配置文件 [mysql] default-character-set=utf-8
--mysql命令行 set names 'utf8'; set names 'utf8' collate 'utf8-general-ci';
--編程接口:MySQL connectot/J, 應用程序連接URL jdbc:mysql://localhost/cookbook?characterEncoding=UTF-8
----------------------串字母-------------------------
--定義字符串 'my string' "my string" /*ansi_quotes 模式啟用時雙引號內(nèi)的字符解釋為表名或列名*/
--使用十六進制數(shù)標記abcd 0x6162364 x'6162364' X'6162364' insert into t set binary_col = 0xdeadbeef; /*sql中使用十六進制字符串指代二進制數(shù)值*/
--為字符串指定一個字符集解釋器 _latin1 'abcd' /*包含四個單字節(jié)字符的字符串*/ _ucs2 'abcd' /*包含兩個雙字節(jié)字符的字符串*/
--包含單引號的字符串 select "I'm asleep"; select 'I''m asleep'; select 'I\'m wide awake';
--包含雙引號的字符串 select 'He said, "Boo!"'; select "He said, ""Bob!"""; select "And I said \"Yikes!\"";
--包含\反斜線的字符串(特殊字符\b,\n,\r,\t,\0,\\) select 'Install MySQL in c:\\mysql on Windows';
--使用十六進制數(shù)表示字符串 select 0x49276D2061736C656570;
----------------------檢查一個字符串的字符集或字符排序-------------------------
--返回特定字符集和字符排序 select user(), charset(user()), collation(user());
--改變當前字符集影響普通字符串字符集和字符排序 set names 'latin1'; select charset('abc'), collation('abc'); set names latin7 collate 'latin7_bin'; select charset('abc'), collation('abc');
--返回二機制字符串 select charset(md5('a')), collation(md5('a')); select charset(password('a')), collation(password('a'));
--轉換二機制字符串結果大小寫將失敗 select upper(md5('a')), lower(md5('a'));
----------------------改變字符串的字符集或字符排序-------------------------
--改變字符集 set @s1 = "my string"; set @s2 = convert(@s1 using utf8); select charset(@s1), charset(@s2);
--改變collation set @s1 = 'my string'; set @s2 = @s1 collate latin1_spanish_ci; select collation(@s1), collation(@s2); select _latin1 'abc' collate utf8_bin; /*出錯，Collation對于字符串的字符集必須是合法的*/
set @s1 = "my string"; set @s2 = convert(@s1 using utf8) collate utf8_spanish_ci; /*正確，先轉換字符集，再轉換排序*/ select charset(@s1), collation(@s1), charset(@s2), collation(@s2);
--轉換二進制字符串和非二進制字符串 set @s1 = 'my string'; set @s2 = convert(@s1 using binary); set @s3 = convert(@s2 using utf8); select charset(@s1), charset(@s2), charset(@s3);
--binary操作符產(chǎn)生二進制字符串 set @s1 = 'my string'; set @s2 = binary @s2; /*與convert(str using binary)等效*/ select charset(@s1), charset(@s2);
----------------------更改字符串字母的大小寫-------------------------
--整體替換大小寫 select thing, upper(thing), lower(thing) from limbs;
--局部修改大小寫 select thing, concat(upper(left(thing, 1)), mid(thing, 2)) from limbs;
--自定義函數(shù)局部修改大小寫 create function initial_cap(s varchar(255)) returns varchar(255) deterministic return concat(upper(left(s, 1)), mid(s, 2));
select thing, initial_cap(thing) from limbs;
----------------------更改字符串字母的大小寫失敗處理情況-------------------------
--改變非二進制字符串大小寫 set @s = 'aBcD'; select upper(@s), lower(@s);
--改變二進制字符串大小寫 drop table if exists t; create table t(b blob) select 'aBcD' as b; select b, upper(b), lower(b) from t; /*upper、lower改變二進制字符串大小寫不生效*/
set @s = binary 'aBcD'; select @s, lower(@s), upper(@s); /*MySQL4.1之前生效*/
select b, upper(convert(b using latin1)) as upper, lower(convert(b using latin1)) as lower from t; /*先轉換二進制字符串到非二機制字符串，再進行大小寫轉換*/ select charset(version()), charset(md5('some string')); select upper(version()); select md5('some thing'), upper(convert(md5('some thing') using latin1)); ----------------------控制字符串比較中的大小寫敏感-------------------------
select 'cat' = 'cat', 'cat' = 'dpg'; select 'cat' != 'cat', 'cat' != 'dpg'; select 'cat' < 'awk', 'cat' < 'dpg'; select 'cat' between 'awk' and 'egret';
set @s1 = binary 'cat', @s2 = binary 'CAT'; select @s1 = @s2; /*二進制字符串比較大小寫敏感，結果不等*/ set @s1 = convert(@s1 using latin1) collate latin1_swedish_ci; set @s2 = convert(@s2 using latin1) collate latin1_swedish_ci; select @s1 = @s2; /*轉換成非二進制字符串，并指定字符集collation大小寫不敏感，結果相等*/
/*latin1默認collation為latin1_swedish_ci*/ set @s1 = convert(@s1 using latin1); set @s2 = convert(@s2 using latin1); select @s1 = @s2;
set @s1 = _latin1 'cat', @s2 = _latin1 'CAT'; select @s1 = @s2;
select @s1 collate latin1_general_cs = @s2 collate latin1_general_cs as '@s1 = @s2';
select _latin1 'cat' = binary 'CAT';
set @s1 = _latin1 'cat', @s2 = _latin1 'CAT'; select @s1 = @s2, binary @s1 = @s2, @s1 = binary @s2;
--修改表定義，改變列大小寫敏感模式 create table new ( id int unsigned not null auto_increment, article blob, /*大小寫敏感*/ primary key(id) );
alter table news modify article text charcter set utf8 collate utf_general_ci; /*大小寫不敏感*/
----------------------使用sql模式進行模式匹配-------------------------
--消除可能存在的表沖突 drop table if exists metal;
--創(chuàng)建表 create table metal (name varchar(20));
--初始化表數(shù)據(jù) insert into metal (name) values('copper'), ('gold'), ('iron'), ('lead'), ('mercury'), ('platinum'), ('silver'), ('tin');
--查詢初始化數(shù)據(jù) select * from metal;
--查詢以特定字符開頭的字符串 select name from metal where name like 'co%';
--查詢以特定字符結尾的字符串 select name from metal where name like '%er';
--查詢含有特定字符的字符串 select name from metal where name like '%er%';
--查詢特定位置出現(xiàn)特定字符 select name from metal where name like '__pp%';
--查詢不含有特定字符的字符串 select name from metal where name not like '%i%';
--Null值 select null like '%', null not like '%';
----------------------使用正則表達式進行模式匹配-------------------------
--模式字符與定義 模式字符 定義 ^ 匹配字符串的開始部分 $ 匹配字符串的結束部分 . 匹配任何字符（包括回車和新行） [...] 括號內(nèi)任意一個字符 [^...] 除了括號內(nèi)所列字符之外的任意一個字符 p1 | p2 | p3 p1、p2或p3中任意一個模式串 * 匹配0或多個*之前的任何序列 + 匹配1或多個+之前的任何序列 {n} n個{n}之前的任何序列 {m, n} 最少m個，最多n個，{m, n}之前的任何序列
--查詢以特定字符開頭的字符串 select name from metal where name regexp '^co';
--查詢以特定字符結尾的字符串 select name from metal where name regexp 'er$';
--查詢含有特定字符的字符串 select name from metal where name regexp 'er';
--查詢特定位置出現(xiàn)特定字符 select name from metal where name regexp '^..pp%';
--查詢不含有特定字符的字符串 select name from metal where name not like '%i%';
--posix字符集定義正則表達式 posix類 匹配定義 [:alnum:] 字符和數(shù)字 [:alpha:] 字母 [:blank:] 空格或制表符(tab) [:cntrl:] 控制符 [:digit:] 數(shù)字 [:graph:] 圖形符號(不包括空格) [:lower:] 小寫字母 [:print:] 圖形符號(包括空格) [:punct:] 標點符號 [:space:] 空格、制表符、換行、回車換行 [:upper:] 大寫字母 [:xdigit:] 十六進制符(0-9, a-f, A-F)
--檢查是否含有十六進制字符 select name, name regexp '[[:xdigit:]]' from metal;
--選擇性匹配 select name from metal where name regexp '^[aeiou]|er$';
--對正則表達式進行分組 select '0m' regexp '^[[:digit:]]+|[[:alpha:]]+$'; /*以數(shù)字開頭或字母結尾*/ select '0m' regexp '^([[:digit:]]+|[[:alpha:]]+)$'; /*完全由數(shù)字或者完全由字母組成*/
--Null值 select null regexp '.*', null not regexp '.*';
----------------------模式匹配大小寫問題-------------------------
select 'a' like 'A', 'a' regexp 'A'; select 'a' regexp '[[:lower:]]', 'a' regexp '[[:upper:]]';
set names latin1; set @s = 'a' collate latin1_general_cs; select @s like 'A', @s regexp 'A';
set @s = 'a', @s_cs = 'a' collate latin1_general_cs; select @s regexp '[[:upper:]]', @s_cs regexp '[[:upper:]];'
----------------------分割或者串聯(lián)字符串-------------------------
--取得左側、中間、右側字串 select name, left(name, 2), mid(name, 3, 1), right(name, 3) from metal;
--取得字串substring() select name, substring(name, 4), mid(name, 4) from metal;
--取得字串substring_index() select name, substring_index(name, 'r', 1), substring_index(name, 'i', -1); /*正數(shù)從左到右，負數(shù)從右到左*/ select name from metal where left(name, 1) >= 'n';
--拼接字符串concat() select concat('Hello', user(), ', welcome to MySQL!') as greeting; select concat(name, ' ends in "d": ', if(right(name, 1)='d', 'yes', 'no')) as 'ends in "d"?' from metal; update metal set name = concat(name, 'ide'); select name from metal;
update tbl_name set set_col = if(set_col is null, val, concat(set_col, ', ', val));
update metal set name = left(name, char_length(name) - 3); select name from metal;
----------------------查詢字串-------------------------
select name, locate('in', name), locate('in', name, 3) from metal;
----------------------使用fulltext查詢-------------------------
--創(chuàng)建表 create table kjv ( bsect enum('0', 'N') not null, bname varchar(20) not null, bnum tinyint unsigned not null, cnum tinyint unsigned not null, vnum tinyint unsigned not null, vtext text not null ) engine = MyISAM;
--導入初始化數(shù)據(jù) load data local infile 'kjv.txt' into table kjv;
--添加全文索引 alter table kjv add fulltext(vtext);
--查詢'Mizraim'一共出現(xiàn)了多少次 select count(*) from kjv where match(vtext) against('Mizraim');
--查詢'Mizraim'具體出現(xiàn)在什么地方 select bname, cnum, vnum, vtext from kjv where match(vtext) against('Mizraim')\G select bname, cnum, vnum, vtext from kjv where match(vtext) against('search string') order by bnum, cnum, vnum\G
select count(*) from kjv where match(vtext) against('Abraham') and bsect = 'N'; select count(*) from kjv where match(vtext) against('Abraham') and bname = 'Hebrews'; select count(*) from kjv where match(vtext) against('Abraham') and bname = 'Hebrews' and cnum = 11;
alter table kjv add index(bnum), add index(cnum), add index(vnum); select count(*) from kjv where match(vtext) against('Abraham'); select count(*) from kjv where match(vtext) against('Abraham Sarah'); select count(*) from kjv where match(vtext) against('Abraham Sarah Ishmael Isaac');
alter table tbl_name add fulltext(col1, col2, col3); select ... from tbl_name where match(col1, col2, col3) against('search string');
----------------------用短語來進行fulltext查詢-------------------------
select count(*) from kjv where match(vtext) against('God'); select count(*) from kjv where match(vtext) against('sin');
select count(*) as 'total verses', count(if(vtext like '%God%', 1, null)) as 'Verses containing "God"', count(if(vtext like '%sin%', 1, null)) as 'Verses containing "sin"' from kjv;
--修改配置文件/etc/my.cnf 或者mysqlhome/my.ini [mysqld] ft_min_world_len=3;
--重啟服務器啟用新的設置 repair table kjv quick;
select count(*) from kjv where match(vtext) against('God'); select count(*) from kjv where match(vtext) against('sin');
----------------------要求或靜止fulltext搜索單詞-------------------------
select count(*) from kjv where match(vtext) against('David Goliath'); select count(*) from kjv where match(vtext) against('+David +Goliath' in boolean mode); select count(*) from kjv where match(vtext) against('+David -Goliath' in boolean mode); select count(*) from kjv where match(vtext) against('-David +Goliath' in boolean mode); select count(*) from kjv where match(vtext) against('whirl*' in boolean mode);
----------------------用fulltext索引來執(zhí)行詞組查詢-------------------------
select count(*) from kjv where match(vtext) against('still small voice'); select count(*) from kjv where match(vtext) against('"still small voice"' in boolean mode);
--------------------------------------------------------- ----------------------使用日期或時間--------------------- ---------------------------------------------------------
----------------------選擇合適的提起或者時間變量類型-------------------------
--創(chuàng)建表、初始化數(shù)據(jù) DROP TABLE IF EXISTS date_val; CREATE TABLE date_val ( d DATE );
INSERT INTO date_val (d) VALUES('1864-02-28'); INSERT INTO date_val (d) VALUES('1900-01-15'); INSERT INTO date_val (d) VALUES('1987-03-05'); INSERT INTO date_val (d) VALUES('1999-12-31'); INSERT INTO date_val (d) VALUES('2000-06-04');
DROP TABLE IF EXISTS datetime_val; CREATE TABLE datetime_val ( dt DATETIME );
INSERT INTO datetime_val (dt) VALUES('1970-01-01 00:00:00'); INSERT INTO datetime_val (dt) VALUES('1987-03-05 12:30:15'); INSERT INTO datetime_val (dt) VALUES('1999-12-31 09:00:00'); INSERT INTO datetime_val (dt) VALUES('2000-06-04 15:45:30');
DROP TABLE IF EXISTS time_val; CREATE TABLE time_val ( t1 TIME, t2 TIME );
INSERT INTO time_val (t1,t2) VALUES('15:00:00','15:00:00'); INSERT INTO time_val (t1,t2) VALUES('05:01:30','02:30:20'); INSERT INTO time_val (t1,t2) VALUES('12:30:20','17:30:45');
DROP TABLE IF EXISTS time_val; CREATE TABLE time_val ( t1 TIME, t2 TIME );
INSERT INTO time_val (t1,t2) VALUES('15:00:00','15:00:00'); INSERT INTO time_val (t1,t2) VALUES('05:01:30','02:30:20'); INSERT INTO time_val (t1,t2) VALUES('12:30:20','17:30:45');
DROP TABLE IF EXISTS timestamp_val; CREATE TABLE timestamp_val ( ts TIMESTAMP );
INSERT INTO timestamp_val (ts) VALUES('1970-01-01 09:00:00'); INSERT INTO timestamp_val (ts) VALUES('1987-03-05 12:30:15'); INSERT INTO timestamp_val (ts) VALUES('1999-12-31 09:00:00'); INSERT INTO timestamp_val (ts) VALUES('2000-06-04 15:45:30');
--查看數(shù)據(jù) select * from date_val; select * from datetime_val; select * from time_val; select * from timestamp_val;
----------------------修改MySQL日期格式-------------------------
select str_to_date('May 13, 2007', '%M %d, %Y'); insert into date_val (d) values(str_to_date('May 13, 2007', '%M %d, %Y')); select * from date_val; select d, date_format(d, '%M %d, %Y') from date_val; select d, date_format(d, '%M %d, %Y') as date from date_val;
--格式化串定義 格式化字符 含義 %Y 年份，數(shù)字形式，4位數(shù) %y 年份，數(shù)字形式，2位數(shù) %M 完整度月份名稱（Hanuary-December） %b 月份名稱的前三個字母（Jan-Dec） %m 月份，數(shù)字形式（01..12） %c 月份，數(shù)字形式(1..12) %d 該月日期，數(shù)字形式（01..31） %e 該月日期，數(shù)字形式（1..31） %W 工作日名稱（Sunday..Saturday） %r 時間，12小時制，以AM或PM結尾 %T 時間，24小時制 %H 小時，數(shù)字形式，2位數(shù)（00..23） %i 分鐘，數(shù)字形式，2位數(shù)（00..59） %s 秒，數(shù)字形式，2位數(shù)（00..59） %% '%'文字字符
select dt, date_format(dt, '%c/%e/%y %r') as format1, date_format(dt, '%M %e, %Y %T') as format2 from datetime_val; select dt, time_format(dt, '%r') as '12-hour time', time_format(dt, '%T') as '24-hour time' from datetime_val;
delimiter $$ create function time_ampm(t time) returns varchar(13) # mm:dd:ss (a.m.|p.m.)格式 begin declare ampm char(4); if time_to_sec(t) < 12*60*60 then set ampm = 'a.m.'; else set ampm = 'p.m.'; end if; return concat(left(time_format(4, '%r'), 9), ampm); end; $$ delimiter ;
select t1, time_ampm(t1) from time_val;
----------------------設置客戶端時區(qū)-------------------------
--客戶端位于不同時區(qū)需要注意，如果位于同一時區(qū)則不需要關心 drop table if exists t; create table t (ts timestamp); insert into t (ts) values('2006-06-01 12:30:00'); select ts from t;
select @@global.time_zone, @@session.time_zone; select ts from t; set session time_zone = '+04:00'; select @@global.time_zone, @@session.time_zone; select ts from t;
----------------------獲取當前日期或時間-------------------------
select curdate(), curtime(), now(); select current_date(), current_time(), current_timestamp(); select utc_date(), utc_time(), utc_timestamp();
----------------------使用timestamp跟蹤行修改時間-------------------------
--timestamp初始化為當前日期，并隨行值改變而改變 drop table if exists t; create table t(ts timestamp); show create table t\G
drop table if exists tsdemo1; create table tsdemo1(ts timestamp, val int); insert into tsdemo1(val) values(5); insert into tsdemo1(ts, val) values(null, 10); select * from tsdemo1; update tsdemo1 set val = 6 where val = 5; select * from tsdemp1; update tsdemo1 set val = val + 1;
--timestamp初始化為當前日期，行值改變不改變 drop table if exists tsdemo2; create table tsdemo2(t_create timestamp default current_timestamp, val int); select * from tsdemo2;
insert into tsdemo2(val) values(5); insert into tsdemo2(t_create, val) values(null, 10); select * from tsdemo2;
update tsdemo2 set val = val + 1; select * from tsdemo2;
----------------------從日期或者時間值中分解出各部分值-------------------------
--使用成分分解函數(shù)來分解日期和時間值 select dt, date(dt), time(dt) from datetime_val;
--日期相關函數(shù) 函數(shù) 返回值 year() 月份數(shù)值 month() 月份名稱（1..12） monthname() 月份中的天數(shù)值（January..December） dayofmonth() 一周中的天數(shù)（1..31） dayname() 一周中的天數(shù)（Sunday..Saturday） dayofweek() 一周中的天數(shù)（1..7對應Sunday..Saturday） weekday() 一周中的天數(shù)（0..6對應Monday..Sunday） dayofyear() 一年中的天數(shù)值（1.366） hour() 時間中的小數(shù)值（0..23） minute() 時間中的分數(shù)值（0..59） second() 時間中的秒數(shù)（0..59）
select dt, year(dt), dayofmonth(dt), hour(dt), second(dt) from datetime_val; select d, dayofyear(d) from date_val; select d, dayname(d), left(dayname(d), 3) from date_val;
select d, dayname(d), dayofweek(d), weekday(d) from date_val; /*dayofweek 1~7星期天到星期六 weekday0~6星期一到星期天*/ select dt, extract(day from dt), extract(hour from dt) from datetime_val; /*year,month,day,hour,minute,second*/ select curdate(), year(curdate()) as year, month(curdate()) as month, monthname(curdate()) as monthname, dayofmonth(curdate()) as day, dayname(curdate()) as dayname; select now(), hour(now()) as hour, minute(now()) as minute, second(now()) as second;
--使用格式化分解日期或者時間值 select dt, date_format(dt, '%Y') as year, date_format(dt, '%d') as day, time_format(dt, '%H') as hour, time_format(dt, '%s') as second from datetime_val; select dt, date_format(dt, '%Y-%m-%d') as 'date part', time_format(dt, '%T') as 'time part' from datetime_val; select ts, date_format(ts, '%M %e, %Y') as 'descriptive date', time_format(ts, '%H:%i') as 'hours/minutes' from timestamp_val;
--使用字符串函數(shù)分解時間或者日期值 select dt, left(dt, 4) as year, mid(dt, 9, 2) as day, right(dt, 2) as second from datetime_val; select dt, left(dt, 10) as date, right(dt, 8) as time from datetime_val; select ts, left(ts, 10) as date, right(ts, 8) as time from timestamp_val;
----------------------合成日期或者時間-------------------------
select maketime(10,30,58), maketime(-5,0,11); select makedate(2007,60); select d, date_format(d, '%Y-%m-01') from date_val; select t1, time_format(t1, '%H:%i:00') from time_val; select d, concat(year(d), '-', month(d), '-01') from date_val; select d, concat(year(d), '-', lpad(month(d), 2, '0'), '-01') from date_val; select d, concat(year(d), '-', lpad(month(d), 4, '0'), '-01') from date_val; select t1, concat(lpad(hour(t1), 2, '0'), ':', lpad(minute(t1), 2, '0'), ':00') as recombined from time_val;
set @d = '2006-02-28'; set @t = '13:10:05'; select @d, @t, concat(@d, ' ', @t);
----------------------在時間數(shù)據(jù)類型和基本單位間進行轉換-------------------------
--在時間和秒之間進行轉換 select t1, time_to_sec(t1) as 'TIME to seconds', sec_to_time(time_to_sec(t1)) as 'TIME to seconds to TIME' from time_val; select t1, time_to_sec(t1) as 'seconds', time_to_sec(t1)/60 as 'minutes', time_to_sec(t1)/(60*60) as hours, time_to_sec(t1)/(24*60*60) as 'days' from time_val;
select t1, time_to_sec(t1) as 'seconds', floor(time_to_sec(t1)/60) as 'minutes', floor(time_to_sec(t1)/(60*60)) as hours, floor(time_to_sec(t1)/(24*60*60)) as 'days' from time_val;
select dt, time_to_sec(dt) as 'time part in seconds', sec_to_time(time_to_sec(dt)) as 'time part as TIME' from datetime_val;
select ts, time_to_sec(ts) as 'time part in seconds', sec_to_time(time_to_sec(ts)) as 'time part as TIME' from timestamp_val;
--在日期值和天數(shù)之間進行轉換 select d, to_days(d) as 'date to days', from_days(to_days(d)) as 'DATE to days to DATE' from date_val; select dt, to_days(dt) as 'date part in days', from_days(to_days(dt)) as 'date part as DATE' from datetime_val; select ts, to_days(ts) as 'date part in days', from_days(to_days(ts)) as 'date part as DATE' from timestamp_val;
--在datetime或者timestamp類型值和秒數(shù)之間進行轉換 select dt, unix_timestamp(dt) as seconds, from_unixtime(unix_timestamp(dt)) as timestamp from datetime_val; select curdate(), unix_timestamp(curdate()), from_unixtime(unix_timestamp(curdate()))\G
----------------------計算兩個日期和時間之間的間隔-------------------------
--使用時間差函數(shù) set @d1 = '2010-01-01', @d2 = '2009-12-01'; select datediff(@d1, @d2) as 'd1 - d2', datediff(@d2, @d1) as 'd2 - d1';
set @t1 = '12:00:00', @t2 = '16:30:00'; select timediff(@t1, @t2) as 't1 - t2', timediff(@t2, @t1) as 't2 - t1';
select t1, t2, timediff(t2, t1) as 't2 - t1 as TIME', if(timediff(t2, t1) >= 0, '+', '-') as sign, hour(timediff(t2, t1)) as hour, minute(timediff(t2, t1)) as minute, second(timediff(t2, t1)) as second from time_val;
set @dt1 = '1900-01-01 00:00:00', @dt2 = '1910-01-01 00:00:00'; select timestampdiff(minute, @dt1, @dt2) as minutes, timestampdiff(hour, @dt1, @dt2) as hours, timestampdiff(day, @dt1, @dt2) as days, timestampdiff(week, @dt1, @dt2) as weeks, timestampdiff(year, @dt1, @dt2) as years; --利用基本時間單位計算時間間隔 select t1, t2, time_to_sec(t2) - time_to_sec(t1) as 't2 - t1 (in seconds)', sec_to_time(time_to_sec(t2) - time_to_sec(t1)) as 't2 - t1 (as TIME)' from time_val;
--使用基本單位計算兩個Date類型值，或者兩個date-and-time類型值的時間間隔 select to_days('1884-01-01') - to_days('1883-06-05') as days; select (to_days('1884-01-01') - to_days('1883-06-05')) / 7 as weeks;
set @dt1 = '1984-01-01 09:00:00'; set @dt2 = @dt1 + interval 14 day; select unix_timestamp(@dt2) - unix_timestamp(@dt1) as seconds;
set @interval = unix_timestamp(@dt2) - unix_timestamp(@dt1); select @interval as seconds, @interval / 60 as minutes, @interval / (60 * 60) as hours, @interval / (24 * 60 * 60) as days, @interval / (7 * 24 * 60 * 60) as weeks; set @dt1 = '1800-02-14 07:30:00'; set @dt2 = @dt1 + interval 7 day; set @interval = ((to_days(@dt2) - to_days(@dt1)) * 24 * 60 * 60) + time_to_sec(@dt2) - time_to_sec(@dt1);
select @interval as seconds, sec_to_time(@interval) as time;
----------------------增加日期或時間值-------------------------
--使用時間加法函數(shù)或者操作符進行時間值求和運算 set @t1 = '12:00:00', @t2 = '15:30:00'; select addtime(@t1, @t2);
set @dt = '1984-03-01 12:00:00', @t = '12:00:00'; select addtime(@dt, @t);
set @d = '1984-03-01', @t = '15:30:00'; select timestamp(@d, @t);
set @dt = '1984-03-01 12:00:00', @t = '12:00:00'; select timestamp(@dt, @t);
--求出即日第三天的日期值 select curdate(), date_add(curdate(), interval 3 day);
--一個星期前的日期值 select curdate(), date_sub(curdate(), interval 7 day);
--60小時之后是什么時間（同時需要日期和時間值） select now(), date_add(now(), interval 60 hour);
select now(), date_add(now(), interval '14:30' hour_minute); select now(), date_add(now(), interval '3 4' day_hour); select curdate(), curdate() + interval 1 year; select now(), now() - interval '1 12' day_hour;
--使用基本時間單位進行時間加法運算 select t1, sec_to_time(time_to_sec(t1) + 7200) as 't1 plus 2 hours' from time_val;
select t1, t2, time_to_sec(t1) + time_to_sec(t2) as 't1 + t2 (in seconds)', sec_to_time(time_to_sec(t1) + time_to_sec(t2)) as 't1 + t2 (as TIME)' from time_val;
select t1, t2, mod(time_to_sec(t1) + time_to_sec(t2), 86400) as 't1 + t2 (in seconds)', sec_to_time(mod(time_to_sec(t1) + time_to_sec(t2), 86400)) as 't1 + t2 (as TIME)' from time_val;
set @d = '2006-01-01'; select @d as date, from_days(to_days(@d) + 7) as 'date + 1 week', from_days(to_days(@d) - 7) as 'date - 1 week'; set @dt = '2006-01-01 12:30:45'; select @dt as datetime, from_days(to_days(@dt) + 7) as 'datetime + 1 week', from_days(to_days(@dt) - 7) as 'datetime - 1 week'; set @dt = '2006-01-01 09:00:00'; select @dt as datetime, from_unixtime(unix_timestamp(@dt) + 3600) as 'datetime + 1 hour', from_unixtime(unix_timestamp(@dt) - 3600) as 'datetime - 1 hour'; ----------------------計算年齡-------------------------
--創(chuàng)建表、初始化數(shù)據(jù) drop table if exists sibling; create table sibling ( name char(20), birth date );
insert into sibling (name,birth) values('Gretchen','1942-04-14'); insert into sibling (name,birth) values('Wilbur','1946-11-28'); insert into sibling (name,birth) values('Franz','1953-03-05');
select * from sibling;
select name, birth, curdate() as today, timestampdiff(year, birth, curdate()) as 'age in years' from sibling; select name, birth, '1953-03-05' as 'Franz'' birth', timestampdiff(year, birth, '1953-03-05') as 'age in years' from sibling where name != 'Franz'; select name, birth, curdate() as today, timestampdiff(month, birth, curdate()) as 'age in months' from sibling;
select dayofyear('1995-03-01'), dayofyear('1996-02-29'); select right('1995-03-01', 5), right('1996-02-29', 5); select if('02-29' < '03-01', '02-29', '03-01') as earliest;
set @birth = '1965-03-01'; set @target = '1975-01-01'; select @birth, @target, year(@target)- year(@birth) as 'difference', if(right(@target, 5) < right(@birth, 5), 1, 0) as 'adjustment', year(@target) - year(@birth) - if(right(@target, 5) < right(@birth, 5), 1, 0) as 'age'; select name, birth, curdate() as today, year(curdate()) - year(birth) - if(right(curdate(), 5) < right(birth, 5), 1, 0) as 'age in years' from sibling; select name, birth, '1953-03-05' as 'Franz'' birthday', year('1953-03-05') - year(birth) - if(right('1953-03-05', 5) < right(birth, 5), 1, 0) as 'age in years' from sibling where name != 'Franz';
select name, birth, curdate() as today, (year(curdate()) - year(birth)) * 12 + (month(curdate()) - month(birth)) - if(dayofmonth(curdate()) < dayofmonth(birth), 1, 0) as 'age in months' from sibling;
--將一個日期和時間值切換到另一個時區(qū) set @dt = '2006-11-23 09:00:00'; select @dt as Chicago, convert_tz(@dt, 'US/Central', 'Europe/Berlin') as Berlin, convert_tz(@dt, 'US/Central', 'Europe/London') as London, convert_tz(@dt, 'US/Central', 'America/Edmonton') as Edmonton, convert_tz(@dt, 'US/Central', 'Australia/Brisbane') as Brisbane\G
select @dt as Chicago, convert_tz(@dt, '-06:00', '+01:00') as Berlin, convert_tz(@dt, '-06:00', '+00:00') as London, convert_tz(@dt, '-06:00', '-07:00') as Edmonton, convert_tz(@dt, '-06:00', '+10:00') as Brisbane\G ----------------------找出每月的第一天和最后一天-------------------------
select d, date_sub(d, interval dayofmonth(d)-1 day) as '1st of month' from date_val; select d, date_add(date_sub(d, interval dayofmonth(d)-1 day), interval -1 month) as '1st of previous month', date_add(date_sub(d, interval dayofmonth(d)-1 day), interval 1 month) as '1st of following month' from date_val; select d, last_day(d) as 'last of month' from date_val;
select d, last_day(date_add(d, interval -1 month)) as 'last of previous month', last_day(date_add(d, interval 1 month)) as 'last of following month' from date_val;
select d, dayofmonth(last_day(d)) as 'days in month' from date_val;
----------------------通過字串替換來計算日期-------------------------
select d, date_format(d, '%Y-%m-01') as method1, concat(year(d), '-', lpad(month(d), 2, '0'), '-01') as method2 from date_val;
select d, date_format(d, '%Y-01-01') as method1, concat(year(d), '-01-01') as method2 from date_val;
select d, date_format(d, '%Y-12-15') as method1, concat(year(d), '-12-15') as method2 from date_val;
select curdate(), date_add(date_format(curdate(), '%Y-12-25'), interval 2 year) as method1, date_format(date_add(curdate(), interval 2 year), '%Y-12-25') as method2; ----------------------計算某個日期為星期幾-------------------------
select curdate(), dayname(curdate());
--一個月的第一天是星期幾 set @d = curdate(); set @first = date_sub(@d, interval dayofmonth(@d)-1 day); select @d as 'starting date', @first as '1st of month date', dayname(@first) as '1st of month day';
----------------------查出給定某周到某天的日期-------------------------
select d, dayname(d) as day, date_add(d, interval 1-dayofweek(d) day) as sunday, date_add(d, interval 7-dayofweek(d) day) as saturday from date_val;
--兩個星期前星期三的日期 set @target = date_sub(date_add(curdate(), interval 4-dayofweek(curdate()) day), interval 14 day); select curdate(), @target, dayname(@target);
set @target = date_add(date_sub(curdate(), interval 14 day), interval 4-dayofweek(curdate()) day); select curdate(), @target, dayname(@target);
----------------------執(zhí)行閏年計算-------------------------
select d, year(d) % 4 = 0 as 'rule-of-thumb test', (year(d) % 4 = 0) and ((year(d) % 100 != 0) or (year(d) % 400 = 0)) as 'complete test' from date_val;
set @d = '2006-04-13'; select dayofyear(date_format(@d, '%Y-12-31'));
set @d = '2008-04-13'; select dayofyear(date_format(@d, '%Y-12-31'));
----------------------接近但不是iso格式的日期格式-------------------------
select d, concat(year(d), '-', month(d), '-01') from date_val; select d, concat(year(d), '-', lpad(month(d), 2, '0'), '-01') from date_val;
select concat(year(d), '-', month(d), '-01') as 'non-iso', date_add(concat(year(d), '-', month(d), '-01'), interval 0 day) as 'iso 1', concat(year(d), '-', month(d), '-01') + interval 0 day as 'iso2', from_days(to_days(concat(year(d), '-', month(d), '-01'))) as 'iso 3', str_to_date(concat(year(d), '-', month(d), '-01'), '%Y-%m-%d') as 'iso 4' from date_val;
----------------------將日期或時間當成數(shù)值-------------------------
select t1, t1+0 as 't1 as number', floor(t1) as 't1 as number', floor(t1/10000) as 'hour part' from time_val;
select d, d+0 from date_val; select dt, dt+0, floor(dt+0) from datetime_val; select '1999-01-01' + 0, '1999-01-01 12:30:45' + 0, '12:30:45'+0; show warnings;
----------------------強制MySQL將字符串當作時間值------------------------- select '12:30:45'+0, sec_to_time(time_to_sec('12:30:45'))+0; select '1999-01-01'+0, from_days(to_days('1999-01-01'))+0; select date_add('1999-01-01 12:30:45', interval 0 day)+0 as 'numberic datetime';
--------------------------------------------------------- -----------------------排序查詢結果---------------------- ---------------------------------------------------------
--建表 drop table if exists driver_log; create table driver_log ( rec_id int unsigned not null auto_incerment, name varchar(20) not null, trav_date date not null, miles int not null, primary key (rec_id) );
--初始化數(shù)據(jù) insert into driver_log (name,trav_date,miles) values ('Ben','2006-08-30',152), ('Suzi','2006-08-29',391), ('Henry','2006-08-29',300), ('Henry','2006-08-27',96), ('Ben','2006-08-29',131), ('Henry','2006-08-26',115), ('Suzi','2006-09-02',502), ('Henry','2006-09-01',197), ('Ben','2006-09-02',79), ('Henry','2006-08-30',203) ;
--查看數(shù)據(jù) select * from driver_log; select * from mail;
----------------------使用order by命令排序查詢結果-------------------------
select * from driver_log order by name; select * from driver_log order by name asc; select * from driver_log order by name desc; select * from driver_log order by name, trav_date; select * from driver_log order by name desc, trav_date desc; select * from driver_log order by name desc, trav_date; select name, trav_date, miles as distance from driver_log order by distance; select name, trav_date, miles as distance from driver_log order by distance desc;
----------------------使用表達式排序-------------------------
select * from mail; select t, srcuser, floor((size+1023)/1024) from mail where size > 50000 order by floor((size+1023)/1024);
select t, srcuser, floor((size+1023)/1024) as kilobytes from mail where size > 50000 order by kilobytes;
----------------------顯示一組按照其他屬性排序的值-------------------------
select t, srcuser, concat(floor((size+1023)/1024), 'K') as size_in_k from mail where size > 50000 order by size_in_k;
select t, srcuser, concat(floor((size+1023)/1024), 'K') as size_in_k from mail where size > 50000 order by size;
--建表 drop table if exists roster;
create table roster ( name CHAR(30), # player name jersey_num CHAR(3) # jersey number );
--初始化表 insert into roster (name, jersey_num) values ('Lynne','29'), ('Ella','0'), ('Elizabeth','100'), ('Nancy','00'), ('Jean','8'), ('Sherry','47') ;
select name, jersey_num from roster; select name, jersey_num from roster order by jersey_num; select name, jersey_num from roster order by jersey_num+0;
select t, concat(srcuser, '@', srchost) as sender, size from mail where size > 50000 order by srchost, srcuser;
--建表 drop table if exists name;
create table name ( last_name char(20), first_name char(20) );
--初始化數(shù)據(jù) insert into name (first_name,last_name) VALUES('Kevin','Brown'); insert into name (first_name,last_name) VALUES('Vida','Blue'); insert into name (first_name,last_name) VALUES('Pete','Gray'); insert into name (first_name,last_name) VALUES('Devon','White'); insert into name (first_name,last_name) VALUES('Rondell','White');
select last_name, first_name from name order by last_name, first_name; select concat(first_name, ' ', last_name) as full_name from name order by last_name, first_name;
----------------------字符串排序的大小寫區(qū)分控制-------------------------
--建表 drop table if exists str_val;
create table str_val ( ci_str char(3) character set latin1 collate latin1_swedish_ci, cs_str char(3) character set latin1 collate latin1_general_cs, bin_str binary(3) );
--初始化數(shù)據(jù) insert into str_val (ci_str,cs_str,bin_str) VALUES ('AAA','AAA','AAA'), ('aaa','aaa','aaa'), ('bbb','bbb','bbb'), ('BBB','BBB','BBB');
select * from str_val; select * from str_val order by ci_str; select * from str_val order by cs_str; select * from str_val order by bin_str;
select ci_str from str_val order by ci_str; select cs_str from str_val order by cs_str; select bin_str from str_val order by bin_str;
select ci_str from str_val order by ci_str collate latin1_general_cs; select cs_str from str_val order by cs_str collate latin1_swedish_ci; select cs_str from str_val order by upper(cs_str); select bin_str from str_val order by convert(bin_str using latin1) collate latin1_swedish_ci;
----------------------基于日期的排序-------------------------
--建表 drop table if exists temporal_val;
create table temporal_val ( d date, dt datetime, t time, ts timestamp );
--初始化數(shù)據(jù) insert into temporal_val (d, dt, t, ts) values ('1970-01-01','1884-01-01 12:00:00','13:00:00','1980-01-01 02:00:00'), ('1999-01-01','1860-01-01 12:00:00','19:00:00','2021-01-01 03:00:00'), ('1981-01-01','1871-01-01 12:00:00','03:00:00','1975-01-01 04:00:00'), ('1964-01-01','1899-01-01 12:00:00','01:00:00','1985-01-01 05:00:00') ;
select * from temporal_val； select * from temporal_val order by d; select * from temporal_val order by dt; select * from temporal_val order by t; select * from temporal_val order by ts;
----------------------按日歷排序-------------------------
--建表 drop table if exists event; create table event ( date date, description varchar(255) ) ;
--初始化表 insert into event (date,description) values ('1789-07-04','US Independence Day'), ('1776-07-14','Bastille Day'), ('1957-10-04','Sputnik launch date'), ('1958-01-31','Explorer 1 launch date'), ('1919-06-28','Signing of the Treaty of Versailles'), ('1732-02-22','George Washington\'s birthday'), ('1989-11-09','Opening of the Berlin Wall'), ('1944-06-06','D-Day at Normandy Beaches'), ('1215-06-15','Signing of the Magna Carta'), ('1809-02-12','Abraham Lincoln\'s birthday') ;
select date, description from event; select date, description from event order by date; select date, description from event order by month(date), dayofmonth(date);
select date, description from event order by dayofyear(date); select dayofyear('1996-02-29'), dayofyear('1997-03-01');
----------------------按周歷排序-------------------------
select dayname(date) as day, date, description from event order by dayofweek(date);
select dayname(date), date, description from event order by mod(dayofweek(date)+5, 7);
----------------------按時鐘排序-------------------------
select * from mail order by hour(t), minute(t), second(t); select *, time_to_sec(t) from mail order by time_to_sec(t);
----------------------按數(shù)據(jù)列的字串排序-------------------------
--創(chuàng)建表、初始化數(shù)據(jù) drop table if exists housewares; create table housewares ( id VARCHAR(20), description VARCHAR(255) );
insert into housewares (id,description) values ('DIN40672US', 'dining table'), ('KIT00372UK', 'garbage disposal'), ('KIT01729JP', 'microwave oven'), ('BED00038SG', 'bedside lamp'), ('BTH00485US', 'shower stall'), ('BTH00415JP', 'lavatory') ;
select * from housewares;
drop table if exists housewares2; create table housewares2 ( id varchar(20), description varchar(255) );
insert into housewares2 (id,description) values ('DIN40672US', 'dining table'), ('KIT372UK', 'garbage disposal'), ('KIT1729JP', 'microwave oven'), ('BED38SG', 'bedside lamp'), ('BTH485US', 'shower stall'), ('BTH415JP', 'lavatory') ;
select * from housewares2;
drop table if exists housewares3; create table housewares3 ( id VARCHAR(20), description VARCHAR(255) );
insert into housewares3 (id,description) VALUES ('13-478-92-2', 'dining table'), ('873-48-649-63', 'garbage disposal'), ('8-4-2-1', 'microwave oven'), ('97-681-37-66', 'bedside lamp'), ('27-48-534-2', 'shower stall'), ('5764-56-89-72', 'lavatory') ;
select * from housewares3;
drop table if exists hw_category; create table hw_category ( abbrev VARCHAR(3), name VARCHAR(20) );
insert into hw_category (abbrev,name) values ('DIN', 'dining'), ('KIT', 'kitchen'), ('BTH', 'bathroom'), ('BED', 'bedroom') ;
select * from hw_category;
--按固定長度的字串排序 select * from housewares; select * from housewares order by id;
select id, left(id, 3) as category, mid(id, 4, 5) as serial, right(id, 2) as country from housewares; select * from housewares order by left(id, 3); select * from housewares order by mid(id, 4, 5); select * from housewares order by right(id, 2); select * from housewares order by right(id, 2), mid(id, 4, 5);
--按可變長度的字串排序 select * from housewares2; select id, left(substring(id, 4), char_length(substring(id, 4))-2) from housewares2; select id, substring(id, 4), substring(id, 4, char_length(id)-5) from housewares2; select * from housewares2 order by substring(id, 4, char_length(id)-5);
select * from housewares2 order by substring(id, 4, char_length(id)-5); select * from housewares2 order by substring(id, 4, char_length(id)-5)+0; select *, substring(id, 4, char_length(id)-5)+0 from housewares2 order by substring(id, 4, char_length(id)-5)+0; select * from housewares2 order by substring(id, 4)+0;
select * from housewares3; select id, substring_index(substring_index(id, '-', 2), '-', -1) as segment2, substring_index(substring_index(id, '-', 4), '-', -1) as segment4 from housewares3;
select * from housewares3 order by substring_index(substring_index(id, '-', 2), '-', -1); select * from housewares3 order by substring_index(substring_index(id, '-', 2), '-', -1)+0;
----------------------按域名順序排列主機名-------------------------
--建表 drop table if exists hostname; create table hostname ( name varchar(64) );
--初始化數(shù)據(jù) insert into hostname (name) values ('cvs.php.net'), ('dbi.perl.org'), ('lists.mysql.com'), ('mysql.com'), ('jakarta.apache.org'), ('www.kitebird.com') ;
select * from hostname;
select name from hostname order by name; select name, substring_index(substring_index(name, '.', -3), '.', 1) as leftmost, substring_index(substring_index(name, '.', -2), '.', 1) as middle, substring_index(name, '.', -1) as rightmost from hostname;
select name, substring_index(substring_index(concat('..', name), '.', -3), '.', 1) as leftmost, substring_index(substring_index(concat('.', name), '.', -2), '.', 1) as middle, substring_index(name, '.', -1) as rightmost from hostname;
select name, substring_index(substring_index(concat('....', name), '.', -3), '.', 1) as leftmost, substring_index(substring_index(concat('....', name), '.', -2), '.', 1) as middle, substring_index(name, '.', -1) as rightmost from hostname;
select name from hostname order by substring_index(name, '.', -1), substring_index(substring_index(concat('.', name), '.', -2), '.', 1), substring_index(substring_index(concat('..', name), '.', -3), '.', 1);
----------------------按照數(shù)字順序排序點分式主IP地址-------------------------
--建表 drop table if exists hostip; create table hostip ( ip varchar(64) );
--初始化數(shù)據(jù) insert into hostip (ip) values ('127.0.0.1'), ('192.168.0.2'), ('192.168.0.10'), ('192.168.1.2'), ('192.168.1.10'), ('255.255.255.255'), ('21.0.0.1') ;
select * from hostip ORDER BY ip;
select ip from hostip order by substring_index(ip, '.', 1)+0, substring_index(substring_index(ip, '.', -3), '.', 1)+0, substring_index(substring_index(ip, '.', -2), '.', 1)+0, substring_index(ip, '.', -1)+0;
select ip from hostip order by inet_aton(ip);
select ip, ip+0 from hostip;
----------------------將數(shù)值移動到排序結果的頭部或尾部-------------------------
select null = null;
--建表 drop table if exists t; create table t ( val varchar(64) );
--初始化數(shù)據(jù) insert into t (val) values (3), (100), (null), (null), (9) ;
select val from t; select val from t order by val; select val from t order by if(val is null, 1, 0), val;
select t, srcuser, dstuser, size from mail order by if(srcuser='phil', 0, 1), srcuser, dstuser;
select t, srcuser, dstuser, size from mail order by if(srcuser=dstuser, 0, 1), srcuser, dstuser;
select t, srcuser, dstuser, size from mail order by if(srcuser=dstuser, null, srcuser), dstuser;
----------------------按照用戶定義排序-------------------------
select *, field(name, 'Henry', 'Suzi', 'Ben') from driver_log order by field(name, 'Henry', 'Suzi', 'Ben');
select id, description, field(right(id, 2), 'US', 'UK', 'JP', 'SG') from housewares order by field(right(id, 2), 'US', 'UK', 'JP', 'SG');
----------------------排序枚舉數(shù)值-------------------------
--創(chuàng)建表 drop table id exists weekday;
create table weekday ( day enum('Sunday','Monday','Tuesday','Wednesday', 'Thursday','Friday','Saturday') );
--初始化數(shù)據(jù) insert into weekday (day) values('Monday'),('Friday'), ('Tuesday'),('Sunday'),('Thursday'),('Saturday'),('Wednesday');
select * from weekday; select day, day+0 from weekday; select day, day+0 from weekday order by day; select day, day+0 from weekday order by cast(day as char);
--創(chuàng)建表 drop table if exists color;
create table color ( name cahr(10) );
insert into color (name) values('blue'),('green'),('indigo'),('orange'),('red'),('violet'),('yellow');
select name from color; select name from color order by name; select name from color order by field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
select name, field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') from color order by field(name, 'red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
alter table color modify name enum('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');
select name from color; select name from color order by name;
--------------------------------------------------------- -------------------------生成摘要------------------------ ---------------------------------------------------------
select * from states order by name;
---------------------使用count函數(shù)生成摘要--------------------
select count(*) from driver_log; select count(*) from states;
select table_rows from information_schema.tables where table_schema = 'cookbook' and table_name = 'states';
show tables status;
select count(*) from driver_log where miles > 200; select count(*) from driver_log where name = 'Suzi'; select count(*) from states where statehood < '1900-01-01'; select count(*) from states where statehood between '1800-01-01' and '1899-12-31';
select count(if(dayofweek(trav_date)=7, 1, null)) as 'Staturday trips', count(if(dayofweek(trav_date)=1, 1, null)) as 'Sunday trips' from driver_log;
select count(if(dayofweek(trav_date) in (1, 7), 1, null)) as 'weekend trips', count(if(dayofweek(trav_date) in (1, 7), null, 1)) as 'weekday trips' from driver_log;
create view trip_summary_view as select count(if(dayofweek(trav_date) in (1, 7), 1, null)) as 'weekend trips', count(if(dayofweek(trav_date) in (1, 7), null, 1)) as 'weekday trips' from driver_log;
select * from trip_summary_view; | 
免費注冊
發(fā)表于 2011-12-21 08:42