数据库总结

数据库总结

1.了解数据库

1.1了解什么是数据库： 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库；随着信息技术和市场的发展，数据管理不再仅仅是存储和管理数据，而转变成用户所需要的各种数据管理的方式 。

1.2常用数据库的分类：mysql , oracle

1.3sql语言的分类：DDL，DML , DCL

• DDL:数据定义语言：create： 创建 drop：删除 alter：修改 rename: 重命名 truncate: 截断
• DML数据管理语言： insert ：插入 delete：删除 update：更新 select ：查询
• DCL:数据控制语言： grant： 授权 revoke：回收权力 commit :提交事务 rollback: 回滚事务

1.4表结构：表由表名、字段(名称+类型+约束)、记录 组成。与 java 相对应：

2.select语句

结构：select distinct * 字段|表达式 as 别名 from 表 表别名

• SELECT * FROM 表名; à查询某个表中所有的记录的所有字段信息

• SELECT 列名 FROM 表名; à 查询某个表中所有的记录的指定字段信息

• SELECT 列名1,列名2 FROM 表名; à 查询某个表中所有的记录的字段1 字段2

• SELECT distinct 列名 FROM 表名; à去除重复记录

• SELECT 表达式 FROM 表名; à查询表达式

• SELECT xxx as 别名 FROM 表名 表别名 à使用别名

3.查询行（记录）

结构：…where 行过滤条件

条件：

a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and

b)、and 、or、 not、 union、 union all、 intersect 、minus

c)、null :is null、 is not null、 not is null

d)、like :模糊查询 % _ escape(‘单个字符’)

f)、in 、 exists(难点) 及子查询m

4.in，exists与排序

4.1 in相当于使用or的多个等值，定值集合 ,如果存在 子查询，确保 类型相同、字 段数为1，如果记录多，效率不高，用于 一些 少量定值判断上

4.2 exists条件为true,存在记录则返回结果，后续不再继续 比较查询，与查询的字 段无关，与记录有关

4.3 使用 ORDER BY 排序，排序不是真实改变存储结构的顺序，而是获取的集合的 顺序。

• 顺序 :asc（默认） desc

• 多字段: 在前面字段相等时，使用后面的字段排序

• 空排序: 降序为 desc，注意 null 为最后

5.组函数：sum() count() max() min() avg()

注意：计算组函数,null值不参与运算，组函数不能与非分组字段一起使用,组函数只能和组函数一起使用,不能与其他普通字段一起使用 group by，组函数不能使用在where后面

6.分组：group by

结构：select 查询数据 from 数据来源 where 行过滤条件 group by 分组字段1,分组字段2… having 组过滤条件 order by 排序字段…;
执行流程: from – where – group by – having – select – order by

7.表连接：92语法， 99语法

7.1 92语法：select 数据 from 数据来源1,数据来源2; 对乘 笛卡尔积
select 数据 from 数据来源1,数据来源2 where 表连接条件|行过滤条件;

外链接
当你想要表连接中某一张表中所有的数据全部展示,无论是否满足满足连接条件都显示,可以把这张表设置为主表
在外连接中主表的表中所有数据都能展示
如何设置外连接中的主表,在连接条件位置 主表对象加(+)
左外连接 : 主表在逗号左边就是左连接
右外连接 : 主表在逗号右边就是右连接

7.2：99语法：join

cross join 笛卡尔积 对乘
等值连接与非等值连接

1. --自然连接 natural join 自动根据同名字段|主外键关系
2. select ename,sal,deptno from emp natural join dept; --同名字段不能指明出处
4. jion using(等值连接字段名) 当存在多个同名字段,可以指明使用哪一个做等值连接
5. select ename,sal,deptno from emp join dept using(deptno);
7. 数据来源1 join 数据来源2 on 连接条件 ; 即可以实现等值连接 可以实现非等值连接
8. select * from emp e join dept d on e.deptno = d.deptno;
11. --非等值连接
12. --查询员工信息以及每一个员工的薪资等级
13. select * from emp e join salgrade s on e.sal between s.losal and s.hisal;

