Mysql matters, views, functions, Triggers command (detailed) _mysql

Matters open and use

Modifies table's engine
ALTER TABLE a engine=myisam;
Start the transaction
begin;
Turn off autocommit
set autocommit=0;
Button
Update bank set money=money-100 where bid=1;
Rollback, all SQL statements starting at begin Operation
Rollback;

Start the transaction
begin;
Turn off autocommit
set autocommit=0;
Button
Update bank set money=money-100 where bid=1;
Add the
update bank set money=money+100 where bid=2;
Commits a
commit;

Instance operations

$DSN = "MYSQL:HOST=127.0.0.1;DBNAME=C58";
try {
  //Connect the database via PDO
  $pdo = new PDO ($DSN, ' root ', ');
  The error is set to the exception mode in order to try catch to receive
  $pdo->setattribute (pdo::attr_errmode,pdo::errmode_exception);
  Sets the character set
  $pdo->query ("Set NAMES UTF8");
  Open transaction
  $pdo->query ("BEGIN");
  Turn off autocommit
  $pdo->query ("SET autocommit=0");
  Transfer
  //deduct
  $pdo->exec (' UPDATE bank SET money=money-100 WHERE bid=1 ');
  Plus
  $pdo->exec (' UPDATE bank SET money=money+100 WHERE bid=2 ');
  Submit
  $pdo->query (' COMMIT ');
} catch (Pdoexception $e) {
  $pdo->query (' ROLLBACK ');
  echo $e->getmessage ();
}

NOTES: Items can help us to manipulate data more securely

View creation Delete and use

1. Create VIEW
Bankview as select Bid,bname from bank;
2. View View Show
table status where comment= ' view ';
3. Modify View
alter view Bankview as select bid from bank;
4. Delete View
drop view Bankview;

Creation of stored procedures Delete query and use

Accounts Boundary character

Accounts boundary character \d $//Create stored procedure creation procedure Get_bid (inout n char (M) charset UTF8) begin select bid from bank where Name=n; End $//Invoke Set @name = ' Quake ' $ call Get_bid (@name) $//stored procedure job//1. Create a stored procedure//2 that deletes a class. Deletes the student//3 in this class when implementing the Delete class.
Calling the way call Del_class (1);
Create table Creation table Class (CID int unsigned primary key auto_increment, CNAME char NOT NULL default "); CREATE TABLE stu (SID int unsigned primary key auto_increment, sname char NOT NULL default ", CID int unsigned
Not null default 0);
  \d $ CREATE PROCEDURE del_class (inout ID smallint) begin delete from class where cid=id;
Delete from Stu where cid=id;
  End $ set @id the =1$ call Del_class (@id) $//1.in (output passed values outside, cannot change the value passed outside) CREATE procedure a (in ID int) begin SELECT ID;
Set id=100;
  End $//2.out (can not output the value passed in outside, can change the value passed outside) CREATE PROCEDURE B (out ID int) begin SELECT ID;
Set id=100; End $//3.inout (both of these scenarios) CREATE PROCEDURE insert_data (in num int) begin while num > 0 does insert into class set CN
  Ame=num; Set NUm = num-1;
End while;

End $//view state show procedure status; Delete get_bid This stored procedure drop procedure get_bid;

Stored functions create deletes and use

Creating a Create
function hello (s char () charset UTF8)
returns char
reads SQL data
begin
  Return concat (' Hello ', S, '! ');
End
$

//Call
Select Hello (' HDW ') $
+--------------+
| Hello (' HDW ') |
+--------------+
| Hello HDW! |
+--------------+//

Delete
drop function hello$

//Create a stored
function getcid (n char) charset UTF8)
returns int
reads SQL data
begin return
  (select CID from Stu where sname=n);
End
$
//Storage function can be used in SQL statements
select CNAME from class where Cid=getcid (' Kitten ') $

Triggers create deletes and use

Delete Class Auto trigger Delete student
CREATE trigger Del_class_stu after delete on class for each
row
begin
  Delete from Stu Wher e cid=old.cid;
End
$

//Trigger Job
creation Article table with title, author, publish Time field
if only the title is added, the Publish Time field is automatically set to the current time, and the
author field is set to 123 net
\d $
Create trigger This_name before insert on this_table for each row
begin
If new.uname is null then
set NEW.U Name= ' 123 ';
End If;
If New.timer is a null then
set New.timer=unix_timestamp (now);
End If;
End
$

//query already has a trigger show
triggers;

NOTE: Triggers are set to execute another method when performing an action!

The above MySQL matters, views, functions, trigger commands (detailed) is a small series to share all the content, hope to give you a reference, but also hope that we support the cloud-dwelling community.