sql 之 你还只是会增删改查吗?

1. top子句.

　　select top number| percent column_name(s) from table_name

2. like 操作符

　　select column_name(s) from table_name where column_name (not)like pattern

3. sql 通配符

　　% 代替一个或者多个字符

　　_ 仅仅代替一个字符

　　[charlist] 字符列中任何单一字符

　　[^charlist] [!charlist] 不在字符列中的任何单一字符

4. in 操作符

　　select column_name(s) from table_name where column_name in (value1, value2,...)

5. between 操作符

　　select column_name(s) from table_name where column_name between value1 and value2(包括value1 , 不包括value2)

6. sql alias

　　select column_names from table_name as alias_name

7, sql join : used in two or more table's relationship

　　The tables in database is connected by key. Primary key is a cloumn that unique.

 the below two sql scripts hase same function:

select persons.lastname, persons.firstname, orders.orderNo from persons, orders where persons.id_p = orders.id_p

select persons.lastname, person.firstname, orders.orderNo from persons inner join orders on persons.id_p = orders.id_p order by persons.lastname

• JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

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

• 原始的表 (用在例子中的)：

  "Persons" 表：

  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

  "Orders" 表：

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

• 结果集：

  LastName	FirstName	OrderNo
  Adams	John	22456
  Adams	John	24562
  Carter	Thomas	77895
  Carter	Thomas	44678
  		34764

• FULL JOIN: 只要其中一个表中存在匹配，就返回行

• SELECT column_name(s)
  FROM table_name1
  FULL JOIN table_name2 
  ON table_name1.column_name=table_name2.column_name

• SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
  FROM Persons
  FULL 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
  		34764

  FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配，或者如果 "Orders" 中的行在表 "Persons" 中没有匹配，这些行同样会列出。

8. sql union & union all

　　SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2

　　SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2

　　union会去重. union all则不会

9. sql select into

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename

in 子句可用于向另一个数据库中拷贝表

SELECT *
INTO Persons IN 'Backup.mdb'
FROM Persons