If you want to learn more about MySQL, I have more information on my official account, Test Development Guide

After the spring recruitment, the department came to a lot of fresh meat, some of the students are not used MySQL, I reorganized my notes before, I hope it can be helpful to new students!


What are DML, DDL, DCL and TCL?

Data Manipulation Language (DML) :

They are SELECT, UPDATE, INSERT, and DELETE, and as their name suggests, these four commands are the languages used to manipulate data in the database

Data Definition Language (DDL) : The main commands include CREATE, ALTER, and DROP. DDL is used to define or change the structure of a TABLE, data types, links between tables, and constraints and other initialization tasks. DDL is mostly used when creating a TABLE

Data Control Language (DCL) : indicates the database Control function. Is a statement used to set or change the permissions of a database user or role, including (grant,deny, REVOKE, etc.) statements. By default, only sysadmin, DBCreator, DB_Owner, or DB_SecurityAdmin have the authority to execute the DCL

TCL (Transaction Control Language) : Transaction Control Language, including Set Transaction \rollback\savepoin


MySQL > select * from ‘MySQL’;

DDL

Creating a database

Create database Specifies the database name.Copy the code

Viewing the database list

show databases;Copy the code

Using a database

Use database name;Copy the code

Deleting a Database

Drop database Specifies the database name.Copy the code

Create table

