Java数据库基础--以SQL Server为例

ゞ浴缸里的玫瑰 2022-06-02 06:18 273阅读 0赞

## sql server数据库基本概念 ##

使用文件保存数据存在几个缺点：

1、文件的安全性问题；

2、文件不利于查询和对数据的管理；

3、文件不利于存放海量数据

4、文件在程序中控制不方便。

### 数据库的定义(1) ###

严格地说，数据库是“按照数据结构来组织、存储和管理数据的仓库”。在经济管理的日常工作中，常常需要把某些相关的数据放进这样的“仓库”，并根据管理的需要进行相应的处理。例如，企业或事业单位的人事部门常常要把单位职工的基本情况(职工号、姓名、年龄、性别、籍贯、工资、简历等)存放在表中，这张表就可以看成一个数据库。有了这个“数据仓库”我们就可以根据需要随时查询某职工的基本情况，也可以查询工资在某个范围内的职工人数等等。这些工作如果都能在计算机上自动进行，那我们的人事管理就可以达到极高的水平。此外，在财务管理、仓库管理、生产管理中也需要建立众多的这种“数据库”，使其可以利用计算机实现财务、仓库、生产的自动化管理。

### 数据库的定义(2) ###

J.Martin给数据库下了一个比较完整的定义：数据库是存储在一起的相关数据的集合，这些数据是结构化的，无有害的或不必要的冗余，并为多种应用服务；数据的存储独立于使用它的程序；对数据库插入新数据，修改和检索原有的数据均能按一种公用的和可控制的方式进行。当某个系统中存在结构上完全分开的若干个数据库时，则该系统包含一个“数据库集合”。

## 数据库的基本结构 ##

数据库的基本结构分三个层次，反映了观察数据库的三种不同角度。

1、物理数据层

它是数据库的最内层，是物理存贮设备上实际存储的数据的集合。这些数据的原始数据，是用户加工的对象，由内部模式描述的指令操作处理的位串、字符和字组成。

2、概念数据层

它是数据库的中间一层，是数据库的整体逻辑表示。指出了每个数据的逻辑定义及数据间的逻辑联系，是存贮记录的集合。它所涉及的是数据库所有对象的逻辑关系，而不是它们的物理情况，是数据库管理员概念下的数据库。

3、逻辑数据层

它是用户所看到和使用的数据库，表示了一个或一些特定用户使用的数据集合，即逻辑记录的集合。

数据库不同层次之间的联系是通过映射进行转换的。

## 数据库的基本特点 ##

1、实现数据共享

数据共享包含所有用户可同时存取数据库中的数据，也包括用户可以用各种方式通过接口使用数据库，并提供数据共享。

2、减少数据的冗余度

同文件系统比，数据库实现了数据共享，从而避免了用户各自建立应用文件。减少了大量重复数据，减少了数据冗余，维护了数据的一致性

3、数据实现集中控制

文件管理方式中，数据处于一种分散的状态，不同的用户或同一用户在不同处理中其文件之间毫无关系。利用数据库可对数据进行集中控制和管理，并通过数据模型表示各种数据的组织以及数据间的联系。

4、数据一致性和可维护性，以确保数据的安全性和可靠性。

5、故障恢复

## 目前主流数据库 ##

微软： Sql Server和Access

瑞典MySQL： AB公司MySql

IBM公司： DB2和Informix

美国Sybase公司： Sybase

美国Oracle公司： Oracle

数据库选择：

1、成本；2、功能；3、并发性要求；4、安全性；

### sql server介绍(1) ###

SQL是英文Structured Query Language的缩写，意思为结构化查询语言。SQL语言的主要功能就是同各种数据库建立联系，进行沟通。按照ANSI(美国国家标准协会)的规定，SQL被作为关系型数据库管理系统的标准语言。SQL语句可以用来执行各种各样的操作，例如更新数据库中的数据，从数据库中提取数据等。目前，绝大多数流行的关系型数据库管理系统，如Oracle、Sybase、Microsoft SQL Server、Access等都采用了SQL语言标准。

### sql server介绍(2) ###

SQL Server是一个关系数据库管理系统。它最初是由Microsoft Sybase和Ashton-Tate三家公司共同开发的，于1988年推出了第一个OS/2版本。在Windows NT推出后，Microsoft与Sybase在SQL Server的开发上就分道扬镳了，Microsoft将SQL Server移植到Windows NT系统上，专注于开发推广SQL Server的Windows NT版本。Sybase则较专注于SQL Server在UNIX操作系统上的应用。

企业管理器的使用(PS:比较简单，不再赘述)

## 查询分析器的使用(1) ##

企业管理器给用户提供了一个很方便的图形界面管理工具，用起来感觉直观方便，可是它也有缺点，当一个表的记录非常大的时候，对表的各种操作，都显得不方便了，比如：

