138 diagrams to get you started with MySQL

SQL Basics

MySQL is a relational database. When it comes to relationships, it can’t be separated from the relationship between tables. In fact, the most effective way to reflect this relationship is the main character SQL which we need to introduce next. It is a language designed for table association, that is, to learn MySQL, SQL is the basic and most important. SQL is not just a language specific to MySQL; it is supported by most relational databases.

Here’s a look at this very important language.

Query Language Classification

There are several concepts we need to know before we can understand SQL

• Data Definition language: short for data Definition LanguageDDL(Data Definition Language), used to define database objects: database, table, column, etc.

• Data manipulation language: short for data manipulation languageDMLData Manipulation Language (Data Manipulation Language) is used to update the records of tables in the database. Keywords: INSERT, UPDATE, delete, etc
• Data control language: short for data control languageDCL(Data Control Language), used to define database access permissions and security levels, create users, and so on. Key words: Grant, etc
• Data query language: short for data query LanguageDQL(Data Query Language), used to Query records in the database table, keyword: SELECT from WHERE, etc

DDL statements

Creating a database

To begin our journey of learning SQL statements, first you need to start MySQL services, I am on a MAC, so I can start directly

Then we use the command line to connect to the database, open iterm, and type the following

MacBook:~ mr.l$ mysql -uroot -p
Copy the code

You can connect to the database

In the preceding command, mysql represents the client command, -u indicates the user to be connected, and -p indicates the password of the user to be entered. After you enter your username and password, if you successfully log in, a welcome screen (as shown above) and the mysql> prompt will be displayed.

The welcome screen basically describes these things

• The end of each line, which I use here;or\gTo indicate the end of each line
• Your MySQL connection ID is 4. This is a record of the number of connections in MySQL service so far. Each new connection is automatically increased by 1
• The following is the version of MySQL. We are using 5.7
• throughhelpor\hCommand to display help content\cCommand to clear the command line buffer.

What then needs to be done? Ultimately we want to learn about SQL statements, and SQL statements are definitely about querying data, associating tables with data, so we need data, so where is the data? The location where the data is stored is called a table, and the location where the table is stored is called a database, so we need to create the database first, then create the table, then insert the data, and then query.

So the first thing we’re going to do is we’re going to create a database, and we’re going to create a database directly using instructions

CREATE DATABASE dbname;
Copy the code

For example, we create the cxuandb database

create database cxuandb;
Copy the code

Note the last; Do not throw out the end syntax, otherwise MySQL will think that your command is not finished, and after you type Enter, the output will be straight to a newline

Query OK, 1 row affected Query OK, 1 row affected Query OK indicates Query completion. Why is this displayed? Since all DDL and DML operations are prompted for this when they complete, it can also be interpreted as a success. **1 row affected ** indicates the number of rows affected, and () shows how long it took you to execute the command, which is 0.03 seconds.

We have successfully created a cxuandb database. Now we want to create another database. We execute the same command again

We can’t create the database anymore. The database already exists. Now I have a question, how do I know which databases are available? Instead of creating a database and telling me it already exists, use the show databases command to view your MySQL database

show databases;
Copy the code

The command output is as follows

Since I have used the database before, I need to explain here that besides the newly created Cxuandb, InformationN_Schema, PerformannCE_SCHEMA and SYS are all built-in databases of the system. Is the default database created when installing MySQL. Each of them represents

• Informationn_schema: Stores some database object information, such as user table information, permission information, and partition information
• Performannce_schema: a new database added after MySQL 5.5. It is mainly used to collect performance parameters of the database server.
• Sys: the database provided by MySQL 5.7. The SYS database contains a series of stored procedures, custom functions, and views to help us quickly understand the system metadata information.

All other databases are author created and can be ignored.

After the database is created, you can use the following command to select the database to operate on

use cxuandb
Copy the code

In this way, we can successfully switch to cXUandB database. We can build tables and view basic information under this database.

Let’s say we want to see if there are other tables in our new database

show tables;
Copy the code

Sure enough, our new database doesn’t have any tables under it, but for now, we’re not going to create a table, so let’s look at the database level commands, which are the other DDL commands

Deleting a database

What if we don’t want a database? Why don’t we just delete the database? The deleted table sentence is

drop database dbname;
Copy the code

For example, cxuandb, we don’t want it, we can use it

drop database cxuandb;
Copy the code

I’m going to delete it, and I’m not going to do a demonstration here, because Cxuandb is something that we’re going to use later.

If you can delete the database successfully, it will be affected by 0 rows. Otherwise, it will be affected by 0 rows.

Create a table

Now we are ready to operate on the table. We just showed tables and found that there are no tables yet, so let’s build the table sentence

CREATE TABLE TABLE name (column name 1 data type constraint, column name 2 data type constraint, column name 3 data type constraint,....)Copy the code

So it’s clear that the column name is the name of the column, followed by the column name is the data type, and then the constraint. Why design this? For example, cxuan’s label was printed right after he was born

For example, let’s create a table with five fields: name, sex, age, hiredate, and wage

Create table job(name varchar(20), sex varchar(2), age int(2), hiredate date, wage decimal(10,2));Copy the code

After creating a table, you can use DESC tablename to view basic table information

