黑马程序员技术交流社区

标题: 【石家庄校区】MySQL、多表事务、JDBC课堂笔记 [打印本页]

作者: 学习让我快乐啊 时间: 2018-5-9 17:12
标题: 【石家庄校区】MySQL、多表事务、JDBC课堂笔记
本帖最后由学习让我快乐啊于 2018-5-9 17:16 编辑

MySQL

概述

数据库就是一个文件系统,通过标准的SQL语句获取数据，是一种关系型数据库,关系型数据库存放的是实体之间的关系
常见的关系型数据库:
- MySQL
- Oracle
- SQLServer
- DB2
- SyBase

SQL

结构化查询语言

SQL分类

DDL:数据定义语言
- create,drop,alter...
DCL:数据控制语言
- grant,if...
DML:数据操纵语言
- insert,update,delete...
DQL:数据查询语言
- select

对数据库操作

创建数据库
- create database 数据库名称 [character set 字符集 collate 校对规则];
查看数据库
- 查看所有数据库:show databases;
- 查看某个数据库的定义信息:show create database 数据库名称;
修改数据库
- alter database 数据库名称 character set 字符集 collate 校对规则;
删除数据库
- drop database 数据库名称;
其他操作
- 切换数据库:use 数据库名称;
- 查看正在使用的数据库:select database();

对表进行操作

数据类型对比
- 注:
  - char和varchar的区别：char 代表是固定长度的字符或字符串。varchar代表的是可变长度的字符串。
  - datetime和timestamp区别:datetime就是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用null存入到数据库中;timestamp也是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用当前的系统时间存入到数据库中。

Java中的类型	SQL中的类型
byte/short/int/long	tinyint/smallint/int/bigint
float	float
double	double
boolean	boolean
char/String	char/varchar
Date	date/time/datetime/timestamp
File（文件类型）	Blob（二进制的文件）/text(文档)

单表约束
- 主键约束:primary key,默认是唯一且非空的
- 唯一约束:unique
- 非空约束:not null
创建表
- create table 表名称(字段名称字段类型(长度) 约束,字段名称字段类型(长度) 约束…);
查看表
- 查看数据库下的所有表:show tables;
- 查看某个表的结构信息:desc 表名;
删除表
- drop table 表名;
修改表
- 添加列:alter table 表名 add 列名类型(长度) 约束;
- 修改列类型,长度和约束:alter table 表名 modify 列名类型(长度) 约束;
- 删除列:alter table 表名 drop 列名;
- 修改列名称:alter table 表名 change 旧列名新列名类型(长度) 约束;
- 修改表名:rename table 表名 to 新的表名;
- 修改表的字符集:alter table 表名 character set 字符集;

对记录进行操作

添加表的记录
- 向表中插入某些列:insert into 表名(列名1,列名2,列名3...) values(值1,值2,值3...)
- 向表中插入所有列:insert into 表名 values(值1,值2,值3...)
- 注意:
  - 值的类型与数据库中表列的类型一致
  - 值的顺序与数据库中表列的顺序一致
  - 值的最大长度不能超过列设置最大长度
  - 值的类型是字符串或者是日期类型，使用单引号引起来
修改表的记录
- update 表名 set 列名=值,列名=值 [where 条件];
- 注意:
  - 值的类型与列的类型一致。
  - 值的最大长度不能超过列设置的最大长度。
  - 字符串类型和日期类型添加单引号。
  - 修改某一列的所有值（一般不会用）
    update user set password = 'abc';
删除表的记录
- delete from 表名 [where 条件];
- 注意:
  - 删除表的记录，指的是删除表中的一行记录。
  - 删除如果没有条件，默认是删除表中的所有记录。
- 删除表中的记录有2种做法:
  - delete from 表名;
    - 删除所有记录,属于DML语句
  - truncate table 表名;
    - 删除所有记录,属于DDL语句
