数据库的常见题型--------------（查询）

今天来整理数据库常见查询题型，有助于温故而知新！！！

首先创建三张表：

S (Sno, Sname, Sage, Ssex) 学号、姓名、年龄、性别

-- 创建学生表s（SNO,sname,sage,sex,sdept）sno为主键约束，sname非空，性别自定义约束为0或者为1
CREATE  TABLE s(
 sno VARCHAR(10) PRIMARY KEY,
 sname VARCHAR(20) NOT NULL,
 sage  INT,
 sex   INT CHECK(sex IN(1,0)),
 sdept VARCHAR(10)
 ) 
 
 -- 给学生表插入数据
INSERT INTO s VALUES('95001','李勇',18,1,'IS');
INSERT INTO s VALUES('95002','王军',18,1,'IS');
INSERT INTO s VALUES('95003','赵燕',18,0,'IS');
INSERT INTO s VALUES('95004','钱悦',18,0,'MA');
INSERT INTO s VALUES('95005','刘晨',18,0,'MA');

-- 查看插入数据
SELECT * FROM s

C (Cno, Cname , Ccredit Cpno) 分别为课程号、课程名、学分、先行课程号

 -- 创建课程表c(cno,cname,credit)，其中CNO为主键，Cname不能重复
 CREATE TABLE c(
 cno VARCHAR(10) PRIMARY KEY,
 cname VARCHAR(10) UNIQUE,
 credit INT,
 cpno VARCHAR(255) NULL
 )
 
 -- 给课程表插入数据
INSERT INTO c VALUES('01','数据库原理',2,'课程1');
INSERT INTO c VALUES('02','线性代数',1,'课程2');
INSERT INTO c VALUES('03','数据结构',2,'课程3');

-- 查询课程表
SELECT * FROM c  

SC(Sno, Cno, Grade) 学号、课程号、成绩

-- 创建选修表SC(xh,kh,grade)，其中(xh,kh)为主键,xh参照S表sno，kh参照C表cno
 CREATE TABLE sc(
 sno VARCHAR(20) NOT NULL,
 cno VARCHAR(20) NOT NULL,
 grade INT,
 PRIMARY KEY (sno,cno),
 FOREIGN KEY (sno) REFERENCES s(sno),
 FOREIGN KEY (cno) REFERENCES c(cno)
 )
 
 -- 给选课表插入数据
INSERT INTO sc VALUES('95001','01',58); 
INSERT INTO sc VALUES('95004','01',58); 
INSERT INTO sc VALUES('95002','01',98); 
INSERT INTO sc VALUES('95003','02',88);
INSERT INTO sc VALUES('95002','02',70); 
INSERT INTO sc VALUES('95001','02',88); 
INSERT INTO sc VALUES('95002','03',70); 
INSERT INTO sc VALUES('95003','03',88);
INSERT INTO sc VALUES('95002','03',70); 

-- 查询选课表
SELECT * FROM sc;

1.查询平均成绩大于60分的同学的学号和平均成绩

    SELECT sc.sno AS '学号',AVG(grade) AS '平均成绩'
    FROM sc
    GROUP BY sno
    HAVING AVG(grade)>60;

2.查询所有学生的选课情况，输出学号、学生姓名、课程信息

--方法一
SELECT s.sno,s.sname,a.cname
FROM s
LEFT JOIN (SELECT sno,cname FROM sc,c WHERE sc.cno = c.cno) a
ON s.sno = a.sno;

--方法二
-- 此方法不完善，因为有null值
SELECT sc.sno,s.sname,c.cname
FROM s,sc,c
WHERE sc.sno = s.sno
AND sc.cno = c.cno

3.查询所有同学的学号、姓名、选课数、总成绩

-- 方法一：
SELECT s.sno AS '学号',s.sname AS '姓名',COUNT(sc.cno) AS '选课数',SUM(grade) AS '总成绩'
FROM s
LEFT JOIN sc ON sc.sno = s.sno
GROUP BY s.sno,s.sname

