数据库查询进阶--连接查询

#a.左外联接
#语法：SELECT 表1.字段,表2.字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段x = 表2.字段y;
#查询参加考试的学员名单(成绩表中有成绩的学员)
SELECT 
	students.studentNo AS 学员编号,
	studentName AS 学员姓名,
	studentResult AS 学员成绩
FROM results
LEFT JOIN students ON students.studentNo = results.studentNo;

#查询每位同学的成绩
SELECT 
	students.studentNo AS 学员编号,
	studentName AS 学员姓名,
	studentResult AS 学员成绩
FROM students
LEFT JOIN results ON students.studentNo = results.studentNo;

#b.右外联接
#语法：SELECT 表1.字段,表2.字段 FROM 表1 RIGHT JOIN 表2 ON 表1.字段x = 表2.字段y;
#查询参加考试的学员名单(成绩表中有成绩的学员)
SELECT 
	students.studentNo AS 学员编号,
	studentName AS 学员姓名,
	studentResult AS 学员成绩
FROM students
RIGHT JOIN results ON students.studentNo = results.studentNo;

#查询每个年级的科目信息
SELECT
	grades.gradeId AS 年级编号,
	gradeName AS 年级名称,
	subjectNo AS 科目编号,
	subjectName AS 科目名称
FROM grades
LEFT JOIN subjects 	ON 	grades.gradeId = subjects.gradeId;
#右外联接
SELECT
	grades.gradeId AS 年级编号,
	gradeName AS 年级名称,
	subjectNo AS 科目编号,
	subjectName AS 科目名称
FROM subjects
RIGHT JOIN grades ON 	grades.gradeId = subjects.gradeId;

1.左连接查询的使用：

#创建数据库bank
create database bank;
#选中数据库bank
use bank;
#创建银行卡表，一般余额使用double或者decemial
create table card(
cardid int not null comment'银行卡号',
balance int not null comment '余额'
)comment'银行卡表';
#创建用户表
create table user(
userid int not null comment'用户id',
username varchar(20) not null comment'用户姓名',
cardid int not null COMMENT'银行卡号'
)COMMENT'用户表';
insert into card
values(10001,10),
		  (10002,2000);
			
insert  user 
values(1,'A',10001),
		 (2,'B',10002),
		 (3,'C',10003),
		 (4,'D',10004);

#步骤：
#1.查出删除的两张卡
#2.将查到的卡插入card表，设置余额值为3

#z左连接
#方式1用not in
insert into card 
select cardid,3 from user
where cardid
not in(select user.cardid
from card 
left join user on card.cardid = user.cardid)

#方式2,用连接后是否为空值筛选
insert into card 
select user.cardid,3 from user left join card on user.cardid=card.cardid where card.cardid is null

2.右联接查询：

与左连接查询相似，可以理解为将左连接查询的两张表顺序交换，同样是上表。

insert into card 
select user.cardid,3 from card right join user on user.cardid=card.cardid where card.cardid is null

3.内连接查询

#SELECT 表1.字段,表2.字段 FROM 表1 INNER JOIN 表2 ON 表1.字段x = 表2.字段y;
#查询学员的【学员编号，姓名，年级编号,年级名称】信息
SELECT 
	studentNo AS 学员编号,
	studentName AS 学员姓名,
	students.gradeId AS 年级编号,
	gradeName AS 年级名称
FROM students
INNER JOIN grades	ON students.gradeId = grades.gradeId;

#从两个表中检索数据
SELECT 
	studentNo AS 学员编号,
	studentName AS 学员姓名,
	students.gradeId AS 年级编号,
	gradeName AS 年级名称
FROM students,grades
WHERE students.gradeId = grades.gradeId;

4.自连接查询：

自连接就是本行的某个字段是另一行的一个属性。如，,小米的pid为2，2是手机的id。

#建表
create table if not exists categries
(
id int(4) not null auto_increment comment'类别编号',
name varchar(50) not null comment'类别名称',
pid int(4) not null default 0 comment'父类编号',
constraint PK_id primary key(id)
)comment'类别表';
#数据插入
insert into categries(id,name,pid)
values(1,'计算机',0),
       (2,'手机',0),
       (3,'DELL',1),
			 (4,'Lenovo',1),
			 (5,'ASUS',1),
			 (6,'HUAWEI',2),
			 (7,'小米',2), 
			 (8,'OPPO',2);
