sql 高级查询语句总结

最新推荐文章于 2023-03-31 21:23:50 发布

进一步必将千难万险退一步则万劫不复

最新推荐文章于 2023-03-31 21:23:50 发布

阅读量503

点赞数 1

分类专栏： mysql+sqlserver+Orcle 文章标签： ql 高级查询语句总结

mysql+sqlserver+Orcle 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

–这是一个学生成绩管理系统，创建数据库表语句在最后，表包括四张表tblStudent 学生信息表，tblScore 成绩表，tblteacher 教师信息表，tblcourse 课程表
–希望大家学会使用mysql数据库，以后工作中使用的数据库是由公司决定，有可能是mysql数据库也有可能是oracel 或者sqlserver数据库，
–但是所有数据库都是基于sql操作，每个数据库都有每个数据库的方言，学会基本的sql语句，接触新的数据库时候学习一下该数据库对比与
–其他数据库不同之处，这样可以快速上手一个新的数据库，更快的进入开发流程。
–今天希望大家学会使用Navicat for MySQL数据库管理工具，这个东西只是mysql数据库管理工具，使用这个工具的前提是先安装mysql数据库。
–以下题目为我个人总结，如果大家还有其他其他题目可以锻炼大家能力或者可以让大家学习到更多知识，欢迎大家提出，希望大家营造一个良好的班级氛围，
–让大家学习到更多知识，提高大家的能力。
–如果大家有任何不懂，可以在论坛中提问或者私聊我，论坛采用匿名制度，有任何问题都可以问，我会第一时间回复大家。
–本次练习对sql掌握要求很高，希望大家多多练习，大多数地方涉及到四表联查，但是和两表联查原理都是一样。

–1、查询“001”课程比“002”课程成绩高的所有学生的学号；
Select StuId From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=’001’)>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId=’002’);

【执行情况】
mysql> Select StuId From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=’001’)>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId=’002’);
+——-+
| StuId |
+——-+
| 1001 |
| 1009 |
| 1010 |
+——-+
3 rows in set

mysql>

使用自连接实现
mysql> select DISTINCT A.StuId from tblscore A,tblscore B
where A.CourseId=’001’ and B.CourseId=’002’ and A.Score>B.score and A.stuid=B.stuid;
+——-+
| StuId |
+——-+
| 1001 |
| 1009 |
| 1010 |
+——-+
3 rows in set

–2、查询平均成绩大于60分的同学的学号和平均成绩；
Select StuId,Avg(Score) as AvgScore From tblScore
Group By StuId
Having Avg(Score)>60;

【执行情况】
mysql> Select StuId,Avg(Score) as AvgScore From tblScore
Group By StuId
Having Avg(Score)>60;
+——-+——————-+
| StuId | AvgScore |
+——-+——————-+
| 1001 | 89.5 |
| 1002 | 69.83333333333333 |
| 1004 | 75.5 |
| 1006 | 71.8 |
| 1007 | 82 |
| 1008 | 68.4 |
| 1009 | 88.4 |
| 1010 | 91 |
+——-+——————-+
8 rows in set

–3、查询学习过‘001’课程的男生和女生人数各是多少？
mysql> select A.StuSex sex,count(*) as CountSex from tblstudent A,tblscore B
where B.courseid=’001’ and A.stuid=B.stuid group by A.StuSex ;
+—–+———-+
| sex | CountSex |
+—–+———-+
| 女 | 5 |
| 男 | 5 |
+—–+———-+
2 rows in set

–4、查询姓“李”的老师的个数；
Select Count(*) From tblTeacher Where TeaName like ‘李%’;
【执行情况】
mysql> Select Count(*) From tblTeacher Where TeaName like ‘李%’;
+———-+
| Count(*) |
+———-+
| 1 |
+———-+
1 row in set

–5、查询“张无忌”的所有学习课程名称和授课老师姓名；

【执行情况】
mysql> select a.stuid,a.stuname,c.CourseName,d.teaname from tblstudent a,tblscore b,tblcourse
c,tblteacher d where a.stuid=b.stuid and b.courseid=c.courseid
and c.teaid=d.teaid and a.stuname=’张无忌’;

+——-+———+————+———+
| stuid | stuname | CourseName | teaname |
+——-+———+————+———+
| 1000 | 张无忌 | 数据库 | 裘千尺 |
| 1000 | 张无忌 | Flash动漫 | 姚明 |
+——-+———+————+———+
2 rows in set

–6、查询每一门课程的课程名称，授课教师姓名，课程平均成绩；

