MySQL总结

本文介绍一下MySQL

MAC OS MySQL数据库安装以及坑参考：
安装
mac下启动/停止/重启mysql服务
mac下安装mysql5.7.19，连接出现Access denied for user ‘root’@‘localhost’ (using password: YES)

1、概念

1.1 数据库的特点

1. 持久化存储数据的。数据库就是一个文件系统
2. 方便存储和管理数据
3. 使用了统一的方式操作数据库 – SQL语言

几个基本的数据库操作命令 :

update user set password=password(‘123456’)where user=‘root’; 修改密码
flush privileges; 刷新数据库 show databases; 显示所有数据库 use dbname；打开某个数据库
show tables; 显示数据库mysql中所有的表 describe user; 显示表mysql数据库中user表的列信息
create database name; 创建数据库 use databasename; 选择数据库

exit; 退出Mysql ? 命令关键词 : 寻求帮助
– 表示注释

1.2 SQL

1. SQL：
   Structured Query Language：结构化查询语言
   定义了操作所有关系型数据库的规则。

2. SQL语法：

   1. SQL 语句可以单行或多行书写，以分号结尾。
   2. 可使用空格和缩进来增强语句的可读性。
   3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
   4. 3 种注释
   • 单行注释: – 注释内容 或 # 注释内容(mysql 特有)
   • 多行注释: /* 注释 */

3. SQL分类

   1. DDL(Data Definition Language)数据定义语言
      用来定义数据库对象：数据库，表。关键字：create, drop,alter 等
   2. DML(Data Manipulation Language)数据操作语言
      用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
   3. DQL(Data Query Language)数据查询语言
      用来查询数据库中表的记录(数据)。关键字：select, where 等
   4. DCL(Data Control Language)数据控制语言(了解)
      用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

2、SQL分类详述

DDL操作数据库

2.1 DDL操作数据库 CRUDL

• 创建数据库(Create)

  CREATE DATABASE (IF NOT EXISTS) 数据库名 (CHARACTER SET gbk);
  

• 查询(Retrieve)

1. 查询所有数据库的名称

    show databases;
   

2. 查看某个数据库的定义信息

    show create database 数据库名;
   

3. 修改数据库

   修改数据库默认的字符集

    alter database 数据库名 default character set utf8;
   

   将某一个数据库的字符集改为utf-8

   alter database 数据库名 character set utf8;
   

   删除数据库

    drop database 数据库名;
   

4. 使用数据库

   • 查看正在使用的数据库

     select database();
     

   • 使用/切换数据库

     use 数据库名
     

5. 操作表结构 DDL

   前提是先使用某个数据库

6. 创建表（最后一列不要加逗号）

create table 表名(
         		字段名1 字段类型1,
         		字段名2 字段类型2
         );

