Shulou(Shulou.com)06/02 Report--

Source code of this article: GitHub click here | | GitEE click here |

A brief introduction to PostgreSQL

1. Brief introduction to database

PostgreSQL is a powerful open source database system, which has the characteristics of reliability, stability and data consistency, and can run on all mainstream operating systems, including Linux, Unix, Windows and so on. PostgreSQL is a complete transaction security database that fully supports foreign keys, joins, views, triggers, and stored procedures. It supports most SQL:2008 standard data types, including integer, numeric, Boolean, byte, character, date, time interval, and time. It also supports storing large binary objects, including pictures, sounds, and videos. There are native programming interfaces for many high-level development languages, such as API, Java, etc., as well as various documentation.

2. Highly open source

The source code of PostgreSQL is freely available, and it is licensed under a very free open source license that allows users to use, modify, and distribute PostgreSQL source code in a variety of open source or closed source projects. Users can make any modifications and improvements to the source code according to the wishes of the users. Therefore, PostgreSQL is not only a powerful enterprise database system, but also a database development platform for users to develop private, network and commercial software products.

II. Installation under Centos7

1. Install RPM

RPM package Manager, a packaging and installation tool for downloading packages from the Internet, is included in some Linux distributions.

Yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. Install the client

Yum install postgresql11

3. Install the server side

Yum install postgresql11-server

4. Install the dependency package

Yum install postgresql11-libsyum install postgresql11-contribyum install postgresql11-devel

5. Initialize and start

/ usr/pgsql-11/bin/postgresql-11-setup initdbsystemctl enable postgresql-11systemctl start postgresql-11

6. Reset password

Passwd postgres

7. Login service

Su-postgrespsql

8. Install the Vim command

Yum-y install vim*

9. Configure remote access

# modify 01vim / var/lib/pgsql/11/data/postgresql.conflisten_addresses = 'localhost' change to listen_addresses =' *'# modify 02vim / var/lib/pgsql/11/data/pg_hba.conf add content host all all 0.0.0.0and0 trust # # need to restart after modification

10. Open the port

Firewall-cmd-query-port=5432/tcpfirewall-cmd-add-port=5432/tcpfirewall-cmd-add-port=5432/tcp-zone=public-permanent

11. Restart

Systemctl restart postgresql- 11III. Create a database

1. Create a user

CREATE USER root01 WITH PASSWORD '123456 creating ROLE

2. Create a database

CREATE DATABASE db_01 OWNER root01;CREATE DATABASE

3. Permission grant

GRANT ALL PRIVILEGES ON DATABASE db_01 TO root01;GRANT

4. Exit command

\ Q: exit SQL editing exit: exit script IV. Basic operation

1. Create a table structure

User table CREATE TABLE pq_user (ID INT NOT NULL, user_name VARCHAR (32) NOT NULL, user_age int4 NOT NULL, create_time TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "pg_user_pkey" PRIMARY KEY ("id")) -- order table CREATE TABLE pq_order (id int not null, user_id int not null, order_no varchar (32) not null, goods varchar (20) not null, price money not null, count_num int default 1, create_time timestamp (6) default current_timestamp, constraint "pq_order_pkey" primary key ("id")

2. Write data

INSERT INTO pq_user ("id", "user_name", "user_age", "create_time") VALUES ('1mm,' user01', '18mm,' 2020-04-09 19user_age 44V 57.16154') INSERT INTO pq_order ("id", "user_id", "order_no", "goods", "price", "count_num", "create_time") VALUES ('1th,' 1th, 'NO20200329652362',','$12.20', '313', '2020-04-09 20 user_id 01user_id 09.660208')

3. Regular query

-- basic query select * from pq_user T1 where T1 on 2 'from pq_user T1 left join pq_order 2' from pq_user T1 left join pq_order T2 on t1.id=t2.user_id * from pq_user T1 left join pq_order T2 on t1.id=t2.user_id

4. Update and delete

-- Update data UPDATE pq_user SET "create_time" = '2020-04-09 1914 49 create_time 57' WHERE ("id" =' 2');-- Delete record DELETE FROM pq_user WHERE "id" = 2; 5. Source code address GitHub address https://github.com/cicadasmile/linux-system-baseGitEE address https://gitee.com/cicadasmile/linux-system-base

Recommended reading: environmental installation

The title of the article is to install Redis under Jdk8, Tomcat8 and MySQL5.7 under 01Centos7 and Rocketmq4.3 middleware under Redis cluster service 03Centos7, ZooKeeper3.4 middleware under 04Centos7 configuration console, ElasticSearch middleware under 05Centos7, Nginx,FastDFS file management middleware under 06Centos7 and ClickHouse database under 07Centos7.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.