Too complete! ! 138 pictures to get you started with MySQL! !

SQL basic usage

MySQL is a relational database. When it comes to relationships, it is inseparable from the relationship between tables and tables. The most representative of this relationship is actually the protagonist we need to introduce next SQL. The full name of SQL isStructure Query Language , Structured The query language, which is a language designed for table associations, that is to say, to learn MySQL, SQL is the foundation and the most important. SQL is not just a language unique to MySQL, most relational databases support this language.

Let's learn about this very important language together.

Query language classification

Before understanding SQL, we need to know the following concepts

  • Data Definition Language: abbreviated DDL(Data Definition Language), used to define database objects: databases, tables, columns, etc.;
  • Data manipulation language: abbreviationDMLData Manipulation Language (Data Manipulation Language), used to update the records of the table in the database. Keywords: insert, update, delete, etc.
  • Data Control Language: AbbreviationDCL (Data Control Language), used to define database access permissions and security levels, create users, etc. Keywords: grant, etc.
  • Data query language: abbreviated as DQL(Data Query Language), used to query the records of the table in the database, keywords: select from where, etc.

DDL statement

Create database

Let’s start our SQL statement learning journey. First, you need to start the MySQL service. I am a mac computer here, so I can start it directly

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

MacBook:~ mr.l$ mysql -uroot -p

You can connect to the database

In the above command, it mysqlrepresents the client command, - uthe user who needs to connect later, and -pthe user's password needs to be entered. After you enter your user name and password, if successful landing, will display a Welcome screen (pictured above) and mysql>prompt.

The welcome screen mainly describes these things

  • Each line terminator, used here ;, or \gto indicate the end of each line
  • Your MySQL connection id is 4 , this records the number of connections of the MySQL service so far. Each new link will automatically increase by 1. The number of connections shown above is 4, indicating that we have only connected four times
  • Then the following is the version of MySQL, we are using 5.7
  • By helpor \hto display help content Command, through \cto clear the command line buffer command.

Then what needs to be done? We ultimately want to learn SQL statements. SQL statements are definitely to query data and reflect the relationship of tables through data. So we need data, so where does the data exist? The data storage location is called 表(table), and the table storage location is called 数据库(database), so we need to build the database first, then build the table, insert the data, and then query.

So the first thing we have to do is to create a database, you can directly use the command to create a database

CREATE DATABASE dbname;

To create, for example, we create a database cxuandb

create database cxuandb;

Note that the last of ;the end of grammar must not be lost, otherwise MySQL would think after your command has not finished output, knocking enter wrap directly output

After the creation is complete, it will prompt Query OK, 1 row affected . What does this statement mean? Query OK means that the query is complete, why is this displayed? Because all DDL and DML operations will prompt this after the execution is completed, it can also be understood as a successful operation. The **1 row affected ** that follows represents the number of rows affected, ()and the time it takes you to execute this command is displayed inside, which is 0.03 seconds.

In the above picture, we have successfully created a cxuandb database. At this time, we also want to create a database. We execute the same command again, and the result prompts

It reminds us that we can no longer create a database, the database already exists. At this time I have a question, how do I know which databases are available? I do not think they told me to create a database that already exists, this time you can use the show databasescommand to check your existing MySQL database

show databases;

The result after execution is as follows

Because before I have used the database, you need to explain here, in addition to the newly created successful cxuandb just outside informationn_schema, performannce_schemaand sysall system comes with a database, MySQL database installation is created by default. They each represent

  • informationn_schema: mainly stores some database object information, such as user table information, permission information, partition information, etc.
  • performannce_schema: A newly added database after MySQL 5.5. It is mainly used to collect database server performance parameters.
  • 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 metadata information of the system.

All other databases are created by the author himself, you can ignore them.

After creating the database, you can use the following command to select the database to be operated

use cxuandb

In this way, it is successfully switched to the cxuandb database, and we can perform operations such as building tables and viewing basic information in this database.

For example, if we want to see if there are other tables in our newly created database in Kangkang

show tables;

Sure enough, there is no table under our newly created database, but now, we are not doing table building operations, let's first understand the commands at the database level, that is, other DDL commands

Delete database

If we don't want a database anymore, what should we do? Wouldn't it be fine to just delete the database? The delete table statement is

drop database dbname;

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

drop database cxuandb;

To delete, we will not demonstrate here, because we will use cxuandb later.

But here is one point to note, after you successfully delete the database, 0 rows affected will appear . This can be ignored, because in MySQL, the results of the drop statement operation are all 0 rows affected .

Create table

Now we can operate on the table, we just show tables and found that there is no table yet, so we are now proceeding to create a table statement

CREATE TABLE 表名称
(
列名称1 数据类型 约束,
列名称2 数据类型 约束,
列名称3 数据类型 约束,
....
)

So it's very clear, the column name is the name of the column, followed by the column name is the data type, and then the constraint, why do you want to design this way? For example, you will be clear. For example, cxuan was printed with a label right after it was born.

For example, we create a table, which has 5 fields, name (name), gender (sex), age (age), when to hire (hiredate), salary (wage), the statement to build the table is as follows

create table job(name varchar(20), sex varchar(2), age int(2), hiredate date, wage decimal(10,2));

Facts have proved that this construction of the table statement or no problem, after the completion of construction of the table you can use DESC tablenamethe basic information view the table

DESC The command will view the definition of the table, but the output information is not comprehensive enough, so if you want to view more complete information, you have to check the SQL of the table creation statement to get

show create table job \G;

It can be seen, in addition to see the table definition, but also to see the tables engine(存储引擎)for the InnoDB storage engine, \Gso that the recording can be vertically aligned, if not \G, the result is as follows

Delete table

Table delete statement, there are two, one is the dropstatement, SQL statement is as follows

drop table job

One is the truncatestatement, SQL statement is as follows

truncate table job

The simple understanding of the difference between the two is that after the drop statement deletes the table, you can reply through the log, and truncate can never recover after deleting the table. Therefore, truncate is generally not used to delete the table. '

Modify table

For tables that have been created, especially tables with a large amount of data, if you need to make structural changes to the table, you can delete the table and then re-create the table, but this efficiency will generate some extra work, and the data will be reloaded recently. , If there is a service being accessed at this time, it will also affect the service to read the data in the table, so at this time, we need the modification statement of the table to modify the definition of the created table.

Modified table structure generally used alter tablestatement, the following are some suggestions

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

For example, we want the job table by the name varchar(20)change varchar(25), you can use the following statement

alter table job modify name varchar(25);

You can also modify the table structure, such as adding a field

alter table job add home varchar(30);

Delete the fields of the newly added table

alter table job drop column home;

You can modify the name of the field in the table, for example, change the wage to salary

alter table job change wage salary decimal(10,2);

Modify the arrangement order of the fields. We mentioned that modifying the syntax involves an order problem. There is an option **first | after ** column_name. This option can be used to modify the position of the field in the table. The default ADD is adding as The last field in the table, and CHANGE/MODIFY will not change the position of the field. such as

alter table job add birthday after hiredate;

You can modify the table name, for example, change the job table to worker

alter table job rename worker;

DML statement

In some places, DML statements (additions, deletions, and modifications) and DQL statements (query) are collectively referred to as DML statements, and in some places, we use separate naming methods.

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);

For example, insert the following record into

insert into job(name,sex,age,hiredate,birthday,salary) values("cxuan","男",24,"2020-04-27","1995-08-22",8000);

You can also insert the data directly without specifying the field to be inserted

insert into job values("cxuan02","男",25,"2020-06-01","1995-04-23",12000);

There is a problem here, what happens if the order of insertion is inconsistent?

For fields that contain nullable fields, non-empty fields but contain default values, and auto-increment fields do not need to appear in the field list after insert, only the value of the corresponding field name needs to be written after values, and fields that are not written can be automatically set to NULL, the default value, or the next value that is incremented, which can shorten the length and complexity of the SQL statement to be inserted.

For example, let’s set hiredate and age to be null, let’s try

insert into job(name,sex,birthday,salary) values("cxuan03","男","1992-08-23",15000);

Let's take a look at the actually inserted data

We can see that there is a row of two fields showing NULL. In MySQL, the insert statement also has a good feature, that is, you can insert multiple records at a time

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

It can be seen that each record is divided by a comma. This feature allows MySQL to save a lot of network overhead when inserting a large number of records, and greatly improve the insertion efficiency.

update record

For the data that already exists in the table, you can use the update command to modify it, the syntax is as follows

UPDATE tablename SET field1 = value1, field2 = value2 ;

For example, to change the NULL of age in cxuan03 in the job table to 26, the SQL statement is as follows

update job set age = 26 where name = 'cxuan03';

There is a where condition in the SQL statement, we will talk about where condition later, here is a brief understanding of its concept is based on which record to update, if you do not write where, the entire table will be updated

Delete Record

If the record is no longer needed, you can use the delete command to delete

DELETE FROM tablename [WHERE CONDITION]

For example, delete the record named cxuan03 in the job

delete from job where name = 'cxuan03';

In MySQL, the delete statement can also be used directly without specifying the where condition

delete from job

This deletion method is equivalent to the operation of clearing the table, and all the records in the table will be cleared.

DQL statement

Let's get to know about the DQL statements, data is inserted into MySQL, you can use the SELECTcommand to query, to get the results we want.

SELECT query statement can be said to be the most complex statement, here we only introduce the basic syntax

One of the easiest way is to query all the fields and data from a table, simple and crude, use directly SELECT *

SELECT * FROM tablename;

For example, we will find out all the data in the job table

select * from job;

Among them * is to query all the data, of course, you can also query the specified data items

select name,sex,age,hiredate,birthday,salary from job;

The above SQL statement and select * from job tables are equivalent, but this direct query SQL statement specified field of higher efficiency.

We have introduced basic SQL query statements above, but the actual usage scenarios will be much more complicated than simple queries. Generally, various SQL functions and query conditions are used. Let's get to know them together.

De-duplication

Use one of the very wide range of scenarios that 去重, deduplication can use distinctkeywords to achieve

In order to demonstrate the effect, we first insert batch data into the database, the table structure after insertion is as follows

Next, we use distinct to re-check the effect of age

You will find that there are only two different values, and the other and 25 duplicate values ​​are filtered out, so we use distinct to remove duplicates

