Mysql比知必会系列教程(六) --------Mysql基本信息检索

Mysql基本信息检索

select 语句的语法实例：

select select_list

from table_list

where row_constrainst

group by grouping_columns

order by sorting_columns

having group_constaint

limint count;

指定检索条件-where子句

select * from score where score>95

字符串的比较操作通常是不区分大小写的。

select last name,firstname from president where lastname=‘ABC’;

select last name,firstname from president where lastname=‘abc’;结果一致

null值

null是个特殊的值。他的含义是“无数据，”或者“未知数据”，所以能用它与“有数据”的值进行比较。

select null<0,null=0,null<>0,null>0;

select lastname from president where death is null;

Order By子句

order by 子句默认排序是升序序列。

select lastname from president order by lastname desc;

对于null值的数据行，如果采用的升序desc排列他们讲出现在结果的开头。如果是降序排列则他们将出现在结果的末尾。

select lastname,firstname.death from president order by If(death is null,0,1),death desc;

IF函数的作用是对其紧随其后的表达式进行求值，结果为真则是第一个值，为假则是第二个值。

select lastname,firstname.death from president order by Rand() limit 3;随机出现3条

对输出列进行求值和命名

mysql允许将表达式的计算结果当做输出列的值，而不引用数据表。表达式可以简单也可以复杂。

select 17,Format(sqrt(25+13),3)生成平方根并将结果按3位小数表示

select concat(fisrtname,’ ',lastname) as ‘president name’ from president;连接属性值并取别名。

日期有关的问题

查找日期

select * from grade_event where date=‘2018-09-01’

select lastname,firstname.death from president where death >= ‘1970-01-01’ and death < ‘1980-01-01’;

将日期中的年月日分离出来 用函数 YEAR(),MONTH(),DAYOFMONTH()。

select last_name,first_name,birth from president where month(birth)=3;

select last_name,first_name,birth from president where month(birth)=3 and DayOfMonth(birth)=29;查询出birth为3-29的记录

查询与“今天”有关的日期

select last_name,first_name,birth from president where month(birth)=month(curdate()) and DayOfMonth(birth)=DayOfMonth(curdate());

Timestampdiff()函数：用于指定计算结果的单位 计算两个时间间隔。

select lastname,firstname,birth,death, timestampdiff(year,birth,death) as age from president where death is not null order by age desc limit5 这样既有可以计算出年龄。death-birth;

TO_DAYS函数将日期转换为天数。可计算出据某个特定日期还有多少时间。比如查询60天以内的。

select lastname,firstname,expriation from member where (TO_DAYS(expriation)-TO_DAYS(CURDATE()))<60;

使用TImeStampdiff()函数

select lastname,firstname,expriation from member where timestampdiff(DAY,CURDATE(),expiration)<60;

日期使用DATE_ADD() 或DATE_SUB()函数来完成。这两个函数的输入参数是一个日期值和一个时间间隔值，返回结果则是一个新日期。

Select DATA_ADD(‘1970-1-1’,INTERVAL 10 Year) --输出为–>1980-01-01

Select DATA_sub(‘1970-1-1’,INTERVAL 10 Year) --输出为–>1960-01-01

模式匹配 like

like和not like 提供了包含通配符的字符串，“_” 只能匹配一个字符，百分号“%”则能匹配任何一个字符序列包括空序列在内

如何设置和使用sql变量

mysql允许自定义变量。可以使用查询结果设置变量。变量命名语法：

“@变量名” 赋值语法是在select语句中使用一个”@变量名:=值“的形式的表达式。

比如:select @birth :=birth from president where last_name=‘jack’ and first_name=‘asd’;---->

@birth:=birth
1765-03-15

再次使用变量

select last_name,first_name,birth from president where birth<@birth order by birth;

last_name	first_name	birth
		1732-02-22

sql语句中也可以用来对变量进行赋值。“=”和“:=”都可以进行对其赋值操作符

set @today=curdate();

set @oneweek_ago :=Date_sub(@today,INTERVAL 7 day);

select @today,oneweek_ago;

通过上面可以查出变量的值

如何生成统计信息

Distinct

可以将查出来的结果重复出现的数据行清除掉。

select distinct state from president order by state;

Count,Group by

count是将所有的数据行进统计出来。

select count(*) from member;

count(*)统计的结果是被选中的行数据的总数。count(列名) 统计全体非Null 值的个数。

select count(*),count(eamil),count(expiration) from meber;

count(*)	count(eamil	count(expiration)
102	80	96

count和distinct联合起来统计查询结果里有多少种不同的非null值。

select count(distinct state) from president;

通过count和Group by 子句可以用一个查询查询到某数据列里不同值分别出现的个数

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

sex	count(*)
F	14
M	16

能够统计出这样的结果是Group BY子句必不可少，他可以让数据进行分类。如果不加会出现错误。

使用Group by 与count(*)结合优点如下：

• 在开始统计前，我们不必知道被统计的数据有多少不同的取值。
• 我们只需使用一个而不是好几个查询
• 因为只用一个查询就能把所有的结果查找出来，而且可以进行排序、

select state,count(*) as count from president Group by state order by count desc;

Having子句与Where

having和where都是用来设定查询条件的。输出的行必须符合这些查询条件。

count()之类的汇总函数的计算结果允许在having子句中出现。

select state,count(*) as count from president Group by state having count>1 order by count desc;

带有having子句的查询特别适合用来查找某个数据列重复出现的值

其他

min() 最小值

max() 最大值

sum() 总和

avg() 平均

select event_id min(score) as minimum,max(score) as maximum, max(score)-min(score)+1 as span,sum(score) as total, AVG(score) as average,Count(score) as count from score Group BY event_id;

超级聚合值：with RollUP子句

select sex,count(*) from student Group by sex width rollup;

with rollup还可以结合其他聚合函数使用：

// 统计结果可出现一行对每一列的统计信息
select event_id min(score) as minimum,max(score) as maximum, max(score)-min(score)+1 as span,sum(score) as total, AVG(score) as average,Count(score) as count from score Group BY event_id width rollup;
// 如果group by 子句指定了多列，width rollup 会生成另外的包含高级统计值的超级聚合行。