MySQL 經(jīng)典語句總結

1、MySQL 經(jīng)典語句總結（侯惠陽 PHPer.yang）SELECTSELECT 列名稱 FROM 表名稱SELECT LastName,FirstName FROM PersonsSELECT * FROM PersonsSELECT DISTINCT 列名稱 FROM 表名稱SELECT DISTINCT Company FROM Orders /去除重復的distinctWHERESELECT 列名稱 FROM 表名稱 WHERE 列 運算符 值SELECT * FROM Persons WHERE City='Beijing'SELECT * FROM Persons

2、WHERE Year>1965INSERT INTOINSERT INTO 表名稱 VALUES (值1, 值2,.)INSERT INTO table_name (列1, 列2,.) VALUES (值1, 值2,.)INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysee

3、s')Insert into table () select * from name; /直接讀取數(shù)據(jù)插入數(shù)據(jù)UpdateUPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'WHERE LastName = 'Wilson'DELETEDELE

4、TE FROM 表名稱 WHERE 列名稱 = 值DELETE FROM Person WHERE LastName = 'Wilson'DELETE FROM table_nameORDER BYSELECT Company, OrderNumber FROM Orders ORDER BY CompanySELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumberSELECT Company, OrderNumber FROM Orders ORDER BY Company DESCSELECT

5、Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASCAND & ORSELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'SELECT * FROM Persons WHERE (FirstName='Thomas

6、' OR FirstName='William')AND LastName='Carter'INSELECT 列名稱 FROM 表名稱 WHERE 列名稱 IN (值1,值2,.) SELECT * FROM Persons WHERE LastName IN ('Adams','Gates')BETWEEN . ANDSELECT 列名稱 FROM 表名稱 WHERE 列名稱 BETWEEN 值1 AND 值2SELECT * FROM Persons WHERE LastName BETWEEN 'Adams&

7、#39; AND 'Carter'SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'AliasSELECT 列名稱 AS 列的別名 FROM 表名稱SELECT 列名稱 FROM 表名稱 AS 表的別名SELECT LastName AS Family, FirstName AS Name FROM PersonsSELECT LastName, FirstName FROM Persons AS EmployeesJoin SELECT * FROM tx1

8、 left join (tx2, tx3) ON (tx1.id=tx2.tid AND tx2.tid=tx3.tid) where tx1.id = 3SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)等價于：SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)在MySQL中，CROSS JOIN語法上等價于INNER JOIN

9、 (它們可以彼此代替。在標準SQL中，它們不等價。INNER JOIN結合ON子句使用；CROSS JOIN 用于其它地方。SELECT Employees.Name, Orders.Product FROM Employees, OrdersWHERE Employees.Employee_ID = Orders.Employee_ID SELECT Employees.NameFROM Employees, OrdersWHERE Employees.Employee_ID = Orders.Employee_IDAND Orders.Product = 'Printer'

10、 SELECT 字段1, 字段2, 字段3FROM 第一個表INNER JOIN 第二個表ON 第一個表.keyfield = 第二個表.foreign_keyfield SELECT Employees.Name, Orders.ProductFROM EmployeesINNER JOIN OrdersON Employees.Employee_ID=Orders.Employee_IDSELECT field1, field2, field3FROM first_tableLEFT JOIN second_tableON first_table.keyfield = second_tab

11、le.foreign_keyfield SELECT Employees.Name, Orders.ProductFROM EmployeesLEFT JOIN OrdersON Employees.Employee_ID=Orders.Employee_ID SELECT field1, field2, field3FROM first_tableRIGHT JOIN second_tableON first_table.keyfield = second_table.foreign_keyfield SELECT Employees.Name, Orders.ProductFROM Emp

12、loyeesRIGHT JOIN OrdersON Employees.Employee_ID=Orders.Employee_ID SELECT Employees.NameFROM EmployeesINNER JOIN OrdersON Employees.Employee_ID=Orders.Employee_IDWHERE Orders.Product = 'Printer' cross join ：笛卡爾交集。Inner join ：內連接。left join ：只要滿足左邊表的需求就可以了,右表有無都可以。right join ：只要滿足右表的需求就可以了,左表有

13、無都可以。UNION SQL Statement 1UNIONSQL Statement 2注意：查看一樣的字段,union 是將兩個結果集并在一起,篩選出重復的數(shù)據(jù), union all 只是簡單的將兩個結果結果合并,但是union all 比union 快。SELECT E_Name FROM Employees_ChinaUNIONSELECT E_Name FROM Employees_USASQL Statement 1UNION ALLSQL Statement 2CreateCREATE DATABASE 數(shù)據(jù)庫名稱CREATE TABLE 表名稱(列名稱1 數(shù)據(jù)類型,列名稱2

14、數(shù)據(jù)類型,.)CREATE TABLE Person (LastName varchar,FirstName varchar,Address varchar,Age int) CREATE TABLE Person (LastName varchar(30),FirstName varchar,Address varchar,Age int(3)CREATE UNIQUE INDEX 索引名稱ON 表名稱 (列名稱) CREATE INDEX 索引名稱ON 表名稱 (列名稱)CREATE INDEX PersonIndexON Person (LastName) CREATE INDEX Pe

15、rsonIndexON Person (LastName DESC) CREATE INDEX PersonIndexON Person (LastName, FirstName)DropDROP INDEX index_name ON table_nameDROP TABLE 表名稱DROP DATABASE 數(shù)據(jù)庫名稱TRUNCATE TABLE 表名稱ALTER TABLEALTER TABLE 表名稱 ADD 列名稱 數(shù)據(jù)類型ALTER TABLE 表名稱 DROP COLUMN 列名稱ALTER TABLE Person ADD City varchar(30)ALTER TABLE

16、 Person DROP COLUMN AddressFUNCTIONSSELECT function(列) FROM 表GROUP BY 以及 HAVINGSELECT column,SUM(column) FROM table GROUP BY columnSELECT Company,SUM(Amount) FROM SalesGROUP BY CompanySELECT column,SUM(column) FROM tableGROUP BY columnHAVING SUM(column) condition valueSELECT Company,SUM(Amount) FROM

17、 SalesGROUP BY CompanyHAVING SUM(Amount)>10000SELECT INTOSELECT column_name(s) INTO newtable IN externaldatabase FROM sourceSELECT * INTO Persons_backup FROM PersonsSELECT LastName,Firstname INTO Persons_backupFROM PersonsWHERE City='Sandnes'Create ViewCREATE VIEW 視圖名稱 AS SELECT 列名稱 FROM

18、表名稱 WHERE 條件CREATE VIEW Current Product List ASSELECT ProductID,ProductNameFROM ProductsWHERE Discontinued=NoCREATE VIEW Products Above Average Price ASSELECT ProductName,UnitPriceFROM ProductsWHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)SELECT * FROM Current Product ListCREATE VIEW Category Sales For 1997 ASSELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySalesFROM