SQLServer学习笔记

查询

计算列

select name sal*12 as "年薪" from emp;

distinct

不允许重复

--distinct deptno 会过滤掉重复的deptno值
select distinct deptno from emp;
--distinct 也可以过滤掉null

between

--两个方法查询结果是等价，方法1的效率更高
select * from emp
	where sal betwwen 1500 and 2000; --推荐使用
等价
select * from emp
	where sal >= 1500 and sal <= 2000;

select * from emp
	where sal =< 1500 0r sal >= 2000;
--等价
select * from emp
	where sal not between 1500 and 2000;

in

属于若干个孤立的值

select * from emp
	where sal in (1500,1600,1700);
--等价于
select * from emp
	where sal = 1500 or sal = 1600 or sal = 1700;
	
	select * from emp
	where sal not in (1500,1600,1700);
--等价于
select * from emp
	where sal <> 1500 and sal <> 1600 and sal <> 1700;--编程语言中用 !=

top

select top 10 id from emp; --查询前10个数据

null

表示空值或没有值

零和null是不一样的，null表示空值，没有值，零表示一个确定的值

null 不参与以下运算符： <> != = 运算

null 可能参与如下运算：is not is

任何类型的数据都允许为null

任何数字与null参与数学运算的结果永远为null

select * from emp where comm = null;
select * from emp where comm != null;
select * from emp where comm <> null;
--都是错误的， null 不参与 <> != = 运算
select * from emp where comm is not null;
select * from emp where comm is null;
-- null 可以与 is not 运算

select ename, sal * 12 + isnull(comm, 0) from emp;
--isnull(comm, 0)如果comm是null就返回零 否则返回comm的值

order by

排序

select top 5 *
	from emp
	where sal between 1800 and 2000
	order by sal desc ;--desc 降序 默认是升序asc可以不写
	
order by a ,b  --两个都是升序
order by a ,b desc --a升序 b降序
order by a desc , b --a降序 b升序
order by a desc , b desc --两个都是降序

-- 强烈建议为每个字段写上排序类型

模糊查询

格式：

select * from table_name
	where num_name like ‘通配符’; -- % _[a-F] 正则

select * from table_name
	where num_name like ’_wdm.life‘;

select * from table_name
	where num_name like ’%\%%‘ escape '\'; -- \%转义 查询含有 %号的值

聚合函数

函数的分类

单行函数

每行返回一个值

select lower(ename) from emp; -- 最终返回的是xx行 lower()是单行函数

多行函数

多行返回一个值

select max(sal) from emp; --返回时1行max()是多行函数

聚合函数的分类

max() 最大

min() 最小

avg() 平均值

count() 求个数

select count(*) from emp; --返回emp表所有的记录的个数
select count(emp_no) from emp; --返回emp表emp_no的记录的个数,过滤掉重复的数值

select count(distinct emp_no) from emp; --返回不重复且非空记录的个数

group by

分组后select中只能出现分组后的整体信息，不能出现组内的详细信息

格式：

group by 字段的集合

功能：

把表中的记录按照字段分成不同的组

如：

select * 
	from emp
	group by deptno ;

注意：

理解：group by a, b, c 的用法

先按a分组，如果a相同，再按b分组，如果b相同，再按c分组

最终统计的是小分组的信息

having

【分组之后的信息进行过滤】

因此使用having时通常都会先使用group by ；

如果没有使用group by 但使用了having

则意味着having把所有的记录当做一组来进行过滤极少用

只能出现原始字段不能出现别名

select deptno, avg(sal) as "平均工资"
	from emp
	group by deptno
	having avg(sal) > 1500;

连接查询

定义

将两个表或者两个以上的表以一定的连接条件连接起来，从中检索出满足条件的数据。

分类

内连接

产生的结果：行数是a和b的乘积，列是a和b之和。（笛卡尔积）
```
select * from a, b 
```
产生的笛卡尔积用where中的条件进行过滤
```
select * from a, b where 
```

SQL92与SQL99的区别

select * from a, b where -- sql92标准
select * from a join b on -- sql99标准 （推荐使用）
-- 结果都一样

其他

select、from、where、join、on、group by、order by、 top、 having

外连接

完全连接

交叉连接

自连接

联合

嵌套查询

视图

优点：

简化查询

增加数据的保密性

缺点：

增加了数据库的维护的成本

视图只是简化了查询，但是并不能加快查询的速度，这也是视图使用不足的地方

注意的问题：

创建视图的select语句必须为所有的计算列指定别名

create view v$_a 
	as 
			select avg(sal) as "avg_sal" from emp;

视图不是物理表，是虚拟表

不建议通过视图更新视图所依附的原始表的数据或结果

分页查询

方法一：

select top pageSize 列名 from table_name 
	select top 条数 * from table_name
	where id not in (select top pageSize*(pageIndex -1) id from table_name);
	
select top 2 * from users
	where id not in (select top 2 id from users);

方法二：

select * from (select * ,
	row_number() over (order by id) as rowNumber from table_name) as b
	where row_number between pageIndex-1*pageSize and pageIndex*pageSize ;
	
select * from (
	select *, row_number() over(order by id) as row_number from Users) as b
	where row_number between 0 and 3;

方法三（2012以上版本可使用）：

select * from table_name
	order by id offset pageIndex row fetc next pageSize row only;
	
select * from users order by id offset 2 row fetch next 5 row only;

分割

select left(列 , 截取的长度) from table;

统计姓氏人数

select  LEFT(name,1) "姓", COUNT(LEFT(name,1)) "总数"
	from hh
	group by LEFT(name,1)
	order by COUNT(LEFT(name,1)) desc;

select substring(字符串/列名, 开始, 结束) from table

select  SUBSTRING(name,0,2) "姓", COUNT(SUBSTRING(name,0,2)) "总数"
	from hh
	group by SUBSTRING(name,0,2)
	order by COUNT(SUBSTRING(name,0,2)) desc;

定位特殊字符串

select charindex(“字符串/列名”, ‘查找的字符’, ‘出现的位置’) from table

事务

定义：一系列操作要全部执行成功，要么全部执行失败。

begin transaction --开始事务
commit transaction --提交事务
rollback transaction --回滚事务

避免数据处于不合理的中间状态；如：转账
怎么避免多用户同进访问呈现给用户的数据时合理的；这个很复杂

事务和线程的关系

事务也是通过锁来解决很多问题的

线程同步就是通过锁来解决 synchronized

事务和第三方插件的关系

直接使用事务库技术难度很大很多人是借助第三方插件来实现，因此我们一般人不需要细细的研究数据库中事务的语法细节

第三方插件要想完成预期的功能，一般必须借助数据库中的事务机制来实现

基特