数据库SQL语句

一、MIN(), MAX(),AVG(),COUNT(),是聚合函数.
group by 后面是要跟着的 select 中所有不是聚合函数的字段。
例1:  select count(*) from employInfo;  //只是查询总数。即 employInfo(员工信息表)这张表里一共有多少条记录 所以不用group by

。
例2:  select count(*) , depart  from  employInfo  group by  depart;   // 根据 depart(部门) 分组，查到的数据就是：列出 不同

部门(depart) 的记录总数 
        select count(*) ,  depart ,  comm from emp group by deptno , comm;      // 根据depart 和 comm 分组  以此类推
        group by 后面是要跟着的 select 中所有不是聚合函数的字段   否则会报错。
        having 相当于where     与where的唯一区别是 当查询语句中有 聚合函数 的时候 就不能用where 了，只能用having。

二、自己的例子

1、学生表 Student(stuId,stuName,stuAge,stuSex)

stuId：学号；stuName：学生姓名；stuAge：学生年龄；stuSex：学生性别

课程表 Course(classId,className,teacherId)                 

classId,课程编号；className：课程名字；teacherId：教师编号

成绩表 Scores(stuId,classId,score)                             

stuId：学号；classId,课程编号；score：成绩

教师表  Teacher(teacherId,teacherName) 

teacherId：教师编号； teacherName：教师名字

2、用PowderDesigner画出用例图1-1如下

 

                                                                                                                  图1-1

3、画出以上用例图后点击数据库，里面有Generate Database，会生成相应的数据库。如下图1-2，名为test.sql.

                             图1-2

4、生成数据库脚本之后，我们双击打开它。代码如下，这样就有各种表和表之间的约束关系，我们只需要插入数据就可以了。

/*==============================================================*/
/* Database name:  test                                         */
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2014/4/20 17:17:54                           */
/*==============================================================*/

drop database test--注释：这里和下一行都不用管
go

/*==============================================================*/
/* Database: test                                               */
/*==============================================================*/
create database test--注释：这里是生成数据库，和下面的use一起执行
go

use test
go

/*==============================================================*/
/* Table: Course  从这里往下都是生成各种表和插入数据，可以一起执行                                           */
/*==============================================================*/
create table Course (
   classId              varchar(20)          not null,
   className            varchar(20)          not null,
   teacherId            varchar(20)          null,
   constraint PK_COURSE primary key (classId)
)
go

/*==============================================================*/
/* Table: Scores                                                */
/*==============================================================*/
create table Scores (
   stuId                int                  not null,
   classId              varchar(20)          not null,
   score                int                  null,
   constraint PK_SCORES primary key (stuId, classId)
)
go

/*==============================================================*/
/* Table: Student                                               */
/*==============================================================*/
create table Student (
   stuId                int                  not null,
   stuName              varchar(20)          not null,
   stuAge               int                  null,
   stuSex               char(2)              null,
   constraint PK_STUDENT primary key (stuId)
)
go

/*==============================================================*/
/* Table: Teacher                                               */
/*==============================================================*/
create table Teacher (
   teacherId            varchar(20)          not null,
   teacherName          varchar(20)          not null,
   constraint PK_TEACHER primary key (teacherId)
)
go

alter table Course
   add constraint FK_COURSE_REFERENCE_TEACHER foreign key (teacherId)
      references Teacher (teacherId)
go

alter table Scores
   add constraint FK_SCORES_REFERENCE_COURSE foreign key (classId)
      references Course (classId)
go

alter table Scores
   add constraint FK_SCORES_REFERENCE_STUDENT foreign key (stuId)
      references Student (stuId)
go

5、现在插入数据，插入数据时注意，插入顺序，不然报错。至于为什么这样，自己的方法：

当点击表Student和表Scores之间的约束线时会出现如下图1-3

                                                                  图1-3

