MySQL 中数据的检索（一：select 语句的基本用法）

超、凢脫俗 2022-05-29 08:50 242阅读 0赞

首先创建要用到的数据表：  
use zhrq95;  
create table T\_Employee  
(  
FNumber varchar(20),  
FName varchar(20),  
FAge int,  
FSalary decimal(10,2),  
primary key (FNumber)  
);  
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(‘SALE001′,’John’,23,5000);  
insert into T\_Employee(FNumber,FName,FAge,FSalary) values(‘SALE002′,’Kerry’,28,6200);  
insert into T\_Employee(FNumber,FName,FAge,FSalary) values(‘SALE003′,’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(‘HR003′,’Smith’,28,3900);

取出一张表中所有的数据（select \*）：  
select \* from T\_Employee

检索出需要的列：  
select FNumber from T\_Employee;  
select FName,FAge from T\_Employee

给列取别名（as）：  
select FNumber as Number1, FName as Name, FAge as Age, FSalary as Salary from T\_Employee;  
select FNumber as 工号, FName as 姓名, FAge as 年龄, FSalary as 工资 from T\_Employee

按条件过滤：  
select FName from T\_Employee where FSalary<5000; select \* from T\_Employee where FSalary<5000 or FAge>25

数据汇总（聚合函数）：  
select max(FSalary) from T\_Employee where FAge>25; \#计算字段的最大值  
select max(FSalary) as MAX\_SALARY from T\_Employee where FAge>25; \#为聚合函数取别名  
select avg(FAge) from T\_Employee where FSalary>3800; \#计算字段的平均值  
select sum(FSalary) from T\_Employee; \#计算字段的合计值  
select min(FSalary),max(FSalary) from T\_Employee; \#计算字段的最小值  
select count(\*),count(FNumber) from T\_Employee; \#统计数据条数  
注：count(\*) 统计的是结果集的总条数；  
count(字段名) 比如 count(FName) 统计的是除了结果集中 FName 不为空值（即不为 NULL）的记录的总条数

\# 排序  
select \* from T\_Employee order by FAge ASC \#ASC 代表升序，默认为升序，所以 ASC 可以省略  
select \* from T\_Employee order by FAge DESC \# DESC 代表降序  
select \* from T\_Employee order by FAge DESC,FSalary DESC  
注：  
select \* from T\_Employee \#order by 必须在 where 之后，不能颠倒它们的顺序  
where FAge>23  
order by FAge DESC,FSalary DESC