1、要求从1000行记录中查询是否存在名为“孙小明”的人

2、要求把1000行记录中工资低于100的人，工资增加10%

3、要求把年龄大于30的人从数据表中删除

这时，会发现使用企业管理器是很不方便的

所以微软为我们提供另外一个操作数据库的工具，查询分析器。查询分析器可以非常方便的完成上述任务。

## 查询分析器的使用(2)--sql语句 ##

SQL全称是“结构化查询语言(Structured Query Language)”。

SQL(Structured Query Language)是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。结构化查询语言(Structured Query Language)最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言，它的前身是SQUARE语言。SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言得到了广泛的应用。如今无论是像Oracle、Sybase、Informix、SQL Server这些大型的数据库管理系统，还是像Visual Foxpro、PowerBuilder这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。

## 查询分析器的使用(3)--sql语句 ##

SQL语言包含4个部分：

数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句。

数据操作语言(DML)，例如：INSERT、UPDATE、DELETE语句。

数据查询语言(DQL)，例如：SELECT语句。

数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。

## 查询分析器的使用(4)--实例 ##

**\[sql\]** view plain copy

1.  \--创建数据库  
2.  **create** **database** LiangshanHero;  
3.    
4.  \--使用数据库  
5.  use LiangShanHero;  
6.    
7.  \--创建表  
8.  **create** **table** hero  
9.  (  
10.     heroId **int**, \--排名  
11.     heroName **varchar**(50), \--名字  
12.     heroNickName **varchar**(50), \--外号  
13.     sex **char**(2) \--性别  
14. );  
15.   
16. **alter** **table** hero **add** sal **int**;   \--添加薪水属性  
17.   
18. \--使用SQL语句来添加数据  
19. **insert** **into** hero **values**(1, '宋江', '及时雨', '男');  
20. **insert** **into** hero **values**(2, '卢俊义', '玉麒麟', '男');  
21. **insert** **into** hero **values**(3, '吴用', '智多星', '男');  
22. **insert** **into** hero **values**(4, '公孙胜', '入云龙', '男');  
23.   
24. \--使用SQL语句来修改数据  
25. **update** hero **set** sal = 20000 **where** heroid = 1;  
26. **update** hero **set** sal = 15000 **where** heroid = 2;  
27. **update** hero **set** sal = 10000 **where** heroid = 3;  
28. **update** hero **set** sal = 13000 **where** heroid = 4;  
29.   
30. \--使用SQL语句来查询数据  
31. **select** \* **from** hero;  
32.   
33. \--查询工资低于的人  
34. **select** \* **from** hero **where** sal < 15000;  
35.   
36. \--把工资低于的人，工资提高%  
37. **update** hero **set** sal = sal \* 1.1 **where** sal < 15000;  
38.   
39. \--删除性别为女的人  
40. **delete** **from** hero **where** sex = '女';

## 表的管理--表名和列的命名规则 ##

1、必需以字母,\_开头，例如：ta,\_ta

2、长度不能超过128个字符

3、不要使用sql server的保留字

4、只能使用如下字符A-Z，a-z，0-9，$，\#，\_等

## 表的管理--支持的数据类型 ##

### 字符型(非unicode编码) ###

char 定长 最大8000字符(非unicode编码)

char(10)'Switch'前5个字符放'Switch'，后添5个空格补全

varchar 变长 最大8000字符(非unicode编码)

varchar(10)'Switch'sql server分配5个字符，这样可以节省空间

ntext可变长度Unicode数据的最大长度为2的30次方-1(1,073,741,823)个字符

text可变长度非Unicode数据的最大长度为2的31次方-1(2,147,483,647)个字符

区别：

1、text是字节格式存储英文的，也可以存中文但有时候会显示成乱码

2、ntext是多字节格式存储unicode的，也就是存储各种文字用的。

在什么时候使用char型而不使用varchar,在知道字段定长固定时就使用char

### 字符型(unicode编码) ###

nchar 定长 最大4000字符(unicode编码)

nchar(20)'Switch'前10个字符放'Switch'，后添10个空格补全

nvarchar 变长 最大4000字符(unicode编码)

nvarchar(20)'Switch'sql server分配10个字符，这样可以节省空间

特别说明：

1、一般带有汉字的字段用nvarchar，全英文或符号的用varchar，因为nvarchar为unicode字符集，该类型的字段无论是单个字母还是单个汉字都占两个字节，而varchar，字母占一个字节，汉字占两个，nvarchar处理汉字或其它unicode字符集的速度要比varchar字段快。

2、如果有一些特殊字符在nvarchar中没有的，如日文的某些字符，那当然只能选合适的代码页用varchar了，而且这些特有语言特有的字符转换到nvarchar会消失

### 数字型 ###

1、bit 范围 0到1

