1.初识Mysql
1.1什么是数据库
数据库(DB,datebase)
概念:数据仓库,软件,安装在操作系统之上 SQL,可以存储大量的数据
作用:管理数据,存储数据
1.2数据库分类
关系型数据库:(SQL)
- Mysql,Oracle,Sql Server,DB2,SqlLite
- 通过表和表之间,行和列之间的关系进行数据的储存 学院信息表,考勤表
非关系型数据库:(not only sql)
- Redis,MongDB
- 非关系型数据库,对象存储,通过对象自身的属性来决定。
==DBMS(数据库管理系统)==
- 数据库的管理软件,科学有效的管理数据,维护和获取数据
- Mysql,数据库管理系统
1.3Mysql简介
MySQL是一种开放源代码的关系型数据库管理系统(RDBMS),使用最常用的数据库管理语言–结构化查询语言(SQL)进行数据库管理。
MySQL是开放源代码的,因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。
MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下,MySQL是管理内容最好的选择。
1.4Mysql安装
网上百度教程即可
1.5SQLyog安装
Mysql的可视化工具
1.6SQLyog的使用
- 登录
- 创建一个数据库,严格使用格式,避免中文软码
==每一个sqlyog的执行操作,本质上就是对应了一个sql语句,可以在软件的历史记录中查看==
新建一张表student
1
字段:id. name. age.
- 查看表
1.7连接数据库
命令行连接
1 | mysql -uroot -p123456 --连接数据库 |
==数据库语言==CRUD增删改查
- DDL 定义
- DML 操作
- DQL 查询
- DCL 控制
2、操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
==mysql关键字不区分大小写==
2.1操作数据库
创建数据库
1
CREATE DATABASE [IF NOT EXISTS] westos; --[]代表可选
删除数据库
1
DROP DATABASE [IF EXISTS] westos;
使用数据库
1
2--tab键的上面,如果表名或者字段名是一个特殊字符,就需要带``
USE `school`查看所有的数据库
1
show databases --
==学习思路:==
对比sqlyog的可视化历史记录查看sql语句
固定的关键字必须记住
2.2数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小数据 2个字节
- mediumint 中等数据 3个字节
- ==int 标准的整数 4个字节 常用==
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- ==double 浮点数 8个字节==
- decimal 字符串形式的浮点数 金融计算
字符串
- char 字符串固定的大小 0~255
- ==varchar 可变字符串 0~65535==
- tinytext 微型文本 2^8-1
- ==text 文本串 2^16-1==
时间日期
- date YYYY–MM-DD 日期格式
- time HH-MM–SS 时间格式
- ==datetime YYYY–MM-DD HH-MM–SS==
- ==timestamp 时间戳==
- year 年份表示
null
- 没有值
- ==尽量不要使用null进行计算,结果为NULL==
2.3数据库的字段属性
==Unsigned:==
- 无符号的整数
- 声明了该列不能为负数
==zerofill:==
- 0填充的
- 不足的位数,使用0填充
==自增==:
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的主键 -index 必须是整数类型
- 可以自定义设计主键的自增的起始值和步长
==非空 NULL NOT NULL==
- 假设设置为not null,不赋值就会报错
- NULL 如果不填写,默认就是NULL
==默认:==
- 设置默认的值
==2.4创建数据库表==
1 | --注意点: |
格式:
1 | CREATE TABLE [IF NOT EXISTS] `表名`( |
常用命令:
1 | SHOW CREATE DATABASE SCHOOL --查看创建数据库的语句 |
2.5数据表的类型
1 | --关于数据库引擎 |
MYISAM | INNOD | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大约为两倍 |
常规使用操作:
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事物的处理,多表多用户操作
在物理空间存在的位置
所有的数据文件都存在data目录下,一个文件夹对应一个数据库
本质还是文件的存储
MYSQL引擎在物理文件上的区别
- INNODB在数据库表中只有一个*.frm文件,以及上级目录下的ibdate1文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(date)
- *.myi 索引文件(index)
设置数据库表的字符集编码
1 | CHARSET=utf8 --如果不设置,则不支持中文 |
2.6修改删除表
修改
1 | --修改表名: ALTER TABLE 旧表名 RENAME AS 新表名 |
删除
1 | --删除表 (如果存在再删除) |
==所有的创建和删除操作尽量加上判断,以免报错~==
3 Mysql数据管理
3.1 外键(了解即可)
外键约束:
删除有外键关系的表的时候,必须要先删除引用别人的表(从表),再删除被引用的表(主表)
创建表之后添加外键:
1 | ALTER TABLE `student` |
物理外键,不建议使用。
3.2 DML语言(全部记住)
==数据库意义:==数据存储,数据管理
DML语言:数据操作语言
- insert
- update
- delete
3.3 添加 insert
insert
语法:insert into 表名(字段名1,字段名2,字段名3) values (‘值1’),(‘值2’), ………..
注意事项:
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的,但是后面的值必须要一一对应。
- 可以同时插入多条数据,values后面的值,需要使用英文逗号隔开。values(),()
1 | INSERT INTO `student` (`name`,`pwd`) VALUES ('李四','12345678'); |
3.4 修改 update
update
1 | --修改学员名字,带了条件 |
条件:where子句 运算符 id等于某个值,大于某个值,在某个区间修改。。。
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<>或!= | 不等于 | 5!=6 | true |
> | 大于 | ||
< | 小于 | ||
between….and… | 在某个范围内 | 【2,5】 | 2,3,4,5 |
AND | 并且 && | ||
OR | 或者 | ||
>= | |||
<= |
1 | --通过多个条件定位数据 |
语法:**UPDATE 表名 SET colnum_name =value.[colnum_name] where [条件]**
注意:
- olnum_name 是数据库的列,尽量带上 ``
- 不指定条件的情况下,会改变所有表!
- values不一定是值,也有可能为变量
3.5 删除 delete
delete
语法:delete from 表名 【where 条件】
1 | -- 删除数据 (避免这样写,会全部删除) |
TRUNCATE命令 一般使用该命令删除库
作用:完全清空一个数据库,表的结构和索引约束不会变
1 | --清空 student表 |
delete与TRUNCATE 的区别
相同点:都能删除数据,都不会删除表结构
不同点:
- TRUNCATE 重新设置,自增列 计数器会归零
- TRUNCATE 不会影响事物
- DELETE 不会影响自增
==4 DQL查询语言==
4.1 DQL
(Date Query Language:数据查询语言)
- 所有的查询操作都用它,Select
- ==数据库中最核心的语言最重要的语句==
- 使用频率最高的语句
==select 完整语法:==
====select 去重选项 字段列表 [as 字段别名] from 数据源 ====
联表查询
==[where子句]== 指定结果满足的条件
==== [group by 子句]== 指定结果按照什么字段分组
==[having子句]== 过滤分组记录必须满足的条件
==[order by 子句]== 指定结果排序 升序 降序
==[limit子句];== 指定结果怎么显示
4.2 指定查询字段
1 | --查询全部的学生 SELECT 字段 FROM 表 |
语法:**SELECT 字段 FROM 表**
有的时候,列名字不是那么的见名知意。我们起别名 AS 字段名 as 别名 表名 AS 别名
==去重 distinct==
1 | select distinct studentno from student |
数据库的列(表达式)
1 | SELECT VERSION();--查看版本(函数) |
==数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量。。。==
select 表达式 from 表
4.3 where 条件子句
逻辑运算符
1 | --并且 |
==模糊查询:比较运算符==
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为NULL,结果为真 |
IS NOT NULL | a is not null | 如果操作符不为NULL,结果为真 |
BETWEEN | a between b and c | 若a在b和c之间,结果为真 |
LIKE | a like b | SQL匹配,如果a匹配b,则结果为真 |
IN | a in (a1,a2,a3…) | a在a1,a2,a3.。之间的任意值,结果为真 |
1 | --与LIKE结合使用,%代表任意个字符,_代表一个字符 |
4.4 联表查询
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
1 | SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` |
1 | SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` |
1 | SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` |
1 | SELECT s.`studentno`,`studentname`,r.`subjectno`,`studentresult`,`subjectname` |
自连接
自己的表和自己的表连接:==一张表拆成两张一样的表即可==
4.5 分页和排序
排序
1 | --升序 ASC 降序 DESC |
分页
1 | --语法 LIMIT 起始值 页面大小 |
4.6 子查询和嵌套查询
1 | SELECT s.`studentno`,`studentname` |
4.7 分组和过滤
1 | SELECT `subjectname`,AVG(`studentresult`) AS 平均分 |
4.8 where和having区别
where:**对原始表的筛选**
having:**对分组后的表过滤筛选**
5.Mysql 函数
5.1常用函数
1 | --返回绝对值 |
5.2 聚合函数(常用)
函数 | 描述 |
---|---|
count | 计数 |
sum | 求和 |
avg | 平均值 |
max | 最大值 |
min | 最小值 |
1 | --count(字段),会忽略所有的NUll值 |
MD5加密:
UPDATE textmd5 SET pwd=MD5(pwd)
6 事务
所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。
事务原则:ACID特性
原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
一致性(Consistency)
事务前后数据的完整性必须保持一致。
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
事务的隔离级别
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
事务的处理
在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。
场景模拟 转账
1 | SET autocommit=0; --关闭自动提交事务 |
7 索引
索引定义 :==索引就是帮助Mysql高效获取数据的数据结构==
索引的作用:
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
分类
- 主键索引 (Primary Key)
- 唯一索引 (Unique)
- 常规索引 (Index)
- 全文索引 (FullText)
主键索引
主键 : 某一个属性组能唯一标识一条记录
特点 :
- 最常见的索引类型
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
唯一索引
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别
- 主键索引只能有一个
- 唯一索引可能有多个
1 | CREATE TABLE `Grade`( |
常规索引
作用 : 快速定位特定数据
注意 :
- index 和 key 关键字都可以设置常规索引
- 应加在查询找条件的字段
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作
1 | CREATE TABLE `result`( |
全文索引
百度搜索:全文索引
作用 : 快速定位特定数据
注意 :
- 只能用于MyISAM类型的数据表
- 只能用于CHAR , VARCHAR , TEXT数据列类型
- 适合大型数据集
1 | /* |
拓展:测试索引
建表app_user:
1 | CREATE TABLE `app_user` ( |
批量插入数据:100w
1 | DROP FUNCTION IF EXISTS mock_data; |
索引效率测试
无索引
1 | SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时 |
创建索引
1 | CREATE INDEX idx_app_user_name ON app_user(name); |
测试普通索引
1 | mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G |
索引准则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
索引的数据结构
1 | -- 我们可以在创建上述索引的时候,为其指定索引类型,分两类 |
8 权限管理和备份
8.1 用户权限管理
使用SQLyog可视化管理
基本命令
1 | /* 用户和权限管理 */ ------------------ |
8.2 Mysql备份
数据库备份必要性
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份方法
- mysqldump备份工具
- 数据库管理工具,如SQLyog
- 直接拷贝数据库文件和相关配置文件
mysqldump客户端
作用 :
- 转储数据库
- 搜集数据库进行备份
- 将数据转移到另一个SQL服务器,不一定是MySQL服务器
1 | -- 导出 |
9 规范化数据库
问题 : 为什么需要数据规范化?
不合规范的表设计会导致的问题:
- 信息重复
- 更新异常
- 插入异常
- 无法正确表示信息
- 删除异常
- 丢失有效信息
三大范式
第一范式 (1st NF)
第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式
第二范式(2nd NF)
第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。
第二范式要求每个表只描述一件事情
第三范式(3rd NF)
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
规范化和性能的关系
为满足某种商业目标 , 数据库性能比规范化数据库更重要
在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询
==10 JDBC==
10.1 什么是JDBC
sun公司为了简化开发人员对数据库的统一操作,提供了一个java操作数据库的规范,俗称JDBC。
对于开发人员,只需要掌握JDBC接口的操作即可。
需要导入数据库驱动包 mysql-connector-java-5.1.47.jar
10.2第一个JDBC程序
1 | package com.liu.lesson01; |
步骤总结:
- 加载驱动
- 连接数据库
- 执行sql对象
- 获得返回的结果集
- 释放连接查询
DriverManager
1 | // DriverManager.registerDriver(new com.mysql.jdbc.Driver()); |
URL
1 | String url = "jdbc:mysql://localhost:3306/jdbcstudy? useUnicode=true&characterEncoding=utf8&useSSL=true"; |
Statement 执行SQL 的对象 PrepareStatement 执行SQL 的对象
1 | String sql = "SELECT * FROM users"; // 编写SQL |
ResultSet 查询的结果集:封装了所有的查询结果
获得指定的数据类型
1 | resultSet.getObject(); // 在不知道列类型的情况下使用 |
遍历、指针
1 | resultSet.beforeFirst(); // 移动到前面 |
释放资源
1 | resultSet.close(); |
10.3 statement对象
Jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象 向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sql语句,executeUpdate执行 完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement对象的Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的 ResultSet对象。
create操作
使用executeUpdate(String sql)方法完成数据添加操作
1 | Statement st = conn.createStatement(); |
delete操作
使用executeUpdate(String sql)方法完成数据删除操作
1 | Statement st = conn.createStatement(); |
update操作
使用executeUpdate(String sql)方法完成数据修改操作
1 | Statement st = conn.createStatement(); |
read操作
使用executeQuery(String sql)方法完成数据查询操作
1 | Statement st = conn.createStatement(); |
10.4 数据库连接池
数据库连接——>数据库连接——>执行完毕——>释放
连接——>释放 十分浪费系统资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
最小连接数: 10
最大连接数: 15
等待超时:100ms
编写连接池:实现接口 DataSource
开源数据源实现
- DBCP
- C3P0
- Druid:阿里巴巴
使用了这些数据库连接池后,在开发中就不需要编写连接数据库的代码了
DBCP
需要的Jar包
commons-dbcp-1.4 、 commons-pool-1.6
C3P0
需要的Jar 包
c3p0-0.9.5.5、mchange-commons-java-0.2.19
不管什么数据源,本质都是实现DataSource接口,方法一样