MySQL8.0之DDL语句（详细全面） |
您所在的位置：网站首页 › ddl的分类 › MySQL8.0之DDL语句（详细全面） |
|
数据库之DDL语句
DDL（Data Definition Language）语句： 数据定义语言，主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等。 一、MySQL字符集简介字符集规定了字符在数据库中的存储格式，比如占多少空间，支持哪些字符等等。不同的字符集有不同的编码规则，在有些情况下，甚至还有校对规则的存在。在运维和使用MySQL数据库中，选取合适的字符集非常重要，如果选择不恰当，轻则影响数据库性能，严重的可能导致数据存储乱码。 对MySQL8 以后来说 性能影响不大 为什么字符集会对数据库有些影响（了解）： mysql修改字符集 性能影响谈谈性能优化：Mysql 的字符集以及带来的一点存储影响...撕得粉·扔的博客-CSDN博客 查看全部字符集： show charset; 字符集长度说明GBK2bit支持中文，但是不是国际通用字符集UTF-83bit支持中英文混合场景，是国际通用字符集utf8mb44bit完全兼容UTF-8，用四个字节存储更多的字符，支持emoji（表情包）MySQL数据库在开发运维中，字符集选用规则如下： 1、如果系统开发面向国外业务，需要处理不同国家、不同语言，则应该选择utf-8或者utf8mb4。 2、如果只需要支持中文，没有国外业务，则为了性能考虑，可以采用GBK 创建数据库。同时指定数字集 create database 库名 default charset utf8mb4 二、排序规则说起排序规则就离不开字符集，严格来说，排序规则是依赖于字符集的 字符集是用来定义MySQL存储不同字符的方式，而排序规则一般指对字符集中字符串之间的比较、排序制定的规则。一种字符集可以对应多种排序规则，但是一种排序规则只能对应指定的一种字符集，两个不同的字符集不能有相同的排序规则。 总结： 字符集决定了能够存储哪些字符，比如 ASCII 字符集只能存储简单的英文、数字和一些控制字符；GB2312 字符集可以存储中文；Unicode 字符集能够支持世界上的各种语言。排序规则定义了字符集中字符的排序和比较顺序，包括是否区分大小写，是否区分重音等。 查看全部字符集： show collation;查看mysql默认的字符编码状况 注意：在MySQL 8.0 之后 所有的默认创建库的字符集都为 utf8mb4 ， 排序方式都是 utf8mb4_bin MySQL 5.7 不指定默认字符集与排序方式 是latin1的方式 对 latin1 字符集感兴趣的 可以去这个博客简单看看，或者自行百度吧 MySQL基础知识---------Latin1_卧浪居士的博客-CSDN博客 查看排序规则 show variables like "%character%"; # 查询内容详解 1,设置客户端使用的字符集 2,设置链接数据库时的字符集 3，设置创建数据库的编码格式 4,文件系统的编码格式 5，数据库给客户端返回数据时的编码格式 6,服务器安装时指定的编码格式 7,数据库系统使用的编码格式 8，字符集的安装目录 排序规则对于英文字符串的,大小写的敏感utf8mb4_general_ci大小写不敏感utf8mb4_bin大小写敏感(存拼音,日文)针对已有数据库修改排序规则 alter database 库名 collate utf8mb4_bin排序规则： MySQL 排序规则云满笔记的博客-CSDN博客mysql排序规则选什么 MySQL字符串类型 MySQL数据类型---字符串Cat God 007的博客-CSDN博客mysql 字符串 MySQL字符串类型用法详解charchar(100)为定长格式，写入两个字符 剩下的98个会用空格占满varcharvarchar(100)为变长格式，写入两个字符占一个空格 剩下的会压缩 超过255会多占两个空格enumenum(“男”,”女”,”保密”)字符串对象，其值通常选自一个允许值列表中，该列表在表创建时的列规格说明中被明确地列举如何选择这两个数据类型? 少于255个字符串长度,定长的列值,选择char 多于255字符长度,变长的字符串,可以选择varchar DDL的应用 创建数据库 CREATE DATABASE test1 CHARSET utf8mb4 COLLATE utf8mb4_bin;查看 # 列出所有库名 SHOW DATABASES; # 查看创建库的结构 SHOW CREATE DATABASE 库名; # 查看所有表 SHOW TABLES; # 显示创表语法和存储引擎，还有字符集 SHOW CREATE TABLE 表名; # 显示表的列结构 DESC 表名; # 创建一个表结构一样的表 CREATE TABLE 表1 LIKE 表2;删除数据库、删表 慎用 DROP DATABASE 库名; DROP TABLE 表名; 索引类型和操作方式 索引类型详解用法主键索引数据列不允许重复，不允许为NULL，一个表只能有一个主键。唯一索引数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引ALTER TABLE 表名 ADD UNIQUE (列名); 创建唯一索引 ，ALTER TABLE 表名 ADD UNIQUE (列1,列2);创建唯一组合索引普通索引基本的索引类型，没有唯一性的限制，允许为NULL值ALTER TABLE 表名 ADD INDEX 索引名 (列名);创建普通索引，ALTER TABLE 表名 ADD INDEX 索引名(列1, 列2, 列3);创建组合索引主键索引 与 唯一索引的 区别只在于 能否为空 以及能否创建多个 唯一索引与普通索引的区别在于 数值可重复 查看索引创建情况 show index from 表名;查看索引的参数说明 参数（show index from 表名;）详解Table表示创建索引的数据表名Non_unique表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。Key_name表示索引的名称Seq_in_index表示该列在索引中的位置，如果索引是单列的，则该列的值为1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。Column_name表示定义索引的列字段Collation表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类Cardinality索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大Sub_part表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为NULL。Packed指示关键字如何被压缩。若没有被压缩，值为 NULL。Null用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。Index_type显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。Comment显示评注 索引的优缺点优点： 一、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 二、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 四、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 五、通过使用索引，可以在查询的过程中，使用查询优化器，提高系统的性能。 缺点： 一、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 二、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。 三、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度 DDL 定义表DDL --- 表定义 重点 列属性详解PRIMARY KEY主键约束,表中只能有一个,非空且唯一. 主键不能重复 方便查询NOT NULL非空约束,不允许空值UNIQUE KEY唯一键约束,不允许重复值DEFAULT修饰符为字段设定一个默认值，一般配合 NOT NULL 一起使用TINYINT非常小的整数（TINYINT 最大是127，那 TINYINT UNSIGNED 最大 就可以到 127 * 2）UNSIGNED该属性只能用于设置数据类型，不允许数据列出现负数。如果不需要向某字段中插入负数，则使用该属性修饰可以使用该字段的最大存储长度增加一倍。例如，正常情况下数据类型TINYINT的数值范围在 -128 ~ 127，而使用UNSIGNED 属性修饰以后最小值为0，最大值可以达到255。COMMENT注释AUTO_INCREMENT自增长的列mysql提供了五种整型tinyint、smallint、mediumint、int和bigint： 理解mysql中的unsigned是什么意思L.Cheng的博客-CSDN博客mysql tinyint unsigned 关于日期的详细内容 MySQL8.0中的日期类数据及其函数_Sun_Sherry的博客-CSDN博客 时间类型详解DATEYYYY-MM-DD 1000-01-01 ~ 9999-12-31TIME"hh:mm:ss" 格式表示的时间值datetime范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。timestamp范围为从 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。MySQL8 创表语句的两种写法 CREATE TABLE test1( id INT NOT NULL AUTO_INCREMENT COMMENT 'ID', sno INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '学号', sName VARCHAR(20) NOT NULL COMMENT '姓名', sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别', age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄', Time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间', -- Time DATE NOT NULL DEFAULT '1000-01-01' COMMENT '入学时间', # 注意设定默认值的时候 NOW 以及其他函数不管用 PRIMARY KEY(id), INDEX(sName), UNIQUE(sno) # 写法误区 切勿在最后一行的时候 加逗号 ！ )ENGINE INNODB CHARSET utf8mb4 COLLATE utf8mb4_bin AUTO_INCREMENT=10; # 第二种写法 CREATE TABLE test2( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT 'ID', sno INT UNSIGNED NOT NULL DEFAULT 0 UNIQUE COMMENT '学号', sname VARCHAR(255) NOT NULL INDEX COMMENT '姓名', age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄', gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别', intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间' )ENGINE INNODB CHARSET utf8mb4 COLLATE utf8mb4_bin AUTO_INCREMENT=10;查看表结构 DESC test1; # 字段名称详解 1、字段名称(Field) 2、字段类型(Type) 3、字段是否为null 4、字段是否为主键(key) 5、字段的默认值(default) 6、Extra其他删除索引 drop index 索引名 on 数据表名; alter table 表名 drop index 索引名称; # 可以删除 除主键索引以外的其他索引 # 注意如果自定义的索引名称 删除索引的时候需要 写自定义的名称 不然删除会失败修改表 # 在 年龄 后面添加 微信 列 ALTER TABLE test1 ADD VX VARCHAR(20) NOT NULL COMMENT '微信号' DEFAULT '无' AFTER age; # 删除列 ALTER TABLE test1 DROP VX; # 放在第一列 ALTER TABLE test1 ADD qwe INT UNSIGNED NOT NULL UNIQUE COMMENT 'ID列' FIRST; # 注意 如果不加 AFTER 和 FIRST的话 默认是放在最后列 ALTER TABLE test1 ADD ID_card INT UNSIGNED NOT NULL UNIQUE COMMENT '身份证'; # 修改表字段 限制条件 ALTER TABLE test1 MODIFY sName VARCHAR(60) NOT NULL COMMENT '名字'; # 更改表字段名字 ALTER TABLE test1 CHANGE VX WeChat VARCHAR(33) NOT NULL COMMENT '微信'; |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |