- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 4378 Articles for MySQL
63 Views
As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car (number char(9)); Query OK, 0 rows affected (0.32 sec)Creating BEFORE UPDATE trigger to emulate CHECK CONSTRAINT for updating the values −Now, suppose if we will try to update the table ... Read More
843 Views
Suppose if an error occurs during trigger execution then MySQL can handle it as follows −If a BEFORE trigger fails, the operation on the corresponding row is not performed.A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.An AFTER trigger is executed only if any BEFORE triggers and the row operation execute successfully.An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.For transactional tables, failure of a statement should cause a rollback of all changes performed by the statement. ... Read More
361 Views
As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car (number char(9)); Query OK, 0 rows affected (0.32 sec) mysql> Insert into car values('AB-235-YZ'); Query OK, 1 row affected (0.10 sec)The above value is a valid one but what ... Read More
3K+ Views
Actually, MySQL SIGNAL statement is an error handling mechanism for handling unexpected occurrences and a graceful exit from the application if need to be. Basically, it provides error information to the handler. Its basic syntax would be as follows −SIGNAL SQLSTATE | condition_value [SET signal_information_item = value_1, [, signal_information_item] = value_2, etc;]Here, the SIGNAL keyword is an SQLSTATE value or a condition name declared by a DECLARE CONDITION statement. The SIGNAL statement must always specify an SQLSTATE value or a named condition that defined with an SQLSTATE value. The SQLSTATE value for a The SIGNAL statement consists of a five-character ... Read More
157 Views
We can change the name of a particular existing column from a MySQL table by using CHANGE statement along with ALTER statement. Its syntax would be as follows −SyntaxALTER TABLE table_name CHANGE old_column_name new_column_name datatype;Here, table_name is the name of the table from which we want to delete the column.Old_column_name is the name of the column which is to be changed.new_column_name is the name of the column which has to be given to the old column.ExampleIn this example, we are changing the name of the column ‘id’ to ‘studentid’ from table ‘student_info’ as follows −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | ... Read More
156 Views
We can delete a particular existing column from a MySQL table by using the DROP statement along with an ALTER statement. Its syntax would be as follows −SyntaxALTER TABLE table_name DROP column_name;Here, table_name is the name of the table from which we want to delete the column.Column_name is the name of the column which is to be deleted from the table.ExampleIn this example, we are deleting the column ‘address’ from table ‘student_detail’ as follows −mysql> select * from student_detail; +-----------+-------------+----------+ | Studentid | StudentName | address | +-----------+-------------+----------+ | 100 | Gaurav | Delhi ... Read More
506 Views
Since we cannot use the MINUS query in MySQL, we will use JOIN to simulate the MINUS query. It can be understood with the help of the following example −ExampleIn this example, we are two tables namely Student_detail and Student_info having the following data −mysql> Select * from Student_detail; +-----------+---------+------------+------------+ | studentid | Name | Address | Subject | +-----------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 130 | Ram | Jhansi ... Read More
103 Views
With the help of SHOW STATUS statement, we can get the count of MySQL event-related operations. It can be used as follows −mysql> SHOW STATUS LIKE '%event%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Com_alter_event | 16 | | Com_create_event | 6 | | Com_drop_event | 4 | | Com_show_binlog_events | 0 | | Com_show_create_event | 0 | | Com_show_events | 4 | | Com_show_relaylog_events | 0 | +--------------------------+-------+ 7 rows in set (0.17 sec)
44 Views
Followings are the status variables in MYSQL which provide us the counts of event-related operations −Com_create_event It provides us the number of CREATE EVENT statements executed since the last server restart.Com_alter_event − It provides us the number of ALTER EVENT statements executed since the last server restart.Com_drop_event − It provides us the number of DROP EVENT statements executed since the last server restart.Com_show_create_event − It provides us the number of SHOW CREATE EVENT statements executed since the last server restart.Com_show_events − It provides us the number of SHOW EVENTS statements executed since the last server restart.Read More
151 Views
It can be done with the help of the INFORMATION_SCHEMA database. The following statement will give us the metadata of events −mysql> SELECT * from INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME LIKE '%event%' A ND EVENT_SCHEMA = 'query'\G *************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: query EVENT_NAME: testing_event6 DEFINER: root@localhost TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: INSERT INTO event_message(message, generated_at) values('EVENT ALTERED', NOW()) EVENT_TYPE: ONE TIME EXECUTE_AT: 2017-11-22 20:03:52 ... Read More