2023年6月21日发(作者：)

MySQL数据库（⼆）SQL语⾔和数据类型成功不易，加倍努⼒！1 SQL语⾔1.1 关系型数据库的常见组件数据库：database表：table，⾏：row 列：column索引：index视图：view⽤户：user权限：privilege存储过程：procedure存储函数：function触发器：trigger事件调度器：event scheduler，任务计划1.2 SQL语⾔的兴起与语法标准20世纪70年代，IBM开发出SQL，⽤于DB21981年，IBM推出SQL/DS数据库业内标准微软和Sybase的T-SQL，Oracle的PL/SQLSQL作为关系型数据库所使⽤的标准语⾔，最初是基于IBM的实现在1986年被批准的。1987年，国际标准化组织(ISO) 把ANSI(美国国家标准化组织) SQL作为国际标准SQL：ANSI SQL，SQL-1986, SQL-1989, SQL-1992, SQL-1999, SQL-2003，SQL-2008, SQL-20113.2.1 SQL语⾔规范在数据库系统中，SQL语句不区分⼤⼩写，建议⽤⼤写SQL语句可单⾏或多⾏书写，以“;”结尾关键词不能跨多⾏或简写⽤空格和缩进来提⾼语句的可读性⼦句通常位于独⽴⾏，便于编辑，提⾼可读性注释：SQL标准：– 注释内容 单⾏注释，注意有空格/*注释内容*/ 多⾏注释MySQL注释：# 注释内容3.2.2 数据库对象和命名数据库的组件(对象)：数据库、表、索引、视图、⽤户、存储过程、函数、触发器、事件调度器等命名规则：必须以字母开头，可包括数字和三个特殊字符（# _ $）不要使⽤MySQL的保留字同⼀database(Schema)下的对象不能同名3.2.3 SQL语句分类DDL: Data Defination Language 数据定义语⾔CREATE，DROP，ALTERDML: Data Manipulation Language 数据操纵语⾔INSERT，DELETE，UPDATEDQL：Data Query Language 数据查询语⾔SELECTDCL：Data Control Language 数据控制语⾔GRANT，REVOKE，COMMIT，ROLLBACK软件开发：CRUD运维⼈员：查询居多3.2.4 SQL语句构成关健字Keyword组成⼦句clause，多条clause组成语句⽰例：SELECT * #SELECT⼦句FROM products #FROM⼦句WHERE price>400 #WHERE⼦句说明：⼀组SQL语句，由三个⼦句构成，SELECT,FROM和WHERE是关键字数据库操作获取SQL 命令使⽤帮助：mysql> HELP KEYWORD3.2.5 字符集和排序早期MySQL版本默认为latin1，从MySQL8.0开始默认字符集已经为 utf8mb4查看⽀持所有字符集：SHOW CHARACTER SET;范例：字符集和相关⽂件mysql> SHOW CHARACTER SET;+----------+---------------------------------+---------------------+--------+| Charset | Description | Default collation | Maxlen |+----------+---------------------------------+---------------------+--------+| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 || ascii | US ASCII | ascii_general_ci | 1 || big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 || binary | Binary pseudo charset | binary | 1 || cp1250 | Windows Central European | cp1250_general_ci | 1 || cp1251 | Windows Cyrillic | cp1251_general_ci | 1 || cp1256 | Windows Arabic | cp1256_general_ci | 1 || cp1257 | Windows Baltic | cp1257_general_ci | 1 || cp1257 | Windows Baltic | cp1257_general_ci | 1 || cp850 | DOS West European | cp850_general_ci | 1 || cp852 | DOS Central European | cp852_general_ci | 1 || cp866 | DOS Russian | cp866_general_ci | 1 || cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 || dec8 | DEC West European | dec8_swedish_ci | 1 || eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 || euckr | EUC-KR Korean | euckr_korean_ci | 2 || gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 || gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 || gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 || geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 || greek | ISO 8859-7 Greek | greek_general_ci | 1 || hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 || hp8 | HP West European | hp8_english_ci | 1 || keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 || koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 || koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 || latin1 | cp1252 West European | latin1_swedish_ci | 1 || latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 || latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 || latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 || macce | Mac Central European | macce_general_ci | 1 || macroman | Mac West European | macroman_general_ci | 1 || sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 || swe7 | 7bit Swedish | swe7_swedish_ci | 1 || tis620 | TIS620 Thai | tis620_thai_ci | 1 || ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 || ujis | EUC-JP Japanese | ujis_japanese_ci | 3 || utf16 | UTF-16 Unicode | utf16_general_ci | 4 || utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 || utf32 | UTF-32 Unicode | utf32_general_ci | 4 || utf8 | UTF-8 Unicode | utf8_general_ci | 3 || utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |+----------+---------------------------------+---------------------+--------+41 rows in set (0.00 sec)[root@centos8 ~]#ll /usr/share/mysql/charsets/total 240-rw-r--r-- 1 root root 5939 Jun 25 2019 -rw-r--r-- 1 root root 5925 Jun 25 2019 -rw-r--r-- 1 root root 8654 Jun 25 2019 -rw-r--r-- 1 root root 8783 Jun 25 2019 -rw-r--r-- 1 root root 5982 Jun 25 2019 -rw-r--r-- 1 root root 9315 Jun 25 2019 -rw-r--r-- 1 root root 5919 Jun 25 2019 -rw-r--r-- 1 root root 5941 Jun 25 2019 -rw-r--r-- 1 root root 6026 Jun 25 2019 -rw-r--r-- 1 root root 6942 Jun 25 2019 -rw-r--r-- 1 root root 5929 Jun 25 2019 -rw-r--r-- 1 root root 6141 Jun 25 2019 -rw-r--r-- 1 root root 5930 Jun 25 2019 -rw-r--r-- 1 root root 5915 Jun 25 2019 -rw-r--r-- 1 root root 19495 Jun 25 2019 -rw-r--r-- 1 root root 5942 Jun 25 2019 -rw-r--r-- 1 root root 5923 Jun 25 2019 -rw-r--r-- 1 root root 6945 Jun 25 2019 -rw-r--r-- 1 root root 10229 Jun 25 2019 -rw-r--r-- 1 root root 7651 Jun 25 2019 -rw-r--r-- 1 root root 5928 Jun 25 2019 -rw-r--r-- 1 root root 7851 Jun 25 2019 -rw-r--r-- 1 root root 8460 Jun 25 2019 -rw-r--r-- 1 root root 8471 Jun 25 2019 -rw-r--r-- 1 root root 1749 Jun 25 2019 README-rw-r--r-- 1 root root 6943 Jun 25 2019 查看当前字符集的使⽤情况MariaDB [mysql]> show variables like 'character%';+--------------------------+------------------------------+| Variable_name | Value |+--------------------------+------------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | latin1 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /usr/share/mariadb/charsets/ |+--------------------------+------------------------------+8 rows in set (0.001 sec)查看⽀持所有排序规则：SHOW COLLATION;查看当前使⽤的排序规则SHOW VARIABLES LIKE 'collation%';设置服务器默认的字符集vim /etc/[mysqld]character-set-server=utf8mb4设置mysql客户端默认的字符集vim /etc/[mysql]default-character-set=utf8mb4范例：Mariadb10.3 默认的字符集和排序规则MariaDB [mysql]> SELECT VERSION();+-----------------+| VERSION() |+-----------------+| 10.3.17-MariaDB |+-----------------+1 row in set (0.000 sec)MariaDB [mysql]> show variables like 'character%';+--------------------------+------------------------------+| Variable_name | Value |+--------------------------+------------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | latin1 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /usr/share/mariadb/charsets/ |+--------------------------+------------------------------+8 rows in set (0.001 sec)MariaDB [mysql]> SHOW VARIABLES LIKE 'collation%';+----------------------+-------------------+| Variable_name | Value |+----------------------+-------------------+| collation_connection | utf8_general_ci || collation_database | latin1_swedish_ci || collation_server | latin1_swedish_ci |+----------------------+-------------------+3 rows in set (0.001 sec)范例：MySQL 8.0 默认的字符集和排序规则mysql> SELECT VERSION();+-----------+| VERSION() |+-----------+| 8.0.17 |+-----------+1 row in set (0.00 sec)mysql> show variables like 'character%';+--------------------------+----------------------------+| Variable_name | Value |+--------------------------+----------------------------+| character_set_client | utf8mb4 || character_set_connection | utf8mb4 || character_set_database | utf8mb4 || character_set_filesystem | binary || character_set_results | utf8mb4 || character_set_server | utf8mb4 || character_set_system | utf8 || character_sets_dir | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)mysql> SHOW VARIABLES LIKE 'collation%';+----------------------+--------------------+| Variable_name | Value |+----------------------+--------------------+| collation_connection | utf8mb4_0900_ai_ci || collation_database | utf8mb4_0900_ai_ci || collation_server | utf8mb4_0900_ai_ci |+----------------------+--------------------+3 rows in set (0.01 sec)2 管理数据库2.1 创建数据库CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'CHARACTER SET 'character set name'COLLATE 'collate name';范例：MariaDB [mysql]> create database db1;Query OK, 1 row affected (0.000 sec)MariaDB [mysql]> show create database db1;+----------+----------------------------------------------------------------+| Database | Create Database |+----------+----------------------------------------------------------------+| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+----------------------------------------------------------------+1 row in set (0.000 sec)[root@centos8 ~]#cat /var/lib/mysql/db1/ault-character-set=latin1default-collation=latin1_swedish_ciMariaDB [(none)]> create database db1;ERROR 1007 (HY000): Can't create database 'db1'; database existsMariaDB [(none)]> create database IF NOT EXISTS db1;Query OK, 0 rows affected, 1 warning (0.000 sec)MariaDB [(none)]> show warnings;+-------+------+----------------------------------------------+| Level | Code | Message |+-------+------+----------------------------------------------+| Note | 1007 | Can't create database 'db1'; database exists |+-------+------+----------------------------------------------+1 row in set (0.000 sec)范例：指定字符集创建新数据库MariaDB [(none)]> create database IF NOT EXISTS db2 CHARACTER SET 'utf8';Query OK, 1 row affected (0.000 sec)MariaDB [(none)]> SHOW CREATE DATABASE db2;+----------+--------------------------------------------------------------+| Database | Create Database |+----------+--------------------------------------------------------------+| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+--------------------------------------------------------------+1 row in set (0.000 sec)MariaDB [(none)]>

