preface

In daily development, some unfamiliar and basic knowledge is easy to forget or become a little ambiguous after a while. This article mainly records some basic knowledge about MySQL database comparison for a quick review in the future.

SQL commands

SQL commands can be divided into four groups: DDL, DML, DCL, and TCL. The commands contained in the four groups are as follows(See watermark for image source)

DDL

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

  • CREATE: Creates databases and their 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: Add a comment
  • RENAME: Renames the 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, 5 group byCopy the code

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) havingCopy the code

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: Allows users to access the database
  • REVOKE: Revokes the access granted to the user using the GRANT command

TCL

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

  • COMMITCommit transaction
  • ROLLBACK: Rolls back the transaction in case of any errors

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. Edgar Coad, the inventor of relational modeling, first proposed this concept and defined the concepts of first normal form, second normal form and third normal form in the early 1970s. In 1974, he and Raymond F. Boyce jointly defined an improved third normal form — BC normal form. Exceptions include the fourth normal form for multi-valued dependencies, and the fifth, DK, and sixth normal forms for join dependencies.

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.

The first paradigm

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.

The first normal form is to ensure the atomicity of columns.

The above table does not meet the first normal form, in which the address column can be divided into provinces, cities, districts, etc

The second paradigm

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

In the table above, if you want to set a primary key, you must combine the product name with the supplier name to form a joint primary key. But prices and categories depend only on the name of the item, and vendor phone numbers depend only on the name of the supplier, so the table above does not satisfy the second normal form and can be changed to the following form:

Commodity information sheet

Supplier information Sheet

Merchandise – supplier association table

The third paradigm

Definition: All non-primary key attributes are associated only with candidate keys. That is, non-primary key attributes should be independent of each other.

The third normal form is to eliminate transitive dependence between columns on the basis of satisfying the second normal form.

In the table above, the category description of an item depends on the category, and the category depends on the name of the item, rather than the category description directly depends on the name of the item. This creates transitive dependencies, so it doesn’t fit the third normal form. You can rewrite it as follows

Goods table

Classification list

Paradigm – following and anti-paradigm are always a controversial issue in database design. Data consistency can be better guaranteed by following paradigms with better constraints on data relationships and by reducing data redundancy. The antiparadigm is for better performance. So there is no clear standard between paradigm and anti-paradigm, but what is best for your business scenario.

In anti-paradigm design, the following issues need to be considered: insert exception, update exception, and delete exception.

  • Insert exception: If an entity exists with the presence of another entity, that is, the entity cannot be represented without it, then the table has an insert exception.
  • Update exception: If multiple rows need to be updated to change individual attributes of an entity instance to which the table corresponds, then the table has an update exception
  • Delete exception: A table has a delete exception if deleting a row to indicate an entity instance failure results in the loss of information for a different entity instance

Take a table that violates the second normal form

If coke No. 2 Factory has not yet started to supply, the second record does not exist in the table, and the phone number of the supplier cannot be recorded, so there will be an insert exception. If you need to increase the price of cola, you need to update multiple records in the table, so there is an update exception; If you delete the supply information of Coke No. 2 Factory, then the supplier’s phone will be lost, so there will be deletion exception.

Generally, tables with insert exceptions will have update exceptions and delete exceptions.

Cross table longitudinal table

SQL script

# CREATE TABLE 'table_h2z' (' name 'varchar(32) DEFAULT NULL,' Chinese 'int(11) DEFAULT NULL,' math 'int(11) DEFAULT NULL,`english` int(11) DEFAULT NULL) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; /*Data for the table `table_h2z` */insert into `table_h2z`(`name`,`chinese`,`math`,`english`) values (' mike ', 45,43,87), (' lily '53,64,88), (' Lucy' 57,75,75); # CREATE TABLE 'table_z2h' (' name 'varchar(32) DEFAULT NULL,' subject 'varchar(8) NOT NULL DEFAULT ', `score` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `table_z2h` */insert into `table_z2h`(`name`,`subject`,`score`) values ('mike','chinese',45),('lily','chinese',53),('lucy','chinese',57),('mike','math',43),('lily','math',64),('lucy','math',7 5),('mike','english',87),('lily','english',88),('lucy','english',75);Copy the code

Horizontal table to vertical table

SELECT NAME, 'chinese' AS `subject`,  chinese AS `score` FROM table_h2zUNION ALLSELECT NAME, 'math' AS `subject`,  math AS `score` FROM table_h2zUNION ALLSELECT NAME, 'english' AS `subject`, english AS `score` FROM table_h2z
Copy the code

The execution result

+------+---------+-------+| name | subject | score |+------+---------+-------+| mike | chinese | 45 || lily | chinese | 53 || lucy | chinese | 57 || mike | math | 43 || lily | math | 64 || lucy | math | 75 || mike | english | 87 || lily | English | 88 | | Lucy | English | 75 | + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 9 rows in the set (0.00 SEC)Copy the code

Vertical table to horizontal table

SELECT NAME, SUM(CASE `subject` WHEN 'chinese' THEN score ELSE 0 END) AS chinese, SUM(CASE `subject` WHEN 'math' THEN score ELSE 0 END) AS math, SUM(CASE `subject` WHEN 'english' THEN score ELSE 0 END) AS englishFROM table_z2hGROUP BY NAME
Copy the code

The execution result

+------+---------+------+---------+| name | chinese | math | english |+------+---------+------+---------+| lily | 53 | 64 | 88 | | Lucy 57 | | 75 | 75 | | mike 45 43 | | 87 | | + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.00 SEC)Copy the code

This article is published by OpenWrite, a blogging tool platform