连接数据库指令:mysql - u用户名 -p密码 -h IP地址 -P 端口号
一、MySQL 基础使用
1、数据库操作:
查询数据库:show databases;
删除数据库:drop database db_name;
使用数据库:use db_name;
创建数据库:create database db_name character set utf8mb4;
2、表操作
创建表:create tables tables_name(字段1,字段2,…);
删除表:drop table table_name;
查询所有表:show tables; (删除所有数据以及表结构)
修改表结构:alter table table_name add clumom 字段名 类型;
新增数据:
添加单条数据:insert into table_name(字段名…) values(…)
添加多行信息:insert into table_name(字段名…) values(…),(…)
修改数据:update 表名 set 字段名=值 where…
清空数据:delete; (清空表中数据,保留表结构)
全列查询:select * from 表名;
指定列查询:select 列明 from 表名;
表达式查询:select math+10 from 表名; 【禁止使用】
别名查询:select uname as username from 表名;
聚合查询:
聚合函数包括: count()、sum()、avg()、max()、min()
去重:distinct —— select distinct 列名 from 表名;
排序:order by 成绩 desc/asc; desc是倒叙;asc是升序
where查询:
Null和’'不是同一个意思
分页查询:limit
limit 3,3 ——> 查询3条数据,跳过前3条,查询的是第4-6条数据
3、进阶操作
(1)查询的进阶(多表查询):
内联查询:inner join
select a.,b. from a inner join b; (这个查询出来的是一个笛卡尔积,是说有数据的组合)
select a.,b. from a inner join b on a.id = b.id
外联查询:
左连接查询:left join
select a.,b. from a left join b on a.id = b.id
右连接查询: right join
子查询: select * from student where classid in (select classid from student where username=‘周成’);
现通过学表表查到’周成’的classid,然后将这个calssid作为另一张表的入参
(2)表约束:
非空约束:not null;
唯一约束:unique;
主键约束:primary key;
外键约束:foreign key;【禁止使用,物理外键】
将 物理外键 转换成 逻辑外键 使用
物理删除 delect where … 将数据直接删除
逻辑删除 给表添加一个delect的属性,默认为0,当想要删除这个表时,另delect的属性为1,在程序上认为它被删除
默认值:default;
(3)表设计:
三范式:
- 第一范式(1NF)无重复的列(确保每列的原子性.)
- 第二范式(2NF)属性完全依赖于主键(确保表中的每列都和主键相关.)
- 第三范式(3NF)属性不依赖于其它非主属性(确保每列都和主键列直接相关,而不是间接相关.)
表的关系:
- 1对1:1张表就可以完成(一到两张表)
- 1对多:一个班级里面有多个学生
- 多对多:最少需要三张表
二、索引(保证 MySQL 高效运行的主要手段)
1、索引的分类
(1)普通索引: create index 索引名 on 表名 (字段)
(2)主键索引: 在设置表的主键时就会设置索引,无需显示的创建
(3)唯一索引: create unique index 索引名 on 表名 (字段)
(4)组合索引: create index 索引名 on 表名(字段A,字段B)
2、索引的操作
查看一个表的所有索引:show index from table_name;
删除索引:drop index [索引名] on [表名];
索引使用的注意事项:
- 1、在生产服务器不要直接执行添加索引的操作(创建索引的过程特别慢,且数据量十分巨大的情况下运行时间越长)
- 2、对于多读(多查询)场景适合使用索引。面对经常添加、删除的场景,尽量不使用索引
3、避免查询的时候,不触发索查询
什么情况下索引不会被触发?
- 1、当一个列(已设置索引的列),进行赋值查询的时候。
- 2、避免使用LIke 查询,当使用Like 查询的时候 可能不触发索引,
比如使用 like ‘%XXX’ 就不会触发
当使用 like ‘XXX%’ 会触发索引 - 3、当使用组合索引的时候,一定要注意遵循最左匹配原则
比如当一个表 表名(A,B,C)
当使用 where A=XXX and B=XXX and C=XXX; 就可以触发索引
当使用 where A=XXX and B=XXX 也是可以触发
当使用 where A=XXX and C=XXX 同样可以触发
但当使用 where C=XXX and A=XXX 是不会触发索引的 - 4、尽量避免使用 or 查询,有可能导致索引不生效
- 5、不要使用 != 或 < >
- 6、如果是字符串查询,一定要加上单引号,否则会导致索引失效
如何优化MySQL的性能?
- 1、在查询比较多的关键列上加索引
- 2、开启慢查询日志,找到执行比较慢的SQL,针对性地进行优化
- 3、能使用主键索引的时候,尽量使用主键索引。
- 4、分表(垂直分割)分库(水平分割)
- 5、提高数据库的硬件配置。更换读写性能更高的磁盘,更换更大的内存
主键索引 和 普通索引 的区别?
- (1)主键索引不需要认为的创建,在创建表的时候就有了
- (2)主键索引不能删除,而普通索引可以
- (3)主键索引查询更快,而普通索引因为有回表查询,索引的性能没有主键索性能高。
数据库使用的是B+树,为什么不是用B树?
- (1)因为当数据量比较大的时候,B树的层级是比较高的,当使用B+树时,数据量越大,每一个叶子节点存放的元素就越多,这样子当它的数据量比较高的情况下B+树的层级是比较低的,这样查询效率就比较高
- (2)当使用B树时,在查询时需要进行中序遍历;当使用B+树时,每一个层级之间使用链表查询,避免了全树的中序遍历
什么是回表查询?
- 对于主键索引来说,粉红框内的数字就是它主键索引的ID;而在他的非叶子节点中存放的就是ID所对应的全数据
- 而对于非主键索引来说,当根据非主键索引找到对应的主键索引ID,然后再去主键索引的树上去找到主键索引ID对应的全数据
如何开启慢查询?
(1)修改配置文件
修改配置文件 my.cnf,在[mysqld]下的下方加入:
[mysqld]
slow_query_log = ON(表示开启慢查询)
slow_query_log_file = 日志文件存放的目录地址
long_query_time = 设置慢查询的时间(根据情况设定,单位为秒)
(2)重启MySQL服务
service mysql restart
三、事务(保证MySQL 稳定的使用)
1、事务特性(ACID)
(1)隔离性:并发事务执行时,隔离问题 —— 事务在执行期间不能相互影响
(2)原子性:要么全部成功,要么全部失败
(3)持久性(永久性):事务再执行完成后,结果要一直保存下来
(4)一致性:事务在执行前后,数据要保证是正确(比如转账前后金额总数要一致)
2、并发事务存在的问题:
(1)脏读:事务A 读取到了 事务B 没提交的数据,然后 事务B 回滚了。
(2)不可重复读:事务A 使用相同的查询条件,读取到了不一样的结果,因为在这个过程中,事务B 修改了数据
(3)幻读:事务A 将数据修改之后,事务B 又添加了一条数据,导致事务A 执行的结果和预期不一致
不可重复读 和 幻读 的区别:
不可重复读的侧重点在于修改,侧重于修改,数据原本是存在的
幻读侧重点在于添加或删除,数据忽然出现或是消失了
3、事务的隔离级别:
事务的隔离级别就是为了解决并发事务存在的三个问题而提出的。
√ 表示会出现这种问题
× 表示没有这种问题
脏读 不可重复读 幻读
读未提交 √ √ √
读已提交 × √ √
可重复读 × × √ (MySQL的默认隔离界别)
串行化(加锁) × × ×
幻读问题解决方案:
1、使用MVCC 版本号来解决,若在读取过程中发现版本号不一样,那说明在这期间有人去修改了,重新读一次
2、gap 间隙锁 在事务A进行数据修改时,事务B 等待事务A执行完后再进行执行
四、MySQL 执行引擎 / MySQL 日志
myisam (不支持事务、但是运行快)
InnoDB (MySQL 的默认引擎)