【Java】Java 基础知识（三）数据库

---

一、数据库概述

1.什么是数据库

2.MySQL数据库

（1） MySQL使用的图形化界面：

二、SQL语句

1.概述：

（1） SQL语言又称为结构化查询语言（Structured Query Language），简称SQL；也是数据库脚本文件的扩展名；

2.SQL语言分类：

• DML（data Manipulation Language）数据操纵语言（CRUD），用来对数据库中表的记录进行增删改操作；如：insert 插入、delete 删除、update 修改、selelct 检索；
• DDL（data Definition Language）数据库定义语言（创建库，创建表），操作表结构，表中字段的；如：create table之类；
• DCL（data Control Language）数据库控制语言（分配具体权限）；
• DQL（data Query Language）数据查询语言（各种查的语法）；注意：SQL不区分大小写；

3.DDL：用来操作表结构，表中字段的；

3.1 数据库的常用操作：

（1） 创建数据库，数据库名aaa

create database aaa default character set utf8;  // 创建名称为aaa的数据库，默认字符设置为utf8；

（2） if not exists 如果已存在，则不用操作了

create database if not exists aaa;  // 创建名称为aaa的数据库，如果已存在，则不创建；

（3） 删除数据库：

drop database aaa;  // 删除名称是aaa的数据库；

（4） 查看所有数据库：

show databases;  // 查看所有数据库

（5） 查询当前数据库

select database();   // 查询当前数据库；

（6） 使用数据库，在操作表之前，需要先选中数据库；

use 数据库名;  // 使用指定数据库
use aaa;   // 使用数据库名为aaa的数据库；

3.2 表的常用操作（字段：列，记录：行）（DDL）：

3.2.1 表格操作：

（1） 创建（新建）表，方式一：

• 新建表格式一：

create table 表名(
	字段1名字 字段类型(字段长度),
	字段2名字 字段类型(字段长度),
	字段3名字 字段类型(字段长度),
	...
);

• 举例，创建名称叫emp的表，有字段 id，door_name，tel；

create table emp(
	id int(11),
	door_name varchar(100),
	tel varchar(50)
);

（2） 创建（新建）表，方式二：

• 新建表格式二：

create table 表名(
	字段1名字 字段类型(字段长度)[comment 字段1注释],
	字段2名字 字段类型(字段长度)[comment 字段2注释],
	...
);[comment 表注释]

• 举例：

name varchar(50) comment'姓名';

（3） 删除表

drop table 表名;  

drop table emp;  // 删除名称是emp的表格；

（4） 删除指定表，并重新创建该表：

truncate table 表名;

（5） 查看当前数据库的所有表

show tables;

（6） 查询指定表的建表语句：

show create table 表名;

show create table tb_user;  // 查询tb_user的建表语句；

（7） 查看表结构/设计表：

desc 表名;

desc tb_door;  // 查看表名为tb_door 的表格内容；

（8） 修改表名

alter table 表名 rename to 新表名;

3.2.2 表格 字段（列） 操作（DDL）

（1） 添加字段（列）

alter table 表名 add 字段名 类型(字段长度)[comment 注释][约束];

alter table emp add nickname varchar(20) comment '昵称';  // 为emp表增加一个新的字段“昵称”为nickname，类型为varchar(20)

（2） 修改字段的数据类型：

alter 表名 modify 字段名 新数据类型(长度);

（3） 修改字段名和字段类型：

alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束];

alter table emp change nickname username varchar(30) comment'用户名';   // 将emp表的nickname 字段修改为username，类型为varchar(30);

（4） 删除字段：

alter table 表名 drop 字段名;

alter table emp drop username;   // 将emp表的字段username 删除；

4.DML：用来对数据库表的记录进行增删改操作

4.1 定义：

（1） 添加数据（insert），修改数据（update），删除数据（delete）

4.2 表格 记录（行） 操作（DML）

（1） 给全部字段插入/添加记录

insert into 表名 values (字段1的值,字段2的值,字段3的值);

// 举例：向 tb_door 表中插入2条记录
insert into tb_door values (null,'吃饭',888);
set name gbk;
insert into tb_door values(1,'张三',18);

（2） 给指定字段添加数据

insert into 表名(字段名1,字段名2,...) values (值1,值2,...);

（3） 给字段批量添加数据

insert into 表名(字段1,字段2,...) values (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...)

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

（4） 查询记录

