我生待明日,万事成蹉跎

MYSQL基础:联接和组合查询

注:要特别注意在何种情况下使用外联接、索引和联接的区别,另外,一般组合查询和联接的区别在于所使用的表的数量。以下是演示示例:

/*两张表 班级表(t_classs), 学生表(t_student)

t_classs._infor 与 t_student._fk 连接*/

CREATE TABLE t_class(

_id INT PRIMARY KEY AUTO_INCREMENT,

_cname VARCHAR(50),

_code VARCHAR(20),

_infor INT

);

CREATE TABLE t_student(

_id INT PRIMARY KEY AUTO_INCREMENT,

_name VARCHAR(50),

_age INT,

_code VARCHAR(50),

_sex CHAR(2),

_fraction FLOAT,

_fk INT

);

INSERT INTO t_class(_cname,_code,_infor) VALUES 

(‘一班’,’t1′,1),

(‘二班’,’t2′,2),

(‘三班’,’t3′,3),

(‘四班’,’t4′,4);

INSERT INTO t_student(_name,_age,_code,_sex,_fraction,_fk) VALUES

(‘凤姐’,22,’网络’,’女’,30,1),

(‘屌丝哥’,27,’网络’,’男’,55,1),

(‘马化腾’,32,’IT’,’男’,44.5,2),

(‘李咏’,32,’央视’,’男’,77.5,3),

(‘张朝阳’,35,’IT’,’男’,79,1),

(‘倪萍’,32,’央视’,’女’,88,3),

(‘白岩松’,32,’央视’,’男’,89,2),

(‘崔永元’,37,’央视’,’男’,79,3),

(‘朱军’,41,’央视’,’男’,84,3),

(‘柳传志’,42,’IT’,’男’,59,2),

(‘雷军’,32,’IT’,’男’,31,2),

(‘乔布斯’,52,’IT’,’男’,95,2),

(‘唐骏’,38,’IT’,”,53,1),

(‘刘强东’,35,’IT’,’男’,73,1),

(‘周鸿祎’,36,’网络’,’男’,69,1);

— 1、查询一班中年龄在30 以上有哪些同学

SELECT _name FROM t_class,t_student WHERE t_class._infor=t_student._fk

AND _cname=’一班’ AND _age>30;

— 2、查询二班的平均分是多少

SELECT AVG(_fraction) FROM t_class,t_student WHERE t_class._infor=t_student._fk

AND _cname=’二班’;

— 3、查询一班的平均分比二班的平均分多多少?(分别算出一班和二班的平均分:SELECT (一班平均分)-(二班平均分) 后面有几道题和次同理)

SELECT (SELECT AVG(_fraction) FROM t_class,t_student WHERE t_class._infor=t_student._fk

AND _cname=’一班’) 

(SELECT AVG(_fraction) FROM t_class,t_student WHERE t_class._infor=t_student._fk  AND _cname=’二班’);

— 4、查询每个班的人数

SELECT _cname,COUNT(_name) FROM t_class LEFT JOIN t_student ON t_class._infor=t_student._fk GROUP BY _fk;

— 5、查询三班的人数比二班的人数多多少

SELECT (SELECT COUNT(_name) FROM t_class,t_student WHERE t_class._infor=t_student._fk AND _cname=’三班’ GROUP BY _fk)  

(SELECT COUNT(_name) FROM t_class,t_student WHERE t_class._infor=t_student._fk AND _cname=’二班’ GROUP BY _fk);

— 6、查询二班中哪些学生的年龄超过本班的平均年龄

SELECT _name FROM t_class,t_student WHERE t_class._infor=t_student._fk AND _age >

(SELECT AVG(_age) FROM t_class,t_student WHERE t_class._infor=t_student._fk AND _cname=’二班’)

AND _cname=’二班’;

— 7、查询一班中男生比女生多多少?

SELECT (SELECT COUNT(_name) FROM t_class,t_student WHERE t_class._infor=t_student._fk AND _sex=’男’

AND _cname=’一班’) –

(SELECT COUNT(_name) FROM t_class,t_student WHERE t_class._infor=t_student._fk AND _sex=’女’

AND _cname=’一班’)

— 8、查询一班中姓李的学生人数比二班中姓李的学生人数多多少

SELECT (SELECT COUNT(_name) FROM t_class,t_student WHERE t_class._infor=t_student._fk AND _name LIKE ‘李%’ AND _cname=’一班’) –

(SELECT COUNT(_name) FROM t_class,t_student WHERE t_class._infor=t_student._fk AND _name LIKE ‘李%’ AND _cname=’二班’);

— 9、查询每个班中的男生人数

SELECT _cname,COUNT(_name) FROM t_class LEFT JOIN t_student ON t_class._infor=t_student._fk AND _sex=’男’ GROUP BY _cname;

— 10、修改一班中每个学生的年龄加上一岁

UPDATE t_class,t_student SET _age=_age+1 WHERE t_class._infor=t_student._fk AND _cname=’一班’;

 

未经允许不得转载:徐宏涛博客 » MYSQL基础:联接和组合查询

分享到:更多 ()

评论 抢沙发

评论前必须登录!