资源描述:
《mysql经典语句总结》由会员上传分享,免费在线阅读,更多相关内容在应用文档-天天文库。
1、PHPer.yang文章整理集合MySQL经典语句总结(侯惠阳PHPer.yang)SELECTSELECT列名称FROM表名称SELECTLastName,FirstNameFROMPersonsSELECT*FROMPersonsSELECTDISTINCT列名称FROM表名称SELECTDISTINCTCompanyFROMOrders//去除重复的distinctWHERESELECT列名称FROM表名称WHERE列运算符值SELECT*FROMPersonsWHERECity='Beijing'SELECT*FROMPe
2、rsonsWHEREYear>1965INSERTINTOINSERTINTO表名称VALUES(值1,值2,....)INSERTINTOtable_name(列1,列2,...)VALUES(值1,值2,....)INSERTINTOPersonsVALUES('Gates','Bill','Xuanwumen10','Beijing')INSERTINTOPersons(LastName,Address)VALUES('Wilson','Champs-Elysees')Insertintotable()select*from
3、name;//直接读取数据插入数据UpdateUPDATE表名称SET列名称=新值WHERE列名称=某值UPDATEPersonSETFirstName='Fred'WHERELastName='Wilson'UPDATEPersonSETAddress='Zhongshan23',City='Nanjing'WHERELastName='Wilson'DELETEDELETEFROM表名称WHERE列名称=值DELETEFROMPersonWHERELastName='Wilson'DELETEFROMtable_nameORD
4、ERBYSELECTCompany,OrderNumberFROMOrdersORDERBYCompanySELECTCompany,OrderNumberFROMOrdersORDERBYCompany,OrderNumberSELECTCompany,OrderNumberFROMOrdersORDERBYCompanyDESCSELECTCompany,OrderNumberFROMOrdersORDERBYCompanyDESC,OrderNumberASCAND&ORSELECT*FROMPersonsWHEREFirs
5、tName='Thomas'ANDLastName='Carter'SELECT*FROMPersonsWHEREfirstname='Thomas'ORlastname='Carter'侯惠阳PHPer.yang文章整理集合SELECT*FROMPersonsWHERE(FirstName='Thomas'ORFirstName='William')ANDLastName='Carter'INSELECT列名称FROM表名称WHERE列名称IN(值1,值2,..)SELECT*FROMPersonsWHERELastNameIN
6、('Adams','Gates')BETWEEN...ANDSELECT列名称FROM表名称WHERE列名称BETWEEN值1AND值2SELECT*FROMPersonsWHERELastNameBETWEEN'Adams'AND'Carter'SELECT*FROMPersonsWHERELastNameNOTBETWEEN'Adams'AND'Carter'AliasSELECT列名称AS列的别名FROM表名称SELECT列名称FROM表名称AS表的别名SELECTLastNameASFamily,FirstNameASNa
7、meFROMPersonsSELECTLastName,FirstNameFROMPersonsASEmployeesJoinSELECT*FROMtx1leftjoin(tx2,tx3)ON(tx1.id=tx2.tidANDtx2.tid=tx3.tid)wheretx1.id=3SELECT*FROMt1LEFTJOIN(t2,t3,t4)ON(t2.a=t1.aANDt3.b=t1.bANDt4.c=t1.c)等价于:SELECT*FROMt1LEFTJOIN(t2CROSSJOINt3CROSSJOINt4)ON(t2.
8、a=t1.aANDt3.b=t1.bANDt4.c=t1.c)在MySQL中,CROSSJOIN语法上等价于INNERJOIN(它们可以彼此代替。在标准SQL中,它们不等价。INNERJOIN结合ON子句使用;CROSSJOIN用于其它地方。SEL