文章目录
基础
数据库管理系统支持的功能:
- 数据库定义语言(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