MySQL学习

MySQL教程

一、初识MySQL

1.1 数据库(DB,DataBase)：

概念：

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。

作用：

存储，管理数据

1.2 数据库分类：

关系型数据库(SQL)：

• MySQL，Oracle，Sql Server，DB2，SQLlite
• 通过表与表之间，行与列之间的关系进行数据的存储

非关系型数据库(NoSQL)：

• Redis，MongDB
• 非关系型数据库，对象存储，通过对象自身的属性来决定。

DBMS(数据库管理系统)：

• 数据库的管理软件，科学有效的管理数据。维护和获取数据。
• MySQL，数据库管理系统

1.3 MySQL简介：

MySQL是一个关系型数据库管理系统

安装建议：

不建议下载exe，注册表

1.4 连接数据库：

mysql -uroot -pxxxxx	--连接数据库

update mysql.user set authentication_string=password(‘设置的密码‘) where user=‘root‘ and Host=‘localhost‘;		--修改数据库密码

flush privileges;		--刷新权限

--------------------------------------------------------------
--所有的语句使用：结尾

show datebase;			--查看所有的数据库

use xxx					--切换数据库

show tables;			--查看数据库中所有的表

describe 表名			   --显示数据库中所以表的信息

create database 数据库名称	--创建数据库

exit					--退出连接

--						--单行注释

/**/					--多行注释

1.5 数据库语言：CRUD 增删改查

DDL 定义

DML 操作

DQL 查询

DCL 控制

二、操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据

mysql关键字不区分大小写

2.1 操作数据库

创建数据库：

CREATE DATABASE [IF NOT EXISTS] 数据库名				--[]代表可选(避免重复)

删除数据库：

DROP DATEBASE [IF EXISTS] 数据库名

使用数据库：

-- tab 键的上面，如果你的表名或者字段名是一个特殊字符，就需要带``
USE 数据库名

查看数据库：

SHOW DATABASE

2.2 数据库的列类型：

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• 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 1970年到现在的毫秒数！
• year 年份表示

null

• 没有值
• 不要使用null计算，结果为null

2.3 数据库的字段属性：

Unsigned

• 无符号的整数
• 声明了该列不能声明为负数

zerofill

• 0填充的
• 不足的位数，使用0来填充，int(3)，5 --- 05

自增

• 通常理解为自增，自动在上一条记录的基础上+1(默认)
• 通常用来设计唯一的主键~ index，必须是整数类型
• 可以自定义设计主键自增的起始值和步长

非空 NULL not null

• 假设设置为 not null，如果不给他赋值，就会报错
• NULL，如果不填写，默认就是null

默认

• 设置默认的值
• sex，默认值为男，如果不知道该列的值，则会有默认的值

2.4 创建数据库表:

-- AUTO_INCREMENT 自增
-- 字符串使用单引号括起来
-- 所有语句后面加,最后一个不用加
-- comment 注释
-- default 默认值
-- primary key 主键 (不建议写在语句里，写到最后明了) 一般一个表只有唯一一个的主键
-- ENGINE 引擎 INNODB
-- CHARSET 编码

create table if not exists `student`(
    `id` int(20) not null auto_increment comment ‘学号‘,
    `name` varchar(30) not null default ‘匿名‘ comment ‘姓名‘,
    `password` varchar(20) not null default ‘123456‘ comment ‘密码‘,
    `sex` varchar(2) not null default ‘女‘ comment ‘性别‘,
    `birthday` datetime default null comment ‘出生日期‘,
    `address` varchar(100) default null comment ‘家庭住址‘,
    `email` varchar(20) default null comment ‘邮箱‘,
    primary key (`id`)
)ENGINE = INNODB DEFAULT CHARSET = UTF8

格式：

