常用sql命令

select * from xiaowei;

--age的最大值，最小值
select MAX(age),MIN(age) from xiaowei;

--对表行数计数，对age计数（不包括null）
select count(*),count(age) from xiaowei;

--计算age的平均值（不包括null），age求和
select AVG(age),SUM(age) from xiaowei;

--此句的意思是将age值为null替换为0，0可以与其他int值一起计算平均值。因为有两个null被替换为0参与了平均值的计算，所以这里计算的平均值就跟之前不一样了
--mysql可以用isnull函数，pgsql只支持COALESCE；
select avg(isnull(age,0)) from xiaowei;
select avg(COALESCE(age,0)) from xiaowei;

--此句的意思是用age进行分组，展示对应的name，MySQL使用group_concat，pgsql不支持group_concat，可以用array_agg，返回的name是一个数组
select age,group_concat(name) from xiaowei group by age;
select age,array_agg(name) from xiaowei group by age ;
--给分组的内容加一个查询条件
select age,array_agg(name) from xiaowei where age = '15' group by age ;
--对name排序，并转为string，中间用空格分隔
select age,array_to_string(array_agg(name order by name), ' ') from xiaowei group by age;

--对age进行去重
select distinct age from xiaowei;
select age,array_agg(name) from xiaowei group by age ;

--对id和name两列都一致的数据进行去重
select distinct id,name from xiaowei;   

--存储过程
CREATE OR REPLACE PROCEDURE get_employees_by_department(
    department_name text,
    OUT employee_name text,
    OUT employee_salary numeric
)
LANGUAGE plpgsql
AS $$ 
BEGIN
    SELECT name, salary INTO employee_name, employee_salary FROM employees WHERE department = department_name;
END;
$$;

CREATE PROCEDURE AddOrder(CustomerID INT)
AS
BEGIN
    INSERT INTO Orders (CustomerID, ProductID, Quantity, OrderDate, Status)
    VALUES (@CustomerID, @ProductID, @Quantity, GETDATE(), 'Pending')
END

CREATE PROCEDURE AddOrder
    @CustomerID INT,
    @ProductID INT,
    @Quantity INT
AS
BEGIN
    INSERT INTO Orders (CustomerID, ProductID, Quantity, OrderDate, Status)
    VALUES (@CustomerID, @ProductID, @Quantity, GETDATE(), 'Pending')
END