重庆华清远见中心-数据库学习

数据库 DataBase

简称为 DB ，是结构化管理数据的仓库，称为电子化的文件柜。

数据库管理系统 DataBase Manager

System

简称 DMBS ，安装在操作系统上，管理数据库的软件。

数据库的分类

关系型数据库

SQLServer

MySQL

Oracle

非关系型数据库 nosql

Redis

SQL 语句

在关系型数据库中，用于操作数据库的结构化查询语言。

查看信息

查看所有数据库： show databases;

查看所有表： show tables;

查看表结构： desc 表名 ;

查看建表语言： show create table 表名 ;

数据库操作

创建数据库： create database 数据库名：

使用数据库： use 数据库名：

删除数据库： drop database 数据库名 ;

 

数据表操作

数据类型

数据完整性

数据完整性指数据精确可靠。

创建数据表

修改数据表

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

添加新字段： alter table 表名 add column 字段名 数据类型 字段特征 ;

修改字段： alter table 表名 change 旧字段 新字段 数据类型 字段特征 ;

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

删除数据表： drop table 表名

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

添加约束

添加主键约束： alter table 表名 add primary key( 字段名 )

添加唯一约束： alter table 表名 add unique( 字段名 )

create table 表名 (

字段名 数据类型 [ 是否为空 | 唯一约束 | 主键约束 | 默认值约束 | 自动递增 ],

字段名 数据类型 [ 是否为空 | 唯一约束 | 默认值约束 ],

...

字段名 数据类型 [ 是否为空 | 唯一约束 | 默认值约束 ]

) 条件

符号

指定值

= 、 != 、 <>

指定范围

> 、 < 、 >= 、 <= 、 between 值 and 值

指定集合

[not] in ( 元素 1, 元素 2...)

模糊查询 (% 表示未知长度字符串， _ 表示一个字符 )

like '% 值 _'

空置

is [not] null

多条件

使用 and 、 or 、 & 、 | 串联条件

添加默认值约束： alter table 表名 alter 字段名 set default ' 值 '

添加外键约束： alter table 从表 add foreign key( 字段名 ) references 主表 ( 主键字段 )

删除约束： 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 表名 ; 会重置自增列的值

查询

 

常用函数

统计函数

sum() ：求和

avg() ：平均

count() ：统计数量

max() ：最大值

min() ：最小值

字符串函数 concat(' 值 ',' 值 ', 字段 ,) ：拼接值或字段

trim() ：去除首尾空格

left(length)/right(length) ：从左 / 右开始截取指定长度字符串

数学函数

round()/ceil()/floor() ：取整

abs() ：绝对值

时间函数

now()/curdate()curtime() ：得到当前日期时间

year()/month()/day() ：得到日期中的指定部分

datediff()/timediff()/timestampdiff() ：计算时间间隔

补充

if( 条件 , 表达式 1, 表达式 2) ：条件为真，结果为表达式 1 ，条件为假，结果为表达式 2

group_concat() ：拼接分组后的其他字段

嵌套查询

多表查询

交叉连接 / 笛卡尔积

内连接

外连接

左外连接

右外连接

数据库设计

实体关系模型

select * from 表 where 字段=(select * from 表 where 条件);
select * from 表,(select * from 表)t where 表.字段=t.字段;
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="jdbc:mysql://localhost:3306/数据库名?serverTimezone=Asia/Shanghai";
String username="root";
String password="root";

查询的步骤

1. 获取连接对象 Connection

2. 构造 sql 语句 String

3. 预处理 sql 语句 pst=conn.prepareStatement(sql)

4. 给 sql 语句中的 ? 赋值 pst.setXXX(? 顺序 , 值 )

5. 调用 executeQuery() 得到 ResultSet 结果集

6. 遍历结果集 rs.next() 后 rs.getXXX( 字段顺序 / 字段名 )

7. 释放资源

增删改的步骤

1. 获取连接对象 Connection

2. 构造 sql 语句 String

3. 预处理 sql 语句 pst=conn.prepareStatement(sql)

4. 给 sql 语句中的 ? 赋值 pst.setXXX(? 顺序 , 值 )

5. 调用 executeUpdate() 得到受影响的行数

6. 释放资源

事务 transaction

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

事务的特性 ACID

原子性 Atomicity

事务是最小的执行单元

一致性 Consistency

事务执行前后，数据整体保持一致。

隔离性 Isolation

各个事务之间应当互不干涉

持久性 Durability

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

事务并发出现的问题

脏读

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

不可重复读

事务 A 中前后两次读取到的数据不一致。

事务 A 在读取过程中，事务 B 对数据进行了修改。

幻读

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

以上问题可以通过设置事务隔离级别解决。

事务隔离级别

 

事务相关指令

查看事务自动提交状态

select @@autocommit

修改事务自动提交状态

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

手动开启事务

start transaction;

提交

commit;

回滚 rollback;

查询当前事务隔离级别

select @@transaction_isolation

设置事务隔离级别

set [session|global] transaction isolation level [Read Uncommitted | Read Committed |

Repeatable Read |Serializable]

视图

定义

create view 视图名 as

sql 语句 ;

使用

select * from 视图名 ;

触发器

定义

create trigger 触发器名

触发时机 触发条件 on 表 for each row

begin

触发器触发时执行的 sql 语句；

end

存储过程

创建

create procedure 存储过程名 (in/out/inout 参数名 数据类型 )

begin

sql 语句 ;

end

调用

call 存储过程名 ( 参数 );

二错题

1. 某理财购买信息管理模块包含两个表，结构如下：（40分）

理财产品信息表（product）

字段名	字段标签		类型	特征
productid	理财产品编号		Varchar(16)	主键
productname	理财产品名称		Varchar(48)	非空
interest	利率（%）		Double	非空

购买信息表（purchase）

字段名	字段标签	类型	特征
serialno	流水号	Int	主键,自增
productid	理财产品编号	Varchar(16)	外键，关联理财产品信息表中的编号字段
customerid	客户编号	Varchar(16)
amount	购买金额	Varchar(48)
purchasedate	购买时间	Varchar(24)

1. 查询所有在2019年1月1日零点后，购买理财产品总金额大于1000的客户，输出理财产品名称，购买总金额

Select productname,amount

from product pt ,purchase pc

where pt.productid = pc.productid

And Purchasedate >’20190101 0:0:0’ and Amount >1000

在这个题中让我体会到有的数据可以直接对它作用,例如时间,这是我学习时没有注意到的.