数据库总结

基础

数据库管理系统支持的功能：

• 数据库定义语言（DDL）：数据定义数据库设计的各种对象，定义数据完整性约束，保密约束等。建表也是
• 数据库操作语言（DML）：提供了对数据的操作，增删改查，查询
• 数据库控制语言（DCL）：实现了对数据库的控制，数据完整性、安全性、数据库恢复等

查看当前引擎

show variables like “storage_engine%”;

引擎

主要有三种引擎

MyISAM：因为不支持事务和外键所以访问速度快，适合不要求事务完整性

InnoDB：事务有优势，可以提交回滚和崩溃恢复，所以需要更多的磁盘空间，需要频繁更新删除。需要并发和高事务的可以用这个

MEMORY：使用内存访问数据，速度快，不安全，小数据的快速访问可以。

#	MyISAM	InnoDB	MEMORY
存储限制	有	64T	有
事务安全	不支持	支持	不支持
锁机制	表锁	行锁	表锁
B树索引	支持	支持	支持
哈希索引	不支持	不支持	支持
全文索引	支持	不支持	不支持
集群索引	不支持	支持	不支持
数据缓存		有	有
索引缓存	有	支持	支持
数据可压缩	支持	不支持	不支持
空间使用	低	高	N/A
内存使用	低	高	中等
批量插入速度	高	低	高
外键	不支持	支持	不支持

数据类型

tinyint，samallint，mediumint，int和bigint无符号最大分别是1，2，3，4，8字节

float和double分别是4和8字节，十位以上精度用double。如果精度再高可以用dec或者decmal(M,D)，采用字符串保存数据，占用M+2字节，常用于货币等的标识

基本操作

修改表

建立数据库

CREATE DATABASE databaset;

建立表

CREATE TABLE databaset(

   number INT,

   name VARCHAR(30),

   属性名 数据类型,

)

删除数据库/表

DROP DATABASE databaseset;
	DROP TABLE table;

增加字段

新增int类型的age字段，FIRST表示增加到第一个

ALTER TABLE tablett
	ADD age INT ==FIRST==;

如果想在指定字段后面增加

ALTER TABLE tablett
	ADD age INT ==AFTER== number;

删除字段

ALTER TABLE tabletable
	DROP age;

修改字段

修改字段的名称和类型

ALTER TABLE {table}
	MODIFY {名} {新类型}

ALTER TABLE {table}
	CHANGE {名} {旧类型} {新类型}

ALTER TABLE {table}
	CHANGE {旧名} {新名} {旧类型}

ALTER TABLE {table}
	CHANGE {旧名} {新名} {新类型}

修改字段的顺序

ALTER TABLE {table}
	MODIFY number INT ==FIRST/AFTER== name

修改数据

修改表中已有的数据某些字段

UPDATE {table} SET {字段}={值}
	WHERE BALABALA

约束

完整性指的是数据准确性和一致性，完整性检查就是检查数据的准确性和一致性，MYSQL 提供了一直机制来检查数据库中的数据是1
否符合规定
常用的约束关键词

• UNIQUE：字段唯一
• NOT NULL：字段不得为空
• DEFAULT：字段默认值
• AUTO_INCREMENT：字段自行增加
• FOREIGN KEY：外键
• PRIMARY KEY：主键，唯一标识该字段

根据最近django的使用，还有一种是在外键中可以添加一些约束约束
table a有一个外键id
table b有一个a_id，然后设置一个on_delete_cascade
是ON_DELETE ON_UPDATE，后面分别有几种值：

• no action：不采取任何措施
• set null：当a被删除时，b的a_id设置为null，前提是可null
• set default：当a被删除时，b的a_id设置为一个指定默认值
• cascade：a随着b的a_id一起更新或者删除

比如django的on_delete=models.CASCADE，就是设置cascade

每个约束都可以设置单一约束和多列约束。比如单一约束就是只设置一个字段的约束，多列约束则是对多个字段设置约束。

单一约束

比如age INT not null

name varchar(40) default ‘ssss’

还可以设置一个名字，在创建表的时候设置
CONSTRAINT uk_bbalaala UNIQUE(name)，即设置name为主键

多列约束

在创建表的时候设置
CONSTRAINT uk_name_id UNIQUE(name,id)，即设置name和id为主键

设置外键约束

在创建表的时候设置
CONSTRAINT uk_foreign FIREIGN KEY(id) REFERENCES out(id);，即设置id为外键，关联到表out的id字段，保证多个表之间的参照完整性。

索引

使用索引可以提高数据库中检索数据的速度。索引是创建在数据库表对象上的，由表中的一个字段或者多个字段生成的键组成，存储在B-树或者哈希树中，可以快速有效查找与键值相关联的字段，根据索引的存储，可以分成B型树索引或者哈希索引。

InnoDB和MyISAM支持BTREE，Memory支持HASH。mysql默认是InnoDB的BTREE

索引除了可以提高查找速度，还可以保证字段的唯一性，实现数据表的完整性。索引有六种：普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。
虽然创建索引可以提高检索速度，但是会占用一些磁盘空间。

