MySQL 查询（DQL）

DQL

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端,查询返回的

一、 SELECT 查询列

SELECT 列名 FROM 表名 【WHERE --> GROUP BY --> HAVING --> ORDER BY】

二、 首先创建stu和employee表插入数据

+--------+----------+------+--------+
| sid    | sname    | age  | gender |
+--------+----------+------+--------+
| s_1001 | liuYing  |   35 | male   |
| s_1002 | chenger  |   20 | male   |
| s_1003 | zhangsan |   15 | female |
| s_1004 | wangwu   |   23 | male   |
| s_1005 | liuYin   |   35 | female |
| s_1006 | zhaoliu  |   70 | male   |
| s_1007 | sunQi    |   15 | female |
| s_1008 | zhouBa   |   43 | male   |
| s_1009 | wuJiu    |   85 | female |
| s_1010 | zhengShi |    5 | male   |
+--------+----------+------+--------+

create table employee ( empno int, ename varchar(50), job varchar(50), mgr varchar(50),hiredata datae, sal decimal(7,2), comm decimal(7,2), deptno int);

mysql> select * from employee;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredata   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST  | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST  | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+

create table dept(deptno int, dname varchar(50), loc varchar(13));
Query OK, 0 rows affected (0.01 sec)
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(50) | YES  |     | NULL    |       |
| loc    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

三、 SELECT 查询列

select ename from employee; // 查询单列
select empno, ename, ... from employee; /// 查询多列用逗号隔开

+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+