#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)*记录数
78
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
