Common Commands

- SELECT - extracts data from a database
- UPDATE - updates data in a database
- DELETE - deletes data from a database
- INSERT INTO - inserts new data into a database
- CREATE DATABASE - creates a new database
- ALTER DATABASE - modifies a database
- CREATE TABLE - creates a new table
- ALTER TABLE - modifies a table
- DROP TABLE - d
  eletes a table
- CREATE INDEX - creates an index (search key)
- DROP INDEX - deletes an index



Select

    SELECT * FROM tablename;
    SELECT colum_name, colum_name FROM tablename;
    SELECT DISTINCT City FROM Customers;



Where

    SELECT column_name,column_name
    FROM table_name
    WHERE column_name operator value;



And

    SELECT * FROM Customers
    WHERE Country='Germany'
    AND City='Berlin';



Order by

    SELECT column_name, column_name
    FROM table_name
    ORDER BY column_name ASC|DESC, column_name ASC|DESC;



Insert into

The first form does not specify the column names where the data will be inserted, only their values:

    INSERT INTO table_name
    VALUES (value1,value2,value3,...);

The second form specifies both the column names and the values to be inserted:

    INSERT INTO table_name (column1,column2,column3,...)
    VALUES (value1,value2,value3,...);



Update

    UPDATE Customers
    SET City='Hamburg'
    WHERE CustomerID=1;

    --multiple colums
    UPDATE Customers
    SET City='Hamburg',Country='China'
    WHERE CustomerID=1;

    --multiple records
    UPDATE Customers
    SET City='Beijing'
    WHERE Country='China';



Delete

    DELETE FROM Customers
    WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';



Sql Injection

The code on the server :

    txtUserId = getRequestString("UserId");
    txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

if your input is

    '' or ''=''

will return the whole table...



Like

    SELECT * FROM Customers
    WHERE Country LIKE '%land%';

the pattern after LIKE is called Wildcards.

You can also write NOT LIKE

    SELECT * FROM Customers
    WHERE Country NOT LIKE '%land%';



Wildcards

  Wildcard                	Description
  %                       	A substitute for zero or more characters
  _                       	A substitute for a single character
  [charlist]              	Sets and ranges of characters to match
  [^charlist]or[!charlist]	Matches only a character NOT specified within the brackets

Examples:

    SELECT * FROM Customers
    WHERE City LIKE '[bsp]%';

    SELECT * FROM Customers
    WHERE City LIKE '[a-z]%';

    SELECT * FROM Customers
    WHERE City LIKE '_erli_';

    --note that sql is not case sensitive, the b% will lead to all results begin with b or B.
    SELECT TOP 3 * FROM Customers
    WHERE City LIKE 'b%';



IN

    SELECT * FROM Customers
    WHERE City IN ('Paris','London');

    --note that the code above is just the same as:
    SELECT * FROM Customers
    WHERE City ='Paris' or City='London';



Between

    SELECT * FROM Products
    WHERE ProductName BETWEEN 'C' AND 'M';

    SELECT * FROM Products
    WHERE ProductID BETWEEN 1 AND 10;



Alias

    SELECT o.OrderID, o.OrderDate, c.CustomerName
    FROM Customers AS c, Orders AS o
    WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;



MYSQL

    #run the .sql script from command line.
    mysql -u root -p RUNOOB < new.sql

    login in the myql:
    mysql -u root -p
    pwd 123456