1.Conneting to mysql database

mysql -h example.com -u USERNAME -p # input password in the next line;

2.To clear the screen in MySQL prompt

    system clear;

3. Create & Use & Drop Database

    create database db_name;
    use db_name;
    drop database db_name;

4. Create Tables

    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

An example of creating table:

    CREATE TABLE DEPT (
    DEPTNO INT NOT NULL PRIMARY KEY,
    DNAME CHAR(20),
    LOC CHAR(10)
    );

5. Foreign keys:

Foreign key is useful to keep the data in sync and avoid trash data, but may compromise system performance A discussion on foreign can be viewed here (in Chinese)

to use foreign key:

    CREATE TABLE EMP (
    EMPNO INT NOT NULL PRIMARY KEY,
    ENAME CHAR(10) NOT NULL CHECK(ENAME = UPPER(ENAME)),
    JOB CHAR(10),
    MGR INT,
    HIREDATE DATE,
    SAL DECIMAL(10,2),
    COMM DECIMAL(9.0) DEFAULT NULL,
    DEPTNO INT(4) NOT NULL,
    CONSTRAINT FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
    );

note that the last column DEPTNO is a foreign key refers to the table dept.
From now on the emp and dept are constrainted by the key.
e.g.
1. When you want to add values to emp table, the system will check if the deptno exists in the table dept, if not, you cant add values to emp.
2. When you want to delete certain row from dept table, the system will check if there is any row in dept table that refer to the row you want to delete, if there is, then you CANT delete the row. The system will say like, hey where are these emplyees gonna to work if you delete their working place? You can’t do that. You have to figure out a new place for them to work before you delete it 🙂

This is very useful to remind the DBA that there is a link between these and avoid mis-use.