Category: MySQL


TRIGGER

Filed Under: MySQL
Jun.18, 2010

Trigger means procedural code that is automatically executed in response to certain events on a particular table or view in a database. Create trigger requires the trigger privilege for the table associated with the trigger.

TRIGGER syntax:

CREATE TRIGGER name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON tablename
FOR EACH ROW statement

BEFORE or AFTER on trigger is action time to indicate when trigger activities statement activated.
INSERT or UPDATE or DELETE is event indicates the kind of statement that activates the trigger.

How to drop the TRIGGER ?, use DROP TRIGGER order following with table name and trigger name. And the syntax is like this:

DROP TRIGGER tablename.triggername;

Here they are the example case use trigger:
For inserting prosess:

CREATE trigger tr_input before INSERT ON user
FOR each
ROW
BEGIN
INSERT INTO master_user( id, name, pass )
VALUES (
NEW.id, NEW.name, NEW.pass
);
END$$

For updating process:

CREATE trigger tr_update before UPDATE ON user
FOR each
ROW
BEGIN
UPDATE master_user set id=new.id, name=new.name, pass=new.pass
where id=old.id;
END$$

For deleting process:

CREATE trigger tr_delete before DELETE ON user
FOR each
ROW
BEGIN
DELETE from master_user where id=old.id;
END$$

The SELECT privilege for the subject table if references to table colums occur via OLD.col_name or NEW.col_name
The UPDATE privilege for the subject table columns are targets of SET NEW.col_name=value assignment.

Note: in this case I use two table user and master_user. And the scenario is when I input some data or something else into user which automatically updated into master_user.

Tags: , , , , ,
read more

CREATE PROCEDURE and CREATE FUNCTION

Filed Under: MySQL
Jun.15, 2010

CREATE PROCEDURE syntax
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

CREATE FUNCTION syntax
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

func_parameter:
param_name type

CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. They might also require the SUPER privilege, depending on the DEFINER value.

DROP PROCEDURE and DROP FUNCTION Syntax: this statement is used to drop a stored procedure or function.

DROP {PROCEDURE | FUNCTION} [IF EXISTS] procedure_or_function_name

Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters. An IN parameter passes a value into a procedure. An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns. An INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.
Lets see a simple stored procedure using OUT parameter:

CREATE PROCEDURE jumlah( out juml int ) BEGIN SELECT count( * )
INTO juml
FROM FUNCTIONS;
END

Note: The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This allows the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.
And now how to CALL the procedure? Lets see this query:

CALL jumlah(@a);
SELECT @a ;

If you want to see the that procedure is exists on your server, you can try to Export as a sql and check the structure especially Add CREATE PROCEDURE / FUNCTION. On the bottom you will see the procedure you have been created:
–
– Procedures
–
DELIMITER $$
–
CREATE DEFINER=`root`@`localhost` PROCEDURE `jumlah`( out juml int )
BEGIN SELECT count( * )
INTO juml
FROM FUNCTIONS;
END$$

–
DELIMITER ;
–

And then the syntax to drop the procedure if you want to trash it:
DROP PROCEDURE IF EXISTS jumlah

Lets see a simple function:

CREATE FUNCTION fungsi(
aCHAR( 10 )
) RETURNS CHAR( 20 ) DETERMINISTIC RETURN CONCAT( 'Fungsi, ', a, '.' ) ;

Then try to execute that query like this to see the result:

SELECT fungsi('test only');

Like procedure above you can see the function by Export as sql and the bottom look view:

CREATE DEFINER=`root`@`localhost` FUNCTION `fungsi`(a CHAR(10)) RETURNS char(20) CHARSET latin1
DETERMINISTIC
RETURN CONCAT(‘Fungsi, ‘,a,’.')

And then the syntax to drop the procedure if you want to trash it:

DROP FUNCTION IF EXISTS fungsi

Well improve your self with aother case and develop yours.

Tags: , , ,
read more

Regular Expressions

Filed Under: MySQL
Jun.15, 2010

A regular expression describes a set of strings. A regular expressions for the REGEXP operator may use any of the following special characters and constructs:

  • . match any character (including carriage return and new line) 
    SELECT function_name
FROM `functions`
WHERE function_name
REGEXP 'anchor..'
  • ^ match the beginning of a string 
    SELECT function_name
FROM `functions`
WHERE function_name
REGEXP '^a';
  • $ match the end of a string 
    SELECT function_name
FROM `functions`
WHERE function_description
REGEXP 't$';
  • [characters] match any characters or using range 
    SELECT function_name
FROM `functions`
WHERE function_name
REGEXP '^[a-d]';

Still using function table, let’s see the other developing using regex.
To view function_name which has 4 characters:

SELECT function_name
FROM `functions`
WHERE function_name
REGEXP '^....$';

Or we can write like this:

SELECT function_name
FROM `functions`
WHERE function_name
REGEXP '^.{4}$';

Hmm I think is enough for introduction of REGEXP :) .

Tags: ,
read more

LIKE and NOT LIKE

Filed Under: MySQL
Jun.14, 2010

The like and not like have two search symbols. The underscore _ character that looks for one character and the percentage % character that looks for zero or more characters. I use function  table which has function_name,  function_name and function_description fields. Lets see the example:

SELECT *
FROM `functions`
WHERE function_name LIKE 'a%'
LIMIT 0 , 30

Above query will only pick out result that provide a TRUE result according to the WHERE equation. We can see that equation will equal the LIKE value plus some possible extra characters afterwards.

The LIKE search is not case sensitive, so it will accept anything starting with ‘a’ as well.

So how LIKE search can make a different lowercase or uppercase letters? by adding BINARY word after LIKE.

SELECT *
FROM `functions`
WHERE function_name LIKE BINARY "a%"
LIMIT 0 , 30;

And the change the query like below to see the different:

SELECT *
FROM `functions`
WHERE function_name LIKE BINARY "A%"
LIMIT 0 , 30;

Queries using the LIKE or NOT LIKE parameters may be a bit slower than a normal query search considering they are a broader value and do not take advantage of any indexing.

Note: If you want to have an underscore or percentage character actually be part of the search value, put an escape slash \ in front of the character.

The underscore wildcard can be used a number of times to find a specific number of characters. Example, this would be used in an equation to return a value of ‘Stan’ plus 3 characters.

SELECT *
FROM `functions`
WHERE function_name LIKE BINARY "mdat___"
LIMIT 0 , 30;

The underscore and percentage characters (also known as wildcard) can be used in front, at the end, or both ends of a value.

Tags: , , , ,
read more

Operator Precedence

Filed Under: MySQL
Jun.14, 2010

The precedence of operators determines the order of evaluation. To override this order and group terms explicity, use parentheses. Lets see the example below:

Select 6+2-4*1, (6+2-4)*1;

Operator are shown bellow from lowest to the highest precedence. Operators shown together on a line have the same precedence. Lets take a look:

:=
||, OR, XOR
&&, AND
NOT
BETWEEN, CASE, WHEN, THEN, ELSE
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
|
&
<<, >>
-, +
*, /, DIV, %, MOD
^
- (unary minus), ~ (unary bit inversion)
!
BINARY, COLLATE

Note: If the HIGH_NOT_PRECEDENCE SQL mode is enabled, the precedence of NOT is the same as that of the ! operator.

Tags:
read more
Powered by WordPress.