数据库关系模式的理解

第一范式(1NF):

数据库系统概论引用:

  1. 列是同质的(Homogeneous),即每一列中的分量是同一类型的数据,来自同一个域.
  2. 不同的列可出自同一个域,称其中的每一列为一个属性,不同的属性要给予不同的属性名.
  3. 列的顺序无所谓,即列的次序可以任意交换.
  4. 任意两个元组不能完全相同.
  5. 行的顺序无所谓,即行的次序可以任意交换.
  6. 分量必须取原子值,即每一个分量都必须是不可分的数据项.

所谓第一范式(1NF)是指在关系模型中,对域添加的一个规范要求,所有的域都应该是原子性的,即数据库表的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等非原子数据项。即实体中的某个属性有多个值时,必须拆分为不同的属性。在符合第一范式(1NF)表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之,第一范式就是无重复的域。
说明:在任何一个关系数据库中,第一范式(1NF)是对关系模式的设计基本要求,一般设计中都必须满足第一范式(1NF)。不过有些关系模型中突破了1NF的限制,这种称为非1NF的关系模型。换句话说,是否必须满足1NF的最低要求,主要依赖于所使用的关系模型。

第二范式(2NF):

数据库系统概论引用:
若R<U,F>∈1NF,并且每一个非主属性都完全函数依赖于码,则R<U,F>∈2NF。

在1NF的基础上,非码属性必须完全依赖于码[在1NF基础上消除非主属性对主码的部分函数依赖]
第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组,作为实体的唯一标识。例如在员工表中的身份证号码即可实现每个一员工的区分,该身份证号码即为候选键,任何一个候选键都可以被选作主键。在找不到候选键时,可额外增加属性以实现区分,如果在员工关系中,没有对其身份证号进行存储,而姓名可能会在数据库运行的某个时间重复,无法区分出实体时,设计辟如ID等不重复的编号以实现区分,被添加的编号或ID选作主键。(该主键的添加是在ER设计时添加,不是建库时随意添加)
第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的唯一标识。简而言之,第二范式就是在第一范式的基础上属性完全依赖于主键。

第三范式(3NF):

数据库系统概论引用:
传递依赖:在R<U,F>中,如果X → Y,(Y ⊈ X),Y !→ X,Y → Z,则称Z对X传递函数依赖.

3NF:关系模式R<U,F>中若不存在这样的码X,属性组Y及非主属性Z(Z⊈Y), 使得X → Y,Y → Z成立,Y !→ X,则称R<U,F>∈3NF。

简单的说,就是没有传递的第二范式。

第三范式(3NF)是第二范式(2NF)的一个子集,即满足第三范式(3NF)必须满足第二范式(2NF)。简而言之,第三范式(3NF)要求一个关系中不包含已在其它关系已包含的非主关键字信息。例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性,也就是在满足2NF的基础上,任何非主属性不得传递依赖于主属性。

数据库系统概论引用:
设关系模式R<U,F>∈1NF,若X → Y且Y ⊈ X时X必含码,则R<U,F>∈BCNF。

在1NF基础上,任何非主属性不能对主键子集依赖[在3NF基础上消除对主码子集的依赖]
巴斯-科德范式(BCNF)是第三范式(3NF)的一个子集,即满足巴斯-科德范式(BCNF)必须满足第三范式(3NF)。通常情况下,巴斯-科德范式被认为没有新的设计规范加入,只是对第二范式与第三范式中设计规范要求更强,因而被认为是修正第三范式,也就是说,它事实上是对第三范式的修正,使数据库冗余度更小。这也是BCNF不被称为第四范式的原因。某些书上,根据范式要求的递增性将其称之为第四范式是不规范,也是更让人不容易理解的地方。而真正的第四范式,则是在设计规范中添加了对多值及依赖的要求。
对于BCNF,在主码的任何一个真子集都不能决定于主属性。关系中U主码,若U中的任何一个真子集X都不能决定于主属性Y,则该设计规范属性BCNF。例如:在关系R中,U为主码,A属性是主码中的一个属性,若存在A->Y,Y为主属性,则该关系不属于BCNF。
一般关系型数据库设计中,达到BCNF就可以了!

