SQLServer学习笔记

SQLServer学习笔记

1.注释
	--单行注释
	/*
	多行注释
	*/

2.创建表和主键

		create table DEPA(
			depa_id int primary key,
			depa_name nvarchar(100) not null,
			depa_address nvarchar(100)
		);

3.外键，参照完整性

		create table EMP
		(   
			emp_id int constraint pk_1 primary key,
			emp_name nvarchar(20) not null,
			emp_gender nchar(1) ,
			depa_id int constraint fk_depart_id foreign key references DEPA(depa_id)
		);

4.约束
	1.check约束
		表示一个范围

			create table student(
			stu_id int primary key,
			stu_sal int check(stu_sal>=1000 and stu_sal<=8000)
			);
			--向student插入数据
			insert into student values(1,1000);
			insert into student values(2,1000);
			insert into student values(3,1000);

	2.default约束
		表示默认值

			create table stu1(
				stu_id int primary key,
				stu_sal int check(stu_sal>=1000 and stu_sal<=8000),
				stu_gender nchar(1) default('男')
				--字符串用两个单引号括起来，双引号表示某个名字
			);
			--向stu1插入数据
			insert into stu1 values(1000,2000,'女');
			insert into stu1(stu_id,stu_sal) values(1001,2000);
			insert into stu1 values(1002,2000,'女');

	3.unique约束
		1.唯一约束 unique,主键可以为空，但是唯一约束的值可以为空
			保证不重复，但是可以为空
		2.但SQLServer中只允许一列为空，Oracle允许多列为空

			create table stu2(
				stu_id int primary key,
				stu_sal int check(stu_sal>=1000 and stu_sal<=8000),
				stu_gender nchar(1) default('男'),
				stu_name nvarchar(200) unique
			);
			--插入数据
			insert into stu2  values(1,4000,'男','张三');
			insert into stu2  values(2,4000,'男','李四');
			insert into stu2  values(3,4000,'女','李红');
			insert into stu2  values(4,4000,'男','王五');
			insert into stu2  values(5,4000,'男',null);

	4.not null约束
		1.要求用户必须给该属性赋值
		2.如果没有not null约束，有一个字段不写，默认是null，若有default值则默认default值
		3.使用默认要用默认的格式
		4.比如：

				insert into stu1(stu_id,stu_sal) values(1001,2000);

5.主键和unique约束的区别
	1.主键的选择
		添加没有任何意义的编号当主键
	**其他有意义的数据有修改的可能性
	2.一定不要用有业务逻辑意义的数据当主键
	3.唯一键的选择
		把有业务逻辑意义的、只能取唯一值的数据当唯一键
	比如：用户登录
		一般主键比较难记，并且主键常常还充当另一个表的外键，不能修改
		有业务逻辑意义的唯一键一般比较好记，以后也允许修改
		例：一条数据的主键是1234515451，唯一键是huangheng123
			唯一键更加好记，以后也允许修改
6.删除表drop

		drop table student;

7.查找一个表的全部数据select

		select*from stu1;

8.表和约束的实际意义
	数据库通过表来解决实体的存储问题
	数据库通过约束来解决实体取值的有效性和合法性问题
	建表的过程就是指定实体属性及其约束的过程
9.外键可以实现的联系
	1.一对一联系
		把表A的主键充当表B的外键，并且，把表B的主键充当表A的外键
	2.一对多联系
		1.把表A的主键充当表B的外键
		2.在多的一方添加外键
	3.多对多联系
		必须通过单独的一张表来表示
		例子：

			--班级
			create table aclass(
				aclass_id int primary key,
				aclass_num int not null,
				aclass_name nvarchar(100)
			);
			--教师
			create table teacher(
				teacher_id int primary key,
				teacher_name nvarchar(100)
			);
			--第三张表，用来模拟班级与教师多对多的关系
			create table aclass_teacher(
				aclass_id int constraint fk_aclass_id foreign key references aclass(aclass_id),
				teacher_id int constraint fk_teacher_id foreign key references teacher(teacher_id),
				course nvarchar(20),
				constraint pl_aclassId_teacherId_course primary key(aclass_id,teacher_id,course)
			);
			--班级一张表、教师一张表、班级和教师的多对多联系又是一张表