The DESC command looks at the table definition, but the output is not comprehensive enough, so if you want to see more information, you have to look at the SQL for the table creation statement

show create table job \G;
Copy the code

As you can see, in addition to the table definition, you can also see that the engine of the table is InnoDB storage engine. \G makes it possible to arrange the records in a vertical order. If \G is not used, the effect is as follows

Delete table

There are two types of table drop statements. One is the DROP statement, and the SQL statement is as follows

drop table job
Copy the code

The truncate statement is the following SQL statement

truncate table job
Copy the code

The difference between the two statements is that after a table is dropped by the DROP statement, the table can be replied through the log. After a table is dropped by the TRUNCate statement, the table can never be restored. Therefore, the TRUNCate statement is not used to delete a table. ‘

Modify the table

To have created a good table, especially a large number of data table, if need to make changes in the structure, the table can delete and recreate the table, the table will produce some of the extra work, but the efficiency will reload data recently, if you have any service is visiting at this time, may also affect the service reads the data in the table, so this time, We need the table change statement to modify the definition of the table that has been created.

The alter TABLE statement is used to modify the table structure. The following commands are commonly used

ALTER TABLE tb MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];
Copy the code

For example, if we want to change the name in the job table from varchar(20) to varchar(25), we can use the following statement

alter table job modify name varchar(25);
Copy the code

You can also make changes to the table structure, such as adding a field

alter table job add home varchar(30);
Copy the code

Delete the fields of the newly added table

alter table job drop column home;
Copy the code

You can change the names of the fields in the table, such as wage to SALARY

Alter table job change wage salary decimal(10,2);Copy the code

Modify the fields of order, we introduced changes in front of the grammar order problem involves a, has an option first | * * after * * column_name, this option can be used to modify the position of the fields in the table, the default ADD is added as the last field in the table, CHANGE/MODIFY does not CHANGE the field position. Such as

alter table job add birthday after hiredate;
Copy the code

You can change the table name, such as changing the job table to worker

alter table job rename worker;
Copy the code

DML statements

In some places, DML statements (add, delete and change) and DQL statements (query) are collectively referred to as DML statements, and in some places, separate statements are used

insert

After the table is created, we can insert data into the table. The basic syntax for inserting records is as follows

INSERT INTO tablename (field1,field2) VALUES(value1,value2);
Copy the code

For example, insert the following record into

Insert into the job (the name, sex, age, hiredate, birthday, salary) values (" cxuan ", "male", 24, "2020-04-27", "1995-08-22", 8000);Copy the code

You can also insert data without specifying the fields to be inserted

Insert into values("cxuan02"," male ",25,"2020-06-01","1995-04-23",12000);Copy the code

So there’s a problem here, what happens if the order of insertion is different?

Insert columns with NULL fields, non-null fields with default values, and autoincrement fields do not need to appear in the insert column list. Insert columns with NULL fields, non-null fields with default values, and autoincrement fields do not need to appear in the insert column list. This reduces the length and complexity of the SQL statement to be inserted.

For example, if we set hiredate and age to null, let’s try it

Insert into job(name,sex,birthday,salary) values("cxuan03"," male ","1992-08-23",15000);Copy the code

Let’s look at the actual data that was inserted

We can see a row with two fields showing NULL. Another nice feature of MySQL’s INSERT statement is that you can insert multiple records at once

INSERT INTO tablename (field1,field2) VALUES (value1,value2), (value1,value2), (value1,value2), ... ;Copy the code

As can be seen, each record is separated by commas. This feature can save a lot of network overhead when MySQL inserts a large number of records and greatly improve the insertion efficiency.

Update record

You can use the update command to modify the existing data in the table. The syntax is as follows

UPDATE tablename SET field1 = value1, field2 = value2 ;
Copy the code

For example, change NULL for age in cxuan03 in the job table to 26. The SQL statement would look like this

update job set age = 26 where name = 'cxuan03';
Copy the code

There’s a WHERE condition in the SQL statement, and we’ll talk about the WHERE condition later, but just to understand the concept of what record is updated, or if you don’t write where, the entire table will be updated

Delete records

If a record is no longer needed, run the delete command to delete it

DELETE FROM tablename [WHERE CONDITION]
Copy the code

For example, delete a record in job whose name is cxuan03

delete from job where name = 'cxuan03';
Copy the code

In MySQL, delete statements can also be used without specifying a WHERE condition

delete from job
Copy the code

This is equivalent to a clear table operation, and all records in the table will be cleared.

DQL statement

Let’s take a look at DQL statements. Data is inserted into MySQL and can be queried using the SELECT command to get the results we want.

The SELECT query is arguably the most complex statement, but we’ll just cover the basic syntax here

One of the easiest ways to do this is to query all the fields and data from a table

SELECT * FROM tablename;
Copy the code

For example, let’s look up all the data in the job table

select * from job;
Copy the code

* is used to query all data, of course, you can also query specified data items

select name,sex,age,hiredate,birthday,salary from job;
Copy the code

The above SQL statement is equivalent to the select * from job table, but this SQL statement is more efficient.

Above we introduced the basic SQL query statement, but the actual use of the scenario will be much more complex than simple query, generally use a variety of SQL functions and query conditions, and so on, let’s have a look.

duplicate removal

One of the most widely used scenarios is deduplication, which can be implemented using the DISTINCT keyword

To demonstrate the effect, we first insert batch data into the database. The table structure after insertion is as follows

Let’s see the effect of using distinct to override the age

You will find that there are only two distinct values, the others and 25 duplicate values are filtered out, so we use DISTINCT to de-duplicate

Conditions of the query

All of our previous examples have queried all records. What if we only wanted to query the specified record? For example, we want to query all records of age 24, as shown below

select * from job where age = 24;
Copy the code

The where statement is followed by the operator =. In addition to the = sign, you can also use >, <, >=, <=,! = equal comparison operator; For example,

select * from job where age >= 24;
Copy the code

Select * from job where age > 24

In addition, there can also be multiple parallel query conditions in the WHERE condition query. For example, we can query the records where the age is greater than or equal to 24 and the salary is 8000

select * from job where age >= 24 and salary > 8000;
Copy the code

Multiple conditions can also be queried by using logical operators such as OR and. Operators will be explained in detail in the following sections.

The sorting

We often need to sort by a certain field, so we use the sorting function of the database. We use the keyword order by to achieve this. The syntax is as follows

SELECT * FROM tablename [the WHERE CONDITION] [ORDER BY field1 [DESC | ASC], field2 [DESC | ASC],... fieldn [DESC | ASC]]Copy the code

Where DESC and ASC are sorted in order, DESC will be sorted in descending order by field, ASC will be sorted in ascending order by field, the default is ascending, that is, if you don’t write order by, the default is ascending. Order BY can be followed by multiple sort fields, and each sort field can have a different sort order.

To demonstrate the function, let’s first modify the salary column in the table, and record the table after modification as follows

Let’s sort by salary in the following SQL statement

select * from job order by salary desc;
Copy the code

After the statement is executed, the result is as follows

This is the result of sorting a single field, or multiple fields, with one caveat

If there are three sort fields A, B, and C, if the A sort field has the same value, then the second field will be sorted, and so on.

If there is only one sort field, records with the same fields will be sorted out of order.

limit

For sorted fields, or unsorted fields, we use the LIMIT keyword if we only want to display a portion of them, such that we only want to fetch the first three records

select * from job limit 3;
Copy the code

Or we take the first three records of the sorted fields

select * from job order by salary limit 3;
Copy the code

Select * from table where limit = 0; select * from table where limit = 0; select * from table where limit = 0; select * from table where limit = 0

Select * from job order by salary desc limit 2;Copy the code

Limit is often used in conjunction with the Order by syntax for paging queries.

Note: MySQL extends SQL92 syntax and is not common on other databases such as Oracle. One idiocy I have committed is to use the LIMIT query statement in Oracle.

The aggregation

Let’s take a look at the operations that summarize records

• Summary functionsFor example, sum, count, Max, min, etc
• group byFor example, count the number of employees by department. Then group by should be followed by department
• withIs an optional syntax that indicates that the records that have been summarized are summarized again
• havingKeyword: filters the classified results.

It looks like where and having have the same meaning, but they have different uses. Where is used to filter entries before statistics, and having is used to filter aggregated results. In other words, where is always used before having. We should filter the filtered records first, and then filter the grouped records.

You can select the total salary, maximum salary, and minimum salary by making statistics on the salary of employees in the job table

select sum(salary) from job;
Copy the code

select max(salary),min(salary) from job;
Copy the code

Let’s say we want to count the number of people in the job table

select count(1) from job;
Copy the code

The results are as follows

We can make the corresponding statistics according to the age in the job table

select age,count(1) from job group by age;
Copy the code

Count the number of people of all ages as well as the total number

select age,count(1) from job group by age with rollup;
Copy the code

Based on this, the group counts the number of records greater than 1

select age,count(1) from job group by age with rollup having count(1) > 1;
Copy the code

Table joins

Table connection has always been the author more painful place, once because of a table connection failed the interview, now to a serious wanke again.

Table joins are generally reflected in the relationship between tables. Table joins are used when fields from multiple tables need to be displayed simultaneously.

To demonstrate the ability of table joins, add a type field to the job table to indicate the job type and a job_type table to indicate the specific job type, as shown below

So let’s start our demo

The name and job type that match type in the job table and job_type table are displayed

select job.name,job_type.name from job,job_type where job.type = job_type.type;
Copy the code

The above connection uses an inner join, in addition to the outer join. So what’s the difference between them?

Inner join: select the records in two tables that match each other;

External connection: not only the matched records but also the unmatched records are selected.

There are two kinds of external connections

• Left outer join: filters out records that contain the left table and that the right table does not match
• Outer right join: Filters out records that contain the right table even if the left table does not match it

To demonstrate the effect, add records to the job table and job_type table, respectively

Select * from ‘job’ where ‘type’ matches’ job ‘and’ job_type ‘where’ type ‘matches’ name’ and ‘job’

select job.name,job_type.name from job left join job_type on job.type = job_type.type;
Copy the code

The query results are as follows

We can see that cxuan06 was also queried, but cxuan06 has no specific work type.

Use the right outer join to query

select job.name,job_type.name from job right join job_type on job.type = job_type.type;
Copy the code

The waiter and Manager roles are not included in the job table, but they are also queried.

The subquery

In some cases, the query condition we need is the result of another SQL statement. This type of query is called a subquery. The subquery has keywords such as in, not in, =,! =, exists, not exists, etc. For example, we can query the work type of each person by subquery

select job.* from job where type in (select type from job_type);
Copy the code

If the self-query quantity is unique, you can also replace in with =

select * from job where type = (select type from job_type);
Copy the code

This means that the query is not unique, and we use limit to limit the number of records returned

Select * from job where type = (select type from job_type limit 1,1);Copy the code

In some cases, subqueries can be transformed into table joins

The joint query

We often encounter such a scenario where the data of two tables are queried separately and the results are combined for display. In this case, the keywords UNION and UNION ALL are needed to achieve such a function. The main difference between UNION and UNION ALL is that UNION ALL directly combines the result sets, while UNION ALL performs a DISTINCT to remove duplicate data from the results after UNION ALL.

Such as

select type from job union all select type from job_type;
Copy the code

The results are as follows

Select * from ‘UNION ALL’ where ‘UNION ALL’ = ‘job’ and ‘job_type’ = ‘job_type’

The SQL statement using UNION is as follows

select type from job union select type from job_type;
Copy the code

Distinct deprocessing is used for UNION ALL.

DCL statement

DCL statements are primarily used to manage database permissions. This type of operation is typically used by DBAs, not developers.

About the use of help documents

When we use MySQL, we often have to consult the online materials and even the official documents of MySQL, which will consume a lot of time and energy.

MySQL > select * from ‘MySQL’ where ‘MySQL’ = ‘MySQL’

Query by hierarchy

Can it be used? Contents to query all available categories, as shown below

? contents;
Copy the code

We enter

? Account Management
Copy the code

You can query specific rights management commands

Let’s say we want to know about data types

? Data Types
Copy the code

Then we want to look at the basic definition of VARCHAR, which can be used directly

? VARCHAR
Copy the code

You can see the detailed information about the VARCHAR data types, and then the official MySQL documentation at the bottom for a quick review.

Quick access to

In the actual application process, if you want to quickly query a certain syntax, you can use keywords for quick query. For example, we use

? show
Copy the code

Be able to quickly list some commands

For example, if we want to access the database, use

SHOW CREATE DATABASE cxuandb;
Copy the code

MySQL Data type

MySQL provides a variety of data types to distinguish different constants, variables, the data type in MySQL is mainly value type, the date and time type, string type Select the appropriate data type for data storage is very important, in the actual development process, select the appropriate data type can also improve SQL performance, So it’s worth recognizing these data types.

Numeric types

MySQL supports all standard SQL data types, including strict numeric types of strict data types, such as

• INTEGER
• SMALLINT
• DECIMAL
• NUMERIC.

Approximate numeric data types are not stored strictly according to the specified data type

• FLOAT
• REAL
• DOUBLE PRECISION

There are also extended data types, which are

• TINYINT
• MEDIUMINT
• BIGINT
• BIT

Where INT is short for INTEGER and DEC is short for DECIMAL.

Here is a summary of all the data types

The integer

In the integer type, the value range and storage mode are different

! [image-20200613091331344](/Users/mr.l/Library/Application Support/typora-user-images/image-20200613091331344.png)

• TINYINT, takes 1 byte
• SMALLINT: takes 2 bytes
• MEDIUMINT, which takes 3 bytes
• INT or INTEGER, which occupies 4 bytes
• BIGINT, which takes 8 bytes

Five data types. If you operate out of the type range, you will get an error message, so it is important to choose the right data type.

Remember our construction sentence above

We usually add a specified length after the data type of the SQL statement to indicate the allowable range of the data type, for example

int(7)
Copy the code

Int (int(11), int(11), int(11), int(11));

Let’s create a table to demonstrate

create table test1(aId int, bId int(5)); /* select * from test1;Copy the code

Integer types are usually used in conjunction with Zerofill, which, as the name implies, is filled with zeros.

Modify two fields in table test1, respectively

alter table test1 modify aId int zerofill;

alter table test1 modify bId int(5) zerofill;
Copy the code

The query operation is then performed by inserting two pieces of data

As shown in the figure above, zerofill can be used to fill numbers with zeros, so what happens if the width exceeds the specified length? Let’s try inserting numbers that exceed the character limit into aId and bId, respectively

We will see that the aId is out of the specified range, so we will insert an aId that is within its allowed range

If the bId is int(5) and the bId is int(5), then the bId is inserted.

All integers have an optional UNSIGNED attribute. This option can be used if you want to store a non-negative number in a field or if you need a large upper limit. The value ranges from zero to twice the normal value. If a column is zerofill, an UNSIGNED attribute is automatically added to that column.

In addition, there is an integer type called AUTO_INCREMENT, which is used when you need to generate a unique identifier or sequence value. This property is used only for integer characters. A table has at most one AUTO_INCREMENT property, which is usually used to increment the PRIMARY KEY, and is NOT NULL. The PRIMARY KEY must be UNIQUE, and the PRIMARY KEY must be UNIQUE.

The decimal

What does a decimal say? There are really two types of it; One is floating point number type, the other is fixed point number type;

There are two kinds of floating-point numbers

• Single precision floating point – Float
• Double floating-point – Double

There is only one type of decimal for fixed-point numbers. Fixed point numbers exist internally in MySQL as strings and are more accurate than floating point numbers, making them suitable for expressing data with particularly high precision.

Both floating-point and fixed-point numbers can be represented in the form (M,D), where M is the integer digit plus the decimal digit, and D is the digit located in. The decimal in the back. M is also called precision and D is called scale.

Here’s an example to illustrate

Start by creating a test2 table

CREATE TABLE test2 (aId float(6,2) default NULL, bId double(6,2) default NULL,cId decimal(6,2) default NULL)
Copy the code

Then insert some data into the table

Insert into test2 values (1234.12, 1234.12, 1234.12);Copy the code

The data that is displayed at this point is

Then insert some data into the table that is outside the constraint

Insert into test2 values (1234.123, 1234.123, 1234.123);Copy the code

After the insert is complete, 1234.12 is displayed, and the third decimal place value has been abandoned.

Now let’s remove all the precision from test2 and insert again

alter table test2 modify aId float;

alter table test2 modify bId double;

alter table test2 modify cId decimal;
Copy the code

So if we look it up, we see that cId has eliminated the decimal place.

Then insert 1.23 again, and the SQL statement is as follows

Insert into test2 values (1.23, 1.23, 1.23);Copy the code

The results are as follows

This time can verify

• If the precision and scale of floating point numbers are not written, the actual precision value is displayed
• If you don’t write precision and scale, the fixed point number will be in accordance withA decimal (10, 0)If the data exceeds the accuracy and title, MySQL will report an error

A type of

For BIT types, which are used to store field values, BIT(M) can be used to store multi-bit binary numbers. M ranges from 1 to 64, and defaults to 1 if not written.

Now let’s gloss over bit types

Create a table test3 with only one field of type bit

create table test3(id bit(1));
Copy the code

Then insert a random piece of data

insert into test3 values(1);
Copy the code

The corresponding result cannot be queried.

We then use the hex() and bin() functions to query

Find that the corresponding result can be queried.

That is, when data is inserted into Test3, the data is first converted into binary numbers. If the number of bits is allowed, the insertion succeeds. If the number of bits is smaller than the number of bits actually defined, the insertion fails. If we insert data 2 into the table

insert into test3 values(2);
Copy the code

So you get an error

Because the binary representation of 2 is 10, and the table defines bit(1), it cannot be inserted.

So let’s change the table fields

Then I insert again, and I find that I can insert

Date-time type

The DATE and TIME types in MySQL include YEAR, TIME, DATE, DATETIME, and TIMESTAMP. Each version may be different. These types of properties are listed in the following table.

The following are respectively introduced

YEAR

YEAR can be represented in three ways

• Use a 4-digit number or string to indicate the range 1901-2155. Insert data outside the range and an error will be reported.
• The value is a two-character string ranging from 00 to 99. 00 to 69 indicates 2000 to 2069, and 70 to 99 indicates 1970 to 1999. ‘0’ and ’00’ are both recognized as 2000, and data out of range is also recognized as 2000.
• The value is a two-digit number ranging from 1 to 99. 1 to 69 indicates the range from 2001 to 2069, and 70 to 99 indicates the range from 1970 to 1999. But a 0 value is recognized as 0000, unlike a 2-bit string that is recognized as 2000

Let’s demonstrate the use of YEAR by creating a test4 table

create table test4(id year);
Copy the code

Then let’s look at the table structure for Test4

The default year is 4 bits, so let’s insert data into test4

insert into test4 values(2020),('2020');
Copy the code

You then query and find that the representation is the same

Use a two-digit string

delete from test4;

insert into test4 values ('0'),('00'),('11'),('88'),('20'),('21');
Copy the code

Use two digits

delete from test4;

insert into test4 values (0),(00),(11),(88),(20),(21);
Copy the code

Only the first two are different.

TIME

The scope of TIME is different from what we expect

Let’s change test4 to type TIME. Here’s an example of TIME

alter table test4 modify id TIME;

insert into test4 values ('15:11:23'),('20:13'),('2 11:11'),('3 05'),('33');
Copy the code

The results are as follows

DATE

There are many types of DATE. Here are a few examples of DATE

create table test5 (id date);
Copy the code

Take a look at table Test5

Then insert some data

insert into test5 values ('2020-06-13'),('20200613'),(20200613);
Copy the code

There are many different representations of DATE. The following are all the forms of DATE

• ‘YYYY-MM-DD’
• ‘YYYYMMDD’
• YYYYMMDD
• ‘YY-MM-DD’
• ‘YYMMDD’
• YYMMDD

DATETIME

DATETIME type, containing the date and time sections, can be a reference string or a number, and the year can be 4 or 2 bits.

Here is an example of DATETIME

create table test6 (id datetime);

insert into test4 values ('2020-06-13 11:11:11'),(20200613111111),('20200613111111'),(20200613080808);
Copy the code

TIMESTAMP

The format of the TIMESTAMP type is the same as that of the DATETIME type. It stores 4 bytes (less than that of DATETIME). The value range is smaller than that of DATETIME.

Here are some usage scenarios for each time type

• DATE = DATE; DATE = DATE;

• Used to express TIME, minutes, seconds, and usually TIME;

• Year, month, day, hour, minute, second, usually DATETIME;

• If you want to insert the current time, you usually use TIMESTAMP. The value is returned as a string in the format YYYY-MM-DD HH:MM:SS.

• If you represent only the YEAR, you should use YEAR, which requires less space than the DATE type.

Each date type has a range, and if the range is exceeded, an error is displayed in the default SQLMode and the value is stored as zero.

Now, what is SQLMode

MySQL has an environment variable called SQL_mode. Sql_mode supports MySQL syntax and data verification. You can use the following method to check the sql_mode used by the database

select @@sql_mode;
Copy the code

There are several patterns

From www.cnblogs.com/Zender/p/82…

String type

MySQL provides many string types. The following is a summary of string types

Let’s take a closer look at these data types

CHAR and VARCHAR types

CHAR and VARCHAR are very similar types, so many students will overlook the differences between them. First of all, they are both data types used to hold strings. The main difference between them is the way they are stored. The length of a CHAR type is what you define to be displayed. It takes M bytes. For example, if you declare a CHAR(20) string, then each string takes 20 bytes. M ranges from 0 to 255. A VARCHAR is a string of variable length ranging from 0 to 65535. When a string is retrieved, CHAR removes trailing whitespace, while VARCHAR retains those whitespace. Here’s an example

create table vctest1 (vc varchar(6),ch char(6));

insert into vctest1 values("abc  ","abc  ");

select length(vc),length(ch) from vctest1;
Copy the code

The results are as follows

You can see that the string type for VC is varchar and is of length 5, and the string type for CH is char and is of length 3. You can conclude that varchar will keep the last whitespace and char will remove the last whitespace.

BINARY and VARBINARY

BINARY and VARBINARY are very similar to CHAR and VARCHAR, except that they contain BINARY strings instead of non-binary strings. The maximum length of BINARY and VARBINARY is the same as that of CHAR and VARCHAR, except that they define the length of bytes, whereas CHAR and VARCHAR correspond to the length of characters.

BLOB

A BLOB is a large binary object that can hold a variable amount of data. There are four BLOB types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They differ in the range of storage they can hold.

The TEXT type

There are four TEXT types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. The maximum length of the BLOB varies according to the BLOB type. You can select one as required.

ENUM type

Enums are commonly used in Java to represent enumerated types. Its range needs to be specified when the table is created, and an enumeration of 1-255 requires 1 byte of storage; Enumerations for 255 to 65535 require 2 bytes of storage. The ENUM is case-insensitive and is converted to uppercase when stored.

The SET type

There are two differences between the SET and ENUM types

• storage

SET takes 1 byte for every 0-8 members, up to 64 and 8 bytes

• The main difference between a Set type and an ENUM type is that a Set type can select multiple members at a time, while an ENUM type can select only one member.

MySQL operator.

There are many operators in MySQL. The following is a classification of MySQL operators

• Arithmetic operator
• Comparison operator
• Logical operator
• An operator

Next, we will introduce each operator

Arithmetic operator

The arithmetic operators supported by MySQL include addition, subtraction, multiplication, division, and mod. These operators are used more frequently

The following is a classification of operators

The operator	role
+	add
–	subtraction
*	The multiplication
/, DIV	Division, return quotient
%, MOD	Divide, returning the remainder

The following is a brief description of how to use these operators

• +Used to get a sum of one or more values
• -Used to subtract another value from one value
• *Used to multiply two numbers to produce the product of two or more values
• /Dividing one value by another is worth the quotient
• %Used to divide one value by another value to obtain a remainder

One thing to note in division and mod is that if the divisor is 0, it is an invalid divisor and returns NULL.

Comparison operator

MySQL allows you to compare operands on both sides of an expression. If the comparison result is true, return 1, if the comparison result is false, return 0, and if the comparison result is uncertain, return NULL. Here are all the comparison operators

The operator	describe
=	Is equal to the
<> Or! =	Is not equal to
< = >	Null-safe is equal to null-safe
<	Less than
< =	Less than or equal to
>	Is greater than
> =	Greater than or equal to
BETWEEN	Within the specified range
IS NULL	Whether to NULL
IS NOT NULL	Whether to NULL
IN	Exists in the specified collection
LIKE	Wildcard match
The REGEXP or RLIKE	Regular expression matching

Comparison operators can be used to compare numbers, strings, or expressions. Numbers are compared as floating point numbers, and strings are compared in a case-insensitive manner.

• The = operator is used to compare whether the operands on both sides of the operator are equal. If they are equal, return 1. If they are not equal, return 0

• <>The number is used to indicate not equal to, and=The number is opposite, as shown in the following example

• < = >The null-safe equals operator differs from the = sign in that NULL values can be compared

• <Operator, which returns 1 if the left operand is less than the right operand, and 0 otherwise.

• Same as above, except return 1 if <=, otherwise return 0. I have a question here. Why

select 'a' <= 'b'; Select * from 'a' where 'a' >= 'b'; /* Return 0 */Copy the code

