《数据库的简单查询和连接查询》

《数据库的简单查询和连接查询》

用SQL语句完成以下查询要求

1.简单查询

①求全体学生信息。
②查询软件工程系所有学生的学号和姓名。
③查询全体学生的姓名和年龄。
④查询选修了课程的学生学号。
⑤查询选修了001课程的学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。
⑥查询选修了课程001且成绩在80至90（包括80和90）分之间的学生学号和成绩，并将成绩乘以0.9系数输出。
⑦查询讲师或副教授姓王的教师信息。
⑧查询缺少成绩的学生学号和课程号。

2.连接查询

①查询每个学生的基本情况及其选修课程情况。
②查询学生的学号、姓名、选修的课程名及成绩。
③查询王美同学的学号、姓名及其最高成绩。
④查询所有教师的教师号、姓名、职称和所授课程名。
⑤查询所有讲师所授课程的课程名称及学分。
⑥查询选修了两门以上（含两门）课程的学号。
⑦查询选修了数据库原理及应用课程且成绩在85分以上的学生学号、姓名及成绩。
⑧用语句Transact-SQL表示学生基本情况表（S）和选修课程情况表（SC）之间的左外连接、右外连接和完全外连接。

简单查询
# 1
SELECT * FROM s;
# 2
SELECT Sno, Sname FROM s WHERE Sdept = '软件工程系';
# 3
SELECT Sname, YEAR(CURRENT_DATE()) - YEAR(Sbirth) as age FROM s;
# 4
SELECT DISTINCT Sno FROM sc;
# 5
SELECT Sno, grade FROM sc WHERE Cno = '001' ORDER BY grade DESC, Sno ASC;
# 6
SELECT Sno, grade * 0.9 as adjusted_grade FROM sc WHERE Cno = '001' AND grade BETWEEN 80 AND 90;
# 7
SELECT * FROM t WHERE Tname LIKE '王%' AND (Ttitle = '讲师' OR Ttitle = '副教授');
# 8
SELECT Sno, Cno FROM sc WHERE grade IS NULL;

连接查询

#1
查询每个学生的基本情况及其选修课程情况。 
SELECT * FROM s LEFT JOIN sc ON s.Sno = sc.Sno;

使用了 LEFT JOIN 连接了学生表（s）和选课表（sc），以学生编号为连接条件，获取了学生表中每条记录的所有字段和对应选课表中的记录（如果有），如果没有选课则对应字段为 NULL。

LEFT JOIN 是 SQL 中的一种关联查询方式，它表示左连接。左连接会返回左表中所有的记录，即使在右表中没有匹配的记录。如果右表中没有匹配的记录，那么对应的字段值就会被填充为 NULL 。在 LEFT JOIN 中，左表是主表，右表是从表，连接条件是主表中的某个字段与从表中的某个字段相等。

#2
查询学生的学号、姓名、选修的课程名及成绩。
SELECT s.Sno, s.Sname, c.Cname, sc.grade FROM s JOIN sc ON s.Sno = sc.Sno JOIN c ON sc.Cno = c.Cno;

#3
查询王美同学的学号、姓名及其最高成绩。
SELECT s.Sno, s.Sname, ade) as highest_grade FROM s JOIN sc ON s.Sno = sc.Sno WHERE s.Sname = '王美';

#4
查询所有教师的教师号、姓名、职称和所授课程名。
SELECT t.Tno, t.Tname, t.Ttitle, c.Cname FROM t JOIN tc ON t.Tno = tc.Tno JOIN c ON tc.Cno = c.Cno;

#5
查询所有讲师所授课程的课程名称及学分。
SELECT c.Cname, c.Credits FROM t JOIN tc ON t.Tno = tc.Tno JOIN c ON tc.Cno = c.Cno WHERE t.Ttitle = '讲师';

#6
查询选修了两门以上（含两门）课程的学号。
SELECT Sno FROM sc GROUP BY Sno HAVING COUNT(*) >= 2;

#7
查询选修了数据库原理及应用课程且成绩在85分以上的学生学号、姓名及成绩。
SELECT s.Sno, s.Sname, sc.grade FROM s JOIN sc ON s.Sno = sc.Sno JOIN c ON sc.Cno = c.Cno WHERE c.Cname = '数据库原理及应用' ade > 85;

#8
用语句Transact-SQL表示学生基本情况表（S）和选修课程情况表（SC）之间的左外连接、右外连接和完全外连接。
SELECT * FROM s LEFT JOIN sc ON s.Sno = sc.Sno;-- 右外连接
SELECT * FROM s RIGHT JOIN sc ON s.Sno = sc.Sno;-- 完全外连接
SELECT * FROM s FULL JOIN sc ON s.Sno = sc.Sno;

