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(*)?