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注入的本质:把传递进来的参数当作字符
假设其中存在转义字符,比如说 ‘ 会被直接转义