本文記錄了mysql的一些常用操作,不多說了,直接一一列出:
/* Author:?liuyazhuang Date:2016-04-12 */ use?test; DROP?table?if?exists?equipment; create?table?equipment( assetTag?INTEGER?primary?key, description?varchar(20), acquired?Date ); /*數(shù)據(jù)表插入語句*/ INSERT?INTO?equipment?values?(50431,'21寸監(jiān)視器',?DATE?'2013-01-23'); INSERT?INTO?equipment?values?(50532,'pad',?DATE?'2013-01-26'); /*數(shù)據(jù)表增加新的列,并初始化*/ ALTER?Table?equipment?ADD?COLUMN?office?VARCHAR(20); UPDATE?equipment?SET?office='Headquarters'; /*向修改后的表中插入記錄*/ INSERT?INTO?equipment(assetTag,description,acquired)?values?(50432,'IBM-PC',DATE?'2013-01-01'); /*創(chuàng)建代替表的新表(復(fù)制)*/ DROP?TABLE?if?EXISTS?equipmentMultiSite; CREATE??TABLE?equipmentMultiSite( assetTag?INTEGER?PRIMARY?KEY, office?varchar(20)?DEFAULT?'Headquarters', description?varchar(20), acquired?DATE ); INSERT?INTO?equipmentMultiSite SELECT?assetTag,'Headquarters',description,acquired?FROM?equipment; /*刪除現(xiàn)有equipment用視圖代替*/ DROP?TABLE?equipment; CREATE?VIEW?equipment?AS SELECT?assetTag,description,acquired?'dateAcquired', FROM?equipment?WHERE?office='Headquarters'; /*基礎(chǔ)的查詢*/ select?sum(amount)?from?payment?where?customer_id=1;?/*某顧客從開戶以來的消費(fèi)總額*/ select?count(*)?from?payment?where?customer_id=1;??/*某顧客從開戶以來的消費(fèi)次數(shù)*/ /*查詢某顧客在2005年5月份和6月份消費(fèi)總額,用"between”或者""來建立條件*/ select?sum(amount)?from?payment?where?customer_id=1?and?extract(year?from?payment_date)=2005?and?extract(month?from?payment_date)?between?05?and?06; select?sum(amount)?from?payment?where?customer_id=1?and?payment_date?>=?'2005-05-01?00:00:00'?and?payment_date??UNIX_timestamp('2005-05-01?00:00:00')?and?payment_date? ?create?view?v_tmp_user?as?select?*?from?tmp_user; ERROR?1352?(HY000):?View's?SELECT?refers?to?a?temporary?table?'tmp_user' */ /*創(chuàng)建預(yù)處理語句*/ create?view?pay_view?as select?sum(amount)?from?payment?where?extract(year?from?payment_date)=2005; /*mysql中連接字符串用concat函數(shù),||僅作邏輯運(yùn)算用*/ create?view?pay_view?as? select?concat(c.first_name,'?',c.last_name)?as?name,?sum(p.amount)?as?amount?from?payment_copy?As?p,customer?As?c ???? where?extract(year?from?p.payment_date)=2005?and?p.customer_id=c.customer_id?group?by?p.customer_id; select?*?from?pay_view; /*輸出前10條數(shù)據(jù)*/ select?*?from?pay_view?limit?10; /*輸出第11條到20條數(shù)據(jù)*/ select?*?from?pay_view?limit?11,20; /*隨機(jī)抽取5條數(shù)據(jù),order?by?rand()*/ select?*?from?pay_view?order?by?rand()?limit?5; drop?view?pay_view; /*不能給視圖添加索引,只能在基本表上添加索引*/ /*create?index?pay_view_index?on?pay_view(amount);*/ /*ERROR?1347?(HY000):?'test.pay_view'?is?not?BASE?TABLE*/ /*特別注意:如果視圖和基本表一一對應(yīng),視圖的更新可以達(dá)到同步修改基本表的目的;如果進(jìn)行抽取,運(yùn)算等操作得到視圖,對視圖的操作不能同步到 基本表,視圖中數(shù)據(jù)和基本表中的數(shù)據(jù)不一致,視圖中的數(shù)據(jù)在內(nèi)存中,做臨時(shí)顯示使用,有必要時(shí)才將數(shù)據(jù)同步到基本表*/ /*事務(wù),mysql中默認(rèn)每個sql語句是一個事務(wù),就自動提交一次。考慮到性能,多個語句放在一個事務(wù)塊中*/ begin? drop?view?pay_view; create?view?pay_view?as? select?concat(c.first_name,'?',c.last_name)?as?name,?sum(p.amount)?as?amount?from?payment_copy?As?p,customer?As?c ???? where?extract(year?from?p.payment_date)=2005?and?p.customer_id=c.customer_id?group?by?p.customer_id; select?*?from?pay_view?limit?10; end /*更改表的存儲引擎*/ alert?table?payment_copy?engine=InnoDB; /*創(chuàng)建mysql定時(shí)執(zhí)行的事件*/ set?global?event_scheduler=1; create?table?testduty( time?varchar(20)?default?null )engine=myisam?default?charset=latin1; create?event?if?not?exists?test_event?on?schedule?every?10?second?do?insert?into?testduty(time)?values(now()); /*刪除定時(shí)任務(wù)*/ drop?event?test_event; /*優(yōu)化數(shù)據(jù)表*/ optimize?table?payment; /*測試加入索引的性能*/ /*建立兩張表:一個建立索引,另一個不建立*/ create??table??if?not?exists?test_has_index( id?integer?not?null?auto_increment, num?integer?not?null?default?0, d_num?varchar(30)?not?null?default?'0', primary?key(id) )engine=MyISAM?default?charset=utf8?auto_increment=1; create?table?if?not?exists?test_no_index( id?integer?not?null?auto_increment, num?integer?not?null?default?0, primary?key(id) )engine=MyISAM?default?charset=utf8?auto_increment=1; /*創(chuàng)建存儲過程,用于初始化數(shù)據(jù)表*/ delimiter?| create?procedure?i_test(pa?integer,tab?varchar(30)) begin declare?max_num?integer?default?10000; declare?i?integer?default?0; declare?rand_num?integer; declare?double_num?char; if?tab!='test_no_index'??then select?count(id)?into?max_num?from?test_has_index; while?i?(select?count(*)?from?t1_t2? where?user_id=t.user_id?and?comment_counts>t.comment_counts order?by?t.user_id,t.comment_counts) order?by?t.user_id; select??t1.user_id,t1.blog_id?from?t1?inner?join??t2?on?t1.blog_id=t2.blog_id?order?by?counts?desc; ?group?by?t1.blog_id?; ? ?select??t1.user_id,t1.blog_id,count(t2.comment_id)?as?counts? from?t1?inner?join??t2?on?t1.blog_id=t2.blog_id? group?by?t1.blog_id? having?count(t2.comment_id)?where子句?聯(lián)結(jié)條件和查詢條件?>?group?by子句分組?>?having子句搜索?>order?by子句結(jié)果排序?>?limit顯示某些記錄 */ /*連接查詢常用模式*/ /*1、select?*?from?table1,?table2?where?table1.id=table2.id 2、select?*?from?table1?left?join?table2?on?table1.id?=?table2.id 3.?select?*?from?table1?left?join?table2?using(id) 4.?select?*?from?table1?left?join?table2?on?table1.id=table2.id?left?join?table3?on?table2.id=table3.id 5.?select?*?from?table1?use?index(key1,key2)?where?key1=1?and?key2=2?and?key3=3 6.?slect?*?from?table1?ignore?index(key1)?where?key1=1?and?key2=2?and?key3=3 */ /*驗(yàn)證各種連接結(jié)果,以film,?film_actor,?actor為例*/ select??f.title,fa.actor?from?film?f?left?join?film_actor?fa?on?f.film_id?=?fa.film_id?limit?10; select??film.*,film_actor.*?from?film?left?join?film_actor?on?film.film_id?=?film_actor.film_id?limit?10; select??film.title,film_actor.actor_id?from?film?left?join?film_actor?on?film.film_id?=?film_actor.film_id?limit?10; select??film.title,film_actor.actor_id?from?film?left?join?film_actor?using(film_id)?limit?10; select??film.title,film_actor.actor_id?from?film?left?join?film_actor?using(film_id)?group?by?film.film_id?limit?10; select??film.title,film_actor.actor_id?from?film?inner?join?film_actor?on?film.film_id?=?film_actor.film_id?limit?10; /**/ /**/ /**/ /*查詢中去掉重復(fù)字段*/ /*建立測試數(shù)據(jù)表school_report*/ drop??table?if?exists?school_report; create?table?school_report( id?int(10)?not?null?auto_increment?comment?'表ID', u_id?int(10)?not?null?comment?'學(xué)生ID', name?varchar(20)?not?null?default?''?comment?'學(xué)生姓名', score?varchar(4)?not?null?default?0?comment?'學(xué)生成績', message?varchar(50)?not?null?default?'', dateline?timestamp?not?null?default?current_timestamp, primary?key(id) )engine=innodb?default?charset=utf8?auto_increment=1; /*插入測試數(shù)據(jù)*/ insert?into?school_report(u_id,name,score,message) values(1,'張三',89,'helloworld'),(1,'張三',90,'hello'),(2,'李四',92,'helloworld'), (3,'王五',93,'world'); /*查詢,去掉重復(fù)*/ select?distinct?name,score?from?school_report; select?*,?count(distinct?name)?from?school_report?group?by?name; /**/ select?*? from?school_report?a?inner?join(? select?max(dateline)?as?dateline? from?school_report?group?by?u_id)?b on?a.dateline?=?b.dateline group?by?id?order?by?a.dateline?desc; /*記錄和分析?花費(fèi)時(shí)間較多的select*/ /*首先進(jìn)行如下設(shè)置,設(shè)置long_query_time時(shí)間限*/ show?variables?like?"%long%"; set?global?long_query_time=2; /*檢查并開啟慢查詢,會顯示mysql-slow.log文件的路徑*/ show?variables?like?"%slow%"; set?global?slow_query_log='ON'; /*這樣超過long_query_time的查詢會記錄到mysql日志中*/ /*分組后前n條數(shù)據(jù):?http://www.php.cn/*/ drop??table?if?exists?tb; create?table?tb?( name?varchar(10), val?int, memo?varchar(20) ); insert?into?tb?values('a',?2,?'a2(a的第二個值)'),('a',1,'a1--a第一個值'), ('a',3,'a3--a第三個值'),('b',1,'b1--b第一個值'),('b',3,'b3--b第三個值'), ('b',2,'b3--b2b2b2'),('b',4,'b4b4b4'),('b',5,'b5b5b5b5'); /*按name分組取value?最大的值的記錄*/ --方法1:select?a.*?from?tb?a?where?val?=?(select?max(val)?from?tb?where?name?=?a.name)?order?by?a.name? --方法2:? select?a.*?from?tb?a?where?not?exists(select?1?from?tb?where?name?=?a.name?and?val?>?a.val);? --方法3:? select?a.*?from?tb?a,(select?name,max(val)?val?from?tb?group?by?name)?b?where?a.name?=?b.name?and?a.val?=?b.val?order?by?a.name;? --方法4:? select?a.*?from?tb?a?inner?join?(select?name?,?max(val)?val?from?tb?group?by?name)?b?on?a.name?=?b.name?and?a.val?=?b.val?order?by?a.name?; --方法5? select?a.*?from?tb?a?where?1?>?(select?count(*)?from?tb?where?name?=?a.name?and?val?>?a.val?)?order?by?a.name?; /*? name?val?memo? ----------?-----------?--------------------? a?3?a3:a的第三個值? b?5?b5b5b5b5b5? 方法三、四效率比較高 */? /*按name分組取val最小的值的記錄*/ --方法1:select?a.*?from?tb?a?where?val?=?(select?min(val)?from?tb?where?name?=?a.name)?order?by?a.name? --方法2:? select?a.*?from?tb?a?where?not?exists(select?1?from?tb?where?name?=?a.name?and?val??(select?count(*)?from?tb?where?name?=?a.name?and?val??(select?count(*)?from?tb?where?name?=?a.name?and?val??(select?count(*)?from?tb?where?name?=?a.name?and?val?>?a.val?)?order?by?a.name,a.val? select?a.*?from?tb?a?where?val?in?(select?top?2?val?from?tb?where?name=a.name?order?by?val?desc)?order?by?a.name,a.val? select?a.*?from?tb?a?where?exists?(select?count(*)?from?tb?where?name?=?a.name?and?val?>?a.val?having?Count(*)?
? 版權(quán)聲明
文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載。
THE END