preface

Development environment: MySQL5.7.31

This article is not mysql statement grammar tutorial or notes, if beginners mysql, want to see the SQL tutorial or learning various grammar language specification, see one thousand rows mysql learning notes or mysql tutorial | novice tutorial

SQL commands

SQL commands can be divided into four groups: DDL, DML, DCL, and TCL.

DDL

DDL is short for Data Definition Language. It works with database schemas and describes how Data should reside in the database.

  • CREATE: Creates a database and its objects (such as tables, indexes, views, stored procedures, functions, and triggers)
  • ALTER: Changes the structure of an existing database
  • DROP: Deletes an object from the database
  • TRUNCATE: Deletes all records from the table, including all space allocated for records
  • COMMENT: Adds a COMMENT
  • RENAME: renames an object

Common commands are as follows:

CREATE TABLE sicimike (id int(4) primary key auto_increment COMMENT 'primary key id ', name varchar(10) unique, age int(3) default 0, Identity_card varchar(18) # PRIMARY KEY (id) # UNIQUE KEY (name (identity_card, col1...) // Create index in this way) ENGINE = InnoDB; Alter table sicimike add primary key(id); Alter table sicimike drop primary key; Alter table sicimike add unique key(column_name); Alter table sicimike drop index column_name; Alter table sicimike add [unique/fulltext/spatial] index/key index_name (identity_card[(len)] [ASC /desc])[using btree/hash] create [unique/fulltext/spatial] index index_name on sicimike(identity_card[(len)] [asc/desc])[using Btree /hash] example: ALTER table sicimike add index idx_na(name, age); Alter table sicimike drop key/index identity_card; drop index index_name on sicimike; # show index from sicimike; # check column desc sicimike; Alter table sicimike add column column_name varchar(30); Alter table sicimike drop column column_name; Alter table sicimike change column_name new_name varchar(30); Alter table sicimike modify column_name varchar(22); Show create table sicimike; Alter table sicimike comment 'comment '; Alter table sicimike modify column column_name varchar(10) comment '名';Copy the code

DML

DML is short for Data Manipulation Language. It contains the most common SQL statements, such as SELECT, INSERT, UPDATE, and DELETE, which are used to store, modify, retrieve, and DELETE Data in a database.

paging

Select * from sicimike limit 10, 5Copy the code

group by

By default, the group by statement in MySQL does not require that the column returned by SELECT be either a grouped column or an aggregate function. If the column of the SELECT query is not a grouping column or an aggregate function, the data for the first record in that grouping is returned. Compare the following two SQL statements. In the second SQL statement, cnames are neither grouped columns nor appear as aggregate functions. So in the liming group, cname takes the first data.

mysql> select * from c; + + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + | CNO | CNAME | CTEACHER | + + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + | 1 | | mathematics liming | | 2 | | Chinese liming | | | 3 | history xueyou | | 4 physical | | guorong | | 5 chemical | | liming | + + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 5 rows in the set (0.00 SEC) mysql >  select cteacher, count(cteacher), cname from c group by cteacher; +----------+-----------------+-------+ | cteacher | count(cteacher) | cname | +----------+-----------------+-------+ | Guorong physical | | 1 | | liming mathematical | | 3 | | xueyou history | 1 | | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 3 rows in the set (0.00) sec)Copy the code

having

The having keyword is used to filter data after grouping. It is similar to where before grouping, but more stringent. The filter condition is either an aggregate function (… Having count(x) > 1), either in the column after select (select col1, col2… group by x having col1 > 1)

More table updates

update tableA a inner join tableB b on a.xxx = b.xxx set a.col1 = xxx, b.col1 = xxx where ...
Copy the code

More table delete

delete a, b from tableA a inner join tableB b on a.xxx = b.xxx where a.col1 = xxx and b.col1 = xxx
Copy the code

##DCL

The DCL is short for Data Control Language. It contains commands like GRANT and deals mainly with permissions, permissions, and other controls on the database system.

  • GRANT: GRANT user access to the database
  • REVOKE: Revokes the access granted to a user using the GRANT command

TCL is short for Transaction Control Language, which is used to process transactions in databases

  • COMMIT: Commits the transaction
  • ROLLBACK: ROLLBACK the transaction in the event of any errors

Some common functions