查看表的记录
- select [distinct] *|列名 from 表名 [条件];
  - 别名查询:select [distinct] *|列名 [as] 别名 from 表名 [条件];
  - 条件查询:使用where子句
    - > , < , >= , <= , <> ,=
    - like:模糊查询:使用_或者%作为占位符。_只能代表一个字符，而%可以代表任意个字符。
    - in:范围查询
    - 条件关联:and , or ,not
  - 排序查询:使用order by 字段名称 asc/desc;
  - 分组统计查询
    - 聚合函数:
      [td]
      求和
      最大值
      最小值
      计数
      平均值
      sum()
      max()
      min()
      count()
      avg()
    - 分组查询: 使用group by 字段名称;
    - 注: where的子句后面不能跟着聚合函数。如果现在使用带有聚合函数的条件过滤（分组后条件过滤）需要使用一个关键字having
总结:
- S(select)… F(from)…W(where)…G(group by)…H(having)…O(order by);

多表

约束的作用

约束是用来保证数据的完整性

单表约束

主键约束

唯一约束

非空约束

多表约束

外键约束：用来保证数据完整性（多表之间）。

添加外键约束在员工表上添加外键

alter table employee add foreign key (dno)references dept(did);

设置外键为非空

alter table employee modify dno int not null;

多表设计之表关系的介绍一对多的关系

一对多的例子：

一个部门下可以有多个员工，一个员工只能属于某一个部门。

在多的一方创建外键指向一的一方的主键

多对多的关系

多对多的例子：

一个学生可以选择多门课程，一门课程可以被多个学生选择。

需要创建中间表，中间表中至少两个字段，分别作为外键指向多对多双方的主键

一对一的关系

一对一的例子：

一个公司可以有一个注册地址，一个注册地址只能对一个公司。

唯一外键对应

假设是一对多，在多的一方创建外键指向一的一方的主键，将外键设置为unique

(不推荐使用)主键对应

将两个表的主键建立对应关系即可

多表查询

连接查询
- 交叉连接 cross join(任意两表之间均可查询)
  - select * from 表1 cross join 表2;
  - select * from 表1,表2;
- 内连接 inner join(inner可省略)(存在外键约束的两表之间可以查询)
  - 根据关联条件去匹配两个表中的记录,如果匹配成功则返回记录,否则不返回
  - 显式内连接:select * from表1 inner join 表2 on 关联条件;
  - 隐式内连接(较常用):select * from 表1,表2 where 关联条件;
- 外连接 outer join(outer 可省略)(存在外键约束的两表之间可以查询)
  - 左外连接: select * from 表1 left outer join 表2 on 关联条件;
    - 以左表为基准,根据关联条件去右表查询,如果匹配成功则返回记录,否则返回null
  - 右外连接:select * from 表1 right outer join 表2 on 关联条件;
    - 以右表为基准,根据关联条件去左表查询,如果匹配成功则返回记录,否则返回null
- 内连接与外连接的区别
  - 内连接查询出的是两个表的交集
  - 外连接查询的是左表或右表的全部加上交集
子查询
- 带in的子查询
  例：select * from classes where cid in (SELECT cno FROM student WHERE birthday > '1991-01-01');
- 带exists的子查询
  例:select * from classes where exists (SELECT cno FROM student WHERE birthday > '1991-01-01');
- 带any的子查询
  例:SELECT * FROM classes WHERE cid > ANY (SELECT cno FROM student )
- 带all的子查询
  例:SELECT * FROM classes WHERE cid > ALL (SELECT cno FROM student)

事务

概述

指的是逻辑上的一组操作,组成这组操作的各个逻辑单元,要么全都成功,要么全都失败
基本操作
开启事务:start transaction;
提交事务:commit;
回滚事务:rollback;

事务的特性(ACID特性)

原子性：事务的不可分割，组成事务的各个逻辑单元不可分割
一致性：事务执行的前后，数据完整性保持一致
隔离性：事务执行不应该受到其他事务的干扰
持久性：事务一旦结束，数据就持久化到数据库中

事务的隔离

