MySQL数据库基础

第一章 MySQL

1.1概念

1.1.1 什么是数据库

DataBase，简称DB，在计算机中，以一定组织和结构，存取和管理数据的软件服务。

用户

应用软件

服务软件

系统软件

驱动程序

硬件

持久化：长久保持数据。

DBA：DataBaseAdminister                数据库管理员

DBS：DatabaseSystem                      数据库系统

DNMS：DatabaseManageSystem      数据库管理系统

1.1.2常见的数据库

关系型数据库：以关系形式保存数据

MySQL：轻量型关系数据库，开源免费的。

阿里变动：Linux Apache MySQL Php    ->      JavaEE+Oracle

Oracle：大型面向对象的关系型数据库，功能十分强大，商业软件。

SQL Server：MS SQL    C++，C#，   .net

SQLite：轻量型纯文件数据库

非关系型数据库：通常以键值对形式保存数据。

Redis：基于键值对的，内存型数据库。快

MongoDB：基于键值对

1.1.3 数据库的安装

MySQL

安装MySQL程序

配置MySQL服务：系统服务中可以看到。

命令行工具

可视化的客户端工具

Navicat

SQL-结构化查询语言

1.1.4 命令行

MySQL语句不区分大小写

1）连接数据库      -u代表用户

mysql -u root -p 

2）显示当前服务下的所有数据库   

show databases；

3）切换数据库

use+数据库名字

4）显示当前数据库下所有的表

show tables

5）启动和停止服务

使用的Windows的命令net

net start 服务名                                      net stop

1.1.5 MySQL的数据类型

1.数值型

tinyint

smallint

int

bigint

与Java中的byte、short、int、long相似

float

double

既要指定长度也要指定精度，长度包括小数点后面的长度

decimal  表示的是精确的值

2.字符串

char      必须设置长度，自动补充，name char(16)  值tom

varchar    设置长度，值需要多少申请都少，速度快

tinytext    不需要设置长度

text      不需要设置长度

longtext    不需要设置长度

3.时间日期

Date                                   年月日

Time                                   时分秒

DateTime                            年月日时分秒

TimeStamp                         时间戳

4.二进制

binary

blob

1.2 SQL

1.2.1 DDL (Data Define Language)

数据定义语言

1. 数据库

1. 创建数据库

create database name;

1. 删除数据库

drop database name;

1. 查看数据库

show databases;

1. 切换数据库

use database;

1. 数据表

1. 创建

create table 表名(

##列  定义类似于Java定义一个变量

列名 数据类型(长度，精度) 约束条件，

…

列名 数据类型(长度，精度) 约束条件

)；

1. 查看

查看所有表   Show tables；

查看表的结构   desc 表名；

1. 修改

alter table 表名 修改操作；

1. 添加字段

Alter table 表名 add 字段名 数据类型(长度，精度) 约束条件；

1. 删除字段

Alter table 表名 drop 字段名；

1. 修改字段

Alter table 表名 change 旧字段名 新字段名 数据类型(长度，精度) 约束；

Alter table 表名 modify 字段名 数据类型(长度，精度) 约束；

1. 删除

Drop table 表名；

1.2.2 DML (Data Manage Language)

数据管理语言，负责对数据表的增，删，改

数据表，分为行和列

列：字段Field

行：记录row

1. 增

Insert into 表名 (字段列表) value/values(值列表)

1. 字段列表可以不写，此时相当于把每一个字段都进行设置。
2. 字段列表中有些字段可以不写

自增主键，允许为空，有默认值。

1. 批量添加

多条记录都放在value/values后面，每一条用，分隔

1. 删

语法：

Delete from 表名where 条件

1. 没有where子句时，只有符合条件的记录才会被删除；
2. 当没有where时，所有记录都会被删删除
3. 当删除之后，自增主键还会延之前值。

Truncat 表名：将表重构，相当于将表删除，再重新创建。

与delete区别：

1. Truncat是重构表，delect是删除记录，如果要删除所有的记录truncat会更快
2. Truncate只能删除所有，delete可以删除部分。
3. Truncate自增主键会归零，delete还会沿用之前值
4. Truncate不能回滚，delete可以回滚。

1. 改

语法

Update 表名 set 字段=值，字段2=值 where 条件(primary key)

1.2.3 DQL (Data Query Language)

数据查询语言

见第二章

1.2.4 DCL (Data Control Language)

数据控制语言

1.权限

1.3 navicat

Navicat是一个数据库操作工具

第二章 查询

2.1简单查询

2.1.1 基本语法

select 字段列表

from 表名

where 条件

group by 字段列表

having 条件

order by 字段列表 desc/asc

