数据库简单操作以及多表查询操作的一些总结-CSDN博客

student学生数据表


Sno	Sname	Ssex	Sage	Sclass
S101	张三	男	18	1
S102	李四	男	18	2
S103	王五	女	18	2
S104	赵六	男	18	3
S105	钱七	女	18	1
S106	孙八	女	18	1

course课程数据表

Cno	Cname	Tno
C001	大学语文	T01
C002	高等数学	T02
C003	大学英语	T03
C004	java编程	T04

score成绩数据表

Sno	Cno	Degree
S102	C002	78
S105	C002	87
S105	C003	65
S103	C001	76
S104	C001	83
S104	C002	76
S101	C003	45
S101	C004	63
S106	C002	20
S106	C003	84
S103	C003	62
S103	C004	55

teacher老师数据表

Tno	Tname	Tsex	Tage	Prof	Depart
T01	小布	男	38	副教授	计算机系
T02	小贤	女	52	讲师	电子工程系
T03	小谷	男	47	助教	计算机系
T04	小伟	女	45	助教	电子工程系

创建表时的一些约束条件的关键字：

　　not null：不能为空；　　unique：唯一；　　primary key：主键；　　foreign key references：外键；　　check：查询约束；　　default：默认约束；　　　　　　　　//注：foreign key(外键字段) references(外键的表)

1：查询列不重复的数据用 distinct

　　查询老师的职位有哪些？

　　SELECT DISTINCT Prof FROM teacher；　　　　　　　　　　//结果：副教授讲师助教

2：查询两个数之间的数据 between and

　　查询成绩在70-80之间的数据？

　　SELECT Degree FROM score WHERE Degree BETWEEN 70 AND 80　　　　　　　　　　//结果：78 76 76

3：以排序的形式查询数据 order by desc/asc

　　按降序排序显示所有人的成绩？

　　SELECT * FROM score ORDER BY Degree DESC;　　　　　　//desc：降序；asc：升序；都不写的话默认是升序

4：查询一列的有多少条数据 count() [as]

　　查询有多少个学生？

　　SELECT COUNT(Sno) as 学生人数 FROM student;　　　　　　　　　　//注：as后面是查询出来的数据显示的列名

5：查询平均数 avg()与分组查询group by

　　查询每门课的平均分与Cno

　　SELECT AVG(Degree),Cno FROM score GROUP BY Cno

6：模糊查询 like　　　　　　　　　　　　　　//注：%代表一个或者多个字符，_代表一个字符

　　查询名字里有小的教师的信息

　　SELECT * FROM teacher WHERE Tname LIKE ‘%小%’；

7：使用having对结果进行筛选

　　查询每科成绩大于80分的人的ID？

　　SELECT student.Sname FROM student,score WHERE student.Sno=score.Sno GROUP BY Cno HAVING score.degree>=80

8：使用limit进行分页查询

　　查询student表的第2条到第5条记录。

　　SELECT * FROM student LIMIT 1,4

9：使用in查询包含条件的信息　　　　　　　　　　　　　　　　　　　　//注：除了in，sql中还有any，all，some，其使用方法可见：https://www.cnblogs.com/HDK2016/p/8578587.html

　　查询ID=S102和S103的学生信息

　　SELECT * FROM student WHERE Sno IN (S102,S103)

10：使用union和union all使两个SELECT的结果子集合并　　　　　　　　//注：union会排除掉重复的数据，并进行排序，union all则不会

　　条件：要有相同的列，列的数据类型和顺序也要一样。

11：子查询　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　查询每科成绩大于每科平均成绩的信息

　　SELECT * FROM student,score WHERE Degree>(SELECT AVG(Degree) FROM score GROUP BY Cno) GROUP BY Cno

12：行列转换

　　SELECT name，SUM(CASE subject WHEN "语文" THEN grade END) AS "语文"，

　　　　　　　　　　SUM(CASE subject WHEN "数学" THEN grade END) AS "数学”，

　　　　　　　　　　SUM(CASE subject WHEN "英语" THEN grade END) AS "英语"

　　WHERE student GROUP BY name;

在进行多表查询的时候可遵循以下一些规则进行查询：

1、简单嵌套查询　　

　　子查询:子查询是一个SELECT查询，返回单个值且嵌套在SELECT、INSERT、UPDATE和DELETE语句或其它查询语句中，任何可以使用表达式的地方都可以使用子查询. 　　

　　SELECT id,name,sex,date FROM table1 WHERE id in(SELECT id FROM table2 WHERE id="")

内连接：把查询结果作为WHERE子句的查询条件即称为内连接

2、复杂的嵌套查询

　　多表之间的嵌套查询可以通过谓词IN实现，语法格式如下:

　　test_expression[NOT]

　　IN{

　　　　subquery

　　}

　　参数说明：test_expression指SQL表达式，subquery包含某结果集的子查询

多表嵌套查询的原理:无论是多少张表进行嵌套，表与表之间一定存在某种关联，通过WHERE子句建立此种关联实现查询

3、使用子查询作派生的表

　　在实际项目开发过程中经常用到从一个信息较为完善的表中派生出一个只含有几个关键字段的信息表，通过子查询就可以来实现这一目标,如

　　SELECT people.name,people.chinese,people.math,people.english FROM (SELECT name,chinese,math,english FROM tb_demo071) AS people

　　注:子查询应遵循以下规则:

　　　　(1)由比较运算符引入的内层子查询只包含一个表达式或列名，在外层语句中的WHERE子句内命名的列必须与内层子查询命名的列兼容

　　　　(2)由不可更改的比较运算符引入的子查询(比较运算符后面不跟关键字ANY或ALL)不包括GROUP BY 或 HAVING子句，除非预先确定了成组或单个的值

　　　　(3)用EXISTS引入的SELECT列表一般都由*组成，不必指定列名

　　　　(4)子查询不能在内部处理其结果

4、多表联合查询

　　利用SQL语句中的UNION，可以将不同表中符合条件的数据信息显示在同一列中。

　　SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten

　　注:使用UNION时应注意以下两点：

　　　　(1)在使用UNION运算符组合的语句中，所有选择列表的表达式数目必须相同，如列名、算术表达式及聚合函数等

　　　　(2)在每个查询表中，对应列的数据结构必须一样。

5、简单内连接查询

　　SELECT filedlist FROM table1 [INNER] JOIN table2 ON table1.column1 = table2.column1

　　其中，filedlist是要显示的字段,INNER表示表之间的连接方式为内连接，table1.column1=table2.column1用于指明两表间的连接条件。

6、利用HAVING语句过滤分组数据

　　HAVING子句用于指定组或聚合的搜索条件，HAVING通常与GROUP BY 语句一起使用，如果SQL语句中不含GROUP BY子句，则HAVING的行为与WHERE子句一样。

　　SELECT name,math FROM tb_demo083 GROUP BY id HAVING math > '95'

7、由IN引入的关联子查询

　　SELECT * FROM tb_demo083 WHERE code IN(SELECT code FROM tb_demo083 WHERE code = "")

转载于:https://www.cnblogs.com/EmilZs/p/9272346.html