Postgre SQL(低阶介绍)

Postgre SQL (低阶)

创建数据库

创建数据库有三种方式:

  1. 使用 create datebase SQL语句创建
  2. 使用 createdb 命令创建
  3. 使用 pgAdmin 工具

create datebase 命令创建数据库

语法如下:

create datebase dbname;

例如:

postgres=# create datebase student;

createbd 命令创建数据库

createbd 是一个SQL命令前者的封装。

语法如下:

createbd [option...] [bdname] [description] //不常用可以不记

说明:

**bdname:**要创建的的数据库名称。

**description:**关于新数据库的说明。

**options:**参考可选项,权限(自己理解的)

pgAdmin 工具创建数据库

用鼠标点,简单吧,可是显得不太专业呦!!!

同样给数据库起名字,并且可以写相关说明。

选择数据库

命令窗口操作

首先可以使用\l用于查看已经存在的数据库(注意是英文l)

postgres=# \l
                                                        数据库列表
   名称    |  拥有者  | 字元编码 |            校对规则            |             Ctype              |       存取权限     
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
 postgres  | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 student   | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 template0 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
 template1 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
(4 行记录)

接下来使用 \c+ 数据库名 进入数据库:

postgres=# \c student
您现在已经连接到数据库 "student",用户 "postgres".
student=#

pgAdmin工具

直接点击即可(是不是超级简单)

删除数据库

drop datebase 删除数据库

需在SQL命令窗口操作

语法如下:

drop datebase name

**name:**要删的数据库的名称。

例如:

postgres=# drop datebase student;

dropbd 命令删除数据库

dropdb daname

pgAdmin 工具删除数据库

鼠标右击点击删除

创建表

语法

与 SQLservice 相似:

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 )
);

实例:

create table 教学计划(
课程号 char(4)  primary key not null,
	专业代码 char(4) not null,
	专业学级 date,
	选课类型 char(8),
	开课学期 int not null,
	学分 int not null,
	开始周 int not null,
	结束周 int not null
);

当然可以再命令窗口操作,先进入想要进入的数据库,然后代码块和前者模式一样。

可以使用\d命令查看表格是否创建成功。

可以使用\d tablename查看表中信息:

student=# \d 学生
                     数据表 "public.学生"
   栏位   |         类型         | 校对规则 |  可空的  | 预设
----------+----------------------+----------+----------+------
 学号     | character(12)        |          | not null |
 姓名     | character varying(8) |          |          |
 性别     | character(2)         |          |          |
 出生日期 | date                 |          |          |
 入学时间 | date                 |          |          |
 班级代码 | character(9)         |          |          |
索引:
    "pk_xh" PRIMARY KEY, btree ("学号")
外部键(FK)限制:
    "fk_xsbjdm" FOREIGN KEY ("班级代码") REFERENCES "班级"("班级代码")
由引用:
    TABLE ""课程收费"" CONSTRAINT "fk_kcsfxh" FOREIGN KEY ("学号") REFERENCES "学生"("学号")
    TABLE ""课程注册"" CONSTRAINT "fk_kczcxh" FOREIGN KEY ("学号") REFERENCES "学生"("学号")
    TABLE ""收费表"" CONSTRAINT "fk_sfbxh" FOREIGN KEY ("学号") REFERENCES "学生"("学号")

模式(schema)

模式可以看做是一个表的集合。(可以包括视图、索引、据类型、函数、操作)

相同的对象名称可以用于不同的模式并且不会冲突。

允许多个用户使用同一个数据库,并且互不干扰。

语法

使用 create schema 语句来创建,格式如下:

create schema myschema.mytable(
...
);

在shell命令窗口中先进入数据库中再通过命令创建

student=# create schema myschema;

删除模式

删除一个空的模式(空的,没有对象)

drop schema myschema;

删除一个模式及其所有对象:

drop schema myschema cascade;

对于现在的我来说,这个模式就是表的另一张形式,还不太懂。

insert into 语句

语法

几乎同SQL service 相同

语法如下:

insert into table_name (column1, column2, column3,...columnN)
values (value1, value2, value3,...valueN);

**注意:**在insert into 语句中,字段列必须和数据值数量相同,且顺序也要对应。

如果我们向表中的所有字段插入值,则可以不需要指定字段,只需要指定插入的值即可:

insert into table_name values (value1,value2,value3,...valueN);

