MySQL经典语句总结6200字.docx
20页
MySQL经典语句总结6200字 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='Beijing'SELECT * FROM Persons 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-Elysees')Insert into table () select * from name; //直接读取数据插入数据UpdateUPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'WHERE LastName = 'Wilson'DELETEDELETE 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, OrderNumber SELECT 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='Carter'SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'SELECT * FROM Persons WHERE (FirstName='Thomas' 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' 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 EmployeesJoinSELECT * 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_IDSELECT Employees.NameFROM Employees, OrdersWHERE Employees.Employee_ID = Orders.Employee_IDAND Orders.Product = 'Printer'SELECT 字段1, 字段2, 字段3FROM 第一个表INNER JOIN 第二个表ON 第一个表.keyfield = 第二个表.foreign_keyfieldSELECT 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_keyfieldSELECT Employees.Name, Orders.ProductFROM EmployeesLEFT JOIN OrdersON Employees.Employee_ID=Orders.Employee_IDSELECT field1, field2, field3FROM first_tableRIGHT JOIN second_tableON first_table.keyfield = second_table.foreign_keyfieldSELECT Employees.Name, Orders.ProductFROM EmployeesRIGHT JOIN OrdersON Employees.Employee_ID=Orders.Employee_IDSELECT Employees.NameFROM EmployeesINNER JOIN OrdersON Employees.Employee_ID=Orders.Employee_IDWHERE Orders.Product = 'Printer'cross join ：笛卡尔交集。
Inner join ：内连接left join ：只要满足左边表的需求就可以了,右表有无都可以right join ：只要满足右表的需求就可以了,左表有无都可以UNIONSQL 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_n。