create table [if not exists] `表名`(
   '字段名1' 列类型 [属性][索引][注释],
   '字段名2' 列类型 [属性][索引][注释],
  #...
   '字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];

反引号用于区别MySQL保留字与普通字符而引入的

Mysql 常用数据类型

类型	描述
int	整型
double	浮点型
varchar	字符串型
date	日期类型（格式为yyyy-MM-dd）

数据值和列类型

列类型 : 规定数据库中该列存放的数据类型

数值类型

字符串类型

日期和时间型数值类型

NULL值

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

数据字段属性

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：默认的，用于设置默认值

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

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库

CREATE TABLE IF NOT EXISTS `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student;  -- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';

-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student;  -- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';

数据表的类型

设置数据表的类型

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

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

MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等…

常见的 MyISAM 与 InnoDB 类型：

经验 ( 适用场合 ) :

• 适用 MyISAM : 节约空间及相应速度
• 适用 InnoDB : 安全性 , 事务处理及多用户操作数据表

数据表的存储位置

MySQL数据表以文件方式存放在磁盘中，包括表文件 , 数据文件 , 以及数据库的选项文件

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

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

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

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

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

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

设置数据表字符集

我们可为数据库,数据表,数据列设定不同的字符集，设定方法 :

创建时通过命令来设置 , 如 : CREATE TABLE 表名 CHARSET = utf8;

如无设定 , 则根据MySQL数据库配置文件 my.ini 中的参数设定
MySQL默认编码是Latin1，不支持中文

2. 查看表

    show tabels; # 查看某个数据库中的所有表
   

3. 查看表结构

   DESC 表名;
   

• eg:

  create table student(
  		id int(11) default null,
  		name varchar(20) default null,
  		birthday date default null
  )  
  # 可以使用``将变量名包围，防止与关键字冲突
  

4. 快速创建一个表结构相同的表

    create table 新表名 like 旧表名;
   

5. 删除表

     drop table (if exists) 表名;
   

6. 修改表结构

添加表列 add

   alter table 表名 add 列名 类型;

修改列类型 modify

 alter table 表名 modify 列名 新的类型;

修改列名 change

   alter table 表名 change 旧列名 新列名 类型;

注意： change用来字段重命名，不能修改字段类型和约束；
modify不用来字段重命名，只能修改字段类型和约束；

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

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

修改字段 :

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

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

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

删除列 drop

 alter table 表名 drop 列名;

修改表名 rename

 rename table 表名 to 新表名

修改字符集

alter table 表名 character set 字符集

其他

1. 可用反引号（`）为标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符！

2. 每个库目录存在一个保存当前数据库的选项文件db.opt。

3. 注释：
  单行注释 # 注释内容
  多行注释 /* 注释内容 */
  单行注释 -- 注释内容       (标准SQL注释风格，要求双破折号后加一空格符（空格、TAB、换行等）)
   
4. 模式通配符：
  _   任意单个字符
  %   任意多个字符，甚至包括零字符
  单引号需要进行转义 \'
   
5. CMD命令行内的语句结束符可以为 ";", "\G", "\g"，仅影响显示结果。其他地方还是用分号结束。delimiter 可修改当前对话的语句结束符。

6. SQL对大小写不敏感 （关键字）

7. 清除已有语句：\c

ML操作表中的数据(用于对表中的数据进行增删改操作)

MD5 加密

一、MD5简介

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

二、实现数据加密

新建一个表 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');

2.2 DML操作表中的数据

插入记录

insert [into] 表名 [字段名] values(字段值)

tips:

1. 插入的字段应与字段的数据类型相同
2. 字符和日期型数据应包含在单引号中，Mysql中也可以使用双引号作为分隔符

# 查看包含character开头的全局变量，即可查看mysql内部设置的编码
show variables like 'character%';  

蠕虫复制

将一张已经存在的表中的数据复制到另一张表中

insert into 表名1 select * from 表名2;

只复制部分列

insert into 表名1(列1, 列2) select 列1, 列2 from student;

更新记录

update 表名 set 列名=值 [where 条件表达式];

删除表记录

delete from 表名 [where 条件表达式];

truncate

truncate table 表名;

truncate与delete区别：

1 drop、truncate都是DDL语句(数据定义语言),执行后会自动提交，也就是说，不能回滚

2 delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效

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

相同点：4 truncate（更快） 和 delete 只删除数据不删除表的结构，而DROP则删除整个表（结构数据).

测试：

-- 创建一个测试表
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入几个测试数据
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

-- 删除表数据(不带where条件的delete)
DELETE FROM test;
-- 结论:如不指定Where则删除该表的所有列数据,自增当前值依然从原来基础上进行,会记录日志.

-- 删除表数据(truncate)
TRUNCATE TABLE test;
-- 结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志.

-- 同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后
-- InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
-- MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)

外键

外键概念

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

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

DQL查询表中的数据

外键作用

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

创建外键

建表时指定外键约束

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

