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
// [><] 和 [] 可以用于 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-16' (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" => [
  //实际应用时这儿可以使用 AND 或者 OR
        "OR" => [  //第一个条件
            "user_name" => "foo",
            "email" => "foo@bar.com"
        ],
        "OR" => [ //第二个条件
            "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", [
 	// 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"	]]
);

全文检索

// [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