SQL基础（七）---SELECT

初始数据：

mysql> create table t_employee(
    -> fnumber varchar(20),
    -> fname varchar(20),
    -> fage int,
    -> fsalary decimal(10,2),
    -> primary key(fnumber));
Query OK, 0 rows affected

mysql> INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('DEV001','Tom',25,8300);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('DEV002','Jerry',28,2300.80);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES001','John',23,5000);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES002','Kerry',28,6200);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES003','Stone',22,1200);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('HR001','Jane',23,2200.88);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('HR002','Tina',25,5200.36);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('IT001','Smith',28,3900);
Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

mysql> select * from t_employee;
+----------+-------+------+---------+
| fnumber  | fname | fage | fsalary |
+----------+-------+------+---------+
| DEV001   | Tom   |   25 | 8300    |
| DEV002   | Jerry |   28 | 2300.8  |
| HR001    | Jane  |   23 | 2200.88 |
| HR002    | Tina  |   25 | 5200.36 |
| IT001    | Smith |   28 | 3900    |
| SALES001 | John  |   23 | 5000    |
| SALES002 | Kerry |   28 | 6200    |
| SALES003 | Stone |   22 | 1200    |
+----------+-------+------+---------+
8 rows in set

查询FNUMBER列数据：

mysql> select fnumber from t_employee;
+----------+
| fnumber  |
+----------+
| DEV001   |
| DEV002   |
| HR001    |
| HR002    |
| IT001    |
| SALES001 |
| SALES002 |
| SALES003 |
+----------+
8 rows in set

查询所有的员工名称和年龄：

mysql> select fname,fage from t_employee;
+-------+------+
| fname | fage |
+-------+------+
| Tom   |   25 |
| Jerry |   28 |
| Jane  |   23 |
| Tina  |   25 |
| Smith |   28 |
| John  |   23 |
| Kerry |   28 |
| Stone |   22 |
+-------+------+
8 rows in set

查询所有的员工信息：

mysql> select fnumber,fname,fage,fsalary from t_employee;
+----------+-------+------+---------+
| fnumber  | fname | fage | fsalary |
+----------+-------+------+---------+
| DEV001   | Tom   |   25 | 8300    |
| DEV002   | Jerry |   28 | 2300.8  |
| HR001    | Jane  |   23 | 2200.88 |
| HR002    | Tina  |   25 | 5200.36 |
| IT001    | Smith |   28 | 3900    |
| SALES001 | John  |   23 | 5000    |
| SALES002 | Kerry |   28 | 6200    |
| SALES003 | Stone |   22 | 1200    |
+----------+-------+------+---------+
8 rows in set

定义列别名：

mysql> select fnumber as number1, fname as name, fage as age, fsalary as salary from t_employee;
+----------+-------+-----+---------+
| number1  | name  | age | salary  |
+----------+-------+-----+---------+
| DEV001   | Tom   |  25 | 8300    |
| DEV002   | Jerry |  28 | 2300.8  |
| HR001    | Jane  |  23 | 2200.88 |
| HR002    | Tina  |  25 | 5200.36 |
| IT001    | Smith |  28 | 3900    |
| SALES001 | John  |  23 | 5000    |
| SALES002 | Kerry |  28 | 6200    |
| SALES003 | Stone |  22 | 1200    |
+----------+-------+-----+---------+
8 rows in set

定义列别名2：

mysql> select fnumber number, fname name, fage age, fsalary salary from t_employee;
+----------+-------+-----+---------+
| number   | name  | age | salary  |
+----------+-------+-----+---------+
| DEV001   | Tom   |  25 | 8300    |
| DEV002   | Jerry |  28 | 2300.8  |
| HR001    | Jane  |  23 | 2200.88 |
| HR002    | Tina  |  25 | 5200.36 |
| IT001    | Smith |  28 | 3900    |
| SALES001 | John  |  23 | 5000    |
| SALES002 | Kerry |  28 | 6200    |
| SALES003 | Stone |  22 | 1200    |
+----------+-------+-----+---------+
8 rows in set

定义列别名3：

mysql> select fname '员工',fage '年龄' from t_employee;
+-------+------+
| 员工  | 年龄 |
+-------+------+
| Tom   |   25 |
| Jerry |   28 |
| Jane  |   23 |
| Tina  |   25 |
| Smith |   28 |
| John  |   23 |
| Kerry |   28 |
| Stone |   22 |
+-------+------+
8 rows in set