Introduction to MySQL
MySQL is one of the best Open Source Relational SQL Database Management Systems. MySQL is an RDBMS used for developing various web-based software applications. MySQL is developed by MySQL AB, which is a Swedish company. MySQL Community Edition is free for download and it is the world’s most popular open-source database. Anyone can get it under the GPL license and it supports a huge and active community of open source developers.
CASCADE CONSTRAINT
CASCADE helps us in the update and deletion of data in multiple tables at the same time. It is related to foreign keys. If the rows from the parent table are deleted or updated, it will automatically delete or update the rows from the child table. A foreign key with a cascade can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
For Example: If there are 2 tables one parent and one child. So whenever we make changes in the parent table, the same changes will happen in the child table. It will save time and effort. We can use ON DELETE CASCADE and ON UPDATE CASCADE clause at the end of the foreign key constraint definition.
-
ON DELETE CASCADE with CREATE TABLE statement: CREATE TABLE statement is used to create a new table. A foreign key with ON DELETE CASCADE means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.
Syntax:
CREATE TABLE table_name(
column_name1 INT PRIMARY KEY AUTO_INCREMENT,
column_name2 VARCHAR(255) NOT NULL,
column_name3 INT NOT NULL,
FOREIGN KEY (column_name1)
REFERENCES parent_table (column_name1)
ON DELETE CASCADE
); -
ON UPDATE CASCADE with CREATE TABLE statement: A foreign key with ON UPDATE CASCADE means that if a record in the parent table is updated, then the corresponding records in the child table will automatically be updated.
Syntax:
CREATE TABLE table_name(
column_name1 INT PRIMARY KEY AUTO_INCREMENT,
column_name2 VARCHAR(255) NOT NULL,
column_name3 INT NOT NULL,
FOREIGN KEY (column_name1)
REFERENCES parent_table (column_name1)
ON UPDATE CASCADE
);
-
ON DELETE CASCADE with ALTER TABLE statement: ALTER TABLE statement is used to add, delete, or modify columns in an existing table. A foreign key with ON DELETE CASCADE means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.
Syntax:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column.. column_n)
ON DELETE CASCADE;
-
ON UPDATE CASCADE with ALTER TABLE statement: A foreign key with ON UPDATE CASCADE means that if a record in the parent table is UPDATED, then the corresponding records in the child table will automatically be updated.
Syntax:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column.. column_n)
ON UPDATE CASCADE;
Logical Operators in MySQL: There are three Logical Operators namely, AND, OR, and NOT. The AND and OR operators are used to filter records depending on more than one condition.
-
AND Operator:- Used to filter records with more than two conditions. AND will work only when all the conditions are satisfied.
Syntax:SELECT * FROM table_name
WHERE condition1 AND condition2;
-
OR Operator:- Used to filter records with more than two conditions. OR will work even if only one condition is satisfied.
Syntax:
SELECT * FROM table_name
WHERE condition1 OR condition2; -
NOT Operator:- Used to filter records more than two conditions. It works with both AND and OR Operators.
Syntax:
SELECT * FROM table_name
WHERE NOT condition1 and NOT condition2
Get certification for your knowledge in the fundamentals of Computer functioning by clearing the Computer Certification exam conducted by StudySection. After going through this Computer Certification exam, you will be able to evaluate your basic knowledge of computers.