CREATE TABLE IF NOT EXISTS `codes_user`(
->    `user_id` INT UNSIGNED AUTO_INCREMENT,
->    `user_title` VARCHAR(100) NOT NULL,
->    `user_author` VARCHAR(40) NOT NULL,
->    `submission_date` DATE,
->    PRIMARY KEY ( `user_id` )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;Copy the code

View the data table design

Methods a

Desc table nameCopy the code

Method 2

Show create table Specifies the name of the tableCopy the code

Delete table

Drop table table name;Copy the code

Clear the table

Truncate TABLE name;Copy the code

or

Delete from table name;Copy the code

Delete the field

ALTER TABLE DROP name;Copy the code


The new field

ALTER TABLE table_name ADD table_name INT;# INT is the field typeCopy the code

Changing the field Type

ALTER TABLE name ALTER TABLE name ALTER TABLE name CHAR(10);# change the field typeCopy the code

Modify the fields

ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE# BIGINRT is the data type for the new fieldCopy the code

Create indexes

Method a #Alter table alter table add index alter table add index ;Create a normal indexAlter table alter table add unique alter table add unique ;Create index uniqueAlter table alter table alter table add primary alter table add primary ;Create primary key index

Method # 2Create index Index name on Table name (field 1, field 2...) ;Create a normal indexCopy the code

Viewing Index Information

SHOW INDEX FROM table_name \G;Copy the code

Remove the index

ALTER TABLE TABLE name DROP INDEX INDEX name DROP INDEX Name ON TABLE nameCopy the code

DQL and DML

The query

The query result is deduplicated

Select distinct from table name;Copy the code

Limit limit and offset

Select * from table namelimit 5; Retrieve only 5 items of dataSelect * from table namelimit 5 offset 10; Start with number 5 and take 10Copy the code

Sort order by

Select * from table_name order by id;# ASC is in ascending order by defaultSelect * from table_name order by id desc;# DESC is in ascending order by defaultCopy the code

Data filtering

Or with the and

select * from TB_User where(score=100 or score=200) and age>18;# or has a lower priority than and requires parenthesesCopy the code


In with the not in

select * from TB_User where age in (20, 21)Copy the code


between.. and

select * from TB_User where age between 18 and 20Copy the code

Fuzzy matching keyword like

# % stands for any number of characters
# Perfect match for Joe
select * from TB_User where name like 'Joe';
# End with a threes
select * from TB_User where name like Zhang SAN '%';
# Begin with a threesome
select * from TB_User where name like 'Joe Smith %';
# result contains three
select * from TB_User where name like 'Joe Smith % %';
# "_" indicates any single character
select * from TB_User where name like '_ zhang';
select * from TB_User where name like 'Joe _';Copy the code

Re matches the keyword regexp

# Begin with a threesome
select * from TB_User where name regexp '^ zhang SAN';
# End with a threes
select * from TB_User where name regexp 'Joe $';
# "." Matches any character
select * from TB_User where name regexp Three ' '.;
# match student whose surname is Zhang, Zhao
SELECT * FROM tb_student WHERE `name` REGEXP [zhang zhao] '^';
# Match "Zhang Zhang 3"
SELECT * FROM tb_student WHERE `name` REGEXP 'a {3}';Copy the code

Concatenate field

select Concat(name, '(', age, ') ') from user order by age;Copy the code

Built-in function

select AVG(price) AS avg_price from TB_Order where orderID='100'; Return the average value of a column
select Count(*) from TB_User Count (*) does not omit null
select Max(score) from TB_Student # return maximum value
select Min(score) from TB_Student Return the minimum value
select SUM(score) from TB_Student where name='Lao wang'; # return sumCopy the code

grouping

Group by Groups groups by a field

select name, AVG(score) as avg_score from TB_Student group_by name; 
# Count the average score of each studentCopy the code


Having filters groups. Having is usually used with aggregate functions and should be used with group by

select name, AVG(score) as avg_score from TB_Student group_by name having avg_score>90; 
# Count the average score of each student and filter out the records with an average score greater than 90Copy the code

SELECT clause order

select > from > where > group by > having > order by > limit


JOIN query JOIN

Inline queries

TB_A and TB_B have the common field A
# write a
select a, b from TB_A, TB_B where TB_A.a = TB_B.a
# write two
select a, b from TB_A inner join TB_B on TB_A.a = TB_B.aCopy the code

Since the coupling

select student, score from TB_Student where tag = (select tag from TB_StudentTag where tag = 'good' );Copy the code

External links (left links and right links)

TB_A and TB_B have the common field A
select TB_A.a, TB_b.b from TB_A left outer JOIN TB_B ON TB_A.a = TB_B.a;Copy the code


All links

select TB_A.a, TB_B.b from TB_A full join TB_B on TB_A.a=TB_B.a;Copy the code

Attached is a diagram of various SQL join queries


Combined query UNION

# Usage scenario:
SQL > execute multiple queries on a single table and return data as a single query
# 2, in a single query, return similar structured data from different tables

# Data deunion
select A.key1 from A union select B.key1 from B;
# Data is not reunion all
select A.key1 from A union all select B.key1 from B;Copy the code

increase

INSERT INTO VALUES(1, 2...) ; INSERT INTO table_name (select * from table_name where table_name = 1) VALUES(1, 2...) ; INSERT INTO table_name (select * from table_name where table_name = 1) Select 1, 2... From table name (can be another table);Copy the code

Modify the

Update the table namesetField name = new valuewhere id = '100';Copy the code

delete

Select * from data where id = '100'The delete from the table namewhere id = '100';Copy the code

DCL

Database Authorization

grant all privileges  on *.* to root@The '%' identified by "root_pwd";Copy the code

Mysql replication table

Mysqldump -u username -p password -d database name table name > script name;

Export the entire database structure and data
mysqldump -h localhost -uroot -p123456 database > dump.sql
Export a single table structure and data
mysqldump -h localhost -uroot -p123456  database table > dump.sql
Export the entire database structure (no data)
mysqldump -h localhost -uroot -p123456  -d database > dump.sql
Export single table structure (no data)
mysqldump -h localhost -uroot -p123456  -d database table > dump.sqlCopy the code

How to perform slow query analysis

Explain select SQL statements

Results analysis

Type: const > eq_reg > ref > range > index > all

Key: If null, no index is used

Key_len: the length of the index. The shorter the better


SQL optimization method

3. Avoid select * from tb_name, avoid select * from tb_name Avoid select * from tb_namewhere name like 'xxx'5. Avoid group by, order by, and offset operations on large tables. 6Copy the code

Comparison of non-relational and relational databases

Relational databases: MySQL, SQL Server, Oracle, PostgreSQL, SQLite

Advantages:

1. With high query capability, you can perform complex queries

2. High consistency. During data synchronization, locks are generally used to ensure data reliability. During data processing, tables are blocked to ensure that other operations cannot change the data in the query range

3. Tables are logical and easy to understand

Disadvantages:

1. Do not apply to high concurrent read and write operations

2. It is not applicable to efficient read and write of massive data

3. Multiple layers and low expansibility

4. Maintaining consistency is expensive

5. It involves joint table query, complex and slow


Non-relational databases: Hbase, Redis, MongoDB, and Memcached

Advantages:

1. Since there is no relationship between data, it is easy to expand and query

2. Flexible data structure, each data can have a different structure

3. The query speed is faster because the consistency requirement is reduced

Disadvantages:

1. Data accuracy is not that high

What are the four characteristics of a transaction?

Features: atomicity, consistency, isolation, and durability (ACID)


What are dirty reads, unrepeatable reads, and phantom reads?

Dirty read

Dirty reads are when a transaction is accessing data and making changes to the data that have not yet been committed to the database, and then another transaction accesses the data and consumes it. (Not submitted for reading)


Unrepeatable read

Read the same data multiple times within a transaction. The same data is accessed by another transaction while the transaction is still active. Therefore, between the two reads in the first transaction, the data read by the first transaction may be different due to the modification of the second transaction. So what happens is that the data that is read twice in a transaction is different, (non-repeatable read)


Phantom read

A phenomenon that occurs when a transaction is not executed independently, for example, when the first transaction modifies data in a table that involves all rows in the table. At the same time, the second transaction also modifies the data in the table by inserting a new row into the table. Then, as if in an illusion, the user operating on the first transaction will discover that there are unmodified rows in the table.


How to avoid dirty reads, unrepeatable reads, and phantom reads?

Isolation level set to: Serializable


What are the transaction isolation levels?

From top to bottom, the level goes up

1. Read uncommitted (Read uncommited) [Lowest isolation level, high concurrent performance] (dirty read, unrepeatable read, illusory read)

2. Commit (read commited)

Repeatable read (lock all lines of read area) 4. Serializable

Careful students may notice that the SQL statement keywords in the sample are both uppercase and lowercase. Of course, both uppercase and lowercase execute successfully, but at the end of this article, there are two things to add.

1. Use uppercase to write the keywords of SQL statements

2. Do not use both uppercase and lowercase keywords in the same SQL statement