“This is the fourth day of my participation in the August More Text Challenge. For details, see: August More Text Challenge.”

MySQL built-in functionality

Connecting to the database

-u
-p
-S
-h
-P
-e
Copy the code

Example:

# mysql -S /tmp/mysql.sock
# mysql -uroot -p -hlocalhost -P3306
Copy the code

The -e parameter command is not interactive: mysql -uroot -e “show processlist”;

# mysql -uroot -e "show processlist;"+----+------+-----------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time  | State | Info | +----+------+-----------+------+---------+------+----------+------------------+ | 10 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+-----------+------+---------+------+----------+------------------+Copy the code

< used to restore data and import external data into MySQL.

For example, we import data from the T_XZ_INVITE table into MySQL.

#mysql -uroot account < t_xz_invite.sq
Copy the code

The built-in command

  • Help — help command

    • \cClear the current input, ending the previous command (control + c)
    • \qMysql > exit (control + d)
    • \GFormat the output
    • sourceRestore data (same as <)

Introduction to SQL

  • Structured query language
  • Relational database general command
  • Compliance with SQL92 (SQL_MODE)

2 Common TYPES of SQL

  • DDL: Data definition language
  • DCL: Data control language
  • DML: Data manipulation language
  • DQL: Data query language

SQL introduction – Logical structure of database

  • library

    • The library
    • Library properties: character set, collation

  • table

    • The name of the table
    • Table properties: storage engine, character set, collation
    • Column properties: data type, constraints, other properties
    • The data line

4. Character set

This is equivalent to a password book (character set encoding) for MySQL.

We can do this by showing charset; Command to view the character set supported by MySQL.

mysql> show charset;
+----------+---------------------------------+---------------------+--------+
| 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.02 sec)
Copy the code

The default character set for MySQL5.7 is latin1, which is, of course, a legacy.

Chinese takes bytes in the following character sets:

  • GBK — 2 bytes
  • Utf8 — 3 bytes
  • Utf8mb4 — 4 bytes (recommended)

Note: UTF8MB4 supports emoji.

5. Collation

We use show collation; Command to view collation rules.

Note: Collation is case-sensitive to strings.

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 | | gb18030_unicode_520_ci | gb18030 | 250 | | Yes | 8 | +--------------------------+----------+-----+---------+----------+---------+
222 rows in set (0.00 sec)
Copy the code

Let’s take utF8MB4 as an example:

| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | | utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | | utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | | utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | | utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | | utf8mb4_spanish_ci  | utf8mb4 | 231 | | Yes | 8 | | utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | | utf8mb4_turkish_ci | utf8mb4 | 233 |  | Yes | 8 | | utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | | utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | | utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | | utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | | utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | | utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | | utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | | utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | | utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | | utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 |Copy the code

Collations ending in _ci are case insensitive, such as UTf8mb4_general_CI, followed by Yes to indicate that this is the default collation.

Therefore, when we need to be case-sensitive, we need to modify the collation accordingly.

6. Data type description

Data types are planned into four types in MySQL:

  1. Numeric types
  2. String type
  3. Time to type
  4. Binary type

6.1 Number Types

  • Integer (Mainly introduced)
  • Floating point Numbers
class type instructions
The integer tinyint Minimum integer type (0-255), signed -128-127
The integer smallint Smaller integer data types (-2^15 to 2^15-1)
The integer mediumint Medium integer data type
The integer int Integer data types of regular (average) size (-2^31 to 2^31-1)
The integer bigint Large integer data types (-2^63 to 2^63-1)
Floating point Numbers float Small single-precision (four bytes) floating point number
Floating point Numbers double Regular double (eight bytes) floating point number
Fixed-point number decimal An exact value containing an integer part, a fractional part, or both
BIT bit A field value

2^31=2147483648, so an int can store up to 10 bits.

6.2 String Types

Special attention should be paid to:

  • Char:

    • Storing fixed-length strings such as char(100), whether you store 20 or 50, will take up 100 characters.
    • At allocation time, no matter how long the string length is, the storage space of 100 characters is allocated immediately, and the empty space is filled with “Spaces”.

  • Varchar:

    • Stores variable-length strings. Before storing data, check the length and allocate disk space as required.
    • A separate space of a string length is requested to store the length of the character (less than 255, if more than 255, two storage space will be occupied).

  • Enum:

    • Enumeration data types