2、int 范围 负的2的31次方到正的 2的31次方-1

3、bigint 范围 负的2的63次方到正的 2的63次方-1

4、float 存放小数，不推荐使用

5、numeric 小数

强烈建议 如果要去存放小数最好使用numeric

### 日期类型 ###

datetime(表示日期)

timestamp(时间戳)

一般情况下 用datetime表示日期

\--sql server为我们提供一个专门的时间函数getdate()

**\[sql\]** view plain copy

1.  **create** **table** ta\--建表  
2.  (bir datetime)\--字段  
3.  **insert** **into** ta **values**(getdate())\--添加时间

### 图片 ###

image保存图片，但是用的很少，一般用路径保存图片，在软件公司往往使用图片服务器和图床技术

### 视频 ###

binary字段可以存放，但是往往将视频文件保存在文件服务器上，sql server中只保留文件路径，这样存取效率高。

## 表的管理--添加数据 ##

插入部分字段(语法)

insert into 表名 (字段名1,字段名2,...) values (对应字段数据1,对应字段数据2,...)

## 表的管理--修改数据 ##

修改一个字段(语法)

update 表名 set 字段名='新值' where 字段='值'

例：

**\[sql\]** view plain copy

1.  **update** student **set** sex='女' **where** xh='A001'  
2.  **update** clerk **set** age=38 **where** cleName='贾政'

修改多个字段(语法)

update 表名 set 字段名1='新值',字段名2='新值'... where 字段='值'

例：

**\[sql\]** view plain copy

1.  **update** student **set** sex='男',birthday='1980-04-01' **where** xh='A001'  
2.  **update** clerk **set** cleName='薛蟠',age=40 **where** cleId=2

修改含有null值的数据(语法)

update 表名 set 字段名='新值' where 字段 is null

例：

**\[sql\]** view plain copy

1.  **update** clerk **set** cleName='薛宝钗' **where** age **is** null

## 表的管理--删除数据 ##

删除全部数据

delete from 表名

删除指定数据

delete from 表名 where 字段名='值'

删除多个指定数据

delete from 表名 where 字段名1='值' and 字段名2='值' and或or ...

## 表的管理--修改表 ##

添加一个字段

**\[sql\]** view plain copy

1.  **ALTER** **TABLE** distributors **ADD** **COLUMN** address **varchar**(30);

修改字段的类型/或是名字(不能有数据)

**\[sql\]** view plain copy

1.  **ALTER** **TABLE** distributors  
2.      **ALTER** **COLUMN** address TYPE **varchar**(80),  
3.      **ALTER** **COLUMN** **name** TYPE **varchar**(100);  
4.  **ALTER** **TABLE** distributors RENAME **COLUMN** address **TO** city;

删除一个字段

**\[sql\]** view plain copy

1.  **ALTER** **TABLE** distributors **DROP** **COLUMN** address **RESTRICT**;

修改表的名字

**\[sql\]** view plain copy

1.  **ALTER** **TABLE** distributors RENAME **TO** suppliers;

删除表

drop table 表名;

## 主键和外键 ##

主键，一张表中只能有一个主键

1、主键(primary key)，不能够重复出现；

2、主键必需给值，换言之主键不能为null

3、主键可以修改，但不能修改为同名的，同时要明确字段不有重复，否则报错。

外键

1、外键只能指向主键

2、外键和主键的数据类型要一致

## 员工管理系统 ##

PS:之后很多例子都是基于下面所建数据库和表的

**\[sql\]** view plain copy

