To achieve this, you can use the following syntax in MySQL −select *from yourTableName\G;Here, G can be used for vertical purpose. You need to add yourTableName.Let us create a table in order to understand the above syntax. Creating a table with the help of CREATE command.The following is the query to create a table −mysql> create table TooManyFieldsreturnDemo -> ( -> Id int, -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.64 sec)Now you can insert records in the table ... Read More

In order to stop a running MySQL query, we can use the KILL command with process id. The syntax is as follows −kill processId;Or you can stop a running MySQL query with the help of below syntax −call mysql.rds_kill(queryId);Let us first get the processId with the help of show command. The query is as follows −mysql> show processlist;Here is the output with the list of processes −+----+-----------------+-----------------+----------+---------+--------+------------------------+------------------+ | Id |  User           | Host            | db       | Command | Time   | State           ... Read More

To select rows where a column is null, you can use IS NULL from MySQL with the help of where clause.The syntax is as follows −select *from yourTableName where yourColumnName IS NULL;Let us first create a table to understand the concept −mysql> create table NULLDemo1 -> ( -> StudentId int, -> StudentName varchar(100) -> ); Query OK, 0 rows affected (1.48 sec)Inserting records into the table. The query to insert records is as follows −mysql> insert into NULLDemo1 values(NULL, 'John'); Query OK, 1 row affected (0.25 sec) mysql> ... Read More

The “unsigned” in MySQL is a data type. Whenever we write an unsigned to any column that means you cannot insert negative numbers. Suppose, for a very large number you can use unsigned type.The maximum range with unsigned int is 4294967295.Note: If you insert negative value you will get a MySQL error.Here is the example demo of unsigned type. Let us first create a table with “unsigned” column. The following is the query to create a table −mysql> create table UnsignedDemoWithPositiveValue -> ( -> Distance int unsigned -> ); Query OK, ... Read More

You can implement MySQL Like IN() with the help of Regular Expression (regexp) as well. The syntax is as follows −select *from yourTableName where yourColumName regexp ‘value1|value2|value3……|valueN’;To understand the above logic, you need to create a table. Let us first create a table −mysql> create table INDemo -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.90 sec)Insert some records into the table. The query is as follows −mysql> insert into INDemo values(100, 'John'); Query OK, 1 row affected (0.13 sec) mysql> ... Read More

To change MySQL default character set to UTF-8 in my.cnf, firstly reach the location of my.cnf file.The following is the screenshot of “my.cnf” file. Firstly, open the C: directory and the “Program Data” folder −Now, click on “MySQL” folder −Now, click the MySQL Server 8.0 folder and open it −After opening MySQL Server 8.0, you will get “my.cnf” file location.After opening my.cnf file, you will get the character-set. Open the file and the following contens are visible −Now you can change the character-set to utf8.

To connect MySQL from the command line, firstly open command prompt. You can do this with the help of shortcut key “Windows + R”. On clicking, a panel will open and you need to type CMD and need to press OK button as shown below −After pressing the OK button, you will get command line window.Reach the MySQL Server “bin” directory as shown in the following screenshot −Now you have reached the bin directory. Type the following statement in order to connect with MySQL.mysql -u yourUserName -pApply the above statement to connect with MySQL. The snapshot is as follows with ... Read More

To get ER model of database from server, you need to first launch MySQL Workbench. The snapshot is as follows −After that you need to select the “Database” menu −Database->Reverse EngineerAfter that a wizard will open as in the following screenshot. Add the password and press OK twice.After pressing the OK button, you will get the next wizard and you need to press next button. The following would be visible now −After that you need to select “Model” menu as I have marked in the above wizard.Model->Create Diagram from Catalog Objects After selecting the option, you will get the ER ... Read More

You can show constraints on tables with the help of SHOW command. The syntax is as follows −show create table yourTableName;The above command will show all constraints with table ENGINE. Using this, you can even see all the column names and corresponding data types.To understand the above MySQL statement, let us first create a table −mysql> create table ShowConstraintsDemo    -> (    -> BookId int not null, -> BookName varchar(200) not null, -> BookAuthor varchar(200) Unique not null, -> Primary key(BookId, BookName) -> ); Query OK, 0 rows affected (1.04 sec)Now you can apply the above syntax in order ... Read More

Order a MySQL table by two columns with the help of below syntax −order by yourColumnName1 DESC, yourColumnName2 DESC;Let us first create a table for our example −mysql> create table OrderByDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (0.57 sec)Inserting records into the table with the help of insert command. The query is as follows −mysql> insert into OrderByDemo values(1, 'John', 23); Query OK, 1 row affected (0.20 sec) mysql> insert into OrderByDemo values(3, ... Read More