精华内容
下载资源
问答
  • SQL高级查询语句 有用的 关键时候解决问题的SQL语句 SQL高级查询语句 有用的 关键时候解决问题的SQL语句
  • SQL高级查询语句_聚合查询,多表查询,连接查询【关系数据库SQL教程5】
  • 高级查询在数据库中用得是最频繁的,也是应用最广泛的。 学习sqlserver的朋友可以参考下。
  • SQL高级查询语句实例

    2008-12-25 00:57:25
    SQL高级查询,内联查询,左外联查询,嵌套查询...
  • sql 高级查询语句总结

    万次阅读 2016-08-23 08:20:53
    –希望大家学会使用mysql数据库,以后工作中使用的数据库是由公司决定,有可能是mysql数据库也有可能是oracel 或者sqlserver数据库, –但是所有数据库都是基于sql操作,每个数据库都有每个数据

    数据库建表语句在最后

    –这是一个学生成绩管理系统,创建数据库表语句在最后,表包括四张表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

    /*
    Navicat MySQL Data Transfer
    
    Source Server         : localhost_3306
    Source Server Version : 50615
    Source Host           : localhost:3306
    Source Database       : 11_aaa
    
    Target Server Type    : MYSQL
    Target Server Version : 50615
    File Encoding         : 65001
    
    Date: 2016-08-17 11:27:27
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `tblcourse`
    -- ----------------------------
    DROP TABLE IF EXISTS `tblcourse`;
    CREATE TABLE `tblcourse` (
      `CourseId` varchar(3) NOT NULL COMMENT '课程编号',
      `CourseName` varchar(20) NOT NULL COMMENT '课程名称',
      `TeaId` varchar(3) DEFAULT NULL COMMENT '授课教师编号',
      PRIMARY KEY (`CourseId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of tblcourse
    -- ----------------------------
    INSERT INTO `tblcourse` VALUES ('001', '企业管理', '002');
    INSERT INTO `tblcourse` VALUES ('002', '马克思', '008');
    INSERT INTO `tblcourse` VALUES ('003', 'UML', '006');
    INSERT INTO `tblcourse` VALUES ('004', '数据库', '007');
    INSERT INTO `tblcourse` VALUES ('005', '逻辑电路', '006');
    INSERT INTO `tblcourse` VALUES ('006', '英语', '003');
    INSERT INTO `tblcourse` VALUES ('007', '电子电路', '005');
    INSERT INTO `tblcourse` VALUES ('008', '毛泽东思想概论', '004');
    INSERT INTO `tblcourse` VALUES ('009', '西方哲学史', '012');
    INSERT INTO `tblcourse` VALUES ('010', '线性代数', '017');
    INSERT INTO `tblcourse` VALUES ('011', '计算机基础', '013');
    INSERT INTO `tblcourse` VALUES ('012', 'AUTO CAD制图', '015');
    INSERT INTO `tblcourse` VALUES ('013', '平面设计', '011');
    INSERT INTO `tblcourse` VALUES ('014', 'Flash动漫', '001');
    INSERT INTO `tblcourse` VALUES ('015', 'Java开发', '009');
    INSERT INTO `tblcourse` VALUES ('016', 'C#基础', '002');
    INSERT INTO `tblcourse` VALUES ('017', 'Oracl数据库原理', '010');
    
    -- ----------------------------
    -- Table structure for `tblscore`
    -- ----------------------------
    DROP TABLE IF EXISTS `tblscore`;
    CREATE TABLE `tblscore` (
      `StuId` varchar(5) DEFAULT NULL COMMENT '学号',
      `CourseId` varchar(3) DEFAULT NULL COMMENT '课程编号',
      `Score` float DEFAULT NULL COMMENT '成绩'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of tblscore
    -- ----------------------------
    INSERT INTO `tblscore` VALUES ('1001', '003', '90');
    INSERT INTO `tblscore` VALUES ('1001', '002', '87');
    INSERT INTO `tblscore` VALUES ('1001', '001', '96');
    INSERT INTO `tblscore` VALUES ('1001', '010', '85');
    INSERT INTO `tblscore` VALUES ('1002', '003', '70');
    INSERT INTO `tblscore` VALUES ('1002', '002', '87');
    INSERT INTO `tblscore` VALUES ('1002', '001', '42');
    INSERT INTO `tblscore` VALUES ('1002', '010', '65');
    INSERT INTO `tblscore` VALUES ('1003', '006', '78');
    INSERT INTO `tblscore` VALUES ('1003', '003', '70');
    INSERT INTO `tblscore` VALUES ('1003', '005', '70');
    INSERT INTO `tblscore` VALUES ('1003', '001', '32');
    INSERT INTO `tblscore` VALUES ('1003', '010', '85');
    INSERT INTO `tblscore` VALUES ('1003', '011', '21');
    INSERT INTO `tblscore` VALUES ('1004', '007', '90');
    INSERT INTO `tblscore` VALUES ('1004', '002', '87');
    INSERT INTO `tblscore` VALUES ('1005', '001', '23');
    INSERT INTO `tblscore` VALUES ('1006', '015', '85');
    INSERT INTO `tblscore` VALUES ('1006', '006', '46');
    INSERT INTO `tblscore` VALUES ('1006', '003', '59');
    INSERT INTO `tblscore` VALUES ('1006', '004', '70');
    INSERT INTO `tblscore` VALUES ('1006', '001', '99');
    INSERT INTO `tblscore` VALUES ('1007', '011', '85');
    INSERT INTO `tblscore` VALUES ('1007', '006', '84');
    INSERT INTO `tblscore` VALUES ('1007', '003', '72');
    INSERT INTO `tblscore` VALUES ('1007', '002', '87');
    INSERT INTO `tblscore` VALUES ('1008', '001', '94');
    INSERT INTO `tblscore` VALUES ('1008', '012', '85');
    INSERT INTO `tblscore` VALUES ('1008', '006', '32');
    INSERT INTO `tblscore` VALUES ('1009', '003', '90');
    INSERT INTO `tblscore` VALUES ('1009', '002', '82');
    INSERT INTO `tblscore` VALUES ('1009', '001', '96');
    INSERT INTO `tblscore` VALUES ('1009', '010', '82');
    INSERT INTO `tblscore` VALUES ('1009', '008', '92');
    INSERT INTO `tblscore` VALUES ('1010', '003', '90');
    INSERT INTO `tblscore` VALUES ('1010', '002', '87');
    INSERT INTO `tblscore` VALUES ('1010', '001', '96');
    INSERT INTO `tblscore` VALUES ('1011', '009', '24');
    INSERT INTO `tblscore` VALUES ('1011', '009', '25');
    INSERT INTO `tblscore` VALUES ('1012', '003', '30');
    INSERT INTO `tblscore` VALUES ('1013', '002', '37');
    INSERT INTO `tblscore` VALUES ('1013', '001', '16');
    INSERT INTO `tblscore` VALUES ('1013', '007', '55');
    INSERT INTO `tblscore` VALUES ('1013', '006', '42');
    INSERT INTO `tblscore` VALUES ('1013', '012', '34');
    INSERT INTO `tblscore` VALUES ('1000', '004', '16');
    INSERT INTO `tblscore` VALUES ('1002', '004', '55');
    INSERT INTO `tblscore` VALUES ('1004', '004', '42');
    INSERT INTO `tblscore` VALUES ('1008', '004', '34');
    INSERT INTO `tblscore` VALUES ('1013', '016', '86');
    INSERT INTO `tblscore` VALUES ('1013', '016', '44');
    INSERT INTO `tblscore` VALUES ('1000', '014', '75');
    INSERT INTO `tblscore` VALUES ('1002', '016', '100');
    INSERT INTO `tblscore` VALUES ('1004', '001', '83');
    INSERT INTO `tblscore` VALUES ('1008', '013', '97');
    
    -- ----------------------------
    -- Table structure for `tblstudent`
    -- ----------------------------
    DROP TABLE IF EXISTS `tblstudent`;
    CREATE TABLE `tblstudent` (
      `StuId` varchar(5) NOT NULL COMMENT '学号',
      `StuName` varchar(10) NOT NULL COMMENT '学生姓名',
      `StuAge` int(11) DEFAULT NULL COMMENT '学生年龄',
      `StuSex` char(1) NOT NULL COMMENT '学生性别',
      PRIMARY KEY (`StuId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of tblstudent
    -- ----------------------------
    INSERT INTO `tblstudent` VALUES ('1000', '张无忌', '18', '男');
    INSERT INTO `tblstudent` VALUES ('1001', '周芷若', '19', '女');
    INSERT INTO `tblstudent` VALUES ('1002', '杨过', '19', '男');
    INSERT INTO `tblstudent` VALUES ('1003', '赵敏', '18', '女');
    INSERT INTO `tblstudent` VALUES ('1004', '小龙女', '17', '女');
    INSERT INTO `tblstudent` VALUES ('1005', '张三丰', '18', '男');
    INSERT INTO `tblstudent` VALUES ('1006', '令狐冲', '19', '男');
    INSERT INTO `tblstudent` VALUES ('1007', '任盈盈', '20', '女');
    INSERT INTO `tblstudent` VALUES ('1008', '岳灵珊', '19', '女');
    INSERT INTO `tblstudent` VALUES ('1009', '韦小宝', '18', '男');
    INSERT INTO `tblstudent` VALUES ('1010', '康敏', '17', '女');
    INSERT INTO `tblstudent` VALUES ('1011', '萧峰', '19', '男');
    INSERT INTO `tblstudent` VALUES ('1012', '黄蓉', '18', '女');
    INSERT INTO `tblstudent` VALUES ('1013', '郭靖', '19', '男');
    INSERT INTO `tblstudent` VALUES ('1014', '周伯通', '19', '男');
    INSERT INTO `tblstudent` VALUES ('1015', '瑛姑', '20', '女');
    INSERT INTO `tblstudent` VALUES ('1016', '李秋水', '21', '女');
    INSERT INTO `tblstudent` VALUES ('1017', '黄药师', '18', '男');
    INSERT INTO `tblstudent` VALUES ('1018', '李莫愁', '18', '女');
    INSERT INTO `tblstudent` VALUES ('1019', '冯默风', '17', '男');
    INSERT INTO `tblstudent` VALUES ('1020', '王重阳', '17', '男');
    INSERT INTO `tblstudent` VALUES ('1021', '郭襄', '18', '女');
    
    -- ----------------------------
    -- Table structure for `tblteacher`
    -- ----------------------------
    DROP TABLE IF EXISTS `tblteacher`;
    CREATE TABLE `tblteacher` (
      `TeaId` varchar(3) NOT NULL COMMENT '教师编号',
      `TeaName` varchar(10) NOT NULL COMMENT '教师名称',
      PRIMARY KEY (`TeaId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of tblteacher
    -- ----------------------------
    INSERT INTO `tblteacher` VALUES ('001', '姚明');
    INSERT INTO `tblteacher` VALUES ('002', '叶平');
    INSERT INTO `tblteacher` VALUES ('003', '叶开');
    INSERT INTO `tblteacher` VALUES ('004', '孟星魂');
    INSERT INTO `tblteacher` VALUES ('005', '独孤求败');
    INSERT INTO `tblteacher` VALUES ('006', '裘千仞');
    INSERT INTO `tblteacher` VALUES ('007', '裘千尺');
    INSERT INTO `tblteacher` VALUES ('008', '赵志敬');
    INSERT INTO `tblteacher` VALUES ('009', '阿紫');
    INSERT INTO `tblteacher` VALUES ('010', '郭芙蓉');
    INSERT INTO `tblteacher` VALUES ('011', '佟湘玉');
    INSERT INTO `tblteacher` VALUES ('012', '白展堂');
    INSERT INTO `tblteacher` VALUES ('013', '吕轻侯');
    INSERT INTO `tblteacher` VALUES ('014', '李大嘴');
    INSERT INTO `tblteacher` VALUES ('015', '花无缺');
    INSERT INTO `tblteacher` VALUES ('016', '金不换');
    INSERT INTO `tblteacher` VALUES ('017', '乔丹');
    
    -- ----------------------------
    -- View structure for `tianmin_view`
    -- ----------------------------
    DROP VIEW IF EXISTS `tianmin_view`;
    CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `tianmin_view` AS select `a`.`StuId` AS `stuid`,`a`.`StuName` AS `stuname`,`d`.`TeaId` AS `teaid`,`d`.`TeaName` AS `teaname`,`c`.`CourseId` AS `Courseid`,`c`.`CourseName` AS `CourseName`,`b`.`Score` AS `score` from (((`tblstudent` `a` join `tblscore` `b`) join `tblcourse` `c`) join `tblteacher` `d`) where ((`a`.`StuId` = `b`.`StuId`) and (`b`.`CourseId` = `c`.`CourseId`) and (`c`.`TeaId` = `d`.`TeaId`)) ;
    
    展开全文
  • --28)查询选修了课程名为“数据库”的学生的学号和姓名。 select sname,sno from course,student where course.cno in( select cno from course where cname='sql') and course.cname=student.sno group by sname ...
  • SQL高级查询语句小结(二)

    千次阅读 多人点赞 2018-08-07 09:55:59
    基本常用查询 select select * from student; all 查询所有 select all sex from student; distinct 过滤重复 select distinct sex from student; count 统计 select count(*) from ...

    2018.8.8—–学习笔记;

    基本常用查询

    select

    select * from student;

    all 查询所有

    select all sex from student;

    distinct 过滤重复

    select distinct sex from student;

    count 统计

    select count(*) from student;
    select count(sex) from student;
    select count(distinct sex) from student;

    top 取前N条记录

    select top 3 * from student;

    alias column name 列重命名

    select id as 编号, name ‘名称’, sex 性别 from student;

    alias table name 表重命名

    select id, name, s.id, s.name from student s;

    column 列运算

    select (age + id) col from student;
    select s.name + ‘-’ + c.name from classes c, student s where s.cid = c.id;

    where 条件

    select * from student where id = 2;
    select * from student where id > 7;
    select * from student where id < 3;
    select * from student where id <> 3;
    select * from student where id >= 3;
    select * from student where id <= 5;
    select * from student where id !> 3;
    select * from student where id !< 5;

    and 并且

    select * from student where id > 2 and sex = 1;

    or 或者

    select * from student where id = 2 or sex = 1;

    between … and … 相当于并且

    select * from student where id between 2 and 5;
    select * from student where id not between 2 and 5;

    like 模糊查询

    select * from student where name like ‘%a%’;
    select * from student where name like ‘%[a][o]%’;
    select * from student where name not like ‘%a%’;
    select * from student where name like ‘ja%’;
    select * from student where name not like ‘%[j,n]%’;
    select * from student where name like ‘%[j,n,a]%’;
    select * from student where name like ‘%[^ja,as,on]%’;
    select * from student where name like ‘%[ja_on]%’;

    in 子查询

    select * from student where id in (1, 2);

    not in 不在其中

    select * from student where id not in (1, 2);

    is null 是空

    select * from student where age is null;

    is not null 不为空

    select * from student where age is not null;

    order by 排序

    select * from student order by name;
    select * from student order by name desc;
    select * from student order by name asc;

    group by 分组
    按照年龄进行分组统计

    select count(age), age from student group by age;

    按照性别进行分组统计

    select count(*), sex from student group by sex;

    按照年龄和性别组合分组统计,并排序

    select count(*), sex from student group by sex, age order by age;

    按照性别分组,并且是id大于2的记录最后按照性别排序

    select count(*), sex from student where id > 2 group by sex order by sex;

    查询id大于2的数据,并完成运算后的结果进行分组和排序

    select count(), (sex id) new from student where id > 2 group by sex * id order by sex * id;

    group by all 所有分组
    按照年龄分组,是所有的年龄

    select count(*), age from student group by all age;

    having 分组过滤条件
    按照年龄分组,过滤年龄为空的数据,并且统计分组的条数和现实年龄信息

    select count(*), age from student group by age having age is not null;

    按照年龄和cid组合分组,过滤条件是cid大于1的记录

    select count(*), cid, sex from student group by cid, sex having cid > 1;

    按照年龄分组,过滤条件是分组后的记录条数大于等于2

    select count(*), age from student group by age having count(age) >= 2;

    按照cid和性别组合分组,过滤条件是cid大于1,cid的最大值大于2

    select count(*), cid, sex from student group by cid, sex having cid > 1 and max(cid) > 2;

    二、 嵌套子查询

    子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择,而包含子查询的语句也成为外部查询或外部选择。

    from (select … table)示例

    将一个table的查询结果当做一个新表进行查询

    select * from (
    select id, name from student where sex = 1
    ) t where t.id > 2;

    上面括号中的语句,就是子查询语句(内部查询)。在外面的是外部查询,其中外部查询可以包含以下语句:
    1、 包含常规选择列表组件的常规select查询
    2、 包含一个或多个表或视图名称的常规from语句
    3、 可选的where子句
    4、 可选的group by子句
    5、 可选的having子句

    示例

    查询班级信息,统计班级学生人生

    select , (select count() from student where cid = classes.id) as num
    from classes order by num;

    in, not in子句查询示例

    查询班级id大于小于的这些班级的学生信息

    select * from student where cid in (
    select id from classes where id > 2 and id < 4
    );

    查询不是班的学生信息

    select * from student where cid not in (
    select id from classes where name = ‘2班’
    )

    in、not in 后面的子句返回的结果必须是一列,这一列的结果将会作为查询条件对应前面的条件。如cid对应子句的id;

    exists和not exists子句查询示例

    查询存在班级id为的学生信息

    select * from student where exists (
    select * from classes where id = student.cid and id = 3
    );

    查询没有分配班级的学生信息

    select * from student where not exists (
    select * from classes where id = student.cid
    );

    exists和not exists查询需要内部查询和外部查询进行一个关联的条件,如果没有这个条件将是查询到的所有信息。如:id等于student.id;

    some、any、all子句查询示例

    查询班级的学生年龄大于班级的学生的年龄的信息

    select * from student where cid = 5 and age > all (
    select age from student where cid = 3
    );
    select * from student where cid = 5 and age > any (
    select age from student where cid = 3
    );
    select * from student where cid = 5 and age > some (
    select age from student where cid = 3
    );

    聚合查询

    1、 distinct去掉重复数据

    select distinct sex from student;
    select count(sex), count(distinct sex) from student;

    2、 compute和compute by汇总查询
    对年龄大于的进行汇总

    select age from student
    where age > 20 order by age compute sum(age) by age;

    对年龄大于的按照性别进行分组汇总年龄信息

    select id, sex, age from student
    where age > 20 order by sex, age compute sum(age) by sex;

    按照年龄分组汇总

    select age from student
    where age > 20 order by age, id compute sum(age);

    按照年龄分组,年龄汇总,id找最大值

    select id, age from student
    where age > 20 order by age compute sum(age), max(id);

    compute进行汇总前面是查询的结果,后面一条结果集就是汇总的信息。compute子句中可以添加多个汇总表达式,可以添加的信息如下:
    a、 可选by关键字。它是每一列计算指定的行聚合
    b、 行聚合函数名称。包括sum、avg、min、max、count等
    c、 要对其执行聚合函数的列
    compute by适合做先分组后汇总的业务。compute by后面的列一定要是order by中出现的列。
    3、 cube汇总
    cube汇总和compute效果类似,但语法较简洁,而且返回的是一个结果集。

    select count(*), sex from student group by sex with cube;
    select count(*), age, sum(age) from student where age is not null group by age with cube;

    cube要结合group by语句完成分组汇总

    排序函数

    排序在很多地方需要用到,需要对查询结果进行排序并且给出序号。比如:
    1、 对某张表进行排序,序号需要递增不重复的
    2、 对学生的成绩进行排序,得出名次,名次可以并列,但名次的序号是连续递增的
    3、 在某些排序的情况下,需要跳空序号,虽然是并列
    基本语法
    排序函数 over([分组语句] 排序子句[desc][asc])
    排序子句 order by 列名, 列名
    分组子句 partition by 分组列, 分组列

    row_number函数

    根据排序子句给出递增连续序号
    按照名称排序的顺序递增
    select s.id, s.name, cid, c.name, row_number() over(order by c.name) as number
    from student s, classes c where cid = c.id;

    rank函数函数

    根据排序子句给出递增的序号,但是存在并列并且跳空
    顺序递增

    select id, name, rank() over(order by cid) as rank from student;

    跳过相同递增

    select s.id, s.name, cid, c.name, rank() over(order by c.name) as rank
    from student s, classes c where cid = c.id;

    dense_rank函数

    根据排序子句给出递增的序号,但是存在并列不跳空
    不跳过,直接递增

    select s.id, s.name, cid, c.name, dense_rank() over(order by c.name) as dense
    from student s, classes c where cid = c.id;

    partition by分组子句

    可以完成对分组的数据进行增加排序,partition by可以与以上三个函数联合使用。
    利用partition by按照班级名称分组,学生id排序

    select s.id, s.name, cid, c.name, row_number() over(partition by c.name order by s.id) as rank
    from student s, classes c where cid = c.id;
    select s.id, s.name, cid, c.name, rank() over(partition by c.name order by s.id) as rank
    from student s, classes c where cid = c.id;
    select s.id, s.name, cid, c.name, dense_rank() over(partition by c.name order by s.id) as rank
    from student s, classes c where cid = c.id;

    ntile平均排序函数

    将要排序的数据进行平分,然后按照等分排序。ntile中的参数代表分成多少等分。

    select s.id, s.name, cid, c.name,
    ntile(5) over(order by c.name) as ntile
    from student s, classes c where cid = c.id;

    集合运算

    操作两组查询结果,进行交集、并集、减集运算

    1、 union和union all进行并集运算

    union 并集、不重复

    select id, name from student where name like ‘ja%’
    union
    select id, name from student where id = 4;

    并集、重复

    select * from student where name like ‘ja%’
    union all
    select * from student;

    2、 intersect进行交集运算

    交集(相同部分)

    select * from student where name like ‘ja%’
    intersect
    select * from student;

    3、 except进行减集运算

    减集(除相同部分)

    select * from student where name like ‘ja%’
    except
    select * from student where name like ‘jas%’;

    公式表表达式

    查询表的时候,有时候中间表需要重复使用,这些子查询被重复查询调用,不但效率低,而且可读性低,不利于理解。那么公式表表达式可以解决这个问题。
    我们可以将公式表表达式(CET)视为临时结果集,在select、insert、update、delete或是create view语句的执行范围内进行定义。
    表达式

    with statNum(id, num) as
    (
    select cid, count(*)
    from student
    where id > 0
    group by cid
    )
    select id, num from statNum order by id;
    with statNum(id, num) as
    (
    select cid, count(*)
    from student
    where id > 0
    group by cid
    )
    select max(id), avg(num) from statNum;

    连接查询

    1、 简化连接查询
    –简化联接查询

    select s.id, s.name, c.id, c.name from student s, classes c where s.cid = c.id;

    2、 left join左连接

    左连接

    select s.id, s.name, c.id, c.name from student s left join classes c on s.cid = c.id;

    3、 right join右连接
    右连接

    select s.id, s.name, c.id, c.name from student s right join classes c on s.cid = c.id;

    4、 inner join内连接
    内连接

    select s.id, s.name, c.id, c.name from student s inner join classes c on s.cid = c.id;

    inner可以省略

    select s.id, s.name, c.id, c.name from student s join classes c on s.cid = c.id;

    5、 cross join交叉连接

    交叉联接查询,结果是一个笛卡儿乘积

    s.id, s.name, c.id, c.name from student s cross join classes c
    where s.cid = c.id;

    6、 自连接(同一张表进行连接查询)
    –自连接
    select distinct s.* from student s, student s1 where s.id <> s1.id and s.sex = s1.sex;

    函数

    1、 聚合函数
    max最大值、min最小值、count统计、avg平均值、sum求和、var求方差

    select
    max(age) max_age,
    min(age) min_age,
    count(age) count_age,
    avg(age) avg_age,
    sum(age) sum_age,
    var(age) var_age
    from student;

    2、 日期时间函数

    select dateAdd(day, 3, getDate());–加天
    select dateAdd(year, 3, getDate());–加年
    select dateAdd(hour, 3, getDate());–加小时

    返回跨两个指定日期的日期边界数和时间边界数

    select dateDiff(day, ‘2011-06-20’, getDate());

    相差秒数

    select dateDiff(second, ‘2011-06-22 11:00:00’, getDate());

    相差小时数

    select dateDiff(hour, ‘2011-06-22 10:00:00’, getDate());
    select dateName(month, getDate());–当前月份
    select dateName(minute, getDate());–当前分钟
    select dateName(weekday, getDate());–当前星期
    select datePart(month, getDate());–当前月份
    select datePart(weekday, getDate());–当前星期
    select datePart(second, getDate());–当前秒数
    select day(getDate());–返回当前日期天数
    select day(‘2011-06-30’);–返回当前日期天数
    select month(getDate());–返回当前日期月份
    select month(‘2011-11-10’);
    select year(getDate());–返回当前日期年份
    select year(‘2010-11-10’);
    select getDate();–当前系统日期
    select getUTCDate();–utc日期

    3、 数学函数

    select pi();–PI函数
    select rand(100), rand(50), rand(), rand();–随机数
    select round(rand(), 3), round(rand(100), 5);–精确小数位

    精确位数,负数表示小数点前

    select round(123.456, 2), round(254.124, -2);
    select round(123.4567, 1, 2);

    4、 元数据

    select col_name(object_id(‘student’), 1);–返回列名
    select col_name(object_id(‘student’), 2);

    该列数据类型长度

    select col_length(‘student’, col_name(object_id(‘student’), 2));

    该列数据类型长度

    select col_length(‘student’, col_name(object_id(‘student’), 1));

    返回类型名称、类型id

    select type_name(type_id(‘varchar’)), type_id(‘varchar’);

    返回列类型长度

    select columnProperty(object_id(‘student’), ‘name’, ‘PRECISION’);

    返回列所在索引位置

    select columnProperty(object_id(‘student’), ‘sex’, ‘ColumnId’);

    5、 字符串函数

    select ascii(‘a’);–字符转换ascii值
    select ascii(‘A’);
    select char(97);–ascii值转换字符
    select char(65);
    select nchar(65);
    select nchar(45231);
    select nchar(32993);–unicode转换字符
    select unicode(‘A’), unicode(‘中’);–返回unicode编码值
    select soundex(‘hello’), soundex(‘world’), soundex(‘word’);
    select patindex(‘%a’, ‘ta’), patindex(‘%ac%’, ‘jack’), patindex(‘dex%’, ‘dexjack’);

    –匹配字符索引

    select ‘a’ + space(2) + ‘b’, ‘c’ + space(5) + ‘d’;–输出空格
    select charIndex(‘o’, ‘hello world’);–查找索引
    select charIndex(‘o’, ‘hello world’, 6);–查找索引
    select quoteName(‘abc[]def’), quoteName(‘123]45’);

    –精确数字

    select str(123.456, 2), str(123.456, 3), str(123.456, 4);
    select str(123.456, 9, 2), str(123.456, 9, 3), str(123.456, 6, 1), str(123.456, 9, 6);
    select difference(‘hello’, ‘helloWorld’);–比较字符串相同
    select difference(‘hello’, ‘world’);
    select difference(‘hello’, ‘llo’);
    select difference(‘hello’, ‘hel’);
    select difference(‘hello’, ‘hello’);
    select replace(‘abcedef’, ‘e’, ‘E’);–替换字符串
    select stuff(‘hello world’, 3, 4, ‘ABC’);–指定位置替换字符串
    select replicate(‘abc#’, 3);–重复字符串
    select subString(‘abc’, 1, 1), subString(‘abc’, 1, 2), subString(‘hello Wrold’, 7, 5);

    截取字符串

    select len(‘abc’);–返回长度
    select reverse(‘sqlServer’);–反转字符串
    select left(‘leftString’, 4);–取左边字符串
    select left(‘leftString’, 7);
    select right(‘leftString’, 6);–取右边字符串
    select right(‘leftString’, 3);
    select lower(‘aBc’), lower(‘ABC’);–小写
    select upper(‘aBc’), upper(‘abc’);–大写

    去掉左边空格

    select ltrim(’ abc’), ltrim(‘# abc#’), ltrim(’ abc’);

    去掉右边空格

    select rtrim(’ abc ‘), rtrim(‘# abc# ‘), rtrim(‘abc’);

    6、 安全函数

    select current_user;
    select user;
    select user_id(), user_id(‘dbo’), user_id(‘public’), user_id(‘guest’);
    select user_name(), user_name(1), user_name(0), user_name(2);
    select session_user;
    select suser_id(‘sa’);
    select suser_sid(), suser_sid(‘sa’), suser_sid(‘sysadmin’), suser_sid(‘serveradmin’);
    select is_member(‘dbo’), is_member(‘public’);
    select suser_name(), suser_name(1), suser_name(2), suser_name(3);
    select suser_sname(), suser_sname(0x01), suser_sname(0x02), suser_sname(0x03);
    select is_srvRoleMember(‘sysadmin’), is_srvRoleMember(‘serveradmin’);
    select permissions(object_id(‘student’));
    select system_user;
    select schema_id(), schema_id(‘dbo’), schema_id(‘guest’);
    select schema_name(), schema_name(1), schema_name(2), schema_name(3);

    7、 系统函数

    select app_name();–当前会话的应用程序名称
    select cast(2011 as datetime), cast(‘10’ as money), cast(‘0’ as varbinary);–类型转换
    select convert(datetime, ‘2011’);–类型转换
    select coalesce(null, ‘a’), coalesce(‘123’, ‘a’);–返回其参数中第一个非空表达式
    select collationProperty(‘Traditional_Spanish_CS_AS_KS_WS’, ‘CodePage’);
    select current_timestamp;–当前时间戳
    select current_user;
    select isDate(getDate()), isDate(‘abc’), isNumeric(1), isNumeric(‘a’);
    select dataLength(‘abc’);
    select host_id();
    select host_name();
    select db_name();
    select ident_current(‘student’), ident_current(‘classes’);–返回主键id的最大值
    select ident_incr(‘student’), ident_incr(‘classes’);–id的增量值
    select ident_seed(‘student’), ident_seed(‘classes’);
    select @@identity;–最后一次自增的值
    select identity(int, 1, 1) as id into tab from student;–将studeng表的烈属,以/1自增形式创建一个tab
    select * from tab;
    select @@rowcount;–影响行数
    select @@cursor_rows;–返回连接上打开的游标的当前限定行的数目
    select @@error;–T-SQL的错误号
    select @@procid;

    8、 配置函数

    set datefirst 7;–设置每周的第一天,表示周日
    select @@datefirst as ‘星期的第一天’, datepart(dw, getDate()) AS ‘今天是星期’;
    select @@dbts;–返回当前数据库唯一时间戳
    set language ‘Italian’;
    select @@langId as ‘Language ID’;–返回语言id
    select @@language as ‘Language Name’;–返回当前语言名称
    select @@lock_timeout;–返回当前会话的当前锁定超时设置(毫秒)
    select @@max_connections;–返回SQL Server 实例允许同时进行的最大用户连接数
    select @@MAX_PRECISION AS ‘Max Precision’;–返回decimal 和numeric 数据类型所用的精度级别
    select @@SERVERNAME;–SQL Server 的本地服务器的名称
    select @@SERVICENAME;–服务名
    select @@SPID;–当前会话进程id
    select @@textSize;
    select @@version;–当前数据库版本信息

    9、 系统统计函数

    select @@CONNECTIONS;–连接数
    select @@PACK_RECEIVED;
    select @@CPU_BUSY;
    select @@PACK_SENT;
    select @@TIMETICKS;
    select @@IDLE;
    select @@TOTAL_ERRORS;
    select @@IO_BUSY;
    select @@TOTAL_READ;–读取磁盘次数
    select @@PACKET_ERRORS;–发生的网络数据包错误数
    select @@TOTAL_WRITE;–sqlserver执行的磁盘写入次数
    select patIndex(‘%soft%’, ‘microsoft SqlServer’);
    select patIndex(‘soft%’, ‘software SqlServer’);
    select patIndex(‘%soft’, ‘SqlServer microsoft’);
    select patIndex(‘%so_gr%’, ‘Jsonisprogram’);
    10、 用户自定义函数

    查看当前数据库所有函数

    –查询所有已创建函数

    select definition,* from sys.sql_modules m join sys.objects o on m.object_id = o.object_id
    and type in(‘fn’, ‘if’, ‘tf’);

    创建函数

    if (object_id(‘fun_add’, ‘fn’) is not null)
    drop function fun_add
    go
    create function fun_add(@num1 int, @num2 int)
    returns int
    with execute as caller
    as
    begin
    declare @result int;
    if (@num1 is null)
    set @num1 = 0;
    if (@num2 is null)
    set @num2 = 0;
    set @result = @num1 + @num2;
    return @result;
    end
    go

    调用函数

    select dbo.fun_add(id, age) from student;

    自定义函数,字符串连接

    if (object_id(‘fun_append’, ‘fn’) is not null)
    drop function fun_append
    go
    create function fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
    returns nvarchar(2048)
    as
    begin
    return @args + @args2;
    end
    go
    select dbo.fun_append(name, ‘abc’) from student;

    修改函数

    alter function fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
    returns nvarchar(1024)
    as
    begin
    declare @result varchar(1024);
    –coalesce返回第一个不为null的值
    set @args = coalesce(@args, ”);
    set @args2 = coalesce(@args2, ”);;
    set @result = @args + @args2;
    return @result;
    end
    go
    select dbo.fun_append(name, ‘#abc’) from student;

    返回table类型函数

    返回table对象函数

    select name, object_id, type from sys.objects where type in (‘fn’, ‘if’, ‘tf’) or type like ‘%f%’;
    if (exists (select * from sys.objects where type in (‘fn’, ‘if’, ‘tf’) and name = ‘fun_find_stuRecord’))
    drop function fun_find_stuRecord
    go
    create function fun_find_stuRecord(@id int)
    returns table
    as
    return (select * from student where id = @id);
    go
    select * from dbo.fun_find_stuRecord(2);

    展开全文
  • sql server 高级查询语句小结 讲述sql高级查询语句小结
  • mysql> select 1+3; +-----+ | 1+3 | +-----+ | 4 | +-----+ 1 row in set (0.00 sec)
  • SQL经典查询语句sql查询语句sql常用查询语句SQL建表,建库,建试图···等等 sql基本查询高级查询,牛逼查询 等等···只有你想不到的,没有你不想要的···
  • MySQL高级SQL语句

    万次阅读 多人点赞 2021-06-28 15:06:13
    MySQL高级SQL语句MySQL高级SQL语句函数数学函数聚合函数字符串函数 MySQL高级SQL语句 use kgc; create table location (Region char(20),store_name char(20)); insert into location values ('East','Boston') ; ...

    MySQL高级SQL语句

    use kgc;
    create table location (Region char(20),store_name char(20));
    insert into location values ('East','Boston') ;
    insert into location values ('East','New York');
    insert into location values ('west','Los Angeles');
    insert into location values ('west','Houston') ;
    

    在这里插入图片描述

    create table Store_Info (Store_Name char(20),sales int(10),Date char(10));
    insert into Store_Info values ('Los Angeles','1500','2020-12-05');
    insert into Store_Info values ('Houston','250','2020-12-07');
    insert into Store_Info values ('Los Angeles','300','2020-12-08');
    insert into Store_Info values ('Boston','700','2020-12-08');
    

    在这里插入图片描述

    SELECT

    ---- SELECT ---- 显示表格中一个或数个栏位的所有资料
    语法: SELECT "栏位" FROM "表名";
    SELECT Store_Name FROM Store_Info;
    

    在这里插入图片描述

    DISTINCT

    ---- DISTINCT ---- 不显示重复的资料
    语法: SELECT DISTINCT "栏位" FROM "表名";
    SELECT DISTINCT Store_Name FROM Store_Info;
    

    在这里插入图片描述

    WHERE

    ---- WHERE ---- 有条件查询
    语法:SELECT "栏位” FROM "表名" WHERE "条件";
    SELECT Store_Name FROM Store_Info WHERE Sales > 1000;
    

    在这里插入图片描述

    AND OR

    ---- AND OR ---- 且 或
    语法:SELECT "栏位" FROM "表名” WHERE "条件1" {[ANDIOR] "条件2"}+;
    SELECT Store_Name FROM Store_Info WHERE Sales > 1000 OR (Sales < 500 AND Sales > 200);
    

    在这里插入图片描述

    IN

    ---- IN ---- 显示己知的值的资料
    语法: SELECT "栏位" FROM "表名" WHERE "栏位" IN ('值1','值2', ...);
    SELECT * FROM Store_Info WHERE Store_Name IN ('Los Angeles','Houston');
    

    在这里插入图片描述

    BETWEEN

    ---- BETWEEN ---- 显示两个值范围内的资料
    语法:SELECT "栏位" FROM "表名" WHERE "栏位" BETWEEN '值1' AND '值2';
    SELECT * FROM Store_Info WHERE Date BETWEEN '2020-12-06' AND '2020-12-10';
    

    在这里插入图片描述

    通配符

    ---- 通配符 ---- 通常通配符都是跟LIKE一起使用的
    % : 百分号表示零个、一个或多个字符
    _ : 下划线表示单个字符
    
    'A_Z':所有以‘A’起头,另一个任何值的字符,且以Z'为结尾的字符串。例如,'A.BZ’和‘A.22’都符合这一个模式,而‘AKK2'并不符合(因为在A和Z之间有两个字符,而不是一个字符)。
    'ABC%':所有以'ABC’起头的字符串。例如,'ABCD’和'ABCABC’都符合这个模式。
    '%XYZ':所有以'XYZ’结尾的字符串。例如,'WXYZ’和‘ZZXYZ’都符合这个模式。
    '%AN%':所有含有'AN'这个模式的字符串。例如,'LOS ANGELES’和'SAN FRANCISCO'都符合这个模式。
    '_AN%':所有第二个字母为‘A'和第三个字母为'N’的字符串。例如,'SAMN FRANCITSCO’符合这个模式,而'LOS ANGELES'则不符合这个模式。
    ---- LIKE ---- 匹配一个模式来找出我们要的资料
    语法:SELECT "栏位" FROM "表名" WHERE "栏位" LIKE {模式};
    SELECT * FROM Store_Info WHERE Store_Name like '%os%';
    ---- ORDER BY ---- 按关键字排序
    语法:SELECT "栏位" FROM "表名" [WHERE "条件"] ORDER BY "栏位" [ASC,DESC];
    #ASC是按照升序进行排序的,是默认的排序方式。
    #DESC是按降序方式进行排序。
    SELECT Store_Name,Sales,Date FROM Store_Info ORDER BY Sales DESC;
    

    在这里插入图片描述

    函数

    数学函数

    abs(x)                #返回x的绝对值
    rand()                #返回0到1的随机数
    mod(x,y)              #返回x除以y以后的余数
    power(x,y)            #返回x的y次方
    round(x)              #返回离x最近的整数
    round(x,y)            #保留x的y位小数四舍五入后的值
    sqrt(x)               #返回x的平方根
    truncate(x,y)         #返回数字x截断为y位小数的值
    ceil(×)               #返回大于或等于x的最小整数
    floor(x)              #返回小于或等于x的最大整数
    greatest(x1,x2...)    #返回集合中最大的值
    least(x1,x2...)       #返回集合中最小的值
    
    
    SELECT abs(-1),rand(),mod(5,3),power(2,3),round(1.89);
    SELECT round(1.8937,3),truncate(1.235,2),ceil(5.2),floor(2.1),least(1.89,3,6.1,2.1);
    

    在这里插入图片描述

    聚合函数

    聚合函数:
    avg()       #返回指定列的平均值
    count()     #返回指定列中非 NULL值的个数
    min()       #返回指定列的最小值
    max()       #返回指定列的最大值
    sum(x)      #返回指定列的所有值之和
    
    SELECT avg(Sales) FROM Store_Info;
    
    SELECT count(store_Name) FROM Store_Info;
    SELECT count(DISTINCT store_Name) FROM Store_Info;
    
    SELECT max(Sales) FROM Store_Info;
    SELECT min(sales) FROM Store_Info;
    
    SELECT sum(sales) FROM Store_Info;
    
    SELECT count(DISTINCT store_Name) FROM Store_Info;
    SELECT count(*) FROM Store_Info;
    #count(*)包括了所有的列的行数,在统计结果的时候,不会忽略列值为NULL
    #count(列名)只包括列名那一列的行数,在统计结果的时候,会忽略列值为NULL的行
    

    在这里插入图片描述

    字符串函数

    字符串函数:
    trim()             #返回去除指定格式的值
    concat(x,y)        #将提供的参数x和y拼接成一个字符串
    substr(x,y)        #获取从字符串x中的第y个位置开始的字符串,跟substring()函数作用相同
    substr(x,y,z)      #获取从字符串x中的第y个位置开始长度为z的字符串
    length(x)          #返回字符串x的长度
    replace(x,y,z)     #将字符串z替代字符串x中的字符串y
    upper(x)           #将字符串x的所有字母变成大写字母
    lower(x)           #将字符串x的所有字母变成小写字母
    left(x,y)          #返回字符串x的前y个字符
    right(x,y)         #返回字符串x的后y个字符
    repeat(x,y)        #将字符串x重复y次
    space(x)           #返回x个空格
    strcmp (x,y)       #比较x和y,返回的值可以为-1,0,1
    reverse(x)         #将字符串x反转
    
    SELECT concat(Region,Store_Name) FROM location WHERE Store_Name = 'Boston';
    
    #如sql_mode开启开启了PIPES_AS_CONCAT,"||"视为字符串的连接操作符而非或运算符,和字符串的拼接函数concat相类似,这和Oracle数据库使用方法一样的
    SELECT Region || ' ' || Store_Name FROM location WHERE Store_Name = 'Boston';
    
    SELECT substr(Store_Name,3) FROM location WHERE Store_Name = 'Los Angeles';
    SELECT substr(Store_Name,2,4) FROM location WHERE Store_Name = 'New York';
    
    SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串);
    #[位置]:的值可以为 LEADING(起头),TRAILING(结尾),BOTH(起头及结尾)。
    #[要移除的字符串]:从字串的起头、结尾,或起头及结尾移除的字符串。缺省时为空格
    
    SELECT TRIM(LEADING 'Ne' FROM 'New York');
    
    SELECT Region,length(Store_Name) FROM location;
    
    SELECT REPLACE(Region,'ast','astern')FROM location;
    

    在这里插入图片描述
    在这里插入图片描述

    ---- GROUP BY ----GROUP BY后面的栏位的查询结果进行汇总分组,通常是结合聚合函数一起使用的
    GROUP BY有一个原则,就是SELECT后面的所有列中,没有使用聚合函数的列,必须出现在GROUP BY后面。
    
    语法:SELECT "栏位1",SUM("栏位2") FROM "表名" GROUP BY "栏位1";
    SELECT Store_Name,SUM(Sales) FROM Store_Info GROUP BY Store_Name ORDER BY sales desc;
    
    ---- HAVING ---- 用来过滤由GROUP BY语句返回的记录集,通常与GROUP BY语句联合使用
    HAVING语句的存在弥补了wHERE关键字不能与聚合函数联合使用的不足。如果被SELECcT的只有函数栏,那就不需要GROUP BY子句。
    
    语法:SELECT "栏位1",SUM("栏位2") FROM "表格名" GROUP BY "栏位1" HAVING (函数条件);
    SELECT Store_Name,SUM(Sales) FROM Store_Info GROUP BY Store_Name HAVING SUM (Sales) > 1500;
    
    ---- 别名 ---- 栏位别名表格别名
    
    语法:SELECT "表格别名"."栏位1” [AS] "栏位别名" FROM "表格名" [AS] "表格别名"
    SELECT A.Store_Name Store,SUM(A.Sales) "Total Sales" FROM Store_Info A GROUP BY A.Store_Name;
    
    ---- 子查询 ---- 连接表格,在WHERE子句或 HAVING子句中插入另一个 SQL语句
    
    语法: SELECT "栏位1" FROM "表格1" WHERE "栏位2" [比较运算符]       #外查询
    (SELECT "栏位1" FROM "表格2" WHERE "条件");                       #内查询
    
    可以是符号的运算符,例如 =、>、<、>=、<= ;也可以是文字的运算符,例如LIKE、IN、BETWEEN
    SELECT SUM(Sales) FROM Store_Info WHERE Store_Name IN
    (SELECT Store_Name FROM location WHERE Region = 'west');
    
    SELECT SUM(A.Sales) FROM Store_Info A WHERE A.Store_Name IN
    (SELECT Store_Name FROM location B WHERE B.Store_Name = A.Store_Name);
    

    在这里插入图片描述

    EXISTS

    ---- EXISTS ---- 用来测试内查询有没有产生任何结果,类似布尔值是否为真
    #如果有的话,系统就会执行外查询中的SQL语句。若是没有的话,那整个SQL语句就不会产生任何结果。
    语法: SELECT "栏位1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");
    SELECT SUM(Sales) FROM Store_Info WHERE EXISTS (SELECT * FROM location WHERE Region = 'West');
    

    在这里插入图片描述

    • 连接查询
      • location 表格
        在这里插入图片描述
    UPDATE Store_Info SET store_name='washington’WHERE sales=300;
    
    • Store_Info表格
      在这里插入图片描述
    inner join(内连接):只返回两个表中联结字段相等的行
    left join(左连接):返回包括左表中的所有记录和右表中联结字段相等的记录
    right join(右连接):返回包括右表中的所有记录和左表中联结字段相等的记录
    
    SELECT * FROM location A INNER JOIN Store_Info B on A.Store_Name = B.store_Name ;
    
    SELECT * FROM location A RIGHT JOIN Store_Info B on A.Store_Name = B.Store_Name ;
    
    SELECT * FROM location A,store_Info B WHERE A.Store_Name = B.Store_Name;
    
    SELECT A.Region REGION,SUM(B.Sales) SALES FROM location A,Store_Info B
    WHERE A.Store_Name = B.Store_Name GROUP BY REGION;
    

    在这里插入图片描述

    CREATE VIEW

    ---- CREATE VIEW ---- 视图,可以被当作是虚拟表或存储查询。
    ·视图跟表格的不同是,表格中有实际储存资料,而视图是建立在表格之上的一个架构,它本身并不实际储存资料。
    ·临时表在用户退出或同数据库的连接断开后就自动消失了,而视图不会消失。
    ·视图不含有数据,只存储它的定义,它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询,而且还要进行统计排序等操作,写SQT语句会很麻烦的,用视图将几个表联结起来,然后对这个视图进行查询操作,就和对一个表查询一样,很方便。
    
    语法:CREATE VIEW "视图表名” AS "SELECT语句";
    CREATE VIEW V_REGION_SALES AS SELECT A.Region REGION, SUM(B.Sales) SALES FROM location A
    INNER JOIN Store_Info B ON A.Store_Name = B.Store_Name GROUP BY REGION;
    
    SELECT * FROM V_REGION_SALES;
    DROP VIEW V_REGION_SALES;
    

    在这里插入图片描述

    UNION

    ---- UNION ---- 联集,将两个SQL语句的结果合并起来,两个SQI语句所产生的栏位需要是同样的资料种类
    UNION:生成结果的资料值将没有重复,且按照字段的顺序进行排序
    语法:[SELECT 语句 1] UNION [SELECT 语句2];
    
    UNION ALL:将生成结果的资料值都列出来,无论有无重复
    语法:[SELECT 语句 1] UNION ALL [SELECT 语句 2];
    
    SELECT Store_Name FROM location UNION SELECT Store_Name FROM Store_Info;
    
    SELECT Store_Name FROM location UNION ALL SELECT Store_Name FROM Store_Info;
    

    在这里插入图片描述

    交集值

    ----- 交集值 ---- 取两个SQL语句结果的交集
    SELECT A.Store_Name FROM location A INNER JOIN Store_Info B ON A.Store_Name = B.Store_Name;
    SELECT A.Store_Name FROM location A INNER JOIN Store_Info B USING(Store_Name);
    
    #两表基中的一个表没有指定的行,而另一个表这个行有重复,并且确实有交集的时候用
    SELECT A.Store_Name FROM
    (SELECT Store_Name FROM location UNION ALL SELECT Store_Name FROM store_Info) A
    GROUP BY A.Store_Name HAVING COUNT(*) > 1;
    
    #取两个sQL语句结果的交集,且没有重复
    SELECT A.Store_Name FRONM (SELECT B.Store_Name FROM location B INNER JOIN Store_Info C ON B.Store_Name = C.store_Name) A
    GROUP BY A.Store_Name;
    
    SELECT DISTINCT A.Store_Name FROM location A INNER JOIN Store_Info B USING(Store_Name);
    
    SELECT DISTIMCT Store_Name FROM location WHERE (Store_Name) IN (SELECT Store_Name FROM Store_Info);
    
    SELECT DISTINCT A.Store_Name FROM location A LEFT JOIN Store_Info B USING(Store_Name) WHERE B.Store_Name IS NOT NULL;
    

    在这里插入图片描述

    无交集值

    ---- 无交集值 ---- 显示第一个sQL语句的结果,且与第二个SQL语句没有交集的结果,且没有重复
    SELECT DISTINCT Store_Name FROM location WHERE (Store_Name) NOT IN (SELECT Store_Name FROM Store_Info);
    
    SELECT DISTINCT A.Store_Name FROM location A LEFT JOIN Store_Info B USING(Store_Name) WHERE B.Store_Name IS NULL;
    

    在这里插入图片描述

    CASE

    ---- CASE ---- 是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字
    语法:
    SELECT CASE ("栏位名")
      WHEN "条件1” THEN "结果1"
      WHEN "条件2" THEN "结果2"
      ...
      [ELSE "结果N"]
      END
    FROM "表名";
    
    #"条件"可以是一个数值或是公式。ELSE子句则并不是必须的。
    
    SELECT store_Name, CASE Store_Name
      WHEN 'Los Angeles' THEN Sales * 2
      WHEN 'Boston' THEN Sales * 1.5
      ELSE Sales
      END
    "New Sales",Date
    FROM Store_Info;
    
    #"New Sales"是用于CASE 那个栏位的栏位名。
    
    CREATE TABLE Total_Sales (Name char(10),sales int(5));
    INSERT INTO Total_Sales VALUES ('zhangsan',10);
    INSERT INTO Total_Sales VALUES ('lisi',15);
    INSERT INTO Total_Sales VALUES ('wangwu',20);
    INSERT INTO Total_Sales VALUES ('zhaoliu',40);
    INSERT INTO Total_Sales VALUES ('sunqi',50);
    INSERT INTO Total_Sales VALUES ('zhouba',20);
    INSERT INTO Total_Sales VALUES ('wujiu',30);
    

    在这里插入图片描述
    在这里插入图片描述

    1、算排名

    ----算排名----表格自我连结(Self Join),然后将结果依序列出,算出每一行之前〈包含那一行本身)有多少行数
    SELECT A1.Name, A1.sales,COUNT(A2.sales) Rank FROM Total_sales A1,Total_sales A2
    WHERE A1.sales < A2.sales 0R (A1.sales = A2.sales AND A1.Name = A2.Name)
    GROUP BY A1.Name, A1.sales ORDER BY A1.sales DESC;
    
    例如:
    select A1.Name,A1.sales,count(A2.sales) rank from Total_Sales A1,Total_Sales A2 where A1.sales <  A2.sales OR (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc;
    
    解释:
    当A1的sales字段值小于A2的sales字段值、或者两表sales字段值相等并且Name字段值相等时,
    从A1和A2表中查询A1的Name字段值、A1的sales字段值、和A2的sales字段的非空值rank是别名,并为A1的Name字段分组,A1的sales字段降序排序
    

    在这里插入图片描述

    2、算中位数

    ---- 算中位数 ----
    SELECT Sales Middle FROM (SELECT A1.Name,A1.Sales,COUNT(A2.Sales) Rank FROM Total_Sales A1,Total_Sales A2
    WHERE A1.Sales < A2.Sales 0R (A1.Sales = A2.Sales AND A1.Name >= A2.Name)
    GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales DESC) A3
    WHERE A3.Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);
    
    例如:
    select * from (select A1.Name,A1.sales,count(A2.sales) rank from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales OR (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from Total_Sales);
    
    select sales mid from (select A1.Name,A1.sales,count(A2.sales) rank from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales OR (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from Total_Sales);
    
    
    #每个派生表必须有自己的别名,所以别名A3必须要有
    #DIV 是在MySQL中算出商的方式
    

    在这里插入图片描述

    3、算累积总计

    ---- 算累积总计 ---- 表格自我连结(Self Join),然后将结果依序列出,算出每一行之前(包含那一行本身)的总合
    SELECT A1.Name, A1.Sales, SUM(A2.Sales) Sum_Total FROM Total_Sales A1,Total_Sales A2
    WHERE A1.Sales < A2.Sales OR (A1.Sales=A2.Sales AND A1.Name = A2.Name)
    GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales DESC;
    
    例如:
    select A1.*,sum(A2.sales) sum_soales from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales or(A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc;
    

    在这里插入图片描述

    4、算总合百分比

    例如:
    select A1.*,A1.sales/(select sum(sales) from Total_Sales) z_sum from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales or (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name;
    
    #select sum(sales) from Total_Sales 是为了算出字段值总合,然后每一行一一除以总合,算出每行的总合百分比。
    

    在这里插入图片描述

    5、算累计总合百分比

    例如:
    select A1.Name,A1.sales,sum(A2.sales),sum(A2.sales)/(select sum(sales) from Total_Sales) Z from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales or (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc;
    
    select A1.Name,A1.sales,sum(A2.sales),TRUNCATE(sum(A2.sales)/(select sum(sales) from Total_Sales),2) ||'%' Z from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales or (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc;
    

    在这里插入图片描述

    6、空值(null)和无值(’ ')的区别

    • 1、无值的长度为0,不占用空间;而空值null 的长度是null,是占用空间的
    • 2、IS NULL或者IS NOT NULL,是用来判断字段是不是NULL或者不是NULL,是不能查出是不是无值的
    • 3、无值的判断使用=’‘或者<>’'来处理。<>代表不等于
    • 4、在通过count()指定字段统计又多少行数时,如果遇到NULL值会自动忽略掉,遇到空值会自动加入记录中进行计算
    例如:
    create table SITE(site varchar(20));
    insert into SITE values('nanjing');
    insert into SITE values('beijing');
    insert into SITE values('');
    insert into SITE values('taijin');
    insert into SITE values();
    insert into SITE values('');
    select * from SITE;	
    

    在这里插入图片描述

    select length(site) from SITE;
    select * from SITE where site is NULL;
    select * from SITE where site is not NULL;
    select * from SITE where site ='';
    select * from SITE where site <> '';
    

    在这里插入图片描述

    7、正则表达式(与Shell部分一样)

    匹配模式			描述									实例
    ^ 				匹配文本的开始字符 						‘^bd’ 匹配以 bd 开头的字符串
    $ 				匹配文本的结束字符 						‘qn$’ 匹配以 qn 结尾的字符串
    . 				匹配任何单个字符							‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串
    * 				匹配零个或多个在它前面的字符 				‘fo*t’ 匹配 t 前面有任意个 o
    + 				匹配前面的字符 1 次或多次					‘hom+’ 匹配以 ho 开头,后面至少一个m 的字符串
    字符串 			匹配包含指定的字符串 						‘clo’ 匹配含有 clo 的字符串
    p1|p2 			匹配 p1 或 p2 							‘bg|fg’ 匹配 bg 或者 fg
    [...] 			匹配字符集合中的任意一个字符 				‘[abc]’ 匹配 a 或者 b 或者 c
    [^...] 			匹配不在括号中的任何字符 					‘[^ab]’ 匹配不包含 a 或者 b 的字符串
    {n} 			匹配前面的字符串 n 次 					‘g{2}’ 匹配含有 2 个 g 的字符串
    {n,m}			匹配前面的字符串至少 n 次,至多m 次		‘f{1,3}’ 匹配 f 最少 1 次,最多 3 次
    
    • 语法:SELECT 字段 FROM 表名 WHERE 字段 REGEXP 匹配模式
    例如:
    select * from Total_Sales where Name regexp '^[n]';
    select * from Total_Sales where Name regexp '[n]';
    select * from Total_Sales where Name regexp 'Ho|Bo';
    

    在这里插入图片描述

    8、存储过程(与Shell函数差不多,代码的复用)

    • 存储过程是一组为了完成特定功能的SQL语句集合
    • 存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称来进行储存,这个过程经编译和优化后存储在数据库服务器中,当需要使用该存储过程时,只需要调用它即可,存储过程在执行上比传统SQL速度更快,执行效率更高。
    • 存储过程的优点
      • 1、执行一次后,会将生成的二进制代码驻留缓冲区,提高执行效率
      • 2、SQL语句加上控制语句的集合,灵活性高
      • 3、在服务器端存储,客户端调用时,降低网络负载
      • 4、可多次重复被调用,可随时修改,不影响客户端调用
      • 5、可完成所有的数据库操作,也可控制数据库的信息访问权限
    语法:
    DELIMITER !!    				#将语句的结束符号从分号;临时修改,以防出问题,可以自定义
    CREATE PROCEDURE XXX()  	#创建存储过程,过程名自定义,()可带参数
     	BEGIN   		    	#过程体以关键字BEGIN开始
     	select * from xxx;  	#过程体语句
     	END!!    				#过程体以关键字END结尾
    DELIMITER ; 			    	#将语句的结束符号恢复为分号
    
    call XXX;	  				#调用存储过程
    
    ====查看存储过程====
    show create procedure [数据库.]储存过程名; 	  #查看某个储存过程的具体信息
    show create procedure XXX;
    show procedure status [like '%XXX%'] \G
    
    例如:
    DELIMITER !!
    CREATE PROCEDURE KIND1()
    BEGIN
    	select * from Total_Sales;
    END!!
    
    DELIMITER ;
    CALL KIND1;
    
    show create procedure KIND1\G
    show procedure status like '%KIND1%'\G
    

    在这里插入图片描述

    • 存储过程的参数:
      • IN 输入参数,表示调用者向过程传入值(传入值可以是字面量或变量)
      • OUT 输出参数:表示过程向调用者传出值(可以返回多个值,传出值只能是变量)
    例如:
    DELIMITER !!
    CREATE PROCEDURE KIND2(IN people char(20))
    BEGIN
    	select * from Total_Sales where Name=people;                                                    
    END!!         
    
    DELIMITER ;
    CALL KIND2('lisi');
    

    在这里插入图片描述

    8.1、存储过程的条件语句

    例如:
    DELIMITER !!
    CREATE PROCEDURE KIND7(IN num int(10))
    BEGIN
    declare var int;
    set var=num*2;
    if var>=10 then
    	update Total_Sales set sales=sales+1;
    else 
    	update Total_Sales set sales=sales-1;
    end if;
    END!!
    DELIMITER ;
    
    CALL KIND7(5);
    CALL KIND7(4);
    

    在这里插入图片描述
    在这里插入图片描述

    8.2、循环语句while

    例如:
    create table akg(id int);
    
    DELIMITER !!
    CREATE PROCEDURE KIND6()
    BEGIN
    declare var int;
    set var=0;
    while var<5 do
    	insert into akg values(var);
    	set var=var+1;
    end while;
    END!!
    
    DELIMITER ;
    CALL KIND6;
    select * from akg;
    

    在这里插入图片描述

    展开全文
  • SQL语句高级查询

    2012-06-05 10:25:13
    SQL语句高级查询
  • 利用spl数据库中所学知识编制的高级查询语句
  • SQL语句增删改查/高级查询

    千次阅读 2018-11-15 11:29:53
    sql高级查询语句 SQL语句规范 分号结尾 关键词大小写不区分 创建/删除数据库 创建:CREATE DATABASE 数据库名 ysj_example; 删除:DROP DATABASE 数据库名 ysj_example; 创建/删除表 创建:CREATE TABLE 表名(字段) ...

    有道笔记查看常用sql语句
    sql高级查询语句

    1. SQL语句规范
      分号结尾
      关键词大小写不区分
    2. 创建/删除数据库
      创建:CREATE DATABASE 数据库名 ysj_example;
      删除:DROP DATABASE 数据库名 ysj_example;
    3. 创建/删除表
      创建:CREATE TABLE 表名(字段)
      删除:DROP TABLE 表名
      DROP TABLE IF EXISTE 表名 (规范写法删除表)
    4. 主键设置
      PRIMARY KEY (列名)
    5. 插入数据
      INSERT INTO 数据库名.表名
      (列1,列2…列N)
      VALUES
      (‘值1’,‘值2’…列N);
    6. 查询数据
      select * from 表名 where 列名=‘值’
    7. 更新数据
      UPDATE 表名 SET 列名 =‘值’ WHERE 过滤条件
    8. 删除数据
      TRUNCATE TABLE 表名 (清空/截断)
      DELETE FROM 表名 WHERE 过滤条件
    9. 结果集ResultSet
      SELECT * FROM 表名 (返回所有列)
      SELECT 列名 FROM 表名 (返回部分列)
    10. 排序ORDER BY
      select * from 表名 where 条件
      order by 列名 asc,列名 desc order by 一般放在后面
      ASC表示升序
      DESC表示降序
    11. 范围查询
      select * from 表名 where 列名 IN (值1,值2)
      NOT IN 表示不在列表
      IN 指定一个列表,凡在此列表中的符合条件
    12. 空值查询
      select * from 表名 where 列名 IS NULL
      IS NOT NULL 不为空
    13. 相似查询
      select * from 表名 where 列名 LINK 字符(张%) %表示通配符只适合用Mysql
    14. 函数使用
      select * from 表名 where YEAR (表名) >=…
      百度:MySQL函数
    > 数据库常用命令行
    
    创建:CREATE DATABASE 数据库名 ysj_example;
    删除:DROP DATABASE 数据库名 ysj_example;
    创建:CREATE TABLE 表名(字段)
    删除:DROP TABLE 表名
         DROP TABLE IF EXISTE 表名 (规范写法删除表)
    主键:PRIMARY KEY (列名) 
    插入数据:
                INSERT INTO 数据库名.表名
                (列1,列2.....列N)
                VALUES
                ('值1','值2'......列N);
    查询数据:select * from 表名 where 列名='值'
    更新数据:UPDATE 表名 SET 列名 ='值' WHERE 过滤条件
    删除数据:
                TRUNCATE TABLE 表名 (清空所有数据)
                DELETE FROM 表名 WHERE 过滤条件
    
    
    > 高级查询命令行
    
    结果集ResultSet
    SELECT * FROM 表名 (返回所有列)
    SELECT 列名 FROM 表名  (返回部分列)
    
    排序ORDER BY
    select * from 表名 where 条件  
    order by 列名 asc,列名 desc  
    orderby一般放在后面
    ASC表示升序
    DESC表示降序
    
    范围查询
    select * from 表名 where 列名  IN  (值1,值2)
    NOT IN 表示不在列表
    IN 指定一个列表,凡在此列表中的符合条件
    
    空值查询
    select * from 表名 where 列名  IS  NULL
    IS NOT NULL  不为空
    
    相似查询
    select * from 表名 where 列名  LINK (张%)   
    %表示通配符只适合用Mysql
    
    函数使用
    select * from 表名 where YEAR  (表名) >=....
    百度:MySQL函数
    
    
    展开全文
  • 经典SQL语句大全 高级SQL查询技巧 。有示例。
  • sql高级语句.txt

    2010-10-10 00:47:53
    sql高级语句.txt sql高级语句.txt sql高级语句.txt sql高级语句.txt
  • 上一篇:【SQL高级(七)】【SQL CHECK 约束+SQL DEFAULT 约束】 文章目录SQL CREATE INDEX 语句索引SQL CREATE INDEX 语法SQL CREATE UNIQUE INDEX 语法SQL 撤销索引、撤销表以及撤销数据库DROP INDEX 语句DROP TABLE...
  • SQL查询经典语句

    2012-02-29 09:45:25
    sqlserver 基础语句,高级语句,经典语句积累。
  • sql中的高级查询语句

    2010-08-08 21:15:57
    思路清晰,容易看懂!有利于用户下载,提高sql语句的编写能力。
  • SQL查询语句高级应用

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 132,861
精华内容 53,144
关键字:

sql高级查询语句