Yii2 -- Use Database Operation Summary (add, delete, query, modify, and transaction), and add and delete yii2

Source: Internet
Author: User

Yii2 -- Use Database Operation Summary (add, delete, query, modify, and transaction), and add and delete yii2

This article introduces Yii2-Database Operation Summary (add, delete, query, modify, and transaction), as follows:

Object operations

Query

// 1. simple query $ admin = Admin: model ()-> findAll ($ condition, $ params); $ admin = Admin: model ()-> findAll ("username =: name ", array (": name "=> $ username); $ infoArr = NewsList: model () -> findAll ("status = '1' order by id DESC limit 10"); // 2. findAllByPk (this method queries a set based on the primary key and multiple primary keys can be used) $ admin = Admin: model ()-> findAllByPk ($ postIDs, $ condition, $ params ); $ admin = Admin: model ()-> findAllByPk ($ id, "name like: name and age =: age", array (': Name' => $ name, 'age' => $ age); $ admin = Admin: model ()-> findAllByPk (array (1, 2 )); // 3. findAllByAttributes (this method is used to query a set based on conditions. Multiple conditions can be used to put conditions in the array) $ admin = Admin: model ()-> findAllByAttributes ($ attributes, $ condition, $ params); $ admin = Admin: model ()-> findAllByAttributes (array ('username' => 'admin'); // 4. findAllBySql (this method queries an array based on an SQL statement) $ admin = Admin: model ()-> findAllBySql ($ SQL, $ params); $ admin = Admin :: model ()-> find AllBySql ("select * from admin where username like: name", array (': name' =>' % ad % '); User: find () -> all (); this method returns all data; User: findOne ($ id); this method returns a piece of data with primary key id = 1 (for example); User :: find ()-> where (['name' => 'hangzhou'])-> one (); this method returns a piece of data from ['name' => 'hangzhou']; User: find ()-> where (['name' => 'hangzhou']) -> all (); this method returns all data from ['name' => 'hangzhou']; User: find ()-> orderBy ('Id desc ') -> all (); this method is a Sort query; User: findBySql ('select * FROM u Ser ')-> all (); this method uses SQL statements to query all data in the user table. User: findBySql ('select * FROM user')-> one (); this method uses an SQL statement to query a piece of data in the user table. User: find ()-> andWhere (['sex' => 'male ', 'age' => '24'])-> count ('id'); Total number of qualified items; User: find ()-> one (); this method returns a data entry; User: find ()-> all (); this method returns all data; User: find ()-> count (); this method returns the number of records; User: find ()-> average (); this method returns the average value of the specified column; User: find ()-> min (); this method returns the minimum value of the specified column; User: find ()-> max (); this method returns Specify the maximum value of the column; User: find ()-> scalar (); query result of the first column in the first row returned by this method; User: find () -> column (); this method returns the value of the first column in the query results; User: find ()-> exists (); this method returns a value indicating whether the data row contains the query result. User: find ()-> batch (10); 10 data records are retrieved each time. User: find () -> each (10); obtain 10 pieces of data each time. Iteration query 2. query object method // query an object based on the primary key, for example, findByPk (1 ); $ admin = Admin: model ()-> findByPk ($ postID, $ condition, $ params); $ admin = Admin: model ()-> findByPk (1 ); // a group of data can be queried based on one condition, but only the first row of Data $ row = Admi is returned. N: model ()-> find ($ condition, $ params); $ row = Admin: model ()-> find ('username =: name ', array (': name' => 'admin'); // This method is used to query a group of data based on conditions. Multiple conditions can be used to put conditions into the array, the first data item is queried. $ admin = Admin: model ()-> findByAttributes ($ attributes, $ condition, $ params); $ admin = Admin: model () -> findByAttributes (array ('username' => 'admin'); // This method is used to query a group of data based on SQL statements, it also queries the first data $ admin = Admin: model ()-> findBySql ($ SQL, $ params); $ admin = Admin: model ()-> fi NdBySql ("select * from admin where username =: name", array (': name' => 'admin'); // spell a method to obtain the SQL statement, query an object based on find $ criteria = newCDbCriteria; $ criteria-> select = 'username '; // only select the 'title' column $ criteria-> condition = 'username =: username'; // note that this is a query condition and only one query condition exists. use addCondition $ criteria-> params = array (": username => 'admin'") for multiple conditions; $ criteria-> order = "id DESC "; $ criteria-> limit = "3"; $ post = Post: model ()-> Find ($ criteria); // $ params isnot needed // multi-condition query statement $ criteria = new CDbCriteria; $ criteria-> addCondition ("id = 1 "); // query condition, that is, where id = 1 $ criteria-> addInCondition ('id', array (, 5); // represents where id IN, 3, 4, 5,); $ criteria-> addNotInCondition ('id', array (1, 2, 3, 4, 5); // exactly the same as above, is not in $ criteria-> addCondition ('Id = 1', 'or'); // This is an OR condition. When multiple conditions exist, this condition is OR instead of AND $ criteria-> addSearchCondition ('name', 'classified'); // The search condition is actually .. Where name like '% Category %' $ criteria-> addBetweenCondition ('id', 1, 4); // between 1 and 4 $ criteria-> compare ('id ', 1); // This method is special. It will be automatically processed as addCondition or addInCondition based on your parameters. $ criteria-> compare ('id', array (1, 2, 3); // if the second parameter is an array, addInCondition $ criteria-> select = 'id, parentid, name'; // indicates the field to be queried. The default value is select = '*'; $ criteria-> join = 'xxx '; // connect table $ criteria-> with = 'xxx'; // call relations $ criteria-> limit = 10; // obtain 1 piece of data, If the value is less than 0, $ criteria-> offset = 1; // if the two values are merged, the limit 10 offset 1 is used. Limit $ criteria-> order = 'xxx DESC, xxx ASC '; // sorting condition $ criteria-> group = 'group condition '; $ criteria-> having = 'having condition '; $ criteria-> distinct = FALSE; // whether the query is unique. 3. The number of queries, determine whether a query has results // This method queries the number of records in a set based on a condition and returns an int number $ n = Post: model () -> count ($ condition, $ params); $ n = Post: model ()-> count ("username =: name", array (": name "=> $ username); // This method queries the number of records in a set based on SQL statements, and returns an int number $ n = Post: model () -> countBySql ($ SQL, $ params); $ n = Post: model ()-> countBySql ("select * from admin where username =: name", array (': name' => 'admin'); // This method is used to query the queried Array Based on a condition. If any data exists, a true value is returned, otherwise, $ exists = Post: model ()-> exists ($ condition, $ params); $ exists = Post: model ()-> exists ("name =: name ", array (": name "=> $ username); 4. Add $ admin = new Admin; $ admin-> username = $ username; $ admin-> password = $ password; if ($ admin-> save ()> 0) {echo "added successfully";} else {echo "failed to add ";} 5. Modify Post: model ()-> updateAll ($ attributes, $ condition, $ params); $ count = Admin: model () -> updateAll (array ('username' => '000000', 'Password' => '000000'), 'password =: pass', array (': pass '=> '1111a1'); if ($ count> 0) {echo "modification succeeded";} else {echo "modification failed";} $ rt = PostList :: model ()-> updateAll (array ('status' => '1'), 'staff _ id =: staff AND host_id =: host', array (': staff '=> $ staff_id,': host' => $ host_id); // $ primary key, either a set or, $ attributes is the set of fields to be modified, $ condition, $ params passed value Post: model ()-> updateByPk ($ pk, $ attributes, $ condition, $ params); $ count = Admin: model ()-> updateByPk (1, array ('username' => 'admin ', 'Password' => 'admin'); $ count = admin: model ()-> updateByPk (array (1, 2), array ('username' => 'admin ', 'Password' => 'admin'), 'username =: name', array (': name' => 'admin'); if ($ count> 0) {echo "modification succeeded";} else {echo "modification failed";} Post: model ()-> updateCounters ($ counters, $ condition, $ params ); $ count = Admin: model ()-> updateCounters (array ('status' => 1), 'username =: name', array (': name' => 'admin'); if ($ count> 0) {echo "modification succeeded";} else {echo "modification failed ";} // array ('status' => 1) indicates that the admin table in the database is named 'admin' according to the condition username = 'admin ', all the status fields in the query result are added to "1" and "delete" // deleteAll Post: model ()-> deleteAll ($ condition, $ params); $ count = Admin :: model ()-> deleteAll ('username =: name and password =: pass', array (': name' => 'admin ',': pass '=> 'admin'); $ count = admin: model ()-> deleteAll ('Id in ("1, 2, 3 ")'); // if ($ count> 0) {echo "deleted successfully" ;}else {echo "deleted failed" ;}// deleteByPk Post :: model ()-> deleteByPk ($ pk, $ condition, $ params); $ count = Admin: model ()-> deleteByPk (1); $ count = Admin :: model ()-> deleteByPk (array (1, 2), 'username =: name', array (': name' => 'admin'); if ($ count> 0) {echo "deleted successfully";} else {echo "failed to delete ";}

