MySQL

Overview
- CLI
- SELECT
- INSERT/UPDATE
- SHOW
- DATABASE
- TABLE
- TYPES
- KEY
- VIEW
- GRANT/REVOKE
Reference
- DROP TABLE
Topics
How-to
Links

Overview

https://en.wikibooks.org/wiki/MySQL/CheatSheet

CLI

mysql -h <host> -u <user> -p<passwd>
mysql -h <host> -u <user> -p
Enter password: ********
mysql -h <host> -u <user> -p <Database>

mysql -u <user> -p
mysql -u <user> -p -e "select * from schema.table"

# load data
mysql database < filename-20120201.sql
cat filename-20120201.sql | mysql database

# repair tables
mysqlcheck --all-databases
mysqlcheck --all-databases --fast

http://stackoverflow.com/questions/1602904/how-do-you-run-a-single-query-through-mysql-from-the-command-line

SELECT

SELECT * FROM table
SELECT * FROM table1, table2, ...
SELECT field1, field2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUP BY field
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...
SELECT ... FROM t1 JOIN t2 USING(id) WHERE condition

field1 = value1
field1 <> value1
field1 LIKE 'value _ %'
field1 IS NULL
field1 IS NOT NULL
field1 IN (value1, value2)
field1 NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2

INSERT/UPDATE

INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...)
INSERT table1 SET field1=value_1, field2=value_2 ...

LOAD DATA INFILE '/tmp/mydata.txt' INTO TABLE table1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

DELETE FROM table1 / TRUNCATE table1
DELETE FROM table1 WHERE condition
-- join:
DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition

UPDATE table1 SET field1=new_value1 WHERE condition
-- join:
UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ...
WHERE table1.id1 = table2.id2 AND condition

SHOW

SHOW DATABASES
SHOW TABLES
SHOW FIELDS FROM table / SHOW COLUMNS FROM table / DESCRIBE table / DESC table / EXPLAIN table
SHOW CREATE TABLE table
SHOW CREATE TRIGGER trigger
SHOW TRIGGERS LIKE '%update%'
SHOW PROCESSLIST
KILL process_number
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '**yourdbname**';
$ mysqlshow
$ mysqlshow database

DATABASE

CREATE DATABASE [IF NOT EXISTS] mabase [CHARACTER SET charset] [COLLATE collation]
CREATE DATABASE mabase CHARACTER SET utf8
DROP DATABASE mabase
USE mabase

ALTER DATABASE mabase CHARACTER SET utf8

TABLE

CREATE TABLE table (field1 type1, field2 type2, ...)
CREATE TABLE table (field1 type1 unsigned not null auto_increment, field2 type2, ...)
CREATE TABLE table (field1 type1, field2 type2, ..., INDEX (field))
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1))
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1, field2))
CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
  FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA)
    [ON UPDATE] [CASCADE|SET NULL|RESTRICT]
    [ON DELETE] [CASCADE|SET NULL|RESTRICT])
CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
  FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))
CREATE TABLE table IF NOT EXISTS (...)

CREATE TABLE new_tbl_name LIKE tbl_name
  [SELECT ... FROM tbl_name ...]

CREATE TEMPORARY TABLE table (...)

CREATE table new_table_name as SELECT [ *|column1, column2 ] FROM table_name

DROP TABLE table
DROP TABLE IF EXISTS table
DROP TABLE table1, table2, ...
DROP TEMPORARY TABLE table

ALTER TABLE table MODIFY field1 type1
ALTER TABLE table MODIFY field1 type1 NOT NULL ...
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
ALTER TABLE table ALTER field1 SET DEFAULT ...
ALTER TABLE table ALTER field1 DROP DEFAULT
ALTER TABLE table ADD new_name_field1 type1
ALTER TABLE table ADD new_name_field1 type1 FIRST
ALTER TABLE table ADD new_name_field1 type1 AFTER another_field
ALTER TABLE table DROP field1
ALTER TABLE table ADD INDEX (field);
ALTER TABLE table ADD PRIMARY KEY (field);

