Knowledge integration - a primer on MySQL is enough

Basic concepts of SQL

Structured Query Language (SQL)

SQL is a domain-specific programming language for managing relational database management systems (RDBMSS), or for stream processing in relational stream Data management systems (RDSMS). The scope of SQL includes data add, delete, change and query (CRUD), database schema creation and modification, and data access control.

2. DB (Database)

Database is a collection of data stored in an organized way. In short, it can be regarded as an electronic file cabinet — the place where electronic files are stored. Users can add, intercept, update, delete and other operations to the data in the files. It is essentially a container for organized data.

3, DBMS (Database Management System)

Database management system is a large computer software management system designed for object database. Each DBMS has some degree of extension for SQL, this article is about the syntax of MySQL, using MySQL5.7.

Two, data storage characteristics

1, table,

In a relational database, a database table is a collection of two-dimensional arrays that can be used to store and represent relationships between data objects. The name of each table is unique, which in the case of MySQL means unique within the same library. Data stored in the same table must be of the same type. For example, to store user information and user orders, two tables must be used to store related data.

2. Rows and columns

A database table is composed of columns, which represent a field in the table. The set composed of all columns can be regarded as the table head of the table. Each column has a corresponding data type, which limits the type of data stored in the column. In contrast, a data record in a table is called a row.

Syntax and common commands

1. Grammatical norms

1) a case-insensitive, but suggest key caps, the table name and column name lowercase 2) SQL statements also includes the semicolon (statement terminator), multiple statements must be combined with a semicolon, although not every platform must add a semicolon after single statement, but it is defined as a standard part of the SQL syntax 3) note: single-line comments: # text annotation single-line comments: 4) Ambiguous table/field names should be escaped using ‘ ‘

2. Common commands

#1 View all libraries in the current database server
SHOW DATABASES;
#2 Open the specified library for which all the following operations are performed
USEThe library;Display all tables in the library
SHOW TABLES;
#4 Displays all tables in the specified library
SHOW TABLES FROMThe library;#5 View the structure of the specified tableDESC table name;Copy the code

Fourth, SQL language classification

1) Data Query Language (DQL) : Data Query Language, such as SELECT

2) Data Manipulate Language (DML): Data manipulation Language, such as INSERT, Update, and DELETE

3) Data Define Languge (DDL) : Data definition language, such as CREATE, DROP, and ALTER

4) TCL (Transaction Control Language) : Transaction Control Language such as COMMIT and rollback

5) Data Control Language (DCL) : database Control Language. Statements used to set or change the permissions of a database user or role, including (grant,deny, REVOKE, etc.) statements.

5. DQL

Table structure of the database used:

1, query/retrieve [SELECT]

SELECT statements in MySQL can be used to retrieve one or more columns of data. Standard SELECT does not have a persistent effect on the database. Queries allow the user to describe the required data, leaving it to the database management system (DBMS) to plan, optimize, and perform the physical operations needed to produce the results it selects. The query contains a series of fields with the final result, followed by the SELECT keyword. The asterisk (“*”) can also be used to specify that the query should return all fields of the table.

1.1 Basic Query

SELECT * FROM table_name; Select a virtual table

② Things to query can be constant values, expressions, fields, functions

③ Multiple columns can be retrieved simultaneously, separated by commas

④ All fields can be retrieved with *, but the retrieval performance will be reduced

⑤ Deduplication can be achieved by using the DISTINCT keyword, which is placed before the column name

⑥ In MySQL, limit x,y indicates the number of rows from the first row to the last row

#1 Select name from employees
SELECT name FROM employees;
Select * from employees
SELECT * FROM employees;
Select * from employees where name = 'phone'
SELECT name,phone FROM employees;
#4 The DISTINCT keyword is de-duplicated
SELECT DISTINCT id FROM employees;
#5 limit limits the number of entries that can be retrieved
SELECT id FROM employees LIMIT 3.5;
Copy the code

1.2 Sort Query

Grammar: SELECT queries content FROM table ORDER BY sorting the field expression | | | function is an alias of the ASC | DESC.

① The ORDER BY clause must be at the end of the SELECT statement

② It is possible to use non-retrievable columns for sorting

③ You can sort multiple columns, separated by commas

④ASC indicates ascending order (the default), DESC indicates descending order, and this flag applies directly to the preceding column name

#1 Retrieve the ID and phone fields in the Employees table and sort by SALARY
SELECT id,phone FROM employees ORDER BY salary;
#2 Retrieve the ID and phone fields in the Employees table and sort by SALARY and ID
SELECT id,phone FROM employees ORDER BY salary,id;
#3 Retrieve the ID and phone fields in the Employees table and sort by salary descending order
SELECT id,phone FROM employees ORDER BY salary DESC;
Copy the code

1.3 Conditional Query

Conditional query refers to adding filtering criteria to retrieve the desired data. In MySQL, the WHERE clause is used after the table name.

Syntax: SELECT query content FROM table WHERE filter condition;

① A filter condition can be an expression with the conditional operator (< >= <= >= <>), for example, id<>10, where <> means not equal to

② If you want to match a range value, you can use BETWEEN x AND y, which means that the data BETWEEN x AND y is retrieved. Similarly, you can use the IN operator to specify a range of conditions

MySQL > select * from ‘IS NOT NULL’; MySQL > select ‘IS NOT NULL’

(4) The WHERE clause allows multiple filter conditions to be separated by AND, OR OR. The AND operator is preferentially processed when both AND AND OR occur together

⑤ If you want to make fuzzy query on data, you can use the LIKE operator with wildcards (% means to match any character of any time, _ means to match a single character, [] means to match a character set, similar to re), non-text data type fields can not be fuzzy query

