Archive For The “DB – MySQL” Tutte le categorie:

MYISAM vs INNOBD

The 2 major types of table storage engines for MySQL databases (/var/lib/mysql) are InnoDB and MyISAM. To summarize the differences of features and performance: InnoDB is newer while MyISAM is older. InnoDB is more complex while MyISAM is simpler. InnoDB is more strict in data integrity while MyISAM is loose. InnoDB implements row-level lock for…

SHOW COLUMNS

SHOW COLUMNS displays information about the columns in a given table (It also works for views as of MySQL 5.0.1). The LIKE clause, if present, indicates which column names to match. The WHERE clause can be given to select rows using more general conditions. SHOW COLUMNS FROM <tablename>

– STORED PROCEDURE –

How to create a new procedure Sql Data Access: CONTAINS SQL (indicates that the routine does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly); NO SQL (indicates that the routine contains no SQL statement); READS SQL DATA (the procedure has only reading SQL…

STRING FUNCTIONS

CONCAT(‘string1’, ‘string2’, …., ‘stringN’); SUBSTRING_INDEX(‘string’, ‘delim’ , ‘count’ ): returns the substring from string before occurrences of the delimiter (If count is positive, everything to the left of the final delimiter is returned. If count is negative, everything to the right of the final delimiteris returned); LPAD(‘string’, ‘length’, ‘pad’); Most valuable links – http://dev.mysql.com/doc/refman/5.0/en/string-functions.html –

FETCH

BEGIN DECLARE v_field1 INTEGER(4) DECLARE v_field2 DECIMAL(2,0), DECLARE v_field3 CHAR(1), DECLARE FineTabella INT default 0; –dichiaro cursore per la table1 DECLARE Cursor1 CURSOR FOR SELECT * FROM table1 –dichiaro l’handle per fine cursore DECLARE CONTINUE HANDLER FOR NOT FOUND SET FineTabella =3D 1; –apro il cursore OPEN Cursor1; –ciclo il cursore Loop1: LOOP FETCH Cursor1…

MySql variables declaration

Stored procedures can access and set local, session, and global variables. Local variables are either passed in as parameters or created using the DECLARE statement (no need to use DECLARE statement). Using the DECLARE statement with a DEFAULT will set the value of a local variable: DECLARE myInt INT DEFAULT 0; You can assign values…

Exporting data from mysql to csv file

MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server. Using extended options of the INTO OUTFILE nomenclature, it is possible to create a comma separated value (CSV) which can be imported into a spreadsheet application such as OpenOffice or Excel or any other applciation…

MySql lock table

Lock Table To lock a table in a MySQL database, you should use the LOCK TABLES statement, as shown in the following: LOCK TABLE Books READ; LOCK TABLES Books READ, BookOrders WRITE; You must specify a READ lock type or a WRITE lock type. If you specify READ, any connection can read from the table,…

If, IfNull, NullIf

IF (<expr1>, <expr2>, <expr3>): if <expr1> = TRUE or <expr1> != 0 or <expr1> IS NOT NULL then     <expr2> else     <expr3> IFNULL (<expr1>, <expr2>): if (<expr1> is not null) then     <expr1> else     <expr2> NULLIF (<expr1>, <expr2>): if (<expr1> = <expr2>) = TRUE then     NULL else    …

MySql date functions

DATE_ADD (Add time values (intervals) to a date value): DATE_ADD(MyDate, INTERVAL 2 DAY) [add two days] DATE_SUB (Subtract a time value (interval) from a date): DATE_SUB(MyDate, INTERVAL 5 DAY) [Subtract 5 days] TO_DATE (the to_date function converts a string to a date): TO_DATE(‘2003/07/09’, ‘yyyy/mm/dd’) [would return a date value of July 9, 2003.] TO_DATE(‘070903’, ‘MMDDYY’)…

Go Top