MySQL 2/6

//usual MySQL structure [command/verb][subject][adjective]

C:\Users\user>mysql -h localhost -u root -p

//password is "root"

assumption: everything below prefixed by >mysql

SHOW DATABASES;

CREATE DATABASE pubs; //pubs is database name

//DDL   - data definition, define structure
    - CREATE

DROP DATATABASE pubs; //to delete

---------DML----------

//USE
//CRUD - CREATE RETRIEVE UPDATE DELETE (C = insert, R = select
//INSERT

USE pubs2;

SHOW TABLES;

CREATE TABLE publisher(
    pub_id  INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,
    name    VARCHAR(255) NOT NULL,
    city    VARCHAR(255) NOT NULL DEFAULT 'Quezon City',
    state   VARCHAR(255) NOT NULL DEFAULT 'Manila',
    country VARCHAR(255) NOT NULL DEFAULT 'Philippines',
    logo    VARCHAR(255),
    pr_info TEXT
);

CREATE TABLE title(
    title_id    INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    author_name VARCHAR(255),
    author_address  VARCHAR(255),
    author_contract VARCHAR(255),
    price       FLOAT(2) NOT NULL,
    royalty     FLOAT(2),
    notes       TEXT,
    pubdate     DATE NOT NULL,
    pub_id      INT NOT NULL
);

);

DESC table_name; //shows data dictionary of table

//SQL is NOT case sensitive and ignores new line and indentations

INSERT INTO publisher(name, city, state)
    VALUES('Phoenix Publishing House', 'Boston', 'Massachusetts');

SELECT * FROM publisher;

SELECT name, city FROM publisher;