可以通过insert 0 #控制插入的数据,#表示行数

实例

由于和SQL service 几乎相同,列出不同之处

  • 若在表中不需要插入某项数据,可以在字段中不写此字段,然后输入数据:

    忽略入学年龄字段:

    insert into 学生(学号,姓名,性别,入学时间,班级代码) values(1001,张三,男,2019,060101001);
    
  • 若某条记录中没有某个字段,可以使用default来代替默认值:

    insert into 学生(学号,姓名,性别,入学年龄,入学时间,班级代码) values(1001,张三,男,default,2019,060101001);
    
  • 可以插入多条数据:

    insert into 学生(学号,姓名,性别,入学年龄,入学时间,班级代码) values(1001,张三,男,default,2019,060101001),(1002,刘云,女,19,2019,060101001),(1003,田小宁,女,20,2019,060101001);
    

select 语句

同SQL service select语句

语法

select column1, column2,...columnN from table_name;
  • 其中 column1,column2,…columnN表示表中的字段名。
  • table_name为表的名字。

若需要读取所有字段,可以使用:

select * from table_name;

实例

select *from 学生;
     学号     |  姓名  | 性别 |  出生日期  |  入学时间  | 班级代码
--------------+--------+------+------------+------------+-----------
 060101001001 | 张小泽 || 1985-06-04 | 2006-09-18 | 060101001
 060101001002 | 刘永辉 || 1986-09-10 | 2006-09-18 | 060101001
 060101001003 | 孙辉   || 1986-07-08 | 2006-09-18 | 060101001
 060101001004 | 李洪普 || 1986-02-02 | 2006-09-18 | 060101001
 060102002001 | 付盘峰 || 1985-05-04 | 2006-09-18 | 060102002
 060102002002 | 李红   || 1986-09-24 | 2006-09-18 | 060102002
 060201001001 | 罗昭   || 1986-10-10 | 2006-09-18 | 060201001
 060202002001 | 郭韩   || 1985-12-30 | 2006-09-18 | 060202002
 060301001001 | 田小宁 || 1985-08-06 | 2006-09-18 | 060301001
 060302001001 | 刘雅丽 || 1986-12-03 | 2006-09-18 | 060302001
 060401001001 | 刘云   || 1986-05-06 | 2006-09-18 | 060401001
(11 行记录)

select 学号,姓名 from 学生;
 学号     |  姓名
--------------+--------
 060101001001 | 张小泽
 060101001002 | 刘永辉
 060101001003 | 孙辉
 060101001004 | 李洪普
 060102002001 | 付盘峰
 060102002002 | 李红
 060201001001 | 罗昭
 060202002001 | 郭韩
 060301001001 | 田小宁
 060302001001 | 刘雅丽
 060401001001 | 刘云
(11 行记录)

运算符

算术运算符

假设变量a为2,变量b为3,则:

运算符描述实例
+a + b 结果为 5
_a - b 结果为 -1
*a * b 结果为 6
/b / a 结果为 1
%模(取余)b % a 结果为 1
^指数a ^ b 结果为 8
|/平方根/ 25.0 结果为 5
||/立方根||/27.0结果为3
阶乘5 ! 结果为 120
!!阶乘(前缀操作符)!! 5 结果为 120

比较运算符

假设变量 a 为 10,变量 b 为 20,则:

运算符描述实例
=等于(a = b) 为 false。
!=不等于(a != b) 为 true。
<>不等于(a <> b) 为 true。
>大于(a > b) 为 false。
<小于(a < b) 为 true。
>=大于等于(a >= b) 为 false。
<=小于等于(a <= b) 为 true。

逻辑运算符

序号运算符&描述

1
and
逻辑与运算符。如果两个操作数都非零,则条件为真。
PostgresSQL 中的 where 语句可以用 and 包含多个过滤条件。

2
not
逻辑非运算符。用来逆转操作数的逻辑状态。如果条件为真则逻辑非运算符将使其为假。
PostgresSQL 有 not exists,not between,not in等运算符

3
or
逻辑或运算符。如果两个操作数中有任意一个非零,则条件为真。
PostgresSQL 中的 where 语句可以用 or 包含多个过滤条件。

位运算符

主要作用于位

pqp&qp|q
0000
0101
1111
1001

假如A=60,且B=13,二进制格式表示为A=0011 1100,B=0000 1101