Condition query

All our previous examples are to query all records. What if we only want to query the specified records? Here will be used wherecondition query, the query can query the condition specified fields, such as we would like to check all age record of 24, as follows

select * from job where age = 24;

where a conditional statement will follow behind the operator determines =, in addition =to comparing numbers, may be used >, <,> =, <=,! = , etc. comparison operators; e.g.

select * from job where age >= 24;

It will query the records with age greater than or equal to 24 from the job table

In addition, there can be multiple parallel query conditions in the where condition query. For example, we can query 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;

You can also use logical operators such as or, and to perform multi-condition joint query between multiple conditions. The operators will be explained in detail in the following chapters.

Sorting

We often have such needs, sorted by a field, which is used in the sorting of the database, using keywords order byto achieve, syntax is as follows

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] , field2 [DESC|ASC],……fieldn [DESC|ASC]]

Among them, DESC and ASC are the keywords for sequential sorting, DESC will be sorted in descending order according to the field, ASC will be sorted in ascending order according to the field, and the ascending order will be used by default, that is, you do not write order by the specific sequencing, the default will be used in ascending order . Order by can be followed by multiple sort fields, and each sort field can have a different sort order.

To demonstrate the function, we first table in salarythe salary column to be modified, the modified table records the completion of the following

Below we sort by salary, the SQL statement is as follows

select * from job order by salary desc;

The result after the statement is executed is as follows

This is the result of sorting one field, and you can sort multiple fields, but you need to pay attention to it

Sort according to the order of fame after order by. If there are three sort fields A, B, and C, if the value of the sort field in the A field is the same, the sort will be sorted according to the second field, and so on.
If there is only one sort field, records with the same fields will be arranged out of order.

limit

For the latter sort fields, sort fields or not, if you want to display only part of it, will use the LIMITkeywords to achieve, for example, we just want to take the first three records

select * from job limit 3;

Or we take the first three records of the sorted field

select * from job order by salary limit 3;

The above limit is taken from the 0th entry of the table record. If it is taken from the specified record, for example, from the second entry, take three records, the SQL is as follows

select * from job order by salary desc limit 2,3;

Limit is often used together with order by syntax to implement paging queries.

Note: limit is the grammar after MySQL extends SQL92. It is not universal in other databases such as Oracle. I have committed an idiotic behavior that uses limit query statements in Oracle. . .

polymerization

Let's take a look at the operation of summarizing records. This type of operation mainly includes

  • 汇总函数, Such as sum summation, count statistics, max maximum, min minimum, etc.
  • group by, The keyword means to group the classified and aggregated fields. For example, if the number of employees is counted according to the department, then the group by should be followed by the department
  • with Is an optional syntax, which means that the records after the summary are summarized again
  • having Keyword means to filter the results after classification.
It seems that where and having have the same meaning, but their usage is different. Where is used before statistics to filter records before statistics, and having is used after statistics to filter the results after aggregation. In other words, where is always used before having, we should filter the filtered records first, and then filter the grouped records.

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

select sum(salary) from job;
select max(salary),min(salary) from job;

For example, we want to count the number of people in the job table

select count(1) from job;

The results after the statistics are as follows

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

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

It is necessary to count the number of people of each age group and the total number of people

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

Group on this basis and count the records whose number is greater than 1

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

Table join

The table connection has always been a painful place for the author. Once I ended up with an interview because of a table connection, now I will do it seriously.

Table connection is generally reflected in the relationship between the tables. When you need to display fields in multiple tables at the same time, you can use table joins to achieve.

To demonstrate the menu connected to the job table we add a typefield indicating the type of work, to increase job_type a table showing a specific type of work, as shown in FIG.

Let's start our demo

Query the name and job type that match the type in the job table and the type in the job_type table

select job.name,job_type.name from job,job_type where job.type = job_type.type;

The above connection uses internal connections, in addition to external connections. So what is the difference between them?

Inner join: select the records that match each other in the two tables;
Outer join: not only select matching records, but also unmatched records;

There are two types of external connections

  • Left outer join: filter out the records containing the left table and the right table does not match it
  • Right outer join: filter out the records containing the right table even the left table does not match it

In order to demonstrate the effect, we add records to the job table and job_type table respectively. The two tables after the addition are as follows

Below we perform a left outer join query: query the name and job type that match the type in the job table and the type in the job_type table

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

The results of the query are as follows

It can be seen that cxuan06 has also been queried, and cxuan06 has no specific type of work.

Use right outer join query

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

It can be seen that the roles of waiter and manager are not in the job table, but they are also queried.

Subqueries

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

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

If the number of unique words from a query, can also be used =to replacein

select * from job where type = (select type from job_type);

It means that self-query is not unique, we use limit to limit the number of records returned

select * from job where type = (select type from job_type limit 1,1);

In some cases, subqueries can be converted to table joins

Joint query

We also often encounter such scenarios. After querying the data of the two tables separately, the results are merged together for display. At this time, the two keywords UNION and UNION ALL are needed to achieve this function, UNION and the main difference is that UNION ALL UNION ALL is the combined result set directly, but is the result of UNION UNION ALL once DISTINCTto remove duplicate data.

