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

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.