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

数据库

        数据库DataBase，简称DB。运行在操作系统上，按一定的数据结构，保存数据的仓库。是一个电子化的文件柜。数据库管理系统DataBase Manger System，简称DBMS。是一种操作和管理数据库的大型软件，用于建立，使用和维护数据库。

关系数据库的特点：

·优点：

1. 易于维护：都是使用表结构储存数据，格式一致
2. 使用方便：SQL语句通用，可用于不同关系型数据库
3. 复杂查询：可以通过SQL语句在多个表之间查询出复杂数据

·缺点：

1. 读写性能差，尤其是还是数据的高效读写
2. 固定的表结构，灵活度少欠
3. 高并发读写时，硬盘I/O决定了读写速度

非关系型数据库的特点：

1. 保存数据的格式多样
2. 对于海量数据的读写性能高
3. 不支持复杂查询

操作数据库

• 查看所有数据库

show databases;

• 切换数据库

use 数据库名;

• 查看当下所有表

show tables;

• 创建一个数据库

create database 数据库名;

• 删除一个数据库

drop database 数据库名;

操作数据

• 创建数据表，并且添加约束

create table goods(
	goods_id int not null primary key auto_increment,
	goods_name varchar(10) not null,
	goods_price double not null
)
create table client(
	client_id int not null primary key auto_increment,
	client_name varchar(10) not null,
	client_telephone varchar(11) not null,
	client_address text
)
create table consume(
	consume_id int not null primary key auto_increment,
	client_id int not null ,
	goods_id int not null,
	quantity int not null,
	foreign key (client_id) references client(client_id),
	foreign key (goods_id) references goods(goods_id)
)

• 删除数据表

  drop table 表名;

• 修改数据表

对表重命名
alter table 旧表名 rename to 新表名;
添加新字段
alter table 表名 add column 字段名 数据类型 字段特征;
修改字段
alter table 表名 change 旧字段名 新字段名 数据类型 字段特征;
删除字段
表名 drop 字段名;

• 数据添加insert

insert into 表名 values('值1','值2'...)

• 数据修改update

update 表名 set 字段 = 值;

• 数据删除delete

delete from 表名;
-- 或
truncate table 表名;

数据查询select

统计函数（聚合函数）

count（字段名）	统计数量
sum（字段名）	求和
avg（字段名）	平均
max（字段名）	最大
min（字段名）	最小

条件

指定值	=,!=,<>
指定范围	>,<,>=,<=,betwen值and值
指定集合	[not] in (元素1,元素2,,,)
模糊查询(%表示未知长度字符串,_表示一个字符)	like'%值'
空置	is[not]null
多条件	使用and,or,&,|串联条件

数学相关函数

abs（值或字段）	绝对值
pow（值或字段）	次幂
sqrt（值或字段）	开平方
round（值或字段）	四舍五入
ceil（值或字段）	向上取整
floor（值或字段）	向下取整

字符串相关函数

length(字符串或字段)	得到字符串长度
trim(字符串或字段)/ltrim(字符串或字段)/rtrim(字符串 或字段)	去除字符串首尾/首/尾空格
left(字符串或字段,n)/right(字符串或字段,n)	从字符串左/右开始截取n个字符
substr(字段或字符串,start)	从start开始截取至末尾
substr(字段或字符串,start,length)	从start开始截取length个字符
lcase(字符串或字段)/ucase(字符串或字段)	转换小写/大写
instr(字符串1,字符串2)/locate(字符串2,字符串1)	得到字符串2在字符串1中出现的顺 序
reverse(字符串或字段)	翻转字符串
concat(字符串1,字符串2...)	拼接所有字符串
replace(字符串或字段,旧字符串,新字符串)	将字符串中的旧字符串替换为新字 符串

时间相关函数

now()	当前日期时间
current_date()/curdate()	当前日期
current_time()/curtime()	当前时间
year(日期)/month(日期)/day(日期)	得到年/月/日部分
datediff(时间1,时间2)	计算时间1与时间2相隔的天数
timediff(时间1,时间2)	计算时间1与时间2相隔的时分秒
TIMESTAMPDIFF(时间单位,时间1,时间2)	计算时间1与时间2之间相隔的指定时间单位

• 查询所有字段

• select * from 表名;

• 查询指定字段

select 字段名1,字段名2... from 表名;

• 字段重命名

select 字段1 as '重命名',字段2 '重命名'... from 表名;

• 查询指定条数

-- 查询前N条记录
select * from 表名 limit N;
-- 查询从索引N开始的M条记录
select * from 表名 limit N,M;
-- 每页显示size条，第page页
select * from 表名 limit (page-1)*size,size

• 去重复

select distinct 字段名 from 表名;

• 条件查询

select * from 表名 where 条件;

• 排序

