华清远见-重庆中心-数据库阶段技术总结

查看信息

• 查看所有数据库：show database;
• 查看所有表：show tables;
• 查看表结构：desc 表名;
• 查看建表语言：show create table 表名;

数据库操作

• 创建数据库：create database 数据库名
• 使用数据库：use 数据库名;
• 删除数据库：drop database 数据库名

数据表操作

数据类型

整型
tinyint	短整型	对应java中的byte和short
int	整型	对应java中的int
bigint	长整型	对应java中的long

浮点型
float	单精度浮点型	对应java中的float
double	双精度浮点型	对应java中的double
decimal(宽度,精度)	指定保留的小数位数和整体宽度	如decimal(4,2) 3.1415926–>3.14

字符串
char(长度)	定长字符串	char(10)表示占10个字符，就算保存了3个字符，也占10个字符，对应java中的String
varchar(长度)	可变字符串	varchar(10)表示最多占10个字符，保存了3个字符，旧占3个字符，对应java中的String
text	文本

日期
date	日期	yyyy-MM-dd
time	时间	HH:mm:ss
datetime	日期时间	yyyy-MM-dd HH:mm:ss
timestamp(14或8)	毫秒	保存日期的毫秒数.14表示yyyyMMddHHmmss，8表示yyyyMMdd

数据完整性

数据完整性值数据精准可靠

完整性约束	关键字	作用
非空约束	not null	保证字段值不为空
主键约束	primary key	保证字段值不重复们勇于唯一区分每条记录
唯一约束	unique	保证字段值不重复
默认值	default	保证字段没有填充数据时，自动使用默认填充
外键约束	foreign key references	保证表中的记录只能来自于主表

• 创建数据表

create table 表名(
	字段名 数据类型 [字段特征],
    字段名 数据类型 [字段特征],
    ...
    字段名 数据类型 [字段特征]
)

• 修改数据表

• 表重命名：alter table 表名 rename to 新表名

• 添加字段：alter table 表名 add columm 字段名 数据类型 字段特征

• 修改字段：alter table 表名 change 旧字段 数据类型 字段特种证

• 删除字段：alter table 表名 drop 字段名

• 删除数据表

  删除时如果有外键约束、先删除从表、再删除主表

