First, you need to open my.cnf file. The following is the query to get the directory location of the config file on Windows −mysql> select @@datadir;Output+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Here is the snapshot of the directory −Now open my.cnf file. The snapshot is as follows −If you want to add more data to cache, ... Read More

Error occurs when you set a negative value to UNSIGNED column in MySQL. For example, let us first create a table with an UNSIGNED field −mysql> create table UnsignedDemo    -> (    -> Id int UNSIGNED    -> ); Query OK, 0 rows affected (0.79 sec)The error is as follows whenever you insert negative value to column Id which is declared as UNSIGNED −mysql> INSERT INTO UnsignedDemo VALUES(-100); ERROR 1264 (22003): Out of range value for column 'Id' at row 1ExampleHowever, positive values work well for UNSIGNED. The same is shown in the example below. Insert some records in ... Read More

To get the average string length in MySQL, we will work around a query that gets rows from 1 to 10 and displays the result.Let us first create a table. The query to create a table is as follows −mysql> create table AverageString -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Value varchar(20) -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into AverageString(Value) values('MySQL Query'); Query OK, 1 row ... Read More

To understand the GROUP BY and MAX on multiple columns, let us first create a table. The query to create a table is as follows −mysql> create table GroupByMaxDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> CategoryId int,    -> Value1 int,    -> Value2 int    -> ); Query OK, 0 rows affected (0.68 sec)ExampleInsert some records in the table using insert command. The query is as follows −mysql> insert into GroupByMaxDemo(CategoryId, Value1, Value2) values(10, 100, 50); Query OK, 1 row affected (0.15 sec) mysql> insert into GroupByMaxDemo(CategoryId, Value1, Value2) values(10, 100, ... Read More

The SHOW TABLE STATUS in MySQL displays the NAME, ENGINE, VERSION, ROWS, CHECKSUM, etc of a table −ExampleLet us first create a table. Here, we are using the MyISAM engine. The query to create a table is as follows −mysql> create table Post_Demo    -> (    -> PostId int,    -> PostName varchar(100),    -> PostDate datetime,    -> PRIMARY KEY(PostId)    -> )ENGINE = MyISAM; Query OK, 0 rows affected (0.28 sec)Now you can check the table status using SHOW TABLE command. The query is as follows −mysql> show table status where Name = 'Post_Demo'\GOutput*************************** 1. row *************************** ... Read More

MySQL uses foreign key constraint instead of inheritance. MySQL does not support table inheritance.You can achieve the same with the help of foreign key constraint. Let us create a table and use the foreign key constraint. The query to create the first table is as follows −mysql> create table Parent_Table -> ( -> ParentId int, -> PRIMARY KEY(ParentId) -> ); Query OK, 0 rows affected (3.59 sec)Now create the second table. The query to create the second table is as follows −mysql> create table Child_Table ... Read More

Let us first create a table −mysql> create table OrderDemo    -> (    -> OrderId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> OrderPrice int,    -> OrderDatetime datetime    -> ); Query OK, 0 rows affected (0.66 sec)ExampleNow you can insert some records in the table using insert command. The query is as follows −mysql> insert into OrderDemo(OrderPrice, OrderDatetime) values(200, '2016-09-12'); Query OK, 1 row affected (0.24 sec) mysql> insert into OrderDemo(OrderPrice, OrderDatetime) values(NULL, '2002-11-18'); Query OK, 1 row affected (0.26 sec) mysql> insert into OrderDemo(OrderPrice, OrderDatetime) values(1000, '2017-12-28'); Query OK, 1 row affected (0.15 sec)Display all records ... Read More

To use HAVING with GROUPBY in MySQL, the following is the syntax. Here, we have set a condition under HAVING to get check for maximum value condition −SELECT yourColumnName FROM yourTableName GROUP BY yourColumnName HAVING MAX(yourColumnName) < yourValue;Let us see an example by creating a table in MySQL −mysql> create table WhereAfterGroupDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserProcess int, -> UserThreadId int -> ); Query OK, 0 rows affected (5.74 sec)ExampleInsert some records in the table using insert command. The query is as follows −mysql> insert into WhereAfterGroupDemo(UserProcess, UserThreadId) values(1211, 3); Query OK, 1 ... Read More

You can get the enum possible values in a MySQL database with the help of INFORMATION_SCHEMA.COLUMNS table. The syntax is as follows −SELECT    COLUMN_TYPE AS anyAliasName FROM    INFORMATION_SCHEMA.COLUMNS WHERE    TABLE_SCHEMA = ‘yourDatabaseName’ AND TABLE_NAME = 'yourTableName' AND COLUMN_NAME = 'yourEnumColumnName';To understand the above syntax, let us create a table with an ENUM data type. The query to create a table is as follows −mysql> create table EnumDemo -> ( -> Id int, -> Color ENUM('RED', 'GREEN', 'BLUE', 'BLACK', 'ORANGE') -> ); Query OK, 0 rows affected (0.66 sec)Here the table ‘EnumDemo’ is present in the ‘sample’ database. ... Read More

You can achieve this with the help of INSERT statement i.e, you can simply insert it like a normal insert. The syntax is as follows −INSERT INTO yourTableName (yourIdColumnName, yourColumnName) values(value1, 'value2'); Let us first create a table: mysql> create table InsertValueInAutoIncrement -> ( -> 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. Here, we are also inserting our own values for the auto_increment field UserId. ... Read More