MRECW Students E- Learning Point Computer Security Student (CSS)

MySQL by Examples

-- Database-Level

DROP DATABASE databaseName                 -- Delete the database (irrecoverable!)

DROP DATABASE IF EXISTS databaseName       -- Delete if it exists

CREATE DATABASE databaseName               -- Create a new database

CREATE DATABASE IF NOT EXISTS databaseName -- Create only if it does not exists

SHOW DATABASES                             -- Show all the databases in this server

USE databaseName                           -- Set the default (current) database

SELECT DATABASE()                          -- Show the default database

SHOW CREATE DATABASE databaseName          -- Show the CREATE DATABASE statement

-- Table-Level

DROP TABLE [IF EXISTS] tableName, ...

CREATE TABLE [IF NOT EXISTS] tableName (

   columnName columnType columnAttribute, ...

   PRIMARY KEY(columnName),

   FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae)

)

SHOW TABLES                -- Show all the tables in the default database

DESCRIBE|DESC tableName    -- Describe the details for a table

ALTER TABLE tableName ...  -- Modify a table, e.g., ADD COLUMN and DROP COLUMN

ALTER TABLE tableName ADD columnDefinition

ALTER TABLE tableName DROP columnName

ALTER TABLE tableName ADD FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae)

ALTER TABLE tableName DROP FOREIGN KEY constraintName

SHOW CREATE TABLE tableName        -- Show the CREATE TABLE statement for this tableName

-- Row-Level

INSERT INTO tableName

   VALUES (column1Value, column2Value,...)               -- Insert on all Columns

INSERT INTO tableName

   VALUES (column1Value, column2Value,...), ...          -- Insert multiple rows

INSERT INTO tableName (column1Name, ..., columnNName)

   VALUES (column1Value, ..., columnNValue)              -- Insert on selected Columns

DELETE FROM tableName WHERE criteria

UPDATE tableName SET columnName = expr, ... WHERE criteria

SELECT * | column1Name AS alias1, ..., columnNName AS aliasN

   FROM tableName

   WHERE criteria

   GROUP BY columnName

   ORDER BY columnName ASC|DESC, ...

   HAVING groupConstraints

   LIMIT count | offset count

-- Others