#1 Query the ids of employees whose salaries are greater than 1000
SELECT id FROM employees WHERE salary>1000;
#2 Query the id of an employee whose salary is between 1000 and 3000
SELECT id FROM employees WHERE salary BETWEEN 1000 AND 3000;
#3 Query the ID of the employee whose phone is not null
SELECT id FROM employees WHERE phone IS NOT NULL;
#4 Query the ID of the employee whose phone is null and salary is greater than 1000
SELECT id FROM employees WHERE phone IS NULL AND salary>1000;
#5 Select id from employee whose name ends with I character
SELECT id FROM employees WHERE name LIKE '%i';
Copy the code

1.4 Alias Query

Mysql > SELECT * FROM ‘AS’;

(1) The computed fields can be queried using alias names. The computed fields can be the concatenation of strings or the result of arithmetic expressions

In MySQL, concat is used to concatenate fields. Pass in the concatenate fields as parameters

(3) Alias query is used to replace query fields/expressions, and AS can be omitted

#1 Query the employee ID in the employees table and return e_id
SELECT id AS e_id FROM employees;
Select * from employees where salary *12
SELECT salary*12 AS y_salary FROM employees;
#3 Select employee ID and name as a field separated by Spaces
SELECT CONCAT(id.' '.`name`) id_name FROM employees;
Copy the code

1.5 Clustered Query

Syntax: SELECT aggregate function FROM table;

AVG returns the average value of a column, COUNT returns the number of rows in a column, MAX returns the maximum value of a column, MIN returns the maximum value of a column, and SUM returns the total value of a column

②COUNT(*) indicates the number of rows in the corresponding column. NULL is not ignored

③ When MAX is used on non-numeric data, the last row will be returned, and MIN will return the first row

(4) When using the aggregate function, you can query with an alias, and the alias should not be the actual column name in the table

#1 Returns the average and maximum value of employee salary
SELECT AVG(salary) salary_avg,MAX(salary) salary_max
FROM employees;
#2 Return the number of employees in the table
SELECT COUNT(*) sum_employee FROM employees;
Copy the code

Other functions:

1. Character functions

Concat (x, y,…). Splicing data X, Y…

Substr (STR,pos,len) intercepts a substring of length len starting at pos in the STR string

Upper (STR) converts the STR content to uppercase

Lower (STR) converts the STR content to lowercase

Trim (STR) Removes Spaces before and after the STR content

Ltrim (STR) Removes the space left of the STR content

Rtrim (STR) Removes the space to the right of the STR content

Replace (STR,from_str,to_str) replaces the from_str content in STR content with the to_str content

Lpad (STR,len,padstr) Left fills STR to len length using padstr

Rpad (STR,len,padstr) Right fill STR to len length using padstr

Instr (STR,substr) returns the index of the first occurrence of the substr substring of STR

Length (STR) Gets the length of STR

2. Mathematical functions

Round (x, y) Round (x, y) round (x, y) round (x, y) round (x, y) round (x, y) Round (x, y

Floor (x) rounds down on x

Ceil of x is rounded up with respect to x

Mod (a,b) = a- A /b*b, so if a is negative, the result is negative

Truncate (x, y) Truncates x from the Y decimal place

3. Date function

Now () Current system date + time

Curdate () Indicates the current system date

Curtime () Indicates the current system time

Str_to_date () converts characters to dates

Date_format () converts dates to characters

1.6 Group Query

SELECT * FROM table GROUP BY GROUP;

(1) Groups can be grouped BY a single field or multiple fields, separated BY commas (,). Note that the fields in SELECT must appear after GROUP BY at the same time

Select * from GROUP BY; select * from GROUP BY; select * from GROUP BY; select * from GROUP BY In the case of post-group filtering, you are operating on the post-group result set, and you need to use HAVING filtering after GROUP BY

③ It is better to query the grouped fields together with the aggregation function

④ Support sorting of grouped data, ORDER BY must be placed at the end of the statement

#1 Query the number of people in each department and sort them by department ID
SELECT COUNT(*),department_id FROM employees 
GROUP BY department_id ORDER BY department_id;
#2 Query the average salary of each job and rank it
SELECT AVG(salary),job_id FROM employees 
GROUP BY job_id ORDER BY salary;
#3 Query which department has more than 2 employees
SELECT COUNT(*) sum_person,department_id FROM employees 
GROUP BY department_id HAVING sum_person>2;
Copy the code

1.7 Connection Query

In a relational database, the mechanism used to associate tables in a SELECT statement is called join. Simply put, join queries are used when the data in the query comes from different tables. The standard for connecting queries in MySQL is SQL92, SQL99, and the SQL99 syntax is recommended in ANSI SQL. The types of join queries are inner join, outer join, and cross join.

1) SQL92 standard: equivalent connection/non-equivalent connection/self-connection

SELECT * FROM table 1, table 2… WHERE join condition;

Table 1 rows * table 2 rows * table 1 rows * table 2 rows *

② You can alias the table. The original name cannot be used when querying the table after the alias

③ If you need to filter after the connection query, you can use AND after the connection condition to filter the condition

(4) Self-join indicates the join of the same table. In this case, you must alias the table

#1 Query the department name corresponding to the employee name
SELECT `name`,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
Select * from department where phone number is not empty
SELECT `name`,department_name,phone
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`phone` IS NOT NULL;
#3 Query employee name and superior name
SELECT e.`id` id_employee,e.`name` name_employee,
m.`id` id_manager,m.`name` name_manager
FROM employees e,employees m
WHERE e.`manager_id`=m.`id`;
Copy the code

2) SQL99 standard

Syntax: SELECT query content FROM table 1 JOIN type JOIN table 2 IN JOIN condition;

Connection types: INNER, LEFT, RIGHT, FULL, CROSS

① Sql99 standard connection query advantage is in the statement connection conditions and filter conditions to achieve separation

② There is no difference between internal join and SQL92 standard except syntax difference

Mysql > select * from tablea where tableA = null; mysql > select * from tableA where tableA = null; mysql > select * from tableA where tableA = null; Therefore, for this assumption, outer join query result = inner join result + data in table A but not in table B

④ For LEFT outer JOIN, LEFT JOIN is the main table; For a RIGHT outer JOIN, the RIGHT side of the RIGHT JOIN is the main table

⑤ Join result = join result + join result = join result + join result = join result + join result = join result + join result = join result

#1 Query employee name and corresponding department name
SELECT `name`,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
Select * from department where phone number is not empty
SELECT `name`,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.phone IS NOT NULL;
#3 Query employee name and superior name
SELECT e.`id`,e.`name`,m.`id`,m.`name` 
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`id`;
#4 Query which department has no employees
SELECT department_name,employees.`id`
FROM departments
LEFT OUTER JOIN employees
ON employees.`department_id`=departments.`department_id`
WHERE employees.`id` IS NULL
GROUP BY departments.`department_name`;
Copy the code

1.8 the subquery

Another complete SELECT statement is nested in a query statement. The nested SELECT statement is called a sub-query or internal query, and the query statement outside is called the primary query or external query. According to the result set, there are four sub-queries, which are standard quantum query (result set row and column), column sub-query (result set row and column), row sub-query (result set row and column), table sub-query (result set is generally multi-row and multi-column).

SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM) WHERE/HAVING (SELECT * FROM (SELECT * FROM) WHERE/HAVING (SELECT * FROM (SELECT * FROM) WHERE/HAVING (SELECT * FROM) EXISTS;

① All subqueries are placed in parentheses

Subqueries can be placed after FROM, SELECT, WHERE, and HAVING, but are usually placed on the right side of a condition

③ The sub-query takes precedence over the primary query, which uses the results of the sub-query

④ Subqueries with single-line results are generally matched with single-line operators: > <= <> >= <=. Subqueries with multi-line results are generally matched with multi-line operators: ANY, ALL, IN, NOT IN

#1 Query the user whose salary is higher than Lily's
SELECT * FROM employees
WHERE salary>(
	SELECT salary FROM employees 
    WHERE last_name = "lily"
);
#2 Query user information of the company with the lowest salary
SELECT * FROM employees 
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);
#3 Returns the names of all employees in the corresponding department whose department location id is 1 or 2
SELECT name FROM employees
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id
	IN (1.2));#4 Query information about the employee with the smallest employee number and the highest salary
SELECT * FROM employees
WHERE (id,salary)=(
	SELECT MIN(id),MAX(salary)
	FROM employees
);
#5 Query the number of employees in each department
SELECT departments.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.id=d.id
) sum_person
FROM departments d;
#6 Query the name of the department to which an employee belongs
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT * FROM employees e 
	WHERE e.`department_id`=d.`department_id`
);
Copy the code

1.9 Paging Query

Syntax: SELECT query contents FROM table LIMIT start index, number of entries;

① The start item index starts at 0

The limit clause is placed at the end of the query statement

SELECT * FROM table LIMIT (page-1) *sizePerPage,sizePerPage; SizePerPage is the number of items displayed on each page, and page is the number of pages to be displayed

#1 Query the first five employees
SELECT * FROM employees
LIMIT 0.5;
#2 Query employee information items 11 to 25
SELECT * FROM employees
LIMIT 10.15; 
#3 Query the information of the top 10 employees whose phone number is not empty
SELECT * FROM employees
WHERE phone IS NOT NULL
ORDER BY salary DESC
LIMIT 0.10;
Copy the code

1.10 Joint Query

Query 1 UNION [ALL] query 2…

① The number of columns queried in multiple query statements must be the same

② By default, the query result is the table header of the first query statement

③UNION means heavy, UNION ALL means not heavy

#1 Query information about the employee whose department id is >100 and whose name contains a
SELECT * FROM employees WHERE department_id>100
UNION
SELECT * FROM employees WHERE `name` LIKE '%a%';
Copy the code

2. INSERT

INSERT INTO table name VALUES(1,…) ;

INSERT INTO table name SET INSERT INTO table name SET INSERT INTO table name SET INSERT INTO table name SET INSERT INTO table name SET INSERT INTO table name SET INSERT INTO table name SET INSERT INTO table name ;

(1) The table field type and the value type passed in must be the same or compatible, and must correspond to each other in the same number

(2) Columns in the table that are allowed to be empty can be filled with NULL. Columns that are not allowed to be empty must be inserted

(3) You can omit all fields following the table name. By default, all fields are inserted in the table in the same order as those stored in the table

Insert only required fields
INSERT INTO stuinfo(id.NAME)
VALUES(14.'Joe');
#2 Insert fields as needed
INSERT INTO stuinfo(NAME.id,phone,sex)
VALUES('bill'.15.'15812345678'.'male');
#3 Omit the field name and insert it all by default
INSERT INTO stuinfo
VALUES(16.'Cathy'.'male'.'1999-12-01'.'15212345678'.NULL)
#4 Insert using syntax two
INSERT INTO stuinfo
SET id=17.NAME='Daisy',sex='male',phone='13912345678';
Copy the code

3, Modify (UPDATE)

3.1 Modifying a single table

UPDATE table_name SET table_name = 1, table_name = 2, table_name = 2,… [Screening conditions];

3.2 Modifying multiple tables

SQL > alter table 1; UPDATE table 2; WHERE join condition AND filter condition;

SQL > UPDATE table 1 JOIN type JOIN table 2 ON SET field = value,… WHERE filter criteria;

#1 change the number of names starting with nan in stuinfo to 12300
UPDATE stuinfo
SET phone='12300'
WHERE `name` LIKE 'nan%';
#2 alter table stuinfo select * from stuinfo where id = 1 and phone = 188000
UPDATE stuinfo
SET `name`='jack',phone='188000'
WHERE id=1;
#3 Change the remarks of the employee whose department is "test" to "test"
UPDATE employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
SET e.remark='test'
WHERE d.`department_name`='test';
Copy the code

4. DELETE (DELETE, TRUNCATE)

4.1 the DELETE

DELETE FROM table_name WHERE table_name = 1;

Table DELETE (sql92) : DELETE the table name FROM table 1, table 2 WHERE connection condition AND the filter;

Table DELETE (sql99) : DELETE the table name FROM table 1 connection type ON the JOIN table 2 connection condition WHERE the filter;

4.2 TRUNCATE

Syntax: TRUNCATE TABLE name;

①DELETE Deletes data based on requirements. TRUNCATE deletes data in the entire table

②TRUNCATE cannot add a WHERE condition, while DELETE can add a WHERE condition

Under the same circumstances, TRUNCATE has higher efficiency

4 TRUNCATE After a table with self-growing columns is deleted, the self-growing column starts from 1 if data is inserted. DELETE After a table with self-growing columns is deleted, the self-growing column starts from the last breakpoint if data is inserted

⑤ The TRUNCATE deletion cannot be rolled back, while the DELETE deletion can be rolled back

#1 Delete user information whose mobile phone number ends in 00
DELETE FROM stuinfo WHERE phone LIKE "% 00";
#2 Delete the information about the employee whose department is test
DELETE e
FROM departments d
INNER JOIN employees e
ON e.`department_id`=d.`department_id`
WHERE d.`department_name`='test';
Copy the code

DML language

1. Data types

1.1 the numeric

1) integers

TINYINT(1 byte) SMALLINT(2 bytes) MEDIUMINT(3 bytes) INT/INTEGER(4 bytes) BIGINT(8 bytes)

(1) The default is signed. If UNSIGNED is used, it should be marked UNSIGNED

② If the inserted value exceeds the range, an error will be raised

(3) When these types are used, the form INT(3) may appear. In fact, the number in parentheses only indicates the maximum width of the display, not the number of bytes

If the length is not enough, fill in 0 on the left. After using ZEROFILL, the corresponding field will become UNSIGNED. If the length is not set, the default length is given

2) decimal

FLOAT(M,D) DOUBLE(M,D)

2.2) Fixed-point type DEC(M,D) DECIMAL(M,D)

(1) M: integer part + decimal part length, D: decimal part length, if M is out of the range will report an error, D is out of the range will be rounded

②M D can be omitted if DECIMAL defaults to (10,0)

③FLOAT requires 4 bytes, DOUBLE 8 bytes, and DEC and DECIMAL M+2 bytes

The precision of fixed point type is higher than that of floating point type

1.2 character

CHAR(M): stores fixed-length characters, occupying large space and high efficiency. M indicates the length and can be omitted. The default value is 1

VARCHAR(M): stores variable length characters, occupying small space and low efficiency. M indicates the length and cannot be omitted

2) Long text:

TEXT: Saves non-binary strings

3) Others:

ENUM: Stores enumeration types. SET: Stores SET types

(1) Any type of character must be enclosed in parentheses.

VARCHAR and TEXT are variable length types, and their storage requirements depend on the actual length of the column value (the final length is the actual length +1).

1.3 Binary Type

BIT(M) : BIT field type, approximately (M+7)/8 bytes

BINARY(M) : fixed length BINARY string, M bytes

VARBINARY(M) : variable length binary string, M+1 bytes

TINYBLOB(M) : L+1 bytes, L is the actual size

BLOB(M) : L+2 bytes, L is the actual size

MEDIUMBLOB(M) : L+3 bytes, L is the actual size

LONGBLOB(M) : L+4 bytes, L is the actual size

1.4 Date and time type

TIME: indicates the TIME in HH:MM:SS format

DATE: indicates the DATE in the format of YYYY-MM-DD

DATETIME: indicates the date + time in the format of YYYY-MM-DD HH:MM:SS

TIMESTAMP: date + time, also known as TIMESTAMP, in the format yyyy-mm-dd HH:MM:SS

YEAR: indicates the YEAR in the format of YYYY

The DATETIME value is 8 bytes. The value ranges from 1000 to 9999, regardless of the time zone

②TIMESTAMP is 4 bytes, indicating a range of 1970-2038, which is affected by time zone

③DATETIME stores date data in the format actually entered, regardless of the time zone. The TIMESTAMP value is stored in UTC (Universal Standard Time) format. The current time zone is converted when stored and then converted back to the current time zone when retrieved. That is, the time displayed varies according to the current time zone.

2. Management of tables

2.1 the new table

Syntax: CREATE TABLE name (column name column type [constraint], column name column type [constraint]…) ;

You can specify the DEFAULT value for a field by the DEFAULT keyword after the column type

# only basic syntax is shown here for ease of reading.
Create a table of books
CREATE TABLE `books`(
	id INT,
	bname VARCHAR(20),
	price DOUBLE,
	authorId INT,
	publicDate DATETIME
);
Create an authors table
CREATE TABLE `authors`(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(20));Copy the code

2.2 modify the table

Grammar: the ALTER TABLE TABLE name CHANGE | the MODIFY | ADD | DROP COLUMN COLUMN names [] COLUMN type constraints

① To CHANGE the column name, use CHANGE

② MODIFY the type or constraint of a column

③ ADD/DROP a column

4 Change the table name to RENAME

Alter table books publicDate = pubDate; type = DATETIME
ALTER TABLE books CHANGE COLUMN publicDate pubDate DATETIME;
Alter table books alter table pubDate TIMESTAMP
ALTER TABLE books MODIFY COLUMN pubDate TIMESTAMP;
Add an annual column of type DOUBLE to the authors table
ALTER TABLE `authors` ADD COLUMN annual DOUBLE;
Delete the annual field from the authors table
ALTER TABLE `authors` DROP COLUMN annual;
Rename the authors table to books_authors
ALTER TABLE `authors` RENAME TO books_authors;
Copy the code

2.3 delete table

Syntax: DROP TABLE [IF EXISTS] Specifies the name of the TABLE.

#1 add IF EXISTS to delete the table
DROP TABLE IF EXISTS books;
Copy the code

2.4 the replicated table

1) Copy only the table structure

Syntax: CREATE TABLE the TABLE to which the copy is made LIKE the TABLE to which the copy is made;

Copy the structure of the authors table to copy_authors
CREATE TABLE `copy_authors` LIKE `authors`;
Copy the code

2) Replicate table structure + all data

Syntax: CREATE TABLE copied to TABLE SELECT * FROM copied TABLE;

#1 Copy the structure of the authors table and all the data to copy_authors2
CREATE TABLE `copy_authors2` SELECT * FROM `authors`;
Copy the code

3) Replication structure + partial data

Syntax: CREATE TABLE replicated TABLE SELECT * FROM replicated TABLE WHERE filter criteria;

Copy the structure of the authors table with data id 1 to copy_authors3
CREATE TABLE `copy_authors3` SELECT * FROM `authors` WHERE id=1;
Copy the code

4) Copy part of the structure without copying data

SELECT column name FROM copied TABLE WHERE FALSE;

#1 Copy the id, nation column structure of the authors table to copy_AUTHORS4
CREATE TABLE `copy_authors4` SELECT id,nation FROM `authors` WHERE FALSE;
Copy the code

3, constraints,

3.1 an overview of the

Constraints Restrict data in a table to ensure data accuracy and reliability.

3.2 classification

There are five types of constraints in MySQL

NOT NULL — Ensure that the field is NOT NULL

Ensure that fields have default values

The PRIMARY KEY must be unique and not empty

(4) UNIQUE — ensure that the field is UNIQUE, can be empty

⑤FOREIGN KEY — Ensure that the value of this field is from the associated column value of the primary table, and the FOREIGN KEY is added from the table

According to their definition position, they can be divided into column level constraints and table level constraints. Column level constraints support constraint definition except key, table level constraints support non-null and default, other support.

3.3 Adding Constraints

1) Add column level constraints when creating a table

Syntax: CREATE TABLE name (column name 1 column type 1 constraint 1, column name 2 column type 2 constraint 2,…) ;

CREATE TABLE stuinfo2(
	id INT PRIMARY KEY.# key
	stuName VARCHAR(20) NOT NULL.# is not empty
	seat INT UNIQUE.# only
	age INT DEFAULT 18.# the default
    gender CHAR(1),
    majorId INT
);
Copy the code

2) Add table level constraints when creating a table

Syntax: [Constraint name] Constraint type

CONSTRAINT CONSTRAINT name FOREIGN KEY REFERENCES Primary table

CREATE TABLE stuinfo3(
	id INT,
	stuName VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorId INT.CONSTRAINT pk PRIMARY KEY(id),# key
	CONSTRAINT uq UNIQUE(seat),# only
	CONSTRAINT fk FOREIGN KEY(majorId) REFERENCES major(id)Outside the # key
);
Copy the code

3) Add column level constraints when modifying tables

ALTER TABLE TABLE name MODIFY COLUMN COLUMN type constraint;

Stuinfo4 table stuinfo4 table stuinfo4 table stuinfo4 table stuinfo4 table stuinfo4
#1 Add a non-null constraint
ALTER TABLE stuinfo5 MODIFY COLUMN stuName VARCHAR(20) NOT NULL;
#2 Add default constraints
ALTER TABLE stuinfo5 MODIFY COLUMN age INT DEFAULT 18;
#3 Add primary key constraint
ALTER TABLE stuinfo5 MODIFY COLUMN id INT PRIMARY KEY;
#4 Add unique key
ALTER TABLE stuinfo5 MODIFY COLUMN seat INT UNIQUE;
Copy the code

4) Add table level constraints when modifying a table

ALTER TABLE ADD [constraint name] constraint type [constraint name]

#1 Add primary key constraint
ALTER TABLE stuinfo5 ADD PRIMARY KEY(id);
#2 Add a unique key
ALTER TABLE stuinfo5 ADD UNIQUE(seat);
#3 Add foreign keys
ALTER TABLE stuinfo5 ADD CONSTRAINT fk_stuinfo5_major FOREIGN KEY(majorId) REFERENCES major(id);
ALTER TABLE stuinfo5 ADD FOREIGN KEY(majorId) REFERENCES major(id);
Copy the code

3.4 Constraints on Deletion

Syntax 1: ALTER TABLE TABLE name MODIFY COLUMN type constraint

ALTER TABLE ALTER TABLE DROP ALTER TABLE ALTER TABLE DROP

#1 Remove the non-null constraint
ALTER TABLE stuinfo5 MODIFY COLUMN stuName VARCHAR(20) NULL;
#2 Remove the default constraint
ALTER TABLE stuinfo5 MODIFY COLUMN age INT ;
#3 Delete primary key
ALTER TABLE stuinfo5 DROP PRIMARY KEY;
#4 Delete unique
ALTER TABLE stuinfo5 DROP INDEX seat;
#5 Delete the foreign key
ALTER TABLE stuinfo5 DROP FOREIGN KEY fk_stuinfo5_major;
Copy the code