MySQL 用户与授权管理详解

一、前言
做为Mysql数据库管理员管理用户账户,是一件很重要的事,指出哪个用户可以连接服务器,从哪里连接,连接后能做什么。Mysql从3.22.11开始引入两个语句来做这件事,GRANT语句创建Mysql用户并指定其权限,而REVOKE语句删除权限。CREATE和REVOKE语句影响4个表,
user 能连接服务器的用户以及他们拥有的任何全局权限
db 数据库级权限
tables_priv 表级权限
columns_priv 列级权限
还有第5个授权表host,但它不受GRANT和REVOKE的影响
当你对一个用户发出一条GRANT语句时,在user表中为该用户创建一条记录。如果语句指定任何全局权限(管理权限或适用于所有数据库的权限),这些也记录在user表中。如果你指定数据库、表和列级权限,他们被分别记录在db、tables_priv和columns_priv表中。

二、创建用户并授权
1.GRANT 语句的用法

mysql> help grant 
Name: 'GRANT'  
Description:  
Syntax:  
GRANT  
    priv_type [(column_list)]  
      [, priv_type [(column_list)]] ...  
    ON [object_type] priv_level  
    TO user_specification [, user_specification] ...  
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]  
    [WITH with_option ...]

GRANT语句的语法看上去像这样,

GRANT privileges [columns] ON what TO user IDENTIFIED BY "password" WITH GRANT OPTION;

2.权限分类 (privileges)
第一组:指定符适用于数据库、表和列
ALTER 修改表和索引
CREATE 创建数据库和表
DELETE 删除表中已有的记录
DROP 删除数据库和表
INDEX 创建或抛弃索引
INSERT 向表中插入新行
REFERENCE 未用
SELECT 检索表中的记录
UPDATE 修改现存表记录
第二组:指定数据库数管理权限
FILE 读或写服务器上的文件
PROCESS 查看服务器中执行的线程信息或杀死线程
RELOAD 重载授权表或清空日志、主机缓存或表缓存
SHUTDOWN 关闭服务器
第三组权限特殊:ALL意味着“所有权限”,UASGE意味着无权限,即创建用户,但不授予权限
ALL 所有;ALL PRIVILEGES“所有权限”
USAGE 特殊的“无权限”权限
3.columns
权限运用的列,它是可选的,并且你只能设置列特定的权限。如果命令有多于一个列,应该用逗号分开它们。
4.what
权限运用的级别。权限可以是全局的(适用于所有数据库和所有表)、特定数据库(适用于一个数据库中的所有表)或特定表的。可以通过指定一个columns字句是权限是列特定的。
5.user
权限授予的用户,它由一个用户名和主机名组成。在MySQL中,你不仅指定谁能连接,还有从哪里连接。这允许你让两个同名用户从不同地方连接。MySQL让你区分他们,并彼此独立地赋予权限。
MySQL中的一个用户名就是你连接服务器时指定的用户名,该名字不必与你的Unix登录名或Windows名联系起来。缺省地,如果你不明确指定一个名字,客户程序将使用你的登录名作为MySQL用户名。这只是一个约定。你可以在授权表中将该名字改为nobody,然后以nobody连接执行需要超级用户权限的操作。
6.password
赋予用户的口令,它是可选的。如果你对新用户没有指定IDENTIFIED BY子句,该用户不赋给口令(不安全)。对现有用户,任何你指定的口令将代替老口令。如果你不指定口令,老口令保持不变,当你用IDENTIFIED BY时,口令字符串用改用口令的字面含义,GRANT将为你编码口令,不要象你用SET PASSWORD 那样使用password()函数。
7.WITH GRANT OPTION子句是可选的。如果你包含它,用户可以授予权限通过GRANT语句授权给其它用户。你可以用该子句给与其它用户授权的能力。
注:用户名、口令、数据库和表名在授权表记录中是大小写敏感的,而主机名和列名不是。

