Mysql基础

• MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中。支持大型的数据库，可以处理拥有上千万条记录的大型数据库。使用标准的 SQL 数据语言形式。

sql语法

• 创建数据库：create database 数据库名;

• 使用数据库：use 数据库名;

• 删除数据库：drop database 数据库名;

• 创建表：create table 表名(字段名 字段类型 [其他属性])ENGINE=InnoDB DEFAULT CHARSET=utf8;

  [其他属性]包括： NOT NULL非空，AUTO_INCREMENT自增，PRIMARY KEY主键

• 删除表：drop table 表名;

• 修改表名或字段：alter table 表名 [drop 字段| add 字段]删除列/添加列

  可以使用MySQL提供的关键字 first (设定位第一列)， after 字段名（设定位于某个字段之后）。

  alter table 表名 modify 字段名 字段类型; 修改该字段的类型为指定类型。
  alter table 表名 change i j BIGINT; 将字段i修改为j，类型为BIGINT。
  alter还可以用来修改字段默认值。

• 插入数据： insert into 表名 (字段，字段...) values (value,value...);

• 更新数据：update 表名 set 字段=value[, 字段=valus] [where 需要满足的条件语句];

• 删除数据：delete from 表名 [where 需要满足的条件语句];

• 查询：select 字段1,字段2... from 表名 [where 条件] [LIMIT N] [ OFFSET M];

  可以使用 LIMIT 属性来设定返回的记录数。
  可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
  where可以连接多个表。

• like：用在where中，用%模糊查询，不加%时和=效果相同

• union：用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。（可以用union all选取重复数据）

• 排序：order by 字段 [asc][desc]，asc升序，desc降序。

• 分组：group by 字段，根据一个或多个字段对结果集分组。可以对分组后的结果集运用函数

  SELECT name, count(*) FROM employee group by name;按姓名分组后，统计每个名字对应几条数据。

• 表的连接：join 另一个表 on 连接条件

  • INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
  • LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
  • RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

• NULL值：在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

• 正则表达式：MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

  

• 临时表：临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间（如果使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然也可以手动销毁。）

数据类型

• MySQL中定义数据字段的类型对数据库的优化是非常重要的。MySQL支持所有标准SQL数值数据类型。
• 数值类型：

  

• 日期和时间

  
  其中TIMESTAMP自动更新

• 字符串

  

存储引擎

• 与其他数据库例如Oracle 和SQL Server等数据库中只有一种存储引擎不同的是，MySQL有一个被称为“Pluggable Storage Engine Architecture”(可替换存储引擎架构)的特性，即MySQL数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。
• MySQL数据库在实际的工作中分为了语句分析层和存储引擎层，其中语句分析层就主要负责与客户端完成连接并且事先分析出SQL语句的内容和功能，而存储引擎层则主要负责接收来自语句分析层的分析结果，完成相应的数据输入输出和文件操作。简而言之，就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
• 实际开发中使用最多的：【MyISAM】和【InnoDB】

  MyISAM：高速引擎，拥有较高的插入，查询速度，但不支持事务
  InnoDB：5.5版本后MySQL的默认数据库，支持事务和行级锁定，比MyISAM处理速度稍慢。

事务

• MySQL 事务主要用于处理操作量大，复杂度高的数据。符合ACID原则。

  例如在人员管理系统中，删除一个人员时，即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务。

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

• 在 MySQL 命令行的默认设置下，事务都是自动提交的（可以设置set autocommit = off改为手动提交），即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务。

• begin 或start transaction开启一个事务。

• commit 提交事务。

• rollback回滚，回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

  mysql的事务提交(commit)与回滚(rollback)详解
  通过set autocommit设置手动提交只对当前session有效：

  • 首先新开session1并建表create table test_a(id1 int,id2 int);
    然后设置手动提交set autocommit=0;（这样设置只对当前session有效，是临时的，如果想要永久全局设置，修改my.cnf文件，添加autocommit=0)；
    再向表中插入数据insert into test_a values(1,1);
  • 此时新开第二个session2，查询该表select * from test_a；可以发现，此时在session2上是查询不到数据的，原因就是因为在session1的事务还未提交。但是此时在session1是可以查到，这是不是和上述有点矛盾呢，其实不然，未提交的数据会临时写到内存或磁盘（内存不足的情况下），且未提交的数据上有个锁（涉及隔离级别，后续讲解），锁是只有当前事务唯一持有的，所以其他事务取不到，但当前事务是可以读取的。
  • 接着换一种思路，在session2（自动提交）上插入一条新数据insert into test_a values(2,2);然后去session1查询，发现session1上仍然查不到新增的数据，这里有人会问session2不是设置的自动提交吗，为什么session1查不到数据呢？这是因为当session为非自动提交状态时，自事务开始后的DML操作对其都是不可见的，只有事务结束后才可见。在session1执行commit，就可以查询到新增数据了。
  • 再看第三种情况，在session1新增一条数据，此时在session2查询是查不到新增数据的；此时在session1再新建一张表，再去session2查询，结果发现在session1未执行commit命令的情况下，session2竟然也可以查询到新增数据了。这是因为session1执行了DDL操作，触发了隐式提交事务的规则，所以其他session也可以看到DDL的修改了。但是drop命令有点特殊，如果事务还未结束，drop命令会被阻塞。

• savepoint 标识符保存点，允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT。

索引

• 索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
• 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
• 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。另外，建立索引会占用磁盘空间的索引文件。

普通索引

• 创建索引：create index 索引名 on 表名(字段名(length)); 也可以创建表时同时指定索引；或者用alter添加索引。
• 删除索引：drop index 索引名 on 表名;

唯一索引

• 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
• 创建：create unique index 索引名 on 表名(字段名(length));

全文索引

• FULLTEXT，仅用于MyISAM，目前只有char、varchar，text 列上可以创建全文索引。
• 主要用来查找文本中的关键字，而不是直接与索引中的值相比较。更像是一个搜索引擎，而不是简单的where语句的参数匹配。

组合索引

• 多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀匹配。

  在创建多列索引时，根据业务需求，where子句中使用最频繁的一列放在最左边，因为MySQL索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以当创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。

【参考文档】https://www.nowcoder.com/tutorial/10006/3aab8cb0cbb94e608de595c9084b71fb