一select语法
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 [ select_expr ]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC] [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC] ]
[LIMIT {[offset] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE file_name
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE file_name
| INTO var_name [ var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
简化一下语法如下
select columncolumn
from tabletable
[where condition]
[group by …]
[having …]
[order by …]
例如下面例子
mysql> create table jokes(
> id int unsigned not null auto_increment primary key
> joketext text
> jokedate date
> )engine=myisam charset=utf;
mysql> insert into jokes values(why not?now());
#计算列（select 用于检索从一个或多个表中选取出的行Select也可以被用于检索没有引用任何表的计算列）
select + as total; //结果为
++
| total |
++
| |
++
#从表Jokes中挑选所有的东西
select * from jokes;
++++
| id | joketext | jokedate |
++++
| | why not? | |
++++
#选择感兴趣的列(投影)
select id jokedate from jokes;
#我们是不是可以多少显示一点笑话正文的内容呢？(预览)
mysql> select idleft(joketext) as contentjokedate from jokes;
++++
| id | content | jokedate |
++++
| | why | |
++++
#统计表中记录数
mysql> select count(*) as records from jokes;
++
| records |
++
| |
++
#统计年月日之后录入的笑话记录数
select count(*) from jokes where jokedate >= ;
#笑话里包含鞋 //%表示任一字符
mysql> select joketext as content from jokes where joketext like %not%;
++
| content |
++
| why not? |
++
#显示年月份的包含鞋的笑话(组合查询)
select joketext from where joketext like %not% and jokedate >= and jokedate < ;
分组语句
员工表emp(empno name tel deptno sal)
部门表dept(deptno dname addr)
）显示每个部门的最高工资
select deptno max(sal) from emp group by deptno;
select deptnomax(sal) as 最高工资 from emp group by deptno;
注意max()min()…函数在显示列中出现时必须与group by一起使用
嵌套子查询
）显示每个部门收入最高的职工信息
select * from emp
where sal in ( select max(sal) from emp
group by deptno );(先按组排序后显示一组中最大的)
）按照工资高低显示职工信息
select * from emp order by sal desc;
）limit sn
limit子句可以被用于强制select语句返回指定的记录数limit接受一个或两个数字参数参数必须是一个整数常量如果给定两个参数第一个参数指定第一个返回记录行的偏移量第二参数指定返回记录行的最大数目n初始记录行的偏移量是(而不是)
（）查看薪水最低的前位员工信息
select * from emp order by sal limit ;
óselect * from emp order by sal limit ;
（）查看第到第号员工信息
select * from emp limit ;
