本文深入介绍了MySQL数据库，从数据库基础到RDBMS术语，详细阐述了DML操作（insert、update、delete）和DQL查询语言，包括子查询、聚合函数、事务ACID原理和索引。还探讨了权限管理、数据库备份和存储引擎的选择，为数据库设计和优化提供指导。
1、什么是数据库（DB，database）
- 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
- 每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。
- 我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
- 所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
- RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database
- 关系型数据库：mysql、oracle、sqlserver
- 通过表和表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表…
非关系型数据库（NoSQL）not only - 非关系型数据库：Redis、MongDB
- 非关系型数据库，对象存储，通过对象的自身属性来决定。
2、数据库管理系统（DBMS）
- 数据库管理系统 ( DataBase Management System )
- 数据库管理软件 , 科学组织和存储数据 , 高效地获取和维护数据
- 我们要学习的MySQL应该算是一个数据库管理系统
3、MySQL数据库
MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。
4、RDBMS 术语
- 数据库： 数据库是一些关联表的集合
- 数据表：表是数据的矩阵，在一个数据库中表看起来限一个简单的电子表格
- 列：一列包含了相同类型的数据
- 行：是一组相关的记录，例如一条用户订阅的数据
- 冗余：存储两倍的数据，降低了性能，提高了安全性
- 主键：主键是唯一的，一个数据表中只能包含一个主键。你可以使用主键来查询数据
- 外键：用来关联两个表
- 复合键：将多个列作为一个索引键
- 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 表头：每一列的名称
- 列：具有相同数据类型的数据的集合
- 行：每一行用来描述某条记录的具体信息
- 值：行的具体信息，每个值必须与该列的数据类型相同
- 键：键的值在每一列中具有唯一性
5、DML数据操作语言（insert、update、delete）
数据库意义：存储数据，数据管理
1、insert添加
- 语法：insert into 表名([字段一], [字段二])values(‘值1’),(‘值2’)
//插入语句（添加）
insert into `grade`(`gradename`)VALUES('大四')
//如果不写表的字段，他就会一一匹配，写插入语句，数据和字段一定要一一对应
insert into `grade` VALUES '大三'
//插入多个字段('a'),('b')
insert into `grade`(`gradename`)
VALUES('大二'）,（'大一')
//插入一条数据
insert into `grade`(`name`,`pwd`,`sex`)
VALUES('小明'，'123','男')
//插入多条数据
insert into `grade`(`name`,`pwd`,`sex`)
VALUES('小李','111','女'),('小王','222','男')
- 注意事项：
- 1、字段和字段之间使用逗号隔开
- 2、字段可以省略，但后边的值必须一一对应
- 3、可以同时插入多条数据，但是VALUES后边的值需要使用逗号隔开
2、update修改
- 语法：update 修改谁（条件） set 原来的值=新值
//修改学院名字,指定条件
update `student` set `name` = '狂神' where id = 1;
//不指定条件，会修改所有表
update `student` set `name` = '长江7号'
//修改多个属性，逗号隔开
update `student` set `name` = '狂神',`email` = '1234@qq.com' where id = 1;
- 条件：where 子句 运算符 id 等于 某个值，大于某个值，在某个区间内修改
3、delete删除
- 语法L: delete from 表名 where
- delete和truncate（清空表）的区别，
- 相同：都能删除数据，都不会删除表数据
- 不同：TRUNCATE 重新设置 自增长，计数器回归零，不会影响事务
//删除数据(避免这么写，会全部删除)
delete from `student` where id = 1;
//删除表
TRUNCATE table `test`
6、DQL（database query language）查询数据
1、select简单查询
//查询全部的学生
select * from student
//查询指定字段
select `StudntNo`,`StudentName` from student
//别名，给结果起一个名字 AS
select `StudentNo` AS 学号,`StudentName` AS 学生姓名 from student AS s
//函数Concat(a,b)
select CONCAT('姓名:',StudentName) AS 新名字 from student
- 所有查询语句都用Select
- 数据库中最核心的语言
- 使用频率最高的语言
- 简单的查询，复杂的查询，他都能做
- 语法：SELECT 字段 … FROM 表
- 去重：distinct
- 作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条
//查询一下有哪些同学参加了考试，成绩
//查询全部的考试成绩
select * from result
//查询有哪些同学参加了考试
select `studentNo` from result
//发现重复数据，去重
select distinct `studentNo` from result
2、where条件子句
- 作用：检索数据中符合条件的值
- 逻辑运算符
运算符 语法 结果
and && a and b a&&b 逻辑与
or || a or b a||b 逻辑或
Not != not a !a 逻辑非
查询考试成绩在95分到100分之间,and &&
SELECT `StduentNo`,`StudentResult`
FROM result
WHERE StudentResult >=95 AND StudentResult<=100
模糊查询（区间）
SELECT `StduentNo`,`StudentResult`
FROM result
WHERE StudentResult BETWEEN 95 AND 100
除了1000号学生之外的同学成绩
SELECT `StduentNo`,`StudentResult`
FROM result
WHERE NOT StudentNo = 1000
除了1000号学生之外的同学成绩
SELECT `StduentNo`,`StudentResult`
FROM result
WHERE StudentNo != 1000;
3、模糊查询:比较运算符
-- 查询姓刘的同学-- 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 '刘__';
-- 查询名字中间有嘉字的同学 %嘉%
SELECT `StudentNo`,`StudentName`
FROM `student`
WHERE StudentName LIKE '%嘉%';
===================IN(具体的一个或者多个值)===========================--
查询1001 1002 1003 学员信息
SELECT `StudentNo`,`StudentName` FROM `student`WHERE StudentNo = 1001
SELECT `StudentNo`,`StudentName` FROM `student`WHERE StudentNo = 1002
SELECT `StudentNo`,`StudentName` FROM `student`WHERE StudentNo = 1003
SELECT `StudentNo`,`StudentName` FROM `student`WHERE StudentNo IN(1001,1002,1003);
-- 查询在北京的学生
SELECT `StudentNo`,`StudentName`
FROM `student`
WHERE `Address` IN('安徽','河南洛阳');
===================NULL NOT NULL===================================
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName`
FROM `student`
WHERE address=''OR address IS NULL
-- 查询有出生日期的同学 不为空下·
SELECT `StudentNo`,`StudentName`
FROM `student`
WHERE `BornDate` IS NOT NULL;
4、联表查询
- 左查询 left join
- 右查询 right jion
- 并集查询 inner join
联表查询示例：
======================联表查询 join ==============================
-- 查询参加考试的同学 （学号，姓名，考试编号，分数）
SELECT * FROM student SELECT * FROM result
/*1. 分析需求，分析查询的字段来自哪些表2.确定使用哪种连接查询？7种确定交叉点（这两个表中哪个数据是相同的）判断的条件： 学生表中 studentNo = 成绩表中 studentNo */
-- JION（表） ON （判断的条件）连接查询
-- where 等值查询
SELECT studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo=r.studentNo
--Right Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo
--LEFT Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
5、分页limit 和 排序order by，排序升序ASC，降序DESC
排序order by（通过那个字段排序）排序升序ASC，降序DESC
select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentName`
from student s
inner join `result` r
on r.`SubiectNo` = sub.`SubjectNo`
where subjectname = '数据结构-1'
order by StudentName DESC
分页limit
作用：缓解数据库压力，给人更好的体验
语法：limit 当前页，页面的大小
分页，每页只显示五条数据
select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentName`
from student s
inner join `result` r
on r.`SubiectNo` = sub.`SubjectNo`
where subjectname = '数据结构-1'
order by StudentName DESC
limit 0,5
查询 java第一学年 课程成绩排名前十的学生，并且分数要大于80 的学生循序（学号，姓名，课程名称，分数）
select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
from student s
inner join result r
on s.StudentNo = r.StudentNo
inner join subject sub
on sub.StudentNo = r.StudentNo
where SubjectName = 'JAVA第一学年' And StudentResult>80
order by StudentResult DESC
limit 0,10
6、having分组
– 查询不同课程的平均分，最高分，最低分，平均分大于80
– 核心：（根据不同的课程分组）
– group by后面使用having来代替where过滤
SELECT `SubjectName`,AVG(StudentResult),MAX(StudentResult)
FROM result r
INNER JOIN `Subject` sub
ON r.SubjectNo=sub.SubjectNo
GROUP BY r.SubjectNo -- 通过什么字段来分组
HAVING AVG(StudentResult)>80
7、子查询
where （这个值不是固定的，是计算出来的）
本质：在where语句中嵌套一个子查询
-- =========================where========================
-- 1.查询 数据库结构-1的所有考试结构（学号，科目编号，成绩） 降序
-- 方式一，连接查询
select `StudentNo`,`SubjectName`,`StudentRuselt`
from `result` r
inner join `subject` s
on r.SubjectNo = s.SubjectNo
where SubjectName = '数据库结构-1'
order by SubjectResult DESC
--方式二，使用子查询(由里及外执行查询)
Select `StudentNo`,`SubjectName`,`StudentRuselt`
from `result`
where SubjectNo = (
select SubjectNo
from `subject`
where subjectName = '数据库结构-1'
)
order by SubjectResult DESC
-- 分数不少于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80
-- 在这个基础上 增加一个科目 ，高等数学-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80 AND `SubjectNo`=(
SELECT Subject FROM `subject`
WHERE SubjectName='高等数学-2'
)
-- 查询课程为 高等数学-2 且分数不小于80分的同学的学号和姓名
SELECT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectName`='高等数学-2'
WHERE `SubjectaName`='高等数学-2' AND StudentResult >=80
-- 再改造 (由里即外)
SELECT `StudentNo`,`StudentName` FROM student
WHERE StudentNo IN(
SELECT StudentNo result WHERE StudentResult >80 AND SubjectNo =(
SELECT SubjectNo FROM `subject` WHERE `SubjectaName`='高等数学-2'
)
)
8、常用函数
- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回0-1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('2323232') -- 返回字符串长度
SELECT CONCAT('我','233') -- 拼接字符串
SELECT INSERT('java',1,2,'cccc') -- 从某个位置开始替换某个长度
SELECT UPPER('abc')
SELECT LOWER('ABC')
SELECT REPLACE('坚持就能成功','坚持','努力')
-- 查询姓 周 的同学 ，改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'
-- 时间跟日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
9、聚合函数（常用）
7、事务ACID
- 什么是事务：要么都成功，要么都失败
- A 原子性：要么都成功，要么都失败
- C 一致性：事务前后的数据完整性要保持一致
- I 隔离性：事务产生多并发时，互不干扰
- D 持久性：事务一旦提交就不可逆转，被持久化到数据库中
- 隔离产生的问题：
- 1、脏读：指一个事务读取了另外一个事务未提交的数据。
- 2、不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
- 2、虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行）
- 解决以上问题：
- 隔离级别默认：可重复读
7、索引index
重点：在一个表中，主键索引只能有一个，唯一索引可以有多个
-
什么是索引:索引是帮助mysql快速获取数据的数据结构
-
提取句子的主干，就可以得到索引的本质：索引是数据结构
-
主键索引 （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 -- 非全文索引
测试索引sql
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`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`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('用户',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('用户',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100))
SELECT mock_data();
SELECT * FROM app_user WHERE `name`='用户9999' -- 接近半秒
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询99999条记录
-- id _ 表名_字段名
-- create index on 字段
CREATE INDEX id_app_user_name ON app_user(`name`); -- 0.001 s
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询一条记录
索引原则：
- 索引不是越多越好
- 不要对进程数据增加索引
- 小数据量的表不需要增加索引
- 索引一般加在常用的查询字段上
8、权限管理和备份
用户表：mysql.user
本质：对这张表进行，增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER sanjin IDENTIFIED BY '123456'
-- 修改密码（修改当前密码）
SET PASSWORD = PASSWORD('111111')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR sanjin = PASSWORD('111111')
-- 重命名 rename user 原名字 to 新名字
RENAME USER sanjin TO sanjin2
-- 用户授权 ALL PRIVILEGES 全部的权限 库，表
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO sanjin2
-- 查询权限
SHOW GRANTS FOR sanjin2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM sanjin2
-- 删除用户
DROP USER sanjin2
8.1mysql备份
为什么备份：
保证重要数据不丢失
数据转移
MySQL数据库备份的方式
直接拷贝物理文件
在SQLyog这种可视化工具中手动导出
在想要导出的表或者库中，右键选择备份和导出
8.2数据库设计规范
当数据库比较复杂的时候，我们就需要设计了
糟糕的数据库设计：
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图 E-R图
9、使用navicate
- 历史日志，可以显示sql语句，工具——历史日志
数据库设计三大范式：
10、数据库存储引擎
- MYISAM：节约空间，速度较快
- INNODB（默认）：安全，支持事务，多表多用户操作
扫一扫
312
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
