程序员必备的免费自然语言转SQL (摸鱼)工具，人手必备

程序员必备的免费自然语言转SQL (摸鱼)工具，人手必备

1、SQL查询中添加过滤

请对附加的SQL查询添加筛选条件，仅显示在加州居住且消费总额排名前10位的客户。

“SELECT customer_name, SUM(order_total) AS total_spent FROM orders GROUP BY customer_name ORDER BY total_spent DESC;”
1

2、编写一条SQL语句，检索所有订单中客户名称、订单日期、产品名称和数量，其中订单总额大于$1,000且产品名称包含单词“chair”

SELECT customers.customer_name, orders.order_date, order_details.product_name, order_details.quantity
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_details ON orders.order_id = order_details.order_id
WHERE orders.order_total > 1000 AND order_details.product_name LIKE '%chair%';
1
2
3
4
5

3、按日期范围过滤数据

显示 2022 年 1 月 1 日至 2022 年 3 月 1 日之间的所有订单。 SELECT * FROM orders WHERE
order_date BETWEEN ‘2022-01-01’ AND ‘2022-03-01’;

private static String getString13(String text) {
        String regs[] = {"(\\d{4})年(\\d{1,2})月(\\d{1,2})日"};
        for (String reg : regs) {
            Pattern pattern = Pattern.compile(reg);
            Matcher matcher = pattern.matcher(text);
            if (matcher.find()) {
                String year = matcher.group(1);
                String month = String.format("%02d", Integer.parseInt(matcher.group(2)));
                String day = String.format("%02d", Integer.parseInt(matcher.group(3)));
                return year + "-" + month + "-" + day;
            }
        }
        return "";
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

4、不仅仅是 SELECT - 还可以Update

使用 SQL 更新电子产品分类是electronics的价格，并将价格提高10%。
UPDATE products SET price =
price * 1.1 WHERE category = ‘electronics’;

5、创建数据库

BITO:
https://share.bito.co/static/share?aid=1edd448a-544c-4526-a96d-59bc04166709

public static String generatorSqlQuery2(String sql, String demand, String table){
        String keyword = "",d2="";
        if(demand.contains("查询")||demand.contains("select")||demand.contains("筛选")){
            keyword = "SELECT * FROM "+table+" ";
            String d = keyword,text = demand;
            keyword = getColums(d, text); //第二部分 column
//            d2 = getColums51(d2, text); //第5部分 set
            keyword = getColums5(keyword, text, 0); //第5部分 where
            //第3部分 GROUP BY 总额
            keyword = getColums3(keyword, text); //第3部分 column
            //第4部分 ORDER BY 排名
            keyword = getColums4(keyword, text); //第4部分 column

        }else if(demand.contains("删除数据")||demand.contains("delete")){
            keyword = "DELETE";
        }else if(demand.contains("删除")||demand.contains("drop")){
            keyword = "DROP";
        }else if(demand.contains("修改数据")||demand.contains("update")||demand.contains("更新")){
            keyword = "UPDATE ";
            String d = keyword,text = demand;
            keyword = getColums(d, text); //第二部分 column
            keyword = getColums51(keyword, text); //第5部分 set
            keyword = getColums5(keyword, text, 0); //第5部分 where
        }else if(demand.contains("修改")||demand.contains("alter")){
            keyword = "ALTER";
        }else if(demand.contains("增加")||demand.contains("insert")){
            keyword = "INSERT";
        }else if(demand.contains("创建")||demand.contains("create")){
            String name = getText5(demand);
            keyword = "create database if not exists "+name+" character set utf8";
        }else if(demand.contains("显示")||demand.contains("show")){
            keyword = "SHOW";
        }else if(demand.contains("清空")||demand.contains("truncate")){
            keyword = "TRUNCATE";
        }
        keyword = keyword+";";
        return keyword;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

查询代码的书写顺序和执行顺序

• 查询语句的书写顺序：SELECT-FROM-WHERE-GROUP BY-HAVING-ORDER BY-LIMIT
• 查询语句的执行顺序：FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY-LIMIT
• 语法：
• SELECT selection_list /*要查询的列名称
  //
  // *FROM table_list /要查询的表名称/
  // *
  // *WHERE condition /行条件/
  // *
  // *GROUP BY grouping_columns /对结果分组/
  // *
  // *HAVING condition /分组后的行条件/
  // *
  // *ORDER BY sorting_columns /对结果分组/
  // *
  // *LIMIT offset_start,row_count /结果限定/