database

The database is similar to a folder with many excel tables, but the database is a software specially used to manage data, so the efficiency is much higher than that of Excel or text storage

• Database management system consists of a collection of interrelated data and a set of programs to access these data. This data set is often called a database
• classification
  • Relational database: Based on the relational model, relational database uses a series of tables to express data and the relationship between these data. MySQL is a relational database. Relational model has become the main data type today. It simplifies the work of programmers than the previous network model and hierarchical model. Now the popular NoSQL database generally refers to non relational database
  • Non relational databases: independent lists

MySQL database

Linux system is used

1. MySQL profile

The mysql configuration file level directory installed by Ubuntu through apt is / etc / mysql / mysql conf.d

Above Windowns is my. In the installation directory Ini. If you need to change some MySQL settings

2. View MySQL services

On Linux, through: service mysql status/start stop

You can view it through the task manager under Windowns

3. Log in to MySQL

• Local connection:

mysql -u username - p

Input password

• Remote connection:

mysql -hIP address - p port - u user - p

Input password

4. View database

MySQL statement specification

• Keywords and function names are all capitalized
• The database name, table name and field name are all lowercase and enclosed in backquotes
• SQL statements must end with a semicolon

Check which databases are available: SHOW DATABASES;

Enter a database: USE mysql;

Judge whether it is in which database: SELECT DATABASE();

View current user: SELECT user();

5. Create database

CREATE{DATABASE|SCHEMA}[IF NOT EXISTS] `name`;

The part with braces is to choose one more

The part in brackets is optional

In MySQL, the database name is enclosed in backquotes

DATABASE in MySQL DATABASE is equivalent to SCHEMA, which may be different in other databases

6. Delete database

DROP {DATABASE|SCHEMA}[IF EXISTS] `name`;

The part with braces is to choose one more

The part in brackets is optional

7. Table operation

increase

1. Create table

   CREATE TABLE [IF NOT EXISTS] table_name(
       column_name data_type
   );
   

   column_name is the field name, that is, the column name

   data_type is the field type. The common field types are integer (INT) and string (varchar)

   The part in brackets is optional

2. Add field

   ALTER TABLE table_name ADD (
       column_name data_type
   );
   

Delete

1. Delete table

   DROP TABLE table_name;
   

change

1. Modify table

   • Delete field

   ALTER TABLE table_name DROP (
       column_name
   );
   

   • Modify field type

   ALTER TABLE table_name MODIFY column_name data_type;
   

   • Modify field type and name

   ALTER TABLE table_name CHANGE old_column_name new_column_name data_type;
   

   • Modify table name

   ALTER TABLE table_name RENAME new_table_name;
   

check

1. View which tables are in the database

SHOW TABLES[FROM `Library name`];

1. See what information is in the table

   • View table creation information

   SHOW CREATE TABLE `name`;
   

   • View table field information

   DESC table_name;
   SHOW COLUMNS FROM table_name;
   

constraint

Non NULL constraint

Whether a value of a database field can be NULL, NULL field value can be NULL, and NOT NULL field value cannot be NULL. When the field is set to non empty, the value must be inserted to insert the value, otherwise an error will be reported

Add a non empty constraint

ALTER TABLE `table_name` MODIFY column_name type NOT NULL; 

NOT NULL can be added at the end for non null constraints

Delete non empty constraints

ALTER TABLE `table_name` MODIFY column_name type NULL; 

Unique constraint

After a unique constraint is added to a field, the value of the field does not duplicate, that is, the value of the field is the only unique key in the table

Add unique constraint

ALTER TABLE `table_name` ADD CONSTRAINT `index_name` UNIQUE(column_name);

index_name is an alias for easy deletion

Delete unique constraint

ALTER TABLE `table_name` DROP INDEX `index_name`;

Primary key constraint

The primary key ensures the uniqueness of records. The primary key is saved as NOT NULL. Each data table can only have one primary key (non empty and unique)

Add primary key constraint

ALTER TABLE `table_name` ADD PRIMARY KEY(column_name);

Delete primary key constraint

ALTER TABLE `table_name` DROP PRIMARY KEY;

Self increasing constraint

AUTO_INCREMENT is numbered automatically and must be used in combination with the primary key. By default, the starting value is 1 and each increment is 1

Add self increasing constraint

ALTER TABLE `table_name` CHANGE `column_name` `column_name` INT NOT NULL AUTO_INCREMENT;

Delete auto increment constraint

ALTER TABLE `table_name` CHANGE `column_name` `column_name` INT NOT NULL;

Default constraint

The setting of the initial value of default constraint. When inserting a record, if there is no explicit assignment for the field, the default value will be automatically assigned

DEFAULT TABLE `table_name` ALTER `cloumn_name`{SET DEFAULT `default_name`|DROP DEFAULT}

Foreign key constraint

Constraint object

The constraints mentioned earlier are intra table constraints themselves, while foreign key constraints constrain other tables

Create condition

The storage engine of the data table can only be InnoDB. The data types of the foreign key column and the reference column are the same. The foreign key must be associated with the key. The foreign key name must be unique in the database

Create foreign key constraints

ALTER TABLE `table_name` ADD CONSTRANT `index_name` FOREIGN KEY (`column_name`) RRENCES `Foreign key associated table`(`column_name`);

Set when creating a table, as long as the content after ADD

Delete foreign key constraint

ALTER TABLE `table_name` DROP FOREIGN KEY `index_name`;

Combined use cases

CREATE TABLE stu (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) DAFAULT 'nan',
    phone VARCHAR(11) UNIQUE,
    d_id INT,
    CONSTRAINT fk_id FOREIGN KEY (d_id) REFERENCES department(d_id)
)AUTO_INCREMENT=100;

Table relation

One to one relationship refers to the relationship between one data of one entity and one data of another entity

One to many relationship, one to many and many to one is a concept, which means that one data of one entity is associated with multiple data of another entity

In the many to many relationship, the data of one entity corresponds to multiple data of another entity, and the data of another entity also corresponds to multiple data of the current entity

Three data paradigms

The role of the paradigm is to avoid data redundancy and insert / delete / update exceptions as much as possible

• The first normal form (1NF): each attribute in the relationship conforming to 1NF is not separable. 1NF is the most basic requirement of all relational databases
  Simply put, multiple headers cannot appear

• Second normal form (2NF): Based on 1NF, 2NF eliminates the partial functional dependence of non primary attributes on codes
  In short, only one object (primary key) can be described, and other column names (secondary keys) are completely dependent on the object

• The third normal form (3NF): Based on 2NF, 3NF eliminates the transfer function dependence of non primary attributes on codes
  In short, all non primary attributes only appear once in the whole database. There can be no dependency between secondary keys

Database transaction

A sequence of database operations that accesses and may operate various data items. These operations are either all performed or not performed. It is an inseparable work unit
In short: several operations are bound together, either all or none

view

The three paradigms make the table query complex. For common data queries, it is very inconvenient to write complex query statements repeatedly. Therefore, a virtual table (without data) can be created. The data of this virtual table comes from other tables in the database, and the data source of the virtual table is given at the time of definition

Create view

CREATE VIEW `view_name`[(column_name_list)] AS select_statement;

Delete view

DROP VIEW `view_name`;

8. Table data operation

increase

INSERT INTO table_name [(column_name,···)]{VALUES|VALUE}(···)

When the inserted field name is not specified, insert all fields

() the inside shall be separated by

If VALUES is inserted (··), (···)

Delete

DELETE FROM table_name [WHERE condition];

If no condition is added, all contents of the table will be deleted

where can be used as is null or is not null

< > it is not equal to

change

1. Update data

UPDATA table_name SET column_detail_name=value [WHERE condition];

If there is no WHERE, modify all data

check

Single table query

SELECT {*|column_name} FROM table_name [WHERE Conditions to add];

It can be matched using and and or

Alias

SELECT column_name AS new_column_name FROM table_name AS new_table_name;

The function is to prevent conflicts of the same name between tables

Where AS can be replaced by spaces

You can use table_name.column_name to query table contents

multi-table query

• Inner connection

Cartesian coordinate system

Imagine the two tables as each point on the x-axis and y-axis, which is the set of rows in the two tables

SELECT * FROM `table_name1` INNER JOIN `table_name2`;
SELECT * FROM `table_name1`, `table_name2`;
SELECT * FROM `table_name1`CROSS JOIN `table_name2`;
SELECT * FROM `table_name1` JOIN `table_name2`;

The results of the above four SQL are the same, which are Cartesian products, also known as unconditional connection / cross connection, etc

The number of columns is the addition of two table columns, and the number of rows is the product of two table rows

Add condition query

SELECT * FROM `table_name1` INNER JOIN `table_name2` ON condition;
SELECT * FROM `table_name1`, `table_name2` WHERE condition;
SELECT * FROM `table_name1`CROSS JOIN `table_name2`;
SELECT * FROM `table_name1` JOIN `table_name2`;

Multi table connection

SELECT * FROM `table_name1` INNER JOIN `table_name2` ON condition INNER JOIN `table_name3` ON condition;
SELECT * FROM `table_name1`, `table_name2`, `table_name3` WHERE condition;

• External connection

Right connection (left connection is similar to right connection, but the effect is opposite)

SELECT * FROM `table_name1` RIGHT JOIN `table_name2`ON condition;

Left connection

SELECT * FROM `table_name1` LEFT JOIN `table_name2`ON condition;

It mainly retains the data connected on the left, deletes more data on the right, and fills in less data with null

Full connection

