1、初识数据库

1.1 数据库的概念

数据库（DB database）：数据仓库、软件、在装在操作系统上。可以存储大量数据，500万条以下，若超过500w需要进行索引优化

1.2 数据库的分类

关系型数据库：（SQL）

  • MySQL、Oracle、SQL Server、DB2
  • 通过表和表之间、行和列之间的管理进行数据存储

非关系型数据库：（NoSQL）

  • Redis、MongoDB
  • 对象存储，通过对象的自身属性来决定

2 MySQL下载与安装

2.1 下载地址

[官网下载地址]:MySQL :: MySQL Downloads

提取码：q844

2.2 安装步骤（压缩包安装）

1、下载解压zip压缩包（不建议使用.exe安装）

2、配置MySQL系统环境变量，在系统变量下的path中填写mysql安装目录的bin文件夹路径

3、在安装目录下创建一个my.ini配置文件

  1. [mysqld]
  2. #设置3306端口
  3. port=3306
  4. #设置mysql的安装目录
  5. basedir=D:\\mysql\\mysql8 
  6. #设置mysql数据库的数据的存放目录
  7. datadir=D:\\mysql\\mysql8\\Data   
  8. #更改时区
  9. default-time_zone = '+8:00'
  10. #允许最大连接数
  11. max_connections=200
  12. #允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
  13. max_connect_errors=10
  14. #服务端使用的字符集默认为UTF8
  15. character-set-server=utf8
  16. #创建新表时将使用的默认存储引擎
  17. default-storage-engine=INNODB
  18. #默认使用“mysql_native_password”插件认证
  19. default_authentication_plugin=mysql_native_password
  20. [mysql]
  21. #设置mysql客户端默认字符集
  22. default-character-set=utf8
  23. [client]
  24. #设置mysql客户端连接服务端时默认使用的端口，可能和VMware的端口冲突，可自行修改
  25. port=3306
  26. default-character-set=utf8
  27. [WinMySQLAdmin]
  28. Server=D:\mysql\mysql8\bin\mysqld.exe
  29. skip-grant-tables    
  30. #设置将mysql的服务添加到注册表中，反正我当时没加这句话初始化有问题，目录照猫画虎，单双杠自己选
  31. #第一次登录mysql可以跳过密码输入
  32. skip-grant-tables

4、以管理员权限启动CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)

5、再输入 mysqld —initialize-insecure —user=mysql 初始化数据文件

6、然后再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面 （第一次不需要输入密码）

7、进入界面后更改root密码

  1. update mysql.user set authentication_string=password('123456') where user='root'and Host = 'localhost';

8、 刷新数据库 删除my.ini配置文件的最后一行“skip-grant-tables”

  1. flush privileges;

9、重启连接

  1. net stop mysql
  2. net start mysql

10、连接成功界面

11、安装SQLyog或navicat

3、MySQL数据类型

3.1 数值类型

3.2 字符串类型

3.3 日期和时间

4、基本命令

4.1 操作数据库

  1. create database if not exists dbName; #创建数据库
  2. show create database dbName #查询创建数据库的语句
  3. drop database if exists dbName #删除数据库
  4. show databases; #显示所有数据库
  5. use dbName；#打开某个数据库

4.2 操作数据表

4.2.1 创建表

  1. -- 创建学生信息表 (学号,姓名,性别,出生日期)
  2. CREATE TABLE if not exists `student` (
  3. `studentno` INT(4) NOT NULL COMMENT '学号',
  4. `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  5. `sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
  6. `birthday` DATETIME DEFAULT NULL COMMENT '生日'
  7. ) ENGINE=INNODB DEFAULT CHARSET=utf8
  9. show create table student #查询数据表的定义语句
  10. desc student #显示表的结构

4.2.2 数据表的类型

4.2.3 修改表

  1. #修改表名
  2. ALTER TABLE 旧表名 RENAME AS 新表名
  3. #添加字段名
  4. ALTER TABLE 表名 ADD 字段名 列属性[属性]
  5. #修改字段名
  6. ALTER TABLE 表名 MODIFY 字段名 列类型[属性]             #修改字段名的属性
  7. ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]   #重命名
  8. #删除字段
  9. ALTER TABLE 表名 DROP 字段名

4.2.4 删除表

  1. DROP TABLE [IF EXISTS] 表名

4.3 MySQL数据管理

4.3.1 外键（不建议使用）

创建外键：