由此我们可以看出表Student是Prent Table,表Scores是Child Table，那我们就先插入Student表的数据，再插入Scores表的数据(至于原理为什么，我还不是很清楚，希望知道的贤士跟我探讨）。先插入Student表的数据，我们知道了。那下面的数据该再插入谁呢，由图1-1我们可以看出表Course是Parent Table,表Scores是Child Table，同时表Teacher和表Course之间，表Teacher又是Parent Table 而表Course是Child Table。所以依照这种关系，我们都是先插入Parent Table ，那我们应先插入表Teacher的数据，再插入表Course的数据，然后再插入表Scores的数据。

insert into Student values('001','张三','18','男')
insert into Student values('002','李四','16','男')
insert into Student values('003','王五','17','男')
insert into Student values('004','李玥','20','女')
insert into Student values('005','小胖','86','男')

insert into Teacher values('a01','王老师')
insert into Teacher values('b01','郭老师')
insert into Teacher values('c01','于老师')
insert into Teacher values('d01','张老师')

insert into Course values('a','语文','a01')
insert into Course values('b','数学','b01')
insert into Course values('c','英语','c01')
insert into Course values('d','计算机','d01')

insert into Scores values('001','a','85')
insert into Scores values('002','b','80')
insert into Scores values('003','c','95')
insert into Scores values('004','d','90')

SELECT * FROM STUDENT
SELECT * FROM SCORES
SELECT * FROM COURSE
SELECT * FROM TEACHER

 

三、聚合函数：是对一组值执行计算并返回单一的值的函数。

它经常与SELECT语句的GROUP BY子句一同使用，SQL SERVER 中具体有哪些聚合函数呢？我们来一一看一下：

1. AVG  返回指定列中的平均值，空值被忽略。
select stuSex,AVG(stuAge) from student group by stuSex; //这里表示在表(Student)中以stuSex(学生性别)分组，求stuAge(学生年龄均值)。即男生和女生的平均年龄 

 首先在数据库中操作查询表Student如图1-4所示

                                                                      图1-4

当我们执行语句select stuSex,AVG(stuAge) from student group by stuSex时，如图1-5所示

                      

                                                图1-5

这样就按性别分成了男生组和女生组，男生组的平均年龄和女生组的平均年龄也都求出来了。

PS:后来发现了年龄那列无列名，我们可以改为如下两种语句的任意一种写法都是可以的。
select stuSex,AVG(stuAge) As'stuAge' from student group by stuSex;

select stuSex,AVG(stuAge) stuAge from student group by stuSex

结果如图所示

这里就有了列名：stuAge，注意跟上图的区别。（由于这是后来注意到的问题，其他图类似）

 

2. COUNT  返回指定列中有多少行记录数据。
      例：select count(stuId) from student

表student如上图1-4所示，我们执行COUNT聚合函数语句之后的结果如图1-6所示

 

                                                              图1-6

此结果表示按stuId这列查询的话共有5条记录。

 

3. MAX  返回指定分组中的最大值。
      例：select stuSex,MAX(stuAge) from student group by stuSex;//这里还是那性别分组，男女组。整体表示查询出男组的最大年龄和女组的最大年龄。

其中student表入上图1-4所示，通过聚合函数MAX查询出的结果如图1-7所示

 

                                                图1-7

由以上结果可以看出，男生组年龄最大的是86岁，女生组年龄最大的20岁。

 

4. MIN  返回指定分组中的最小值。
      例：select stuSex,MIN(stuAge) from student group by stuSex

同3. 结果如图1-8所示

                                                                     图1-8

 

5. SUM  返回表Student中按stuSex(性别分组)中分为男组和女组，男组学生年龄的总和和女生组年龄的总和。只能用于数字列，空值被忽略。
      例：select stuSex,SUM(stuAge) from student group by stuSex

结果如图1-9所示

 

                                                                                图1-9

6. COUNT_BIG  返回指定组中的项目数量，COUNT_BIG 的用法与 COUNT 函数类似。 两个函数唯一的差别是它们的返回值。 COUNT_BIG 始终返回 bigint(会超过2的32次方) 数据类型值。 COUNT 始终返回 int(最大是2的32次方范围内) 数据类型值。
     例：select count_big(stuSex) from student

结果如图1-10所示

 

                                                                 图1-10
7. GROUPING  产生一个附加的列，当用CUBE或ROLLUP运算符添加行时，输出值为1.当所添加的行不是由CUBE或ROLLUP产生时，输出值为0.
     例：select stuSex,SUM(stuAge),grouping(stuSex) from student group by stuSex with rollup

           select stuSex,SUM(stuAge) stuAge,grouping(stuSex) from student group by stuSex with cube ;//两句完全相同，用哪个都可以。

结果如图1-11所示

                                                                                                             图1-11

例子2.select stuSex,grouping(stuSex) from student group by stuSex with cube

结果如图1-12所示

                                                                                                                  图1-12

 

8. BINARY_CHECKSUM  返回对表中的行或表达式列表计算的二进制校验值，用于检测表中行的更改。
     例：select  prd_no,binary_checksum(qty) from sales group by prd_no
 
9. CHECKSUM_AGG  返回指定数据的校验值，空值被忽略。
     例：select  prd_no,checksum_agg(binary_checksum(*)) from sales group by prd_no
 
10. CHECKSUM  返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引。
 
11. STDEV  返回给定表达式中所有值的统计标准偏差。
     例：select  stdev(prd_no) from sales
 
12. STDEVP  返回给定表达式中的所有值的填充统计标准偏差。
     例：select  stdevp(prd_no) from sales
 
13. VAR  返回给定表达式中所有值的统计方差。
     例：select  var(prd_no) from sales
 
14. VARP  返回给定表达式中所有值的填充的统计方差。
     例：select  varp(prd_no) from sales

（8、9、10、11、12、13、14暂不做解答）

以上用到的所有SQL语句

select stuSex,AVG(stuAge) As'stuAge' from student group by stuSex
select stuSex,AVG(stuAge) stuAge from student group by stuSex
select count(stuId) from student
select stuSex,MAX(stuAge) from student group by stuSex
select stuSex,MIN(stuAge) from student group by stuSex
select stuSex,SUM(stuAge) from student group by stuSex
select count_big(stuSex) from student
select stuSex,SUM(stuAge),grouping(stuSex) from student group by stuSex with rollup
select stuSex,SUM(stuAge) stuAge,grouping(stuSex) from student group by stuSex with cube
select stuSex,grouping(stuSex) from student group by stuSex with cube