Question

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// [><] 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" => ["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')// [Negative condition]$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// 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" => "foo@bar.com"		],		"password" => "12345"	]]);// WHERE (user_name = 'foo' OR email = 'foo@bar.com') 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" => "foo@bar.com"		],		"OR" => [			"user_name" => "bar",			"email" => "bar@foo.com"		]	]]);// [X] SELECT * FROM "account" WHERE ("user_name" = 'bar' OR "email" = 'bar@foo.com')// 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" => "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'// 	)// )

列的关系处理

$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[ "foo",			"account.email" => "foo@bar.com",		]	]);// WHERE "post"."restrict" < "account"."age" AND "account"."user_name" = 'foo' AND "account"."email" = 'foo@bar.com'

模糊匹配 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	]]);