初始数据:
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