WHERE 语句
SQL中使用where可能会有一些不安全的动态参数传入或者一些复杂的SQL语句,但是Medoo提供非常简介和安全的方法来实现这些．
基础使用
在基础使用中. 你可以使用一些符号对参数进行过滤
- $database->select("account", "user_name", [
- "email" => "foo@bar.com"
- ]);
- // WHERE email = 'foo@bar.com'
- $database->select("account", "user_name", [
- "user_id" => 200
- ]);
- // WHERE user_id = 200
- $database->select("account", "user_name", [
- "user_id[>]" => 200
- ]);
- // WHERE user_id > 200
- $database->select("account", "user_name", [
- "user_id[>=]" => 200
- ]);
- // WHERE user_id >= 200
- $database->select("account", "user_name", [
- "user_id[!]" => 200
- ]);
- // WHERE user_id != 200
- $database->select("account", "user_name", [
- "age[<>]" => [200, 500]
- ]);
- // WHERE age BETWEEN 200 AND 500
- $database->select("account", "user_name", [
- "age[><]" => [200, 500]
- ]);
- // WHERE age NOT BETWEEN 200 AND 500
- // [><] 和 [<>] 可以用于 datetime
- $database->select("account", "user_name", [
- "birthday[><]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
- ]);
- //WHERE "create_date" BETWEEN '2015-01-01' AND '2015-05-01' (now)
- // 你不仅可以使用字符串和数字，还可以使用数组
- $database->select("account", "user_name", [
- "OR" => [
- "user_id" => [2, 123, 234, 54],
- "email" => ["foo@bar.com", "cat@dog.com", "admin@medoo.in"]
- ]
- ]);
- // WHERE
- // user_id IN (2,123,234,54) OR
- // email IN ('foo@bar.com','cat@dog.com','admin@medoo.in')
- // 多条件查询
- $database->select("account", "user_name", [
- "AND" => [
- "user_name[!]" => "foo",
- "user_id[!]" => 1024,
- "email[!]" => ["foo@bar.com", "cat@dog.com", "admin@medoo.in"],
- "city[!]" => null,
- "promoted[!]" => true
- ]
- ]);
- // WHERE
- // `user_name` != 'foo' AND
- // `user_id` != 1024 AND
- // `email` NOT IN ('foo@bar.com','cat@dog.com','admin@medoo.in') AND
- // `city` IS NOT NULL
- // `promoted` != 1
- // 或者嵌套 select() ak get() 方法
- $database->select("account", "user_name", [
- "user_id" => $database->select("post", "user_id", ["comments[>]" => 40])
- ]);
- // WHERE user_id IN (2, 51, 321, 3431)
条件搜索
你可以使用"AND" 或 "OR" 来拼接非常复杂的SQL语句
- // 基础使用
- $database->select("account", "user_name", [
- "AND" => [
- "user_id[>]" => 200,
- "age[<>]" => [18, 25],
- "gender" => "female"
- ]
- ]);
- // WHERE user_id > 200 AND age BETWEEN 18 AND 25 AND gender = 'female'
- $database->select("account", "user_name", [
- "OR" => [
- "user_id[>]" => 200,
- "age[<>]" => [18, 25],
- "gender" => "female"
- ]
- ]);
- // WHERE user_id > 200 OR age BETWEEN 18 AND 25 OR gender = 'female'
- // 复合条件
- $database->has("account", [
- "AND" => [
- "OR" => [
- "user_name" => "foo",
- "email" => "foo@bar.com"
- ],
- "password" => "12345"
- ]
- ]);
- // WHERE (user_name = 'foo' OR email = 'foo@bar.com') AND password = '12345'
- // 注意
- // 因为medoo使用的是数组传参，所以下面这种用法是错误的。
- $database->select("account", '*', [
- "AND" => [
- "OR" => [
- "user_name" => "foo",
- "email" => "foo@bar.com"
- ],
- "OR" => [
- "user_name" => "bar",
- "email" => "bar@foo.com"
- ]
- ]
- ]);
- // [X] SELECT * FROM "account" WHERE ("user_name" = 'bar' OR "email" = 'bar@foo.com')
- // 正确的方式是使用如下方式定义复合条件
- $database->select("account", '*', [
- "AND #Actually, this comment feature can be used on every AND and OR relativity condition" => [
- "OR #the first condition" => [
- "user_name" => "foo",
- "email" => "foo@bar.com"
- ],
- "OR #the second condition" => [
- "user_name" => "bar",
- "email" => "bar@foo.com"
- ]
- ]
- ]);
- // SELECT * FROM "account"
- // WHERE (
- // (
- // "user_name" = 'foo' OR "email" = 'foo@bar.com'
- // )
- // AND
- // (
- // "user_name" = 'bar' OR "email" = 'bar@foo.com'
- // )
- // )
模糊匹配 like
LIKE 使用语法 [~] .
- // 默认情况下，使用%在前后包含关键词
- $database->select("person", "id", [
- "city[~]" => "lon"
- ]);
- WHERE "city" LIKE '%lon%'
- // 数组形式，查询多个关键词
- $database->select("person", "id", [
- "city[~]" => ["lon", "foo", "bar"]
- ]);
- WHERE "city" LIKE '%lon%' OR "city" LIKE '%foo%' OR "city" LIKE '%bar%'
- // 不包含 [!~]
- $database->select("person", "id", [
- "city[!~]" => "lon"
- ]);
- WHERE "city" NOT LIKE '%lon%'
- // 使用SQL自带的一些通配符
- // 你可以使用sql自带的一些通配符来完成较复杂的查询
- $database->select("person", "id", [
- "city[~]" => "stan%" // Kazakhstan, Uzbekistan, Türkmenistan
- ]);
- $database->select("person", "id", [
- "city[~]" => "Londo_" // London, Londox, Londos...
- ]);
- $database->select("person", "id", [
- "name[~]" => "[BCR]at" // Bat, Cat, Rat
- ]);
- $database->select("person", "id", [
- "name[~]" => "[!BCR]at" // Eat, Fat, Hat...
- ]);
排序使用
- $database->select("account", "user_id", [
- // "ORDER" => "age DESC"
- "ORDER" => "age",
- ]);
- // SELECT user_id FROM account
- // ORDER BY age
- // 多个排序
- $database->select("account", "user_id", [
- "ORDER" => ['user_name DESC', 'user_id ASC']
- ]);
- // SELECT user_id FROM account
- // ORDER BY "user_name" DESC, "user_id" ASC
- // 根据字段自定义排序顺序
- // "ORDER" => array("column_name", [array #ordered array])
- $database->select("account", "user_id", [
- "user_id" => [1, 12, 43, 57, 98, 144],
- "ORDER" => ["user_id", [43, 12, 57, 98, 144, 1]]
- ]);
- // SELECT "user_id"
- // FROM "account"
- // WHERE "user_id" IN (1,12,43,57,98,144)
- // ORDER BY FIELD("user_id", 43,12,57,98,144,1)
- // array(6) {
- // [0]=> string(2) "43"
- // [1]=> string(2) "12"
- // [2]=> string(2) "57"
- // [3]=> string(2) "98"
- // [4]=> string(3) "144"
- // [5]=> string(1) "1"
- // }
全文检索
- // [MATCH]
- $database->select("post_table", "post_id", [
- "MATCH" => [
- "columns" => ["content", "title"],
- "keyword" => "foo"
- ]
- ]);
- // WHERE MATCH (content, title) AGAINST ('foo')
使用SQL函数
在一些特殊的情况下，你可能需要使用SQL系统函数，只需要字段名前加上#号即可
- $data = $database->select('account', [
- 'user_id',
- 'user_name'
- ], [
- '#datetime' => 'NOW()'
- ]);
- // SELECT "user_id","user_name"
- // FROM "account"
- // WHERE "datetime" = NOW()
- // [IMPORTANT] Keep in mind that, the value will not be quoted should be matched as XXX() uppercase.
- // The following sample will be failed.
- $database->select('account', [
- 'user_id',
- 'user_name'
- ], [
- '#datetime2' => 'now()',
- 'datetime3' => 'NOW()',
- '#datetime4' => 'NOW'
- ]);
附加条件
- $database->select("account", "user_id", [
- "GROUP" => "type",
- // Must have to use it with GROUP together
- "HAVING" => [
- "user_id[>]" => 500
- ],
- // LIMIT => 20
- "LIMIT" => [20, 100]
- ]);
- // SELECT user_id FROM account
- // GROUP BY type
- // HAVING user_id > 500
- // LIMIT 20,100