sqlserver语句整理.docVIP

以下的sql语句是对网页/sql/index.asp ? 中的SQL教程的整理，适用于初步学习SQL的同事O(∩_∩)O~ ? 红色部分为知识点 ? SELECT ? SELECT * FROM Persons WHERE FirstName=BushSELECT * FROM Persons WHERE Year1965 ? SELECT * FROM Persons WHERE (FirstName=Thomas OR FirstName=William) AND LastName=Carter ? INSERT INTO Persons VALUES (Gates, Bill, Xuan 10, Beijing)INSERT INTO Persons (LastName, Address) VALUES (Wilson, Champs-Elysees) ? UPDATE Person SET Address = Zhongshan 23, City = Nanjing WHERE LastName = Wilson ? DELETE FROM Person WHERE LastName = Wilson/删除所有行：可以在不删除表的情况下删除所有的行/DELETE FROM? 表?? 或者? DELETE * FROM? 表 ? TOPSERVER 语句SELECT TOP number|percent 列 FROM 表SELECT TOP 50 PERCENT * FROM PersonsORACLE 语句SELECT * FROM Persons WHERE ROWNUM = 5 ? SELECT * FROM Persons WHERE City NOT? LIKE N% ? SQL 通配符% 替代一个或多个字符_?? 替代一个字符[charlist] 字符列中的任何单一字符[^charlist]或[!charlist] 不在字符列中的任何单一字符从 Persons 表选取居住的城市（不）以 A 或 L 或 N 开头的人：SELECT * FROM Persons WHERE City LIKE [ALN]%或[!ALN]% ? SELECT * FROM Persons WHERE LastName IN (Adams,Carter) ? /以字母顺序显示介于 Adams和 Carter之间的人/SELECT * FROM Persons WHERE LastName NOT? BETWEEN Adams AND Carter ? SELECT po.OrderID, p.LastName, p.FirstNameFROM Persons AS p, Product_Orders AS poWHERE p.LastName=Adams AND p.FirstName=John JOIN语句 参照/sql/sql_join.asp ? SELECT P.LastName, P.FirstName, O.OrderNoFROM Persons AS P, Orders AS OWHERE P.Id_P = O.Id_P或者：SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsINNER JOIN OrdersON Persons.Id_P = Orders.Id_PORDER BY Persons.LastNameINNER JOIN 与 JOIN 相同: 如果表中有至少一个匹配，则返回行LEFT JOIN: 从左表返回所有的行RIGHT JOIN: 从右表返回所有的行FULL JOIN: 只要其中一个表中存在匹配，就返回行 ? UNION内部SELECT 语句的列必须数量相同、数据类型相似、顺序相同SELECT E_Name FROM Employees_ChinaUNION (ALL)SELECT E_Name FROM Employees_USAUNION 命令只会选取不同的值，UNION ALL 命令会列出所有的值 ? SELECT INTO 语句从一个表中选取数据插入另一个表中或创建表的备份或对记录存档制作 Persons 表的备份复件：SELECT * INTO Persons_backup FROM Persons向另一个数据库中拷贝表：SELECT * INTO Persons IN Backup.mdb FROM Persons创建名为 Persons_backup 的表从 Persons 表中提取居住在 Beijing 的人的信息