方式一：创建子表同时创建外键

  1. -- 年级表 (id\年级名称)
  2. CREATE TABLE `grade` (
  3. `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
  4. `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
  5. PRIMARY KEY (`gradeid`)
  6. ) ENGINE=INNODB DEFAULT CHARSET=utf8
  8. -- 学生信息表 (学号,姓名,性别,年级,出生日期)
  9. CREATE TABLE `student` (
  10. `studentno` INT(4) NOT NULL COMMENT '学号',
  11. `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  12. `sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
  13. `gradeid` INT(10) DEFAULT NULL COMMENT '年级',
  14. `borndate` DATETIME DEFAULT NULL COMMENT '生日',
  15. `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  16. PRIMARY KEY (`studentno`),
  17. KEY `FK_gradeid` (`gradeid`),
  18. CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
  19. ) ENGINE=INNODB DEFAULT CHARSET=utf8

方式二： 创建子表完毕后,修改子表添加外键

  1. ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

删除外键：

删除具有主外键关系的表时 , 要先删子表 , 后删主表

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

4.3.2 添加

  1. INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
  2. /*
  3. 字段或值之间用英文逗号隔开 .
  4. ’ 字段1,字段2…’ 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致 .
  5. 可同时插入多条数据 , values 后用英文逗号隔开 .
  6. */

4.3.3 修改

  1. UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHEREcondition];
  2. UPDATE grade SET gradename = '高中' WHERE gradeid = 1;

4.3.4 删除

  1. DELETE FROM 表名 [WHERE condition];
  2. DELETE FROM grade WHERE gradeid = 5
  3. #condition为筛选条件 , 如不指定则删除该表的所有列数据
  5. TRUNCATE命令：用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 
  6. TRUNCATE table table_name;

注：truncate与delete的区别

  • 都能删除数据 , truncate不删除表结构，而delete是删除整张表 , 且TRUNCATE速度更快
  • 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器

4.4 DQL查询

4.4.1 指定查询字段

  1. -- selcet查询中可以使用表达式
  2. SELECT @@auto_increment_increment; -- 查询自增步长 默认自增1
  3. SELECT VERSION(); -- 查询版本号
  4. SELECT 100*3-1 AS 计算结果; -- 表达式
  6. -- 查询所有学生信息
  7. SELECT * FROM student;
  9. -- 查询指定列(学号 , 姓名)
  10. SELECT studentno,studentname FROM student;
  12. -- 这里是为列取别名(当然as关键词可以省略)
  13. SELECT studentno AS 学号,studentname AS 姓名 FROM student;
  15. -- 使用as也可以为表取别名
  16. SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;
  18. -- 使用as,为查询结果取一个新名字
  19. -- CONCAT()函数拼接字符串
  20. SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;
  22. -- DISTINCT 去除重复项 , (默认是ALL)
  23. SELECT DISTINCT studentno FROM result; 
  25. -- 学员考试成绩集体提分一分查看
  26. SELECT studentno,StudentResult+1 AS '提分后' FROM result;

4.4.2 where条件字句

逻辑操作符：

  1. -- 满足条件的查询(where)
  2. SELECT Studentno,StudentResult FROM result;
  4. -- 查询考试成绩在95-100之间的
  5. SELECT Studentno,StudentResult FROM result WHERE StudentResult>=95 AND StudentResult<=100;
  7. -- AND也可以写成 &&
  8. SELECT Studentno,StudentResult FROM result WHERE StudentResult>=95 && StudentResult<=100;
  10. -- 模糊查询(对应的词:精确查询)
  11. SELECT Studentno,StudentResult FROM result WHERE StudentResult BETWEEN 95 AND 100;
  13. -- 除了1000号同学,要其他同学的成绩
  14. SELECT studentno,studentresult FROM result WHERE studentno!=1000;
  16. -- 使用NOT
  17. SELECT studentno,studentresult FROM result WHERE NOT studentno=1000;

比较操作符：

  1. /*
  2. ------like-------
  3. */
  4. -- like结合使用的通配符 : % (代表0到任意个字符)   _ (匹配一个字符)
  6. -- 查询所有姓陈的同学
  7. SELECT studentno,studentname FROM student WHERE studentname LIKE '陈%';
  9. -- 查询姓卿的同学,后面只有一个字的
  10. SELECT studentno,studentname FROM student WHERE studentname LIKE '卿_';
  12. -- 查询姓名中含有 卿 字的
  13. SELECT studentno,studentname FROM student WHERE studentname LIKE '%卿%';
  15. /*
  16. ------in-------
  17. */
  18. -- 查询学号为1000,1001,1002的学生姓名
  19. SELECT studentno,studentname FROM student WHERE studentno IN (1000,1001,1002);
  21. -- 查询地址在北京,南京,河南洛阳的学生
  22. SELECT studentno,studentname,address FROM student WHERE address IN ('北京','南京','河南洛阳');
  25. /*
  26. ------NULL 空-------
  27. */
  28. -- 查询出生日期没有填写的同学
  29. -- 不能直接写=NULL , 这是错误的 , 用 is null
  30. SELECT studentname FROM student WHERE BornDate IS NULL;
  32. -- 查询出生日期填写的同学
  33. SELECT studentname FROM student WHERE BornDate IS NOT NULL;
  35. -- 查询没有写家庭住址的同学(空字符串不等于null)
  36. SELECT studentname FROM student WHERE Address='' OR Address IS NULL;

4.4.3 联表查询 join

  1. /*
  2. 连接查询
  3.    如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
  4. 内连接 inner join
  5.    查询两个表中的结果集中的交集
  6. 外连接 outer join
  7.    左外连接 left join
  8.        (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
  9.    右外连接 right join
  10.        (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
  12. 等值连接和非等值连接
  13. */
  15. -- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
  16. SELECT s.studentno,studentname,subjectno,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno
  18. -- 右连接(也可实现)
  19. SELECT s.studentno,studentname,subjectno,StudentResult FROM student s RIGHT JOIN result r ON r.studentno = s.studentno
  21. -- 等值连接
  22. SELECT s.studentno,studentname,subjectno,StudentResult FROM student s , result r WHERE r.studentno = s.studentno
  24. -- 左连接 (查询了所有同学,不考试的也会查出来)
  25. SELECT s.studentno,studentname,subjectno,StudentResult FROM student s LEFT JOIN result r ON r.studentno = s.studentno
  27. -- 查一下缺考的同学(左连接应用场景)
  28. SELECT s.studentno,studentname,subjectno,StudentResult FROM student s LEFT JOIN result r ON r.studentno = s.studentno
  29. WHERE StudentResult IS NULL
  1. /*
  2. 自连接（了解）
  3.    数据表与自身进行连接
  5. 需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
  6.     查询父栏目名称和其他子栏目名称
  7. */
  9. -- 创建一个表
  10. CREATE TABLE `category` (
  11. `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  12. `pid` INT(10) NOT NULL COMMENT '父id',
  13. `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
  14. PRIMARY KEY (`categoryid`)
  15. ) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
  17. -- 插入数据
  18. INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
  19. VALUES('2','1','信息技术'),
  20. ('3','1','软件开发'),
  21. ('4','3','数据库'),
  22. ('5','1','美术设计'),
  23. ('6','3','web开发'),
  24. ('7','5','ps技术'),
  25. ('8','2','办公信息');
  27. -- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
  28. -- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
  29. SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目' FROM category AS a,category AS b WHERE a.`categoryid`=b.`pid`

4.4.4 分页和排序

  1. /*
  2. ------排序-------
  3.  ORDER BY 语句用于根据指定的列对结果集进行排序。
  4.    ORDER BY 语句默认按照ASC升序对记录进行排序。按照降序对记录进行排序，可以使用 DESC 关键字。
  5. */
  6. SELECT * FROM student ORDER BY StudentResult DESC  #按StudentResult降序
  8. /*
  9. ------分页-----------
  10. limit 页面起始显示值，页面的大小
  11. pageNo：当前页 pageSzie：页面大小
  12. limit (pageNo-1)*pageSzie,pageSzie
  13.    第一页 : limit 0,5（1-5）
  14.    第二页 : limit 5,5（6-10）
  15.    第三页 : limit 10,5
  16. */
  17. SELECT * FROM result  order by grade LIMIT 0,5

4.4.5 子查询

  1. SELECT *FROM student WHERE studentno=(SELECT studentno FROM result WHERE grade=9)
  3. -- 分步写简单sql语句,然后将其嵌套起来
  4. SELECT studentno,studentname FROM student WHERE studentno IN(
  5.    SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
  6.        SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
  7.   )
  8. )

5、MySQL函数

5.1 常用函数

5.1.1 数学运算

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

5.1.2 字符串函数

  1.  SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
  2.  SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/
  3.  SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替换某个长度*/
  4.  SELECT LOWER('KuangShen'); /*小写*/
  5.  SELECT UPPER('KuangShen'); /*大写*/
  6.  SELECT LEFT('hello,world',5);   /*从左边截取*/
  7.  SELECT RIGHT('hello,world',5);  /*从右边截取*/
  8.  SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
  9.  SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
  10.  SELECT REVERSE('狂神说坚持就能成功'); #反转
  12.  -- 查询姓周的同学,改成邹
  13.  SELECT REPLACE(studentname,'周','邹') AS 新名字 FROM student WHERE studentname LIKE '周%';

5.1.3 日期和时间函数

  1. SELECT CURRENT_DATE();   /*获取当前日期*/
  2.  SELECT CURDATE();   /*获取当前日期*/
  3.  SELECT NOW();   /*获取当前日期和时间*/
  4.  SELECT LOCALTIME();   /*获取当前日期和时间*/
  5.  SELECT SYSDATE();   /*获取当前日期和时间*/
  7.  -- 获取年月日,时分秒
  8.  SELECT YEAR(NOW());
  9.  SELECT MONTH(NOW());
  10.  SELECT DAY(NOW());
  11.  SELECT HOUR(NOW());
  12.  SELECT MINUTE(NOW());
  13.  SELECT SECOND(NOW());

5.2 聚合函数（常用）

  1.  -- 聚合函数
  2.  /*COUNT:非空的*/
  3.  SELECT COUNT(studentname) FROM student;
  4.  SELECT COUNT(*) FROM student;
  5.  SELECT COUNT(1) FROM student;  /*推荐*/
  7.  -- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
  8.  -- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
  9.  -- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
  10.  -- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
  11.  /*
  12.  很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。
  14.  下面它们之间的一些对比：
  16.  1）在表没有主键时，count(1)比count(*)快
  17.  2）有主键时，主键作为计算条件，count(主键)效率最高；
  18.  3）若表格只有一个字段，则count(*)效率较高。
  19.  */
  21.  SELECT SUM(StudentResult) AS 总和 FROM result;
  22.  SELECT AVG(StudentResult) AS 平均分 FROM result;
  23.  SELECT MAX(StudentResult) AS 最高分 FROM result;
  24.  SELECT MIN(StudentResult) AS 最低分 FROM result;

5.3 MD5加密

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

  1. INSERT INTO testmd5 VALUES(1,'qingfan',md5('123456'));
  2. update testmd5 set pwd = md5(pwd) where name = 'qingfan';

6、事务

6.1 什么是事务

  • 事务就是将一组SQL语句放在同一批次内去执行
  • 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
  • MySQL事务处理只支持InnoDB和BDB数据表类型

6.2 ACID

6.2.1 原子性(Atomic)

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

6.2.2 一致性(Consist)

  1. 事务前后的数据完整性要保证一致

6.2.3 隔离性(Isolated)

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

6.2.4 持久性(Durable)

  1. 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

6.3 语法

  1. -- 使用set语句来改变自动提交模式
  2. SET autocommit = 0;   /*关闭*/
  3. SET autocommit = 1;   /*开启*/
  5. -- 注意:
  6. -- 1.MySQL中默认是自动提交
  7. -- 2.使用事务时应先关闭自动提交 SET autocommit = 0;
  9. -- 开始一个事务,标记事务的起始点
  10. START TRANSACTION  
  12. -- 提交一个事务给数据库
  13. COMMIT
  15. -- 将事务回滚,数据回到本次事务的初始状态
  16. ROLLBACK
  18. -- 还原MySQL数据库的自动提交
  19. SET autocommit =1;
  21. -- 保存点
  22. SAVEPOINT 保存点名称 -- 设置一个事务保存点
  23. ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
  24. RELEASE SAVEPOINT 保存点名称 -- 删除保存点
  26. -- 测试：转账实现
  27. SET autocommit = 0; -- 关闭自动提交
  28. START TRANSACTION;  -- 开始一个事务,标记事务的起始点
  29. UPDATE account SET cash=cash-500 WHERE `name`='A';
  30. UPDATE account SET cash=cash+500 WHERE `name`='B';
  31. COMMIT; -- 提交事务
  32. # rollback;
  33. SET autocommit = 1; -- 恢复自动提交

7、索引

7.1 索引作用

  • 提高查询速度
  • 确保数据的唯一性
  • 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
  • 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
  • 全文检索字段进行搜索优化

7.2 索引分类

  • 主键索引 (Primary Key)
  • 唯一索引 (Unique)
  • 常规索引 (Index)
  • 全文索引 (FullText)

7.2.1 主键索引

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

特点 :

  • 最常见的索引类型
  • 确保数据记录的唯一性
  • 确定特定数据记录在数据库中的位置

7.2.2 唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别：

  • 主键索引只能有一个
  • 唯一索引可能有多个
  1. CREATE TABLE `Grade`(
  2.   `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  3.   `GradeName` VARCHAR(32) NOT NULL UNIQUE
  4.    -- 或 UNIQUE KEY `GradeID` (`GradeID`)
  5. )