• The same is true for > and >=

• The BETWEEN operator is used in the format a BETWEEN min AND Max. Return 1 if a is greater than or equal to min AND less than or equal to Max, AND 0 otherwise. When the operands are of different types, they are converted to the same data type and processed. Such as

• IS NULL 和 IS NOT NULLIf ISNULL is true, return 1; otherwise, return 0; IS NOT NULL

• INThis comparison operator determines whether a value is in a set, using XXX in (value1,value2,value3)

• LIKEThe format of the operator isxxx LIKE %123%For example:

When like is followed by 123%, XXX returns 1 if it is 123, 1 if it is 123xxx, and 0 if it is 12 or 1. 123 is the whole thing.

• REGEXThe format of the operator iss REGEXP str, returns 1 on a match, and 0 otherwise.

More on the use of regexp later.

Logical operator

Boolean operators are Boolean operators, Boolean operators return true and false. MySQL supports four types of logical operators

The operator	role
Or NOT!	Logic is not
AND or &&	Logic and
The OR OR the | |	Logic or
XOR	Xor logic

The following are respectively introduced

• NOTOr is it!If the operand is 0 (false), the return value is 1; otherwise, the value is 0. The exception is that NOT NULL returns NULL

• AND 和 &&If all operands are non-zero and are not NULL, the result is 1, but if there is a 0, the result is 0, and if there is a NULL, the result is NULL