• 添加约束

  • 添加主键约束：alter table 表名 add primary key(字段名
  • 添加唯一约束：alter table 表名 add unique(字段名
  • 添加默认约束：alter table 表名 alter 字段名 set default ‘值’
  • 添加外键约束：alter table 从表 add foreign key(字段名) referneces 主表(主键字段)

• 删除约束：alter table 表名 drop [primary key|index/foreign key 约束名]

数据增加

• 增加
  • insert into 表名 values(值1，值2…)，保证表中字段顺序添加数据，不能缺少任何一个字段
    • 自增字段用null
    • 默认字段用default
    • 允许为空字段用null
  • insert into 表名(字段1，字段2…) values(值1，值2…);保证必须写上非空字段，值的顺序和字段顺序保持一致
• 修改
  • update 表名 set 字段=值,字段=值[where 条件]
• 删除
  • delete from 表名 [where 条件];会保留自增列的值
  • truncate table 表名;会终止自增列的值
• 查询
  • selecr [字段|*] from 表名 [where 条件] [order by 字段][having 条件]
  • limit N:查询前N条记录
  • limit N,M:从所有为N的开始查询M条记录
  • distinct:去重复
  • order by 字段名:根据字段分组
  • having：统计函数条件

常用函数

• 统计函数
  • sum():求和
  • avg():平均
  • count():统计数量
  • max()；最大值
  • min():最小值
• 字符串函数
  • concat(‘值’，‘值’，‘字段’)：拼接值或字段
  • trim():去除首位空格
  • left(legth)/rigth(length):从左/右开始截取指定长度字段串
• 数学函数
  • round()/ceil()/floor()：取整
  • abs():绝对值
• 时间函数
  • now()/curdate()curtime():得到当前日期
  • year()/month()/day():得到日期中的指定部分
  • datediff()/timediff()/timesampdiff():计算时间间隔
• 补充
  • if(条件，表达式1，表达式2)：条件为真，结果为表达式1，条件为假，结果为表达式2
  • group_concat():凭借分组后的其他字段

多表查询

• 交叉连接/笛卡尔积

select * from 表1，表2...;
select * from 表1 cross/inner join 表2;

• 内连接

select * from 表1，表2 where 表1.字段=表2.字段;
select * from 表1 cross /inner join 表2 on 表1.字段=表2.字段;

• 外连接
  • 左外连接

  select * from 表1 left join 表2 on 表1.字段=表2.字段; -- 保证表1中的数据完整
  
  

  • 右外连接

  select * from 表1 right join 表2 on 表1.字段=表2.字段;--保证表2中的数据完整 
  

数据库设计

实体关系模型

ER图

• 矩形：实体
• 椭圆形：实体的属性
• 菱形：实体之间的关系

实体关系分类

• 一对一：一个国家有一个领导人
  • 1.根据连个实体创建两张表，都加上主键
  • 2.在其中的一张表中添加一个字段，保存另一张表的主键并设置唯一
• 一对多/多对一：一个班级有多个学生，一个学生不能有多个班级
  • 1.根据两个实体创建的两张表，都加上主键
  • 2.在从表中添加外键字段管理主表中的主键字段
• 多对多
  • 1.根据两个实体创建两张表，都加上主键
  • 2.创建第三张‘关键表’，在该表中添加连个字段分别保存两个实体表中的主键

范式

第一范式1NF

字段不可再分

第二范式2Nf

在满足1NF的基础上，消除部分依赖。

对于联合主键而言，所有主键属性字段必须完全依赖主属性

第三范式3NF

在满足2NF的基础上，消除传递依赖

JDBC

java提供了一套规范用于连接各种数据库，不同的数据库厂商根据该贵干设计连接驱动

java.sql包下一组接口用于操作数据库

• Connection接口 用于获取连接对象
• preparedStatement接口 用于发送sql语句，处理sql语句
• ResultSet接口 用于查询后的结果集

加载mysql驱动

Class.forname("com.mysql.cj.jdbc.Driver")

连接MySQL数据库字符串

String url="jdc:mysql://localhost:3306/数据库名?serverTimezone=Asia/Shanghai"

查询的步骤

1. 获取连接对象Connection
2. 构造sql语句String
3. 预处理sql语句pst=conn.preparestatement
4. 给sql语句中的?赋值pst.setXXX(?顺序,值)
5. 调用executeQuery()得到Result结果集
6. 遍历结果集rs.next()后rs.getXXX(字段顺序/字段名)

增删改的步骤

public void addHero(Hero hero) throws SQLException, ClassNotFoundException {
    //1.加载驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    //2.获取连接对象
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/gamedb?serverTimezone=Asia/Shanghai", "root", "root");
    //3.构造sql,参数使用?占位
    String sql = "insert into hero values(null,?,?,?,?,curdate())";
    //4.预处理sql
    pst = conn.prepareStatement(sql);
    //给?赋值
    pst.setString(1,hero.getName());
    pst.setString(2,hero.getPosition());
    pst.setString(3,hero.getSex());
    pst.setInt(4,hero.getPrice());
    //5.调用更新的方法
    int i = pst.executeUpdate();
    //6.判断执行结果
    if(i>0){
        System.out.println("添加成功");
    }else{
        System.out.println("添加失败");
    }
    //7.释放资源
    pst.close();
    conn.close();
}

事务transaction

一组sql执行单元，要么全部执行，要么全部不执行

事务的特性ACID

原子性atomicity

事务是最小的执行单元

一致性Consistency

各个事务之间互不干涉

隔离性Isolation

各个事务之间互不干涉

持久性durability

事务一段提交，改变是永久的

事务并发出现的问题

脏读

事务A读取到了事务B未提交的数据

不可重复读

事务A中前后量词读取到的数据不一致

事务A在读取的过程汇总，事务B对数据进行修改

幻读

事务A在读取过程中，事务B向其中添加数据，导致事务A读取到了事务B中添加的“幻影”数据

隔离级别	能否出现脏读	能否出现不可重复读	能否出现幻读
Read Uncommittedd未提交读	会	会	会
Read Committed已提交读	不会	会	会
Repeatable Read可重复读(MySQL默认)	不会	不会	会
serializable序列化	不会	不会	不会

事务相关指令

• 查看事务自动提交状态

  select @@autocommit

• 修改事务自动提交状态

  set @@autocommit=0/1 0 表示不开启动，1表示开启

• 手动开启事务

  start transaction

• 提交

  commit

• 回滚

  rollback

• 查询当前事务隔离级别

  select @@transaction_isolation

• 设置事务隔离界别

  set [session|global] transaction islation level[read Uncommitted | read commitedd | Repeatable Read | Serializable]

存储引擎

• MySQL5.5版本之前，默认使用MyIsam存储引擎，不支持事务
• MySQL5.5版本之后，默认使用InnoDB存储引擎，支持事务