增删改查
几乎所有的应用软件都需要数据库

数据库与数据结构的区别？
数据库是在应用软件级别研究数据的存储和操作外存级别（硬盘）
数据结构是在系统软件级别研究数据的存储和操作内存级别
什么是连接？
库本身和客户端sql server 是分开的需要连接服务
sql结构化查询语句是命令没有像编程语言的for循环之类所以有些命令不能一起发送
有了编程语言为什么还需要数据库？
对内存数据操作是编程语言的强项，但对硬盘数据的操作是数据库的强项。
数据库三方面
一.数据库如何存储数据
字段记录表约束（主键外键唯一键非空 check default 触发器）
二.数据库如何操作数据
insert update delete T-SQL 存储过程函数触发器
三.数据库如何查询数据
select
mdf数据文件 LDF日志文件

基础内容

1.表相关数据
主键：唯一标识符解决冗余数据问题字段=列=属性
记录=元祖
表：记录的组合
外键：一个表中若干字段来自另一个表若干字段的主键或唯一键表事务与表事务之间的联系
2.create table命令
3.约束
定义：对一个表中属性操作的限制
分类：
主键约束不允许重复元素，避免了数据的冗余。
外键约束通过外键约束从语法上保证了本事务所关联的其他事物是存在的
check约束保证事物属性在一个规定值范围内
default 保证事物属性一定会有一个值
unique约束保证事物属性不能重复但与主键明显不同的是允许为空
4.表和约束的异同
5.什么是关系分类：一对一一对多（在多的表中设置外键）
多对多
6.主键
7.外键

查询 (顺序)

计算机列

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


select * from emp;
	-- * 表示所有的
	-- from emp 表示从emp表查询

select empno, ename from emp;

select ename, sal from emp;

select ename, sal*12 as "年薪" from emp;
		--as 可以省略 记住: "年薪" 不要写成'年薪' 也不要写成 年薪

select ename, sal*12 as "年薪", sal "月薪", job from emp;

select 888 from emp;
	--ok
	--输出的行数是emp表的行数  每行只有一个字段，值是888

select 5;  --ok 
		   --不推荐	

distinct(不允许重复的)

1
2
3
4
5
6
7
8


select deptno from emp; --14行记录 不是3行记录

select distinct deptno from emp;  --distince deptno 会过滤掉重复的deptno
select distinct comm from emp;  --distinct也可以过滤掉重复的null  或者说如果有多个null 只输出一个
select distinct comm, deptno from emp;--把comm和deptno的组合进行过滤 
select deptno, distinct comm from emp;--error 逻辑上有冲突

select 10000 from emp; --14行记录

between(在某个范围)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


--查找工资在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(属于若干个孤立的值)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


select * from emp where sal in (1500, 3000, 5000)
等价于
select * from emp 
	where sal=1500 or sal=3000 or sal=5000

select * from emp where sal not in (1500, 3000, 5000) --把sal既不是1500 也不是3000也不是5000的记录输出
等价于
select * from emp 
	where sal<>1500 and sal<>3000 and sal<>5000
		--数据库中不等于有两种表示：  !=    <>   推荐使用第二种
		--对或取反是并且  对并且取反是或

top(最前面的若干个记录专属sql server的语法，不可移植到其他数据库)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


select * from emp;

select top 5 * from emp;
select top 15 percent * from emp; --输出的是3个，不是2个

--把工资在1500到3000之间(包括1500和3000)的员工中工资最高的前4个人的信息输出
select top 4 * 
	from emp
	where sal between 1500 and 3000
	order by sal desc  --desc降序 不写则默认是升序
    执行顺序 过滤 排序 前四个

null（没有值，空值）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


select * from emp;
--输出奖金非空的员工的信息
select * from emp where comm <> null; --输出为空 error
select * from emp where comm != null; --输出为空 error


select * from emp where comm = null;--输出为空 error
	--总结：null不能参与<>  !=  =运算

--null可以参与is  not is
select * from emp where comm is null; --输出奖金为空的员工的信息
select * from emp where comm is not null; --输出奖金不为空的员工的信息


--任何类型的数据都允许为null
create table t1 (name nvarchar(20), cnt int, riqi datetime);  
insert into t1 values (null, null, null)
select * from t1;


select * from emp;
--输出每个员工的姓名 年薪(包含了奖金)  comm假设是一年的奖金
select empno, ename, sal*12+comm "年薪" from emp;
	--本程序证明了：null不能参与任何数据运算 否则结果永远为空

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

order by (以某个字段排序)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


--asc是升序的意思 默认可以不写  desc是降序

select * from emp order by sal; --默认是按照升序排序
select * from emp order by deptno, sal; --先按照deptno升序排序，如果deptno相同，再按照sal升序排序

select * from emp order by deptno desc, sal;
	--先按deptno降序排序 如果deptno相同 再按照sal升序排序
	--记住sal是升序不是降序
	--order by a desc, b, c, d    desc只对a产生影响 不会对后面的b  c d 产生影响

select * from emp order by deptno, sal desc
	--问题：desc是否会对deptno产生影响?
	--答案：不会
	--先按deptno升序，如果deptno相同，再按sal降序
	

模糊查询 (搜索时经常用)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40


select * from emp 	where ename like '%A%'  --ename只要含有字母A就输出
select * from emp where ename like 'A%'  --ename只要首字母是A的就输出
select * from emp where ename like '%A'  --ename只要尾字母是A的就输出


select * from emp where ename like '_A%' --ename只要第二个字母是A的就输出
 

select * from emp where ename like '_[A-F]%' --把ename中第二个字符是A或B或C或D或E或F的记录输出 


select * from emp where ename like '_[^A-F]%' --把ename中第二个字符不是A也不是B也不是C也不是D也不是E也不是F的记录输出 




select * from emp where ename like '_[^A-F]%' --把ename中第二个字符不是A也不是B也不是C也不是D也不是E也不是F的记录输出 



create table student
(
	name varchar(20) null
	,age int
);


insert into student values ('张三', 88);
insert into student values ('Tom', 66);
insert into student values ('a_b', 22);
insert into student values ('c%d', 44);
insert into student values ('abc_fe', 56);
insert into student values ('haobin', 25);
insert into student values ('HaoBin', 88);
insert into student values ('c%', 66)
insert into student values ('long''s', 100)
select * from student;

select * from student where name like '%\%%' escape '\'  --把name中包含有%的输出 
select * from student where name like '%\_%' escape '\' --把name中包含有_的输出

聚合函数（多行记录返回一个值通常用于统计分组信息）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


select lower(ename) from emp; -- 最终返回的是14行 lower()是单行函数
select max(sal) from emp; --返回1行 max()是多行函数

select * from emp;

select count(*) from emp;  --返回emp表所有记录的个数
select count(deptno) from emp;  --返回值是14 这说明deptno重复的记录也被当做有效的记录
select count(distinct deptno) from emp; --返回值是3  统计deptno不重复的记录的个数
select count(comm) from emp; --返回值是4 这说明comm为null的记录不会被当做有效的记录


select max(sal) "最高工资", min(sal) "最低工资", count(*) "员工人数" from emp; --ok 


select max(sal), lower(ename) from emp;

group by (分组)【重点】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51


use scott;
select * from emp;

--输出每个部门的编号 和 该部门的平均工资
select deptno, avg(sal) as "部门平均工资" 	
	from emp 
	group by deptno 

-- 判断下面语句是否正确
select deptno, avg(sal) as "部门平均工资", ename
	from emp
	group by deptno 

-- 判断下面语句是否正确
select deptno, ename
	from emp
	group by deptno 


总结： 使用了group by 之后 select 中只能出现分组后的整体信息，不能出现组内的详细信息



group by a, b 的用法
--error
select deptno , job, sal
	from emp
	group by deptno, job

--error
select *
	from emp
	group by deptno, job

--ok
select deptno , job, avg(sal) "平均工资"
	from emp
	group by deptno, job
	order by deptno
--ok
select deptno , job, avg(sal) "平均工资", count(*) "部门人数", sum(sal) "某部门某工作总工资", min(sal) "某部门某工作最低工资" 
	from emp
	group by deptno, job
	order by deptno
	

select comm, count(*) 
	from emp
	group by comm

select max(sal) from emp; --默认把所有的信息当做一组

having[对分组之后的信息过滤难点]

having子句是用来对分组之后的数据进行过滤，因此使用having时通常都会先使用group by

如果没有使用group by 但使用了having 则意味着having 把所有的记录当做一组来进行过滤

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119


--输出部门平均工资大于2000的部门的部门编号 部门的平均工资
select deptno, avg(sal)
	from emp
	group by deptno
	having avg(sal) > 2000


--判断下列sql语句是否正确
select deptno, avg(sal) as "平均工资"
	from emp
	group by deptno
	having avg(sal) > 2000


--判断下列sql语句是否正确
select deptno, avg(sal) as "平均工资"
	from emp
	group by deptno
	having "平均工资" > 2000

--判断下列sql语句是否正确
select deptno "部门编号", avg(sal) as "平均工资"
	from emp
	group by deptno
	having deptno > 10


--判断下列sql语句是否正确
select deptno "部门编号", avg(sal) as "平均工资"
	from emp
	group by deptno
	having "部门编号" > 1



--判断下列sql语句是否正确
select deptno, avg(sal) as "平均工资"
	from emp
	group by deptno
	having  deptno > 10


--判断下列sql语句是否正确
select deptno, avg(sal) as "平均工资"
	from emp
	group by deptno
	having count(*) >5 --统计部门个数大于5个的


--判断下列sql语句是否正确
select deptno, avg(sal) as "平均工资"
	from emp
	group by deptno
	having ename like '%A%'







--把姓名不包含A的所有的员工按部门编号分组，
--统计输出部门平均工资大于2000的部门的部门编号 部门的平均工资
select deptno, avg(sal) "平均工资"
	from emp
	where ename not like '%A%'
	group by deptno
	having avg(sal) > 2000

--把工资大于2000，
--统计输出部门平均工资大于3000的部门的部门编号 部门的平均工资
select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门的最高工资"
	from emp
	where sal > 2000  --where是对原始的记录过滤
	group by deptno
	having avg(sal) > 3000  --对分组之后的记录过滤



--判断入选语句是否正确
select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门的最高工资"
	from emp	
	group by deptno
	having avg(sal) > 3000  --对分组之后的记录过滤
	where sal > 2000  --where是对原始的记录过滤

总计： 所有select的参数的顺序是不允许变化的，否则编译时出错 




select * from emp;



select count(*) 
	from emp 
	having avg(sal) > 1000



select ename, sal "工资" 
	from emp
	where sal > 2000

select ename, sal "工资" 
	from emp
	where "工资"  > 2000



select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门的最高工资"
	into emp_2 //把查询结果放入一个新表
	from emp
	where sal > 2000  --where是对原始的记录过滤
	group by deptno
	having avg(sal) > 3000  --对分组之后的记录过滤

select * from emp_2

连接查询

内连接

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128


emp
dept
salgrade

select * from emp, dept -- 70行 11列
select * from dept, emp -- 输出结果等价于 select * from emp, dept

select * from emp, dept where 1=1   --  70行 11列  where中写的过滤条件实际是对(emp, dept)产生的笛卡尔积临时表过滤
select * from emp, dept where empno = 7369 --5行
select * from emp, dept where deptno = 10; --error
select * from emp, dept where emp.deptno = 10;  --5行的倍数
select * from emp, dept where dept.deptno = 10;  --14行


--本语句输出结果等价于  select * from emp, dept where dept.deptno = 10; 
select *
	from emp
	join dept
	on 2=2
	where dept.deptno = 10;




--输出70行11列
select *
	from emp
	join dept
	on 1=1
等价于
select * from emp, dept
等价于
select * from dept, emp
等价于
select *
	from dept
	join emp
	on 1=1



--考虑下面语句的实际含义  以及这样写是否恰当?
select * from emp
	join dept
	on emp.deptno = dept.deptno and emp.sal>2000  --on中既可以写连接条件 也可以写过滤条件 但是不推荐 应该分开写 on中只写连接条件 where中写过滤条件
等价于
select * from emp
	join dept
	on emp.deptno = dept.deptno 
	where emp.sal>2000
等价于
select * from dept
	join emp
	on dept.deptno  = emp.deptno
	where emp.sal>2000
等价于
select *
	from dept,emp
	where dept.deptno  = emp.deptno and emp.sal>2000


--error 有join就必须的有on
select * from emp
	join dept




查询的顺序
select top ....
	from A
	join B
	on ....
	join C
	on ....
	where ......
	group by ...
	having .....
	order by .....

--把工资大于1500的所有的员工按部门分组 把部门平均工资大于2000的最高前2个的部门的编号 部门的名称 部门平均工资的等级
--第一种写法
select "T".*, "D".dname, "S".grade
	from dept "D"
	join (
		select top 2 "E".deptno, avg(sal) "avg_sal"
			from emp "E"
			join dept "D"
			on "E".deptno = "D".deptno
			join salgrade "S"
			on "E".sal between "S".losal and "S".hisal
			where "E".sal > 1500
			group by "E".deptno
			having avg("E".sal) > 2000
			order by avg("E".sal) desc
	) "T"
	on "D".deptno = "T".deptno
	join salgrade "S"
	on "T"."avg_sal" between "S".losal and "S".hisal


--第二种写法
select "T".*, "D".dname, "S".grade
	from dept "D"
	inner join (
		select top 2 deptno, avg(sal)  as "avg_sal"
			from emp 
			where sal > 1500
			group by deptno
			having avg(sal) > 2000
			order by "avg_sal" desc
	) "T"
	on "D".deptno = "T".deptno
	inner join salgrade "S"
	on "T"."avg_sal" between "S".losal and "S".hisal
	

	


