Java数据库

该学习中主要使用数据库：Mysql

一、概述

1.1 数据库选用

1. MySql AB公司——>Sun公司——>Oracle公司；
2. 免费开源、关系型、小巧、便捷、功能齐全、适用于小型\大型网站应用；

3. 不区分大小写；

   1.2 数据库概述

4. DataBase DB数据库作为软件体系结构中最核心的一个存在；

5. 概念 : 长期存放在计算机内,有组织,可共享的大量数据的集合；
6. 作用 : 保存,并能安全管理数据(如:增删改查等),减少冗余..

1.3数据库划分

1. 关系型数据库 ( SQL )

• MySQL , Oracle , SQL Server , SQLite , DB2 , …
• 关系型数据库通过外键关联来建立表与表之间的关系

1. 非关系型数据库 ( NOSQL )

• Redis , MongoDB , …
• 非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

二、环境安装

1. 下载地址:

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
安装配置总共分如下步骤；

1. 解压文件，并配置环境变量，在path中添加bin文件的路径。

2. 在bin同级目录下创建my.ini文件。

   [mysqld]
   basedir=D:\mysql\mysql-5.7.19\
   datadir=D:\mysql\mysql-5.7.19\data\
   port=3306
   skip-grant-tables

   注意文件中的skip-grant-tables命令用于首次登陆数据库时跳过密码登陆的过程。

3. 启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，注意切换命令cd /d D:\mysql\mysql-5.7.19\bin ，然后输入mysqld –install (安装mysql)

4. 再输入 mysqld —initialize-insecure —user=mysql 初始化数据文件
5. 然后再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）

6. 进入界面后更改root密码

   update mysql.user set authentication_string=password('密码') where 
   user='root'and Host = 'localhost';

7. flush privileges; 刷新权限，注意命令都以分号结尾。

8. 修改 my.ini文件删除最后一句skip-grant-tables
9. 重启mysql即可正常使用

   net stop mysql 关闭mysql服务 net start mysql 开启mysql服务

1. 连接上测试出现以下结果就安装好了

2. 安装mysql图形化管理界面：

• 解压Navicat文件；
• 安装文件；

• 注册并输入

  名:life404 组织:life404.cn 注册码:NAVN-2QJ2-YZA9-46N6

• 进入软件，输入mysql的用户名和密码测试连接，连接成功即可。

三、数据库基本操作

3.1 sql语言的分类

• 数据查询语言(DQL-Data Query Language) 代表关键字:select
• 数据操纵语言(DML-Data Manipulation Language)代表关键字:insert,delete,update
• 数据定义语言(DDL-Data Definition Language)代表关键字:create ,drop,alter,
• 事务控制语言(TCL-Transactional Control Language)代表关键字:commit ,rollback;
• 数据控制语言(DCL-Data Control Language)代表关键字:grant,revoke.

  3.2 表及相关操作

1. 表：一种结构化的文件，存储相关信息。
2. 表的内容：表中具有几个概念：列、行、主键。 列叫做字段(Column),行叫做表中的记录,每一个字段都有:字段名称/字段数据类型/字段约束/字段长度

3. 创建表操作：

   VACHAR是数据库中的字符串类型括号跟着字符串长度；
   CREATE TABLE EMP
    (EMPNO int(4)  not null ,
    ENAME VARCHAR(10),  
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE  DEFAULT NULL,
    SAL DOUBLE(7,2),
    COMM DOUBLE(7,2),
    primary key (EMPNO),
    DEPTNO INT(2)
    );