复制代码

外链接
想要某张表中不满足连接条件的数据都显示,把这张表定义为主表
左外 left join
右外 right join

8.rowid 和 rownum

8.1 rowid 相当于对象的地址,区分表中的数据,是记录的唯一,在数据插入表中时就存在

作用: 可以对多数相同数据做去重(没有主键的表)

8.2 rownum 伪列 结果集的序号 只要有select就有结果集就有rownum,每一套结果集都有自己的rownum
规律: 把以确定结果集中的数据从第一个开始 设置rownum,从1开始,依次+1
优点: 有规律,规律可循,是数字,可以进行判断

作用：实现分页查询

9.索引

• 是数据库的对象之一
• 字典的目录
• 只有在大量数据查询的时候效率较高
• 大量数据 查询,如果增删改效率降低,因为对象要维护
• 索引的创建与删除完全不影响字段的使用
  • 唯一字段适合设置索引
  • oracle自动为主键设置索引

1. -创建索引
2. --create index 索引名 on表名 (字段列表...)
3. create index index_sal on emp(sal);
4. --删除
5. --drop index 索引名
6. drop index index_sal;

复制代码

10.表设计

10.1 设计表要注意的问题：
表 表名 字段 约束 表与表之间的关系
三范式
表与表之间的关系: 一对一 一对多|多对一(主外键) 多对多{中间表}

创建表与约束问题
1)创建表的同时不创建约束, 结束后追加约束
2)创建表的同时为字段添加约束
3)创建表的结构结束之间添加约束

约束的添加: 1)物理约束 :表中字段上添加 2)逻辑约束:java代码上使用逻辑判断

主键 外键 非空 唯一 检查

10.2 创建表：

1. create table 表名(
2. 字段 字段类型,
3. 字段 字段类型,
4. ....
5. )

复制代码

10.3删除表

1. drop table 表名;

复制代码

10.4.添加数据

1. insert into 表名t values(字段1，字段2，字段3.....);

复制代码

10.5 添加约束

• 字段后直接添加约束,没有约束名 不便于后期维护,但是编写简单

1. create table sxt_student(
2. --学生编号 主键约束
3. sid number(5) primary key,
4. --学生姓名 非空约束
5. sname varchar2(15) not null unique,
6. --年龄 检查约束0~150
7. sage number(3) check(sage between 0 and 150),
8. --性别 检查约束 '男' '女'
9. sgender char(1 char) check(sgender in('男','女')),
10. --入学日期 默认值 sysdate
11. hiredate date default(sysdate),
12. cid number(5)
13. )

复制代码

• 字段后直接添加约束包括约束名

1. create table sxt_student(
2. --学生编号 主键约束
3. sid number(5) constraints pk_sxt_student_sid primary key,
4. --学生姓名 非空约束
5. sname varchar2(15) constraints sxt_student_sname_notnull not null,
6. --年龄 检查约束0~150
7. sage number(3) check(sage between 0 and 150),
8. --性别 检查约束 '男' '女'
9. sgender char(1 char),
10. --入学日期 默认值 sysdate
11. hiredate date default(sysdate),
12. cid number(5),
14. --创建表结构结束前 添加约束
15. constraint ck_user_pwd check(length(userpwd) between 4 and 18),
16. -- constraints pk_sgender check(sgender in('男','女')),
17. constraints sxt_student_sname_unique unique(sname)
18. )

复制代码

10.6 添加注释

comment on table 表名 is ‘注释字段’;

11.数据删除

如果两张表存在主外键约束:
删除表:
默认先删除从表 再删除主表
cascade constraints 删除主表的同时级联删除约束
删除数据:
删除从表数据: 直接删除
删除主表数据:
从表中是否有数据引用了当前的主表数据
没有引用可以直接删除
有引用,想要删除需要一下三种方式处理:
默认 先删除从表引用的数据,再删除被引用的主表数据
添加外键约束时,on delete set null 删除主表数据的同时,从表引用字段设置为null
添加外键约束时,on delete cascade 删除主表数据的同时,级联删除从表引用了的数据

