MySQL学习记录（源自bilibili狂神说）

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

提示：这里可以添加本文要记录的大概内容：
例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、数据库基本指令

1、数据库基本指令

命令行操作数据库
1.登陆 ：mysql -u root -p2.查看所有数据库： show tables;
3. 使用数据库：use school;
4. 查看数据库中所有的表：show tables;
5. 显示数据库中所有表的信息：describe student;
6. 创建一个数据库：create databases westos;
8. 退出连接：exit；
9. 注释： – 或者/* */

2. 操作数据库

创建数据库 : create database [if not exists] 数据库名;

删除数据库 : drop database [if exists] 数据库名;

查看数据库 : show databases;

使用数据库 : use 数据库名;
刷新后，左侧teststudy数据库被删除

删除table指令

二、数据库操作

1.数据库的列类型

1.数值
2.字符串

3.时间和日期
4.null

 理解为 "没有值" 或 "未知值"不要用NULL进行算术运算 , 结果仍为NULL

2.数据库字段属性（重点）

UnSigned

无符号的

声明该数据列不允许负数 .

ZEROFILL

0填充的

不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)

通常用于设置主键 , 且为整数类型

可定义起始值和步长

    当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表

    SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL

默认为NULL , 即没有插入该列的数值

如果设置为NOT NULL , 则该列必须有值（否则会报错）

DEFAULT

默认的

用于设置默认值

例如,性别字段,默认为"男" , 否则为 "女" ; 
若无指定该列的值 , 则默认值为"男"的值

2.1 创建表格代码：

CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(50) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(3) NOT NULL DEFAULT 'men'	COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '生日',
`adress` VARCHAR(100) DEFAULT NULL COMMENT '地址',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student; 

若有default关键字，后面必须加null或者’内容’ ！！！！！！！否则报错

3.数据表的类型

CREATE TABLE 表名(
   -- 省略一些代码
   -- Mysql注释
   -- 1. # 单行注释
   -- 2. /*...*/ 多行注释
)ENGINE = MyISAM (or InnoDB)

-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;

常见的 MyISAM 与 InnoDB 类型：
经验 ( 适用场合 ) :

适用 MyISAM : 节约空间及相应速度

适用 InnoDB : 安全性 , 事务处理及多用户操作数据表

3.数据表的存储位置

MySQL数据表以文件方式存放在磁盘中

包括表文件 , 数据文件 , 以及数据库的选项文件

位置 : Mysql安装目录\data\下存放数据表 . 
目录名对应数据库名 , 该目录下文件名对应数据表 .

注意 :

* . frm -- 表结构定义文件

* . MYD -- 数据文件 ( data )

* . MYI -- 索引文件 ( index )

InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件

MyISAM类型数据表对应三个文件 :

4.修改与删除

修改数据库

1.修改表 ( ALTER TABLE )

修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名

添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]

修改字段 :

ALTER TABLE 表名 MODIFY 字段名 列类型[属性]

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

eg:

	--1.修改表名
	ALTER TABLE `grade` RENAME AS gradetest  
	ALTER TABLE `gradetest` RENAME AS grade;
	--2.添加字段
	ALTER TABLE `grade` ADD 'age' INT(2);--错误示例
	ALTER TABLE `grade` ADD age INT(2);--正确示例（添加字段的字段名不能用‘’引住）
	--3.修改字段
	ALTER TABLE `grade` MODIFY `age` VARCHAR(10);
	ALTER TABLE `grade` MODIFY `age` VARCHAR(10);--MODIFY修改字段和属性
	--change可以用来重命名和修改字段属性
	ALTER TABLE `grade` CHANGE `age` age1 INT(2);	
	ALTER TABLE  grade CHANGE age1 age2 INT(2);

2.删除字段 : ALTER TABLE 表名 DROP 字段名

eg:

ALTER TABLE  `grade` DROP `age2`;

3. 删除数据表

   语法：DROP TABLE [IF EXISTS] 表名

eg: 加不加``飘字符都可以实现。

