SQL基础（七）---SELECT

来源：互联网发布：原味淘宝现在用的暗语编辑：程序博客网时间：2024/06/03 14:17

初始数据：

mysql> create table t_employee(    -> fnumber varchar(20),    -> fname varchar(20),    -> fage int,    -> fsalary decimal(10,2),    -> primary key(fnumber));Query OK, 0 rows affectedmysql> 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 affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedmysql> 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

0 0