-- 年级表 (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`);

删除外键

操作：删除 grade 表，发现报错

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

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

mysql查看某一数据库中某个表的所有字段名和字段类型
mysql安装成功后可以看到已经存在mysql、information_schema和test这个几个数据库。
information_schema库中有一个名为COLUMNS的表，这个表中记录了数据库中所有表的字段信息。

Select COLUMN_NAME 列名, DATA_TYPE 字段类型
from INFORMATION_SCHEMA.COLUMNS 
Where table_name = 'table_name' ##表名 
AND table_schema = 'database_name'##数据库名

删除外键之后，此索引还在，还需删除

-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

2.3 DQL查询表中的数据

DQL( Data Query Language 数据查询语言 )

查询数据库数据 , 如SELECT语句

简单的单表查询或多表的复杂查询和嵌套查询

是数据库语言中最核心,最重要的语句

使用频率最高的语句

1. 查询语句

select 列名 from 表名 [where 条件表达式]

select 字段名1 as 别名 from 表名

清除重复值

select distinct 字段名 from 表名

查询结果参与运算

列数据与固定值运算

select 列名1 + 固定值 from 表名 

列数据与其他列数据运算

select 列名1 + 列名2 from 表名

比较运算符

比较运算符	说明
> , <, <=, >=, = , <>	<>表示不等于，也可以 !=
between…and	包含两端
IN（集合）	集合尔表示多个值，使用逗号分隔开
LIKE ‘张%’	模糊查询，以张开头的人，%匹配多个， _ 匹配单个
IS NULL	查询某一列为NULL的值，不能写成=NULL

逻辑运算符

and 或 &&	SQL中使用前者，后者不通用
or 或 ||	或
not 或 !	非

使用表达式的列

数据库中的表达式 : 一般由文本值 , 列值 , NULL , 函数和操作符等组成

应用场景 :

• SELECT语句返回结果列中使用
• SELECT语句中的ORDER BY , HAVING等子句中使用
• DML语句中的 where 条件语句中使用表达式

注意：
数值数据类型的记录之间才能进行算术运算 ;

相同数据类型的数据之间才能进行比较 ;

-- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式

-- 学员考试成绩集体提分一分查看
SELECT studentno,StudentResult+1 AS '提分后' FROM result;

避免SQL返回结果中包含 ’ . ’ , ’ * ’ 和括号等干扰开发语言程序.

-- 模糊查询 between and \ like \ in \ null

-- =============================================
-- 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 '%嘉%';

-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':'

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

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

-- =============================================
-- 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. MySQL 表的约束与优化

3.1 排序

select 字段名 from 表名 where 字段=值 order by 字段名 [ASC|DESC]  # 默认ASC：升序

组合排序

select 字段名 from 表名 where 字段=值 order by 字段名1 [ASC|DESC], 字段名2 [ASC|DESC]

3.2聚合函数

聚合函数	作用
max(列名)	求此列最大值
min(列名)	求此列最小值
avg(列名)	求此列平均值
count(列名)	统计此列有多少条记录
sum(列名)	对此列求和

select count(id) as 总人数 from student;  # 不通风机NULL值

ifnull(列名,默认值) 如果不为空，返回相应的值，否则，返回默认值

select count(ifnull(id,0)) from student

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

题目：

-- 查询不同课程的平均分,最高分,最低分
 -- 前提:根据不同的课程进行分组
 
 SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING 平均分>80;
 
 /*
 where写在group by前面.
 要是放在分组后面的筛选
 要使用HAVING..
 因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行的筛选的
 */

常用函数

数据函数

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

字符串函数

 SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
 SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/
 SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替换某个长度，索引从1开始*/
 SELECT LOWER('KuangShen'); /*小写*/
 SELECT UPPER('KuangShen'); /*大写*/
 SELECT LEFT('hello,world',5);   /*从左边截取，索引从1开始*/
 SELECT RIGHT('hello,world',5);  /*从右边截取*/
 SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
 SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度，索引从1开始*/
 SELECT REVERSE('狂神说坚持就能成功'); /*反转
 
 -- 查询姓周的同学,改成邹
 SELECT REPLACE(studentname,'周','邹') AS 新名字
 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 VERSION();  /*版本*/
SELECT USER();     /*用户*/

小结

 -- ================ 内置函数 ================
 -- 数值函数
 abs(x)            -- 绝对值 abs(-10.9) = 10
 format(x, d)    -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
 ceil(x)            -- 向上取整 ceil(10.1) = 11
 floor(x)        -- 向下取整 floor (10.1) = 10
 round(x)        -- 四舍五入去整
 mod(m, n)        -- m%n m mod n 求余 10%3=1
 pi()            -- 获得圆周率
 pow(m, n)        -- m^n
 sqrt(x)            -- 算术平方根
 rand()            -- 随机数
 truncate(x, d)    -- 截取d位小数
 
 -- 时间日期函数
 now(), current_timestamp();     -- 当前日期时间
 current_date();                    -- 当前日期
 current_time();                    -- 当前时间
 date('yyyy-mm-dd hh:ii:ss');    -- 获取日期部分
 time('yyyy-mm-dd hh:ii:ss');    -- 获取时间部分
 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- 格式化时间
 unix_timestamp();                -- 获得unix时间戳
 from_unixtime();                -- 从时间戳获得时间
 
 -- 字符串函数
 length(string)            -- string长度，字节
 char_length(string)        -- string的字符个数
 substring(str, position [,length])        -- 从str的position开始,取length个字符
 replace(str ,search_str ,replace_str)    -- 在str中用replace_str替换search_str
 instr(string ,substring)    -- 返回substring首次在string中出现的位置
 concat(string [,...])    -- 连接字串
 charset(str)            -- 返回字串字符集
 lcase(string)            -- 转换成小写
 left(string, length)    -- 从string2中的左边起取length个字符
 load_file(file_name)    -- 从文件读取内容
 locate(substring, string [,start_position])    -- 同instr,但可指定开始位置
 lpad(string, length, pad)    -- 重复用pad加在string开头,直到字串长度为length
 ltrim(string)            -- 去除前端空格
 repeat(string, count)    -- 重复count次
 rpad(string, length, pad)    --在str后用pad补充,直到长度为length
 rtrim(string)            -- 去除后端空格
 strcmp(string1 ,string2)    -- 逐字符比较两字串大小
 
 -- 聚合函数
 count()
 sum();
 max();
 min();
 avg();
 group_concat()
 
 -- 其他常用函数
 md5();
 default();

3.3 分组

分组查询是指使用GROUP BY语句对查询结果进行分组，相同的数据作为一组

select 字段1, 字段2...from 表名 group by 分组字段 [having 条件]

-- 按性别进行个分组，求男生和女生数学的平均分
select sex, avg(math) from student group by sex;

-- 查询男女各有多少人
-- 1）查询所有数据，按性别分组
-- 2）统计每组的人数
select sex,count(*) from student group by sex;

having 和where 的区别：

where子句：分组前筛选，后面不可以跟聚合函数

having子句：筛选满足条件的组，分组之后过滤数据，后面可以跟聚合函数

3.4 limit语句

limit作用：限制查询记录的条数

select 字段 from 表名 [where子句] [group by子句] [having子句] [order子句] [limit子句]

limit offset,length
# offset：起始行数，从0开始计数，如果省略的话，默认值是0
# length：返回的行数

4. 数据库备份、还原

4.1 数据备份

mysqldump -u 用户名 -p 密码 数据库 > 文件的路径
mysqldump -u root -p root student > /test.sql
# 导出结果：数据库中所有的表和数据都会导出为SQL语句

4.2 还原

use 数据库;
source 导入文件的路径;

5. 数据库表的约束

5.1 约束的作用

对表中的数据进行限制，保证数据的正确性、有效性和完整性，一个表如果添加了约束，不正确的数据将无法插入到表中。

约束在创建表的时候添加比较合适

• 约束的种类

  约束名	约束关键字
  主键	primary key
  唯一	unique
  非空	not null
  外键	foreign key
  检查约束	Check （mysql不支持)

5.2 主键约束

主键作用：用来唯一标识数据库中的每一条记录

通常不用业务字段作为主键，单独给每一张表设计第一个id的字段，把id作为主键，主键是给数据库和程序使用的，不是给最终的客户使用的，所以主键有没有含义没有关系，只要不重复，非空即可

创建主键

主键关键字：primary key

特点：非空 not null

​ 唯一

在创建表的时候给字段添加主键

 字段名 字段类型 primary key

在已有的表中添加主键

alter table 表名 add primary key(字段名)

删除主键

alter table 表名 drop primary key
# alter table student drop primary key;

设置主键自增

create table 表名(
	列名 int primary key auto_increment
)auto_increment=起始值;

修改起始值

alter table 表名 auto_increment = 起始值;
# alter table 表名 auto_increment = 10;

delete 和 truncate 对自增长的影响

delete：删除所有的记录之后，自增长没有影响，即继续原来的序号增长

truncate：删除所有的记录以后，自增长又重新开始

5.3 唯一约束

指某一列中不能出现重复的值

5.3.1 唯一约束的基本格式

字段名 字段类型 unique

create table student{
		id int;
		name varchar(20) unique;
}

5.4 非空约束

某一列不能为null

5.4.1 基本格式

字段名 字段类型 not null

create table student{
		id int,
		name varchar(20) not null,
		gender char(1)
}

5.4.2 默认值

字段名 字段类型 default 默认值

create table student{
		id int,
		name varchar(20) not null,
		gender char(1) default '男'
};
# 添加一条记录，使用默认地址
insert into student values(1, '张三', default )

如果一个字段设置了非空与唯一约束，该字段与主键的区别？

1）主键在一个表中只能有一个，主键可以是单列，也可以是多列

2）自增长只能用在主键上

5.5 外键约束

外键：在从表中，与主表主键对应的那一列

主表：一方，用来约束别人的表

从表：多方，被别人约束的表

主表（约束别人）：department部门表

id	dep_name	dep_location
1	研发部	广州
2	销售部	深圳

从表（使用别人的数据，被别人约束）：employee员工表

id	NAME	age	dep_id
1	张三	20	1
2	李四	21	1
3	王五	34	2

5.5.1 新建表时增加外键

[constraint] [外键约束名] foreign key (外键字段名) references 主表名(主表字段名)
# constraint emp_depid_fk foreign key (dep_id) references department(id)

5.5.2 已有表增加外键

alter table 从表 add [constraint] [外键约束名] foreign key (外键字段名) references 主表(主键字段名)
# alter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id);

5.5.3 删除外键

alter table 从表 drop foreign key 外键名称
# alter table employee drop foreign key emp_depid_fk;

5.5.4 外键的级联

修改和删除主表的主键时，同时更新或删除从表的外键值，称为级联操作

on update cascade # 级联更新，只能是创建表的时候创建级联关系，更新主表中的主键，从表中的外键也自动同步更新
on delete cascade # 级联删除

constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade

小结：

约束名	关键字	说明
主键	primary key	1）唯一 2）非空
默认	default	如果一列没有值，则使用默认值
非空	not null	此列必须有值
唯一	unique	此列不能有重复值
外键	foreign key	主表中主键列，在从表中外键列

6. 表与表之间的关系

一对一：员工表，简历表

一对多：最常用的关系，部门和员工

多对多：学生选课表 和 学生表

7. 数据库设计

7.1 数据规范化

范式：好的数据库设计对数据的存储性能和后期程序的开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则成为范式。

范式种类：第一范式(1NF), 第二范式(2NF), 第三范式(3NF), 巴斯-科德范式(BCNF), 第四范式(4NF) 和 第五范式(5NF, 又称完美范式)

7.2 各类范式

7.2.1 1NF

数据库表的每一列都是不可分割的院子数据项，不是集合，数组等非原子数据项，即表中的某个列有多个值时，必须拆分为不同的列。即第一范式的每一列不可再拆分，称原子性。

7.2.2 2NF

在满足第一范式的前提下，表中的每一个字段都完全依赖与主键。

所谓的完全依赖是指，不能存在仅依赖主键一部分的列，即 第二范式是在第一范式的基础上所有列完全依赖于主键列，当存在一个 复合主键把汗多个主键列的时候，才会发生不符合第二范式的情况。

eg: 一个主键有两个列，不能存在这样的属性，它只依赖于其中的一个列。

特点：

1）一张表只能描述一件事

2）表中的每一项都完全依赖于主键

7.2.3 3NF

在第二范式的前提下，表中的每一项都直接依赖于主键，而不是通过其他列来间接已派员主键，即不存在传递依赖

简而言之，3NF就是所有列不依赖于其他非主键列，也就是在2NF的基础上，任何非主列不得传递依赖于主键。

传递依赖：指若存在" A -> B -> C "的决定关系，则C传递依赖于A，因此，满足第三范式的数据库中不应该存在如下依赖关系：主键列 -> 非主键列x -> 非主键列y

小结：

范式	说明
1NF	原子性：表中每列不可再拆分
2NF	不产生局部依赖，一张表只描述一件事情
3NF	不产生传递依赖，表中每一列都直接依赖于主键，而不是通过其他列间接依赖于主键

8. 多表查询

8.1 多表查询：

8.1.1 内连接：

隐示内连接

看不到join关键字，条件使用where指定

select 字段名 from 左表, 右表 where 条件
# select * from emp,dept where emp.dep_id = dept.id

显式内连接

使用inner join … on 语句，可以省略inner

select 字段名 from 左表 [inner] join 右表 on 条件

8.1.2 外连接

左外连接

使用left outer join … on 语句，outer可以省略

用左边表的记录去匹配右边表的记录，如果符合条件的则显示，否则，显示null

可以理解为在内连接的基础上，保证左表的数据全部显示

select 字段名 from 左表 left [outer] join 右表 on 条件

右外连接

select 字段名 from 左表 right [outer] join 右表 on 条件

JOIN 对比

七种Join：

测试:

/*
连接查询
   如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
   查询两个表中的结果集中的交集
外连接 outer join
   左外连接 left join
       (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
   右外连接 right join
       (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
       
等值连接和非等值连接

自连接
*/

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

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

-- 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

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

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

自连接

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

需求:从一个包含栏目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`

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

-- 查询科目及所属的年级(科目名称,年级名称)
SELECT subjectname AS 科目名称,gradename AS 年级名称
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查询 数据库结构-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'

9. 子查询

9.1 子查询

1）一个查询的结果作为另一个查询的条件

2）有查询的嵌套，内部的查询称为子查询

3）子查询要使用括号

# 查询开发部中有哪些员工
select * from emp where dep_id = (select id from dept where name='开发部')

9.2 子查询结果的三种情况

子查询的结果是：

• 单行单列
• 多行单列
• 多行多列

9.2.1 子查询的结果是一个值

子查询结果只要是单行单列，肯定是在where后面作为条件，父查询使用，比较运算符如：>, <, <>, =等

9.2.2 子查询的结果是多行单列

子查询结果是多行单列，结果集类似于一个数组，父查询使用IN运算符

select 查询字段 from 表 where 字段 in (子查询)

9.2.3 子查询的结果是多行多列

子查询的结果是多行多列，肯定是在from后面做表

select 查询字段 from (子查询) 表别名 where 条件

子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

• 子查询结果只要是单列，则在where后面作为条件
• 子查询结果只要是多列，则在from后面作为表进行二次查询

/*============== 子查询 ================
什么是子查询?
   在查询语句中的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;

-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学-2' AND StudentResult>=80

-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '高等数学-2'
)

-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
   SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
       SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
  )
)

/*
练习题目:
   查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数)
   使用子查询,查询郭靖同学所在的年级名称
*/

10. 事务

事务执行是一个整体，所有的SQL语句都必须执行成功，若其中有一条SQL语句出现异常，则所有的SQL语句都要回滚，整个业务执行失败

10.1 手动提交事务

MySQL中可以有两种方式进行事务的操作:

• 手动提交事务
• 自动提交事务

手动提交：

• 开启事务：start transaction;
• 提交事务：commit;
• 回滚事务：rollback;

自动提交事务
MySQL默认每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕后自动提交事务，MySQL默认自动开始提交事务

# 取消自动提交 
# 查看MySQL是否开启自动提交事务
 select @@autocommit;

# @@表示全局变量，1表示开启，0表示关闭
 set @@autocommit = 0;

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

10.2 事务原理

事务开启之后，所有的操作都会临时保存到事务日志中，事务日志只有在得到commit命令才会同步到数据表中，其他任何情况都会清空日志记录(rollback, 断开连接)

10.3 回滚点

在某些成功的操作作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面的操作都已经成功，可以在当前成功的位置设置一个回滚点，可以供后续失败操作返回到该位置，而不是返回所有操作。

10.3.1 回滚点的操作语句

savepoint 名字  # 设置回滚点
rollback 名字   # 回到回滚点

10.4 事务的隔离级别

10.4.1事务的四大特性ACID

事务特性	含义
原子性（Atomicity）	每个事物都是一个整体，不可再拆分，事务中的SQL语句，要么都执行成功，要么都失败
一致性（Consistency）	事务在执行前后数据库的状态保持一致，eg：转账前两个人的总金额是2000，转账后2个人的总金额也是2000
隔离性（Isolation）	事务与事务之间不应该相互影响，执行时保持隔离的状态
持久性（Durability）	一旦事务执行成功，对数据库的修改是持久的，也就是说，就算关机，也是保存下来的

10.4.2 事务的隔离级别

事务在操作时的理想状态：所有的事务之间保持隔离，互不影响，因为并发操作，多个用户同时访问一个数据，可能会引发并发访问的问题：

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复度	一个事务两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务update时引发的问题
幻读	一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是insert 或 delete 时引发的问题

10.4.3 MySQL数据库4种隔离级别

级别由低到高

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	Read committed	否	是	是	Oracle 和 SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

隔离级别越高，性能越差，安全性越高

# 查询隔离级别
select @@tx_isolation

# 设置事务隔离级别，需要退出MySQL再重新登录才能看到隔离级别的变化
set global transaction isolation level 级别字符串

11. 索引

索引的作用

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

分类

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

主键索引

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

特点 :

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

唯一索引

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

与主键索引的区别:

• 主键索引只能有一个，唯一索引可能有多个

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

常规索引

作用 : 快速定位特定数据

注意 :

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

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

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

全文索引

作用 : 快速定位特定数据

注意 :

• 只能用于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 版本、存储引擎和数据类型是否支持全文索引。
*/

拓展：测试索引

建表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 UPDATE CURRENT_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)