一般使用的场景：

• 经常被查询的字段，如where中经常出现的字段
• 在分组中的字段，group by里面的字段
• 存在依赖关系的子表和父表的联合查询，如主键或者外键字段
• 设置唯一完整性约束的字段

不适合的场景：

• 极少使用到的字段
• 有很多重复值的字段

创建、查看索引

创建一个索引

属性名指定索引关联的字段，长度可以指定索引的长度，后面指定是升序还是降序

每个表至少支持16个索引，然后索引长度至少为256个字节

CREATE TABLE {table}(
    属性名 数据类型，
    属性名 数据类型，
    INDEX|KEY {索引名}(属性名 {长度} {ASC|DESC})
);

create table table_index(
    -> id INT NOT NULL UNIQUE PRIMARY KEY,
    -> name VARCHAR(40) NOT NULL,
    -> age INT NOT NULL,
    -> INDEX index_id(id));

插入一些数据

insert into table_index value
    -> (1,'123123',12),
    -> (2,'321321',23);

然后执行explain查询一下，explain语句好像是可以查看哪些索引被使用了，表的读取顺序，表的直接引用等，可以用来优化sql语句之类的，模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈。

explain select * from table_index where id=1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table_index
         type: const
possible_keys: PRIMARY,id,index_id
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra:
1 row in set (0.00 sec)

可以看到possible_keys是索引名，说明索引启用了。

在已有的表创建普通索引

CREATE INDEX {索引名} ON {table_name} ({属性名} ({长度}) {ASC|DESC});
比如
CREATE INDEX index_name ON table_index (name);

还可以通过ALTER的方式添加索引，也就是修改表结构

ALTER TABLE table_index
    ADD INDEX|KEY {索引名} ({属性名} ({长度}) {ASC|DESC});

其实我根本没发现索引有什么用

视图

在有些时候查询表的数据，并不需要获取到所有数据，而是需要部分数据，比如员工的工资字段则不能给员工查询显示出来。为了提高复用性和表的安全性，引入了视图。
视图的本质是虚拟表，，也是一系列字段和相应的行数据组成，但是并不实际存在于数据库中，而是动态生成。
视图可以让程序员只关心某些特定数据，只能看到视图中定义的数据，而不是表中的所有数据，具有如下特点：

• 视图的数据可以来自不同表，是表的抽象和在逻辑意义上的新关系
• 视图是由基本表产生的虚拟表
• 视图的建立和删除不影响基本表
• 视图内容的更新（添加、删除和修改）直接影响基本表
• 但是当视图来自多个基本表的是哦胡，不允许添加和删除数据

创建视图

其实视图就是封装了一些复杂操作的查询语句

mysql> create table salary(
    -> name varchar(50),
    -> salary int,
    -> department varchar(50));

CREATE VIEW {view_name}
	AS {查询语句}

比如针对如下基本表

CREATE VIEW view
    ->	AS
    ->		SELECT name,salary
    ->      	FROM salary;

然后查询一下这个视图，就会发现只有刚才指定的两个字段了

mysql> select * from view;
+--------+--------+
| name   | salary |
+--------+--------+
| 123123 |  12345 |
+--------+--------+

稍微复杂的

还可以封装一些聚合函数，比如SUM、MIN、MAX、COUNT
还可以排个序ORDER BY {id} DESC\ASC

联合查询

两个表共同匹配一些字段然后查询。查询salary和table_index的name相等而且为123123的salary

CREATE VIEW view2
    ->  AS
    ->		SELECT a.salary
    ->			FROM salary as a,table_index as t
    ->				where a.name=t.name and a.name='123123';

子查询相关的，如下。这个就是先获取一个table_index的name集合。然后选择salary.name在这个集合中的salary.salary为一个表。

mysql> CREATE VIEW view3
    -> AS
    -> SELECT s.salary
    -> FROM salary as s
    -> where s.name IN (SELECT name FROM table_index);
Query OK, 0 rows affected (0.02 sec)

mysql> select * from view3;
+--------+
| salary |
+--------+
|  12345 |
+--------+

联合查询UNION、UNION ALL，好像就是把结果都接起来

mysql> CREATE VIEW view4
    -> AS
    -> SELECT name FROM table_index
    -> UNION ALL
    -> SELECT name FROM salary;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view4;
+--------+
| name   |
+--------+
| 123123 |
| 321321 |
| 123123 |
+--------+

查看视图

执行show tables可以发现不仅可以看到基本表，也可以看到视图的存在。
执行SHOW TABLE STATUS FROM {database_name}，后面参数是数据库名，可以展示出当前数据库所有的表和视图的详细信息。我不知道为什么视图的信息都是NULL

mysql> show table status from learning\G;
*************************** 2. row ***************************
           Name: table_index
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 32768
      Data_free: 5242880
 Auto_increment: NULL
    Create_time: 2021-11-23 22:16:20
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 3. row ***************************
           Name: view
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW