เล่าถึงการทดสอบ SQL บน MariaDB ใน XAMPP 5.6
1. View
MariaDB [northwind]> [create/replace] view products_asc as
-> select productname from products
-> order by productname asc;
MariaDB [northwind]> show tables; จะพบ products_asc
MariaDB [northwind]> select * from products_asc;
2. Function
MariaDB [northwind]> delimiter //
MariaDB [northwind]> create function mycal(qty int, price int)
-> returns int
-> begin
-> return (qty * price);
-> end//
MariaDB [northwind]> delimiter ;
MariaDB [northwind]> select productid, unitprice, unitsinstock,
-> mycal(unitprice,unitsinstock) from products;
3. Store Procedure
MariaDB [(none)]> use northwind;
Database changed
MariaDB [northwind]> describe products;
MariaDB [northwind]> delimiter //
MariaDB [northwind]> create procedure getpname (in pid int(11))
-> begin
-> select productname from products
-> where productid = pid;
-> end//
MariaDB [northwind]> delimiter ;
MariaDB [northwind]> call getpname(1);
productname = Chai
MariaDB [northwind]> delimiter //
MariaDB [northwind]> create procedure cntproducts (out cntpro int(11))
-> begin
-> set cntpro = (select count(productid) as cnt from products);
-> end//
MariaDB [northwind]> delimiter ;
MariaDB [northwind]> call cntproducts(@mycnt);
MariaDB [northwind]> select @mycnt;
MariaDB [northwind]> show procedure status;
MariaDB [northwind]> drop procedure cntproducts;
4. Trigger
MariaDB [northwind]> create table account(id int,amt int);
MariaDB [northwind]> create trigger ins_sum before insert on account
-> for each row set @sum = @sum + new.amt;
MariaDB [northwind]> set @sum = 0;
MariaDB [northwind]> insert into account values(1,10),(2,25);
MariaDB [northwind]> select @sum as 'total';
total = 35
MariaDB [northwind]> drop trigger ins_sum;
MariaDB [northwind]> set @sum = NULL;
https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html
