Open henryliangt opened 1 year ago
how many passwords do we have ? pgadmin pass. db pass ?
su postgres psql -U postgres -d postgres -h 127.0.0.1 -p 5432
?? ALTER USER postgres WITH PASSWORD 'new_password';
create user postgres with superuser password 'postgres';
/usr/pgsql-9.6/bin/psql --version psql (PostgreSQL) 9.6.20
\l List all databases
\c postgres Connect &show database
\dn
\d 列出当前数据库的所有表格。
\d [table_name]:列出某一张表格的结构。
\du:列出所有用户。
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');
SELECT * FROM user_tbl;
UPDATE user_tbl set name = '李四' WHERE name = '张三';
DELETE FROM user_tbl WHERE name = '李四' ;
ALTER TABLE user_tbl ADD email VARCHAR(40);
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
ALTER TABLE user_tbl DROP COLUMN email;
ALTER TABLE user_tbl RENAME TO backup_tbl;
DROP TABLE IF EXISTS backup_tbl;
\h:查看SQL命令的解释,比如\h select。 \?:查看psql命令列表。 \l:列出所有数据库。 \c [database_name]:连接其他数据库。 \d:列出当前数据库的所有表格。 \d [table_name]:列出某一张表格的结构。 \du:列出所有用户。 \e:打开文本编辑器。 \conninfo:列出当前数据库和连接的信息。
write the relational algebra?
relational algebra $\pi$
σ
SELECT S.Name FROM STUDNT S, Transcript T WHERE S,studId = T.studId AND T.uosCodee IN ('SSS' , 'AAA')
FROM STUDENT S , Transcript T
Project P σ
Select S $\pi$
Rename R
Join J ⋈
WIKI 投影 (π) 选择 (σ) 重命名 (ρ) 自然连接 (⋈) θ-连接和相等连接 半连接 (⋉)(⋊) 反连接 (▷) 除法 (÷)
集合的差运算(set-defference)==>相当于SQL语句中的EXCEPT 格式:(关系r)-(关系s)
Select all author who never published a book with Acme publishers
SELECT AUTHOR FROM
EXCEPT
SELECT AUTHOR FROM EMP WHERE PUB
Get version. SELECT version();
to path: C:\Program Files\PostgreSQL\14\bin
check service: services.msc check port: netstat -a
netsh firewall show state
"local" is for Unix domain socket connections only
local all all trust
IPv4 local connections:
host all all 127.0.0.1/32 trust host all all 0.0.0.0/0 md5 host postgres postgres 0.0.0.0/0 md5 host usyd usyd 0.0.0.0/0 md5