1.  \---创建dept表  
2.  **create** **table** dept  
3.  (  
4.      deptno **int** **primary** **key**, \--部门编号  
5.      dname nvarchar(30), \--部门名称  
6.      loc nvarchar(30) \--所在地  
7.  );  
8.    
9.  \--创建emp表  
10. **create** **table** emp  
11. (  
12.     empno **int** **primary** **key**, \--员工编号  
13.     ename nvarchar(30), \--员工姓名  
14.     job nvarchar(30), \--员工职位  
15.     mgr **int**, \--上级编号  
16.     hiredate datetime, \--入职日期  
17.     sal **numeric**(8, 2), \--薪水  
18.     comm **numeric**(8, 2), \--奖金  
19.     deptno **int** **foreign** **key** **references** dept(deptno)              \--deptno是外键  
20. );  
21.   
22. \--外键  
23. \--①外键引用的键必须是主键  
24. \--②外键的数据类型必须和引用的主键数据类型一致  
25.   
26. \--向dept表中添加数据  
27. **insert** **into** dept **values** (10,'accounting','new york');  
28. **insert** **into** dept **values** (20,'research','dallas');  
29. **insert** **into** dept **values** (30,'sales','chicago');  
30. **insert** **into** dept **values** (40,'operations','boston');  
31.   
32. \--向emp表中添加数据  
33. **insert** **into** emp (empno,ename,job,mgr,hiredate,sal,deptno) **values** (7369,'smith','clerk',7902,'1980-12-17',800.00,20);  
34. **insert** **into** emp **values**(7499,'allen','salesman',7698,'1981-2-20',1600.00,300.00,30);  
35. **insert** **into** emp **values**(7521,'ward','salesman',7698,'1981-2-22',1250.00,500.00,30);  
36. **insert** **into** emp (empno,ename,job,mgr,hiredate,sal,deptno) **values** (7566,'jones','manager',7839,'1981-4-2',2975.00,20);  
37. **insert** **into** emp **values**(7654,'martin','salesman',7698,'1981-9-28',1250.00,1400.00,30);  
38. **insert** **into** emp (empno,ename,job,mgr,hiredate,sal,deptno) **values** (7698,'blake','manager',7839,'1981-5-1',2850.00,30);  
39. **insert** **into** emp (empno,ename,job,mgr,hiredate,sal,deptno) **values** (7782,'clark','manager',7839,'1981-6-9',2450.00,10);  
40. **insert** **into** emp (empno,ename,job,mgr,hiredate,sal,deptno) **values** (7788,'scott','analyst',7566,'1987-4-19',3000.00,20);  
41. **insert** **into** emp (empno,ename,job,hiredate,sal,deptno) **values** (7839,'king','president','1981-11-17',5000.00,10);  
42. **insert** **into** emp **values** (7844,'turner','salesman',7698,'1981-9-8',1500.00,0.00,30);  
43. **insert** **into** emp (empno,ename,job,mgr,hiredate,sal,deptno) **values** (7876,'adams','clerk',7788,'1987-5-23',1100.00,20);  
44. **insert** **into** emp (empno,ename,job,mgr,hiredate,sal,deptno) **values** (7900,'james','clerk',7698,'1981-12-3',950.00,30);  
45. **insert** **into** emp (empno,ename,job,mgr,hiredate,sal,deptno) **values** (7902,'ford','analyst',7566,'1981-12-3',3000.00,20);  
46. **insert** **into** emp (empno,ename,job,mgr,hiredate,sal,deptno) **values** (7934,'miller','clerk',7782,'1982-1-23',1300.00,10);

### 表的简单查询 ###

**\[sql\]** view plain copy

1.  \--表的简单查询  
2.  \--查询部门信息  
3.  **select** \* **from** dept;  
4.    
5.  \--查询员工信息  
6.  **select** \* **from** emp;  
7.    
8.  \--查询SMITH的薪水，工作，所在部门  
9.  **select** sal, job, deptno   
10. **from** emp **where** ename = 'SMITH';  
11.   
12.   
13. \--查询员工表中有多少个部门  
14. \--distinct会消除全部属性一样的行,只保留一行  
15. **select** **distinct** deptno **from** emp;  
16. **select** **distinct** deptno, ename **from** emp;  
17.   
18.   
19. \--显示每个员工的年工资  
20. \--as后面是别名，可以不加as，别名也可以不加引号  
21. \--四则运算，空值问题,用isnull()解决  
22. **select** ename, (sal \* 12) + isnull(comm \* 12, 0) **as** '年工资' **from** emp;  
23.   
24. \--显示工资高于3000的员工  
25. **select** \* **from** emp **where** sal > 3000;  
26.   
27. \--查找.1.1以后入职的员工  
28. **select** \* **from** emp **where** hiredate > '1982-1-1';  
29.   
30. \--显示工资在到的员工  
31. **select** \* **from** emp **where** sal > 2000 and sal < 2500;  
32.   
33. \--between A and B表示A >= X <= B  
34. **select** \* **from** emp **where** sal between 2001 and 2499;  
35.   
36. \--显示首字符为S的员工的姓名和工资  
37. **select** ename, sal **from** emp **where** ename like 'S%';  
38.   
39. \--显示第三个字符是O的员工姓名和工资  
40. **select** ename, sal **from** emp **where** ename like '\_\_O%';  
41.   
42. \--显示empno为123,345,800的员工  
43. **select** \* **from** emp **where** empno = 123 or empno = 345 or empno = 800;  
44. **select** \* **from** emp **where** empno in(123, 345, 800);  
45.   
46. \--查询没有上级的员工  
47. **select** \* **from** emp **where** mgr **is** null;  
48.   
49. \--查询工资高于或者是岗位为MANAGER的员工，并且姓名首字母为J  
50. **select** \* **from** emp **where** (sal > 500 or job = 'MANAGER') and ename like 'J%';  
51.   
52. \--按照工资从低到高显示员工  
53. \--order by ASC 默认升序  
54. \--order by DESC 降序  
55. **select** \* **from** emp **order** **by** sal **ASC**;  
56.   
57. \--按照入职先后顺序显示员工  
58. **select** \* **from** emp **order** **by** hiredate **ASC**;  
59.   
60. \--按照名字升序排列  
61. **select** \* **from** emp **order** **by** ename **ASC**;  
62.   
63. \--按照部门升序，员工的工资降序显示  
64. \--order by 可以根据不同的字段排序，order by a, b;  
65. **select** \* **from** emp **order** **by** deptno **ASC**, sal **DESC**;  
66.   
67. \--算出每个人的年薪，并按照升序显示  
68. **select** ename, sal \* 13 + isnull(comm \* 13, 0) **as** '年薪' **from** emp **order** **by** '年薪' **ASC**;