DROP TABLE IF EXISTS grade;
DROP TABLE IF EXISTS `grade2`;

三、DML语言（重点）

1.外键（了解即可）

1.1外键概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

外键作用

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据、约束。
使两张表形成关联，外键只能引用外表中的列的值或使用空值。

1.创建2外键

建表时指定外键约束

-- 创建外键的方式一 : 创建子表同时创建外键

-- 年级表 (id\年级名称)
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

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

建表后修改

-- 创建外键方式二 : 创建子表完毕后,修改子表添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

删除外键

ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

2.DML语言

2.1 INSERT命令

语法：

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')

注意 :

1. 字段或值之间用英文逗号隔开
2. ‘字段1,字段2…’ 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致 。
3. 可同时插入多条数据 , values 后用英文逗号隔开
   eg:

INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
INSERT INTO `subject` (`subjectno`,`subjectname`) VALUES (18,'英语');
INSERT INTO `subject` (`subjectname`) VALUES ('英语');
INSERT INTO `subject`  VALUES ('23','C#',130,1);

2.2UPDATE (修改数据语句)

update命令

语法：

**UPDATE 表名 SET column_name=value [,column_name2=value2,...]
 [WHERE condition];**

注意 :

column_name 为要更改的数据列

value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果

condition 为筛选条件 , 如不指定则修改该表的所有列数据

where条件 : 有条件地从表中筛选数据

eg：

UPDATE `subject` SET `gradeid`=5 WHERE `subjectno` BETWEEN 18 AND 23;
UPDATE `subject` SET `classhour`=4 WHERE  18<=`subjectno`<=23; 

2.3DELETE (删除数据语句)

1、 DELETE命令

语法：

DELETE FROM 表名 [WHERE condition];

注意：condition为筛选条件 , 如不指定则删除该表的所有列数据

2 .TRUNCATE命令

作用：用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;

语法：

TRUNCATE [TABLE] table_name;

DELETE FROM `subject` WHERE `subjectno` BETWEEN 19 AND 29;
TRUNCATE TABLE SUBJECT;

3.区别于DELETE命令

相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快

不同 :

    使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器

    使用TRUNCATE TABLE不会对事务有影响

四、使用DQL查询数据

1.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}];
   -- 指定查询的记录从哪条至哪条

注意 : [ ] 括号代表可选的 , { }括号代表必选得

1.1指定查询字段

– 查询表中所有的数据列结果 , 采用 " * " 符号; 但是效率低，不推荐 .

-- 查询所有学生信息
SELECT * FROM student;

-- 查询指定列(学号 , 姓名)
SELECT studentno,studentname FROM student;

1.2AS 子句作为别名

作用：

可给数据列取一个新别名

可给表取一个新别名

可把经计算或总结的结果用另一个新名称来代替

-- 这里是为列取别名(当然as关键词可以省略)
SELECT studentno AS 学号,studentname AS 姓名 FROM student;

-- 使用as也可以为表取别名
SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;

-- 使用as,为查询结果取一个新名字
-- CONCAT()函数,把姓名拼接到studentname上行程新的字符，再进行后续操作
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

1.3 DISTINCT关键字的使用

作用 : 
去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) ,
只返回一条

SELECT	* FROM result;
SELECT subjectno AS 学科号 FROM result;
SELECT DISTINCT studentno FROM result;--消除重复

2.where条件语句与模糊查询

作用：用于检索数据表中 符合条件 的记录

搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假

SELECT studentno,studentresult FROM result;
-- 查询考试成绩在90以上的
SELECT studentno,studentresult 
FROM result 
WHERE studentresult>=90;
-- 查询考试成绩在95-100之间的
SELECT studentresult,studentresult
FROM result
WHERE studentresult>=95 && studentresult<=100;
-- 模糊查询(对应的词:精确查询)
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;
-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

2.1 LIKE关键字的使用

2.1 in关键字的使用

-- 1、LIKE
-- =============================================
-- 查询姓刘的同学的学号及姓名
-- 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 '%嘉%';

-- =============================================

