初识 Mysql 数据库

一般来说 java EE 分为三部分：
前端 后端 数据库
也就是表现层 业务逻辑层 数据访问层

  • 表现层：展现给用户的界面
  • 业务逻辑层：针对具体问题操作，对数据层操作，对数据业务逻辑处理
  • 数据访问层：直接操作数据，crud;

什么是数据库

数据库（DataBase、DB）
是一个数据仓库，用于存储数据

DBMS

关系型数据库 （SQL）:行和列

  • Mysql，Oracl，Sql Server
  • 通过表和表之间，行和列之间关系进行数据的存储

非关系型数据库 （No SQL）：{key : value}

  • Redis。MongDB
  • 对象存储，通过对象的自身的属性来决定

==DBMS(数据库管理软件）==

  • 可以科学有效管理我们的数据，维护和获取数据
  • Mysql 就是一个 DBMS

数据库中语言

数据库中的语言

  • DDL [Data Mefinition Language] 数据定义语言
  • DML [Data Manipulation Language]　 数据操纵语言
  • DQL [Data Query Language ] 数据查询语言
  • DCL [Data Control Language] 数据控制语言

1、DDL 数据定义

关键点：CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE/DROP INDEX 等

主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DDL 操作是隐性提交的！不能 rollback 。

2、DML 数据操纵

关键点：INSERT、UPDATE、DELETE

主要是对表数据进行操作，如对表数据进行增、删、改。

3、DCL 数据控制

关键点：GRANT、REVOKE、ROLLBACK、COMMIT

主要用来授予或回收访问数据库的某种特权，并控制**数据库操纵事务发生的时间及效果，对数据库实行监视等。**

如：

用户授权 GRANT

**ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。

**

**回滚—ROLLBACK

**

**回滚命令使数据库状态回到上次最后提交的状态。其格式为：

**

SQL>ROLLBACK;

COMMIT [WORK]：提交事务

对数据进行增、删、改操作时，只有当事务在提交到数据库时才算完成。

在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。

提交数据有三种类型：显式提交、隐式提交和自动提交。

  1. 显式提交
    用 COMMIT 命令直接完成的提交为显式提交。其格式为：
1
SQL>COMMIT；

(2) 隐式提交
用 SQL 命令间接完成的提交为隐式提交。这些命令是：

1
2
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自动提交
若把 AUTOCOMMIT 设置为 ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为：

1
2
3
SQL>SET AUTOCOMMIT ON；开启

SQL>SET AUTOCOMMIT OFF；关闭

在实际开发中，创建事务的时候需要关闭自动提交（默认是开启的），等操作成功全部成功后显性提交事务。事务提交完毕后重新开启自动提交。

4、DQL 数据查询

关键点：SELECT

DQL 基本结构是由 SELECT 语句，FROM 语句，WHERE 语句组成的查询块：

基本语法：SELECT <字段名表> FROM <表或视图名> WHERE <查询条件>

数据库列类型

数值

数值类型分为准确型和近似型两种

==准确型==:指在计算机中能精确存储的数据

==近似型==:指近似的数值数据类型

  • tinyint 十分小的数据 只占一个字节
  • smallint 较小的数据 占两个字节
  • mediumint 在 small 和标准之间的数据 占 3 个字节
  • int 标准的整数 占 4 个字节==常用==
  • bigint 较大的数字 8 个字节 →java 里的 long
  • float 单精度浮点数 4 个字节
  • double 多精度浮点数
  • decimal 字符串形式的浮点数 金融计算的时候使用这个

文本类型

文本类型分为普通字符编码和 Unicode 字符编码

==普通字符编码==：每个英文字符占一个字节存储空间，每个汉字占两个字节存储空间

==Unicode 字符编码==：每个英文字符和汉字都占两个字节存储空间

  • char 固定大小的字符串 0-255
  • varchar 可变字符串 0-65535 → java 里的 String， 变量用这个
  • tinytext 微型文本 2^8 - 1
  • text 文本串 2^31- 1 保存大文本
数据类型 占用字节数 说明
char（普通字符编码 n 决定 char[(n)]固定长度，非 Unicode 字符串数据。n 用于定义字符串长度，并且它必须为 1 到 8000 之间的值，存储大小为 n 字节
varchar（普通字符编码） n 决定 varchar[(n)]可变长度，非 Unicode 字符串数据，n 用于定义字符串长度，并且它可以为 1 到 8000 之间的值
text（普通字符编码） 服务器代码页中长度可变的非 Unicode 数据，字符串最大长度为 2^31 - 1（2,147,483,647）个字节
nchar n 决定 nchar[(n)]存储固定长度的 Unicode 字符串数据。n 用于定义字符串长度，并且它必须为 1-4000 之间的值，存储大小为 n 字节的两倍
nvarchar n 决定 nvarchar[(n)]存储可变长度的 Unicode 字符串数据。u 用于定义字符串长度，并且它可以为 1-4000 的值
ntext 长度可变的 Unicode 数据，字符串最大长度是 2^30 - 1（1,073,741,823）个字节，存储大小是所输入字符串长度的两倍（以字节为单位）

时间日期

java.util.Date

数据类型 占用字节数 说明
date 3 字节 YYYY-MM-DD 日期格式；从 0001-01-01 到 9999-12-31
time 5 字节 HH：mm：ss 时间格式；从 00:00:00:0000000 到 23:59:59:9999999
datetime 8 字节 YYYY-MM-DD HH：mm：ss 时间格式；日期范围从 1753 年 1 月 1 日 到 9999 年 12 月 31 日；时间范围从 00:00:00 到 23:59:59:997
timestamp 8 字节 是指格林威治时间 1970 年 01 月 01 日 bai00 时 00 分 00 秒(北京时间 1970 年 01 月 01 日 08 时 00 分 00 秒)起至现在的总秒数。
smalldatetime 4 字节 日期范围:1900-01-01 到 2079-06-06 ;时间范围：00:00:00 到 23:59:59

null

  • 没有值 未知

数据库的字段属性

==Unsigned==

  • 无符号的整数
  • 声明该列不能为负数

==zerofill==

  • 0 填充
  • 不足的位数用 0 去填充：比如说规定了三个位数 我写了个 1 他会填充成 001

==自增（auto-increment）==

  • 自动在上一条记录基础上 +1
  • 通常用来设计唯一的主键 可以理解成 index（索引） 那么此时主键是 int 型
  • 可以自定义设计主键的自增的起始值和步长（增量）

==非空==

  • 假设设置为 非空，那么如果不赋值就会报错
  • 如果不勾选非空，假如不赋值就是==null==

==默认==

  • 设置默认的值
  • 假如不指定该列的值，就是默认值

==extra==

image-20201126004651528

连接查询

连接查询主要指的是使用join关键字，将不同的表进行连接，由于做项目的时候，写 sql 语句经常会用到 join，但分不清该用哪个 join，于是专门整理一下

我们首先创建两个表：

image-20210905173834993

内连接 - inner join

内连接返回的结果就是两个集合的【交集】

内连接

  • *隐式内连接 *

要使用内连接未必一定要用 inner join 语句，使用= < > where都算作内连接

  • 显示内连接

使用显示连接就是用上了关键字：inner join on

  • 内连接例子

对于一开始的 a，b 表，我们返回的结果就应该是：

内连接例子

左连接 - left join

左连接的返回结果是left join语句中指定的左表的所有行（不管右表中有没有匹配到）。如果没匹配到，则结果集中右表的的选择列表中都是空值

左连接

  • 关键字

LEFT JOIN 或 LEFT OUTER JOIN

  • 左连接例子

左连接例子

右连接 - right join

右连接的返回结果是right join语句中指定的右表的所有行（不管左表中有没有匹配到）。如果没匹配到，则结果集中左表的的选择列表中都是空值

右连接

  • 关键字

RIGHT JOIN 或 RIGHT OUTER JOIN

  • 右连接例子

image-20210905174756994

全连接

全连接 = 左连接 + 右连接，返回左表和右表的所有行，如果某行在另一个表中没匹配时，则选择列表为空值

  • 关键字

FULL JOIN 或 FULL OUTER JOIN

  • 全连接例子

全连接例子

基本命令行操作

启用/停用 Mysql

1
net start mysql -- 启动mysql服务
1
net stop mysql -- 停止mysql服务

连接数据库

  • 如果你使用的是命令行窗口，连接到数据库只需要如下命令即可，前提是你是连接的本地数据库并且端口号是默认的 3306
1
mysql -u root -p --连接数据库
  • 如果不是：
1
2
mysql -uroot -p3306 -hlocalhost -proot
# mysql -u账户 -p端口号 -h主机地址 -p密码

创建，显示，使用，删除数据库

  • 创建数据库
1
2
3
CREATE DATABASE xxx;
-- 创建xxx数据库并指定编码
CREATE DATABASE 数据库名 charset=utf8;
  • 显示数据库或相关信息
1
2
3
4
5
6
7
8
-- 显示所有数据库
SHOW DATABASES;

-- 查看创建数据库的语句
SHOW CREATE DATABASE 数据库名

-- 显示数据库版本(记得加;
SELECT version();
  • 使用特定的数据库
1
USE database(数据库名);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 数据库的基本操作

-- 显示数据库版本(记得加;
select version();

-- 显示当前的时间
select now();

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

-- 创建数据库
create database 数据库名 charset=utf8;
-- 创建xxx数据库
create database xxx;
-- 创建xxx数据库并指定编码
create database xxx charset=utf8;

-- 查看创建数据库的语句
show create database school
  • 删除数据库
1
2
3
4
--删除数据库
DROP DATABASE xxxx;

DROP DATABASE [IF EXISTS] database_name;

表相关操作

创建，展示表

  • 创建表
1
2
3
4
5
6
7
CREATE TABLE student(
    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
    s_birthday DATETIME COMMENT'学生生日',
    s_class VARCHAR(20) COMMENT'学生所在的班级'
);
  • 显示表的列
1
2
3
4
DESCRIBE 表名;

-- 它会对每个字段返回一行，其中包含字段名、数据类型、是否可以为NULL、Key列的键的类型、默认值、其他信息
show columns from 表名;
  • 查看数据库中的表
1
SHOW tables;
  • 其他 show 命令
1
2
3
4
5
6
7
8
9
10
11
# 用户显示广泛的服务器状态信息，会有很多输出
show status;
# 显示创建特定数据库和表的MySQL语句
show create database 数据库名字;
show create table 表名字;
# 显示授予用户（所有用户和特定用户）的安全权限
show grants;
# 显示服务器错误信息和警告信息
show errors;
show warnings;
# 进一步了解SHOW 请在mysql命令行实用程序中，执行命令HELP SHOW;显示允许的SHOW语句。

数据库检索语句

  • select 关键字，在 test 表中查询列名为 username 的所有的值
  • 注意：SQL 语句不区分大小写，下面的两行操作的结果是一样的
  • 建议：关键字大写，非关键字小写，便于区分
1
2
select username from test;
SELECT username FROM test;

Mysql - 编写练习

有一个员工 employees 表简况如下:

img

  • SQL1 查找最晚入职员工所有信息（select 嵌套查询 + max 函数应用）
1
2
3
4
select * from employees
where
hire_date = (
select max(hire_date) from employees)

  • SQL2 查找入职员工时间排名倒数第三的员工所有信息（select 嵌套查询 + order by 倒序 + limit 限制）

    limit 后如果是两个参数 a，b：表示从第 a 项开始查 b 个数据

1
2
3
4
select *from employees
WHERE
hire_date = (
    select hire_date from employees order by hire_date desc limit 2, 1)

-