mysql> SELECT * FROM tablename WHERE name like “sonia%” AND phone_number = ‘9876543210’ limit 1,5; To show all records starting with the letters ‘sonia’ and the phone number ‘9876543210’ limited to records 1 through 5 in MySQL, you can use the following SQL query: sql SELECT * FROM your_table_name WHERE column_name LIKE ‘sonia%’ AND phone_number_column = ‘9876543210’ LIMIT 1, 5; Replace your_table_name with the actual name of your table, column_name with the actual name of the column containing the names,…

Read More Read More

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3); To load a CSV file into a table in MySQL, you can use the LOAD DATA INFILE statement. Here’s a basic example of how to do it: sql LOAD DATA INFILE ‘path_to_your_csv_file.csv’ INTO TABLE your_table_name FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ IGNORE 1 ROWS; — If your CSV file contains a header row Replace ‘path_to_your_csv_file.csv’ with…

Read More Read More

MySQL Commands: Show databases; Create database db_name; Use dbname; Show tables; Create table tb_name(id int, name varchar(20)); Desc tb_name; Insert into tb_name values(101 , ‘name of person’); Insert into tb_name (id) values(102); Update tb_name set name=’person name’ where id=102; Select * from tb_name; Delete from tb_name where id=102; Drop table tb_name; Drop database db_name; Rename table tb_old_name to tb_new_name; Alter table customer add (remark varchar(20)); Alter table customer modify remark varchar(25); Alter table customer modify remark varchar(20); Alter table customer…

Read More Read More

mysql> SELECT DISTINCT columnname FROM tablename; To show unique records in MySQL, you can use the DISTINCT keyword in a SELECT statement. Here’s an example: sql SELECT DISTINCT column1, column2, … FROM your_table; Replace column1, column2, … with the columns for which you want to retrieve unique records, and your_table with the actual name of your table. If you want to select all columns, you can use the asterisk *: sql SELECT DISTINCT * FROM your_table; This query will return…

Read More Read More

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql To dump all databases in MySQL for backup, you can use the mysqldump command-line utility. Here’s the command: bash mysqldump -u username -p –all-databases > backup.sql Replace username with your MySQL username. When you run this command, it will prompt you to enter your MySQL password. After entering the password, it will dump all databases into a file named backup.sql. This backup.sql file contains SQL commands to recreate all databases, including their…

Read More Read More

How to Import data into MySQL from any file: Mysql –u root <db.sql (for database and tables) Mysql –u root <data.sql (for data into tables) To import data into MySQL from a file, you can use the LOAD DATA INFILE statement. Here’s a basic example of how to use it: sql LOAD DATA INFILE ‘path/to/your/file.csv’ INTO TABLE your_table FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES; — if your file has a header and…

Read More Read More

mysql> SELECT col1,col2 FROM tablename ORDER BY col2 DESC; mysql> SELECT col1,col2 FROM tablename ORDER BY col2 ASC; In MySQL, you can use the ORDER BY clause to sort selected records in either ascending (ASC) or descending (DESC) order. Here’s the basic syntax: sql SELECT column1, column2, … FROM table_name ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], …; If you want to sort in ascending order, you can omit ASC as it is the default: sql SELECT…

Read More Read More

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql To dump a MySQL database for backup, you can use the mysqldump command. Here’s the basic syntax: bash mysqldump -u username -p database_name > backup_file.sql Replace username with your MySQL username, database_name with the name of the database you want to backup, and backup_file.sql with the name you want to give to your backup file. After running this command, you’ll be prompted to enter your MySQL password. If you want to…

Read More Read More

Interview Scenario on MySQL: There is a table named SAMPLE, and we want to delete all the data from the table. Which is better option? delete * from SAMPLE truncate table SAMPLE In delete cursor is on the current location, data is deleted from the table but memory is not released by the table, by which searching and sorting operation may take so much time. While, in truncate cursor is on the starting location, data is deleted permanently and memory is released for…

Read More Read More

mysql> SELECT COUNT(*) FROM tablename; To return the total number of rows in a MySQL table, you can use the COUNT() function in a SQL query. Here’s an example: sql SELECT COUNT(*) AS total_rows FROM your_table_name; Replace your_table_name with the actual name of your table. This query will return a single value named total_rows, representing the total number of rows in the specified table.