CREATE TABLE [IF NOT EXISTS] ‘表名‘ (
	‘字段名‘ 列类型 [属性] [索引] [注释],
    ‘字段名‘ 列类型 [属性] [索引] [注释],
    ‘字段名‘ 列类型 [属性] [索引] [注释],
    	.......
    ‘字段名‘ 列类型 [属性] [索引] [注释]
)[表类型] [字符集] [注释]

常用命令：

show create database school;    -- 查看创建数据库的语句
show create table student;      -- 查看建表语句
DESC student;                   -- 查看一张表的具体结构

2.5 数据表的类型

-- 数据库引擎
INNODB 默认使用
NYISAM 早年使用

常规使用操作：

• MYISAM 节约空间，速递较快
• INNODB 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都放在data目录下

本质都是文件的存储！

MySQL引擎在物理文件上的区别

• InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
• MYISAM对应文件
  • *.frm -表结构的定义文件
  • *.MYD 数据文件(data)
  • *.MYI 索引文件(index)

设置数据库表的字符集编码

CHARSET=utf8

不设置，mysql默认字符集(不支持中文)

mysql默认编码为Latin1，不支持中文

在my.ini中设置默认编码

character-set-server=utf8

2.6 修改删除表

修改

-- 修改表名称 alter table 旧表名 rename as 新表名;
alter table teacher rename as student;

-- 增加表的字段 alter table 表名 add 字段名 列属性;
alter table student add age int(11);

-- 修改表的字段

-- 修改约束 alter table 表名 modify 字段名 列属性;
alter table student modify age varchar(11);

-- 字段重命名 alter table 表名 change 旧字段名 新字段名 列属性;
alter table student change age age1 int(11);

-- 删除表的字段
alter table student drop age;

删除

-- 删除表
drop table if exists student;

== 所有的创建和删除操作尽量加上判断，以免报错==

三、MySQL数据管理

3.1 外键

创建表时添加外键约束