### 表的复杂查询 ###

数据分组 -max(最大),min(最小),avg(平均),sum(和),count(统计)

group by和having子句

group by用于对查询的结果分组统计

having子句用于限制分组显示结果

**\[sql\]** view plain copy

1.  \--表的复杂查询  
2.  \--显示所有员工中工资最高的员工  
3.  **select** ename, sal  
4.  **from** emp   
5.  **where** sal = (   **select** **max**(sal) **as** '最高薪水' **from** emp);  
6.    
7.  \--显示所有员工中工资最低的员工  
8.  **select** ename, sal  
9.  **from** emp   
10. **where** sal = (   **select** **min**(sal) **as** '最低薪水' **from** emp);  
11.   
12. \--显示所有员工的平均工资和总工资  
13. **select** avg(sal) **as** '平均工资', sum(sal) **as** '总工资' **from** emp;  
14.   
15. \--把高于平均工资的员工的名字和工资显示  
16. **select** ename, sal **from** emp **where** sal > (**select** avg(sal) **from** emp);  
17.   
18. \--显示员工名并显示平均工资  
19. **select** ename, sal,(**select** avg(sal) **from** emp) **as** '平均工资' **from** emp **where** sal > (**select** avg(sal) **from** emp );  
20.   
21. \--计算一共有多少员工  
22. **select** count(\*) **as** '员工总数' **from** emp;  
23.   
24. \--显示每个部门的平均工资和最高工资  
25. **select** deptno,avg(sal) **as** '平均工资', **max**(sal) **as** '最高工资' **from** emp **group** **by** deptno;  
26.   
27. \--显示每个部门的每个岗位的平均工资和最低工资  
28. **select** deptno, job, avg(sal) **as** '平均工资', **min**(sal) **as** '最低工资' **from** emp **group** **by** deptno, job **order** **by** deptno **ASC**, '平均工资' **DESC**;  
29.   
30. \--显示平均工资低于2000的部门号和平均工资  
31. \--group by ... having ... having是对分组后的数据进行筛选  
32. **select** deptno, avg(sal) **as** '平均工资' **from** emp **group** **by** deptno **having** avg(sal) < 2000;

对数据分组的总结

1、分组函数只能出现在选择列表，having、order by子句中

2、如果在select语句中同时包含有group by，having，ovrder by那么他们的顺序是group by，having，order by

3、在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必需有一个出现在group by子句中，否则就会出错

4、如果想在分组情况下，选择列中显示某列，那么这行必须要在group by子句中，否则会出错

如：select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000

这里deptno就一定要出现在group by中

### 表的复杂查询--多表查询 ###

多表查询是指基于两个或两个以上的表或是视图的查询，在实际应用中，查询单个表可能不能满足需求，(如显示sales部门位置和其员工的姓名)，这种情况下需要使用到(dept表和emp表)

**\[sql\]** view plain copy

1.  \--多表查询  
2.  \--如果多张表都有相同名字的字段，则需要带表名(别名)  
3.  \--显示sales部门位置和其员工的姓名  
4.  **select** loc, ename **from** dept, emp **where** dname = 'sales'  and dept.deptno = emp.deptno;  
5.    
6.  \--显示员工名，部门名和部门编号  
7.  \--连接的表中有相同的字段，需要带表名(别名)  
8.  **select** dept.deptno, dname, ename **from** dept, emp **where** dept.deptno = emp.deptno;  
9.    
10. \--显示部门号为10的部门名，员工名和工资  
11. **select** d.deptno, e.ename, e.sal **from** emp e, dept d **where** e.deptno = d.deptno and d.deptno = 10;  
12.   
13. \--显示员工名，员工工资及所在部门的名称，并按部门排序  
14. **select** e.ename, e.sal, d.dname **from** emp e, dept d **where** e.deptno = d.deptno **order** **by** d.deptno;

### 表的复杂查询--自连接查询 ###

自连接是指在同一张表的连接查询

**\[sql\]** view plain copy

