数据库基础
一、描述几个概念
1.1 关系型数据库和非关系型数据库
1.1.1 关系型数据库
指采用了关系模型来组织数据的数据库，保持事物的一致性，关系型就是指二维表格模型，一个关系型数据库就是由二维表以及它们之间的联系所组成的一个数据组织。SQLite，Oracle，MySql
优点：使用方便（通用的标准化数据库语言SQL），易于维护（完整性），较低的数据冗余
缺点：读写能力相对较差，表结构固定，大量数据的高效率读写问题
1.1.2 非关系型数据库
指采用键值对存储数据，严格上说不算是数据库，只是一种数据结构化存储的集合。MongoDb，redis，HBase
优点：读写性能相对较高，数据没有耦合性易于扩展，可以支持多种数据类型
缺点：事务处理能力弱，没有完整性约束，难以支持复杂场景
1.2 关系代数
| 类别 | 运算符 | 意义 |
|---|---|---|
| 集合运算 | ∪ - ∩ × | 并 差 交 笛卡尔积 |
| 关系运算 | σ ∏ ⋈ ÷ | 选择 投影 连接 除 |
| 比较运算 | > ≥ < ≥ = <>或!= | 大于 大于等于 小于 小于等于 等于 不等于 |
| 逻辑运算 | ﹁ ∧ ∨ | 非 与 或 |
1.2.1 传统的集合运算
传统的集合运算是二目运算，包括并、交、差、笛卡儿积四种运算。
-
并（union）R∪S
其结果仍为n目关系，由属于R或属于S的元组组成。
-
差（except）R－S
其结果关系仍为n目关系，由属于R而不属于S的所有元组组成。
-
交（intersection）R∩S
其结果仍为n目关系，由既属性R又属于S的元组组成。交可以用差来表示，即R∩S=R-（R-S）。
-
笛卡儿积
关系R和S的笛卡儿积是一个（n+m）列的元组的集合，元组的前n列是关系R的一个元组，后m列是关系S的一个元组。若R有x个元组，S有y个元组，则关系R和S的笛卡儿积有x*y个元组。
1.2.2 专门的关系运算
-
选择（selection）
选择的逻辑表达式的基本形式为：XθY。其中θ代表比较运算符，它可以是比较运算符。X、Y是属性名或常量或简单函数。它是从行的角度进行的运算。
-
投影（projection）
关系R上的投影是从关系R中选出若干属性列组成新的关系。它是从列的角度进行的运算。由于投影取消了某些列之后可能出现重复的行，应取消这些完全相同的行。
-
连接（join）
也称θ连接，它是从两个关系的笛卡儿积中选取属性间满足一定条件的元组。
-
非等值连接‘
θ不为“=”的连接称为非等值连接
-
等值连接
θ为“=”的连接称为等值连接，它是从关系R和S的笛卡儿积中选取A、B属性值相等的那些元组。等值连接的属性名可以相同也可以不相同。
-
自然连接
自然连接是一种特殊的等值连接，它要求两个关系进行比较的分量必须是同名的属性组，并且在结果中把重复的属性列去掉。一般的连接是从行的角度进行操作，自然连接需要取消重复列，所以它是从行和列的角度进行操作。
-
外连接
两个关系R和S在做自然连接时，选择两个关系在公共属性上值相等的元组构成新的关系。此时，关系R和S可能有在公共属性上不相等的元组，从而造成R或S中元组的舍弃，这些舍弃的元组被称为悬浮元组。如果把悬浮元组也保存在结果关系中，而在其他属性上填空值，那么这种连接就叫做外连接。
- 左外连接，如果只保留左边关系R中的悬浮元组就叫做左外连接。
- 右外连接，如果只保留右边关系S中的悬浮元组就叫做右外连接。
- 全外连接，如果保留两边关系R和S中的所有悬浮无级就叫做全外连接。
-
自连接
-
-
除运算（division）
设关系R除以关系S的结果为关系T，则T包含所有在R但不在S中的书香以及值，且T的元组与S的元组所有组合都在R中。
二、MySql数据库
MySQL 是一种开放源代码开放源代码/114160)的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言–结构化查询语言（SQL）进行数据库管理。
2.1基本常用命令如下
-
登录mysql,在终端输入命令
mysql -u root -p -
数据库操作（查看，创建，删除，打开）
show databases; create database 库名 default charset=utf8; drop database 库名; use 库名; -
数据表操作（查看，创建，删除，查看表结构，查看建表语句）
show tables; create table if not exists 表名(字段名1 类型,字段名2 类型)engine=innodb default charset=utf8; drop table 表名; desc 表名; show create table users; -
数据操作
# ------------------------------插入----------------------------------------
insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);
# ------------------------------查询----------------------------------------
select * from 表名;
select 字段1,字段2,字段3 from 表名;
select * from 表名 where 字段=某个值;
# ------------------------------修改----------------------------------------
update 表名 set 字段=某个值 where 条件;
update 表名 set 字段1=值1,字段2=值2 where 条件;
update 表名 set 字段=字段+值 where 条件;
# ------------------------------删除----------------------------------------
delete from 表名 where 字段=某个值;
-
修改数据表结构
# 语法：alter table 表名 add 添加的字段信息 -- 在 users 表中 追加 一个 num 字段 alter table users add num int not null; -- 在指定字段后面追加字段 在 users 表中 age字段后面 添加一个 email 字段 alter table users add email varchar(50) after age; -- 在指定字段后面追加字段，在 users 表中 age字段后面 添加一个 phone alter table users add phone char(11) not null after age; -- 在表的最前面添加一个字段 alter table users add aa int first; # ------------------------------删除字段--------------------------------- # 删除字段 alter table 表名 drop 被删除的字段名 alter table users drop aa; # -----------------------------修改表字段-------------------------------- # 修改表中的 num 字段 类型，使用 modify 不修改表名 alter table users modify num tinyint not null default 12; # 修改表中的 num 字段 为 int并且字段名为 nn alter table users change num mm int; # 注意：一般情况下，无特殊要求，不要轻易修改表结构 # -----------------------------修改表名-------------------------------- # 语法：alter table 原表名 rename as 新表名 alter table old_name rename as new_nmae # ---------------------------修改表的自增值------------------------------ # 在常规情况下，auto_increment 默认从1开始继续递增 alter table users auto_increment = 1000; # ---------------------------修改表的引擎------------------------------ # 推荐在定义表时，表引擎为 innodb。 # 通过查看建表语句获取当前的表引擎 # 修改表引擎语句 alter table users engine = 'myisam'; -
退出MySQL
exit; 或者 quit;
2.2 MySQL数据类型
参考
2.2.1 字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
| 类型 | 说明 |
|---|---|
| CHAR | 1~255个字符的定长字符串，如果不指定默认为CHAR(1) |
| VARCHAR | 变长字符串 |
| TINYBLOB | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 短文本字符串 |
| BLOB | 二进制形式的长文本数据 |
| TEXT | 长文本数据，最大长度为64K的变长文本 |
| MEDIUMBLOB | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 中等长度文本数据 |
| LONGBLOB | 二进制形式的极大文本数据 |
| LONGTEXT | 极大文本数据 |
2.2.2 数值类型
MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
2.2.3 日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
| 类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
2.3 表的字段约束
unsigned无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以)- 字段类型后面加括号限制宽度
char(5).varchar(7)在字符类型后面加限制 表示 字符串的长度int(4)没有意义，默认无符号的int为int(11)，有符号的int(10)int(4) unsigned zerofill只有当给int类型设置有前导零时，设置int的宽度才有意义。
not null不能为空,在操作数据库时如果输入该字段的数据为NULL ，就会报错default设置默认值primary key主键不能为空,且唯一.一般和自动递增一起配合使用。auto_increment定义列为自增属性，一般用于主键，数值会自动加1unique唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度
2.4 MySQL的运算符
- 算术运算符： +、 -、 *、 /、 %
- 比较运算符： =、 >、 <、 >=、 <=、!=
- 数据库特有的比较： in、not in、is null、is not null、like、between、and
- 逻辑运算符： and、or、not
- like: 支持特殊符号%和_ ;
2.5 主键
- 表中每一行都应该有可以唯一标识自己的一列，用于记录两条记录不能重复，任意两行都不具有相同的主键值
- 应该总是定义主键 虽然并不总是都需要主键，但大多数数据库设计人员都应保证他们创建的每个表具有一个主 键，以便于以后的数据操纵和管理。
2.5.1 主键要求
- 记录一旦插入到表中，主键最好不要再修改
- 不允许
NULL - 不在主键列中使用可能会更改的值。
- 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键
- 可以使用多个列作为联合主键，但联合主键并不常用。使用多列作为主键时，所有列值的组合必须是唯一的。
三、命名规范
可能不同公司的命名有不同规范，在这里说明一下一般的规范
- 名称应该是唯一的，并且不能是保留关键字
- 长度控制
- 字符范围：字母，下划线，数字
- 以字母开头
- 避免使用多个连续下滑线
- 避免使用缩写，除非公认的缩写
- 命名尽量使用小写字母，语法关键字使用大写
1.1 数据库规范
- 用产品或项目的名字命名
- 长度最多32字节
- 数据库对象命名使用小写字母并用下划线分割
1.2 数据表规范
- 表名使用复数形式
- 列名使用单数形式
- 临时表以
tmp_为前缀并以日期为后缀 - 备份表以
bak_为前缀并以日期(时间戳)为后缀 - 表中数据必须有唯一标示，即主键定义。无特殊情况，主键都为数字并自增即可
- 表引擎推荐使用
innodb，并无特殊情况都要求为utf8或者utf8mb4的字符编码
1.3 数据路使用规范
- 避免数据类型的隐式转换
- 使用
SELECT <字段列表>进行查询 - 使用包含字段列表的
INSERT语句 - 避免使用子查询，使用
JOIN操作
四、MySQL数据库表引擎
4.1介绍
MySQL 服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。我们知道表是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是 存储引擎 负责的事情。为了实现不同的功能， MySQL 提供了各式各样的存储引擎 ，不同存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同
为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为 MySQL server 的功能，把真实存取数据的功能划分为存储引擎的功能。各种不同的存储引擎向上边的 MySQL server 层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像"读取索引第一条内容"、“读取 索引下一条内容”、"插入记录"等等。
4.2 MyISAM和InnoDB表引擎的区别
4.2.1 事务支持
MyISAM不支持事务，而InnoDB支持。
4.2.2 存储结构
- MyISAM：每个MyISAM在磁盘上存储成三个文件。
.frm，文件存储表结构.MYD，文件存储数据.MYI，文件存储索引
- InnoDB：主要分为两种文件进行存储
.frm存储表结构.ibd存储数据和索引（也可能是多个.ibd文件，或者是独立的表空间文件）
4.2.3 表锁差异
MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
InnoDB：支持事务和行级锁，是 innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有 效的，非主键的WHERE都会锁全表的。
4.2.4 表主键
MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。 InnoDB的主键范围更大，最大是MyISAM的2倍。
4.2.5 表的具体行数
MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。
InnoDB：没有保存表的总行数 (只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后， myisam和innodb处理的方式都一样。
4.2.6 CURD操作
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果数据执行大量的INSERT或 UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table 时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用 truncate table这个命令。
4.2.7 外键
MyISAM，不支持外键。InnoDB，支持外键。
4.2.8 查询效率
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
推荐考虑使用InnoDB来替代MyISAM引擎，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视 图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。
另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优 势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。
4.2.9 MyISAM和InnoDB两者的应用场景
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那 么MyISAM是更好的选择。 InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要 执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。现在默认使用 InnoDB。
五、MySQL 数据操作 DML
5.1 添加数据
格式：INSERT INTO 表名[(字段列表)] VALUE(值列表...);
-- 标准添加（指定所有字段，给定所有的值）
INSERT INTO studnet(id,name,age,sex,classid) VALUE(1,'zhangsan',20,'m','lamp138');
-- 指定部分字段添加值
INSERT INTO studnet(name,classid) VALUE('lisi','lamp138');
-- 不指定字段添加值
INSERT INTO studnet VALUE(null,'wangwu',21,'w','lamp138');
-- 批量添加值
INSERT INTO studnet VALUE
(null,'zhaoliu',25,'w','lamp94'),
(null,'uu01',26,'m','lamp94'),
(null,'uu02',28,'w','lamp92'),
(null,'qq02',24,'m','lamp92'),
(null,'uu03',32,'m','lamp138'),
(null,'qq03',23,'w','lamp94'),
(null,'aa',19,'m','lamp138');
5.2 修改数据
格式：UPDATE 表名 SET 字段1=值1,字段2=值2,字段n=值n... WHERE 条件;
-- 将id为11的age改为35，sex改为m值
UPDATE studnet SET age=35,sex='m' WHERE id=11;
-- 将id值为12和14的数据值sex改为m，classid改为lamp92
UPDATE studnet SET sex='m',classid='lamp92' WHERE id=12 OR id=14 -- 等价于下面
UPDATE studnet SET sex='m',classid='lamp92' WHERE id IN(12,14);
5.3 删除数据
格式：DELETE FROM 表名 [WHERE 条件];
-- 删除stu表中id值为100的数据
DELETE FROM stu WHERE id=100;
-- 删除stu表中id值为20到30的数据
DELETE FROM stu WHERE id>=20 AND id<=30;
-- 删除stu表中id值为20到30的数据（等级于上面写法）
DELETE FROM stu WHERE id BETWEEN 20 AND 30;
-- 删除stu表中id值大于200的数据
DELETE FROM stu WHERE id>200;
276
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