-- Change field order:
ALTER TABLE table MODIFY field1 type1 FIRST
ALTER TABLE table MODIFY field1 type1 AFTER another_field
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER another_field

ALTER TABLE old_name RENAME new_name;

TYPES

TINYINT   (1o: -127+128)
SMALLINT  (2o: +-65 000)
MEDIUMINT (3o: +-16 000 000)
INT       (4o: +-2 000 000 000)
BIGINT    (8o: +-9.10^18)
Precise interval: -(2^(8*N-1)) -> (2^8*N)-1
/!\ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max"

INT NOT NULL auto_increment PRIMARY KEY -- auto-counter for PK

FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53)
/!\ 8,3 -> 12345,678 -- NOT 12345678,123!

TIME (HH:MM) YEAR (AAAA) DATE (AAAA-MM-JJ) DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)

VARCHAR (single-line; explicit size)
TEXT (multi-lines; max size=65535)
BLOB (binary; max size=65535)
Variants for TEXT&BLOB: TINY (max=255) MEDIUM (max=~16000) LONG (max=4Go)
Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT

ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)

KEY

CREATE TABLE table (..., PRIMARY KEY (field1, field2))
CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2 (t2_field1, t2_field2))
ALTER TABLE table ADD PRIMARY KEY (field);
ALTER TABLE table ADD CONSTRAINT constraint_name PRIMARY KEY (field, field2);

VIEW

CREATE VIEW view AS SELECT ... FROM table WHERE ...

GRANT/REVOKE

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions

SET PASSWORD = PASSWORD('new_pass')
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass')
SET PASSWORD = OLD_PASSWORD('new_pass')

DROP USER 'user'@'host'

Reference

DROP TABLE

RESTRICT and CASCADE are permitted to make porting easier. In MySQL 5.6, they do nothing.

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

https://dev.mysql.com/doc/refman/5.6/en/drop-table.html

Topics

Database? Schema?

CREATE SCHEMA is a synonym for CREATE DATABASE
Most other database solutions make a distinction. Usually a schema is a collection of tables and a database is a collection of schemas.

http://stackoverflow.com/questions/11618277/difference-between-schema-database-in-mysql

DATETIME vs TIMESTAMP

`TIMESTAMP`	`DATETIME`
4 bytes	8 bytes
000-01-01 00:00:00 TO 9999-12-31 23:59:59	1970-01-01 00:00:01 UTC TO 2038-01-19 03:14:07 UTC
A specific point in time	A specific date and time in calender
Mostly for logging	Mostly for domain data like reservation, reminder etc.

http://stackoverflow.com/questions/409286/should-i-use-field-datetime-or-timestamp

Foreign key constraint

Dropping a table whose column is referenced as a foreign key by another table can fail
SET FOREIGN_KEY_CHECKS = 0; to ignore the constraint
It's not recommended for a general use

-- Specify to check referential constraints
SET FOREIGN_KEY_CHECKS = 1;

-- Create a parent table
CREATE TABLE states
(
  abbr CHAR(2) PRIMARY KEY,
  name VARCHAR(90)
) ENGINE = InnoDB;

CREATE TABLE cities
(
  name VARCHAR(90),
  state CHAR(2),
  FOREIGN KEY (state) REFERENCES states(abbr)
) ENGINE = InnoDB;

-- Try to insert a row to child table (corresponding rows does not exist in the parent table)
INSERT INTO cities VALUES ('Boston', 'MA');
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

-- Do not check referential constraints
SET FOREIGN_KEY_CHECKS = 0;

-- Now we can insert row
INSERT INTO cities VALUES ('Boston', 'MA');
-- Query OK, 1 row affected (0.03 sec)

Table of Contents

Overview

CLI

SELECT

INSERT/UPDATE

SHOW

DATABASE

TABLE

TYPES

KEY

VIEW

GRANT/REVOKE

Reference

DROP TABLE

Topics

Database? Schema?

DATETIME vs TIMESTAMP

Foreign key constraint

How-to

Links