CREATE TABLE `student` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT ‘学号‘,
  `name` varchar(30) NOT NULL DEFAULT ‘匿名‘ COMMENT ‘姓名‘,
  `password` varchar(20) NOT NULL DEFAULT ‘123456‘ COMMENT ‘密码‘,
  `sex` varchar(2) NOT NULL DEFAULT ‘女‘ COMMENT ‘性别‘,
  `birthday` datetime DEFAULT NULL COMMENT ‘出生日期‘,
  `gradeid` int(10) NOT NULL COMMENT ‘学生的年级‘,
  `address` varchar(100) DEFAULT NULL COMMENT ‘家庭住址‘,
  `email` varchar(20) DEFAULT NULL COMMENT ‘邮箱‘,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `grade` (
  `gradeid` int(10) NOT NULL AUTO_INCREMENT COMMENT ‘年级id‘,
  `gradename` varchar(50) NOT NULL COMMENT ‘年级名称‘,
  PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

物理外键不建议使用！(避免数据库过多造成困扰)

最佳实践：

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 我们想使用多张表的数据，想使用外键（程序去实现）

3.2 DML语言(数据操作语言)

数据库的意义：

数据存储，数据管理

• Insert
• update
• delete

3.3 添加

insert

-- 插入语句(添加)

-- insert into 表名([字段1，字段2，字段3]) values (‘值1‘),(‘值2‘),(‘值3‘)；
insert into `grade` (`gradename`) values (‘大四‘);

-- 由于主键自增，我们可以省略 (如果不写表的字段，他就会--匹配)

-- 一般写插入语句，要将数据与字段一一对应
-- 插入多个字段
insert into `grade` (`gradename`) values (‘大三‘),(‘大二‘),(‘大一‘);

语法： insert into 表名 ([字段名1,字段名2,字段名3]) values (‘值1‘),(‘值2‘),(‘值3‘);

注意事项：

1. 字段和字段之间使用英文逗号隔开
2. 字段可以省略，但是后面的值必须一一对应
3. 可以同时插入多条数据，values后面的值，需要使用,隔开

3.4 修改

update

-- 修改语句
update `grade` set `gradename` = ‘大一‘ where gradeid = 1;

-- 不指定条件的情况下，会改动所有
update `grade` set `gradename` = ‘初始‘;

-- 修改多个条件
update `grade` set `gradename` = ‘大四‘, `gradep` = 12 where gradeid = 1;

语法： update 表名 set colnum_name = value.[colnum_name = value] where [条件]

条件：where 子句 运算符 id 等于某个值，大于某个值，在某个区间内修改....

操作符会返回 布尔值

-- 通过多个定位条件定位数据
update `grade` set `name` = ‘长江7号‘ where `name` = ‘磊‘ and sex = ‘男‘

注意：

• colnum_name 是数据库的列，尽量带上``
• 条件，筛选的条件，如果没有指定，则会修改所有的列
• value，是一个具体的值

update `student` set `brithday` = CURRENT_TIME `name` = ‘长江7号‘ AND sex = ‘男‘

3.5 删除

delete 命令

语法：delete from 表名 [where 条件]

-- 删除数据
delete from `grade`;

-- 删除指定数据
delete from `grade` where gradeid = 1;

TRUNCATE 命令

作用：完全清空一个数据库表，表的结构和索引约束不会变！

-- 清空 student 表
truncate `student`

delete 和 TRUNCATE 区别

• 相同点：都能删除数据，都不会删除表结构

• 不同：

  • TRUNCATE 重新设置 自增列 计数器会归零
  • TRUNCATE 不会影响事务

-- 测试delete 和 TRUNCATE区别

create table `test` (
    `id` int(4) not null auto_increment,
    `coll` varchar(20) not null,
    primary key (`id`)
)ENGINE = InnoDB Default charset = utf8;

insert into `test` (`coll`) values (‘1‘),(‘2‘),(‘3‘);

delete from test;       -- 不会影响自增
insert into `test` (`coll`) values (‘1‘),(‘2‘),(‘3‘);

TRUNCATE table `test`;  -- 自增归零
insert into `test` (`coll`) values (‘1‘),(‘2‘),(‘3‘);

DELETE删除问题：重启数据库现象

• InnoDB 自增列会从1开始 (存在内存当中，断电即失)
• MyISAM 继续从上一个自增量开始(存在文件中，不会丢失)

四、DQL查询数据

4.1 DQL

(Data Query Language：数据查询语言)

• 所有的查询操作都用他 Select
• 简单的查询，复杂的查询他都能做~
• 数据库中最核心的语言
• 使用频率最高

4.2 查询所有字段

-- 查询所有
select * from student;

-- 查询指定字段
select `name`,`sex` from student;

-- 别名，给结果起一个名字 AS 可以给字段起别名
select `name` as 姓名, `sex` from student;

-- 函数 Concat (a,b)
select concat(‘姓名: ‘, name) from student;

列名字可以起别名 AS

去重 disinct

作用：去除SELECT查询出来的结果中重复的数据，只显示一条

select distinct `gradeid` from student;

数据库的列

-- 查询系统的版本(函数)
select VERSION();

-- 用来计算(表达式)
select 100 * 3 - 1 as how;

-- 查询自增的步长(变量)
select @@auto_increment_increment;

-- 学生的密码 +1 查看
select `name`,`password`+1 as `密码+1后`from student;

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量....

select 表达式 from 表

4.3 where条件子句

作用：检索数据中 符合条件 的值

逻辑运算符

搜索的条件由一个或者多个表达式组成！结果 布尔值

-- ============================ where =================================
select `name`, `password` from student;

-- 查询学生密码在10000以上的学生
select `name`, `password` from student where password > 10000;

-- and && 查询学生密码在1000以上,10000以下的学生
select `name`, `password` from student where password > 1000 && password < 10000;

-- 模糊查询（区间） between and
select `name`, `password` from student where password between 1000 and 10000;

-- 除了1234密码的学生  ! not
select `name`, `password` from student where password != 1234;
select `name`, `password` from student where not password = 1234;

模糊查询：比较运算符

-- =============================== 模糊查询 ====================================
-- 查询姓张的同学
-- like结合   %(代表0到任意个字符)    _(一个字符)
select `id`,`name` from student where name like ‘张%‘;

-- 查询姓张，后面只有一个字的
select `id`,`name` from student where name like ‘张_‘;

-- 查询名字中有烁的同学
select `id`,`name` from student where name like ‘%烁%‘;

-- ====================== in(具体的一个或者多个值) ===========================
-- 查询 1，2,3号学生
select `id`,`name` from student where id in (1,2,3);

-- 查询地址在呼和浩特的学生
select `id`,`name` from student where address in (‘呼和浩特‘);

-- 查询地址为空的学生
-- ====================== null  not null ========================
select `id`,`name` from student where address = ‘‘;

-- 查询有出生日期的同学
select `id`,`name` from student where birthday is not null;

-- 查询没有出生日期的学生
select `id`,`name` from student where birthday is null;

4.4 联表查询

JOIN 对比

-- ======================== 联表查询 ==========================
-- 查询，同学的学号，姓名，年级
select * from student;
select * from grade;

/*
    1.分析需求，分析查询的字段来自哪些表，(连接查询)
    2.确定使用哪种连接查询？   7种
    确定交叉点(这两个表中哪个数据是相同的)
    判断的条件：学生表中 gradeid = 成绩表 gradeid
*/

select id,name,s.gradeid,gradename
from student as s
inner join grade as g
where s.gradeid = g.gradeid;

-- right join
select `id`,`name`,s.gradeid,`gradename`
from student s
right join grade g
on s.gradeid = g.gradeid;

-- left join
select `id`,`name`,s.gradeid,`gradename`
from student s
left join grade g
on s.gradeid = g.gradeid;

-- join （连接的表） on （判断条件） 			连接查询
-- where								   等值查询

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

父类

子类

操作：查询父类对应的子类关系

-- ============================= 自连接 ==================================

create table category (
    categoryid int(10) unsigned not null auto_increment comment ‘主题id‘,
    pid int(10) not null comment ‘父id‘,
    categoryName varchar(50) not null comment ‘主题名字‘,
    primary key (`categoryid`)
) engine = InnoDB auto_increment = 9 default charset = utf8;

insert into category values
                            (2,1,‘c语言‘),(3,1,‘JavaSE‘),(4,3,‘JavaEE‘),
                            (5,1,‘web开发‘),(6,3,‘操作系统‘),(7,5,‘数据结构‘),
                            (8,2,‘离散数学‘);

-- 查询父子信息：把一张表看成两个一模一样的表
select a.categoryName as ‘父栏目‘, b.categoryName as ‘子栏目‘
from `category` as a,
     `category` as b
where a.categoryid = b.pid;

SELECT语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,....]]}
FROM table_name [as table_alias]
	[left | right | inner join table_name2]		-- 联合查询
	[WHERE ...]		-- 指定结果需要满足的条件
	[GROUP BY ...]		-- 指定结果按照哪几个字段来分组
	[HAVING]		-- 过滤分组的记录必须满足的次要条件
	[ORDER BY ...]		-- 指定查询记录按一个或多个条件查询
	[LIMIT {[offset,]row_count | row_countOFFSET offset}];
	-- 指定查询的记录从哪条至哪条