select 字段名/* from 表名;   // 查询表中某个字段名/所有的记录；

// 查询表tb_door 中的所有字段的记录
select * from tb_door;

（5） 修改记录（字符串要加单引号’'）

// 修改表格中某个字段的所有记录
update 表名 set 字段名=字段值;  

// 修改student表格中的age字段的记录，全部修改成100；
update student set age=100;  

（6） 修改某个字段的记录（注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据）

update 表名 set 字段名1=值1,字段名2=值2,...[where 条件];

// 将表emp中，修改id为1的数据，将name修改为lisa；
update emp set name='lisa' where id=1;

// 将所有员工的入职日期修改为 2008-01-01（修改的是一整列的数据）
update emp set entrydate = '2008-01-01';

（7） 删除记录

// 删除表格中的所有记录
delete from 表名;

// 删除表格student中的所有记录
delete from student;

// 删除表里某个字段中为这个字段值的数据
delete from 表名 where 字段名=字段值;


// 删除 tb_door 表中id字段中为2的数据
delete from tb_door where id=2;

（8） 排序

// 将表中的记录按照字段名降序排列
select * from 表名 order by 字段名 desc;   

// 将tb_door 表中的记录按照tel 字段中的数据降序排列
select * from tb_door order by tel desc;

（9） 记录总数

// 查询表格中的总记录数
select count(*) from 表名;

// 查询 tb_door 表中的总记录数
select count(*) from tb_door;

5.DQL语句：数据查询语言，用来查询数据中表的记录

5.1 DQL语法：

5.2 DQL 基本查询

（1） 查询多个字段

// 查询返回多个字段
select 字段1,字段2,字段3... from 表名;

// 查询返回所有字段
select * from 表名;

（2） 设置别名：对于查询返回的字段，可以设置别名，增强字段的可读性；

select 字段1[as 别名1],字段2[as 别名2]... from 表名;

// 例：查询表emp的字段名workaddress，起别名为“工作地址”
select workaddress as '工作地址' from emp;
// 注意，上面的 as 是可以省略掉的，可以写成下面格式
select workaddress '工作地址' from emp;

（3） 对于查询返回的结果，去除重复的记录

select distinct 字段列表 from 表名;

// 例：查询员工的上班地址，不要重复

5.2 DQL 条件查询

（1） 语法：

select 字段列表 from 表名 where 条件列表;

（2） 条件

三、函数

1.基础函数

（1） 概述：MySQL提供了很多函数，可以快速操作数据；

（2） 各种函数的使用：

      ① 基本函数：lower（小写）/ upper（转大写）/ length（长度）/ substr（截取字符串）/ concat（拼接）/ replace / ifnull / uuid

      ② 小数函数： round / deil / floor

      ③ 日期函数： now / year / month / day / hour / minute / second / 转义字符

（3） 基本函数

      ① lower：数据转小写

// 将表格dept中查询字段dname，将字段dname里面的内容转为小写
select dname,lower(dname) from dept;

      ② upper：数据转大写

// 从表格dept中查询dname字段，将dname字段里面的内容转大写
select dname,upper(dname) from dept;

      ③ length：查询数据长度

// 查询表格dept中dname字段的所有记录的长度
select length(dname) from dept;

// 从dept表格中查询字段dname和字段loc中所有记录参数的长度
select dname,length(dname),loc,length(loc) from dept;

      ④ substr(1,2)：1是要截取谁，2是从哪里开始（从1开始数）

      ⑤

      ⑥

四、约束

五、多表查询 Join

1.多表关系

1.1 多表关系概述

（1） 项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：一对多（多对一）、多对多、一对一。

（2） 一对多（多对一）：

（3） 多对多：

（4） 一对一：

2.多表查询概述

2.1 多表查询概述：指从多张表中查询数据

2.2 笛卡尔积

（1） 定义：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）。

2.1 多表查询分类

2.1.1 连接查询

（1） 内连接（inner Join）：相当于查询A、B交集部分数据；

（2） 外连接：

      ① 左外连接（left Join）：查询左表所有数据，以及两张表交集部分的数据；

      ② 右外连接（right）：查询右表所有数据，

（3） 自连接：当前表与自身的连接查询，自连接必须使用表别名；

（4） inner Join、left Join、right Join的区别？

• inner Join 两边都对应有记录的才展示；
• left Join 左边表中的数据都出现，右边没有数据以null填充；
• right Join 右边表中的数据都出现，左边没有数据以null填充；

2.1.2 子查询：

3.内连接

3.1 给表名起个别名：

select * from emp e;

select * from emp e, dept d;

3.2 内连接语法

（1） 隐式内连接：

select 字段列表 from 表1,表2 where 条件...;

（2） 显示内连接

select 字段列表 from 表1 [inner] Join 表2 on 连接条件...;

（3） 内连接查询的是两张表交集的部分；

4.外连接

4.1 左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件...;

• 相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据；

4.2 右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件...;

• 相当于查询表2（右表）的所有数据 包含 表1和表2交集部分的数据；

5.自连接：一张表自己连接自己

（1） 自连接查询语法：

select 字段列表 from 表A 别名A Join 表A 别名B on  条件...;

• 自连接查询，可以是内连接查询，也可以是外连接查询；
• 注意，自连接查询一定要给表起别名；

6.联合查询 union，union all

（1） 概念：对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果；

（2） 语法：

select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致；
• union all 会将全部的数据直接合并在一起，不会去重；Union 会对合并之后的数据去重；

7.子查询

（1） 概念：SQL语句中嵌套select语句，称为嵌套查询，又称为子查询。

（2） 语法：

select * from t1 where column1 = (select column1 from t2);

• 子查询外部的语句可以是insert / update / delete / select 的任何一个。

（3） 根据子查询结果不同，分为：

• 标量子查询（子查询结果为单个值）；
• 列子查询（子查询结果为一列）；
• 行子查询（子查询结果为一行）；
• 表子查询（子查询结果为多行多列）；

      ① 标量子查询：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询；（常用的操作符： = <> > >= < <=）

（4） 根据子查询位置，分为：where之后，from之后，select之后；

8.多表查询案例

六、事务

七、存储引擎

八、索引

1.

九、SQL优化

十、视图/存储过程/触发器

十一、锁

十二、InnoDB核心

十三、MySQL管理

十四、日志

十五、主从复制

十六、分库分表

十七、读写分离

十八、JDBC

1.JDBC概念

（1） JDBC 就是使用Java语言操作关系型数据库的一套API；

写入Java文件中的样式：

（2） JDBC全称是 Java DataBase Connectivity Java数据库连接。

2.JDBC 快速入门

3.JDBC API详解

**（1）**DriverManager 驱动管理类API：

• 作用是注册驱动、获取数据库连接

（2） Connection API

      ① Connection（数据库连接对象）作用

• 获取执行SQL的对象；
• 管理事务；

      ②

（3） Statement

（4） ResultSet

      ① ResultSet 案例

需求：查询account账户表数据，封装为Account对象中，并且存储到ArrayList集合中；

（5） PreparedStatement

      ①

      ②