首页 > 代码库 > 基于MySql数据库的单表与多表联合查询

基于MySql数据库的单表与多表联合查询

2024-09-11 01:28:34   242人阅读

这里以学生 班级 身份证 以及课程为例

1,启动MySql数据库  开启服务

2.1.0新建一张班级表

备注：CHARSET = UTF8 (指定编码格式为utf8 防止中文乱码)

/*班级表*/CREATE TABLE CLASS_INFO(C_ID INT PRIMARY KEY,CLASS_NAME VARCHAR(20) not NULL)CHARSET = UTF8;

运行效果：

技术分享

新建成功

 

2.1.1依次建好学生表(学生表有一个指向班级表的主键  以便做关联查询)

/*学生表*/CREATE TABLE STUDENTS(STU_ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,STU_NAME VARCHAR(20) NOT NULL,STU_AGE INT NOT NULL,C_ID INT references CLASS_INFO(C_ID))CHARSET = UTF8;

 

 2.1.2身份证表(身份证表有一个外键指向学生表的主键 以便做学生—身份证一对一查询)

/*身份证表*/CREATE TABLE STU_CARD(CARD_ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,CARD_NUM VARCHAR(30)  NOT NULL,STU_ID INT REFERENCES  STUDENTS(STU_ID) )CHARSET = UTF8;/*给STU_CARD列添加一个唯一约束  保证身份证跟学生是一对一的关系*/ALTER TABLE STU_CARD ADD UNIQUE (STU_ID);

 

 2.1.3课程表

/*课程表*/CREATE TABLE COURSE(    COURSE_ID INT PRIMARY KEY NOT NULL,    COURSE_NAME VARCHAR(20) NOT NULL)CHARSET = UTF8;

 

 2.1.4 学生跟课程关联的中间表(多对多需要有一个中间表来维护2张表的关系)

/*关联学生和课程的中间表*/CREATE TABLE COURSE_STU(    STU_ID INT references STUDENTS(STU_ID),    COURSE_ID INT REFERENCES COURSE(COURSE_ID))CHARSET = UTF8;

 

3.分别插入测试数据

3.1.0给班级表插入测试数据

INSERT INTO CLASS_INFO VALUES (1,‘TEAM01‘);INSERT INTO CLASS_INFO VALUES (2,‘TEAM02‘);INSERT INTO CLASS_INFO VALUES (3,‘TEAM03‘);

 

3.1.1给学生表插入测试数据(学生id,学生姓名,年龄,该学生所在的班级)

INSERT INTO STUDENTS VALUES(1,‘张三‘,12,1);INSERT INTO STUDENTS VALUES(2,‘李四‘,15,3);INSERT INTO STUDENTS VALUES(3,‘王五‘,15,2);INSERT INTO STUDENTS VALUES(4,‘赵六‘,16,2);INSERT INTO STUDENTS VALUES(5,‘小二‘,12,3);

 

3.1.2给身份证表插入测试数据(id,身份证号,身份证所关联的学生)

INSERT INTO STU_CARD VALUES(1,‘1234566543‘,2);INSERT INTO STU_CARD VALUES(2,‘4324543565‘,1);INSERT INTO STU_CARD VALUES(3,‘2345654334‘,3);

 

3.1.3给课程表表插入测试数据(id,课程)

INSERT INTO COURSE VALUES(1,‘语文‘);INSERT INTO COURSE VALUES(2,‘数学‘);INSERT INTO COURSE VALUES(3,‘英语‘);

 

3.1.4给学生—学科中间表插入测试数据(第一列指向学生ID , 第二列指向课程ID)

INSERT INTO COURSE_STU VALUES (1,1);INSERT INTO COURSE_STU VALUES (2,2);INSERT INTO COURSE_STU VALUES (2,3);INSERT INTO COURSE_STU VALUES (3,2);

 

4.查出所有所建的表 以及 插入的数据

SELECT * FROM CLASS_INFO;SELECT * from students;SELECT * FROM STU_CARD;SELECT * from COURSE;SELECT * FROM COURSE_STU;

