medoo 支持5大数据库 · PHP/Python/前端/Linux 等等学习笔记

[TOC] >[github](https://github.com/catfan/Medoo) >[home](https://medoo.in/) ## 概述 | Name | Driver | | --- | --- | | MySQL, MariaDB | php\_pdo\_mysql | | MSSQL (Windows) | php\_pdo\_sqlsrv | | MSSQL (Linux/UNIX) | php\_pdo\_dblib / php\_pdo\_sqlsrv | | Oracle | php\_pdo\_oci | | Oracle version 8 | php\_pdo\_oci8 | | SQLite | php\_pdo\_sqlite | | PostgreSQL | php\_pdo\_pgsql | | Sybase | php\_pdo\_dblib | php5.4+ ## 安装 `composer require catfan/medoo` ## 结合 Slim [Slim 微型框架](../%E7%BD%91%E7%BB%9C/Slim%E5%BE%AE%E5%9E%8B%E6%A1%86%E6%9E%B6.md) ``` use Medoo\Medoo; $app = new \Slim\App(); $container = $app->getContainer(); $container['database'] = function () { return new Medoo([ 'database_type' => 'mysql', 'database_name' => 'name', 'server' => 'localhost', 'username' => 'your_username', 'password' => 'your_password' ]); }; $app->get('/', function($request, $response, $args) { $data = $this->database->select('account', ['id', 'name']); return $response->write(json_encode($data)); }); $app->run(); ``` ## 接口 ### 连接 ``` use Medoo\Medoo; $database = new Medoo([ 'database_type' => 'mysql', 'database_name' => 'coffee_system', 'server' => 'localhost', 'username' => 'root', 'password' => 'www.upsoft01.com' ]); ``` ### where 语句 ``` $database->select("account", "user_name", [ // WHERE email = 'foo@bar.com' "email" => "foo@bar.com" ]); $database->select("account", "user_name", [ // WHERE user_id = 200 "user_id" => 200 ]); $database->select("account", "user_name", [ // WHERE user_id > 200 "user_id[>]" => 200 ]); $database->select("account", "user_name", [ // WHERE user_id >= 200 "user_id[>=]" => 200 ]); $database->select("account", "user_name", [ // WHERE user_id != 200 "user_id[!]" => 200 ]); $database->select("account", "user_name", [ // WHERE age BETWEEN 200 AND 500 "age[<>]" => [200, 500] ]); $database->select("account", "user_name", [ // WHERE age NOT BETWEEN 200 AND 500 "age[><]" => [200, 500] ]); ``` #### or ``` $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') ``` #### and ``` $data = $database->select("user_info_eq_info", ["name",],[ 'AND'=>[ 'machine_id[!]'=>[1,2], 'category_id'=>1, ] ]); //等于 $data = $database->select("user_info_eq_info", ["name",],[ 'machine_id[!]'=>[1,2], 'category_id'=>1, ]); ``` #### 子查询 ``` $database->select("account", "user_name", [ "user_id" => $database->select("post", "user_id", ["comments[>]" => 40]) ]); // WHERE user_id IN (2, 51, 321, 3431) ``` #### 混合 ``` $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' ``` #### like ``` //SELECT `machine` FROM `user_info_eq_info` WHERE (`name` LIKE '%cpj%') $database->select("user_info_eq_info",'machine' ,[ 'name[~]'=>'cpj', // 数组 ["lon", "foo", "bar"] ] ); //SELECT `machine` FROM `user_info_eq_info` WHERE (`name` LIKE '%cpj') $database->select("user_info_eq_info",'machine' ,[ 'name[~]'=>'%cpj' ] ) //SELECT `machine` FROM `user_info_eq_info` WHERE (`name` NOT LIKE '%cpj') $database->select("user_info_eq_info",'machine' ,[ 'name[!~]'=>'cpj' ] ); // WHERE ("content" LIKE '%lon%' AND "content" LIKE '%on%') $database->select("person", "id", [ "content[~]" => ["AND" => ["lon", "on"]] ]); // WHERE ("content" LIKE '%lon%' OR "content" LIKE '%on%') $database->select("person", "id", [ "content[~]" => ["OR" => ["lon", "on"]] ]); ``` ### join查询 ``` /** * [>]account LEFT JOIN `account` * [<]account RIGHT JOIN `account` * [><]account INNER JOIN `account` * [<>]account FULL JOIN `account` */ $database->select("post", [ "[><]account" => ["author_id" => "user_id"], ], [ "post.id", "post.content" ], [ "AND" => [ "post.restrict[<]account.age", "account.user_name" => "foo", "account.email" => "foo@bar.com", ] ] ); print_r($database->last());////SELECT `post`.`id`,`post`.`content` FROM `post` INNER JOIN `account` ON `post`.`author_id` = `account`.`user_id` WHERE (`post`.`restrict` < `account`.`age` AND `account`.`user_name` = 'foo' AND `account`.`email` = 'foo@bar.com') ``` ### 排序 order ``` $database->select("account", "user_id", [ // Single condition "ORDER" => "user_id", //or // 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" ] ]); ``` ### limit ``` $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 ``` $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 ] ]); ``` ### 增 ``` // 主键自动添加阿萨德 $database->insert("account", [ "user_name" => "foo", "email" => "foo@bar.com", "age" => 25 ]); ``` #### Last Insert ID ``` $database->insert("account", [ "user_name" => "foo", "email" => "foo@bar.com", "age" => 25 ]); $account_id = $database->id(); ``` #### 批量插入 ``` $database->insert("account", [ [ "user_name" => "foo", "email" => "foo@bar.com", "age" => 25, "city" => "New York", "lang [JSON]" => ["en", "fr", "jp", "cn"] ], [ "user_name" => "bar", "email" => "bar@foo.com", "age" => 14, "city" => "Hong Kong", "lang [JSON]" => ["en", "jp", "cn"] ] ]); ``` #### 序列化 ``` $database->insert("account", [ "user_name" => "foo", "email" => "foo@bar.com", "age" => 25, "lang" => ["en", "fr", "jp", "cn"] // => 'a:4:{i:0;s:2:"en";i:1;s:2:"fr";i:2;s:2:"jp";i:3;s:2:"cn";}' ]); $database->insert("account", [ "user_name" => "foo", "email" => "foo@bar.com", "age" => 25, "lang [JSON]" => ["en", "fr", "jp", "cn"] // => '["en","fr","jp","cn"]' ]); ``` ### 查 #### get 单条 ``` $data = $database->get("user_info_eq_info", 'mac_addr'); print_r($data); //BC-EE-7B-5B-6B-0FSELECT $data = $database->get("user_info_eq_info", ['mac_addr']); print_r($data); //( [mac_addr] => BC-EE-7B-5B-6B-0F ) ``` #### 查询所有字段 ``` $data = $database->get("user_info_eq_info", '*',[ 'category_id'=>1, ]); ``` #### 自定义返回数组 ``` $data = $database->select("post", [ "user_id" => [ "nickname", "location", "email" ] ]); // Output data [ 10: { nickname: "foo", location: "New York", email: "foo@example.com" }, 12: { nickname: "bar", location: "New York", email: "bar@medoo.in" } ] ``` #### join 查询并自定义返回结构 ``` $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); // Output 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("post", [ "[>]account" => ["user_id"] ], [ "post.post_id", "profile" => [ "account.age [Int]", "account.is_locked [Bool]", "account.userData [JSON]" ] ], [ "LIMIT" => [0, 2] ]); echo json_encode($data); // Output data [ { post_id: "1", profile: { age: 20, is_locked: true, userData: ["foo", "bar", "tim"] } }, { post_id: "2", profile: { age: 25, is_locked: false, userData: ["mydata1", "mydata2"] } } ] ``` #### 别名 ``` $data = $database->select("account", [ "user_id", "nickname(my_nickname)" // nickname as my_nickname ], [ "LIMIT" => 20 ]); $data = $database->select("post (content)", [ "[>]account (user)" => "user_id", // account as user ], [ "content.user_id (author_id)", "user.user_id" ], [ "LIMIT" => 20 ]); ``` ### 更 ``` $data =$database->update("account", [ "type" => "user", // age + 1 "age[+]" => 1, "level[-]" => 5, // 乘 2 "score[*]" => 2, "lang" => ["en", "fr", "jp", "cn"], "lang [JSON]" => ["en", "fr", "jp", "cn"], "is_locked" => true, ], [ "user_id[<]" => 1000 ]); echo $data->rowCount(); ``` ### 删 ``` $database->delete("account", [ "AND" => [ "type" => "business", "age[<]" => 18 ] ]); ``` ### has 表是否存在 ``` if ($database->has("post", ["user_id" => 2312])) { return false; } ``` ### 聚合函数 count,max,min,avg ``` $count = $database->count("account", [ "gender" => "female" ]); ``` ### 创建表 ``` $database->create("account", [ "id" => [ "INT", "NOT NULL", "AUTO_INCREMENT" ], "email" => [ "VARCHAR(70)", "NOT NULL", "UNIQUE" ], "PRIMARY KEY (<id>)" ], [ "ENGINE" => "MyISAM", "AUTO_INCREMENT" => 200 ]); ``` ### 删除表 ``` $database->drop("account"); ``` ### 执行sql函数 ``` $data = $database->insert('account', [ 'user_name' => 'apple', 'user_id' => Medoo::raw('UUID()') ]); $data = $database->select('account', [ 'user_id', 'email' ], Medoo::raw('WHERE LENGTH(<user_name>) > 5 ORDER BY RAND() LIMIT 10 ') ); ``` ### 事务 ``` $database->pdo->beginTransaction(); $database->insert("account", [ "user_name" => "foo", "email" => "foo@bar.com", "age" => 25 ]); /* Commit the changes */ $database->pdo->commit(); /* Recognize mistake and roll back changes */ $database->pdo->rollBack(); ``` ### Debug #### Debug ``` $database->debug()->select("bccount", [ "user_name", "email" ], [ "user_id[<]" => 20 ]); // Will output: // SELECT "user_name","email" FROM "bccount" WHERE "user_id" < 20 ``` #### 处理error ``` $database->select("bccount", [ "user_name", "email" ], [ "user_id[<]" => 20 ]); var_dump( $database->error() ); ``` #### log ``` $database->insert("account", [ "user_name" => "foo", "email" => "foo@bar.com" ]); var_dump($database->log()); //array(1) { [0] => string(74) "INSERT INTO `account` (`user_name`, `email`) VALUES ('foo', 'foo@bar.com')" } ``` ### 查询最后执行的sql ``` $database->last() ```