mysql> select avg(b.score) as AvgScore,c.CourseName,d.teaname from tblstudent a,tblscore b,tblcourse
c,tblteacher d where a.stuid=b.stuid and b.courseid=c.courseid
and c.teaid=d.teaid group by b.courseid ;
+——————-+—————-+———-+
| AvgScore | CourseName | teaname |
+——————-+—————-+———-+
| 67.7 | 企业管理 | 叶平 |
| 79.14285714285714 | 马克思 | 赵志敬 |
| 71.375 | UML | 裘千仞 |
| 43.4 | 数据库 | 裘千尺 |
| 70 | 逻辑电路 | 裘千仞 |
| 56.4 | 英语 | 叶开 |
| 72.5 | 电子电路 | 独孤求败 |
| 92 | 毛泽东思想概论 | 孟星魂 |
| 24.5 | 西方哲学史 | 白展堂 |
| 79.25 | 线性代数 | 乔丹 |
| 53 | 计算机基础 | 吕轻侯 |
| 59.5 | AUTO CAD制图 | 花无缺 |
| 97 | 平面设计 | 佟湘玉 |
| 75 | Flash动漫 | 姚明 |
| 85 | Java开发 | 阿紫 |
| 76.66666666666667 | C#基础 | 叶平 |
+——————-+—————-+———-+
16 rows in set

mysql>

–7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
Select StuId,StuName From tblStudent st
Where (Select Count(*) From tblScore s1 Where s1.StuId=st.StuId And s1.CourseId=’001’)>0
And
(Select Count(*) From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId=’002’)>0;

【执行情况】
mysql> Select StuId,StuName From tblStudent st
Where (Select Count(*) From tblScore s1 Where s1.StuId=st.StuId And s1.CourseId=’001’)>0
And
(Select Count(*) From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId=’002’)>0;
+——-+———+
| StuId | StuName |
+——-+———+
| 1001 | 周芷若 |
| 1002 | 杨过 |
| 1004 | 小龙女 |
| 1009 | 韦小宝 |
| 1010 | 康敏 |
| 1013 | 郭靖 |
+——-+———+
6 rows in set

方法二：
【执行情况】
mysql> SELECT C.stuid,C.stuName from tblStudent C where C.stuid in (SELECT A.stuid from
tblScore A where A.CourseId=’001’) and C.stuid
in(SELECT b.stuid from tblScore b where b.CourseId=’002’);
+——-+———+
| stuid | stuName |
+——-+———+
| 1001 | 周芷若 |
| 1002 | 杨过 |
| 1004 | 小龙女 |
| 1009 | 韦小宝 |
| 1010 | 康敏 |
| 1013 | 郭靖 |
+——-+———+
6 rows in set

–8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

Select StuId,StuName From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=’001’)>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId=’002’);

【执行情况】
mysql> Select StuId,StuName From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=’001’)>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId=’002’);
+——-+———+
| StuId | StuName |
+——-+———+
| 1001 | 周芷若 |
| 1009 | 韦小宝 |
| 1010 | 康敏 |
+——-+———+
3 rows in set

mysql>
–9、查询已经学完所有课程的同学的学号、姓名；
Select StuId,StuName From tblStudent st
Where (Select Count(*) From tblScore sc Where st.StuId=sc.StuId)=
(Select Count(*) From tblCourse)
【执行情况】无结果
mysql> Select StuId,StuName From tblStudent st
Where (Select Count(*) From tblScore sc Where st.StuId=sc.StuId)=
(Select Count(*) From tblCourse);
Empty set
–10、查询课程补考过的学生学号，课程号；[同一门课程成绩存在两次代表补考]
Select AcourseId,Astuid From tblStudent st,
(Select Count(*) as CountNum,courseId as AcourseId,stuid as Astuid From tblScore sc group by sc.courseId,stuid) A where st.stuid=A.Astuid and A.CountNum>1;

mysql> Select AcourseId,Astuid From tblStudent st,
(Select Count(*) as CountNum,courseId as AcourseId,stuid as Astuid From tblScore sc group by sc.courseId,stuid) A where st.stuid=A.Astuid and A.CountNum>1;
+———–+——–+
| AcourseId | Astuid |
+———–+——–+
| 009 | 1011 |
| 016 | 1013 |
+———–+——–+
2 rows in set

mysql>

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

mysql> Select StuId,StuName,
(Select Count(DISTINCT CourseId) From tblScore t1 Where t1.StuId=s1.StuId ) as SelCourses,
(Select Sum(Score) From tblScore t2 Where t2.StuId=s1.StuId ) as SumScore
From tblStudent s1;
+——-+———+————+———-+
| StuId | StuName | SelCourses | SumScore |
+——-+———+————+———-+
| 1000 | 张无忌 | 2 | 91 |
| 1001 | 周芷若 | 4 | 358 |
| 1002 | 杨过 | 6 | 419 |
| 1003 | 赵敏 | 6 | 356 |
| 1004 | 小龙女 | 4 | 302 |
| 1005 | 张三丰 | 1 | 23 |
| 1006 | 令狐冲 | 5 | 359 |
| 1007 | 任盈盈 | 4 | 328 |
| 1008 | 岳灵珊 | 5 | 342 |
| 1009 | 韦小宝 | 5 | 442 |
| 1010 | 康敏 | 3 | 273 |
| 1011 | 萧峰 | 1 | 49 |
| 1012 | 黄蓉 | 1 | 30 |
| 1013 | 郭靖 | 6 | 314 |
| 1014 | 周伯通 | 0 | NULL |
| 1015 | 瑛姑 | 0 | NULL |
| 1016 | 李秋水 | 0 | NULL |
| 1017 | 黄药师 | 0 | NULL |
| 1018 | 李莫愁 | 0 | NULL |
| 1019 | 冯默风 | 0 | NULL |
| 1020 | 王重阳 | 0 | NULL |
| 1021 | 郭襄 | 0 | NULL |
+——-+———+————+———-+
22 rows in set

