postgreSQL创建关系数据库
-
注释:--
-
数据类型:官网链接,如果碰到不熟悉的数据类型可以进入链接查看https://www.postgresql.org/docs/current/datatype.html
-
real 单精度浮点数
-
text 变长字符串
-
serial 自动递增四字节整数常常使用
-
numeric [ (
p
,s
) ] 可选精度的精确数字 -
date 日历日期(年、月、日)
-
integer 有符号四字节整数
-
基础操作
-
约束检查:
CREATE TABLE tablename ( field1fieldtype, field2 fieldtype, field3 fieldtype CHECK (condition) ); 比如:限制价格是正值 CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0) ); 或者可以单独命名约束: CREATE TABLE products ( product_no integer, name text, price numeric constraint positive_price CHECK (price > 0) ); 可以引用多个列: CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) ); 可以多个条件对表约束: CREATE TABLE Salespeople ( snum integer NOT NULL UNIQUE, sname char (10) NOT NULL UNIQUE, city char(10), comm decimal, CHECK (comm < .15 OR city = 'Barcelona') );
-
更改表的结构:
添加列: alter table t1 add pole1 char(10); 更改表格列的类型: alter table t1 alter column name type integer; 删除表格列: alter table t1 drop column pole1; 更改表名字: alter table t1 rename to t2;
-
删除表格:
drop table table_name;
-
创建表格:
CREATE TABLE table_name ( column1 datatype ограничение(если есть), column2 datatype ограничение(если есть), column3 datatype ограничение(если есть), .... );
-
-
一些约束:
-
自增:SERIAL
CREATE TABLE example_table ( id SERIAL PRIMARY KEY, column1 INTEGER, column2 VARCHAR(50) );
-
不空:NOT NULL
CREATE TABLE example_table ( id SERIAL PRIMARY KEY, column1 INTEGER NOT NULL, column2 VARCHAR(50) NOT NULL );
-
唯一性:unique
如果要给一组列定义唯一性约束,写成表约束:
CREATE TABLE example_table ( id SERIAL PRIMARY KEY, column1 INTEGER UNIQUE, column2 VARCHAR(50) UNIQUE ); CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) );
-
默认值:default
CREATE TABLE example_table ( id SERIAL PRIMARY KEY, column1 INTEGER DEFAULT 0, column2 VARCHAR(50) DEFAULT 'default_value' );
-
-
主键:primary key
主键也可以包含多个列,语法与唯一性约束类似:
CREATE TABLE example ( a integer, b integer, c integer, PRIMARY KEY (a, c) );
-
外键:
foreign key (此表里的列名称) references main_table(main_table里面的关联列)
有关外键的删除和更新链接行时候的操作:
- Set Null(设置为 NULL):当主表中的链接记录被删除或更新时,Set Null选项将外键列设置为 NULL 值。在这种情况下,外键列必须允许存储 NULL 值,否则会出现错误。
- Restrict(限制):如果从属表中存在与主表相关联的行,则Restrict选项会阻止删除或修改主表中的行。这意味着只有在从属表中没有相关数据时,才能删除或修改主表中的行。
- No Action(无操作):No Action与Restrict类似,都是在从属表中存在相关行时阻止删除或修改主表中的行。其本质区别在于No Action允许您延迟检查到事务的稍后阶段,而Restrict则立即执行检查。
- Set Default(设置为默认值):当从主表中删除链接行时,Set Default选项将外键列设置为默认值。默认值是根据适当的约束设置的,这样可以确保数据完整性。
- 例如:这个file1的表中user_id外键连接到users表中,使用Cascade 如下,放在外键后面,删除或更新主表中的行,相关的从属表中的行也会被相应地删除或更新
删除前:
删除后:
对于表的增删查改
-
添加数据:
insert into table1(p1,p2) values("**",**);
一次添加多行:
INSERT INTO Products
VALUES('iPhone 6', 'Apple', 3, 36000),('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000);
将另个表中的内容插入到这个表中:
INSERT INTO Product_2
SELECT ProductName, Price, Manufacturer
FROM Product
WHERE price > 36000;
-
数据更新:
UPDATE <table_name>
SET <col_name1> = <value1>, <col_name2> = <value2>, ...
WHERE <condition>;
例如:
Update Product SET Manufacturer = ‘Samsung’ where ProductName =
‘Galaxy’;
-
数据删除:
delete from table1;
delete from table1 where id=1;
删除表格:drop table table1;
-
查询数据:
SELECT <col_name1>, <col_name2>, …
FROM <table_name>;
去重复查询:
select distinct <col_name1>, <col_name2>, …
FROM <table_name>;
有条件的查询:
select<col_name1>, <col_name2>, …
FROM <table_name>
from <condition>;
复杂数据类型:
-
数组:
CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] ); 插入数据: INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {"training", "presentation"}}'); 也可以用array进行插入: INSERT INTO sal_emp VALUES ('Bill',ARRAY[10000, 10000, 10000, 10000], ARRAY[['meeting', 'lunch'], ['training', 'presentation']]); 查询: SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR pay_by_quarter[2] = 10000 OR pay_by_quarter[3] = 10000 OR pay_by_quarter[4] = 10000; 或者上面的这个查询可以直接更改为: SELECT * FROM sal_emp WHERE 10000 = any(pay_by_quarter); unnest 函数将数组展开为一组字符串比如:unnest(ARRAY[1,2]) array_append 和 array_remove 函数用于添加和删除数组元素: update sal_emp set pay_by_quarter=array_append(pay_by_quarter,10000)where id=3; UPDATE sal_emp SET pay_by_quarter =array_append(pay_by_quarter、 10000) WHERE id=3; array_remove 从数组中移除所有等于指定值的元素(数组必须是一维的): UPDATE sal_emp SET pay_by_quarter =array_remove(pay_by_quarter、 10000) WHERE id=3; 使用 array_length 函数可以找出数组的长度: SELECT array_length(pay_by_quarter, 1) AS pay_by_quarter_elements FROM sal_emp;
-
结构体
CREATE TYPE inventory_item AS ( name text, supplier_id integer, price numeric ); 例如:CREATE TABLE on_hand ( item inventory_item, count integer ); SELECT (item).name FROM on_hand WHERE (item).price > 9.99; 如何添加或修改整个列: INSERT INTO mytab (complex_col) VALUES((1.1,2.2)); UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...; 结构类型全输出: SELECT item FROM on_hand;
-
. 枚举
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); 参数赋值为上面列举出来的,如果里面没有相关值就会报错: UPDATE Student" SET mood='very happy' WHERE id=2;
-
几何
我的例子是‘点’,‘圆形’,‘线’,‘正方形’,注意他们每一个在赋值的时候都要用单引号引住
-
Range:
-
JSON类型:
补充一下关于json的相关知识:JSON(JavaScript Object Notation)是一种轻量级的数据交换格式,jSON的基本结构包括两种数据结构:
- 对象(Object):对象是一个无序的键值对集合,每个键值对之间用逗号分隔,整个对象包含在花括号 {} 中。键必须是字符串,值可以是字符串、数字、对象、数组、布尔值、null等;
- 数组(Array):数组是一个有序的值集合,值之间用逗号分隔,整个数组包含在方括号 [] 中。数组中的值可以是字符串、数字、对象、数组、布尔值、null等。
补充一下关于json的相关知识:JSON(JavaScript Object Notation)是一种轻量级的数据交换格式,jSON的基本结构包括两种数据结构:
对象(Object):对象是一个无序的键值对集合,每个键值对之间用逗号分隔,整个对象包含在花括号 {} 中。键必须是字符串,值可以是字符串、数字、对象、数组、布尔值、null等; 数组(Array):数组是一个有序的值集合,值之间用逗号分隔,整个数组包含在方括号 [] 中。数组中的值可以是字符串、数字、对象、数组、布尔值、null等。