Addition, deletion, modification and query of MySQL common command manual

Time:2021-10-26

1、 Database operation

Create database

Syntax:

CREATE DATABASE database_name;

Delete database

Be careful when deleting the database! Because after executing the delete command, all data will disappear.

Syntax:

DROP DATABASE database_name;

Select database

Syntax:

USE database_name;

2、 Data table operation

data type

value type

type size purpose
TINYINT 1 byte Small integer value
SMALLINT 2 bytes Large integer value
MEDIUMINT 3 bytes Large integer value
Int or intger 4 bytes Large integer value
BIGINT 8 bytes Maximum integer value
FLOAT 4 bytes Single precision floating point value
DOUBLE 8 bytes Double precision floating point value
DECIMA For decimal (m, d), if M > D, it is m + 2, otherwise it is d + 2 Small value

Date and time type

type size Range format purpose
DATE 3 bytes 1000-01-01/9999-12-31 YYYY-MM-DD Date value
TIME 3 bytes ‘-838:59:59‘/‘838:59:59‘ HH-MM-SS Time value
YEAR 1 byte 1901/2155 YYYY Year value
DATETIME 8 bytes 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS Mixed date
TIMESTAMP 4 bytes 1970-01-01 00:00:00 2038 YYYY-MM-DD HH:MM:SS Mixed date

String type

type size purpose
CHAR 0-255 bytes Fixed length string
VARCHAR 0-65 535 bytes Variable length string
TINYBLOB 0-255 bytes Binary string up to 255 characters
TINYTEXT 0-255 bytes Short text string
BLOB 0-65 535 bytes Long text data in binary form
TEXT 0-65 535 bytes Long text data
MEDIUMBLOB 0-16 777 215 bytes Medium length text data in binary form
MEDIUMTEXT 0-16 777 215 bytes Medium length text data
LONGBLOB 0-4 294 967 295 bytes Maximum text data in binary form
LONGTEXT 0-4 294 967 295 bytes Maximum text data

Binary strings are used to store non-traditional data, such as images, audio and video files, program executables, etc

be careful: N in the brackets of char (n) and varchar (n) represents the number of characters, not the number of bytes. For example, char (30) can store 30 characters.

Char and varchar types are similar, but they are saved and retrieved differently. Their maximum length and whether trailing spaces are retained are also different. Case conversion is not performed during storage or retrieval.

Binary and varbinary are similar to char and varchar, except that they contain binary strings instead of non binary strings. That is, they contain byte strings instead of character strings. This means that they have no character set, and they sort and compare numeric values based on column value bytes.

Blob is a large binary object that can hold a variable amount of data. There are four types of blobs: tinyblob, blob, mediablob and longblob. The difference is that they can accommodate different storage ranges.

There are four text types: tinytext, text, mediamtext and longtext. The corresponding four blob types have different maximum lengths that can be stored, which can be selected according to the actual situation.

Create data table

Syntax:

CREATE TABLE table_name (
    column_name column_type
);

Example:

CREATE TABLE IF NOT EXISTS t_student(
	stu_id INT NOT NULL AUTO_INCREMENT,
    stu_name VARCHAR(100) NOT NULL,
    stu_email VARCHAR(200),
    PRIMARY KEY (stu_id)
);

result:

截屏2021-02-23 19.47.39

Delete data table

Syntax:

DROP TABLE table_name;

insert data

Syntax:

INSERT INTO table_name(
    field1, field2, ... fieldN
) VALUES(
    value1, value2, ... valueN
);

#Insert multiple pieces of data
INSERT INTO table_name(
    field1, field2, ... fieldN
) VALUES(
    valueA1, valueA2, ... valueAN
),(
	valueB1, valueB2, ... valueBN
);

Example:

INSERT INTO t_student(
	stu_id, stu_name, stu_email
) VALUE (
	10001, 'Tom', '[email protected]'
);

result:

截屏2021-02-23 19.55.31

Query data

Syntax:

SELECT field1, field2 ...
FROM table_name
[WHERE Clause]
[LIMIT N][OFFSET N]
  • One or more tables can be used in a query statement. The tables are separated by commas (,), and the where statement is used to set query conditions.
  • The select command can read one or more records.
  • You can use an asterisk (*) instead of other fields, and the select statement will return all field data of the table
  • You can use the where statement to include any condition.
  • You can use the limit property to set the number of records returned.
  • You can specify the data offset at which the select statement starts the query through offset. By default, the offset is 0

Where clause

Syntax:

SELECT field1, field2, ... field2 FROM table_name1, table_name2
[WHERE condition1 [AND [OR]]] condition2 ...
  • One or more tables can be used in query statements, and commas are used between tables,Split, and use the where statement to set the query conditions.
  • You can specify any condition in the where clause.
  • You can specify one or more conditions using and or.
  • The where clause can also be applied to SQL delete or update commands.
  • The where clause is similar to the if condition in the programming language. It reads the specified data according to the field values in the MySQL table.

Update update

Syntax:

UPDATE table_name SET field1 = new_value1, field2 = new_value2
[WHERE Clause]
  • You can update one or more fields at the same time.
  • You can specify any condition in the where clause.
  • Data can be updated simultaneously in a single table.
  • The update statement must add a where condition, otherwise all records will be updated

Delete statement

Syntax:

DELETE FROM table_name 
[WHERE Clause]
  • If the where clause is not specified, all records in the MySQL table will be deleted.
  • You can specify any condition in the where clause
  • Records can be deleted once in a single table.