such as

select type from job union all select type from job_type;

Its result is as follows

The above result is to query the type field in the job table and the type field in the job_type table, and summarize them, you can see that UNION ALL just lists all the results

The SQL statement using UNION is as follows

select type from job union select type from job_type;

It can be seen UNION ALL UNION is used distinctdeduplication processing.

DCL statement

DCL statements are mainly used when managing database permissions. This type of operation is generally used by DBAs, and developers will not use DCL statements.

About the use of help files

We generally use MySQL when we encounter things that we don't know or have questions, we often need to check online materials, and we may even need to check MySQL official documents, which will consume a lot of time and energy.

Let me teach you a statement that can directly query data on the MySQL command line

Query by level

It can be used ? contentsto query all categories for the query, as shown below

? contents;

We enter

? Account Management

You can query specific commands for authority management

For example, we want to know about the data type

? Data Types

Then we would like to know VARCHARthe basic definition, can be used directly

? VARCHAR

You can see that there are detailed information about the VARCHAR data type, and then there is the official MySQL document at the bottom, which is convenient for us to quickly refer to.

Quick reference

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

? show

Ability to quickly list some commands

For example, if we want to check database information, use

SHOW CREATE DATABASE cxuandb;

MySQL data type

MySQL provides a wide variety of data types to distinguish between different constants, variables, data types are mainly MySQL numeric type, date and time, strings select the appropriate data type of data storage is very important, in the actual development process In, choosing the appropriate data type can also improve SQL performance, so it is necessary to understand these data types.

Numerical type

MySQL supports all standard SQL data types, these data types include strict data types 严格数值类型, these data types are

  • INTEGER
  • SMALLINT
  • DECIMAL
  • NUMERIC.

近似数值数据类型 It is not necessary to store strictly in accordance with the specified data type, these are

  • FLOAT
  • REAL
  • DOUBLE PRECISION

There are also expanded data types, they are

  • TINYINT
  • MEDIUMINT
  • BIGINT
  • BIT

Among them, INT is the abbreviation of INTEGER, and DEC is the abbreviation of DECIMAL.

Below is a summary of all data types

Integer

In the integer type, according to the value range and storage method, it is divided into

Insert picture description here
  • TINYINT, occupies 1 byte
  • SMALLINT, occupies 2 bytes
  • MEDIUMINT, occupying 3 bytes
  • INT, INTEGER, occupy 4 bytes
  • BIGINT, occupy 8 bytes

For the five data types, if the operation exceeds the type range, an error message will occur, so it is very important to select the appropriate data type.

Remember our table building statement above?

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

int(7)

Indicates that the maximum length of the int type data is 7, if the filling is not full, it will be filled automatically, if the length of the int data type is not specified, the default is int(11) .

Let's create a table to demonstrate

create table test1(aId int, bId int(5));

/* 然后我们查看一下表结构 */
desc test1;

With the integer type is generally zerofillused, by definition, is filled with zeros, the number of digits is not enough space is filled with 0.

Modify the two fields in the test1 table separately

alter table test1 modify aId int zerofill;

alter table test1 modify bId int(5) zerofill;

Then insert two pieces of data and perform the query operation

As shown above, use zerofillmay be used in front of the numbers 0to be filled, if it exceeds the specified width show how the length? Let's try it out and insert numbers that exceed the character limit into aId and bId respectively

It will be found that aId has exceeded the specified range, so we insert a data within its allowable range into aId

You will find that aId has been inserted, and bId is also inserted. Why does bId display int(5) but can insert a 7-digit value?

All integers have an optional attribute UNSIGNED(无符号). If you need to store non-negative numbers in the field or need a larger upper limit, you can use this option. Its value range is 0 for the lower limit of the normal value, and the original value for the upper limit. 2 times. If a column is zerofill, the UNSIGNED attribute is automatically added to the column.

In addition, there is another type of AUTO_INCREMENTinteger. This attribute can be used when a unique identifier or sequence value needs to be generated. This attribute is only used for integer characters. A maximum of one table AUTO_INCREMENT property, is generally used 自增主键, but also NOT NULL, and is PRIMARY KEY, and UNIQUEthe primary key must be guaranteed to be unique and not empty.

Decimal

What does the decimal say? It actually has two types; one is 浮点数type and the other is 定点数type;

There are two types of floating point numbers

  • Single-precision floating point type-float type
  • Double-precision floating-point type-double type

There is only one fixed-point number decimal . Fixed-point numbers exist in the form of strings inside MySQL, which are more accurate than floating-point numbers and are suitable for expressing extremely high-precision data.

Floating-point and fixed-point numbers are used (M,D)way to represent, M is is an integer bits + decimal digits, D denotes located .behind the decimal. M is also called accuracy, and D is called scale.

Let’s demonstrate with an example

First create a test2table

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

Then insert a few pieces of data into the table

insert into test2 values(1234.12,1234.12,1234.12);

The data displayed at this time is

Then insert some data outside the constraints into the table

insert into test2 values(1234.123,1234.123,1234.123);

It was found that after the insertion was completed, it was also displayed that 1234.12the value of the third decimal place was dropped.

Now we remove all the precision in the test2 table and insert it again

