DB——postgresql的安装与使用与备份

今天药忘吃喽~ 2022-05-09 03:56 324阅读 0赞

**安装**

*  Ubuntu / Debian  
    `sudo apt-get install postgresql`  
    此命令默认创建了一个名为　postgres　的linux用户,无密码。相当于mysql中无密码的root 用户。
 *  Mac  
    移步[mac下brew安装postgresql][mac_brew_postgresql]查看详情

**Postgresql 约定**

>  *  单引号用来标识实际的值，双引号用来标识表名（table name）或列名（column name）等数据库中存在的值。
>  *  创建数据库或数据表时如果库名或表名不加双引号会被自动转换为全部小写

**默认客户端操作**  
*登录客户端：*　`sudo -u postgres psql`  
*创建用户：*　`create user <username> with password <password>;`  
*创建数据库(指定拥有者)：*　`create database <databasename> owner <username>`  
*赋予权限：*　`grant all privileges on database <databasename> to <username>;`  
*列出数据库：*　`\l`  
*切换数据库：*　`\c [databasename]`  
*切换用户：*　`\c - [username]`  
*列出数据表：*　`\dt`  
*查看表结构：*　`\d [tablename]`  
*查看表索引：*　`\di`

**常规操作**  
*登录数据库：*　`psql -U dbuser -d databasename -h localhost -p 5432;`  
*修改密码：*　`alter user <username> with password <password>;`  
*删除密码：*　`sudo -u <username> psql -d <databasename>;`  
*删除用户：*　`DROP POLE <username>;`  
*删除库：*　`DROP DATABASE <databasename>;`  
*删除表：*　`DROP TABLE 'tablename';`  
*创建表：*

CREATE TABLE table_name(  
       column1 datatype,  
       column2 datatype,  
       column3 datatype,  
       .....  
       columnN datatype,  
       PRIMARY KEY( one or more columns )  
    );

**备份与恢复**

*表的备份与恢复*  
备份：`pg_dump -U <username> -t <tablename> -d <databasename> -h <host> -p <port> > back.sql`  
恢复：`psql -U <username> -t <tablename> -d <databasename> -h <host> -p <port> < back.sql`

*库的备份与恢复*  
备份：`pg_dump -U <username> -h <host> -p <port> <databasename>> back.sql`  
恢复：`psql -U <username> -h <host> -p <port> <databasename> < back.sql`

**数据导出**

*  客户端导出  
    `psql -U <username> -d <dbname> -h <host> -o file.csv -c 'select 语句'；`
 *  服务端导出  
    `COPY <tablename> TO 'data.csv' WITH csv;`：导出全表数据  
    `COPY 'select 语句' TO 'data.csv' WITH csv;`：导出选择的数据  
    \-服务端导入  
    `COPY <tablename> from 'data.csv' WITH csv HEADER;`  
    `若csv数据中含有标题，sql语句就添加HEADER，如果没有，就将HEADER除去`

**增删改查**

*插入：*

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  VALUES (value1, value2, value3,...valueN);

*查询：*

SELECT "column1", "column2".."column" FROM "table_name";

*更改：*

UPDATE table_name  SET column1 = value1, column2 = value2...., columnN = valueN  WHERE [condition];

*添加字段*

ALTER TABLE 'table_name' ADD 'new_column' 'data_type';

*更新字段类型*

ALTER TABLE 'table_name' ALTER COLUMN 'column' SET 'NOT NULL 一些约束等';

*更新字段名*

ALTER TABLE 'table_name' ALTER COLUMN 'column' to 'new_column';

*删除字段*

ALTER TABLE 'table_name' DROP COLUMN 'column';

*表名更改*

ALTER TABLE 'table_name' RENAME TO 'new_table_name';

*删除：*

DELETE FROM table_name  WHERE [condition];

*排序：*

SELECT "column1", "column2".."column" FROM "table_name" ORDER BY column1 ASC;

跟在查询语句后。后接处理字段和规则，升序：ASC（默认）　降序：DESC

*分组：*

SELECT "column1", "column2".."column" FROM "table_name" GROUP BY 'column1';

跟在查询语句后。后接处理字段，可以将重复的数据合并，减少冗余。

*条件满足：*

SELECT "column1", "column2".."column" FROM "table_name" GROUP BY 'column1' having 'column2'>100;

跟在查询语句后。后接条件，达到筛选的效果。  
where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。  
having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

*条件语句：*

*  AND 条件
 *  OR 条件
 *  AND & OR 条件
 *  NOT 条件
 *  LIKE 条件
 *  IN 条件 `在...里`
 *  NOT IN 条件
 *  BETWEEN 条件　`在　. 和 .　之间`

**连接查询**

*  内连接(INNER JOIN)  
    `此连接返回满足连接条件的多个表中的所有行。`

SELECT table1.columns, table2.columns  
    FROM table1  
    INNER JOIN table2  
    ON table1.common_filed = table2.common_field;

*  左外连接(LEFT OUTER JOIN)  
    `左外连接返回从“ON”条件中指定的左侧表中的所有行，只返回满足条件的另一个表中的行。`

SELECT table1.columns, table2.columns  
    FROM table1  
    LEFT OUTER JOIN table2  
    ON table1.common_filed = table2.common_field;

*  右外连接(RIGHT OUTER JOIN)  
    `右外连接返回从“ON”条件中指定的右侧表中的所有行，只返回满足条件的另一个表中的行。`

SELECT table1.columns, table2.columns  
    FROM table1  
    RIGHT OUTER JOIN table2  
    ON table1.common_filed = table2.common_field;

*  全连接(FULL OUTER JOIN)  
    `全外连接从左表和左表中返回所有行。 它将NULL置于不满足连接条件的位置。`

SELECT table1.columns, table2.columns  
    FROM table1  
    FULL OUTER JOIN table2  
    ON table1.common_filed = table2.common_field;

*  跨连接(CROSS JOIN)  
    `跨连接(CROSS JOIN)将第一个表的每一行与第二个表的每一行相匹配。 它也被称为笛卡尔积。 如果table1具有“x”行，而table2具有“y”行，则所得到的表将具有(x * y)行。`