Mysql cases, views, functions, trigger commands (details), mysql triggers

Mysql cases, views, functions, trigger commands (details), mysql triggers

Enable and use

// Modify the table engine alter table a engine = myisam; // enable transaction begin; // disable automatic commit set autocommit = 0; // deduct 100 update bank set money = money-100 where bid = 1; // roll back, all SQL statement operations starting with begin rollback; // start the transaction begin; // disable automatic submission set autocommit = 0; // deduct 100 update bank set money = money-100 where bid = 1; // Add 100 update bank set money = money + 100 where bid = 2; // submit commit;

Instance operations

$ Dsn = "mysql: host = 127.0.0.1; dbname = c58"; try {// connect to the database through pdo $ pdo = new Pdo ($ dsn, 'root ',''); // set the error to the exception mode to try catch to receive $ pdo-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION ); // SET the character SET $ pdo-> query ("set names utf8"); // start the transaction $ pdo-> query ("BEGIN "); // disable automatic submission $ pdo-> query ("set autocommit = 0 "); // transfer // deduct 100 $ pdo-> exec ('Update bank SET money = money-100 WHERE bid = 1 '); // Add 100 $ pdo-> exec ('Update bank SET money = money + 100 WHERE bid = 2 '); // submit $ pdo-> query ('commit ');} catch (PDOException $ e) {$ pdo-> query ('rollback '); echo $ e-> getMessage ();}

Note: items can help us operate data more securely.

View creation, deletion, and use

// 1. create view bankview as select bid, bname from bank; // 2. VIEW the show table status where comment = 'view'; // 3. modify the view alter view bankview as select bid from bank; // 4. delete view drop view bankview;

Create, delete, query, and use stored procedures

// Change the boundary operator

// Change the boundary \ d $ // create procedure get_bid (inout n char (20) charset utf8) begin select bid from bank where name = n; end $ // call set @ name = 'zhen' $ call get_bid (@ name) $ // Stored Procedure job // 1. create a stored procedure for deleting a class // 2. delete the students in this class when deleting the class. // 3. call del_class (1); // create table class (cid int unsigned primary key auto_increment, cname char (20) not null default ''); create table stu (sid int unsigned primary key auto_increment, sname char (20) 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 = 1 $ call del_class (@ id) $ // 1.in( output value passed in outside, cannot change value passed in outside) create procedure a (in id int) begin select id; set id = 100; end $ // 2.out( the value passed in outside cannot be output, and the value passed in outside can be changed) create procedure B (out id int) begin select id; set id = 100; end $ // 3. inout (based on the above two cases) create procedure insert_data (in num int) begin while num> 0 do insert into class set cname = num; set num = num-1; end while; end $ // view the status show procedure status; // Delete the stored procedure "get_bid" drop procedure get_bid;

Create, delete, and use storage functions

// Create function hello (s char (20) charset utf8) returns char (50) reads SQL databegin return concat ('hello', s ,'! '); End $ // call select hello ('hdw') $ + -------------- + | hello ('hdw') | + -------------- + | hello hdw! | + -------------- + // Delete drop function hello $ // create storage function getcid (n char (20) charset utf8) returns intreads SQL databegin return (select cid from stu where sname = n); end $ // The storage function can be used in SQL statements to select cname from class where cid = getcid ('kitten ') $

Trigger creation, deletion, and use

// Deletion class automatically triggers deletion of student create trigger del_class_stu after delete on classfor each rowbegin delete from stu where cid = old. cid; end $ // when a trigger job creates an article table that contains the title, author, and release time fields. If only the title is added, the release time field is automatically set to the current time, the author field is set to 123 Network \ d $ create trigger this_name before insert on this_table for each rowbeginif new. uname is null thenset new. uname = '000000'; end if; if new. timer is null thenset new. timer = unix_timestamp (now (); end if; end $ // query existing triggers show triggers;

Note: The trigger is set to execute another method when executing a behavior!

The above Mysql Case, views, functions, and trigger commands (detailed description) are all the content shared by Alibaba Cloud. I hope you can give us a reference and support for the customer's house.