--方法二：
-- 此方法不可取，没有null值
SELECT sc.sno AS '学号',s.sname AS '姓名',COUNT(cno) AS '选课数',SUM(grade) AS '总成绩'
FROM s,sc
WHERE sc.sno=s.sno
GROUP BY s.sno,s.sname;

4.查询学过“01”但没有学过编号“02”课程的同学的学号、姓名

SELECT s.sno,s.sname
FROM s,sc sc1
WHERE sc1.sno = s.sno AND sc1.cno = '01'
AND NOT EXISTS(SELECT * FROM sc sc2 WHERE sc2.sno = sc1.sno AND sc2.cno='02'); 

5.统计姓“刘”的男学生的个数

SELECT COUNT(*) FROM s
WHERE sname LIKE'刘%' AND sex = '男';

6.查询选修了数据库原理的学生信息

-- 方法一：
SELECT s.* FROM s,sc,c
WHERE sc.sno = s.sno AND sc.cno=c.cno AND c.cname='数据库原理'; 

-- 方法二：
SELECT s.* FROM s
WHERE EXISTS(SELECT * FROM sc WHERE sc.sno=s.sno AND sc.cno='01');

7.查询数据库原理成绩最高分学生信息

SELECT s.* FROM s
WHERE sno IN(SELECT sno FROM sc 
WHERE grade = (SELECT MAX(grade) FROM sc,c WHERE sc.cno = c.cno AND c.cname='数据库原理'));

8.查询学生数据库原理的成绩，并按照成绩降序、姓名升序排列

SELECT sc.grade,s.sname FROM sc,c,s
WHERE sc.cno = c.cno AND sc.sno = s.sno AND c.cname='数据库原理'
ORDER BY grade DESC,sname ASC

9.编写存储过程求N的阶乘

CREATE  PROC  p_jiechen
  @num INT  
AS
 DECLARE @result BIGINT,@i  INT
 SET @result=1
 SET @i=1
 SET @ num = @num + 1
 WHILE(@i<@num)
 BEGIN
     SET @result = @result * @i
     SET @i = @i *1
   END
 print '10!='
     print @result

10.编写触发器,当插入一个学生时默认插入线性代数和数据结构课程

CREATE TRIGGER tri_insert
   ON  s 
   AFTER INSERT
AS 
BEGIN
  DECLARE @sno VARCHAR(50)
  
  SELECT @sno = sno  FROM  inserted

  INSERT INTO  SC (Sno,cno)
  SELECT @sno,cno
  FROM C
  WHERE Cname IN ('数据库','数据结构')
END

11.纵表变横表(聚合函数[max或者sum]配合case语句)

CREATE TABLE Table_A
(
    姓名 VARCHAR(20),
    课程 VARCHAR(20),
    成绩 INT
)

INSERT INTO Table_A(姓名,课程,成绩) VALUES('张三','语文',60);
INSERT INTO Table_A(姓名,课程,成绩) VALUES('张三','数学',70);
INSERT INTO Table_A(姓名,课程,成绩) VALUES('张三','英语',80);
INSERT INTO Table_A(姓名,课程,成绩) VALUES('李四','语文',90);
INSERT INTO Table_A(姓名,课程,成绩) VALUES('李四','数学',100);
-- 查询横表
select * from Table_A;

-- 纵表变横表
SELECT 姓名,
SUM(CASE 课程 WHEN '语文' THEN 成绩 ELSE 0 END) AS 语文,
SUM(CASE 课程 WHEN '数学' THEN 成绩 ELSE 0 END) AS 数学,
SUM(CASE 课程 WHEN '英语' THEN 成绩 ELSE 0 END) AS 英语
FROM tble_a
GROUP BY 姓名;

详解纵表和横表之间的相互转换

---

敲黑板

下来好好思考下两种方法之间的区别：

外连接可以取得主表中存在，子表中不存在的数据

自连接（内连接）只能找到共同的数据（交集）

---