mySQL

creating tables

simple example

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), 
    species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

if not exists

CREATE TABLE IF NOT EXISTS pet (name VARCHAR(20), owner VARCHAR(20), 
    species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

copy table schema

CREATE TABLE new_tbl LIKE orig_tbl;

with primary key auto increment and not null

CREATE TABLE t1 (
    c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c2 VARCHAR(100),
    c3 VARCHAR(100) )

with table engine

CREATE TABLE t1 (
    c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c2 VARCHAR(100),
    c3 VARCHAR(100) )
ENGINE=InnoDB

FOREIGN KEY

CREATE TABLE ORDERS (
   ID INT NOT NULL,
   DATE DATETIME, 
   CUSTOMER_ID INT references CUSTOMERS(ID),
   AMOUNT double,
   PRIMARY KEY (ID)
);

altering table

adding foreign key

ALTER TABLE ORDERS 
   ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);

changing column name

ALTER TABLE t1 CHANGE col1 newname BIGINT NOT NULL;

remove column

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

create new column

ALTER TABLE mytable ADD COLUMN dummy1 VARCHAR(40) AFTER id;

insert rows

adding row

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

adding multi rows

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

drop table

droping table

DROP TABLE IF EXISTS table_name;

remove row

delete row example

DELETE FROM somelog WHERE user = 'dan'
  ORDER BY timestamp_column LIMIT 1;

select

simplest select

SELECT * FROM tutorials_tbl

specific columns

SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
  FROM tutorials_tbl

where is

SELECT * FROM tutorials_tbl WHERE tutorial_author = 'amit';

order by and limit

SELECT * FROM somelog WHERE user = 'dan'
  ORDER BY timestamp_column LIMIT 1;

inner join

SELECT employees.id, employees.department_id, employees.name, departments.department
FROM employees
INNER JOIN departments ON employees.department_id = departments.id