UNION is used to merge the result sets of two or more SELECT statements and eliminate any duplicate rows in the table

SELECT s.`name`,c.`name` FROM `students` s LEFT JOIN `select` se ON s.`s_id`=se.`stu_id` LEFT JOIN `course` c ON c.`id`=se.`coures_id`
UNION
SELECT s.`name`,c.`name` FROM `students` s LEFT JOIN `select` se ON s.`s_id`=se.`stu_id` LEFT JOIN `course` c ON c.`id`=se.`coures_id`;

Keep two tables and fill with null if none

Sub table query

Subqueries can be placed not only after JOIN, but also after WHERE

SELECT * FROM `students` s WHERE s.`dept_id` = (SELECT `id` FROM `department` d WHERE d.`name` = 'College of foreign languages');

sort

ORDER BY sorts the query results, ASC ascending (default) DESC descending

SELECT * FROM `table1_name` AS s JOIN `table2_name` sd ON s.`id`=sd.`stu_id`ORDER BY s.`id` DESC;

Limit the number of rows displayed

LIMIT limits the number of rows displayed for the query results

SELECT * FROM `table1_name` AS s JOIN `table2_name` sd ON s.`id`=sd.`stu_id`ORDER BY s.`id` DESC LIMIT 2,3;

Where 2,3 indicates that 2 to 3 lines are displayed

Grouping query

Grouping is a common operation, which is often used for grouping statistics. After using GROUP BY, groups will be grouped according to the fields after GROUP BY, and must be clear fields, not *, so it cannot be * after SELECT. Secondly, you can use HAVING to filter the results after grouping. Note: the fields after HAVING must appear after SELECT

How many statistics are there

SELECT COUNT(*) FROM `table1_name`;

Number of occurrences of Statistics

SELECT `column_name`,count(*) FROM `table_name` GROUP BY `column_name` HAVING Selection conditions;

MySQL built-in functions

1. Field processing

SELECT s.`name`,IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;  # Handle NULL
SELECT DISTINCT `name` FROM `students`;  # Field de duplication

1. String interception

LEFT is to intercept from the LEFT, RIGHT is to intercept from the RIGHT, and SUBSTRING can specify the interception range

SELECT LEFT(s.`name`,2),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
SELECT RIGHT(s.`name`,2),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
SELECT SUBSTRING(s.`name`,2,5),IFNULL(sd.`id_card`,430) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

1. String splicing

SELECT CONCAT(s.`name`,sd.`id_card`) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;

1. Type conversion

CAST CONVERT

SELECT CAST(sd.`id_card` AS CHAR) FROM `students` s LEFT JOIN `student_details` sd ON s.`s_id`=sd.`stu_id`;
SELECT CONVERT(s.`dept_id`,SIGNED) FROM `students` s ;

1. Time function

SELECT DAY('2017-08-18')-DAY('2017-08-01');
SELECT NOW();

There are many MySQL functions, many of which are the same as those we usually use. Aggregate functions such as ABS/MAX/MIN/ROUND/AVG/SUM are also used in the same way. If you need to use them, please query by yourself

SELECT MAX(sd_age) FROM `table_name`;
SELECT sd_age,MAX(sd_age) FROM `table_name` GROUP BY sd_age;

MySQL execution order

1. FORM: calculate the Cartesian product of the left table and the right table of FROM. Generate virtual table VT1

2. ON: ON filter virtual table VT1. Only those rows that meet < join condition > will be recorded in virtual table VT2

3. Join: if OUTER JOIN (such as left join and right join) is specified, unmatched rows in the reserved table will be added to virtual table VT2 as external rows to generate virtual table VT3. If the rug from clause contains more than two tables, steps 1-3 will be repeated for the result VT3 generated by the previous join connection and the next table, Until all tables are processed

4. WHERE: perform WHERE condition filtering on virtual table VT3. Only records conforming to < WHERE condition > will be inserted into virtual table VT4

5. group by: group the records in VT4 according to the columns in the group by clause to generate VT5

6. CUBE |ROLLUP: perform cube or rollup operations on table VT5 to generate table VT6

7. Having: apply having filter to virtual table VT6. Only records conforming to < having condition > will be inserted into virtual table VT7

8. Select: execute the select operation, select the specified column and insert it into the virtual table VT8

9. DISTINCT: de duplicate the records in VT8. Generate virtual table VT9

10. ORDER BY: record in virtual table VT9 according to < order_ by_ List > sort to generate virtual table VT10

11. LIMIT: fetch the record of the specified row, generate the virtual table VT11, and return the result

proposal

1. Try to avoid whole table scanning, such as SELECT*

2. Establish appropriate index

3. Use the appropriate storage engine

4. In the JOIN, try to use the small table LEFT JOIN large table

5. Unless necessary, try not to use ORDER BY,GROUP BY and distinct, and try to use index instead