(Note: MySQL does not support FULL JOIN. You can achieve FULL JOIN using UNION of LEFT JOIN and RIGHT JOIN if required.)

(注意:MySQL不支持FULL JOIN。如果需要，您可以使用LEFT JOIN和RIGHT JOIN的UNION来实现FULL JOIN。)

SELECT * FROM s LEFT JOIN sc ON s.Sno = sc.Sno
UNION
SELECT * FROM s RIGHT JOIN sc ON s.Sno = sc.Sno;

外连接例子:

MySQL：内左外、右外连接和完全连接的3个查询要求表达为：

select * from s inner join sc on s.sno = sc.sno;select s.sno, s.sname, sc.grade  from s left join sc on .sno=sc.sno;select  s.*, sc.grade  from  s  right  join  sc on s.sno=c.sno;MySQL不支持全连接（full join）可以使用: 
left join+ union + right join;(SELECT * from s left JOIN sc on s.sno=sc.sno)
UNION
(SELECT * from s RIGHT JOIN sc on s.sno=sc.sno );

注意：
如果想去掉查询结果表中的重复行，必须加指定distinct短语。

输入SQL语句时，应注意语句中均使用西文操作符号。

本地服务器名称可以通过查询“我的电脑”属性得到，或者将鼠标移至屏幕右下角正在运行的服务器图标上获取。

通过top关键字来限制返回到结果集中的行数，其基本格式如下。

Select  top  n  [ percent ]  *  from  表名
如果没有percent关键字，n就是返回前几条记录的行数；如果带有percent关键字，n则是返回结果集中行数的百分比。

对数据类型为text和image的字段不能使用Order by进行排序；

在Order by子句中也不能使用子查询、聚合函数和常量表达式。

思考：

在Select语句中使用** DISTINCT **关键字，可消除字段的重复项。

如果要查询学生表中前4行记录，应该使用的语句是

SELECT * FROM s LIMIT 4;

有一种查询叫模糊查询，在SQL语句中要使用LIKE关键字来实现这种查询方式。

如何提高数据查询和连接速度。

 1. 数据库设计优化：良好的数据库设计有助于提高查询和连接速度。例如，使用合适的数据类型、规范化表结构、添加索引等都可以加速查询和连接操作。2. 数据库服务器优化：优化数据库服务器也是提高性能的重要方面。例如，增加服务器内存、调整内存缓存、减少磁盘 I/O、使用 RAID 等技术来提高数据读写速度，都可以提高查询和连接速度。3. 合理使用索引：索引是数据库查询和连接速度提高的关键。使用恰当的索引可以大大减少查询时间和连接时间。但是索引不是越多越好，应该根据具体情况进行优化，因为索引需要占用空间并且会影响数据插入、更新和删除等操作的速度。4. 使用连接池技术：连接池技术可以提高并发查询和连接的效率。连接池将一些连接预先创建，查询时从池中获取连接并执行，执行完毕后将连接释放回池中，这样就避免了重复创建和销毁连接的开销。5. 编写优化的 SQL 语句：编写优化的 SQL 语句也可以提高查询和连接速度。例如，避免使用  `SELECT *` ，只查询需要的字段；使用  `JOIN`  时不要嵌套过多层；避免使用子查询等。

理解内连接、左外连接和右外连接的含义及表达方法

内连接、左外连接和右外连接是数据库中常用的三种表连接方式。1. 内连接：是指两个表之间的共同部分，只返回两个表中都存在的记录。内连接的语法如下：
SELECT * FROM table1 INNER JOIN table2 lumn = lumn;
其中， `INNER JOIN`  表示使用内连接， `table1`  和  `table2`  是要连接的两个表， `column`  是连接两个表的条件。2. 左外连接：是指返回左表的所有记录以及右表中与左表匹配的记录，如果右表中没有匹配的记录，则返回空值 NULL。左外连接的语法如下：
SELECT * FROM table1 LEFT JOIN table2 lumn = lumn;
其中， `LEFT JOIN`  表示使用左外连接， `table1`  和  `table2`  是要连接的两个表， `column`  是连接两个表的条件。3. 右外连接：是指返回右表的所有记录以及左表中与右表匹配的记录，如果左表中没有匹配的记录，则返回空值 NULL。右外连接的语法如下：
SELECT * FROM table1 RIGHT JOIN table2 lumn = lumn;
其中， `RIGHT JOIN`  表示使用右外连接， `table1`  和  `table2`  是要连接的两个表， `column`  是连接两个表的条件。需要注意的是，以上语法中的  `SELECT`  语句可以根据需要选择需要返回的列， `ON`  后面的条件可以根据具体情况进行调整。