#查看插入数据
select * from categries		

#自连接的使用	 
select 
  a.id as 父类编号,
  a.name as 父类名称,
	b.id as 子类编号,
	b.name as 子类名称
from categries as a
join categries as b on a.id = b.pid;

#NATURAL JOIN
SELECT 
			gradeId as 年级编号,
			gradeName as 年级名称,
			studentNo as 学号,
			studentName as 姓名
	FROM grades
	NATURAL JOIN students;
	
#使用USING子句
	SELECT 
			gradeId as 年级编号,
			gradeName as 年级名称,
			studentNo as 学号,
			studentName as 姓名
	FROM grades
	JOIN students USING(gradeId); 
	
#查询参加考试的学员的每门课程的成绩(多表联接)
SELECT results.subjectNo as 课程编号,
       subjectName as 课程名称,
       results.studentNo as 学号,
       studentName as 姓名,
       studentResult as 成绩
FROM results
JOIN subjects ON subjects.subjectNo = results.subjectNo
JOIN students ON students.studentNo = results.studentNo;

#使用AND 子句或WHERE 子句可应用附加条件：
  #AND
	SELECT results.subjectNo as 课程编号,
	       subjectName as 课程名称,
	       results.studentNo as 学号,
	       studentName as 姓名,
	       studentResult as 成绩
	FROM results
	JOIN subjects ON subjects.subjectNo = results.subjectNo
	JOIN students ON students.studentNo = results.studentNo
	AND results.subjectNo = 1;
 #WHERE（常用）
	SELECT results.subjectNo as 课程编号,
	       subjectName as 课程名称,
	       results.studentNo as 学号,
	       studentName as 姓名,
	       studentResult as 成绩
	FROM results
	JOIN subjects ON subjects.subjectNo = results.subjectNo
	JOIN students ON students.studentNo = results.studentNo
	WHERE results.subjectNo = 1;
	


# 交叉联接
SELECT 
				grades.gradeId as 年级编号,
        gradeName as 年级名称,
        subjectNo as 课程编号,
				subjectName as 课程名称
FROM grades
CROSS JOIN subjects;

SELECT 
				grades.gradeId as 年级编号,
        gradeName as 年级名称,
        subjectNo as 课程编号,
				subjectName as 课程名称
FROM grades,subjects;


#子查询
#查询姓名为张三的考试成绩
  #步骤一：在学员表中，查询姓名为张三的学号
	SELECT studentNo FROM students WHERE studentName='张三';#1004
  #步骤二：在成绩表中查询学号为1004的成绩
	SELECT * FROM results WHERE studentNo = 1004;
#使用子查询完成（使用关系运算符时，确保子查询的结果为单行单列）	
SELECT * FROM results WHERE studentNo=(SELECT studentNo FROM students WHERE studentName='张三');
#建议如下操作（如果子查询的结果为多行单列时，常使用IN子查询）
SELECT * FROM results WHERE studentNo IN(SELECT studentNo FROM students WHERE studentName like '李%');

#查询科目编号为1的学员成绩且成绩大于90
SELECT 
	studentResult as 成绩
FROM results WHERE subjectNo=1 AND studentResult>90;

#在成绩表中查询 任意 小于等于（科目编号为1的学员成绩且成绩大于90） 成绩信息
SELECT * FROM results 
WHERE studentResult <= ANY (SELECT 
	studentResult as 成绩
FROM results WHERE subjectNo=1 AND studentResult>90);

SELECT * FROM results 
WHERE studentResult <= ALL (SELECT 
	studentResult as 成绩
FROM results WHERE subjectNo=1 AND studentResult>90);

#使用EXISTS 运算符：
   #查询参加考试的学员信息。
	 #EXISTS子句中如果有记录返回True,如果没有记录返回false
  SELECT * FROM students WHERE EXISTS(
	SELECT * FROM results WHERE students.studentNo = results.studentNo);
	
	SELECT * FROM students WHERE 1=1;
	
