MYSQL Cheatsheet

MySQL is the de-facto standard database system for web sites include Php code examples using php pdo and mysqli

Overview

PHP has two main options for accessing MYSQL

  • MySQLi extension (the "i" stands for improved) object-oriented or procedural
  • PDO (PHP Data Objects)
  • PDO will work on 12 different database systems, MySQLi will only work with MySQL databases.
  • Both are object-oriented, but MySQLi also offers a procedural API.
  • Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.

Security hints

MySQL DDL

DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.
  • CREATE - to create a database and its objects like (table, index, views, store procedure, function, and triggers)
  • ALTER - alters the structure of the existing database
  • DROP - delete objects from the database
  • TRUNCATE - remove all records from a table, including all spaces
  • allocated for the records are removed
  • COMMENT - add comments to the data dictionary
  • RENAME - rename an object
References:-

MySQL DML

Common DML commands

  • SELECT – retrieve data from the a database
  • INSERT – insert data into a table
  • UPDATE – updates existing data within a table
  • DELETE – deletes all records from a table, the space for the records remain
  • MERGE – UPSERT operation (insert or update)
  • CALL – call a PL/SQL or Java subprogram
  • LOCK TABLE – control concurrency
References :-

Small Business websites, generated by Hyperaccess ltd