-- 2、IN
-- =============================================
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');

2.1 null关键字的使用

-- 3、NULL 空
-- =============================================
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

3.连接查询

1.JOIN 对比

操作符名称	描述
INNER JOIN	取相交，两者必须都有
LEFT JOIN	即使右表没有匹配，只要左表中有就返回行
RIGHT JOIN	即使左表没有匹配，只要右表中有就返回行

2.测试

SELECT *FROM result;
SELECT *FROM student;

================联表查询=============================
/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
-- (内连接应用场景)
SELECT s.studentno ,studentname AS '姓名',studentresult AS '成绩' 
FROM student AS s
INNER JOIN result AS r
ON s.studentno=r.studentno
WHERE subjectno=1

-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno=r.studentno
WHERE studentresult=NULL;

-- 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

查询练习：

3.自连接

自连接
   数据表与自身进行连接

需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
    查询父栏目名称和其他子栏目名称
*/

-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

4.排序和分页

/*============== 排序 ================
语法 : ORDER BY
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照ASC升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

*/
eg：
– 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
– 按成绩降序排序

SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC

/*============== 分页 ================
语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好处 : (用户体验,网络传输,查询压力)

推导:
第一页 : limit 0,5
第二页 : limit 5,5
第三页 : limit 10,5
…
第N页 : limit (pageNo-1)*pageSzie,pageSzie
[pageNo:页码,pageSize:单页面显示条数]

*/
– 每页显示5条数据

eg:

5.子查询

============== 子查询 ================
什么是子查询?

在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句

嵌套查询可由多个子查询组成,求解的方式是由里及外;

子查询返回的结果一般都是集合,故而建议使用IN关键字;

– 查询数据库结构-1 的所有考试结果(学号,科目编号,成绩),成绩降序排列

-- 方法一:使用连接查询
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;

-- 方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;

方法二只查询一个表result，在where条件中附加查询subject表格。注意
WHERE subjectno=(
SELECT subjectno FROM subject
WHERE subjectname = ‘数据库结构-1’
)这里是通过subjectno来连接关系的。

eg：！！！！！！！！！！！！！

五、MySQL函数

1.常用函数

1.1数据函数

 SELECT ABS(-8);  /*绝对值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
 SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

1.2字符串函数

 SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
 SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/
 SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); 
  /*替换字符串,从某个位置开始替换某个长度*/
 SELECT LOWER('KuangShen'); /*小写*/
 SELECT UPPER('KuangShen'); /*大写*/
 SELECT LEFT('hello,world',5);   /*从左边截取*/
 SELECT RIGHT('hello,world',5);  /*从右边截取*/
 SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
 SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/ 
 SELECT REVERSE('狂神说坚持就能成功'); /*反转

– 查询姓周的同学,改成邹
SELECT REPLACE(studentname,‘周’,‘邹’) AS 新名字
FROM student WHERE studentname LIKE ‘周%’;

1.3日期和时间函数

 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());

1.3系统信息函数

 SELECT VERSION();  /*版本*/
 SELECT USER();     /*用户*/

2.聚合函数（！！！）

函数名称	描述
COUNT()	返回满足Select条件的记录总和数，如 select count(*) （不建议使用 *，效率低）
SUM()	返回数字字段或表达式列作统计，返回一列的总和。
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段，字符字段或表达式列作统计，返回最大的值。
MIN()	可以为数值字段，字符字段或表达式列作统计，返回最小的值。

测试：

A:

	SELECT COUNT(studentname) FROM student
	SELECT COUNT(*) FROM student
	SELECT COUNT(1) FROM student

