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

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 objects from the database TRUNCATE: deletes all records from the table, including all space allocated to records. COMMENT: Add 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 '名'; ———————————————— Copyright notice: This article is the original article of CSDN blogger "Sicimike", in accordance with CC 4.0 BY-SA copyright agreement, please attach the original source link and this statement. The original link: https://blog.csdn.net/Baisitao_/article/details/104714764Copy 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) The SEC) -- -- -- -- -- -- -- --Copy the code

  • The having keyword is used to filter data after grouping. It is the same as 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

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: A table in a database does not have a partial function dependence on any keyword field. Partial function dependence refers to the existence of a keyword in the combination of keywords to determine the non-keyword. The second normal form satisfies the first normal form, eliminating 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 depends only on the name of the supplier, so the table above does not meet the second normal form and can be changed to the following form: Commodity information table

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 ',' English ') values ('mike', 55,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',75), ('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_h2z
UNION ALL
SELECT NAME, 'math' AS `subject`,  math AS `score` FROM table_h2z
UNION ALL
SELECT 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 english
FROM table_z2h
GROUP 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

reference

  • www.w3schools.in/mysql/ddl-d…

Author: Sicimike

Source: blog.csdn.net/Baisitao_/a…

This article was first published on the public account: Java Version of the Web project, welcome to pay attention to get more exciting content