Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the basic knowledge points in MySQL", the content is simple and clear, and I hope it can help you solve your doubts. Let the editor lead you to study and learn this article "what are the basic knowledge points in MySQL".

A brief introduction of SQL and an overview of 1.SQL

Structure Query Language (structured query language) is referred to as SQL, which is determined by the American National Standards Institute (ANSI) as the American standard of relational database language, and then adopted as the international standard of relational database language by the International Standards Organization (ISO). The database management system can manage the database through SQL, define and operate data, and maintain the integrity and security of the data.

Advantages of 2.SQL

1. Easy to learn, with strong maneuverability

2. Most important database management systems support SQL.

3. Highly non-procedural; when operating the database with SQL, most of the work is done automatically by DBMS

Classification of 3.SQL

1. DDL (Data Definition Language) data definition language, which is used to operate databases, tables, columns, etc.; commonly used statements: CREATE, ALTER, DROP

2. DML (Data Manipulation Language) data manipulation language, which is used to manipulate the data in the table in the database; commonly used sentences: INSERT, UPDATE, DELETE

3. DCL (Data Control Language) data control language, which is used to operate access rights and security level; commonly used statements: GRANT, DENY

4. DQL (Data Query Language) data query language, which is commonly used to query data: SELECT

Second, the three paradigms of database

1. The first normal form (1NF) means that each column of a database table is an indivisible basic data line; that is to say, the value of each column is atomic and can no longer be divided.

2. The second normal form (2NF) is established on the basis of the first normal form (1NF). To meet the second normal form (2NF), it must first satisfy the first normal form (1NF). If the table is a single primary key, then columns other than the primary key must be completely dependent on the primary key; if the table is a compound primary key, then columns other than the primary key must be completely dependent on the primary key, not just a portion of the primary key.

3. The third paradigm (3NF) is established on the basis of the second paradigm, that is, to meet the third paradigm, we must first meet the second paradigm. The third normal form (3NF) requires that non-primary key columns in a table must be directly related to the primary key rather than indirectly; that is, non-primary key columns cannot be dependent on each other.

3. Data types of the database

When using MySQL database to store data, different data types determine how MySQL stores data. To this end, the MySQL database provides a variety of data types, including integer types, floating-point types, fixed-point types, date and time types, string types, binary... Wait, data type.

1. Integer type

According to the value range of different MySQL, the integer types can be divided into five types, which are TINYINT, SMALUNT, MEDIUMINT, INT and BIGINT. The following figure lists the byte size and value range corresponding to different integer types of MySQL, and the most commonly used is the INT type.

The value range of data type byte number without sign number is TINYINT10~255-128~127SMALLINT20~65535-32768~32768MEDIUMINT30~16777215-8388608~8388608INT40~4294967295-2147483648 ~ 2147483648BIGINT80~18446744073709551615-922337203685477580882233720368547758082. Floating point number type and fixed point number type

Use floating-point and fixed-point numbers to store decimals in MySQL databases. There are two types of floating-point numbers: single-precision floating-point numbers (FLOAT) and double-precision floating-point numbers (DOUBLE). There is only one fixed-point number type, that is, the DECIMAL type. The following figure lists the byte size and value range corresponding to floating-point and fixed-point types in MySQL:

Data type byte number signed value range unsigned value range FLOAT4-3.402823466E+38~-1.175494351E-380 and 1.175494351E-38~3.402823466E+38DOUBLE8-1.7976931348623157E+308~2.2250738585072014E-3080 and 2.2250738585072014E-308~1.7976931348623157E+308DECIMAL (MMagazine D) Maure2-1.7976931348623157E+308~2.2250738585072014E-3080 and 2.2250738585072014E-308~1.7976931348623157E+308

As you can see from the figure above, the value range of the DECIMAL type is the same as that of the DOUBLE type. Note, however, that the range of valid values for the DECIMAL type is determined by M and D. Where M represents the length of the data and D represents the length after the decimal point. For example, the result of inserting data 6.5243 of data type DECIMAL (6jin2) into the database is 6.52.

3. String type

CHAR and VARCHAR are often used to represent strings in MySQL. The difference between the two is that VARCHAR stores strings of variable length.

When the data is of type CHAR (M), its storage space is M bytes regardless of the actual length of the inserted value, while the number of bytes occupied by the data corresponding to VARCHAR (M) is the actual length plus 1.

Insert value CHAR (3) Storage requirements VARCHAR (3) Storage requirements''3 bytes''1 byte 'a''a'3 byte' a 'ab''ab'3 byte' ab'3 byte 'abc''ab'3 byte' abc'4 byte 'abcd''ab'3 byte' abc'4 byte 4. String type

Text types are used to represent large text data, such as article content, comments, details, and so on. There are four types of text types:

Data type storage range TINYTEXT0~255 bytes TEXT0~65535 bytes MEDIUMTEXT0~16777215 bytes LONGTEXT0~4294967295 bytes 5. Date and time type

The date and time data types provided by MySQL are: YEAR, DATE, TIME, DATETIME, and TIMESTAMP. The following figure lists the number of bytes, value range, date format, and zero values for date and time data types:

Data type byte value range date format zero value YEAR11901~2155YYYY0000DATE41000-01-01 "9999-12-31YYYY-MM-DD0000-00-00TIME3-838 838:59:59HH:MM:SS00:00:00DATETIME81000 59 838:59:59HH:MM:SS00:00:00DATETIME81000-01 0012 23:59:59YYYY-MM-DD HH:MM:SS0000-00-0000: 00:00TIMESTAMP41970-01-01 00001 23:59:59YYYY-MM-DD HH:MM:SS0000-00-0000: 012038-01-19 03:14:07YYYY-MM-DD HH:MM:SS0000-00-0000: 00Flux 005.1 YEAR type

