select 查询
优质
小牛编辑
131浏览
2023-12-01
select
数据库查询
select($table, $columns, $where)table [string]
表名.
columns [string/array]
要查询的字段名.
where (optional) [array]
查询的条件.
table [string]
表名.
join [array]
多表查询,不使用可以忽略.
columns [string/array]
要查询的字段名.
where (optional) [array]
查询的条件.
$database = new medoo();$datas = $database->select("account", [ "user_name", "email"], [ "user_id[>]" => 100]);// $datas = array(// [0] => array(// "user_name" => "foo",// "email" => "foo@bar.com"// ),// [1] => array(// "user_name" => "cat",// "email" => "cat@dog.com"// )// )foreach($datas as $data){ echo "user_name:" . $data["user_name"] . " - email:" . $data["email"] . "
";}// Select all columns$datas = $database->select("account", "*");// Select a column$datas = $database->select("account", "user_name");// $datas = array(// [0] => "foo",// [1] => "cat"// )表关联
多表查询SQL较为复杂，使用Medoo可以轻松的解决它
// [>] == LEFT JOIN// [<] == RIGH JOIN// [] == FULL JOIN// [>select("post", [ // Here is the table relativity argument that tells the relativity between the table you want to join. // The row author_id from table post is equal the row user_id from table account "[>]account" => ["author_id" => "user_id"], // The row user_id from table post is equal the row user_id from table album. // This is a shortcut to declare the relativity if the row name are the same in both table. "[>]album" => "user_id", // [post.user_id is equal photo.user_id and post.avatar_id is equal photo.avatar_id] // Like above, there are two row or more are the same in both table. "[>]photo" => ["user_id", "avatar_id"], // If you want to join the same table with different value, // you have to assign the table with alias. "[>]account (replyer)" => ["replyer_id" => "user_id"], // You can refer the previous joined table by adding the table name before the column. "[>]account" => ["author_id" => "user_id"], "[>]album" => ["account.user_id" => "user_id"], // Multiple condition "[>]account" => [ "author_id" => "user_id", "album.user_id" => "user_id" ]], [ "post.post_id", "post.title", "account.user_id", "account.city", "replyer.user_id", "replyer.city"], [ "post.user_id" => 100, "ORDER" => ["post.post_id" => "DESC"], "LIMIT" => 50]); // SELECT// `post`.`post_id`,// `post`.`title`,// `account`.`city`// FROM `post`// LEFT JOIN `account` ON `post`.`author_id` = `account`.`user_id`// LEFT JOIN `album` USING (`user_id`)// LEFT JOIN `photo` USING (`user_id`, `avatar_id`)// WHERE// `post`.`user_id` = 100// ORDER BY `post`.`post_id` DESC// LIMIT 50数据映射
根据您所需要的数据结构，自定义输出的数据格式
$data = $database->select("post", [ "[>]account" => ["user_id"]], [ "post.post_id", "post.content", "userData" => [ "account.user_id", "account.email", "meta" => [ "account.location", "account.gender" ] ]], [ "LIMIT" => [0, 2]]); echo json_encode($data); // Outputed data[ { post_id: "1", content: "Hello world!", userData: { user_id: "1", email: "foo@example.com", meta: { location: "New York", gender: "male" } } }, { post_id: "2", content: "Hey everyone", userData: { user_id: "2", email: "bar@example.com", meta: { location: "London", gender: "female" } } }]字段别名
你可以使用别名，以防止字段冲突
$data = $database->select("account", [ "user_id", "nickname(my_nickname)"], [ "LIMIT" => 20]); // $data = array(// [0] => array(// "user_id" => "1",// "my_nickname" => "foo"// ),// [1] => array(// "user_id" => "2",// "my_nickname" => "bar"// )// ) $data = $database->select("post (content)", [ "[>]account (user)" => "user_id",], [ "content.user_id (author_id)", "user.user_id"], [ "LIMIT" => 20]); // SELECT// "content"."user_id" AS author_id,// "user"."user_id"// FROM// "post" AS "content"// LEFT JOIN "account" AS "user" USING ("user_id")// LIMIT 2 // $data = array(// [0] => array(// "author_id" => "1",// "user_id" => "321"// ),// [1] => array(// "author_id" => "2",// "user_id" => "322"// )// )