作者：瀚高PG實(shí)驗(yàn)室 （Highgo PG Lab）- 波羅
以下示例可以協(xié)助理解PG或HighgoDB中用戶、數(shù)據(jù)庫(kù)、表空間以及數(shù)據(jù)庫(kù)模式、對(duì)象的創(chuàng)建及用戶權(quán)限管理，詳細(xì)如下：

--psq客戶端連接數(shù)據(jù)庫(kù) psql -h ip -U username -p port -d dbname--查看當(dāng)前用戶和當(dāng)前連接的數(shù)據(jù)庫(kù) select user; select current_database();--創(chuàng)建用戶 create user jwadm password '123#adm'; create user tea password '123#tea'; create user stu password '123#stu';--如果要?jiǎng)h除用戶需要?jiǎng)h除用戶下的數(shù)據(jù)庫(kù)和表空間對(duì)象 --drop user jwadm; --drop user tea; --drop user stu;--創(chuàng)建表空間 CREATE TABLESPACE tbls_jw OWNER jwadm LOCATION '/pgdata/data/jwdb1';--查看表空間 \db--創(chuàng)建數(shù)據(jù)庫(kù) create database jwdb with owner jwadm encoding 'UTF8' tablespace tbls_jw;--查看數(shù)據(jù)庫(kù) \l --或者 select * from pg_database; \x--將數(shù)據(jù)庫(kù)jwdb的權(quán)限授予jwadm grant all on database jwdb to jwadm;--以用戶jwadmin切換連接到創(chuàng)建的數(shù)據(jù)庫(kù)jwdb \c jwdb jwadm--創(chuàng)建模式 create schema jwadm;--模式授權(quán)給用戶，ALL關(guān)鍵字將包含CREATE和USAGE兩種權(quán)限 grant all on schema jwadm to jwadm; --revoke all on schema wsgs from username;grant all on schema jwadm to tea; grant all on schema jwadm to stu;--刪除模式： --DROP SCHEMA myschema; --如果要?jiǎng)h除模式及其所有對(duì)象，請(qǐng)使用級(jí)聯(lián)刪除： --DROP SCHEMA myschema CASCADE;--改變登錄db的默認(rèn)schema SHOW search_path; SET search_path TO jwadm; ALTER database "jwdb" SET search_path TO jwadm;--查看權(quán)限 select * from information_schema.table_privileges where grantee='jwadm'; \x--create語(yǔ)法 創(chuàng)建表 create table student(sid varchar(10) primary key,sname varchar(10),sage int,ssex varchar(10)); create table course(cid varchar(10) primary key,cname varchar(10),tid varchar(10)); create table teacher(tid varchar(10) primary key,tname varchar(10)); create table score(sid varchar(10),cid varchar(10),score decimal(18,1));--insert語(yǔ)法 插入數(shù)據(jù) insert into Student values('01' , '趙雷' , 18 , '男'); insert into Student values('02' , '錢電' , 18 , '男'); insert into Student values('03' , '孫風(fēng)' , 17 , '男'); insert into Student values('04' , '李云' , 18 , '男'); insert into Student values('05' , '周梅' , 17 , '女'); insert into Student values('06' , '吳蘭' , 19 , '女'); insert into Student values('07' , '鄭竹' , 18 , '女'); insert into Student values('08' , '王菊' , 18 , '女');insert into Course values('01' , '語(yǔ)文' , '02'); insert into Course values('02' , '數(shù)學(xué)' , '01'); insert into Course values('03' , '英語(yǔ)' , '03');insert into Teacher values('01' , '張三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五');insert into score values('01' , '01' , 80); insert into score values('01' , '02' , 90); insert into score values('01' , '03' , 99); insert into score values('02' , '01' , 70); insert into score values('02' , '02' , 60); insert into score values('02' , '03' , 80); insert into score values('03' , '01' , 82); insert into score values('03' , '02' , 80); insert into score values('03' , '03' , 87); insert into score values('04' , '01' , 50); insert into score values('04' , '02' , 30); insert into score values('04' , '03' , 20); insert into score values('05' , '01' , 76); insert into score values('05' , '02' , 87); insert into score values('06' , '01' , 31); insert into score values('06' , '03' , 34); insert into score values('07' , '02' , 89); insert into score values('07' , '03' , 98);--查看表 --\dt--查詢表授權(quán)給用戶 grant select on student to tea; grant select on student to stu; grant select on teacher to stu; grant select on teacher to tea; grant select on score to tea; --表列級(jí)別授權(quán)給用戶 --grant select(id,name) on student to stu; --驗(yàn)證用戶權(quán)限 \c jwdb jwadm select * from student; -----------------------------------------

總結(jié)

以上是生活随笔為你收集整理的PostgreSQL 之 学籍管理示例的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。