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 -- 插入100万数据. 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`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2)); SET i=i+1; END WHILE; RETURN i; END; SELECT mock_data() -- 执行此函数 生成一百万条数据
索引在小数据量的时候,用户感觉使用效率不大,,但是在大数据的时候,感觉就会十分明显
索引原则:
- 索引不是越多越好,只需要常用的查询字段进行加索引
- 不要对经常变动的数据加索引
- 小数据的表不需要加索引
INNODB默认的数据结构:Btree
拓展:java中的事务
方法(){
try(){
正常的业务代码
commit()
}catch(){
rollback()
}
}
二.用户权限:
Sql命令:用户表:mysql.user
实际上就是对用户表进行增删改查
-- 创建用户 CREATE USER maming IDENTIFIED BY '123456' -- 修改密码(修改当前用户) SET PASSWORD = PASSWORD('123456') -- 修改密码(修改指定用户) SET PASSWORD FOR maming =PASSWORD=('123456') -- 重命名 RENAME USER maming TO maming1 -- 授予全部权限 库.表 -- 除了给别人授权,其它都可以做 GRANT ALL PRIVILEGES ON *.* TO maming1 -- 查询权限 SHOW GRANTS FOR maming1 -- 查询指定用户权限 -- root用户权限 SHOW GRANTS FOR root@localhost -- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -- 撤销权限 那些权限 ,那个库,给谁撤销 REVOKE ALL PRIVILEGES ON *.* FROM maming1
公式:
创建用户 :create user 要创建的用户名 identified by 创建用户密码
修改当前用户密码:set password = password(' 新密码 ')
修改指定用户密码:set password for 要修改用户名 =password =(’新密码‘)
重命名 :rename user 旧的用户名 to 新的用户名
给用户授权 :grant 什么权限 on 那个库.那个表 to 那个用户
查询权限 :show grants for 用户名
查询root权限 :show grans for root@localhost
撤销用户权限 :revoke 权限名 on 那个库 . 那个表 from 用户名
三.数据库备份
为什么要备份数据库:
- 保证数据不丢失
- 数据转移
数据库备份方式:
- 直接在物理文件下拷贝走Data文件
- 在可视化工具中手动导出
- 使用命令行导出 MySQLdump
-- 导出数据表 mysqldump -hlocalhost -uroot -p123456 school student >D:/student.sql -- 导出数据库 mysqldump -hlocalhost -uroot -p123456 school >D:/student.sql -- 登录的情况下 切换到要导入的数据库 -- 执行下面的命令,导入表 source studen.sql -- 或直接导入 mysql -uroot -p123456 库名< 备份文件
公式:
导出数据表
mysqldump -h主机名 -u用户名 -p密码 那个数据库 那个表 > 导出位置
导出数据库
mysqldump -h主机名 -u用户名 -p密码 那个数据库 > 导出位置
导入数据表
- 登录mysql
- 切换到要导入的数据库
- 输入命令:source 备份文件
四.数据库设计
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会很麻烦(屏蔽使用物理外键)
- 程序性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统,或开发功能
软件开发中,关于数据库的设计
- 分析需求:分析业务需求和需求处理的数据需求
- 概要设计:设计E -R 图
设计数据库分析(个人博客,参考)
收集信息,分析需求:
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,由谁创建的)
- 文章表(文章信息)
- 友链表(超链接,友链信息)
- 自定义表(装载一些杂乱,未分类的信息)
标识实体(把需求落到每个实体):
- 写博客:user -> blog
- 创建分类: user -> category
- 关注: user -> user
- 友链接:links
五.数据库设计的三大范式
第一范式(1NF):原子性,保证每一列不可再拆分
第二范式(2NF):
- 前提:满足第一范式
- 每张表只描述一件事情
第三范式(3NF):
- 前提:满足第二范式
- 确保主键与每一列数据直接相关,不能间接相关
============================================================================举例======================================================================================
第一范式(1NF):要求数据库表的每一列都是不可分割的原子数据项。
举例说明:
在上面的表中,“家庭信息”和“学校信息”列均不满足原子性的要求,故不满足第一范式,调整如下:
可见,调整后的每一列都是不可再分的,因此满足第一范式(1NF);
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
举例说明:
在上图所示的情况中,同一个订单中可能包含不同的产品,因此主键必须是“订单号”和“产品号”联合组成,
但可以发现,产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关,但是订单金额和订单时间仅与“订单号”相关,与“产品号”无关,
这样就不满足第二范式的要求,调整如下,需分成两个表:
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
举例说明:
上表中,所有属性都完全依赖于学号,所以满足第二范式,但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”,
而不是主键“学号”,所以需做如下调整:
============================================================================END======================================================================================
拓展:(规范性和性能的问题)
阿里巴巴明文规定,联表查询不得超过三张表:
- 考虑商业化的需求和目标,(成本和用户体验!),数据库的性能更加重要(太多表查询反而更慢)
- 再满足性能的最大限度下,去满足规范性
- 有时候会故意给某些表增加冗余字段(从多表查询转换为单表查询)
- 故意增加一些计算列(从大数据量,降低成单列小数据量的查询:索引)