Direct database operations

Query

// CreateCommand (executes native SQL statements) $ SQL = "SELECT u. account, I. * FROM sys_user as u left join user_info as I on u. id = I. user_id "; $ rows = Yii: $ app-> db-> createCommand ($ SQL)-> query (); foreach ($ rows as $ k => $ v) {echo $ v ['add _ time'];} multiple rows are returned for the query: $ command = $ connection-> createCommand ('select * FROM Post '); $ posts = $ command-> queryAll (); returns a single row: $ command = $ connection-> createCommand ('select * FROM post WHERE id = 1 '); $ post = $ command-> queryOne (); query multiple rows of single value: $ command = $ connection-> createCommand ('select title FROM Post '); $ titles = $ command-> queryColumn (); query scalar value/calculated value: $ command = $ connection-> createCommand ('select COUNT (*) FROM Post '); $ postCount = $ command-> queryScalar ();

Update

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

Insert update Delete

// 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 (); // UPDATE $ connection-> createCommand ()-> update ('user', ['status' => 1], 'Age> 30')-> execute (); // DELETE $ connection-> createCommand ()-> delete ('user', 'status = 0')-> execute ();

Transactions

// Basic structure of the transaction (use the transaction to process the update and insert operations for multiple tables) $ dbTrans = Yii: app ()-> db-> beginTransaction (); try {$ post = new Post; $ post-> 'title' = 'Hello dodobook !!! '; If (! $ Post-> save () throw newException ("Error Processing Request", 1); $ dbTrans-> commit (); // $ this-> _ end (0, 'added successfully !!! ');} Catch (Exception $ e) {$ dbTrans-> rollback (); // $ this-> _ end ($ e-> getCode (), $ e-> getMessage ());

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.