• 【PostgreSql基础语法 】1、增删改查、where、limit、like模糊查询

    Shell命令框和Navicat联合使用

    一、数据库层面（shell命令行）

    1. 创建数据库

      postgres=# CREATE DATABASE school;

      • 1

    2. 查看数据库

      postgres=# \l

      • 1

    3. 选择数据库

      postgres=# \c school

      • 1

    4. 删除数据库

      postgres=# DROP DATABASE school;

      • 1

    二、表格层面（Navicat）

    1. 创建表格

      CREATE TABLE table_name(
   字段名称 字段数据类型,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 )
);

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8

    2. 删除表格

      DROP TABLE table_name;

      • 1

    3. 创建schema模式

      CREATE SCHEMA myschema.表名(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8

    4. 删除schema模式

      • 删除一个空模式

        DROP SCHEMA myschema;

        • 1

      • 删除一个模式及包含的所有对象

        DROP SCHEMA myschema CASCADE;

        • 1

    三、增删改查

    1. 增insert into

    sql语法：

    INSERT INTO TABLE_NAME (字段名1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN)

    INSERT INTO TABLE_NAME (字段名1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
// 举例
INSERT INTO book(id,name,age)VALUES(1,'张一山',24);
INSERT INTO book values (2,'王祖蓝',23);
INSERT INTO book values (3,'李一同');
INSERT INTO book values (4,'吴晓非');
INSERT INTO book values (5);
INSERT INTO book values (6,24);
// 遵循顺序对应
// 下面是结果
1	张一山	24
2	王祖蓝	23
3	李一同	null
4	吴晓非	null
5    null   null
6	 24     null

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    2. 查询select

    SQL语法：

    SELECT column1, column2,...columnN FROM table1 where 条件;

    // 查询所有字段使用*；
SELECT * FROM book
// 查询系统当前时间
SELECT CURRENT_TIMESTAMP; 

    • 1
    • 2
    • 3
    • 4

    3. UPDATE 改

    sql语法：

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

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

    • 1
    • 2
    • 3

    // 修改name=lcy字段的年龄age为16

    UPDATE company SET age=16 WHERE name='lcy';

    // 若不加where条件则 修改某个字段下的所有内容

    UPDATE company set salary=13000, age=18

    1 paul 18 加拿大 13000.00 2023-07-05
    2 allen 18 纽约 13000.00 2023-07-20
    3 teddy 18 加利福尼亚 13000.00 2023-07-06
    4 mark 18 旧金山 13000.00 2023-07-07
    5 ming 18 曼彻斯特 13000.00 2023-07-08
    8 zx 18 北京 13000.00 2023-07-15
    7 lqq 18 陕西 13000.00 2023-07-10
    6 james 18 曼哈顿 13000.00 2023-07-09

    // 和IN连用

    UPDATE company set salary=19000,age=19 WHERE id IN(1,2,3)

    // 和like连用

    UPDATE company set salary=700,age=19 WHERE name like '%y'

    5 ming 18 曼彻斯特 13000.00 2023-07-08
    6 james 18 曼哈顿 13000.00 2023-07-09
    1 paul 19 加拿大 19000.00 2023-07-05
    2 allen 19 纽约 19000.00 2023-07-20
    4 mark 21 旧金山 11000.00 2023-07-07
    7 lqq 19 陕西 700.00 2023-07-10
    8 zx 19 北京 700.00 2023-07-15
    3 teddy 19 加利福尼亚 700.00 2023-07-06

    4. DELETE 删除

    delete删除表的内容，drop删除表的结构
    sql语法：

    DROP TABLE 表名;
    TRUNCATE TABLE 表名

    语法：DELETE FROM table_name WHERE [condition];

    // 条件删除

    DELETE FROM company WHERE name='james'

    // 删除表里所有内容

    DELETE FROM company

    区别：
    使用 TRUNCATE TABLE 表名=DELETE FROM company但是前者不会产生日志

    四、 关键字

    1. AND

    AND 表示多个条件必须同时成立

    找出 AGE(年龄) 字段大于等于 25，并且 SALARY(薪资) 字段大于等于 16000 的数据：
    SELECT * FROM company WHERE age>=25 AND salary >= 16000

    2.OR

    OR 表示示多个条件中只需满足其中任意一个即可

    找出 AGE(年龄) 字段大于等于 25，或者 SALARY(薪资) 字段大于等于 26000 的数据：SELECT * FROM company WHERE age>=25 OR salary >= 26000

    3. NOT NULL 和 NULL

    在公司表中找出 AGE(年龄) 字段不为空/为空的记录：
    SELECT * FROM company WHERE age IS NOT NULL;
    SELECT * FROM company WHERE age IS NULL;

    4. LIKE 模糊查询

    模糊查询就需要用到like操作符。另外还有两个通配符。

    ①%通配符。%表示出现任意字符，出现的字符数可以是0，1，无数。

    ②_ 通配符。_ 表示出现有且仅有一次字符。

    • 如果没有使用以上两种通配符，LIKE 子句和等号 = 得到的结果是一样的。

    在 PostgreSQL 中，LIKE 子句是只能用于对字符进行比较，因此在下面例子中，我们要将整型数据类型转化为字符串数据类型。

    实例描述
    WHERE SALARY::text LIKE ‘200%’找出 SALARY 字段中以 200 开头的数据。
    WHERE SALARY::text LIKE ‘%200%’找出 SALARY 字段中含有 200 字符的数据。
    WHERE SALARY::text LIKE ‘_00%’找出 SALARY 字段中在第二和第三个位置上有 00 的数据。
    WHERE SALARY::text LIKE ‘2_%_%’找出 SALARY 字段中以 2 开头的字符长度大于 3 的数据。
    WHERE SALARY::text LIKE ‘%2’找出 SALARY 字段中以 2 结尾的数据
    WHERE SALARY::text LIKE ‘_2%3’找出 SALARY 字段中 2 在第二个位置上并且以 3 结尾的数据
    WHERE SALARY::text LIKE ‘2___3’找出 SALARY 字段中以 2 开头，3 结尾并且是 5 位数的数据

    如：在 COMPANY 表中找出 NAME(名字) 字段中以 Pa 开头的的数据：

    SELECT * FROM company WHERE name like 'pa%';//以pa开头的
    SELECT * FROM company WHERE name like '%l%';// name中含有字母l的
    SELECT * FROM company WHERE name like '%l';//以字母l结尾的

    当我们大概知道name后面有一个字符，前面有一个字符的时候，我们就可使用_

    SELECT * FROM company WHERE name like '_z_';

    4.1 like查找非字符类型

    // 查询年龄=19的
    SELECT * FROM company WHERE age::TEXT like '19'
    1 paul 19 加拿大 19000.00 2023-07-05
    2 allen 19 纽约 19000.00 2023-07-20
    7 lcx 19 陕西 700.00 2023-07-10

    // 查询年龄以2开头的
    SELECT * FROM company WHERE age::TEXT like '2_'
    // 查询工资以19开头的
    SELECT * FROM company WHERE salary::TEXT like '19%'

    4.2 😊sql中使用模糊查询[ LIKE CONCAT() 的使用 ] 😊

    • 目的：为防止like的使用导致sql注入问题，可以使用 LIKE CONCAT() 或者利用 $‘符

      举例：
      LIKE CONCAT()：SELECT id FROM cons_account WHERE cons_no LIKE concat('%', #{qry_cons_no}, '%') 建议使用like concat()
      $‘符：SELECT id FROM cons_account WHERE cons_no LIKE "%" ${qry_cons_no} "%"

    • LIKE CONCAT 还可以用于同时搜索符合当前条件的两个字段的信息

      查询name和age都为2的信息
      SELECT * FROM user WHERE CONCAT( name, age ) LIKE CONCAT( ‘%’, ‘2’, ‘%’ )

    5. IN 包含

    比如使用or时：薪水在where salary = 10000 or 20000时，就可以使用in子句
    SELECT * FROM company WHERE salary=17000 OR salary=19000;
    现在我们可以直接使用IN子句：可以是1个或多个
    SELECT * FROM company WHERE salary in(17000,19000,12000);
    SELECT * FROM company WHERE salary in(28000);

    5.2 NOT IN

    刚好和IN相反

    6. BETWEEN 和 AND 的联合使用 包含两个端点值

    // 找出工资在4500到9000之间的
    SELECT * FROM "company" WHERE salary BETWEEN 4500 AND 9000;

    7. LIMIT

    主要用于分页查询 一般分页关键字放在最后的

    limit 子句用于限制 SELECT 语句中查询的数据的数量。

    基本语法:

    SELECT column1, column2 FROM table_name LIMIT [no of rows]

    举例：
    // 返回四个数据
    SELECT * FROM company LIMIT 4
    // 返回四个数据并按照id排序
    SELECT * FROM company ORDER BY id LIMIT 4

    8. OFFSET

    • 概念：OFFSET 偏移量（初始偏移量为0）offset 1 ：意思就是从第2行开始
      注：LIMIT x OFFSET y 是为了与PostgreSQL兼容, 查询出的数据结果集为 [y+1,x+y]。

    • offset使用： offset x

    • sql语法：

      // 从x+1行开始 取数据
      SELECT * FROM "company" OFFSET 1从第二行开始取数据 含x+1行的数据

    • 配合limit使用

      // 全部数据

      5 ming 18 曼彻斯特 13000.00 2023-07-08
      1 paul 19 加拿大 19000.00 2023-07-05
      2 allen 19 纽约 19000.00 2023-07-20
      4 mark 21 旧金山 11000.00 2023-07-07
      7 lcx 19 陕西 700.00 2023-07-10
      8 zx 19 北京 700.00 2023-07-15
      3 teddy 19 加利福尼亚 700.00 2023-07-06
      6 ljj 28 shanxi 900.00 2023-07-19

      // 从第2行开始，取4行数据

      SELECT * FROM "company" LIMIT 4 OFFSET 1

      1 paul 19 加拿大 19000.00 2023-07-05
      2 allen 19 纽约 19000.00 2023-07-20
      4 mark 21 旧金山 11000.00 2023-07-07
      7 lcx 19 陕西 700.00 2023-07-10

    9. ORDER BY

    • 概念：排序， 一般配合where子句一起使用

    • 基础语法

      SELECT column-list
FROM   table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];


      • 1
      • 2
      • 3
      • 4
      • 5

    • 默认是升序排序 即 order by column asc，若想改成降序则使用 desc

      // 按照id降序排序
      SELECT * FROM company ORDER BY id desc

    10. GROUP BY ：

    • 基础知识：分组，配合select使用 和聚合函数使用

      GROUP BY子句通常与聚合函数一起用于统计数据。
      GROUP BY子句将行排列成组，聚合函数返回每个组的统计量。

      • 概念：

        目的： GROUP BY 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。

        位置：GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。

        SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

      • 语法：

        SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN

        • 1
        • 2
        • 3
        • 4

      • 使用 group by 时 只能select 出 分组字段和聚合函数

      • 举例

        // 全部

        5 ming 18 曼彻斯特 13000.00 2023-07-08
        1 paul 19 加拿大 19000.00 2023-07-05
        2 allen 19 纽约 19000.00 2023-07-20
        4 mark 21 旧金山 11000.00 2023-07-07
        7 lcx 19 陕西 700.00 2023-07-10
        8 zx 19 北京 700.00 2023-07-15
        3 teddy 19 加利福尼亚 700.00 2023-07-06
        6 ljj 28 shanxi 900.00 2023-07-19

        // 根据name进行分组，并且找出每个人的工资总额

        SELECT name,SUM(salary) FROM company GROUP BY name

        teddy 700.00
        allen 19000.00
        ljj 900.00
        lcx 700.00
        paul 19000.00
        ming 13000.00
        mark 11000.00
        zx 700.00

    10.1 何为分组

    目前我们表中的数据人名都是一个，我们现在往表中加入几条数据

    1 paul 19 加拿大 19000.00 2023-07-05
    2 allen 19 纽约 19000.00 2023-07-20
    4 mark 21 旧金山 11000.00 2023-07-07
    7 lcx 19 陕西 700.00 2023-07-10
    8 zx 19 北京 700.00 2023-07-15
    3 teddy 19 加利福尼亚 700.00 2023-07-06
    6 ljj 28 shanxi 900.00 2023-07-19
    9 paul 24 Houston 20000.00 2023-07-11
    10 James 44 Norway 5000.00 2023-07-19
    11 James 45 Texas 5000.00 2023-07-20

    再次进行一次分组看
    SELECT name,SUM(salary) FROM company GROUP BY name

    teddy 700.00
    allen 19000.00
    James 10000.00
    ljj 900.00
    lcx 700.00
    paul 39000.00
    ming 13000.00
    mark 11000.00
    zx 700.00
    发现james和paul已经进行了合并相加

    // 根据name进行分组，并且找出每个人的工资总额，根据工资总额进行排序

    SELECT name,SUM(salary) FROM company GROUP BY name ORDER BY SUM(salary)

    teddy 700.00
    lcx 700.00
    zx 700.00
    ljj 900.00
    mark 11000.00
    ming 13000.00
    paul 19000.00
    allen 19000.00

    // 根据name进行分组，并且找出每个人的工资总额，且筛选出年龄大于20的数据，最后根据工资总额进行排序

    SELECT name,SUM(salary) FROM company WHERE age>20 GROUP BY name ORDER BY SUM(salary)

    ljj 900.00
    mark 11000.00

    11. HAVING 用来筛选分组后的各项数据

    • 基础语法：HAVING 是在由 GROUP BY 子句创建的分组基础上设置条件，所以要放在group by后面

       SELECT column1, column2
 FROM table1
 WHERE [ conditions ]
 GROUP BY column1, column2
 HAVING [ conditions ]
 ORDER BY column1, column2

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    • 位置：

      AVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面

    • 举例

      // 找出根据 NAME 字段值进行分组，并且 name(名称) 字段的计数少于 2 数据

      select name from company group by name having count(name) < 2

      teddy
      allen
      ljj
      lcx
      ming
      mark
      zx

      // 找出根据 NAME 字段值进行分组，并且 name(名称) 字段的计数大于 1 数据

      select name from company group by name having count(name) > 1

      James
      paul
      其他： SELECT id,salary FROM company GROUP BY salary,id HAVING MAx(age)>23 ORDER BY id

    12. DISTINCT 去重复 与select连用

    • 概念：

      DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录

    • 基础语法
      SELECT DISTINCT column1, column2,.....columnN FROM table_name WHERE [condition]

    • 举例
      剔除company中重复的name 我们可以在结果中看到去掉了重复的james和paul

    SELECT DISTINCT name FROM company

    teddy
    allen
    James
    ljj
    lcx
    paul
    ming
    mark
    zx

  • 相关阅读:
    【第二十篇】-Maven IntelliJ
    项目实战（依旧还是登录认证，JWT解析异常处理，授权信息处理）
    【新知实验室-TRTC开发】实时音视频之欢度世界杯
    markdown绘制流程图相关代码片段记录
    python_django农产品物流信息服务系统6m344
    悄悄上线：CSS @starting-style 新规则
    架构师系列-消息中间件（九）- RocketMQ 进阶（三）-消费端消息保障
    Spring AOP如何基于AspectJ XML开发呢？
    CentOS-7安装Docker并设置开机自启动docker镜像
    2023-10-10 mysql-{mysql_alter_table}-出错后回滚-记录
  • 原文地址：https://blog.csdn.net/m0_48904153/article/details/134247011