MySQL Select tone Common manual

Database as a warehouse for storing data, the query can be said to be the daily operation of the database accounted for the largest proportion, and select tone can be said to be the most complex.

Select is one of the DML tones, and other DML tones are included. Insert INTO, delete,update

Select Tone General usage is: Select field name from Tb_name where condition; Select query mood type generally divided into three kinds: single-table query, multi-table query, sub-query the simplest single-table query: SELECT * from tb_name;*, all fields query a specific field (projection): Select field Name 1, field Name 2, from Tb_name; Where Statement filter query (SELECT) SELECT * from tb_name where condition; The keyword DISTINCT is followed by the Select to indicate that repeated data in a field is displayed only once. For example, if there is a Table students field gender (gender), there is nothing more than a male or female, we look at the field, we need to display each category once is to distinct select distinct gender from students;From clause:From behind can follow a table, multiple tables, or other select to follow a table is a single table query, followed by more than one table is a multi-table query, followed by select is a nested query (subquery) so from is the relationship that we want to query.WHERE clause:WHERE clause: is a boolean relationship expression, general use >  <  >=  <= where   clause value does not need to be quoted, word characters need to be quoted where can also keep up with the logical and Or  not for example, query the students table for the field age, the student name during 20-25. Select Name,age from students where age >=20 and age <=25; where follow the LIKE keyword: like  : followed by% to indicate any character of any length &NBSP;__ represents a single character   such as finding the  name field in a students table and starting all data with Y, you need to use a like match. Select Name from students where Name ' y% ';  find Students table  name field followed by y 4 characters  select Name from Studen t where Name like ' y____ '   find  name field in students table, contains the fields of ING.  select name from students "where Name like '%ing% ';  find students in table the  name field begins with uppercase m,n,y the user  selcet Name F ROM students where name like ' M% ' or the name like ' N% ' or the name like '%Y ';  or using rlike (regular expression).  select name from students where Name rlike ' ^[mny].*$ ';  find students table for users aged 18,20,25   First method: Select Name fr OM students where age=18 or age=20 or age=25;  the second method: Use the in   keyword followed by a list of select Name fromStudent where Age in (18,20,25);  students has a field course (CID2). Find out the field, inside is empty field, student name NameSelect name from students where CID2 is null; query not empty is to be not null is null   and is not Nu ll can realize whether the field is empty   ORDER BY clause:Sort results after query: for example, we have a field course (CID2) in students. Find out the field, the name of the field that is empty, name of the student, if we want to sort the results. The default is ascending ASC uses the keyword ORDER by + the field to be sorted desc (Descending) Select Name from students where CID2 is null for ORDER by Name Desc;As clauseUsed to alias a field or table. If a field name is longer, or the table name is longer, for example: Selcet name as Na from students as Stu;LIMIT clause:The first N rows used to display the results display only the first 3 rows of select * from students limit 3; Only 3 rows are displayed, and the SELECT * from students limit 3, 5 is calculated from line 5th.GROUP BY clause:For grouping, for example, the students of the students table are grouped according to men and women select Age,gender from students group by Gender so that only two groups, male and female students table fields have a course field CID to find out this field, you need to display the number of courses, greater than or equal to 2Having clauseCan only be used with group by, after using group by, the having filter select CID from students where have CID >=2Multi-table query: If we have two tables students (student table) and course (curriculum, the table shows the number of each course (CID), name (Cname)).  Now we want to inquire, each student, to learn the first course of the course, what the name is. Select students. Name course. Cname from students, courses where students. Cid1=course. CID; Such a query is called a natural query. is based on a two-field set up the corresponding relationship on the basis of the query only the students take a course on the display, if the course is not shown as null so that you can not use the natural connection, you need to use the outer connection outside the connection is divided into: Left outer join Tb_name on + join condition right outer connection Right jion Tb_name + join conditions For example, we query the name of the first course that each student is taking, if the course does not exist display NULL select students. Name,course. Cname from students left joins course on students. Cid1=course. CID query which courses have candidates, show the name of the classmate, no candidate, display null select students. Name,course. Cname from students right joins course on students. Cid1=course. Cidsubquery: (select query nested another Select) Find students in the table, older than the average age of the classmate select Name from students where > (select AVG (ages) from students); Where avg: is the function of averaging to use subqueries in comparison operators: Subqueries can only return single words. Using subqueries in In

MySQL Select tone Common manual