alter table test2 modify aId float;

alter table test2 modify bId double;

alter table test2 modify cId decimal;

Check first and find that cId has dropped off the decimal place.

Then insert 1.23 again, the SQL statement is as follows

insert into test2 values(1.23,1.23,1.23);

The result is as follows

This time can be verified

  • If the precision and scale are not written in the floating-point number, it will be displayed according to the actual precision value
  • If you do not write fixed-point precision and scale, we will follow decimal(10,0)to carry out the operation, if the data exceeds the precision and title, MySQL will complain

Bit type

For the bit type, it is used to store the field value. It BIT(M)can be used to store a multi-bit binary number. The range of M is 1-64. If it is not written, it defaults to 1 bit.

Let's cover up the bit type

Create a new test3 table, there is only one bit type field in the table

create table test3(id bit(1));

Then insert a piece of data at will

insert into test3 values(1);

Found that the corresponding results cannot be queried.

Then we use hex()and bin()function query

Found that the corresponding results can be queried.

That is to say, when data is inserted into test3, it will first convert the data into binary numbers. If the number of digits allows, the insertion will be successful; if the number of digits is less than the actual number of digits, the insertion will fail. If we insert data into the table 2

insert into test3 values(2);

Then it will report an error

因为 2 的二进制数表示是 10,而表中定义的是 bit(1) ,所以无法插入。

那么我们将表字段修改一下

然后再进行插入,发现已经能够插入了

日期时间类型

MySQL 中的日期与时间类型,主要包括:YEAR、TIME、DATE、DATETIME、TIMESTAMP,每个版本可能不同。下表中列出了这几种类型的属性。

下面分别来介绍一下

YEAR

YEAR 可以使用三种方式来表示

  • 用 4 位的数字或者字符串表示,两者效果相同,表示范围 1901 - 2155,插入超出范围的数据会报错。
  • 以 2 位字符串格式表示,范围为 ‘00’‘99’。‘00’‘69’ 表示 20002069,‘70’‘99’ 表示1970~1999。‘0’ 和 ‘00’ 都会被识别为 2000,超出范围的数据也会被识别为 2000。
  • 以 2 位数字格式表示,范围为 199。169 表示 2001~2069, 70~99 表示 1970~1999。但 0 值会被识别为0000,这和 2 位字符串被识别为 2000 有所不同

下面我们来演示一下 YEAR 的用法,创建一个 test4 表

create table test4(id year);

然后我们看一下 test4 的表结构

默认创建的 year 就是 4 位,下面我们向 test4 中插入数据

insert into test4 values(2020),('2020');

然后进行查询,发现表示形式是一样的

使用两位字符串来表示

delete from test4;

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

使用两位数字来表示

delete from test4;

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

发现只有前两项不一样。

TIME

TIME 所表示的范围和我们预想的不一样

我们把 test4 改为 TIME 类型,下面是 TIME 的示例

alter table test4 modify id TIME;

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

结果如下

DATE

DATE 表示的类型有很多种,下面是 DATE 的几个示例

create table test5 (id date);

查看一下 test5 表

然后插入部分数据

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

DATE 的表示一般很多种,如下所示 DATE 的所有形式

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

DATETIME

DATETIME 类型,包含日期和时间部分,可以使用引用字符串或者数字,年份可以是 4 位也可以是 2 位。

下面是 DATETIME 的示例

create table test6 (id datetime);

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

TIMESTAMP

TIMESTAMP 类型和 DATETIME 类型的格式相同,存储 4 个字节(比DATETIME少),取值范围比 DATETIME 小。

下面来说一下各个时间类型的使用场景

一般表示年月日,通常用 DATE 类型;

用来表示时分秒,通常用 TIME 表示;

年月日时分秒 ,通常用 DATETIME 来表示;

如果需要插入的是当前时间,通常使用 TIMESTAMP 来表示,TIMESTAMP 值返回后显示为 YYYY-MM-DD HH:MM:SS 格式的字符串,

如果只表示年份、则应该使用 YEAR,它比 DATE 类型需要更小的空间。

每种日期类型都有一个范围,如果超出这个范围,在默认的 SQLMode 下,系统会提示错误,并进行零值存储。

下面来解释一下 SQLMode 是什么

MySQL 中有一个环境变量是 sql_mode ,sql_mode 支持了 MySQL 的语法、数据校验,我们可以通过下面这种方式来查看当前数据库使用的 sql_mode

select @@sql_mode;

一共有下面这几种模式

来源于 https://www.cnblogs.com/Zender/p/8270833.html

字符串类型

MySQL 提供了很多种字符串类型,下面是字符串类型的汇总

下面我们对这些数据类型做一个详细的介绍

CHAR 和 VARCHAR 类型

CHAR 和 VARCHAR 类型很相似,导致很多同学都会忽略他们之间的差别,首先他俩都是用来保存字符串的数据类型,他俩的主要区别在于存储方式不同。CHAR 类型的长度就是你定义多少显示多少。占用 M 字节,比如你声明一个 CHAR(20) 的字符串类型,那么每个字符串占用 20 字节,M 的取值范围时 0 - 255。VARCHAR 是可变长的字符串,范围是 0 - 65535,在字符串检索的时候,CHAR 会去掉尾部的空格,而 VARCHAR 会保留这些空格。下面是演示例子

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

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

select length(vc),length(ch) from vctest1;

结果如下

可以看到 vc 的字符串类型是 varchar ,长度是 5,ch 的字符串类型是 char,长度是 3。可以得出结论,varchar 会保留最后的空格,char 会去掉最后的空格。

BINARY 和 VARBINARY 类型

BINARY 和 VARBINARY 与 CHAR 和 VARCHAR 非常类似,不同的是它们包含二进制字符串而不包含非二进制字符串。BINARY 与 VARBINARY 的最大长度和 CHAR 与 VARCHAR 是一样的,只不过他们是定义字节长度,而 CHAR 和 VARCHAR 对应的是字符长度。

BLOB 类型

BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

TEXT 类型

有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。

ENUM 类型

ENUM 我们在 Java 中经常会用到,它表示的是枚举类型。它的范围需要在创建表时显示指定,对 1 - 255 的枚举需要 1 个字节存储;对于 255 - 65535 的枚举需要 2 个字节存储。ENUM 会忽略大小写,在存储时都会转换为大写。

SET 类型

SET 类型和 ENUM 类型有两处不同

  • 存储方式

SET 对于每 0 - 8 个成员,分别占用 1 个字节,最大到 64 ,占用 8 个字节

  • Set 和 ENUM 除了存储之外,最主要的区别在于 Set 类型一次可以选取多个成员,而 ENUM 则只能选一个。

MySQL 运算符

MySQL 中有多种运算符,下面对 MySQL 运算符进行分类

  • 算术运算符
  • 比较运算符
  • 逻辑运算符
  • 位运算符

下面那我们对各个运算符进行介绍

算术运算符

MySQL 支持的算术运算符包括加、减、乘、除和取余,这类运算符的使用频率比较高

下面是运算符的分类

运算符作用
+加法
-减法
*乘法
/, DIV除法,返回商
%, MOD除法,返回余数

下面简单描述了这些运算符的使用方法

  • + 用于获得一个或多个值的和
  • - 用于从一个值减去另一个值
  • * 用于两数相乘,得到两个或多个值的乘积
  • / 用一个值除以另一个值得到商
  • % 用于一个值除以另一个值得到余数

在除法和取余需要注意一点,如果除数是 0 ,将是非法除数,返回结果为 NULL。

比较运算符

熟悉了运算符,下面来聊一聊比较运算符,使用 SELECT 语句进行查询时,MySQL 允许用户对表达式的两侧的操作数进行比较,比较结果为真,返回 1, 比较结果为假,返回 0 ,比较结果不确定返回 NULL。下面是所有的比较运算符

运算符描述
=等于
<> 或者是 !=不等于
<=>NULL 安全的等于,也就是 NULL-safe
<小于
<=小于等于
>大于
>=大于等于
BETWEEN在指定范围内
IS NULL是否为 NULL
IS NOT NULL是否为 NULL
IN存在于指定集合
LIKE通配符匹配
REGEXP 或 RLIKE正则表达式匹配

比较运算符可以用来比较数字、字符串或者表达式。数字作为浮点数进行比较,字符串以不区分大小写的方式进行比较。

  • = 号运算符,用于比较运算符两侧的操作数是否相等,如果相等则返回 1, 如果不相等则返回 0 ,下面是具体的示例,NULL 不能用于比较,会直接返回 NULL
  • <> 号用于表示不等于,和 = 号相反,示例如下
  • <=> NULL-safe 的等于运算符,与 = 号最大的区别在于可以比较 NULL 值
  • < 号运算符,当左侧操作数小于右侧操作数时,返回值为 1, 否则其返回值为 0。
  • 和上面同理,只不过是满足 <= 的时候返回 1 ,否则 > 返回 0。这里我有个疑问,为什么
select 'a' <= 'b';  /* 返回 1 */

/*而*/

select 'a' >= 'b'; /* 返回 0 呢*/

关于 >>= 是同理

BETWEEN 运算符的使用格式是 a BETWEEN min AND max ,当 a 大于等于 min 并且小于等于 max 时,返回 1,否则返回 0 。操作数类型不同的时候,会转换成相同的数据类型再进行处理。比如

  • IS NULLIS NOT NULL 表示的是是否为 NULL,ISNULL 为 true 返回 1,否则返回 0 ;IS NOT NULL 同理
  • IN 这个比较操作符判断某个值是否在一个集合中,使用方式是 xxx in (value1,value2,value3)
  • LIKE 运算符的格式是 xxx LIKE %123%,比如如下

当 like 后面跟的是 123% 的时候, xxx 如果是 123 则返回 1,如果是 123xxx 也返回 1,如果是 12 或者 1 就返回 0 。123 是一个整体。

  • REGEX 运算符的格式是 s REGEXP str ,匹配时返回值为 1,否则返回 0 。

后面会详细介绍 regexp 的用法。

逻辑运算符

逻辑运算符指的就是布尔运算符,布尔运算符指返回真和假。MySQL 支持四种逻辑运算符

运算符作用
NOT 或 !逻辑非
AND 或者是 &&逻辑与
OR 或者是 ||逻辑或
XOR逻辑异或

