Skip to content

SQL

Databases

Create a database

SQL
CREATE [OR REPLACE] DATABASE <base>;

DATABASE is an alias of SCHEMA

Delete a database

SQL
DROP DATABASE <base>; 

List all databases in a sql engine

SQL
SHOW DATABASES;

Table

Use a table

SQL
USE <table>;

Create a table

SQL
CREATE TABLE <tablename>(
    <column> <type> <extra>,
    <column> <type> <extra>,
    <column> <type> <extra>
);

Users

Create a user

SQL
CREATE USER '<user>'@'localhost' IDENTIFIED BY '<password>';

Delete a user

SQL
DROP USER '<user>'@localhost;

Give privileges on table

SQL
GRANT ALL PRIVILEGES ON dvwa.* TO 'dvwa'@'localhost';

Give privileges on all tables

SQL
GRANT ALL PRIVILEGES ON * . * TO '<user>'@'localhost';

Arguments

Quick list of daily use arguments

SQL
     SELECT -- Select data from database
         AS -- Rename column with alias
       FROM -- Specify table we are pulling from
      WHERE -- Filter query with matching condition
       JOIN -- Combine rows from 2 or more tables
        AND -- Combine conditions in a query. All must be met.
         OR -- Combine conditions in a query. One must be met.
       LIKE -- Search for patterns in a column
         IN -- Specify severales values when using WHERE
    IS NULL -- Return only rows with NULL value
      LIMIT -- Limit the number of rows returned
       CASE -- Return value on a specified condition

     CREATE -- Create TABLE,DATABASE,INDEX,View
       DROP -- Drop TABLE,DATABASE,INDEX,View
     UPDATE -- Update table data
     DELETE -- Delete rows from a table
ALTER TABLE -- ADD/Remove columns from table

   GROUP BY -- Group rows that have same values into summary rows
   ORDER BY -- Set order of a result. ASC by default, DESC for reverse
     HAVING -- Same as WHERE but used for aggregate functions
        SUM -- Return sum of a column
        AVG -- Return average of column
        MIN -- Return min of column
        MAX -- Return max of column
      COUNT -- Return the number of rows

Execution order of arguments

Markdown
 | FROM
 |
 | WHERE
 |
 | GROUP BY
 |
 | HAVING
 |
 | SELECT
 |
 | ORDER BY
 |
 | LIMIT
\*/

Source