三、GRANT语句的种类
一般地,你可以通过询问几个简单的问题来识别GRANT语句的种类:
谁能连接,从那儿连接?
用户应该有什么级别的权限,他们适用于什么?
用户应该允许管理权限吗?
1.谁能连接,从那儿连接?
(1).你可以允许一个用户从特定的或一系列主机连接。

GRANT ALL ON db.* TO free@localhost  IDENTIFIED BY "123456";

说明:db.*意思是“db数据库的所有表
(2).你可能有一个经常外出并需要能从任何主机连接的用户free。在这种情况下,你可以允许他无论从哪里连接:

GRANT ALL ON db.* TO free@% IDENTIFIED BY "123456";

说明:“%”字符起通配符作用,与LIKE模式匹配的含义相同。在上述语句中,它意味着“任何主机”。所以free和free@%等价。这是建立用户最简单的方法,但也是最不安全的。
(3).你可以允许一个用户从一个受限的主机集合访问。例如,要允许mary从free.net域的任何主机连接,用一个%.free.net主机指定符:

GRANT ALL ON db.* TO mary@%.free.net IDENTIFIED BY "123456";

(4).如果你喜欢,用户标识符的主机部分可以用IP地址而不是一个主机名来给定。你可以指定一个IP地址或一个包含模式字符的地址,而且,从MySQL 3.23,你还可以指定具有指出用于网络号的位数的网络掩码的IP号:

GRANT ALL ON db.* TO free@192.168.12.10  IDENTIFIED BY "123456";
GRANT ALL ON db.* TO free@192.168.12.% IDENTIFIED BY "123456";
GRANT ALL ON db.* TO free@192.168.12.0/24  IDENTIFIED BY "123456";

说明:第一个例子指出用户能从其连接的特定主机,第二个指定对于C类子网192.168.12的IP模式,而第三条语句中,192.168.12.0/24指定一个24位网络号并匹配具有192.168.12头24位的IP地址。
(5).如果你指定的用户值报错,你可能需要使用引号(只将用户名和主机名部分分开加引号)。

GRANT ALL ON db.* TO "free"@"test.free.net"  IDENTIFIED BY "123456";

2.用户应该有什么级别的权限和它们应该适用于什么?
(1).你可以授权不同级别的权限,全局权限是最强大的,因为它们适用于任何数据库。要使free成为可做任何事情的超级用户,包括能授权给其它用户,发出下列语句:

GRANT ALL ON *.* TO free@localhost IDENTIFIED BY "123456" WITH GRANT OPTION;

说明:ON子句中的*.*意味着“所有数据库、所有表”。从安全考虑,我们指定free只能从本地连接。限制一个超级用户可以连接的主机通常是明智的,因为它限制了试图破解口令的主机。
有些权限(FILE、PROCESS、RELOAD和SHUTDOWN)是管理权限并且只能用”ON *.*”全局权限指定符授权。如果你愿意,你可以授权这些权限,而不授权数据库权限。例如,下列语句设置一个flush用户,他只能发出flush语句。这可能在你需要执行诸如清空日志等的管理脚本中会有用:

GRANT RELOAD ON *.* TO flushl@localhost IDENTIFIED BY "123456";

一般地,你想授权管理权限,吝啬点,因为拥有它们的用户可以影响你的服务器的操作。
(2).数据库级权限适用于一个特定数据库中的所有表,它们可通过使用ON db_name.*子句授予:

GRANT ALL ON db TO free@test.free.net INDETIFIED BY "123456";
GRANT SELECT ON db TO free@% INDETIFIED BY "123456";

说明:第一条语句向free授权db数据库中所有表的权限,第二条创建一个严格限制访问的用户free(只读用户),只能访问db数据库中的所有表,但只有读取,即用户只能发出SELECT语句。你可以列出一系列同时授予的各个权限。例如,如果你想让用户能读取并能修改现有数据库的内容,但不能创建新表或删除表,如下授予这些权限:

GRANT SELECT,INSERT,DELETE,UPDATE ON db TO free@test.net INDETIFIED BY "123456";

