分享我收藏的SQL知识以及SQL语句简单实践通俗易懂
小引
首先说明,这个笔者2年前学习SQL的遗漏下来的笔记,由于参加完腾讯的笔试,内容比较偏向数据机构和编译以及数据库,刚好要换台本本,心里不想把它弄死在硬盘里,觉得蛮好的,所以把它都分享了,主要第一部分简单介绍SQL语言,第二部分简单实践,纯属于简单类似的记录,通俗易懂,希望对学习数据库原理的同学有一定的帮助!
主要内容:
- 综述
- 简单实践
综述
大家都知道SQL是结构化查询语言,是关系数据库的标准语言,是一个综合的,功能极强的同时又简洁易学的,它集级数据查询(Data Quest),数据操纵(Data Manipulation),数据定义(Data Definition),数据控制(Data Control)于一体 即:
SQL语言包含4个部分:
※ 数据定义语言(DDL),例如:CREATE、DROP、ALTER等语句
※ 数据操作语言(DML),例如:INSERT(插入)、UPDATE(修改)、DELETE(删除)语句
※ 数据查询语言(DQL),例如:SELECT语句
※ 数据控制语言(DCL),例如:GRANT、REVOKE、COMMIT、ROLLBACK等语句
SQL语言包括三种主要程序设计语言类别的语句:数据定义语言(DDL),数据操作语言(DML)及数据控制语言(DCL)
现在说说SQL的几个基本概念:
基本表:本身独立存在的一个表,一个关系就是对应一个基本表
内模式:存储文件的逻辑结构组成了关系数据库的内模式
视图:从一个或几个基本表导出的表,他是一个虚表
简单实践
建一个数据库,包含“学生表”,“教员表”,“任课表”,“专业表”,“课程表”,“学生个人情况表”,“成绩表”七张表
下面几个表是我们这次实践要用到的表: 用SQL语句建表:
建立一个数据库cc
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create database cc; 2 use cc;
创建一个名为tb_student的学生表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create table tb_student( 2 stunum char(7) primary key, --学号 3 stuname char(8) not null, --姓名 4 stusex char(2) check(stusex in('女','男')), --性别 5 stubirthday smalldatetime not null, --学生出生日期 6 stuspec char(6)not null, --专业 7 stuscore numeric(4,1), --入学成绩 8 stuloan char(2) check(stuloan in ('是','否'))not null, --是否贷款 9 )
创建一个名为tb_teacher的教师表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create table tb_teacher( 2 3 教师代号 char(7) primary key, --教师代号 4 姓名 char(8) not null, --教师名字 5 性别char(2) check(teasex in('女','男')), --教师性别 6 出生年月 datetime not null, --教师出生日期 7 职称 char(6), --职称 8 )
创建一个名为tb_renke的任课表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create table tb_renke( 2 课程代号 char(5), --课程代号 3 教师代号 char(7), -- 教师代号 4 )
创建一个名为tb_major的专业表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create tabletb_major( 2 专业名称 char(8) primary key, --专业名称 3 负责人 char(8) not null, --负责人 4 )
创建一个名为tb_course的课程表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create table course( 2 课程代号 char(5)primary key, --课程代号 3 课程名 char(14) not null, --课程名 4 周学时 int, --周学时 5 学分 int, --学分 6 )
创建一个名为tb_studentinfo的学生个人情况表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create table tb_studentinfo( 2 学号 char(7), --学号 3 身份证 char(15) unique, --身份证号码 4 籍贯 char(8), --籍贯 5 家庭住址 text, --家庭住址 6 电话 char(30), --电话 7 特长 text, --特长 8 奖励 text, --奖励 9 处分 char(100), --处分 10 )
创建一个名为tb_grade的成绩表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create table tb_grade( 2 学号 char(7), --学号 3 课程代号 char(5), --课程代号 4 平时 numeric(3,1), --平时 5 期中 numeric(3,1), --期中 6 期末 numeric(3,1), --期末 7 )
现在向每一张表插入记录
给学生表tb_student插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 insert into tb_student values('9607039','邓盈莹','女','1978-6-6','外贸',666.6,'是'); 2 insert into tb_student values('9907002','倪雯娴','女','1981-4-1','外贸',641.4,'是'); 3 insert into tb_student values('9801055','赵东','男','1979-11-9','中文',450,'否'); 4 insert into tb_student values('9902006','和音','女','1982-6-19','数学',487.1,'否'); 5 insert into tb_student values('9704001','克敏敏','女','1978-7-22','物理',463,'否'); 6 insert into tb_student values('9603001','申强','男','1978-1-15','新闻',512,'是'); 7 insert into tb_student values('9606005','迟大为','男','1976-9-3','化学',491.3,'否'); 8 insert into tb_studentvalues('9803011','欧阳小娟','女','1981-8-11','新闻',526.5,'否'); 9 insert into tb_student values('9908088','毛杰','男','1982-1-1','计算机',622.2,'否'); 10 insert into tb_student values('9608066','康红','女','1979-9-7','计算机',596.8,'是'); 11 insert into tb_student values('9805026','夏天','男','1980-5-7','历史',426.7,'否'); 12 insert into tb_student values('9702033','李力','男','1979-7-7','数学',463.9,'否');
给教师表tb_teacher插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 insert into tb_teacher values('20222','于朵','女','1962-6-19','副教授'); 2 insert into tb_teacher values('20406','张建','女','1946-7-16','教授'); 3 insert into tb_teacher values('10429','蒋成功','男','1959-3-12','副教授'); 4 insert into tb_teacher values('10616','万年','男','1945-9-1','教授'); 5 insert into tb_teacher values('20626','孙乐','女','1971-12-15','讲师'); 6 insert into tb_teacher values('10803','李铁','男','1958-9-22','副教授'); 7 insert into tb_teacher values('10812','米粟','男','1960-1-3','副教授'); 8 insert into tb_teacher values('11015','柴淮','男','1973-8-26','讲师'); 9 insert into tb_teacher values('11107','方华','女','197**-6','讲师'); 10 insert into tb_teacher values('20836','张静','女','1974-11-15','讲师'); 11 insert into tb_teacher values('10101','高树声','男','1940-12-5','教授'); 12 insert into tb_teacher values('10312','巩文','男','1959-3-17','副教授'); 13 insert into tb_teacher values('20506','吴燕','女','1947-10-6','教授'); 14 insert into tb_teacher values('20701','沈菲菲','女','1960-6-18','副教授'); 15 insert into tb_teacher values('10202','梁龙林','男','1948-6-18','教授'); 16 insert into tb_teacher values('10428','李阳','男','1955-8-12','教授'); 17 insert into tb_teacher values('10621','鲁师','男','1943-11-18','教授'); 18 insert into tb_teachervalues('10809','邓为民','男','1957-1-26','副教授'); 19 insert into tb_teacher values('20106','姜晓红','女','1961-6-5','副教授'); 20 insert into tb_teacher values('10131','付林','男','1968-9-11','讲师'); 21 insert into tb_teacher values('10802','杨亮红','男','1941-5-23','教授'); 22 insert into tb_teacher values('10223','周毅','男','1970-3-8','讲师'); 23 insert into tb_teacher values('20255','孙莉莉','女','1975-9-12','讲师'); 24 insert into tb_teacher values('20705','夏雪','女','1969-10-28','讲师'); 25 insert into tb_teacher values('10712','南方','男','1975-9-13','讲师'); 26 insert into tb_teacher values('10201','代顺达','男','1940-12-17','讲师'); 27 insert into tb_teacher values('20301','高珊','女','1965-6-19','副教授'); 28 insert into tb_teacher values('20319','林妮','女','1973-4-1','讲师'); 29 insert into tb_teacher values('21025','张旗','女','1972-6-6','讲师'); 30 insert into tb_teacher values('11117','韩明','男','1976-2-14','助教'); 31 insert into tb_teacher values('10503','孙建国','男','1949-10-1','教授'); 32 insert into tb_teacher values('10509','黄宁','男','1956-12-23','副教授');
给任课表tb_renke插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 insert into tb_renke values('21003','21025'); 2 insert into tb_renke values('30211','20255'); 3 insert into tb_renke values('30232','10201'); 4 insert into tb_renke values('40711','10712'); 5 insert into tb_renke values('40722','20701'); 6 insert into tb_renke values('10101','20106'); 7 insert into tb_renke values('11101','11107'); 8 insert into tb_renke values('20511','10509'); 9 insert into tb_renke values('10101','10131'); 10 insert intotb_renke values('20534','10503'); 11 insert into tb_renke values('10712','20705'); 12 insert into tb_renke values('20115','20106'); 13 insert into tb_renke values('10222','10223'); 14 insert into tb_renke values('30412','10429'); 15 insert into tb_renke values('40316','20319'); 16 insert into tb_renke values('40612','20626'); 17 insert into tb_renke values('20328','20301'); 18 insert into tb_renke values('10812','10429'); 19 insert into tb_renke values('20801','10803'); 20 insert into tb_renke values('30802','10812'); 21 insert into tb_renke values('11001','11015'); 22 insert into tb_renke values('20113','10131'); 23 insert into tb_renke values('30416','10428'); 24 insert into tb_renke values('20327','10312'); 25 insert into tb_renke values('20521','20506'); 26 insert into tb_renke values('30213','10201'); 27 insert into tb_renke values('11101','11117'); 28 insert into tb_renke values('10715','20222'); 29 insert intotb_renke values('20111','10101'); 30 insert into tb_renke values('10218','10202'); 31 insert intotb_renke values('30423','20406'); 32 insert into tb_renke values('40331','20319'); 33 insert intotb_renke values('40625','10616'); 34 insert into tb_renke values('20314','20301'); 35 insert into tb_renke values('10811','20836'); 36 insert into tb_renke values('30819','10802');
给专业表tb_major插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 insert into tb_major values('化学','鲁师'); 2 insert into tb_major values('计算机','邓为民'); 3 insert into tb_major values('软件','李明'); 4 insert into tb_major values('外贸','沈菲菲'); 5 insert into tb_major values('数学','梁龙林'); 6 insert into tb_major values('物理','李阳'); 7 insert intotb_major values('物理学','王国玉'); 8 insert into tb_major values('历史','吴燕'); 9 insert into tb_major values('中文','高树声'); 10 insert into tb_major values('新闻','巩文');
给课程表tb_course插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 insert into tb_course values('20511','世界近代史',4,4); 2 insert into tb_course values('10101','大学语文',2,2); 3 insert into tb_course values('20801','计算机基础(一)',4,3); 4 insert into tb_course values('10218','高等代数',4,4); 5 insert into tb_course values('11001','英语(一)',6,6); 6 insert into tb_course values('20113','外国文学',4,4); 7 insert into tb_course values('30416','接口技术',4,3); 8 insert into tb_course values('20327','报刊编辑学',2,2); 9 insert into tb_course values('20521','中国民族史',3,2); 10 insert into tb_course values('30213','数论',4,4); 11 insert into tb_course values('11101','体育',2,2); 12 insert into tb_course values('21003','英语(二)',4,4); 13 insert into tb_course values('10715','高等数学',4,4); 14 insert into tb_course values('20111','古代汉语',3,3); 15 insert into tb_course values('30802','计算机基础(二)',3,3); 16 insert into tb_course values('30423','电磁场理论',3,3); 17 insert into tb_course values('40331','传播心理学',2,2); 18 insert into tb_course values('40625','色谱学',2,2); 19 insert into tb_course values('20314','新闻学概论',2,2); 20 insert into tb_course values('10811','离散数学',2,2); 21 insert into tb_course values('30819','编译技术',4,4); 22 insert into tb_course values('20534','中国近代史',4,4); 23 insert into tb_course values('10712','政治经济学',3,3); 24 insert into tb_course values('20115','近代汉语',4,4); 25 insert into tb_course values('30211','概论统计',3,3); 26 insert into tb_course values('30232','数学分析',2,2); 27 insert into tb_course values('40711','国际投资学',2,2); 28 insert into tb_course values('40722','国际商法',2,2); 29 insert into tb_course values('30832','算法设计',4,4); 30 insert into tb_course values('10812','数字电路',4,4); 31 insert into tb_course values('10222','解析几何',2,2); 32 insert into tb_course values('30412','近代物理实验',3,2); 33 insert into tb_course values('40316','当代新闻史',2,2); 34 insert into tb_course values('40612','配位化学',3,3); 35 insert into tb_course values('20328','现代新闻报道',4,4);
给学生个人情况表tb_studentinfo插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 insert into tb_studentinfo values('9607039','530120169021101','安徽','江岸小区栋单元','5033228','唱歌,摔跤','被评为三好学生',''); 2 insert into tb_studentinfo values('9907002','530120170060701','云南','江岸小区栋单元','5033226','跳舞,篮球','被评为三好学生',''); 3 insert into tb_studentinfo values('9801055','530120171072501','湖北','白马小区栋单元','4133224','围棋','',''); 4 insert into tb_studentinfo values('9902006','530120170122901','湖南','金星小区栋单元','3133218','象棋','被评为三好学生',''); 5 insert into tb_studentinfo values('9704001','530120168121101','云南','静园小区栋单元','2133227','排球,足球','',''); 6 insert into tb_studentinfo values('9603001','530120174050101','云南','江岸小区栋单元','5033219','唱歌,跳舞','被评为红花少年',''); 7 insert into tb_studentinfo values('9606005','530120175040702','江苏','江岸小区栋单元','5033123','演讲','',''); 8 insert into tb_studentinfo values('9803011','530120173021201','四川','白马小区栋单元','4133124','集邮','获集邮展三等奖',''); 9 insert into tb_studentinfo values('9908088','530120172092801','四川','阳光小区栋单元','3133177','长跑足球','获省长跑第二名','作弊受处分'); 10 insert into tb_studentinfo values('9608066','530120174092201','云南','阳光小区栋单元','3133222','摄影','获优秀作文奖',''); 11 insert into tb_studentinfo values('9805026','530120174110901','贵州','阳光小区栋单元','3133189','围棋','获数学竞赛一等奖',''); 12 insert into tb_studentinfo values('9702033','530120170080401','黑龙江','阳光小区栋单元','3148212','围棋','获数学竞赛三等奖','');
给成绩表tb_grade插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 insert into tb_grade values('9805026','20801',75,87,82); 2 insert into tb_grade values('9702033','30802',80,89,91); 3 insert into tb_grade values('9907002','11001',91,83,85); 4 insert into tb_grade values('9801055','20113',70,65,55); 5 insert into tb_grade values('9607039','40711',85,80,88); 6 insert into tb_grade values('9907002','10715',83,90,86); 7 insert into tb_grade values('9801055','20111',78,60,65); 8 insert into tb_grade values('9902006','10218',75,63,52); 9 insert into tb_grade values('9902006','11001',78,86,81); 10 insert into tb_grade values('9704001','30416',80,90,90); 11 insert into tb_grade values('9803011','20327',95,93,90); 12 insert into tb_grade values('9908088','11001',90,91,87); 13 insert into tb_grade values('9805026','20521',90,97,96); 14 insert into tb_grade values('9702033','30213',88,69,76); 15 insert into tb_grade values('9907002','11101',88,65,72); 16 insert into tb_grade values('9801055','21003',70,90,84); 17 insert into tb_grade values('9902006','11101',80,70,70); 18 insert into tb_grade values('9803011','21003',78,84,82); 19 insert into tb_grade values('9908088','11101',82,75,78); 20 insert into tb_grade values('9805026','21003',83,85,84); 21 insert into tb_grade values('9805026','20511',90,82,86); 22 insert into tb_grade values('9702033','30232',80,84,83); 23 insert into tb_grade values('9907002','10101',84,96,92); 24 insert into tb_grade values('9801055','20801',76,78,60); 25 insert into tb_grade values('9902006','10101',85,88,81); 26 insert into tb_grade values('9704001','30802',90,87,82); 27 insert into tb_grade values('9803011','20801',60,50,51); 28 insert into tb_grade values('9704001','30423',80,81,85); 29 insert into tb_grade values('9603001','40331',67,72,70); 30 insert into tb_grade values('9606005','40625',83,85,84); 31 insert into tb_grade values('9803011','20314',76,76,76); 32 insert into tb_grade values('9908088','10811',82,92,89); 33 insert into tb_grade values('9608066','30819',78,84,82); 34 insert into tb_grade values('9805026','20534',70,90,84); 35 insert into tb_grade values('9702033','30211',93,89,90); 36 insert into tb_grade values('9607039','40722',90,87,82); 37 insert into tb_grade values('9907002','10712',90,96,97); 38 insert into tb_grade values('9801055','20115',80,82,87); 39 insert into tb_grade values('9902006','10222',85,91,79); 40 insert into tb_grade values('9704001','30412',78,87,90); 41 insert into tb_grade values('9603001','30316',66,71,73); 42 insert into tb_grade values('9606005','40612',70,78,60); 43 insert into tb_grade values('9803011','20328',90,88,87); 44 insert into tb_grade values('9908088','10812',80,67,83); 45 insert into tb_grade values('9608066','30832',95,92,93); 46 insert into tb_grade values('9908088','10101',80,82,87);
下面进行数据查询:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select学号,姓名from tb_student where 出生年月>'1979'; 2 或是: 3 select学号,姓名from tb_student where 出生年月>cast('1978-12-31' as datetime);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select姓名from tb_student where (入学成绩>500 and 性别='女')or(入学成绩>600 and 性别='男') ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select家庭住址,姓名from tb_studentinfo,tb_student where( 家庭住址like'江岸小区%')and (tb_studentinfo.学号=tb_student.学号) ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select学号from tb_student where 学号not in(select 学号from tb_grade,tb_teacher,tb_renke where tb_grade.课程代号=tb_renke.课程代号and tb_renke.教师代号=tb_teacher.教师代号and姓名='姜晓红' );
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select tb_student.姓名,tb_grade.学号,avg(期末) from tb_student,tb_grade where tb_student.学号=tb_grade.学号group by tb_student.姓名,tb_grade.学号;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select tb_student.姓名,tb_grade.学号,课程名from tb_student,tb_grade ,tb_course where tb_grade.学号=tb_student.学号and tb_grade.课程代号=tb_Course.课程代号and姓名='赵东';
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select课程名from tb_course,tb_teacher ,tb_renke where tb_course.课程代号=tb_renke.课程代号and tb_teacher.教师代号=tb_renke.教师代号and职称='教授' 2 或是: 3 select课程名from tb_course where 课程代号in (select 课程代号from tb_renke where 教师代号in(select教师代号from tb_teacher where 职称='教授'));
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select count(*)没贷款的人数from tb_student where 是否货款='false';
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select学号成绩平均在分以上的学号from tb_grade group by 学号having avg(平时+期中+期末)>80;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select学号,姓名from tb_student where 专业in (select 专业from tb_student where 姓名='毛杰'); 2
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select 姓名,课程代号,期末,tb_grade.学号from tb_grade,tb_student where tb_student.学号=tb_grade.学号and 期末>=all(select 期末from tb_grade); 2
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 insert into tb_studentinfo (学号,身份证)values('9607001','530120169021100'); 2 insert into tb_student(学号)values('9607001');
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 update tb_grade set 期末=期末+10 where 期末>(select avg(期末) from tb_grade);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Select学号from tb_grade x Where not exists (select * from tb_grade y Where 教师代号in( Select 教师代号from tb_teacher,tb_renke Where 姓名=’付林’and tb_teacher.教师代号=tb_renke.教师代号) And not exists(select *from tb_grade z Where z.课程代号=y.课程代号and z.学号=x.学号))
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select教师代号,count(*)课程种数from tb_renke group by tb_renke.教师代号;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select 学号,avg(期末)平均分from tb_grade where 期末> 60 group by 学号having count(*)>2 order by avg( 期末)desc;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select学号没贷款的学号from tb_student where 是否货款='false';
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select学号,姓名from tb_student where 出生年月in ( select min(出生年月) from tb_student );
希望这些资料,能够对你有所帮助!
作者:类菌体
出处:http://www.cnblogs.com/bacteroid/
关于作者:群众
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接
如有问题,可以通过2050372586@qq.com 联系我,非常感谢。