mysql笔记(6)--select高级查询(一)

1.表和列的别名:

(1) mysql> SELECT name,species FROM pet ORDER BY name, species;

mysql> SELECT name,species FROM pet ORDER BY 1,2;

mysql> SELECT name AS n,species AS s FROM pet ORDER BY n,s;

   mysql> SELECT name n,species s FROM pet ORDER BY n,s;

用列的别名构造新表: mysql> CREATE TABLE pet1

                           -> SELECT name (AS) animal,owner (AS) child

                            -> FROM pet;

你可以在GROUP BY、ORDER BY或在HAVING部分中使用别名引用列。注意,你的ANSI SQL 不允许你在一个WHERE子句中引用一个别名。

select name n, count(*)as total from xindi group by n;//正确

select phone p from xindi where p>123;//错误

 

(2)表的别名具体方法类似于列。

列的别名经常用于表自身的连接中。你不必有2个不同的表来执行一个联结。如果你想要将一个表的记录与同一个表的其他记录进行比较,联结一个表到自身有时是有用的。

   pet联结自身来进行相似种类的雄雌配对:

   mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species

       -> FROM pet AS p1, pet AS p2

       -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";


2.where ,group by,having 用法小注:select name,sum(phone) from xindi group by phone having sum(phone)>1233;

执行where子句查找符合条件的数据;

使用group by 子句对数据进行分组;

对group by 子句形成的组运行聚集函数计算每一组的值;

最后用having 子句去掉不符合条件的组。

需要注意:having 子句中的每一个元素也必须出现在select列表中。有些数据库例外,如oracle.

having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。

having子句限制的是组,而不是行。where子句中不能使用聚集函数,而having子句中可以。

select dno,sum(salary)from employe group by dno having sum(salary)>10000


3.limit,concat的用法,分离year,month,day

(1)limit的用法

mysql> select * from xindi order by phone limit 2; //只取前两个值

+-------+-------+-------+----+

| name | phone | email | id |

+-------+-------+-------+----+

| brown | NULL | NULL | 5 |

| lili |   123 | NULL | 6 |

+-------+-------+-------+----+

2 rows in set (0.01 sec)


mysql> select * from xindi order by phone limit 2,2; //只取第三和第四个值

+------+-------+-------+----+

| name | phone | email | id |

+------+-------+-------+----+

| lili |   123 | NULL | 7 |

| Lucy | 13456 | NULL | 3 |

+------+-------+-------+----+

2 rows in set (0.00 sec)

(2) concat

select concat(name,email)from xindi;

select concat(name,email) as name from xindi;

concat()函数把这个值转化为字符串,无论输出参数是一个字符串还是一个数值, concat() 都会返回一个字符串.

(3) 三个函数year(),month(),dayofmonth()可以分别分离出日期中的年月日来。

mysql> create table stu(id int primary key,name char(10),birth date,death date);

insert stu values(1,'lili','2000-01-01','2010-01-01');

insert stu values(2,'lucy','2002-1-1','2009-1-1');

select * from stu where death>'2009-12-31';

select * from stu where month(birth)=1;

select * from stu where year(birth)>2000;

select * from stu where dayofmonth(birth)>1;

select * from stu where dayofmonth(birth)>=1;

dayofmonth()函数返回当月的第几天。。

Posted in 数据库 by 傻猫 at March 1, 2012.
Tags: mysql, 高级查询

添加新评论