(3).对于更精致的访问控制,你可以在各个表上授权,或甚至在表的每个列上。当你想向用户隐藏一个表的部分时,或你想让一个用户只能修改特定的列时,列特定权限非常有用。如:

GRANT SELECT ON db.member TO free@localhost INDETIFIED BY "123456";
GRANT UPDATE (expiration) ON db. member TO free@localhost;

说明:第一条语句授予对整个member表的读权限并设置了一个口令,第二条语句增加了UPDATE权限,当只对expiration列。没必要再指定口令,因为第一条语句已经指定了。
(4).如果你想对多个列授予权限,指定一个用逗号分开的列表。例如,对free用户增加member表的地址字段的UPDATE权限,使用如下语句,新权限将加到用户已有的权限中:

GRANT UPDATE (street,city,state,zip) ON db TO free@localhost ;

说明:通常,你不想授予任何比用户确实需要的权限宽的权限。然而,当你想让用户能创建一个临时表以保存中间结果,但你又不想让他们在一个包含他们不应修改内容的数据库中这样做时,发生了要授予在一个数据库上的相对宽松的权限。你可以通过建立一个分开的数据库(如tmp)并授予开数据库上的所有权限来进行。例如,如果你想让来自test.net域中主机的任何用户使用tmp数据库,你可以发出这样的GRANT语句:

GRANT ALL ON tmp.* TO ""@test.net;

在你做完之后,用户可以创建并用tmp.tb_name形式引用tmp中的表(在用户指定符中的””创建一个匿名用户,任何用户均匹配空白用户名)。
3 用户应该被允许管理权限吗?
你可以允许一个数据库的拥有者通过授予数据库上的所有拥有者权限来控制数据库的访问,在授权时,指定WITH GRANT OPTION。例如:如果你想让free能从big.free.com域的任何主机连接并具有sales数据库中所有表的管理员权限,你可以用如下GRANT语句:

GRANT ALL ON sales.* TO free@%.big.free.com INDETIFIED BY "123456" WITH GRANT OPTION;

在效果上WITH GRANT OPTION子句允许你把访问授权的权利授予另一个用户。要注意,拥有GRANT权限的两个用户可以彼此授权。如果你只给予了第一个用户SELECT权限,而另一个用户有GRANT加上SELECT权限,那么第二个用户可以是第一个用户更“强大”。
四、撤权并删除用户
要取消一个用户的权限,使用REVOKE语句。REVOKE的语法非常类似于GRANT语句,除了TO用FROM取代并且没有INDETIFED BY和WITH GRANT OPTION子句:

mysql> help REVOKE 
Name: 'REVOKE'  
Description:  
Syntax:  
REVOKE  
    priv_type [(column_list)]  
      [, priv_type [(column_list)]] ...  
    ON [object_type] priv_level  
    FROM user [, user] ...

REVOKE 语句的语法看上去像这样,

REVOKE privileges (columns) ON what FROM user;  

1.user部分必须匹配原来GRANT语句的你想撤权的用户的user部分。
2.privileges部分不需匹配,你可以用GRANT语句授权,然后用REVOKE语句只撤消部分权限。
3.REVOKE语句只删除权限,而不删除用户。即使你撤销了所有权限,在user表中的用户记录依然保留,这意味着用户仍然可以连接服务器。要完全删除一个用户,你必须用一条DELETE语句明确从user表中删除用户记录,具体操作如下:

mysql>DELETE FROM user WHERE User="user_name" and Host="host_name";
mysql>FLUSH PRIVILEGES;

DELETE语句删除用户记录,而FLUSH语句告诉服务器重载授权表。(当你使用GRANT和REVOKE语句时,表自动重载,而你直接修改授权表时不是)。

MySQL服务器架构

#1,系统分区
文件系统创建
#2,文件存放
文件系统创建2
#3,编译依赖
文件系统创建3
#4,基本编译参数
文件系统创建4
生产环境关闭debug,embedded_server,layout用standle.
#5,cmake && make -j && make install

