• MySQL笔记

    数据库

    请不要假装很努力，因为结果不会陪你演戏。

    typora快捷键 https://blog.csdn.net/weixin_30619101/article/details/97707926

    1. 认识数据库

    1.1 数据库分类

    类型 关系型数据库 非关系型数据库
    举例 MySQL，Oracle，SQL server，DB2，SQLite Redis，MongDB
    特征 通过表和表之间，行和列之间的关系进行数据的存储。 非关系型数据库，对象存储，通过对象的自身属性来决定

    1.2 Mysql数据库

    MySQL是一个关系型数据库管理系统，由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844) 公司开发，属于 Oracle 旗下产品。

    MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

    MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。

    2. 操作数据库

    2.1 连接数据库

    • 连接到服务器
    $> mysql -h host -u user -p
Enter password: ********

    • 断开连接

      QUIT(或\q或exit)来断开连接：

    mysql> QUIT
Bye
    • 单行注释
    --
    • 多行注释
    /*
*/

    2.1.1 结构化语言（CURD）

    DDL（Data DefinitionLanguage） 数据库定义语言

    DML（Data Manipulation Language） 数据库操作语言

    DQL（Data QueryLanguage） 数据库查询语言

    DCL（Data Control Language） 数据库控制语言

    2.2 操作数据库

    1. 创建数据库
    create database[if not exists] 数据库名;
    1. 删除数据库
    drop database[if exists] 数据库名;
    1. 使用数据库(如果表名是特殊字符，则需要加``)
    use 数据库名--;-- 可以省略
    1. 查看数据库
    show databases;-- 查看所有的数据库
    1. 查看创建数据库
    show create database 数据库名；
    1. 修改数据库编码方式
    alter database 数据库名 default character set 编码方式 collate 编码方式_bin;

    2.3 数据类型

    整数和浮点数

    类型 特征 大小
    tinyint 十分小的数据 1个字节
    smallint 较小的数据 2个字节
    mediumint 中等大小的数据 3个字节
    int 标准的整数 4个字节
    bigint 较大的数据 8个字节
    float 浮点数 4个字节
    double 浮点数(双精度) 8个字节
    decimal(M,D) 字符串形式的浮点数(金融行业)

    时间日期

    类型 格式 备注
    year YYYY 年份表示
    date YYYY-MM-DD 日期格式
    time HH：mm：ss 时间格式
    datetime YYYY-MM-DD HH:mm:ss 最常用的格式
    timestamp YYYY-MM-DD HH:mm:ss(1970-1-1至今) 时间戳

    字符串

    类型 特征 大小
    char 字符串固定大小 0~255
    varchar 可变字符串(string) 0~6553
    tinytext 微型文本 2^8-1
    text 文本串(保存大文本) 2^16-1

    null

    空值，未知

    • 不要使用null运算，结果为null

    2.4 数据表的字段属性

    Unsigned：

    • 无符号整数
    • 声明了该列不能为负数

    Zerofill（无符号）：

    • 0填充
    • 不足位数，用0填充

    自增：

    • 自动在上一条记录的基础上加1
    • 通常用来设计唯一主键--index，必须是整数
    • 可以自定义初始值和步长

    不是Null：

    • 如果设置为not null，不赋值则为空
    • null，如果不填写值，默认为nnull

    默认（default）：

    • 设置默认值

    表的定义规则

    ***每一个表，都必须存在以下五个字段！表示一个记录存在的意义！

id			主键
`version`	乐观锁
is_create	伪删除
gmt_create	创建时间
gmt_update	修改时间

    2.5数据表的基本操作

    2.5.1 创建数据表

    create table [if not exists] `表名`(
	`字段名1` 列类型[属性][索引][注释]，
	`字段名2` 列类型[属性][索引][注释]，
	…………
	`字段名n` 列类型[属性][索引][注释]
)[表类型][字符集设置][注释];
    • 格式
    create table if not exists `student`(
`id` int(4) not null auto_increment primary key,
`name` varchar(10) not null,
`gender` varchar(2) not null,
`birthdy` datetime not null
)engine=innodb default charset=utf8;

    2.5.2 查看数据表

    show create table 表名；
    • 查看表的结构
    describe 表名;
