MySQL Sub-query

First, the basic concept of the database:
1. The advantages of centralized control of database:
(1) Reduce the redundancy of storage data
(2) Higher data consistency
(3) The stored data can be shared
(4) standards that can be established for database compliance
(5) Easy to maintain data integrity
(6) The ability to achieve data security (database administrator--dba)

2. Data Model of database:
(1) Hierarchical model (upside down tree---Use example: IBM's information Management system)
(2) Mesh model
(3) The relational model "sees the world as a body (entity) and an association (relationship)"
(4) Object model

3, the basic concept of relational database
(1) Summary of English words:
Columns: column; row: row;
Property: Attribute; Primary key: Primary key; FOREIGN key: Foreign key
Database management System (DBMS): Datebase Management system;
Relational data Management System (RDBMS) relational datebase Management system;

(2) Primary key: In a table in a relational database, use a unique identifier to identify each row.

(3) Foreign key: It is used to express the relationship between table and tables.

(4) The basic relationship types of the tables in the relational database: one-to-one relationships, one-to-many relationships, and many-to-many relationships.

(5) Relational Data Management system (RDBMS): It is divided into local database management system (same client), Database server management system (on different machines).
In the development of commercial reference programs, the database server management system is usually used for security and performance considerations.

(6) Structured Query Language SQL (Structured query Language):
A, data definition language (DDL): Used to create, modify, delete data structures within a database
b, data Query Language (DQL): Used to query the specified data from one or more tables in the database
C, Data Manipulation language (DML): Used to modify data in a database, including: inserting, updating, deleting data
D, Data Control Language (DCL): Used to control access to the database

4. Basic rules:
(1) Database name: lowercase, cannot have Chinese
(2) ' Variable name ' is expanded to avoid keywords such as name.
(3) Table name, variable name is not case sensitive
(4) The main class cannot be deleted directly, because there is a reference to it from the table, unless there is no reference to it from the table, so there is no link to delete it.
(5) SQLyog inside a single comment-multi-line Comment/**/;sqlyog inside Select a section run can run the section alone

Second, the use of data definition language:
1. Management database:

(1) The database name must be unique within the server and conform to the rules of the identifier:

A, the first character must be an underscore or an @ or a number that is defined by the # or Unicode Standard 3.0
B. The following characters can be: The Unicode Standard 3.0 defines the letter or underscore or the @ or the number in accordance with the dollar or the decimal number from the basic Latin alphabet or the footsteps of other countries/regions.
C, identifiers cannot be reserved words for RDBMS
D, do not allow embedding of spaces or other special characters.

(2) Creating database: Create database name; Example: Create DB Datebase_demo;

(3) Connect to database: use database name; example: Use Datebase_demo;

(4) Delete database: drop database name; example: Drop DB Datebase_demo;

(5) The type of data in the database: (concrete can see the textbook p22-24 or picture)
A, Integer data type:
B, floating-point data type:
C, String type:
D. Binary data type:
E, date and time data type:
2. Management table:
(1) Creating Table: Create table table name (
The constraints of the Column Name column for the data type--when there are multiple columns, use "," separated, and the last one does not need to write ",". And you cannot exchange the location of the column name and the data type of the column.
）；

Example: CREATE TABLE Student (
ID bigint PRIMARY key,--ID with an integer better than bigint, good compatibility with Java, small error rate, and finally set the default encoding of UTF8.
Name VARCHAR (a) is not NULL,
Age Int (150)

) Charset=utf8;

(2) Delete tables: drop table name; example: Drop table Student; Note: A table joined by a FOREIGN key constraint cannot be deleted, and the constraint must be deleted before it can be deleted.

(3) Copy table: use SELECT. Example: CREATE TABLE student_copy SELECT * from Student;
NOTE: Replication here does not copy the constraints of the table. For copying only the structure of a table, the data of the table is not copied we can use where, after where to add a condition that never equals true:
Example: CREATE TABLE student_copy SELECT * from Student WHERE id =-1;

(4) Modify the table:
A. Change the data structure and add a column to the database: ALTER TABLE Student add address VARCHAR (12);--Adds a list of Address for table Student. To add more than one column at a time, use the "," Partition, for example: ALTER TABLE Student ADD Address varchar (n), Teacher varchar (12);
B. Change the definition of a column and add a default value constraint to the column: ALTER TABLE Student Change the type of the new column name for the old column names
Example: ALTER TABLE Student change name Studentname VARCHAR (All) DEFAULT ' Zhang San ';

C, delete columns: ALTER TABLE Student DROP column age;

D. Modify table name: ALTER table Student RENAME Student12;--RENAME is only for tables and cannot be used to modify the name of the database and the column names of the columns in the table.

(5) Management index:
A, create an index (the benefit of creating an index: query faster when the data is large, improve efficiency): Example: CREATE index nameindex on Student (name); --The index value defaults to a numeric type and does not need to be specified.

B, by index query: example: SELECT id,name from Student WHERE name= ' Zhang 32 ';

C, delete index: example: ALTER TABLE Student DROP index nameindex;

III. Overview of data integrity:

1, according to the method of data integrity implementation, divides it into 4 categories:
(1) Entity integrity: Ensures that a row of data is valid.
Implementation method: PRIMARY KEY constraint, UNIQUE constraint, identity column property.

(2) Domain integrity: Ensure that a column of data is valid.
Implementation method: FOREIGN KEY constraint, check constraint (not supported in MySQL), default value constraint, non-null constraint.

(3) Referential integrity: guarantees that the reference number is valid.
Implementation method: FOREIGN KEY constraint
(4) Custom integrity: ensure custom integrity.

2, the use of the method of data integrity implementation;

(1) Create a non-null constraint:
The NOT NULL constraint enforces that the column does not accept null values. A NOT NULL constraint forces a field to always contain a value. This means that if you do not add a value to the field, you cannot insert a new record or update the record.
CREATE TABLE T_person (
p_id INTEGER PRIMARY Key auto_increment,/* Set primary key to self-grow. */

P_name VARCHAR (a) NOT null,--a non-null constraint is used here.
P_age INTEGER not NULL,
P_address VARCHAR (+) DEFAULT ' Chengdu City '

) Charset=utf8, Engine=innodb;
Set the starting value of the self-growth ALTER TABLE t_person auto_increment = 100; If the ID value is set manually, the self-growth does not take effect, and the next entry takes effect at the largest 1

(2) Unique constraint:
A. Set UNIQUE constraints:
Unique constraints uniquely identify each record in a database table. Both unique and PRIMARYKEY constraints provide a unique guarantee for a column or column collection. The PRIMARYKEY has a UNIQUE constraint that is automatically defined. Note: Each table can have multiple UNIQUE constraints, but only one PRIMARY KEY constraint per table.

Example 1:create TABLE Persons (
id_p int not NULL UNIQUE
) Charset=utf8, Engine=innodb;

Example 2:create TABLE Persons (
id_p int not NULL,
UNIQUE (id_p)
) Charset=utf8, Engine=innodb;

Example 3:create TABLE Persons (
id_p int not NULL,
LastName varchar (255) is not NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
CONSTRAINT Uc_personid Unique (id_p,lastname)--one step to set the constraints for multiple attributes is a unique constraint. --the constraint is named Uc_personid.
)；

Example 4: Add a unique constraint to a property when the table has been created:
ALTER TABLE Persons ADD UNIQUE (id_p);
ALTER TABLE Persons ADD CONSTRAINT uc_personid Unique (id_p,lastname)--one step to set constraints on multiple properties as a unique constraint with a constraint named Uc_personid
B. Revoke a UNIQUE constraint:
ALTER TABLE Persons DROP INDEX Uc_personid;
ALTER TABLE Persons DROP CONSTRAINT Uc_personid; --This method of revoking the unique constraint is not supported in MySQL.
(3) PRIMARY KEY constraint
A. Setting A PRIMARY KEY constraint
Note: By default it contains non-null constraints and UNIQUE constraints, only one primary key in a table, but one primary key does not equal one column in the table as the primary key, and multiple columns can be used as a primary key, which is called the composite primary key.

Example 1:create TABLE Persons (
id_p int not NULL,
LastName varchar (255) is not NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
PRIMARY Key (id_p)--Set PRIMARY KEY constraint
)；

Example 2:create TABLE Persons (
id_p int not NULL PRIMARY key,--Set PRIMARY KEY constraint
LastName varchar (255) is not NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255)
)；

Example 3:create TABLE Persons (
id_p int not NULL,
LastName varchar (255) is not NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
CONSTRAINT Uc_personid PRIMARY KEY (id_p,lastname)
)；

Example 4: Adding a PRIMARY KEY constraint if the table has been created:
ALTER TABLE Persons MODIFY id_p PRIMARY KEY;
ALTER TABLE Persons ADD PRIMARY KEY (id_p)
ALTER TABLE Persons ADD CONSTRAINT pk_personid PRIMARY KEY (id_p,lastname)
Note: If you use the ALTER table statement to add a primary key, you must declare the primary key column to not contain a NULL value (when the table is first created).
B. Undoing a PRIMARY KEY constraint:
ALTER TABLE Persons DROP PRIMARY KEY;

(4) FOREIGN KEY constraint: FOREIGN key in one table points to PRIMARY key in another table. The FOREIGN KEY constraint is used to prevent actions that disrupt the connection between tables. The FOREIGN key constraint can also prevent illegal data from being inserted into the foreign key column, because it must be one of the values in the table it points to.
A. Set FOREIGN KEY constraints:
Example 1:create TABLE Orders (
o_id INT not NULL PRIMARY KEY,
OrderNo INT not NULL,
Id_p INT not NULL,
FOREIGN key (id_p) REFERENCES Persons (id_p)-Set FOREIGN KEY constraints
);

Example 2:create TABLE Orders (
o_id INT not NULL PRIMARY KEY,
OrderNo INT not NULL,
Id_p INT FOREIGN key REFERENCES Persons (id_p)-This method of setting a foreign key is not supported in MySQL.
);

Example 3:create TABLE Orders (
o_id int not NULL,
OrderNo int not NULL,
id_p int,
PRIMARY KEY (o_id),
CONSTRAINT fk_perorders FOREIGN KEY (id_p) REFERENCES Persons (id_p)
)

Example 4: Set a FOREIGN KEY constraint if the table already exists:

ALTER TABLE Orders ADD FOREIGN KEY (id_p) REFERENCES Persons (id_p);
ALTER TABLE Orders ADD CONSTRAINT fk_perorders FOREIGN KEY (id_p) REFERENCES Persons (id_p);

B, cancel the foreign KEY constraint:
ALTER TABLE Orders DROP FOREIGN KEY fk_perorders;
ALTER TABLE Orders DROP CONSTRAINT fk_perorders;--this method of revoking foreign keys is not supported in MySQL.

(5) Default value: The default constraint is used to insert defaults into the column. If no other value is specified, the default value is added to all new records.
A. Specify the default value:
Example 1:create TABLE Persons (
id_p int not NULL,
LastName varchar (255) is not NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255) Default ' Sandnes '--Set default value constraint
)
Example 2: Add a default value in case the table already exists:
Alter TABLE Persons ALTER CITY SET DEFAULT ' Sandnes ';

B, undo the default value:
Alter TABLE Persons ALTER CITY DROP DEFAULT;

Iv. using DML statements to change data:

1. Add New data
Syntax: INSERT into table name (column list) values (list of values); Note: When a list of column names is omitted, the attributes are assigned in order one by one
Example: INSERT into Student (studentid,name) VALUES (1, ' Zhang San '), (2, ' 31 '), (3, ' 32 '), (4, ' Zhang 33 ');

2. Inserting multiple rows of records: actually copying data from one table to another.
Syntax: INSERT into table name (List of column names) select SELECT statement;
Example: INSERT into Student (studentid,name) SELECT StudentID + 2,name from Student1;
Explanation: The Studentid,name in select in the example above refers to the attribute in Student1, and the reason for StudentID + 2 is because StudentID is the primary key in student and 2 data is already in the student table. Add 2 to avoid duplication of primary keys.

3. Replication of Table data:
Syntax: SELECT column name into new table name from table name;
Example: SELECT * into Student2 from Student1;

4. Change existing data:
Syntax: UPDATE table name SET new column Value list WHERE filter condition;
Example 1:update Student SET name= ' John Doe ' WHERE studentid=2;
Example 2:update Student SET name=null, which removes the entire column of data named name.

5. Delete data
(1) Syntax: Delete from table name WHERE filter condition; (Note: If you do not specify a WHERE clause, all rows in the table are deleted, but the data in the table still has some records that are associated with data in other tables.) For example: Primary key, which is associated from the table, the previously used data is still not available)
Example: DELETE from Student WHERE StudentID < 2;

(2) Syntax: TRUNCATE table name;
Note: TRUNCATE table can delete all rows in the table (the original no data state), but the structure, columns, constraints, indexes, etc. of the table will not be changed. And he can't use a table with a FOREIGN key constraint reference, and deleting the table's data requires a DELETE statement without a WHERE clause.
Executes the TRUNCATE table name; After the statement, the automatically growing identity value in the table is set to the default value.

V. Simple query of the database

CREATE TABLE Persons (
id_p int not NULL,
LastName varchar (255) is not NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255) Default ' Sandnes '--Set default value constraint
); The following query takes this table as an example

1, the database query operation basic classification:
(1) Projection operation
(2) Select operation
(3) Sorting operations

2. Projection operation:
(1) Query syntax:
Select column Names list from table name; (When multiple columns are selected, columns are separated by ","), and if all columns are selected, column name lists are replaced with "*"
Example 1:select id_p from Persons;
Example 2:select id_p,lastname from Persons;
Example 3:select * from Persons; --Common * query statement to do the test, do not use it to do the query.

(2) Use the table name prefix (function: To understand the information from that table, to distinguish between different tables of the same column name)
Syntax: SELECT table name 1. column Name 1, table name 1. column Name 2, table name 2. column Name 1 from table name 1, table name 2;
Example: SELECT persons.id_p from Persons;

(3) Alias the column name:
Syntax: SELECT column name as new column name from table name; (Query column displays column alias)
Example: Select Id_p as ' number ', LastName as name from Persons; (When multiple columns are selected, columns are separated by ",")

(4) Use the DISTINCT (DISTINCT) keyword to exclude duplicate data:
Syntax: SELECT DISTINCT column name from table name;
Example: SELECT DISTINCT FirstName from persons;--query Persons table for what kind of FirstName.
When listed as a multi-column, that is, select DISTINCT column 1, column 2 from the table name;--column 1, column 2 at the same time the same data information filtered query results are displayed.

(5) Computed column:
A, connection string, using the concat (concat) keyword:
Syntax: SELECT CONCAT (column name 1, column name 2, column name 3) as ' title ' from table name; (Other strings can be added between column names)
Example 1:select CONCAT (firstname,lastname) as Name from Persons;
Example 2:select CONCAT (FirstName, '-', LastName) as Name from Persons;
B, do a simple arithmetic:
(6) A query that returns the number of qualified rows:
Syntax: SELECT column name from table name LIMIT starts ordinal, returns the number of rows; (note: The start ordinal is starting from 0, i.e.: If starting from the first line, the starting ordinal is 0)
Example 1:select DISTINCT FirstName from Persons LIMIT 1,4;--starts the query from the second row, returning a total of 4 rows of data.
Example 2:select DISTINCT FirstName from Persons LIMIT 4;--start query from the first line, return 4 rows of data

3. Select the operation (using the WHERE keyword):
SELECT column list from table name WHERE condition;
(1) Single condition selection operation:
A, Syntax: SELECT column 1, column 2 from table name WHERE column 3= value;
Example: SELECT firstname,lastname from Persons WHERE id_p=1;
B, comparison operator (=,!=,^=,<>,>,>=,<,<=):
For ^=,<> is similar to! =, it is only different when the value is null.

