#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
~~~