-- 简写
desc 表名;

    2.5.3 修改数据表

    1. 修改表名
    alter table 旧表名 rename [to] 新表名;
    1. 修改字段名
    alter table 表名 change 旧字段名 新字段名 新数据类型;
    1. 修改字段的数据类型
    alter table 表名 modify 字段名 数据类型;
    1. 增加字段
    alter table 表名 add 新字段名 数据类型[约束条件][first | after已存在字段名];
    1. 删除字段
    alter table 表名 drop 字段名;
    1. 修改字段的排列位置
    alter table 表名 modify 字段名1数据类型 [first|after]字段名2

    modify和change区别

    • modify不能用来字段重命名，只能修改字段类型和约束

    • change用来字段重命名，不能修改字段类型和约束

    1. alter table 表名 drop 字段名;

    2.5.4 删除数据表

    删除没用关联的表

    drop table [if exists] 表名;

    2.6 数据库引擎

    myisam innodb（默认）
    事物支持 不支持 支持
    数据行锁定 不支持 支持
    外键约束 不支持 支持
    全文索引 支持 不支持
    表空间的大小 较小 较大，约为2倍

    常规使用操作：

    • myisam 节约空间，速度较快
    • innodb 安全性高，事务的处理，多表用户操作

    数据库的物理空间

    在安装目录的data文件夹下!

    本质还是文件的存储

    MySQL引擎在物理文件上的区别

    • innodb在数据库表中只有一个*.frm文件，以及上机目录下的ibdata1文件
    • myisam对应文件
      • *.fram 表结构的定义文件
      • *.myd 数据文件（data）
      • *.myi 索引文件（index）

    2.7 字符集编码

    修改字符集编码

    默认编码格式latin1（不支持中文）

    方法一：在my.ini中修改默认配置

    [mysql]
default-character-set=utf8;

    方法二：dos窗口（只生效于当前窗口）

    set character_set_client=utf8;

    查看数据库信息

    \s

    3. MySQL数据管理

    3.1 外键

    用于加强多张表之间的联系

    方法一 （创建的时候添加外键约束）

    定义外键key，添加外键添加约束

    create table `grade`(
`gradeid` int(4) not null auto_increment,
`gradename` varchar(10) not null,
primary key (`gradeid`)
)engine=innodb default charset=utf8;

create table if not exists `student`(
`id` int(4) not null auto_increment ,
`name` varchar(10) not null,
`gradeid`  varchar(10) not null,  
`gender` varchar(2) not null,
`birthdy` datetime not null.
primary key (`id`),
key `fk_gradeid` (`gradeid`),    
constraint `fk_gradeid` foreign key (`gradeid`) references `grade` (`gradeid`)
)engine=innodb default charset=utf8;

    方法二 （表创建完添加外键约束）

    alter table 表名 
