Creating DB & Rights

Create MySQL DB

mysql> CREATE DATABASE bank_db;

mysql> GRANT ALL ON bank_db.* TO 'account_name'@'host';

Drop MySQL DB

mysql> DROP DATABASE bank_db

NOTE:

Always drop DB using DROP DATABASE. InnoDB DB cannot be removed by deleting the corresponding directory
Do not manually add files to the the Database directory (e.g. c:\mysql\data\app_db). DROP DATABASE will remove all table files but the drop statement will fail:
```
ERROR 1010 (HY000): Error dropping database (can't rmdir '.\test\', errno: 17)
```

Create and Loading Data in MySQL

mysql> CREATE TABLE user_profile (first_name VARCHAR(20), last_name VARCHAR(20),
         sex CHAR(1), answer CHAR(1), dob DATE);

mysql> LOAD DATA INFILE '/mydata/profile.txt' INTO TABLE user_profile
         LINES TERMINATED BY '\r\n'; # For Windows line terminator

profile.txt

Field is separated by a single tab
\N stands for NULL value

\ to escape character

Jonathan	Chan	m	\N	1980-11-11
Jane	Smith	f	bike	1977-10-30

Suspense MySQL Index when Loading Data for MyISAM Table

Disabling Index can speed up the loading process signficantly

Lockdown the table from read & write
Flush the table data
```
mysql> FLUSH TABLES;
```

Disable index

% myisamchk --keys-used=0 -rq /path/to/my_db/my_tbl

Load data with LOAD DATA INFILE

Enable index

mysql> myisamchk -rq /path/to/my_db/my_tbl

Flush the table data
```
mysql> FLUSH TABLES;
```

Turn On or Off Unique & Foreign Constraints in MySQL

Data loading can be speed up by turning unique and foreign constraints off temporarily

mysql> SET unique_checks=0;
mysql> SET foreign_key_checks=0;

... LOAD DATA HERE ...

mysql> SET unique_checks=1;
mysql> SET foreign_key_checks=1;

Turn autocommit off for InnoDB Tables

To speed up data loading for InnoDB, turn off autocommit during data loading

mysql> SET autocommit=0;
... LOAD DATA INFILE ...
mysql> COMMIT;

Show MySQL Table Information

Show information on the tables and tablespace (if any)

mysql> SHOW TABLE STATUS FROM app_db\G

Truncate MySQL Table Data

Dropping table data

mysql> TRUNCATE TABLE tbl;

MySQL Table Creation (Example)

mysql> CREATE TABLE emp_profile (
         emp_id INT(10) UNSIGNED DEFAULT NOT NULL,
         comp_id INT(10) UNSIGNED DEFAULT NOT NULL,
         dept_no INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
         detail CHAR(20) DEFAULT '' NOT NULL,
         salary DOUBLE(6,2) DEFAULT '0.00' NOT NULL,
         PRIMARY KEY(article, dealer));

mysql> CREATE TABLE order_items (
         order_id INT UNSIGNED NOT NULL REFERENCES orders(id),
         item_id CHAR(13) NOT NULL,
         quantity TINYINT UNSIGNED,
         PRIMARY KEY (order_id, item_id)
       ) ENGINE=InnoDB;

mysql> ALTER TABLE order_items
         ADD FOREIGN KEY (order_id) REFERENCES orders(id);

mysql> CREATE TABLE tbl (
         id INT NOT NULL,
         last_name CHAR(20) NOT NULL,
         first_name CHAR(20) NOT NULL,
         PRIMARY KEY (id),
         INDEX name (last_name, first_name)
       );

mysql> INSERT INTO emp_profile VALUES
         (1034, 20, 34,'CEO', 9999.00),(1037, 20, 34,'COO', 8999.00));

# Auto-increment, Enum & Foreign constraint
mysql> CREATE TABLE member (
         id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
         status ENUM('normal', 'vip') NOT NULL,
         dept_no SMALLINT UNSIGNED NOT NULL REFERENCES department(id),
         PRIMARY KEY (id)
       );

mysql> ALTER TABLE member AUTO_INCREMENT = 1000; # Start id from 1000

mysql> INSERT INTO member VALUES
          ('vip', 34),('normal', 35));

Index Prefix

If the first 10 characters of a column is unique already, we can reduce the index size by simply indexing the first 10 characters of a column.

CREATE INDEX index_name ON tbl (col(10));

MySQL Data Type

MySQL Numeric Type

Name	Size (Byte)
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT	4
BIGINT	8
FLOAT(n)	4 if p <= 24, 8 if p>=24
FLOAT	4
DOUBLE	8
REAL	8
DECIMAL(M,D)	vary
NUMERIC(M,D)	vary
BIT(n)	celling(n/8)

MySQL Time Type

Name	Format
DATETIME	'nnnn-nn-nn nn:nn:nn'
DATE	'nnnn-nn-nn'
TIMESTAMP	'nnnn-nn-nn nn:nn:nn'
TIME	'nn:nn:nn'
YEAR	nnnn

MySQL String

Name	Description
CHAR(n)	Char string with a fixed length n
VARCHAR(n)	Variable string up to length n
BLOB	Binary Large Object
TEXT	Large Text
ENUM	Enumerator String

Boolean

Boolean: TRUE | FALSE
Boolean is actually implemented as TINYINT

MySQL Create Tables, Loading Data & Data Type