运行效果：

技术分享技术分享

 

 技术分享技术分享

 

 技术分享

 

5.查询

5.1.1查出班级ID为2的所有学生信息

/* 查出班级ID为2的所有学生信息*/SELECT * FROM STUDENTS S LEFT JOIN CLASS_INFO C ON  S.C_ID=C.C_ID WHERE C.C_ID = 2;

 运行效果：

技术分享

 

5.1.2查询学生表的总记录数

/*查询学生表的总记录数*/SELECT COUNT(*) FROM STUDENTS;

 运行效果：

技术分享

 

5.1.3查询班级id为2的总记录数

/*查询班级id为2的总记录数*/SELECT COUNT(*) FROM STUDENTS S LEFT JOIN CLASS_INFO C ON  S.C_ID=C.C_ID WHERE C.C_ID = 2;

运行效果：

技术分享

 

5.1.4查出班级名称为TEAM01的所有学生信息

/* 查出班级名称为TEAM01的学生信息(班级，姓名，年龄)*/SELECT C.CLASS_NAME, S.STU_NAME,S.STU_AGE FROM STUDENTS S LEFT JOIN CLASS_INFO C ON  S.C_ID=C.C_ID WHERE C.CLASS_NAME=‘TEAM01‘;

运行效果：

技术分享

 

5.1.5查出所有人以及所在的班级

/*查出所有人以及所在的班级*/SELECT S.STU_NAME, C.CLASS_NAME FROM STUDENTS S LEFT JOIN CLASS_INFO C ON C.C_ID = S.C_ID;

 运行效果：

技术分享

 

5.1.6查询班级ID为2 的所有学生id 姓名 以及年龄

/*查询班级ID为2 的所有学生id 姓名 以及年龄*/SELECT C.C_ID , C.CLASS_NAME, S.STU_NAME,S.STU_AGE FROM CLASS_INFO C LEFT JOIN STUDENTS S ON C.C_ID = S.C_ID WHERE S.C_ID= 2;

运行效果：

技术分享

 

5.1.7查询班级ID为3 的班级名称 以及学生年龄  降序排列  

/*查询班级ID为3 的班级名称 以及学生年龄  降序排列   升序ASC*/SELECT C.CLASS_NAME, S.STU_NAME,S.STU_AGE FROM CLASS_INFO C LEFT JOIN STUDENTS S ON C.C_ID = S.C_ID WHERE C.C_ID=3 ORDER BY S.STU_AGE DESC;

 运行效果：

技术分享

 

5.1.8查询ID为1 的学生所在的班级(CLASS_INFO) 姓名 年龄(STUDENTS) 以及 身份证号(STU_CARD) 3表联查

/*查询ID为1 的学生所在的班级(CLASS_INFO) 姓名 年龄(STUDENTS) 以及 身份证号(STU_CARD) 3表联查*/SELECT C.CLASS_NAME, S.STU_NAME,S.STU_AGE,CA.CARD_NUM FROM STU_CARD CA LEFT JOIN STUDENTS S ON CA.STU_ID=S.STU_ID LEFT JOIN class_info C ON C.C_ID=S.C_ID WHERE S.STU_ID = 1;

运行效果：

技术分享

 

5.1.9右连接查询每个学生所选的每一门课程  以课程为基准

/*右连接查询每个学生所选的每一门课程  以课程为基准*/SELECT * FROM STUDENTS S RIGHT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID RIGHT  JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID;

运行效果：

技术分享

 

5.2.0右连接查询 学生ID 姓名 以及所选的课程

/*右连接查询 学生ID 姓名 以及所选的课程*/SELECT S.STU_ID, S.STU_NAME,C.COURSE_NAME FROM STUDENTS S RIGHT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID RIGHT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID;

运行效果：

技术分享

 

5.2.1查询所有学生ID 姓名 年龄(STUDENTS) 以及他所选的课程(3表联查)

