集团网站建设价格,网站广告推广平台,昆明网站建设方案策划,网站域名购买com目录 创建学生表聚合查询聚合函数直接查询设置别名查询设置条件查询 常用的聚合函数 分组查询单个字段Group by报错分组查询多字段分组查询 多表查询直接查询重命名查询Students表新建一列CourseID 连接#xff08;JOIN#xff09;查询INNER JOINRIGHT JOIN, LEFT JOINFULL J… 目录 创建学生表聚合查询聚合函数直接查询设置别名查询设置条件查询 常用的聚合函数 分组查询单个字段Group by报错分组查询多字段分组查询 多表查询直接查询重命名查询Students表新建一列CourseID 连接JOIN查询INNER JOINRIGHT JOIN, LEFT JOINFULL JOIN课后练习复习 参考 创建学生表
创建Students和Courses表
CREATE TABLE Students (StudentID int PRIMARY KEY,Name varchar(255),Gender varchar(1),Age int,City varchar(255)
);
INSERT INTO Students VALUES(1, David, M, 21, Shanghai);
INSERT INTO Students VALUES(2, Kevin, M, 19, Beijing);
INSERT INTO Students VALUES(3, Emily, F, 22, Shanghai);
INSERT INTO Students VALUES(4, William, M, 20, New York City);
INSERT INTO Students VALUES(5, Alice, F, 19, Los Angeles);
INSERT INTO Students VALUES(6, Frank, F, 22, Los Angeles);CREATE TABLE Courses (CourseID int PRIMARY KEY,CourseName varchar(255)
);
INSERT INTO Courses VALUES(1, CS101);
INSERT INTO Courses VALUES(2, CS202);
INSERT INTO Courses VALUES(3, EE101);聚合查询 聚合函数
直接查询
SELECT COUNT(*) FROM Students;设置别名查询
SELECT COUNT(*) AS StudentsNum FROM Students;设置条件查询
使用COUNT(*) 和 COUNT(StudentID)是一样的效果因为StudentID是主键每行记录的主键都不同。另外我们在聚合查询中还是能使用WHERE子句的比如我们要查找年龄大于20岁的学生数量可使用以下SQL语句
SELECT COUNT(*) FROM Students AS s WHERE s.age 20; 常用的聚合函数
函数 说明 SUM 计算某一列的总和该列必须为数值类型 AVG 计算某一列的平均数该列必须为数值类型 MAX 计算某一列的最大值 MIN 计算某一列的最小值
查询学生的平均年龄
SELECT AVG(Age) FROM Students;使用AVG聚合函数
分组查询
单个字段Group by
根据城市City对学生进行分组并需要查询每个城市有多少学生
SELECT City, COUNT(*) FROM Students GROUP BY City;报错分组查询
如果将Name也放入查询结果会报错因为来自相同城市的学生可能有很多位名字各不相同所以以下语句是不合法的
SELECT City, COUNT(*), Name FROM Students GROUP BY City;报错信息如下 SELECT 列表的表达式 #3 不在 GROUP BY 子句中并且包含非聚合列“tuling.Students.Name”该列在功能上不依赖于 GROUP BY 子句中的列这与 sql_modeonly_full_group_by 不兼容。
#3就指的是Name那一列
多字段分组查询
GROUP BY 也能放入多个字段比如我们想先将学生根据City分组然后再根据性别分组可以使用以下语句
SELECT City, Gender, COUNT(*) FROM Students GROUP BY City, Gender;学生表中Los Angeles的两个人性别一样分组显示的为F计数为2 现在我将其中一人人的性别改为M思考一下查询结果会发生变化吗
地点相同性别不同的被区分出来了。 可以看到原来Los Angeles为2现在变成了两个1性别M和F被区分了。
多表查询
SELECT 查询除了能从单表中查数据外也能从多表中查询数据。语法如下
直接查询
SELECT * FROM Students, Courses;查询结果是两个表的笛卡尔积 假设Students有5列字段和7行记录Courses有2列字段和3行记录其结果就是一个拥有21 (3 * 7) 行记录和 7 (5 2) 列字段的二维表既Students表的每一行和Courses表的每一行两两拼在一起。结果集的列数会是两表列数之和而行数是两表行数之积。 重命名查询
有时候两张表可能拥有相同名字的字段那么结果则会让人疑惑我们可以通过使用 AS 取别名来区别字段。比如使用以下语句给StudentID和CourseID改名为StudentId和CourseId
SELECT Students.StudentID AS StudentId, Courses.CourseID AS CourseId FROM Students, Courses;给字段取别名的语法类似 column_name AS new_column_name上面的语句分别给StudentID和CoursesID取了别名StudentId和CourseId虽然在这个例子中我们只是将最后的字母D变为小写。但考虑一下另一种情况假设Students和Courses的主键字段都叫ID那么这个别名就很有帮助了我们可以使用以下语句使查询结果更明了
SELECT Students.ID AS StudentId, Courses.ID AS CourseId FROM Students, Courses;除了给输出字段取别名外给表格取别名也很有用的语法类似table_name AS alias我们可以将上面的语句写成下面这样
SELECT S.ID AS StudentId, C.ID AS CourseId FROM Students AS S, Courses AS C;Students表新建一列CourseID
在多表查询中还是能使用WHERE子句。为了帮助大家理解之后的内容我们需要给Students添加一列新的字段CourseID代表此学生选择的课程
新建一列CourseID在City之后
alter table Students add column CourseID int(4) not null after City;CouseID的值设置为StudentID的值一半
update Students set CourseID StudentID/2查询StudentID和其对应的课程名字
SELECT S.StudentID,C.CourseName
FROM Students AS SCourses AS C
WHERE S.CourseID C.CourseID;报错 知道为什么吗一般 erro syntax就是中文字符的问题
Students as SCourses as C 这个逗号是中文字符改成英文字符后运行成功啦除了WHERE子句其他子句类似ORDER BYGROUP BY也都适用于多表查询。
除了WHERE子句其他子句类似ORDER BYGROUP BY也都适用于多表查询。
连接JOIN查询
INNER JOIN
连接查询是另一种类型的多表查询连接查询会对多个表格进行JOIN运算。也就是说先确定一个主表作为结果集然后将其他表的记录有选择性地“嵌入”到主表结果集上。
假设我们想要知道每个学生选择的课程名字除了上面提到的多表查询加WHERE子句我们还能使用INNER JOIN子句
SELECT S.StudentID, C.CourseName
FROM Students AS S
INNER JOIN Courses AS C ON S.CourseID C.CourseID;此语句就能将每个StudentID和其对应的课程名查询出来要注意INNSER JOIN语句的内在执行过程如下
1 确定主表使用 FROM table_name2 紧接着确认连接的表使用 INNER JOIN table_name3 再确定连接条件使用 ON condition上面语句的条件就是 S.CourseID C.CourseID4 最后还能加上 WHERE、ORDER BY等子句 除了INNER JOIN外我们还有LEFT JOIN, RIGHT JOIN, 和FULL JOIN。
补充别名不是必须的只是为了增加可读性。
RIGHT JOIN, LEFT JOIN
如果我们将语句改为 RIGHT JOIN
SELECT S.StudentID, C.CourseName
FROM Students AS S
RIGHT JOIN Courses AS C ON S.CourseID C.CourseID;修改Students表格让5,6选课程1重新右连接查询。 执行上面的语句之后如果有一节课没有任何学生加入我们会有一行多余的记录记录中仅有CourseName但是StudentID为NULL。 为什么会这样呢INNER JOIN 会返回同时存在两张表的数据如果Students有1, 2, 3, 5课号Courses也有1, 2, 3, 4课号那么结果就是其相交集1, 2, 3。而 RIGHT JOIN 返回的则是右表存在的记录如果左表不存在右表中的某几行那结果中的那几行就会是NULL。
而 LEFT JOIN 则会返回左表中都存在的数如果给Students加上CourseID10即使Courses表中没有ID为10的课程记录那么LEFT JOIN的结果还是会多一行记录其对应的CourseName是NULL。补充LEFT JOIN 在有些数据库中叫做 LEFT OUT JOIN同理 RIGHT JOIN 也可能叫做 RIGHT OUT JOIN。
FULL JOIN
最后一种 JOIN 是 FULL JOIN结果集会把两张表的所有记录全部选出来并自动把两张表中不存在的列补充为NULL。
为了帮助大家理解连接查询的逻辑大家可以参看以下的图示左边的圆可以理解为左表右边的圆可以理解为右表。 以上就是SQL语法的基本教程啦现在大家已经学会了如何使用 SQL 创建表格和记录并使用高级的 SQL 语句进行复杂的查询下一章我们就开始学习使用真正的数据库软件 MySQL。
课后练习
请写出SQL语句找出加入CourseID为1的学生数量和课程名字要注意的是我们只寻找那些年纪大于20岁的男学生数量。
方法1 使用Where
SELECT COUNT(*) AS StudentsNum,CourseName
FROM Students AS S,Courses AS C
WHERE S.CourseID C.CourseID AND S.CourseID 1 AND S.Age 20 AND S.Gender M;方法2 Group by where
SELECT COUNT(*) AS StudentsNum, CourseName
FROM Students LEFT JOIN Courses ON Students.CourseID Courses.CourseID
WHERE Students.Age 20 AND Students.Gender M
GROUP BY Students.CourseID;复习
1 确定主表使用 FROM table_name2 紧接着确认连接的表使用 INNER JOIN table_name3 再确定连接条件使用 ON condition上面语句的条件就是 S.CourseID C.CourseID4 最后还能加上 WHERE、ORDER BY等子句 除了INNER JOIN外我们还有LEFT JOIN, RIGHT JOIN, 和FULL JOIN。
参考
【SQL高级查询语句:聚合查询,多表查询,连接查询【关系数据库SQL教程5】】 https://www.bilibili.com/video/BV1Zp4y1Q7mj/?share_sourcecopy_webvd_sourcefe6c23f6f1353ed1eff5d5e866171572