7.2.3 常规索引

作用 : 快速定位特定数据

注意 :

  • index 和 key 关键字都可以设置常规索引
  • 应加在查询找条件的字段
  • 不宜添加太多常规索引,影响数据的插入,删除和修改操作
  1. CREATE TABLE `result`(
  2.    -- 省略一些代码
  3.   INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
  4. )
  5. -- 创建后添加
  6. ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

7.3 测试索引

测试查询100w条数据的表

  1. -- 建表app_user：
  2. CREATE TABLE `app_user` (
  3. `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  4. `name` varchar(50) DEFAULT '' COMMENT '用户昵称',
  5. `email` varchar(50) NOT NULL COMMENT '用户邮箱',
  6. `phone` varchar(20) DEFAULT '' COMMENT '手机号',
  7. `gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
  8. `password` varchar(100) NOT NULL COMMENT '密码',
  9. `age` tinyint(4) DEFAULT '0' COMMENT '年龄',
  10. `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  11. `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
  12. PRIMARY KEY (`id`)
  13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
  15. -- 插入100w条数据
  16. DROP FUNCTION IF EXISTS mock_data;
  17. DELIMITER $$
  18. CREATE FUNCTION mock_data()
  19. RETURNS INT
  20. BEGIN
  21. DECLARE num INT DEFAULT 1000000;
  22. DECLARE i INT DEFAULT 0;
  23. WHILE i < num DO
  24.   INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
  25.    VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  26.   SET i = i + 1;
  27. END WHILE;
  28. RETURN i;
  29. END;
  30. SELECT mock_data();
  32. -- 创建索引
  33. CREATE INDEX idx_app_user_name ON app_user(name);
  35. -- 删除索引
  36. ALTER TABLE app_user DROP INDEX idx_app_user_name

没有创建索引的查询时间为1.8s

添加索引后的查询时间为0.025s

7.4 索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表建议不要加索引
  • 索引一般应加在查找条件的字段

7.5 索引数据结构

  1. -- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
  2. hash类型的索引：查询单条快，范围查询慢
  3. btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
  5. -- 不同的存储引擎支持的索引类型也不一样
  6. InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  7. MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  8. Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
  9. NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
  10. Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

8、权限管理和备份

8.1 用户管理

  1. /* 用户和权限管理 */ ------------------
  2. 用户信息表：mysql.user
  4. -- 刷新权限
  5. FLUSH PRIVILEGES
  7. -- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
  8. CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
  9.   - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
  10.   - 只能创建用户，不能赋予权限。
  11.   - 用户名，注意引号：如 'user_name'@'192.168.1.1'
  12.   - 密码也需引号，纯数字密码也要加引号
  13.   - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
  15. -- 重命名用户 RENAME USER kuangshen TO kuangshen2
  16. RENAME USER old_user TO new_user
  18. -- 设置密码
  19. SET PASSWORD = PASSWORD('密码')    -- 为当前用户设置密码
  20. SET PASSWORD FOR 用户名 = PASSWORD('密码')    -- 为指定用户设置密码
  22. -- 删除用户 DROP USER kuangshen2
  23. DROP USER 用户名
  25. -- 分配权限/添加用户
  26. GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
  27.   - all privileges 表示所有权限
  28.   - *.* 表示所有库的所有表
  29.   - 库名.表名 表示某库下面的某表
  31. -- 查看权限   SHOW GRANTS FOR root@localhost;
  32. SHOW GRANTS FOR 用户名
  33.    -- 查看当前用户权限
  34.   SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
  36. -- 撤消权限
  37. REVOKE 权限列表 ON 表名 FROM 用户名
  38. REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

8.2 MySQL备份

  1. -- 导出
  2. 1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
  3. 　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
  4. 2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
  5. 　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
  6. 3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
  7. 　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
  8. 4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
  9. 　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
  11. 可以-w携带备份条件
  13. -- 导入
  14. 1. 在登录mysql的情况下：-- source D:/a.sql
  15. 　　source 备份文件
  16. 2. 在不登录的情况下
  17. 　　mysql -u用户名 -p密码 库名 < 备份文件