MySQL database operations and data types

1. Database Operation

Notice:

• SQL statements are not case sensitive. The following will demonstrate using lowercase statements.
• Each SQL statement must be followed by a semicolon (some statements do not need to be followed by a semicolon, but it is recommended to be followed without hesitation)
• Statements in brackets [] are optional
• The library name, table name, column name, etc. cannot be the same as a keyword. If you must use a keyword as a name, you can use backticks to quote the name.

1.1 Display Database

grammar:

show databases;

Example:

Replenish:

(0.00 sec) : Indicates the time taken to execute this statement, which is 0.00 seconds

1.2 Create a database

grammar:

create database [if not exists] database name [create_specification [,create_specification]];

• if not exists: If the database does not exist in the system, create a new one; if not exists , do not create it.
• create_specification : contains two contents: character set name and collate database character set validation rules
• When no character set and validation rules are specified, the default character set used by the system is: latin1 . To better support Chinese, you can change it to utf8mb4 or GBK.

Example 1:

Example 2:

Example 3:

Replenish:

(1) In fact, the statement if not exists is very important. Because we are now inputting in a single line, if the SQL statement reports an error, the impact is not significant. We just need to re-enter the correct one. However, if you write the SQL to a file first and then execute it in batches, as long as one of the statements reports an error, the subsequent SQL statements cannot continue to execute. (2) Character set: refers to a set of multiple characters. Different character sets contain different numbers of characters, contain different characters, and encode characters in different ways. For example, GB2312 is the simplified Chinese character set of China's national standard. GB2312 includes simplified Chinese characters (6763) and general symbols, serial numbers, numbers, Latin letters, Japanese kana, Greek letters, Russian letters, Chinese phonetic symbols, and Chinese phonetic letters, a total of 7445 graphic characters. The ASCII character set only contains 128 characters. The main characters included in this character set are English letters, Arabic letters and some simple control characters. In addition, there are other commonly used character sets including GBK character set, GB18030 character set, Big5 character set, Unicode character set, etc.
(3) Character set validation rule: refers to the criteria used when comparing the size of a character set. For example, if we compare the size of a and B, if we do not consider the case, then a<B. If we consider the case, then a>B. In other words, different comparison rules for the same character set will produce different sorting results for a column of data. The character validation rules in MySQL can be viewed using show collation ; syntax, as follows:

We can see that there are a total of 195 comparison rules in the results. Each character set contains its own default validation rule. Let us briefly explain one of them: utf8_polish_ci compares based on Polish. This validation rule consists of three parts. The comparison rule name starts with the name of the character set associated with it. utf8 refers to the comparison rule of the utf8 character set, polish refers to Polish, and _ci refers to case-insensitive.
(4) The utf8 encoding in MySQL is not true utf8 because it does not include some complex Chinese characters and some symbols, such as emoji . MySQL's real utf8 character set is utf8mb4

1.3 Select the database

grammar:

use database name;

Example:

1.4 Deleting a database

grammar:

drop database [if exists] database name;

Example:

Replenish:

Deleting a database is actually a very dangerous thing. Emmm, if you delete a database when you enter a company, it is easy to get in trouble.

2. Data types in MySQL

introduce:

Defining the type of data fields in MySQL is very important for optimizing the database. MySQL supports a variety of types, which can be roughly divided into three categories: numeric, date/time, and string (character) types. MySQL supports all standard SQL numeric data types.

2.1 Numeric Types

Replenish:

• The numeric type can be specified as unsigned , which means that negative numbers are not allowed. It is not recommended to use it, and the official documentation clearly states that it will be deprecated.
• MySQL's numeric type does not have a separate character type char , but it has a string type
• In the previous article on analyzing the storage of C language data in memory, I wrote about the storage of floating-point numbers. Since the storage of floating-point numbers is actually a binary scientific notation method, it can only accurately represent values ​​such as: 0.5, 0.25, 0.125, and the rest can only represent an approximate value.
• BigDecimal in Java is used to accurately represent floating-point numbers, using variable-length memory storage, similar to strings. But it also comes at the cost of more space and time.
• Types that represent money: Although money can be represented by floating point numbers, since money is actually integers, you can directly use int type, and the unit is cents

2.2 String Type

Replenish:

• size of varchar(size) indicates the maximum length of the string in characters.
• A Chinese character is a character, but not necessarily a byte
• text has no parameters and the space occupied will be dynamically determined based on the data inserted by the user
• The first three string types are all stored text data, and blob is stored binary data.
• Binary data such as pictures, mp3 , video, world , ppt , excel , class files, etc.
• Indicates the type of image: Although images are binary data, the maximum size of a blob that can store data is 64k, so many images are not used. Therefore, you can put the image on the hard disk as a file, and then record the path of the image in the database. In this case, you can use a string type to easily handle it.

2.3 Date Type

Replenish:

timestamp means timestamp, which is a way for a computer to represent time, such as:

Note: The timestamp type will not be available in 2038, so it is recommended to use datetime when writing code.

This is the end of this article about MySQL database operations and data types. For more information about MySQL database operations and data types, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!