您的当前位置：首页正文

MySQL练习-主外键多表查询

2023-11-11 来源：东饰资讯网

USE db1; 2 CREATE TABLE class( 3 cid INT AUTO_INCREMENT PRIMARY KEY, 4 caption VARCHAR(10) 5 ); 6 CREATE TABLE teacher( 7 tid INT AUTO_INCREMENT PRIMARY KEY, 8 tname VARCHAR(10) 9 );10 CREATE TABLE course(11 cid INT AUTO_INCREMENT PRIMARY KEY,12 cname VARCHAR(10),13 teach_id INT,14 CONSTRAINT fk_course_teacher FOREIGN KEY(teach_id) REFERENCES teacher(tid)15 );16 CREATE TABLE student(17 sid INT AUTO_INCREMENT PRIMARY KEY,18 sname VARCHAR(10),19 gender VARCHAR(10),20 class_id INT,21 CONSTRAINT fk_student_class FOREIGN KEY(class_id) REFERENCES class(cid)22 );23 CREATE TABLE score(24 sid INT AUTO_INCREMENT PRIMARY KEY,25 student_id INT,26 course_id INT,27 number INT,28 CONSTRAINT fk_score_student FOREIGN KEY(student_id) REFERENCES student(sid),29 CONSTRAINT fk_score_course FOREIGN KEY(course_id) REFERENCES course(cid)30 );创建表并建立表关系技术分享

 1 INSERT INTO class(caption) VALUES("三年二班"); 2 INSERT INTO class(caption) VALUES("一年三班"); 3 INSERT INTO class(caption) VALUES("三年一班"); 4 5 INSERT INTO teacher(tname) VALUES("波多"); 6 INSERT INTO teacher(tname) VALUES("苍空"); 7 INSERT INTO teacher(tname) VALUES("饭岛"); 8 9 INSERT INTO student(sname,gender,class_id) VALUES("钢蛋","女",1);10 INSERT INTO student(sname,gender,class_id) VALUES("铁锤","女",1);11 INSERT INTO student(sname,gender,class_id) VALUES("山炮","男",2);12 13 INSERT INTO course(cname,teach_id) VALUES("生物",1);14 INSERT INTO course(cname,teach_id) VALUES("体育",1);15 INSERT INTO course(cname,teach_id) VALUES("物理",2);16 17 INSERT INTO score(student_id,course_id,number) VALUES(1,1,60);18 INSERT INTO score(student_id,course_id,number) VALUES(1,2,59);19 INSERT INTO score(student_id,course_id,number) VALUES(2,2,100);

表数据

2.操作练习:

1、自行创建测试数据

1 INSERT INTO student(sname,gender,class_id) VALUES("钢铁侠","男",4);2 -- Cannot add or update a child row: a foreign key constraint fails (`db1`.`student`, CONSTRAINT `fk_student_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`))

测试失败数据

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

1 SELECT s1.student_id FROM (SELECT a.student_id,a.`number` FROM score a,course b 2 WHERE a.`course_id` = b.`cid` AND b.`cname` ="生物") s1 , (SELECT a.student_id,a.`number` FROM score a,course b 3 WHERE a.`course_id` = b.`cid` AND b.`cname`="体育") s24 WHERE s1.`student_id` = s2.student_id AND s1.number >s2.number

多表嵌套查询

3、查询平均成绩大于60分的同学的学号和平均成绩；

1 SELECT student_id,AVG(number)2 FROM score 3 GROUP BY student_id4 HAVING AVG(number) > 60

HAVING用法

4、查询所有同学的学号、姓名、选课数、总成绩；

5、查询姓“李”的老师的个数；6、查询没学过“叶平”老师课的同学的学号、姓名；7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；10、查询有课程成绩小于60分的同学的学号、姓名；11、查询没有学全所有课的同学的学号、姓名；12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；15、删除学习“叶平”老师课的SC表记录；16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； 17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分；18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；20、课程平均分从高到低显示（现实任课老师）；21、查询各科成绩前三名的记录:(不考虑成绩并列情况) 22、查询每门课程被选修的学生数；23、查询出只选修了一门课程的全部学生的学号和姓名；24、查询男生、女生的人数；25、查询姓“张”的学生名单；26、查询同名同姓学生名单，并统计同名人数；27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； 31、求选了课程的学生人数32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；33、查询各个课程及相应的选修人数；34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；35、查询每门课程成绩最好的前两名；36、检索至少选修两门课程的学生学号；37、查询全部学生都选修的课程的课程号和课程名；38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；39、查询两门以上不及格课程的同学的学号及其平均成绩；40、检索“004”课程分数小于60，按分数降序排列的同学学号；41、删除“002”同学的“001”课程的成绩；

