您好,登錄后才能下訂單哦!
下文主要給大家?guī)?lái)MySQL多表查詢中如何運(yùn)用函數(shù),希望這些內(nèi)容能夠帶給大家實(shí)際用處,這也是我編輯MySQL多表查詢中如何運(yùn)用函數(shù)這篇文章的主要目的。好了,廢話不多說(shuō),大家直接看下文吧。
一、多表組合查詢
1.1 外連接
SQL語(yǔ)句可以通過(guò)外連接的方式對(duì)多表進(jìn)行聯(lián)合查找,外連接右分為做鏈接和有鏈接之分,其使用方法如下;
左外連接
語(yǔ)法:select 字段 from tb_a left join tb_b on 鏈接條件
注:tb_a表是主表,tb_b是從表,其中tb_a作為主表顯示全部?jī)?nèi)容,若表中無(wú)數(shù)據(jù)顯示則顯示為null。
右鏈接
語(yǔ)法:select 字段 from tb_a right join tb_b on 條件
注:tb_a表是從表,tb_b則為主表,都顯示。
例:首先創(chuàng)建數(shù)據(jù)庫(kù)jiaowu,并創(chuàng)建學(xué)生表和成績(jī)表
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,'孫悟空',1),(2,'豬八戒',2),(3,'沙悟凈',3),(4,'小白龍',4),(5,'唐三藏',5),(6,'紅孩兒',6),(7,'哪吒',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 | 孫悟空 | 1 | 1 | 1234 | | 2 | 豬八戒 | 2 | 2 | 1235 | | 4 | 小白龍 | 4 | 4 | 1423 | | 5 | 唐三藏 | 5 | 5 | 1120 | | 6 | 紅孩兒 | 6 | 6 | 1354 | | 6 | 紅孩兒 | 6 | 6 | 1367 | | 3 | 沙悟凈 | 3 | NULL | NULL | | 7 | 哪吒 | 7 | NULL | NULL | +------+-----------+------+------+-------+ 8 rows in set (0.00 sec)
使用別名查詢
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 | 孫悟空 | 1 | | 2 | 1235 | 2 | 豬八戒 | 2 | | 4 | 1423 | 4 | 小白龍 | 4 | | 5 | 1120 | 5 | 唐三藏 | 5 | | 6 | 1354 | 6 | 紅孩兒 | 6 | | 6 | 1367 | 6 | 紅孩兒 | 6 | +------+-------+------+-----------+------+ 6 rows in set (0.00 sec)
三張表如何鏈接
創(chuàng)建成績(jī)表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(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: 0 mysql> 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 | 孫悟空 | 1 | 1 | 1011 | 1 | 1234 | | 2 | 豬八戒 | 2 | 2 | 1012 | 2 | 1235 | | 4 | 小白龍 | 4 | 4 | 1162 | 4 | 1423 | | 5 | 唐三藏 | 5 | 5 | 920 | 5 | 1120 | | 6 | 紅孩兒 | 6 | 6 | 1107 | 6 | 1354 | | 6 | 紅孩兒 | 6 | 6 | 1107 | 6 | 1367 | | 6 | 紅孩兒 | 6 | 6 | 1118 | 6 | 1354 | | 6 | 紅孩兒 | 6 | 6 | 1118 | 6 | 1367 | +------+-----------+------+------+-------+------+-------+ 或者: 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 | 孫悟空 | 1 | 1011 | 1234 | | 2 | 豬八戒 | 2 | 1012 | 1234 | | 4 | 小白龍 | 4 | 1162 | 1234 | | 5 | 唐三藏 | 5 | 920 | 1234 | | 6 | 紅孩兒 | 6 | 1107 | 1234 | | 6 | 紅孩兒 | 6 | 1118 | 1234 | +------+-----------+------+-------+-------+ 6 rows in set (0.00 sec)
二、MySQL下的聚合函數(shù)
函數(shù):被封裝成特定功能的代碼塊
2.1 求和函數(shù)
查看總分?jǐn)?shù)
mysql> select sum(score) from grade; +------------+ | sum(score) | +------------+ | 7733 | +------------+ 1 row in set (0.01 sec)
2.2 查看平均分
mysql> select avg(score) from grade; +------------+ | avg(score) | +------------+ | 1288.8333 | +------------+ 1 row in set (0.00 sec)
2.3 查看最高分?jǐn)?shù)
mysql> select max(score) from grade; +------------+ | max(score) | +------------+ | 1423 | +------------+ 1 row in set (0.00 sec)
2.4 查看最高分?jǐn)?shù)及其對(duì)應(yīng)的sid
mysql> select sid,score from grade where score=(select max(score) from grade); +------+-------+ | sid | score | +------+-------+ | 4 | 1423 | +------+-------+
2.5 查看最低分及對(duì)應(yīng)的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 統(tǒng)計(jì)分?jǐn)?shù)大于1300的人數(shù)
mysql> select count(*) from grade where score > 1300; +--------------+ | count(score) | +--------------+ | 3 | +--------------+ 1 row in set (0.00 sec)
2.7 算術(shù)運(yùn)算
由于算法不同,沒(méi)人在原本基礎(chǔ)上各加30分;
mysql> update grade set score=score+30; Query OK, 6 rows affected (0.01 sec) Rows matched: 6 Changed: 6 Warnings: 0 mysql> select * from grade; +------+-------+ | sid | score | +------+-------+ | 1 | 1264 | | 2 | 1265 | | 4 | 1453 | | 5 | 1150 | | 6 | 1384 | | 6 | 1397 | +------+-------+ 6 rows in set (0.00 sec)
分?jǐn)?shù)設(shè)置為原來(lái)的80%
mysql> update grade set score=score*0.8;
2.8 字符串函數(shù)
substr(string,start,len)截取:從start開(kāi)始,長(zhǎng)度為len,。start從1開(kāi)始算。 mysql> select substr(name,1,2) from student where sid=1; +------------------+ | substr(name,1,2) | +------------------+ | 孫悟 | +------------------+ 1 row in set (0.00 sec) concat(str1,str2,str3,...)拼接 mysql> select concat(sid,name,id) from student; +---------------------+ | concat(sid,name,id) | +---------------------+ | 1孫悟空1 | | 2豬八戒2 | | 3沙悟凈3 | | 4小白龍4 | | 5唐三藏5 | | 6紅孩兒6 | | 7哪吒7 | +---------------------+ 7 rows in set (0.00 sec)
大小寫切換
mysql> select upper(name) from student where sid=10; +------------------+ | upper(name) | +------------------+ | CAPTAION AMERICA | +------------------+ 1 row in set (0.00 sec)
將大寫字母改成小寫
mysql> select lower(name) from student where sid=9; +-------------+ | lower(name) | +-------------+ | green giant | +-------------+ 1 row in set (0.00 sec)
日期查詢
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)
創(chuàng)建日期及使用
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 exist mysql> select * from tbdate; +------+------------+ | name | birthday | +------+------------+ | HA | 2017-11-17 | +------+------------+ 1 row in set (0.00 sec)
對(duì)于以上關(guān)于MySQL多表查詢中如何運(yùn)用函數(shù),大家是不是覺(jué)得非常有幫助。如果需要了解更多內(nèi)容,請(qǐng)繼續(xù)關(guān)注我們的行業(yè)資訊,相信你會(huì)喜歡上這些內(nèi)容的。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。