/*查询所有学生ID 姓名 年龄(STUDENTS) 以及他所选的课程(3表联查)*/SELECT S.STU_ID , S.STU_NAME,S.STU_AGE, C.COURSE_NAME FROM STUDENTS S LEFT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID;

运行效果：

技术分享

 

5.2.2查询学生id为2的学生姓名 以及他所选的课程

/*查询学生id为2的学生姓名 以及他所选的课程*/SELECT S.STU_NAME,C.COURSE_NAME FROM STUDENTS S LEFT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID WHERE S.STU_ID = 2;

运行效果：

技术分享

 

5.2.3查询班级id为2 的学生姓名 所选的课程 以及所在的班级名称

/*查询班级id为2 的学生姓名 所选的课程 以及所在的班级名称*/SELECT  S.STU_NAME, C.COURSE_NAME,CL.CLASS_NAME FROM STUDENTS S LEFT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID LEFT JOIN CLASS_INFO CL ON CL.C_ID=S.C_ID WHERE CL.C_ID=2;

运行效果：

技术分享

 

5.2.4查询所有学生的学生身份证信息，身份证信息，班级信息

/*查询所有学生的学生身份证信息，身份证信息，班级信息*/SELECT * FROM STUDENTS S LEFT JOIN STU_CARD CA ON S.STU_ID = CA.STU_ID RIGHT JOIN CLASS_INFO C ON S.C_ID = C.C_ID;

运行效果：

技术分享

 

5.2.5  4表联查 学生信息，课程信息 班级信息 身份证信息

/*4表联查 学生信息，课程信息 班级信息 身份证信息*/SELECT * FROM STUDENTS S RIGHT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID LEFT JOIN CLASS_INFO CI ON CI.C_ID=S.C_ID LEFT JOIN STU_CARD SC ON SC.STU_ID = S.STU_ID; 

运行效果:

技术分享

 

5.2.6  5张表联查  查出学生姓名 他所在的班级 所选的课程 以及该学生的身份证号

/*5张表联查  查出学生姓名 他所在的班级 所选的课程 以及该学生的身份证号*/SELECT S.STU_NAME,CI.CLASS_NAME,C.COURSE_NAME,SC.CARD_NUM FROM STUDENTS S RIGHT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID LEFT JOIN CLASS_INFO CI ON CI.C_ID=S.C_ID LEFT JOIN STU_CARD SC ON SC.STU_ID = S.STU_ID;

运行效果：

技术分享

 

5.2.7  5张表联查  查出学生ID为2的学生姓名 他所在的班级 所选的课程 以及该学生的身份证号

/*5张表联查  查出学生ID为2的学生姓名 他所在的班级 所选的课程 以及该学生的身份证号*/SELECT S.STU_NAME,CI.CLASS_NAME,C.COURSE_NAME,SC.CARD_NUM FROM STUDENTS S RIGHT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID LEFT JOIN CLASS_INFO CI ON CI.C_ID=S.C_ID LEFT JOIN STU_CARD SC ON SC.STU_ID = S.STU_ID WHERE S.STU_ID=2; 

运行效果： 

 技术分享

 全部建表语句 以及 查询语句如下:

drop TABLE studentsdrop table CLASS_INFOdrop table coursedrop table stu_card;drop table COURSE_STU;/*学生表*/CREATE TABLE STUDENTS(STU_ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,STU_NAME VARCHAR(20) NOT NULL,STU_AGE INT NOT NULL,C_ID INT references CLASS_INFO(C_ID))CHARSET = UTF8;/*班级表*/CREATE TABLE CLASS_INFO(C_ID INT PRIMARY KEY,CLASS_NAME VARCHAR(20) not NULL)CHARSET = UTF8;/*身份证表*/CREATE TABLE STU_CARD(CARD_ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,CARD_NUM VARCHAR(30)  NOT NULL,STU_ID INT REFERENCES  STUDENTS(STU_ID) )CHARSET = UTF8;/*给STU_CARD列添加一个唯一约束  保证身份证跟学生是一对一的关系*/ALTER TABLE STU_CARD ADD UNIQUE (STU_ID);/*课程表*/CREATE TABLE COURSE(    COURSE_ID INT PRIMARY KEY NOT NULL,    COURSE_NAME VARCHAR(20) NOT NULL)CHARSET = UTF8;/*关联学生和课程的中间表*/CREATE TABLE COURSE_STU(    STU_ID INT references STUDENTS(STU_ID),    COURSE_ID INT REFERENCES COURSE(COURSE_ID))CHARSET = UTF8;INSERT INTO CLASS_INFO VALUES (1,‘TEAM01‘);INSERT INTO CLASS_INFO VALUES (2,‘TEAM02‘);INSERT INTO CLASS_INFO VALUES (3,‘TEAM03‘);INSERT INTO STUDENTS VALUES(1,‘张三‘,12,1);INSERT INTO STUDENTS VALUES(2,‘李四‘,15,3);INSERT INTO STUDENTS VALUES(3,‘王五‘,15,2);INSERT INTO STUDENTS VALUES(4,‘赵六‘,16,2);INSERT INTO STUDENTS VALUES(5,‘小二‘,12,3);INSERT INTO STU_CARD VALUES(1,‘1234566543‘,2);INSERT INTO STU_CARD VALUES(2,‘4324543565‘,1);INSERT INTO STU_CARD VALUES(3,‘2345654334‘,3);INSERT INTO STU_CARD VALUES(4,‘4345324534‘,3);INSERT INTO COURSE VALUES(1,‘语文‘);INSERT INTO COURSE VALUES(2,‘数学‘);INSERT INTO COURSE VALUES(3,‘英语‘);INSERT INTO COURSE_STU VALUES (1,1);INSERT INTO COURSE_STU VALUES (2,2);INSERT INTO COURSE_STU VALUES (2,3);INSERT INTO COURSE_STU VALUES (3,2);SELECT * FROM CLASS_INFO;SELECT * from students;SELECT * FROM STU_CARD;SELECT * from COURSE;SELECT * FROM COURSE_STU;DELETE FROM STU_CARD WHERE CARD_ID = 4;DELETE FROM COURSE_STU WHERE COURSE_ID = 3;/* 查出班级ID为2的所有学生信息*/SELECT * FROM STUDENTS S LEFT JOIN CLASS_INFO C ON  S.C_ID=C.C_ID WHERE C.C_ID = 2;/*查询学生表的总记录数*/SELECT COUNT(*) FROM STUDENTS;/*查询班级id为2的总记录数*/SELECT COUNT(*) FROM STUDENTS S LEFT JOIN CLASS_INFO C ON  S.C_ID=C.C_ID WHERE C.C_ID = 2;/* 查出班级名称为TEAM01的所有学生信息*/SELECT C.CLASS_NAME, S.STU_NAME,S.STU_AGE FROM STUDENTS S LEFT JOIN CLASS_INFO C ON  S.C_ID=C.C_ID WHERE C.CLASS_NAME=‘TEAM02‘;/*查出所有人以及所在的班级*/SELECT S.STU_NAME, C.CLASS_NAME FROM STUDENTS S LEFT JOIN CLASS_INFO C ON C.C_ID = S.C_ID;/*查询班级ID为2 的所有学生id 姓名 以及年龄*/SELECT C.C_ID , C.CLASS_NAME, S.STU_NAME,S.STU_AGE FROM CLASS_INFO C LEFT JOIN STUDENTS S ON C.C_ID = S.C_ID WHERE S.C_ID= 2;/*查询班级ID为1 的班级名称 以及学生年龄  降序排列   升序ASC*/SELECT C.CLASS_NAME, S.STU_NAME,S.STU_AGE FROM CLASS_INFO C LEFT JOIN STUDENTS S ON C.C_ID = S.C_ID WHERE C.C_ID=3 ORDER BY S.STU_AGE DESC;/*查询ID为1 的学生所在的班级(CLASS_INFO) 姓名 年龄(STUDENTS) 以及 身份证号(STU_CARD) 3表联查*/SELECT C.CLASS_NAME, S.STU_NAME,S.STU_AGE,CA.CARD_NUM FROM STU_CARD CA LEFT JOIN STUDENTS S ON CA.STU_ID=S.STU_ID LEFT JOIN class_info C ON C.C_ID=S.C_ID WHERE S.STU_ID = 1;/*右连接查询每个学生所选的每一门课程  以课程为基准*/SELECT * FROM STUDENTS S RIGHT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID RIGHT  JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID;/*右连接查询 学生ID 姓名 以及所选的课程*/SELECT S.STU_ID, S.STU_NAME,C.COURSE_NAME FROM STUDENTS S RIGHT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID RIGHT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID;/*查询所有学生ID 姓名 年龄(STUDENTS) 以及他所选的课程(3表联查)*/SELECT S.STU_ID , S.STU_NAME,S.STU_AGE, C.COURSE_NAME FROM STUDENTS S LEFT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID;/*查询学生id为2的学生姓名 以及他所选的课程*/SELECT S.STU_NAME,C.COURSE_NAME FROM STUDENTS S LEFT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID WHERE S.STU_ID = 2;/*查询班级id为2 的学生姓名 所选的课程 以及所在的班级名称*/SELECT  S.STU_NAME, C.COURSE_NAME,CL.CLASS_NAME FROM STUDENTS S LEFT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID LEFT JOIN CLASS_INFO CL ON CL.C_ID=S.C_ID WHERE CL.C_ID=2;/*查询学生ID为2的所有学生信息(学生表+身份证表+班级表)*/SELECT * FROM STUDENTS S LEFT JOIN STU_CARD CA ON S.STU_ID = CA.STU_ID LEFT JOIN CLASS_INFO C ON S.C_ID = C.C_ID WHERE S.STU_ID = 2;/*查询所有学生的学生身份证信息，身份证信息，班级信息*/SELECT * FROM STUDENTS S LEFT JOIN STU_CARD CA ON S.STU_ID = CA.STU_ID RIGHT JOIN CLASS_INFO C ON S.C_ID = C.C_ID;/*查询班级名称为TEAM02的所有学生的学生身份证信息，身份证信息，班级信息*/SELECT * FROM STUDENTS S LEFT  JOIN STU_CARD CA ON S.STU_ID = CA.STU_ID RIGHT JOIN CLASS_INFO C ON S.C_ID = C.C_ID WHERE C.CLASS_NAME=‘TEAM02‘;/*4表联查 学生信息，课程信息 班级信息 身份证信息*/SELECT * FROM STUDENTS S RIGHT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID LEFT JOIN CLASS_INFO CI ON CI.C_ID=S.C_ID LEFT JOIN STU_CARD SC ON SC.STU_ID = S.STU_ID;/*5张表联查  查出学生姓名 他所在的班级 所选的课程 以及该学生的身份证号*/SELECT S.STU_NAME,CI.CLASS_NAME,C.COURSE_NAME,SC.CARD_NUM FROM STUDENTS S RIGHT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID LEFT JOIN CLASS_INFO CI ON CI.C_ID=S.C_ID LEFT JOIN STU_CARD SC ON SC.STU_ID = S.STU_ID;/*5张表联查  查出学生ID为2的学生姓名 他所在的班级 所选的课程 以及该学生的身份证号*/SELECT S.STU_NAME,CI.CLASS_NAME,C.COURSE_NAME,SC.CARD_NUM FROM STUDENTS S RIGHT JOIN COURSE_STU CS ON S.STU_ID = CS.STU_ID LEFT JOIN COURSE C ON C.COURSE_ID = CS.COURSE_ID LEFT JOIN CLASS_INFO CI ON CI.C_ID=S.C_ID LEFT JOIN STU_CARD SC ON SC.STU_ID = S.STU_ID WHERE S.STU_ID=2;

 

 

 

基于MySql数据库的单表与多表联合查询


联系
我们