4.5 分页和排序

排序

-- ========================= 分页 排序 ===============================
-- 排序 ： 升序 ASC  ，  降序 DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 根据密码排序
select `id`,`name`,`password`,s.`gradeid` from student s left join grade g on s.gradeid = g.gradeid
where not id = 1 order by password asc;

select `id`,`name`,`password`,s.`gradeid` from student s left join grade g on s.gradeid = g.gradeid
where not id = 1 order by password desc;

分页

-- 分页 语法：limit 起始值 页面的大小
-- 缓解数据库压力 (瀑布流)
-- 每页只显示5条数据
select `id`,`name`,`password`,s.`gradeid` from student s left join grade g on s.gradeid = g.gradeid
where not id = 1 order by password desc limit 0,3;

-- [pageSize：页面大小]
-- [(n - 1) + pageSize：起始值]
-- [n：当前页]
-- [数据总数/页面大小 = 总页数]

4.6 子查询

本质：在where语句中嵌套一个子查询语句

-- ===================== where ======================
-- 1.查询学号，姓名，密码，年级号，年级 根据降序排列

-- 方式一、连接查询
select `id`,`name`,`password`,s.`gradeid`,`gradename` from student s left join grade g on s.gradeid = g.gradeid
order by password desc;

-- 方式二、子查询 查询gradeid 小于2
select `name`,`password` from student s
    inner join grade g on s.gradeid = g.gradeid where g.gradeid < 2;