排序可以是升序或降序
默认不写是升序asc
降序需要写desc
排序时如果有条件，where条件写在表名之后，排序之前
多字段排序时，在order by之后写多个字段及排序规则，用逗号隔开
按字段顺序优先排序
select * from 表名 where 条件 order by 排序字段 [ASC/DESC],排序字段 [ASC/DESC]...

• 分组

select 分组字段,统计函数 from 表名 group by 分组字段

• group_concat（）函数

group_concat(字段1或字符串,字段2或字符串...)
select group_concat(字段1,字段2...) from 表名 group by 分组字段
-- 根据图书类型分组，查看每组下的图书名和作者
select group_concat(book_name,'--',book_author) from book_info group by type_id

• 行列转换

select stu_id,stu_name,
sum(if(c_name='高等数学',cj,null)) as '高等数学',
sum(if(c_name='思修',cj,null)) as '思修',
sum(if(c_name='大学英语',cj,null)) as '大学英语',
sum(if(c_name='大学体育',cj,null)) as '大学体育'
from score
group by stu_id

连接查询

• 交叉连接，笛卡尔积

select * from 表1,表2;
select * from 表1 cross join 表2;
select * from 表1 inner join 表2;

• 内连接

select * from 表1,表2 where 表1.字段=表2.字段;
select * from 表1 inner join 表2 on 表1.字段=表2.字段;
-- 如查询图书类型表(类型编号、类型名称)和图书详情表(图书编号、类型编号、图书名称)
select * from 图书类型表 t1 ,图书详情表 t2 where t1.类型编号=t2.类型编号;
select * from 图书类型表 t1 inner join 图书详情表 t2 on t1.类型编号=t2.类型编号;

• 左连接

-- 在保证左表数据显示完整的情况下，关联右表中的数据，没有关联的数据用null表示
select * from 表1 left join 表2 on 表1.字段=表2.字段;
-- 以上语句中表1称为左表，表2称为右表,会完整显示表1中的数据

• 右链接

-- 在保证右表数据显示完整的情况下，关联左表中的数据，没有关联的数据用null表示
select * from 表2 right join 表1 on 表1.字段=表2.字段;
-- 以上语句中表1称为左表，表2称为右表，会完整显示表2中的数据

JDBC

        java数据库连接Java Database Connectivity，用于Java程序连接不同的数据库。实际在Java中定义的相关数据库连接时所需的接口，不同的数据库对其进行了实现。

核心接口：

• Connection：用于设置连接的数据库的地址，账号，密码
• PreparedStatement：用于预处理，执行sql语句
• ResultSet：用于接收查询后的数据

例：

关系模型

        实体关系模型也称为ER模型。实体Entity：一个表就是一个实体。关系Releationship：实体与实体之间的关系。用矩形表示实体，用椭圆形表示实体的属性，用菱形表示实体之间的关系，用直线连接各个图形。

• 一对一：一个国家有一个领导人
• 多对一/一对多：一个班级有多个学生，一个学生不能有多个班级
• 多对多：一个学生学习多门课程，一门课程可以被多个学生学习

数据库设计规范：

• 第一范式1NF：字段不可再分。
• 第二范式2NF：在满足1NF的基础上，消除部分依赖。
• 第三范式3NF：在满足2NF的基础上，消除传递依赖。

视图view

视图可以当做数据库中的一个临时表，保存一些较为复杂的查询后的结果，

之后可以直接通过该视图查询数据，不需要再次编写复杂的sql语句。

视图同时可以隐藏一些查询细节，定制查询数据。

• 创建视图
• 使用视图
• 删除视图

事务transaction

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

事务的特性ACID

• 原子性Atomicity：事务是最小的执行单元
• 一致性Consistency：事务执行前后，数据整体保持一致
• 隔离性Isolation：各个事务之间应当互不干涉
• 持久性Durability：事务一旦提交，改变是永久的

事务并发出现的问题：脏读，不可重复读，幻读。

事务隔离级别:

 事务相关指令：

• 查看事务自动提交

select @@autocommit

• 修改事务自动提交

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

• 手动开启事务

start transaction；

• 提交

commit；

• 回滚

rollback；

• 查询当前事务隔离级别

select @@transaction_isolation

• 设置事务隔离级别

set[sesion|global]transaction isolation level[Read Uncommitted | Read Committed | Repeatable Read | Serializable]

难点:

内连接，左连接，右链接的区分。通常是通过主表的主键字段关联从表的外键字段。如果两张表中关联的字段名一致，一定要通过"表名.字段名"进行区分，通常给表重命名。如果使用inner join，带条件时需要加入where子句；如果使用,隔开各个表，带条件时使用and拼接条件。内连接只会显示两张表中有关联的数据。左链接在左表数据显示完整的情况下，关联右表的数据。右链接在右表数据显示完整的情况下，关联左表的数据