CFLAGS="-O3 -g -fno-exceptions -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing"
CXX=g++
CXXFLAGS="-O3 -g -fno-exceptions -fno-rtti -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing"
export CFLAGS CXX CXXFLAGS
cmake . [PARAMETERS]
make -j & make install

#5.TPC-C压力测试模型:
文件系统创建5
系统I/O压力测试工具:fio
fio是系统IO测试的工具,覆盖多种不同类型、不同方式的IO测试,并且简单易用。fio在压力测试中,常用于了解不同文件操作的IOPS极限,可以更加全面的了解系统IO处理能力。
1、编译安装
fio是开源的工具,可以在官方网址 http://freecode.com/projects/fio 查看相关的内容,源码编译和安装如下所示:
1.1、安装依赖

yum install libaio
yum install libaio-devel

1.2、获取源码

wget http://brick.kernel.dk/snaps/fio-2.1.7.tar.bz2

1.3、编译安装

tar -xjf fio-2.1.7.tar.bz2; cd fio-2.1.7
./configure 
make & make install

2、使用说明
fio工具支持多种类型的测试,并且参数非常多,可以通过帮助文档获得使用信息。以下内容简单说明如何查看帮助文档。

主要参数说明:
--help:获得帮助信息。
--cmdhelp:获得命令的帮助文档。
--enghelp:获得引擎的帮助文档。
--debug:通过debug方式查看测试的详细信息。(process, file, io, mem, blktrace, verify, random, parse, diskutil, job, mutex, profile, time, net, rate)
--output:测试结果输出到文件。
--output-format:设置输出格式。(terse, json, normal)
--crctest:测试crc性能。(md5, crc64, crc32, crc32c, crc16, crc7, sha1, sha256, sha512, xxhash:)
--cpuclock-test	:CPU始终测试。

3、测试
fio测试的类型和选项非常多,但是通常情况下,对于MySQL数据库服务器,一般测试随机读、随机写、随机读写三种情况下,sync、fsync的ioengine方式下,IO的性能指标情况。通过测试这些指标,可以对系统的IO处理能力进行资源评估和分配。

fio -filename=/dev/sdb1 -direct=1 -iodepth 1 -rw=randread -ioengine=sync -bs=16k -size=200G -numjobs=10 -runtime=1000 -group_reporting -name=mytest

参数说明
filename:测试的系统盘目录。
direct:测试绕过机器自带的buffer,使测试结果更真实。
iodepth:设置IO队列的深度。
rw:测试读写类型。
ioengine:io引擎方式。
bs:数据块大小。
size:测试文件的大小。
numjobs:测试次数。
runtime:测试时间。
rwmixwrite:在混合读写的模式下,写占的权重。
group_reporting:测试结果汇总每个进程的信息。
lockmem:测试使用内存大小。
zero_buffers:测试过程使用0初始化系统buffer。
nrfiles:测试过程中每个进程生成文件的数量。
4、测试结果
测试结果显示了详细的系统信息,包括io、latency、bandwidth、cpu等信息,详细如下所示:

mytest: (groupid=0, jobs=10): err= 0: pid=10775: Thu Jun 12 08:47:28 2014
  read : io=262560KB, bw=22965KB/s, iops=1435, runt= 11433msec
    clat (usec): min=108, max=57601, avg=488.40, stdev=483.77
     lat (usec): min=108, max=57601, avg=488.62, stdev=483.78
    clat percentiles (usec):
     |  1.00th=[  112],  5.00th=[  314], 10.00th=[  334], 20.00th=[  414],
     | 30.00th=[  442], 40.00th=[  462], 50.00th=[  486], 60.00th=[  506],
     | 70.00th=[  532], 80.00th=[  556], 90.00th=[  620], 95.00th=[  684],
     | 99.00th=[  812], 99.50th=[  868], 99.90th=[ 1144], 99.95th=[ 5024],
     | 99.99th=[10048]
    bw (KB  /s): min=    1, max=42144, per=83.52%, avg=19179.29, stdev=16716.64
    lat (usec) : 250=3.02%, 500=53.40%, 750=41.37%, 1000=2.04%
    lat (msec) : 2=0.08%, 4=0.03%, 10=0.05%, 100=0.01%
  cpu          : usr=0.05%, sys=88.55%, ctx=17703, majf=0, minf=379
  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued    : total=r=16410/w=0/d=0, short=r=0/w=0/d=0
     latency   : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):
   READ: io=262560KB, aggrb=22965KB/s, minb=22965KB/s, maxb=22965KB/s, mint=11433msec, maxt=11433msec

