To insert pipe(|) character in string on INSERT INTO, let us first see an example and create a table. The query to create a table is as followsmysql> create table PipeInsertDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserPassword varchar(100) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into PipeInsertDemo(UserPassword) values('John123|'); Query OK, 1 row affected (0.15 sec) mysql> insert into PipeInsertDemo(UserPassword) values('|123456CarolTaylor'); Query OK, 1 row affected ... Read More

In order to view the auto_increment value for a table, you can use SHOW TABLE command.The syntax is as followsSHOW TABLE STATUS LIKE 'yourTableName'\GThe syntax is as followsSELECT `AUTO_INCREMENT`    FROM `information_schema`.`TABLES`    WHERE `TABLE_SCHEMA` = ‘yourDatabaseName’    AND `TABLE_NAME` =’yourTableName';To understand the above syntaxes, let us create a table. The query to create a table is as followsmysql> create table viewAutoIncrementDemo    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(20)    -> ); Query OK, 0 rows affected (0.59 sec)Now you can insert some records in the table using insert command. The ... Read More

If a table is crashed that means your ENGINE is NULL or empty. The syntax is as follows to check for crashed table.SHOW TABLE STATUS FROM yourDatabaseName;Let us implement the above syntax to check for crashed table Here, our database name is ‘test3’ with some tablesmysql> show table status from test3;The following is the output+------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | bestdateformatdemo           ... Read More

You need to use INFORMATION_SCHEMA.SCHEMATA for current default database collation.The syntax is as followsSELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'yourDatabaseName' LIMIT 1;Let us implement the above syntax to discover current default database collation (via command line client). Our database here is ‘sample’.The query is as follows −mysql> SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'sample' LIMIT 1;The following is the output+------------------------+ | DEFAULT_COLLATION_NAME | +------------------------+ | utf8_general_ci | +------------------------+ 1 row in set (0.00 sec)

To understand the concept, let us first create a demo table.mysql> create table addToExistingValueDemo    -> (    -> Instructor_Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Instructor_Name varchar(30),    -> Instructor_TechnicalSubject text    -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into addToExistingValueDemo(Instructor_Name, Instructor_TechnicalSubject) values('John', 'C, C++'); Query OK, 1 row affected (0.15 sec) mysql> insert into addToExistingValueDemo(Instructor_Name, Instructor_TechnicalSubject) values('Carol', 'Java, Python'); Query OK, 1 row affected (0.18 sec) mysql> insert into addToExistingValueDemo(Instructor_Name, Instructor_TechnicalSubject) values('Bob', 'MySQL, SQL Server'); Query OK, 1 row ... Read More

To count boolean field values within a single query, you can use CASE statement. Let us create a demo table for our example −mysql> create table countBooleanFieldDemo    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentFirstName varchar(20),    -> isPassed tinyint(1)    -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into countBooleanFieldDemo(StudentFirstName, isPassed) values('Larry', 0); Query OK, 1 row affected (0.12 sec) mysql> insert into countBooleanFieldDemo(StudentFirstName, isPassed) values('Mike', 1); Query OK, 1 row affected (0.17 sec) mysql> insert into ... Read More

To avoid inserting duplicate rows in MySQL, you can use UNIQUE(). The syntax is as follows −ALTER TABLE yourTableName ADD UNIQUE(yourColumnName1, yourColumnName2, ...N);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table avoidInsertingDuplicateRows    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> FirstValue int,    -> SecondValue int    -> ); Query OK, 0 rows affected (0.53 sec)Now check the description of table using desc command. The query is as follows −mysql> desc avoidInsertingDuplicateRows;Sample The following is The output −+-------------+---------+------+-----+---------+----------------+ | Field       ... Read More

The desc is a MySQL reserved word, therefore you cannot use it. But, if you still want to set the column name as ‘desc’, you need to use backticks. The backtick notation is (` `).To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table useOfDescColumnNameDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(20),    -> `desc` varchar(200) default 'GOOD'    -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. The query is as follows ... Read More

To create a view only if it does not already exist, you can use the following syntax −CREATE OR REPLACE VIEW yourViewName AS SELECT *FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table createViewDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name 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 createViewDemo(Name) values('John'); Query OK, 1 row affected (0.22 sec) mysql> insert into ... Read More

The syntax is as follows to count all characters in all rows of a field −select sum(char_length(yourColumnName)) AS anyAliasName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table CountAllCharactersDemo    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(20),    -> UserSubject text    -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into CountAllCharactersDemo(UserName, UserSubject) values('Larry', 'Introduction To Java'); Query OK, 1 row affected (0.19 ... Read More