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.
MySQL DDLDDL 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
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