DDL language

1. Library management

1.1 the new library

Syntax: CREATE DATABASE [IF NOT EXISTS] DATABASE name;

#1 add IF NOT EXISTS to create database
CREATE DATABASE IF NOT EXISTS dbtest;
Copy the code

1.2 delete library

Syntax: DROP DATABASE [IF EXISTS] The name of the DATABASE;

#1 add IF EXISTS to delete IF database EXISTS
DROP DATABASE IF EXISTS dbtest;
Copy the code

8. TCL Language

1, the transaction

1.1 Transaction Concept

Transaction: A set of logical units of operation that change data from one state to another. Note that of MySQL’s storage engine, only InnoDB supports transactions.

Transaction processing (transaction operations) : Ensure that all transactions are executed as a unit of work, and that this execution cannot be changed even if a failure occurs. When multiple operations are performed in a transaction, either all transactions are committed, and the changes are saved permanently; Either the database management system will abandon all changes made and the entire transaction will rollback to its original state.

1.2 Transaction Characteristics (ACID)

Atomicity refers to the fact that a transaction is an indivisible unit of work in which all or none of the operations occur.

2) Consistency transactions must change the database from one Consistency state to another Consistency state.

The Isolation of a transaction means that the execution of a transaction cannot be disturbed by other transactions, that is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrently executed transactions cannot interfere with each other.

Durability means that once a transaction is committed, its changes to data in the database are permanent and subsequent operations and database failures should not affect them in any way.

1.3 Transaction Creation

1) Implicit transactions

Implicit transactions have no obvious start and end flags, such as INSERT, DELETE, UPDATE, and so on.

2) Explicit transactions

Explicit transactions have obvious start and end flags, so make sure automatic commit is disabled before creating a transaction.

Step 1: Disable the automatic submission function

SET autocommit=0;
Copy the code

Step 2: Write SQL statements in the transaction (SELECT, INSERT, UPDATE, DELETE…)

Step 3: End the transaction and COMMIT the transaction with COMMIT. ROLLBACK transaction with ROLLBACK; Use ROLLBACK with SAVEPOINT to ROLLBACK to the SAVEPOINT

Note that if TRUNCATE is used to delete the table, data will still be deleted even if ROLLBACK is used. In other words, CREATE and DROP operations cannot be undone even if they are rolled back.

3) instance

Here is a common transfer scenario to illustrate how a transaction is created and what it does

Suppose you have a user information table: there are id, name, deposit three fields

Now Zhang SAN transfers 100 yuan to Li Si. Normally, the payment of Zhang SAN’s account and the payment of Li Si’s account should be a whole and cannot be completed independently. Therefore, this process can be called a transaction in the database. You can actually set the appropriate commit timing and rollback premise during development by catching exceptions or adding certain conditions.

When the transaction is committed, the transaction takes effect and the data is persisted to the database
SET autocommit=0;
UPDATE money_test SET deposit=1100 WHERE id=1;
UPDATE money_test SET deposit=900 WHERE id=2;
COMMIT;
#2 Simulate an abnormal rollback of the transaction, in which case the transaction is rolled back and the data remains unchanged
SET autocommit=0;
UPDATE money_test SET deposit=1100 WHERE id=1;
UPDATE money_test SET deposit=900 WHERE id=2;
ROLLBACK;
Copy the code

2. Isolation level

2.1 Transaction concurrency problem

For multiple transactions running at the same time, when these transactions access the same data in the database, all kinds of concurrency problems can result if the necessary isolation mechanisms are not in place:

1) Dirty reads: for two transactions T1 and T2, T1 reads fields that have been updated by T2 but have not yet been committed. After that, if T2 rolls back, what T1 reads is temporary and invalid.

2) Unrepeatable reads: for two transactions T1 and T2, T1 reads a field, and T2 updates the field. T1 then reads the same field again with a different value.

3) Phantom read: For two transactions T1 and T2, T1 reads a field from a table, and T2 inserts some new rows in the table. Later, if T1 reads the same table again, there will be a few more rows.

2.2 Transaction Isolation

The database system must have the ability to isolate the individual transactions running concurrently so that they do not interfere with each other and avoid various concurrency problems. The degree to which a transaction is isolated from other transactions is called the isolation level. The database specifies multiple transaction isolation levels, which correspond to different levels of interference. The higher the isolation level, the better the data consistency, but the weaker the concurrency.

2.3 Isolation Level

MySQL supports four transaction isolation levels. The default transaction isolation level is REPEATABLE READ.

#1 Check the current isolation level:
SELECT @@tx_isolation;
MySQL > set isolation level to READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Set the isolation level of the database system to READ COMMITTED:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Copy the code

Nine, view,

A view is a virtual table that is used just like a regular table. The data in the view is from the table queried in a custom statement and is dynamically generated when the view is used, preserving only SQL logic. The data in the view depends on the data in the real table, and if the data in the real table changes, the data displayed in the view also changes. It is usually used to replace SQL statements with high reuse or complex structure, and sometimes to protect data to improve security.

1. Create a view

CREATE VIEW VIEW name AS query statement;

Suppose that for the employees table in this article: now you just want to open the name and phone to others to view, do you want to create a new table? What if I have new data? Maybe creating a view is a good way to solve this problem. We can create a view that contains only the name and phone columns, and since it is just a representation of the original table, we don’t have to worry about data synchronization.

Create a view named employees_view
CREATE VIEW employees_view 
AS 
SELECT `name`.`phone` FROM employees;
Copy the code