limit m,n

2.1.2最简单的查询

select * from 表名

*：通配符，代表要查询所有的字段

筛选字段

select 字段，字段 from表名

字段加别名，as可以省略

select 字段 as 别名，字段 表名

1. 1. 1. where 字句

从表中筛选记录

1. 比较运算符

>、>=、<=、<

=、!=、<>

1. 逻辑运算符

逻辑与     and

逻辑或     or

逻辑非     not

1. 空值判断

is null

is not null

1. 模糊查询

like ‘表达式’

%：代表任意个，任意字符

_：代表任意一个字符

1. 范围

between…and…

1. 集合

in（1，3，5）

not in（）

1. 1. 1. 分组
2. 聚合函数

聚合函数	作用	特点
count	统计数量	Count(字段名) count(*) count(1)  速度最快
sum	求和
avg	求平均值	会忽略空值
max	求最大值
min	求最小值

1. 分组

根据查询结果中，对指定字段值相同的记录，分成一组，合并成一条。

distinct合并相同值

group by 字段名，字段名     有多个字段时，这些字段都相同时才会分成一组

group by 字段的下标，从1开始

1. 分组可以和聚合函数联用

对分组后的每一组进行聚合操作

1. having

对分组后的数据进行筛选

where 与having 的区别

1. 语法：where写在group by前面，having写在group by的后面
2. 执行时间：where在从表中提取记录时执行，在分组之前。having是在group by执行之后
3. 聚合函数：where不能使用聚合函数，having可以使用聚合函数

2.1.5排序

让查询结果按照指定的字段或升序或降序进行排列

order by 排序字段 asc/desc

默认是升序

每一个字段的排序规则必须单独指定

排序字段也可以使用字段下标

1. 1. 1. limit

提取查询结果中的一部分。

limit m             只取前面m条

limit m,n          从第m条记录开始往后提取，提取n条。

m是从0开始的

通常用于数据的分页操作。

2.2子查询

在一个SQL语句中，包含了另一个select语句

2.2.1 insert

insert into 表名（字段列表）

select …

子查询的字段要与插入的字段列表一一对应

2.2.2 delete

delete from 表名 where 条件

2.2.3 update

update 表名 set 字段=值，字段=值 where 条件

2.2.4 select

1.where

1）where 字段 比较运算符（子查询）

子查询只能有一个字段。

子查询只能有一条结果。

2）where 字段 in（子查询）

子查询只能有一个字段

3）where exists/not exists（子查询）

存在

先执行外层父查询，

每筛选一条记录，都会单独执行一次子查询

他的效率会很低。

通过可以转换成in或者not in的

1. where 字段 比较运算符 any/all（子查询）

any：只需要子查询中的任意一条记录满足比较运算符即成立。

all：所有的子查询记录都要满足比较运算符才会成立。

2.作为表

把子查询当作表来使用

select * from （子查询）as 别名

1. 作为筛选字段

select（子查询） from 表名

2.2.5 结果拼接

查询

union / union all

查询

union会合并相同的记录

union all 不会

2.3连接查询

多表连查

2.3.1迪卡尔积

select * from 表一，表二

求两个集合的乘积

2.3.2 内连接

select * from 表1 inner join 表二 on 连接条件

inner可以省略

效果与迪卡尔积一样。

只有符合连接条件的记录才会出现在结果中。

2.3.3 外连接

有一个表是主表，一个从表。

主表中的每一条记录都会出现在查询的结果中。

如果主表中的记录在重表中没有对应记录此时，将以null形式

1. 左外连接

select * from 表一 left join 表二 on 连接条件

左外连接左表是主表

1. 右外连接

select * from 表二 right join 表二 on 连接条件

右外连接右表是主表

1. 1. 1. 全外连接

MySQL不支持。有limit

SQL Server

第三章 数据库相关概念

3.1索引

索引是用来提高数据的查询效率

3.1.1 索引的分类

1.普通索引

只排序，用于提升查询速度

2.唯一索引

要求字段必须唯一，可以为null

3.主键索引

主键必须唯一，不能为null

４.全文索引

以英文单词为单位，创建的索引（基本不用）。

搜索引擎：ES

5.空间索引

针对空间数据类型。向量、坐标。

在表结构以外，索引会创建新的排序结构。

索引在增删改时，需要维护排序结构正确。

导致增删改的效率降低。

3.1.2索引的结构

BTree      B+树

Hash      哈希表

3.1.3联合索引

由两个或两个以上的字段共同组成的索引。

先以第一个字段进行排序，然后第一个字段相同的记录再按照第二字段进行排序。

最左前缀原则