10.约束是可以删除的
	删除约束之后，表与表之间的关系消失，然后可以把外键对应的主键删除
11.主键
	定义：能够唯一标识一个实体的一个字段或者多个字段的组合
	附注：
		1.含有主键的表叫作主键表
		2.主键通常都是整数，不建议使用字符串当做主键
			如果主键用于集群式服务，才可以考了用字符串当主键
		3.主键的值通常都不允许修改，除非本记录被删除
		4.主键的命名要定义成:表名_id
		5.要用代理主键，不要用业务主键
			我们通常都是在表中单独添加一个整型的编号充当主键字段
12.外键
	另外一张表，或另外几张表的主键或唯一键
	附注：
		1.外键通常是来自另外表的主键而不是唯一键，因为唯一键可能是null
		2.外键不一定来自另外的表，也可能是本表的主键
		3.含有外键的表叫外键表，外键字段来自的那张表叫作主键表
	问题：先删外键表还是主键表
		先删外键表
		如果先删主键表，会报错，因为这会导致外键表中的数据引用失败
13.查询【最重要的，数据库中的最重要的操作】
	1.计算列
		1.查询列：select ename,sal from emp;
		2.计算列：select ename,sal*12 as "年薪" from emp;
			注意这里的年薪是加上英文双引号的
		3.查询常量：select 5 from emp;
			语法是正确的
			输出的行数是emp表的行数，每行只有一个字段，值是5
		  ** select 5;
			也是正确的，但是只输出一行，这一行只有一个字段，值是5
		注意：
			Oracle中字段的别名不允许用单引号括起来，只能使用双引号括起来
			SQLServer却允许使用单引号或者双引号
			因此，为了兼容性，最好字段别名使用双引号括起来
	2.distinct【不允许重复的】
		select deptno from emp; 
			查询出来14条记录，不是3条
		select distinct deptno from emp;
			 distinct deptno会过滤重复的deptno
		select distinct comm from emp;
			 distinct也可以过滤掉重复的null
		select distinct comm,deptno from emp;
			把comm和deptno的组合进行过滤
		select deptno,distinct comm from emp;
			逻辑上有冲突，error
	3.between
		1.情况1：
			select* from emp
				where sal between 1500 and 3000;
			等价于：
			select *from emp
				where sal>=1500 and sal<=3000;
		2.情况2：【not】
			select* from emp
				where sal not between 1500 and 3000;
			等价于：
			select *from emp
				where sal<1500 or sal>3000;
	4.in【某一属性属于若干个孤立的值组成的集合】
		情况1：
			select * from emp
				where sal in(1500,3000,5000);
			等价于：
			select *from emp
				where sal=1500 or sal=3000 or sal=5000;
		情况2：【not】
			select * from emp
				where sal not in(1500,3000,5000);
			等价于：
			select *from emp
				where sal!=1500 and sal=3000 and sal=5000;
			***数据库中的不等于有两种：!=和<> 推荐第二种
	5.top
		1.select top 2 * from emp;
			选出emp表中的前2行数据
		2.select top 15 percent * from emp;
			选出emp表中前 行总数的15%行的数据，向上取整，例如：2.1个是3个
		3.SQLServer使用【top】来分页
			Oracle使用【rownum】来分页
		4.select top 5 from emp;
			报错 
	6.null【没有值，空值】
		输出奖金非空的员工的信息
		1.error：select * from emp where comm <> null;
			输出为空，没有报错，但与事实不符，
		2.error：select * from emp where comm != null;
			输出为空，没有报错，但与事实不符，
		3.right：select * from emp where comm is not null;
		4.总结
			1.null不能参与<>,!=,<,>,<=,>=,=,+,-,*,/等运算
				但是可以参与is和is not运算
			2.任何类型的数值都允许是null
			3.任何数字与null参与数学运算的结果永远都是null
		5.isnull()函数
			1.计算年薪的方法
				select ename,sal*12+isnull(comm,0) as "年薪"from emp;
			2.isnull(comm,0)的意思
				如果comm为null，则返回0，否则返回原comm值

	7.order by
		1.把工资在1500到3000之间（包括上下边界）的员工中
			工资最高的4个人的信息按降序输出
			select top 4 *
				from emp
				where sal between 1500 and 3000
				order by sal desc;
			代码执行顺序：先执行from，然后找出1500<=sal<=3000的数据
							然后，进行降序排序，最后取出前4个；
		2.desc：降序
			asc：升序，默认值是asc
		3.多个属性组合排序
			select * from emp order by deptno,sal;
				先按照deptno升序排序，再按照sal升序排序【默认升序排序】
			select * from emp order by deptno desc,sal;
				先按照deptno降序排序，再按照sal升序排序
	8.模糊查询
		1.示例代码
			1.ename只要含有A就输出
				select * from emp
					where ename like '%A%';
			2.ename只要首字母是A就输出
				select * from emp
					where ename like 'A%';
			3.ename只要尾字母是A就输出
				select * from emp
					where ename like '%A';
			4.ename只要第二个字母是A就输出
				select * from emp
					where ename like '_A%';
			
		2.格式：
			select 字段的集合 from 表名 where 某个字段的名字 like 匹配条件
			匹配条件通常含有通配符，匹配条件必须使用单引号括起来
		3.通配符
			1.%：
				表示任意0个或多个字符
			2.下划线_：
				表示任意单个字符
			3.[a-f]：
				表示a-f中的任意单个字符
			4.[a,f]：
				a或f
			5.[^a-c]：
				不是a-f中的任意单个字符
		4.使用转义字符
			select * from student where name like '%\%%' escape '\';
			1.escape '\'表示把'\'字符当做转义字符的标志
			2.在sql中可以把任意字符当做转义字符的标志，具体是把哪个字符当做转移字符，
				这由escape后面跟着的字符决定
	9.聚合函数【多行记录返回一个值，通常用于统计分组的信息】
		1.函数的分类
			1.单行函数
				每行返回一个值
			2.多行函数
				多行返回一个值
			3.示例
				1.select lower(ename)from emp;
					最终返回的是14行，lower()是单行函数
				2.select max(sal) from emp;
					返回1行，max()是多行函数
		2.常见聚合函数
			1.max()
			2.min()
			3.avg()  平均值
			4.count()
				1.select count(*) from emp;
					返回emp表所有记录的个数
				2.select count(deptno) from emp;
					返回deptno的个数，重复值也计数
				3.select count(distinct deptno) from emp;
					不记录重复值
				4.select count(comm) from emp;
					返回值是4，不记录空值，只记录非空值
				5.判断如下sql语句是否正？
					select max(sal),min(sal),count(*) from emp;  --正确
					select max(sal) "最高工资",min(sal) "最低工资",count(*) "员工人数" from emp;  --正确
					select max(sal),lower(ename) from emp;  --错误
	10.group by
		**补充：use 库名;  --表示后面的操作都是在库名对应的数据库中进行的
		1.输出每个部门的编号和该部门的平均工资
			select deptno,avg(sal) as "部门平均工资"
				from emp;
				group by deptno;
		2.判断下面语句是否正确
			select deptno,ename
				from emp
				group by deptno;
			错误，只能选中分组内部的信息，不能选择分组以外的信息，ename就是部门以外的信息
		3.总结
			使用group by之后select中只能出现分组后的整体信息，不能出现组内的详细信息
		4.组合分组
			select deptno,job,avg(sal) "平均值",count(*)"分组成员数",sum(sal)"总工资",min(sal)"最小工资"
			from emp
			group by deptno,job;
		5.功能：
			把表中的记录按照字段分成不同的组
		6.空值也可以当成一组
	11.having【对分组之后的信息进行过滤】
		1.输出部门平均工资大于1500的部门编号 部门的平均工资

				select deptno,avg(sal)
					from emp
					group by deptno
					having avg(sal)>2000;

		2.having和where的区别
			where对分组之前的信息进行过滤
			having对分组之后的信息进行过滤
			having和where一样，子句中不能出现字段的别名
		**补充：into的用法
			select * into emp_1 from emp where dept=10;
				新建一张表，把查询结果存放在里面
	12.连接查询
		1.定义
			将两个或两个以上的表以一定的连接条件连接起来，从中检索满足条件的数据。
		2.分类
			1.内连接【重点】
				1.select ... from A,B;的用法【无连接条件】
					表A和B做笛卡尔积
					示例：select * from emp,dept;
					一共70行，14*5=70，emp是14行，dept是5行，emp和dept做笛卡尔积
				2.select ... from A,B where ... ;的用法【有连接条件】
					对多个表做笛卡尔积之后的结果进行过滤，where后面的是连接条件
					示例：select * from emp,dept where emp.deptno=dept.deptno;
				3.select ... from A join B on ... ;的用法
					有join就必须有on
					示例：
						select "E".ename "员工姓名","D".dname "部门名称"
							from emp "E"
							join dept "D"	--join是连接
							on "E".deptno="D".deptno;  --on后面的是连接条件
				4.select ... from A,B where ...;与 select ... from A join B on ... ;可以等价转换
					1.select ... from A,B where ...;是SQL92标准
					2.select ... from A join B on ... ;是SQL99标准
						推荐使用SQL99标准
						在SQL99标准中，on和where可以做不同的分工
						1.on指连接条件
						2.where对连接之后临时表的数据进行过滤，where只能写在join on的后面
						3.顺序可以理解为：先根据on的连接条件连接，连接之后再根据where的过滤条件过滤
					3.把工资大于2000的员工的姓名和部门的编号输出
						1.92标准实现

								select * 
									from emp,dept
									where emp.sal>2000 --过滤条件
										and emp.deptno=dept.deptno;  --连接条件

						2.99标准实现

								select *
									from emp
									join dept on emp.deptno=dept.deptno  --连接条件
									where emp.sal>2000;  --过滤条件

					4.select * from emp,dept where dept.deptno=10;使用SQL99标准如何实现
						select * from emp join dept on 1=1 where dept.deptno=10;
				5.select、from、where、join、on、group、order、top、having的混合使用

						select top ...
							from A
							join B
							on ...
							join C
							on ... 
							where
							group by ...
							having ...
							order by ...

				6.习题
					1.求出每个员工的姓名、部门编号、薪水和薪水等级

							select ename,deptno,sal,grade
								from emp "E"
								join SALGRADE "SAL" 
								on "E".sal<="SAL".HISAL and "E".sal>="SAL".LOSAL

					2.查找每个部门的编号 部门名称，该部门所有员工的平均工资 平均工资的等级

							select "Temp".deptno "部门编号",dname "部门名称",avg_sal "平均工资",grade "工资等级"
								from(select "E".deptno,AVG("E".sal) as "avg_sal"
									from emp "E"
									group by "E".deptno) "Temp"
								join SALGRADE "SAL" 
								on "Temp".avg_sal between "SAL".LOSAL and "SAL".HISAL
								join dept 
								on dept.deptno="Temp".deptno;

					3.求出emp表中所有领导的姓名
						1.答案1：
							select ename 
								from(select distinct mgr from emp) "T"
								join emp 
								on "T".mgr=emp.EMPNO;
						2.答案2：
							select ename
								from emp
								where empno in (select mgr from emp);
						3.思考题：求出emp表中非领导的姓名
							select ename
								from emp
								where empno not in (select mgr from emp where mgr is not null);
							**这里要思考in和null组合时，虽然不会报错，但是输出的结果与事实不符
					4.求出平均薪水最高的部门的编号和部门的平均工资

							select top 1 "E".deptno "部门编号",avg("E".sal) as "最高平均工资"
								from emp "E"
								group by "E".deptno
								order by avg(sal) desc;

					5.将工资最低的那人排除，然后求出最低工资的前3人的姓名 工资 部门编号 部门名称 工资等级输出

							select top 3 ename,"T".deptno,dname,GRADE
								from (select ename,empno,deptno,sal
										from emp
										where emp.EMPNO=(select empno from emp where sal>(select min(sal)from emp)).EMPNO)"T" --筛选条件
								join dept on dept.deptno="T".deptno	--连接条件
								join SALGRADE on "T".sal<=SALGRADE.HISAL and "T".sal>=LOSAL --连接条件
								order by sal desc;

			2.外连接
				【左外连接】和【右外连接】
				1.左外连接
					不但返回满足连接条件的记录，而且返回左表不满足条件的记录
					左边是原值，右边全是null
					【右外连接】类比
				2.实际意义
					返回一个实体及该实体的相关信息，如果该实体没有相关信息
					则输出null，防止遗漏信息
				3.示例代码

						select * from emp "E"
							left join dept "D"
							on E.deptno = D.deptno;
						select * from dept "D"
							left join emp "E"
							on E.deptno = D.deptno;
						select * from emp "E"
							right join dept "D"
							on E.deptno = D.deptno;
						select * from dept "D"
							right join emp "E"
							on E.deptno = D.deptno;

			3.完全连接
				同时不遗漏左边和右边的信息
			4.交叉连接【笛卡尔积】
			5.自连接
				一张表自己和自己连接起来
			6.联合
				1.定义
					表和表之间的数据以纵向的方式连接在一起
					注意:我们以前讲的所有的连接是以横向的方式连接在一起的
				2.输出每个员工的姓名 工资 上司的姓名

						select E1".ename,“E1".sal,"E2".ename "上司的姓名*
							from emp"E1"
							join emp"E2"
							onE1".mgr =“E2".empno
						union
						select ename,sal,'已是最大老板’
							from emp 
							where mgr is null

				3.注意
					若干个select子句要联合成功的话，必须的满足两个条件
					1.这若干个select子句输出的列数必须是相等的
					2.这若于个select子句输出列的数据类型至少是兼容的
	13.嵌套查询
	14.分页查询
