Create a Index in MySql

ALTER TABLE tablename ADD id INT PRIMARY KEY AUTO_INCREMENT

Creating a BLOB table

/*
mysql> Drop table Inventory;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE Inventory
    -> (
    ->    ID SMALLINT UNSIGNED,
    ->    Name VARCHAR(40),
    ->    Photo BLOB,
    ->    Quantity INT UNSIGNED
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> Describe Inventory;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| ID       | smallint(5) unsigned | YES  |     | NULL    |       |
| Name     | varchar(40)          | YES  |     | NULL    |       |
| Photo    | blob                 | YES  |     | NULL    |       |
| Quantity | int(10) unsigned     | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)


*/

Drop table Inventory;

CREATE TABLE Inventory
(
   ID SMALLINT UNSIGNED,
   Name VARCHAR(40),
   Photo BLOB,
   Quantity INT UNSIGNED
);

Describe Inventory;

Create a Calendar Table

DROP TABLE IF EXISTS time_dimension;
CREATE TABLE time_dimension (
        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
        db_date                 DATE NOT NULL,
        year                    INTEGER NOT NULL,
        month                   INTEGER NOT NULL, -- 1 to 12
        day                     INTEGER NOT NULL, -- 1 to 31
        quarter                 INTEGER NOT NULL, -- 1 to 4
        week                    INTEGER NOT NULL, -- 1 to 52/53
        day_name                VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
        month_name              VARCHAR(9) NOT NULL, -- 'January', 'February'...
        holiday_flag            CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
        weekend_flag            CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
        event                   VARCHAR(50),
        UNIQUE td_ymd_idx (year,month,day),
        UNIQUE td_dbdate_idx (db_date)

) Engine=MyISAM;

DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate < stopdate DO
        INSERT INTO time_dimension VALUES (
                        YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
                        currentdate,
                        YEAR(currentdate),
                        MONTH(currentdate),
                        DAY(currentdate),
                        QUARTER(currentdate),
                        WEEKOFYEAR(currentdate),
                        DATE_FORMAT(currentdate,'%W'),
                        DATE_FORMAT(currentdate,'%M'),
                        'f',
                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
                        NULL);
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE time_dimension;

CALL fill_date_dimension('1-01-01','2015-01-01');
OPTIMIZE TABLE time_dimension;

Create a simply table with primary key and auto_increment

create table pizzatypes ( typeID int primary key auto_increment, type_name varchar(50));

MySql Search and Replace

MySQL REPLACE string function example

For example, if you want to correct the spelling mistake in the products table in the sample database, you use the REPLACE function as follows:
   
UPDATE products
SET
    productDescription = REPLACE(productDescription,
        'abuot',
        'about');

The query finds all occurrences of a spelling mistake abuot and replaces it by the correct word about in the productDescription column of the products table.

It is very important to note that in the REPLACE function, the first parameter is the column name without quotes (“). If you put the quotes to the field name like “field_name”, the query will update the content of that column to “field_name”, which is causing unexpected data loss.

Join to Tables with From and Where

SELECT dt_tda_master_2.id,dt_accomodation_master.business_name,dt_accomodation_master.accomodation_reference FROM `dt_accomodation_master`,`dt_tda_master_2`
WHERE dt_accomodation_master.accomodation_id = dt_tda_master_2.accommodation_id