Char stores up to 255 characters, varchar stores up to 65535 characters (actually 65533, use two to store length)

How do you choose the two data types?

  • Less than 255 characters in length, fixed length column value, select char
  • More than 255 characters long, variable length string, can choose Varchar

6.3 Time Type

  • Datetime:

    • Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59

  • Timestamp:

    • Range: 1970-01-01 00:00:00 to 2038-12-31 23:59:59
    • You can automatically change the time according to the time zone

7. DDL application

7.1 Definition of DDL statement Library

  • Create database:
mysql> create database account charset utf8mb4 collate utf8mb4_bin;
Query OK, 1 row affected (0.00 sec)
Copy the code
  • View database (not part of DDL) :
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| account            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
Copy the code
  • View database creation statement (not part of DDL) :
mysql> show create database account;
+----------+-----------------------------------------------------------------------------------------+
| Database | Create Database                                                                         |
+----------+-----------------------------------------------------------------------------------------+
| account  | CREATE DATABASE `account` / *! 40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
+----------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Copy the code
  • Delete database:
Copy the code

  • Modify database:

    • Alter database character set (utf8 -> UTf8MB4);
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

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

mysql> alter database test charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> show create database test;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| test     | CREATE DATABASE `test` / *! 40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
Copy the code
* The same is true for changing the sort type.Copy the code

7.2 About the library definition specification

  1. Use lowercase characters for library names
  2. The library name cannot start with a number
  3. The keyword cannot be an internal database keyword
  4. The character set must be set

7.3 DDL — Table definition

  • Build table
    • The name of the table
    • The column name
    • List of attributes
      • PRIMARY KEY: a non-null and unique constraint on a table.
      • NOT NULL: a non-null constraint that does NOT allow NULL values;
      • UNIQUE KEY: a UNIQUE KEY constraint that does not allow duplicate values;
      • DEFAULT: Used together with NOT NULL. The DEFAULT value.
      • UNSIGNED: UNSIGNED, usually with a numeric column, non-negative;
      • COMMENT C.
    • Table properties
create table student (
id INT primary key NOT NULL AUTO_INCREMENT COMMENT 'student id',
sname VARCHAR(255) NOT NULL COMMENT 'name',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'age',
gender ENUM('m'.'f'.'n') NOT NULL DEFAULT 'n' COMMENT 'gender',
intime DATETIME NOT NULL DEFAULT NOW(a)COMMENT 'Time of Admission'
) ENGINE InnoDB CHARSET utf8mb4;
Copy the code

  • Table specification:

    1. The table name must be a lowercase letter and cannot start with a number.
    2. Cannot be reserved characters. Use the table name related to the business.
    3. Select the appropriate data type and length;
    4. Set NOT NULL + DEFAULT for each column, padding with zeros for numbers and valid strings for strings;
    5. Set comments for each column;
    6. The table must have a storage engine and character set;
    7. Primary key columns should be as independent as possible, preferably self-growing;
    8. For the enum type, do not save numbers. Only string types can be saved.

  • Query the created table information:

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

mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                           |
+---------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'student id'.`sname` varchar(255) NOT NULL COMMENT 'name'.`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'age'.`gender` enum('m'.'f'.'n') NOT NULL DEFAULT 'n' COMMENT 'gender'.`intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time of Admission',
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4             |
+---------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------+
1 row in set (0.00 sec)
Copy the code
  • Create an identical table:
mysql> create table test like student;
Query OK, 0 rows affected (0.04 sec)
Copy the code
  • Delete table;
mysql> drop table test; Query OK, 0 rows affected (0.02sec)Copy the code
  • View table fields:
mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field  | Type                | Null | Key | Default           | Extra          |
+--------+---------------------+------+-----+-------------------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(255) | NO | | NULL | | | age | tinyint(3) unsigned  | NO | | 0 | | | gender | enum('m','f','n') | NO | | n | | | intime | datetime | NO | | CURRENT_TIMESTAMP | | +--------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)
Copy the code
  • Add columns:
mysql> alter table student add qq varchar(20) NOT NULL COMMENT 'qq number';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field  | Type                | Null | Key | Default           | Extra          |
+--------+---------------------+------+-----+-------------------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(255) | NO | | NULL | | | age | tinyint(3) unsigned  | NO | | 0 | | | gender | enum('m','f','n') | NO | | n | | | intime | datetime | NO | | CURRENT_TIMESTAMP | | | qq | varchar(20) | NO | | NULL | | +--------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)
Copy the code
  • Add columns (after a column) :
mysql> alter table student add wechat varchar(60) NOT NULL UNIQUE COMMENT 'wechat' after sname;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field  | Type                | Null | Key | Default           | Extra          |
+--------+---------------------+------+-----+-------------------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(255) | NO | | NULL | | | wechat | varchar(60) | NO  | UNI | NULL | | | age | tinyint(3) unsigned | NO | | 0 | | | gender | enum('m','f','n') | NO | | n | | | intime | datetime | NO | | CURRENT_TIMESTAMP | | | qq | varchar(20) | NO | | NULL | | +--------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)
Copy the code
  • Add columns (add a column before a column) :
mysql> alter table student add num INT NOT NULL COMMENT 'ID card' FIRST;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field  | Type                | Null | Key | Default           | Extra          |
+--------+---------------------+------+-----+-------------------+----------------+| num | int(11) | NO | | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(255) | NO | | NULL | | | wechat | varchar(60) | NO | UNI | NULL | | | age | tinyint(3) unsigned | NO | | 0 | | | gender | enum('m','f','n') | NO | | n | | | intime | datetime | NO | | CURRENT_TIMESTAMP | | | qq | varchar(20) | NO | | NULL | |  +--------+---------------------+------+-----+-------------------+----------------+
8 rows in set (0.00 sec)
Copy the code
  • Delete the columns:
mysql> alter table student drop num;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field  | Type                | Null | Key | Default           | Extra          |
+--------+---------------------+------+-----+-------------------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(255) | NO | | NULL | | | wechat | varchar(60) | NO  | UNI | NULL | | | age | tinyint(3) unsigned | NO | | 0 | | | gender | enum('m','f','n') | NO | | n | | | intime | datetime | NO | | CURRENT_TIMESTAMP | | | qq | varchar(20) | NO | | NULL | | +--------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.01 sec)
Copy the code
  • Modify field properties:
mysql> alter table student modify sname varchar(64) NOT NULL COMMENT 'name'; Query OK, 0 rows affected (0.10sec) Records: 0 Duplicates: 0 Warnings: 0Copy the code
  • Change the field type:
mysql> alter table student change gender sex char(4) NOT NULL COMMENT 'gender';
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field  | Type                | Null | Key | Default           | Extra          |
+--------+---------------------+------+-----+-------------------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(64) | NO | | NULL | | | wechat | varchar(60) | NO | UNI | NULL | | | age | tinyint(3) unsigned | NO | | 0 | | | sex | char(4) | NO | | NULL | | | intime | datetime | NO |  | CURRENT_TIMESTAMP | | | qq | varchar(20) | NO | | NULL | | +--------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)
Copy the code

7.4 DCL

  • grant
  • revoke

7.5 DML

  • insert

Insert a line without fields
mysql> insert into student values(1.'guan yu'.20.'m'.now());
Query OK, 1 row affected (0.00 sec)

Insert a line with fields
mysql> insert into student(sname,age,sex) values ('liu bei'.21.'m'); Query OK, 1 row affected (0.01sec)Insert multiple rows
mysql> insert into student(sname,age,sex) values ('zhaoyun'.22.'m'), ('huang'.23.'m'); Query OK, 2 rows affected (0.01sec) Records: 2 Duplicates: 0 Warnings: 0 mysql>select * from student;
+----+--------+-----+-----+---------------------+
| id | sname  | age | sex | intime              |
+----+--------+-----+-----+---------------------+| 1 | guan yu | | | 20 m 2021-01-24 20:21:50 | | | | | | 21 m liu2 bei4 2021-01-24 20:23:23 | | 3 | zhaoyun | | | m 22 2021-01-24 20:24:38 | | | 4. Huang zhong | | | 23 m 2021-01-24 20:24:38 | +----+--------+-----+-----+---------------------+
4 rows in set (0.00 sec)
Copy the code
  • update
  • delete