Yii Framework2.0 Development Tutorial (5) database MySQL function

Continuation of the Yii Framework2.0 Development Tutorial (3) database MySQL Primer

First, give some information about YII2 database support

YII builds a sophisticated database access layer based on PHP's PDO. It provides a unified API and solves the disadvantages of some different DBMS-generated usage. YII supports the following DBMS by default:
Mysql
MariaDB
Sqlite
PostgreSQL
Cubrid:version 9.1.0 or higher.
Oracle
Mssql:version 2012 or later, if you want to use Limit/offset.

Configuration

Getting started with a database requires first configuring the database Connectivity component by adding a DB component to the application configuration implementation (the "underlying" Web application is config/web.php) as follows:

return [//...        ' Components ' = [//... ' db ' = = [' Class ' = ' yii\db\connection ', ' dsn ' = ' mysql:host=localhost;dbname=mydatabase ' ,//MySQL, MariaDB//' DSN ' = ' sqlite:/path/to/database/file ',//SQLite/' dsn ' = ' Pgsql:ho St=localhost;port=5432;dbname=mydatabase ',//PostgreSQL//' DSN ' = ' Cubrid:dbname=demodb;host=localhost;por t=33000 ',//Cubrid//' DSN ' = ' sqlsrv:server=localhost;database=mydatabase ',//MS SQL Server, sqlsrv Drive R//' DSN ' = ' dblib:host=localhost;dbname=mydatabase ',//MS SQL Server, dblib driver//' DSN ' =&G T ' Mssql:host=localhost;dbname=mydatabase ',//MS SQL Server, MSSQL driver//' DSN ' = ' oci:dbname=//localhost ':            1521/mydatabase ',//Oracle ' username ' + ' root ',//database user name ' password ' = ', '//Database password ' CharSet ' = ' utf8 ',],],//...];

Please also refer to PHP manual for more information about DSN format.

The configuration file given in our example is

<?php//configured database connection can be used in the application via Yii:: $app->db access return [    ' class ' = ' yii\db\connection ',    ' dsn ' = ' MySQL: Host=localhost;dbname=zhyoulun ',    ' username ' = ' root ',    ' password ' = ' 20092565 ',    ' charset ' and ' = ' UTF8 ',];

Next we can write our next test code in the views/zhyoulun/helloworld.php setup of the Yii Framework2.0 Development Tutorial (1) configuration environment and the first application HelloWorld.

The first step, two ways to initialize the database connection

(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 two: Query and display the data

$command = $connection->createcommand (' SELECT * from country '); $countries = $command->queryall (); Echo ' <pre > ';p rint_r ($countries); Echo ' </pre> ';

The entire helloworld.php code is as follows

<?php//$connection = Yii:: $app->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 ' <pre> ';p rint_r ($countries); Echo ' </pre> ';? >

The third step, we continue to write, experiment with other commonly used functions.

(1) Queryone, return single line

$command = $connection->createcommand ("SELECT * from country WHERE code= ' BR '"); $country = $command->queryone (); Echo ' <pre> ';p rint_r ($country); Echo ' </pre> ';

(2) Querycolumn, querying multi-column values

$command = $connection->createcommand ("Select code from Country"); $country = $command->querycolumn (); Echo ' < Pre> ';p rint_r ($country); Echo ' </pre> ';

(3) Queryscalar, query scalar value/calculated value

$command = $connection->createcommand ("SELECT count (*) from country"), $country = $command->queryscalar (); Echo ' <pre> ';p rint_r ($country); Echo ' </pre> ';

(4) If execution SQL does not return any data, you can use the Execute method in the command (update, INSERT, delete updates, insert, delete, etc.)

Update

$command = $connection->createcommand ("UPDATE country SET name= ' Brazil-haha ' WHERE code= ' BR '); $command Execute ();

Update

$connection->createcommand ()->update (' User ', [' status ' = 1], ' Age > ')->execute ();

Insert

$connection->createcommand ()->insert (' User ', [    ' name ' = ' Sam ',    ' age ' = []])->execute ();

Insert multiple lines at once

$connection->createcommand ()->batchinsert (' User ', [' name ', ' age '], [    [' Tom ', '], [    ' Jane ', [+]],    [ ' Linda ', [+],])->execute ();

Delete

$connection->createcommand ()->delete (' User ', ' status = 0 ')->execute ();

%====================================== Split Line ======================================%

$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 query statement $row = $ Query->all (), Echo ' <pre> ';p rint_r ($row); Echo ' </pre> ';

Other functions such as where, GroupBy, by-and-having, etc. see Http://www.yiichina.com/guide/2/db-query-builder

Reference:

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

Reprint Please specify source: http://blog.csdn.net/zhyoulun/article/details/40476019

Yii Framework2.0 Development Tutorial (5) database MySQL function