SQL Syntax

  1. Create Table 
    
CREATE TABLE table1 (   
    id INT PRIMARY KEY,
    name VARCHAR(20),
);
    
    
CREATE TABLE table1 (   
    id INT,
    name VARCHAR(20),
    PRIMARY KEY(id),
);

    Other Constraints: NOT NULL,UNIQUE,AUTO_INCREMENT, DEFAULT 'sth'.

  2. Access column format

    3. DESCRIBE table1;

  3. Delete table

    4. DROP TABLE table1;

  4. Add column

    5. ALTER TABLE table1 ADD discount DECIMAL(3,2);

    3 digits, 2 decimal places.

  5. Drop column

    6. ALTER TABLE table1 DROP COLUMN discount;

  6. Insert data
    7. 
INSERT INTO table1 VALUES(1,'Jack');
INSERT INTO table1(id) VALUES(2);
INSERT INTO table1(name) VALUES('Leo'); #AUTO_INCREMENT

    Notes: Cannot insert duplicate values.

  7. Update rows
    8. 
UPDATE table1;
SET name = 'Kim' # SET VAL1 = val1, VAL2 = val2, ...
WHERE id = 2; # OR, AND
  8. Delete rows
    9. 
DELETE FROM table1
WHERE id=1 AND name='Leo';
  9. Queries
    1. Basic
      2. 
SELECT * FROM table1;
SELECT id,name FROM table1;
SELECT table1.id,table1.name FROM table1;
    2. ARRANGE IN ORDER
      3. 
ORDER BY name;
ORDER BY id,name ASC; # order by id, then by name
ORDER BY name DESC;
    3. Limit the number of rows
      4. 
LIMIT 10;
    4. Select columns with specific values 
      
WHERE id > 0  AND name = 'Jack';
WHERE id > 0  AND name <> 'Jack';
WHERE id > 0  AND name IN ('Jack','Leo');

    References