1.  \--自关联查询，内连接  
2.  \--显示'FORD'的上级领导的姓名  
3.  **select** e2.ename **from** emp e1, emp e2 **where** e1.mgr = e2.empno and e1.ename = 'FORD';  
4.    
5.  \--显示每个员工的名字和他的上级姓名  
6.  **select** e1.ename,e2.ename **from** emp e1 emp e2 **where** e1.mgr = e2.empno;

### 表的复杂查询--子查询 ###

子查询是批嵌入在其它sql语句中的select语句，也叫嵌套查询

#### 单行子查询 ####

单行子查询是指子查询只返回一行数据的子查询语句

**\[sql\]** view plain copy

1.  \--显示与SMITH同一部门的所有员工  
2.  **select** \* **from** emp **where** deptno = (**select** deptno **from** emp **where** ename = 'SMITH');

#### 多行子查询 ####

多行子查询指子查询返回多行数据的子查询

**\[sql\]** view plain copy

1.  \--查询和部门10的工作相同的员工的名字  
2.  **select** ename **from** emp **where** job =  any(**select** **distinct** job **from** emp **where** deptno = 10);   
3.  **select** ename **from** emp **where** job in(**select** **distinct** job **from** emp **where** deptno = 10);   
4.    
5.  \--查询和部门的工作相同的员工的名字并且不包括号部门的员工  
6.  **select** ename **from** emp **where** job in(**select** **distinct** job **from** emp **where** deptno = 10) and deptno != 10;

#### 在from子句中使用子查询 ####

当在from子句中使用子查询时，该子查询会被作为一个临时表来对待，当在from子句中使用子查询时，必需给子查询指定别名

**\[sql\]** view plain copy

1.  \--显示高于部门平均工资的员工的信息  
2.  **select** emp.ename, emp.deptno, emp.sal   
3.  **from** emp, (**select** deptno, avg(sal) **as** '部门平均工资' **from** emp **group** **by** deptno) e2   
4.  **where** emp.deptno = e2.deptno and emp.sal > e2.部门平均工资;

### 分页查询 ###

**\[sql\]** view plain copy

1.  \--显示第5个到第10个入职的员工(按照时间的先后顺序)  
2.  \--top后面的数表示要取出几条记录  
3.  **select** **top** 6 \* **from** emp **where** empno not in  
4.  (**select** **top** 4 empno **from** emp **order** **by** hiredate)  
5.  **order** **by** hiredate;  
6.    
7.  \--显示第11个到第13个入职的员工(按照时间的先后顺序)  
8.  **select** **top** 3 \* **from** emp **where** empno not in  
9.  (**select** **top** 10 empno **from** emp **order** **by** hiredate)  
10. **order** **by** hiredate;  
11.   
12. \--显示第5个到第9个员工(按照薪水的降序)  
13. **select** **top** 5 \* **from** emp **where** empno not in  
14. (**select** **top** 4 empno **from** emp **order** **by** sal **DESC**)  
15. **order** **by** sal **DESC**;  
16.   
17. \--演示分页性能，PS:可以试试看自己电脑性能怎样  
18. \--第一步：建一个表  
19. \--第二步：利用一条语句成指数增长插入数据  
20. \--第三步：测试查询全部的时间  
21. \--第四步：测试分页查询的时间  
22.   
23.   
24. \--第一步：建表  
25. **create** **table** test  
26. (  
27.     tId **int** **primary** **key** identity(1, 1), \--自增主键  
28.     tName **varchar**(30), \--账号  
29.     tPass **varchar**(30) \--密码  
30. );  
31.   
32. \--第二步：插入数据  
33. **insert** **into** test **values**('test','test');  
34.   
35. \--指数增长复制插入,一共插入w+记录  
36. **insert** **into** test(tName, tPass) **select** tName,tPass **from** test;  
37.   
38. \--第三步：查询全部的时间  
39.   
40. \--查询全部信息用时:45s  
41. **select** \* **from** test;  
42.   
43. \--查询tId 用时:39s  
44. **select** tId **from** test;  
45.   
46. \--第四步：查询分页的时间  
47.   
48. \--查询第100-105的数据按照id排序用时:0.1s  
49. **select** **top** 6 \* **from** test **where** tId not in  
50. (**select** **top** 99 tId **from** test);  
51.   
52. \--查询第500000-550000的数据按照id排序用时:6s  
53. **select** **top** 50001 \* **from** test **where** tId not in  
54. (**select** **top** 499999 tId **from** test);  
55.   
56.   
57. \--删除一张表的重复记录  
58. \--把test的记录distincth后的结果，放入\#temp  
59. **select** **distinct** tName, tPass **into** \#**temp** **from** test  
60. \--清除test中数据表  
61. **delete** **from** test  
62. \--把\#temp表的数据(没有重复记录),插入到test表中  
63. **insert** **into** test(tName, tPass) **select** tName, tPass **from** \#**temp**  
64. \--删除\#temp  
65. **drop** **table** \#**temp**;