2. View

Syntax 1: DESC view name; [Query field information]

Syntax 2: SHOW CREATE VIEW View a detailed definition of the view

3. Modify the view

Syntax 1: CREATE OR REPLACE VIEW VIEW name AS query statement;

Syntax 2: ALTER VIEW VIEW name AS;

4. Delete the view

DROP VIEW VIEW name 1, VIEW name 2,… ;

5. View update

If you update data in a view through insert, modify, and delete operations, you are essentially updating the data in the base table referenced by the view, so the data definition for the base table must be met. For updatable views, there must be a one-to-one relationship between the rows in the view and the rows in the base table. Views with the following characteristics cannot be updated:

① Contains keywords: GROUP BY, DISTINCT, HAVING, UNION, UNION ALL.

② Views are constant views

③SELECT contains subqueries

④ The data source is a view that cannot be changed, and the FROM in the query statement is a view that cannot be changed

Stored procedures and functions

1, variables,

Variables in MySQL can be divided into system variables and custom variables.

1) System variables include session variables and global variables. It should be noted that the server will assign initial values to global variables every time the server restarts. That is to say, the modified global variables will be restored to their original values after the restart (that is, they cannot be restarted across).

#1 View all session variables
SHOW SESSION VARIABLES; 
#2 View all global variables
SHOW GLOBAL VARIABLES; 
#3 View the variables that meet the criteria
SHOW SESSION VARIABLES LIKE '%char%';
SHOW GLOBAL VARIABLES LIKE '%char%';
#4 View the specified variable
SELECT@ @ global variable nameSELECT@@session. Variable name orSELECT@ @ variable names#5 assigns a value to the global variableMethods (1) :set globalVariable name = value mode ② :set@@global. Variable name = value#6 assigns the session variableMethods (1) :set sessionVariable name = value mode ② :set@@session. Variable name = value mode ③ :set@@ Variable name = valueCopy the code

2) Custom variables are divided into user variables and local variables. User variables are valid in the current connection/session set, while local variables are valid only in BEGIN END and need to be defined in the header. Custom variables need to be declared, assigned, and then used.

# user variables
Declare and initializeMethods (1) :SET@ Variable name = value; (2) :SET@ Variable name := value; (3) :SELECT@ Variable name := value;Assignment # 2Methods (1) :SET@ Variable name = value; (2) :SET@ Variable name := value; (3) :SELECT@ Variable name := value; (4) :SELECTfieldINTO@ the variable nameFROMTable; (Single value required)#3 View variables
SELECT@ Variable name;Copy the code

# local variables
Declare and initialize
DECLAREVariable name typeDEFAULT 值
Assignment # 2Methods (1) :SETVariable name = value; (2) :SETVariable name := value; (3) :SELECT@ Variable name := value; (4) :SELECTfieldINTOThe variable nameFROMTable;Use # 3
SELECTThe variable name.Copy the code

2. Stored procedures

A stored procedure is a collection of pre-compiled SQL statements, similar to batch statements. To the caller, the stored procedure encapsulates the SQL statement, and the caller does not have to worry about the implementation of the logical function. The use of stored procedures improves the reuse of SQL statements and helps reduce network load.

2.1 Creation and Invocation

CREATE PROCEDURE Name (parameter list) BEGIN The body of the stored PROCEDURE (a group of valid SQL statements) END;

CALL syntax: CALL end flag of stored procedure name

① If the stored procedure is only an SQL statement, omit BEGIN END

② The SQL statement of the stored procedure body is required to use; End, so you need to reset the end flag of the stored procedure with the DELIMITER end flag, for example: DELIMITER sets the end flag to set the end flag to set the end flag to

The parameter list consists of three parts: parameter mode + parameter name + parameter type. There are three parameter modes: INOUT INOUT. IN indicates that the parameter is used as input, OUT indicates that the parameter is used as output, and INOUT indicates that the parameter can be used as input or output

④ If the OUT/INOUT mode is used, you need to define a variable to receive the parameter values

The following examples are performed in the MySQL command line client

Select * from stuinfo where name, sex, phone
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	SELECT `name`.`sex`.`phone` FROM stuinfo;
END $
# 2 call
CALL myp1()$
Copy the code

Create a stored procedure with an IN mode parameter to query the student information based on the incoming name
CREATE PROCEDURE myp2(IN uname VARCHAR(50))
BEGIN
	SELECT * 
	FROM stuinfo
	WHERE stuinfo.`name`=uname;
END $
# 2 call
CALL myp2("Zhang") $
Copy the code

Create a stored procedure with OUT mode parameters: used to query the corresponding student's phone based on the incoming name and return it as a parameter value
CREATE PROCEDURE myp3(IN uname VARCHAR(50),OUT uphone VARCHAR(11))
BEGIN
	SELECT phone INTO uphone
	FROM stuinfo
	WHERE stuinfo.`name`=uname;
END $
Create a variable, call the stored procedure, and view the value of the variable
SET @phone_out=' ' $
CALL myp3("Zhang",@phone_out) $
SELECT @phone_out $
Copy the code

#1 Create a stored procedure with INOUT mode: pass in num1, num2 and return the square of each
CREATE PROCEDURE myp4(INOUT num1 INT ,INOUT num2 INT)
BEGIN
	SET num1=num1*num1;
	SET num2=num2*num2;
END $
Create a variable, call the stored procedure, and view the value of the variable
set @num1=10 $
SET @num2=6 $
call myp4(@num1,@num2) $
select @num1,@num2 $
Copy the code

2.2 Deleting a Stored Procedure

Syntax: DROP PROCEDURE Name of the stored PROCEDURE

2.3 Viewing Stored Procedures