–12、创建一个查询视图，视图中包括学生学号，学生姓名，授课教师编号，教师姓名，课程编号，课程名称，成绩，查询出视图中的内容
create view tianmin_view as select a.stuid,a.stuname,d.teaid,d.teaname,c.Courseid,c.CourseName,b.score from tblstudent a,tblscore b,tblcourse
c,tblteacher d where a.stuid=b.stuid and b.courseid=c.courseid and c.teaid=d.teaid ;

mysql> select * from tianmin_view;
+——-+———+——-+———-+———-+—————-+——-+
| stuid | stuname | teaid | teaname | Courseid | CourseName | score |
+——-+———+——-+———-+———-+—————-+——-+
| 1001 | 周芷若 | 006 | 裘千仞 | 003 | UML | 90 |
| 1001 | 周芷若 | 008 | 赵志敬 | 002 | 马克思 | 87 |
| 1001 | 周芷若 | 002 | 叶平 | 001 | 企业管理 | 96 |
| 1001 | 周芷若 | 017 | 乔丹 | 010 | 线性代数 | 85 |
| 1002 | 杨过 | 006 | 裘千仞 | 003 | UML | 70 |
| 1002 | 杨过 | 008 | 赵志敬 | 002 | 马克思 | 87 |
| 1002 | 杨过 | 002 | 叶平 | 001 | 企业管理 | 42 |
| 1002 | 杨过 | 017 | 乔丹 | 010 | 线性代数 | 65 |
| 1003 | 赵敏 | 003 | 叶开 | 006 | 英语 | 78 |
| 1003 | 赵敏 | 006 | 裘千仞 | 003 | UML | 70 |
| 1003 | 赵敏 | 006 | 裘千仞 | 005 | 逻辑电路 | 70 |
| 1003 | 赵敏 | 002 | 叶平 | 001 | 企业管理 | 32 |
| 1003 | 赵敏 | 017 | 乔丹 | 010 | 线性代数 | 85 |
| 1003 | 赵敏 | 013 | 吕轻侯 | 011 | 计算机基础 | 21 |
| 1004 | 小龙女 | 005 | 独孤求败 | 007 | 电子电路 | 90 |
| 1004 | 小龙女 | 008 | 赵志敬 | 002 | 马克思 | 87 |
| 1005 | 张三丰 | 002 | 叶平 | 001 | 企业管理 | 23 |
| 1006 | 令狐冲 | 009 | 阿紫 | 015 | Java开发 | 85 |
| 1006 | 令狐冲 | 003 | 叶开 | 006 | 英语 | 46 |
| 1006 | 令狐冲 | 006 | 裘千仞 | 003 | UML | 59 |
| 1006 | 令狐冲 | 007 | 裘千尺 | 004 | 数据库 | 70 |
| 1006 | 令狐冲 | 002 | 叶平 | 001 | 企业管理 | 99 |
| 1007 | 任盈盈 | 013 | 吕轻侯 | 011 | 计算机基础 | 85 |
| 1007 | 任盈盈 | 003 | 叶开 | 006 | 英语 | 84 |
| 1007 | 任盈盈 | 006 | 裘千仞 | 003 | UML | 72 |
| 1007 | 任盈盈 | 008 | 赵志敬 | 002 | 马克思 | 87 |
| 1008 | 岳灵珊 | 002 | 叶平 | 001 | 企业管理 | 94 |
| 1008 | 岳灵珊 | 015 | 花无缺 | 012 | AUTO CAD制图 | 85 |
| 1008 | 岳灵珊 | 003 | 叶开 | 006 | 英语 | 32 |
| 1009 | 韦小宝 | 006 | 裘千仞 | 003 | UML | 90 |
| 1009 | 韦小宝 | 008 | 赵志敬 | 002 | 马克思 | 82 |
| 1009 | 韦小宝 | 002 | 叶平 | 001 | 企业管理 | 96 |
| 1009 | 韦小宝 | 017 | 乔丹 | 010 | 线性代数 | 82 |
| 1009 | 韦小宝 | 004 | 孟星魂 | 008 | 毛泽东思想概论 | 92 |
| 1010 | 康敏 | 006 | 裘千仞 | 003 | UML | 90 |
| 1010 | 康敏 | 008 | 赵志敬 | 002 | 马克思 | 87 |
| 1010 | 康敏 | 002 | 叶平 | 001 | 企业管理 | 96 |
| 1011 | 萧峰 | 012 | 白展堂 | 009 | 西方哲学史 | 24 |
| 1011 | 萧峰 | 012 | 白展堂 | 009 | 西方哲学史 | 25 |
| 1012 | 黄蓉 | 006 | 裘千仞 | 003 | UML | 30 |
| 1013 | 郭靖 | 008 | 赵志敬 | 002 | 马克思 | 37 |
| 1013 | 郭靖 | 002 | 叶平 | 001 | 企业管理 | 16 |
| 1013 | 郭靖 | 005 | 独孤求败 | 007 | 电子电路 | 55 |
| 1013 | 郭靖 | 003 | 叶开 | 006 | 英语 | 42 |
| 1013 | 郭靖 | 015 | 花无缺 | 012 | AUTO CAD制图 | 34 |
| 1000 | 张无忌 | 007 | 裘千尺 | 004 | 数据库 | 16 |
| 1002 | 杨过 | 007 | 裘千尺 | 004 | 数据库 | 55 |
| 1004 | 小龙女 | 007 | 裘千尺 | 004 | 数据库 | 42 |
| 1008 | 岳灵珊 | 007 | 裘千尺 | 004 | 数据库 | 34 |
| 1013 | 郭靖 | 002 | 叶平 | 016 | C#基础 | 86 |
| 1013 | 郭靖 | 002 | 叶平 | 016 | C#基础 | 44 |
| 1000 | 张无忌 | 001 | 姚明 | 014 | Flash动漫 | 75 |
| 1002 | 杨过 | 002 | 叶平 | 016 | C#基础 | 100 |
| 1004 | 小龙女 | 002 | 叶平 | 001 | 企业管理 | 83 |
| 1008 | 岳灵珊 | 011 | 佟湘玉 | 013 | 平面设计 | 97 |
+——-+———+——-+———-+———-+—————-+——-+
55 rows in set

