MySQL之创建库
刘宇帅MySQL 创建库语法
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name创建一个默认的数据库
create database school;执行结果
mysql> create database school;
Query OK, 1 row affected (0.03 sec)查看数据库创建情况
mysql> show create database school;
+----------+-----------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)如上语法中创建数据库可以使用 database 也可以使用 schema，使用 schema 并和上面做下对比
mysql> create schema school;
Query OK, 1 row affected (0.00 sec)
mysql> show create database school;
+----------+-----------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)创建数据库语法中有一句if not exists用来检查数据是否存在，当不存在的时候才会创建，再次执行创建语句如下
mysql> create database school;
ERROR 1007 (HY000): Can't create database 'school'; database exists
mysql> create database if not exists school;
Query OK, 1 row affected, 1 warning (0.00 sec)当我们直接创建的话会直接报错，用 if not exists 就不会，当我们要导入库和表到库里的时候可以加上这句避免 sql 执行一半就停掉等。
字符集 和 字符序
Mysql 提供不同的字符集支持数据字符和字符编码，另外提供了字符序来进行数据比较。
举例：字符 A、a、B、b的编码分别为 0、1、2、3，当进行字符比较的时候 0 < 2所有 A < B,而当比较 A、a 的时候虽然 0 < 1 但是 A = a。
字符序 和 字符集关系
一个字符集至少对应一个字符序
两个不同的字符集不能有相同的字符序。
每个字符集都有默认的字符序。
MySQL 提供了四种级别的字符集和字符序的设置，包括 server级、database级、table级、column级，我们可以在任何级别来设置字符集和字符序。
查看 MySQL 支持的字符集
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.01 sec)其他的查询方式之 information_schema
mysql> use information_schema;
Database changed
mysql> select * from CHARACTER_SETS;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| dec8 | dec8_swedish_ci | DEC West European | 1 |
| cp850 | cp850_general_ci | DOS West European | 1 |
| hp8 | hp8_english_ci | HP West European | 1 |
| koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 |
| latin1 | latin1_swedish_ci | cp1252 West European | 1 |
......
......直接全局查询
mysql> show character set where charset='utf8';
+---------+---------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
+---------+---------------+-------------------+--------+
1 row in set (0.00 sec)
mysql> show character set where charset like 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+--------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.00 sec)查看 MySQL 支持的字符序
mysql> SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
| cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
| cp850_bin | cp850 | 80 | | Yes | 1 |
| hp8_english_ci | hp8 | 6 | Yes | Yes | 1 |
| hp8_bin | hp8 | 72 | | Yes | 1 |
| koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 |
| koi8r_bin | koi8r | 74 | | Yes | 1 |
.........
.........其他查询之 information_schema
mysql> USE information_schema;
mysql> SELECT * FROM COLLATIONS字符序命名是以字符集为前缀命名的，例如 utf8_general_ci 是 utf8 的字符序。
创建指定字符序 和 字符集的 database
mysql> create database if not exists school character set 'utf8' collate 'utf8_general_ci';
Query OK, 1 row affected (0.00 sec)
mysql> show create database school;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@character_set_database,@@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8 | utf8_general_ci |
+--------------------------+----------------------+
1 row in set (0.00 sec)删除数据库
mysql> drop database school;
Query OK, 0 rows affected (0.01 sec)库创建实践
我们在实践中建库使用 utf8mb4 编码，如下
mysql> create database if not exists school character set 'utf8mb4' collate 'utf8mb4_general_ci';
Query OK, 1 row affected (0.00 sec)
mysql> show create database school;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)刘宇帅
非著名程序员，全栈开发工程师，长期专注系统开发与架构设计。
提示
功能待开通！
相关文章
MySQL之创建表
创建表 创建学生表 SQL create table if not exists`students` ( `id` int not null auto_increment comment '学生id', `no` char(5) not null comment '学生学号', `name` varchar(128) not null default '' comment '学生姓名', `sex` tinyint not null default 0 comment '0 无 1 女 2 男', `age` tinyint null default
MySQL之删除数据
单表删除语法 DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias] [PARTITION (partition_name [, partition_name] ...)] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] 多表删除 DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_r
MySQL 技术内幕阅读笔记（0）--- InnoDB 体系结构和存储引擎
MySQL 体系结构 从图我们可以看出，MySQL 有如下几部分组成： 连接池组件 管理服务和工具组件 SQL 接口组件 查询优化组件 优化器组件 缓冲组件 插件式存储引擎 物理文件 需要注意存储引擎是基于表的而不是库，比如我们可以在建表的时候为表指明存储引擎。 MySQL 存储引擎 常见存储引擎 InnoDB MySQL 5.5.8 版本之后的默认存储引擎，事务性，行锁，支持外键。 MyISAM 存储引擎不支持事务、表锁，支持全文索引，主要面对一些 OLAP 数据库应用。MySQL 5.5.6 版本之前的默认存储引擎。 NDB 存储引擎是一个集群存储引擎。 Memory 存储引擎将表
查看 MySQL 表的大小
information_schema MySQL 自带一个数据库 information_schema ，这个数据库用于记录 MySQL 数据库的基本信息，比如数据库名，数据库的表，表栏的数据类型与访问权限等。 mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql>
高性能MySQL阅读笔记（0）--- 选择优化的数据类型
MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种数据类型，下面几个简单的原则都有助于做出更好的选择。 更小的通常更好 一般情况下，尽可能使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为他们占用的更少的磁盘、内存和CPU缓存，处理时需要的CPU周期更少。 简单最好 简单的数据类型的操作通常需要更少的CPU周期。例如整数比字符操作代价更低，因为字符集和校对规则使字符串比较比整型更复杂。 两个实际使用中的例子，一个是使用MySQL内置的类型而不是字符串存储时间，另一个是使用整型而不是字符串存储IP。 尽量避免NULL 很多表都包含可为 NULL