Text processing function

  • LENGTH() returns the string LENGTH
  • LOWER() converts the string to lowercase
  • UPPER() converts the string to uppercase
  • LTRIM() strips the Spaces on the left of the string
  • RTRIM() removes the Spaces to the right of the string

Date and time handlers

  • SELECT DATE(NOW()) 2017-07-22

  • DATE_FORMAT() returns a formatted date and time string

  • HOUR() returns the HOUR portion of a time

  • MINUTE() returns the MINUTE portion of a time

  • MONTH() returns the MONTH portion of a date

  • NOW() returns the current date and time

  • SECOND() returns the SECOND portion of a time

  • TIME() returns the TIME portion of a date-time

  • YEAR() returns the YEAR portion of a date

  • SELECT * FROM TABLE WHERE date=’2020-02-22′ SELECT * FROM TABLE WHERE date=’2020-02-22′ The screen is 2020-02-22 00:00:00

  • SELECT * FROM TABLE WHERE DATE(DATE)=’2020-02-22′ SELECT * FROM TABLE WHERE DATE(DATE)=’2020-02-22′

  • SELECT * FROM TABLE WHERE DATE(DATE) BETWEEN ‘2020-02-22’ AND ‘2020-03-22’ SELECT * FROM TABLE WHERE DATE(DATE) BETWEEN ‘2020-02-22’ AND ‘2020-03-22’

Array handlers

  • ABS() returns the absolute value of a number
  • Cosine of theta returns the cosine of an Angle
  • EXP() returns the exponent of a number
  • MOD() returns the remainder of the division operation
  • PI() returns PI
  • RAND() returns a random number
  • Sine () returns the sine of an Angle
  • SQRT() returns the square root of a number
  • Tangent of theta returns the tangent of an Angle

Aggregation function

  • SELECT AVG(student_score) AS AVG_ PRIVE FROM score
  • SELECT COUNT(*) FROM TABLE
  • SELECT COLUMN(name) FROM TABLE Specifies the number of columns in the TABLE with a value. If the value is NULL, no count is required
  • SELECT MAX(score) FROM TABLE
  • SELECT MIN(score) FROM TABLE to find the minimum value
  • SELECT SUM(count) FROM TABLE and SUM(price*count)

The union and union all

  • A union is a combination of data, excluding duplicate rows, and a default sort
  • A Union all operation is performed on the data, including duplicate rows, without sorting

Example:

Create two tables

