5. mysql Multi-table query

The previous chapter briefly describes the operation of a single database table, this chapter details the methods and techniques for querying multiple tables, and the usage of some of the functions in MySQL query statements.

One, multi-table combination query

1.1 External connection

SQL statements can be joined in the way of multi-table joint lookup, outer joins right is divided into links and links, the use of the following methods;

Left outer connection

Syntax: Select field from tb_a left join Tb_b on link condition

Note: The Tb_a table is the main table, Tb_b is from the table, where tb_a as the main table shows the entire contents, if no data display in the table is displayed as null.

Right link

Syntax: Select field from Tb_a right join Tb_b on condition

Note: The Tb_a table is from the table, Tb_b is the main table and is displayed.

Example: first create a database Jiaowu, and create a student table and a score table

mysql> create database jiaowu;mysql> create  table student (Sid int), Name varchar, Id int (one));mysql> create  Table grade (Sid int (Ten), Score int (5)); Mysql> insert into student values ( 1, ' Monkey King ', 1), (2, ' pig ', 2), (3, ' Sha Wu ', 3), (4, ' Little White Dragon ', 4), (5, ' Don Sanzang ', 5), (6, ' Red Babe ', 6), (7, ' the ' ", 7););mysql>   Insert into  grade (Sid,score)  values (1,1234), (2,1235), (4,1423), (5,1120), (6,1354), (6,1367); 

mysql> select * from student  left join grade on  student.sid=grade.sid;+------+-----------+------+------+-------+| sid  | name       | id   | sid  | score |+------+---------- -+------+------+-------+|    1 |  Monkey King     |     1 |    1 |  1234 | |     2 |  Pig     |    2 |     2 |  1235 | |     4 |  Small white Dragon     |    4 |     4 |  1423 | |     5 |  Tang Sanzang     |    5 |     5 |  1120 | |     6 | Red Babe     |    6 |    6 |  1354  | |     6 |  Red babe     |    6 |     6 |  1367 | |     3 |  sha Wu Net     |    3 | null  |  null | |     7 |        |    7 |  null |  null |+------+-----------+------+------+-------+8 rows in set   (0.00 SEC)

Use alias query

Mysql> select * from student as s left join grade as  g on s.sid=g.sid;mysql> select * from grade as g right  join student as s on g.sid=s.sid;mysql> select * from  grade as g left join student as s on g.sid=s.sid;+------+-------+ ------+-----------+------+| sid  | score | sid  | name       | id   |+------+-------+------+-----------+------+|     1 |  1234 |    1 |  Monkey King      |    1 | |     2 |  1235 |    2 |  Pig      |    2 | |     4 |  1423 |    4 |  small white Dragon     |    4 | |     5 |  1120 |    5 |  Tang Sanzang      |    5 | |     6 |  1354 |    6 |  Red babe      |    6 | |     6 |  1367 |    6 |  Red babe      |    6 |+------+-------+------+-----------+------+6 rows in  set  (0.00 SEC)

Three sheets how to link

Create a score table Grade2

Mysql> create table grade2 (Sid int (Ten), Score int (5)); query ok, 0 rows affected  (0.03 sec) mysql> insert into   Grade2 (Sid,score)  values (1,1234), (2,1235), (4,1423), (5,1120), (6,1354), (6,1367); query ok, 6 rows affected  (0.00 sec) records: 6  duplicates:  0  warnings: 0mysql> show tables;+----------------+| tables_in_book |+-- --------------+| books          | |  category       | |  grade          | |  grade2         | |  student        | |  tbdate         |+----------------+6 rows in  set  (0.00 SEC)

Mysql> select s.*,g1.*,g2.* from student as s right join grade  as g1 on s.sid=g1.sid right join grade2 as g2 on s.sid =g2.sid;+------+-----------+------+------+-------+------+-------+| sid  | name       | id   | sid  | score | sid   | score |+------+-----------+------+------+-------+------+-------+|    1  |  Monkey King     |    1 |    1 |   1011 |    1 |  1234 | |     2 |  Pig     |    2 |     2 |  1012 |    2 |  1235 | |     4 |  Small white Dragon     |    4 |    4 |  1162 |     4 |  1423 | |     5 |  Tang Sanzang     |    5 |     5 |   920 |    5 |  1120 | |     6 |  Red babe     |    6 |     6 |  1107 |    6 |  1354 | |     6 |  Red babe     |    6 |     6 |  1107 |    6 |  1367 | |     6 |  Red babe     |    6 |     6 |  1118 |    6 |  1354 | |     6 |  Red babe     |    6 |     6 |  1118 |    6 |  1367 |+------+-----------+---- --+------+-------+------+-------+ or: mysql> select s.*,g1.*,g2.* from student s, Grade g1,grade2 g2 where s.sid=g1.sid and g1.sid=g2.sid;mysql> select  s.*,g1.score,g2.score from student s,grade g1,grade2 g2 where s.sid= G1.SID=G2.SID;MYSQL> SELECT S.*,G1.SCORE,G2.SCORE FROM STUDENT S,GRADE G1, grade2 g2 where s.sid=g1.sid=g2.sid;+------+-----------+------+-------+-------+| sid   | name      | id   | score | score  |+------+-----------+------+-------+-------+|    1 |  Monkey King      |    1 |  1011 |  1234 | |     2 |  Pig     |    2 |   1012 |  1234 | |     4 |  Small white Dragon     |    4 |   1162 |  1234 | |     5 |  Tang Sanzang     |    5 |    920 |  1234 | |     6 |  Red babe     |    6 |   1107 |  1234 | |     6 |  Red babe     |    6 |   1118 |  1234 |+------+-----------+------+-------+-------+6 rows in set   (0.00 SEC)

Second, the aggregation function under MySQL

Function: A block of code that is encapsulated into a specific function

2.1 Summation function

View total number of points

Mysql> select SUM (Score) from grade;+------------+|       SUM (score) |+------------+| 7733 |+------------+1 row in Set (0.01 sec)

2.2 Viewing average points

Mysql> Select AVG (score) from grade;+------------+|  AVG (Score) |+------------+| 1288.8333 |+------------+1 row in Set (0.00 sec)

2.3 View Highest Score

Mysql> select Max (score) from grade;+------------+|       Max (score) |+------------+| 1423 |+------------+1 row in Set (0.00 sec)

2.4 Viewing the highest score and its corresponding SID

Mysql> Select Sid,score from grade where score= (select Max (score) from grade) +------+-------+| Sid |    Score |+------+-------+|  4 | 1423 |+------+-------+

2.5 View the lowest score and corresponding SID;

Mysql> Select Sid,score from grade where score= (select min (score) from grade) +------+-------+| Sid |    Score |+------+-------+|  5 | 1120 |+------+-------+1 row in Set (0.00 sec)

2.6 People with statistical scores greater than 1300

Mysql> Select COUNT (*) from grade where score > 1300;+--------------+|            Count (score) |+--------------+| 3 |+--------------+1 row in Set (0.00 sec)

2.7 Arithmetic operations

Because the algorithm is different, no one added 30 points on the original basis;

Mysql> update grade set score=score+30; Query OK, 6 rows affected (0.01 sec) rows matched:6 changed:6 warnings:0mysql> select * from grade;+------+-------+ | Sid |    Score |+------+-------+|  1 |    1264 | |  2 |    1265 | |  4 |    1453 | |  5 |    1150 | |  6 |    1384 | |  6 | 1397 |+------+-------+6 rows in Set (0.00 sec)

The score is set to the original 80%

Mysql> update grade set score=score*0.8;

2.8 String Functions

SUBSTR (String,start,len) Intercept: From Start, Len, length. Start counting from 1. Mysql> select substr (name,1,2)  from student where sid=1;+------------------+|  substr (name,1,2)  |+------------------+|  Gohan               |+------------------+1 row in set  (0.00 sec) concat (str1 , STR2,STR3,...) Stitching Mysql> select concat (sid,name,id)  from student;+---------------------+| concat ( Sid,name,id)  |+---------------------+| 1 Monkey King 1             | |  2 Pig 2            | |  3 Sand Wu 3            | |  4 small white Dragon 4            | |  5 Tang Sanzang 5            | |  6 Red babe 6            | |  7 7              |+-------------- -------+7 rows in set  (0.00 SEC)

Case switching

Mysql> Select upper (name) from student where sid=10;+------------------+| Upper (name) |+------------------+| Captaion AMERICA |+------------------+1 row in Set (0.00 sec)

Change uppercase letters to lowercase

Mysql> Select lower (name) from student where sid=9;+-------------+| Lower (name) |+-------------+| Green Giant |+-------------+1 row in Set (0.00 sec)

Date Query

Mysql> Select Curdate (), now (), curtime (); +------------+---------------------+-----------+| Curdate () | Now () | Curtime () |+------------+---------------------+-----------+| 2017-11-17 | 2017-11-17 00:12:42 | 00:12:42 |+------------+---------------------+-----------+1 row in Set (0.00 sec) mysql> CREATE table tbdate (name Char (a), birthday date); Query OK, 0 rows affected (0.04 sec)

Creation date and use

mysql> INSERT into tbdate values (' HA ', now ()); Query OK, 1 row affected, 1 Warning (0.00 sec) mysql> select * from Tbdae; ERROR 1146 (42S02): Table ' Book.tbdae ' doesn ' t existmysql> select * FROM tbdate;+------+------------+| name | Birthday |+------+------------+| HA | 2017-11-17 |+------+------------+1 row in Set (0.00 sec)

This article is from "Little Chong" blog, please make sure to keep this source http://maoxiaoxiong.blog.51cto.com/11705634/1982665

5. mysql Multi-table query