常用SQL语句

建表

create table T_Person (Id int not null,Name nvarchar(50),Age int null)

插入

insert into T_Person (Id,Name,Age) values (1,'tom',30)

insert into T_Person values(3,'lily',33,GETDATE())

更新

update T_Person set Age=40 where name='tom' or Age>20

更改表

ALTER TABLE T_Employee ADD FSubCompany VARCHAR(20);

删除表中数据

delete from T_Person

DELETE FROM T_Person WHERE Age > 32

清空表中数据

Truncate table T_Person

删除表

drop table  T_Person

检索

简单的数据检索 ：SELECT * FROM T_Employee
只检索需要的列 ：SELECT FNumber FROM T_Employee 、SELECT FName,FAge FROM T_Employee
列别名：SELECT FNumber AS 编号,FName AS 姓名,FAge AS Age111 FROM T_Employee 
使用where检索符合条件的数据：SELECT FName FROM T_Employee WHERE FSalary<5000。故事：新员工的数据检索噩梦。

还可以检索不与任何表关联的数据：select 1+1;select newid();select getdate();

数据汇总

SQL聚合函数：MAX（最大值）、MIN（最小值）、AVG （平均值）、SUM （和）、COUNT（数量）

大于25岁的员工的最高工资 ：

    SELECT MAX(FSalary) FROM T_Employee WHERE FAge>25 

最低工资和最高工资：

    SELECT MIN(FSalary),MAX(FSalary) FROM  T_Employee 

select max(fsalary) as '最高工资',min(fsalary) as '最低工资',avg(fsalary) as '平均工资',sum(fsalary) as '总工资',count(fsalary) as '总人数' from T_employee

数据排序

ORDER BY子句位于SELECT语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序（从小到大排列，ASC）还是降序（从大到小排列，DESC）。 
按照年龄升序排序所有员工信息的列表：
    SELECT * FROM  T_Employee ORDER BY FAge ASC 
按照年龄从大到小排序，如果年龄相同则按照工资从大到小排序 ：SELECT * FROM  T_Employee ORDER BY FAge DESC,FSalary DESC（多个排序条件）
ORDER BY子句要放到WHERE子句之后 ：SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC 

通配符过滤

通配符过滤关键字使用LIKE 。
单字符匹配的通配符为半角下划线“_”，它匹配单个出现的字符。
   eg:以任意字符开头，剩余部分为“erry”
   SELECT * FROM T_Employee WHERE FName LIKE '_erry' 
多字符匹配的通配符为半角百分号“%”，它匹配任意次数（零或多个）出现的任意字符。 “k%”匹配以“k”开头、任意长度的字符串
   eg:检索姓名中包含字母“n”的员工信息 
   SELECT * FROM T_Employee WHERE FName LIKE '%n%' 
通配符    解释                             示例
‘_’   一个字符                         A Like 'C_'
  %     任意长度的字符串                 B Like 'CO_%'
 [ ]    括号中所指定范围内的一个字符      C Like '9W0[1-2]'
 [^]    不在括号中所指定范围内的一个字符 D Like ‘%[A-D][^1-2]'

 

 select * from t_employee where fname like '[J-K]%'

空值处理

SQL中使用 is null、is not null来进行空值判断： 

SELECT * FROM T_Employee WHERE FNAME is null ； 

SELECT * FROM T_Employee WHERE FNAME is not null 

多值匹配

SELECT FAge,FNumber,FName FROM T_Employee 
WHERE FAge IN (23,25,28) 
范围值：
   SELECT * FROM T_Employee WHERE FAGE>=23 AND FAGE <=27 
   SELECT * FROM T_Employee WHERE FAGE BETWEEN 23 AND 27 

select * from T_Employee
where FName <>null


select * from T_Employee
where FName  is null;


select * from T_Employee
where FName  is not null;

select * from T_Employee

where FAge=23 or FAge=25 or FAge=28

select * from T_Employee

where FAge in (23,25,28)

select * from T_Employee

where FAge>20 and FAge<30

select * from T_Employee

where FAge between 20 and 30

数据分组

按照年龄进行分组统计各个年龄段的人数：SELECT FAge,Count(*) FROM T_Employee GROUP BY Fage

GROUP BY子句必须放到WHERE语句的之后 

没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 (聚合函数中除外)

Having语句(分组后查询条件)