The YEAR type is used to represent the year, and in MySQL, the value of the YEAR type can be specified in the following three formats.

1. Use a 4-digit string or number, with a range of '1901mm color 2155' or 1901-2155. For example, input '2019' or 2019 inserts a value of 2019 into the database.

2. It is represented by a two-digit string with a range of'00 characters'. Among them, the value in the range of '00percent' will be converted to the value of year in the range of 2000-2069, and the value in the range of '70percent color will be converted to the value of YEAR in the range of 1970-1999. For example, the value of input '19' into the database is 2019.

3. Use a two-digit representation with a range of 1-99. Values in the range 1-69 are converted to year values in the range 2001-2069, and values in the range 70-99 are converted to year values in the range 1970-1999. For example, input 19 inserts a value of 2019 into the database.

Note that when using the YEAR type, be sure to distinguish between'0' and 0. Because the value of year represented by'0' in string format is 2000 and that of 0 in numeric format is 0000.

5.2 TIME Typ

The TIME type is used to represent the time value, and it is generally displayed in the form of HH:MM:SS, where HH represents hours, MM represents minutes, and SS represents seconds. In MySQL, you can specify values of type TIME using the following three formats.

1. Expressed in the format of'D HH:MM:SS' string. Where D represents a daily value between 0 and 34, and when inserting data, the value of the hour is equal to (DX24+HH). For example, enter'2 11 30 15'to insert the date in the database as 59:30:50.

2. It is expressed in 'HHMMSS' string format or HHMMSS number format. For example, if you enter '115454' or 115454, the date inserted into the database is 11:54:54

3. Use CURRENT_TIME or NOW () to enter the current system time.

5.3 DATETIME Typ

The DATETIME type is used to represent the date and time, and it is displayed in the form of 'YYYY-MM-DD HH: MM:SS', where YYYY represents the year, MM represents the month, DD represents the day, HH represents the hour, MM represents minutes, and SS represents seconds. In MySQL, you can specify values of type DATETIME using the following four formats.

The date and time of the in the format of 'YYYY-MM-DD HH:MM:SS' or' YYYYMMDDHHMMSS' string. The range of values is' 1000-01-01 00 YYYYMMDDHHMMSS'. For example, the DATETIME value in the input database of '2019-01-22 09-01-22' or '20140-122' is 2019-01-22 09:01:23.

1. The date and time expressed in the format of 'YY-MM-DD HH:MM:SS'' or 'YYMMDDHHMMSS' string, where YY represents the year, and the value range is' 00 years or 99'. Like YY in the DATE type, values in the range of'00'-'69 'are converted to values in the range of 2000-2069, and values in the range of' 70 'are converted to values in the range of 1970-1999.

2. Date and time in YYYYMMDDHHMMSS or YYMMDDHHMMSS numeric format. For example, if you insert 20190122090123 or 190122090123, the DATETIME value in the insert database is 2019-01-2209: 01:23.

3. Use NOW to input the date and time of the current system.

5.4 TIMESTAMP Typ

The TIMESTAMP type is used to represent the date and time, and it is displayed in the same form as DATETIME but with a smaller range of values than DATETIME. Here, we introduce several different forms of TIMESTAMP types from DATATIME types:

1. Use CURRENT_TIMESTAMP to input the current date and time of the system.

2. When you input NULL, the system will input the current date and time of the system.

3. When there is no input, the system will enter the current date and time of the system.

6. Binary type

In MySQL, BLOB is commonly used to store binary data, such as pictures, PDF documents and so on. There are four types of BLOB:

Data type storage range TINYBLOB0~255 byte BLOB0~65535 byte MEDIUMBLOB0~16777215 byte LONGBLOB0~4294967295 byte 4, database, data table basic operation 1. Basic operation of database

After the MySQL installation is complete, to store the data in a table in the database, first create a database. To create a database is to divide a space in the database system to store data. The syntax is as follows:

Create database database name

Create a database MySQL command called db1:

-- create a database called db1 show create database db1

Show the running effect:

View the basic information of the database after creating the database MySQL command:

Show create database db1

Show the running effect:

Delete database MySQL command:

Drop database db1

Show the running effect:

Query all the database MySQL commands in MySQL:

Show databases

Show the running effect:

Change the character set of the database to the gbk MySQL command:

Alter database db1 character set gbk

Show the running effect:

Toggle database MySQL command:

Use db1

Show the running effect:

View the database MySQL commands currently in use:

Select database ()

Show the running effect:

two。 Basic operation of data table

After the database is created successfully, you can create data tables (referred to as tables for short) in the database to store data. Note that you should use the "USE database name;" before manipulating the data table; specify in which database the operation is performed first, otherwise a "No database selected" error will be thrown.

The syntax is as follows:

Create table table name (field 1 field type, field 2 field type, … Field n field type); 2.1 create a data table

Example: create a student table MySQL command:

Create table student (id int, name varchar (20), gender varchar (10), birthday date)

Show the running effect:

2.2 View the data sheet

Example: view all tables in the current database MySQL command:

Show tables

Show the running effect:

Example: look up the basic information of the table MySQL command:

Show create table student

Show the running effect:

Example: view the field information of a table MySQL command:

Desc student

Show the running effect:

2.3 modify the data sheet

Sometimes, you want to modify some information in the table, such as: modify the table name, modify the field name, modify the field data type. Wait. Use alter table to modify the data table in MySQL.

Example: modify the table name MySQL command:

Alter table student rename to stu

Show the running effect:

Example: modify the field name MySQL command:

