一、各语言中sql语句的写法
在PostgreSQL中,sql语句的写法总是比较简单且常见。不过在java或python中,由于需考虑到数据格式的匹配,sql语句的写法有些特殊,现将其总结如下。
java中的sql语句
数字型
sql1 = "SELECT id FROM account WHERE age = 20"; int age = 20; sql2 = "SELECT id FROM account WHERE age =" + age ;
字符串型
sql3 = "SELECT id FROM account WHERE name = 'marry'"; String name = "marry"; sql4 = "SELECT id FROM account WHERE name ='" + name + "'" ;
python中的sql语句
类似地,python中的sql语句如下,
数字型
sql1 = "SELECT id FROM account WHERE age = 20"; age = 20 sql2 = "SELECT id FROM account WHERE age =" + str(age) ;
字符串型
sql3 = "SELECT id FROM account WHERE name = 'marry'"; name = "marry" sql4 = "SELECT id FROM account WHERE name ='" + str(name) + "'" ;
至于日期,则按字符串同样处理。
二、问题:表的引号
PostgreSQL中,当定义表名为大写或为关键字时,系统会自动加上双引号,比如定义表名为“user”。
此时,在PostgreSQL中执行的sql语句如“sql5”,表名必须加”“;但在java中对该表名还需加上转义字符才行,如“sql6”。
sql5 = SELECT id FROM "user" WHERE age = 20;
sql6 = "SELECT id FROM \"user\" WHERE age = 20";