SQL server学习笔记

表操作

对表里的数据进行修改update emp set emp_id=1000 where emp_ename='张三'
删除表里的数据delete from emp
删除表drop table emp
查看数据库里的所有表select * from sys.tables或select * from sysobjects where xtype='U'
删除索引drop index emp_index_sal on emp
删除视图drop view v$_emp

创建表

	create table bumen
		(	--注释
主键  		bumen_id int primary key,
			bumen_name nvarchar(20) not null
		)

创建主键外键

列名 数据类型 constraint（约束） 约束名称（pk_……） primary key（主键）
列名 数据类型 constraint（约束） 约束名称（fk_……） foreign key（外键） references（参考,来自） 表名（列名）

	create table yuangong
	(
		yuangong_id int constraint PK_yuangong_id_zhujian primary key,
		yuangong_name nvarchar(20) not null,
		yuangong_sex nchar(1) ,
		yuangong_address nvarchar(20) not null,
		bumen_id int constraint FK_bumen_id_waijian foreign key references bumen(bumen_id)
	)

插入数据到表中

insert into bumen（表名） values (1,'研发部')
指定插入哪列的数据
insert into bumen (bumen_id,bumen_name) values (1,'研发部')

约束

check约束

列名 数据类型 check （列名>1000 and 列名<8000）(not null)  给数据加上了约束

default约束（指定默认值）

列名 数据类型 default  ('男') not null

unique约束（唯一约束）

	列名 数据类型 unique （not null）
	和主键的区别:主键值不能为空,唯一约束键可以为空,且只能有一列为空
unique 可以和 not null组合使用,便不能为空了

自增约束（identity）

stu_id int primary key identity,
	identity(1001,1)

提取表的数据显示出来

select * from  student（表名）

多对多的关系要建立第三张表来设主键与外键

create table banji_teacher_mapping
(
	banji_id int constraint fk_banji_id foreign key references banji (banji_id) ,--两列分别设置外键
	teacher_id int constraint fk_teacher_id foreign key references teacher (teacher_id) ,--两列分别设置外键
	kecheng nvarchar(50) not null,
	constraint pk_banji_id_teacher_id primary key (banji_id,teacher_id,kecheng)--设置三列共同为主键
)

查询

select 选择

select * from emp 显示所有的		（*表示所有的）
select ename, sal from emp		显示ename,和asl（显示特定的列）
select ename as 姓名,sal as 工资 from emp	改名字后显示
select ename, sal*12 as "年薪" from emp	计算列查询  显示年薪列（为sal*12）

distinct不允许重复的

select distinct deptno from emp  过滤掉重复的记录
select distinct comm from emp 过滤掉重复的null
select distinct comm, deptno from emp  过滤交叉重复的

between查询某一个范围内的信息 （ where 表示添加条件）

【工资在1500到3000,之间包括1500和3000】
select * from emp
	where sal >=1500 and sal <=3000
等价于
select * from emp
	where sal between 1500 and 3000
【工资小于1500或大于3000】
select * from emp
	where sal < 1500 or sal > 3000
等价于
select * from emp
	where sal not between 1500 and 3000

in查询若干个孤立的值

select * from emp where sal in (1500,3000) 【查询工资是1500和3000的人】
等价于
select * from emp 
	where sal in (1500,3000)
等价于
select * from emp
	where sal = 1500 or sal = 3000

select * from emp 
	where sal not in (1500,3000)  【查询工资不是1500或3000的人】
等价于
select * from emp
	where sal != 1500 and sal != 3000
等价于
select * from emp
	where sal <> 1500 and sal <> 3000   【数据库中不等于最好用<>】

top 查询前面的若干个值

	select top 2 * from emp  【查询前两条数据】
	select top 15 percent * from emp  【查询前15%】
	select top 4 * from emp 【查询工资在1500到3000中最高的前三个】
		where sal between 1500 and 3000	
order by sal desc      【order by排序,desc降序,不写默认按升序】

null查询

【null不可以参与 <> , != , = 的运算】
【任何与null参与数学运算的值都为空】
	select * from emp 
		where comm is null【输出奖金为空的信息】
	select * from emp 
		where comm is not null【输出奖金不为空的信息】
	select ename, sal * 12 + isnull(comm ,0) "年薪" from emp 【输出工资*12+奖金,奖金有null的计算,用isnull函数】

order by

【排序,默认是升序】【asc为升序默认不写,desc为降序】
	select * from emp order by sal 【默认升序】
	select * from emp order by deptno ,sal 【先按部门排序,然后再按照工资排序】
	select * from emp order by deptno desc ,sal 【先按部门降序,再按工资升序】 

模糊查询

select * from emp                       【%表示任意0个或多个字符】
	where  ename like '%A%'   【查询带有A的信息】【A%表示首字母是A】
select * from emp                     
	where  ename like '_A%' 	  【输出第二个字母是A的】	【_表示任意单个字符】					
select * from emp                     
	where  ename like '_[A-F]%'  【输出第二个字母是A到F的信息】
select * from emp                     
	where  ename like '_[A,F]%'   【输出第二个字母是A或F】
select * from emp                     
	where  ename like '_[^A-F]%'	  【输出第二个字母不是A到F的】【^表示取反】	
select * from emp
	where ename like '%\%%' escape '\'     【将包含有%的输出】
select * from emp
	where ename like '%\_%' escape '\'       【将包含有下划线_的输出】