索引（a字段，b字段）

select * from 表

where a > 值 and b > 值                       可以命中索引

select * from 表

where b > 值 and a > 值                        索引不起作用

3.1.4MySQL的引擎

1.InnoDB

支持聚集索引和非聚集索引

主键就是聚集索引，其他的就是非聚集索引

支持事务

2.MyISAM

支持全文索引

都是非聚集索引

不支持事务

效率更高

3.1.5聚集索引和非聚集索引

聚集索引：索引中的每一个结点，就是一条记录。

非聚集索引：索引上的每一个结点包含两个部分，一部分是索引字段的值，另一部分是记录所在的位置（Innodb用的是主键，MyISAM用的是地址）。

3.2数据库的三大范式

规范关系型数据库的设计

有一套数据需要使用关系型数据库保存时

设计表的结构。

1. 1. 1. 1NF

数据表的字段，应该是不可再分的！-原子性

不可再分是相对于具体业务需求

1. 1. 1. 2NF

一个表应该有一个主键，其他的字段都应该依赖于主键。-依赖性

主键：primary key，一个表中记录的唯一的标识

外键：foreign key，字段的一种约束，用来将两个表进行关联

当一个表中的记录与另一个表中的记录需要进行关联时，在当前表中添加一个外键，与另一个表的主键进行关联。

要求：外键字段的值，必须来源于另一个表，或者只能是null

1. 1. 1. 3NF

表中的非主键字段，必须直接依赖于主键。-去冗余

存在间接依赖时，就会造成数据冗余，此时需拆分表。

1. 1. 事务

事务是一组不可分割的SQL语句，这组语句要么都执行成功，要么都执行失败。

3.3.1事务的操作

1.开启事务

begin

start transaction

2.提交事务

commit

3.回滚事务

rollback

3.3.2 ACID四大特性

1.Atomicity 原子性

事务是不可分割的，一个事务中的SQL语句，要么都执行成功，要么都执行失败。

2.Consistency 一致性

事务在执行前，和事务执行后，无论事务是否执行成功，数据库的状态应该是一致的。

1. Isolation 隔离性
2. ；‘4

事务并发时，他们之间不应该相互干扰。

4.Durability持久性

事务一旦提交，对数据库的影响是永久的。

3.3.3 事务并发问题

1.脏读

一个事务读取到了另一个事务未提交的数据。

2.不可重复读

同一事务在读取同一数据时，出现前后两次读取到的数据不一致。

3.幻读

一个事务读取不到另一个事务插入或删除的记录。

3.3.4隔离级别

1.读未提交 Read uncommitted

一个事务会读到另一个事务未提交的记录。

事务会出现脏读、不可重复读、幻读

2.读已提交 read committed

一个事务只能读到另一个事务已提交的记录。

不会出现脏读，会出现不可重复读，幻读

3.可重复读 Repeatable read

一个事务在查询时不会出现两次查询不一致的情况。

不会出现脏读、不可重复读，会出现幻读。

它也是MySQL数据库默认的隔离级别。

4.串行化 Serializable

事务只能一个个排着队去操作同一个数据。

不会出现脏读、不可重复读，也不会出现幻读。

3.3.5 隔离级别的操作

show variables；                        ‘查看数据库中的变量’

show variables like ‘isolation’      查看隔离级别

SELECT @@GLOBAL.transaction_isolation       查看全局隔离级别

SELECT @@SESSION.transaction_isolation       查看会话隔离级别

set session transaction isolation level read UNCOMMITTED   修改会话隔离级别

3.3.6原理

MySQL InnoDB引擎下，事务的隔离级别是通过MVCC机制完成

MVCC多版本并发控制。

MVCC是在执行事务时，根据执行的时间生成一数据库的快照。

读未提交：不做任何事情

读已提交：查询数据时，只查询事务提交后的记录，未提交的事务，读取的是事务开启前的快照。

可重复读：查询数据时，只查询当前事务启动前，所有的表的快照。

串行化：不使用MVCC，给表添加读锁和写锁。

读锁：是一种共享锁，多个事务可以同时读取同一条数据

写锁：是一种排他锁，当一个事务要写某一条数据时，其他所有的事务都必须等待。

MyISAM引擎不支持事务。

3.4 视图

view

并不是一张在数据库中存在的表。

视图只是一条查询的SQL语句。他所展示的数据来源于具体的物理表。

可以像使用表一样，使用视图。

在进行增删改时有一定限制。

好处：

1. 可以简化数据库的操作
2. 可以保护数据，只给部分用户展示部分数据的权限

坏处：

1. 不能随便的增删改
2. 可能会带来更低的效率

3.5触发器

在一个表进行增insert、删delete、改update操作时，触发一个事件，

这个事件可以执行一段SQL语句。

3.5.1三种触发器

1.insert

使用new访问新插入的记录

1. delete

使用old访问刚删除的记录

3.update

使用old访问修改前的记录，

使用new访问修改后的记录

3.5.2触发器的时机

1.before

在增删改操作前，执行触发器。

2.After

再增删改操作后，执行触发器

3.5.3多条SQL

如果一个触发器中包含多条SQL，使用begin end包括起来。

create trigger 触发器名

after/before insert/delete/update

on表名

for each row

begin

SQL语句

END

3.6函数和存储过程

类似于Java中的方法

在数据库中，也可以将SQL封装起来，放在函数或存储过程中。

3.6.1函数

1.特点

传递参数，参数只能是入参。

函数只能返回简单的数据，不能返回查询结果集。

2.语法

create function 函数名(参数列表)

return 返回值类型

deterministic

begin

SQL语句

end

3.调用

select函数名(参数)

3.6.2存储过程

1.特点

参数有三种类型：in（只能输入），out（只能输出），inout（既能输入也能输出）

可以返回结果集，可以返回多个结果

2.定义

create procedure 过程名(参数列表)

Begin

SQL

end

3.调用

set @a=值；

call 存储过程名(参数列表)

3.6.3函数和存储过程的优点

1）对SQL语句进行封装从而简化对数据库的操作。

2）方便DBA与程序员配合工作。使用数据库与系统开发相分离。

３）减少客户端与数据库的网络通讯压力。

４）方便DBA与程序员配合工作。使用数据库与系统开发相分离。

第四章 JDBC

4.1什么是JDBC

Java DataBase Connection

Java连接数据库的一种技术

关系型数据库的种类很多，怎么办？

4.1.1类和接口

1.DriverManager类

驱动管理器：管理驱动程序的。

创建与数据库的连接

2.Connection接口

与数据库的连接，称为连接对象

1. Statement接口

执行SQL语句，执行对象

1. ResultSet接口

结果集，表示数据库执行的结果

4.2代码实现

4.2.1驱动程序

1）在项目下创建一个文件夹lib

2）将驱动程序copy到lib下

3）在驱动程序上右键->add as library…

4.2.2代码编写

1.加载驱动程序

Class.forName(“驱动程序名字”)

2.创建连接对象

DriverManager。getConnection(url,user,password);

MySQL连接字符串：

jdbc:mysql://主机名或IP:端口号/数据库名?参数

3.生成执行对象

conn.createStatement();

4.执行SQL语句

stmt.excuteUpdate(sql)      增删改

ResultSet rs = stmt.excuteQuery(sql) 查

1. 处理返回结果

解析ResultSet

rs.next()          指向下一条记录

rs.getXXX(字段下标/字段名)

1. 关闭访问资源

倒序

re.close()

stmt.close()

conn.close()

第五章 项目

1.用户登录

2.操作

3.技术

JavaSE

命令行

窗口化GUI，awt->swing

MySQL

5.1.2如何做一个简单的项目

软件工程

生命周期：软件从0开始，到死亡的过程。

1. 软件定义：

员工管理系统

管理员，登陆后才能使用

1. 部门管理
2. 员工管理
3. 项目管理

1. 需求分析

登录：

界面

输入账号，密码，验证码

输出成功->动作，失败->动作

员工管理系统

管理员，登陆后才能使用

账号，密码，登录，退出

1. 部门管理

CRUD操作

1. 员工管理

CRUD操作

添加员工工号不能重复

修改

查询

所有

根据工号

根据姓名模糊

1. 项目管理

CRUD操作

1. 系统设计

总体设计

模块设计

四个模块

1. 管理员 登陆退出                  admin(id,username,password,status)
2. 部门管理                              dept(id,name,room,phone,status)

CRUD

1. 员工模块                         staff(id,no,name,sex,birthday,idcard,phone,email,addr,salary,dept_id,status)        自反关联

CRUD

1. 项目模块                              project(id,name,dscp,start,end,status)

project staff(id,projet id,staff id,status)

CRUD

数据库设计

E-R图

逻辑模型，数据字典

物理模型

详细设计

每一项功能的细节进行详细的设计

项目

实体类entity

持久层dao

接口

实现类

业务层service               处理操作

接口

实现类

控制层controller          接受输入，调运业务，返回输出

视图层view

工具类

DBUtil

设计类和接口，包括属性和方法

1. 编码实现

写代码，改bug，占不超过40%

1. 软件测试

自动化测试

1. 运营与维护

保障软件正常运行，升级更新