华清远见重庆中心数据库阶段知识点梳理

数据库

DateBase，简称为DB。是运行在操作系统上，按一定的数据结构，保存数据的仓库。

数据库管理系统

DataBase Manager System，简称为DBMS。是一种操作和管理数据库的大型软件，用于建立、使用和维护数据库。

SQL

Structrued Query Language 结构化查询语言

操作数据库

1.创建数据库

create database 数据库名;

2.切换数据库

use 数据库名；

3.删除数据库

drop database 数据库名；

操作数据表

1.创建数据表

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

2.删除数据表

drop table 表名;

3.修改数据表

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

添加约束

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

操作数据

数据的操作，是指数据的增加create，修改update，查询read和删除delete。 简称为CURD。

数据添加insert

数据添加时，都是整行(一条记录)添加。不能只给一个字段添加数据。

-- 给所有字段赋值
insert info 表名 values('值1','值2'.....)
--给指定字段赋值
insert into 表名(字段1,字段2...) values('值1','值2'...)
--批量添加
insert into 表名[(字段1,字段2)] values
('值1','值2'...),
('值1','值2'...),
...
('值1','值2'...)

数据修改update

--修改单个字段的所有值
update 表名 set 字段 = 值;
--修改多个字段的所有值
update 表名 set 字段1 = '值',字段2 = '值'...
--根据条件修改(where子句)
update 表名 set 字段 = '值' where 条件
--多个条件时
update 表名 set 字段 = '值' where 条件 and 条件

模糊查询

-- 字段 like '%娜%'
-- 带有'娜'字
-- 字段 like '张%'
-- ‘张’字开头
-- 字段 like '%儿'
-- ‘儿’字结尾
-- 字段 like '%瑞_'
-- 倒数第二个字为‘瑞’
-- 字段 like '___'
-- 3个字
update 表名 set 字段 = '值' where 字段 like ‘%文字%’

数据删除delete

--删除所有
delete from 表名;
-- 或
truncate table 表名;
--条件删除
delete from 表 where 条件

数据查询select

--查询所有字段
select * from 表名;
--查询指定字段
select 字段名1,字段名2... from 表名;
--字段重命名
select 字段名1 as '重命名',字段名2 '重命名'... from 表名;
--去重复
select distinct 字段名 from 表名;
--条件查询
select * from 表名 where 条件;
--排序
select * from 表名 where 条件 order by 排序字段 [ASC/DESC],排序字段 [ASC/DESC]...

查询指定条数

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

统计函数

数学相关函数

 字符串相关函数

 时间相关函数

 时间相关函数

分组 

按指定的字段进行分组，会将该字段值相同的记录归纳到同一组中。

select 分组字段,统计函数 from 表名 group by 分组字段
--如果统计函数作为条件，不能写在where之后，要写在having之后，having子句放在分组之后。
-- 按图书作者分组，查询平均价格大于50的信息
select book_author,avg(book_price) from book_info
group by book_author
having avg(book_price)>50

连接查询

内连接

通常是通过主表的主键字段关联从表的外键字段

如果两张表中关联的字段名一致，一定要通过"表名.字段名"进行区分，通常给表重命名

如果使用inner join，带条件时需要加入where子句；如果使用,隔开各个表，带条件时使用and拼 接条件

内连接只会显示两张表中有关联的数据

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中的数据

嵌套查询

-- 查询大于平均价格的图书
select * from 表
where price >(select avg(price) from 表)
-- 根据作者分组，查询每组大于平均价格的图书
select *
from 表 t1,(select author,avg(price) avg_price from 表)t2
where t1.author=t2.author and price>avg_price

JDBC

Java Database Connectivity Java数据库连接

用于Java程序连接不同的数据库。

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

核心接口

Connection:用于设置连接的数据库的地址、账号、密码

PreparedStatement:用于预处理、执行sql语句

ResultSet:用于接收查询后的数据

实体关系模型

实体Entity：一个表就是一个实体。

关系Relationship：实体与实体之间的关系。

实体关系模型也称为ER模型。 用图形表示ER模型时，这个图就称为ER图。

用矩形表示实体，用椭圆形表示实体的属性，用菱形表示实体之间的关系，用直线连接各个图形。

实体之间的关系

一对一

实体A与实体B之间唯一对应。

 创建国家表

create table country(
country_id int not null primary key auto_increment,
country_name varchar(20) not null,
country_population bigint not null,
country_area int not null
)

创建领导人表

create table leader(
leader_id int not null primary key auto_increment,
leader_name varchar(20) not null,
leader_birthday date not null
)

在以上的两个实体表中，选择任意一张表中添加一列，保存另一张表的主键，添加唯一约束

create table leader(
leader_id int not null primary key auto_increment,
leader_name varchar(20) not null,
leader_birthday date not null,
country_id int not null unique,
foreign key (country_id) references country(country_id)
)

一对多/多对一

一对多：一个实体A对应多个实体B，一个实体B不能对应多个实体A

多对一：多个实体B对应一个实体A，多个实体A不能对应一个实体B

 创建主表(一)

create table coach(
coach_id int not null primary key auto_increment,
coach_name varchar(20) not null,
coach_leave varchar(20) not null
)

创建从表(多)，在表中添加字段关联主表中的主键字段

create table student(
stu_id int not null primary key auto_increment,
stu_name varchar(20) not null,
stu_phone varchar(20) not null，
coach_id int not null,
foriegn key (coach_id) references coach(coach_id)
)

多对多

一个实体A可以对应多个实体B，一个实体B也可以对应多个实体A。

 创建课程表

create table course(
course_id int not null primary key auto_increment,
course_name varchar(20) not null,
course_score int not null
)

创建学生表

create table student(
stu_id int not null primary key auto_increment,
stu_name varchar(20) not null,
stu_phone varchar(20) not null
)

体现多对多关系的表：成绩表

create table score(
s_id int not null,
course_id int not null,
stu_id int not null,
cj int not null,
-- 可以选择添加外键约束
foreign key (course_id) references course(course_id),
foreign key (stu_id) references student(stu_id)
)

事务transaction

事务的特性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]