To delete all rows containing string “foo” in table “bar”, you need to use LIKE operator.To understand the above syntax, let us create a sample table with name “bar”. The query to create a table is as follows. We will always insert records with string “foo” using INSERT command after creating the below table −mysql> create table bar    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Words longtext,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.61 sec)Now you can insert some records in the table using insert command. The string “foo” ... Read More

To compare two tables and return missing ids, you need to use a subquery. The syntax is as follows −SELECT yourFirstTableName.yourIdColumnName FROM yourFirstTableName WHERE NOT IN(SELECT yourSecondTableName.yourIdColumnName FROM youSecondTableName);To understand the above syntax, let us create a table with sample fields and then we will insert records. The query to create the first table −First_Tablemysql> create table First_Table    -> (    -> Id int    -> ); Query OK, 0 rows affected (0.88 sec)Now insert some records in the table using insert command. The query is as follows −mysql> insert into First_Table values(1); Query OK, 1 row affected (0.68 ... Read More

To drop a database with the character ‘?’ in its name, you need to use backtick symbol around the database name. The syntax is as follows −DROP DATABASE `yourDatabaseName`;To understand the above syntax, let us create a database. The query to create a database is as follows −mysql> create database `test?data`; Query OK, 1 row affected (0.14 sec)So, I have a database with? character. The query to show all databases is as follows −mysql> show databases;The following is the output −+-----------------------+ | Database              | +-----------------------+ | business              | ... Read More

To remove \r from the text, you need to use REPLACE command. The syntax is as follows −UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName, ’\r’, ’ ‘);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name text, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.79 sec)Now insert some records in the table using insert command. The query is as follows −mysql> insert into removeDemo(Name) values('John\rSmithCarol'); Query OK, 1 row affected (0.13 sec) mysql> insert into removeDemo(Name) values('LarryMike\rSam'); ... Read More

To extract the Day/Month/Year from a timestamp, you need to use the date_parse() function. The syntax as follows −print_r(date_parse(“anyTimeStampValue”));The PHP code is as follows −$yourTimeStampValue="2019-02-04 12:56:50"; print_r(date_parse($yourTimeStampValue));The snapshot of PHP code is as follows −The following is the output −Array ( [year] => 2019 [month] => 2 [day] => 4 [hour] => 12 [minute] => 56 [second] => 50 [fraction] => 0 [warning_count] => 0 [warnings] => Array ( ) [error_count] => 0 [errors] => Array ( ) [is_localtime] => )The snapshot of the sample output −

You cannot get the type of variable in MySQL. Cast the type of variable into another using CAST operator. The syntax is as follows −SET @yourVariableName:=’anyValue’Use the CAST operator to cast to another type. The syntax is as follows −SELECT CAST( @yourVariableName AS SIGNED);To understand the above syntax, let us cast to another type.Case 1: String to unsigned −mysql> set @StringToInt:='12345'; Query OK, 0 rows affected (0.00 sec)The query is as follows to another type −mysql> select CAST(@StringToInt as UNSIGNED);The following is the output −+--------------------------------+ | CAST(@StringToInt as UNSIGNED) | +--------------------------------+ | 12345               ... Read More

To find invalid email address, use the below syntax −SELECT yourColumnName FROM yourTableName WHERE yourColumnName NOT LIKE '%_@_%._%';The above syntax will give the list of all invalid email addresses. To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table FindInvalidEmailAddressDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> EmailAddress varchar(40),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.75 sec)Now you can insert some records in the table using insert command. We have inserted some invalid ... Read More

You can achieve this with the help of INSERT INTO SELECT statement. The syntax is as follows −INSERT INTO yourDatabaseName.yourTableName(SELECT *FROM yourDatabaseName.yourTableName);To understand the above syntax, let us create a table in a database and second table in another databaseThe database name is “bothinnodbandmyisam”. Let us create a table in the same database. The query is as follows −mysql> create table Student_Information    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(10),      -> Age int    -> ); Query OK, 0 rows affected (0.67 sec)Now you can insert some records in the ... Read More

You can use a dynamic query for this. First set the variable name for username and variable name for a password. The syntax is as follows −SET @anyVariableName=’yourUserName’; SET @anyVariableName1=’yourpassword’;Now you can use the CONCAT() function from MySQL. The syntax is as follows −SET @yourQueryName = CONCAT ('    CREATE USER "', @anyVariableName, '"@"localhost" IDENTIFIED BY "', @anyVariableName1, '" ' );Let us use the prepared statement PREPARE. The syntax is as follows −PREPARE yourStatementVariableName FROM @yourQueryName;Now you can execute the statement. The syntax is as follows −EXECUTE yourStatementVariableName;Deallocate the above using the DEALLOCATE PREPARE. The syntax is as follows −DEALLOCATE ... Read More

You need to use ADD KEY to make a column with Key=MUL. The syntax is as follows −ALTER TABLE yourTableName MODIFY COLUMN yourColumnName data type, ADD KEY(yourColumnName);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Instructor    -> (    -> Instructor_Id int,    -> Instructor_Name varchar(30),    -> Instructor_CourseName varchar(100)    -> ); Query OK, 0 rows affected (0.63 sec)Now you can look the table description of the table, the column KEY does not have any MUL key. The query is as follows to check the ... Read More