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

How to give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs # mysql -u root -p # mysql -u root -p mysql> use mysql; mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’); mysql> flush privileges; or mysql> grant all privileges on databasename.* to username@localhost; mysql> flush privileges How to show all records starting with the letters ‘sonia’ AND the phone number ‘9876543210’ limit to records 1 through 5. mysql> SELECT * FROM…

Read More Read More

mysql> alter table [table name] drop column [column name]; mysql> alter table [table name] add column [new column name] varchar (20); To delete a column in MySQL, you would use the ALTER TABLE statement followed by the DROP COLUMN keyword. Here’s the syntax: sql ALTER TABLE table_name DROP COLUMN column_name; Replace table_name with the name of your table and column_name with the name of the column you want to delete. To add a new column, you would also use the…

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