在Where中不能使用聚合函数，必须使用Having，Having要位于Group By之后：
    SELECT FAge,COUNT(*) AS 人数 FROM T_Employee 
    GROUP BY FAge 
    HAVING COUNT(*)>1 

注意Having中不能使用未参与分组的列，Having不能替代where。作用不一样，Having是对组进行过滤。

select fAge,count(*) from t_employee
where fSalary >3000
group by fage
having count(*)>1

优先级：where->group by ->having

限制结果集行数

检索按照工资从高到低排序检索从第六名开始一共三个人的信息 ：

select top 3 * from t_employee where fnumber not in 
(select top 5 fnumber from t_employee order by fsalary desc)
order by fsalary desc

去掉数据重复

SELECT  DISTINCT FDepartment FROM T_Employee 

联合结果集 

简单的结果集联合：
SELECT FNumber,FName,FAge FROM T_Employee  UNION  SELECT FIdCardNumber,FName,FAge FROM T_TempEmployee 
基本的原则：每个结果集必须有相同的列数；每个结果集的列必须类型相容。 

SELECT FNumber,FName,FAge,FDepartment FROM T_Employee  UNION  SELECT FIdCardNumber,FName,FAge,‘临时工，无部门' FROM T_TempEmployee

Union all

SELECT FName FROM T_Employee UNION  
SELECT FName FROM T_TempEmployee
1.UNION合并两个查询结果集，并且将其中完全重复的数据行合并为一条
SELECT FName FROM T_Employee
UNION ALL
SELECT FName FROM T_TempEmployee 
2.Union因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，那么就用UNION ALL

数字函数（*）

ABS() ：求绝对值。
CEILING()：舍入到最大整数 。3.33将被舍入为4、2.89将被舍入为3、-3.61将被舍入为-3。 Ceiling→天花板
FLOOR()：舍入到最小整数。3.33将被舍入为3、2.89将被舍入为2、-3.61将被舍入为-4。 Floor→地板。

ROUND()：四舍五入。舍入到“离我半径最近的数” 。Round→“半径”。Round(3.1425,2)。

索引Index

全表扫描：对数据进行检索（select）效率最差的是全表扫描，就是一条条的找。

如果没有目录，查汉语字典就要一页页的翻，而有了目录只要查询目录即可。为了提高检索的速度，可以为经常进行检索的列添加索引，相当于创建目录。

创建索引的方式，在表设计器中点击右键，选择“索引/键”→添加→在列中选择索引包含的列。

使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低Insert、Update、Delete的速度。只在经常检索的字段上(Where)创建索引。

（*）即使创建了索引，仍然有可能全表扫描，比如like、函数、类型转换等。

表连接Join

有客户表（T_Customers）和订单表（T_Orders）两个表，客户表字段为：Id、Name、Age，订单表字段为：Id、BillNo、CustomerId，订单表通过CustomerId关联客户表。测试数据见备注。

SELECT o.BillNo,c.Name,c.Age from T_Orders as o

JOIN T_Customers as c on o.CustomerId=c.Id

join是和哪个表连接，on后是连接的关系是什么。(多表)

要求显示所有年龄大于15岁的顾客购买的订单号、客户姓名、客户年龄。

要求显示年龄大于平均年龄的顾客购买的订单

（*）Inner Join、Left Join、Right Join

子查询

将一个查询语句做为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。SELECT * FROM(SELECT * FROM T2 where FAge<30) 
 执行备注中的SQL。
单值做为子查询：SELECT 1 AS f1,2,(SELECT MIN(FYearPublished) FROM T_Book),(SELECT MAX(FYearPublished)  FROM T_Book) AS f4
只有返回且仅返回一行、一列数据的子查询才能当成单值子查询。下面的是错误的：SELECT 1 AS f1,2,(SELECT FYearPublished FROM T_Book)
SELECT * FROM T_ReaderFavorite WHERE FCategoryId=(SELECT FId FROM T_Category WHERE FName='Story')

如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合。

SELECT * FROM T_Reader 

WHERE FYearOfJoin IN

(

select FYearPublished FROM T_Book

)

限制结果集。返回第3行到第5行的数据（ ROW_NUMBER 不能用在where子句中，所以将带行号的执行结果作为子查询，就可以将结果当成表一样用了）： 

SELECT * FROM 

( 

SELECT ROW_NUMBER() OVER(ORDER BY FSalary DESC) AS rownum, 

FNumber,FName,FSalary,FAge FROM T_Employee 

) AS a 

WHERE a.rownum>=3 AND a.rownum<=5