-- 在上述基础上增加 学生名字中有王的
select `name`,`password` from student s
    inner join grade g on s.gradeid = g.gradeid where g.gradeid < 2 and name like ‘%王%‘;

-- 子查询
select `name`,`password`
from student s where name like ‘%王%‘ and gradeid = (
    select `gradeid` from grade where gradeid < 2 and s.gradeid = grade.gradeid
);

五、MySQL函数

5.1 常用函数

-- ==================== 常用函数 ========================

-- 数学运算
select abs(-8);             -- 绝对值
select ceiling(9.4);        -- 向上取整
select floor(9.4);          -- 向下取整
select rand();              -- 返回0-1随机数
select sign(-10);           -- 判断符号 (0 - 0    -   -1    +  1)

-- 字符串函数
select char_length(‘你猜我有几位‘);
select concat(‘我‘,‘爱‘,‘你们‘);        -- 拼接字符串
select insert(‘我爱MySQL‘,2,2,‘你好‘);  -- 替换
select lower(‘ABCdef‘);                -- 小写字母
select upper(‘ABCdef‘);                -- 大写字母
select instr(‘abcdefg‘,‘a‘);           -- 返回索引(第一次出现)
select replace(‘你好啊，你好‘,‘你好‘,‘Hello‘);       -- 替换指定字符串
select substr(‘abcdefg‘,4,2);                     -- (字符串，截取的位置，截取的长度)
select reverse(‘abc‘);                            -- 反转字符串

-- 时间和日期函数
select current_date();                 -- 获取当前日期
select curdate();                      -- 获取当前日期
select now();                          -- 获取当前的时间
select localtime();                    -- 本地时间
select sysdate();                      -- 系统时间

select year(now());
select month(now());
select day(now());
select hour(now());
select minute(now());
select second(now());

-- 系统
select system_user();

5.2 聚合函数

-- ============================== 聚合函数 ================================
-- 都能够统计 表中的数据
select count(name) from student;        -- count(字段)，会忽略所有的null值
select COUNT(1) from student;           -- 不会忽略null值
select COUNT(*) from student;

select sum(id) as 总和 from student;
select avg(id) as 平均 from student;
select max(id) as 最大 from student;
select min(id) as 最小 from student;

5.3 数据库级别的MD5加密(拓展)

MD5:

增强算法复杂度和不可逆性

MD5 不可逆，具体的值的md5 是一样的

MD5破解网站的原理：背后有一个字典，MD5加密后的值 加密前的值

-- ============================= 测试MD5 加密 ==================================
create table `TestMD5` (
    `id` int(4) not null,
    `name` varchar(20) not null,
    `pwd` varchar(50) not null,
    primary key (`id`)
)engine = InnoDB default charset = utf8;

-- 明文密码
insert into TestMD5 values
                           (1,‘张三‘,‘123456‘),
                           (2,‘李四‘,‘123456‘),
                           (3,‘王五‘,‘123456‘),
                           (4,‘刘大‘,‘123456‘);

