分类： Mysql/postgreSQL
2008-02-16 16:32:46
常用的查询格式如下:
SELECT columnsFROM tables[WHERE conditions][GROUP BY group[HAVING group_conditions]][ORDER BY sort_columns][LIMIT limits];
Select的语法下一章介绍。
跨数据库的查询方式：
select namefrom employee.employee;select employee.employee.namefrom employee;
后者要求选定数据库employee，前者只要进入mysql就可以。
可以使用别名,如下
mysql> select name as
employeeName
-> from employee;
+---------------+
| employeeName |
+---------------+
| Ajay Patel |
| Nora Edwards |
| Candy Burnett |
| Ben Smith |
| Meil |
| 0 |
+---------------+
6 rows in set (0.00 sec)
别名在多表查询时使用会比较多。
select e.namefrom employee as e;
别名中的as是可以省略的，这样做是为了和其他语言兼容。
select e.namefrom employee as e;
select employeeID, namefrom employeewhere job='Programmer'; +------------+--------------+
| employeeID | name |
+------------+--------------+
| 6651 | Ajay Patel |
| 7513 | Nora Edwards |
+------------+--------------+
2 rows in set (0.00 sec)
注意其他语言中使用＝＝来判断等于，这里仅仅有＝。其他符号还有!= or <>，>，<，>=，<=，IS NULL，IS NOT
NULL, AND, OR, and NOT，count()
select count(*) from employee;
mysql>
select count(*) from employee;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set
(0.00 sec)
使用where 子句的时候不允许使用列别名，这是ANSI SQL的限制。因为检查WHERE条件时，还未必知道这个别名。
select jobfrom employee;
执行结果：
+-----------------------+
| job |
+-----------------------+
| Programmer |
| Programmer |
| Systems Administrator |
| DBA |
| NULL |
| NULL |
+-----------------------+
select distinct jobfrom employee;执行结果
+-----------------------+
| job |
+-----------------------+
| Programmer |
| Systems Administrator |
| DBA |
| NULL |
+-----------------------+
select count(distinct job) from employee;
select count(job) from employee;
GROUP BY 按组来获取数据,一般与函数配合使用.
mysql> select count(*), job
-> from employee
-> group by job;
+----------+-----------------------+
| count(*) | job |
+----------+-----------------------+
| 1 | DBA |
| 2 | Programmer |
| 1 | Systems Administrator |
+----------+-----------------------+
3 rows in set (0.00 sec)
ANSI SQL 和MYSQL处理GROUP BY不同.前者要根据select列中选出来的所有列来分组.MYSQL则可以,甚至还可以给分组排序.
mysql> select count(*), job
-> from employee
-> group by job desc;
+----------+-----------------------+
| count(*) | job |
+----------+-----------------------+
| 1 | Systems Administrator |
| 2 | Programmer |
| 1 | DBA |
+----------+-----------------------+
3 rows in set (0.00 sec)
HAVING 一般是针对组,它是组中的where语句.
select count(*), jobfrom employeegroup by jobhaving count(*)=1;以上为雇员职业数为1的查询 select *from employeeorder by job asc, name desc;
执行结果
+-------------+---------------+-----------------------+---------------+| employeeID | name | job | departmentID |+-------------+---------------+-----------------------+---------------+| 9842 | Ben Smith | DBA | 42 || 7513 | Nora Edwards | Programmer | 128 || 6651 | Ajay Patel | Programmer | 128 || 9006 | Candy Burnett | Systems Administrator | 128 |+-------------+---------------+-----------------------+---------------+4 rows in set (0.02 sec)
选取6-8行,可以和order by配合选取最后面的
select *from employeeSkillslimit 5, 3; 从第5个开始,选3个,即选取6-8,是从0开始计数的.
The SELECT statement has the following general form:
SELECT columns
FROM tables
[WHERE conditions]
[GROUP BY group
[HAVING group_conditions]]
[ORDER BY sort_columns]
[LIMIT limits];
The clause select * retrieves all columns; select columnname retrieves a particular column.
We can specify tables as database.table and columns as table.column or database.table.column to avoid ambiguity.
Aliases are alternative names for tables and columns. Specify them this way:
select column as column_alias
from table as table_alias;
The WHERE clause is used to select rows matching search criteria.
The keyword DISTINCT removes duplicates from the result set.
The GROUP BY clause treats the rows retrieved group by group. Its chief use is in conjunction with group functions like count().
The HAVING clause is like a WHERE clause for groups.
The ORDER BY clause sorts result rows according to the columns you specify.
The LIMIT clause is used to control which rows are returned from the total possible result set. You can specify the maximum rows returned and an offset from which to start.