表的复杂查询

 

 http://blog.csdn.net/jkhere/article/details/27113139 

***一、表的复杂查询
    1、连接查询
        1.0连接的基本语法格式：
            from TABLE1 join_type TABLE2 [on (join_condition)][where (query_condition)]
            TABLE1:左表
            TABLE2：右表
            join_type：连接的类型。交叉、内连接、左外连接、右外连接
            on：设置连接条件
            where：对连接查询的结果进步一的筛选
        1.1交叉连接
            select * from CUSTOMER cross join ORDERS;
            或者
            select * from CUSTOMER,ORDERS;
            
            select c.name,o.order_number from CUSTOMER c,ORDERS o;
        1.2内连接：
            隐式内连接：(不使用on关键字，使用where)
                select * from CUSTOMER c,ORDERS o where c.id=o.customer_id;
            显式内连接：(使用on关键字)
                select * from CUSTOMER c inner join ORDERS o on c.id=o.customer_id;
        1.3外连接：
            左外连接：(返回符合连接条件的所有记录，同时还返回左表中其余的所有记录)
                select * from CUSTOMER c left outer join ORDERS o on c.id=o.customer_id;
            右外连接：(返回符合连接条件的所有记录，同时还返回右表中其余的所有记录)
                select * from CUSTOMER c right outer join ORDERS o on c.id=o.customer_id;
    2、子查询（嵌套查询）
        查询“陈冠希”的所有订单信息
        查询“陈冠希”的客户id select id from customer where name='陈冠希';
        查询订单信息：  select * from orders where customer_id=1;
        
        子查询： select * from orders where customer_id=(select id from customer where name='陈冠希');
    3、联合查询
        SELECT * FROM orders WHERE price>200 UNION SELECT * FROM orders WHERE customer_id=1;
        取两条语句的并集，并去除重复的记录。
    4、报表查询（合计函数）（使用原来的test数据库）
        
        统计一个班级共有多少学生？
        select count(*) from student;
        统计数学成绩大于90的学生有多少个？
        select count(*) from student where math>=90;
        统计总分大于250的人数有多少？
        select count(*) from student where (chinese+math+english)>250;
        
        统计一个班级数学总成绩？
        select sum(math) from student;
        统计一个班级语文、英语、数学各科的总成绩
        select sum(chinese),sum(english),sum(math) from student;
        统计一个班级语文、英语、数学的成绩总和
        select sum(chinese+english+math) from student;
        统计一个班级语文成绩平均分
        select sum(chinese)/count(*) from student;
        求一个班级数学平均分？
        select avg(math) from student;
        求一个班级总分平均分
        select avg(chinese+english+math) from student;

Tips:如果要使用关键字作为表名，要使用`(Esc按键下面的)包围起来。
        对订单表中商品归类后，显示每一类商品的总价
        select product,sum(price) from orders group by product;
        查询购买了几类商品，并且每类总价大于100的商品
        select product,sum(price) from orders group by product having sum(price)>100;
二、MySQL的数据库的备份与恢复
    数据库的备份：(不会备份数据库名)
        shell>mysqldump -u root -psorry test>c:/test.sql 
    恢复数据库：(数据库名必须存在)
        方式一：
        shell>mysql -u root -psorry test<c:/test.sql
        
        方式二：
        mysql>USE test;
        mysql>SOURCE c:/test.sql;

 

 

        //根据老师的id查学生的基本信息：方式三种
//            String sql = "select * from student where id in (select s_id from teacher_student where t_id=?)";//子查询
//            String sql = "select s.* from student s,teacher_student ts where s.id=ts.s_id and ts.t_id=?";//隐式内连接
            String sql = "select s.* from student s inner join teacher_student ts on s.id=ts.s_id where ts.t_id=?";;//显式内连接

full join 包含了左连接与右连接

 

例子

 

--Test Data
CREATE TABLE #First
(
    userID smallint,
    userName nvarchar(50),
    userAddress nvarchar(200)
)
INSERT INTO #First
SELECT 1, 'Kim', 'China' UNION ALL
SELECT 2, 'Tom', 'America' UNION ALL
SELECT 3, 'Lily', 'Japan'

CREATE TABLE #Second
(
    userID smallint,
    userName nvarchar(50),
    userAddress nvarchar(200)
)
INSERT INTO #Second
SELECT 2, 'Kim', 'China' UNION ALL
SELECT 5, 'Tom', 'America' UNION ALL
SELECT 6, 'Lily', 'Japan'

--FULL JOIN
/*
FULL JOIN就是把两个表完全连起来，不管对应的连接值是否存在
*/
SELECT * FROM #First a
FULL JOIN #Second b
ON a.userID = b.userID

--CROSS JOIN
/*
CROSS JOIN按笛卡尔积连接，也就是两表组合的任何形式
*/
SELECT * FROM #First a
CROSS JOIN #Second b

DROP TABLE #First
DROP TABLE #Second