We can change the MySQL root password using the below statement in the new notepad file and save it with an appropriate name: ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword’; Next, open a Command Prompt and navigate to the MySQL directory. Now, copy the following folder and paste it in our DOS command and press the Enter key. C:\Users\javatpoint> CD C:\Program Files\MySQL\MySQL Server 8.0\bin Next, enter this statement to change the password: mysqld –init-file=C:\\mysql-notepadfile.txt Finally, we can log into the MySQL…

Read More Read More

A trigger is a procedural code in a database that automatically invokes whenever certain events on a particular table or view in the database occur. It can be executed when records are inserted into a table, or any columns are being updated. We can create a trigger in MySQL using the syntax as follows: CREATE TRIGGER trigger_name [before | after] {insert | update | delete} ON table_name [FOR EACH ROW] BEGIN –variable declarations –trigger code END;

Heap tables: Heap tables are found in memory that is used for high-speed storage temporarily. They do not allow BLOB or TEXT fields. Heap tables do not support AUTO_INCREMENT. Indexes should be NOT NULL. Temporary tables: The temporary tables are used to keep the transient data. Sometimes it is beneficial in cases to hold temporary data. The temporary table is deleted after the current client session terminates. Main differences: The heap tables are shared among clients, while temporary tables are…

Read More Read More

SELECT * FROM table_name LIMIT 0,20; To display the top 20 rows in MySQL, you can use the LIMIT clause in your query. Here’s the syntax: SELECT * FROM your_table_name LIMIT 20; Replace your_table_name with the name of your table from which you want to retrieve the top 20 rows. This query will retrieve the first 20 rows from the table according to the default order (usually the order in which the rows were inserted). If you want to specify…

Read More Read More

The default location of MySQL data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data. To determine the location of the MySQL data directory, you can use one of the following methods: Using MySQL Command Line: You can log into the MySQL command line interface and run the following SQL query: SHOW VARIABLES LIKE ‘datadir’; This will display the path to the MySQL data directory. Checking my.cnf Configuration File: MySQL configuration is often specified in the my.cnf or…

Read More Read More

mysql> show columns from tablename; In MySQL, you can use the DESCRIBE statement or the SHOW COLUMNS statement to retrieve information about the columns of a table. Both commands provide details about the structure of a table. DESCRIBE statement: sql DESCRIBE your_table_name; Example: sql DESCRIBE employees; SHOW COLUMNS statement: sql SHOW COLUMNS FROM your_table_name; Example: sql SHOW COLUMNS FROM employees; Both of these commands will return information about the columns, such as the column name, data type, whether it allows…

Read More Read More

Sometimes our table name is non-meaningful. In that case, we need to change or rename the table name. MySQL provides the following syntax to rename one or more tables in the current database: mysql> RENAME old_table TO new_table; If we want to change more than one table name, use the below syntax: RENAME TABLE old_tab1 TO new_tab1, old_tab2 TO new_tab2, old_tab3 TO new_tab3;

If we use MySQL in Windows, it is not possible to clear the screen before version 8. At that time, the Windows operating system provides the only way to clear the screen by exiting the MySQL command-line tool and then again open MySQL. After the release of MySQL version 8, we can use the below command to clear the command line screen: mysql> SYSTEM CLS;

FLOAT stores floating-point numbers with accuracy up to 8 places and allocate 4 bytes. On the other hand, DOUBLE stores floating-point numbers with accuracy up to 18 places and allocates 8 bytes. In MySQL, FLOAT and DOUBLE are both data types used for storing floating-point numbers. The primary difference between them lies in their storage size and precision. FLOAT typically requires 4 bytes of storage and offers single-precision floating-point numbers, which can store approximate values with up to 7 significant…

Read More Read More

If you want to display the current date and time, use: SELECT NOW(); If you want to display the current date only, use: SELECT CURRENT_DATE(); To display the current date and time in MySQL, you can use the NOW() function. Here’s the query: SELECT NOW(); This will return the current date and time in the format ‘YYYY-MM-DD HH:MM:SS’.