本文談談MySQL的開發(fā)必會的sql語句
創(chuàng)建數據庫
createdatabasedb1;
刪除數據庫
dropdatabasedb1;
創(chuàng)建數據表
createtabletb1用戶表(idintnotnullauto_increment?primarykey,namechar(10),?????????????????????department_idint,?????????????????????p_idint,?????????????????)engine=innodbdefaultcharset=utf8;
主鍵(primary key)一個表只能有一個主鍵，主鍵可以由一列或者多列組成
外鍵的創(chuàng)建
CREATETABLEt5?(???????????????????????nidint(11)NOTNULLAUTO_INCREMENT,???????????????????????pidint(11)notNULL,numint(11),???????????????????????primarykey(nid,pid)--這里就是把兩列設置成了主鍵?)ENGINE=InnoDBDEFAULTCHARSET=utf8;createtablet6(idintauto_increment?primarykey,namechar(10),?????????????????????????id1int,?????????????????????????id2int,CONSTRAINTfk_t5_t6?foreignkey(id1,id2)REFERENCESt1(nid,pid)--這里是設置外鍵?)engine=innodbdefaultcharset=utf8;
數據行的操作
數據的插入
insertintotb1(name,age)values('ax',8);insertintotb12(name,age)selectname,agefromtb11;
表中的數據的刪除
deletefromt1;truncatetablet1;droptablet1deletefromtb1whereid>10deletefromtb12whereid>=2orname='alex'
數據的更新
updatetb1setname='root'whereid>10
數據的查詢
select*fromtb;selectid,namefromtb;
表結構的查看
showcreatetablet1;?desc?t1;
其他
select*fromtb12whereid!=1select*fromtb12whereidin(1,5,12);select*fromtb12whereidnotin(1,5,12);select*fromtb12whereidin(selectidfromtb11)select*fromtb12whereidbetween5and12;
通配符
select*fromtb12wherenamelike"a%"select*fromtb12wherenamelike"a_"
分頁
select*fromtb12?limit10;select*fromtb12?limit0,10;select*fromtb12?limit10,10;select*fromtb12?limit20,10;select*fromtb12?limit10offset20;#?page?=?input('請輸入要查看的頁碼')?#?page?=?int(page)?#?(page-1)?*?10?#?select?*?from?tb12?limit?0,10;?1??#?select?*?from?tb12?limit?10,10;2?
排序
select*fromtb12orderbyiddesc;?大到小select*fromtb12orderbyidasc;??小到大select*fromtb12orderbyagedesc,iddesc;??取后10條數據select*fromtb12orderbyiddesclimit10;
分組
selectcount(id),max(id),part_idfromuserinfo5groupbypart_id;?聚合函數有下面幾個：???????????????????????????????????????count?????????????????????max?????????????????????min?????????????????????sum?????????????????????avg??****?如果對于聚合函數結果進行二次篩選時？必須使用having?****selectcount(id),part_idfromuserinfo5groupbypart_id?havingcount(id)?>?1;selectcount(id),part_idfromuserinfo5whereid?>?0groupbypart_id?havingcount(id)?>?1;
自增值設置
表自增值的設置
altertablet1?auto_increment=20;--?這個就表示從開始20開始算，用上面的show?create?table?t1\G?就可以看到當前的值是多少。?
基于會話級別
--?查看當前的會話值?showsessionvariableslike'auto_incre%'--?設置會話步長?setsessionauto_increment_increment=2;--?設置起始值?setsessionauto_increment_offset=10;
基于全局設置
--?查看全局的設置值?showglobalvariableslike'auto_inc%';--?設置全局步長值?setglobalauto_increment_increment=3;--?設置起始值?setglobalauto_increment_offset=11;
sql server 是在創(chuàng)建表的時候就可以自己設置，靈活度很高REATE TABLE t5 (nid int(11) NOT NULL AUTO_INCREMENT,pid int(11) NOT NULL,num int(11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4, 步長=2 DEFAULT CHARSET=utf8
CREATE TABLE `t6` (
nid int(11) NOT NULL AUTO_INCREMENT,pid int(11) NOT NULL,num int(11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4, 步長=20 DEFAULT CHARSET=utf8
唯一索引
createtablet1(idint,numint,?????xxint,uniquequ1?(num,xx)--?意思就是這兩列在一行上面數據不能相同，例如都是1,1,就不行?);
唯一索引：約束不能重復(可以為空)主鍵索引：約束不能重復(不可以為空)他們的特點都是加速查詢
外鍵一對一
createtableuserinfo1(idintauto_increment?primarykey,namechar(10),?????????????????????genderchar(10),?????????????????????emailvarchar(64)?????????????????)engine=innodbdefaultcharset=utf8;createtableadmin(idintnotnullauto_increment?primarykey,?????????????????????usernamevarchar(64)notnull,passwordVARCHAR(64)notnull,?????????????????????user_idintnotnull,uniqueuq_u1?(user_id),CONSTRAINTfk_admin_u1?FOREIGNkey(user_id)REFERENCESuserinfo1(id)?????????????????)engine=innodbdefaultcharset=utf8;
外鍵多對多
示例1：?????????????????用戶表?????????????????相親表??????????????示例2：?????????????????用戶表?????????????????主機表?????????????????用戶主機關系表?????????????===》多對多createtableuserinfo2(idintauto_increment?primarykey,namechar(10),?????????????????????genderchar(10),?????????????????????emailvarchar(64)?????????????????)engine=innodbdefaultcharset=utf8;createtablehost(idintauto_increment?primarykey,?????????????????????hostnamechar(64)?????????????????)engine=innodbdefaultcharset=utf8;createtableuser2host(idintauto_increment?primarykey,?????????????????????useridintnotnull,?????????????????????hostidintnotnull,uniqueuq_user_host?(userid,hostid),CONSTRAINTfk_u2h_user?FOREIGNkey(userid)REFERENCESuserinfo2(id),CONSTRAINTfk_u2h_host?FOREIGNkey(hostid)REFERENCEShost(id)?????????????????)engine=innodbdefaultcharset=utf8;
連表操作
select*fromuserinfo5,department5select*fromuserinfo5,department5whereuserinfo5.part_id?=?department5.idselect*fromuserinfo5leftjoindepartment5onuserinfo5.part_id?=?department5.idselect*fromdepartment5leftjoinuserinfo5onuserinfo5.part_id?=?department5.id?????????????????????#?userinfo5左邊全部顯示??????????????????????#select*fromuserinfo5rightjoindepartment5onuserinfo5.part_id?=?department5.id?????????????????????#?department5右邊全部顯示select*fromuserinfo5?innderjoindepartment5onuserinfo5.part_id?=?department5.id?????????????????????將出現null時一行隱藏select*fromdepartment5leftjoinuserinfo5onuserinfo5.part_id?=?department5.idleftjoinuserinfo6onuserinfo5.part_id?=?department5.idselectscore.sid,?????????????????????????student.sidfromscoreleftjoinstudentonscore.student_id?=?student.sidleftjoincourseonscore.course_id?=?course.cidleftjoinclassonstudent.class_id?=?class.cidleftjointeacheroncourse.teacher_id=teacher.tiselectcount(id)fromuserinfo5;