12.jdbc的流程

12.1JDBC基本流程:

• 1.加载驱动 (选择哪一个数据库)
• 2.获取连接 (与数据库连接上)
• 3.准备sql
• 4.获取处理块(打包发送)
• 5.接收结果集
• 6.处理结果数据
• 7.关闭

代码例子：

1. package com.shsxt.jdbc;
3. import java.sql.Connection;
4. import java.sql.DriverManager;
5. import java.sql.ResultSet;
6. import java.sql.SQLException;
7. import java.sql.Statement;
9. /*
10. * JDBC基本流程:
11. * 1.加载驱动 (选择哪一个数据库)
12. * 2.获取连接 (与数据库连接上)
13. * 3.准备sql
14. * 4.获取处理块(打包发送)
15. * 5.接收结果集
16. * 6.处理结果数据
17. * 7.关闭
18. */
19. public class JDBCDemo01 {
20. public static void main(String[] args) throws Exception {
21. //1.加载驱动 (选择哪一个数据库)
22. Class.forName("oracle.jdbc.OracleDriver");
23. //2.获取连接 (与数据库连接上)
24. Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "SCOTT", "TIGER");
25. //3.准备sql
26. String sql = "select deptno,dname,loc from dept";
27. //4.获取处理块(打包发送)
28. Statement state = conn.createStatement();
29. //5.接收结果
30. ResultSet result = state.executeQuery(sql);
31. //6.处理结果数据
32. while(result.next()){
33. //注意:字段索引从1开始
34. int num = result.getInt(1);
35. String dname = result.getString(2);
36. String loc = result.getString(3);
37. System.out.println(num+"--->"+dname+"--->"+loc);
38. }
39. //7.关闭
40. result.close();
41. state.close();
42. conn.close();
43. }
44. }

复制代码

12.2 jdbc的流程优化与封装

优化： 1.异常捕获

​ 2.数据库的配置信息使用配置文件动态获取

封装：将配置文件的加载与驱动的加载以及连接的建立分装成方法

13.Mybatis框架

13.1 特点：

属于持久层的ORM框架

半自动化

13.2 Mybatis的环境搭架

• 创建项目

• 导入jar包：Mybatis的核心jar包 ，Mybatis依赖的jar包 ，数据库驱动包

• Mybatis核心配置文件：是一个xml文件,命名无要求,位置无要求,一般成为mybatis.xml,放在src路径下

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE configuration
  3. PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  4. "http://mybatis.org/dtd/mybatis-3-config.dtd">
  6. <!-- Mybatis全局配置文件 根元素 -->
  7. <configuration>
  8. <!-- 用户指定使用哪一个开发环境
  9. default : 用户指定使用的开发环境的id
  10. -->
  11. <environments default="dev">
  12. <!-- id : 环境的标识 定义唯一 -->
  13. <environment id="dev">
  14. <!--
  15. 事务管理器:
  16. type : 设置mybatis采用什么方式管理事务
  17. JDBC : 标识采用jdbc的事务管理方式
  18. -->
  19. <transactionManager type="JDBC"/>
  20. <!--
  21. 用户配置数据库连接池和数据库连接参数
  22. type : 用户设置mybatis是否采用连接池技术
  23. POOLED :表示mybatis采用连接池技术
  24. -->
  25. <dataSource type="POOLED">
  26. <!-- 设置连接数据库参数 -->
  27. <property name="driver" value="oracle.jdbc.OracleDriver"/>
  28. <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
  29. <property name="username" value="SCOTT"/>
  30. <property name="password" value="TIGER"/>
  31. </dataSource>
  32. </environment>
  33. </environments>
  35. <!--
  36. sql映射文件加载设置
  37. -->
  38. <mappers>
  39. <!-- 包路径 : 报名+文件名 -->
  40. <mapper resource="com/shsxt/mappers/DeptMapper.xml"/>
  41. </mappers>
  42. </configuration>

  复制代码