• OR 和 ||If either of the operands is non-zero, the result is 1; otherwise, the result is 0.

• XORIndicates logical xOR. If either of the operands is NULL, the return value is NULL. For operands that are not NULL, return 1 if the logical true and false values of the two are different; Otherwise return 0.

An operator

Bit-operation refers to the operation that converts a given operand into binary and performs a specified logical operation on each bit of each operand. The binary result is the result of bit-operation when converted to decimal. The following are all the bit-operation operators.

The operator	role
&	with
|	or
^	An exclusive or
~	Who take the
>>	Who moves to the right
<<	A shift to the left

These examples are shown below

• withIt means bitwise and, you convert the &to binary and then you do the &

Bitwise and is a numeric reduction operation

• orRefers to is the bitwise or of the | both sides converted to binary again by using the | operator

Bit or an operation that increases a number

• An exclusive orThis is an xOR operation on the binary bits of the operand

• Who take theI’m doing it to the bits of the operandNOTIn this case, the operand can only be one bit. In this case, the operand can only be one bit.

The reason for this is that in MySQL, constant numbers are displayed as 8 bytes by default. 8 bytes is 64 bits. The binary representation of the constant 1 is 63 zeros plus 1. This is 18446744073709551614, which we can use select bin() to see

• Who moves to the rightMove the left operand to the right by a specified number of bits. For example, 50 >> 3. Take the binary value of 50, move it to the right by three bits, and add a 0 to the left

• A shift to the leftAs opposed to a bit right shift, the left operand is moved to the left by a specified number of bits, such as 20 << 2

MySQL > Select * from ‘MySQL’;

Let’s take a look at MySQL functions. MySQL functions are often used in our daily development process. Choosing appropriate functions can improve our development efficiency, so let’s take a look at these functions

String function

String functions are one of the most commonly used. MySQL also supports many string functions. Here is a list of string functions supported by MySQL

function	function
LOWER	Changes all characters of the string to lowercase
UPPER	Capitalize all characters in the string
CONCAT	String concatenation
LEFT	Returns the leftmost character in the string
RIGHT	Returns the rightmost character of the string
INSERT	String substitution
LTRIM	Remove Spaces on the left side of the string
RTRIM	Remove the space on the right side of the string
REPEAT	Returns duplicate results
TRIM	Remove Spaces at the end of string lines and at the beginning of the line
SUBSTRING	Returns the specified string
LPAD	Fill the leftmost part with a string
RPAD	Fill the rightmost part with a string
STRCMP	Compare the strings S1 and s2
REPLACE	Do string substitution

Here’s a concrete example of how each function is used

• LOWER(STR) and UPPER(STR) functions: Used to convert case

• CONCAT(s1,s2 … Sn) : Concatenates the passed arguments into a string

This concatenates C Xu an into a string, and the other thing to note is that any concatenation with NULL is NULL.

• LEFT(STR,x) and RIGHT(STR,x) functions: return the leftmost x and rightmost x characters of the string, respectively. If the second argument is NULL, no string will be returned

• INSERT(STR,x,y,instr) : INSERT(STR,x,y,instr) : INSERT(STR,x,y,instr) : INSERT(STR,x,y,instr) : INSERT(STR,x,y,instr) : INSERT(STR,x,y,instr);

• LTRIM(STR) and RTRIM(STR) mean to remove Spaces on the left and right sides of the string STR, respectively

• The REPEAT(STR,x) function returns the result of repeating STR x times

• TRIM(STR) function: used to remove whitespace from the target string