select * from emp
	where ename like '_\%%' escape '\'	【输出第二个字母是%的信息】
select * from emp
	where ename like '_\_%' escape '\'		【输出第二个字母是_的信息】

聚合函数

单行函数:每行返回一个值
	select lower(ename ) from emp  【lower函数  表示小写】
多行函数:多行返回一个值

聚合函数的分类
min()
max()
avg()【平均值】
count()函数 【求个数】
	select count (*) from emp 【返回emp表所有记录的个数】
	select count (comm) from emp  【返回值是4,说明null不会当做有效记录】
count(distinct 字段名) 【返回字段不重复且非空的个数】
	select count (distinct deptno) from emp 【返回值是3,去掉了重复的】

group by查询 【以什么分组】

select deptno , avg (sal) as "平均工资"
	from emp
	group by deptno         --以部门分组,查询每组 的平均工资
select deptno ,job, avg (sal) as "平均工资"
	from emp
	group by deptno ,job  --deptno大组,job小组,在每个部门中各个工作的平均工资
	order by deptno          --按部门进行排序
select deptno ,job, avg (sal) as "平均工资" ,count(*) as "部门人数", sum(sal) as "部门总工资" , min (sal) as "部门最低工资"
	from emp
	group by deptno ,job
	order by deptno

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

	select deptno , avg(sal) as "部门平均工资" 
		from emp 
		group by deptno 
		having avg(sal)> 2000 --过滤,输出平均工资大于2000的
	select deptno , avg(sal) as "部门平均工资" 
		from emp 
		group by deptno 
		order by deptno desc --错误,排序应放在最后,在过滤操作之后,在所有操作做完之后再排序,否则会错误
		having avg(sal)> 2000
	select deptno , avg(sal) as "部门平均工资" 
		from emp 
		group by deptno 
		having count(*) > 3  --对部门人数大于3 ,进行过滤
	select deptno ,count(*) as "部门人数", avg(sal) as "部门平均工资",max (sal) as "部门最高工资" 
		from emp 
		where sal >2000
		group by deptno 
		having avg(sal)> 3000
	having和where的区别:
都是对数据进行过滤,where是过滤原始数据,having是过滤分组后的数据

连接查询

内连接
select * from emp ,dept  【行数是两表的行数相乘,列数是列数相加】
和select * from dept ,emp 相同【笛卡尔积emp的所有数据分别和dept中的每个数据组合一遍】

select * from emp ,dept 
	where empno = 7369   【输出5行11列记录,从上面的临时表笛卡尔积中过滤】
	
select  "E" .ename "员工姓名","D". dname "员工部门"  --输出"E"即emp表中的ename,并取名为"员工姓名"
	from emp "E"  --给emp表取个别名
	join dept "D" --join是连接
	on "E" .deptno ="D" .deptno    --on为连接条件    ,【有join就必须有on】
							   --条件为E与D表中的deptno相同

select "E".ename "员工姓名" ,"D".deptno "员工部门" ,"S" .grade "工资等级" 
	from emp "E"
	join dept "D"
	on "E".deptno="D".deptno          【连接三个表,用两个join on】
	join SALGRADE "S"
	on "E".sal>=  "S".losal and "E".sal<="S".HISAL   
	--on "E".sal between "S".losal and "S".HISAL   【用between也可以】	
外连接
	用左表的第一行与右表所有航进行连接,然后再用第二行进行连接,依次类推
	select * from emp "E"			--左外连接,例子为emp在左边
		left join dept "D"
			on "E".deptno="D".deptno
完全连接
	两个表中匹配所有行记录,如果左边没有找到匹配的记录,那么左边为空,类此右边为空
	full join……
	on ……
交叉连接
	select * from dept D		--只是做笛卡尔积
		cross join emp E
自连接
	一张表和自己连接起来查询数据
	select * from emp "E1" , emp "E2"
联合 union【若干个select子句的列数要是相同的,类型要兼容】
	select "E1".ename ,"E1".sal, "E2".ename
		from emp "E1"
		join emp "E2"
		on "E1".mgr = "E2".EMPNO
	union  --联合,表和表之间的数据以纵向的方式连接
	select ename,sal,'最大老板'
		from emp
		where mgr is null

分页查询

分页查询

索引（针对列）

聚集索引	
	一个表中有且仅有一个（一般以主键创建,默认会自动创建一个）
非聚集索引
	自己建的叫非聚集索引
	create index emp_index_sal on emp(sal)	对工资列创建索引
	drop index emp_index_sal on emp			删除索引

如果索引碎片小于30%使用索引重组,大于30%使用索引重建
alter index emp_index_sal on emp reorganize --重组索引
alter index all on emp reorganize	--对所有索引重组
alter index emp_index_sal on emp rebuild	  --重建索引
alter index all on emp rebuild		--对所有索引重建

视图

【可以避免书写大量重复代码,简化查询,更方便】
	create view v$_emp    【创建一个名为v$_emp的临时表】
	as
	select deptno ,avg(sal) "平均工资"
		from emp	
		group by deptno

	create view v$_emp		--视图就是一个临时表
	as
	select deptno as 部门编号,avg(sal) as 平均工资
		from emp
		group by deptno
	GO	--用于隔离,在视图下方有波浪线时,用GO隔离
	select * from v$_emp

	drop view v$_emp	删除视图

事务

开始事务:BEGIN TRANSACTION
提交事务:COMMIT TRANSACTION
回滚（撤销）事务:ROLLBACK TRANSACTION