• 添加Dept|User类

  1. package com.shsxt.pojo;
  3. import java.io.Serializable;
  5. public class Dept implements Serializable{
  6. private int deptno;
  7. private String dname;
  8. private String loc;
  10. public Dept() {
  11. // TODO Auto-generated constructor stub
  12. }
  14. public Dept(int deptno, String dname, String loc) {
  15. super();
  16. this.deptno = deptno;
  17. this.dname = dname;
  18. this.loc = loc;
  19. }
  22. @Override
  23. public int hashCode() {
  24. final int prime = 31;
  25. int result = 1;
  26. result = prime * result + deptno;
  27. result = prime * result + ((dname == null) ? 0 : dname.hashCode());
  28. result = prime * result + ((loc == null) ? 0 : loc.hashCode());
  29. return result;
  30. }
  32. @Override
  33. public boolean equals(Object obj) {
  34. if (this == obj)
  35. return true;
  36. if (obj == null)
  37. return false;
  38. if (getClass() != obj.getClass())
  39. return false;
  40. Dept other = (Dept) obj;
  41. if (deptno != other.deptno)
  42. return false;
  43. if (dname == null) {
  44. if (other.dname != null)
  45. return false;
  46. } else if (!dname.equals(other.dname))
  47. return false;
  48. if (loc == null) {
  49. if (other.loc != null)
  50. return false;
  51. } else if (!loc.equals(other.loc))
  52. return false;
  53. return true;
  54. }
  56. @Override
  57. public String toString() {
  58. return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
  59. }
  60. }

  复制代码

• Mybatis SQL映射文件：在Mybatis中,推荐使用mappers作为包名,我们只需要写一个映射配置文件就可以,DeptMapper.xml,用于定义要执行的sql语句,同时可以设置参数|返回值结果类型

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper
  3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4. "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <!--
  6. namespace 命名空间
  7. 要求: 全部唯一,在整个应用中不能出其他的映射文件的命名空间相同情况,保证唯一
  8. 设置值方式:
  9. 1)随意定义,只要不重复就行,但是不便于后期维护
  10. 2)推荐使用当前的报名+文件名形式(不要文件名后缀)->com.shsxt.mappers.DeptMapper
  11. -->
  12. <mapper namespace="com.shsxt.mappers.DeptMapper">
  13. <!--
  14. 查询标签:
  15. id: 当前Statement的唯一标识,当前文件中不能重复
  16. resultType: 入参类型(结束的数据类型)
  18. -->
  19. <select id="queryAll" resultType="com.shsxt.pojo.Dept"> <!-- 权限定名 -->
  20. select * from Dept
  22. </select>
  24. </mapper>

  复制代码

• 测试代码

  1. package com.shsxt.test;
  3. import java.io.IOException;
  4. import java.io.InputStream;
  5. import java.util.List;
  7. import org.apache.ibatis.io.Resources;
  8. import org.apache.ibatis.session.SqlSession;
  9. import org.apache.ibatis.session.SqlSessionFactory;
  10. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  12. import com.shsxt.pojo.Dept;
  14. public class DeptTest {
  15. public static void main(String[] args) throws IOException {
  16. //1.加载Mybatis全局配制文件
  17. InputStream is=Resources.getResourceAsStream("mybatis.xml");
  18. //2.构建SqlSessionFactory对象
  19. SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
  20. //3.根据工厂构建SqlSession会话对象
  21. SqlSession session=factory.openSession();
  22. //4.通过session中的方法执行查询
  23. //参数: 命名空间+id
  24. List<Dept> list=session.selectList("com.shsxt.mappers.DeptMapper.queryAll");
  25. list.forEach(System.out::println);
  26. //5.关闭会话
  27. session.close();
  28. }
  29. }

  复制代码