Alter table stu change name sname varchar (10)

Show the running effect:

Example: modify field data type MySQL command:

Alter table stu modify sname int

Show the running effect:

Example: add field MySQL command:

Alter table stu add address varchar (50)

Show the running effect:

Example: delete field MySQL command:

Alter table stu drop address

Show the effect of transportation:

2.4 Delete the data table

Syntax:

Drop table table name

Example: delete Datasheet MySQL command:

Drop table stu

Show the running effect:

Fifth, the constraint of the data table

To prevent incorrect data from being inserted into a data table, MySQL defines rules that maintain database integrity; these rules are often referred to as table constraints. Common constraints are as follows:

The constraint condition states that the PRIMARY KEY primary key constraint is used to uniquely identify the corresponding record FOREIGN KEY foreign key constraint NOT NULL non-empty constraint UNIQUE uniqueness constraint default value constraint, which is used to set the default value of the field

The above five constraints restrict the fields in the table to ensure the correctness and uniqueness of the data in the data table. In other words, the constraints of the table are actually the constraints of the data in the table.

1. Primary key constraint

The primary key constraint is the primary key used to uniquely identify each row in the table. The data identified as the primary key is unique in the table and its value cannot be empty. This is similar to the fact that each of us has an ID number, and this ID number is unique.

Basic syntax for primary key constraints:

Field name data type primary key

The first way to set primary key constraints (primary key)

Example: MySQL command:

Create table student (id int primary key,name varchar (20))

Show the running effect:

The second way to set primary key constraints (primary key)

Example: MySQL command:

Create table student01 (id intname varchar (20), primary key (id))

Show the running effect:

two。 Non-empty constraint

A non-null constraint, or NOT NULL, means that the value of a field cannot be empty. The basic syntax format is as follows:

Field name data type NOT NULL

Example: MySQL command:

Create table student02 (id intname varchar (20) not null)

Show the running effect:

3. Default value constraint

The default value constraint, that is, DEFAULT is used to specify the default value for the field in the data table, that is, if the field is not assigned a value when a new record is inserted in the table, the database system automatically inserts the default value for the field. The basic syntax format is as follows:

Field name data type DEFAULT default

Example: MySQL command:

Create table student03 (id int,name varchar (20), gender varchar (10) default 'male')

Show the running effect:

5. Uniqueness constraint

The uniqueness constraint, that is, UNIQUE is used to ensure the uniqueness of the fields in the data table, that is, the values of the fields in the table cannot be repeated. The basic syntax format is as follows:

Field name data type UNIQUE

Example: MySQL command:

Create table student04 (id int,name varchar (20) unique)

Show the running effect:

6. Foreign key constraint

Foreign key constraints, that is, FOREIGN KEY are often used for constraints between multiple tables. The basic syntax is as follows:

The syntax for creating a data table is as follows: CONSTRAINT foreign key name FOREIGN KEY (slave table foreign key field) REFERENCES master table (primary key field)-- the syntax after creating the data table is as follows: ALTER TABLE slave table name ADD CONSTRAINT foreign key name FOREIGN KEY (slave table foreign key field) REFERENCES master table (primary key field)

Example: create a student table MySQL command:

Create table student05 (id int primary key,name varchar (20))

Example: create a class table MySQL command:

Create table class (classid int primary key,studentid int)

Example: student table as primary table, class table as secondary table to set foreign key, MySQL command:

Alter table class add constraint fk_class_studentid foreign key (studentid) references student05 (id)

Show the running effect:

6.1 data consistency concept

As we all know: the establishment of foreign keys is to ensure the integrity and unity of the data. But what if the data in the master table is deleted or the corresponding data in the slave table is modified? Obviously, the corresponding data from the table should also be deleted, otherwise there will be a lot of meaningless junk data in the database.

6.2 Delete foreign keys

The syntax is as follows:

Alter table from table name drop foreign key foreign key name

Example: delete foreign key MySQL command:

Alter table class drop foreign key fk_class_studentid

Show the running effect:

The field of the foreign key is gone, which proves that the deletion was successful.

6.3 details to be paid attention to on foreign key constraints

1. The foreign key in the slave table is usually the primary key of the primary table

2. The data type of the foreign key in the slave table must be the same as that of the primary key in the primary table.

3. when the master table changes, attention should be paid to the data consistency between the master table and the slave table.

VI. Insert data into the data table

In MySQL, insert data into the data table through the INSERT statement. Here, we first prepare a student table, the code is as follows:

Create table student (id int, name varchar (30), age int, gender varchar (30)); Insert data for all fields in the table

Each field corresponds strictly to its value. That is to say: each value, the order of values, and the type of values must match the corresponding fields. However, the fields do not need to be in the same order as they are defined in the table, as long as they are in the same order as the values in VALUES.

The syntax is as follows:

INSERT INTO table name (field name 1, field name 2...) VALUES (value 1, value 2, etc.)

Example: insert a student information MySQL command into the student table:

Insert into student (id,name,age,gender) values (1 recounted bobbles, 16)

Show the running effect:

two。 Insert data for the specified field in the table

The syntax is as follows:

INSERT INTO table name (field name 1, field name 2...) VALUES (value 1, value 2, etc.)

The method of inserting data is basically the same as inserting data for all fields in a table, except that you specify the fields that need to be inserted.

3. Insert multiple records at the same time

The syntax is as follows:

INSERT INTO table name [(field name 1, field name 2...)] VALUES (value 1, value 2,...) , (value 1, value 2,...) ,...

In this way: (field name 1, field name 2, …) Is optional, which is used to specify the field name of the inserter; (value 1, value 2,...) , (value 1, value 2,...) Represents a record to be inserted, which can have multiple entries and each record is separated by a comma.