下面分别来介绍一下

  • NOT 或者是 ! 表示的是逻辑非,当操作数为 0(假) ,则返回值为 1,否则值为 0。但是有一点除外,那就是 NOT NULL 的返回值为 NULL
  • AND&& 表示的是逻辑与的逻辑,当所有操作数为非零值并且不为 NULL 时,结果为 1,但凡是有一个 0 则返回 0,操作数中有一个 null 则返回 null
  • OR|| 表示的是逻辑或,当两个操作数均为非 NULL 值时,如有任意一个操作数为非零值,则结果为 1,否则结果为 0。
  • XOR 表示逻辑异或,当任意一个操作数为 NULL 时,返回值为 NULL。对于非 NULL 的操作数,如果两个的逻辑真假值相异,则返回结果 1;否则返回 0。

位运算符

一听说位运算,就知道是和二进制有关的运算符了,位运算就是将给定的操作数转换为二进制后,对各个操作数的每一位都进行指定的逻辑运算,得到的二进制结果转换为十进制后就说是位运算的结果,下面是所有的位运算。

运算符作用
&位与
|位或
^位异或
位取反
>>位右移
<<位左移

下面分别来演示一下这些例子

  • 位与 指的就是按位与,把 & 双方转换为二进制再进行 & 操作

按位与是一个数值减小的操作

  • 位或 指的就是按位或,把 | 双方转换为二进制再进行 | 操作

位或是一个数值增大的操作

  • 位异或 指的就是对操作数的二进制位做异或操作
  • 位取反 指的就是对操作数的二进制位做 NOT 操作,这里的操作数只能是一位,下面看一个经典的取反例子:对 1 做位取反,具体如下所示:

为什么会有这种现象,因为在 MySQL 中,常量数字默认会以 8 个字节来显示,8 个字节就是 64 位,常量 1 的二进制表示 63 个 0,加 1 个 1 , 位取反后就是 63 个 1 加一个 0 , 转换为二进制后就是 18446744073709551614,我们可以使用 select bin() 查看一下

  • 位右移 是对左操作数向右移动指定位数,例如 50 >> 3,就是对 50 取其二进制然后向右移三位,左边补上 0 ,转换结果如下
  • 位左移 与位右移相反,是对左操作数向左移动指定位数,例如 20 << 2

MySQL 常用函数

下面我们来了解一下 MySQL 函数,MySQL 函数也是我们日常开发过程中经常使用的,选用合适的函数能够提高我们的开发效率,下面我们就来一起认识一下这些函数

字符串函数

字符串函数是最常用的一种函数了,MySQL 也是支持很多种字符串函数,下面是 MySQL 支持的字符串函数表

函数功能
LOWER将字符串所有字符变为小写
UPPER将字符串所有字符变为大写
CONCAT进行字符串拼接
LEFT返回字符串最左边的字符
RIGHT返回字符串最右边的字符
INSERT字符串替换
LTRIM去掉字符串左边的空格
RTRIM去掉字符串右边的空格
REPEAT返回重复的结果
TRIM去掉字符串行尾和行头的空格
SUBSTRING返回指定的字符串
LPAD用字符串对最左边进行填充
RPAD用字符串对最右边进行填充
STRCMP比较字符串 s1 和 s2
REPLACE进行字符串替换

下面通过具体的示例演示一下每个函数的用法

  • LOWER(str) 和 UPPER(str) 函数:用于转换大小写
  • CONCAT(s1,s2 … sn) :把传入的参数拼接成一个字符串

上面把 c xu an 拼接成为了一个字符串,另外需要注意一点,任何和 NULL 进行字符串拼接的结果都是 NULL。

  • LEFT(str,x) 和 RIGHT(str,x) 函数:分别返回字符串最左边的 x 个字符和最右边的 x 个字符。如果第二个参数是 NULL,那么将不会返回任何字符串
  • INSERT(str,x,y,instr) : 将字符串 str 从指定 x 的位置开始, 取 y 个长度的字串替换为 instr。
  • LTRIM(str) 和 RTRIM(str) 分别表示去掉字符串 str 左侧和右侧的空格
  • REPEAT(str,x) 函数:返回 str 重复 x 次的结果
  • TRIM(str) 函数:用于去掉目标字符串的空格
  • SUBSTRING(str,x,y) 函数:返回从字符串 str 中第 x 位置起 y 个字符长度的字符串
  • LPAD(str,n,pad) 和 RPAD(str,n,pad) 函数:用字符串 pad 对 str 左边和右边进行填充,直到长度为 n 个字符长度
  • STRCMP(s1,s2) 用于比较字符串 s1 和 s2 的 ASCII 值大小。如果 s1 < s2,则返回 -1;如果 s1 = s2 ,返回 0 ;如果 s1 > s2 ,返回 1。
  • REPLACE(str,a,b) : 用字符串 b 替换字符串 str 种所有出现的字符串 a

数值函数

MySQL 支持数值函数,这些函数能够处理很多数值运算。下面我们一起来学习一下 MySQL 中的数值函数,下面是所有的数值函数

