表的复杂查询
http://blog.csdn.net/jkhere/article/details/27113139
1 ***一、表的复杂查询 2 1、连接查询 3 1.0连接的基本语法格式: 4 from TABLE1 join_type TABLE2 [on (join_condition)][where (query_condition)] 5 TABLE1:左表 6 TABLE2:右表 7 join_type:连接的类型。交叉、内连接、左外连接、右外连接 8 on:设置连接条件 9 where:对连接查询的结果进步一的筛选 10 1.1交叉连接 11 select * from CUSTOMER cross join ORDERS; 12 或者 13 select * from CUSTOMER,ORDERS; 14 15 select c.name,o.order_number from CUSTOMER c,ORDERS o; 16 1.2内连接: 17 隐式内连接:(不使用on关键字,使用where) 18 select * from CUSTOMER c,ORDERS o where c.id=o.customer_id; 19 显式内连接:(使用on关键字) 20 select * from CUSTOMER c inner join ORDERS o on c.id=o.customer_id; 21 1.3外连接: 22 左外连接:(返回符合连接条件的所有记录,同时还返回左表中其余的所有记录) 23 select * from CUSTOMER c left outer join ORDERS o on c.id=o.customer_id; 24 右外连接:(返回符合连接条件的所有记录,同时还返回右表中其余的所有记录) 25 select * from CUSTOMER c right outer join ORDERS o on c.id=o.customer_id; 26 2、子查询(嵌套查询) 27 查询“陈冠希”的所有订单信息 28 查询“陈冠希”的客户id select id from customer where name='陈冠希'; 29 查询订单信息: select * from orders where customer_id=1; 30 31 子查询: select * from orders where customer_id=(select id from customer where name='陈冠希'); 32 3、联合查询 33 SELECT * FROM orders WHERE price>200 UNION SELECT * FROM orders WHERE customer_id=1; 34 取两条语句的并集,并去除重复的记录。 35 4、报表查询(合计函数)(使用原来的test数据库) 36 37 统计一个班级共有多少学生? 38 select count(*) from student; 39 统计数学成绩大于90的学生有多少个? 40 select count(*) from student where math>=90; 41 统计总分大于250的人数有多少? 42 select count(*) from student where (chinese+math+english)>250; 43 44 统计一个班级数学总成绩? 45 select sum(math) from student; 46 统计一个班级语文、英语、数学各科的总成绩 47 select sum(chinese),sum(english),sum(math) from student; 48 统计一个班级语文、英语、数学的成绩总和 49 select sum(chinese+english+math) from student; 50 统计一个班级语文成绩平均分 51 select sum(chinese)/count(*) from student; 52 求一个班级数学平均分? 53 select avg(math) from student; 54 求一个班级总分平均分 55 select avg(chinese+english+math) from student; 56 57 Tips:如果要使用关键字作为表名,要使用`(Esc按键下面的)包围起来。 58 对订单表中商品归类后,显示每一类商品的总价 59 select product,sum(price) from orders group by product; 60 查询购买了几类商品,并且每类总价大于100的商品 61 select product,sum(price) from orders group by product having sum(price)>100; 62 二、MySQL的数据库的备份与恢复 63 数据库的备份:(不会备份数据库名) 64 shell>mysqldump -u root -psorry test>c:/test.sql 65 恢复数据库:(数据库名必须存在) 66 方式一: 67 shell>mysql -u root -psorry test<c:/test.sql 68 69 方式二: 70 mysql>USE test; 71 mysql>SOURCE c:/test.sql;
1 //根据老师的id查学生的基本信息:方式三种 2 // String sql = "select * from student where id in (select s_id from teacher_student where t_id=?)";//子查询 3 // String sql = "select s.* from student s,teacher_student ts where s.id=ts.s_id and ts.t_id=?";//隐式内连接 4 String sql = "select s.* from student s inner join teacher_student ts on s.id=ts.s_id where ts.t_id=?";;//显式内连接
full join 包含了左连接与右连接
例子
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 --Test Data 2 CREATE TABLE #First 3 ( 4 userID smallint, 5 userName nvarchar(50), 6 userAddress nvarchar(200) 7 ) 8 INSERT INTO #First 9 SELECT 1, 'Kim', 'China' UNION ALL 10 SELECT 2, 'Tom', 'America' UNION ALL 11 SELECT 3, 'Lily', 'Japan' 12 13 CREATE TABLE #Second 14 ( 15 userID smallint, 16 userName nvarchar(50), 17 userAddress nvarchar(200) 18 ) 19 INSERT INTO #Second 20 SELECT 2, 'Kim', 'China' UNION ALL 21 SELECT 5, 'Tom', 'America' UNION ALL 22 SELECT 6, 'Lily', 'Japan' 23 24 --FULL JOIN 25 /* 26 FULL JOIN就是把两个表完全连起来,不管对应的连接值是否存在 27 */ 28 SELECT * FROM #First a 29 FULL JOIN #Second b 30 ON a.userID = b.userID 31 32 --CROSS JOIN 33 /* 34 CROSS JOIN按笛卡尔积连接,也就是两表组合的任何形式 35 */ 36 SELECT * FROM #First a 37 CROSS JOIN #Second b 38 39 DROP TABLE #First 40 DROP TABLE #Second