14.identity【主键自动增长，用户不需要为identity修饰的主键赋值】
	1.示例代码

			create table student(
				student_id int primary key identity(100,5),
				student_name nvarchar(200) not null
			);
			select * from student;
			insert into student(student_name) values('张三');--ok
			insert into student values('李四');--ok

	2.表中删除数据又插入数据，会导致主键不连续递增
		checkident()函数可以解决这个问题
15.视图
	1.为什么需要视图
		1.求出平均工资最高的部门的编号和部门的平均工资
			create view v$_emp_1 as
				select deptno,avg(sal) "avg_Sal"
					from emp
					group by deptno;
		2.总结：
			简化查询，避免代码的冗余，避免了属性大量重复的sql语句
	2.什么是视图
		视图实际上是一个select语句
		逻辑上被当成一个虚拟表
	3.视图的格式
		create view 视图的名字
		as 
		--select的前面不能添加begin
			select语句
		--select的后面不能添加end
	4.视图的优点
		1.简化查询
		2.增加数据的保密性
	5.视图的缺点
		1.增加了数据库维护的成本
		2.视图只是为了简化查询，但是并不能加快查询的速度
	6.注意的问题
		1.视图中一般只做查询，不做更改
		2.创建视图的select语句必须要为所有的计算列指定别名
		3.视图不是物理表，是虚拟表
		4.不建议通过视图更新视图所依附的原始表的数据或者结果
16.事务【重要，没学清楚】
	1.为什么需要事务
		事务主要用来保证数据的合理性和并发处理的能力
		通俗点说：
			1.事务可以保证避免数据处于一种不合理的中间状态【类似：操作系统中的原语】
			2.利用事务可以实现多个用户对共享资源的同时访问
				很复杂，现在任然没有很好的解决办法
	2.什么是事务
		一系列操作要么全部执行成功，要么全部执行失败
	3.怎么使用事务
		1.怎么使用事务来保证避免数据处于一种不合理的中间状态
		2.怎么使用事务来实现多个用户对共享资源的同时访问
		3.如何创建事务
			1.开始事务 BEGIN TRANSACTION
			2.提交事务 COMMIT TRANSACTION
			3.撤销事务 ROLLBACK TRANSACTION
		4.事务的三种运行模式：
			1.自动提交事务
			2.显式事务
			3.隐性事务
	4.事务和线程的关系
		1.事务也是通过锁来解决很多问题的
		2.线程同步就是通过锁来解决的 synchronized
	5.总结事务的四大特性：原子性，一致性，隔离性，持久性
17.后期数据库内容概述
	1.索引
	2.存储过程
	3.游标
	4.TL_SQL