Use DATE_FORMAT() and set the specifiers to display only the MonthName and Year. Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    AdmissionDate date ); Query OK, 0 rows affected (0.69 sec)Insert records in the table using insert command −mysql> insert into DemoTable(AdmissionDate) values('2013-04-21'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(AdmissionDate) values('2014-01-31'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(AdmissionDate) values('2016-09-01'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(AdmissionDate) values('2018-12-12'); Query OK, 1 row affected (0.11 sec) mysql> insert ... Read More

To select first element of a comma-separated list, you can use SUBSTRING_INDEX(). Let us first create a table:mysql> create table DemoTable (    CSV_Value varchar(200) ); Query OK, 0 rows affected (0.81 sec)Following is the query to insert some records in the table using insert command. We have inserted records in the form of comma-separated integer list:mysql> insert into DemoTable values('10, 20, 50, 80'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('100, 21, 51, 43'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('1, 56, 103, 1090'); Query OK, 1 row affected (0.26 ... Read More

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