Read file content

Load file content
SELECT LOAD_FILE("D:/Temp/test.txt");
Copy a file to another one
SELECT LOAD_FILE("D:/Temp/test.txt") INTO OUTFILE "D:/Temp/test2.txt"

Generate a thousand daily dates starting today

CREATE TABLE test.ints(i tinyint);
INSERT INTO test.ints VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 
SELECT CURDATE() + INTERVAL t1.i*100 + t2.i*10 + t3.i DAY AS Date
FROM test.ints AS t1 
  JOIN test.ints AS t2
  JOIN test.ints AS t3
WHERE ( t1.i*100 + t2.i*10 + t3.i ) < 1000
ORDER BY Date; 

Pligg message table

This table contains messages sent to users.
CREATE TABLE `pligg_messages` (
`idMsg` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`body` text COLLATE utf8_unicode_ci,
`sender` int(11) NOT NULL DEFAULT '0',
`receiver` int(11) NOT NULL DEFAULT '0',
`senderLevel` int(11) NOT NULL DEFAULT '0',
`readed` int(11) NOT NULL DEFAULT '0',
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`idMsg`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Get Pligg version

SELECT data
FROM pligg_misc_data
WHERE name = 'pligg_version'

Pligg Comment table

Go to Pligg website to learn more.
CREATE TABLE `pligg_comments` (
`comment_id` int(20) NOT NULL auto_increment,
`comment_randkey` int(11) NOT NULL default '0',
`comment_parent` int(20) default '0',
`comment_link_id` int(20) NOT NULL default '0',
`comment_user_id` int(20) NOT NULL default '0',
`comment_date` datetime NOT NULL,
`comment_karma` smallint(6) NOT NULL default '0',
`comment_content` text collate utf8_unicode_ci,
`comment_votes` int(20) NOT NULL default '0',
`comment_status` enum('discard','moderated','published') character set utf8 NOT NULL default 'published',
PRIMARY KEY (`comment_id`),
UNIQUE KEY `comments_randkey` (`comment_randkey`,`comment_link_id`,`comment_user_id`,`comment_parent`),
KEY `comment_link_id` (`comment_link_id`,`comment_parent`,`comment_date`),
KEY `comment_link_id_2` (`comment_link_id`,`comment_date`),
KEY `comment_date` (`comment_date`),
KEY `comment_parent` (`comment_parent`,`comment_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Pligg Category table

Go to Pligg website to learn more.
CREATE TABLE `pligg_categories` (
`category__auto_id` int(11) NOT NULL auto_increment,
`category_lang` varchar(2) collate utf8_unicode_ci default 'en',
`category_id` int(11) NOT NULL default '0',
`category_parent` int(11) NOT NULL default '0',
`category_name` varchar(64) collate utf8_unicode_ci default NULL,
`category_safe_name` varchar(64) collate utf8_unicode_ci default NULL,
`rgt` int(11) NOT NULL default '0',
`lft` int(11) NOT NULL default '0',
`category_enabled` int(11) NOT NULL default '1',
`category_order` int(11) NOT NULL default '0',
`category_desc` varchar(255) collate utf8_unicode_ci default NULL,
`category_keywords` varchar(255) collate utf8_unicode_ci default NULL,
`category_author_level` enum('normal','admin','god') character set utf8 NOT NULL default 'normal',
`category_author_group` varchar(255) collate utf8_unicode_ci default NULL,
`category_votes` varchar(4) character set utf8 NOT NULL default '',
PRIMARY KEY (`category__auto_id`),
KEY `category_id` (`category_id`),
KEY `category_parent` (`category_parent`),
KEY `category_safe_name` (`category_safe_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

MySQL Quiz

What is the maximum length of a table name, a database name, index name, constraint name, stored procedure name, or a field name in MySQL?

A. 256
B. 50
C. 64
D. 128

Answer: C
Highlight to find out the answer.

Get all running queries in MySQL

Sometimes you need to determine which queries are running too long in MySQL db, this script may help you:
SHOW PROCESSLIST; (get first 100 characters of the query)
OR
SHOW FULL PROCESSLIST; for more detail in the query

PIVOT with MySQL

DROP TABLE IF EXISTS tbl_sales_revenue;
CREATE TABLE tbl_sales_revenue(dept_id int, year int, revenue double);
INSERT INTO tbl_sales_revenue(dept_id, year, revenue)
VALUES
 (1,2007,1200),
 (1,2008,1000),
 (1,2009,2000),
 (2,2007,3000),
 (2,2008,4000),
 (2,2009,5000),
 (3,2007,500),
 (3,2008,1200),
 (3,2009,1500);
 
SELECT
 IFNULL(dept_id,'Total') AS 'Department',
 a.2007, a.2008, a.2009,
 a.2007 + a.2008 + a.2009 AS Total
FROM (
 SELECT
   dept_id,
   SUM(IF(year=2007, revenue, 0)) As '2007',
   SUM(IF(year=2008, revenue, 0)) As '2008',
   SUM(IF(year=2009, revenue, 0)) As '2009'
 FROM tbl_sales_revenue
 GROUP BY dept_id WITH ROLLUP
 ) AS a; 

Restore a MySQL database from backup file

First, create an empty database:
CREATE DATABASE new_database_name;

Use this syntax to restore:
$mysql -u root -p new_database_name < backup_file_name.sql

Note that this mysql command run from the folder you install MySQL, for example: C:\Program Files\MySQL\MySQL Server 5.1\bin
The following command I run from my computer to restore vtigercrm510 database from its backup file vtigercrm510bk.sql:
CREATE DATABASE vtigercrm;
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -u root -p vtigercrm < rm510bk.sql

Rename multiple tables in MySQL

DROP TABLE IF EXISTS tbl_test;
CREATE TABLE tbl_test(a int, b int);
DROP TABLE IF EXISTS tbl_test2;
CREATE TABLE tbl_test2(a int, b int);
RENAME TABLE
tbl_test TO tbl_test1,
tbl_test2 TO tbl_test3;

Copy a table in MySQL

Use structure CREATE TABLE new_table LIKE old_table

The following queries copy table vtiger_account in vtigerCRM
First, create an empty table with the same structure
CREATE TABLE vtiger_account_bk LIKE vtiger_account;

Second, copy data
INSERT INTO vtiger_account_bk SELECT * FROM vtiger_account;

Get indexes of a MySQL table

Get all indexes
SHOW INDEXES FROM your_table_name;

Get primary keys only
SHOW INDEXES FROM your_table_name
WHERE key_name = "PRIMARY";

Get indexes of vtiger_account table in vtigerCRM
SHOW INDEXES FROM vtiger_account;

Insert multiple rows into a table

DROP TABLE IF EXISTS tbl_test;
CREATE TABLE tbl_test(a varchar(10), b int);
INSERT INTO tbl_test(a, b)
VALUES
('Name 1', 1),
('Name 2', 2),
('Name 3', 3),
('Name 4', 4);
SELECT * FROM tbl_test;

Cross Table Delete

Delete all accounts with their statuses are inactive and delete related contacts belong to deleted accounts.
Be careful when executing this script on your database.

DELETE account.*, contact.*
FROM account a
INNER JOIN contact c
ON a.account_id = c.account_id
WHERE a.is_active = 0

Drop several tables

Be very careful when executing this script on your database.
You should create test tables such as tbl_test1, tbl_test2, and tbl_test3 and practice.

DROP TABLE tbl_test1, tbl_test2, tbl_test3;

Generate script for MySQL table

The following script gets create table script of vtiger_account table in vtigerCRM.

SHOW CREATE TABLE vtiger_account;

Transaction with MySQL

SET autocommit=0;
START TRANSACTION;
DROP TABLE IF EXISTS tbl_test;
CREATE TABLE tbl_test(a int, b int);
INSERT INTO tbl_test(a,b) VALUES(1,2);
COMMIT;
INSERT INTO tbl_test(a,b) VALUES(3,4);
ROLLBACK;
SELECT * FROM tbl_test;
Return:
a b
1 2

Get all tables/views in a database

The following query returns all tables/views in database vtigercrm510 and numbers of records for each table.

SELECT table_name, table_type, engine, table_rows
FROM information_schema.tables t1
WHERE table_schema = 'vtigercrm510'
ORDER BY table_name DESC;

Get all vtigerCRM quotes

The following query gets vtigerCRM Quote List

SELECT t1.quote_no AS 'Quote No'
, t1.subject AS 'Subject'
, t1.quotestage AS 'Quote Stage'
, t4.potentialname AS 'Potential Name'
, t5.accountname AS 'Account Name'
, t1.total AS 'Total'
, t3.user_name AS 'Assigned To'
FROM vtiger_quotes t1
INNER JOIN vtiger_crmentity t2 ON t1.quoteid = t2.crmid AND t2.setype='Quotes'
INNER JOIN vtiger_users t3 ON t2.smownerid = t3.id
LEFT JOIN vtiger_potential t4 ON t1.potentialid = t4.potentialid
LEFT JOIN vtiger_account t5 ON t1.accountid = t5.accountid