A&B=0000 1100(冗余)

A|B=0011 1101(0与1:1,0与0:0,1与1:1)

A^B=0011 0001(相同为0,不同为1)

~A=1100 0011(0为1,1为0)

where 子句

语法

select column1, column2, columnN
from table_name
where [condition1]

and

找出性别为男,并且班级代码为060101001的学生

student=# select *from 学生 where 性别='男' and 班级代码='060101001';
     学号     |  姓名  | 性别 |  出生日期  |  入学时间  | 班级代码
--------------+--------+------+------------+------------+-----------
 060101001001 | 张小泽 || 1985-06-04 | 2006-09-18 | 060101001
 060101001002 | 刘永辉 || 1986-09-10 | 2006-09-18 | 060101001
 060101001003 | 孙辉   || 1986-07-08 | 2006-09-18 | 060101001
(3 行记录)

or

找出班级代码为060101001或者060102002的学生

student=# select * from 学生 where 班级代码='060102002' or 班级代码='060101001';
     学号     |  姓名  | 性别 |  出生日期  |  入学时间  | 班级代码
--------------+--------+------+------------+------------+-----------
 060101001001 | 张小泽 || 1985-06-04 | 2006-09-18 | 060101001
 060101001002 | 刘永辉 || 1986-09-10 | 2006-09-18 | 060101001
 060101001003 | 孙辉   || 1986-07-08 | 2006-09-18 | 060101001
 060101001004 | 李洪普 || 1986-02-02 | 2006-09-18 | 060101001
 060102002001 | 付盘峰 || 1985-05-04 | 2006-09-18 | 060102002
 060102002002 | 李红   || 1986-09-24 | 2006-09-18 | 060102002
(6 行记录)

NULL 和NOT NULL

确定为空或者不为空的字段用is 代替=

like

如其名像什么

  • %表示可代替一串字符,比如姓刘的可以表示为:like '刘%'
  • _ 表示一个字符,比如姓刘的三个子的名字最后一个字是丽的可以表示为: like '刘_丽'

in 和 not in

比如表示年龄为25和27的可以表示为年龄 in(25,27)

between

比如表示年龄在25到27可以表示为 年龄 between 25 and 27

嵌套查询

语法如下:

select column1, column2, columnN
from table_name
where(
select column1, column2, columnN
from table_name
where [condition1])

update 语句

此语句用于更新数据库中的数据

语法

语法如下:

update table_name
set column1 = value1, column2 = value2...., columnN = valueN
where [condition];
  • 可以同时更新一个或者多个字段
  • where语句中可以加条件

delete 语句

用于删除数据

语法

delete
from table_name 
where [condition];

可以通过where中的条件来控制要删除的数据,不写,则全删

limit 子句

用于限制select语句中查询的数量。

语法如下:

select column1, column2, columnN
from table_name
limit [no of rows]

和offset子句一起使用:

select column1, column2, columnN 
from table_name
limit [no of rows] offset [row num]

实例

单独使用limit,例如读取前5条数据:

student=# select * from 学生 limit 5;
     学号     |  姓名  | 性别 |  出生日期  |  入学时间  | 班级代码
--------------+--------+------+------------+------------+-----------
 060101001001 | 张小泽 || 1985-06-04 | 2006-09-18 | 060101001
 060101001002 | 刘永辉 || 1986-09-10 | 2006-09-18 | 060101001
 060101001003 | 孙辉   || 1986-07-08 | 2006-09-18 | 060101001
 060101001004 | 李洪普 || 1986-02-02 | 2006-09-18 | 060101001
 060102002001 | 付盘峰 || 1985-05-04 | 2006-09-18 | 060102002
(5 行记录)

limitoffset配合使用,例如从第二个位置读取5条数据:

student=# select * from 学生 limit 5 offset 2;
     学号     |  姓名  | 性别 |  出生日期  |  入学时间  | 班级代码
--------------+--------+------+------------+------------+-----------
 060101001003 | 孙辉   || 1986-07-08 | 2006-09-18 | 060101001
 060101001004 | 李洪普 || 1986-02-02 | 2006-09-18 | 060101001
 060102002001 | 付盘峰 || 1985-05-04 | 2006-09-18 | 060102002
 060102002002 | 李红   || 1986-09-24 | 2006-09-18 | 060102002
 060201001001 | 罗昭   || 1986-10-10 | 2006-09-18 | 060201001
