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