Database system principle and Application Tutorial （021）—— MySQL Database operation

A database is a special container for storing data objects , Include ： Data sheet 、 View 、 trigger 、 Stored procedure, etc , And the data table （table） Is the most basic data object , All the data is stored in the data table . Before creating any data objects , Create a database first , Then create all kinds of objects needed in the database .

One 、 The command to create a database

Use CREATE DATABASE Statement create database , The syntax is as follows ：

CREATE DATABASE [IF NOT EXISTS] < Database name >
[[DEFAULT] CHARACTER SET < Character set name >] [[DEFAULT] COLLATE < Proofreading rule name >];

/*  explain ： （1）[]  The content in is optional . （2）< Database name >： Name of the database to be created . The database name cannot have the same name as an existing database ,MySQL  It is not allowed to create two databases with the same name in the same system . （3）IF NOT EXISTS： The creation operation can only be performed when the database does not exist , It can avoid errors caused by the existence of the database . （4）[DEFAULT] CHARACTER SET： Specifies the default character set for the database . The character set is used to define  MySQL  How to store strings . This option can be omitted , If omitted, use the character set specified in the configuration file . （5）[DEFAULT] COLLATE： Specifies the default collation rules for the character set . Proofing rules are used to define how strings are compared , To solve the problem of sorting and character grouping . This option can be omitted , If omitted, use the proofing rules specified in the configuration file . */

1、 see MySQL Default character set and proofing rules

--  Check the system default character set 
mysql> show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

--  Check the default proofing rules 
mysql> show variables like 'COLL%'; 
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

2、 see MySQL Supported character sets and proofreading rules

Use show character set Commands can be viewed MySQL Supported character sets and proofreading rules .

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.11 sec)

3、 Create database mydb（ Do not specify character sets and proofing rules ）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database mydb;
Query OK, 1 row affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

--  Use  MySQL  Default character set , namely ：character_set_database  Character set corresponding to parameter .
mysql> show create database mydb;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

4、 Create database wgx（ Specify character sets and proofing rules ）

mysql> create database wgx character set latin1 collate latin1_general_ci;
Query OK, 1 row affected (2.18 sec)

mysql> show create database wgx;
+----------+--------------------------------------------------------------------------------+
| Database | Create Database                                                                          +----------+------------------------------------------------------------------------------+
| wgx      | CREATE DATABASE `wgx` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci */ 
+----------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
| wgx                |
+--------------------+
6 rows in set (0.00 sec)

Two 、 see MySQL database

Use show databases The command can view which databases have been created , The order is as follows ：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
| wgx                |
+--------------------+
6 rows in set (0.00 sec)

3、 ... and 、 modify the database

Use alter database The command can modify the properties of the database , It mainly modifies the character set and proofreading rules of the database , The meaning of each option in this command is the same as create database identical . The grammar is as follows ：

ALTER DATABASE [ Database name ]
[ DEFAULT ] CHARACTER SET < Character set name > |
[ DEFAULT ] COLLATE < Proofreading rule name >

hold wgx The character set of the database is modified to gbk, The proofreading rules are modified to gbk_chinese_ci.

mysql> alter database wgx default character set gbk default collate gbk_chinese_ci;
Query OK, 1 row affected (0.01 sec)

mysql> show create database wgx;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| wgx      | CREATE DATABASE `wgx` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

Four 、 Select database

Due to table 、 Objects such as views must be included in a database , Therefore, you must select the database of the object before creating and viewing it . The syntax for selecting a database is as follows ：

use  Database name ;

Select database wgx, Create tables in the database t1：

mysql> use mydb;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.29 sec)

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

5、 ... and 、 Delete database

Delete database usage drop database command , The grammar is as follows ：

drop database  Database name ;

Delete database wgx：

mysql> drop database wgx;
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)