Disk stats (read/write):
  sdb: ios=16418/55, merge=57/69, ticks=7780/39, in_queue=7816, util=18.80%

MySQL的左右内连接面试题

题目如下:
mysql左右连接

解题思路:解构,分拆.把复杂的问题分拆成简单问题逐个处理.

先建表:

create table matchtable(
matchid int primary key auto_increment not null,
hostteamid int not null default 0,
guestteamid int not null default 0,
matchresult varchar(10) not null default '',
matchtime date not null default '2006-06-06'
)charset utf8;

insert into matchtable
(hostteamid,guestteamid,matchresult,matchtime)
values
(1,2,'2:0','2006-05-21'),
(2,3,'1:2','2006-06-21'),
(3,1,'2:5','2006-06-25'),
(3,2,'0:0','2006-07-01'),
(2,1,'3:2','2006-07-21');

create table teamtable(
teamid int primary key auto_increment not null,
teamname varchar(10) not null default ''
)charset utf8;

insert into teamtable
(teamname)
values
('国安'),
('申花'),
('恒大');

#1,先筛选出比赛结果:

select hostteamid,guestteamid,matchresult,matchtime from matchtable;

join1
#2,两表连查,分别筛选匹配出对应的主客队ID,队名:

select hostteamid,teamname as hostteam,matchresult,guestteamid,matchtime
from matchtable left join teamtable as hostteamtable
on matchtable.hostteamid=hostteamtable.teamid;

join2

select hostteamid,matchresult,guestteamid,teamname as guestteam,matchtime
from matchtable left join teamtable as guestteamtable
on matchtable.hostteamid=guestteamtable.teamid;


#3,把#2中两表连查出来的两张表组合成一张主客队对战结果表:

select hostteamid,hostteamtable.teamname as hostteam,matchresult,guestteamid,guestteamtable.teamname as guestteam,matchtime
from matchtable left join teamtable as hostteamtable
on matchtable.hostteamid=hostteamtable.teamid
left join teamtable as guestteamtable 
on matchtable.hostteamid=guestteamtable.teamid;


因为hostteamid和guestteamid都是到teamtable里边去对应teamid,所以要分别起别名以区分匹配字段.
#4,最后对#3的结果进行排列筛选:

select hostteamtable.teamname as hostteam,matchresult,guestteamtable.teamname as guestteam,matchtime
from matchtable left join teamtable as hostteamtable
on matchtable.hostteamid=hostteamtable.teamid
left join teamtable as guestteamtable 
on matchtable.guestteamid=guestteamtable.teamid
where matchtime between '2006-06-01' and '2006-07-01';

join5

MySQL having的运用

假设在stu表内有成绩表如下:
QQ图片20160101215857
试查询两门以及两门以上不及格同学的平均分,只允许使用一个select,不使用子查询等.

解题思路:反向思维.
选算出所有人的平均分

select name,avg(score) from stu group by name;

QQ图片20160101220605

然后根据name分组找出不同人挂的科目数量:

select name,sum(score<60) from stu group by name;

QQ图片20160101221059
这里用sum而不是用count,因为count(*)只是算出行数,不管在()里边给予什么值或者条件都不起作用.score<60出来的结果中,”张三”这个name的结果为一个0两个1,sum结果为2,count结果为3.

最后用sum的值作为条件来过滤筛选出最终结果:

select name,sum(score<60) as failnum,avg(score) from stu group by name having failnum>=2;

QQ图片20160101222317