当前位置:Gxlcms > mysql > MySQl心得4

MySQl心得4

时间:2021-07-01 10:21:17 帮助过:28人阅读

MySQL中纯数的记录在程序中可以不加单引号;如果字符型的记录中只有数字则也可以不加单引号;as 语句后面的记录加不加单引号都一样。 1.语法式: select [all | distinct | distinctrow ] [high_priority] [straight_join] [sql_small_result] [sql_big_resu

MySQL中纯数值的记录在程序中可以不加单引号;如果字符型的记录中只有数字则也可以不加单引号;as 语句后面的记录加不加单引号都一样。

1.语法格式:

select

[all | distinct | distinctrow ]

[high_priority]

[straight_join]

[sql_small_result] [sql_big_result] [sql_buffer_result]

[SQL_cache | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr, ...

[into outfile 'name' export_options | INTO dumpfile 'name']

[from table_reference [ ,table_reference] …] /*FROM子句*/

[wherewhere_definition] /*WHERE子句*/

[group by{col_name| expr | position} [asc|desc], ... [with rollup]] /*GROUP BY子句*/

[having where_definition] /* HAVING 子句*/

[order by{col_name | expr | position} [ASC| DESC] , ...] /*ORDER BY子句*/

[limt {[offset,] row_count | row_count OFFSET offset}] /*LIMIT子句*/

说明:

从这个基本语法可以看出,最简单的SELECT语句是SELECTselect_expr,利用这个最简单的SELECT语句,可以进行MySQL所支持的任何运算,例如,SELECT 1+1,它将返回2。

SELECT关键词的后面可以使用很多的选项:

ALL | distinct | DISTINCTROW:这几个选项指定是否重复行应被返回。如果这些选项没有被给定,则默认值为ALL(所有的匹配行被返回)。DISTINCT和 DISTINCTROW是同义词,用于消除结果集合中的重复行。

● HIGH_PRIORITY,STRAIGHT_JOIN和以SQL_为开头的选项都是MySQL相对于标准SQL的扩展,这些选项在多数情况下可以选择不使用

● high_priority:给予SELECT更高的优先权,使查询立刻执行,加快查询速度。

straight_join:用于促使MySQL优化器把表联合在一起,加快查询速度。

sql_small_result:可以与GROUP BY或DISTINCT同时使用,来告知MySQL优化器结果集合是较小的。在此情况下,MySQL使用快速临时表来储存生成的表,不使用分类。

SQL_buffer_RESULT:促使结果被放入一个临时表中。这可以帮助MySQL提前解开表锁定,在需要花费较长时间的情况下,也可以帮助把结果集合发送到客户端中。

● SQL_BIG_RESULT:可以与GROUPBY或DISTINCT同时使用,来告知MySQL优化器结果集合有很多行。在这种情况下,MySQL会优先进行分类,不优先使用临时表。

SQL_cache:告知MySQL把查询结果存储在查询缓存中。对于使用UNION的查询或子查询,本选项会影响查询中的所有SELECT。

● SQL_NO_CACHE:告知MySQL不要把查询结果存储在查询缓存中

● SQL_CALC_FOUND_ROWS:告知MySQL计算有多少行应位于结果集合中,不考虑任何LIMIT子句。

● SELECT…INTO OUTFILE 'name':这个语句可以将表中的行导出到一个文件中,这个文件被创建在服务器主机中,name为文件名。

所有被使用的子句必须按语法说明中显示的顺序严格地排序。例如,一个HAVING子句必须位于GROUP BY子句之后,并位于ORDER BY子句之前。

2. 消除结果集中的重复行

对表只选择其某些列时,可能会出现重复行。例如,若对XSCJ数据库的XS表只选择专业名和总学分,则出现多行重复的情况。可以使用distinct或distinctrow关键字消除结果集中的重复行,其格式是:select distinct|distinctrow 字段名1 [ , 字段名2…]

其含义是对结果集中的重复行只选择一个,保证行的唯一性

例:对XSCJ数据库的XS表只选择专业名和总学分,消除结果集中的重复行。 select distinct 专业名,总学分 from xs;

2. 聚合函数(aggregationfunction)(分组:分类汇总)

SELECT子句的表达式中还可以包含所谓的聚合函数。聚合函数常常用于对一组值进行计算,然后返回单个值。除COUNT函数外,聚合函数都会忽略空值。聚合函数通常与GROUP BY子句一起使用,但是不能与where语句一起使用。如果SELECT语句中有一个GROUP BY子句,则这个聚合函数对所有列起作用,如果没有,则SELECT语句只产生一行作为结果。

函 数 名

说 明

COUNT

求组中项数,返回int类型整数

MAX

求最大值

MIN

求最小值

SUM

返回表达式中所有值的和

AVG

求组中值的平均值

STD或STDDEV

返回给定表达式中所有值的标准差

VARIANCE

返回给定表达式中所有值的方差

GROUP_CONCAT

返回由属于一组的列值连接组合而成的结果

BIT_AND

逻辑或

BIT_OR

逻辑与

BIT_XOR

逻辑异或

1)COUNT函数

聚合函数中最经常使用的是COUNT()函数,用于统计组中满足条件的行数或总行数,返回SELECT语句检索到的行中非NULL值的数目,若找不到匹配的行,则返回0。

语法格式为:COUNT ( { [ ALL | DISTINCT ] expression } | * )

其中,expression是一个表达式,其数据类型是除BLOB或TEXT之外的任何类型。ALL表示对所有值进行运算,DISTINCT表示去除重复值,默认为ALL。使用COUNT(*)时将返回检索行的总数目,不论其是否包含 NULL值。

count(*):统计记录的条数(包括null);

count(字段名):统计字段中有值的记录个数(不包括null);

count(distinct 字段名):去掉重复后再统计字段中有值的记录个数(也不包括null)

例1:求学生的总人数。

SELECT COUNT(*) AS '学生总数' FROMXS;

例2. 统计备注不为空的学生数目。

SELECT COUNT(备注)AS '备注不为空的学生数目' FROM XS;

注意:这里COUNT(备注)计算时备注为NULL的行被忽略

例3: 统计总学分在50分以上的人数。

SELECT COUNT(总学分) AS '总学分50分以上的人数'

FROM XS WHERE 总学分>50;

(2)MAX和MIN

MAX和MIN分别用于求表达式中所有值项的最大值与最小值,语法格式为:MAX / MIN ( [ ALL | DISTINCT ] expression )其中,expression是常量、列、函数或表达式,其数据类型可以是数字、字符和时间日期类型。

例:求选修101课程的学生的最高分和最低分。

SELECT MAX(成绩), MIN(成绩)

FROM XS_KC WHERE 课程号 ='101';

注意:当给定列上只有空值或检索出的中间结果为空时,MAX和MIN函数的值也为空。

(3)SUM函数和AVG函数

SUM和AVG分别用于求表达式中所有值项的总和与平均值,语法格式为:SUM / AVG ( [ ALL | DISTINCT ] expression )其中,expression是常量、列、函数或表达式,其数据类型只能是数值型数据。

例:求学号081101的学生所学课程的总成绩。

SELECT SUM(成绩) AS '课程总成绩'

FROM XS_KC WHERE 学号 ='081101';

(4)variance和stddev(STD)函数

variance和stddev函数分别用于计算特定的表达式中的所有值的方差和标准差。语法格式:

VARIANCE / STDDEV ( [ ALL | DISTINCT ]expression )

说明:方差的计算按照以下几个步骤进行。

① 计算相关列的平均值;

② 求列中的每一个值和平均值的差;

③ 计算差值的平方的总和;

④ 用总和除以(列中的)值得结果。

STDDEV函数用于计算标准差。标准差等于方差的平均根。所以,stddev(…)和sqrt(VARIANCE(…))这两个表达式是相等的。

STDDEV可以缩写为STD,这对结果没有影响。

(5)group_concat函数

MySQL支持一个特殊的聚合函数group_concat函数。该函数返回来自一个组指定列的所有非NULL值,这些值一个接着一个放置,中间用逗号隔开,并表示为一个长长的字符串。这个字符串的长度是有限制的,标准值是1024。

语法格式为:GROUP_CONCAT({[ALL|DISTINCT] expression }| * )

(6)BIT_AND、BIT_OR和BIT_XOR

与二进制运算符|(或)、&(与)和^(异或)相对应的聚合函数也存在,分别是BIT_OR 、BIT_AND、BIT_XOR。例如,函数BIT_OR在一列中的所有值上执行一个二进制OR。

语法格式为: BIT_AND |BIT_OR | BIT_XOR( { [ ALL | DISTINCT ] expression } | * )

例: 有一个表BITS,其中有一列bin_value上有3个integer值:1、3、7,获取在该列上执行BIT_OR的结果,使用如下语句:

SELECT BIN(BIT_OR(bin_value)) FROM BITS;

说明:MySQL在后台执行如下表达式:(001|011)|111,结果为111。 其中BIN函数用于将结果转换为二进制位。

3. 范围比较

用于范围比较的关键字有两个:between和in。

当要查询的条件是某个值的范围时,可以使用between关键字。BETWEEN关键字指出查询范围,格式为:

expression [ NOT ] between expression1 andexpression2

当不使用NOT时,若表达式expression的值在表达式expression1与expression2之间(包括这两个值,相反,使用not时,不包括这两个值),则返回TRUE,否则返回FALSE;使用NOT时,返回值刚好相反。

注意:expression1的值不能大于expression2的值。

使用IN关键字可以指定一个值表,值表中列出所有可能的值,当与值表中的任一个匹配时,即返回TRUE,否则返回FALSE。使用IN关键字指定值表的格式为:expression IN ( expression [,…n])

例:前面那个求1900年出生的学生的第三种做法;

SELECT 学号, 姓名, 专业名, 出生日期 FROM XS

WHERE 出生日期 NOT BETWEEN '1990-01-01'and '1990-12-31';

例:查询XS表中专业名为“计算机”、“通信工程”或“无线电”的学生的情况。

SELECT * FROM XS

WHERE 专业名 IN ('计算机', '通信工程', '无线电');

该语句与下列语句等价:

SELECT * FROM XS

WHERE 专业名 ='计算机' OR 专业名 = '通信工程' OR 专业名 = '无线电';

说明:IN关键字最主要的作用是表达子查询。

4. 空值比较

当需要判定一个表达式的值是否为空值时,使用IS NULL关键字,格式为: expression IS [ NOT ] NULL

当不使用NOT时,若表达式expression的值为空值,返回TRUE,否则返回FALSE;当使用NOT时,结果刚好相反。

例: 查询XSCJ数据库中总学分尚不定的学生情况。

SELECT * FROM XS

WHERE 总学分 IS NULL;

本例即查找总学分为空的学生,结果为空。

5. group by子句主要用于根据字段对行分组。

例如,根据学生所学的专业对XS表中的所有行分组,结果是每个专业的学生成为一组。group by子句的语法格式如下:group by {col_name | expr| position} [ASC | DESC], ... [WITH ROLLUP]

说明:

group by子句后通常包含列名或表达式。MySQL对GROUP BY子句进行了扩展,可以在列的后面指定ASC(升序)或DESC(降序)。GROUP BY可以根据一个或多个列进行分组,也可以根据表达式进行分组,经常和聚合函数一起使用。

例1:将XSCJ数据库中各专业名输出。

SELECT 专业名 FROM XS GROUP BY 专业名;

例2: 求XSCJ数据库中各专业的学生数。

SELECT 专业名,COUNT(*) AS '学生数'

FROM XS GROUP BY 专业名;

使用带rollup操作符的GROUP BY子句: 指定在结果集内不仅包含由 GROUP BY 提供的正常行,还包含汇总行。

例: 在XSCJ数据库上产生一个结果集,包括每个专业的男生人数、女生人数、总人数,以及学生总人数。

SELECT 专业名, 性别, COUNT(*) AS '人数'

FROM XS GROUP BY 专业名,性别 with rollup;

使用了ROLLUP操作符后,将对GROUP BY子句中所指定的各列产生汇总行,产生的规则是:按列的排列的逆序依次进行汇总。如本例根据专业名和性别将XS表分为4组,使用ROLLUP后,先对性别字段产生了汇总行(针对专业名相同的行),然后对专业名与性别均不同的值产生了汇总行。所产生的汇总行中对应具有不同列值的字段值将置为NULL。

6. having子句(使用分组后在进行挑选)

使用having子句的目的与where子句类似,having与where的区别:WHERE子句是用来在FROM子句之后选择行,where是对原始记录进行挑选;而HAVING子句用来在GROUP BY子句后选择行,对分组后的记录进行挑选。

语法格式: having 选择条件;

条件的定义和WHERE子句中的条件类似,不过HAVING子句中的条件可以包含聚合函数,而WHERE子句中则不可以。

SQL标准要求HAVING必须引用GROUP BY子句中的列或用于聚合函数中的列。不过,MySQL支持对此工作性质的扩展,并允许HAVING引用SELECT清单中的列和外部子查询中的列。

例: 查找XSCJ数据库中平均成绩在85分以上的学生的学号和平均成绩。

SELECT 学号, AVG(成绩) AS '平均成绩'

FROM XS_KC GROUP BY 学号 HAVING AVG(成绩) >=85;

7. order by字句

在一条SELECT语句中,如果不使用order by子句,结果中行的顺序是不可预料的。使用ORDER BY子句后可以保证结果中的行按一定顺序排列。 语法格式:ORDER BY {col_name | expr | position} [ASC | DESC] , ...

说明:ORDER BY子句后可以是一个列、一个表达式或一个正整数。正整数表示按结果表中该位置上的列排序。关键字asc表示升序排列,desc表示降序排列,系统默认值为ASC。

例: 将通信工程专业的学生按出生日期先后排序。

SELECT 学号,姓名,专业名,出生日期 FROM XS

WHERE 专业名 = '通信工程' ORDER BY 出生日期;

注意:当对空值排序时,ORDER BY子句将空值作为最小值对待,按升序排列的话将空值放在最上方,降序放在最下方。

如果是按多个字段排序,先按第一个字段排,当第一个字段的值相同时,再按第二个排;如果第一个字段都不相同,则第二个字段用不着;order by语句在limit语句之前。

8. limit字句

LIMIT子句是SELECT语句的最后一个子句,主要用于限制被SELECT语句返回的行数。 语法格式:LIMIT {[offset,]row_count | row_count OFFSET offset}

说明:如果记录特别多可以用limit语句分页!

语法格式中的offset和row_count都必须是非负的整数常数,offset指定返回的第一行的偏移量,row_count是返回的行数。例如,“LIMIT 5”表示返回SELECT语句的结果集中最前面5行,而“LIMIT3,5”则表示从第4行开始返回5行。

值得注意的是初始行的偏移量为0而不是1。

例: 查找XS表中学号最靠前的5位学生的信息。

SELECT 学号, 姓名, 专业名, 性别, 出生日期, 总学分

FROM XS ORDER BY 学号 LIMIT 5;

为了与PostgreSQL兼容,MySQL也支持LIMIT row_count OFFSET offset语法。所以将上面例子中的LIMIT子句换成“limit 5 offset 3”,结果一样。

人气教程排行