openGauss的单表查询SQL语句与MySQL基本类似，这里不再赘述。

单表查询的基本语法：

SELECT 字段1，字段2 ，... FROM  表名
            WHERE 条件
            GROUP BY field
            HAVING 筛选
            ORDER BY filed
            LIMIT 限制条数

1.实例

/*openGauss单表查询语句课堂练习*/

/*openGauss生成dual表*/
/*这个dual永远返回的是一个单行单例的值*/
CREATE OR REPLACE VIEW dual AS SELECT NULL::"unknown" WHERE 1 = 1; 
ALTER TABLE dual OWNER TO omm;
GRANT ALL ON TABLE dual TO omm;
GRANT SELECT ON TABLE dual TO public;


/*创建序列*/
SET search_path = public;
CREATE SEQUENCE sq_employee_id
 START 1 
 INCREMENT 1
 CACHE 20; 

 /*创建一个员工表*/
 CREATE  TABLE "employee" (
    id integer NOT NULL DEFAULT nextval('sq_employee_id') PRIMARY KEY,
    name character varying,
    gender character varying,
    age integer,
    hire_date date,
    post character varying,
    salary numeric(8,2),
    office integer,
    depart_id character varying
)
WITH (orientation=row, compression=no);
ALTER TABLE "employee" ADD CONSTRAINT employee_id_key UNIQUE (id); 


/*我们初始化20个员工资料*/
insert into employee (name,gender,age,hire_date,post,salary,office,depart_id) values ('宋小宝','男',35,'2002-10-10','销售经理',12000,10,'销售部');

select * from employee;

insert into employee (name,gender,age,hire_date,post,salary,office,depart_id) values ('小沈阳','女',28,'2004-10-10','人事经理',8000,11,'人事部'),('王健林','男',68,'2000-10-10','董事长',180000,12,'董事部'),('王思聪','男',30,'2001-10-10','总经理',100000,12,'董事部'),('赵四','男',37,'2005-12-10','销售经理',7000,10,'销售部'),('刘能','男',40,'2002-10-10','销售经理',95000,10,'销售部'),('袁野','男',41,'2003-10-10','销售经理',6700,10,'销售部'),('郭美美','女',25,'2002-10-10','公关经理',14000,13,'公关部'),('杨颖','女',26,'2003-10-10','公关经理',11000,13,'公关部'),('古力娜扎','女',22,'2002-10-10','公关经理',15000,13,'公关部'),('迪丽热巴','女',24,'2002-10-10','公关经理',4000,14,'办公室');

/*单表查询结构*/
/*select 字段 from 表 where 条件 group by 分组  having 子句 order by 排序*/

/*查询所有姓‘王’的员工*/
select * from employee where name like '王%';

/*查询工资在10000-20000之间的员工*/
select * from employee where salary between 10000 and 20000; 

/*统计每个部门的员工人数*/
/*注意：使用了group by分组，要求select 后面的字段只能是聚合函数，或者是gorup by 后面出现过的字段*/
select count(*) as 人数,depart_id from  employee group by depart_id;

/*统计每个部门的平均工资*/
select round(AVG(salary),2) as 平均工资 ,depart_id from employee group by depart_id;

/*CTS 中央标准时间，以英国伦敦时间为标准*/
select sysdate from dual;

/*统计每个部门的平均工资，部门平均工资大于1万*/
select round(AVG(salary),2) as 平均工资 ,depart_id from employee group by depart_id having 平均工资 > 10000;

/*统计每个部门的平均工资，部门平均工资大于1万，并且按照平均工资降序排序*/
select round(AVG(salary),2) as 平均工资 ,depart_id from employee group by depart_id having 平均工资 > 10000 order by 平均工资 desc;

/*获得第二页的分页数据*/
/*
  第一个参数：起始记录的下标。(不是那个编号)
    第二个参数：每页的记录数。
    和MySQL是一模一样的。
*/
select * from  employee limit 4,4;