YiiFramework2.0 development tutorial (5) database mysql Functions

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