HashMap MyBatis 方法 orm jqgrid nuget 系统后台模板 jquery第一个子元素 jquery清除子元素 python中的def python路径设置 javasubstring stringjava java目录 怎么安装linux系统 路由器辐射大吗 枪神传说辅助 html5网页制作 图片批量处理工具 maxtoc4d spoonwep getelementbyid 魔兽地图七个人 电子商城系统 bz2解压命令 陌陌电脑直播设置教程 迅雷会员共享账号 go程序设计语言 cad特性匹配 vscode全局搜索 mac版matlab 看图软件cad wmiprvse是什么进程 js递归函数 fireworks序列号 例外被抛出且未被接住 软媒u盘启动 谷歌浏览器升级 jquery添加样式 移动硬盘检测工具
当前位置: 首页 > 学习教程  > 编程语言

MySQL数据管理和操作(事务,索引,权限)

2020/10/16 18:11:25 文章标签:

MySQL数据管理 外键 方式一:在创建表的时候加上约束 KEY、FK_ gradeid ( gradeid ), CONSTRAINT、FK_ gradeid FOREIGN KEY ( gradeid ) REFERENCES、 grade ( gradeid )方式二;创建表后,添加外键约束 以上的操作都是物理外键&am…

MySQL数据管理

外键

在这里插入图片描述

方式一:在创建表的时候加上约束

KEY、FK_ gradeid ( gradeid' ),
CONSTRAINT、FK_ gradeid FOREIGN KEY ( gradeid' ) REFERENCES、 grade' ( gradeid )

在这里插入图片描述

方式二;创建表后,添加外键约束

在这里插入图片描述

以上的操作都是物理外键,数据库级别的外键,不建议使用,避免数据库过多造成困扰

最佳实践

  • 数据库就是单纯的表,只用来存数据,只有行(数据)列(字段)
  • 我们想使用多张表的数据,用程序实现,在应用层解决

DML语言(全部记住,背下来)

**数据库意义:**存储数据,数据管理

DML语言:数据操作语言

  • Insert

  • update

  • delete

添加(插入)

语法:insert into 表名([字段名])values('值') -- 一一对应原则
在这里插入图片描述
在这里插入图片描述

注意事项:

  1. 字段和字段之间使用英文逗号隔开
  2. 字段是可以省略的,但是后面的值必须要一一对应
  3. 可以同时插入多条数据,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学习


本文链接: http://www.dtmao.cc/news_show_300390.shtml

附件下载

相关教程

    暂无相关的数据...

共有条评论 网友评论

验证码: 看不清楚?