黑马程序员-005SQL语句锦集

---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ --------------------------------------------

1.创建、删除表：

CREATE TABLE T_Person(Id int NOT NULL,Name nvarchar(50),Age int NULL)

Drop table T_Person1

 2.插入数据：

INSERT INTO T_Person(Id,Name,Age) VALUES(1,'Jim',20)

3.主键可以用GUID码

SQLServer中生成GUID的函数newid()，.Net中生成Guid的方法：Guid.NewGuid()

 4.更新：

UPDATE T_Person Set Age=30 Where Name=’LiLei’

 5.检索：

SELECT * From T_Person Where Age<20 and Age>15（可以检索不相关的数据，例如select 1+1;select newid();select getdate();）

6.删除数据：

  DELETE FROM T_Person WHERE FAge > 20

7.数据汇总：

  SELECT MAX(FSalary) FROM T_Employee WHERE FAge>25

8.数据排序：

  SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC

9.通配符：

  SELECT * FROM T_Employee WHERE FName LIKE '%n%'

10.空值处理：

  SELECT * FROM T_Employee WHERE FNAME is null

11.多值匹配：

SELECT FAge,FNumber,FName FROM T_Employee WHERE FAge IN (23,25,28)

SELECT * FROM T_Employee WHERE FAGE>=23 AND FAGE <=27

12.数据分组：

  SELECT FAge,Count(*) FROM T_Employee GROUP BY Fage

13.Having语句：

  SELECT FAge,COUNT(*) AS人数 FROMT_Employee GROUP BY FAge HAVING COUNT(*)>1

14.限制结果集行数：

  select top 5 * from T_Employee order by FSalary Desc

15.抑制重复数据：

  SELECT DISTINCT FDepartment FROM T_Employee

16.联合结果集：

  SELECT FNumber,FName,FAge FROM T_Employee UNION SELECT FIdCardNumber, FName, FAge FROM T_TempEmployee

17.允许重复的联合结果集：

  SELECT FName FROM T_Employee UNION ALL SELECT FName FROM T_TempEmployee

18.表连接Join

  select o.BillNo,c.Name,c.Age from T_Orders as o join T_Customers as c on o.CustomerId=c.Id

19.子查询

  SELECT 1 AS f1,2,(SELECT MIN(FYearPublished) FROM T_Book),(SELECT MAX(FYearPublished) FROM T_Book) AS f4

---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ --------------------------------------------