Mysql

Syntax

Data Manipulation

Basic

• Select

SELECT columna, columnb FROM mytable;
SELECT columna as alias1, columnb as alias2 FROM mytable;

Special

• Dummy Table

SELECT 1+1;
SELECT 1+1 FROM DUAL;

• Concatenate

SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable;

Joint

SELECT m.MenuName, o.MenuOption FROM `menuoption` o left outer join `menu` m   on o.MenuId=m.id;

Data Definition

Basic

Create Database

CREATE DATABASE mydatabase;
CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

Create User

GRANT ALL PRIVILEGES ON mydatabase.* TO myuser@localhost IDENTIFIED BY 'myuser_password';

Special Case

TimeStampe

CREATE TABLE test_table( 
  Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  CreatedTime TIMESTAMP DEFAULT '0000-00-00 00:00:00', 
  UpdatedTime TIMESTAMP DEFAULT NOW() ON UPDATE NOW() 
);

Maintenance

Binary File

• Binary file are usefull to recuperate a database in any point of time
• There are usefull for replication
• But as all informationare written into the logfile before updating the database, you may expect an impact on the perfomance

Setup

log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 250M
binlog_do_db            = include_database_name
binlog_format           = 'MIXED'

The binary logs in mysql-bin.index is expected to be numerically consecutive. If the binary logs are not numerically consecutive, log rotation is disabled.