《MySQL经典语句总结》由会员分享，可在线阅读，更多相关《MySQL经典语句总结（6页珍藏版）》请在金锄头文库上搜索。
1、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 * FR
2、OM 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;
3、 /直接读取数据插入数据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 BY
4、SELECT 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
5、FirstName=Thomas AND LastName=CarterSELECT * FROM Persons WHERE firstname=Thomas OR lastname=CarterPHPer.yang 文章整理集合侯惠阳SELECT * FROM Persons WHERE (FirstName=Thomas OR FirstName=William)AND LastName=CarterINSELECT 列名称 FROM 表名称 WHERE 列名称 IN (值 1,值 2,.) SELECT * FROM Persons WHERE LastName IN (Adams,G
6、ates)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 FRO
7、M 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 JOI
8、N 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, Ord
9、ersWHERE 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, field
10、2, field3FROM first_tableLEFT JOIN second_tablePHPer.yang 文章整理集合侯惠阳ON 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_tab
11、leON 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 cros
12、s 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_USASQ
13、L Statement 1UNION ALLSQL Statement 2CreateCREATE DATABASE 数据库名称CREATE TABLE 表名称(列名称 1 数据类型,列名称 2 数据类型,.)PHPer.yang 文章整理集合侯惠阳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 U
14、NIQUE 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 表名称
15、ALTER TABLEALTER TABLE 表名称 ADD 列名称 数据类型ALTER TABLE 表名称 DROP COLUMN 列名称ALTER TABLE Person ADD City varchar(30)ALTER TABLE Person DROP COLUMN AddressFUNCTIONSSELECT function(列) FROM 表PHPer.yang 文章整理集合侯惠阳GROUP BY 以及 HAVINGSELECT column,SUM(column) FROM table GROUP BY columnSELECT Company,SUM(Amount) FR
16、OM 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
