Yii Framework2.0 Development Tutorials (5) database MySQL performance

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

First, give some respect. Yii2 Database Support Introduction

Yii is based on the establishment of a mature database access layer for PHP's PDO. It provides a unified API and overcomes the use of many different DBMS violations. YII supports the following DBMS by default:
Mysql
MariaDB
Sqlite
PostgreSQL
Cubrid:version 9.1.0 or higher.
Oracle
Mssql:version 2012 or higher, if you want to use Limit/offset.

Configuration

To start using a database, you first need to configure the database connectivity component by adding the DB component to the application configuration implementation (the "underlying" Web application is config/web.php), as seen in the following:

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 many other information about DSN formats.

The configuration file given in our sample is

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

We will then be able to write our next test code in the views/zhyoulun/helloworld.php set up in 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> ';

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvemh5b3vsdw4=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

The entire helloworld.php code such as the following

<?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 frequently used functions.

(1) Queryone. Return single row

$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) Assume that running SQL does not return no matter what data can be used in commands in the Execute method (update, INSERT, delete update, 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 ();

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

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

References:

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

Copyright notice: This article Bo Master original article. Blog, not reproduced without consent.

Yii Framework2.0 Development Tutorials (5) database MySQL performance