{"id":2320,"date":"2025-03-04T08:37:24","date_gmt":"2025-03-04T00:37:24","guid":{"rendered":"https:\/\/www.laixuexila.com\/?p=2320"},"modified":"2025-03-04T08:37:24","modified_gmt":"2025-03-04T00:37:24","slug":"sqlite-%e8%bf%90%e7%ae%97%e7%ac%a6","status":"publish","type":"post","link":"https:\/\/www.laixuexila.com\/index.php\/2025\/03\/04\/sqlite-%e8%bf%90%e7%ae%97%e7%ac%a6\/","title":{"rendered":"SQLite \u8fd0\u7b97\u7b26"},"content":{"rendered":"\n
\n

SQLite \u652f\u6301\u591a\u79cd\u8fd0\u7b97\u7b26\uff0c\u53ef\u4ee5\u7528\u4e8e\u67e5\u8be2\u548c\u6570\u636e\u64cd\u4f5c\u3002\u8fd0\u7b97\u7b26\u53ef\u4ee5\u5206\u4e3a\u4ee5\u4e0b\u51e0\u7c7b\uff1a<\/p>\n<\/blockquote>\n\n\n\n

\n\n\n\n

1. \u6bd4\u8f83\u8fd0\u7b97\u7b26<\/strong><\/h2>\n\n\n\n

\u8fd9\u4e9b\u8fd0\u7b97\u7b26\u7528\u4e8e\u5728 SQL \u67e5\u8be2\u4e2d\u8fdb\u884c\u6bd4\u8f83\u3002<\/p>\n\n\n\n

\u8fd0\u7b97\u7b26<\/th>\u63cf\u8ff0<\/th>\u793a\u4f8b<\/th><\/tr><\/thead>
=<\/code><\/td>\u7b49\u4e8e<\/td>WHERE age = 30<\/code><\/td><\/tr>
!=<\/code> \u6216 <><\/code><\/td>\u4e0d\u7b49\u4e8e<\/td>WHERE age != 30<\/code> \u6216 WHERE age <> 30<\/code><\/td><\/tr>
><\/code><\/td>\u5927\u4e8e<\/td>WHERE age > 30<\/code><\/td><\/tr>
<<\/code><\/td>\u5c0f\u4e8e<\/td>WHERE age < 30<\/code><\/td><\/tr>
>=<\/code><\/td>\u5927\u4e8e\u7b49\u4e8e<\/td>WHERE age >= 30<\/code><\/td><\/tr>
<=<\/code><\/td>\u5c0f\u4e8e\u7b49\u4e8e<\/td>WHERE age <= 30<\/code><\/td><\/tr>
BETWEEN<\/code><\/td>\u8303\u56f4\u68c0\u67e5\uff0c\u68c0\u67e5\u4e00\u4e2a\u503c\u662f\u5426\u5728\u4e24\u4e2a\u503c\u4e4b\u95f4\uff08\u5305\u542b\u8fb9\u754c\uff09<\/td>WHERE age BETWEEN 20 AND 30<\/code><\/td><\/tr>
IN<\/code><\/td>\u68c0\u67e5\u4e00\u4e2a\u503c\u662f\u5426\u5b58\u5728\u4e8e\u4e00\u7ec4\u503c\u4e2d<\/td>WHERE age IN (20, 30, 40)<\/code><\/td><\/tr>
LIKE<\/code><\/td>\u6a21\u7cca\u5339\u914d\u5b57\u7b26\u4e32<\/td>WHERE name LIKE 'A%'<\/code><\/td><\/tr>
IS NULL<\/code><\/td>\u68c0\u67e5\u4e00\u4e2a\u503c\u662f\u5426\u4e3a NULL<\/td>WHERE name IS NULL<\/code><\/td><\/tr>
IS NOT NULL<\/code><\/td>\u68c0\u67e5\u4e00\u4e2a\u503c\u662f\u5426\u4e0d\u662f NULL<\/td>WHERE name IS NOT NULL<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n

2. \u903b\u8f91\u8fd0\u7b97\u7b26<\/strong><\/h2>\n\n\n\n

\u8fd9\u4e9b\u8fd0\u7b97\u7b26\u7528\u4e8e\u8fde\u63a5\u591a\u4e2a\u6761\u4ef6\u3002<\/p>\n\n\n\n

\u8fd0\u7b97\u7b26<\/th>\u63cf\u8ff0<\/th>\u793a\u4f8b<\/th><\/tr><\/thead>
AND<\/code><\/td>\u4e24\u4e2a\u6761\u4ef6\u90fd\u4e3a\u771f\u65f6\u8fd4\u56de\u771f<\/td>WHERE age > 20 AND age < 30<\/code><\/td><\/tr>
OR<\/code><\/td>\u4efb\u4e00\u6761\u4ef6\u4e3a\u771f\u65f6\u8fd4\u56de\u771f<\/td>WHERE age < 20 OR age > 30<\/code><\/td><\/tr>
NOT<\/code><\/td>\u5bf9\u6761\u4ef6\u53d6\u53cd<\/td>WHERE NOT age = 30<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n

3. \u7b97\u672f\u8fd0\u7b97\u7b26<\/strong><\/h2>\n\n\n\n

\u8fd9\u4e9b\u8fd0\u7b97\u7b26\u7528\u4e8e\u5bf9\u6570\u503c\u8fdb\u884c\u8fd0\u7b97\u3002<\/p>\n\n\n\n

\u8fd0\u7b97\u7b26<\/th>\u63cf\u8ff0<\/th>\u793a\u4f8b<\/th><\/tr><\/thead>
+<\/code><\/td>\u52a0\u6cd5<\/td>SELECT price + tax FROM products<\/code><\/td><\/tr>
-<\/code><\/td>\u51cf\u6cd5<\/td>SELECT price - discount FROM products<\/code><\/td><\/tr>
*<\/code><\/td>\u4e58\u6cd5<\/td>SELECT quantity * price FROM products<\/code><\/td><\/tr>
\/<\/code><\/td>\u9664\u6cd5<\/td>SELECT total \/ quantity FROM orders<\/code><\/td><\/tr>
%<\/code><\/td>\u53d6\u4f59<\/td>SELECT price % 2 FROM products<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n

4. \u5b57\u7b26\u4e32\u8fd0\u7b97\u7b26<\/strong><\/h2>\n\n\n\n

\u8fd9\u4e9b\u8fd0\u7b97\u7b26\u7528\u4e8e\u5904\u7406\u5b57\u7b26\u4e32\u3002<\/p>\n\n\n\n

\u8fd0\u7b97\u7b26<\/th>\u63cf\u8ff0<\/th>\u793a\u4f8b<\/th><\/tr><\/thead>
||<\/code><\/td>\u5b57\u7b26\u4e32\u8fde\u63a5<\/td>SELECT first_name || ' ' || last_name FROM employees<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n

5. \u805a\u5408\u51fd\u6570<\/strong><\/h2>\n\n\n\n

\u8fd9\u4e9b\u8fd0\u7b97\u7b26\u4e0e\u805a\u5408\u51fd\u6570\u4e00\u8d77\u4f7f\u7528\uff0c\u5e38\u7528\u4e8e\u67e5\u8be2\u6c47\u603b\u6570\u636e\u3002<\/p>\n\n\n\n

\u8fd0\u7b97\u7b26<\/th>\u63cf\u8ff0<\/th>\u793a\u4f8b<\/th><\/tr><\/thead>
COUNT<\/code><\/td>\u8ba1\u7b97\u884c\u6570<\/td>SELECT COUNT(*) FROM users<\/code><\/td><\/tr>
SUM<\/code><\/td>\u8ba1\u7b97\u603b\u548c<\/td>SELECT SUM(price) FROM products<\/code><\/td><\/tr>
AVG<\/code><\/td>\u8ba1\u7b97\u5e73\u5747\u503c<\/td>SELECT AVG(age) FROM users<\/code><\/td><\/tr>
MIN<\/code><\/td>\u83b7\u53d6\u6700\u5c0f\u503c<\/td>SELECT MIN(age) FROM users<\/code><\/td><\/tr>
MAX<\/code><\/td>\u83b7\u53d6\u6700\u5927\u503c<\/td>SELECT MAX(age) FROM users<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n

6. \u5e38\u89c1\u64cd\u4f5c<\/strong><\/h2>\n\n\n\n

1. ISNULL<\/code> \u64cd\u4f5c\u7b26<\/strong><\/h3>\n\n\n\n

ISNULL<\/code> \u7528\u4e8e\u66ff\u4ee3 NULL \u503c\uff0c\u53ef\u4ee5\u4e3a NULL \u503c\u63d0\u4f9b\u9ed8\u8ba4\u503c\u3002<\/p>\n\n\n\n

\u8fd0\u7b97\u7b26<\/th>\u63cf\u8ff0<\/th>\u793a\u4f8b<\/th><\/tr><\/thead>
IFNULL(expression, default_value)<\/code><\/td>\u5982\u679c\u8868\u8fbe\u5f0f\u7684\u503c\u662f NULL\uff0c\u5219\u8fd4\u56de default_value<\/code>\u3002<\/td>SELECT IFNULL(age, 0) FROM users<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n

7. \u793a\u4f8b<\/strong><\/h2>\n\n\n\n

1. \u4f7f\u7528\u6bd4\u8f83\u8fd0\u7b97\u7b26<\/strong><\/h3>\n\n\n\n

\u67e5\u8be2\u5e74\u9f84\u5927\u4e8e 25 \u7684\u7528\u6237\uff1a<\/p>\n\n\n\n

SELECT * FROM users WHERE age > 25;<\/code><\/pre>\n\n\n\n
2. \u4f7f\u7528\u903b\u8f91\u8fd0\u7b97\u7b26<\/strong><\/h3>\n\n\n\n
\u67e5\u8be2\u5e74\u9f84\u5728 20 \u5230 30 \u5c81\u4e4b\u95f4\uff0c\u4e14\u59d3\u540d\u4ee5 A<\/code> \u5f00\u5934\u7684\u7528\u6237\uff1a<\/p>\n\n\n\n
SELECT * FROM users WHERE age BETWEEN 20 AND 30 AND name LIKE 'A%';<\/code><\/pre>\n\n\n\n
3. \u4f7f\u7528\u7b97\u672f\u8fd0\u7b97\u7b26<\/strong><\/h3>\n\n\n\n
\u8ba1\u7b97\u6240\u6709\u4ea7\u54c1\u7684\u4ef7\u683c\u52a0\u4e0a\u7a0e\u540e\u7684\u603b\u91d1\u989d\uff1a<\/p>\n\n\n\n
SELECT price + tax FROM products;<\/code><\/pre>\n\n\n\n
4. \u4f7f\u7528\u5b57\u7b26\u4e32\u8fd0\u7b97\u7b26<\/strong><\/h3>\n\n\n\n
\u5c06\u59d3\u548c\u540d\u62fc\u63a5\u6210\u5168\u540d\uff1a<\/p>\n\n\n\n
SELECT first_name || ' ' || last_name FROM employees;<\/code><\/pre>\n\n\n\n
5. \u4f7f\u7528\u805a\u5408\u51fd\u6570<\/strong><\/h3>\n\n\n\n
\u67e5\u8be2 users<\/code> \u8868\u4e2d\u6240\u6709\u7528\u6237\u7684\u5e73\u5747\u5e74\u9f84\uff1a<\/p>\n\n\n\n
SELECT AVG(age) FROM users;<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u603b\u7ed3<\/strong><\/h2>\n\n\n\n
    \n
  • \u6bd4\u8f83\u8fd0\u7b97\u7b26<\/strong>\uff1a\u7528\u4e8e\u6bd4\u8f83\u6570\u636e\uff08\u5982 =<\/code>, ><\/code>, IN<\/code> \u7b49\uff09\u3002<\/li>\n\n\n\n
  • \u903b\u8f91\u8fd0\u7b97\u7b26<\/strong>\uff1a\u7528\u4e8e\u8fde\u63a5\u591a\u4e2a\u6761\u4ef6\uff08\u5982 AND<\/code>, OR<\/code>, NOT<\/code>\uff09\u3002<\/li>\n\n\n\n
  • \u7b97\u672f\u8fd0\u7b97\u7b26<\/strong>\uff1a\u7528\u4e8e\u6570\u5b66\u8fd0\u7b97\uff08\u5982 +<\/code>, -<\/code>, *<\/code>\uff09\u3002<\/li>\n\n\n\n
  • \u5b57\u7b26\u4e32\u8fd0\u7b97\u7b26<\/strong>\uff1a\u7528\u4e8e\u5904\u7406\u5b57\u7b26\u4e32\uff08\u5982 ||<\/code> \u62fc\u63a5\uff09\u3002<\/li>\n\n\n\n
  • \u805a\u5408\u51fd\u6570<\/strong>\uff1a\u7528\u4e8e\u8ba1\u7b97\u603b\u548c\u3001\u5e73\u5747\u503c\u7b49\uff08\u5982 COUNT<\/code>, SUM<\/code>, AVG<\/code>\uff09\u3002<\/li>\n\n\n\n
  • \u5e38\u7528\u51fd\u6570<\/strong>\uff1a\u5982 IFNULL<\/code> \u7528\u4e8e\u66ff\u6362 NULL \u503c\u3002<\/li>\n<\/ul>\n\n\n\n
    \u66f4\u591a\u8be6\u7ec6\u5185\u5bb9\u8bf7\u5173\u6ce8\u5176\u4ed6\u76f8\u5173\u6587\u7ae0\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
    SQLite \u652f\u6301\u591a\u79cd\u8fd0\u7b97\u7b26\uff0c\u53ef\u4ee5\u7528\u4e8e\u67e5\u8be2\u548c\u6570\u636e\u64cd\u4f5c\u3002\u8fd0\u7b97\u7b26\u53ef\u4ee5\u5206\u4e3a\u4ee5\u4e0b\u51e0\u7c7b\uff1a 1. \u6bd4\u8f83\u8fd0\u7b97\u7b26 \u8fd9\u4e9b\u8fd0\u7b97\u7b26\u7528 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[76],"tags":[],"class_list":["post-2320","post","type-post","status-publish","format-standard","hentry","category-sqlite"],"_links":{"self":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/2320","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/comments?post=2320"}],"version-history":[{"count":1,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/2320\/revisions"}],"predecessor-version":[{"id":2321,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/2320\/revisions\/2321"}],"wp:attachment":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/media?parent=2320"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/categories?post=2320"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/tags?post=2320"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}