MySQL经典语句总结.doc

PHPer.yang文章整理集合MySQL 经典语句总结（侯惠阳 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=BeijingSELECT * FROM Persons WHERE Year1965INSERT 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-Elysees)Insert into table () select * from name; /直接读取数据插入数据UpdateUPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值UPDATE Person SET FirstName = Fred WHERE LastName = WilsonUPDATE Person SET Address = Zhongshan 23, City = NanjingWHERE LastName = WilsonDELETEDELETE FROM 表名称 WHERE 列名称 = 值DELETE FROM Person WHERE LastName = WilsonDELETE 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 Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASCAND & ORSELECT * FROM Persons WHERE FirstName=Thomas AND LastName=CarterSELECT * FROM Persons WHERE firstname=Thomas OR lastname=CarterSELECT * FROM Persons WHERE (FirstName=Thomas OR FirstName=William)AND LastName=CarterINSELECT 列名称 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 AND CarterSELECT * FROM Persons WHERE LastName NOT BETWEEN Adams AND CarterAliasSELECT 列名称 AS 列的别名 FROM 表名称SELECT 列名称 FROM 表名称 AS 表的别名SELECT LastName AS Family, FirstName AS Name FROM PersonsSELECT LastName, FirstName FROM Persons AS EmployeesJoin SELECT * FROM tx1 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 (它们可以彼此代替。在标准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 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_table.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 EmployeesRIGHT 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 ：只要满足右表的需求就可以了,左表有无都可以。UNION SQL Statement 1UNIONSQL Statement 2注意：查看一样的字段,union 是将两个结果集并在一起,筛选出重复的数据, union all 只是简单的将两个结果结果合并,但是union all 比union 快。SELECT E_Name FROM Employees_ChinaUNIONSELECT E_Name FROM Employees_USASQL Statement 1UNION ALLSQL Statement 2CreateCREATE DATABASE 数据库名称CREATE TABLE 表名称(列名称1 数据类型,列名称2 数据类型,.)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 PersonIndexON Person (LastName DESC) CREATE INDEX PersonIndexON Person (LastName, FirstName)DropDROP INDEX index_name ON table_nameDROP TABLE 表名称DROP DATABASE 数据库名称TRUNCATE TABLE 表名称ALTER TABLEALTER TABLE 表名称 ADD 列名称 数据类型ALTER TABLE 表名称 DROP COLUMN 列名称ALTER TABLE Person ADD City varchar(30)ALTER TABLE 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 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=SandnesCreate ViewCREATE VIEW 视图名称 AS SELECT 列名称 FROM 表名称 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 Product Sales for 1997GROUP BY CategoryName SELECT * FROM Category Sales For 1997WHERE CategoryName=Beverages正则匹配:SELECT 列 FROM 表 WHERE 列 LIKE 模式SELECT * FROM Persons WHERE FirstName LIKE B%SELECT * FROM Persons WHERE FirstName LIKE %sSELECT * FROM Persons WHERE FirstName