Example: insert multiple student information MySQL commands into the student table:

Insert into student (id,name,age,gender) values (2), (3)), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3,), (3,), (3,), (3), (3, (),

Show the running effect:

Update data

Update the data in the data table through the UPDATE statement in MySQL. Here, we will use the student student form of No. 6 Middle School.

1. UPDATE basic syntax UPDATE table name SET field name 1 = value 1 [, field name 2 = value 2,...] [WHERE conditional expression]

In this syntax: field name 1, field name 2. Used to specify the name of the field to update; value 1, value 2... Used to represent the new data of the field; the WHERE conditional expression is optional and is used to specify the conditions to be met for updating the data

2. UPDATE updates part of the data

Example: set the age of a record whose name is tom to 20 and its gender to the female MySQL command:

Update student set age=20,gender='female' where name='tom'

Show the running effect:

3. UPDATE updates all data

Example: set the age for all records to 18 MySQL command:

Update student set age=18

Show the running effect:

8. Delete data

Delete the data in the data table through the DELETE statement in MySQL. Here, let's prepare a data table with the following code:

-- create student table create table student (id int, name varchar (30), age int, gender varchar (30)) -- insert data insert into student (id,name,age,gender) values (2) values (2), (3) jackhammer (19)), (4) (4) (4) (18), (5) (5) (6) (20)), (7) sadistic (13)), (1) DELETE basic grammar.

In this syntax: the table name is used to specify the table to delete; [WHERE conditional expression] is an optional parameter to specify the condition for deletion.

DELETE FROM table name [WHERE conditional expression]; 2. DELETE deletes some data

Example: delete all records with age equal to 14 MySQL command:

Delete from student where age=14

Show the running effect:

3. DELETE deletes all data

Example: delete all records in the student table MySQL command:

Delete from student

Show the running effect:

4. The difference between TRUNCATE and DETELE

Both TRUNCATE and DETELE can delete all data in a table, but there is a difference between them:

1. The DELETE statement can be followed by the WHERE clause, and only some records that meet the conditions can be deleted by specifying the conditional expression in the WHERE clause; however, the TRUNCATE statement can only be used to delete all records in the table.

2. After deleting the data in the table using the truncate statement, the default initial value of the automatically increasing field starts with 1 when adding records to the table again; after deleting all records in the table using the delete statement, the value of the automatically added field when adding records to the table again is the maximum value of the field when deleted plus 1.

3. The DELETE statement is the DML statement, and the TRUNCATE statement is usually regarded as the DDL statement.

9. Simple query of MySQL data sheet 1. Overview of simple query

A simple query is a select statement without where. Here, we talk about the two most commonly used queries in simple queries: query all fields and query specified fields.

Here, prepare the test data first, the code is as follows:

