Day15 MySQL基础05

Day14 MySQL基础07

7.2测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'


-- 插入100万条数据  -- 执行耗时   : 34.301 sec
DELIMITER $$  -- 写函数之前必写，标志
CREATE FUNCTION mock_data()  -- 创建一个函数
RETURNS INT   -- 返回值类型
BEGIN-- 开始
   DECLARE num INT DEFAULT 1000000;  -- 定义了两个数
   DECLARE i INT DEFAULT 0;
   
   WHILE i<num DO-- 循环
     INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES(CONCAT('用户',i),'124658444@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-1000000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
     SET i = i+1;
   END WHILE;  -- 结束循环
   RETURN i;
END;

SELECT mock_data();

INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户1'),'124658444@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-1000000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100))


-- 开始玩索引
SELECT * FROM app_user WHERE `name`='用户9999';

EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'; -- 分析这条语句


-- 创建索引
-- id_表名_字段名
-- CREATE INDEX 索引名 on 表(字段)   给哪个表中的哪个字段添加索引
CREATE INDEX id_app_user_name ON app_user(`name`);


-- 索引创建玩完后再搜索，快很多 0.035 sec
SELECT * FROM app_user WHERE `name`='用户9999';

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

---

7.3索引原则

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

索引的数据结构

hash类型的索引

Btree:innodb的默认数据结构

阅读：https://blog.codinglabs.org/articles/theory-of-mysql-index.html

---

8.权限管理和备份

8.1用户管理

sqlyog的可视化操作

SQL命令操作

用户表： mysql.user

本质：对这张表进行增删改查

-- 创建用户
CREATE USER syy2 IDENTIFIED BY'123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD= PASSWORD('1234456')


-- 修改密码（修改指定用户密码）
SET PASSWORD FOR Ashuu  =PASSWORD('1234456')

-- 重命名
RENAME USER syy2 TO sy2

-- 用户授权 ALL PRIVILEGES 全部的权限，库.表
-- ALL PRIVILEGES除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO A5huu


-- 查询权限
SHOW GRANTS FOR root@localhost



-- 撤销权限 remove 哪些权限 在那个库撤销，给谁撤销
REMOVE ALL PRIVILEGES ON *.* FROM A5huu

-- 删除用户
DROP USER A5huu

---

8.2MySQL备份

为什么要备份：

• 保证重要的数据不丢失
• 数据转移

MySQL数据库备份的方式

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

假设你要备份数据库，为了防止数据丢失

把数据库给朋友，sql文件发送

---

9.规范数据库设计

9.1为什么需要设计

当数据库比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

• 数据冗余，浪费空间

• 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】

• 程序的性能差

良好的数据库设计：

• 节省内存空间

• 保证数据的完整性

• 方便我们开发系统

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

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

设计设计库的步骤（个人博客）

• 收集信息，分析需求

用户表（用户登录注销，用户的个人信息，写博客，创建分类）

分类表（文章分类，谁创造的）

文章表（文章的信息）

友链表（友情链接信息）

自定义表（系统信息，某个关键的字，或者一些主字段）

评论表

• 标识实体（把需求落地到每个字段）

• 标识实体之间的关系

写博客：user----->blog

创建分类：user---->category

关注：user---->user

友链：links

评论：user-user-blog

---

9.2三大范式

为什么需要数据规范化？

• 信息重复
• 更新异常
• 插入异常

无法显示正常信息

• 删除异常

丢失有效的信息

三大范式

第一范式(1NF)

要求数据库表的每一列都是不可分割的院子数据项（每一列拆到不能再拆或者是同一类型的）

第二范式(2NF)

前提：满足第一范式

每张表只描述一件事情

主键一定要和后面的列都相关

保证每张表只描述一件事情

第三范式(3NF)

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

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

规范性和性能的问题

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

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

---

10.JDBC（重点）

Java操作数据库

10.1数据库驱动

应用程序-MySQL驱动-数据库

我们的程序会通过数据库驱动，和数据库打交道

10.2JDBC

SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个（JAVA操作数据库的）规范，俗称JDBC

这些规范的实现由具体的厂商去做

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

应用程序 JDBC mysql驱动 数据库

两个包：java.sql javax.sql

还需要到导入一个数据库驱动包

https://mvnrepository.com/artifact/mysql/mysql-connector-java

10.3第一个JDBC程序

导入测试数据库

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `NAME` VARCHAR(40),
 `PASSWORD` VARCHAR(40),
 `email` VARCHAR(60),
 `birthday` DATE
);

INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES('1','zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

1. IDEA创建一个普通项目
2. 导入数据库驱动
3. 编写测试代码

package com.shu.lesson01;

import com.mysql.jdbc.Driver;

import java.sql.*;

public class jdbc01 {
    public static void main(String[] args) throws  ClassNotFoundException,SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.用户信息和url
        String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username="root";
        String password="123456";
        //3.连接成功，返回数据库对象 Connection代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);
        //4.执行SQL的对象  Statement执行SQL的对象
        Statement statement = connection.createStatement();
        //5.执行SQL的对象 去执行SQL
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询出来的结果

        while(resultSet.next()){
            System.out.println("id=" + resultSet.getObject( "id"));
            System.out.println("name=" + resultSet.getObject( "NAME"));
            System.out.println("pwd=" + resultSet.getObject( "PASSWORD"));
            System.out.println("email=" + resultSet.getObject( "email"));
            System.out.println("birthday=" + resultSet.getObject( "birthday"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

1. 加载驱动
2. 连接数据库 DriverManger
3. 获得执行sql的对象 Statement
4. 获得返回的结果集
5. 释放连接

• 以上属于业务级别的MySQL学习

• 运维级别的MySQL学习