--输出的行数肯定是dept表行数的倍数
select * from emp, dept  
where emp.deptno =10 --5*3=15

--输出的行数肯定是emp表行数的倍数 
select * from emp, dept  
	where dept.deptno = 10   --14*1=14
	

外连接

1
2
3


select * from dept,emp 
select * from dept  left join emp  on emp.deptno=dept.deptno --左外链接
select * from emp  right join dept  on emp.deptno=dept.deptno --右外连接

完全连接

1

select * from emp full join dept  on emp.deptno=dept.deptno --完全连接

交叉连接

1
2


select * from emp cross join dept --交叉连接
等价于 select * from dept,emp 

自连接

1
2
3
4
5
6
7
8


select * ,sal "薪水最高"from emp where sal=(select max(sal)from emp )
DELETE FROM emp where ename = 'zhoujun'

select * from emp 
where sal not in (select distinct"e1".sal from emp "e1", emp "e2" where e1.sal<e2.sal)

select * from emp 
where sal not in(select distinct "e1".sal from emp"e1" join emp "e2"  on e1.sal<e2.sal)

联合

1
2
3
4
5
6


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

分页查询

1
2
3
4


select top 3 *
	from emp
	where empno not in (select top 6 empno from emp order by sal desc)
	order by sal desc	

identity

1
2
3
4
5


create table student3
(
	student_id int primary key identity(100, 5), --必须的为student_id赋值
	student_name nvarchar(200) not null 
)

嵌套查询

视图

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


求出平均工资最高的部门 的编号和部门的平均工资
select *
	from (
		select deptno, avg(sal) "avg_sal" 
			from emp 
			group by deptno	
	) "T"
	where "T"."avg_sal" = (
		select max("E"."avg_sal") from (
			select deptno, avg(sal) "avg_sal" 
				from emp 
				group by deptno
		) "E"
	)
用视图如下：
create view emp_1 
as
	select deptno, avg(sal) "avg_sal" 
		from emp 
		group by deptno

select * from  emp_1
	where avg_sal  = (select max(avg_sal) from v$_emp_1) 

视图中select必须为计算列指定别名
--error
			create view v$_a
				as 
					select avg(sal) from emp;

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

视图只简化了查询但并不能加快查询速度

事务

实现多个用户对共享资源的同时访问，保证数据的并发处理能力。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38


create database Test

select * from bank 
delete from bank 

use Test
create table bank
(
	customerEname nvarchar(200),
	currentMoney money 
)
insert into bank values ('张三', 1000)
insert into bank values ('李四', 1)

alter table bank add constraint check_currentMoney check(currentMoney>=1)

update bank set currentMoney=currentMoney-1000 where customerEname='张三'
update bank set currentMoney=currentMoney+1000 where customerEname='李四'

begin transaction
declare @errorSum int
set @errorSum = 0
update bank set currentMoney=currentMoney-1000 
	where customerEname='张三'
set @errorSum = @errorSum + @@error
update bank set currentMoney=currentMoney+1000 
	where customerEname='李四'
set @errorSum = @errorSum + @@error
if (@errorSum <> 0)
	begin
		print '转账失败'
		rollback transaction
	end
else
	begin
		print '转账成功'
		commit transaction
	end

数据库原理笔记

文章目录

基础内容

查询 (顺序)

计算机列

distinct(不允许重复的)

between(在某个范围)

in(属于若干个孤立的值)

top(最前面的若干个记录专属sql server的语法，不可移植到其他数据库)

null（没有值，空值）

order by (以某个字段排序)

模糊查询 (搜索时经常用)

聚合函数（多行记录返回一个值通常用于统计分组信息）

group by (分组)【重点】

having[对分组之后的信息过滤难点]

连接查询

内连接

外连接

完全连接

交叉连接

自连接

联合

分页查询

identity

嵌套查询

视图

事务

索引

存储过程

游标

TL-SQL

触发器

文章目录

基础内容

查询 (顺序)

计算机列

distinct(不允许重复的)

between(在某个范围)

in(属于若干个孤立的值)

top(最前面的若干个记录 专属sql server的语法，不可移植到其他数据库)

null（没有值，空值）

order by (以某个字段排序)

模糊查询 (搜索时经常用)

聚合函数（多行记录返回一个值通常用于统计分组信息）

group by (分组)【重点】

having[对分组之后的信息过滤 难点]

连接查询

内连接

外连接

完全连接

交叉连接

自连接

联合

分页查询

identity

嵌套查询

视图

事务

索引

存储过程

游标

TL-SQL

触发器

top(最前面的若干个记录专属sql server的语法，不可移植到其他数据库)

having[对分组之后的信息过滤难点]