本文給大家分享了一篇關于mysql數據庫必會sql語句加強版內容，非常不錯，具有參考借鑒價值，需要的朋友參考下吧
這一篇屬于加強版，問題和sql語句如下。
創建users表，設置id,name,gender,sal字段，其中id為主鍵?
drop?table?if?exists?users;? create?table?if?not?exists?users(? ??id?int(5)?primary?key?auto_increment,? ??name?varchar(10)?unique?not?null,??? ??gender?varchar(1)?not?null,? ??sal?int(5)?not?null? );? insert?into?users(name,gender,sal)?values('AA','男',1000);? insert?into?users(name,gender,sal)?values('BB','女',1200);
————————————————————————————–?
一對一：AA的身份號是多少?
drop?table?if?exists?users;? create?table?if?not?exists?users(? ??id?int(5)?primary?key?auto_increment,? ??name?varchar(10)?unique?not?null,??? ??gender?varchar(1)?not?null,? ??sal?int(5)?not?null? );? insert?into?users(name,gender,sal)?values('AA','男',1000);? insert?into?users(name,gender,sal)?values('BB','女',1200);? drop?table?if?exists?cards;? create?table?if?not?exists?cards(? ??id?int(5)?primary?key?auto_increment,? ??num?int(3)?not?null?unique,? ??loc?varchar(10)?not?null,? ??uid?int(5)?not?null?unique,? ??constraint?uid_fk?foreign?key(uid)?references?users(id)? );? insert?into?cards(num,loc,uid)?values(111,'北京',1);? insert?into?cards(num,loc,uid)?values(222,'上海',2);
【注：inner join表示內連接】?
select?u.name?"姓名",c.num?"身份證號"? from?users?u?inner?join?cards?c? on?u.id?=?c.uid? where?u.name?=?'AA';? --? select?u.name?"姓名",c.num?"身份證號"? from?users?u?inner?join?cards?c? on?u.id?=?c.uid? where?name?=?'AA';
———————————————?
一對多：查詢”開發部”有哪些員工?
創建groups表?
drop?table?if?exists?groups;? create?table?if?not?exists?groups(? ??id?int(5)?primary?key?auto_increment,? ??name?varchar(10)?not?null? );? insert?into?groups(name)?values('開發部');? insert?into?groups(name)?values('銷售部');
創建emps表?
drop?table?if?exists?emps;? create?table?if?not?exists?emps(? ??id?int(5)?primary?key?auto_increment,? ??name?varchar(10)?not?null,? ??gid?int(5)?not?null,? ??constraint?gid_fk?foreign?key(gid)?references?groups(id)? );? insert?into?emps(name,gid)?values('哈哈',1);? insert?into?emps(name,gid)?values('呵呵',1);? insert?into?emps(name,gid)?values('嘻嘻',2);? insert?into?emps(name,gid)?values('笨笨',2);
查詢”開發部”有哪些員工?
select?g.name?"部門",e.name?"員工"? from?groups?g?inner?join?emps?e? on?g.id?=?e.gid? where?g.name?=?'開發部';? --? select?g.name?"部門",e.name?"員工"? from?groups?g?inner?join?emps?e? on?g.id?=?e.gid? where?g.name?=?'開發部';
——————————————————?
多對多：查詢”趙”教過哪些學生?
創建students表?
drop?table?if?exists?students;? create?table?if?not?exists?students(? ??id?int(5)?primary?key?auto_increment,? ??name?varchar(10)?not?null? );? insert?into?students(name)?values('哈哈');? insert?into?students(name)?values('嘻嘻');
創建teachers表?
drop?table?if?exists?teachers;? create?table?if?not?exists?teachers(? ??id?int(5)?primary?key?auto_increment,? ??name?varchar(10)?not?null? );? insert?into?teachers(name)?values('趙');? insert?into?teachers(name)?values('劉');
創建middles表?? primary key(sid,tid)? 表示聯合主鍵，這兩個字段的整體要唯一?
drop?table?if?exists?middles;? create?table?if?not?exists?middles(? ??sid?int(5),? ??constraint?sid_fk?foreign?key(sid)?references?students(id),? ??tid?int(5),? ??constraint?tid_fk?foreign?key(tid)?references?teachers(id),? ??primary?key(sid,tid)?? );? insert?into?middles(sid,tid)?values(1,1);? insert?into?middles(sid,tid)?values(1,2);? insert?into?middles(sid,tid)?values(2,1);? insert?into?middles(sid,tid)?values(2,2);
查詢”趙”教過哪些學生?
select?t.name?"老師",s.name?"學生"? from?students?s?inner?join?middles?m?inner?join?teachers?t? on?(s.id=m.sid)?and?(m.tid=t.id)? where?t.name?=?'趙';? --? select?t.name?"老師",s.name?"學生"? from?students?s?inner?join?middles?m?inner?join?teachers?t?? on?(s.id=m.sid)?and?(t.id=m.tid)? where?t.name?=?"趙";
——————————————————————————————————–?
將5000元（含）以上的員工標識為”高薪”，否則標識為”起薪”?
將薪水為NULL的員工標識為”無薪”?
將5000元（含）以上的員工標識為”高薪”，否則標識為”起薪”?
將7000元的員工標識為”高薪”,6000元的員工標識為”中薪”,5000元則標識為”起薪”,否則標識為”試用薪”
———————————————————————————————————?
內連接（等值連接）：查詢客戶姓名，訂單編號，訂單價格?
【注：customers c inner join orders o使用了別名，以后o就代表orders】?
select?c.name?"客戶姓名",o.isbn?"訂單編號",o.price?"訂單價格"? from?customers?c?inner?join?orders?o? on?c.id?=?o.customers_id;? --? select?c.name?"客戶姓名",o.isbn?"訂單編號",o.price?"訂單價格"? from?customers?c?inner?join?orsers?o? on?c.id?=?o.customers_id;
on+兩張表連接的條件.一張表的主鍵，一張表的外鍵?
內連接：只能查詢出二張表中根據連接條件都存在的記錄，有點類似于數學中交集?
—————————————————-?
外連接：按客戶分組，查詢每個客戶的姓名和訂單數?
外連接：既可以根據連接條件查詢出二張表中都存在的記錄，也能根據一方，強行將另一方就算不滿兄條件的記錄也能查詢出來?
外連接可以細分為：
右外連接?:?以右側為參照，right?outer?join表示? select?c.name,count(o.isbn)? from?orders?o?right?outer?join?customers?c? on?c.id?=?o.customers_id? group?by?c.name;
left outer join表示左邊的內容都會顯現出來，例如customers c left out join 表示會把customers中的某列所有內容都找出來??
——————————————————?
自連接：求出AA的老板是EE。把自己想象成兩張表。左右各一張?
select?users.ename,bosss.ename? from?emps?users?inner?join?emps?bosss? on?users.mgr?=?bosss.empno;? select?users.ename,bosss.ename? from?emps?users?left?outer?join?emps?bosss? on?users.mgr?=?bosss.empno;
———————————————————————————————–?
演示MySQL中的函數（查詢手冊）??
日期時間函數：?
select?addtime('2016-8-7?23:23:23','1:1:1');??時間相加? select?current_date();? select?current_time();? select?now();? select?year(?now()?);? select?month(?now()?);? select?day(?now()?);? select?datediff('2016-12-31',now());
字符串函數：
select?charset('哈哈');? select?concat('你好','哈哈','嗎');? select?instr('www.baidu.com','baidu');? select?substring('www.baidu.com',5,3);
數學函數：?
select?bin(10);? select?floor(3.14);//比3.14小的最大整數---正3? select?floor(-3.14);//比-3.14小的最大整數---負4? select?ceiling(3.14);//比3.14大的最小整數---正4? select?ceiling(-3.14);//比-3.14大的最小整數---負3，一定是整數值? select?format(3.1415926,3);保留小數點后3位，四舍五入? select?mod(10,3);//取余數? select?rand();//
加密函數：?
select md5(‘123456’);?
返回32位16進制數 e10adc3949ba59abbe56e057f20f883e??
演示MySQL中流程控制語句?
use?json;? drop?table?if?exists?users;? create?table?if?not?exists?users(? ??id?int(5)?primary?key?auto_increment,? ??name?varchar(10)?not?null?unique,? ??sal?int(5)? );? insert?into?users(name,sal)?values('哈哈',3000);? insert?into?users(name,sal)?values('呵呵',4000);? insert?into?users(name,sal)?values('嘻嘻',5000);? insert?into?users(name,sal)?values('笨笨',6000);? insert?into?users(name,sal)?values('明明',7000);? insert?into?users(name,sal)?values('絲絲',8000);? insert?into?users(name,sal)?values('君君',9000);? insert?into?users(name,sal)?values('趙趙',10000);? insert?into?users(name,sal)?values('無名',NULL);
將5000元（含）以上的員工標識為”高薪”，否則標識為”起薪”
select?name?"姓名",sal?"薪水",? ????if(sal>=5000,"高薪","起薪")?"描述"? from?users;
將薪水為NULL的員工標識為”無薪”
select?name?"姓名",ifnull(sal,"無薪")?"薪水"? from?users;
將5000元（含）以上的員工標識為”高薪”，否則標識為”起薪”
select?name?"姓名",sal?"薪水",? ????case?when?sal>=5000?then?"高薪"? ????else?"起薪"?end?"描述"? from?users;
將7000元的員工標識為”高薪”，6000元的員工標識為”中薪”,5000元則標識為”起薪”，否則標識為”試用薪”
select?name?"姓名",sal?"薪水",? ????case?sal? ??????when?3000?then?"低薪"? ??????when?4000?then?"起薪"? ??????when?5000?then?"試用薪"? ??????when?6000?then?"中薪"? ??????when?7000?then?"較好薪"? ??????when?8000?then?"不錯薪"? ??????when?9000?then?"高薪"? ??????else?"重薪"? ????end?"描述"? from?users;
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