MySQL练习-主外键多表查询

标签：varchar http date 建表表数据相同物理 var 语文

小编还为您整理了以下内容，可能对您也有帮助：

“mysql ”多表联合查询语句怎么写？

一使用SELECT子句进行多表查询SELECT 字段名 FROM 表1，表2 … WHERE 表1字段 = 表2字段 AND 其它查询条件SELECT a.id,a.name,a.address,a.date,b.math,b.english,b.chinese FROM tb_demo065_tel AS b,tb_demo065 AS a WHERE a.id=b.id

注:在上面的的代码中，以两张表的id字段信息相同作为条件建立两表关联，但在实际开发中不应该这样使用，最好用主外键约束来实现。

1、联合查询可合并多个相似的选择查询的结果集。等同于将一个表追加到另一个表，从而实现将两个表的查询组合到一起，使用谓词为UNION或UNION ALL。联合查询时，查询结果的列标题为第一个查询语句的列标题。因此，要定义列标题必须在第一个查询语句中定义。要对联合查询结果排序时，也必须使用第一查询语句中的列名、列标题或者列序号。

2、在使用UNION 运算符时，应保证每个联合查询语句的选择列表中有相同数量的表达式，并且每个查询选择表达式应具有相同的数据类型，或是可以自动将它们转换为相同的数据类型。在自动转换时，对于数值类型，系统将低精度的数据类型转换为高精度的数据类型。

3、在包括多个查询的UNION语句中，其执行顺序是自左至右，使用括号可以改变这一执行顺序。例如:查询1 UNION (查询2 UNION 查询3)。

第四题怎么做??mysql 怎么用多表查询where来做??

一使用SELECT子句进行多表查询
SELECT 字段名 FROM 表1，表2 … WHERE 表1.字段 = 表2.字段 AND 其它查询条件
SELECT a.id,a.name,a.address,a.date,b.math,b.english,b.chinese FROM tb_demo065_tel AS b,tb_demo065 AS a WHERE a.id=b.id
注:在上面的的代码中，以两张表的id字段信息相同作为条件建立两表关联，但在实际开发中不应该这样使用，最好用主外键约束来实现
二使用表的别名进行多表查询
如:SELECT a.id,a.name,a.address,b.math,b.english,b.chinese FROM tb_demo065 a,tb_demo065_tel b WHERE a.id=b.id AND b.id='$_POST[textid]'
SQL语言中，可以通过两种方式为表指定别名
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权*，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

第四题怎么做??mysql 怎么用多表查询where来做??

mysql复杂查询--多表查询

多表查询是指基于两个和两个以上的表或是视图的查询.在实际应用中,查询单个表可能不能满足你的需求,（如显示sales部门位置和其员工的姓名),这种情况下需要使用到(dept表和emp表)

自连接是指在同一张表的连接查询

select * from emp where empno=(select mgr from emp where ename='ford');

子查询是指嵌入在其它sql查询语句中的select语句,也叫嵌套查询

单行子查询是指只返回一行数据的子查询语句

select * from emp where deptno=(select deptno from emp where ename='smith');

多行子查询指返回多行数据的子查询使用关键字 in

如果我们的一个子查询，返回的结果是多列，就叫做列子查询

合并多个select语句的结果，可以使用集合操作符 union,union all

mysql如何查看外键

查看mysql外键方式主要是通过第三方工具或者是sql语句，主要有以下三种方式

1、使用Navicateformysql，打开数据库、查看数据库表、查看设计表、选择外键选项卡，就可以查看外键

2、使用sql语句

showcreatetable表名；这个命令可以查看表的所有信息，包括一些字段类型，字段的约束，外键，主键，索引，字符编码等等。

3、查看某个表或者某个列的外键信息

selectTABLE＿NAME，COLUMN＿NAME，CONSTRAINT＿NAME，

REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = '<table>';

如果需要查看某一列上的外键关系，需要添加列的条件REFERENCED＿COLUMN＿NAME．xx＝xx

方法一比较直观，方法三比较准确！

扩展资料：

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），MySQL数据库系统使用最常用的数据库管理语言－－结构化查询语言（SQL）进行数据库管理。

由于MySQL是开放源代码的，因此任何人都可以在GeneralPublicLicense的许可下下载并根据个性化的需要对其进行修改。MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。

参考资料来源：百度百科——mySQL