[root@centos8 ~]#cat /mysql/3306/data/db2/ault-character-set=utf8default-collation=utf8_general_ci2.2 修改数据库ALTER DATABASE DB_NAME character set utf8;范例：MariaDB [(none)]> ALTER DATABASE db1 character set utf8;Query OK, 1 row affected (0.001 sec)MariaDB [(none)]> show create database db1;+----------+--------------------------------------------------------------+| Database | Create Database |+----------+--------------------------------------------------------------+| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+--------------------------------------------------------------+1 row in set (0.000 sec)[root@centos8 ~]#cat /var/lib/mysql/db1/ault-character-set=utf8default-collation=utf8_general_ci2.3 删除数据库DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';MariaDB [(none)]> drop database db1;Query OK, 0 rows affected (0.002 sec)MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema |+--------------------+3 rows in set (0.000 sec)MariaDB [(none)]>[root@centos8 ~]#ls /var/lib/mysql/aria_log.00000001 ib_buffer_pool ib_logfile0 ibtmp1 mysqlmysql_upgrade_info a_log_control ibdata1 ib_logfile1 rformance_schema2.4 查看数据库列表SHOW DATABASES;范例：MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema |+--------------------+3 rows in set (0.000 sec)3 数据类型数据类型：数据长什么样数据需要多少空间来存放数据类型系统内置数据类型⽤户定义数据类型MySQL⽀持多种内置数据类型数值类型⽇期/时间类型字符串(字符)类型1. 更⼩的通常更好，尽量使⽤可正确存储数据的最⼩数据类型2. 简单就好，简单数据类型的操作通常需要更少的CPU周期3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化3.1 整数型tinyint(m) 1个字节 范围(-128~127)smallint(m) 2个字节 范围(-32768~32767)mediumint(m) 3个字节 范围(-8388608~8388607)int(m) 4个字节 范围(-2147483648~2147483647)bigint(m) 8个字节 范围(+-9.22*10的18次⽅)上述数据类型，如果加修饰符unsigned后，则最⼤值翻倍（默认第⼀位都是符号位，导致范围正负都有）如：tinyint unsigned的取值范围为(0~255)int(m)⾥的m是表⽰SELECT查询结果集中的显⽰宽度，并不影响实际的取值范围，规定了MySQL的⼀些交互⼯具（例如MySQL命令⾏客户端）⽤来显⽰字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，⾮zero值视为真3.2 浮点型(float和double)，近似值float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d⼩数位double(m,d) 双精度浮点型16位精度(8字节) m总个数，d⼩数位设⼀个字段定义为float(6,3)，如果插⼊⼀个数123.45678,实际数据库⾥存的是123.457，但总个数还以实际为准，即6位3.3 定点数在数据库中存放的是精确值,存为⼗进制decimal(m,d) 参数m<65 是总个数，d<30且 d255)，所以varchar(4),存⼊3个字符将占⽤4个字节类型的字符串检索速度要⽐varchar类型的快varchar和text：r可指定n，text不能指定，内部存储varchar是存⼊的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。类型不能有默认值r可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text数据类型3.5 ⼆进制数据BLOBBLOB和text存储⽅式不同，TEXT以⽂本⽅式存储，英⽂存储区分⼤⼩写，⽽Blob以⼆进制⽅式存储，不分⼤⼩写BLOB存储的数据只能整体读出TEXT可以指定字符集，BLOB不⽤指定字符集3.6 ⽇期时间类型date ⽇期 ‘2008-12-2’time 时间 ‘12:25:36’datetime ⽇期时间 ‘2008-12-2 22:06:44’timestamp ⾃动存储记录修改时间YEAR(2), YEAR(4)：年份timestamp字段⾥的时间数据会随其他字段修改的时候⾃动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间3.7 修饰符适⽤所有类型的修饰符：NULL 数据列可包含NULL值，默认值NOT NULL 数据列不允许包含NULL值，*为必填选项DEFAULT 默认值PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为NULLUNIQUE KEY 唯⼀键，所有记录中此字段的值不能重复，但可以为NULLCHARACTER SET name 指定⼀个字符集适⽤数值型的修饰符：AUTO_INCREMENT ⾃动递增，适⽤于整数类型UNSIGNED ⽆符号范例：关于AUTO_INCREMENTMariaDB [hellodb]> SHOW VARIABLES LIKE 'auto_inc%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 || auto_increment_offset | 1 |+--------------------------+-------+2 rows in set (0.001 sec)# auto_increment_offset

