MySQL database query and instance.

Material is from the textbook down, the main content is practice examples, in writing blog post learning command line, of course, I hope this blog post can help others learn mysq database command

Select statement to select specific rows and columns from one or more tables

SELECT Specifies the name of the column to query, separated by commas between the columns and columns.

[all|   DISTINCT] All (default) displays all rows, including duplicate rows | DISTINCT Eliminating duplicate rows

[from table name [, table name] ...] Specify a table to query, you can specify more than two tables, and the table is separated by commas.

[WHERE clause] specifies the condition to query. The WHERE clause must immediately follow the FROM clause.

Lists the filter type and the criteria used to filter the data.

The [GROUP BY clause] clause is used to group the query structure.

[HAVING clause] specifies the conditions for grouping, usually after the GROUP BY clause.

The [ORDER BY clause] is used to sort the results of the query. "ASC (Ascending) | Desc (Descending) "

The [limit clause] restricts the output result rows of the query.

Here is an example

1. Check student's number (S_NO), name (s_name), contact number (phone) on student form (students)

Select  from students;

Operation Result:

2. Query (students) Table Middle School student's Department (D_NO), remove duplicate values. The SQL statements are as follows

Select  D_no   from  students;

This is a diagram with a distinct(remove duplicate lines) statement

This is not a distinct statement (a sharp contrast)

3. From the students table, count the number of boys (sex) students, and the SQL statements are as follows.

Select  as  from where sex=" male ";

Operation Result:

Count () is a calculated number of functions, the * number is a wildcard, as * * is a comment

4. Check the student's name (students table, s_name), Department name (department table, D_name) and contact address (students form, address), SQL statement as follows

Select  as  as  as  from where students.d_no = departments.d_no;

Multi-table query, each table following the from is separated by a comma where it indicates the full connection between the table and the table, because this is 2 tables, and they have the same column name, which must be linked to the table. Or you get the wrong data.

5. In the score table (score), the following is the SQL statement for students who have enrolled in the A001 course (C_NO) with a score of 80 or more.

Select  from where c_no="A001";

When there are 2 or more conditions, the conditions and conditions are linked directly with and

6. In the students table, check the date of birth (birthday) of students born in May 1992.

Select  from where " 1992-5-1 " ' 1992-5-31 ';

Between is the interval, the meaning of the range

Run effect

7. In the students table, check the school number (D_NO) for students who are D001 or D002.

Select  from where inch ('d001','d002');

Using in to find the data in the D_no table, there are a few more commas on the line.

8. In the students table, inquire about student information that is not empty for telephone (phone).

Select  from where  is null;

Query results

A true null value shows null , and a space is not NULL

9. Check the student information for surname (S_name) Li in the students table .

Select  from where s_name like' li%';

One of the possible values of the symbol inside the like (sort of a regular expression 0.0)

10. In the students table, inquire about the student information of the address (address) on Beijing Road.

Select  from where address like'% Beijing';

11. Query name (s_name) in the students table is a two-character student information

Select  from where ' __ ';

A ' _ ' represents a character,

Run results

So we can find the name is 2 bits of data.

the GROUP BY clause groups rows primarily based on fields. For example, grouping all the rows in the students table according to the majors that the students have learned, the result is that each professional student becomes a group.

The following example is slightly more difficult.

12. In the students table, according to the system (D_NO), the number of students in each department, the SQL statement is as follows

Select  as  as  from students group by D_no;

13. The number of male and female students (sex) in each department (D_NO) is counted in the students table. The SQL statements are as follows.

Select  as  as  as  from students group by D_no,sex;

You can specify multiple column groupings. If you specify a multi-column grouping, the first column you specify is grouped before the second column you specify, and so on.

14. Average score (report) and number of elective courses (C_NO) in the score table

Select  as  as  from score Group by C_no;

Run results

Because of the average score of the departments (C_NO), the c_no is grouped by the group by C_no

15. The number of faculty (D_NO) teachers in the teachers table is counted, including the summary line. The SQL statements are as follows.

Select  as  as  from  with Rollup;

Null 20 total number of teachers 20

Use the group by clause with the ROLLUP operator: The result set contains not only the normal rows provided by the GROUP by, but also the summary rows.

16. In the score table, in descending order of grades (report), list the student number (S_NO) and scores for the elective AOO1 course (c_no), and the SQL statements are as follows.

Select  as ' School Number '  as ' Courses '  as ' Achievements '  from where c_no='A001' ORDER by report Desc;

Run results

The default is ascending, and when the demand is ascending, the order by column name ASC can be not written when the demand is descending. Plus order BY column name Desc

MySQL database queries and instances.