mysql>

–13、查出”周芷若”同学所有未选课程编号和课程名称

mysql> select X.Courseid ‘未选课程编号’,X.CourseName ‘未选课程名称’ from tblcourse X where Courseid not in(
select c.Courseid from tblstudent a,tblscore b,tblcourse
c where a.stuid=b.stuid and b.courseid=c.courseid and a.stuname=’周芷若’);
+————–+—————–+
| 未选课程编号 | 未选课程名称 |
+————–+—————–+
| 004 | 数据库 |
| 005 | 逻辑电路 |
| 006 | 英语 |
| 007 | 电子电路 |
| 008 | 毛泽东思想概论 |
| 009 | 西方哲学史 |
| 011 | 计算机基础 |
| 012 | AUTO CAD制图 |
| 013 | 平面设计 |
| 014 | Flash动漫 |
| 015 | Java开发 |
| 016 | C#基础 |
| 017 | Oracl数据库原理 |
+————–+—————–+
13 rows in set

–14、查出和”周芷若”同学一起上过课的所有同学学号和姓名
实现思路通过名字查出学号，通过学号，查询出学生学习过的课程，
使用where in进行筛选学过这些课程的学生信息，去除重复值，去除‘周芷若’
mysql> select DISTINCT A.stuid,A.stuname from tblstudent A,tblscore B where B.courseid in(
select DISTINCT C.courseid from tblscore C where stuid=(select stuid from tblstudent
where stuname=’周芷若’)) and A.stuname!=’周芷若’;
+——-+———+
| stuid | stuname |
+——-+———+
| 1000 | 张无忌 |
| 1002 | 杨过 |
| 1003 | 赵敏 |
| 1004 | 小龙女 |
| 1005 | 张三丰 |
| 1006 | 令狐冲 |
| 1007 | 任盈盈 |
| 1008 | 岳灵珊 |
| 1009 | 韦小宝 |
| 1010 | 康敏 |
| 1011 | 萧峰 |
| 1012 | 黄蓉 |
| 1013 | 郭靖 |
| 1014 | 周伯通 |
| 1015 | 瑛姑 |
| 1016 | 李秋水 |
| 1017 | 黄药师 |
| 1018 | 李莫愁 |
| 1019 | 冯默风 |
| 1020 | 王重阳 |
| 1021 | 郭襄 |
+——-+———+
21 rows in set

<code class="hljs asciidoc has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">/*
Navicat MySQL Data Transfer

Source Server         : localhost<span class="hljs-emphasis" style="box-sizing: border-box;">_3306
Source Server Version : 50615
Source Host           : localhost:3306
Source Database       : 11_</span>aaa

Target Server Type    : MYSQL
Target Server Version : 50615
File Encoding         : 65001

Date: 2016-08-17 11:27:27
<span class="hljs-strong" style="box-sizing: border-box;">*/