-- create database DROP DATABASE IF EXISTS mydb;CREATE DATABASE mydb;USE mydb;-- create student table CREATE TABLE student (sid CHAR (6), sname VARCHAR (50), age INT, gender VARCHAR (50) DEFAULT 'male');-- insert data INSERT INTO student (sid,sname,age,gender) VALUES into student table (' INSERT INTO student 1001, 'lili', 14,' male') INSERT INTO student (sid,sname,age,gender) VALUES ('Sparks 1002,' wang', 15, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Seven 1003, 'tywd', 16,' male'); INSERT INTO student (sid,sname,age,gender) VALUES ('Seven 1004,' hfgs', 17, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Seven 1005, 'qwer', 18,' male') INSERT INTO student (sid,sname,age,gender) VALUES ('Sparks 1006,' zxsd', 19, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Smiles 1007, 'hjop', 16,' male'); INSERT INTO student (sid,sname,age,gender) VALUES ('Seven 1008,' tyop', 15, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Smiles 1009, 'nhmk', 13,' male') INSERT INTO student (sid,sname,age,gender) VALUES ('xdfv', 1010,' xdfv', 17, 'female'); 2. Query all fields (the method is not only an example)

Query all fields MySQL command:

Select * from student

Show the running effect:

3. Query specified fields (sid, sname)

Query the specified fields (sid, sname) MySQL command:

Select sid,sname from student

Show the running effect:

4. Query of constants

In SELECT, in addition to column names, you can also write constants. Can be used to mark

Query date mark for constant MySQL command:

Select sid,sname,'2021-03-02 'from student

Show the running effect:

5. Filter duplicate data from query results

Note when using DISTINCT:

The DISTINCT keyword can only be used before the first column name in a SELECT query statement.

MySQL command:

Select distinct gender from student

Show the running effect:

6. Arithmetic operator (example plus operator)

The addition, subtraction, multiplication and division operators can also be used in SELECT query statements.

Inquire about the age of the students after 10 years MySQL command:

Select sname,age+10 from student

Show the running effect:

10. Function

Here, prepare the test data first, the code is as follows:

-- create database DROP DATABASE IF EXISTS mydb;CREATE DATABASE mydb;USE mydb;-- create student table CREATE TABLE student (sid CHAR (6), sname VARCHAR (50), age INT, gender VARCHAR (50) DEFAULT 'male');-- insert data INSERT INTO student (sid,sname,age,gender) VALUES into student table (' INSERT INTO student 1001, 'lili', 14,' male') INSERT INTO student (sid,sname,age,gender) VALUES ('Sparks 1002,' wang', 15, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Seven 1003, 'tywd', 16,' male'); INSERT INTO student (sid,sname,age,gender) VALUES ('Seven 1004,' hfgs', 17, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Seven 1005, 'qwer', 18,' male') INSERT INTO student (sid,sname,age,gender) VALUES ('Sparks 1006,' zxsd', 19, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Smiles 1007, 'hjop', 16,' male'); INSERT INTO student (sid,sname,age,gender) VALUES ('Seven 1008,' tyop', 15, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Smiles 1009, 'nhmk', 13,' male') INSERT INTO student (sid,sname,age,gender) VALUES ('xdfv', 1010,' xdfv', 17, 'female'); 1. Aggregate function

In development, we often have similar requirements: counting the maximum, minimum, average, and so on of a field. For this reason, aggregate functions are provided in MySQL to implement these functions. Aggregation is the aggregation of multiple lines into one line; in fact, this is the case with all aggregate functions-input multiple lines, output one line. The aggregate function has the function of automatically filtering. If a value is NULL, it will be automatically filtered so that it does not participate in the operation.

Aggregate function usage rules:

Aggregate functions can only be used in the SELECT clause, the HAVING clause and the ORDER BY clause. For example, it is wrong to use aggregate functions in the WHERE clause.

Next, let's learn about common aggregate functions.

1.1, count ()

The number of rows of data in a statistical table or the number of data in a specified column whose value is not NULL

Query how many people are in the table

MySQL command:

Select count (*) from student

Show the running effect:

1.2, max ()

Calculates the maximum value of the specified column and uses a string sort operation if the specified column is a string type

Query the oldest student in the student table

MySQL command:

Select max (age) from student

Show the running effect:

1.3The min ()

Calculates the minimum value of the specified column and uses a string sort operation if the specified column is a string type

Query the MySQL command for the youngest student in the student table:

Select sname,min (age) from student

Show the running effect:

1.4, sum ()

Calculates the numeric sum of the specified column, and the result is 0 if the specified column type is not a numeric type

The MySQL command to query the sum of ages in the student table:

Select sum (age) from student

Show the running effect:

1.5. Avg ()

Calculates the average of the specified column. If the specified column type is not a numeric type, the result is

The MySQL command to query the average age in the student table:

Select avg (age) from student

Show the running effect:

two。 Other common functions

I'm not going to give examples here. I basically look familiar, and I'll talk about it in more detail later.

2.1.Time function SELECT NOW (); SELECT DAY (NOW ()); SELECT DATE (NOW ()); SELECT TIME (NOW ()); SELECT YEAR (NOW ()); SELECT MONTH (NOW ()); SELECT CURRENT_DATE (); SELECT CURRENT_TIME (); SELECT CURRENT_TIMESTAMP (); SELECT ADDTIME ('1423 NOW 12); NOW (), INTERVAL 1 DAY); SELECT DATE_ADD (NOW (), INTERVAL 1 MONTH) SELECT DATE_SUB (NOW (), INTERVAL 1 DAY); SELECT DATE_SUB (NOW (), INTERVAL 1 MONTH); SELECT DATEDIFF ('2019-07-22); string function-- concatenation function SELECT CONCAT ()-- SELECT INSTR ();-- Statistical length SELECT LENGTH (); 2.3.Mathematical function-- absolute value SELECT ABS (- 136);-- rounding down SELECT FLOOR (3.14) -- round up SELECT CEILING (3.14); 11. Conditional query

There is a large amount of data in the database, and we can obtain the specified data according to the requirements. At this point, we can filter the query results by specifying query conditions in the query statement through the WHERE clause.

Before we start the learning condition query, let's prepare the test data as follows:

-- create database DROP DATABASE IF EXISTS mydb;CREATE DATABASE mydb;USE mydb;-- create student table CREATE TABLE student (sid CHAR (6), sname VARCHAR (50), age INT, gender VARCHAR (50) DEFAULT 'male');-- insert data INSERT INTO student (sid,sname,age,gender) VALUES into student table (' INSERT INTO student 1001, 'lili', 14,' male') INSERT INTO student (sid,sname,age,gender) VALUES ('Sparks 1002,' wang', 15, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Seven 1003, 'tywd', 16,' male'); INSERT INTO student (sid,sname,age,gender) VALUES ('Seven 1004,' hfgs', 17, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Seven 1005, 'qwer', 18,' male') INSERT INTO student (sid,sname,age,gender) VALUES ('Sparks 1006,' zxsd', 19, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Smiles 1007, 'hjop', 16,' male'); INSERT INTO student (sid,sname,age,gender) VALUES ('Seven 1008,' tyop', 15, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' Smiles 1009, 'nhmk', 13,' male') INSERT INTO student (sid,sname,age,gender) VALUES ('Seven 1010,' xdfv', 17, 'female'); INSERT INTO student (sid,sname,age,gender) VALUES (' lili', 1012, 'lili', 14,' male'); INSERT INTO student (sid,sname,age,gender) VALUES ('Seven 1013,' wang', 15, 'female'); 1. Query using relational operators

You can use relational operators to make conditional queries in WHERE, and the common relational operators are as follows:

The relational operator states that = equal to not equal to! = not equal to greater than or equal to

Query information for students aged 17 or older MySQL command:

Select * from student where age > = 17

Show the running effect:

two。 Query using the IN keyword

The IN keyword is used to determine whether the value of a field is in the specified collection. If the value of the field happens to be in the specified collection, the record in which the field is located will be queried.

Query the MySQL command for student information whose sid is Shop 1002 and Shop 1003:

Select * from student where sid in ('Sparks 1002', 'Shop 1003')

Show the running effect:

Query the MySQL command for information about students whose sid is not Shop 1001:

Select * from student where sid not in ('Shop 1001')

Show the running effect:

3. Query using the BETWEEN AND keyword

BETWEEN AND is used to determine whether the value of a field is within the specified range. If the value of the field is within the specified range, the record will be queried.

MySQL command for student information from 15 to 18 years old:

Select * from student where age between 15 and 18

Show the running effect:

MySQL command to query student information that is not 15 to 18 years old:

Select * from student where age not between 15 and 18

Show the running effect:

4. Use null value query

In MySQL, use the IS NULL keyword to determine whether the value of the field is null. Please note that the null value NULL is different from 0 or an empty string

Since there is no null value in the student table, the query null value will not be demonstrated.

MySQL command to query student information for which sname is not null:

Select * from student where sname is not null

Show the running effect:

5. Query using the AND keyword

In MySQL, you can use the AND keyword to join two or more query conditions.

Query the MySQL command for information about students over 15 years of age and gender male:

Select * from student where age > 15 and gender='male'

Show the running effect:

6. Query using the OR keyword

You can use the OR keyword to connect multiple query conditions when querying data using a select statement. When using the OR keyword, as long as the record meets any of these conditions, it will be queried

Query the MySQL command for student information over 15 or gender male:

Select * from student where age > 15 or gender='male'

Show the running effect:

7. Query using the LIKE keyword

The LIKE keyword can be used in MySQL to determine whether two strings match.

7.1 normal string

Query the student information matching wang in sname MySQL command:

Select * from student where sname like 'wang'

Show the running effect:

7.2 contains a string of% wildcards

% is used to match strings of any length. For example, the string "a%" matches a string of any length starting with the character a

Query the record MySQL command that starts with li for student names:

Select * from student where sname like 'li%'

Show the running effect:

The MySQL command to query the records whose student names end in g:

Select * from student where sname like'% g'

Show the running effect:

The MySQL command to query the records with s in the student name:

Select * from student where sname like'% s%'

Running effect display

7.3 string containing _ wildcard

Underscore wildcards match only a single character, and if you want to match multiple characters, you need to use multiple underscore wildcards in succession. For example, the string "ab_" matches a string of length 3 that starts with the string "ab", such as abc, abp, and so on; the string "axiomatic d" matches a string that contains two characters between the characters "a" and "d", such as "abcd", "atud", and so on.

The MySQL command to query a record whose student name begins with zx and has a length of 4:

Select * from student where sname like 'zx__'

Running effect display

The MySQL command to query a record whose student name ends in g and has a length of 4:

Select * from student where sname like'_ g'

Running effect display

8. Use LIMIT to limit the number of query results

When querying data, many records may be returned, and the data that the user needs may be only one or more of them.

Query the MySQL command for the three youngest students in the student table:

Select * from student order by age asc limit 3

Running effect display

9. Use GROUP BY for grouping queries

The GROUP BY clause can group the data in the table just like cutting a cake, and then perform operations such as queries. In other words, it can be understood as: the original table is split into several small tables through GROUP BY.

Next, we start to learn GROUP BY with an example, the code is as follows

-- create a database DROP DATABASE IF EXISTS mydb;CREATE DATABASE mydb;USE mydb;--; create an employee table CREATE TABLE employee (id int, name varchar (50), salary int, departmentnumber int);-- insert data INSERT INTO employee values into the employee table (1mcmltomega pr 2000 1001); INSERT INTO employee values (2mcmlc; 9000mr.1002); INSERT INTO employee values (3c); INSERT INTO employee values (5000m 1003). INSERT INTO employee values; 9. 1 GROUP BY and aggregate function.

Count the number of employees in each department MySQL command:

Select count (*), departmentnumber from employee group by departmentnumber

Running effect display

Count the number of employees in each department with a department number greater than 1001 MySQL command:

Select count (*), departmentnumber from employee where departmentnumber > 1001 group by departmentnumber

Running effect display

9.2 GROUP BY is used with aggregate functions and HAVING

MySQL commands for departments whose total statistical wages are greater than 8000:

Select sum (salary), departmentnumber from employee group by departmentnumber having sum (salary) > 8000

Running effect display

10. Use ORDER BY to sort query results

The data queried from the table may be out of order or not in the order we expect. To do this, we can use ORDER BY to sort the query results

The syntax format is as follows:

SELECT field name 1, field name 2, … FROM table name ORDER BY field name 1 [ASC], field name 2 [ASC | DESC]

In this syntax: field name 1 and field name 2 are the basis for sorting query results; the parameter ASC indicates that it is sorted in ascending order and DESC indicates that it is sorted in descending order; by default, it is sorted in ASC mode. Typically, the ORDER BY clause is at the end of the entire SELECT statement.

Query all students and sort the MySQL commands in ascending order of age:

Select * from student order by age asc

Running effect display

Query all students and sort the MySQL commands in descending order of age:

Select * from student order by age desc

Running effect display

XII. Alias setting

When querying data, you can alias the table and field, which participates in the query operation instead of the original name of the table and field.

The schedule of operation has been prepared in advance.

1. Alias the table

In the query operation, if the table name is too long to use, you can choose an alias for the table and use that alias instead of the table name. The syntax format is as follows:

SELECT * FROM table name [AS] table alias WHERE.

Change student to stu query entire table MySQL command:

Select * from student as stu

Running effect display

two。 Alias the field

In the query operation, if the field name is very long, it is not convenient to use, at this time, the field can take an alias and use the alias instead of the field name. The syntax format is as follows:

SELECT field name 1 [AS] alias 1, field name 2 [AS] alias 2,... FROM table name WHERE...

Alias the name in student as the name query entire table MySQL command:

Select name as' name', id from student

Running effect display

XIII. The relationship between tables

In the actual development, there are all kinds of relationships between the data tables. Here, three kinds of association relationships of data tables in MySQL are introduced.

Many to one

Many-to-one (also known as one-to-many) is the most common relationship in data tables. For example: the relationship between employees and departments, a department can have multiple employees, while an employee can not belong to multiple departments only belong to one department. In a many-to-one table relationship, foreign keys should be built on more than one side, otherwise it will result in data redundancy.

Many to many

Many-to-many is a common relationship in data tables. For example: the relationship between students and teachers, a student can have multiple teachers and a teacher can have multiple students. In general, in order to achieve this relationship, you need to define an intermediate table (also known as a join table) that has two foreign keys that refer to the teacher table and the student table.

one-for-one

During development, one-to-one relationships are not common in databases; information stored in this way is usually placed in the same table.

Next, let's learn how to add and delete data in an one-to-many relationship. First prepare some test data, the code is as follows:

DROP TABLE IF EXISTS student;DROP TABLE IF EXISTS class;-- create class table CREATE TABLE class (cid int (4) NOT NULL PRIMARY KEY, cname varchar (30));-- create student table CREATE TABLE student (sid int (8) NOT NULL PRIMARY KEY, sname varchar (30), classid int (8) NOT NULL);-- add foreign key constraint ALTER TABLE student ADD CONSTRAINT fk_student_classid FOREIGN KEY (classid) REFERENCES class (cid) to student table Insert data into the class table INSERT INTO class (cid,cname) VALUES (1); INSERT INTO class (cid,cname) VALUES (2); insert data INSERT INTO student (sid,sname,classid) VALUES (1) into the student table; INSERT INTO student (sid,sname,classid) VALUES (2); INSERT INTO student (sid,sname,classid) VALUES (3) INSERT INTO student (sid,sname,classid) VALUES (4); 1. Association query

Query all students in the Java class with the MySQL command:

Select * from student where classid= (select cid from class where cname='Java')

Running effect display

two。 Delete data about associative relationships

Please delete the Java class from the class table. Here, please note that there is an association between the class table and the student table; to delete a Java class, you should first delete the students associated with that class in the student table. Otherwise, if the Java class is deleted first, then the cid in the student table will lose its relevance.

Delete the Java class MySQL command:

Delete from student where classid= (select cid from class where cname='Java'); delete from class where cname='Java'

Running effect display

Fourteen, multi-table join query 1. Cross join query

The result returned by the cross join is the Cartesian product of all the rows of data in the two joined tables; for example: set A = {aforce b}, set B = {0pr 1) 2}, then the Cartesian product of sets An and B is {(aforce 0), (a force 1), (a minute 2), (b line 0), (b rect 1), (b p 2)}. Therefore, a cross connection is also called a Cartesian join, and its syntax format is as follows:

SELECT * FROM Table 1 CROSS JOIN Table 2

In this syntax: CROSS JOIN is used to join two tables to be queried, and this statement allows you to query all the data combinations in the two tables.

Since this cross-join query does not make any sense in practical application, it can only be used as an understanding.

two。 Internal join query

Inner join (Inner Join), also known as simple join or natural join, is a very common join query. The inner join uses the comparison operator to compare the data in the two tables and list the data rows that match the join conditions to form a new record. In other words, only the records that meet the criteria in the inner join query can appear in the query results. The syntax format is as follows:

SELECT query field 1, query field 2,... FROM Table 1 [INNER] JOIN Table 2 ON Table 1. Relation field = Table 2. Relation field

In this syntax: INNER JOIN is used to join two tables, and ON is used to specify join conditions; where INNER can be omitted.

Prepare the data as follows:

Delete DROP DATABASE IF EXISTS mydb;-- create database mydbCREATE DATABASE mydb;-- select database mydbUSE mydb;-- create department table CREATE TABLE department (did int (4) NOT NULL PRIMARY KEY, dname varchar (20)) if database mydb exists;-- create employee table CREATE TABLE employee (eid int (4) NOT NULL PRIMARY KEY, ename varchar (20), eage int (2), departmentid int (4) NOT NULL) -- inserting data into the department table INSERT INTO department VALUES (1001 'Finance Department'); INSERT INTO department VALUES (1002 'Technical Department'); INSERT INTO department VALUES (1003 'Administration'); INSERT INTO department VALUES (1004 'living Department');-- inserting data into the employee table (INSERT INTO employee VALUES (1) 'Zhang San', 19 ~ 1003); INSERT INTO employee VALUES (2'Li Si', 18 ~ 1002); INSERT INTO employee VALUES (3 'Wang Wu', 20 ~ 1001) INSERT INTO employee VALUES (4pr 'Zhao Liu', 20pr 1004)

Query the employee name and the name of the department to which it belongs the MySQL command:

Select employee.ename,department.dname from department inner join employee on department.did=employee.departmentid

Running effect display

3. External join query

When using the inner join query, we find that the returned results contain only the data that meets the query and join conditions. However, sometimes we need to include not only the eligible data in the returned query results, but also all the data in the left, right, or two tables, so we need to use an outer join query. The outer connection is divided into left (outer) connection and right (outer) connection. The syntax format is as follows:

SELECT query field 1, query field 2,... FROM Table 1 LEFT | RIGHT [OUTER] JOIN Table 2 ON Table 1. Relation field = Table 2. Relation Field WHERE condition

Thus, the syntax format of the outer join is very similar to that of the inner join, except that the LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN keywords are used. Where the table to the left of the keyword is called the left table, and the table to the right of the keyword is called the right table; OUTER can be omitted.

When using a left (outer) join and a right (outer) join query, the query results are inconsistent, as follows:

1. LEFT [OUTER] JOIN left (outer) join: returns all records in the left table and records in the right table that meet the join conditions.

2. RIGHT [OUTER] JOIN right (outer) join: returns all records in the right table and records in the left table that meet the join conditions.

Prepare the data first, the code is as follows:

Delete DROP DATABASE IF EXISTS mydb;-- if database mydb exists, delete DROP DATABASE IF EXISTS mydb;--, create database mydbCREATE DATABASE mydb;--, select database mydbUSE mydb;--, create class table CREATE TABLE class (cid int (4) NOT NULL PRIMARY KEY, cname varchar (20)); create student table CREATE TABLE student (sid int (4) NOT NULL PRIMARY KEY, sname varchar (20), sage int (2), classid int (4) NOT NULL) -- inserting data into the class table INSERT INTO class VALUES (1001); INSERT INTO class VALUES (1002); INSERT INTO class VALUES (1003); INSERT INTO class VALUES (1004);-- inserting data into the student table (INSERT INTO student VALUES (1), "Zhang San", 20); INSERT INTO student VALUES (2), "Li Si", 21 "1002); INSERT INTO student VALUES (3)," Wang Wu ", 24 1002); INSERT INTO student VALUES (4" Zhao Liu ", 23" 1003 "). INSERT INTO student VALUES (5, jackknife, 22, and 1009)

There are certain characteristics in preparing this set of data, so that we can intuitively see the difference between the left connection and the right connection.

1. There are no students in the PHP class number 1004.

2. The class number of Wang Yueyue, whose student number is 5, is 1009, which is not in the class table.

3.1 left (outside) join query

The result of a left (outer) join includes all records of the left table specified in the LEFT JOIN clause, as well as all records that meet the join conditions. If a record in the left table does not exist in the right table, it is displayed as empty in the right table.

Query the ID of each class, the name of the class, and the names of all students in the class MySQL command:

Select class.cid,class.cname,student.sname from class left outer join student on class.cid=student.classid

Running effect display

Show the result analysis:

1. Find out the students of Java class, C++ class and Python class respectively.

2. Wang Yueyue in the right table does not meet the query conditions, so it does not appear in the query results.

3. Although there are no students in the PHP class in the left table, the information of PHP is still displayed; however, its corresponding student name is NULL.

3.2 right (outside) join query

The result of a right (outer) join includes all records of the right table specified in the RIGHT JOIN clause, as well as all records that meet the join conditions. If a record in the right table does not match in the left table, the left table returns a null value.

Query the ID of each class, the name of the class, and the names of all students in the class MySQL command:

Select class.cid,class.cname,student.sname from class right outer join student on class.cid=student.classid

Running effect display

Show the result analysis:

1. Find out the students of Java class, C++ class and Python class respectively.

2. The PHP class in the left table of does not meet the query conditions, so it does not appear in the query results.

3. Although the jack in the right table of does not have a corresponding class, it still shows the information of Wang Yueyue. However, its corresponding class and class number are NULL.

15. Sub-query

A subquery is a query in which one query statement is nested within another query statement; the query statement can be nested in a SELECT, SELECT... INTO, INSERT... In statements such as INTO. When a query is executed, the statements in the subquery are executed first, and then the returned results are used as filter conditions for the outer query. You can usually use comparison operators and keywords such as IN, EXISTS, ANY, ALL, and so on, in sub-queries.

Prepare the data as follows:

DROP TABLE IF EXISTS student;DROP TABLE IF EXISTS class;-- creates a class table CREATE TABLE class (cid int (4) NOT NULL PRIMARY KEY, cname varchar (20));-- creates a student table CREATE TABLE student (sid int (4) NOT NULL PRIMARY KEY, sname varchar (20), sage int (2), classid int (4) NOT NULL);-- inserts data INSERT INTO class VALUES (1001) into the class table; INSERT INTO class VALUES (1002) INSERT INTO class VALUES (1003); INSERT INTO class VALUES (1004); INSERT INTO class VALUES (1005);-- insert data into students' tables INSERT INTO student VALUES (1) Zhang San, 20); INSERT INTO student VALUES (2) Li Si, 21)); INSERT INTO student VALUES (3), 24) 1003); INSERT INTO student VALUES (4) Zhao Liu, 23); INSERT INTO student VALUES (5) INSERT INTO student VALUES (6) Xiao Hong, 26); INSERT INTO student VALUES (7) Xiao Liang, 27); 1. Subquery with comparison operator

The comparison operator that we mentioned earlier is >, =, (select classid from student where sname=' Zhang San')

Running effect display

two。 Subquery with EXISTS keyword

The parameter after the EXISTS keyword can be any subquery, which does not produce any data and only returns TRUE or FALSE. The outer query is executed only when the return value is TRUE

If Wang Wu is in the student table, query all class information from the class table MySQL command:

Select * from class where exists (select * from student where sname=' Wang Wu')

Running effect display

3. Subquery with ANY keyword

The ANY keyword means that if any of these conditions are met, a result is returned as an outer query condition.

Query the MySQL command for a class number larger than any student's class number:

Select * from class where cid > any (select classid from student)

Running effect display

4. Subquery with ALL keyword

The ALL keyword is similar to ANY, except that the results returned by subqueries with the ALL keyword need to meet all inner query conditions at the same time.

Query the class number MySQL command that is larger than the class number of all students:

Select * from class where cid > all (select classid from student)

Running effect display

Summary

Important (from keyword analysis):

The writing order and execution order of query statements

Select = = > from = = > where = = > group by = = > having = = > order by = > limit

The execution order of query statements

From = = > where = = > group by = = > having = = > select = = > order by = > limi

Based on review, willing to share, so I have this article!

These are all the contents of the article "what are the basic knowledge points in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.