-- Mysql 基础 INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....); UPDATE tb_name SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson' DELETE FROM 表名称 WHERE 列名称 = 值 // 列出不同(distinct)的值,重复值去掉 SELECT DISTINCT username FROM tb_name; // top SELECT TOP number|percent column_name(s) FROM table_name //基础语法 SELECT TOP 2 * FROM tb_name; //查询前2条 SELECT TOP 50 PERCENT * FROM tb_name; //查询前面的50%; // 通配符 % // 0个多个 _ // 匹配一个字符 [charlist] //匹配字符列中任意个字符 [^charlist]或者[!charlist] // 不在字符列中任意字符 SELECT * FROM Persons WHERE LastName LIKE 'C_r_er' SELECT * FROM Persons WHERE City LIKE '[ALN]%'; //查询城市名开头为A、L、N开头的(或) SELECT * FROM Persons WHERE City LIKE '[!ALN]%' //城市开头A L N 的排除 // IN SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...) SELECT * FROM table_name WHERE user_id IN (2,3,5) //操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。 // AS //假设我们有两个表分别是:"Persons" 和 "Product_Orders"。我们分别为它们指定别名 "p" 和 "po"。现在,我们希望列出 "John Adams" 的所有定单。 SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Adams' AND p.FirstName='John' //不使用别名sql如下 SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName FROM Persons, Product_Orders WHERE Persons.LastName='Adams' AND Persons.FirstName='John' //例2 SELECT LastName AS Family, FirstName AS NameFROM Persons;
//INNER JOIN(内连接)
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName
查询结果:
// LEFT JOIN (左连接)
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P=Orders.Id_O ORDER BY Persons.LastName
查询结果:
// RIGHT JOIN (右连接)
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName
// FULL JOIN (右连接)
只要其中某个表存在匹配,FULL JOIN 关键字就会返回行。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName