1. Prepare table information
2.MySQL advanced statement
2.1 alias
Field alias, table alias
Syntax: SELECT "table alias" "Field 1" [AS] "field alias" FROM "table name" [AS] "table alias";
Example:
select A.age 'Age' from REGION A order by Age asc;
2.2 sub query: join table
Insert another SQL statement into the child WHERE clause or HAVING clause
Syntax:
#External query SELECT "Field 1" FROM "Table 1" WHERE "Field 2" [Comparison operator] #Internal query (SELECT "Field 1" FROM "Table 2" WHERE "condition"); #Operators that can be symbols, for example: =, >, <, > =, < =; It can also be a literal operator, such as LIKE, IN, BETWEEN
Example:
select A.name,age from REGION A where A.name in (select B.name from FARE B where city ='Nanjing');
2.3 EXISTS
Used to test whether the internal query produces any results
Whether similar Boolean values are true
If so, the system will execute the SQL statements in the external query. If not, the entire SQL statement will not produce any results.
Syntax: SELECT "field 1" FROM "Table 1" WHERE EXISTS (SELECT * FROM "Table 2" WHERE "conditions");
Example:
select sum(age) from REGION where exists (select * from FARE where city ='Nanjing'); select sum(age) from REGION where exists (select * from FARE where city ='Nan');
3. Connection query
3.1 inner join
Returns only rows with equal join fields in two tables
SELECT * FROM table 1 alias of Table 1 internal join table 2 alias of Table 2 on table 1 alias Field = Table 2 alias Field# The field names here are the same
Example:
select * from REGION A inner join FARE B on A.name = B.name;
3.2 left join
Returns records that include all records in the left table and join fields in the right table
SELECT * FROM table 1 alias of Table 1 LEFT JOIN table 2 alias of Table 2 on table 1 alias Field = Table 2 alias Field# The field names here are the same
Example:
select * from REGION A left join FARE B on A.name = B.name;
3.3 right join
Returns records that include all records in the right table and the same join field in the left table
SELECT * FROM table 1 aliases of Table 1 RIGHT JOIN table 2 aliases of Table 2 on table 1 aliases Field = Table 2 alias Field# The field names here are the same
Example:
select * from REGION A right join FARE B on A.name = B.name;
4.CREATE VIEW
the difference between a view and a table is that a table actually stores data, while a view is a structure based on a table and does not actually store data itself.
the temporary table will disappear automatically after the user exits or disconnects from the database, and the view will not disappear.
view does not contain data, but only stores its definition. Its purpose is generally to simplify complex queries. For example, you need to join and query several tables, and also perform operations such as statistical sorting. It will be very troublesome to write SQL statements. Join several tables with a view, and then query this view, which is as convenient as querying a table.
Syntax: CREATE VIEW "view table name" AS "SELECT statement";
Example:
create view new_info as select A.name 'full name', A.age 'Age', B.city 'city' from REGION A inner join FARE B on A.name= B.name; select * from new_info; drop view new_info;
5. Join set, intersection value, no intersection value and case of MySQL
5.1 joint collection
Combine the results of the two SQL statements. The fields generated by the two SQL statements need to be of the same data type
(1)UNION
UNION: the data values of the generated results will not be repeated, and will be sorted in the order of fields
Syntax: [SELECT statement1] Union [SELECT statement2];
Example:
select name from REGION union select name from FARE;
(2)UNION ALL
UNION ALL: lists the data values of the generated results, whether there are duplicates or not
Syntax: [SELECT statement 1] UNION ALL [SELECT statement 2];
Example:
select name from REGION union all select name from FARE;
5.2 intersection value
Take the intersection of the results of two SQL statements
select A.name from REGION A inner join FARE B on A.name = B.name;
There are no duplicate rows in the two tables, and they are used when they do intersect
select name from (select name from REGION union all select name from FARE) A group by A.name me having count(*) > 1;
Take the intersection of the results of two SQL statements without repetition
select name from (select A.name from REGION A inner join FARE B on A.name = B.name) C group by C.name;
5.3 no intersection value
The results of the first SQL statement are displayed, and the results that do not intersect with the second SQL statement are not repeated
select distinct name from FARE where (name) not in (select name from REGION);
5.4 CASE
CASE is a keyword used by SQL as logic such as IF-THEN-ELSE
Syntax:
SELECT CASE ("field name")
WHEN "condition 1" THEN "result 1"
WHEN "condition 2" THEN "result 2"
...
[ELSE "result N"]
END
FROM "table name";
**The condition can be a numeric value or a formula. ELSE clause is not required**
6. Sorting
Prepare the table for the experiment before sorting
create table class(name varchar(20),scores int(5));
insert into class values ('aa1',80);
insert into class values ('bb2',100);
insert into class values ('cc3',78);
insert into class values ('dd4',94);
insert into class values ('ee5',66);
insert into class values ('ff6',53);
insert into class values ('gg7',77);
insert into class values ('hh8',98);
select * from class;
6.1 ranking
Self join the table, and then list the results in order to calculate the number of rows before each row (including the row itself)
select a1.name, a1.scores,count(a2.scores) rank from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name = a2.name) group by a1.name,a1.scores order by a1.scores desc; #The value of the score field is smaller than its own value, and the number of the score field and the name field are the same. For example, ff6 is 6 + 1 = 7
6.2 calculate the median
select scores middle from (select a1.name, a1.scores,count(a2.scores) rank from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name <= a2.name) group by a1.name,a1.scores order by a1.scores desc) a3 where a3.rank = (select (count(*)+1) div 2 from class); #Each derived table must have its own alias, so alias A3 must have #DIV is a way to calculate quotient in MySQL
6.3 calculate cumulative total
Table self join, and then list the results in order to calculate the sum before each row (including the row itself)
select A1.*,sum(A2.scores) sum_socores from class A1,class A2 where A1.scores < A2.scores or(A1.scores = A2.scores and A1.name = A2.name) group by A1.name order by A1.scores desc;
6.4 total percentage
select A1.*,A1.scores/(select sum(scores) from class) per_sum from class A1,class A2 where A1.scores < A2.scores or (A1.scores = A2.scores and A1.name=A2.name) group by A1.name order by A1.scores desc; #SELECT SUM(Sales) FROM Total_ The sub query of sales is used to calculate the total #After the total is calculated, we can divide each row by the total to find the total percentage of each row
6.4 calculate cumulative total percentage
Calculate the cumulative total percentage of each row by dividing the cumulative total SUM(a2.Sales) by the total
select A1.name,A1.scores,sum(A2.scores),sum(A2.scores)/(select sum(scores) from class) per_sum from class A1,class A2 where A1.scores < A2.scores or (A1.scores = A2.scores and A1.name=A2.name) group by A1.name order by A1.scores desc;
Take a few digits after the decimal point
select A1.name,A1.scores,sum(A2.scores),truncate((sum(A2.scores)/(select sum(scores) from class)*100),0) ||'%' per_sum from class A1,class A2 where A1.scores < A2.scores or (A1.scores = A2.scorees and A1.name=A2.name) group by A1.name order by A1.scores desc;
