MySQL第一章笔记

1、数据类型
数据类型决定了数据在计算机中的存储格式，代表不同的信息类型。常用的数据类型有整数数据类型、浮点数数据类型、精确小数类型、二进制数据类型、日期/时间数据类型。表中的每一个字段就是某种指定数据类型。

类型	大小	范围（无符号）	用途
TINYINT	1Bytes	（0,255）	小整数值
SMALLINT	2Bytes	（0,65535）	大整数值
MEDIUMINT	3Bytes	（0,16777215）	大整数值
INT或INTEGER	4Bytes	（0,42994967295）	大整数值
BIGINT	8Bytes		极大整数值
FLOAT	4Bytes		单精度浮点数值
DOUBLE	8Bytes		双精度浮点数值
DECIMAL		对DECIMAl(M,D),如果M>D,为M+2否则为D+2

日期和时间类型
表示时间值得日期和时间类型为DATETIME、DATE、 TIMESTAMP、TIME和YEAR。
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT。

类型	大小	用途
CHAR	0-255bytes	定长字符串
VARCHAR	0-65535bytes	变长字符串
BLOB	0-65535bytes	二进制形式的长文本数据
TEXT	0-65535bytes	长文本数据

主键
主键（Primary Key）又称主码，用于唯一地标识表中的每一条记录。可以定义表中的一列或多列为主键，主键列上既不能有两行相同的值，也不能为空值。假如，定义authors表，该表给每一个作者分配一个“作者编号”，该编号作为数据表的主键，如果出现相同的值，将提示错误，系统不能确定查询的究竟是哪一条记录；如果把作者的“姓名”作为主键，则不能出现重复的名字，这与现实中的情况不符，因此“姓名”字段不适合作为主键。

2、数据库技术构成
数据库系统
数据库系统有3个主要的组成部分
数据库：用于存储数据的地方。
数据库管理系统：用于管理数据库的软件。
数据库应用程序：为了提高数据库系统的处理能力所使用的管理数据库的软件补充。

3、SQL语言
SQL语言包含以下4部分。
（1）数据定义语言（Data Definition Language DDL）：DROP、CREATE、ALTER等语句。
（2）数据操作语言（Data Manipulation Language DML）：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。
（3）数据查询语言（Data Query Language DQL）：SELECT语句。
（4）数据控制语言（Data Control Language DCL）：GRANT、REVOKE、COMMIT、 ROLLBACK等语句。

4、 数据库操作
显示所有的库：SHOW DATABASES;
创建库：CREATE DATABASE database_name;
查看数据库的定义：SHOW CREATE DATABASE mybatis;

删除库：DROP DATABASE database_name
其中，“database_name”为要删除的数据库的名称。若指定的数据库不存 在，则删除出错

5、 InnoDB表
从MySQL 8.0开始，系统表全部换成事务型的InnoDB表，默认的 MySQL实例将不包含任何MyISAM
表，除非手动创建MyISAM表。
SELECT DISTINCT ENGINE FROM information_schema.TABLES;

面试题：
1. InnoDB和MyISAM的区别
区别：

1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB
   的重要原因之一；
2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
3. InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，
   然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成InnoDB 的重要原因之一；
   如何选择：
   1.是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
   2.如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。
   3.系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；
   4.MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹
   的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

5.删除数据库时需要注意什么？
使用DROP DATABASE命令时要非常谨慎，在执行该命令时MySQL 不会给出任何提醒确认信
息。用DROP DATABASE声明删除数据库后，数 据库中存储的所有数据表和数据也将一同被删除，而且不能恢复。
练习：
1.查看当前系统中的数据库
2.创建数据库mydb,使用语句查看数据库定义新新
3.删除数据库mydb

6、 创建数据表
在创建完数据库之后，接下来的工作就是创建数据表。所谓创建数据 表，指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数 据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整 性和域完整性等）约束的过程。
数据表属于数据库，在创建数据表之前，应该使用语句“USE <数据库 名>”指定操作是在哪个数据库中进行，如果没有选择数据库，就会抛出“No database selected”的错误。
创建数据表的语句为CREATE TABLE，语法规则如下：

使用CREATE TABLE创建表时，必须指定以下信息：
（1）要创建的表的名称，不区分大小写，不能使用SQL语言中的关键 字，如DROP、ALTER、INSERT
等。
（2）数据表中每一列（字段）的名称和数据类型，如果创建多列，就要用逗号隔开。

