- DELETE is a DML Command.
- DELETE statement is executed using a row lock, each row in the table is locked for deletion.
- We can specify filters in where clause
- It deletes specified data if where condition exists.
- Delete activates a trigger because the operation are logged individually.
- Slower than truncate because, it keeps logs.
- Rollback is possible.
- TRUNCATE is a DDL command.
- TRUNCATE TABLE always locks the table and page but not each row.
- Cannot use Where Condition.
- It Removes all the data.
- TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
- Faster in performance wise, because it doesn’t keep any logs.
- Rollback is not possible.
Primary key uniquely identify a record in the table.
Primary Key can’t accept null values.
By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
We can have only one Primary key in a table.
Foreign key is a field in the table that is primary key in another table.
Foreign key can accept multiple null value.
Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.
We can have more than one foreign key in a table.
MySQL JOINS are used to retrieve data from multiple tables. A MySQL JOIN is performed whenever two or more tables are joined in a SQL statement.
Three types of joines:
1. Inner join
2. Left join
3. Right join
If you’ve already written a statement that uses a MySQL INNER JOIN. It is the most common type of join. MySQL INNER JOINS return all rows from multiple tables where the join condition is met.
The syntax for the MySQL INNER JOIN is:
INNER JOIN table2
ON table1.column = table2.column;
MySQL is a relational database management system (or RDBMS) –meaning that it is a database management system based on the relational model. This RDMS runs as its own server and provides multi-user access to multiple databases at once. The source code of MySQL is available under the terms set forth in the GNU General Public License as well as a plethora of proprietary agreements. Members of the MySQL community have created many different branches of the RDMS –the most popular of which are Drizzle and MariaDB. As well as being the prototype of several branches, most free software projects that must have a full featured database management system (or DMS) use MySQL.
MySQLi Extension (or simply known as MySQL Improved or MySQLi) is a relational database driver that is used mainly in the PHP programming language. It provides an interface to the already founded MySQL databases. It is quite literally an improved version of its predecessor, MySQL, which was simply a means to manage databases over servers.