SQL语言案例4

六、联表查询

1. 创建院系表

CREATE TABLE t_college(
col_id number,
col_name varchar2(256),
master varchar2(64)
);

2. 添加院系表数据

-- 语法:
-- insert into 表名 values(值1, 值2, 值n);
-- insert into 表名(列1, 列2) values(值1, 值2);
-- 如果不指定列名，默认是给所有列插入值，
-- 值和列的顺序和数据类型要一一对应。
INSERT INTO t_college values(1, '通信与信息工程学院', '胡欣荣');
INSERT INTO t_college(master, col_name, col_id)
values('李小林', '数字经济与信息管理学院', 2);

3. 修改语句

-- 语法: update 表名 set 列1 = 值1, 列2 = 值2, 列n = 值n where 条件;
UPDATE t_college SET master = '陈' WHERE col_id = 1;
-- t_student province
UPDATE t_student SET province = '宜宾' WHERE stu_id = '2023233893';
SELECT * FROM t_student WHERE stu_name = 'x';

4. 删除语句

-- 语法：delete from 表名 where 条件;
-- delete from 表名; 不要记。
-- delete 关键字后面没有列名，没有*号，谁写错了抄100遍！
DELETE FROM t_college WHERE col_id = 4;
DELETE FROM t_college WHERE master = 'allen';

5. 两张表联表查询：学生表和院系表

1. 查询学生姓名、学号、院系名称、院长

-- 多表查询之内连结的两种语法：
-- SQL89标准：
-- select 列名 from 表1, 表2 where 表1.关联列名 = 表2.关联列名;
SELECT stu_name, col_name, master
FROM t_student stu, t_college col
WHERE stu.COL_ID = col.COL_ID ;
-- 查询学生的学号、姓名、辅导员、院长
-- 如果某个字段在表1和表2中都存在，
-- 那么必须指定别名，例如：col.col_id
SELECT stu.stu_Id, stu.stu_name, stu.mentor,
col.master, col.col_id
FROM t_college col, t_student stu
WHERE col.COL_ID = stu.COL_ID ;

-- SQL99标准：
-- select 列名 from 表1 inner join 表2
-- on 表1.关联列名 = 表2.关联列名;
-- 查询学生姓名、学号、院系名称、院长
SELECT stu_name, stu_id, col_name, master
FROM t_student stu INNER JOIN t_college col
ON stu.COL_ID = col.COL_ID ;

6. 统计各院系的平均成绩，并按成绩倒序显示，要求显示：院系名称、平均成绩

-- 统计各院系、专业的平均成绩，并按成绩倒序显示，
-- 要求只显示平均成绩在60以上的数据
-- 只查询男生的成绩
-- 要求显示：院系名称、专业名称、平均成绩
SELECT col_name, major, avg(score) avgScore
FROM T_STUDENT ts, T_COLLEGE tc
WHERE ts.COL_ID = tc.COL_ID AND ts.GENDER = '男'
GROUP BY col_name, major
HAVING avg(score) > 60
ORDER BY avgScore DESC;

7. 三张表联表查询：学生表、课程表、成绩表

1. 查询出你自己的姓名、课程名称、成绩三个字段

SELECT stu_name, cou_name, sc.score
FROM T_STUDENT ts , T_COURSE tc , T_SCORE sc
WHERE ts.STU_ID = sc.STU_ID AND tc.COU_ID = sc.COU_ID
AND STU_NAME = '银';
-- sql 99
SELECT stu_name, cou_name, sc.score
FROM t_student stu INNER JOIN t_score sc
ON stu.STU_ID = sc.STU_ID
INNER JOIN T_COURSE cou
ON sc.COU_ID = cou.COU_ID
AND STU_NAME = '银';