Syntax: SHOW CREATE PROCEDURE Name of the stored PROCEDURE

3, functions,

A function has the same advantages and uses as a stored procedure. The difference is that a stored procedure can have no return or multiple returns, while a function can only have one return.

3.1 Creation and Invocation

CREATE syntax: CREATE FUNCTION FUNCTION name (parameter list) RETURNS Return type BEGIN FUNCTION body END

Call syntax: SELECT function name (parameter) end flag

(1) The parameter list includes parameter names and parameter types

② There must be a RETURN statement in the function body. The RETURN statement may not be placed at the end

③ If the function body has only one sentence, you can omit begin end

4 DELIMITER is also required to set the end flag

#1 Create a function that returns the number of stuinfo bytes
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE countx INT DEFAULT 0;
	SELECT COUNT(*) INTO countx
	FROM stuinfo;
	RETURN countx;
END $
# 2 call
SELECT myf1()$
Copy the code

#1 Create an input function that returns a phone number based on the incoming employee name
CREATE FUNCTION myf2(uname VARCHAR(50)) RETURNS VARCHAR(11)
BEGIN
	DECLARE uphone VARCHAR(11) DEFAULT ' ';
	SELECT phone INTO uphone 
	FROM stuinfo
	WHERE `name`=uname;
	RETURN uphone; 
END $
# 2 call
SELECT myf2('Joe') $
Copy the code

3.2 Delete Function

Syntax: DROP FUNCTION The name of the FUNCTION

3.3 Viewing Functions

Syntax: SHOW CREATE FUNCTION Specifies the FUNCTION name

11. Process control

1, the branch

1.1 the IF function

Syntax: IF(condition, value 1, value 2)

① Simple double branch can be applied anywhere

② If the condition is true, return 1, otherwise return 2

1.2 IF construct

Syntax: IF condition 1 THEN statement 1; ELSEIF condition 2 THEN statement 2; . 【ELSE statement n;】 END IF;

① Implement multiple branches, need to be used in BEGIN END

1.3 CASE function

Case 1: Used to achieve equivalence judgment

Grammar:

CASE variable expression | | field the WHEN to determine the value of the THEN returns the value of 1 or 1 the WHEN to determine the value of the THEN returns the value of 2 or 2; . ELSE The value n or statement n to return; END CASE;Copy the code

Case two: Used to realize interval judgment

Grammar:

CASE WHEN condition 1 THEN return 1 or statement 1; WHEN condition 2 THEN returns the value 2 or statement 2; . ELSE The value n or statement n to return; END CASE;Copy the code

① As expressions, nested in other statements, can be placed anywhere

② As an independent statement, place it in BEGIN END

(3) If the value of WHEN is true, execute the statement after THEN and end the CASE; if not, execute the statement or value of ELSE

ELSE can be omitted. If ELSE is omitted and all WHEN conditions are not met, NULL is returned

2, circulation

Cycle control has two parts: ITERATE, which indicates the cycle continues, similar to continue; The LEAVE indicator breaks out of the loop, similar to break

2.1 the WHILE loop

WHILE loop condition DO loop body; END WHILE 【 label 】;

2.2 the LOOP cycle

Mainly used for simple infinite loops

Syntax: [label:] LOOP body END LOOP;

2.3 REPEAT loop

【 tag: 】 REPEAT; Conditions for ending the loop UNTIL; END REPEAT [tag];

References:

[1] SQL Must Know must Know

[2]”MySQL Database biancheng.net”

[3]”Mysql > select * from tinyInt, smallINT, mediumInt, int, bigINT”

[4]”Star’s Tech Blog-CSDN Blog SQL query data”

[5]”For example, mysql-satisfactions (゜-゜) analysis ロ cheers ~- ゜ bili”

Knowledge integration – a primer on MySQL is enough

Basic concepts of SQL

Structured Query Language (SQL)

2. DB (Database)

3, DBMS (Database Management System)

Two, data storage characteristics

1, table,

2. Rows and columns

Syntax and common commands

1. Grammatical norms

2. Common commands

Fourth, SQL language classification

5. DQL

1, query/retrieve [SELECT]

1.1 Basic Query

1.2 Sort Query

1.3 Conditional Query

1.4 Alias Query

1.5 Clustered Query

1.6 Group Query

1.7 Connection Query

1.8 the subquery

1.9 Paging Query

1.10 Joint Query

2. INSERT

3, Modify (UPDATE)

3.1 Modifying a single table

3.2 Modifying multiple tables

4. DELETE (DELETE, TRUNCATE)

4.1 the DELETE

4.2 TRUNCATE

DML language

1. Data types

1.1 the numeric

1.2 character

1.3 Binary Type

1.4 Date and time type

2. Management of tables

2.1 the new table

2.2 modify the table

2.3 delete table

2.4 the replicated table

3, constraints,

3.1 an overview of the

3.2 classification

3.3 Adding Constraints

3.4 Constraints on Deletion

DDL language

1. Library management

1.1 the new library

1.2 delete library

8. TCL Language

1, the transaction

1.1 Transaction Concept

1.2 Transaction Characteristics (ACID)

1.3 Transaction Creation

2. Isolation level

2.1 Transaction concurrency problem

2.2 Transaction Isolation

2.3 Isolation Level

Nine, view,

1. Create a view

2. View

3. Modify the view

4. Delete the view

5. View update

Stored procedures and functions

1, variables,

2. Stored procedures

2.1 Creation and Invocation

2.2 Deleting a Stored Procedure

2.3 Viewing Stored Procedures

3, functions,

3.1 Creation and Invocation

3.2 Delete Function

3.3 Viewing Functions

11. Process control

1, the branch

1.1 the IF function

1.2 IF construct