Примеры запросов в MySQL 5.x

-- /* Описание таблицы */ --------------------------------------
DESCRIBE `crm_profiles` ;
SHOW FULL COLUMNS FROM `crm_profiles`;
SHOW CREATE TABLE `crm_persons`;
-- /*Создание таблицы с foreign key:*/--------------------------
CREATE TABLE tbl_post
(
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR(128) NOT NULL,
        content TEXT NOT NULL,
        tags TEXT,
        status INT NOT NULL,
        create_time INT,
        update_time INT,
        author_id INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE tbl_comment (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        content TEXT NOT NULL,
        status INT NOT NULL,
        create_time INT,
        author VARCHAR(128) NOT NULL,
        email VARCHAR(128) NOT NULL,
        url VARCHAR(128),
        post_id INT NOT NULL,
        CONSTRAINT FK_comment_post FOREIGN KEY (post_id) REFERENCES tbl_post (id) ON DELETE CASCADE/*[...|SET NULL|NO ACTION|RESTRICT]*/ ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------Создание перечисления в таблице-------------------------
CREATE TABLE sizes (name ENUM('small', 'medium', 'large'),id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
-- Переименование типа и описания столбца:-------------------------
ALTER TABLE users MODIFY COLUMN nick varchar(25) NOT NULL COMMENT 'логин' /*AFTER column_before*/;
-- Переименование имени столбца:-----------------------------------
ALTER TABLE companies CHANGE name c_name varchar(32) NOT NULL;
-- ДОбавелние ограничения и обозначение внешнего ключа:-------------
ALTER TABLE User ADD CONSTRAINT userProperties FOREIGN KEY(this_tbl_id) REFERENCES ref_tbl(ref_tbl_main_ID);
-- Переименование таблицы:------------------------------------------
ALTER TABLE accounts RENAME TO acc;
-- ---Вставка записи:-----------------------------------------------
INSERT INTO `companies` (id, name, descript) VALUES (NULL, 'i am', 'own records');
--  ---Удаление записей из таблицы----------------------------------
truncate table ref_codes;
-- ------Удаление колонки из таблицы:-------------------------------
ALTER TABLE ref_codes DROP COLUMN referrer_id;
-- ---------Заппросы с limit[offset],[quantity]---------------------
select id, name from articles limit 1,2;
-- ---MySQL, найти в базе все таблицы с движком не InnoDB
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='my_db_name'
AND TABLE_TYPE='BASE TABLE'
AND ENGINE NOT IN ('InnoDB');
-- --------Запрос по временному промежутку:-------------------------
select * FROM table BETWEEN `lowerdate` AND `upperdate`;
SELECT * FROM news
  WHERE created_at BETWEEN '2008-08-14 00:00:00' AND '2008-08-23 23:59:59';
  SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate;
FROM Orders;

select dataready.equipage_id
from (select trips.equipage_id, trips.start_date, date_format(
                        date_add(trips.assigned_date,INTERVAL (@my + 2 * (
                        select regions.duration
                        from regions where regions.id = trips.region_id)) HOUR),'%Y-%m-%d') as finish_date
                        from trips) as dataready where '2015-10-23'
                        BETWEEN dataready.start_date and dataready.finish_date;
-- ---------Подзапросы:---------------------------------------------
select id, name, (SELECT count(ref_code) FROM ref_codes WHERE refer = id) qty_sess FROM companies;
-- --------SELECT @ ------------------------------------------------
SET @s:='%slovo%';
SELECT * FROM table
WHERE fam LIKE @s
OR sour LIKE @s
OR dolj LIKE @s
OR otdel LIKE @s
OR  cab LIKE @s;
-- -----------join запрос:------------------------------------------
/*
    INNER JOIN: Returns all rows when there is at least one match in BOTH tables
    LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
    RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
    FULL JOIN: Return all rows when there is a match in ONE of the tables
*/
SELECT id, name, count(ref_code) qty_sess
FROM users JOIN ref_codes ON id = referal_id
WHERE id = 123 GROUP BY id ORDER BY id;
-- ---- запрос замены подстроки:---------------------
UPDATE table_one SET field_one = REPLACE(field_one, 'что_заменить', 'чем_заменить');
UPDATE uploads SET path = REPLACE(path, '/var/www/', '/var/home/site/htdocs/');
INSERT INTO `start`.`page` (
`id` ,
`title` ,
`text` ,
`category_id`
)
VALUES (
NULL , 'Третья страница', '...уже о чемто...что более интересное чем просто ниочем:)', ''
);

-- -----Вывод в файл:------
[select...] | tee /var/Sites/hiker/all_tables.txt;
-- ----------Hierarhical table:---------
CREATE TABLE category(
id BIGINT( 21 ) NOT NULL PRIMARY KEY ,
parent_id BIGINT( 21 ) REFERENCES category.id,
name VARCHAR( 100 ) NOT NULL
)
-- --------------------------------------