MySQL相关基础知识整理

MySQL相关基础知识整理

1. MySQL备份

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

MySQL数据备份的方式

• 直接拷贝物理文件

• 在可视化工具中手动导出

  • 在想要导出的表或库，点击右键，选择备份与导出

• 使用命令行导出 mysqldump

  # mysql -h 主机 -u 用户名 -p 密码  数据库 表名 物理磁盘位置/文件名
  mysql -h localhost -u root -p 1234556 school student >D:/a.sql
  
  #导出多张表
  # mysql -h 主机 -u 用户名 -p 密码  数据库 表名1 表名2 表名3 物理磁盘位置/文件名
  mysql -h localhost -u root -p 1234556 school student1 student2 student3 >D:/a.sql
  
  #导出数据库
  # mysql -h 主机 -u 用户名 -p 密码  数据库  物理磁盘位置/文件名
  mysql -h localhost -u root -p 1234556 school >D:/a.sql
  
  #导入表(在登录的情况下，切换到指定的数据库)
  source D:/a.sql
  

2.如何设计一个项目的数据库

为什么需要设计数据库？

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

糟糕的数据库设计：

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

良好的数据库设计

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

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

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

设计数据库的步骤（如：个人博客）

• 收集信息，分析需求

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

    CREATE TABLE `yblog`.`user`( 
        `id` INT(11) NOT NULL COMMENT '用户唯一id',
        `username` VARCHAR(60) NOT NULL COMMENT '用户名',
        `passwword` VARCHAR(60) NOT NULL COMMENT '用户密码', 
        `sex` VARCHAR(2) NOT NULL COMMENT '性别', 
        `age` INT(3) COMMENT '年龄', 
        `sign` VARCHAR(200) COMMENT '签名', 
        PRIMARY KEY (`id`)
    ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
    
    ALTER TABLE `yblog`.`user` 
    ADD COLUMN `open_id` VARCHAR(1000) NOT NULL COMMENT '微信id' AFTER `sign`, 
    ADD COLUMN `avatar` VARCHAR(1000) NOT NULL COMMENT '头像链接地址' AFTER `open_id`; 
    
    

  • 分类表（文章分类，谁创建的）

     CREATE TABLE `yblog`.`catagory`(
         `id` INT(10) NOT NULL COMMENT '分类id', 
         `category_name` VARCHAR(30) NOT NULL COMMENT '分类标题', 
         `catrgory_user_id` VARCHAR(10) NOT NULL COMMENT '创建用户的id', 
         PRIMARY KEY (`id`)
     ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
    

  • 文章表（文章的信息）

    CREATE TABLE `yblog`.`blog`(
        `id` INT(10) NOT NULL COMMENT '文章的唯一标识',
        `title` VARCHAR(100) NOT NULL COMMENT '文章标题',
        `author_id` INT(10) NOT NULL COMMENT '编写文章的用户',
        `content` TEXT NOT NULL COMMENT '文章分类', 
        `create_time` DATETIME NOT NULL COMMENT '创建时间', 
        `update_time` DATETIME NOT NULL COMMENT '修改时间',
        `love` INT(10) COMMENT '喜欢', PRIMARY KEY (`id`)
    ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
    
     ALTER TABLE `yblog`.`comment` ADD COLUMN `user_id_parent` INT(10) NOT NULL COMMENT '回复人的id' AFTER `create_time`; 
    
    
    

  • 评论表（评论id，所属文章，评论人，评论内容，评论时间）

    CREATE TABLE `yblog`.`comment`( 
        `id` INT(10) NOT NULL COMMENT '评论id', 
        `blog_id` INT(10) NOT NULL COMMENT '所属文章', 
        `user_id` INT(10) NOT NULL COMMENT '评论人', 
        `content` TEXT(2000) NOT NULL COMMENT '评论的内容', 
        `create_time` DATETIME NOT NULL COMMENT '评论时间', 
        PRIMARY KEY (`id`)
    )ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
    
    

  • 有链表（友链信息）

     CREATE TABLE `yblog`.`links`( 
         `id` INT(11) NOT NULL COMMENT '友链id', 
         `links` VARCHAR(50) NOT NULL COMMENT '网站名称', 
         `href` VARCHAR(2000) NOT NULL COMMENT '网站链接',
         `sort` INT(10) COMMENT '排序', 
         PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
    
    

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

  • 说说表（发表心情：id，content，createt_ime）

  • 粉丝表

     CREATE TABLE `yblog`.`user_follow`( 
         `id` INT(10) NOT NULL COMMENT '唯一标识', 
         `user_id` INT(10) NOT NULL COMMENT '被关注的id', 
         `follow_id` INT(10) NOT NULL COMMENT '关注人的id', 
         PRIMARY KEY (`id`)
     ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
    
    

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

• 标识实体之间的关系

  • 写博客： user–> blog
  • 创建分类：user–>category
  • 关注：user–>user
  • 友链：links
  • 评论：user -->user–>blog

三大范式

为什么需要数据规范化

• 信息重复

• 更新异常

• 插入异常

  • 无法正常显示信息

• 删除异常

  • 丢失有效信息

第一范式

确保每列的原子性，如果每列都是不可再分的最小数据单元，则满足第一范式

第二范式

前提：满足第一范式

每张表只描述一件事情

第三范式

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

确保数据表中的每一列数据都和主键直接相关，而不能间接相关

规范性 和 性能问题

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

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

3. JDBC----java操作数据库的规范，为了方便java 操作数据库

驱动？—声卡，网卡，数据库

创建项目

导入数据库驱动

public class JdbcTest {
    public static void main(String[] args) throws ClassNotFoundException，SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        //2.用户信息和URL
        //useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?                useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";
            
        //3.连接成功，数据库对象
        Connection conn = DriverManager.getConnection(url,username,password);
        
        //4.执行SQL的对象
       Statement state = conn.createStatement();
        
        //5.执行SQL的对象 去执行SQL，可能存在结果，查看返回结果
        String sql = "SELECT * FROM users"；
        
        ResultSet r = state.executeQuery(sql);//封装了所有的查询结果
        
        while(r.next()) {
            System.out.println("id" + r.getObject("id"));
             System.out.println("name" + r.getObject("username"));
             System.out.println("pwd" + r.getObject("password"));
        }
        //6.释放连接
        r.close();
        state.close();
        conn.close();
    }
}

SQL注入问题

SQL存在漏洞，会被攻击导致数据泄露。-----本质：SQL会被拼接 or

public class JdbcTest {
    public static void main(String[] args) throws ClassNotFoundException，SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        //2.用户信息和URL
        //useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?                useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";
            
        //3.连接成功，数据库对象
        Connection conn = DriverManager.getConnection(url,username,password);
        
        //4.执行SQL的对象
      
        
        //5.执行SQL的对象 去执行SQL，可能存在结果，查看返回结果
        String sql = "SELECT * FROM users where id = ？"；
        PreparedStatement st = conn.prepareStatement(sql);//预编译SQL，先写SQL，不执行
        st.setInt(1,1);//传递参数
        ResultSet r = st.executeQuery();
       
        while(r.next()) {
            System.out.println("id" + r.getObject("id"));
             System.out.println("name" + r.getObject("username"));
             System.out.println("pwd" + r.getObject("password"));
        }
        //6.释放连接
        r.close();
        state.close();
        conn.close();
    }
}

PreparedStatement防止SQL注入的本质：把传递进来的参数当作字符

假设其中存在转义字符，比如说 ‘ 会被直接转义