隔离性：一个事务的执行,不应该受到其他事务的干扰;如果不考虑隔离性（一个事务执行受到其他的事务的干扰）,引发一些安全问题,主要体现在读取数据上：
- 脏读:一个事.务读到了另一个事务未提交的数据，导致查询结果不一致
- 不可重复读:一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致
- 虚读/幻读:一个事务读到了另一个事务已经提交的insert的数据,导致多次查询结果不一致
事务的隔离级别
- read uncommitted:脏读,不可重复读,虚读都有可能发生
- read conmmitted:避免脏读,但是不可重复读和虚读是有可能发生
- repeatable read:避免脏读和不可重复读,但是虚读有可能发生
- serializable:避免脏读,不可重复读,虚读
- 查看隔离级别:select @@_tx isolation
- 设置隔离级别:SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;
  安全级别越高执行效率就越低
- JDBC
- JDBC概述：
  Java Data Base Connectivity是一种用于执行SQL语句的Java API
  使用JDBC需要引入jar包
  一个应用:
  高性能,高可用
  高可用:
  代码的复用性
  对扩展是开放的,对修改源码是关闭的
  模糊查询时,最好将占位符放到变量中
  preparedstatement批处理只预编译一次
  - 驱动:两个设备(应用)之间通信的桥梁
  相关API
  - DiverManager
    - 注册驱动
      
      static void registerDriver(Diver driver):一般不使用
      Class.forName("驱动全名"):一般使用这种方式(右键-Copy Qualified Name可以复制类全名)
    - 获得连接
      
      static Connection getConnection(String url, String user, String password)
      url格式:数据库协议://IP地址:端口号/数据库(数据库可以不写);
      例子:jdbc:mysql://localhost:3306/web_test3
  - Connection
    - 创建执行SQL语句的对象
      
      Statement createStatement():创建Statement对象,执行SQL语句(存在SQL注入漏洞)
      CallaleStatement prepareCall(String sql):创建CallableStatement对象调用数据库存储过程
      PreparedStatement prepareStatement(String sql):创建PreparedStatement对象将参数化的SQL语句发送到数据库(解决了SQL注入漏洞)
    - 管理事务
      
      void setAutoCommit(boolean autoCommit):将此链接的自动提交模式设置为给定状态
      void commit:提交,并释放此Connection对象当前持有的所有数据库锁
      void rollback():回滚,并释放此Connection对象当前持有的所有数据库锁
  - Statement
    - 执行SQL语句
      
      boolean execute(String sql)
      esultSet executeQurey(String sql)
      int excuteUpdate()
    - 批处理
      
      void addBatch(String sql)
      void excuteBatch()
      void clearBatch()
  - ResultSet
    - boolean next()用于遍历结果集
      while(rs.next()){
      rs.getXXX(column);
      }
    - XXX getXXX(String column):获取相应记录的值
    - Object getObject(String column)
  JDBC开发步骤
  step1:注册驱动：Class.forName()
  step2:获得连接：Connection conn = DriverManger.getConnection(url,username,password)
  step3:编写SQL语句，String sql =""，值的地方用占位符?代替
  step4:预编译sql并且获得可执行sql语句的对象PreparedStatement ps = conn.prepareStatemet(String sql)
  step5:为sql语句设置值，用值代替sql语句中的? ps.setXXX(int n,值) n：代表第几个问号的意思，从1开始
  step6:执行sql语句
  如果是查询：ResutSet rs = ps.executeQuery();//获得结果集
  如果是增删改：int num = ps.executeUpdate() ;// num 代表影响的行数
  step7:释放资源
- CRUD操作
- 批处理
  String sql = "insert into user values (null,?)";
  // 预编译SQL:
  pstmt = conn.prepareStatement(sql);
  for(int i=1;i<=10000;i++){
  pstmt.setString(1, "name"+i);
  // 添加到批处理
  pstmt.addBatch();//注意内存溢出
  if(i % 1000 == 0){
        // 执行批处理:
        pstmt.executeBatch();
        // 清空批处理:
        pstmt.clearBatch();
  }
  }

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2

求和	最大值	最小值	计数	平均值
sum()	max()	min()	count()	avg()