Mysql基础

目录

一：主键、外键、索引分别是什么？

二：数据的操作

插入数据

修改数据

删除数据

查询数据

多表查询

事务

事务特性

什么是存储过程和触发器？

数据库怎么优化查询效率？

什么是sql注入？

---

一：主键、外键、索引分别是什么？

主键：表中的一个或多个字段，它的值用于唯一的标识表中的某一条记录

外键：表中一个字段是另外一个表的主键

索引：就是一个字段，设置为索引之后可以快速的找到表中的记录

二：数据的操作

插入数据

 

修改数据

删除数据

查询数据

 

 查询表中名字相同的同学：思路--首先学生name>1也是条件。以名字分组，并且数量大于1

SELECT name FROM student GROUP BY NAME HAVING COUNT(name)>1

排序

使用limit指定查询前N行的结果

多表查询

先给出两张表：

（1）内连接：只返回两个表都有，而且id相等的值

（2）左连接:返回包括左表所有记录，还有和右表中相等的记录

（3）右连接：返回包括右表所有记录，还有和左表中相等的记录

 

 嵌套子查询

分组查询

（1）使用group by分组

（2）使用having设置条件

 

 HAVING子句只能和数值比较

语句的执行顺序如下：

FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

FROM 选择数据来源，WHERE选择条件，符合条件的记录留下来，然后经过GROUP BY分组，分完组根据SELECT子句里面聚合函数做计算，然后ORDER BY对结果集排序，最后交给LIMIT挑选返回哪些分页的数据显示

SELECT deptno, COUNT(*) 

FROM t_emp 

WHERE AVG(sal) >= 2000 GROUP BY deptno;

WHERE子句不允许出现聚合函数。而且WHERE优先级高于GROUP BY，在条件筛选的时候不知道按照什么范围去筛选，是全部数据筛选还是分部门数据筛选呢？

解决方案就是HAVING子句，HAVING子句的出现主要是为了WHERE子句不能使用聚合函数的问题，HAVING子句不能独立存在，必须依赖于GROUP BY子句而存在，GROUP BY 执行完成就立即执行HAVING子句

SELECT deptno, COUNT(*)
FROM t_emp
GROUP BY deptno HAVING AVG(sal) >= 2000;

事务

 默认情况下，MySQL执行每条SQL语句都会自动开启和提交事务。为了让多条SQL语句纳入到一个事物之下，可以手动管理事务

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

事务特性

原子性，一个事物中的所有操作要么全部完成，要么全部失败。事物执行后，不允许停留在中间某个状态。

一致性，不管在任何给定的时间，并发事务有多少，事务必须保证运行结果的一致性。事务可以并发执行，但是最终MySQL却串行执行。

怎么保证一致性？

阻止事务之间相互读取临时数据

隔离性，每个事务只能看到事务内的相关数据，别的事务的临时数据在当前事务是看不到的。隔离性要求事务不受其他并发事务的影响，在给定时间内，该事务是数据库运行的唯一事务。

持久性，事务一旦提交，结果便是永久性的。即便发生宕机，仍然可依靠事务日志完成数据持久化

如果事务没有隔离性，按照不受控制的顺序并发读取和修改数据，想像一下会出现哪些问题？

一、脏读：一个事务读取了第二个事物未提交的数据，当第二个事务回滚了数据之后，第一个事务就读取到了无效的数据

二、不可重复读：一个事物前后两次读取的同一数据不一致。

三、幻读：指一个事务两次查询的结果集记录数不一致

锁机制: innodb默认是行锁, 但是如果在事务操作的过程中, 没有使用到索引,那么系统会自动全表检索数据, 自动升级为表锁

行锁: 只有当前行被锁住, 别的用户不能操作

表锁: 整张表被锁住, 别的用户都不能操作

更详细教程学习可参考：数据库MySQL详解_砖业洋__-CSDN博客_mysql数据库

什么是存储过程和触发器？

存储过程：是数据库中的一个对象

触发器：是一种特殊类型的存储过程，触发器生效是通过update、delete、insert等操作对数据进行操作时

数据库怎么优化查询效率？

1，引擎选择：需要事务选择innoDB，不需要事务选择MyISAM

2，分表分库，主从分表，读写分离

3，查询优化，避免全表扫描，首先考虑where和group by涉及的列上建索引

4，尽量避免在where子句对字段进行null值判断，使用!=,或<>操作符，或使用or连接条件，否则引擎放弃索引而进行全表扫描

5，update如果只更改1，2个字段，不要update全部字段，频繁调用会引起明显的性能消耗，带来大量的日志

6，对于大数据量的表，要先分页再join

7，尽量避免大事物操作，提高系统并发能力

8，使用缓存，把经常访问和不常变化的数据放到缓存中，节约io

9，优化硬件，使用ssd

什么是sql注入？

程序开发过程中不注意规范书写sql语句对特殊字符进行过滤导致客户端可以通过全部变量post、get提交一些sql正常执行，产生sql注入