函数功能
ABS返回绝对值
CEIL返回大于某个值的最大整数值
MOD返回模
ROUND四舍五入
FLOOR返回小于某个值的最大整数值
TRUNCATE返回数字截断小数的结果
RAND返回 0 - 1 的随机值

下面我们还是以实践为主来聊一聊这些用法

  • ABS(x) 函数:返回 x 的绝对值
  • CEIL(x) 函数: 返回大于 x 的整数
  • MOD(x,y),对 x 和 y 进行取模操作
  • ROUND(x,y) 返回 x 四舍五入后保留 y 位小数的值;如果是整数,那么 y 位就是 0 ;如果不指定 y ,那么 y 默认也是 0 。
  • FLOOR(x) : 返回小于 x 的最大整数,用法与 CEIL 相反
  • TRUNCATE(x,y): 返回数字 x 截断为 y 位小数的结果, TRUNCATE 知识截断,并不是四舍五入。
  • RAND() :返回 0 到 1 的随机值

日期和时间函数

日期和时间函数也是 MySQL 中非常重要的一部分,下面我们就来一起认识一下这些函数

函数功能
NOW返回当前的日期和时间
WEEK返回一年中的第几周
YEAR返回日期的年份
HOUR返回小时值
MINUTE返回分钟值
MONTHNAME返回月份名
CURDATE返回当前日期
CURTIME返回当前时间
UNIX_TIMESTAMP返回日期 UNIX 时间戳
DATE_FORMAT返回按照字符串格式化的日期
FROM_UNIXTIME返回 UNIX 时间戳的日期值
DATE_ADD返回日期时间 + 上一个时间间隔
DATEDIFF返回起始时间和结束时间之间的天数

下面结合示例来讲解一下每个函数的使用

  • NOW(): 返回当前的日期和时间
  • WEEK(DATE) 和 YEAR(DATE) :前者返回的是一年中的第几周,后者返回的是给定日期的哪一年
  • HOUR(time) 和 MINUTE(time) : 返回给定时间的小时,后者返回给定时间的分钟
  • MONTHNAME(date) 函数:返回 date 的英文月份
  • CURDATE() 函数:返回当前日期,只包含年月日
  • CURTIME() 函数:返回当前时间,只包含时分秒
  • UNIX_TIMESTAMP(date) : 返回 UNIX 的时间戳
  • FROM_UNIXTIME(date) : 返回 UNIXTIME 时间戳的日期值,和 UNIX_TIMESTAMP 相反
  • DATE_FORMAT(date,fmt) 函数:按照字符串 fmt 对 date 进行格式化,格式化后按照指定日期格式显示

具体的日期格式可以参考这篇文章 https://blog.csdn.net/weixin_38703170/article/details/82177837

我们演示一下将当前日期显示为年月日的这种形式,使用的日期格式是 %M %D %Y

  • DATE_ADD(date, interval, expr type) 函数:返回与所给日期 date 相差 interval 时间段的日期

interval 表示间隔类型的关键字,expr 是表达式,这个表达式对应后面的类型,type 是间隔类型,MySQL 提供了 13 种时间间隔类型

表达式类型描述格式
YEARYY
MONTHMM
DAYDD
HOUR小时hh
MINUTEmm
SECONDss
YEAR_MONTH年和月YY-MM
DAY_HOUR日和小时DD hh
DAY_MINUTE日和分钟DD hh : mm
DAY_SECOND日和秒DD hh :mm :ss
HOUR_MINUTE小时和分hh:mm
HOUR_SECOND小时和秒hh:ss
MINUTE_SECOND分钟和秒mm:ss
  • DATE_DIFF(date1, date2) 用来计算两个日期之间相差的天数

查看离 2021 - 01 - 01 还有多少天

流程函数

流程函数也是很常用的一类函数,用户可以使用这类函数在 SQL 中实现条件选择。这样做能够提高查询效率。下表列出了这些流程函数

函数功能
IF(value,t f)If value is true, return t; otherwise return f
IFNULL(value1,value2)If value1 is not NULL, return value1, otherwise return value2.
CASE WHEN[value1] THEN[result1] …ELSE[default] ENDIf value1 is true, return result1, otherwise return default
CASE[expr] WHEN[value1] THEN [result1]… ELSE[default] ENDIf expr is equal to value1, return result1, otherwise return default

Other functions

In addition to the string functions, date and time functions, and process functions that we have introduced, there are some functions that do not belong to the above three types of functions. They are

functionFeatures
VERSIONReturns the version of the current database
DATABASEReturns the current database name
USERReturn the current login user name
PASSWORDReturns the encrypted version of the string
MD5Return MD5 value
INET_ATON(IP)Returns the numeric representation of the IP address
INET_NTOA(num)Return the IP address represented by the number

Let's take a look at the specific use

  • VERSION: Returns the current database version
  • DATABASE: returns the current database name
  • USER: return the currently logged in user name
  • PASSWORD(str): Returns the 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
  • INET_NTOA(num) function: returns the IP address represented by the network byte sequence, as opposed to INET_ATON

I had six PDFs on my own, and they spread more than 10w+ on the Internet. After searching for "programmer cxuan" on WeChat and following the official account, I responded to cxuan in the background and received all the PDFs. These PDFs are as follows

Six PDF links