JavaWeb-02-数据库

小咪咪 2023-10-06 11:05 54阅读 0赞

**Table of Contents**

1：数据类型

1.1：字符串类型

1.2：数值

1.3：日期

2:表的约束

2.1：约束类型

2.2：主键约束

2.3:删除约束

2.3：检查约束

2.4:追加约束（建表时没加，后期追加）

2.5：外键约束（foreign key）

3:对表的操作

3.1：创建表

3.2:删除表

3.3：增加数据

3.4：删除数据

3.5：修改数据

3.6：查询数据

3.7：修改表名

3.8:给列改名

3.9：数据库操作的条件

3.10:模糊查询

3.11：去重

3.12:分组

3.13：case

3.14：多表联合查询

4：表的拷贝

4.1：全部拷贝

4.2：表结构拷贝

4.3:拷过来的表没有约束，得自己加

5：一组统计函数

5.1：min(列名) 最小

5.2：max(列名) 最大

5.3：sum(列名) 求和

5.4：avg(列名) 求当前列平均值

5.5：count(列名) 用来统计查询结果有几条

6：存储过程和存储过程：

6.1:创建存储过程；

6.2:函数

7：视图

7.1：创建视图

7.2：删除视图

7.3:使用

8：表的集合运算

8.1:并集

8.2：交集

8.3：差集

9：内外链接

9.1：标准内连接（inner join）

9.2：标准外连接

9.2.1：全连接

9.2.2：左连接

9.2.3：右链接

10：索引（index）

10.1：索引优缺点：

10.2：使用索引的原则

10.3：创建索引

10.4：删除索引

--------------------

# 1：数据类型 #

## 1.1：字符串类型 ##

**变长；varchar2(10);nvarchar(8);**

*  **双字节； 两个字节的空间；**
 *  **单字节；一个英文或数字在表中占有一个字节的空间；**

**定长；char(10); nchar(n);**

*  **单字节；**
 *  **双字节；**

## 1.2：数值 ##

number

## 1.3：日期 ##

date

## 2:表的约束 ##

## 2.1：约束类型 ##

1：非空约束 not null

2：主键约束 primary key

3：默认约束 default

4：唯一性约束 unigue

5：检查约束 check

6：外键约束 foreign key

例子：

id char(32) default sys-guid prinary key(主键加默认)

name varchar2(10) unique(唯一)

sex nchar() check(sex in ('男','女'))

sage number check(sage between 16 and 30)

![20200614163748701.png][]

## 2.2：主键约束 ##

sid char(8) not null constraint pk-student 约束名称

简化：

sid char(8) primary key;

**追加主键约束**

alter table student add constraint 约束名 primary key(sid)

## 2.3:删除约束 ##

alter table student drop constraint 约束名称

## 2.3：检查约束 ##

**保证列数据的安全性和有效性；**

**区间；约束列数据使其，限制在某个范围区间内；比较运算符；**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70][]

## 2.4:追加约束（建表时没加，后期追加） ##

![20200614163809643.png][]

![20200614163816758.png][]![20200614163826516.png][]

![2020061416384064.png][]

![20200614163849674.png][]

## 2.5：外键约束（foreign key） ##

**外键约束通过用于多表之间，也偶尔用于特殊的单表中；**

**在一个A表中，用主键唯一的标识这一行记录；**

**在另一个B表中，要引用前一个表A中的记录；如何引用？用表A的主键代表，但此列在B表中，此列则为外键；两表中：主键名称和外键名称可以相同，也可以不同；但为了操作方便，名称定义为相同；**

**在具有主外键关联关系的两表中，外键所在的表（销售表）为子表或从表，主键所在表为父表或主表；对于销售表和商品表来说，先有商品表，即为主表，后有销售表，为从表；**

**1：主表和从表，现有主表才有从表，主键所在为主表，外键所在为从表**

**2：表中多对多，建两个表，在第三个表中设立两个外键**

追加外键约束：

**alter table stud\_teach**

**add**

**foreign key(tid) references teachs(tid);**

# 3:对表的操作 #

## 3.1：创建表 ##

