数据表的查询:
# 从t_emp中查询empno、年薪,年薪字段显示为income
SELECT empno, sal*12 AS "income" FROM t_emp;
# 数据分页显示--LIMIT 起始位置,偏移量:
mysql> SELECT * FROM student LIMIT 0,2;
+----+------+-----+------------+-------------+--------+
| id | name | sex | birthday | tel | remark |
+----+------+-----+------------+-------------+--------+
| 1 | ABC | m | 1999-01-01 | 11111111111 | NULL |
| 2 | BC | m | 1999-01-01 | 11111111111 | NULL |
+----+------+-----+------------+-------------+--------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM student LIMIT 2,2;
+----+------+-----+------------+-------------+--------+
| id | name | sex | birthday | tel | remark |
+----+------+-----+------------+-------------+--------+
| 3 | C | m | 1999-01-01 | 11111111111 | NULL |
| 4 | CA | m | 1999-01-01 | 11111111111 | NULL |
+----+------+-----+------------+-------------+--------+
2 rows in set (0.00 sec)
mysql>
# 排序 ORDER BY,默认为升序(ASC),DESC为降序
mysql> select * from student ORDER BY name;
+----+------+-----+------------+-------------+--------+
| id | name | sex | birthday | tel | remark |
+----+------+-----+------------+-------------+--------+
| 1 | ABC | m | 1999-01-01 | 11111111111 | NULL |
| 2 | BC | m | 1999-01-01 | 11111111111 | NULL |
| 3 | C | m | 1999-01-01 | 11111111111 | NULL |
| 4 | CA | m | 1999-01-01 | 11111111111 | NULL |
| 5 | CQA | m | 1999-01-01 | 11111111111 | NULL |
| 6 | CQQA | m | 1999-01-01 | 11111111111 | NULL |
+----+------+-----+------------+-------------+--------+
6 rows in set (0.01 sec)
mysql> select * from student ORDER BY name DESC;
+----+------+-----+------------+-------------+--------+
| id | name | sex | birthday | tel | remark |
+----+------+-----+------------+-------------+--------+
| 6 | CQQA | m | 1999-01-01 | 11111111111 | NULL |
| 5 | CQA | m | 1999-01-01 | 11111111111 | NULL |
| 4 | CA | m | 1999-01-01 | 11111111111 | NULL |
| 3 | C | m | 1999-01-01 | 11111111111 | NULL |
| 2 | BC | m | 1999-01-01 | 11111111111 | NULL |
| 1 | ABC | m | 1999-01-01 | 11111111111 | NULL |
+----+------+-----+------------+-------------+--------+
6 rows in set (0.00 sec)
# select * from student ORDER BY name DESC LIMIT 0,2;
# DISTINCT-去重;
# 1.只能查询一列 2.一个查询中只能出现一次distinct
mysql> SELECT DISTINCT sex FROM student;
+-----+
| sex |
+-----+
| m |
+-----+
1 row in set (0.00 sec)
–
查询顺序:不是从左向右,先FROM选择数据来源,之后SELECT选择输出内容,有字段后进行ORDER BY排序,之后从排序好的记录中输出LIMIT数据
条件查询:
SELECT empno,ename,sal,hiredate
FROM t_emp
WHERE depno=10 AND (sal+IFNUL L(comm, 0))*12>10 #如果comm字段为NULL则用0替换
AND DATADIFF(NOW(),hiredate)>366; # 天数