(5 行记录)

order by 语句

此语句主要用于整理表中的数据,方便用户查看,统计

语法如下:

select column-list
from table_name
[where condition]
[order by column1, column2, .. columnN] [asc | desc];

order by后面的条件便是根据的内容,所以这个字段必须是表中有的字段;

ascdesc分别表示列表排列的顺序,分别表示升序降序,并且默认是升序

实例

查询学生表所有内容并且通过性别升序排列

student=# select * from 学生 order by 性别;
     学号     |  姓名  | 性别 |  出生日期  |  入学时间  | 班级代码
--------------+--------+------+------------+------------+-----------
 060202002001 | 郭韩   || 1985-12-30 | 2006-09-18 | 060202002
 060301001001 | 田小宁 || 1985-08-06 | 2006-09-18 | 060301001
 060101001002 | 刘永辉 || 1986-09-10 | 2006-09-18 | 060101001
 060101001001 | 张小泽 || 1985-06-04 | 2006-09-18 | 060101001
 060102002001 | 付盘峰 || 1985-05-04 | 2006-09-18 | 060102002
 060101001003 | 孙辉   || 1986-07-08 | 2006-09-18 | 060101001
 060201001001 | 罗昭   || 1986-10-10 | 2006-09-18 | 060201001
 060401001001 | 刘云   || 1986-05-06 | 2006-09-18 | 060401001
 060101001004 | 李洪普 || 1986-02-02 | 2006-09-18 | 060101001
 060102002002 | 李红   || 1986-09-24 | 2006-09-18 | 060102002
 060302001001 | 刘雅丽 || 1986-12-03 | 2006-09-18 | 060302001
(11 行记录)

查询学生年龄从小到大

select (now()-出生日期)/365 as 年龄
from 学生
order by 年龄;
          年龄
-------------------------
 33 days 22:21:09.588203
 34 days 01:54:12.053956
 34 days 02:57:19.451217
 34 days 03:52:33.423819
 34 days 08:05:03.012861
 34 days 12:13:35.889573
 34 days 18:20:30.136148
 34 days 20:34:38.355326
 35 days 06:10:38.355326
 35 days 10:19:11.232039
 35 days 12:21:29.31423
(11 行记录)

now()获取的现在的准确时间,所以会有小数

group by 语句

字面意思分组,主要为了方便使用sum(),avg()等函数,位于where后面,当然不需要限定条件时,就可以直接写

语法

select column-list
from table_name
where [ conditions ]
group by column1, column2....columnN

实例

比如计算学生中男生和女生的平均年龄

select  性别,avg((now()-出生日期)/365) as 平均年龄
from 学生
group by 性别;

结果如下:

 性别 |        平均年龄
------+-------------------------
 男   | 34 days 19:19:23.74198
 女   | 33 days 31:57:51.687185
(2 行记录)

由于now()函数的默认单位为days,所以感觉有搞笑

with语句

with 语句可以有效地将一个复杂的查询分解为一些简单的小查询,便于阅读。with 语句对于执行多次子查询非常好用,可以通过命名的方式多次使用,所以with 语句在使用前必须先定义。

语法

with
   name_for_summary_data as(
      select statement)
   select columns
   from name_for_summary_data
   where conditions <=> (
      select column
      from name_for_summary_data)
   [order by columns]

在我的理解中 with相当与sql service 中的嵌套查询,但这个更方便,虽然原理相当,但是它仿佛是定义了一个查询某结果的方法,可以重复使用在多条查询语句中

with递归

既然像函数自然就能实现递归查询了,设置好条件,反复调用自己,直至查到想要的结果

实例

with 
男生 as(select * from 学生 where 性别='男' )
select 姓名 from 男生;
  姓名
--------
 张小泽
 刘永辉
 孙辉
 付盘峰
 罗昭
 郭韩
 田小宁
(7 行记录)

having 语句

主要用于group by后进行

语法

select column1, column2
from table_name
where [ conditions ]
group by column1, column2
having [ conditions ]

这个就不举例子了,和where差不多

distinct 关键词

用于去除重复行数据

语法

语法也特别简单在要去除重复行的字段前加关键词distinct

select distinct column1, column2,.....columnN
from table_name
where [condition]

比如distinct column1表示column1字段去除重复行

这个也不举例子了

©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页