(2) Multi-condition selection operation:
Use the keyword and (two conditions are met), OR (at least one of the two conditions).
Syntax 1:select column name from table name WHERE condition and condition;
Syntax 2:select column name from table name WHERE condition or condition;
Note: The NOT keyword can also be used in query conditions, for example: SELECT column name from table name WHERE condition 1 and not condition 2;--condition 1 satisfied, Condition 2 not satisfied.

(3) Execution scope test: (using between keyword)
Syntax: SELECT column list from table name WHERE condition between upper and lower bounds, including upper and lower limit values. Of course, you can also use the NOT keyword to exclude a range. SELECT column names list from table name WHERE condition not between upper and lower bounds;)
Example: SELECT * from Persons WHERE id_p between 3 and 8;

(4) Define the set relationship:

SELECT * FROM table name WHERE column name in (Value collection); (Find the specified range)
SELECT * FROM table name WHERE column name is not in (value collection); (Find out not specified range)
Example: SELECT * from Persons WHERE id_p in (3,7,8);

(5) Fuzzy query: Use the wildcard character '% ' (any string containing 0 or more characters), ' _ ' (any single character).
A, Syntax: SELECT column name from table name WHERE column name like mode;
Example 1:select firstname,lastname from Persons WHERE FirstName like '% King ';--FirstName contains ' king '
Example 2:select firstname,lastname from Persons WHERE FirstName like ' _ ';--FirstName only one word.

B, escape character
Example: Match contains 50%: '%50p%% ' ESCAPE ' P ' (here p is best not to appear in the query string) or '%50<escape char>%% ' or '%50[%% ' (note: the latter two are not used in MySQL)

(6) Processing null value data:
SELECT column name from table name WHERE column Name 1 is not NULL; (Query test column name 1 not empty)
SELECT column name from table name WHERE column name 1 is null; (Query test column name 1 is empty)

4. Sort operation:

Syntax: SELECT column names list 1 from table name ORDER by column 1, column 2 ASC (ascending);
List of SELECT columns list 1 from table name ORDER by column 1, column 2 DESC (descending); (Note: Sort by column 1 first (descending, ascending), when the data for column 1 appears in the same order (descending, ascending) in sequence of column 2)