表结构（c、s、sc、t、tc）已知一个表c，其建表语句为：
CREATE TABLE `c` (  `Cno` char(3) NOT NULL,  `Cname` varchar(20) NOT NULL,  `Cpno` char(3) DEFAULT NULL,  `Credits` tinyint(1) DEFAULT NULL,  PRIMARY KEY (`Cno`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3  表中数据为：
Cno	Cname	Cpno	Credits  
001	程序设计方法学	002	2  
002	C程序设计	NULL	2  
003	大学英语	NULL	2  
004	数据库原理及应用	007	4  
005	高等数学	NULL	4  
006	操作系统	004	2  
007	数据结构	002	3  
008	软件工程	001	3  
009	DB_design	004	1  
555	VB	002	1  
666	VB	002	1  
777	VB	002	1  
888	CC	001	2  
999	VB	002	2   已知一个表s，建表语句为：
CREATE TABLE `s` ( `Sno` char(8) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, `Sname` varchar(8) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, `Ssex` char(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, `Sbirth` date DEFAULT NULL, `Sdept` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT '软件工程系', PRIMARY KEY (`Sno`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 表中数据为：
Sno	Sname	Ssex	Sbirth	Sdept 
20418001	于文轩	男	2001-11-18	计算机科学系 
20418002	吴广斌	男	2002-02-08	计算机科学系 
20418003	王美	女	2001-09-17	软件工程系 
20418004	刘方超	男	2002-05-09	软件工程系 
20418005	孙涛	男	2002-11-26	网络工程系 
20418006	沈蒙	男	2002-01-22	网络工程系 
20418007	田玲	女	2001-12-17	信息科学系 已知一个表sc，建表语句为：
CREATE TABLE `sc` ( `Sno` char(8) NOT NULL, `Cno` char(3) NOT NULL, `grade` tinyint DEFAULT NULL, PRIMARY KEY (`Sno`,`Cno`), KEY `sc-c` (`Cno`), CONSTRAINT `sc-c` FOREIGN KEY (`Cno`) REFERENCES `c` (`Cno`) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT `sc-s` FOREIGN KEY (`Sno`) REFERENCES `s` (`Sno`) ON DELETE RESTRICT ON UPDATE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 表中数据为：
Sno	Cno	grade 
20418001	001	80 
20418001	002	91 
20418001	003	88 
20418001	004	85 
20418001	005	77 
20418001	006	73 
20418001	007	68 
20418001	008	80 
20418001	009	90 
20418002	001	79 
20418002	002	73 
20418002	003	84 
20418003	002	92 
20418003	003	81 
20418003	005	82 
20418004	004	75 
20418005	007	85 
20418005	009	NULL 已知一个表t，建表语句为：
CREATE TABLE `t` ( `Tno` char(6) NOT NULL, `Tname` varchar(10) NOT NULL, `Tsex` char(2) NOT NULL, `Tbirthday` date NOT NULL, `Ttitle` char(10) DEFAULT NULL, PRIMARY KEY (`Tno`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 表中数据为：Tno	Tname	Tsex	Tbirthday	Ttitle 
000001	李英	女	1964-11-03	教授 
000002	王大山	男	1985-03-07	副教授 
000003	张朋	男	1990-03-02	讲师 
000004	陈为军	男	1992-05-01	助教 
000005	宋浩然	男	1986-12-05	讲师 
000006	许红霞	女	1982-05-08	副教授 
000007	徐永军	男	1984-04-08	教授 已知一个表tc，建表语句为：
CREATE TABLE `tc` ( `Cno` char(5) NOT NULL, `Tno` char(6) NOT NULL, PRIMARY KEY (`Cno`,`Tno`), KEY `Tno` (`Tno`), CONSTRAINT `tc_ibfk_1` FOREIGN KEY (`Cno`) REFERENCES `c` (`Cno`) ON UPDATE CASCADE, CONSTRAINT `tc_ibfk_2` FOREIGN KEY (`Tno`) REFERENCES `t` (`Tno`) ON UPDATE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 表中数据为：Cno	Tno 
004	000001 
001	000003 
005	000003 
004	000005 现在需要使用SQL语句完成