1 引言
1.1 现有的数据存储方式
- Java程序存储数据(变量、对象、数组、集合),数据保存在内存中,属于瞬时状态存储。
- 文件(File)存储数据,保存在硬盘上,属于持久状态存储。
1.2 以上存储方式存在的缺点
- 没有数据类型的区分。
- 存储数据量级较小。
- 没有访问安全限制。
- 没有备份、恢复机制。
2 数据库
2.1 概念
数据库是“按照数据结构来组织、存储和管理数据的仓库。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。
2.2 数据库分类
- 网状结构数据库:美国通用电气公司IDS(Integrated Data Store),以节点形式存储和访问。
- 层次结构数据库:IBM公司IMS(Information Management System)定向有序的树状结构实现存储和访问。
- 关系结构数据库:Oracle、DB2、MySQL、SQL Server,以表格(Table)存储,多表间建立关联关系,通过分类、合并、连接、选取等运算实现访问。
- 非关系型数据库:ElastecSearch、MongoDB、Redis,多数使用哈希表,表中以键值(key-value)的方式实现特定的键和一个指针指向的特定数据。
3 数据库管理系统【了解】
3.1 概念
数据库管理系统(DataBase Management System,DBMS):指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。
3.2 常见的数据库系统
- Oracle:被认为是业界目前比较成功的关系型数据库管理系统。Oracle数据库可以运行在UNIX、Windows等主流操作系统平台,完全支持所有的工业标准,并获得最高级别的ISO标准安全性认证。
- DB2:IBM公司的产品,DB2数据库系统采用多进程多线索体系结构,其功能足以满足大中公司的需要,并可灵活地服务于中小型电子商务解决方案。
- SQL Server:Microsoft 公司推出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点。
- SQLLite:应用在手机端的数据库。
4 MySQL【了解】
4.1 简介
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS(Relational Database Management System,关系数据库管理系统) 应用软件之一。
4.2 下载与安装【重要】
下载地址:https://dev.mysql.com/downloads/mysql/
安装详看安装文档
4.3 配置环境变量【重要】
5 SQL语言
5.1 概念
SQL(Structured Query Language)结构化查询语言,用于存取数据、更新、查询和管理关系数据库系统的程序设计语言。
经验:通常执行对数据库的“增删改查”,简称C(Create)R(Read)U(Update)D(Delete)。
5.2 数据库操作命令【了解】
-- 单行注释 # 单行注释 /* 多行注释 */ -- 数据操作命令 -- 连接数据库 -- mysql -u用户名 -p用户密码 -h主机ip地址 mysql -uroot -p123456 -- 断开连接 quit; -- 查看所有数据库 show databases; -- 创键数据库 -- create database 数据库名 create database db001; -- create database 数据库名 character set 编码名称 create database db002 character set gbk; -- create database if not exists 数据库名 create database if not exists db001; -- 查看数据库的创建信息 -- show create database 数据库名 show create database db001; -- 删除数据库 -- drop database 数据库名; drop database db001; -- drop database if exists 数据库名 drop database if exists db001; -- 修改数据库 -- alter database 数据库名 character set 新的编码 alter database db002 character set utf8; -- 查看当前使用的数据库 select database(); -- 使用数据库 -- use 数据库名 use db002;
6 客户端工具
6.1 Navicat
**Navicat是一套快速、可靠并价格相宜的数据库管理工具,专为简化数据库的管理及降低系统管理成本而设。**它的设计符合数据库管理员、开发人员及中小企业的需要。Navicat 是以直觉化的图形用户界面而建的,让你可以以安全并且简单的方式创建、组织、访问并共用信息。
6.2 SQLYog
**MySQL可能是世界上最流行的开源数据库引擎,但是使用基于文本的工具和配置文件可能很难进行管理。**SQLyog提供了完整的图形界面,即使初学者也可以轻松使用MySQL的强大功能。其拥有广泛的预定义工具和查询、友好的视觉界面、类似 Excel 的查询结果编辑界面等优点。
6.3 Navicat的安装和使用
这部分另出教程
7 数据查询【重要】
7.1 数据库表的基本结构
关系结构数据库是以表格(Table)进行数据存储,表格由“行”和“列”组成
经验:执行查询语句返回的结果集是一张虚拟表。
- 一行:一条记录
- 一列:一个字段
*表示所有列,不推荐用,数据达到百万级的时候会造成索引失效,效率会变低
1 表示
eg :
select coun(1)from 表名;
7.2 基本查询
语法:SELECT 列名 FROM 表名
-- 基本查询 -- 查询 select 列名,...| * from 表名 -- 1、查询员工姓名和工资 select last_name,salary from t_employees; -- 2、查询员工的所有信息 *表示所有的列(在实际开发中不能使用*) select * from t_employees; -- 在基本查询中可以进行四则运算 -- 1、查询所有员工的年薪(月薪*12) select last_name,salary*12 from t_employees; -- 2、查询所有员工的月薪(扣除社保-800) select last_name,salary-800 from t_employees -- 3、查询所有员工的月薪(扣除社保-800+绩效) -- ifnull(判断的字段值,如果为null的结果) select last_name,salary*IFNULL(commission_pct,0)+salary-800 from t_employees -- 4、查询所有的员工的时薪 select last_name,salary/22/8 from t_employees; -- 注意:mysql中%有别的含义,不能在四则中使用
7.3 别名查询
-- 别名查询 -- 一般用于列名和表名。1、当列名过长或者多表的字段名重复 2、表名使用别名进行区分 -- 语法: select 列名 as 别名,..... from 表名; --as是可以省略不写 select first_name as '名',last_name as '姓' from t_employees; select first_name fn,last_name ln from t_employees; select last_name,salary*IFNULL(commission_pct,0)+salary-800 salary from t_employees select x* from t_employees emp;
7.4 去重查询
-- 去重查询 -- 语法:select distinct 去重字段 from 表名 -- 查询所有的部门 (按照一个字段进行去重) select distinct department_id from t_employees; -- 按照工资、部门、职位 (按照多个字段进行去重) select DISTINCT salary,department_id,job_id from t_employees;
7.5 排序查询
-- 排序查询 -- 语法:select 列名,....|* from 表名 order by 排序字段 排序的规则 -- 语法:select 列名,....|* from 表名 order by 排序字段 排序的规则,排序字段 排序的规则,... -- 排序规则: asc 升序(默认) desc 降序 -- 1、按照员工工资进行降序排序 select last_name,salary from t_employees order by salary desc; select last_name,salary from t_employees order by salary; -- 2、按照员工工资进行降序排序,如果工资相等按照入职日期升序排序 select last_name,salary,hire_date from t_employees order by salary desc, hire_date;
7.6 条件查询
语法: select * from 表名 where 查询条件
-- 条件查询 -- 语法: select 列名,...|* from 表名 where 过滤条件 /* 关系条件: > < >= <= = != <> 逻辑条件: and or not 区间条件: (包含起始值和结束值) between 起始值 and 结束值 枚举条件 in(值1,值2,...) not in(值1,值2,...) 非空条件 (sql中null的不能使用=或者!=进行判断) is null is not null 模糊条件 like not like 占位符: %: 表示0个或者多个字符 _:表示一个字符 */
-- 1、查询工资大于10000的员工信息 select * from t_employees where salary > 10000 -- 2、查询不是销售的员工信息 select * from t_employees where job_id <> "sa_man" -- 3、查询steven的员工信息 select * from t_employees where first_name = 'steven'; -- 4、查询1998年以后入职,且工资大于8000的员工信息 select * from t_employees where hire_date > '1998-01-01' and salary > 8000; -- 5、查询不是30部门的员工信息 select * from t_employees where not department_id = 30 -- 6、查询50、60、90部门的员工信息 select * from t_employees where department_id = 50 or department_id = 60 or department_id = 90 -- 7、查询工资在8000到12000的员工信息 select * from t_employees where salary between 8000 and 12000; select * from t_employees where salary >= 8000 and salary <= 12000; -- 8、查询30、40、70部门的员工信息 select * from t_employees where department_id in(30,40,70) -- 9、查询没有绩效的员工信息 select * from t_employees where commission_pct = null; select * from t_employees where commission_pct is null; select * from t_employees where not commission_pct is not null; -- 10、查询有绩效的员工信息 select * from t_employees where commission_pct is not null; # 模糊查询 -- 11、查询last_name名字中带'en'的员工 select * from t_employees where last_name like '%en%' -- 12、查询last_name中以'La'的员工信息 select * from t_employees where last_name like 'la%' -- 13、查询last_name中第二个和第三个字符为'in'的员工信息 select * from t_employees where last_name like '_in%' -- 14、查询last_name中带's'且名称长度大于6 select * from t_employees where last_name like '%s%' and last_name like '%_______%'
7.7 分支查询
-- 分支结构查询 /* case when 条件1 then 结果1 when 条件2 then 结果2 when 条件3 then 结果3 else 结果4 end */ -- 15、查询员工工资,按照工资分等级,A、B、C、D、E select salary, case when salary > 12000 then 'A类' when salary > 8000 then 'B类' when salary > 5000 then 'C类' when salary > 3000 then 'D类' else 'E类' end from t_employees
7.8 时间函数
语法:SELECT 时间函数([参数列表])
经验:执行时间函数查询,会自动生成一张虚表(一行一列)
时间函数 | 描述 |
SYSDATE() | 当前系统时间(日、月、年、时、分、秒) |
CURDATE() | 获取当前日期 |
CURTIME() | 获取当前时间 |
WEEK(DATE) | 获取指定日期为一年中的第几周 |
YEAR(DATE) | 获取指定日期的年份 |
HOUR(TIME) | 获取指定时间的小时值 |
MINUTE(TIME) | 获取时间的分钟值 |
DATEDIFF(DATE1,DATE2) | 获取DATE1 和 DATE2 之间相隔的天数 |
ADDDATE(DATE,N) | 计算DATE 加上 N 天后的日期 |
-- 日期函数 -- 获取系统当前日期 select sysdate(); -- 获取系统当前日期 select now(); -- 获取系统当前日期(不包含时间) select curdate(); -- 获取系统当前时间 select curtime(); -- 获取指定日期中的年份 select year('2021-09-15'); -- 计算两个日期相隔天数 select datediff('2021-09-15','2021-06-10'); -- 在指定的日期基础上添加指定的天数 select ADDDATE(now(),10);
7.9 字符串函数
语法: SELECT 字符串函数 ([参数列表])
字符串函数 | 说明 |
CONCAT(str1,str2,str…) | 将 多个字符串连接 |
INSERT(str,pos,len,newStr) | 将str 中指定 pos 位置开始 len 长度的内容替换为 newStr |
LOWER(str) | 将指定字符串转换为小写 |
UPPER(str) | 将指定字符串转换为大写 |
SUBSTRING(str,num,len) | 将str 字符串指定num位置开始截取 len 个内容 |
-- 字符串函数 select 'hello' + 'world'; -- 错误 -- concat 函数拼接字符串 select concat('hello','world'); select concat('%','手机','%'); -- 字符串替换函数 参数1:原始字符串 参数2:起始位置(从1开始) 参数3:替换的长度 参数4:替换的内容 select insert('helloworld',6,5,'mysql') select lower('HELLO'); select upper('hello'); -- 字符串截取函数 参数1:原始字符串,参数2:起始位置 参数3:截取的长度 select substring('hello,java123',7,4);
7.10 聚合函数
- 一般做统计使用
语法:SELECT 聚合函数(列名) FROM 表名;
经验:对多条数据的单列进行统计,返回统计后的一行结果。
聚合函数 | 说明 |
SUM() | 求所有行中单列结果的总和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
COUNT() | 求总行数 |
-- 聚合函数 -- sum函数求和 select sum(salary+salary*ifnull(commission_pct,0)) from t_employees; -- max函数求最大值 select max(salary) from t_employees; -- min函数求最小值 select min(salary) from t_employees; -- avg函数求平均值 select avg(salary) from t_employees; -- count函数求个数 *表示所有列 count函数只会统计不为null的数据 select count(*) from t_employees; select count(1) from t_employees; select count(commission_pct) from t_employees;
7.11 分组查询
语法:SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组字段 [having 分组过滤条件];
-- 分组查询 -- 语法:select 列名,..|* from 表名 group by 分组字段 -- 语法:select 列名,..|* from 表名 group by 分组字段 having 分组过滤条件 -- 分组查询之后只能查询分组字符和使用聚合函数,即不能查询分组字段之外的字段 -- 1、查询每个部门的平均工资 select department_id,avg(salary) from t_employees group by department_id; -- 2、查询平均工资大于9000的部门信息 -- select department_id,avg(salary) from t_employees where avg(salary) >9000 group by department_id; select department_id,avg(salary) from t_employees group by department_id having avg(salary) >9000; -- 3、查询工资大于9000的员工部门的平均工资 select department_id,avg(salary) from t_employees where salary >9000 group by department_id;
注意:分组查询之后只能查询分组字符和使用聚合函数,即:不能查询分组字段之外的字段(没有意义)
where 和 having 的区别
- 都是用于条件过滤
- where中不能使用聚合函数,而having中可以
- where是在分组之前过滤,而having分组之后过滤
- where可以单独使用,having必须要在分组从句中使用
7.12 分页查询
SELECT 列名 FROM 表名 LIMIT 起始行,查询行数
-- 限定查询 -- 语法:select 列名,..|* from 表名 limit m m:表示查询的条数(默认从第一条数据开始) -- 语法:select 列名,..|* from 表名 limit n,m n:表示起始位置(从0开始) m:表示查询的条数 -- 查询前5条数据 select * from t_employees limit 5; select * from t_employees limit 0,5; -- 1 -- 查询6~10条数据 select * from t_employees limit 5,5; -- 2 -- 查询11~15条数据 select * from t_employees limit 10,5; -- 3
分页公式:当前页 curPage 每页条数 pageSize
*分页公式:select * from 表名 limit (curPage-1)pageSize,pageSize;
7.13 基础查询总结
/* 基本查询总结: SQL编写顺序 SQL执行顺序 SQL执行顺序 select ⑤select 1 from from ①from 2 where where ②where 3 group by group by ③group by 4 having hvaing ④hvaing 5 select order by ⑥order by 6 order by limit ⑦limit 7 limit */
注意:
- mysql中不区分大小写。不区分单双引号
- mysql日期类型可以使用’yyyy-MM-dd’表示
- mysql数值类型和字符串类型是可以自动转换
Java数据库部分(MySQL+JDBC)(一、MySQL超详细学习笔记)(中):https://developer.aliyun.com/article/1580543