MYSQL/MARIADB DML Operation Select

Objective

We have explained the basics of MYSQL/MARIADB and DDL-related operations, and then we will take a look at the mysql/mariadb DML operation, because the select query is more complex and more important, so this article is mainly a detailed explanation of the select query.

DML operations

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/73/wKiom1VJkm_hxly7AAMNR_G1grk976.jpg "title=" Dml.jpg "alt=" Wkiom1vjkm_hxly7aamnr_g1grk976.jpg "/>

A SELECT of DML

Projection Query

Select Col_name,[col_name1,...] FROM Table_name;select * FROM TABLE_NAME, #显示全表遍历整张数据表, but when the system resource consumption is large, then the large data volume of the query, Prohibit the use of this type of operation

Conditional query

Select Col_name,[col_name1,...] FROM table_name where where_definition# condition comparison operator = #等值比较 <=> #等值比较, including safe comparison with NULL < > or! = #不等值比较 <,<=,>,>= #其它比较符IN #指定范围内值的存在性测试BETWEEN ... And ... #在某取值范围内IS null #是否为空值IS NOT null #是否为非空LIKE #可使用通配符:%, _rlike or regexp #可使用正则表达式的模式 # logical operator and Ornot

Aggregate queries

AVG (): Average sum (): Sum Max (): Max min (): Min count (): Total Records

Clause modifier

GROUP by #对符合条件的结果进行分组HAVING: Filter The results of an aggregate query order by Col1[,col2] {asc| DESC} #排序LIMIT [Offset,]count #限制输出行数

Select Execution Process

From---------------and-----and

Connection Query

two or more tables in advance to perform the corresponding join operation, Then make a query based on the join result

cross join: Cartesian product, cross connection select * from tab1,tab2;  Inner join: Inner Connection # equivalent connection Select * from tab1 inner join tab2 on tab1.col _name = tab2.col_nameouter join: Outer connection left outer join: Left outer connection Select s.name as  student,t.name as teacher from students as s lift join  Teachers as t on s.teacherid = t.tid;  right outer join: Right outer connection     select s.name as student,t.name as teacher from  Students as s right join teachers as ton s.teacherid = t.tid ;     natural join: Natural connection, equivalent connection select tab1.col_name,tab2.col_name from  Tab1,tab2 where tab1.col_name = tab2.col_name; 

Sub-query

Query based on the result of a query statement

Subquery for the WHERE clause

① used to compare subqueries in an expression requires subqueries to return only a single result select Name,age from students where > (select AVG (age) from students); ② used to determine whether the subquery exists in the specified list select Name from students where Stuid in (select TID from teschers); ③ for exists neutron query

For subqueries in from

Select Alias.col,... from (SELECT statement) as alias where Clauseselect s.name from (SELECT * from students where ; As s where s.name like ' s% ';

Note:mysql/mariadb is not mature in sub-query optimization, so try to avoid using subqueries

Federated queries

Merge output from the results of the same query with two or more return value fields

SELECT statement Union select Statementselect name,age from teachers where age>=40 union select name,age from students where age>=40;

Select combat

Table structure

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/75/wKioL1VJ3PvQAmUpAAKjBbhoib8087.jpg "title=" 1.jpg " alt= "Wkiol1vj3pvqamupaakjbbhoib8087.jpg"/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/79/wKiom1VJ26DRdh3NAAH7mRXXnIA739.jpg "title=" 2.jpg " alt= "Wkiom1vj26drdh3naah7mrxxnia739.jpg"/>

Group by ClassID, showing the number of students per class

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/79/wKiom1VJ29vRLcI6AACq3EN5vzA400.jpg "title=" 3.jpg " alt= "Wkiom1vj29vrlci6aacq3en5vza400.jpg"/>

Group by gender, showing the sum of their age

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/79/wKiom1VJ3IvxA-sKAAB6Gyk75Go008.jpg "title=" 4.jpg " alt= "Wkiom1vj3ivxa-skaab6gyk75go008.jpg"/>

Group in ClassID to show classes with an average age greater than 25

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/75/wKioL1VJ35GhhSPVAACbXt1hYIs496.jpg "title=" 5.jpg " alt= "Wkiol1vj35ghhspvaacbxt1hyis496.jpg"/>

Group by gender, showing the age of students older than 25 in each group

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/79/wKiom1VJ3zLDcN4pAAByhZBzhJE193.jpg "title=" 6.jpg " alt= "Wkiom1vj3zldcn4paabyhzbzhje193.jpg"/>

Show the first 5 students ' names, courses and grades

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/76/wKioL1VJ40yxvGm1AAHMozrkQQU793.jpg "title=" 7.jpg " alt= "Wkiol1vj40yxvgm1aahmozrkqqu793.jpg"/>

Show the names and courses of students whose grades are above 80

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/76/wKioL1VJ4_bgzH7kAAGQ7rHbyT8098.jpg "title=" 8.jpg " alt= "Wkiol1vj4_bgzh7kaagq7rhbyt8098.jpg"/>

Ask the first 8 students each of their own two classes of average results, and in descending order

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/76/wKioL1VJ5puQXyfBAAFGckvbgUs609.jpg "title=" 9.jpg " alt= "Wkiol1vj5puqxyfbaafgckvbgus609.jpg"/>

Show the name of each course and the number of students who have studied this course

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/7A/wKiom1VJ6MOCvZFrAAEJAPED65E191.jpg "title=" 10.jpg "alt=" Wkiom1vj6mocvzfraaejaped65e191.jpg "/>

Show the names of students whose age is greater than the average age

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/7B/wKiom1VJ6cmgrkBJAACWDPkAnNo217.jpg "title=" 11.jpg "alt=" Wkiom1vj6cmgrkbjaacwdpkanno217.jpg "/>

Statistics of the average age of the students in each class

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/76/wKioL1VJ7U6AEN4MAACyOqVPhD8468.jpg "title=" 12.jpg "alt=" Wkiol1vj7u6aen4maacyoqvphd8468.jpg "/>

The end

OK, select query on the first point here, it seems that select is so little things, in fact, it is not easy to really use good, so the small partners to practice it, follow-up article will continue to explain the MYSQL/MARIADB series of knowledge, interested can continue to pay attention to OH. The above is only for individual learning to organize, if there are mistakes, big God do not spray ~ ~ ~

This article is from the "North Scholar" blog, please make sure to keep this source http://scholar.blog.51cto.com/9985645/1643563

MYSQL/MARIADB DML Operation Select