7、 表约束
1.数据完整性
数据的完整性是指数据的可靠性和准确性
1.实体完整性：实体的完整性强制表的标识符列或主键的完整性(通过索引,唯一约束,主键约束或标识列属性).
2.域完整性：限制类型(数据类型),格式(通过检查约束和规则),可能值范围(通过外键约束,检查约束,默认值定义,非空约束和规则).
3.引用完整性：在删除和输入记录时,引用完整性保持表之间已定义的关系.引用完整性确保键值在所有表中一致.这样的一致辞性要求不能引用不存在的值.如果一个键值更改了,那么在整个数据库中,对该键值的引用要进行一致的更改.
4.自定义完整性：用户自己定义的业务规则.
四种完整性约束：
实体完整性：唯一约束、主键约束、标识列
域完完整性：限制数据类型、外键约束、默认值、非空约束
引用完整性：外键
自定义完整性：过程，触发器等

2、约束
约束是在表上强制执行一些数据校验规则，被插入、修改或删除的数据必须符合在相关字段上设置的这些约束条件
五类完整性约束：
NOT NULL 非空
UNIQUE 唯一
PRIMARY KEY 主键
FOREIGN KEY 外键
CHECKED 检查（MySQL不支持的）

3、 主键约束
1、单字段主键
主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键能够唯一地标 识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以 加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关 系，它们之间是一一对应的。主键分为两种类型：单字段主键和多字段联合主键。

在MySQL中AUTO_INCREMENT的初始值是1，每新增一条记录，字段 值自动加1。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字 段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类
（1)定义列的同时指定主键，语法如下
字段名 字段类型 primary key
（2）定义表之后指定主键
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY（字段）;

主键选取原则：

1. 主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。
2. 主键应该是单列的，以便提高连接和筛选操作的效率。
3. 永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对用户无意义的原则被违反了。
4. 主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
5. 主键应当有计算机自动生成

2、非空约束
非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空 约束的字段，如果用户在添加
数据时没有指定值，数据库系统会报错。
字段名 数据类型 not null

3 唯一约束
唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出 现一个空值。唯一约束可以确
保一列或者几列不出现重复值。 唯一性约束的语法规则如下
字段名 数据类型 UNIQUE
如果定义完表后也可以给某个字段添加唯一约束，语法如下：
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (约束字段);
删除唯一索引的语法：
ALTER TABLE 表名 DROP INDEX 约束名;

4 默认值约束
默认约束（Default Constraint）指定某列的默认值。如男性同学较多， 性别就可以默认为‘男’。如果插
入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。 默认约束的语法规则如
下：
字段名 字段类型 default 默认值

5、 查看表结构
DESCRIBE/DESC语句可以查看表的字段信息，其中包括字段名、字段 数据类型、是否为主键、是否有默认值等。语法规则如下：
DESCRIBE 表名;
或者简写为
DESC 表名;

其中，各个字段的含义分别解释如下
NULL：表示该列是否可以存储NULL值。
Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；
UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定 值允许出现多次。
Default：表示该列是否有默认值，有的话指定值是多少。
Extra：表示可以获取的与给定列有关的附加信息，例如 AUTO_INCREMENT等。

查看表详细结构语句：
SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE 语句，语法格式如下：
SHOW CREATE TABLE employee;

6、 修改表结构
修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用 ALTER TABLE语句修改表。常用的修改表的操作有修改表名、修改字段数 据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。本节将对和修改表有关的操作进行讲解。
1.修改表名
ALTER TABLE <旧表名> RENAME [TO] <新表名>；
2.修改字段的数据类型
ALTER TABLE<表名> MODIFY<字段名> <数据类型>；
3.修改字段名
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>
4.添加字段
ALTER TABLE <表名> ADD <新字段名> <新字段类型> <约束条件> [FIRST|AFTER 已存在字段名];
5.删除字段
ALTER TABLE <表名> DROP <字段名>；、
6.修改字段的排序位置
ALTER TABLE<表名> MODIFY <字段1> <数据类型> FIRST|AFTER<字段2>；
7.删除表的外键约束
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>；
8.更改表的存储引擎
ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;
9.删除数据表
DROP TABLE [IF EXISTS] 表1，表2，— 表n；