初识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)