索引准则

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

索引的数据结构

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

12. DCL(Data Control Language)

• DDL: create / alter / drop

• DML: insert / update / delete

• DQL: select / show

• DCL: grant / revoke

  一个公司里面的数据库服务器上面可能同时运行着很多项目的数据库，所以，我们应该根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库

  11.1 创建用户

  create user '用户名'@'主机名' identified by '密码';
  

  用户名：将创建的用户名

  主机名：指定该用户在哪个主机上可以登录，如果是本地用户的话，可以用localhost，如果想让该用户从任意远程主机登录，可以使用通配符%

  create user 'user1'@'%' identified by '123456'       # user1 用户可以在任何的电脑上登录MySQL服务器
  

  密码：该用户的登录密码，密码可以为空，表示该用户不需要密码登录服务器

  11.2 给用户授权

  用户创建之后没有什么权限，需要给用户授权

  grant 权限1,权限2... on 数据库名.表名 to '用户名'@'主机名';
  

  权限: create / alter / select / insert / update / ALL

  数据库名.表名: 所有数据库的所有表可使用 *.*

  ‘用户名’@‘主机名’：给那个用户授权

  11.3 撤销授权

  revoke 权限1,权限2... on 数据库名.表名 from '用户名'@'主机名';  # revoke all ...
  

  11.4 查看权限

  show grants for '用户名'@'主机名';
  

  usage是指连接（登录权限），建立一个用户，就会自动授予其usage权限（默认授予）

  11.5删除用户

  drop user '用户名'@'主机名';
  

  11.6 修改管理员密码

  mysqladmin -uroot -p password 新密码
  

  需要在未登录MySQL的情况下操作，新密码不需要加上引号

  11.7 修改普通用户密码

  set password for '用户名'@'主机名' = password('新密码');
  

  此操作在登录MySQL的情况下操作

-- 权限列表
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] ...

13 MySQL备份

数据库备份必要性

• 保证重要数据不丢失
• 数据转移

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密码 库名 < 备份文件