SQL
查看信息
- 查看所有数据库: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…);保证必须写上非空字段,值的顺序和字段顺序保持一致
- insert into 表名 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"
查询的步骤
- 获取连接对象Connection
- 构造sql语句String
- 预处理sql语句pst=conn.preparestatement
- 给sql语句中的?赋值pst.setXXX(?顺序,值)
- 调用executeQuery()得到Result结果集
- 遍历结果集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存储引擎,支持事务