add constraint `fk_gradeid` foreign key(`gradeid`) references `grade` (`gradeid`);

    以上操作为物理外键，通常使用程序来实现外键

    关联关系

    1. 多对一
    2. 多对多
    3. 一对一

    3.2 DML语言

    数据库操作语言

    insert 插入

    update 修改

    delete 删除

    3.3 添加

    insert

    插入语句（字段和值对应 ）

    inert into 表名([字段1][字段2][...]) values([值1][值2[...]);

INSERT INTO `student`(`name`,`age`)VALUES('李四','18');

    3.4 更新

    update

    指定条件

    update 表名 set colnum_name=value where 条件;

    不指定条件（会改动所有表）

    update 表名 set colnum_name=value；

    修改多个属性

    update 表名 set colnum_name1=value1,[colnum_name2=value2] where 条件;

UPDATE `student` SET `name` = '王五' WHERE id = 2;

    操作符返回 布尔值

    操作符 含义 范围 结果
    = 等于 5=6 false
    <> 或 != 不等于 5<>6 true
    > 大于 5>6 false
    < 小于 5<6 true
    >= 大于等于 5>=6 false
    <= 小于等于 5<=6 true
    between...and... 区间 [5,6]
    and 与(&&) 5 and 6
    or 或(||) a>5 or b< 6 
    通过多个条件定位语句

UPDATE `student` SET `name` = '屿龍' WHERE `id` >= 2 OR `name` ！= '屿龍';

    注意：

    • colnum_name 是数据库的列要加上``

    • value可以是具体值，也可以是变量

    3.5 删除

    delete

    语法：delete from 表名 [where 条件]

    -- 删除数据库
delete from `student`;

-- 删除指定数据
delete from `student` where `id` = 1 ;

    truncate 命令

    作用：完全清空一个数据库，表的约束和结构不变

    -- 清空数据库
truncate `student`;

    delete 和truncate区别

    • 相同点：都能删除数据，都不会删除表的结构
    • 不同点：
      • truncate 重新设置自增列 计数器归零
      • truncate 不会影响事物

    4.DQL查询数据

    4.1DQL（数据库查询语言）

    所有查询语句

    select

    SELECT [ALL | DISTINCT]
{*| table.* | [table,field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join tab1e_name2] -- 联合查询
    [WHERE ...]	-- 指定结果需满足的条件
    [GROUP BY ...]	-- 指定结果按照哪几个字段来分组
    [HAVING]  -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...] -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    -- 指定查询的记录从哪条至哪条

    注：[]括号代表可选，{}代表必选

    4.2 查询指定字段

    语法：select 字段 1,...from 表

    -- 查询全部学生
select * from student;

-- 查询指定字段
select `name`,`age` from student;

-- 别名：给结果/表起一个名字 as
select `name` as 姓名,`age` as 年龄 from student from student as a;

-- sql函数 concat拼接字符串
select concat('姓名：',`name`) as 新名字 from student;

    注：当字段名不能很好的表示数据本来的意思，可以给字段起别名以便于理解

    distinct 去重

    作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条

    -- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM resu1t -- 查询全部的考试成绩
SELECT `studentNo` FROM result -- 查询有哪些同学参加了考试
SELECT DISTINCT `studentNo` FROM resu1t -- 发现重复数据，去重

    数据库的列（表达式）

    select version();-- 查询数据库的版本（函数）
selcet 1*55-1 as 计算结果;-- 用来计算（表达式）
select @@auto_increment_increment;-- 查询自增的步长（变量）

-- 成绩+1分
SELECT `studentNo`, `studentResult` +1 AS `提分后` FROM result;

    数据库中的表达式：文本值，列，NUl，函数，计算表达式，系统变量

    select 表达式 from 表

    4.3 条件查询

    作用：检索数据中符合条件的值

    搜索的条件由一个或多个组成，结果为布尔值

    逻辑运算符

    运算符 语法 描述
    and && a and b a&&b 逻辑与，同为真时为真
    or || a or b a||b 逻辑或，同为假时假
    not ! not a !a 逻辑非，取反 
    SELECT studentNo. `studentResult` FROM result

-- 查询考试成绩在95~100分之间
SELECT studentNo, `studentResult` FROM result WHERE StudentResult>=95 AND StudentResult<=100;
-- and&&
SELECT studentNo, `studentResult` FROM result WHERE studentResu1t>=95 && studentResult<=100;
-- 模糊查询(区间)
SELECT studentNo, `studentResult` FROM result WHERE StudentResu1t BETWEEN 95 AND 100;
-- 除了1000号学生之外的同学的成绩
SELECT studentNo, `studentResult` FROM result WHERE studentNo!=1000;
-- !=not
SELECT studentNo, `studentResult` FROM result WHERE NOT studentNo = 1000;

    模糊查询：比较运算符

    运算符 语法 描述
    is null a is null 如果操作符为null，结果为真
    is not null a is not null 如果操作符不为null，结果为真
    between...and a between b and c 若a在b和c之间，结果为真
    like a like b SQL匹配，如果a匹配b，则结果为真
    in a in(a1,a2,...) 假设a在a1，或者a2....其中的一个值，结果为真 
    -- ============like=============
-- 查询姓刘同学
select `name` from `student` where `name` like '刘%';

-- 查询两个字的姓刘的同学
select `name` from `student` where `name` like '刘_';

-- 查询带有龙字的同学
select `name` from `student` where `name` like '%龙%';

-- ============in================
-- 查询学生学号为1001，1002，1003
select `name` from `student` where ·id· in (1001,1002,1003);

-- ============null和not null================
-- 查询年龄为空的
select `name` from `student` where ·age· ='' or is null;

-- 查询出生日期不为空的
select `name` from `student` where ·birthday· is not null;

    通配符

    1. %通配符

    匹配任意长度字符串，包括空字符串

    1. _通配符

    匹配单个字符，不匹配空格

    4.4 联表查询

    join对比

    img 
    /*
    查询思路
    1.分析需求，分析查询的字段来自那些表
    2.确定使用哪种连接查询（7种）
*/

-- Inner join
SELECT s.studentNo,studentName , subjectNo ,studentResultFROM student s
INNER JOIN result r
ON s.studentNo = r.studentNO;

-- Left join
SELECT s.studentNo,studentName , subjectNo ,studentResultFROM student s
LEFT JOIN result r
ON s.studentNo = r.studentNO;

-- Right join
SELECT s.studentNo,studentName , subjectNo ,studentResultFROM student s
RIGHT JOIN result r
ON s.studentNo = r.studentNO;

-- 查询嵌套(三表)
SELECT s.studentNo, studentName, subjectName, `studentResult` 
FROM student s
RIGHT JOIN result r
ON r.studentNo = s.studentNo
INNER JOIN `subject` sub
ON r.subjectNo = sub.subjectNo;
    操作 描述
    inner join 如果表中至少有一个匹配，就返回行
    left join 会从左表中返回所有的值，即使右表中没有匹配
    right join 会从右表中返回所有的值，即使左表中没有匹配

    注：

    • join on ---连接查询

    • where ---等值查询

    自连接

    表和本身的连接：一张表拆为两张表

    父类

    categroyid categroyName
    2 信息技术
    3 软件开发
    5 美术设计

    子类

    pid categroyid categroyName
    3 4 数据库
    3 6 web开发
    5 7 ps技术
    2 8 办公信息

    操作：查询父类对应子类关系

    父类 子类
    信息技术 办公信息
    软件开发 数据库
    软件开发 web开发
    美术设计 ps技术 
    -- 查询父子信息:把一张表看成两张一样的表
SELECT a.`categoryName` AS `父栏目`,b. `categoryName `AS `子栏目` 
FROM `category` As a, `category` As b
WHERE a.`categoryid` = b.`pid`;

    4.5 分页和排序

    排序（order by）

    排序：升序asc，降序desc

    select age from student;
order by age desc;

    分页（limit）

    分页：每页显示几条数据

    语法：limit 起始位置,页面大小

    网页中：当前第几页，总页数，页面大小

    select age from student order by age desc limit 0,5;

第一页：0,5
第二页：6,5
第N页：(n-1)*pageSize,pageSize

    4.6 子查询和嵌套查询

    例一

    -- 1、查询数据库结构-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`,r.`subjectNo`,`studentResult`
FROM `result` r
WHERE SubjectName = (
	SELECT subjectNo FROM `subject`
    WHERE SubjectName = `数据库结构-1`
);

    例二

    -- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s. `studentNo`, `studentName` 
FROM student s
INNER JOIN result r
ON r.studentNo = s.studentNo
WHERE `studentResult`>=80;

-- 在这基础上增加一个科目，高等数学-2
-- 查询高等数学-2的编号
SELECTDISTINCT s.`studentNo`, `studentName` 
FROM student s
INNER JOIN result r
ON r.studentNo = s.studentNo
WHERE `studentResult`>=80 AND `SubjectNo`= (
    SELECT SubjectNo FROM `subject`
    WHERE `SubjectName` = '高等数学-2'
);
-- 其他方式
SELECT studentNo,studentName FROM student WHERE StudentNo IN (
    SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo = (
    SELECT SubjectNo FROM subject WHERE SubjectName = '高等数学-2'
);

    4.7 分组和过滤

    1. 使用group by分组
    select * from student group by gender;
    1. having过滤
    select sum(grade),gender from student group by gender having sum(grade)<300;

    having后可以跟函数，where后不可以。

    5.mysql函数

    5.1 常用函数

    数学运算

    SELECT ABS(-3.141592653589793); -- 绝对值
SELECT CEILING(9.4); -- 向上取整
SELECT RAND(); -- 返回随机数
SELECT SIGN(-10); -- 判断一个数的正负，正数返回1，负数返回-1

    字符串函数

    SELECT CHAR_LENGTH('Helloword!'); -- 字符串长度
SELECT CONCAT('hello','word'); -- 拼接字符串
SELECT INSERT('hello',5,4,'word'); -- 插入字符
SELECT LOWER('HELLOWORD'); -- 转小写字母
SELECT UPPER('helloword'); -- 转大写字母
SELECT INSTR('helloword','e'); -- 返回字符第一次出现的索引
SELECT REPLACE('helloword','h','H'); -- 替换指定字符
SELECT SUBSTR('helloword',4,6); -- 截取指定字符串(字符串,开始位置,截取长度)
SELECT REVERSE('helloword'); -- 反转字符串

    时间日期

    SELECT CURRENT_DATE(); 
SELECT CURDATE(); -- 获取当前日期(yyyy-MM-dd)
SELECT NOW(); -- 获取当前日期时间(yyyy-MM-dd HH:mm:ss)
SELECT LOCALTIME(); -- 获取本地日期时间(yyyy-MM-dd HH:mm:ss)
SELECT SYSDATE(); -- 获取系统日期时间(yyyy-MM-dd HH:mm:ss)

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(); -- 服务器版本

    5.2 聚合函数

    函数名称 描述
    COUNT 计算个数
    SUM 和
    AVG 平均值
    MAX 最大值
    MIN 最小值 
    SELECT COUNT(`s_name`) FROM `student`; -- 统计记录个数，忽略null的值
SELECT COUNT(*) FROM `student`; -- 统计记录个数，包含null值
SELECT COUNT(1) FROM `student`;

SELECT SUM(`s_score`) AS 总分 FROM `score`; -- 求和
SELECT AVG(`s_score`) AS 平均分 FROM `score`; -- 平均值
SELECT MAX(`s_score`) AS 平均分 FROM `score`; -- 最高分
SELECT MIN(`s_score`) AS 平均分 FROM `score`; -- 最低分

    5.3 数据库加密

    md5加密

    用于对普通数据加密，如：用户密码等

    -- 明文密码
insert into user values(1,'qwert','123456');
-- 加密
update user set pwd =MD5(pwd);
-- 校验
select * from user where `name` = 'qwert' and pwd = md5('123456');

    6.事务（transaction）

    6.1 事务的概念

    事务是针对数据库的一组操作，由一条或多条语句组成，同一个事务具备同步的特点，如果其中有一条语句无法执行，那么所有语句都不会执行。

    6.2 事务操作

    mysql默认开启事务

    set autocommit = 0; -- 手动关闭事务
set autocommit = 1; -- 手动开启事务

    事务开启、提交、回滚

    start transaction -- 开启一个事务
...
commit -- 提交
rollback  -- 回滚

    保存点

    savepoint 保存点名称; -- 设置一个事务的保存点
rollback to savepoint 保存点名称; -- 回滚到保存点
release savepoint 保存点名称; -- 撤销保存点

    事物原则：ACID

    四个基本要素：

    1. 原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

    2. 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

    3. 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

    4. 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

    事物隔离级别

    • 读未提交（脏读）（Read uncommitted）

    最低的隔离级别。一个事务可以读取另一个事务并未提交的更新结果

    • 读提交（不可重复读）（read committed）

    大部分数据库采用的默认隔离级别。一个事务的更新操作结果只有在该事务提交之后，另一个事务才可以的读取到同一笔数据更新后的结果。

    • 可重复读（幻读）（repeatable read）

    mysql的默认级别。整个事务过程中，对同一笔数据的读取结果是相同的，不管其他事务是否在对共享数据进行更新，也不管更新提交与否。

    • 串行化（Serializable）

    最高隔离级别。所有事务操作依次顺序执行。注意这会导致并发度下降，性能最差。通常会用其他并发级别加上相应的并发锁机制来取代它。

    问题

    • 脏读

    preview

    • 不可重复读

    img

    • 幻读

    img

    隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read）
    未提交读（Read uncommitted） 可能 可能 可能
    已提交读（Read committed） 不可能 可能 可能
    可重复读（Repeatable read） 不可能 不可能 可能
    可串行化（Serializable ） 不可能 不可能 不可能

    6.3 存储过程的创建

    将sql语句封装成一个代码块，以便重复使用，大大减少工作人员的工作量。

    create proceduce 名称()
begin
-- sql语句
end;

    代码的执行过程

    delimiter //
create proceduce proc()
begin
select * from student;
end //

delimiter;

    if,case,loop,leave,iterate,repect,while等sql语法思路与其他编程语言没有太大区别。

    7.索引

    7.1 索引的概念

    作用：

    1. 提高查询速度

    2. 确保数据的唯一性

    3. 可以加速表和表之间的连接，实现表和表之间的参照完整性

    4. 使用分组和排序子句进行数据检索时，可以减少分组和排序的时间

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

    7.2 索引的分类

    1. 主键索引（PRIMARY KEY）

      • 唯一的标识，主键不可重复，只能有一个列作为主键

    2. 唯一索引（UNIQUE KEY）

      • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

    3. 全文索引（FULLTEXT）

      • 在特定的数据库引擎下才有，myisam

      • 快速定位

    4. 常规索引（KEY/INDEX）

      • 默认的，index。key关键字来设置

    主键索引和唯一索引的区别

    • 主键索引：

      • 主键不能为空
      • 只能有一个主键
      • 主键一定是唯一索引

    • 唯一索引：

      • 可以为空
      • 可以有多个唯一索引
      • 唯一索引不一定是主键

    创建索引

    -- 所有索引数据
show index from student;
    1. 创建表的时候创建索引
    -- 创建时添加索引
create table 表名(
	字段名 数据类型[完整性约束]
    ...
    字段名 数据类型[PRIMARY | UNIQUE  FULLTEXT] index|key [别名](字段名1[(长度)] [asc|desc])
);
    1. 创建完添加索引
    -- 通过create index语句给表添加索引
create 索引类型 index 索引名 on 表名(字段名[(长度)] [asc | desc]);

-- 通过alter table语句添加索引
alter table 表名 add 索引类型 index 索引名 (字段名[(长度)] [asc | desc]);

-- 分析sql执行情况
EXPLAIN SELECT * FROM 表名;

    注：

    create index和alter table区别

    • alter table：

      • 一次可以添加多个索引
      • 索引名可以不写（将会自动创建，使用索引列第一列的列名）
      • 可以创建主键索引

    • create index：

      • 一次只能创建一个索引
      • 必须提供索引名
      • 不可以创建主键索引

    删除索引

    1. 使用alter table删除索引
    alter table 表名 drop 索引名;
    1. 使用drop index删除索引
    drop index 索引名 on 表名;

    7.3 索引的原则

    索引建立的原则

    1. 索引不是越多越好

    2. 不要对进程变动数据加索引

    3. 小数据量的表不需要加索引

    4. 索引一般加在常用来查询的字段上

    索引的数据结构

    1. Hash结构
    2. 二叉树
    3. 红黑树
    4. b-tree树
    5. b+tree树（innoDB的默认数据结构）

    8.视图

    视图的优点

    1. 简单查询语句
    2. 安全性
    3. 逻辑数据独立性

    8.1 视图的创建

    -- 在一张表上创建视图
create view 视图名字 as 完整的查询语句;

-- 在多张表上创建视图
create view 视图名字 
as select s1.name,s2.name
from s1,s2
where age>18；

    查看，更新，删除视图语句和表相同

    9.权限管理和备份

    9.1 用户权限管理

    用户：root用户，普通用户

    创建用户

    create user admin identified by ‘123456’;

    修改密码

    set pwd =   pwd('123456'); -- 修改当前用户的密码
set pwd for admin = pwd('111111'); -- 修改指定用户的密码

    用户重命名

    rename user admin to admin2;

    用户授权

    grant all privileges on 表名 to admin; -- all privileges除了给用户授权，其他权限都允许
show grant for admin; -- 查询权限
release  all privileges on 表名 admin;

    删除用户

    drop user admin;

    9.2 MySQL备份

    • 保证重要的数据
    • 方便数据转义

    备份方式：

    1. 直接拷贝物理文件
    2. 通过可视化工具手动导出
    3. 命令行使用mysqldump
    -- 导出命令：mysqldump -h主机名 -u用户名 -p用户密码 库名 表名 >导出位置:/文件名
mysq1dump -hloca1host -uroot -p123456 school student>D:/a.sql
-- 导出成功
mysq1dump: [warning] using a password on the command 1ine interface can be insecure.

-- 导出多张表：mysqldump -h主机名 -u用户名 -p用户密码 库名 表名1 表名2 表名3 >导出位置:/文件名
mysq1dump -hloca1host -uroot -p123456 school student>D:/a.sql
-- 导出成功
mysq1dump: [warning] using a password on the command 1ine interface can be insecure.

    导入数据

    source d:/a.sql
  • 相关阅读:
    Java实现 LeetCode 730 统计不同回文子字符串（动态规划）
    Python long() 函数
    Python int() 函数
    Python 变量类型
    Python 基础语法
    Python 中文编码
    深度学习点云语义分割：CVPR2019论文阅读
    自动泊车技术短暂困境，前景可期
    深度学习网络模型压缩剪枝详细分析
    用OpenCV4实现图像的超分别率
  • 原文地址：https://www.cnblogs.com/LZC1402548603/p/15685642.html
Copyright © 2020-2023  润新知