YII 2.0 Database Simple operation

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), and the DSN (data source name) is the data source name used to specify the database information. The following is shown:

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 refer to PHP Manual for more information about DSN format. After you configure a connection component, you can access it using the following syntax:

1 $connection = \yii:: $app->db;

Please refer [[yii\db\Connection]] to get a list of configurable properties. If you want to connect to the database through ODBC, you need to configure the [[yii\db\connection::d Rivername]] property, for example:

' db ' = = [    ' class ' = ' yii\db\connection ',    ' drivername ' = ' mysql ',    ' dsn ' = ' + ' odbc:driver={ MySQL}; Server=localhost;database=test ',    ' username ' + ' root ',    ' password ' = ', ', ',

Note: If you need to use multiple databases at the same time, you can define multiple connection components:

return [    //...    ] Components ' = + [        //...        ' db ' = = [            ' class ' = ' yii\db\connection ',            ' dsn ' = ' mysql:host=localhost;dbname=mydatabase ',             ' Username ' + ' root ', ' password ' + ' ', ' charset ' and '            UTF8 ',        ],        ' seconddb ' = [            ' Class ' = ' yii\db\connection ',            ' dsn ' = ' sqlite:/path/to/database/file ',         ],    ],    //...];

Used in code in the following ways:

1 $primaryConnection = \yii:: $app->db;2 $secondaryConnection = \yii:: $app->seconddb;

If you do not want to define a database connection as a global application component, you can initialize it directly in your code:

1 $connection = new \yii\db\connection ([2     ' dsn ' = = $dsn, 3      ' username ' = + $username, 4      ' password ' = > $password, 5]); 6 $connection->open ();

Tip: If you need to perform additional SQL queries after creating a connection, you can add the following code to the application configuration file:

return [    //...    ] Components ' = + [        //...        ' db ' = = [            ' class ' = ' yii\db\connection ',            //...            ' On Afteropen ' = function ($event) {                $event->sender->createcommand ("SET time_zone = ' UTC ')->execute () ;            }        ],    ],    // ...];

SQL Basic Query

Once you have a connection instance, you can execute the SQL query through [[Yii\db\command]].

SELECT Query

The query returns multiple rows:

1 $command = $connection->createcommand (' SELECT * from Post '), 2 $posts = $command->queryall ();

　　

Return a single line:

1 $command = $connection->createcommand (' SELECT * from post WHERE id=1 '); 2 $post = $command->queryone ();

Query multi-line single value:

1 $command = $connection->createcommand (' SELECT title from Post '), 2 $titles = $command->querycolumn ();

Query scalar value/computed value:

1 $command = $connection->createcommand (' SELECT COUNT (*) from Post '), 2 $postCount = $command->queryscalar ();

UPDATE, INSERT, delete updates, insertions and deletions, etc.

If execute SQL does not return any data, you can use the Execute method in the command:

1 $command = $connection->createcommand (' UPDATE post SET status=1 WHERE id=1 '); 2 $command->execute ();

You can use insert , update and delete methods, these methods generate the appropriate SQL based on the parameters and execute them.

1//INSERT 2 $connection->createcommand ()->insert (' User ', [3     ' name ' = ' Sam ', 4     ' age ' = 5])-&G T;execute (); 6  7//Insert multiple lines at a time 8 $connection->createcommand ()->batchinsert (' User ', [' name ', ' age '], [9     [' Tom ', 30],     [' Jane ', 20],11     [' Linda ', 25],12],->execute//UPDATE15 $connection->createcommand () Update (' User ', [' status ' = 1], ' Age >->execute '), +//DELETE18 $connection->createcommand () Delete (' user ', ' status = 0 ')->execute ();

Referenced table and column names

The following syntax is used most of the time to safely reference table and column names:

1 $sql = "Select COUNT ([[$column]]) from {{table}}"; 2 $rowCount = $connection->createcommand ($sql)->queryscalar () ;

The above code will be converted to refer to the [[$column]] appropriate column name instead of {{table}} referencing the appropriate table name. The table name has a special variable {{%Y}}, and if you set the table prefix you can automatically prefix the table name with the variant:

1 $sql = "Select COUNT ([[$column]]) from {{% $table}}"; 2 $rowCount = $connection->createcommand ($sql)->queryscalar ();

If the table prefix is set as follows in the configuration file, the above code will query the result in the Tbl_table table:

1 return [2     //... 3     ' Components ' = [4         //... 5         ' db ' = + [6             //... 7             ' tableprefix ' = ' tbl_ ', 8         ], 9     ],10];

Another option for manually referencing table and column names is to use [[Yii\db\connection::quotetablename ()]] and [[Yii\db\connection::quotecolumnname ()]]:

1 $column = $connection->quotecolumnname ($column), 2 $table = $connection->quotetablename ($table); 3 $sql = "Select COUNT ($column) from $table "; 4 $rowCount = $connection->createcommand ($sql)->queryscalar ();

Preprocessing statements

To pass a query parameter for security, you can use a preprocessing statement, first using a :placeholder placeholder, and then binding the variable to the corresponding placeholder:

$command = $connection->createcommand (' SELECT * from post WHERE id=:id '); $command->bindvalue (': Id ', $_get[' id ']) ; $post = $command->query ();

Another use is to prepare a preprocessing statement and execute multiple queries:

1 $command = $connection->createcommand (' DELETE from post WHERE id=:id '), 2 $command->bindparam (': Id ', $id); 3 4 $id = 1;5 $command->execute (); 6 7 $id = 2;8 $command->execute ();

Hint that binding a variable before execution and then changing the value of the variable in each execution (typically used in a loop) is more efficient.

Transaction

When you need to execute multiple related sequences in sequence query , you can encapsulate them in a transaction to protect data consistency. YII provides a simple interface to implement transactional operations. Execute the SQL transaction query statement as follows:

1 $transaction = $connection->begintransaction (); 2 try {3     $connection->createcommand ($sql 1)->execute () ; 4      $connection->createcommand ($sql 2)->execute (); 5     //... Execute other SQL statements ... 6     $transaction->commit (); 7} catch (Exception $e) {8     $transaction->rollback (); 9}

We start a transaction by capturing the exception by [[Yii\db\connection::begintransaction () |begintransaction ()]] try catch . When execution succeeds, by [[Yii\db\ Transaction::commit () |commit ()]] commits the transaction and ends, and the transaction is rolled back by [[Yii\db\transaction::rollback () |rollback ()]] When an exception failure occurs.

Multiple transactions can also be nested if required:

1//External transaction 2 $transaction 1 = $connection->begintransaction (); 3 try {4     $connection->createcommand ($sql 1)->execute () 5  6     //Internal Transaction 7     $transaction 2 = $ Connection->begintransaction (); 8     try {9         $connection->createcommand ($sql 2)->execute (),         $transaction 2->commit ();     catch (Exception $e) {         $transaction 2->rollback ();     }14     $transaction 1->commit (); Exception $e) {     $transaction 1->rollback (); 18}

Note that the database you are using must be supported for Savepoints proper execution, and the above code can be executed in all relational data, but only support can Savepoints guarantee security.

YII also supports setting isolation levels for transactions isolation levels , which use the default isolation level of the database when executing transactions, or you can specify isolation levels for things. YII provides the following constants as a common isolation level

• [[\yii\db\transaction::read_uncommitted]]-allows reading of changed uncommitted data, which may result in dirty reads, non-repeatable reads, and Phantom reads
• [[\yii\db\transaction::read_committed]]-allows concurrent transactions to be read after committing, can avoid dirty reads, may lead to repeated reading and phantom reading.
• [[\yii\db\transaction::repeatable_read]]-the same field is repeated for multiple reads, which can result in a phantom read.
• [[\yii\db\transaction::serializable]]-the principle of full compliance with acid, to ensure that no dirty read, non-repeatable read and Phantom read.

You can use the constants above or use a string string command to execute the command in the corresponding database to set the isolation level, for example, for a postgres valid command SERIALIZABLE READ ONLY DEFERRABLE .

Note: Some databases can only set the transaction isolation level for connections, so you must explicitly establish an isolation level for the connection. Currently affected database:MSSQL SQLite

Note: SQLite supports only two transaction isolation levels, so you can only set READ UNCOMMITTED and SERIALIZABLE . Using other isolation levels throws an exception.

Note: PostgreSQL does not allow you to set the isolation level before the transaction starts, so you cannot specify the isolation level at the beginning of the transaction. You can call [[Yii\db\transaction::setisolationlevel ()]] to set after the transaction begins.

About isolation levels [isolation levels]: http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Isolation_levels

database replication and read-write separation

Many databases support databases to replicate database replication to improve availability and responsiveness. In database replication, data is always from the primary server to the slave server . All insert and update write operations are performed on the primary server, while read operations are performed from the server.

configuration [[Yii\db\connection]] enables database replication and read-write separation.

1 [2     ' class ' = ' Yii\db\connection ', 3  4     //Configure Master server 5     ' DSN ' = ' DSN for master server ', 6     ' Usern Ame ' = ' master ', 7     ' password ' + ', 8  9     //config from server     ' Slaveconfig ' and [one         ' username ' = ' Slave ', ' password ' + ', ' attributes ' and ' = '/use             A smaller connection timeout15             PDO: : Attr_timeout = 10,16         ],17     ],18     //configuration from server group     ' slaves ' + [[         ' DSN ' = ' DSN ' for Slave server 1 '],22         [' dsn ' = ' = ' DSN for slave server 2 '],23         [' dsn ' = = ' DSN for slave server 3 '],24         [' d sn ' = ' DSN for slave server 4 '],25     ],26]

The above configuration realizes a main multi-slave structure, from the server to execute the read query, the master server performs the write query, the function of read and write separation is completed automatically by the background code. Callers need not care. For example:

1//Use the above configuration to create a database connection object 2 $db = Yii::createobject ($config), 3 4//By performing a query operation from the server 5 $rows = $db->createcommand (' SELECT * from u Ser LIMIT ')->queryall (); 6 7//Perform update operation via master server 8 $db->createcommand ("Update user SET username= ' demo ' WHERE id=1 ')-&G T;execute ();

Note: Queries executed with [[Yii\db\command::execute ()]] are considered to be write operations, and all other query methods executed using [[Yii\db\command]] are considered read operations. You can go through $db->slave Get the slave server that is currently being used.

ConnectionThe component supports load balancing and failover from the server, and when the first read query is executed, a connection is selected from the server, and if the connection fails, the other is selected, and if all the slave servers are unavailable, the primary server is connected. You can configure [[yii\db\connection::serverstatuscache|server status Cache]] to remember those that cannot connect from the server, making yii over a period of time [[Yii\db\connection:: Serverretryinterval]. Attempts to connect to a slave server that are not available at all are not repeated inside.

Note: In the above configuration, each slave server connection timeout is specified as 10s. If the connection cannot be made within 10s, the server is considered dead. You can also customize the timeout parameter.

You can also configure a multi-master multi-slave structure, for example:

 1 [2 ' class ' = ' Yii\db\connection ', 3 4//Configuration Master server 5 ' masterconfig ' = + [6 ' username ' =& Gt ' Master ', 7 ' password ' + ', 8 ' attributes ' = [9//Use a smaller connection timeout1 0 pdo::attr_timeout = 10,11],12],13 14//Configure Primary server group ' masters ' and [[' DS     n ' = = ' DSN for master server 1 '],17 [' dsn ' = ' = ' DSN for master server 2 '],18],19 20//configuration from server 21             ' Slaveconfig ' = [' username ' and ' slave ', ' password ' + ', ' attributes ' and ' = 25 Use a smaller connection timeout26 pdo::attr_timeout = 10,27],28],29 30/ /configured from server group to ' slaves ' = [+ ' DSN ' = ' DSN for slave server 1 '],33 [' dsn ' = ' DSN for slave s Erver 2 '],34 [' dsn ' = ' = ' DSN for slave server 3 '],35 [' dsn ' = ' DSN for slave server 4 '],36],37 ]

The above configuration developed 2 master servers and 4 slave servers. Connection The component also supports load balancing and failover of the primary server, and, unlike from the server, throws an exception if all primary servers are unavailable.

Note: When you use [[yii\db\connection::masters|masters]] to configure one or more primary servers, Connection other properties in relation to the database connection (for example dsn : username , password ) are ignored.

The transaction uses the primary server's connection by default, and all operations in the transaction will use the primary server's connection, for example:

1//Start Transaction 2 $transaction on primary server connection = $db->begintransaction (); 3  4 try {5     //All queries are executed on the primary server for 6     $rows = $db->createcommand (' SELECT * from User LIMIT ')->queryall (); 7     $db->createcommand ("UPDATE User SET username= ' demo ' WHERE id=1 ')->execute (); 8  9     $ Transaction->commit (), \exception $e) {One     $transaction->rollback ();     $e; 13}

If you want to perform transactional operations from the server, you must explicitly specify, for example:

1 $transaction = $db->slave->begintransaction ();

Sometimes you want to force a master server to execute a read query, and you can call the seMaster() method.

123	$rows= $db->useMaster(function ($db) {    return$db->createCommand(‘SELECT * FROM user LIMIT 10‘)->queryAll();});

You can also set $db->enableSlaves it false so that all queries are executed on the primary server.

Manipulating database schemas to obtain schema information

You can get Schema information through [[Yii\db\schema]] instances:

1 $schema = $connection->getschema ();

This example includes a series of methods to retrieve information about the various aspects of the database:

1 $tables = $schema->gettablenames ();

For more information, please refer to [[Yii\db\schema]]

Modify Mode

In addition to the underlying SQL query, [[Yii\db\command]] includes a series of methods to modify the database schema:

• Create/rename/delete/empty tables
• Add/rename/delete/modify fields
• Add/Remove Primary key
• Add/Remove foreign keys
• Create/Delete Index

Examples of Use:

1//CREATE TABLE

2 $connection->createcommand ()->createtable (' Post ', [3     ' id ' = ' PK ', 4     ' title ' = = ' String ', 5     ' Text ' = ' text ', 6]);

　　

YII 2.0 Database Simple operation (GO)