sql常用语句

常用数据库sql语句练习

/*group by 分组查询，查询的字段和分组字段数量要一致，分组列唯一值作为一个组，多个字段组，允许a+b重复，但不允许单个分组，a/a*/

select bin_code,sum(total_qty) from wms_stock group by bin_code order by sum(total_qty) ASC

select * from wms_stock order by total_qty

alter table wms_account_result add field1 varchar
/*联合查询*/
select * from wms_container c join wms_stock s on c.container_code=s.trace_code where c.container_str2='01';

/*创建一个数据库*/
create database user2023;

/*创建一个数据库表*/
create table student1
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

/*向已有的表增加字段ALTER TABLE Persons
ADD Birthday date*/
alter table student add Birthday  varchar(255);

/*向已有的表删除字段,ALTER TABLE Persons
ALTER COLUMN Birthday year*/
alter table student drop column work;

/*向数据表插入数据*/
insert into student (Id_P,LastName,FirstName,Address,City,birthday)  values (generate_series(11,20),'wang','qiyin','hagnzhou','ye',generate_series(11,20));

/*更改数据表参数*/
update student set city='beijing' where birthday='11';

/*删除数据库表的数据*/
DELETE FROM Person WHERE LastName = 'Wilson'

/*查询--子查询，使用NOT EXISTS或者exists*/
select city,id_p,birthday from student where exists (select * from student where birthday like '%1');

select stock_code,bin_code from wms_stock order by stock_code asc;
/*查询列中不重复的值*/
select distinct city from student;

/*查询多少条记录，通过limit*/
select * from student limit 2 where address in ('hangzhou');

/*between可以在两个数字或者文本之间*/
select * from student as s where s.id_p between 1 and 5;
create table fruit(
Id_P int,
Last varchar(255),
First varchar(255),
Address varchar(255),
City varchar(255)
);

insert into fruit (Id_P,last,first,Address,City) values (generate_series(1,10),'test01','test02','nanjing','shanghai');

/*联合查询*/
select * from student as s inner join fruit as f on s.id_p=f.id_p where s.id_p between 1 and 5;
select * from student as s left join fruit as f on s.id_p=f.id_p where s.id_p between 1 and 5;
select * from student as s join fruit as f on s.id_p=f.id_p where s.id_p between 1 and 5;
select * from student as s right join fruit as f on s.id_p=f.id_p where s.id_p between 1 and 5;
select * from student as s full join fruit as f on s.id_p=f.id_p where s.id_p between 1 and 5;

/*数据库表数据的复制，可以复制部分列的数据*/
select * into student1 from student;

/*当两个表的结构相同，可使用unio或者unio all查询两个表的参数*/
select * from student1 union all select * from student;

/*创建一个视图，create view 视图名称,接着可以直接查询视图名称，来按照视图展示*/
create view stu as select * from student where id_p in (1,2,3,4,5);
select * from stu;

/*function(列)*/
select avg(id_p),count(distinct id_p) from student;
SELECT mid(address,2,5) AS LastOrderPrice FROM student