mysql< Five >

--########## 01, use of limit and paging ######### #INSERT into Studentinfo VALUES (null, ' Liu Bei ', ' male ', ' + '), (null, ' Guan Yu ', ' Male ', '), (NULL, ' Zhang Fei ', ' Male ', ', ' (null, ' Marten cicada ', ' female ', '), (null, ' bu ', ' male ', '), (null, ' Shing ', ' female ', '), (null, ' Dong Zhuo ', ' Male ', 55); Use of the SELECT * from studentinfo;--limit:--usage 1, limit start position, number of records--the starting position of limit 0 means the first record select * from Studentinfo LIMIT 0, 3;sel ECT * from Studentinfo LIMIT 1, 3;--error code: 1064 You have a error in your SQL syntax; Check the manual, corresponds to your MySQL server version for the right syntax to use near '-1, 3 ' on line 1SELECT * From Studentinfo LIMIT-1, 3;--usage 2, limit record number--starts from the first record to fetch several records SELECT * from Studentinfo LIMIT 0; SELECT * from Studentinfo LIMIT 1; SELECT * from Studentinfo LIMIT 2;  SELECT * from Studentinfo limit 3;--limit is generally used for paging operations-a total of 7 data, set 3 records per page, divided into 3 pages-a list of records per page to summarize the method of pagination:--get total number of records select COUNT (*) as  Total number of records from studentinfo;--1th: 1th, 2nd, 3rd select * from Studentinfo LIMIT 0, 3;--2nd page: 4th, 5th, 6th select * from Studentinfo LIMIT 3, 3;--3rd page: 7th SELECT * FROM Studentinfo LIMIT 6, 1; --This statement performs OK, but is not conducive to regular induction select * from Studentinfo LIMIT 6, 3;--analysis from the above statement to derive the general method of paging-consider the production method or the method of return value type and parameter list start-1, Return value class Type is the data set of the current page--2, parameter list: For the limit clause, there are two parameters: initial position and number of records--A: Number of records: Set number of records per page--B: Initial position: The value of the change, but as long as you know the current page, you can calculate What is the initial position-calculation formula: (Current page-1) * Number of records--3, implies a data need to obtain: total number of pages--a: Total record count% single page record number = 0 o'clock, total pages = (total number of records/number of records per page)- -B: Total number of records% single-page record number of bars = 0 o'clock, total pages = (total number of records/number of records per page) + 4, get total number of records: SELECT Count (*) as total record number from table name;--########## 02 , insert data and query data combined Operation ##########--Create a student temporary information table created table Studenttemp (ID INT auto_increment PRIMARY KEY, ' name ' VARCHAR (10 ) not NULL, gender ENUM (' Male ', ' female ') not null); SELECT * from Studentinfo; SELECT * FROM studenttemp;--requirements: The Student information table in some of the contents of the query to insert into the student Temporary information table-ideas: Combined use of query and insert--insert Combined query writing: INSERT into a new table (need to insert the column 1, you need to insert Column 2, ...) Querying data contents in other tables insert into Studenttemp (' name ', gender) SELECT Studentname, studentgender from Studentinfo; SELECT * FROM studenttemp;--Note: The fields of the new and old tables can be inconsistent in field names, butIs the data type of the corresponding field must be consistent (the data range of small content into the data range of large fields is OK)--########## 03, subquery ##########--subquery: In one query contains another query statement-1, "Independent subquery": Execute the subquery first, Then execute the outer query--using the Courseinfo table and the Teacherinfo table created earlier insert into Courseinfo VALUES (null, ' language ', 1), (null, ' math ', 2), (null, ' English ', 1); INSERT into Teacherinfo VALUES (null, ' Miss Zhang ', ' Male ', '), (null, ' Miss Wang ', ' Female '), (null, ' Miss Li ', ' Male ', 40); SELECT * from Courseinfo; SELECT * FROM teacherinfo;--: List the information of the substitute teacher-the next sentence means all the teacher's information, we need the information of the substitute teacher, so we should go to the course Information table to see which teachers are substitute, find out the select * from teacherinfo;--error code: 1242 subquery returns more than 1 row (because a subquery gets a collection) SELECT * from Teacherinfowhere Teacherid = (sele CT Teacherid from Courseinfo);--correct notation: use in to determine whether the contents of the field and the values in the collection match select *from teacherinfowhere teacherid in (select Teacherid F ROM courseinfo);--The following is also correct, but it is not necessary to do so, especially for a large number of data, the use of distinct this to repeat the operation is very time-consuming, unless forced to use to repeat the select *from teacherinfowhere Teacherid in (SELECT DISTINCT teacherid from Courseinfo);--can be understood as follows:--STEP1, Get Set data (substitute teacher number): SELECT Teacherid from C Ourseinfo this subquery get {1, 2, 1} Collection--Step2, substitute teacher's number is the previousOne of the data in the collection that gets in the step operation, takes this data to the Teacher Information table to filter select * FROM Teacherinfo WHERE Teacherid in (1, 2, 1);--demand: Find student numbers and student names that pass through all course scores for a single student --Obviously there's a semantic error: The following subquery does something:--as long as the course's score is greater than 60, the student number is screened and left, not the student's other course scores. Select StudentID as student number, studentname as student name from Studentinfowhere StudentID in (SELECT StudentID from Scoreinfo WHERE score >= 60); SELECT * from scoreinfo;--correct wording:------1, reverse thinking: Considering the positive thinking of the difficulty is relatively large, so-called all courses have passed the students--is deleted as long as the course failed even if the students left after the classmate- Also must be has the student who has the achievement is we to look for that part schoolmate (needs to carry on the business logic confirmation) SELECT StudentID as student number, studentname as student name from Studentinfowhere StudentID  (SELECT StudentID from Scoreinfo WHERE score< -)--A student number with a course score and StudentID in (SELECT StudentID from Scoreinfo);--2, Positive thinking: Consider grouping according to the student number in the Score information table,    Gets the minimum score for the group is greater than or equal to 60 students SELECT StudentID as student number, studentname as student name from Studentinfowhere StudentID in (select StudentID From Scoreinfo GROUP by StudentID have MIN (score)>= 60); SELECT * FROM scoreinfo;--subqueries using comparison operators-requirements: List student information for student numbers not less than elective course number 2-error code: 1242 subquery returns more than 1 Rowselect *f ROM studentinfowhere studentid >= (SELECT studentid from scoreinfo WHERE CourseID = 2);--correct notation: Although there is no keyword or symbol greater than or equal to a range, you can With greater than or equal to the largest data value in this range, select *from studentinfowhere studentid >= (select MAX (StudentID) from scoreinfo WHERE CourseID = 2); --subquery using the EXISTS keyword: if the subquery queries the result, the outer query executes; otherwise, the outer query does not execute-demand: Students who have a course score of 90 or above will list all student information Select *from Studentinfowhere EXISTS (SELECT * from Scoreinfo WHERE score >= 90);--demand: Students who have a course score greater than 90 points list all student information SELECT *from studentinfowhere EXISTS (S Elect * from Scoreinfo WHERE score > 90);--Use any or SOME subquery--Requirements: List student information with number greater than either of the student numbers elective for course number 2 Select *from studentinfow Here StudentID >= any (SELECT studentid from scoreinfo WHERE CourseID = 2);--the above sentence is equivalent to the following sentence: List number is greater than elective class number 2 student number minimum that number of student information sel ECT *from studentinfowhere studentid >= (SELECT MIN (StudentID) from scoreinfo WHERE CourseID = 2);--keyword some and keyword any is Synonyms Select *from studentinfowhere StudentID >= SOME (SELECT studentid from scoreinfo WHERE CourseID = 2);--Use all subquery--Requirements: List student numbers not less than all students who have enrolled in Course number 2 Student Information Select *from studentinfowhere studentid >= All (SELECT studentid from scoreinfo WHERE CourseID = 2);--the sentence can be understood as: greater than or equal to check Consult the student number of course number 2 the largest data value in this range select *from studentinfowhere studentid >= (select MAX (StudentID) from Scoreinfo WHERE CourseID = 2);--Combine common scenarios: customer information and order information to see the use of sub-queries-Customer Information table CREATE TABLE Customers (CustomerID INT auto_increment PRIMARY KE Y, CustomerName VARCHAR (x) not NULL), INSERT into customers VALUES (null, ' Zhang San '), (null, ' John Doe '), (null, ' Harry '), (null, ' Zhao Liu ') );--Order Information table CREATE TABLE orders (OrderID int auto_increment PRIMARY KEY, CustomerID int not NULL,--Amount amount D Ecimal (2) NOT NULL,--order type: Cash, credit card OrderType VARCHAR (TEN) not null); INSERT into orders VALUES (NULL, 1, 22.25, ' cash '), (null, 2, 11.75, ' credit card '), (NULL, 2, 5.00, ' credit card '), (NULL, 2, 8.00, ' cash '), (null, 3, 9.33, ' credit card '), (null, 3, 10.11, ' credit card ') ; SELECT * from Customers; SELECT * FROM orders;--1, independent sub-query as a data source (a collection of data) use-demand: customer-based, statistics per customer's total cash consumption--1) filter After grouping-error code: 1054 Unknown column ' OrderType ' I n ' HAVING clause '--analysis: Because there will be different Ordertypeselect customerid as customer numbers in each group formed according to CustomerID, SUM (amount) as cash consumption from Ordersgroup by customeridhaving OrderType = ' cash ';--2) filter before grouping, consider using subqueries--error code: 1248 Every derived table must has its own a Liasselect *from (SELECT customerid as customer number, sum (amount) as cash consumption from orders WHERE OrderType = ' cash ' GROUP B  Y customerid);--Correct notation: Select *from (SELECT CustomerID as customer number, SUM (amount) as cash consumption from orders WHERE OrderType = ' Cash ' GROUP by CustomerID) as temp;--do not use asterisk wildcard character, use specific field-error code: 1054 Unknown Column ' CustomerID ' in ' Field List ' SELECT CustomerID as customer number, sum (amount) as total cash consumption from (SELECT CustomerID as customer number, sum (amount) as cash consumption from orders wher E ordertype = ' cash ' GROUP by customerid) as temp;--correct:--1SELECT customer number, total cash consumption from (SELECT CustomerID as customer number, S UM (amount) as total cash consumption from orDERs WHERE ordertype = ' cash ' GROUP by customerid) as temp;--2SELECT temp. Customer number, temp. Total cash consumption from (SELECT custome RID as customer number, sum (amount) as cash consumption from orders WHERE OrderType = ' cash ' GROUP by customerid) as temp;--2, correlated sub-query--1 Use correlated subqueries in query conditions-demand: Query for customer information with a total consumption of less than 20 yuan select * FROM orders;--1, use independent subquery select *from customerswhere customerid in (Sele  CT CustomerID from Orders GROUP by CustomerID have SUM (amount)< -);---notation 2, using associated subqueries--"when using an associated subquery, the subquery is no longer executed independently as a standalone subquery, and no longer executes the" select *from customers as Cwhere "as a standalone subquery (select Customeri D from orders as O WHERE o.customerid= C.customeridGROUP by O.customerid has SUM (O.amount) < 20);--it can be understood as: a step in a query condition in which the associated subquery is equivalent to traversing each row of the associated field in the outer table--here, the correlation in the query condition The correlation field used by the CustomerID is equivalent to the CustomerID of each row of the associated field in the Customers table that traverses the outer layer--because the associated subquery involves using the associated field in the outer table, the associated subquery cannot be executed independently-- Line 1th Records select Customeridfrom orders as Owhere O.customerid= 1GROUP by O. ' CustomerID ' Having SUM (o. ' Amount ') < 20;--2nd row record select Customeridfrom orders as Owhere O.customerid = 2GROUP by O. ' CustomerID ' Having SUM (o. ' Amount ') < 20;--3rd record (query to a record satisfying the condition, so stay) SELECT Customeridfrom orders as Owhere O.customerid= 3GROUP by O. ' CustomerID ' Having SUM (o. ' Amount ') < 20;--4th row record Select Customeridfrom orders as Owhere O.customerid = 4GROUP by O. ' CustomerID ' Having SUM (o. ' Amount ') < 20;--2) correlated subqueries as a computed column--Requirements: List the customer's name and the customer's personal consumption--analysis: A single acquisition of a customer's name or A single acquisition of the customer's personal consumption is good, but to be in a query results in a bit of a problem, with the help of correlation sub-query select * FROM Orders; SELECT * from Customers; SELECT CustomerID as customer number, COUNT (*) as customer's personal consumption quantity from Ordersgroup by CustomerID; Select CustomerName as Customer Name, (select COUNT (O.orderid) as consumption quantity from orders as O WHERE o.custome Rid= C.customeridas the amount of personal consumption from customers as c;--can be understood as: In a step, the associated subquery in the column is equivalent to traversing each row of the associated field in the outer table--here, The associated subquery used in the query condition is equivalent to the CustomerID of each row of the associated field in the CustomerID of the Customers table that traverses the outer layer-because the associated subquery involves using the associated fields in the outer table. So the associated subquery cannot be executed independently--line 1th Records select COUNT (O.orderid) as consumption from orders as Owhere O.customerid= 1--Line 2nd Records select COUNT (O.orderid) as consumption from orders as Owhere O.customerid= 2--Line 3rd Records select COUNT (O.orderid) as consumption from orders as Owhere O.customerid= 3--Line 4th Records Select COUNT (O.orderid) as consumption from orders as Owhere O.customerid= 4--Subquery Summary:--1, independent sub-query: Can be executed independently, and first executed-2, correlated subquery: Can not be executed independently, and with the operation of the outer query, every time is called-########## 04, table connection ##########--Common scenarios: departments and employees are A one-to-many relationship, the department is a, the staff is more-in order to reflect this one-to-many relationship, put the department number in the Employee Information table This field--Department Table CREATE TABLE Dept (deptid INT auto_increment PRIMARY KEY, DEP Tname VARCHAR (a) UNIQUE not NULL);--Employee Table CREATE TABLE employee (EmployeeID INT auto_increment PRIMARY KEY, Employeen Ame VARCHAR (n) not null, DeptID INT NULL);--Analog data INSERT INTO dept VALUES (NULL, ' development Department '), (null, ' Sales department '), (null, ' General manager '), ( NULL, ' personnel department '); INSERT into employee VALUES (null, ' Liu Bei ', 1), (null, ' Zhang Fei ', 1), (null, ' Guan Yu ', 1), (null, ' Caocao ', 2), (null, ' Xunyu ', 2) , (null, ' Guo Jia ', 2), (null, ' Sun Quan ', 3), (null, ' Zhou Yu ', 3), (null, ' Lu Xun ', 3), (null, ' Meng ', null), (null, ' in Ringgit ', 5); SELECT * FROM Dept; SELECT * from employee;--1, cross join: Get Cartesian product (Cartesian product)-Cross connect: two entities (with M-Records and N-Records) are connected to form a new set of M*n records, without any restriction conditions- 1SELECT * FROM dept, employee;--Notation 2, explicit use of the Cross join keyword (recommended) SELECT * from the Dept Cross join employee;--2, INNER join (INNER JOIN): Conditional (filtering) on cross-connect-requirements: Find out employee information andThe name of its corresponding department-analysis: The above obtained Cartesian product without any conditions, a lot of data is useless-we need to according to the Employee Information table of the department unique identification-----Department number, to the Department information table to obtain the appropriate information from the Department-that is, employee letter  The department number in the rest table should correspond to the department number in the Department information table-consider using the INNER join keyword and the WHERE clause select *from Dept INNER join Employeewhere Dept. ' DeptID '= employee. ' DeptID ';--Improved Select employee. ' EmployeeID ' as employee number, employee. ' EmployeeName ' as Employee name, Dept. ' Deptname ' as Department name from Dept INNER JOI N employeewhere Dept. ' DeptID '= employee. ' DeptID ';--can be understood as: first obtained the cross-connected Cartesian product, and then the use of the Cartesian product conditions limit, the results of the internal connection is obtained select employee. ' EmployeeID ' as employee number, employee. ' EmployeeName ' as Employee name, Dept. ' Deptname ' as Department name from Dept Cross JOIN Employeewhere Dept. ' DeptID '= employee. ' DeptID ';--The above notation is equivalent to select employee. ' EmployeeID ' as employee number, employee. ' EmployeeName ' as Employee name, Dept. ' Deptname ' as Department name from Dept, EMP Loyeewhere Dept. ' DeptID '= employee. ' DeptID ';--For a description of the connection correspondence, the on keyword is provided in SQL Select Employee. ' EmployeeID ' as employee number, employee. ' EmployeeName ' as Staff name, Dept. ' Deptname ' As Department name from Dept INNER JOIN Employeeon Dept. ' DeptID '= employee. ' DeptID ';--3, outer join (OUTER JOIN)--Understanding: Here, is for the focus of that set of--1) LEFT outer connection/LEFT join (OUTER join/left JOIN):--the table on the left is the main, the table on the right can match the data listed to , the match does not appear as null value--2) Right outer join/Right connection (OUTER Join/right join):--The table on the right is the main, the table on the left can match the data on the list, matching not on the display as null value-- Requirements: List all departments and departments of the company's employee information (multi-line display)-Analysis: Focus on the whole department, but also to list all departments in each department corresponding to the employee information-the implied meaning, even if the department does not have employees, but also to list the department, when no employees, staff information is displayed as Nu ll value--however, the employee does not have a department and does not consider the Select *from deptleft OUTER JOIN employeeon Dept. ' DeptID '= employee. ' DeptID ';--notation 1, use left OUTER joinselect Dept. ' Deptname ' as the department name, employee. ' EmployeeID ' as employee number, employee. ' EmployeeName ' as employee name From Deptleft OUTER JOIN employeeon Dept. ' DeptID '= employee. ' DeptID ';--notation 2, use left Joinselect Dept. ' Deptname ' as the department name, employee. ' EmployeeID ' as employee number, employee. ' EmployeeName ' as employee name from D Eptleft JOIN Employeeon Dept. ' DeptID '= employee. ' DeptID ';--Requirements: To list all employees and employees of the company's Department information-analysis: Focus on all employees, but also to list the entire staff of each employee corresponding to the Department of Information-the implied meaning, even if the employee has no department, but also to list the employee, no department, the department information is displayed as N ull value--1, use the left connection to select employee. ' EmployeeID ' as employee number, employee. ' EmployeeName ' as Staff name, Dept. ' Deptname ' as department name from Empl Oyeeleft JOIN Depton employee. ' DeptID '= Dept. ' DeptID ';--notation 2, use right connection to select employee. ' EmployeeID ' as employee number, employee. ' EmployeeName ' as Employee name, Dept. ' Deptname ' as Department name from Dept Right OUTER JOIN Employeeon employee. ' DeptID '= Dept. ' DeptID ';-simplifies select employee. ' EmployeeID ' as employee number, employee. ' EmployeeName ' as Employee name, Dept. ' Deptname ' as department name from Deptright JOIN Employeeon employee. ' DeptID '= Dept. ' DeptID ';---4, self-connected (internal connection variant)--Requirements: Design company's organizational structure information table (This design is important: Be sure to master)--Chua International--Chua's China--Chua's Anhui--CAI                    BEIJING-Chua Shanghai-Chua America-Chua New York-Chua's Texas-Chua Japan- Chua's Kanto--Chua's Kansai--consider a tree structure to describe the location of a node on a tree structure, describe its current node and its parent node, so that it can determine its location-the company organizational structure Information table create T    ABLE CompanyInfo (--Organization number CompanyID INT auto_increment PRIMARY KEY,--Organization name CompanyName VARCHAR () not NULL, --Organization parent number Companypid INT not NULL);--Analog data insert INTO CompanyInfo VALUES (null, ' Chua International ', 0), (null, ' Chua China ', 1), (NULL, '  Chua America ', 1), (NULL, ' Chua Japan ', 1), (null, ' Chua Anhui ', 2), (null, ' Chua Beijing ', 2), (null, ' Chua's Shanghai ', 2), (null, ' Chua New York ', 3), (null, ' Chua's Texas ', 3), (null, ' Chua Kanto ', 4), (null, ' Chua Kansai ', 4); SELECT * FROM companyinfo;--requirements: View its organization number and its next level according to the given organization name-idea: first get its organization number by the given organization name, and then take this organization number to do companypid parent number in the table query, get its next level--  The steps are as follows:--STEP1, first gets its organization number by the given organization name Select CompanyID as organization number, CompanyName as organization name from Companyinfowhere CompanyName= ' Chua China ';--STEP2, take this organization number do companypid parent number in the table query, get its next level select CompanyID as the next level organization number, CompanyName as the next level organization name from Companyinfowhere Companypid= 2;--Does not use a step-by, self-join, the number of the Organization Information table collection 1 and the Organizational Information table Collection 2 of the parent number to match-that is, the number of the left table as the parent number of the right table to match select *from CompanyInfo as C1inner JOIN com Panyinfo as C2on C1. ' CompanyID '= c2. ' Companypid ';--Considering the need to view its organization number and its next level based on the given organization name--error code: 1052 column ' CompanyName ' in WHERE clause is ambiguous CompanyName column has a discrepancy in the WHERE clause (because there are multiple CompanyName fields) SELECT *from CompanyInfo as C1inner JOIN CompanyInfo as C2on C1. ' CompanyID '= c2. ' Companypid 'WHERE CompanyName= ' Chua China ';--correct notation 1, use on and Whereselect C1 in the filter conditions. ' CompanyID ' as organization number, C1. ' CompanyName ' as organization name, C2. ' CompanyName ' as the next level of organization name from CompanyInfo as C1inner JOIN CompanyInfo as C2on C1. ' CompanyID '= c2. ' Companypid 'WHERE C1.companyname= ' Chua China ';--correct notation 2, use ONSELECT C1 in the filter condition. ' CompanyID ' as organization number, C1. ' CompanyName ' as organization name, C2. ' CompanyName ' as the next level of organization name from Companyinf o as C1inner JOIN CompanyInfo as C2on C1. ' CompanyID '= c2. ' Companypid 'and C1.companyname= ' Chua China ';--correct notation 2, use Whereselect C1 in the filter condition. ' CompanyID ' as organization number, C1. ' CompanyName ' as organization name, C2. ' CompanyName ' as the next level of organization name from company Info as C1inner JOIN CompanyInfo as C2where C1. ' CompanyID '= c2. ' Companypid 'and C1.companyname= ' Chua China ';--Requirements: View primary organization (first-level subsidiaries) and their next level organization (multiline display) SELECT C1. ' CompanyID ' as organization number, C1. ' CompanyName ' as-level organization name, C2. ' CompanyName ' as The next level of organization name from CompanyInfo as C1inner JOIN CompanyInfo as C2where C1. ' CompanyID '= c2. ' Companypid 'and C1. ' Companypid '= 1;--Requirements: View primary organization (first-tier subsidiaries) and their next-level organization (single-line display) SELECT temp. Organization number, temp. First-level organization name, Group_concat (temp. Next-level organization name) as lower-level organization name from (SELECT C 1. ' CompanyID ' as organization number, C1. ' CompanyName ' as-level organization name, C2. ' CompanyName ' as the next-level organization name from CompanyInfo as C1 INNER JOIN com Panyinfo as C2 WHERE C1. ' CompanyID '= c2. ' Companypid 'and C1. ' Companypid '= 1as Tempgroup by temp. Organization number, temp. First-level organization name;

mysql< Five >