CREATE TABLE `t_demo` ( `id` int(32) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(2) DEFAULT NULL, `num` int(3) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO ` t_demo ` VALUES (' 1 ', 'zhang', '21', '69'); INSERT INTO ` t_demo ` VALUES (' 2 ', 'bill', '22', '98'); INSERT INTO ` t_demo ` VALUES (' 3 ', 'Cathy', '20', '54'); INSERT INTO ` t_demo ` VALUES (' 4 ', 'zhao sweet', '22', '80'); CREATE TABLE `t_demo_copy` ( `id` int(32) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(2) DEFAULT NULL, `num` int(3) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO ` t_demo_copy ` VALUES (' 2 ', 'pig eight quit,' 22 ', '98'); INSERT INTO ` t_demo_copy ` VALUES (' 3 ', 'Cathy', '20', '54'); INSERT INTO ` t_demo_copy ` VALUES (' 4 ', 'zhao sweet', '22', '80'); INSERT INTO ` t_demo_copy ` VALUES (' 5 ', 'the Monkey King', '22', '100'); INSERT INTO ` t_demo_copy ` VALUES (' 6 ', 'bill', '24', '99');Copy the code

The union result:

SELECT * FROM t_demo UNION SELECT * FROM t_demo copy > OK > time: 0.008sCopy the code

Union all result:

SELECT * FROM t_demo UNION ALL SELECT * FROM t_demo copy > OK > time: 0sCopy the code

conclusion

The UNION ALL simply merges the two results and returns. If two result sets are returned with duplicate data, the returned result set will contain duplicate data.

The efficiency of

A UNION ALL is much faster than a UNION in terms of efficiency, so use a UNION ALL if you can be sure that the two result sets that are merged do not contain duplicate data and do not need sorting.

engine

The difference between MyISAM and InnoDB

  1. InnoDB supports transactions, MyISAM does not support transactions. For InnoDB, each SQL language is wrapped as a transaction by default, which will affect the speed, (so it is best to put multiple SQL languages between begin and commit, constitute a transaction).
  • InnoDB supports foreign keys, while MyISAM does not. Converting an InnoDB table with foreign keys to MYISAM will fail
  • InnoDB is a clustered index, uses B+Tree as the index structure, and the data files are tied together with (primary key) indexes (table data files themselves are an index structure organized by B+Tree). MyISAM is a non-clustered index and also uses B+Tree as the index structure. The index and data files are separated.
  • InnoDB does not store the exact number of rows in a table. Select count(*) from table requires a full table scan. MyISAM uses a variable to hold the number of rows in the entire table, which can be read quickly
    • So why doesn’t InnoDB have this variable?
    • Because of the transaction nature of InnoDB, the number of rows in the same schedule is different for different transactions, so the count count counts the number of rows that can be counted for the current transaction, rather than storing the total number of rows for quick lookup.
  • InnoDB supports table and row (default) level locking, while MyISAM supports table level locking
    • InnoDB row locks are implemented on indexes, not on physical row records. If the access does not hit the index and the row lock cannot be used, it degrades to a table lock.
  • InnoDB tables must have a primary key (users will find or produce one if they do not specify one), while Myisam can have no primary key
  • Innodb stores FRM and IBD files, while Myisam is FRM, MYD and MYI
    • Innodb: FRM is a table definition file, IBD is a data file
    • Myisam: FRM isa table definition file, myd isa data file, and myi is an index file

Select count(*) which is faster and why

Myisam is faster because myISAM maintains a counter that can be called directly.

Four features of the InnoDB engine

  1. Insert buffer
  2. Double write
  3. Adaptive Hash index (AHI)
  4. Pre-reading (read ahead)

Char vs. varchAR

In a nutshell:

  • Char: fixed length, high efficiency, commonly used for fixed-length form submission data storage. For example: ID number, mobile phone number, phone number, password and so on
  • Varchar: variable length, low efficiency

Length:

The length of type char is fixed; The length of a VARCHar type is variable

Efficiency:

Char changes the same length of data each time, which is more efficient. The vARCHAR type changes different data lengths each time, which reduces efficiency

Different storage:

The char type is stored as an initial expected string plus a byte to record the length of the string, which occupies a large space. The vARCHAR type stores the actual string plus a byte to record the length of the string, which takes up less space.

Note: In a char(M) column, each value takes up M bytes. If a value is less than M, MySQL will fill it up with a space character to the right. In a vARCHar (M) data column, each value takes just enough bytes plus one byte to record its length (total length L+1 bytes).

The difference between NULL and NULL

Different space occupancy

mysql> select length(NULL), length(''), length('1');
+--------------+------------+-------------+
| length(NULL) | length('') | length('1') |
+--------------+------------+-------------+
|         NULL |          0 |           1 |
+--------------+------------+-------------+
1 row in set (0.00 sec)
Copy the code

Note: The length of null value is 0, it does not occupy space; NULL has a length of NULL and is space-consuming because NULL columns require extra space in the row to record whether their value is NULL.

Insert/query methods are different

  • If the field is set to NOT NULL, NULL values cannot be inserted, but NULL values can be inserted
  • In the query
    • To query NULL columnsis NULLIs NULL /is not NULL;
    • To query null value columns= ' 'In short, it is recommended to use =,! Arithmetic operators such as =, <, >

Count () and IFNULL(argument 1, argument 2)

  • Using COUNT(field) statistics filters out NULL values, but not NULL values.
  • IFNULL takes two arguments. If the first parameter field is NULL, the second parameter is returned (the default value). Otherwise, the first parameter field is returned

Index problems

If a column in MySql contains NULL, the index containing the column is invalid. –> This sentence is wrong

MySQL can use indexes on columns that contain NULL.

Using common indexes on null-worthy fields, such as normal indexes, composite indexes, full-text indexes, and so on, does not invalidate an index. The index column must be NOT NULL.

join

Join is used to join fields in multiple tables. The syntax is as follows:

. FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditionaCopy the code

Table1: left table; Table2: the right table.

According to functions, JOIN can be roughly divided into the following three categories:

INNER JOIN: Retrieves a record of a JOIN match between two tables.

LEFT JOIN: obtain the complete record of LEFT table (Table1), that is, there is no corresponding matching record of right table (Table2).

RIGHT JOIN: contrary to LEFT JOIN, obtain the complete record of RIGHT table (table2), that is, there is no matching record of LEFT table (table1).

Note: mysql does not support Full JOIN, but you can simulate Full Join by combining LEFT join and RIGHT join with the UNION keyword.

Left join,right join,inner join,full join

  • Left Join returns all rows of the left table, even if there is no matching data in the right table
  • The right Join returns all rows of the right table, even if there is no matching data in the left table
  • An inner Join returns data common to both tables
  • Full Join returns all rows of both tables, even if there is no match

Inner join

An inner join, also known as an equivalent join, produces A set of data that matches both A and B.

mysql> select * from A inner join B on A.name = B.name;
+----+--------+----+--------+
| id | name   | id | name   |
+----+--------+----+--------+
|  1 | Pirate |  2 | Pirate |
|  3 | Ninja  |  4 | Ninja  |
+----+--------+----+--------+
Copy the code

Left join

mysql> select * from A left join B on A.name = B.name; Select * from A left outer join B on a.name = b.name; select * from A left outer join B on a.name = b.name; +----+-----------+------+--------+ | id | name | id | name | +----+-----------+------+--------+ | 1 | Pirate | 2 | Pirate | | 2 | Monkey | NULL | NULL | | 3 | Ninja | 4 | Ninja | | 4 | Spaghetti | NULL | NULL | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- - + 4 rows in the set (0.00 SEC)Copy the code

Left join produces A complete set of records from left table (A), with matching records (right table (B)). If there is no match, null is included on the right.

If you want to generate only one set of records from left table (A), but not from right table (B), you can do this by setting the WHERE statement as follows:

mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null; +----+-----------+------+------+ | id | name | id | name | +----+-----------+------+------+ | 2 | Monkey | NULL | NULL | | | 4 Spaghetti | NULL | NULL | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 2 rows in the set (0.00 SEC)Copy the code

Similarly, you can simulate inner join as follows:

mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null; +----+--------+------+--------+ | id | name | id | name | +----+--------+------+--------+ | 1 | Pirate | 2 | Pirate | | 3 | Ninja | | 4 Ninja | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

A difference set:

According to the above example, the difference set can be calculated as follows:

SELECT * FROM A LEFT JOIN B ON A.name = B.name WHERE B.id IS NULL union SELECT * FROM A right JOIN B ON A.name = B.name WHERE A.id IS NULL; Result # + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 2  Monkey | NULL | NULL | | 4 | Spaghetti | NULL | NULL | | NULL | NULL | 1 | Rutabaga | | NULL | NULL | 3 | Darth Vader |  +------+-----------+------+-------------+Copy the code

Right join

mysql> select * from A right join B on A.name = B.name; +------+--------+----+-------------+ | id | name | id | name | +------+--------+----+-------------+ | NULL | NULL | 1 | Rutabaga | | 1 | Pirate | 2 | Pirate | | NULL | NULL | 3 | Darth Vader | | 3 | Ninja | 4 | Ninja | + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- - + 4 rows in the set (0.00 SEC)Copy the code

With left the join.

Cross join

Cross join: the result is the product of two tables

The Descartes product is also called the direct product. Assuming that set A = {A, b}, set b = {0}, the two sets of cartesian product for {(A, 0), (A, 1), (A, 2), (b, 0), (b, 1), (b, 2)}. Can be extended to multiple collections. Similarly, if A represents the collection of students of A school and B represents the collection of all courses of the school, then the Cartesian product of A and B represents all possible courses.

mysql> select * from A cross join B; +----+-----------+----+-------------+ | id | name | id | name | +----+-----------+----+-------------+ | 1 | Pirate | 1 |  Rutabaga | | 2 | Monkey | 1 | Rutabaga | | 3 | Ninja | 1 | Rutabaga | | 4 | Spaghetti | 1 | Rutabaga | | 1 | Pirate | 2  | Pirate | | 2 | Monkey | 2 | Pirate | | 3 | Ninja | 2 | Pirate | | 4 | Spaghetti | 2 | Pirate | | 1 | Pirate | 3 | Darth Vader | | 2 | Monkey | 3 | Darth Vader | | 3 | Ninja | 3 | Darth Vader | | 4 | Spaghetti | 3 | Darth Vader | | 1 |  Pirate | 4 | Ninja | | 2 | Monkey | 4 | Ninja | | 3 | Ninja | 4 | Ninja | | 4 | Spaghetti | 4 | Ninja | + - + -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- + 16 rows in the set (0.00 SEC)Copy the code

Full join

mysql> select * from A left join B on B.name = A.name -> union -> select * from A right join B on B.name = A.name; +------+-----------+------+-------------+ | id | name | id | name | +------+-----------+------+-------------+ | 1 | Pirate | 2 | Pirate | | 2 | Monkey | NULL | NULL | | 3 | Ninja | 4 | Ninja | | 4 | Spaghetti | NULL | NULL | | NULL | NULL | 1 | Rutabaga | | NULL | NULL | 3 | Darth Vader | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + 6 rows in the set (0.00) sec)Copy the code

Drop, DELETE, truncate

grammar

  • Drop Table Indicates the name of a tabledrop table dbo.Sys_Test
  • Truncate Table Nametruncate table dbo.Sys_Test
  • Delete from table name where column name = valuedelete from dbo.Sys_Test where test='test'

The difference between

  • Drop drops content and definitions and frees space. Executing the DROP statement causes the structure of the table to be dropped together.
  • Truncate (Clear data in the table). Deletes content, frees space, but does not delete the definition (that is, preserves the data structure of the table). Unlike drop, it simply empties table data. Truncate cannot delete row data. Although truncate only deletes data, it is more thorough than DELETE. It only deletes table data.
  • Similar to TRUNCate, DELETE only deletes content and frees space but does not delete definitions. However, delete can be used to delete row data or entire table data.

Execution speed

The DELETE statement deletes a row from a table at a time and stores the row deletion as a transaction in a log for rollback.

Therefore, the execution speed is drop> TRUNCate > DELETE

scenario

  • If you want to drop a table, use drop;
  • If you want to preserve the table and delete all data, use TRUNCate if the data has nothing to do with a transaction.
  • If you want to trigger a transaction, use delete.

other

  • After the TRUNCate statement is executed, ID columns are sequenced and continuous. After the DELETE statement is executed, the ID indicates that the column is not contiguous
  • Delete is a DML operation. The delete operation is recorded in the redo and undo tablespaces for rollback and redo operations. The tablespace must be large enough to take effect only after a manual commit operation is performed. You can undo operations with rollback.
  • Drop and TRUNCate are DDLS and are implicitly committed. Therefore, they cannot be rolled back or triggered.

The three major paradigm

Database standardization, also known as normalization, standardization, is a series of principles and techniques of database design, in order to reduce data redundancy in the database, improve the consistency of data.

At present, the database design can meet 3NF at most, which is generally considered to be too high. Although it has better constraint on data relationship, it also leads to the increase of data relationship tables and makes database IO more busy. The relationship constraint that was originally handled by the database is now more completed in the database using program.

First Normal form — (Make sure each column remains atomic)

Definition: All fields (columns) in a database are single attributes and cannot be divisible. This single attribute is made up of basic data types, such as integer, floating point, string, and so on.

** First normal form is to ensure atomicity of columns. ** Each column of a database table is an indivisible atomic data item, not a collection, array, record and other non-atomic data item. If an attribute in an entity has multiple values, it must be split into different attributes.

The following table

Student id The name address
1000 Xiao Ming 100 Xx Street, Luojiang District, Quanzhou City, Fujian Province, China

Does not satisfy the first normal form, should be split into

Student id The name province The city area The street House number
1000 Xiao Ming Fujian province Quanzhou city Luojiang district Xx street No. 100

Second normal form — (make sure every column in the table is related to the primary key)

Definition: Tables in a database do not have partial function dependencies on any keyword field that are not key fields

Partial function dependence refers to the existence of a keyword in the combination of keywords to determine the non-keyword situation

On the basis of satisfying the first normal form, the second normal form eliminates the partial dependence of non-primary key columns on the joint primary key

Only one type of data can be stored in a database table. Multiple types of data cannot be stored in the same database table. The order table and the item table should not be designed in one table. Instead, separate the two tables and add a many-to-many item ID and order ID association table. (A table only describes one thing)

Third normal Form 3NF — (add foreign key to 2NF)

Third normal Form (3NF) : the second normal form (2NF) must be satisfied first, which requires that each column in the table is only directly related to the primary key, not indirectly related (each column in the table can only depend on the primary key);

For example, if the order table needs customer information, after the customer table is separated, the order table needs only one user ID (foreign key), and no other customer information. Because other customer information is directly related to the user ID, not directly to the order ID.

The user information in the order table does not directly design the field, design a user ID field and then associate the user table with the foreign key.