How can we run batch mode in MySQL? To perform batch mode in MySQL, we use the following command: mysql; mysql mysql.out; In which language MySQL has been written? MySQL is written in C and C++, and its SQL parser is written in yacc. How to install MySQL? Installing MySQL on our system allows us to safely create, drop, and test web applications without affecting our live website’s data. There are many ways to use MySQL on our system, but…
How to change the table name in MySQL? 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; How to set auto increment in MySQL? Auto Increment is a constraint that automatically generates a unique number while…
What is the difference between NOW() and CURRENT_DATE()? NOW() command is used to show current year, month, date with hours, minutes, and seconds while CURRENT_DATE() shows the current year with month and date only. What are the different column comparison operators in MySQL? The =, , <=, =, >, <>, , AND, OR or LIKE operator are the comparison operators in MySQL. These operators are generally used with SELECT statement. How do you determine the location of MySQL data directory?…
What is MySQL data directory? MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default, the information managed my MySQL = server mysqld is stored in the data directory. How to clear screen in MySQL? 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…
What are the security alerts while using MySQL? Install antivirus and configure the operating system’s firewall. Never use the MySQL Server as the UNIX root user. Change the root username and password Restrict or disable remote access. What is the difference between the database and the table? There is a major difference between a database and a table. The differences are as follows: Tables are a way to represent the division of data in a database while the database is…
What is REGEXP? REGEXP is a pattern match using a regular expression. The regular expression is a powerful way of specifying a pattern for a sophisticated search. Basically, it is a special text string for describing a search pattern. To understand it better, you can think of a situation of daily life when you search for .txt files to list all text files in the file manager. The regex equivalent for .txt will be .*.txt. What are the drivers in…
How will Show all records containing the name “sonia” AND the phone number ‘9876543210’ mysql> SELECT * FROM tablename WHERE name = “sonia” AND phone_number = ‘9876543210’ How to Update database permissions/privilages. mysql> flush privileges; how to Return total number of rows mysql> SELECT COUNT(*) FROM tablename; How to dump one database for backup. # [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql How to delete a database from mysql server mysql> drop database databasename; How to Set a root…
How to search second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load? By below query we will get second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load? SELECT DISTINCT(salary) FROM employee order by salary desc limit 1 , 1 ; (This way we will able to find out 3rd highest , 4th highest salary so on just need to change limit condtion like LIMIT…
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…
How to dump a table from a database. # [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql How we get Sum of column mysql> SELECT SUM(*) FROM [table name]; How to allow the user “sonia” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs # mysql -u root -p mysql> use mysql; mysql> grant usage on *.* to sonia@localhost identified by ‘passwd’; mysql> flush…