本文详细介绍了MySQL数据库的基础知识，包括连接数据库、数据类型、操作表（创建、修改、删除）、DML（增删改查）、DQL（查询语言）、事务处理、索引和权限管理。特别强调了DQL的使用，如WHERE子句、JOIN操作、子查询以及排序和分页。此外，还讨论了不同类型的索引，如主键、唯一索引和全文索引，以及数据库设计的规范化和事务的ACID原则。
一、基础语句、属性
1.命令行连接
mysql -uroot -p123456 -- 连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -- 修改用户密码
flush privileges; -- 刷新权限
--------------------------
-- 所有语句使用;结尾
show databases;-- 查看所有数据库
mysql> use school -- 切换数据库 use 数据库名
Database changed
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有表的信息
create database westos; -- 创建一个数据库westos
exit; -- 退出连接
-- 单行
/* 多行注释 */
2.操作数据库（了解即可）
[]表示可选
-
创建数据库
create database [if not exists] westos; -
删除数据库
drop database [if not exists] westos; -
使用数据库
-- tab上的漂符号` 用来括住字段名和表名，防止和关键词冲突 use `school` -
查看数据库
show database; -- 查看所有数据库
3.数据类型
1.数值
| 数据类型 | 所占字节 |
|---|---|
| tinyint | 1 |
| smallint | 2 |
| mediumint | 3 |
| int | 4 |
| bigint | 8 |
| float | 4 |
| double | 8 |
| decimal | a/b*4 (decimal(a,b)) |
整型特点：
① 都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
② 如果超出了范围，会报out or range异常，插入临界值
③ 长度可以不指定，默认会有一个长度
长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型
小数特点：
①M代表整数部位+小数部位的个数，D代表小数部位
②如果超出范围，则报out or range异常，并且插入临界值
③M和D都可以省略，但对于定点数，M默认为10，D默认为0
④如果精度要求较高，则优先考虑使用定点数
2.字符型
| 数据类型 | 大小 | 描述 |
|---|---|---|
| char | 0~255 | 字符 |
| varchar | 0~65535 | 常用 |
| tinytxt | 2^8-1 | 微型文本 |
| txt | 2^16-1 | 保存大文本 |
char、varchar、binary、varbinary、enum、set、text、blob
char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1
varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略
3.日期型
Java包java.util.Data
-
year 年
-
date 日期 YYYY-MM-DD
-
time 时间 HH:mm:ss
-
datetime 日期+时间 YYYY-MM-DD HH:mm:ss
占8
-
timestamp 日期+时间 时间戳 1970.1.1到现在的毫秒数 占4 比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间 也更为常用
4.null
-
没有值，未知
-
注意不要用NULL值进行运算，结果为NULL
4.字段
unsigned
- 无符号整数
- 声明了该列不能声明为负数
zerofill
- 0填充
- 不足位数用0填充 int(3) 5 – 005
自增
- 自动在上一条记录的基础上+1
- 可自定义设计主键增长的起始值和步长
非空 NOT NULL
非空，该字段的值必填
其他
UNIQUE：唯一，该字段的值不可重复
DEFAULT：默认，该字段的值不用手动插入有默认值
CHECK：检查，mysql不支持
PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null
FOREIGN KEY：外键，该字段的值引用了另外的表的字段
规范
每一个表都要存在以下字段
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
5.操作表
1.创建表
create table [if not exists] `表名`(
`字段名` 列属性 [属性] [索引] [注释],
`字段名` 列属性 [属性] [索引] [注释],
......
`字段名` 列属性 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
-- 例子
CREATE TABLE `course` (
`course_id` VARCHAR(10) NOT NULL COMMENT '课程号',
`course_name` VARCHAR(20) NOT NULL COMMENT '姓名',
`course_teacher_id` VARCHAR(20) NOT NULL COMMENT '任课教师号',
`credit_hour` INT(3) NOT NULL COMMENT '学时',
`credit_point` INT(2) NOT NULL COMMENT '学分',
`school_time` DATETIME NOT NULL COMMENT '上课时间',
`class_place` VARCHAR(50) NOT NULL COMMENT '上课地点',
`exam_time` DATETIME NOT NULL COMMENT '考试时间',
PRIMARY KEY (`course_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
show create database `school` -- 查看创建数据库的语句
show create table `student` -- 查看student数据表的定义语句
desc student -- 显示表的结构
2.修改表
修改
-- 修改表名：alter table 旧表名 rename as 新表名
alter table `teacher` rename as `teacher1`
-- 增加表的字段：alter table 表名 add 字段名 列属性
alter table `teacher1` add age int(11)
-- 修改表的字段(重命名 修改约束)
-- alter table 表名 modify 字段名 列属性[]
alter table `teacher1` modify age varchar(11) -- 修改约束
-- alter table 表名 change 旧名字 新名字 列属性[]
alter table `teacher1` change age age1 int(1) -- 字段重命名
-- 删除表的字段：alter table 表名 drop 字段名
alter table `teacher1` drop age1
3.删除表
删除
-- 删除表 (如果存在)
drop table if exists `teacher1`
4.外键约束
-
什么是外键？
外键是相对于主键说的，是建立表之间的联系的必须的前提，比如有两张表，student（学生）表和grade（年级）表，student 中 id 是主键，而 gradeid 是依赖于 student 中的 id ，那么 grade 中的 id 就是 student 的外键；我们可以通过外键使两张表进行关联。 -
外键的作用是什么？
使两张表形成关联，外键只能引用外表中的列的值，可以使得两张表关联，保证数据的一致性和实现一些级联操作。
总结：
1）为了一张表记录的数据不要太过冗余；
2）保持数据的一致性、完整性。
增加约束(物理外键，实际开发中一般不用)
创建一张年级表
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名次',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 学生表的 id 字段要去引用年级表的 gradeid 字段
-- 定义外键key
-- 给最高外键添加约束(执行引用) references 引用
-- >> 方式一：创建表时增加约束（麻烦，复杂）
CREATE TABLE `student` (
`id` VARCHAR(20) NOT NULL COMMENT '学号',
`name` VARCHAR(20) NOT NULL COMMENT '姓名',
`gradeud` INT(10) NOT NULL COMMENT '年级号',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- >> 方式二：床键表成功后，添加外键约束
CREATE TABLE `student` (
`id` VARCHAR(20) NOT NULL COMMENT '学号',
`name` VARCHAR(20) NOT NULL COMMENT '姓名',
`gradeud` INT(10) NOT NULL COMMENT '年级号',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 创建表时没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
-- alter table 表 add constraint 约束名 foreign key (`作为外键的列`) references 哪个表(`哪个字段`)
-- >> 删除有外键关系的表的时候，必须先删除引用别人的表（从表），再删除被引用的表（主表）
-- >> 建立物理外键后，删除grade表时需删除student表
以上操作都是物理外键，数据库级别的外键，不建议使用
避免数据库过多造成困扰，了解即可
一般也不推荐使用外键约束，阿里巴巴的Java规范下是：强制不得使用外键，一切外键在应用层解决，原因就是在DELETE或者UPDATE时都要考虑外键约束，导致开发变得痛苦很多！
5.主键
数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的ID列作为主键.
表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录
语法
PRIMARY KEY (`字段名`)
拓展
MySQL—为什么需要主键？主键为什么最好是单调递增的？
mysql 添加主键和外键【深度解析】
关于业务主键和逻辑主键
6.数据表的类型
-- 关于数据库引擎
/*
INNODB 默认使用，现在使用最多
MYISAM 早些年使用的
*/
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持(1.2.x版本开始支持全文检索) |
| 表空间的大小 | 较小 | 较大（约为2倍） |
常规使用操作：
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户操作
MySQL引擎在物理文件上的区别
- InnoDB 在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata1 文件
- MYISAM 对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
设置数据库表的字符集编码
CHARSET=utf8
不是指的话，会是mysql默认的字符集编码，不支持中文
MySQL的默认编码是Latin1，不支持中文
- 也可以在my.ini中配置默认的编码（不推荐）
character-set-server=utf8
二、DML
DML：数据操纵语言（Data Manipulation Language），它可以实现对数据库的基本操作。
数据库意义：数据存储，数据管理
- insert
- update
- delete
1.添加/insert
语法：INSERT INTO 表名([字段名1,字段2,字段3,...]) VALUES ('值1','值2'...),('值3'，....)…
-- 给上面的student表插入数据
INSERT INTO `student`(`name`) VALUES ('张三')
-- 如果不写表的字段，他就会一一匹配，这条语句就会报错！
INSERT INTO `studnet` VALUES('李四')
-- 插入多个字段
INSERT INTO `student`(`id`,`name`) VALUES ('1001','张三')
INSERT INTO `student`(`id`,`name`) VALUES ('1001','李四'),('1001','王五')
- 注意：
1）字段和字段之间使用英文逗号隔开；
2）字段是可以省略的，但是后面的值必须要要一一对应，不能缺少数据；
3）可以同时插入多条数据，VALUES 后面的值，需要使用逗号隔开。
2.修改/update
语法：UPDATE 表名 SET colnum_name = value,[colnum_name = value,....] WHERE [条件]
-- 修改student表name字段
UPDATE `student` SET `name`='小明' WHERE id = 1;
-- 不指定条件的情况下，会改动所有表
UPDATE `student` SET `name`='小红';
-- 修改多个属性,逗号隔开
UPDATE `student` SET `name`='小强',`email`='123456@qq.com' WHERE id = 1;
- 注意：
1）WHERE 条件子句 ：id 等于某个值，或大于某个值，表示在某个区间内修改；
2）colnum_name 是数据库的列，尽量带上`` ；
3）条件，筛选的条件，如果没有指定，则会修改所有的列；
4）value，是一个具体的值，也可以是一个变量；
5）多个设置的属性之间，使用英文逗号隔开。
通过多个条件定位数据
UPDATE `student` SET `name`='小强' WHERE `name`='张三' AND `id`=1
3.删除/delete
语法：DELETE FROM 表名 [WHERE 条件]
-- 删除数据 （这样写，会全部删除）
DELETE FROM `student`;
-- 删除指定数据
DELETE FROM `student` WHERE id = 1;
完全清空一个数据库表（TRUNCATE）
--清空 student 表
TRUNCATE `student`;
DELETE 和 TRUNCATE：
**相同点：**都能删除数据，都不会删除表结构
不同点： DELETE ，不会影响自增；TRUNCATE ，重新设置自增列，计数器会归零 。
三、DQL
DQL：数据查询语言（Data Query Language，所有的查询操作都用它，数据库中最核心的语言，最重要的语句，使用频率最高的语句。
1.指定查询字段
-- 查询全部的学生 SELECT 字段 FROM 表
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 给查询结果赋值一个新的名字 AS 可以给字段起别名，也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s
-- 函数 Concat（a，b），给查询结果拼接一个字符串
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student
有的时候，列名字不是那么的见名知意，
所以我们赋值一个新的名字，语法：字段名 AS 别名 表名 AS 别名
SELECT完整语法
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条
2.去重 （DISTINCT）
去除SELECT 查询出来的结果中重复的数据，重复的数据只显示一条
SELECT DISTINCT `StudentNo` FROM result
3.数据库的列（表达式）
-- 查询系统版本 （函数）
SELECT VERSION()
-- 用来计算 （表达式）
SELECT 100*3-1 AS 计算的结果为
-- 查询自增的步长 (变量)
SELECT @@auto_increment_increment
-- 给student表的考试成绩都 + 1分
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result
4.WHERE条件子句
用来检索数据中符合条件的值
MySQL官网的函数和运算符说明：点我
常用的：
| 符号 | 描述 | 语法 |
|---|---|---|
| <> 或 != | 不等于 | id <> 3、id != 3 |
| BETWEEN … and … | 在某个范围内 | BETWEEN A and B |
| AND、&& | 逻辑与 | a and b 、 a&&b |
| OR | 逻辑或 | a or b |
| Not ！ | 逻辑非 | not a ！a |
| IS NULL | 如果操作符为 NUll, 结果为真 | a is null |
| IS NOT NULL | 如果操作符不为 null，结果为真 | a is not null |
| BETWEEN | 若a 在 b 和c 之间，则结果为真 | a between b and c |
| LIKE | SQL 匹配，如果a匹配b，则结果为真 | a like b |
| In | a在a1，或者a2…. 其中的某一个值中，结果为真 | a in (a1,a2,a3….) |
应用: 模糊查询
-- 查询考试成绩在 95~100 分之间
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100
-- 模糊查询（区间）
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- 查询学号不为1000的学生成绩
SELECT studentNo,`StudentResult` FROM result
WHERE studentNo!=1000;
SELECT studentNo,`StudentResult` FROM result
WHERE NOT studentNo = 1000
-- 查询姓张的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName 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 '%坤%'
-- 查询 1001,1002,1003号学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- 查询地址为北京的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN ('北京');
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address='' OR address IS NULL
-- 查询有出生日期的同学，不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL
-- 查询没有登记出生日期的同学，为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NULL
- 注意：
WHERE IN()查询时，IN()里面是具体的值，如果地址是北京市海淀区，当你输入WHERE Address IN ('北京')，结果是查询不到的，只能采用WHERE Address LIKE '%北京%'。
5.联表查询（JOIN）
LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法
- 思路：
1）分析需求，分析查询的字段来自哪些表？并进行连接查询；
2）确定使用上图的哪种连接查询？
3）确定交叉点（这两个表中哪个数据是相同的；
4）判断的条件
-- INNER JOIN
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNO = r.studentNO
-- Right JOIN
SELECT s.studentNO,studentName,SubjectNo,StudentResult
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
- 注意：
| JOIN | 描述 |
|---|---|
| INNER JOIN | 如果表中有至少一个匹配，则返回行 |
| LEFT JOIN | 即使右表中没有匹配，也从左表返回所有的行 |
| RIGHT JOIN | 即使左表中没有匹配，也从右表返回所有的行 |
- 总结：
联表查询思路：
1）首先确定要查询哪些数据：SELECT ...
2）从哪几个表中查FROM 表 XXX JOIN 连接的表 on 交叉条件
3）假设存在一种多张表查询，先查询两张表，然后再慢慢增加。
6.自连接
自己的表和自己的表连接，将一张表拆为两张一样的表即可
假设一张表：
| categoryid | pid | categoryName |
|---|---|---|
| 2 | 1 | 水果 |
| 3 | 1 | 蔬菜 |
| 4 | 3 | 生菜 |
| 5 | 1 | 肉类 |
| 6 | 3 | 青菜 |
| 7 | 5 | 猪肉 |
| 8 | 2 | 苹果 |
拆分成两张表：
| categoryid | categoryName |
|---|---|
| 2 | 水果 |
| 5 | 肉类 |
| 3 | 蔬菜 |
| pid | categoryid | categoryName |
|---|---|---|
| 4 | 3 | 生菜 |
| 6 | 3 | 青菜 |
| 7 | 5 | 猪肉 |
| 8 | 2 | 苹果 |
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 蔬菜 | 青菜，生菜 |
| 水果 | 苹果 |
| 肉类 | 猪肉 |
-- 通过别名将一张表看作两个一摸一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`
7.排序与分页
- 排序（ORDER BY）
-- 升序 ASC ， 降序DESC
ORDER BY StudentResult ASC
ORDER BY StudentResult DESC
- 分页（LIMIT）
缓解数据库压力，给人的体验更好
语法： LIMIT (查询起始下标，pageSize)
第一页 LIMIT 0,5 （1-1）*5
第二页 LIMIT 5,5 （2-1）*5
第三页 LIMIT 10,5 （3-1）*5
第N页 LIMIT 0,5 （n-1）* pageSize，pageSize
pageSize：页面大小
(n-1)* pageSize:起始值
n ：当前页
数据总数/页面大小 = 总页数
8.子查询
在where语句中嵌套一个子查询语句，查询高数考试学生的成绩、学号，成绩降序排序
-- 方式一： 使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE SubjectName = '高数'
ORDER BY StudentResult DESC
-- 方式二： 使用子查询（由里及外） -- 查询所有数据库结构-1 的学生学号
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result` WHERE SubjectNo = (
SELECT SubjectNo FROM `subject`
WHERE SubjectName = '高数'
) ORDER BY StudentResult DESC
9.过滤分组
-- 通过什么字段来分组
GROUP BY xxx
-- 过滤分组后的信息，需要满足的条件，次要条件
HAVING xxx
四、函数
1.常用函数
- 数学运算
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('啊哈哈哈哈哈') -- 字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
SELECT INSERT('你好世界',1,2,'Hello') -- 从某个位置开始替换某个长度 你好世界-->Hello世界
SELECT LOWER('Hello') -- 小写字母
SELECT UPPER('Hello') -- 大写字母
SELECT INSTR('kuangshen','h') -- 返回第一次出现的子串的索引
SELECT REPLACE('你好世界','你好','保护') -- 替换出现的指定字符串，将'你好'替换成'保护'
SELECT SUBSTR('今天天气真的很好',4,3) -- 返回指定的子字符串 (截取的位置，截取的长度)
SELECT REVERSE('你爱我') -- 反转
-- 查询姓张的同学，将姓改为李
SELECT REPLACE(studentname,'张','李') FROM student WHERE studentname LIKE '张%'
- 时间和日期函数
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME() -- 本地时间
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() -- 版本号
2.聚合函数
COUNT()：计数
COUNT(字段)：会忽略所有的 null 值
COUNT(*)：不会忽略 null 值， 本质是计算行数
COUNT(1)：不会忽略 null 值， 本质是计算行数
SUM()：求和
AVG()：平均值
MAX()：最大值
MIN()：最小值
-- 统计student`BornDate`字段个数
SELECT COUNT(`BornDate`) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM result;
-- 计算student成绩的总和、平均分、最高分、最低分
SELECT SUM(`StudentResult`) AS 总和 FROM result
SELECT AVG(`StudentResult`) AS 平均分 FROM result
SELECT MAX(`StudentResult`) AS 最高分 FROM result
SELECT MIN(`StudentResult`) AS 最低分 FROM result
3.MD-5加密
主要增强算法复杂性和不可逆性
-- 创建一个test表
CREATE TABLE `test`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入数据，即明文和密码
INSERT INTO test VALUES(1,'zhangsan','123456'),(2,'lisi','123456'), (3,'wangwu','123456')
-- 加密
-- 加密第一个人的密码
UPDATE test SET pwd=MD5(pwd) WHERE id = 1
-- 加密全部的密码
UPDATE test SET pwd=MD5(pwd)
-- 插入数据时加密
INSERT INTO test VALUES(4,'xiaoming',MD5('123456'))
-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM test WHERE `name`='xiaoming' AND pwd=MD5('123456')
-- 加密后：
五、事务
-
概述：
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等，此时这些数据库操作语句就构成一个事务！
-
条件：
事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
事务原则：SCID 原则 原子性，一致性，隔离性，持久性
原子性(Atomic)
- 要么都成功，要么都失败
- 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性(Consist)
- 事物前后的数据完整性要保证一致
- 一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。
隔离性(Isolated)
- 每个操作每个事务之间相互隔离
- 隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
隔离所导致的一些问题
脏读：
指一个事务读取了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。
虚读（幻读）：
是指一个事务内读取到了别的事务插入的数据，导致前后读取不一致
持久性(Durable)
- 事务一旦提交则不可逆，被持久化数据库中
- 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
基本语法
-- 使用set语句来改变自动提交模式
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION
-- 提交一个事务给数据库
COMMIT
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
模拟事务
/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交
六、索引
在一个表中，主键索引只能有一个，唯一索引可以有多个
作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
分类
- 主键索引 (Primary Key)
- 唯一索引 (Unique)
- 常规索引 (Index)
- 全文索引 (FullText)
1.主键索引
主键 : 某一个属性组能唯一标识一条记录
特点 :
- 最常见的索引类型
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
2.唯一索引
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别
- 主键索引只能有一个
- 唯一索引可能有多个
CREATE TABLE `Grade`(
`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE KEY `GradeID` (`GradeID`)
)
3.常规索引
作用 : 快速定位特定数据
注意 :
- index 和 key 关键字都可以设置常规索引
- 应加在查询找条件的字段
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作
CREATE TABLE `result`(
-- 省略一些代码
INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
4.全文索引
百度搜索：全文索引
作用 : 快速定位特定数据
注意 :
- 只能用于MyISAM类型的数据表
- 只能用于CHAR , VARCHAR , TEXT数据列类型
- 适合大型数据集
/*
#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;
*/
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
5.拓展：测试索引
建表app_user：
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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
批量插入数据：100w
DROP FUNCTION IF EXISTS mock_data;
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 = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 992759
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
创建索引
CREATE INDEX idx_app_user_name ON app_user(name);
测试普通索引
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_name
key: idx_app_user_name
key_len: 203
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
6.索引准则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
7.索引的数据结构
-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
七、权限管理和备份
1.权限
基本命令
/* 用户和权限管理 */ ------------------
用户信息表：mysql.user
-- 刷新权限
FLUSH PRIVILEGES
-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
- 只能创建用户，不能赋予权限。
- 用户名，注意引号：如 'user_name'@'192.168.1.1'
- 密码也需引号，纯数字密码也要加引号
- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
-- 设置密码
SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码
-- 删除用户 DROP USER kuangshen2
DROP USER 用户名
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有权限
- *.* 表示所有库的所有表
- 库名.表名 表示某库下面的某表
-- 查看权限 SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
-- 查看当前用户权限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
权限解释
-- 权限列表
ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限
ALTER -- 允许使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存储的子程序
CREATE -- 允许使用CREATE TABLE
CREATE ROUTINE -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE
CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允许使用CREATE VIEW
DELETE -- 允许使用DELETE
DROP -- 允许使用DROP TABLE
EXECUTE -- 允许用户运行已存储的子程序
FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允许使用CREATE INDEX和DROP INDEX
INSERT -- 允许使用INSERT
LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS -- 允许使用SHOW FULL PROCESSLIST
REFERENCES -- 未被实施
RELOAD -- 允许使用FLUSH
REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT -- 允许使用SELECT
SHOW DATABASES -- 显示所有数据库
SHOW VIEW -- 允许使用SHOW CREATE VIEW
SHUTDOWN -- 允许使用mysqladmin shutdown
SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE -- 允许使用UPDATE
USAGE -- “无权限”的同义词
GRANT OPTION -- 允许授予权限
/* 表维护 */
-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
2.备份
数据库备份必要性
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份方法
- mysqldump备份工具
- 数据库管理工具,如SQLyog
- 直接拷贝数据库文件和相关配置文件
mysqldump客户端
作用 :
- 转储数据库
- 搜集数据库进行备份
- 将数据转移到另一个SQL服务器,不一定是MySQL服务器
-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
可以-w携带备份条件
-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件
3.规范化数据库设计
为什么需要数据库设计
当数据库比较复杂时我们需要设计数据库
糟糕的数据库设计 :
- 数据冗余,存储空间浪费
- 数据更新和插入的异常
- 程序性能差
良好的数据库设计 :
- 节省数据的存储空间
- 能够保证数据的完整性
- 方便进行数据库应用系统的开发
软件项目开发周期中数据库设计 :
- 需求分析阶段: 分析客户的业务和数据处理需求
- 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
设计数据库步骤
- 收集信息
-
- 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
- 标识实体[Entity]
-
- 标识数据库要管理的关键对象或实体,实体一般是名词
- 标识每个实体需要存储的详细信息[Attribute]
- 标识实体之间的关系[Relationship]
4.三大范式
问题 : 为什么需要数据规范化?
不合规范的表设计会导致的问题：
- 信息重复
- 更新异常
- 插入异常
-
- 无法正确表示信息
- 删除异常
-
- 丢失有效信息
三大范式
第一范式 (1st NF)
第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式
第二范式(2nd NF)
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
第二范式要求每个表只描述一件事情
第三范式(3rd NF)
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范化和性能的关系
为满足某种商业目标 , 数据库性能比规范化数据库更重要
在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询
八、JDBC
JDBC（概述、Java需要的数据库驱动包下载以及在IDEA中的导入、第一个JDBC程序、提取工具类、常用对象、statement对象详述）
JDBC（SQL注入问题、PreparedStatement对象、IDEA连接数据库、Java代码实现事务、数据库连接池）
2257
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
