关系型数据库

1. 技术目标

• 目标01：MySQL数据
• 目标02：Oracle数据

2. 技术介绍

本篇文章引导你通过对非关系型数据库中最为常见的数据库基本操作进行统一的整理。同时介绍几种操作数据库的可视化操作工具。

3. 环境配置

3.1 开发配置

• MySQL服务
• Oracle服务
• 连接工具Navicat、DataGrip

3.2 技术栈

• DDL
• DML
• DCL

4. 技术实践

4.1 MySQL数据库

4.1.1 MySQL的安装

1. 安装版

   直接下载安装包，直接下一步安装即可！此处重点说下绿色版的安装过程。

2. 绿色版

1.下载：https://dev.mysql.com/downloads/mysql/
2.创建data文件夹
3.创建my-ini配置文件
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3306端口
port=3307
# 设置mysql的安装目录
basedir=E:/dev/soft/MySQL-5.7.19/mysql-5.7.19-winx64/
# 设置mysql数据库的数据的存放目录
datadir=E:/dev/soft/MySQL-5.7.19/mysql-5.7.19-winx64/data/
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 免密码登陆
skip-grant-tables
  
书写脚本：
1.注册数据库服务
E:\dev\soft\MySQL-5.7.19\mysql-5.7.19-winx64\bin\mysqld.exe --install MySQL_OpenJWeb
echo 执行完毕!
pause
2.卸载数据库服务
E:\dev\soft\MySQL-5.7.19\mysql-5.7.19-winx64\bin\mysqld.exe -remove mysql_OpenJWeb
echo 执行完毕!
pause
3.启动数据据库
NET START MySQL_OpenJWeb
echo 执行完毕!
pause
3.停止数据据库
NET STOP MySQL_OpenJWeb
echo 执行完毕!
pause

4.1.2 MySQL的连接

1. Navicat可视化工具连接

   [图片上传失败...(image-65173e-1524742178166)]

2. DataGrip可视化工具连接

[图片上传失败...(image-fd7e21-1524742178166)]

4.2.3 MySQL的配置

第一部分：代码实现

