TIME NOW
World current time now,
CALENDAR
Calendar monthly, yearly
login CONVERT LENGTH
login CONVERT TEMPERATURE
login DICTIONARIES, LISTS
login SCIENCE EDUCATION RELIGION
login WORK CALCULATOR
login CALCULATE LIFE

MYSQL dabatase. How to, tutorial, examples

Previous articlePage bottomNext article  ALL TOPICS

Mysql. create alter drop view procedure function event trigger example

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

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

PROCEDURE, FUNCTION examples:
delimiter //
CREATE PROCEDURE datacount (OUT param1 INT)
BEGIN


SELECT COUNT(*) INTO param1 FROM table;
END//
delimiter ;
CALL datacount(@a);
SELECT @a;
 
CREATE FUNCTION hello (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');
SELECT hello('world');
 
ALTER PROCEDURE ALTER PROCEDURE
ALTER FUNCTION specification:
ALTER FUNCTION func_name [characteristic ...]

ALTER PROCEDURE proc_name [characteristic ...]

characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL

DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
 
DROP PROCEDURE, PROCEDURE
DROP FUNCTION specification:
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

DROP FUNCTION example:
DROP PROCEDURE IF EXISTS datacount;
 
CREATE EVENT

CREATE EVENT specification:
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]

event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;

schedule:
AT timestamp [+ INTERVAL interval] ...

| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]

interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}


Min CREATE EVENT
.
CREATE EVENT event_name
.
ON SCHEDULE
.
DO

CREATE EVENT example:
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO

UPDATE db.table SET column = column + 1;
ALTER EVENT
ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO event_body]


ALTER EVENT example:
CREATE EVENT myevent
ON SCHEDULE


EVERY 6 HOUR
COMMENT 'A comment.'
DO


UPDATE myschema.mytable SET mycol = mycol + 1;
 
ALTER EVENT myevent
ON SCHEDULE


AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
RENAME TO myeventdelete
DO


TRUNCATE TABLE myschema.mytable;

DROP EVENT
 
DROP EVENT [IF EXISTS] event_name

DROP EVENT example:
DROP EVENT IF EXISTS myeventdelete;
CREATE TRIGGER
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body


TRIGGER example:
CREATE TABLE t1 (a1 INT);
CREATE TABLE t2 (a2 INT);
delimiter //
CREATE TRIGGER test_trig BEFORE INSERT


ON t1 FOR EARCH ROW BEGIN
INSERT INTO t2 SET a2 = NEW.a1;


END//
delimiter ;
INSERT INTO t1 VALUES (1), (2), (10);
 
DROP TRIGGER
 
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

DROP TRIGGER example:
DROP TRIGGER IF EXISTS test_trig;
 
CREATE VIEW
CREATE


[OR REPLACE]


[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]


[DEFINER = { user | CURRENT_USER }]


[SQL SECURITY { DEFINER | INVOKER }]


VIEW view_name [(column_list)]


AS select_statement


[WITH [CASCADED | LOCAL] CHECK OPTION]


VIEW example:
CREATE TABLE t (qty INT, price INT);
INSERT INTO t VALUES(3,50);
CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
SELECT * FROM v;
ALTER VIEW
ALTER


[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]


[DEFINER = { user | CURRENT_USER }]


[SQL SECURITY { DEFINER | INVOKER }]


VIEW view_name [(column_list)]


AS select_statement


[WITH [CASCADED | LOCAL] CHECK OPTION]

Same with CREATE OR REPLACE VIEW.
 
DROP VIEW DROP VIEW
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]

Example:
DROP VIEW IF EXISTS v1, v2, v3;
 
Previous articlePage topNext article  ALL TOPICS



 Use username: Guest, Anonymous, Programmer






QUOTES:
Everyone of us needs to show how much we care for each other and, in the process, care for ourselves.
Princess of Wales Diana
Coming together is the beginning. Keeping together is progress. Working together is success.
Henry Ford
The best suggestion ever: Dont choose the one who is beatiful to the world. But rather, choose the one who makes your world beautiful =)
yako