CREATE TABLE `employee` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `lastName` varchar(255) DEFAULT NULL,
      `email` varchar(255) DEFAULT NULL,
      `gender` int(2) DEFAULT NULL,
      `d_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    )

## 3.2:删除表 ##

DROP TABLE IF EXISTS `department`;

## 3.3：增加数据 ##

insert into 表名（id,name）values (12,'张三');

## 3.4：删除数据 ##

delete from 表名 where id = 3;

## 3.5：修改数据 ##

update 表名 set name = '李四' where id = 3;

## 3.6：查询数据 ##

select * from 表名;

## 3.7：修改表名 ##

alert table 表名 rename to 改后的表名;

## 3.8:给列改名 ##

![20200614170251451.png][]

## **3.9：数据库操作的条件** ##

1.  **区间条件；比较运算符； where id >100**
2.  **范围条件；in子句； where name in('张三','李四','王五')**
3.  **模糊条件；与java中的正则表达式极为类似；**

## 3.10:模糊查询 ##

**like**

**任意的那个字符为‘\_’**

**任意多个字符为‘%’**

**例：select \* from emp where ename like 'A%'**

**多个模糊条件时用in**

![202006141707037.png][]

## 3.11：去重 ##

select distinct job form emp;
    
    select distinct job,name from emp;

## 3.12:分组 ##

**group by，分组函数通常和统计函数在一起使用；**

**having，对分组后的数据进行过滤；**

order by 列名 排序，默认为升序，降序用desc

例子：

select deptno as 部门号，count(*) as 人数
    form emp
    group by deptno
    having count(*) >4
    order by 人数 desc

## 3.13：case ##

**根据员工的薪水，将工资分为三个级别：低薪（小于2000）、中薪（2000-3000）和高薪（3000以上）；输出emp表中所有员工的姓名，薪水和级别；**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 1][]

**按照原薪水，涨工资：低薪\*2，中薪\*5，高薪\*10；**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 3][]

## **3.14：多表联合查询** ##

**对于数据库中的单表查询，都是“等值查询”，即只有符合条件的记录才查询出来； 多表查询主要凭借主外键关联关系；**

1.  **查询emp表中每个员工的姓名，部门编号，部门名称；**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 4][]

# **4：表的拷贝** #

## **4.1：全部拷贝** ##

create table 目的表 as select \* from 源表

## 4.2：表结构拷贝 ##

create table 目的表 as select \* from 源表 where 1<>1

## 4.3:拷过来的表没有约束，得自己加 ##

# 5：一组统计函数 #

## 5.1：min(列名) 最小 ##

## 5.2：max(列名) 最大 ##

## 5.3：sum(列名) 求和 ##

## 5.4：avg(列名) 求当前列平均值 ##

## 5.5：count(列名) 用来统计查询结果有几条 ##

# 6：存储过程和存储过程： #

## **6.1:创建存储过程；** ##

**在ORACLE中有两类对象，一是函数，一是存储过程；做为函数，一定有返回值；做为ORACLE中的存储过程，没有返回值；两者综合起来，相当于java中的方法的功能；**

![20200614162705618.png][]

## 6.2:函数 ##

**参数类型；**

1.  **输入类型的参数；用关键字in表示，可以省略；只能将数据带入到函数中，而不能通过参数将数据带出去；java中方法的参数样式；**
2.  **输出类型的参数；用关键字out表示；它只能将数据带出来；**

**输入、输出类型的参数；既可以将数据带进来，又可以带出去；**

create or replace function plus(a in number,b in number)
    return number
    is
    begin
    	declare 
    		tt number:=0;
    	begin
    		tt:=a+b;
    		return tt;
    	end;

# 7：视图 #

**在数据库中，视图是基于一个或多个表（也可以是视图）所生成的一条查询语句，返回结果为一个“虚拟表”；一个视图依赖于一张或多张表；视图不能独立存在；通过查询视图所看到的数据都来自于表本身；**

**视图可以指向整个单表，单表的部分列或部分行；多表的联合查询；**

**与表不同的是，视图没有固定的结构；当然也不能保存数据，通过视图所看到的所有内容，都来自于表； 视图是一种数据库对象，它也有创建、使用和删除的过程；**

## 7.1：创建视图 ##

create view v1
    as
    select empno 工号,ename 姓名,sal 薪水 from emp;

## 7.2：删除视图 ##

drop view v;

## 7.3:使用 ##

create view test as
    (select id,LOGINID,lastname,sex from hrmresource where sex <> '')
    
    select * from test

# 8：表的集合运算 #

aa表：

![20200614172933904.png][]

**bb表：**

![20200614172945815.png][]

## 8.1:并集 ##

**union all；内容完全相同的行记录也不例外；**

![20200614173013869.png][]

**union，会对相同项去重；**

![2020061417305699.png][]

## 8.2：交集 ##

![20200614173105247.png][]

## 8.3：差集 ##

![20200614173110707.png][]

# 9：内外链接 #

## 9.1：标准内连接（**inner join**） ##

我们平时常用的where a.id = b.id 就是简易的内链接

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 5][]

## 9.2：标准外连接 ##

*  **全（外）连接；full join；两表当中不符合等值条件的记录都会显示出来；**
 *  **左（外）连接；left join；左表中不符合条件的记录显示出来；**
 *  **右（外）连接；right join；右表中不符合条件的记录会显示；**

### 9.2.1：全连接 ###

select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
    from emp e
    full join
    dept d
    on
    e.deptno=d.deptno;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 6][]

### **9.2.2：左连接** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 7][]

### 9.2.3：右链接 ###

select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
    from emp e
    right join
    dept d
    on
    e.deptno=d.deptno;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 8][]

# 10：索引（index） #

作用：提高查询效率

## 10.1：索引优缺点： ##

建立索引的优点：  
1. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。  
2. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。  
3. 可以加速表和表之间的连接。  
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。  
索引有一些先天不足：  
1. 建立索引，系统要占用大约为表的 1.2 倍的硬盘和内存空间来保存索引。  
2. 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引  
的一致性。

## 10.2：使用索引的原则 ##

实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

使用原则：  
1. 在大表上建立索引才有意义。  
2. 在 where 子句或是连接条件上经常使用的列上建立索引，很少或从不引用的字段不建索引。

3. 在经常**需要排序的**列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间.  
4. 对于列中有许多空值，但经常查询所有的非空值记录的列，应该建立索引。  
5. LONG 和 LONG RAW 列不能创建索引。  
6. 索引只在返回较少比例的数据时才比全表扫描有效，大多数情况下认为结果集很大，一般大于 5%-15%就不使用索引查询。  
7. **!= 将不使用索引**， 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.  
8. ||是字符连接函数. 就象其他函数那样，停用了索引。  
9. + 是数学函数. 就象其他数学函数那样，停用了索引。  
10. like "%\_" 百分号在前时，停用了索引。  
11. 字符型字段为数字时在 where 条件里不添加引号时，停用了索引  
12. 相同的索引列不能互相比较,这将会启用全表扫描。

## 10.3：创建索引 ##

![20200614174159186.png][]

## **10.4：删除索引** ##

![20200614174212921.png][]

[20200614163748701.png]: https://img-blog.csdnimg.cn/20200614163748701.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200614163258830.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2,size_16,color_FFFFFF,t_70
[20200614163809643.png]: https://img-blog.csdnimg.cn/20200614163809643.png
[20200614163816758.png]: https://img-blog.csdnimg.cn/20200614163816758.png
[20200614163826516.png]: https://img-blog.csdnimg.cn/20200614163826516.png
[2020061416384064.png]: https://img-blog.csdnimg.cn/2020061416384064.png
[20200614163849674.png]: https://img-blog.csdnimg.cn/20200614163849674.png
[20200614170251451.png]: https://img-blog.csdnimg.cn/20200614170251451.png
[202006141707037.png]: https://img-blog.csdnimg.cn/202006141707037.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200614171514936.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200614171613850.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200614171639494.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2020061417185515.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2,size_16,color_FFFFFF,t_70
[20200614162705618.png]: https://img-blog.csdnimg.cn/20200614162705618.png
[20200614172933904.png]: https://img-blog.csdnimg.cn/20200614172933904.png
[20200614172945815.png]: https://img-blog.csdnimg.cn/20200614172945815.png
[20200614173013869.png]: https://img-blog.csdnimg.cn/20200614173013869.png
[2020061417305699.png]: https://img-blog.csdnimg.cn/2020061417305699.png
[20200614173105247.png]: https://img-blog.csdnimg.cn/20200614173105247.png
[20200614173110707.png]: https://img-blog.csdnimg.cn/20200614173110707.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200614173607230.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200614173918251.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200614173940318.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200614174020161.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjk0OTA2,size_16,color_FFFFFF,t_70
[20200614174159186.png]: https://img-blog.csdnimg.cn/20200614174159186.png
[20200614174212921.png]: https://img-blog.csdnimg.cn/20200614174212921.png