定义初始值# auto_increment_increment

定义步进范例：mysql> create database test;Query OK, 1 row affected (0.00 sec)mysql> use testDatabase changedmysql> create table t1(id int unsigned auto_increment primary key)auto_increment = 4294967294;Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values(null);Query OK, 1 row affected (0.01 sec)mysql> select * from t1;+------------+| id |+------------+| 4294967294 |+------------+1 row in set (0.00 sec)mysql> insert into t1 values(null);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+------------+| id |+------------+| 4294967294 || 4294967295 |+------------+2 rows in set (0.00 sec)mysql> insert into t1 values(null);ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'MariaDB [testdb]> insert t1 value(null);ERROR 167 (22003): Out of range value for column 'id' at row 1

2023年6月21日发(作者：)

MySQL数据库（⼆）SQL语⾔和数据类型成功不易，加倍努⼒！1 SQL语⾔1.1 关系型数据库的常见组件数据库：database表：table，⾏：row 列：column索引：index视图：view⽤户：user权限：privilege存储过程：procedure存储函数：function触发器：trigger事件调度器：event scheduler，任务计划1.2 SQL语⾔的兴起与语法标准20世纪70年代，IBM开发出SQL，⽤于DB21981年，IBM推出SQL/DS数据库业内标准微软和Sybase的T-SQL，Oracle的PL/SQLSQL作为关系型数据库所使⽤的标准语⾔，最初是基于IBM的实现在1986年被批准的。1987年，国际标准化组织(ISO) 把ANSI(美国国家标准化组织) SQL作为国际标准SQL：ANSI SQL，SQL-1986, SQL-1989, SQL-1992, SQL-1999, SQL-2003，SQL-2008, SQL-20113.2.1 SQL语⾔规范在数据库系统中，SQL语句不区分⼤⼩写，建议⽤⼤写SQL语句可单⾏或多⾏书写，以“;”结尾关键词不能跨多⾏或简写⽤空格和缩进来提⾼语句的可读性⼦句通常位于独⽴⾏，便于编辑，提⾼可读性注释：SQL标准：– 注释内容 单⾏注释，注意有空格/*注释内容*/ 多⾏注释MySQL注释：# 注释内容3.2.2 数据库对象和命名数据库的组件(对象)：数据库、表、索引、视图、⽤户、存储过程、函数、触发器、事件调度器等命名规则：必须以字母开头，可包括数字和三个特殊字符（# _ $）不要使⽤MySQL的保留字同⼀database(Schema)下的对象不能同名3.2.3 SQL语句分类DDL: Data Defination Language 数据定义语⾔CREATE，DROP，ALTERDML: Data Manipulation Language 数据操纵语⾔INSERT，DELETE，UPDATEDQL：Data Query Language 数据查询语⾔SELECTDCL：Data Control Language 数据控制语⾔GRANT，REVOKE，COMMIT，ROLLBACK软件开发：CRUD运维⼈员：查询居多3.2.4 SQL语句构成关健字Keyword组成⼦句clause，多条clause组成语句⽰例：SELECT * #SELECT⼦句FROM products #FROM⼦句WHERE price>400 #WHERE⼦句说明：⼀组SQL语句，由三个⼦句构成，SELECT,FROM和WHERE是关键字数据库操作获取SQL 命令使⽤帮助：mysql> HELP KEYWORD3.2.5 字符集和排序早期MySQL版本默认为latin1，从MySQL8.0开始默认字符集已经为 utf8mb4查看⽀持所有字符集：SHOW CHARACTER SET;范例：字符集和相关⽂件mysql> SHOW CHARACTER SET;+----------+---------------------------------+---------------------+--------+| Charset | Description | Default collation | Maxlen |+----------+---------------------------------+---------------------+--------+| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 || ascii | US ASCII | ascii_general_ci | 1 || big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 || binary | Binary pseudo charset | binary | 1 || cp1250 | Windows Central European | cp1250_general_ci | 1 || cp1251 | Windows Cyrillic | cp1251_general_ci | 1 || cp1256 | Windows Arabic | cp1256_general_ci | 1 || cp1257 | Windows Baltic | cp1257_general_ci | 1 || cp1257 | Windows Baltic | cp1257_general_ci | 1 || cp850 | DOS West European | cp850_general_ci | 1 || cp852 | DOS Central European | cp852_general_ci | 1 || cp866 | DOS Russian | cp866_general_ci | 1 || cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 || dec8 | DEC West European | dec8_swedish_ci | 1 || eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 || euckr | EUC-KR Korean | euckr_korean_ci | 2 || gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 || gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 || gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 || geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 || greek | ISO 8859-7 Greek | greek_general_ci | 1 || hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 || hp8 | HP West European | hp8_english_ci | 1 || keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 || koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 || koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 || latin1 | cp1252 West European | latin1_swedish_ci | 1 || latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 || latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 || latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 || macce | Mac Central European | macce_general_ci | 1 || macroman | Mac West European | macroman_general_ci | 1 || sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 || swe7 | 7bit Swedish | swe7_swedish_ci | 1 || tis620 | TIS620 Thai | tis620_thai_ci | 1 || ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 || ujis | EUC-JP Japanese | ujis_japanese_ci | 3 || utf16 | UTF-16 Unicode | utf16_general_ci | 4 || utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 || utf32 | UTF-32 Unicode | utf32_general_ci | 4 || utf8 | UTF-8 Unicode | utf8_general_ci | 3 || utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |+----------+---------------------------------+---------------------+--------+41 rows in set (0.00 sec)[root@centos8 ~]#ll /usr/share/mysql/charsets/total 240-rw-r--r-- 1 root root 5939 Jun 25 2019 -rw-r--r-- 1 root root 5925 Jun 25 2019 -rw-r--r-- 1 root root 8654 Jun 25 2019 -rw-r--r-- 1 root root 8783 Jun 25 2019 -rw-r--r-- 1 root root 5982 Jun 25 2019 -rw-r--r-- 1 root root 9315 Jun 25 2019 -rw-r--r-- 1 root root 5919 Jun 25 2019 -rw-r--r-- 1 root root 5941 Jun 25 2019 -rw-r--r-- 1 root root 6026 Jun 25 2019 -rw-r--r-- 1 root root 6942 Jun 25 2019 -rw-r--r-- 1 root root 5929 Jun 25 2019 -rw-r--r-- 1 root root 6141 Jun 25 2019 -rw-r--r-- 1 root root 5930 Jun 25 2019 -rw-r--r-- 1 root root 5915 Jun 25 2019 -rw-r--r-- 1 root root 19495 Jun 25 2019 -rw-r--r-- 1 root root 5942 Jun 25 2019 -rw-r--r-- 1 root root 5923 Jun 25 2019 -rw-r--r-- 1 root root 6945 Jun 25 2019 -rw-r--r-- 1 root root 10229 Jun 25 2019 -rw-r--r-- 1 root root 7651 Jun 25 2019 -rw-r--r-- 1 root root 5928 Jun 25 2019 -rw-r--r-- 1 root root 7851 Jun 25 2019 -rw-r--r-- 1 root root 8460 Jun 25 2019 -rw-r--r-- 1 root root 8471 Jun 25 2019 -rw-r--r-- 1 root root 1749 Jun 25 2019 README-rw-r--r-- 1 root root 6943 Jun 25 2019 查看当前字符集的使⽤情况MariaDB [mysql]> show variables like 'character%';+--------------------------+------------------------------+| Variable_name | Value |+--------------------------+------------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | latin1 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /usr/share/mariadb/charsets/ |+--------------------------+------------------------------+8 rows in set (0.001 sec)查看⽀持所有排序规则：SHOW COLLATION;查看当前使⽤的排序规则SHOW VARIABLES LIKE 'collation%';设置服务器默认的字符集vim /etc/[mysqld]character-set-server=utf8mb4设置mysql客户端默认的字符集vim /etc/[mysql]default-character-set=utf8mb4范例：Mariadb10.3 默认的字符集和排序规则MariaDB [mysql]> SELECT VERSION();+-----------------+| VERSION() |+-----------------+| 10.3.17-MariaDB |+-----------------+1 row in set (0.000 sec)MariaDB [mysql]> show variables like 'character%';+--------------------------+------------------------------+| Variable_name | Value |+--------------------------+------------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | latin1 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /usr/share/mariadb/charsets/ |+--------------------------+------------------------------+8 rows in set (0.001 sec)MariaDB [mysql]> SHOW VARIABLES LIKE 'collation%';+----------------------+-------------------+| Variable_name | Value |+----------------------+-------------------+| collation_connection | utf8_general_ci || collation_database | latin1_swedish_ci || collation_server | latin1_swedish_ci |+----------------------+-------------------+3 rows in set (0.001 sec)范例：MySQL 8.0 默认的字符集和排序规则mysql> SELECT VERSION();+-----------+| VERSION() |+-----------+| 8.0.17 |+-----------+1 row in set (0.00 sec)mysql> show variables like 'character%';+--------------------------+----------------------------+| Variable_name | Value |+--------------------------+----------------------------+| character_set_client | utf8mb4 || character_set_connection | utf8mb4 || character_set_database | utf8mb4 || character_set_filesystem | binary || character_set_results | utf8mb4 || character_set_server | utf8mb4 || character_set_system | utf8 || character_sets_dir | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)mysql> SHOW VARIABLES LIKE 'collation%';+----------------------+--------------------+| Variable_name | Value |+----------------------+--------------------+| collation_connection | utf8mb4_0900_ai_ci || collation_database | utf8mb4_0900_ai_ci || collation_server | utf8mb4_0900_ai_ci |+----------------------+--------------------+3 rows in set (0.01 sec)2 管理数据库2.1 创建数据库CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'CHARACTER SET 'character set name'COLLATE 'collate name';范例：MariaDB [mysql]> create database db1;Query OK, 1 row affected (0.000 sec)MariaDB [mysql]> show create database db1;+----------+----------------------------------------------------------------+| Database | Create Database |+----------+----------------------------------------------------------------+| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+----------------------------------------------------------------+1 row in set (0.000 sec)[root@centos8 ~]#cat /var/lib/mysql/db1/ault-character-set=latin1default-collation=latin1_swedish_ciMariaDB [(none)]> create database db1;ERROR 1007 (HY000): Can't create database 'db1'; database existsMariaDB [(none)]> create database IF NOT EXISTS db1;Query OK, 0 rows affected, 1 warning (0.000 sec)MariaDB [(none)]> show warnings;+-------+------+----------------------------------------------+| Level | Code | Message |+-------+------+----------------------------------------------+| Note | 1007 | Can't create database 'db1'; database exists |+-------+------+----------------------------------------------+1 row in set (0.000 sec)范例：指定字符集创建新数据库MariaDB [(none)]> create database IF NOT EXISTS db2 CHARACTER SET 'utf8';Query OK, 1 row affected (0.000 sec)MariaDB [(none)]> SHOW CREATE DATABASE db2;+----------+--------------------------------------------------------------+| Database | Create Database |+----------+--------------------------------------------------------------+| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+--------------------------------------------------------------+1 row in set (0.000 sec)MariaDB [(none)]>

