mysql_基础_002

数据表的查询：

# 从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; # 天数