Skip to main content

数据库操作

创建库 CREATE​

To create a new database in MySQL, you use the CREATE DATABASE statement with the following syntax:

CREATE DATABASE IF NOT EXISTS school DEFAULT CHARSET utf8mb4;

虽然 MySQL8 开始默认字符集是 uft8mb4 可以不用写，但为了向下兼容性，比如 MySQL 5.x 的默认字符集是 latin1，所以还是写上为好。

info

关于 uft8：

  • 它是 unicode 万国码的一种实现方案，几乎囊括了世界上所有语言的文字和符号
  • 它是一种变长字符，英文字母 1 个字节，汉字占 3 个字节，emoji 占 4 个字节
  • mb4 即 max bytes 4 指最大允许 4 个字节，默认的 utf8 是 mb3（未来将要弃用），所以 mb4 可以存储 emoji
SHOW CHARSET;
  • gb2312: 以前汉子字符集，但只有常用的 6000 多个汉字
  • gbk: 收入两万多个汉字，比较齐全
  • utf8mb4: 现在推荐这个

COLLATION 用来指定默认的字符排序和比较规则，一般不用改变。

SHOW COLLATION;

总共有 272 个字符集，比较多，我们来看只和 utf8mb4 有关系的字符集:

SHOW COLLATION LIKE '%utf8mb4%';
ak3coe

绝大多数业务场景下，对字符串排序比较的时候都不需要区分大小写，所以一般排序规则都是用 ci, 默认的就是比较好的选择。

wc5wU7
tip

字符类型的数据才有字符集和排序规则。

info

Since MySQL 8.0, the default collation is changed to utf8mb4_0900_ai_ci.

  • 0900 is the unicode collation algorithm version.
  • ai means accent insensitive, e.g. ê, é, è and ë are treated equally.

查看库 SHOW​

If you need to find which databases are available on your server by using the show databases statement:

SHOW DATABASES;

如果想看建库当初语句:

SHOW CREATE DATABASE school;
sY7oHJ

删除库 DROP​

The DROP DATABASE statement drops all tables in the database and deletes the database permanently. Therefore, you need to be very careful when using this statement.

The following shows the syntax of the DROP DATABASE statement:

DROP DATABASE [IF EXISTS] database_name;

In this statement, you specify the name of the database which you want to delete after the DROP DATABASE keywords.

danger

If you drop a database that does not exist, MySQL will issue an error.

odRNBX

To prevent an error from occurring if you delete a non-existing database, you can use the IF EXISTS option. In this case, MySQL will terminate the statement without issuing any error.

The DROP DATABASE statement returns the number of tables it deleted.

tip

In MySQL, the schema is the synonym for the database. Therefore, you can use them interchangeably:

DROP SCHEMA [IF EXISTS] database_name;

USE database_name​

To select a database to work with, you use the USE statement:

USE database_name;
tip

If you know which database you want to work with before you log in, you can use the -D flag. For example, the following command connects to the school database with the user account root:

mysql -u root -D school -p

In this command, we specify the database school after the -D flag.

Using TablePlus​

Select Database​

If you connect to a MySQL Server via the TablePlus application, you can select a database when you create the database connection as shown in the following screenshot:

Once logged in, you can also select another database by clicking the database icon on toolbar or using the shortcut: CMD K provided by TablePlus:

Create Database​

Drop Database​

caution

Please note that you have to switch to other database before dropping the database, otherwise there will be shown a waring dialog to stop dropping.

Reference​

  1. Select a Database by mysqltutorial
  2. Create Databases by mysqltutorial
  3. Drop Databases by mysqltutorial