public class MysqlConnection {
Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    private static final String JDRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://127.0.0.1:3333/student";
    private static final String UNAME = "root";
    private static final String PWD = "mysql";
    // 通过静态代码块实现
    static {
        try {
            // 1.加载驱动程序
            Class.forName(JDRIVER);
            System.out.println("驱动加载成功！");
            DriverManager.getConnection(URL, UNAME, PWD);
            System.out.println("连接成功！");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

第二部分：配置实现

spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=123654
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

server:
  port: 7103
spring:
  datasource:
    url: jdbc:mysql://10.9.254.173:3306/invoice?useOldAliasMetadataBehavior=true&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
    username: root
    password: passw0rd

4.1.4 MySQL基本操作

第一部分：DDL语法

创建数据库
create database dbName charset utf8;
进入你选择的库：
use databaseName;
设置编码：
set names gbk;
创建数据库表
create table student(id int,name varchar(20),age int);
create table guo(id int primary key,name varchar(20),age int);（注意添加主键）
create table guo(id int primary key not null AUTO_INCREMENT,name varchar(36),age INT);（主键非空自增）
create table guo(id int primary key not null AUTO_INCREMENT comment '字段注解',name varchar(36) comment '字段注解',age INT comment '字段注解') comment '表注解';（主键非空自增，添加注解）
删除数据库
Drop database dbName;
修改数据库：
Alter datebase db_name;
查看当前数据库服务下的库：
Show databases;
查看当前库中的表：
show tables;
删除表：
drop table table_name；
查看表的结构：
desc tableName;
添加或删除表的字段：
alter table table_name add column ages int;
alter table tb_user drop column ages
查看创建表的sql语句
> show create table tableName;
清空表的数据
Truncate tableName;
更改表名
Rename table oldtable to newtable;

CREATE TABLE guo (
    id VARCHAR (36) PRIMARY KEY NOT NULL COMMENT '主键ID，看情况使用，如果是数量比较多最好不要使用自增',
    name VARCHAR (36) COMMENT '普通字符',
    age INT COMMENT '字段注解',
    price DECIMAL (8, 2) NOT NULL COMMENT '金钱格式',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
) COMMENT '表注解';

第二部分：DML语法

增：insert into users(userid, username, password) values(1, 'mysql', 'mysql');
删：delete from users where userid = 1;
改：update users set password = 'password' where userid = 1;
查：select userid, username, password from users;
过滤查：select distinct id from a

第三部分：查询模型

where：（in&not in、between and、like、is null）
group by：（max，min，sum，avg，count）
having：（磁盘原始数据使用where，内存中的结果集having；having和where的区别）
order by：（升序排列：asc默认的，降序排列：desc）
limit：（limit（偏移量，限制条目），当偏移量为0时，可以省略偏移量！）
例子：
select * from goods where shop_price in(100,12222);
select * from goods where shop_price between 12 and 66666;
select * from goods where goods_name like '诺基亚%';
select * from goods group by cat_id;
select (market_price-shop_price) as guo from goods having guo>100;
select * from goods order by shop_price limit 3;
select * from goods order by shop_price limit 0,3;
MySQL分页公式：select * from goods limit (pageNo-1)*pageRows,pageRows
注意：列是变量，可以放入函数中（大胆的把列看成变量，变量可以计算，可以放入函数中）；投影运算：本有三列，但是取出来两列；广义投影：通过投影的数据进行运算操作得到的列；As：将广义投影给一个新的变量。

第四部分：子查询

where子查询：（内层的查询结果作为外层sql的比较条件！）
from子查询：（把子查询的结果作为一个表，供你的外边的查询语句使用）
exists子查询：（子查询实际上不产生任何数据，它只返回 ture 或 false 值）
比较子查询：
Select子查询：
例子：
SELECT * FROM goods WHERE goods_id IN (SELECT max(goods_id) FROM goods GROUP BY cat_id);
select goods_name from goods where goods_id=(select max(goods_id) from goods);
select * from(select goods_name,goods_id,shop_price,cat_id from goods) as t group by cat_id;
select * from(select num from a) as t where t.num =(select max(num) from a)
select * from category where exists (select * from goods where goods.cat_id=category.cat_id);
select 学号,姓名,专业名,出生日期 from xs where 出生日期 < all(select 出生日期 from xs where 专业名='计算机');

第五部分：连接查询（多表查询）

语法：Select xxxx from Table1 连接查询的类型 table2 on table1xx=table2xx;
左连接：（以左边的数据为主进行查询，右表进行配对，没有的补null）永远以左表为准
右连接：（以右边的表为主要的查询，左边进行配对，没有的补null）
内连接：（以左右数据表查询，没有的不需要补null）
外连接：（MySQL不支持，oracle支持）
左外连接：（进行两张表中的人员配对？（boy表和gril表））
多表关联：
练习：
select boy.hid,boy.bname,girl.hid,girl.gname from boy right join girl on boy.hid=girl.hid;
select boy.hid,boy.bname,girl.hid,girl.gname from boy left join girl on boy.hid=girl.hid;
select boy.hid,boy.bname,girl.hid,girl.gname from boy inner join girl on boy.hid=girl.hid;

第六部分：Union&&Union all查询

Union查询是：把两条或多条sql的查询结果合并成一个结果集。
select * from boy union select * from girl;
注意：
> 1.    满足的条件，各语句取出来的列要相同。
> 2.    使用union的，完全相同的列，将会被合并。Union all 可以不合并。
> 3.    在union的子句中不使用order by，sql合并后得到总的结果集排序。

第七部分：视图操作

视图的创建
第一类：create view v as select * from table;
第二类：create view v as select id,name,age from table;
第三类：create view v[vid,vname,vage] as select id,name,age from table;
查看视图信息：describe v
查看视图基本信息：show table status like 'v'
view的algorithm
视图DML操作
创建视图：create view v as select * from sun;
查看视图：select * from v;
更改视图：update student_view set name= "guo" where id = '1';
删除视图：drop view if exists student_view;

第八部分：索引

MVI：索引文件
索引是数据的目录，能快速定位数据的位置
Key：普通索引（提高查询速度）
primary key：主键索引
unique key：唯一索引（提高查询速度、约束作用）
fulltext：全文索引（中文环境下，全文索引无效，要分词+索引。
一般使用第三方解决方案：sphinx）
索引可以提高查询的速度，但是增删改查的速度变慢
一般在查询频繁的地方加索引
索引的长度：建索引的时候，
多列索引：把两列或多列看成一个整体，然后建索引
冗余索引：在某个列上存在多个索引
Explain 
操作索引：
查看：show index from tableName;
删除：alter table tableName drop index indexName;
增加（普通的）：alter table tableName add index indexName;
增加主键索引：alter table tableName add primary key（列名）；
删除主键索引：alter table tableName drop primary key；

4.2 Oracle数据库

4.2.1 Oracle的安装

此处暂时省略

4.2.2 Oracle的连接

此处暂时省略

4.2.3 Oracle的配置

第一部分：代码实现

public class OracleConnection {
    public static void main(String[] args) {
        String DRIVER = "oracle.jdbc.driver.OracleDriver";
        String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
        String USER = "system";
        String PASSWORD = "123654";
        Connection con = null;
        try {
            Class.forName(DRIVER);
            System.out.println("驱动加载成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection(URL, USER, PASSWORD);
            System.out.println("数据库连接成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

第二部分：配置实现

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:shopxx
spring.datasource.username=root
spring.datasource.password=123654
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

server:
  port: 7103
spring:
  datasource:
    url: jdbc:oracle:thin:@//10.248.1.15:1521/etcextdb
    username: invoice_plat
    password: nvoice@9618968

4.2.4 Oracle基本操作

可以参考MySQL的基本操作语法

第一部分：时间点查询

SELECT *
FROM TB_BILL_INFO a
WHERE trunc(a.CREATE_TIME, 'mm') = to_date('2018-04', 'yyyy-mm');

第二部分：时间区间的查询

SELECT *
FROM TB_BILL_INFO a
WHERE trunc(a.CREATE_TIME, 'mm') >= to_date('2018-05', 'yyyy-mm') AND
      trunc(a.CREATE_TIME, 'mm') <= to_date('2018-06', 'yyyy-mm');

第三部分：分组查询和MySQL不同

SELECT a.BILL_TYPE
FROM TB_BILL_INFO a
GROUP BY a.BILL_TYPE;

第四部分：分组查询并根据某个字段计算和

SELECT
  a.BILL_TYPE,
  sum(a.PRICE_SUM) AS PRICE_SUM,
  sum(CASE WHEN a.BILL_TYPE = 1 THEN a.PRICE_SUM ELSE 0 END) AS PRICE_SUM,
  sum(CASE WHEN a.BILL_TYPE = 1 THEN a.TAX_SUM ELSE 0 END)  AS TAX_SUM
FROM TB_BILL_INFO a
GROUP BY a.BILL_TYPE;

第五部分：时间精度指定返回

SELECT
  a.ID,
  TO_CHAR(a.Create_Time, 'yyyy-MM-dd')
FROM TB_BILL_INFO a;

第六部分：Oracle的分页查询

 Oracle分页公式：SELECT * FROM (SELECT a.*, rownum rn FROM (SELECT * FROM TB_BILL_INFO) a WHERE rownum < page*rows+1) WHERE rn > (page-1)*rows;

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TB_BILL_INFO) A
WHERE ROWNUM < 22
)
WHERE RN > 20;

5. 技术总结

5.1

6. 代码下载

本章以及后续章节的源码地址我都会分享出来，请大家自行下载以及git clone。
SpringBoot相关系列文章请访问：目录：SpringBoot学习目录