Delete, drop and truncate can delete tables. The difference is:

  • 1. Delete and truncate only delete table data. Drop deletes table data and table structure together. For example, delete is a single kill, truncate is a group kill, and drop drops the computer.
  • 2. Delete is a DML statement. After the operation, if there is no transaction that you don’t want to commit, you can roll back. Truncate and drop are DDL statements that take effect immediately after the operation and can’t roll back. For example, delete is sending wechat to say goodbye and regret can be withdrawn. Truncate and drop slap and roll back directly.
  • 3. At the speed of execution,drop>truncate>deleteFor example, drop is the Shenzhou rocket, truncate is the harmony motor car, and delete is the bicycle

Like clause

Description:

We know that the SQL select command is used to read data in mysql, and we can use the where clause in the select statement to obtain the specified record.

An equal sign can be used in the where clause=To set the conditions for obtaining data, such as “stu_email = ‘ [email protected] ‘”。

But sometimes we need to get stu_ The email field contains all records with the “com” character. At this time, we need to use the SQL like clause in the where clause.

Use percent sign in SQL like clause%Character to represent any character, similar to the asterisk * * * * * in UNIX or regular expressions.

If a percent sign is not used%, like clause and equal sign=The effect is the same.

Syntax:

SELECT field1, field2, ... fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] field2 = 'somevalue'
  • You can specify any condition in the where clause.
  • You can use the like clause in the where clause.
  • You can use the like clause instead of the equal sign=
  • Like is usually associated with%Used together, similar to a metacharacter search.
  • You can use and or to specify one or more conditions.
  • You can use the where… Like clause in the delete or update command to specify conditions.
  1. %: indicates any 0 or more characters. It can match characters of any type and length. In some cases, if it is Chinese, please use two percent signs (%%).
  2. _: represents any single character. Matches a single arbitrary character, which is often used to limit the character length of an expression.
  3. []: represents one of the characters listed in parentheses (similar to a regular expression). Specify a character, string, or range that requires the matching object to be any of them.
  4. [^]: indicates a single character that is not listed in parentheses. Its value is the same as [], but it requires the matched object to be any character other than the specified character.
  5. When the query content contains wildcards, because of the wildcards, we cannot query the special characters’% ‘,’ ‘and’ [‘normally, but we can query normally by enclosing the special characters with’ [] ‘.

UNION

Description:

The union operator is used to connect the results of more than two select statements and combine them into a result set. Multiple select statements delete duplicate data.

Syntax:

SELECT field1, field2, ... fieldN
FROM table_name
[WHERE condition]
UNION [ALL | DESTINCT]
SELECT field2, field2, ... fieldN
FROM table_name
[WHERE condition];
  • filed1, field2, … fieldN: the column to retrieve.
  • table_name:The data table to retrieve.
  • WHERE conditions:Optional, search criteria.
  • DISTINCT:Optional, delete duplicate data in the result set. By default, the union operator has deleted duplicate data, so the distinct modifier has no effect on the result.
  • ALL:Optional, return all result sets, including duplicate data.

Order by sort

Syntax:

SELECT field1, field2, ... fieldN
FROM table_name
ORDER BY field1 [ASC [DESC]], [field2 ...] [ASC [DESC]]
  • You can use any field as a sort condition to return the sorted query results.
  • You can set multiple fields to sort.
  • You can use ASC or desc keywords to set whether query results are arranged in ascending or descending order. By default, it is arranged in ascending order.
  • You can add a where… Like clause to set the condition.

Group by

Description:

Group by statementgroups a result set based on one or more columns.

On grouped columns, we can use count, sum, AVG, and other functions.

Syntax:

SELECT column_name function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Join connection

Description:

  • Inner join (inner join, or equivalent join): get the record of field matching relationship in two tables.

    img

  • Left join:Get all records in the left table, even if there is no corresponding matching record in the right table.

img

  • Right join:In contrast to left join, it is used to obtain all records in the right table, even if there is no corresponding matching record in the left table.

img

grammar

# INNER JOIN
SELECT a.filed1, a.filed2, ... b.filed1, ...
FROM table_name1 a INNER JOIN table_name2 b
ON a.id = b.id;
# LEFT JOIN
SELECT a.filed1, a.filed2, ... b.filed1, ...
FROM table_name1 a LEFT JOIN table_name2 b
ON a.id = b.id;
# RIGHT JOIN
SELECT a.filed1, a.filed2, ... b.filed1, ...
FROM table_name1 a RIGHT JOIN table_name2 b
ON a.id = b.id;

Null value processing

We already know that MySQL uses the SQL select command and where clause to read the data in the data table, but when the provided query condition field is null, the command may not work normally.

To handle this situation, MySQL provides three operators:

  • IS NULL:This operator returns true when the value of the column is null.
  • IS NOT NULL:When the value of the column is not null, the operator returns true.
  • <=>:The comparison operator (different from the = operator) returns true when the two values compared are equal or both are null.

The conditional comparison operation of null is special. You cannot use = null or= Null finds a null value in a column.

In mysql, the comparison of null value with any other value (even null) always returns null, that is, null = null returns null.

Is null and is not null operators are used to handle null in MySQL.

Alter command

Description:

When we need to modify the data table name or modify the data table fields, we need the alter command.

Delete table fields

ALTER TABLE table_name DROP column_name;

Add table field

ALTER TABLE table_name ADD column_name datatype;

Modify table fields

# MODIFY
ALTER TABLE table_name MODIFY column_name new_datatype;
# CHANGE
ALTER TABLE table_name CHANGE old_column_name new_column_name new_datatype;

Recommended Today

What process is rundll32.exe

Rundll32.exe process informationProcess file: rundll32 or rundll32.exeProcess name: Microsoft rundll32 normal location: X: (current system partition) \ windows \ system32Description: Test for Netguide.. — caiger2008Produced by: Microsoft CorpOf: Microsoft Windows operating systemSystem process: YesDaemons: YesUse network: noHardware related: noCommon error: unknown n / AMemory usage: unknown n / a security level (0-5): 0We found that […]