MySQL Create Tables, Loading Data & Data Type

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