</span>SET FOREIGN<span class="hljs-emphasis" style="box-sizing: border-box;">_KEY_</span>CHECKS=0;

<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>Table structure for <span class="hljs-code" style="box-sizing: border-box;">`tblcourse`</span>
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
DROP TABLE IF EXISTS <span class="hljs-code" style="box-sizing: border-box;">`tblcourse`</span>;
CREATE TABLE <span class="hljs-code" style="box-sizing: border-box;">`tblcourse`</span> (
<span class="hljs-code" style="box-sizing: border-box;">  `CourseId` varchar(3) NOT NULL COMMENT '课程编号',</span>
<span class="hljs-code" style="box-sizing: border-box;">  `CourseName` varchar(20) NOT NULL COMMENT '课程名称',</span>
<span class="hljs-code" style="box-sizing: border-box;">  `TeaId` varchar(3) DEFAULT NULL COMMENT '授课教师编号',</span>
<span class="hljs-code" style="box-sizing: border-box;">  PRIMARY KEY (`CourseId`)</span>
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>Records of tblcourse
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>001', <span class="hljs-emphasis" style="box-sizing: border-box;">'企业管理'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'002'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>002', <span class="hljs-emphasis" style="box-sizing: border-box;">'马克思'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'008'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>003', <span class="hljs-emphasis" style="box-sizing: border-box;">'UML'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'006'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>004', <span class="hljs-emphasis" style="box-sizing: border-box;">'数据库'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'007'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>005', <span class="hljs-emphasis" style="box-sizing: border-box;">'逻辑电路'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'006'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>006', <span class="hljs-emphasis" style="box-sizing: border-box;">'英语'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'003'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>007', <span class="hljs-emphasis" style="box-sizing: border-box;">'电子电路'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'005'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>008', <span class="hljs-emphasis" style="box-sizing: border-box;">'毛泽东思想概论'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'004'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>009', <span class="hljs-emphasis" style="box-sizing: border-box;">'西方哲学史'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'012'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>010', <span class="hljs-emphasis" style="box-sizing: border-box;">'线性代数'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'017'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>011', <span class="hljs-emphasis" style="box-sizing: border-box;">'计算机基础'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'013'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>012', <span class="hljs-emphasis" style="box-sizing: border-box;">'AUTO CAD制图'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'015'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>013', <span class="hljs-emphasis" style="box-sizing: border-box;">'平面设计'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'011'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>014', <span class="hljs-emphasis" style="box-sizing: border-box;">'Flash动漫'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'001'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>015', <span class="hljs-emphasis" style="box-sizing: border-box;">'Java开发'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'009'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>016', <span class="hljs-emphasis" style="box-sizing: border-box;">'C#基础'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'002'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblcourse` VALUES ('</span>017', <span class="hljs-emphasis" style="box-sizing: border-box;">'Oracl数据库原理'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'010'</span>);

<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>Table structure for <span class="hljs-code" style="box-sizing: border-box;">`tblscore`</span>
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
DROP TABLE IF EXISTS <span class="hljs-code" style="box-sizing: border-box;">`tblscore`</span>;
CREATE TABLE <span class="hljs-code" style="box-sizing: border-box;">`tblscore`</span> (
<span class="hljs-code" style="box-sizing: border-box;">  `StuId` varchar(5) DEFAULT NULL COMMENT '学号',</span>
<span class="hljs-code" style="box-sizing: border-box;">  `CourseId` varchar(3) DEFAULT NULL COMMENT '课程编号',</span>
<span class="hljs-code" style="box-sizing: border-box;">  `Score` float DEFAULT NULL COMMENT '成绩'</span>
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>Records of tblscore
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1001', <span class="hljs-emphasis" style="box-sizing: border-box;">'003'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'90'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1001', <span class="hljs-emphasis" style="box-sizing: border-box;">'002'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'87'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1001', <span class="hljs-emphasis" style="box-sizing: border-box;">'001'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'96'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1001', <span class="hljs-emphasis" style="box-sizing: border-box;">'010'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'85'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1002', <span class="hljs-emphasis" style="box-sizing: border-box;">'003'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'70'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1002', <span class="hljs-emphasis" style="box-sizing: border-box;">'002'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'87'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1002', <span class="hljs-emphasis" style="box-sizing: border-box;">'001'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'42'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1002', <span class="hljs-emphasis" style="box-sizing: border-box;">'010'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'65'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1003', <span class="hljs-emphasis" style="box-sizing: border-box;">'006'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'78'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1003', <span class="hljs-emphasis" style="box-sizing: border-box;">'003'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'70'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1003', <span class="hljs-emphasis" style="box-sizing: border-box;">'005'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'70'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1003', <span class="hljs-emphasis" style="box-sizing: border-box;">'001'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'32'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1003', <span class="hljs-emphasis" style="box-sizing: border-box;">'010'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'85'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1003', <span class="hljs-emphasis" style="box-sizing: border-box;">'011'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'21'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1004', <span class="hljs-emphasis" style="box-sizing: border-box;">'007'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'90'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1004', <span class="hljs-emphasis" style="box-sizing: border-box;">'002'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'87'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1005', <span class="hljs-emphasis" style="box-sizing: border-box;">'001'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'23'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1006', <span class="hljs-emphasis" style="box-sizing: border-box;">'015'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'85'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1006', <span class="hljs-emphasis" style="box-sizing: border-box;">'006'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'46'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1006', <span class="hljs-emphasis" style="box-sizing: border-box;">'003'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'59'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1006', <span class="hljs-emphasis" style="box-sizing: border-box;">'004'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'70'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1006', <span class="hljs-emphasis" style="box-sizing: border-box;">'001'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'99'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1007', <span class="hljs-emphasis" style="box-sizing: border-box;">'011'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'85'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1007', <span class="hljs-emphasis" style="box-sizing: border-box;">'006'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'84'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1007', <span class="hljs-emphasis" style="box-sizing: border-box;">'003'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'72'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1007', <span class="hljs-emphasis" style="box-sizing: border-box;">'002'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'87'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1008', <span class="hljs-emphasis" style="box-sizing: border-box;">'001'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'94'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1008', <span class="hljs-emphasis" style="box-sizing: border-box;">'012'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'85'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1008', <span class="hljs-emphasis" style="box-sizing: border-box;">'006'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'32'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1009', <span class="hljs-emphasis" style="box-sizing: border-box;">'003'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'90'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1009', <span class="hljs-emphasis" style="box-sizing: border-box;">'002'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'82'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1009', <span class="hljs-emphasis" style="box-sizing: border-box;">'001'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'96'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1009', <span class="hljs-emphasis" style="box-sizing: border-box;">'010'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'82'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1009', <span class="hljs-emphasis" style="box-sizing: border-box;">'008'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'92'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1010', <span class="hljs-emphasis" style="box-sizing: border-box;">'003'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'90'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1010', <span class="hljs-emphasis" style="box-sizing: border-box;">'002'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'87'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1010', <span class="hljs-emphasis" style="box-sizing: border-box;">'001'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'96'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1011', <span class="hljs-emphasis" style="box-sizing: border-box;">'009'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'24'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1011', <span class="hljs-emphasis" style="box-sizing: border-box;">'009'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'25'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1012', <span class="hljs-emphasis" style="box-sizing: border-box;">'003'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'30'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1013', <span class="hljs-emphasis" style="box-sizing: border-box;">'002'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'37'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1013', <span class="hljs-emphasis" style="box-sizing: border-box;">'001'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'16'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1013', <span class="hljs-emphasis" style="box-sizing: border-box;">'007'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'55'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1013', <span class="hljs-emphasis" style="box-sizing: border-box;">'006'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'42'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1013', <span class="hljs-emphasis" style="box-sizing: border-box;">'012'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'34'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1000', <span class="hljs-emphasis" style="box-sizing: border-box;">'004'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'16'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1002', <span class="hljs-emphasis" style="box-sizing: border-box;">'004'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'55'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1004', <span class="hljs-emphasis" style="box-sizing: border-box;">'004'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'42'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1008', <span class="hljs-emphasis" style="box-sizing: border-box;">'004'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'34'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1013', <span class="hljs-emphasis" style="box-sizing: border-box;">'016'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'86'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1013', <span class="hljs-emphasis" style="box-sizing: border-box;">'016'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'44'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1000', <span class="hljs-emphasis" style="box-sizing: border-box;">'014'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'75'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1002', <span class="hljs-emphasis" style="box-sizing: border-box;">'016'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'100'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1004', <span class="hljs-emphasis" style="box-sizing: border-box;">'001'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'83'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblscore` VALUES ('</span>1008', <span class="hljs-emphasis" style="box-sizing: border-box;">'013'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'97'</span>);

<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>Table structure for <span class="hljs-code" style="box-sizing: border-box;">`tblstudent`</span>
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
DROP TABLE IF EXISTS <span class="hljs-code" style="box-sizing: border-box;">`tblstudent`</span>;
CREATE TABLE <span class="hljs-code" style="box-sizing: border-box;">`tblstudent`</span> (
<span class="hljs-code" style="box-sizing: border-box;">  `StuId` varchar(5) NOT NULL COMMENT '学号',</span>
<span class="hljs-code" style="box-sizing: border-box;">  `StuName` varchar(10) NOT NULL COMMENT '学生姓名',</span>
<span class="hljs-code" style="box-sizing: border-box;">  `StuAge` int(11) DEFAULT NULL COMMENT '学生年龄',</span>
<span class="hljs-code" style="box-sizing: border-box;">  `StuSex` char(1) NOT NULL COMMENT '学生性别',</span>
<span class="hljs-code" style="box-sizing: border-box;">  PRIMARY KEY (`StuId`)</span>
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>Records of tblstudent
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1000', <span class="hljs-emphasis" style="box-sizing: border-box;">'张无忌'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'18'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'男'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1001', <span class="hljs-emphasis" style="box-sizing: border-box;">'周芷若'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'19'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'女'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1002', <span class="hljs-emphasis" style="box-sizing: border-box;">'杨过'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'19'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'男'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1003', <span class="hljs-emphasis" style="box-sizing: border-box;">'赵敏'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'18'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'女'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1004', <span class="hljs-emphasis" style="box-sizing: border-box;">'小龙女'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'17'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'女'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1005', <span class="hljs-emphasis" style="box-sizing: border-box;">'张三丰'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'18'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'男'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1006', <span class="hljs-emphasis" style="box-sizing: border-box;">'令狐冲'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'19'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'男'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1007', <span class="hljs-emphasis" style="box-sizing: border-box;">'任盈盈'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'20'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'女'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1008', <span class="hljs-emphasis" style="box-sizing: border-box;">'岳灵珊'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'19'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'女'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1009', <span class="hljs-emphasis" style="box-sizing: border-box;">'韦小宝'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'18'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'男'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1010', <span class="hljs-emphasis" style="box-sizing: border-box;">'康敏'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'17'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'女'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1011', <span class="hljs-emphasis" style="box-sizing: border-box;">'萧峰'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'19'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'男'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1012', <span class="hljs-emphasis" style="box-sizing: border-box;">'黄蓉'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'18'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'女'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1013', <span class="hljs-emphasis" style="box-sizing: border-box;">'郭靖'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'19'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'男'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1014', <span class="hljs-emphasis" style="box-sizing: border-box;">'周伯通'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'19'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'男'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1015', <span class="hljs-emphasis" style="box-sizing: border-box;">'瑛姑'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'20'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'女'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1016', <span class="hljs-emphasis" style="box-sizing: border-box;">'李秋水'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'21'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'女'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1017', <span class="hljs-emphasis" style="box-sizing: border-box;">'黄药师'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'18'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'男'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1018', <span class="hljs-emphasis" style="box-sizing: border-box;">'李莫愁'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'18'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'女'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1019', <span class="hljs-emphasis" style="box-sizing: border-box;">'冯默风'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'17'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'男'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1020', <span class="hljs-emphasis" style="box-sizing: border-box;">'王重阳'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'17'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'男'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblstudent` VALUES ('</span>1021', <span class="hljs-emphasis" style="box-sizing: border-box;">'郭襄'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'18'</span>, <span class="hljs-emphasis" style="box-sizing: border-box;">'女'</span>);

<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>Table structure for <span class="hljs-code" style="box-sizing: border-box;">`tblteacher`</span>
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
DROP TABLE IF EXISTS <span class="hljs-code" style="box-sizing: border-box;">`tblteacher`</span>;
CREATE TABLE <span class="hljs-code" style="box-sizing: border-box;">`tblteacher`</span> (
<span class="hljs-code" style="box-sizing: border-box;">  `TeaId` varchar(3) NOT NULL COMMENT '教师编号',</span>
<span class="hljs-code" style="box-sizing: border-box;">  `TeaName` varchar(10) NOT NULL COMMENT '教师名称',</span>
<span class="hljs-code" style="box-sizing: border-box;">  PRIMARY KEY (`TeaId`)</span>
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>Records of tblteacher
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>001', <span class="hljs-emphasis" style="box-sizing: border-box;">'姚明'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>002', <span class="hljs-emphasis" style="box-sizing: border-box;">'叶平'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>003', <span class="hljs-emphasis" style="box-sizing: border-box;">'叶开'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>004', <span class="hljs-emphasis" style="box-sizing: border-box;">'孟星魂'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>005', <span class="hljs-emphasis" style="box-sizing: border-box;">'独孤求败'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>006', <span class="hljs-emphasis" style="box-sizing: border-box;">'裘千仞'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>007', <span class="hljs-emphasis" style="box-sizing: border-box;">'裘千尺'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>008', <span class="hljs-emphasis" style="box-sizing: border-box;">'赵志敬'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>009', <span class="hljs-emphasis" style="box-sizing: border-box;">'阿紫'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>010', <span class="hljs-emphasis" style="box-sizing: border-box;">'郭芙蓉'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>011', <span class="hljs-emphasis" style="box-sizing: border-box;">'佟湘玉'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>012', <span class="hljs-emphasis" style="box-sizing: border-box;">'白展堂'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>013', <span class="hljs-emphasis" style="box-sizing: border-box;">'吕轻侯'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>014', <span class="hljs-emphasis" style="box-sizing: border-box;">'李大嘴'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>015', <span class="hljs-emphasis" style="box-sizing: border-box;">'花无缺'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>016', <span class="hljs-emphasis" style="box-sizing: border-box;">'金不换'</span>);
INSERT INTO <span class="hljs-smartquote" style="box-sizing: border-box;">`tblteacher` VALUES ('</span>017', <span class="hljs-emphasis" style="box-sizing: border-box;">'乔丹'</span>);

<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>View structure for <span class="hljs-code" style="box-sizing: border-box;">`tianmin_view`</span>
<span class="hljs-bullet" style="box-sizing: border-box;">-- </span>----------------------------
DROP VIEW IF EXISTS <span class="hljs-code" style="box-sizing: border-box;">`tianmin_view`</span>;
CREATE ALGORITHM=UNDEFINED DEFINER=<span class="hljs-code" style="box-sizing: border-box;">`root`</span>@<span class="hljs-code" style="box-sizing: border-box;">`localhost`</span> SQL SECURITY DEFINER VIEW <span class="hljs-code" style="box-sizing: border-box;">`tianmin_view`</span> AS select <span class="hljs-code" style="box-sizing: border-box;">`a`</span>.<span class="hljs-code" style="box-sizing: border-box;">`StuId`</span> AS <span class="hljs-code" style="box-sizing: border-box;">`stuid`</span>,<span class="hljs-code" style="box-sizing: border-box;">`a`</span>.<span class="hljs-code" style="box-sizing: border-box;">`StuName`</span> AS <span class="hljs-code" style="box-sizing: border-box;">`stuname`</span>,<span class="hljs-code" style="box-sizing: border-box;">`d`</span>.<span class="hljs-code" style="box-sizing: border-box;">`TeaId`</span> AS <span class="hljs-code" style="box-sizing: border-box;">`teaid`</span>,<span class="hljs-code" style="box-sizing: border-box;">`d`</span>.<span class="hljs-code" style="box-sizing: border-box;">`TeaName`</span> AS <span class="hljs-code" style="box-sizing: border-box;">`teaname`</span>,<span class="hljs-code" style="box-sizing: border-box;">`c`</span>.<span class="hljs-code" style="box-sizing: border-box;">`CourseId`</span> AS <span class="hljs-code" style="box-sizing: border-box;">`Courseid`</span>,<span class="hljs-code" style="box-sizing: border-box;">`c`</span>.<span class="hljs-code" style="box-sizing: border-box;">`CourseName`</span> AS <span class="hljs-code" style="box-sizing: border-box;">`CourseName`</span>,<span class="hljs-code" style="box-sizing: border-box;">`b`</span>.<span class="hljs-code" style="box-sizing: border-box;">`Score`</span> AS <span class="hljs-code" style="box-sizing: border-box;">`score`</span> from (((<span class="hljs-code" style="box-sizing: border-box;">`tblstudent`</span> <span class="hljs-code" style="box-sizing: border-box;">`a`</span> join <span class="hljs-code" style="box-sizing: border-box;">`tblscore`</span> <span class="hljs-code" style="box-sizing: border-box;">`b`</span>) join <span class="hljs-code" style="box-sizing: border-box;">`tblcourse`</span> <span class="hljs-code" style="box-sizing: border-box;">`c`</span>) join <span class="hljs-code" style="box-sizing: border-box;">`tblteacher`</span> <span class="hljs-code" style="box-sizing: border-box;">`d`</span>) where ((<span class="hljs-code" style="box-sizing: border-box;">`a`</span>.<span class="hljs-code" style="box-sizing: border-box;">`StuId`</span> = <span class="hljs-code" style="box-sizing: border-box;">`b`</span>.<span class="hljs-code" style="box-sizing: border-box;">`StuId`</span>) and (<span class="hljs-code" style="box-sizing: border-box;">`b`</span>.<span class="hljs-code" style="box-sizing: border-box;">`CourseId`</span> = <span class="hljs-code" style="box-sizing: border-box;">`c`</span>.<span class="hljs-code" style="box-sizing: border-box;">`CourseId`</span>) and (<span class="hljs-code" style="box-sizing: border-box;">`c`</span>.<span class="hljs-code" style="box-sizing: border-box;">`TeaId`</span> = <span class="hljs-code" style="box-sizing: border-box;">`d`</span>.<span class="hljs-code" style="box-sizing: border-box;">`TeaId`</span>)) ;</code>

进一步必将千难万险退一步则万劫不复

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sql 高级查询语句总结

–这是一个学生成绩管理系统，创建数据库表语句在最后，表包括四张表tblStudent 学生信息表，tblScore 成绩表，tblteacher 教师信息表，tblcourse 课程表 –希望大家学会使用mysql数据库，以后工作中使用的数据库是由公司决定，有可能是mysql数据库也有可能是oracel 或者sqlserver数据库， –但是所有数据库都是基于sql操作，每个数据库都有每
复制链接

扫一扫