4.9 MySQL的基本使用 =================== |image0| -------------- 数据库是每个后端程序员都必须扎实掌握的一项基本技能，而做为最主流的关系型数据库 MySQL，上手也极为容易，这里是我几年前自学 MySQL 时做下的笔记，现整理出来，一共两篇，这是第一篇。 在这篇文章里，我就介绍了一些 MySQL 的基本操作（增删改查）及表结构的解析。 一、库操作 ---------- -------------- 1.1 新增数据库 ~~~~~~~~~~~~~~ :: 【语法】：create database 数据库名字 [库选项]; 一、数据库名字 1. 要避免使用MySQL关键字和保留字，若非要用，必须用反引号括起来，比如`database` 2. 要避免使用中文，大部分情况下直接设为中文会出错（字符集错误），若非要用，得先执行 set names gbk，告诉服务器你当前使用哪种字符集。 二、库选项（可选） 字符集：charset/character set 具体字符集(数据存储的编码格式): 常用字符集: GBK和UTF8 校对集：collate 具体校对集(数据比较的规则)，一般只需指定字符集，校对集会自动生成对应的。 库选项是可选的，默认值是utf-8 其值可以在对应数据库文件夹下的 db.opt 文件中看到。如下：第一行字符集，第二行校对集 default-character-set=utf8 default-collation=utf8_general_ci 也就是说，下面这两种写法等价： create database mydatabase; create database mydatabase charset utf8 collate utf8_general_ci; 1.2 查看数据库 ~~~~~~~~~~~~~~ :: # 查询全部 show databases; # 查询部分（模糊查询） show databases like '匹配规则'; %：匹配多个字符 _：匹配单个字符（也因此，如要匹配_本身，需用\进行转义） 1.3 更新数据库 ~~~~~~~~~~~~~~ :: 数据库的名字不可更改 更改仅限库选项：字符集和校对集 【语法】：alter database 数据库名字 库选项; 举例： alter database mydatabase charset gbk collate gbk_chinese_ci; 1.4 删除数据库 ~~~~~~~~~~~~~~ :: drop database 数据库名字; 删除数据库是不可逆的，要谨之又慎。 1.5 选定数据库 ~~~~~~~~~~~~~~ :: use 数据库名字; 二、表操作 ---------- -------------- 2.1 新增数据表 ~~~~~~~~~~~~~~ :: 【语法】： Create table [if not exists] 表名/数据库名.表名( 字段名字 数据类型, 字段名字 数据类型 -- 最后一行不需要逗号 )[表选项]; 【说明】 If not exists: 如果表名不存在,那么就创建,否则不执行创建代码。 表选项: 字符集: charset/character set 具体字符集; -- 保证表中数据存储的字符集 校对集: collate 具体校对集; 存储引擎: engine 具体的存储引擎(innodb和myisam) 当我们创建表后，会生成.frm和.idb后缀文件。.frm表结构文件，.idb是数据文件。 2.2 查看数据表 ~~~~~~~~~~~~~~ :: # 查看全部 show tables; # 查看部分（模糊查询） show like '匹配规则'; %：匹配多个字符 _：匹配单个字符（也因此，如要匹配_本身，需用\进行转义） # 查看创建语句 show create table 表名; # 查看表结构：字段信息 desc/describe 表名; show columns from 表名; 2.3 修改数据表 ~~~~~~~~~~~~~~ :: 可以修改表名和表选项，和字段 【修改表名】 rename table 老表名 to 新表名; 【修改表选项】 Alter table 表名 表选项 [=] 值; 2.4 修改字段 ~~~~~~~~~~~~ .. code:: shell 【新增】 Alter table 表名 add [column] 字段名 数据类型 [列属性] [位置]; 位置: First: 第一个位置 After: 在哪个字段之后: after 字段名; 默认的是在最后一个字段之后 【修改】 通常修改数据类型和属性、位置 Alter table 表名 modify 字段名 数据类型 [属性] [位置]; 【重命名】 这个其实也可以更改数据类型和属性和位置 Alter table 表名 change 旧字段名 新字段名 数据类型 [属性] [位置]; 【删除】 alter table 表名 drop 字段名; 2.5 删除数据表 ~~~~~~~~~~~~~~ :: 【语法】 Drop table 表名1,表名2...; -- 可以一次性删除多张表 三、数据操作 ------------ -------------- 3.1 新增数据 ~~~~~~~~~~~~ :: 【语法】 1. 按顺序插入（插入所有字段信息） Insert into 表名 values(值列表)[,(值列表)]; -- 可以一次性插入多条记录 2. 按字段插入（插入部分字段） Insert into 表名 (字段列表) values (值列表)[,(值列表)]; 3.2 查看数据 ~~~~~~~~~~~~ :: 【语法】 Select */字段列表 from 表名 [where条件]; 3.3 更新数据 ~~~~~~~~~~~~ :: 【语法】 Update 表名 set 字段 = 值 [where条件]; -- 建议都有where: 要不是更新全部 3.4 删除数据 ~~~~~~~~~~~~ :: 【语法】 Delete from 表名 [where条件]; 四、数据类型 ------------ -------------- ``数据类型``: 对数据进行统一的分类, 从系统的角度出发为了能够使用统一的方式进行管理: 能更好的利用有限的空间，除此之外也能在一定程度上防止数据插入错误。 SQL中将数据类型分成了三大类: ``数值类型``, ``字符串类型``\ 和\ ``时间日期类型`` |image1| 4.1 数值型 ~~~~~~~~~~ 4.1.1 整数型 ^^^^^^^^^^^^ 整数型有符号之分（正负） |image2| 创建表或新增字段的时候，如未指定，默认是有符号的。 | 那么如何指定呢？ | 只要在建表或新增字段的时候，指定\ ``unsigned`` | |image3| | **显示宽度** | 显示宽度，最终显示的位数。 | 比如123，是三位，-123，就是四位了。 | 零填充+显示宽度的意义: 保证数据格式 | 不足显示宽度的话，需要在前面增加前导0来满足宽度（需要设定zerofill） |image4| 4.1.2 小数型 ^^^^^^^^^^^^ 小数型: 带有小数点或者范围超出整型的数值类型. | SQL中:，将小数型细分成两种: ``浮点型``\ 和\ ``定点型`` | ``浮点型``: 小数点浮动, 精度有限,而且会丢失精度 | ``定点型``: 小数点固定, 精度固定, 不会丢失精度 **浮点型** 浮点型：超出指定范围之后, 会丢失精度(自动四舍五入) | 分为两种精度 | ``Float``: 单精度, 占用4个字节存储数据, 精度范围大概为7位左右 | ``Double``: 双精度,占用8个字节存储数据, 精度范围大概为15位左右 | |image5| 浮点的使用方式 - float表示没有小数部分 - float(M,D): M代表总长度,D代表小数部分长度, 整数部分长度为M-D 浮点型数据的插入: 整型部分是不能超出长度的,但是小数部分可以超出长度(系统会自动四舍五入) **定点型** 定点型: 绝对的保证整数部分不会被四舍五入(不会丢失精度),小数部分有可能(理论小数部分也不会丢失精度，因为如果不超过长度，自然不丢失，超过长度就插入失败) 定点型的使用方式 - decimal(M,D)：M最大65，D最大30 4.2 时间日期类型 ~~~~~~~~~~~~~~~~ | ``Datetime``: 时间日期, 格式是YYYY-mm-dd HH:ii:ss,表示的范围是从1000到9999年,有0值: 0000-00-00 00:00:00 | ``Date``: 日期,就是datetime中的date部分 | ``Time``: 时间(段), 指定的某个区间之间, -时间到+时间 | ``Timestamp``: 时间戳, 并不是时间戳,只是从1970年开始的YYYY-mm-dd HH:ii:ss格式与datetime完全一致 | ``Year``: 年份,两种形式, year(2)和year(4): 1901-2156 |image6| timestamp默认是自动更新当前时间的（在记录创建或更新时更新时间） |image7| **插入数据** - time：可以是负数,而且可以是很大的负数 - year：可以使用2位数插入（>=70的为1970-1999，<=69的为2000-2069）,也可以使用4位数 |image8| 4.3 字符串类型 ~~~~~~~~~~~~~~ 在SQL中,将字符串类型分成了6类: char,varchar,text , blob, enum和set. 4.3.1 定长字符串 ^^^^^^^^^^^^^^^^ 定长字符串: char, 磁盘(二维表)在定义结构的时候,就已经确定了最终数据的存储长度. | ``Char(L)``: L代表length, 可以存储的长度, 单位为字符, 最大长度值可以为255. | ``Char(4)``: 在UTF8 环境下,需要4 \* 3 = 12个字节 4.3.2 变长字符串 ^^^^^^^^^^^^^^^^ 变长字符串: varchar, 在分配空间的时候, 按照最大的空间分配, 但是实际上最终用了多少,是根据具体的数据来确定. ``varchar(L)``: L表示字符长度 理论长度是65536个字符,但是会多处1到2个字节来确定存储的实际长度: 但是实际上如果长度超过255,既不用定长也不用变长, 使用文本字符串text。 ``varchar(10)``: 的确存了10个汉字, utf8环境, 10 \* 3 + 1 = 31(bytes)，存储了3个汉字: 3 \* 3 + 1 = 10(bytes) | |image9| 从上图来看，如果长度比较固定，譬如身份证，手机号码等，还是选用定长，因为定长相对变长效率高。 | 如果长度是浮动的，那么就要选择变长，可以在一定长度节省空间。 4.3.3 文本字符串 ^^^^^^^^^^^^^^^^ 如果数据量非常大, 通常说超过255个字节就会使用文本字符串 | 文本字符串根据存储的数据的格式进行分类: ``text``\ 和\ ``blob`` ``Text``: 存储文字(二进制数据实际上都是存储路径) | ``Blob``: 存储二进制数据(通常不用) 4.4 枚举字符串 ~~~~~~~~~~~~~~ 枚举: enum, 事先将所有可能出现的结果都设计好, 实际上存储的数据必须是规定好的数据中的一个. | **枚举的使用方式** | ``定义``: enum(可能出现的元素列表); //如enum(‘男’,’女’,’不男不女’,’保密’); | ``使用``: 存储数据,只能存储上面定义好的数据 |image10| | 插入数据 | |image11| **作用之一** 规范数据格式: 数据只能是规定的数据中的其中一个 **作用之二** 节省存储空间：枚举实际存储的是数值而不是字符串本身. | ``证明字段存储的数据是数值``: 将数据取出来 + 0 就可以判断出原来的数据存的到底是字符串还是数值: 如果是字符串最终结果永远为0, 否则就是其他值. | |image12| 因为枚举实际存储的是数值,所以可以直接插入数值. |image13| 4.5 集合字符串 ~~~~~~~~~~~~~~ 集合跟枚举很类似: 实际存储的也是数值,而不是字符串(区别是集合是多选) | ``集合使用方式``: 定义: Set(元素列表) | 使用: 可以使用元素列表中的元素(多个), 使用逗号分隔 创建集合字段 |image14| 插入数据：可以使用多个元素字符串组合, 也可以直接插入数值 |image15| 查询结果 |image16| 为什么会很这样？ 98是什么东西？3为什么表示（篮球，足球）？ | 原来在数据库内部，set是用二进制表示的。每个元素都对应一个二进制位。 | |image17| 五、列属性 ---------- 列属性: 真正约束字段的是数据类型, 但是数据类型的约束很单一. 需要有一些额外的约束, 来更加保证数据的合法性. 列属性有很多: NULL/NOT NULL, default, Primary key, unique key, auto_increment,comment 5.1 空属性 ~~~~~~~~~~ 两个值: NULL(默认的)和NOT NULL(不为空) |image18| 在实际应用过程中，应尽量保证数据不为空，空是没有任何意义的。并且不能参与运算。很有可能会出错。 5.2 列描述 ~~~~~~~~~~ | 列描述: comment, 描述, 没有实际含义: 是专门用来描述字段,会根据表创建语句保存: 用来给程序猿(数据库管理员)来进行了解的. | |image19| 5.3 默认值 ~~~~~~~~~~ 默认值: 某一种数据会经常性的出现某个具体的值, 可以在一开始就指定好: 在需要真实数据的时候,用户可以选择性的使用默认值. 默认值关键字: default 生效：只要插入数据的时候，不给值，就会自动赋予默认值 |image20| 如果是全字段插入数据，那我们没法跳过，且又不知道默认值是什么？就可以使用default |image21| 5.4 主键 ~~~~~~~~ | 主键对应的字段中的数据不允许重复: 一旦重复,数据操作失败(增和改) | #### 5.4.1 增加主键 SQL操作中有多种方式可以给表增加主键: 大体分为三种. **方案一** 在创建表的时候,直接在字段之后,跟primary key关键字(主键本身不允许为空) 优点：简单直接 缺点：只能使用一个字段作为主键 |image22| **方案二** 在创建表的时候, 在所有的字段之后, 使用primary key(主键字段列表)来创建主键(如果有多个字段作为主键,可以是复合主键) |image23| **方案三** 当表已经创建好之后, 额外追加主键: 可以通过修改表字段属性, 也可以直接追加. Alter table 表名 add primary key(字段列表); ``前提``: 表中字段对应的数据本身是独立的(不重复) |image24| 5.4.2删除主键 ^^^^^^^^^^^^^ :: alter table 表名 drop primary key; |image25| 5.4.3 更新主键 ^^^^^^^^^^^^^^ 无法直接更新，主键必须先删除,才能增加. 5.5 自动增长 ~~~~~~~~~~~~ | 自增长: 当对应的字段,不给值,或者说给默认值,或者给NULL的时候, 会自动的被系统触发, 系统会从当前字段中已有的最大值再进行+1操作,得到一个新的在不同的字段. ​ | 自增长通常是跟主键搭配. 5.5.1 自增长的特点： ^^^^^^^^^^^^^^^^^^^^ 1. 任何一个字段要做自增长必须前提是本身是一个索引(key一栏有值，不管是主键还是唯一键) 2. 自增长字段必须是数字(整型)，字符串等其他类型无法自增长 3. 一张表最多只能有一个自增长 5.5.2 如何触发自增长 ^^^^^^^^^^^^^^^^^^^^ |image26| | 如何确定下一次是什么自增长呢? 可以通过查看表创建语句看到. | |image27| 5.5.3 修改自增长 ^^^^^^^^^^^^^^^^ 由于一张表只能有一个自增长字段，所以要改变自增长字段，需先删除再增加。 | 修改下次自增长的值。必须大于当前自增长数字的最大值，小于不生效。 | |image28| **修改起始值和步长** 查看自增长对应的变量: :: $ show variables like ‘auto_increment%’; # 修改起始值和步长 $ set auto_increment_increment = 5 $ set auto_increment_offset = 10 |image29| 5.5.4 删除自增长 ^^^^^^^^^^^^^^^^ 自增长是字段的一个属性：\ `参见文章 `__\ 。可以通过重新覆盖字段新属性来实现（注意，要是本身有主键，不用再覆盖。因为主键是必须要drop才能新增的，就算是自身的主键也需要遵循） :: alter table 表名 modify 字段 字段类型; # 不写自增长属性就行 |image30| 5.6 唯一键 ~~~~~~~~~~ 一张表往往有很多字段需要具有唯一性,数据不能重复: 但是一张表中只能有一个主键: 唯一键(unique key)就可以解决表中有多个字段需要唯一性约束的问题. 唯一键的本质与主键差不多: 唯一键默认的允许自动为空,而且可以多个为空(空字段不参与唯一性比较) 唯一键和主键的区别：　 ``主键``\ ：一张表中，只能有一个　　 ``唯一键``\ ：一张表中，可以有多个　　 5.6.1 新增/更新唯一键 ^^^^^^^^^^^^^^^^^^^^^ | 新增的方法和主键是完全一致的。也有三种方案。可参见主键。 | 由于唯一键可以有多个，所以可以直接新增，无需删除再新增。 5.6.2 删除唯一键 ^^^^^^^^^^^^^^^^ 也由于唯一键有多个，所以和主键的删除方法有所不同 :: Alter table 表名 drop unique key; -- 错误: 唯一键有多个 Alter table 表名 drop index 索引名字; -- 唯一键默认的使用字段名作为索引名字 5.7 外键 ~~~~~~~~ ``外键``: foreign key, 外面的键(键不在自己表中): 如果一张表中有一个字段(非主键)指向另外一张表的主键,那么将该字段称之为外键. 5.7.1 增加外键 ^^^^^^^^^^^^^^ 外键可以在创建表的时候或者创建表之后增加(但是要考虑数据的问题). 一张表可以有多个外键. :: 创建表的时候增加外键: 在所有的表字段之后,使用 foreign key(外键字段) references 外部表(主键字段) |image31| :: 在新增表之后增加外键: 修改表结构 Alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段); |image32| 5.7.2 更新/删除外键 ^^^^^^^^^^^^^^^^^^^ 外键不可修改: 只能先删除后新增. :: 删除外键语法 Alter table 表名 drop foreign key 外键名; -- 一张表中可以有多个外键,但是名字不能相同 5.7.3 外键的作用 ^^^^^^^^^^^^^^^^ 外键的作用，可以分为两个： - 对子表约束: 子表数据进行写操作(增和改)的时候, 如果对应的外键字段在父表找不到对应的匹配: 那么操作会失败.(约束子表数据操作) - 对父表约束：父表的主键如果已经被子表引用，那么父表对应的记录就不允许删和改。要实现删和改，必须先将子表删或改。使得父子表字段失去联系。 5.7.4 外键的条件 ^^^^^^^^^^^^^^^^ | 满足以下条件，外键才能使用/生效 | 1. 外键要存在: 首先必须保证表的存储引擎是innodb(默认的存储引擎): 如果不是innodb存储引擎,那么外键可以创建成功,但是没有约束效果. 2. 外键字段的字段类型(列类型)必须与父表的主键类型完全一致. 3. 一张表中的外键名字不能重复. 4. 增加外键的字段(数据已经存在),必须保证数据与父表主键要求对应. 5.7.5 外键的约束 ^^^^^^^^^^^^^^^^ 外键的约束，说的其实就是外键的作用。 | **外键的约束有三种模式：** | >District: 严格模式(默认的), 父表不能删除或者更新一个已经被子表数据引用的记录 >Cascade: 级联模式: 父表的操作, 对应子表关联的数据也跟着被删除 >Set null: 置空模式: 父表的操作之后,子表对应的数据(外键字段)被置空 前面讲的外键的作用是默认的采用严格模式。 通常的一个合理的做法(约束模式): 删除的时候子表置空, 更新的时候子表级联操作 指定模式的语法 :: # 在建表的时候指定 Foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade; # 在修改建表后指定，使用新增外键（必须先删除外键，再新增） alter table 表名 drop foreign key 外键名; alter table 表名 add foreign key 外键名 references 父表(主键字段) on delete set null on update cascade; |image33| 来实例操作一下，级联模式和置空模式是怎样的。 ``更新操作: 级联更新`` |image34| ``删除操作: 置空`` |image35| 5.8 索引 ~~~~~~~~ 何为\ ``索引``\ ：系统根据某种算法，将已存在的数据，单独建一个文件，使得能够快速匹配数据和查询。 **索引的意义** 1. 提升查询数据的效率 2. 约束数据的有效性(唯一性等) 索引文件，很大可能比数据文件还大，比较浪费磁盘空间。 **什么情况下使用索引** 1. 一个字段经常查询，为了提高查询效率。设为索引 2. 一个字段需要做唯一性约束。设为索引 **MySQL中索引类型** 1. 主键索引: primary key 2. 唯一索引: unique key 3. 全文索引: fulltext index 4. 普通索引: index ``全文索引``: 针对文章内部的关键字进行索引 | 全文索引最大的问题: 在于如何确定关键字 | 英文很容易: 英文单词与单词之间有空格 | 中文很难: 没有空格, 而且中文可以各种随意组合(分词: sphinx) 六、中文乱码问题 ---------------- -------------- 乱码问题的根本原因是字符集冲突。 **问题剖析：** 我们在windows的终端，采用的是ANSI编码，即GBK编码。服务器接收来自客户端的编码是UTF8，矛盾出现 SO，如果是提交的是英文的话，不会有冲突。因为都是一个字节。 但是，如果有中文的话，GBK的中文是一个汉字两个字节，而UTF8是一个汉字三个字节，如果传过去两个汉字（服务器解析：一汉字+一字节，不够会出错），如果刚好字节数够，那就会出现乱码了。 这里要介绍一个命令。 查看服务器的相关字符集。 :: show variables like 'character_set%'; |image36| 图上这个字符集编码，就不会出错，我们可以正常的插入中文数据。 如果发现\ ``character_set_client``\ 和\ ``character_set_results``\ 是utf8，那很有可能会出错。 **解决办法** :: set names gbk; 或者（二者等价） set character_set_client = gbk; set character_set_results = gbk; -------------- |image37| .. |image0| image:: http://image.iswbm.com/20200602135014.png .. |image1| image:: https://i.loli.net/2017/08/25/599feabef31a6.png .. |image2| image:: https://i.loli.net/2017/08/25/59a0345577dc0.png .. |image3| image:: https://i.loli.net/2017/08/25/59a037907f5ee.png .. |image4| image:: https://i.loli.net/2017/08/25/59a03b9be4226.png .. |image5| image:: https://i.loli.net/2017/08/25/59a03e48e42b6.png .. |image6| image:: https://i.loli.net/2017/08/25/59a040f5eb751.png .. |image7| image:: https://i.loli.net/2017/08/25/59a0418497d8f.png .. |image8| image:: https://i.loli.net/2017/08/25/59a041fe483c2.png .. |image9| image:: https://i.loli.net/2017/08/26/59a0cbe8b2790.png .. |image10| image:: https://i.loli.net/2017/08/26/59a0ce69d1c60.png .. |image11| image:: https://i.loli.net/2017/08/26/59a0cebd074ab.png .. |image12| image:: https://i.loli.net/2017/08/26/59a0cf774e2d4.png .. |image13| image:: https://i.loli.net/2017/08/26/59a0cfddc52da.png .. |image14| image:: https://i.loli.net/2017/08/26/59a0d14772b49.png .. |image15| image:: https://i.loli.net/2017/08/26/59a0d17313a77.png .. |image16| image:: https://i.loli.net/2017/08/26/59a0d19d4b54f.png .. |image17| image:: https://i.loli.net/2017/08/26/59a0d20543ba3.png .. |image18| image:: https://i.loli.net/2017/08/26/59a0d2ed1ecf2.png .. |image19| image:: https://i.loli.net/2017/08/26/59a0d35dd3a86.png .. |image20| image:: https://i.loli.net/2017/08/26/59a0d3d6b85e4.png .. |image21| image:: https://i.loli.net/2017/08/26/59a0d464bb8f1.png .. |image22| image:: https://i.loli.net/2017/08/26/59a0f8aa02375.png .. |image23| image:: https://i.loli.net/2017/08/26/59a0f9141a909.png .. |image24| image:: https://i.loli.net/2017/08/26/59a0f9be5b003.png .. |image25| image:: https://i.loli.net/2017/08/26/59a0fa9a27f35.png .. |image26| image:: https://i.loli.net/2017/08/26/59a0fc0cbb82e.png .. |image27| image:: https://i.loli.net/2017/08/26/59a0fc5759662.png .. |image28| image:: https://i.loli.net/2017/08/26/59a0fd26410ed.png .. |image29| image:: https://i.loli.net/2017/08/26/59a0fd9bc76f9.png .. |image30| image:: https://i.loli.net/2017/08/26/59a101add19bb.png .. |image31| image:: https://i.loli.net/2017/08/27/59a25b5ba7837.png .. |image32| image:: https://i.loli.net/2017/08/27/59a25be55ad8e.png .. |image33| image:: https://i.loli.net/2017/08/27/59a25ecf889b5.png .. |image34| image:: https://i.loli.net/2017/08/27/59a261734e896.png .. |image35| image:: https://i.loli.net/2017/08/27/59a261734ff8b.png .. |image36| image:: https://i.loli.net/2017/08/25/599fc9aa85094.png .. |image37| image:: http://image.iswbm.com/20200607174235.png