In order to know the location of MySQL table data, you can use the below syntax −select @@datadir;You can also use SHOW VARIABLES command for this. Following is the syntax −show variables where Variable_name ='datadir';Let us implement the above syntaxes to know where MySQL table stores data −mysql> select @@datadir;This will produce the following output −+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ ... Read More

To get maximum and minimum values in a single query, use the aggregate function min() and max(). Let us first create a table:mysql> create table DemoTable (    FirstValue int,    SecondValue int ); Query OK, 0 rows affected (0.66 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values(10, 30); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(30, 60); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(100, 500); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(50, 80); Query OK, ... Read More

To find strings beginning with a number, use Regular Expressions. Let us first create a table −mysql> create table DemoTable (    Id varchar(200) ); Query OK, 0 rows affected (0.59 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('123User'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('_$123User'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('User123456'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('0000User'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('&^*User'); Query OK, 1 row affected (0.24 sec)Display records ... Read More

To get number of values that only appear once in a column, use GROUP BY HAVING. Let us first create a table:mysql> create table DemoTable (    Name varchar(20) ); Query OK, 0 rows affected (0.55 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values('Larry'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Larry'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into ... Read More

To export specific column data in MySQL, use OUTFILE −select yourColumnName from yourTableName into outfile 'yourLocationOfFile’;Let us first create a table −mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFirstName varchar(20),    StudentLastName varchar(20) ); Query OK, 0 rows affected (0.54 sec)Insert records in the table using insert command −mysql> insert into DemoTable(StudentFirstName, StudentLastName) values('John', 'Doe'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable(StudentFirstName, StudentLastName) values('David', 'Miller'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(StudentFirstName, StudentLastName) values('John', 'Smith'); Query OK, 1 row affected (0.15 sec) mysql> insert into ... Read More

To understand this, let us create a table with fields like ID, Customer Name, Items, Price. We will first multiply the items with price. After that the rows with similar records i.e. same customer name will get added.Let us first create a table:mysql> create table DemoTable (    CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    CustomerName varchar(100),    CustomerItems int,    CustomerPrice int ); Query OK, 0 rows affected (0.54 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable(CustomerName, CustomerItems, CustomerPrice)values('Larry', 3, 450); Query OK, 1 row affected (0.24 sec) mysql> ... Read More

You can use DEFAULT command for this. Following is the syntax −alter table yourTableName change yourColumnName yourColumnName TINYINT(1) DEFAULT 1 NOT NULL;Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserName varchar(20),    UserAge int,    isMarried tinyint(1) ); Query OK, 0 rows affected (0.80 sec)Let us check the description of table −mysql> desc DemoTable;This will produce the following output −+-----------+-------------+------+-----+---------+----------------+ | Field     | Type        | Null | Key | Default | Extra          | +-----------+-------------+------+-----+---------+----------------+ | Id       ... Read More

You cannot use backticks with column value. For this, use only table name or column name. If you use backtick with column value then MySQL will give the following error message:ERROR 1054 (42S22): Unknown column '191.23.41.10' in 'where clause'Let us first create a table:mysql> create table DemoTable6 (    SystemIPAddress varchar(200) ); Query OK, 0 rows affected (0.46 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values('192.68.1.0'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('191.23.41.10'); Query OK, 1 row affected (0.12 sec)Now you can display specific record ... Read More

You can use SUBSTRING() along with UPDATE command to delete last 4 letters. Let us first create a table −mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentSubject varchar(100) ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentSubject) values('Introduction to Java'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentSubject) values('Introduction to C'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentSubject) values('Introduction to C++'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(StudentSubject) values('Spring And Hibernate'); ... Read More

There is no double equal sign concept. It can be used to compare two values. If you use double equal sign(==) in MySQL, you will get an error message.Let us verify the concept is true or not. Declare a variable −mysql> set @Number=10; Query OK, 0 rows affected (0.00 sec)Now, compare the above variable value with 10. If both the values are same then the result will be 1 otherwise 0.Using double equal sign −mysql> select 10==@Number;This will produce the following output i.e. an error −ERROR 1064 (42000): You have an error in your SQL syntax; check the manual ... Read More