## 制作数据库查询系统知识铺垫,有错或者加补充的话,给作者留言哦!
0 打开SQL SHELL
打开SQL Shell---一直敲回车---到Password for user postgres输入安装时候的密码---进入默认数据库postgres
1 创建数据库
创建数据库: CREATE DATABASE XXX;
连接其他数据库:\c postgres(要连接的数据库名)
删除数据库: DROP DATABASE XXX;
看当下拥有的数据库:\l
2 创建表格
进入要创建表格的数据库后
create table table_name(column_name列名 data_type列数据类型 constrains限定);
--eg:中文是注释,帮助理解
create table person(id bigserial8位递增自动生 not null,限定不为空
primary key,做主键 name varchar(200)字符长度限制 not null,限定不为空
gender varchar(7)字符长度限制 not null,限定不为空
birthday data not null,限定不为空
email varchar(50)); [可以为空的值就不写not null]
3 查询
select * from table_name;
4 插入
insert into table_name(column_name,…) values(‘column_value’,…);
--eg
insert into person(name, gender, birthday)values(‘A’, ‘male’, ‘1997-9-23’);
5 order by排序
select (column_name…) from table_name order by (column_name…) asc/desc;降序
--eg
select * from person order by first_name;
6 distinct去重
select distinct (column_name…) from table_name;
--eg
select distinct first_name from person;
7 where and or
--where 从person表筛选性别为女性的姓氏有哪些
select first_name from person where gender = ‘female ’;
--and 从person表筛选性别为女性且国籍为中国的所有人
select * from person where gender = ‘female’ and country = ‘China’;
--or 多限定可变条件查询
select * from person where gender = ‘female’ and (country = ‘China’ or country = ‘Korea’ );
8 比较语句
——limit 查询person表数据,只取前十行
select * from person limit 10;
--offset 查询person表数据,偏移5行开始,取5行
select * from person offset 5 limit 5;
9 in between like
--In:在某一范围筛选
select * from person where country in (‘China’, ‘Korea’, ‘America’);
--Between:在之间筛选
select * from person where birthdate between data’1990-1-1’ and ‘2000-1-1’;
--Like:以某种形式筛选
select * from person where first_name like ‘%ll’; ——以ll结尾的名字
select * from person where first_name like ‘_ _ll’; ——以ll结尾并且前面有两个字符的名字
select * from person where first_name like ‘J%’; ——以J开头的名字
select * from person where first_name ilike ‘J%’; ——以J/j开头的名字(不区分大小写)
10 删除
delete from table_name where id =4;