Shulou(Shulou.com)05/31 Report--

What are the commonly used sql statements in Mysql databases? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

SQL statement advance

1. Query field:

-query all fields

Select * from table name

-query specified fields

Select field name, field name... From table name

-when multi-data tables join queries

Select table name. Field name, table name. Field name... From table name

-use as to alias the table

Select table alias. Field name from table name as table alias

-eliminate duplicate lines (distinct)

Select distinct field name from table name

two。 Conditional query:

-comparison operator (>, 18

(also means! =)

-logical operator (and,or,not)

Select * from table name where age > 18 and age2 (having is similar to where, filter condition, having can only be used for group by,where for table data)

-Summary with rollup

Select gender count (*) from table name group by gender with rollup; (finally, a new row showing the summary result)

6. Paging:

-query the first n pieces of data (limit is generally written at the best, indicating the display of the data after operation)

Select * from table name limit n

-pagination display

Select * from table name limit 0Jol 3; (3 per page, first page) select * from table name limit 3; (3 per page, 2 pages) select * from table name limit 6, 3; (3 per page, 3 pages)

7. Join query (key points):

-inner join... On (Internal connection)

Select * from table name 1 inner join table name 2 on table name 1.clsroomid= table name 2.id; (join table 1cls.id and table 2id together) select table name 1. Field name 1, table name 2. Field name. 2 from table name 1 inner jion indicates 2 on condition

-left/right join... On (left / right / external connection)

Select * from table name 1 left/right join table name 2 on table name 1.clsroomid= table name 2.id; (the result of the query is the data matched by the two tables and the data specific to the left table, populated with null for data that does not exist in the left / right table)

8. Subquery:

-scalar quantum query (the result returned by the subquery is one data (one row and one column)

Select * from table name where age > (select avg (age) from table name)

-column subquery (the result returned is one column (multiple rows in one column))

Select name from Table name 1 where id in (select cls_id from Table name 2)

-Row subquery (the returned result is one row (multiple columns))

Select * from table name where (height,age) = (select max (height), max (age) from table name). After reading the above, have you mastered any common sql statements in the Mysql database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.