Summary of SQL/MySQL commands

1. Create a table:
CREATE [TEMPORARY] TABLE [table name] ( [column definitions] ) [table parameters].

CREATE TABLE `users` ( `id` INT UNSIGNED NOT NULL 
AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 20 ) NOT NULL ) ENGINE = MYISAM;

Data Type Abbreviations

The following abbreviations are accepted for data type definitions:

Abbreviation	Character
CHAR(n)	CHARACTER(n)
CHAR	CHARACTER
CHAR VARYING(n)	CHARACTER VARYING(n)
VARCHAR(n)	CHARACTER VARYING(n)
VARBINARY(n)	BINARY VARYING(n)
INT(p)	INTEGER(p)
INT	INTEGER
DEC(p, s)	DECIMAL(p, s)
DEC	DECIMAL
NUM(p, s)	NUMERIC(p, s)
NUM	NUMERIC

2. Add a new field:


ALTER TABLE users ADD dateofbirth  DATE  NULL AFTER username;
3. insert into table one row or multi-rows

INSERT INTO table (column1, [column2, ... ]) VALUES (value1, [value2, ...])

INSERT INTO users(username,dateofbirth) VALUES ('John Doe','1996-12-29' ), ('Jiansen Lu','1985-12-29'
 );

4. Select values from tables:
SELECT * from users;
results:

id	username	dataofbirth
1	John Doe	1996-12-29
2	Jiansen Lu	1985-12-29

5. group two select  via inner join:
Suppose we have following table:

username	income_type	income
John Doe	A	2000.0
John Doe	B	2050.5
Jiansen Lu	A	300.0
Jiansen Lu	B	400.0

We need to produce following output:
username, income from type A, income from type B

CREATE TABLE income
(
   username VARCHAR(20) NOT  NULL,
   income_type CHAR(1) NOT NULL,

income DECIMAL(18,2)NOT NULL

) ENGINE  =  MYISAM;

INSERT INTO income(username,income_type,income) VALUES 

 ('John Doe','A','2000.0' ),

 ('John Doe','B','2050.5' ),

 ('Jiansen Lu','A', '300.0'),

 ('Jiansen Lu','B', '400.0');

select a.username, a.income as incomeA, b.income
 as incomeB from
(
    select * from income where income_type ='A'

) a
inner join
(select * from income where income_type='B'
) b
on a.username = b.username;

username	incomeA	incomeB
John Doe	2000.00	2050.50
Jiansen Lu	300.00	400.00

6. alter the length of varchar:
alter table income modify username VARCHAR(50);