WHERE 语句
SQL中使用where可能会有一些不安全的动态参数传入或者一些复杂的SQL语句,但是Medoo提供非常简介和安全的方法来实现这些．
基本使用
在基本使用中. 你可以使用一些符号对参数进行过滤
$database->select("account", "user_name", [
"email" => "[email protected]"
]);
// WHERE email = '[email protected]'
$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
// [><] and [<>] is also available for datetime
$database->select("account", "user_name", [
"birthday[<>]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
]);
// WHERE ("birthday" BETWEEN '2015-01-01' AND '2017-01-01')
$database->select("account", "user_name", [
"birthday[><]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
]);
// WHERE ("birthday" NOT BETWEEN '2015-01-01' AND '2017-01-01')
// You can use not only single string or number value, but also array
$database->select("account", "user_name", [
"OR" => [
"user_id" => [2, 123, 234, 54],
"email" => ["[email protected]", "[email protected]", "[email protected]"]
]
]);
// WHERE
// user_id IN (2,123,234,54) OR
// email IN ('[email protected]','[email protected]','[email protected]')
// [Negative condition]
$database->select("account", "user_name", [
"AND" => [
"user_name[!]" => "foo",
"user_id[!]" => 1024,
"email[!]" => ["[email protected]", "[email protected]", "[email protected]"],
"city[!]" => null,
"promoted[!]" => true
]
]);
// WHERE
// `user_name` != 'foo' AND
// `user_id` != 1024 AND
// `email` NOT IN ('[email protected]','[email protected]','[email protected]') AND
// `city` IS NOT NULL
// `promoted` != 1
// Or fetched from select() or get() function
$database->select("account", "user_name", [
"user_id" => $database->select("post", "user_id", ["comments[>]" => 40])
]);
// WHERE user_id IN (2, 51, 321, 3431)
带有相对条件使用
$database->select("account", "user_name", [
"AND" => [
"user_id[>]" => 200,
"age[<>]" => [18, 25],
"gender" => "female"
]
]);
// Medoo 默认使用AND连接各个条件
$database->select("account", "user_name", [
"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" => "[email protected]"
],
"password" => "12345"
]
]);
// WHERE (user_name = 'foo' OR email = '[email protected]') AND password = '12345'
// [IMPORTANT]
// Because Medoo is using array data construction to describe relativity condition,
// array with duplicated key will be overwritten.
//
// This will be error:
$database->select("account", '*', [
"AND" => [
"OR" => [
"user_name" => "foo",
"email" => "[email protected]"
],
"OR" => [
"user_name" => "bar",
"email" => "[email protected]"
]
]
]);
// [X] SELECT * FROM "account" WHERE ("user_name" = 'bar' OR "email" = '[email protected]')
// To correct that, just assign a comment for each AND and OR key name. The comment content can be everything.
$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" => "[email protected]"
],
"OR #the second condition" => [
"user_name" => "bar",
"email" => "[email protected]"
]
]
]);
// SELECT * FROM "account"
// WHERE (
// (
// "user_name" = 'foo' OR "email" = '[email protected]'
// )
// AND
// (
// "user_name" = 'bar' OR "email" = '[email protected]'
// )
// )
列的关系处理
$database->select("post", [
"[>]account" => ["author_id" => "user_id"],
], [
"post.id",
"post.content"
], [
"AND" => [
// Connect two column with condition sign like [=], [>], [<], [!=] as one of array value
"post.restrict[<]account.age",
"account.user_name" => "foo",
"account.email" => "[email protected]",
]
]
);
// WHERE "post"."restrict" < "account"."age" AND "account"."user_name" = 'foo' AND "account"."email" = '[email protected]'
模糊匹配 like
LIKE 使用语法 [~] .
// 默认情况下，使用%在前后包含关键词
$database->select("person", "id", [
"city[~]" => "lon"
]);
WHERE "city" LIKE '%lon%'
// Array support
$database->select("person", "id", [
"city[~]" => ["lon", "foo", "bar"]
]);
WHERE "city" LIKE '%lon%' OR "city" LIKE '%foo%' OR "city" LIKE '%bar%'
// Negative condition [!~]
$database->select("person", "id", [
"city[!~]" => "lon"
]);
WHERE "city" NOT LIKE '%lon%'
通配符的使用
// You can use SQL wildcard to match more complex situation
$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("person", "id", [
"content[~]" => ["AND" => ["lon", "on"]]
]);
// WHERE ("content" LIKE '%lon%' AND "content" LIKE '%on%')
$database->select("person", "id", [
"content[~]" => ["OR" => ["lon", "on"]]
]);
// WHERE ("content" LIKE '%lon%' OR "content" LIKE '%on%')
排序
$database->select("account", "user_id", [
// Single condition
"ORDER" => "user_id",
// Multiple condition
"ORDER" => [
// Order by column with sorting by customized order.
"user_id" => [43, 12, 57, 98, 144, 1],
// Order by column
"register_date",
// Order by column with descending sorting
"profile_id" => "DESC",
// Order by column with ascending sorting
"date" => "ASC"
]
]);
全文检索
MySQL支持全文检索高级查询
| natural | IN NATURAL LANGUAGE MODE |
| natural+query | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION |
| boolean | IN BOOLEAN MODE |
| query | WITH QUERY EXPANSION |
// [MATCH]
$database->select("post_table", "post_id", [
"MATCH" => [
"columns" => ["content", "title"],
"keyword" => "foo",
// [optional] Search mode
"mode" => "natural"
]
]);
// WHERE MATCH (content, title) AGAINST ('foo' IN NATURAL LANGUAGE MODE)
使用正则
$data = $database->select('account', [
'user_id',
'user_name'
], [
'user_name[REGEXP]' => '[a-z0-9]*'
]);
// SELECT "user_id","user_name"
// FROM "account"
// WHERE "user_name" REGEXP '[a-z0-9]*'
使用SQL函数
处理SQL内置函数的方法在1.2之后做了修改，与以前的不兼容。详情可阅读 Raw object章节
$data = $database->select('account', [
'user_id',
'user_name'
], [
'datetime' => Medoo::raw('NOW()')
]);
// SELECT "user_id","user_name"
// FROM "account"
// WHERE "datetime" = NOW()
LIMIT 和 OFFSET
$database->select("account", "user_id", [
// Get the first 100 of rows
'LIMIT' => 100
// Started from the top 20 rows, and get the next 100
'LIMIT' => [20, 100],
// For Oracle and MSSQL database, you also need to use with GROUP by together
'GROUP' => 'location'
]);
GROUP 和 HAVING
$database->select("account", "user_id", [
'GROUP' => 'type',
// GROUP by array of values
'GROUP' => [
'type',
'age',
'gender'
],
// Must have to use it with GROUP together
'HAVING' => [
'user_id[>]' => 500
]
]);