#案例
CREATE TABLE IF NOT EXISTS game
(
	id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	password VARCHAR(20) NOT NULL
)COMMENT='游戏卡表';
#插入数据
INSERT INTO game(password) VALUES('abcoooiiiabc'),('ooiiabcooii'),('helloabciii');
#查询表中的数据
SELECT * FROM game;
#更新数据
UPDATE game SET password=REPLACE(password,'o','0');
UPDATE game SET password=REPLACE(password,'i','1');
#一条语句完成
UPDATE game SET password=REPLACE(REPLACE(password,'o','0'),'i','1');

#排序
/*
SELECT 列名,列名,...
FROM 表名
ORDER BY 表达式;
*/
#查询科目编号为1的学员成绩，并按由高到低排序
SELECT 
	subjectNo AS 科目编号,
	studentNo AS 学员编号,
	studentResult AS 成绩
FROM results
WHERE subjectNo = 1
ORDER BY studentResult DESC; #descending 降序

SELECT 
	subjectNo AS 科目编号,
	studentNo AS 学员编号,
	studentResult AS 成绩
FROM results
WHERE subjectNo = 1
ORDER BY studentResult ASC; #ascending默认为升序排序

#按多列排序，成绩相同的按学号排序
SELECT 
	subjectNo AS 科目编号,
	studentNo AS 学员编号,
	studentResult AS 成绩
FROM results
WHERE subjectNo = 1
ORDER BY studentResult,studentNo DESC;

/*
任务：将数据库表NumTab中的num列中的数据
       11-101,11-11,12-1,11-110,12-104,101-1先按减号前半部分排序，
	再按减号后面部分排序。
*/
#创建表
	DROP TABLE IF EXISTS NumTab;
	CREATE TABLE IF NOT EXISTS NumTab
	(
			id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
			num varchar(20) NOT NULL
	);
	#插入数据
	INSERT INTO numtab(num)
		VALUES
			('11-101'),
			('11-11'),
			('101-1'),
			('12-1'),
			('11-110'),
			('12-104');
	#查询所有数据
	SELECT * FROM numtab;
	#求-号的位置
	SELECT INSTR(num,'-') from numtab;
	#求-号前面的内容
	SELECT SUBSTRING(num,1,INSTR(num,'-')-1) FROM numtab;
	#求-号后面的内容
	SELECT SUBSTRING(num,INSTR(num,'-')+1) FROM numtab;
	#按-号前面内容排序
	SELECT * FROM numtab ORDER BY convert(SUBSTRING(num,1,INSTR(num,'-')-1),SIGNED);
	#按-号前面内容排序,再按-号后面的排序
	SELECT * FROM numtab 
		ORDER BY 
		convert(SUBSTRING(num,1,INSTR(num,'-')-1),SIGNED),
		convert(SUBSTRING(num,INSTR(num,'-')+1),SIGNED);
		
		
#统计参加考试的学员人数
SELECT COUNT(DISTINCT studentNo) AS 学员人数 FROM results;
#求总成绩、平均成绩、最高成绩和最低成绩
	 SELECT 
		SUM(studentResult) as 总成绩,
		AVG(studentResult) as 平均成绩,
		MAX(studentResult) as 最高成绩,
		MIN(studentResult) as 最低成绩
	FROM
		results;
		
#分组查询，统计每个年级的学员人数
	SELECT 
		gradeId as 年级编号,
		count(*) as 学员人数 
	FROM students
	GROUP BY gradeId;
#统计每个科目的平均成绩
 SELECT 
	subjectNo as 科目编号,
	AVG(studentResult) as 学员成绩
 FROM results 
 GROUP BY subjectNo;
 
 #统计每个年级下的每个性别的学员人数
	SELECT 
		gradeId as 年级编号,
		sex as 性别,
		count(studentNo) as 学员人数 
	FROM students
	GROUP BY gradeId,sex;
	
	
#分组之后再进行筛选，使用having子句
#统计每个年级的学员人数且总人数必须大于等于4
	SELECT 
		gradeId as 年级编号,
		count(*) as 学员人数 
	FROM students
	GROUP BY gradeId
	HAVING count(*)>=4;
	
#查询成绩表中的数据，要求每页显示8条记录，显示第1页数据 和 第8页数据
SELECT * FROM results LIMIT 0,8; #第1页数据
SELECT * FROM results LIMIT 56,8; #第8页数据 起始位置=(页码-1)*记录数