Continue to "YiiFramework2.0 development tutorial (3) getting started with mysql". First, I will introduce yii2 database support. Yii builds a mature database access layer based on PHPsPDO. It provides unified APIs and solves the disadvantages of using different DBMS. Yii supports the following DBMS by default: MySQLMariaDBSQLiteP
Continue to Yii Framework2.0 development tutorial (3) database mysql getting started. First, give some introduction to yii2 database support. Yii builds a mature database access layer based on PHP's PDO. It provides unified APIs and solves the disadvantages of using different DBMS. Yii supports the following DBMS by default: MySQL MariaDB SQLite P
Continue to Yii Framework2.0 development tutorial (3) database mysql getting started
First, we will introduce the yii2 database support.
Yii builds a mature database access layer based on PHP's PDO. It provides unified APIs and solves the disadvantages of using different DBMS. Yii supports the following DBMS by default:
MySQL
MariaDB
SQLite
PostgreSQL
CUBRID: version 9.1.0 or higher.
Oracle
MSSQL: version 2012 or later. Use LIMIT/OFFSET if necessary.
Configuration
To start using a database, you must first configure the database connection component and add the db component to the application configuration ("the Basic" Web application is config/web. php), as shown below:
Return [//... 'components' => [//... 'db' => ['class' => 'yii \ db \ connection', 'dsn '=> 'mysql: host = localhost; dbname = mydatabase', // mysql, mariaDB // 'dsn '=> 'sqlite:/path/to/database/file', // sqlite // 'dsn' => 'pgsql: host = localhost; port = 5432; dbname = mydatabase', // PostgreSQL // 'dsn '=> 'cubrid: dbname = demodb; host = localhost; port = 100 ', // CUBRID // 'dsn '=> 'sqlsrv: Server = localhost; Database = mydatabase', // ms SQL Server, sqlsrv driver // 'dsn' => 'dblib: host = localhost; dbname = mydatabase', // ms SQL Server, dblib driver // 'dsn '=> 'mssql: host = localhost; dbname = mydatabase ', // ms SQL Server, mssql driver // 'dsn '=> 'oss: dbname = // localhost: 1521/mydatabase ', // Oracle 'username' => 'root', // database username 'Password' => '', // Database Password 'charset' => 'utf8',],], //...];
For more information about the DSN format, see PHP manual.
In our example, the configuration file is
Db access return ['class' => 'yii \ db \ connection', 'dsn '=> 'mysql: host = localhost; dbname = zhyoulun ', 'username' => 'root', 'Password' => '000000', 'charset' => 'utf8',];
Next, we can write our next test code in views/zhyoulun/HelloWorld. php created in Yii Framework2.0 development tutorial (1) configuration environment and the first application helloworld.
Step 1: Two Methods for database connection Initialization
(1)
$connection = Yii::$app->db;$connection->open();
(2)
$connection = new \yii\db\Connection(['dsn' => 'mysql:host=localhost;dbname=zhyoulun', 'username' => 'root', 'password' => '20092565', 'charset' => 'utf8']);$connection->open();
Step 2: Query and display data
$command = $connection->createCommand('SELECT * FROM country');$countries = $command->queryAll();echo '';print_r($countries);echo '
';
The entire helloworld. php code is as follows:
db;//$connection->open();$connection = new \yii\db\Connection(['dsn' => 'mysql:host=localhost;dbname=zhyoulun', 'username' => 'root', 'password' => '20092565', 'charset' => 'utf8']);$connection->open();$command = $connection->createCommand('SELECT * FROM country');$countries = $command->queryAll();echo '';print_r($countries);echo '
';?>
Step 3: continue to write and experiment with other common functions.
(1) queryOne, returns a single row
$command = $connection->createCommand("SELECT * FROM country WHERE code='BR'");$country = $command->queryOne();echo '';print_r($country);echo '
';
(2) queryColumn: query the values of multiple columns
$command = $connection->createCommand("SELECT code FROM country");$country = $command->queryColumn();echo '';print_r($country);echo '
';
(3) queryScalar: Query scalar value/calculated value
$command = $connection->createCommand("SELECT count(*) FROM country");$country = $command->queryScalar();echo '';print_r($country);echo '
';
(4) If the SQL statement does not return any data, use the execute method (UPDATE, INSERT, DELETE UPDATE, INSERT, and DELETE) in the command)
Update
$command = $connection->createCommand("UPDATE country SET name='Brazil-haha' WHERE code='BR'");$command->execute();
Update
$connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();
Insert
$connection->createCommand()->insert('user', [ 'name' => 'Sam', 'age' => 30,])->execute();
Insert multiple rows at a time
$connection->createCommand()->batchInsert('user', ['name', 'age'], [ ['Tom', 30], ['Jane', 20], ['Linda', 25],])->execute();
Delete
$connection->createCommand()->delete('user', 'status = 0')->execute();
% ============================================================== ===============================================%
$command = $query->createCommand();$rows = $command->queryAll();
An alternative to writing in a similar way is (select method)
$ Query = new \ yii \ db \ Query; // organization query statement $ query-> select ('code')-> from ('country ') -> limit (5); // compile and execute the query statement $ row = $ query-> all (); echo'';print_r($row);echo '
';
For other functions such as where, groupby, orderby, having, see http://www.yiichina.com/guide/2/db-query-builder
Refer:
Https://github.com/yiisoft/yii2/blob/master/docs/guide-zh-CN/db-dao.md
Http://www.yiichina.com/api/2.0/yii-db-query
Http://www.yiichina.com/guide/2/db-query-builder
Http://blog.csdn.net/zhyoulun/article/details/40476019