• SUBSTRING(STR,x,y) SUBSTRING(STR,x,y) SUBSTRING(STR,x,y) SUBSTRING(STR,x,y) SUBSTRING(STR,x,y

• The LPAD(STR,n,pad) and RPAD(STR,n,pad) functions: Fill the left and right sides of STR with the string pad until the length is n characters long

• STRCMP(s1,s2) is used to compare the ASCII value sizes of the strings S1 and s2. If s1 is less than s2, return -1; If s1 = s2, return 0; If s1 > s2, return 1.

• REPLACE(STR,a,b) : REPLACE all occurrences of string A with the string b

Numerical function

MySQL supports numeric functions, which can handle many numeric operations. Let’s take a look at the numeric functions in MySQL. Here are all the numeric functions

function	function
ABS	Return absolute value
CEIL	Returns the largest integer value greater than a certain value
MOD	Return to die
ROUND	rounded
FLOOR	Returns the largest integer value less than a value
TRUNCATE	Returns the result of a numeric truncated decimal
RAND	Returns a random value of 0-1

Let’s talk about them in terms of practice

• ABS(x) function: returns the absolute value of x

• CEIL(x) : returns an integer greater than x

• MOD(x,y), MOD x and y

• ROUND(x,y) returns the value of the y decimal place after x is rounded; If it’s an integer, then the y-bit is 0; If y is not specified, then y defaults to 0.

• FLOOR(x) : returns the largest integer less than x, the opposite of CEIL

• TRUNCATE(x,y): Returns the truncation of the number x to a y decimal. TRUNCATE is knowledge truncation, not rounding.

• RAND() : returns a random value from 0 to 1

Date and time functions

Date and time functions are also an important part of MySQL. Let’s take a look at these functions

function	function
NOW	Returns the current date and time
WEEK	Return to the week of the year
YEAR	Returns the year of the date
HOUR	Return hour value
MINUTE	Return minute value
MONTHNAME	Return month name
CURDATE	Return current date
CURTIME	Return current time
UNIX_TIMESTAMP	Returns a date UNIX timestamp
DATE_FORMAT	Returns a date formatted as a string
FROM_UNIXTIME	Returns the date value of the UNIX timestamp
DATE_ADD	Return datetime + last interval
DATEDIFF	Returns the number of days between the start time and the end time

Here are some examples of how to use each of these functions

• NOW(): Returns the current date and time

• WEEK(DATE) and YEAR(DATE) : The former returns the WEEK of the YEAR; the latter returns the YEAR of the given DATE

• HOUR(time) and MINUTE(time) : Return hours at a given time, and the latter returns minutes at a given time

• MONTHNAME(date) function: returns the English month of date

• The CURDATE() function returns the current date, including only the date, month, and year

• CURTIME() : returns the current time, including only the time, minute and second

• UNIX_TIMESTAMP(date) : returns the UNIX timestamp

• FROM_UNIXTIME(date) : returns the date value of the UNIXTIME timestamp, as opposed to UNIX_TIMESTAMP

• The DATE_FORMAT(date, FMT) function: formats date according to the string FMT and displays the date in the specified date format

The date format can be found in this article blog.csdn.net/weixin_3870…

Let’s demonstrate displaying the current date as date, month and year, using the date format %M %D %Y.

• DATE_ADD(date, interval, expr type) function: returns the interval between the date and the given date

Interval is the keyword for the interval type. Expr is the expression that corresponds to the following type. Type is the interval type

Expression type	describe	format
YEAR	years	YY
MONTH	month	MM
DAY	day	DD
HOUR	hours	hh
MINUTE	points	mm
SECOND	seconds	ss
YEAR_MONTH	Year and month	YY-MM
DAY_HOUR	Days and hours	DD hh
DAY_MINUTE	Day and minute	DD hh : mm
DAY_SECOND	Day and seconds	DD hh ：mm ：ss
HOUR_MINUTE	Hours and minutes	hh:mm
HOUR_SECOND	Hours and seconds	hh:ss
MINUTE_SECOND	Minutes and seconds	mm:ss

• DATE_DIFF(date1, date2) is used to calculate the number of days between two dates

Check how many days are left until 2021-01-01

The process function

Process functions are also a common class of functions that users can use to implement conditional selection in SQL. Doing so can improve query efficiency. The following table lists these process functions

function	function
IF(value,t f)	If value is true, return t; Otherwise return f
IFNULL(value1,value2)	If value1 is not NULL, value1 is returned, otherwise value2 is returned.
CASE WHEN[value1] THEN[result1] … ELSE[default] END	Return result1 if value1 is true, otherwise return default
CASE[expr] WHEN[value1] THEN [result1]… ELSE[default] END	Return result1 if expr is equal to value1, otherwise return default

Other functions

In addition to the string functions, date and time functions, and process functions that we discussed, there are some functions that do not fall into the above three categories

function	function
VERSION	Returns the version of the current database
DATABASE	Returns the current database name
USER	Returns the name of the currently logged in user
PASSWORD	Returns an encrypted version of the string
MD5	Returns the MD5 value
INET_ATON(IP)	Returns a numeric representation of the IP address
INET_NTOA(num)	Returns the IP address represented by the number

Let’s see how it works

• VERSION: Returns the current database VERSION

• DATABASE: Returns the current DATABASE name

• USER: returns the name of the current login USER

• PASSWORD(STR) : returns an encrypted version of the string, for example

• MD5(STR) function: Returns the MD5 value of the string STR

• INET_ATON(IP): Returns the network byte sequence of IP

• The INET_NTOA(num) function returns the IP address represented by the network byte sequence, as opposed to INET_ATON