本文讲的是MySQL开发创建数据库必备的sql语句createdatabasedb1；删除数据库dropdatabasedb1;创建数据表createtabletb1用户表(idintnotnullauto_incrementprimarykey,namechar(10),department_idint,p_idint,)engine=innodbdefaultcharset=utf8;主键（primarykey）一张表只能有一个主键，主键可以由一列或多列组成。外键创建CREATETABLEt5(nidint(11)NOTNULLAUTO_INCREMENT,pidint(11)notNULL,numint(11),primarykey(nid,pid)--这里是将两个列设置为主键）ENGINE=InnoDBDEFAULTCHARSET=utf8;createtablet6(idintauto_incrementprimarykey,namechar(10),id1int,id2int,CONSTRAINTfk_t5_t6foreignkey(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;desct1;otherselect*fromtb12whereid!=1select*fromtb12whereidin(1,5,12);选择*fromtb12whereidnotin(1,5,12);select*fromtb12whereidin(selectidfromtb11)select*fromtb12whereidbetween5and12;通配符select*fromtb12wherenamelike"a%"select*fromtb12wherenamelike"a_"页面select*fromtb12limit10;select*fromtb12limit0,10;select*fromtb12limit10,10;select*fromtb12limit20,10;select*fromtb12limit10offset20;#page=input('请输入你要查看的页码')#page=int(page)#(page-1)*10#select*fromtb12limit0,10;1#select*fromtb12limit10,10;2排序select*fromtb12orderbyiddesc;从大到小select*fromtb12orderbyidasc;从小到大select*fromtb12orderbyagedesc,iddesc;取出10条数据后select*fromtb12orderbyiddesclimit10;groupingselectcount(id),max(id),part_idgromuserinfo5有如下：countmaxminsumavg****如果对聚合函数结果进行二次筛选呢？必须使用having****selectcount(id),part_idfromuserinfo5groupbypart_idhavingcount(id)>1;selectcount(id),part_idfromuserinfo5whereid>0groupbypart_idhavingcount(id)>1;自增设置表altertablet1auto_increment=20;--意思是从20开始计数，用上面的showcreatetablet1\G看看当前值是多少。基于session级别--查看当前session值showsessionvariableslike'auto_incre%'--设置会话步长setsessionauto_increment_increment=2;--设置初始值setsessionauto_increment_offset=10;基于全局设置--查看全局设置值showglobalvariableslike'auto_inc%';--设置全局步长值setglobalauto_increment_increment=3;--设置初始值setglobalauto_increment_offset=11;SQLServer可以在建表时自行设置，非常灵活REATETABLEt5(nidint(11)NOTNULLAUTO_INCREMENT,pidint(11)NOTNULL,numint(11)DEFAULTNULL,PRIMARYKEY(nid,pid))ENGINE=InnoDBAUTO_INCREMENT=4,stepsize=2DEFAULTCHARSET=utf8CREATETABLE`t6`(nidint(11)NOTNULLAUTO_INCREMENT,pidint(11)NOTNULL,numint(11)DEFAULTNULL,PRIMARYKEY(nid,pid))ENGINE=InnoDBAUTO_INCREMENT=4,stepsize=20DEFAULTCHARSET=utf8uniqueindexcreatetablet1(idint,numint,xxint,uniquequ1(num,xx)--意思是这两列的数据不能是同一行，比如都是1,1,就不行);uniqueindex:constraintcannotberepeated(canbeempty)primarykeyindex:constraintcannotrepeated(cannotbeempty)它们的特点两者加速查询外键一对一createtableuserinfo1(idintauto_incrementprimarykey,namechar(10),genderchar(10),emailvarchar(64))engine=innodbdefaultcharset=utf8;createtableadmin(idintnotnullauto_incrementprimarykeymarykey,usernamevarchar(64)notnull,passwordVARCHAR(64)notnull,user_idintnotnull,uniqueuq_u1(user_id),CONSTRAINTfk_admin_u1FOREIGNkey(user_id)REFERENCESuserinfo1(id))engine=innodbdefaultcharset=utf8;:usertablehosttableuserhostrelationshiptable==="many-to-manycreatetableuserinfo2(idintauto_incrementprimarykey,namechar(10),genderchar(10),emailvarchar(64))engine=innodbdefaultcharset=utf8;createtablehost(idintauto_incrementprimarykey,hostnamechar(64)))engine=innodbdefaultcharset=utf8;createtableuser2host(idintauto_incrementprimarykey,useridintnotnull,hostidintnotnull,uniqueuq_user_host(userid,hostid),CONSTRAINTfk_u2h_userFOREIGNkey(userid)REFERENCESuserinfo2(id),CONSTRAINTfk_u2h_hostFOREIGNkey(hostid)REFERENCEShost(id))engine=innodbdefaultcharset=utf8;连表操作选择*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全部显示在右边.course_id=course.cidleftjoinclassonstudent.class_id=class.cidleftjointeacheroncountcourse.teacherofuser5
