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()函数返回当月的第几天。。