Shulou(Shulou.com)06/02 Report--

This article mainly introduces the operation of the MySQL database and data types, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian with you to understand.

1. Operation of database

Note:

SQL statements are not case-sensitive and will be demonstrated in lowercase below

Each SQL statement should be followed by a semicolon in English (no semicolon is required for individual statements, but it is recommended to add it all without brain).

The statement in [] brackets is optional

The library name, table name, column name, etc., cannot be the same as the keyword. If you must use the keyword as the name, you can enclose the name by backquotation marks.

1.1 display database

Syntax:

Show databases

Example:

Add:

(0.00 sec): indicates the time it took to execute this statement, that is, it took 0.00 seconds

1.2 create a database

Syntax:

Create database [if not exists] Database name [create_specification [, create_specification]]

If not exists: indicates that if the system does not have the database, it will be created; if so, it will not be created

Create_specification: verification rules that contain two content character set character set names and collate database character sets

When no character set and its verification rules are specified, the default character set used by the system is latin1. In order to better support Chinese, it can be modified to utf8mb4 or GBK.

Example 1:

Example 2:

Example 3:

Add:

(1) actually, the sentence if not exists is very important. Because we are now a single-line input, if the SQL statement reported an error, the impact is not significant, we just have to re-enter the correct one. But if you first write SQL to a file and then execute it in batches, as long as one of the statements is reported wrong, the subsequent SQL statements cannot continue to be executed.

(2) character set: a collection of multiple characters. Different character sets contain different numbers of characters, different characters, and different ways to encode characters. For example, GB2312 is the Chinese national standard simplified Chinese character set, GB2312 contains simplified Chinese characters (6763) and general symbols, serial numbers, numbers, Latin letters, Japanese katakana, Greek letters, Russian letters, Hanyu pinyin symbols, Chinese phonetic alphabet, a total of 7445 graphic characters. While the ASCII character set contains only 128 characters, the main characters in this character set are English alphabet, Arabic alphabet and some simple control characters. In addition, there are other commonly used character sets, such as GBK character set, GB18030 character set, Big5 character set, Unicode character set and so on.

(3) character set verification rules: refers to the criteria for comparing the size of the character set. For example, if we compare the size of an and B, if we do not consider case, then aB, that is, the same character set, different comparison rules, a column of data will be sorted differently. The character verification rules in MySQL can be viewed through show collation; syntax, as follows:

We can see that there are 195 comparison rules in the result, in which each character set contains its own default verification rule. Let's simply explain one: utf8_polish_ci uses Polish as the rule for comparison. The verification rule consists of three parts. The name of the comparison rule begins with the name of the character set associated with it. Utf8 refers to the comparison rule of the utf8 character set, and polish refers to Polish. _ ci refers to case insensitivity.

(4) the utf8 coding of MySQL is not utf8 in the real sense, because it fails to contain some complex Chinese characters and some symbols, such as emoji expressions. The real utf8 character set of MySQL is utf8mb4.

1.3 Select the database

Syntax:

Use database name

Example:

1.4 Delete the database

Syntax:

Drop database [if exists] database name

Example:

Add:

Deleting the database is actually a very dangerous thing, emmm. It's easy to enter the company if you delete the database.

2. Data types in MySQL

Introduction:

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

2.1 Numeric type

Add:

The numeric type can be specified as unsigned (unsigned), which means that it is not negative and is not recommended. Official documents are clearly about to be deprecated.

The numeric type of MySQL does not have a separate character type char, but has a string type

In this article, I wrote about the storage of floating-point numbers, because the storage of floating-point numbers is actually a binary scientific counting method, so it can only accurately represent values such as 0.5,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.5,0.25,0.25,0.5,0.25,0.5,0.25,0.25,0.5,0.25,0.25,0.25,0.25,0.25,0.25,0.5,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.25,0.

BigDecimal in Java is used to accurately represent floating-point numbers, using variable-length memory storage, similar to strings. But it also paid the price of more space and time.

Denote the type of money: although money can be expressed as a floating point number, because money is actually an integer, it can be directly used as an int type, in units of points.

2.2 string type

Add:

The size of varchar (size) represents the maximum length of the string in characters

A Chinese character is a character, but not necessarily a byte

Text has no parameters and will dynamically determine the space occupied based on the data inserted by the user

The first three string types are saved text type data, and blob is saved binary type data.

Binary data such as: pictures, mp3, video, world, ppt, excel, .class files, etc.

Indicates the type of picture: although the picture is binary data, because blob can store data with a maximum value of 64k, many pictures are not used, so you can put the picture on the hard disk as a file, and then record the path of the picture in the database, which can be easily done with a string type.

2.3 date Typ

Add:

Timestamp means timestamp, which is a way in which a computer represents time, such as:

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

Thank you for reading this article carefully. I hope the article "what are the operations and data types of the database in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.