To check replication type, you can use SHOW GLOBAL VARIABLES command. The syntax is as follows −SHOW GLOBAL VARIABLES LIKE 'binlog_format';The above syntax returns either ROW, MIXED or STATEMENT. The default resultant is ROW.Now you can implement the above syntax to check replication type. The query is as follows −mysql> SHOW GLOBAL VARIABLES LIKE 'binlog_format';Here is the output −+---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.10 sec)Here is the query to switch from ROW to STATEMENT −mysql> SET GLOBAL binlog_format = 'STATEMENT'; Query OK, 0 rows affected (0.04 ... Read More

To select accumulated column, let us first create a demo table. The query to create a table is as follows −mysql> create table accumulatedDemo    -> (    -> Value int    -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into accumulatedDemo values(10); Query OK, 1 row affected (0.21 sec) mysql> insert into accumulatedDemo values(15); Query OK, 1 row affected (0.09 sec) mysql> insert into accumulatedDemo values(20); Query OK, 1 row affected (0.13 sec) mysql> insert into accumulatedDemo values(25); Query OK, 1 row affected ... Read More

The SELECT INTO equivalent is CREATE TABLE AS SELECT statement. The syntax is as follows −CREATE TABLE yourNewTableName AS SELECT *FROM yourTableName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table selectIntoEquivalentDemo    -> (    -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ClientName varchar(20),    -> ClientAge int    -> ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into selectIntoEquivalentDemo(ClientName, ClientAge) values('Larry', 34); Query OK, 1 row affected (0.13 ... Read More

To find/replace string in fields, the syntax is as follows −update yourTableName set yourColumnName =REPLACE(yourColumnName, yourOldValue, yourNewValue);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table FindReplaceDemo    -> (    -> FileId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> FileDirectory text    -> ); Query OK, 0 rows affected (0.92 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into FindReplaceDemo(FileDirectory) values('C://User//MySQL'); Query OK, 1 row affected (0.19 sec) mysql> insert into FindReplaceDemo(FileDirectory) values('D://WebsiteImage//image1.jpg'); Query OK, ... Read More

Adding an index on column of int type is a good choice to run your query faster whenever your table has lots of records.If your table has less records then it is not a good choice to use index on column of int type.To understand the concept, let us create a table. The query to create a table is as follows −mysql> create table indexOnIntColumnDemo    -> (    -> UserId int,    -> UserName varchar(20),    -> UserAge int,    -> INDEX(UserId)    -> ); Query OK, 0 rows affected (0.85 sec)Now check the description of table −mysql> desc ... Read More

You can use DROP INDEX for this. The syntax is as follows −alter table yourTablename drop index yourUniqueName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeUniquenessConstraint    -> (    -> Id int,    -> Name varchar(100),    -> Age int,    -> isGreaterThan18 bool,    -> UNIQUE(Id, isGreaterThan18)    -> ); Query OK, 0 rows affected (0.69 sec)Now check the details of table with the help of SHOW CREATE command. The query is as follows −mysql> show create table removeUniquenessConstraint;Here is the output −+----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ... Read More

Let us first create a demo table −mysql> create table BulkChangeDemo    -> (    -> CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> CustomerName varchar(20),    -> isEducated boolean    -> ); Query OK, 0 rows affected (1.47 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into BulkChangeDemo(CustomerName, isEducated) values('Larry', true); Query OK, 1 row affected (0.09 sec) mysql> insert into BulkChangeDemo(CustomerName, isEducated) values('John', false); Query OK, 1 row affected (0.16 sec) mysql> insert into BulkChangeDemo(CustomerName, isEducated) values('Carol', false); Query OK, 1 row affected (0.25 sec) mysql> insert into BulkChangeDemo(CustomerName, ... Read More

You can use ORDER BY RIGHT() to ORDER BY last 2 character string.The syntax is as followsselect yourColumnName from yourTableName ORDER BY RIGHT(yourColumnName , 2);To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table OrderByLast2CharactersDemo    -> (    -> CustomerId varchar(20),    -> CustomerName varchar(20)    -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into OrderByLast2CharactersDemo(CustomerId, CustomerName) values('John-98', 'John'); Query OK, 1 row affected (0.20 sec) mysql> insert into OrderByLast2CharactersDemo(CustomerId, CustomerName) values('Carol-91', ... Read More

You can verify SET FOREIGN KEY CHECKS is set to = 1 or not with the help of variable@@foreign_key_checks;The syntax is as follows −select @@foreign_key_checks;You can use SHOW VARIABLES command. The syntax is as follows −show variables like 'foreign%';Now you can implement both the syntaxes.Case 1 − Using variable @@foreign_key_checks.The query is as follows −mysql> SELECT @@foreign_key_checks; Here is the output −+----------------------+ | @@foreign_key_checks | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec)Case 2 − Using SHOW commandThe ... Read More

Ampersands work in Oracle. To work it in MySQL, use @ as shown in the following syntax −SET @yourVariableName1 = yourValue, @yourVariableName2 = yourValue, @yourVariableName3 =yourValue, .........N; insert into yourTableName values(@yourVariableName1, @yourVariableName2, @yourVariableName3, ........N);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Student_Information    -> (    -> StudentId int,    -> StudentName varchar(100),    -> StudentAge int,    -> StudentMarks int,    -> StudentCountryName varchar(10)    -> ); Query OK, 0 rows affected (0.75 sec)Here is the query with values prepended by @. Insert some records ... Read More