Vi. aggregation functions and groupings:
1. Use aggregate functions for statistical summarization:
(1) Common aggregation functions:
COUNT: Returns the number of rows in the result set;
Sum: Returns the sum of all values in the result set;
AVG: Returns the average of all values in the result set;
Max: Returns the maximum value for all values in the result set;
Min: Returns the minimum value of all values in the result set;
Note: The aggregate function (except COUNT (*) handles all the selected values in a single column to produce a single result value.
Keywords for the Count specification:
*--Calculates all selected rows, including null values;
All column-Calculates all non-null value rows for the specified column (this is also the case by default)
Distinct column-Calculates all non-null value rows for the specified column, and does not repeat the number of rows of data.

(2) Use of Count:
Syntax: SELECT count (Count specification) as ' title ' from table name WHERE condition; (count the number of rows that meet the criteria)

(3) Use of sum: (Sum used by the Count specification keyword only all and distinct)
Syntax: syntax: SELECT SUM (Count specification) as ' title ' from table name WHERE condition; (Gets the aggregate value of a single column)

(4) AVG Usage: (AVG uses the Count specification keyword only for all and distinct)
Syntax: syntax: SELECT AVG (Count specification) as ' title ' from table name WHERE condition; (average of a column is computed)

(5) Max's use: (No keyword all,distinct,*)
Syntax: syntax: SELECT max (maximum specification) as ' title ' from table name WHERE condition; (maximum value of computed column)

(6) Min's use: (No keyword all,distinct,*)
Syntax: syntax: SELECT min (minimum specification) as ' title ' from table name WHERE condition; (minimum value of computed column)

2. Data grouping:
(1) Filter grouped data: Use the GROUP BY clause to group data (to get a total of a subset of the data set in a database)
SELECT column A, aggregate function (aggregate function specification) from table name WHERE condition GROUP by column A;
Valid groupings: ① uses an aggregate function; ② the column is in the GROUP BY clause.
Example: Select name from the BBC Grop by ID; --Invalid grouping.

(2) Use the HAVING clause: (Use the HAVING clause when you need to filter the query result set using an aggregate function)
SELECT column A, aggregate function (aggregate function specification) from table name WHERE condition GROUP by column a HAVING clause;

3, the execution order of SQL statements:
1.FROM clause
2.WHERE clause
3.GROUP BY clause
4.SELECT clause
5.HAVING clause
6.ORDER BY clause
7.LIMIT clause

Seven, the combination of query:

1, using sub-query:
(1) introduced: SELECT Subject,count (winner) from Nobel GROUP by SUBJECT; The subquery is not required when the grouped column name is the same as the column name that needs to query the column (not using an aggregate function), or a subquery if it is different.
Example: Query the highest score of each section in the score table and the information of the students who have obtained the highest score:
SELECT account, student name, Max (score) from the Student GROUP by account;--The student's information is not right at this time. You should use subqueries at this point.
(2) In a complete SQL statement, the nested subquery can be located in the location: From, WHERE, GROUP by, SELECT, have, ORDER by.
① SQL statements nested in a SELECT statement require that the values of the query be only single-row and single-column:
Syntax: SELECT column A, (subquery) as column B ftom table name;

② nested in the FROM statement, multiple rows and multiple columns;

③ nested in the WHERE statement:
A, Syntax 1:select column a ftom table name WHERE column B = (subquery);
B. Subqueries that return multiple results: SELECT column A ftom table name WHERE column B (not) in (subquery 1 where column C in subquery 2);
C, using operators in subqueries:
A. Use the EXISTS operator (the operator is followed by a subquery, and the value of exists is true as long as the subquery returns a row. (To use the table name prefix))
Syntax: Select column A from table name s where EXISTS (SELECT column A from table name e WHERE condition E. Column A = S. Column a);

B. Use the all operator with the subquery and comparison operations. True if all values returned by the subquery satisfy the comparison operator)
Syntax: Select column A from table name 1 WHERE column A > (comparison operation) all (SELECT column B from table name 2);

C. Using the Any operator (true if one of the values returned by the fruit query satisfies the comparison operator)
Syntax: Select column A from table name 1 WHERE column A > (comparison operation) any (select column B from table name 2);

2. Combination query data: Select Column A column B from table name 1 operator Select column C column D from table name 2;

(1) Operator of combination query: Union, UNION All, INTERSECT, difference;

(2) UNION operator: When two datasets are federated, each member of each dataset is included, but each member is counted only once.
Syntax: SELECT COLUMNA,COLUMNB from TableA
UNION
SELECT columnc,columnd from TableB;
Interpreted as a mathematical set {1,2,3,4}union{3,4,5,6} with a result set of {1,2,3,4,5,6}
Note: The column name of the result set of the Union is the same as the column name in the result set of the first SELECT statement in the Union operator, and the second one is ignored.

(3) UNION ALL operator: two datasets are combined to include each member of each dataset, and duplicate rows are retained.
Syntax: SELECT COLUMNA,COLUMNB from TableA
UNION All
SELECT columnc,columnd from TableB;
Interpreted as a mathematical set {1,2,3,4}union all{3,4,5,6} with a result set of {1,2,3,3,4,4,5,6}
Note: This can be used when we want to know the number of occurrences of a particular value in multiple tables.

(4) Intersect operator: leaves only two duplicate rows (intersection) of data sets. Supported only by Oracle
Interpreted as a mathematical set {1,2,3,4}intersect{3,4,5,6} with a result set of {3,4}

(5) Except operator: Only rows that have not been repeated (differential) are left. Supported only by Oracle
Interpreted as a mathematical set {1,2,3,4}except{3,4,5,6} with a result set of {1,2,5,6}

MySQL Sub-query