Shulou(Shulou.com)06/01 Report--

The following mainly brings you how to use functions in MySQL multi-table queries. I hope these contents can bring you practical use, which is also the main purpose of this article on how to use functions in editing MySQL multi-table queries. All right, don't talk too much nonsense, let's just read the following.

I. Multi-table combination query

1.1 external connection

The SQL statement can jointly search multiple tables by means of external joins. Outer joins can be divided into links and links. The usage is as follows

Left outer connection

Syntax: select field from tb_a left join tb_b on link condition

Note: tb_ a table is the master table, tb_b is the slave table, where tb_a displays all the contents as the master table, and null if there is no data display in the table.

Right link

Syntax: select field from tb_a right join tb_b on condition

Note: tb_ a table is the slave table, tb_b is the master table, both are displayed.

Example: first, create the database jiaowu, and create the student table and score table.

Mysql > create database jiaowu;mysql > create table student (sid int (10), name varchar (48), id int (11); mysql > create table grade (sid int (10), score int (5)) Mysql > insert into student values (1), (2), (2), (3), (4), (4), (5), (6), (7), (7); mysql > insert into grade (sid,score) values (1) 1234), (2) 1235), (4) 1423), (5) 1120), (6) 1354), (61367) Mysql > select * from student left join grade on student.sid=grade.sid +-+ | sid | name | id | sid | score | +-+ | 1 | Sun WuKong | 1 | 1 | 1234 | | 2 | Zhu Bajie | | 2 | 2 | 1235 | | 4 | Xiao Bailong | 4 | 4 | 1423 | | 5 | Tang Sanzang | 5 | 5 | 1120 | 6 | 6 | 6 | 1354 | 6 | Red Child | 6 | 6 | 1367 | 3 | Sha Wujing | 3 | NULL | NULL | 7 | Naga | 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.sidingg.siditterMySQL > select * from grade as g right join student as s on g.sidsidists.sidpolimysql > select * from grade as g left join student as s on g.sid=s.sid +-+ | sid | score | sid | name | id | +-+ | 1 | 1234 | 1 | Sun WuKong | 1 | 2 | 1235 | 2 | | Zhu Bajie | 2 | | 4 | 1423 | 4 | Xiao Bailong | 4 | | 5 | 1120 | 5 | Tang Sanzang | 5 | 6 | 1354 | 6 | Red Child | 6 | 6 | 1367 | 6 | Red Child | 6 | +-+ 6 sec |

How to link three tables

Create a score sheet grade2

Mysql > create table grade2 (sid int (10), score int (5)); Query OK, 0 rows affected (0.03 sec) mysql > insert into grade2 (sid,score) values (1pr 1234), (2meme 1235), (4meme 1423), (5meme 1120), (6meme 1354), (6je 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. 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 | Sun WuKong | 1 | 1011 | 1 | 1234 | | 2 | Zhu Bajie | 2 | 1012 | 2 | 1235 | 4 | Xiao Bailong | 4 | 1162 | 4 | 1423 | 5 | Tang Sanzang | 5 | 5 | 920 | 5 | 1120 | | 6 | Red Child | 6 | 6 | 1107 | 6 | 1354 | | 6 | Red Child | 6 | 1107 | 6 | 1367 | | 6 | Red Child | 6 | 6 | 1118 | 6 | 1354 | | 6 | Red Child | 6 | 6 | 1118 | 6 | 1367 | +-+ or: mysql > select s.* | G1.century from student g2.* grade g1grade2g2 where s.sid=g1.sid and g1.sid=g2.sid Mysql > select s.grade2 g2.score from student gradesgrade2 g2.score sgradesgrade2 where sgradesgrade2 g2 where s.sid=g1.sid=g2.sid grade2 g2 where s.sid=g1.sid=g2.sid +-+ | sid | name | id | score | score | +-+ | 1 | Sun WuKong | 1 | 1011 | 1234 | | 2 | Zhu Bajie | | 2 | 1012 | 1234 | | 4 | Xiao Bailong | 4 | 1162 | 1234 | 5 | Tang Sanzang | 5 | 920 | 1234 | | 6 | Red Child | 6 | 1107 | 1234 | | 6 | Red Child | 6 | 1118 | 1234 | +-+ 6 rows in set (0.00 sec) |

2. Aggregate function under MySQL

Function: a block of code encapsulated into a specific function

2.1 summation function

View the total score

Mysql > select sum (score) from grade;+-+ | sum (score) | +-+ | 7733 | +-+ 1 row in set (sec)

2.2 check the average score

Mysql > select avg (score) from grade;+-+ | avg (score) | +-+ | 1288.8333 | +-+ 1 row in set (sec)

2.3 View the highest score

Mysql > select max (score) from grade;+-+ | max (score) | +-+ | 1423 | +-+ 1 row in set (sec)

2.4 View 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 (sec)

2.6 the number of people with a statistical score greater than 1300

Mysql > select count (*) from grade where score > 1300 + | count (score) | +-+ | 3 | +-+ 1 row in set (0.00 sec)

2.7 arithmetic operation

Because the algorithm is different, no one adds 30 points on the original basis.

Mysql > update grade set score=score+30;Query OK, 6 rows affected 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)

Set the score to 80% of the original

Mysql > update grade set score=score*0.8

2.8 string function

Substr (string,start,len) interception: the length is len, starting from start. Start starts at 1. Mysql > select substr (name,1,2) from student where sid=1;+-+ | substr (name,1,2) | +-+ | Sun Wu | +-+ 1 row in set (0.00 sec) concat (str1,str2,str3,...) Splicing mysql > select concat (sid,name,id) from student +-+ | concat (sid,name Id) | +-+ | 1 Sun WuKong 1 | | 2 Zhu Bajie 2 | | 3 Shawujing 3 | | 4 Xiaobailong 4 | | 5 Tang Sanzang 5 | | 6 Red Boys 6 | | 7 where 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 capital 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 (13) | Birthday date) Query OK, 0 rows affected (0.04 sec)

Date of creation 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 | 0-11-17 | +-+-+ 1 row in set (2017 sec)

For the above on how to use functions in MySQL multi-table query, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.