[root@centos8 ~]#cat /mysql/3306/data/db2/ault-character-set=utf8default-collation=utf8_general_ci2.2 修改数据库ALTER DATABASE DB_NAME character set utf8;范例：MariaDB [(none)]> ALTER DATABASE db1 character set utf8;Query OK, 1 row affected (0.001 sec)MariaDB [(none)]> show create database db1;+----------+--------------------------------------------------------------+| Database | Create Database |+----------+--------------------------------------------------------------+| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+--------------------------------------------------------------+1 row in set (0.000 sec)[root@centos8 ~]#cat /var/lib/mysql/db1/ault-character-set=utf8default-collation=utf8_general_ci2.3 删除数据库DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';MariaDB [(none)]> drop database db1;Query OK, 0 rows affected (0.002 sec)MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema |+--------------------+3 rows in set (0.000 sec)MariaDB [(none)]>[root@centos8 ~]#ls /var/lib/mysql/aria_log.00000001 ib_buffer_pool ib_logfile0 ibtmp1 mysqlmysql_upgrade_info a_log_control ibdata1 ib_logfile1 rformance_schema2.4 查看数据库列表SHOW DATABASES;范例：MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema |+--------------------+3 rows in set (0.000 sec)3 数据类型数据类型：数据长什么样数据需要多少空间来存放数据类型系统内置数据类型⽤户定义数据类型MySQL⽀持多种内置数据类型数值类型⽇期/时间类型字符串(字符)类型1. 更⼩的通常更好，尽量使⽤可正确存储数据的最⼩数据类型2. 简单就好，简单数据类型的操作通常需要更少的CPU周期3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化3.1 整数型tinyint(m) 1个字节 范围(-128~127)smallint(m) 2个字节 范围(-32768~32767)mediumint(m) 3个字节 范围(-8388608~8388607)int(m) 4个字节 范围(-2147483648~2147483647)bigint(m) 8个字节 范围(+-9.22*10的18次⽅)上述数据类型，如果加修饰符unsigned后，则最⼤值翻倍（默认第⼀位都是符号位，导致范围正负都有）如：tinyint unsigned的取值范围为(0~255)int(m)⾥的m是表⽰SELECT查询结果集中的显⽰宽度，并不影响实际的取值范围，规定了MySQL的⼀些交互⼯具（例如MySQL命令⾏客户端）⽤来显⽰字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，⾮zero值视为真3.2 浮点型(float和double)，近似值float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d⼩数位double(m,d) 双精度浮点型16位精度(8字节) m总个数，d⼩数位设⼀个字段定义为float(6,3)，如果插⼊⼀个数123.45678,实际数据库⾥存的是123.457，但总个数还以实际为准，即6位3.3 定点数在数据库中存放的是精确值,存为⼗进制decimal(m,d) 参数m<65 是总个数，d<30且 d255)，所以varchar(4),存⼊3个字符将占⽤4个字节类型的字符串检索速度要⽐varchar类型的快varchar和text：r可指定n，text不能指定，内部存储varchar是存⼊的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。类型不能有默认值r可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text数据类型3.5 ⼆进制数据BLOBBLOB和text存储⽅式不同，TEXT以⽂本⽅式存储，英⽂存储区分⼤⼩写，⽽Blob以⼆进制⽅式存储，不分⼤⼩写BLOB存储的数据只能整体读出TEXT可以指定字符集，BLOB不⽤指定字符集3.6 ⽇期时间类型date ⽇期 ‘2008-12-2’time 时间 ‘12:25:36’datetime ⽇期时间 ‘2008-12-2 22:06:44’timestamp ⾃动存储记录修改时间YEAR(2), YEAR(4)：年份timestamp字段⾥的时间数据会随其他字段修改的时候⾃动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间3.7 修饰符适⽤所有类型的修饰符：NULL 数据列可包含NULL值，默认值NOT NULL 数据列不允许包含NULL值，*为必填选项DEFAULT 默认值PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为NULLUNIQUE KEY 唯⼀键，所有记录中此字段的值不能重复，但可以为NULLCHARACTER SET name 指定⼀个字符集适⽤数值型的修饰符：AUTO_INCREMENT ⾃动递增，适⽤于整数类型UNSIGNED ⽆符号范例：关于AUTO_INCREMENTMariaDB [hellodb]> SHOW VARIABLES LIKE 'auto_inc%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 || auto_increment_offset | 1 |+--------------------------+-------+2 rows in set (0.001 sec)# auto_increment_offset

定义初始值# auto_increment_increment

定义步进范例：mysql> create database test;Query OK, 1 row affected (0.00 sec)mysql> use testDatabase changedmysql> create table t1(id int unsigned auto_increment primary key)auto_increment = 4294967294;Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values(null);Query OK, 1 row affected (0.01 sec)mysql> select * from t1;+------------+| id |+------------+| 4294967294 |+------------+1 row in set (0.00 sec)mysql> insert into t1 values(null);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+------------+| id |+------------+| 4294967294 || 4294967295 |+------------+2 rows in set (0.00 sec)mysql> insert into t1 values(null);ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'MariaDB [testdb]> insert t1 value(null);ERROR 167 (22003): Out of range value for column 'id' at row 1