### 表的复杂查询--外连接查询 ###

**\[sql\]** view plain copy

1.  \--显示每个员工和他上级的名字，没有上级的人，也要显示出来  
2.  \--左外连接:指左边的表的记录全部显示，如果没有匹配的记录就用NULL填写  
3.  **select** e1.ename **as** '下级', e2.ename **as** '上级' **from** emp e1 left join emp e2 **on** e1.mgr = e2.empno;  
4.  \--右外连接:指右边的表的记录全部显示，如果没有匹配的记录就用NULL填写  
5.  **select** e1.ename **as** '下级', e2.ename **as** '上级' **from** emp e1 right join emp e2 **on** e1.mgr = e2.empno;

#### 内连接，左外连接，右外连接的区别 ####

![9dcbe14cb83e87c0df4a72fd8af010ed.png][]

## 维护数据的完整性--约束 ##

约束用于确保数据库数据满足特定的规则。在sql server和oracle中，约束包括：not null、unique，primary key，foreign key和check五种

## 维护数据的完整性--使用 ##

### not null(非空) ###

如果在列上定义了not null，那么当插入数据时，必需为列提供数据。

**\[sql\]** view plain copy

1.  \--约束机制--not null(非空)  
2.  \--创建一张表  
3.  **create** **table** test1  
4.  (  
5.  test1Id **int** **primary** **key** identity(1,1),  
6.  testname **varchar**(30) not null,\--not null不能为空  
7.  testpass **varchar**(30) not null,  
8.  testage **int** \--不写代表可以为空  
9.  )  
10.   
11. **create** **table** test1  
12. (  
13. test1Id **int** **primary** **key** identity(1,1),\--identity(1,1)自增长1条记录  
14. testname **varchar**(30),   
15. testpass **varchar**(30),  
16. testage **int** \--不写代表可以为空  
17. )  
18. \--删除表  
19. **drop** **table** test1  
20. \--向表插入数据  
21. **insert** **into** test1 (testage) **values** (3)  
22. **insert** **into** test1 (testname,testpass,testage) **values** ('','',5)\--''空与null空是不一样的  
23. \--查询表内容  
24. **select** \* **from** test1

### unique(唯一)(一张表中可以有多个) ###

当定义了唯一约束后，该列值是不能重复的，但是可以为null,并只能有一个空值

**\[sql\]** view plain copy

1.  \--约束机制--unique(唯一)  
2.  \--建表  
3.  **create** **table** test2  
4.  (  
5.  test2Id **int** **primary** **key** identity(1,1),\--identity(1,1)自增长1条记录  
6.  testname **varchar**(30) **unique**, \--unique唯一的，数据不允许重复,但可以为空  
7.  testpass **varchar**(30),  
8.  testage **int** \--不写代表可以为空  
9.  )  
10.   
11. **insert** **into** test2 (testname,testpass,testage) **values** ('aa','123',45)  
12. **insert** **into** test2 (testpass,testage) **values** ('123',45)  
13.   
14. **select** \* **from** test2

### primary key(主键)(一张表中只可以有一个主键) ###

用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null

需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。

表可以有复合主键，有多个列构成一个主键

**\[sql\]** view plain copy

1.  \--复合主键  
2.  **create** **table** test3  
3.  (testId **int**,  
4.  testname **varchar**(30),   
5.  testpass **varchar**(30),  
6.  testage **int**,  
7.  **primary** **key** (testId,testname)\--复合主键，需单独声明  
8.  )  
9.    
10. \--行级定义和表级定义  
11. **create** **table** test4  
12. (testId **int**,\--在字段中定义主键为行级定义.例：testId int primary key  
13. testname **varchar**(30),   
14. testpass **varchar**(30),  
15. testage **int**,  
16. **primary** **key** (testId,testname)\--复合主键，需单独声明为表级定义  
17. )

### foreign key(外键)(外键在从表上，要配合主表，但主表要有主键或unique约束) ###

用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必需具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必需在主表的主键列存在或是null

### check(强制条件) ###

用于强制行数据必需满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不再1000-2000之间就会提示出错。

**\[sql\]** view plain copy

1.  \--check(强制条件)  
2.  **create** **table** test5  
3.  (testId **int**,  
4.  testname **varchar**(30),   
5.  testpass **varchar**(30),  
6.  sal **int** **check**(sal>=1000 and sal<=2000),\--规定sal的值在1000-2000之间  
7.  )  
8.    
9.  **insert** **into** test5 **values** (4,'aa','aa',1200)\--可以加入sal值在范围之内  
10. **insert** **into** test5 **values** (5,'bb','bb',2200)\--不可加入sal值在范围之外  
11.   
12. **select** \* **from** test5

### default使用(默认值) ###

**\[sql\]** view plain copy

1.  \--default使用  
2.  **create** **table** mes  
3.  (mesId **int** **primary** **key** identity(1,1),  
4.  mesccn **varchar**(2000) not null,  
5.  mesDate datetime **default** getdate()\--在不指定值是default可以直接取默认值，也可以由用户指定值  
6.  )  
7.    
8.  **insert** **into** mes(mesccn) **values**('abc')  
9.  **insert** **into** mes(mesccn,mesDate) **values**('cba','1976-1-6')  
10.   
11. **select** \* **from** mes

## 商店售货系统表设计案例 ##

现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：

商品goods(商品号goodsId，商品名goodsName，单价unitprice，商品类别category，供应商provider)；

客户customer(客户号customerId，姓名name，住址address，电邮email，性别sex，身份证cardId)；

购买purchase(客户号customerId，商品号goodsId，购买数量nums)；

请使用sql语言完成下列功能：

1、建表，在定义中要求声明：

1、每个表的主外键；

2、客户的姓名不能为空值；

3、单价必需大于0，购买数量大于0；

4、电邮不能够重复；

5、客户的性别必需是男或者女，默认为男

6、商品类别是'食物'，'日用品'

**\[sql\]** view plain copy

1.  \--商店收货系统表设计  
2.    
3.  \--goods表  
4.  **create** **table** goods  
5.  (  
6.      goodsId  nvarchar(50) **primary** **key**,  \--商品号  
7.      goodsName nvarchar(100) not null,   \--商品名  
8.      unitprice **numeric**(8, 2) **check**(unitprice > 0), \--单价  
9.      category nvarchar(3) **check**(category in('食物', '日用品')), \--商品类别,只能在食物或者日用品中  
10.     provider nvarchar(50)   \--供应商  
11. );  
12.   
13. \--customer表  
14. **create** **table** customer  
15. (  
16.     customId nvarchar(50) **primary** **key**,  \--客户号  
17.     cusname nvarchar(50) not null,      \--客户名  
18.     address nvarchar(100),              \--地址  
19.     email nvarchar(100) **unique**,         \--电子邮件  
20.     sex **nchar**(1) **check**(sex in('男','女')) **default** '男', \--性别  
21.     cardId nvarchar(18)                 \--身份证号  
22. );  
23.   
24.   
25. \--purchase表  
26. **create** **table** purchase  
27. (  
28.     customId nvarchar(50) **foreign** **key** **references** customer(customId), \--客户号  
29.     goodsId nvarchar(50) **foreign** **key** **references** goods(goodsId),      \--商品号  
30.     nums **int** **check**(nums >0)  \--数量  
31. );

## 数据库的备份和恢复 ##

### 使用企业管理器完成备份和恢复 ###

使用企业管理器有两种方式完成备份和恢复

#### 1、分离/附加 ####

分离完后，请到sql server安装的目录下去找两个文件数据库名.mdf和数据库名.ldf，这两个文件就是分离后的文件，数据库分离后，该数据库就不能再使用了。

附加是指，当用户需要重新使用某个分离的数据库时进行的操作，就是让sql server数据库重新关联该数据库。

#### 2、备份/恢复 ####

备份数据库是指，把某个数据库文件从sql server中备份出来，这样用户可以根据需要再使用(用于恢复、复用..)，备份数据库不会影响到源数据库的使用

恢复数据库是指，当源数据库因为某种原因(比如源数据库毁坏、数据丢失、系统崩溃)需要恢复时进行的操作

### 使用查询分析器完成备份和恢复 ###

用企业管理器完成对数据库的备份和恢复简单直观，同样也可以在查询分析器中完成类似的任务。而且这样的好处是在主语言比如JAVA，C++连接好数据库之后，可以实现对数据库的定时备份、恢复等。

**\[sql\]** view plain copy

1.  \--备份数据库  
2.  \--语法：backup database 数据库名 to disk='存储路径'  
3.  backup **database** LiangshanHero **to** disk='C:/LiangshanHero.bak';  
4.    
5.  \--恢复数据库  
6.  \--语法：restore database 数据库名 from disk='读取路径'  
7.  restore **database** LiangshanHero **from** disk='C:/LiangshanHero.bak';      
8.    
9.  \--新建数据库  
10. \--语法：create datebase 数据库名  
11. **create** **database** LiangshanHero;  
12.   
13. \--删除数据库  
14. \--语法：drop database 数据库名  
15. **drop** **database** LiangshanHero;

![6e8b6c624b6448a4ebd266c6d81987d1.gif][]

[9dcbe14cb83e87c0df4a72fd8af010ed.png]: /images/20220602/1f9a33461e574be88b514ecc6a112b46.png
[6e8b6c624b6448a4ebd266c6d81987d1.gif]: /images/20220602/581d221158b64065bfc4c2258ab609c4.png