目录
1.基础使用
1.1数据库操作
查询所有的数据库:show databases;
删除数据库:delete database db_name;
使用数据库:use db_name;
创建数据库:creater dtatbase db_name character set utf8mb4;
1.2表操作
创建表:create table t_name{……};
删除表:drop table t_name;
查询所有表:show tables;
修改表结构:alert table t_name add 字段名 类型;
1.3新增数据
添加单条数据:insert into t_name(字段名……) values(值……);
多行添加:insert into t_name(字段名……) values(……),(……)……;
修改数据:update t_name set 字段名 = 值 where ……;
清空数据:delete
1.4数据查询
全列查询:select * from t_name;
指定列查询:select 列名 from t_name;
表达式查询:select 列名 + 10 from 表名;(企业中禁止使用)
别名查询:uname -> username select uname as username from 表名;
聚合查询:count()、sum()、avg()、max()、min()
去重:distinct——select distinct 列名 from 表名;
排序查询:order by 成绩 desc(倒序)/asc(升序)
分页查询:limit 3,3 -> 查询三条数据,跳过前三条 -> 第二页 4-6
1.5数据查询(进阶)
1.5.1.内联查询
【阿里巴巴规定:禁止三张表以上的联表查询】
查询的是两张表共有的部分
内敛查询:select a.*, b.* from a inner join b on a.id = b.aid;
1.5.2.外联查询
左连查询:left join
select a.*, b.* from a left join b on a.id = b.aid;
右连查询:right join
select a.*, b.* from a right join b on a.id = b.aid;
1.5.3.子查询
select * from student where classid in (select classid from student where username = "**");
2.索引
MySQL高效运行的最主要手段
2.1普通索引
creat index 索引名 on 表名(字段);
2.2主键索引
无需显示创建
2.3唯一索引
create unique index 索引名 on 表名(字段);
2.4组合索引
create index 索引名 on 表名(字段A,字段B);
2.5查询索引
show index from 表名;
2.6删除索引
drop index 索引名 on 表名;
2.7索引注意事项
- 在生产服务不要直接执行添加索引的操作(创建索引的过程非常耗时,且数据量越大创建的时间就越长)。
- 对于多读(查询)的场景适合使用索引,对于添加删除比较多的场景就不适用了。
- 避免使用查询的时候。不能触发索引查询。例如:在列上进行赋值运算。
面试题1:什么情况下索引不会触发?
答:
- 当对于一个列(已经设置索引的列),进行赋值查询的时候。
- 避免使用like查询,当使用模糊查询的时候有可能不触发索引,比如使用like’%XXX%'不会触发索引,当使用like’XXX%'是可以触发索引的。
- 当使用的是组合索引的是,一定要注意遵循最左匹配原则。
- 尽量避免使用or查询,有可能导致索引不生效。
- 不要使用!=、<>都会导致索引不生效。
- 如果是字符串查询,一定要加单引号,否则索引不生效(索引失效)。
面试题2:如何优化MySQL的性能?
答:
- 在查询比较多的关键列上加索引。
- 开启慢查询日志,找到执行比较慢的sql,针对化的进行优化。
- 能使用主键索引的时候,尽量使用主键索引。
- 分表(垂直分割)分库(水平分割)
- 提高数据库硬件配置,更换一个读写性能更好的磁盘,更换一个更大的内存。
面试题3:主键索引和普通索引的区别?
答:
- 主键索引不需要显示创建。
- 主键索引不能删除,普通索引可以。
- 主键索引查询更快,普通索引因为有回表查询,索引性能没有主键索引性能高。
面试题4:如何开启慢查询?
答:
- 修改配置文件:
修改配置文件my.cnf,在[mysqld]下的下方加入
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
- 重启MySQL服务
service mysqld restart
2.8最左匹配原则
在组合索引中,table(A,B,C)
where A=XXX and B=XXX;可以触发索引
where C=XXX and A=XXX;不可以触发索引,因为不符合ABC的从左到右的顺序
3.事务
稳定的使用MySQL
3.1事务的特性(ACID)
- 隔离性:并发事物执行时,不能相互影响,隔离问题。
- 原子性:要么全部成功,要么全部失败。
- 持久性(永久性):事物执行完成后,结果要一直保存下来。
- 一致性:事物执行前后,数据要保证是正确的。
3.2并发事存在的问题
- 脏读:事务A读取到了事务B未提交的事物,事务B回滚了。
- 不可重复读:事务A使用相同的查询条件,读取到了不一样的数据,因为在这期间事务B修改了数据。
- 幻读:事务A将数据修改之后,事务B又添加一条数据,导致事务A执行的结果和预期不一致。
面试题:不可重复读和幻读的区别?
答:不可重复读侧重点在于修改,幻读侧重点在于添加或删除操作。
3.3事物的隔离级别(四个)
为了解决并发事物的额问题而诞生的。
脏读 | 不可重复 | 幻读 | |
---|---|---|---|
读未提交 | √ | √ | √ |
读已提交 | × | √ | √ |
可重复度 | × | × | √ |
串行化 | × | × | × |
默认隔离级别:可重复读
串行化:性能不高
幻读问题的解决方法:
- MVCC标识符,在读一次。
- 间隙锁gap
4.执行引擎/日志
默认引擎:InnoDB;Myisam:支持事物,运行效率高,快
5.表的约束
物理外键 -> 逻辑外键
物理删除 -> 逻辑删除
6.表的关系
一对一
一对多
多对多(最少需要三张表)