-- 加密
update testmd5 set pwd=md5(pwd) where id = 1;
-- 全部加密
update testmd5 set pwd=md5(pwd);

-- 插入的时候加密
insert into testmd5 (id, name, pwd)
values (5,‘匿名‘,‘123456‘);

insert into testmd5 (id, name, pwd)
values (6,‘匿名2‘,md5(‘123456‘));

-- 如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密后的值

六、事务

6.1 什么是事务

要么都成功，要么都失败

1、SQL执行 A给B转账 A 1000 ----> 200 B 200

2、SQL执行 B收到A的钱 A 800 -----> B 400

将一组SQL放在一个批次中去执行~

事务原则：ACID原则 原子性，一致性，隔离性，持久性

原子性 (Atomicity)

要么都成功，要么都失败

一致性 (Consistency)

事务前后的数据完整性要保证一致，1000

持久性 (Durability) --- 事务提交

事务一旦提交则不可逆，被持久化到数据库中！

隔离性 (Isolation)

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，

不能被其他事务的操作数据所干扰事务之间要相互隔离

隔离所导致的一些问题

脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。

虚读：

指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

执行事务

-- ========================= 事务 ==========================
-- mysql事务默认开启 自动提交
set autocommit = 0;         -- 关闭
set autocommit = 1;         -- 开启(默认)

-- 手动处理事务
set autocommit = 0;         -- 关闭自动提交
-- 事务开启
start TRANSACTION;          -- 开启事务 从这个之后的事务都在同一事务中

-- 提交：  持久化
commit;
-- 回滚：  失败
rollback;

-- 事务结束
set autocommit = 1;         -- 开启事务

-- 了解
savepoint 保存点名;    -- 设置一个事务的保存点
rollback to savepoint 保存点名;     -- 回滚到保存点
release savepoint 保存点名;         -- 撤销保存点

模拟场景

-- 模拟转账：事务
set autocommit = 0;     -- 关闭自动提交
start transaction;        -- 开启一个事务（一组事务）

update account set money = money - 500 where name = ‘纪烁‘;       -- 纪烁减500
update account set money = money + 500 where name = ‘张磊‘;       -- 张磊加500

commit;         -- 提交事务 (事务持久化！)
rollback;       -- 回滚事务

set autocommit = 1;     -- 恢复默认值

七、索引

MySQL官方对索引的定义为：索引 (index) 是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

7.1 索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引（primary key）

  • 唯一的标识，主键不可以重复，只能有一个列作为主键

• 唯一索引（unique key）

  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识位 唯一索引

• 常规索引（key/index ）

  • 默认的，index。key关键字来设置

• 全文索引（fulltext）

  • 在特定的数据引擎下才有，MyISAM
  • 快速定位数据

基础语法

-- 索引的使用
-- 1、在创建表时给字段增加索引
-- 2、创建完毕后，增加索引

-- 显示所有的索引信息
show index from account;

索引在小数据量的时候，作用不大，在大数据时，区别十分明显

7.2 索引原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据量的表不需要加索引
• 索引一般加载常用来查询的字段上

索引的数据结构

Hash 类型的索引

Btree：InnoDB的默认数据结构~

八、权限管理和备份

8.1 用户管理

-- 创建用户
create user 张磊 identified by ‘123456‘;

-- 修改密码(当前用户)
set password = password (‘123‘);

-- 修改密码(修改当前用户密码)
set password for 张磊 = password (‘111111‘);

-- 数据库重命名
rename user 张磊 to 新名字;

-- 用户授权 ALL privileges 全部权限
grant all privileges on *.* to shop;

-- 查询权限
show grants for shop;

-- 撤销权限
revoke all privileges on *.* from 张磊;

-- 删除用户
drop user shop;

-- 查看数据库版本
select version();

8.2 数据库备份

MySQL备份的方式：

• 直接拷贝物理文件
• 在可视化工具中手动导出
• 命令行导出 mysqldump 命令行使用

mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名

九、规范数据库设计

当数据库复杂时，需要设计

9.1 为什么要设计

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦、异常[屏蔽使用物理外键]
• 程序性能差

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性
• 方便开发系统

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系图E-R图

设计数据库的步骤：(个人博客)

• 收集信息，分析需求
  • 用户表(用户登录注销，用户的个人信息，写博客，创建分类)
  • 分类表(文章分类，谁创建的)
  • 文章表(文章的信息)
  • 友链表(友链信息)
  • 自定义表(系统信息，某个关键的字，或者一些主字段) key : value
• 标识实体(把需求落实到某个字段)
• 标识实体 之间的关系
  • 写博客：user --> blog
  • 创建分类：user --> category
  • 关注：user --> user
  • 友链：links
  • 评论：user-user-blog

9.2 三大范式

为什么需要数据规范化？

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效的信息

三大范式

第一范式(1NF)

原子性：保证每一列不可再分

第二范式(2NF)

前提：满足第一范式

每张表只描述一件事情

第三范式(3NF)

前提：满足第一范式和第二范式

第三范式需要保证数据表中的每一列数据都和主键直接相关，而不能间接相关

规范性 和 性能的问题

关联查询的表不得超过三张表

• 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
• 在规范性能的问题的时候，需要适当的考虑一下 规范性！
• 故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
• 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

十、JDBC

10.1 数据库驱动

驱动：声卡、显卡、数据库

10.2 JDBC

SUN公司为了简化开发人员（对数据库的统一）操作，提供了一个（Java操作数据库的）规范，俗称JDBC这些规范的实现由具体的厂商去做~

对于开发人员来说，我们只需要掌握JDBC接口的操作即可

10.3 第一个jdbc程序

package cn.imut;

import java.sql.*;

public class Jdbc_demo1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/zl?&useSSL=true&autoReconnect=true&serverTimezone=UTC";
        String username = "root";
        String password = "1870535196";
        //3.连接成功
        Connection connection = DriverManager.getConnection(url,username,password);
        //4.执行sql的对象
        Statement statement = connection.createStatement();
        //5.执行sql，查看返回结果
        String sql = "select * from user";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("username=" + resultSet.getObject("username"));
            System.out.println("password=" + resultSet.getObject("password"));
            System.out.println("name=" + resultSet.getObject("name"));
            System.out.println("=====================================================");
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

问题：

serverTimezone=UTC				-- 时差问题

步骤总结：

1、加载驱动

2、连接数据库DriverManager

3、获得执行sql的对象 Statement

4、或者返回的结果集

5、释放连接

DriverManager

Class.forName("com.mysql.cj.jdbc.Driver");	//固定写法，获取驱动

Connection connection = DriverManager.getConnection(url,username,password);

// Connection	代表数据库
// 数据库设置自动提交
// 事务提交
// 事务滚回

		connection.rollback();
		connection.commit();
		connection.setAutoCommit();

URL

String url = "jdbc:mysql://localhost:3306/zl?&useSSL=true&autoReconnect=true&serverTimezone=UTC";

Statement 执行SQL的对象 PrepareStatement 执行SQL的对象

String sql = "select * from user";			//编写sql

statement.executeQuery();					//查询操作返回 ResultSet
statement.execute();						//执行任何SQL
statement.executeUpdate();					//返回一个行数

ResultSet 查询的结果集：封装了所有的查询结果

获得指定的数据类型

resultSet.getxxx

遍历，指针

resultSet.beforeFirst()...

释放资源

resultSet.close;
statement.close;
connection.close;

10.4 Statement对象

jdbc中的statement对象用于向数据库发送sql语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法，用于向数据库发送增，删，改，的sql语句，executeUpdate执行完成之后，将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-create

使用executeUpdate(String sql) 方法用于向数据库发送查询语句，executeQuery方法代表查询结果的ResultSet