MySQL basic three (advanced)

MySQL basic threeStage One MySQL single-table query1. Querying All Records

SELECT * from    * from   *    student_detail;

2. Querying the selected column records

 from student;

3. Querying for records under specified conditions

 from student where s_id>2;

4. Alias the column after the query

 from student;

5. Fuzzy Query

 from ' Zhao% '    # % represents multiple characters  from ' _ Spring _ '   # _ represents a character

6. Sort order By:asc Ascending (default) desc descending

 from Student order by dept_id;     # Ascending   from Student order by dept_id Desc;    # Descending

7. Limit the number of displayed data

# output The first 2 data in ascending order by school number  from Student order by s_id limit 2; # output the 2 data following the 3rd data in ascending order of the study number  from Student order by s_id limit 3, 2;

8. Common aggregation Functions

#ask for the maximum ageSelect Max (age) fromStu_detail;#ask for the minimum ageSelect min (age) fromStu_detail;#sumSelect SUM (age) fromStu_detail;#Find AverageSelect AVG (age) fromStu_detail;#RoundingSelect Round (AVG (age)) fromStu_detail;#StatisticsSelect COUNT (age) fromStu_detail;

9. Packet Query group by

# Group the College columns in the student table and count the number of students per college  from student GROUP by dept_id;? #  #  must have a field after a select has occurred ? # See which colleges, only one student  from student GROUP by dept_id     Have count (dept_id) = 1;

Phase two Musql subquery

The select sentence that appears in other SQL statements. (Nested select in select)

# identify the ' software academy ' and ' foreign Language academy ' IDs in Tanzhou college  from Tanzhou where Tz_name=' software Academy 'or tz_name=' Foreign Language Institute ' ; +-------+| tz_id |+-------+|     1 | |     3 |+-------+

# identify students who are part of the ' Software Academy ' and ' foreign language academies ' in the student list  from inch  from Tanzhou where Tz_name=' software Academy 'or tz_name=' Foreign Language Institute ' ); +------+--------------+---------+| s_id | S_name       | dept_id |+------+--------------+---------+|    1 | Zhang San         |       3 | |    3 | Wangliuqi         |       1 | |    6 | The Old king next door     |       3 |+------+--------------+---------+

phase three MySQL correlation query1. Inner Connection [inner | cross] Join

• Unconditional internal connection: AKA cross-connect/Cartesian connection

Each item in the first table will be combined with each of the other tables in turn

 from student inner join Tanzhou;

• Conditional Intra-connection:

  On the basis of an unconditional inner join, add an ON clause

  When connecting, filter out rows of meaningful records for stitching

 from student inner joins Tanzhou on dept_id=tz_id;

2. Outer join {LIFET | right} join

• Left outer connection: (Based on left table)

When two tables are connected, the connection conditions do not match.

Leave the data in the left table, and the data in the right table is filled with null

 from Tanzhou left joins student on tz_id=dept_id;? +-------+--------------+------+--------------+---------+| tz_id | Tz_name |      s_id | s_name       | dept_id |+-------+--------------+------+--------------+---------+|     3 | Foreign Language Academy     |    1 | Zhang San         |       3 | |     2 | Academy of Arts     |    2 | John Doe         |       2 | |     1 | Software Academy     |    3 | Wangliuqi         |       1 | |     4 | Language Academy     |    4 | Chen Qi         |       4 | |     2 | Academy of Arts     |    5 | Guo Weitao       |       2 | |     3 | Foreign Language Academy     |    6 | The Old king next door     |       3 | |     5 | Esports Academy     | NULL | NULL         |    NULL |+-------+--------------+------+--------------+---------+

• Right outer join

Right outer join: (The right table is the benchmark)

When connecting to two tables, when the connection condition does not match

Leave the data in the right table, and the data in the left table is filled with null

 from student right join Tanzhou on tz_id=dept_id;? +------+--------------+---------+-------+--------------+| s_id | S_name |       dept_id | tz_id | tz_name      |+------+--------------+---------+-------+--------------+|    1 | Zhang San         |       3 |     3 | College of Foreign Languages     | |    2 | John Doe         |       2 |     2 | Academy of Arts     | |    3 | Wangliuqi         |       1 |     1 | Software Academy     | |    4 | Chen Qi         |       4 |     4 | Language Academy     | |    5 | Guo Weitao       |       2 |     2 | Academy of Arts     | |    6 | The Old king next door     |       3 |     3 | College of Foreign Languages     | | NULL | NULL         |    NULL |     5 | Esports Academy     |+------+--------------+---------+-------+--------------+

• Find a college with no students (esports students)

 SELECT * from  student Right join Tanzhou on tz_id=dept_id where s_id is   null;?  +------+--------+---------+-------+--------------+| s_id | S_name | dept_id | tz_id | Tz_name |+------+--------+---------+-------+--------------+| NULL |    NULL |     NULL | 5 | Esports Academy |+------+--------+---------+-------+--------------+ 

• outer joins more than one table

   Mysql> Select S_id,s_name,dept_id,tz_name,deptc_id,c_name from  student left Join Tanzhou on dept_id=tz_id left join course on Tz_id=deptc_id;  +------+--------------+---------+--------------+----------+--------+| s_id | S_name | dept_id | Tz_name | deptc_id |    C_name |+------+--------------+---------+--------------+----------+--------+| 3 |       Wangliuqi | 1 |        Software Academy | 1 |    Python | | 2 |       John Doe | 2 |        Academy of Arts | 2 |    java | | 5 |       Guo Weitao | 2 |        Academy of Arts | 2 |    java | | 1 |       Zhang San | 3 |        Foreign Language Academy | 3 |    C + + | | 6 |       The old King next door | 3 |        Foreign Language Academy | 3 |    C + + | | 4 |       Chen Qi | 4 |        Language Academy | 4 | Foreign language |+------+--------------+---------+--------------+----------+--------+ 

MySQL basic three (advanced)