MySQL数据管理
外键
方式一:在创建表的时候加上约束
KEY、FK_ gradeid ( gradeid' ),
CONSTRAINT、FK_ gradeid FOREIGN KEY ( gradeid' ) REFERENCES、 grade' ( gradeid )
方式二;创建表后,添加外键约束
以上的操作都是物理外键,数据库级别的外键,不建议使用,避免数据库过多造成困扰
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)列(字段)
- 我们想使用多张表的数据,用程序实现,在应用层解决
DML语言(全部记住,背下来)
**数据库意义:**存储数据,数据管理
DML语言:数据操作语言
-
Insert
-
update
-
delete
添加(插入)
语法:
insert into 表名([字段名])values('值') -- 一一对应原则
注意事项:
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的,但是后面的值必须要一一对应
- 可以同时插入多条数据,values后面的值,需要使用,隔开即可
修改
语法:
update 表名 set 列名= value where[条件]
-- 修改了学员名字
UPDATE `student` SET `name` = "张三" WHERE id = 1;
--不指定条件的话会改动所有的表;准备跑路
UPDATE `student` SET `name` = "李四"
条件:where 子句 运算符 id等于某个值 大于某个值,在区间内修改
注意事项:
- 列名是数据库的列,尽量带上
- 条件,筛选的条件,如果没有指点,则会修改所有的列
- value 是一个具体的值也可以是一个变量
- 多个设置属性之间,使用英文逗号隔开
删除
语法:
delete from 表名 [where 条件]
-- 删除数据
delete from `student` -- 跑路
--删除指定数据
delete from `student` where id = 1;
truncate 命令
作用:完全删除一个数据库表,表的结构和索引约束不会变!
TRUNCATE student
两者的区别
- 相同点:都能删除数据,不会删除表结构
- 不同点:
- Truncate 重新色湖之自增列 计数器归零
- Truncate 不会影响事务
查询数据DQL(重点)
DQL
Data Query Language:数据库查询语言
- 所有查询操作都用它 Select
- 简单的查询,复杂的查都能做
- 数据库中最核心的语言,最重要的语句
- 使用频繁最高的语言
指定查询字段
语法:select 字段…… From 表
查重
数据库的列
数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量
select 表达式 from 表
where条件子句
作用:检索数据中符合条件的值
收索对的条件由一个或者多个表达式组成!
逻辑运算符
尽量用英文
--===================where======================
SELECT studentNo, StudentResult’ FROM result
--查询考试成绩在95~100 分之间
SELECT studentNo, StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100
-- and &&
SELECT studentNo, StudentResult
FROM result
WHERE StudentResult>=95 && StudentResu7t<=100
--模糊查询(区间)
SELECT studentNo, StudentResult FROM result
WHERE StudentResult BETWEEN 95 AND 100
--除了1000号学生之外的同学的成绩
SELECT studentNo, StudentResult FROM result
WHERE studentNo !=1000;
--!= not
SELECT studentNo, StudentResult
FROM result
WHERE NOT studentNo = 1000
模糊查询 :比较运算符
-- ====================模糊查询====================
--查询姓刘的同学
-- like结合 %(代表0到任意个字符)- (一 个字符)
SELECT
StudentNo, StudentName FROM student
WHERE StudentName LIKE ' 刘%'
--查询姓刘的同学,名字后面只有一个字的
SELECT
StudentNo^,StudentName FROM student
WHERE StudentName LIKE '刘_ '
--查询姓刘的同学,名字后面只有两个字的
SELECT、StudentNo^ ,StudentName FROM student
WHERE StudentName LIKE ' 刘__'
--查询名字中间有嘉字的同学%嘉%
SELECTStudentNo, StudentName FROM student
WHERE StudentName LIKE' % 嘉%'
-- ====== in (具体的一个或者多个值) =========
--查询1001,1002, 1003号学员
SELECT StudentNo,StudentName FROM student
WHERE studentNo IN (1001, 1002, 1003);
--查询在北京的学生
SELECT`StudentNo` , StudentName FROM student
WHERE 'Address' IN ('安徽', '河南洛阳');
-- ==== nu71not nu11====
-- 查询地址为空的学生nu11
SELECT StudentNo ,StudentName FROM student
WHERE address='' OR address IS NULL
--查询有出生日期的同学不为空
SELECTStudentNo, StudentName FROM student
WHERE BornDate IS NOT NULL
--查询没有有出生日期的同学为空
SELECT StudentNo , StudentName FROM student
WHERE BornDate IS NULL
联表查询
JOIN 对比
-- Right Join
SELECT s.studentNO, studentName,SubjectNo , StudentResu1t
FROM student S
RIGHT JOIN result r
ON s.studentNo = r.studentNO
-- Left Join
SELECT s.studentNO, studentName,SubjectNo,StudentResult
FROM student S
LEFT JOIN result r
ON s.studentNo = r.studentNO
左右表示左边的表和右边的表
自联接
自己的表和自己的表连接,核心:一张表拆分为两张一样的表即可
表结构:
分页和排序
排序 Order by 升序 ASC 降序 DESC
语法 order by {需要排列的列名} ASC/EDSC
分页
子查询
可以嵌套查询
在where语句中加select语句继续查询
MySQL函数
常用函数
http://c.biancheng.net/mysql/function/
聚合函数(日常使用较多)
统计表中的数据
count(1)和count(*)区别
(1) count(1) 会统计表中的所有的记录数,包含字段为null 的记录。
(2) count(字段) 会统计该字段在表中出现的次数,忽略字段为null 的情况。即不统计字段为null 的记录。
数据库级别的MD5加密
主要增强算法复杂度和不可逆性
MD5不可逆,具体的值的md5是一样的
在MD5破解网站背后原理是,它自己创建了一个字典,存放了一些常用值,加密前及加密后的值,所有可以破解
MD5也是一个函数,使用方法和上面函数一样
事务
什么是事务
事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。
ACDI性质
- 原子性(Atomicity):要么都成功,要么都失败
- 一致性(Consistency):事务前后的数据完整性要保持一致
- 持久性(Durability):C事D务一旦提交则不可逆,被持久化到数据库中
- 隔离性(Isolation):事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他的事物操作数据多干扰,事务之间要互相隔离
AB去银行转账例子
隔离所导致的一些问题
- 脏读:一个事务读取了另外一个事务没提交的数据。
- 不可重复读:在一个事务内读取表中的某一行数据,前后读取的结果不同。(不一定时错误,只是场合不对)
- 幻读:是指在一个事务内读取到了别的事务插入的数据,导致前后不一致。
sql处理事务的一些语句
-- mysqla是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启(默认开始)
-- 手动处理事务
SET autocommit = 0 -- 关闭
-- 事务开启
START TRANSACTION --标记一个事务的开始,从这个之后的sql都在同一个事物内
INSERT xx
INSERT xx
-- 提交:持久化
COMMIT
-- 回滚:回到原来的样子
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启(默认开始)
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK SAVEPOINT -- 回滚到保存点
RELEASE SAVEPOINT -- 删除保存点
模拟场景
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
-- 建立一张account表
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)
-- 模拟转账:事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务(一组事务)
UPDATE account SET money = money-500 WHERE `name`='A'
UPDATE account SET money = money+500 WHERE `name`='B'
COMMIT; -- 提交事务
ROLLBACK;-- 回滚
SET autocommit = 1;-- 恢复默认值
索引
定义:MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。
大佬归纳的索引博客:http://blog.codinglabs.org/articles/theory-of-mysql-index.html
索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引 PRIMARY KEY
- 唯一标识:主键不可重复,只能有一列作为主键
- 唯一索引 UNIQUE KEY
- 避免重复的列出现,唯一索引可以重复,多个列都可以表示唯一索引
- 常规索引 KEY/ INDEX
- 默认的,index key关键字设置
- 全文索引 FULLText
- 在特定的数据库引擎下才有,例如MYISAM
- 快速定位数据
-- 索引的使用
/*
1.在创建表的时候给字段增加索引
2.创建完毕后,增加索引
*/
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个索引 (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`);
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
SELECT *FROM student WHERE MATCH(studentname) AGAINST('刘');
测试索引
CREATE INDEX 索引名 on 表(字段
-- 先建立一个表
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万条数据
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),'24736743@qq.com',
CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
FLOOR(RAND()*2),UUID(),FLOOR (RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
-- ===============开始检验========================
SELECT * FROM app_user WHERE `name` ='用户89888'; -- 1.191 sec耗时
EXPLAIN SELECT * FROM app_user WHERE `name` ='用户89888'; -- 992902 找了992902次
SELECT * FROM student
-- id_ 表名 _字段名
-- CREATE INDEX 索引名 on 表(字段
CREATE INDEX id_app_user_name ON app_user(`name`); -- 12.130 sec 创建索引需要时间(本质建立了B+树)
SELECT * FROM app_user WHERE `name` ='用户89888'; -- 0.042 sec 再次查找块多了,建立索引的好处
EXPLAIN SELECT * FROM app_user WHERE `name` ='用户89888'; -- 1 直接定位了,不需要一个一个去找
索引在小数据量的时候,用户不大,但是在大数据处理时,区别特别明显
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构 http://blog.codinglabs.org/articles/theory-of-mysql-index.html
Hash类型的索引
Btree(B树):InnoDB的默认数据结构
权限管理和备份
用户管理
SQLyog可视化管理
SQL命令操作
用户表:mysql.user
本质:对这张表进行增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER zzj IDENTIFIED BY '123456'
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR zzj = PASSWORD('111111')
-- 重命名 RENAME USER 原来的名字 TO 新的名字
RENAME USER zzj TO zzj2
-- 用户授权 ALL PRIVILEGES 全部的权限(不包括给别的用户授权 )
GRANT ALL PRIVILEGES ON *.* TO zzj2
-- 查看权限
SHOW GRANTS FOR zzj2 -- 指定用户
SHOW GRANTS FOR root@localhost -- root权限
-- 撤销权限 REVOKE 什么权限 在那个库撤销 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM zzj2
MySQL备份
为什么要备份:
- 保证重要数据不丢失
- 数据转移
MySQL备份方式:
-
直接拷贝物理文件
-
在sqlyog可视化工具中手动导出
- 想要导出的表或者库中点击备份或者导出
- 使用命令行导出:mysqldump 命令行使用
规范数据库设计
为什么需要设计
当数据库比较复杂的时候,我们就要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 书数据库插入和删除都会麻烦和异常【屏蔽使用物理外键】
- 程序性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
软件开发中,关于数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图E-R图
设计数据库的步骤:(个人博客)
- 收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 评论表
- 友链表(友链信息)
- 自定义表(系统信息,某个关键对的子,或者一些主字段) key:value
- 说说表(发表心情。。。。。。)
- 标识实体(把需求落实到每个字段)
- 标识实体之间的关系
- 写博客user ——blog
- 创建用户user ——category
- 关注user ——user
- 友情链接:links
- 评论:user ——user-blog
三大范式
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式 大佬博客:https://www.cnblogs.com/wsg25/p/9615100.html
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
在满足第一范式时还要满足: 每张表只描述一件事
第三范式(3NF)
前提满足第一第二范式,第三范式需要确保数据包中的每一列都和主键直接相关,而不能间接相关
规范性和性能的问题
关联查询的表一般不超过三张表
- 考虑商业化的需求和目标,数据库的性能更重要
- 在规范性能的问题时候,需要适当考虑一些规范性
- 故意给某些表增加一些冗余的字段
- 故意增加一些计算列(大数据量变成小数据量的查询:索引)
业务级别的MySQL学习
运维级MySQL学习