– 从含义上讲，count(1) 与 count() 都表示对全部数据行的查询。
–1、 count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
–2、 count() 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
– 3、count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
/*

B:

SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult)最高分,MIN(studentresult)AS 最低分
FROM result r
INNER JOIN `subject` s
ON r.subjectno=s.subjectno
GROUP BY r.subjectno
HAVING subjectname='C#基础'

3.MD5 加密

MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。将数据（如汉字）运算为另一固定长度值，是杂凑算法的基础原理，MD5的前身有MD2、MD3和MD4。

3.1实现数据加密

新建一个表 testmd5

CREATE TABLE testmd5 (
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 testmd5 VALUES(1,‘kuangshen’,‘123456’),(2,‘qinjiang’,‘456789’)

如果我们要对pwd这一列数据进行加密，语法是：

update testmd5 set pwd = md5(pwd);

如果单独对某个用户(如kuangshen)的密码加密：

INSERT INTO testmd5 VALUES(3,‘kuangshen2’,‘123456’)
update testmd5 set pwd = md5(pwd) where name = ‘kuangshen2’;

插入新的数据自动加密

INSERT INTO testmd5 VALUES(4,‘kuangshen3’,md5(‘123456’));

查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对）

SELECT * FROM testmd5 WHERE ‘name’=‘kuangshen’ AND pwd=MD5(‘123456’);

六、 事务和索引

1.事务

1.1什么是事务？？？

事务就是将一组SQL语句放在同一批次内去执行

如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行

MySQL事务处理只支持InnoDB和BDB数据表类型

1.2事务的ACID原则

原子性(Atomic)（要么成功要么失败）

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性(Consist)（针对一个事务操作前与操作后的状态一致）

一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

隔离性(Isolated)（操作相互隔离）

隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性(Durable)（事务一旦提交不可逆）

在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，不可逆。

事务的隔离级别
脏读：
指一个事务读取了另外一个事务未提交的数据不可重复读：

幻读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)：
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
（一般是行影响，如下图所示：多了一行）

1.3基本语法

– 使用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; -- 恢复自动提交

2.索引

索引是帮助MYSQL搞笑获取数据的数据结构！！！！！！！！！！
索引的作用

提高查询速度

确保数据的唯一性

可以加速表和表之间的连接 , 实现表与表之间的参照完整性

使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间

全文检索字段进行搜索优化.

2.1索引分类

主键索引(Primary Key)

主键 : 某一个属性组能唯一标识一条记录

特点 :唯一的标识，主键不可重复，只有一列可被设为主键

唯一索引(Unique)
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别：主键索引只能有一个， 唯一索引可能有多个

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

常规索引(Index)

作用 : 快速定位特定数据

注意 :index 和 key 关键字都可以设置常规索引, 应加在查询找条件的字段不宜添加太多常规索引,影响数据的插入,删除和修改操作

CREATE TABLE `result`(
   -- 省略一些代码
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)

-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

全文索引 (FullText)
作用 : 快速定位特定数据
注意 :

只能用于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;
*/

2.2索引的数据结构

– 我们可以在创建上述索引的时候，为其指定索引类型，分两类
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 等索引；

六、用户管理

使用SQLyog 创建用户，并授予权限演示

基本命令

用户和权限管理 ------------------
用户信息表：mysql.user

– 1.刷新权限
FLUSH PRIVILEGES

2.-- 增加用户 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] …

七、数据库设计

7.1规范化数据库设计

为什么需要数据库设计

当数据库比较复杂时我们需要设计数据库

糟糕的数据库设计 :

数据冗余,存储空间浪费

数据更新和插入的异常

程序性能差

良好的数据库设计 :

节省数据的存储空间

能够保证数据的完整性

方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

需求分析阶段: 分析客户的业务和数据处理需求

概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库步骤

收集信息

    与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.

标识实体[Entity]


    标识数据库要管理的关键对象或实体,实体一般是名词

标识每个实体需要存储的详细信息[Attribute]

标识实体之间的关系[Relationship]

7.2三大范式

问题 : 为什么需要数据规范化?

不合规范的表设计会导致的问题：

信息重复

更新异常

插入异常

    无法正确表示信息

删除异常

    丢失有效信息

三大范式

第一范式 (1st NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2nd NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范化和性能的关系

为满足某种商业目标 , 数据库性能比规范化数据库更重要

在数据规范化的同时 , 要综合考虑数据库的性能

通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间

通过在给定的表中插入计算列,以方便查询