4. 向表添加数据：

   INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) 
    VALUES (7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19', 3000, NULL, 20);

5. 修改语句

   update emp set sal=5000.0 where ename='tom';

6. 删除语句

   delete from emp where EMPNP = 7935;

3.3 具体操作

1. 创建数据库：create database 数据库名称 ;
2. 选择要使用的数据库： use 数据库名称 ；
3. 查询数据库： select database();
4. 查询数据库版本： select version();
5. 终止一条语句如果想要终止一条正在编写的语句，可键入\c。
6. 退出mysql可使用\q、QUIT或EXIT：如：mysql> \q (ctrl+c)

3.3.1查询相关操作

简单查询：

1. 查看表的创建语句 show create table
2. 查询一个字段 select ename from emp;

3. 查询过程中附带计算：在select语句中可以使用运算符，

   select empno, ename, sal*12 from emp;<br />以上存在一些问题，年薪的字段名称不太明确

4. 给查询结果起别名 as也可以省略；

select empno as ‘员工编号’, ename as ‘员工姓名’, sal*12 as ‘年薪’ from emp;

条件查询

1. 条件查询需要用到where语句，where必须放到from语句表的后面

   支持如下运算符：

运算符	说明
=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and ….	两个值之间,等同于>= and <=
is null	为null（is not null 不为空）
and	并且
or	或者
in	包含，相当于多个or（not in不在这个范围中）
not	not可以取非，主要用在is 或in中
like	like称为模糊查询，支持%或下划线匹配 %匹配任意个字符 下划线，一个下划线只匹配一个字符

1. 相关注意操作
• 表达式的优先级，注意括号的使用：

查询薪水大于1800，并且部门代码为20或30的

错误写法： select from emp where sal > 1800 and deptno = 20 or deptno = 30; 正确写法 ： select from emp where sal > 1800 and (deptno = 20 or deptno = 30);

• in的用法：

in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些

查询出job为manager或者job为salesman的员工

3.3.2 排序

排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面；

1. 按照薪水由小到大排序(系统默认由小到大)升序

   select * from emp order by sal;

1. 取得job为MANAGER的员工，按照薪水由小到大排序(系统默认由小到大)如果包含where语句order by必须放到where后面，如果没有where语句order by放到表的后面

   select * from emp where job=’MANAGER’ order by sal;

1. 按照多个字段排序，如：首先按照job排序，再按照sal排序

   select * from emp order by job,sal;

1. 降序排列（asc是默认，desc是降序）

   手动指定按照薪水由大到小排序 select * from emp order by sal desc;

1. 使用字段的位置来排序

   按照薪水升序：select * from emp order by 6; 不建议使用此种方式，采用数字含义不明确，程序不健壮

3.3.3 分组函数/聚合函数/多行处理函数

count	取得记录数
sum	求和
avg	取平均
max	取最大的数
min	取最小的数

1. 注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。

   select count(*) from emp where xxx; 符合条件的所有记录总数。

   1. 统计所有数据；

   Count(*)表示取得所有记录，忽略null，为null的值也会取得

   1. 统计非null数据；

   select count(comm) from emp; comm这个字段中不为空的元素总数。

1. 注意：分组函数不能直接使用在where关键字后面。

   mysql> select ename,sal from emp where sal > avg(sal); ERROR 1111 (HY000): Invalid use of group function

1. 统计去重关键字

   取得工作岗位的个数 select count(distinct job ) from emp; 或 select count(distinct job) ‘岗位个数’ from emp;

3.3.4 sum

1. sum可以取得某一个列的和，null会被忽略

   select sum(sal) from emp;

1. 两列的合计sum以及出现的问题

   select sum(sal+comm) from emp;

• 从以上结果来看，不正确，原因在于comm字段有null值，所以无法计算，sum会忽略掉；
• 正确的做法是将comm字段转换成0 ，如下的判断以及转换语句

  select sum(sal+IFNULL(comm, 0)) from emp;

3.3.5 avg

取得某一列的平均值

select avg(sal) from emp;

3.3.6 max / min

取得某个一列的最大值

1. select max(sal) from emp;

2. 取得最早入职得员工（可以不使用str_to_date转换）稍微看一下格式

   select min(str_to_date(hiredate, ‘%Y-%m-%d’)) from emp;

3.3.7 组合聚合函数

可以将这些聚合函数都放到select中一起使用

select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

3.3.8 分组查询

分组查询主要涉及到两个子句，分别是：group by和having

1. group by

   取得每个工作岗位的工资合计，要求显示岗位名称和工资合计 select job, sum(sal) from emp group by job; 如果使用了order by，order by必须放到group by后面




1. having

   如果想对分组数据再进行过滤需要使用having子句；

   取得每个岗位的平均工资大于2000 select job, avg(sal) from emp group by job having avg(sal) >2000;

3.3.9 select语句总结

一个完整的select语句格式如下

select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……..

1. 首先执行where语句过滤原始数据
2. 执行group by进行分组
3. 执行having对分组数据进行操作
4. 执行select选出数据
5. 执行order by排序

能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

3.3.10 分页 limit

1. 想从哪开始查询以及查询几条数据。
2. 位置：select语句的最后面。

select from emp limit 起始索引位置，分页单位；
select from emp limit 0，3

3.4 连接查询

3.4.1 sql92语法

1. 多表联查时一定要添加条件（指定连接条件）；否则会出现笛卡尔积的错误匹配情况。

   1. 错误实例：

   select ename, dname from emp, dept;

   1. 指定连接条件情况下：
      select emp.ename, dept.dname from emp, dept

        where emp.deptno=dept.deptno;

      也可以使用别名 select e.ename, d.dname from emp e, dept d

           where    e.deptno=d.deptno;

以上查询也称为 “内连接”，只查询相等的数据（连接条件相等的数据）

1. 自连接（本表连接本表）

注意 emp e1, emp e2本表的别名创建

select e1.ename, e2.ename from emp e1, emp e2

-     where e1.mgr=e2.empno;

以上称为“自连接”，只有一张表连接，具体的查询方法，把一张表看作两张表即可，如以上示例：第一个表emp e代码了员工表，emp m代表了领导表，相当于员工表和部门表一样

3.4.2 SQL99语法

1. Sql92语法和sql99语法的区别：99语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比sql92更清晰；当查询多张表时（3张及以上），SQL92语法的查询方式就显得效率低下了，需要使用SQL99语法。

2. 内连接

   select e.ename, e.sal, d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000;

或 select e.ename, e.sal, d.dname from emp e inner join dept d on e.deptno=d.deptno where e.sal>2000; 在实际中一般不加inner关键字

观察可知on后面跟的是条件，where后也可以跟条件，相对于SQL92语法来说条件分开更清晰一些。

1. 外连接

   在使用内连接时，为空字段是不参与查询的，因此要解决该问题需要使用到外连接。

• 左外连接：
  table1 （主表） left join table2 （从表）

主表的内容需要全部显示出来即使那个字段为空也会全部显示出来。

select e1.ename ,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;

• 右外连接：

  table1 （从表） right join table2 （主表）

3.4.3 案例 (三表联查)：

需求：涉及三张表的查询操做：查询所有的员工薪水、薪水等级、部门名称，根据等级精选降序排序。

思路：1. 三表查询需要使用SQL99语法；

       2. 先两张表的联合查询，查询结果再与第三张表联合查询。

代码： select e.ename ‘员工姓名’, d.dname ‘部门名称’,e.sal ‘薪水’,s.grade ‘薪水等级’ from emp e left join dept d on e.deptno = d.deptno left join salgrade s on e.sal between s.losal and s.hisal order by s.grade desc;

3.5 子查询

1. 子查询就是嵌套的select语句，可以理解为子查询是一张表
2. 在where语句中使用子查询，也就是在where语句中加入select语句

   select empno, ename from emp where empno in (select mgr from emp where mgr is not null);