SQL--日常使用2

1、Limit  

SELECT * from leader LIMIT 2;

2、like通配符 略   

3、in  IN 操作符允许您在 WHERE 子句中规定多个值。

SELECT * from leader WHERE phone IN('19000000007','19000000008')

4、between  略

5、as   别名

select phone as 电话号码 from leader

6、SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

表1 Persons   表2 Orders

 

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

　　下面两条执行结果一致

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo  FROM Persons, Orders   WHERE Persons.Id_P = Orders.Id_P 

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo  FROM Persons  INNER JOIN Orders   ON Persons.Id_P = Orders.Id_P 

7、Left jion

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName                  结果如下

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George

LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

8、right join 与left join 一样

9、union

操作符用于合并两个或多个 SELECT 语句的结果集