MySQL Articles - Page 184 of 355

Let us first create a table wherein we have a Primary Key CustomerId −mysql> create table DemoTable    (    CustomerId int NOT NULL AUTO_INCREMENT,    CustomerName varchar(20),    CustomerAge int,    CustomerCountryName varchar(100),    PRIMARY KEY(CustomerId)    ); Query OK, 0 rows affected (0.94 sec)Following is the query to get the primary key “column name” of a specific table in MySQL −mysql> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'DemoTable' AND CONSTRAINT_NAME = 'PRIMARY';This will produce the following output −+-------------+ | COLUMN_NAME | +-------------+ | CustomerId | +-------------+ 1 row in set, 2 warnings (0.12 sec)

You cannot use ‘from’ as column name directly because ‘from’ is a reserved word in MySQL.If you want to still use it, then you need to use the backtick symbol.Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    `from` varchar(100),    Name varchar(10)    ); Query OK, 0 rows affected (0.92 sec)Insert records in the table using insert command −mysql> insert into DemoTable(`from`, Name) values('US', 'John'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(`from`, Name) values('UK', 'Carol'); Query OK, 1 row affected (0.14 sec) mysql> ... Read More

First, you need to know how many columns are present in a table. Following is the syntax to know the column names −show columns from yourTableName;Following is the syntax to concatenate all columns −select concat(yourColumnName1, yourColumnName2, yourColumnName3, ........N) from yourTableName;Let us first create a table −mysql> create table DemoTable    (    CustomerId int,    CustomerName varchar(20),    CustomerAge int    ); Query OK, 0 rows affected (0.66 sec)Following is the query to know the exact column −mysql> show columns from DemoTable;This will produce the following output −+--------------+-------------+------+-----+---------+-------+ | Field        | Type        | Null ... Read More

You can use aggregate function MAX() and MIN() for this.Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Number1 int,    Number2 int    ); Query OK, 0 rows affected (0.89 sec)Insert records in the table using insert command −mysql> insert into DemoTable(Number1, Number2) values(67, 45); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(Number1, Number2) values(90, 40); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(Number1, Number2) values(80, 43); Query OK, 1 row affected (0.48 sec)Display all records from the table using select ... Read More

To modify an existing column’s data type, you can use MODIFY. Let us first create a table −mysql> create table DemoTable    (    ClientId varchar(100),    ClientName varchar(100),    ClientAge int,    ClientProjectDeadline timestamp,    ClientCountryName varchar(100),    isMarried boolean,    ClientNumber bigint    ); Query OK, 0 rows affected (0.70 sec)Check the description of table −mysql> desc DemoTable;This will produce the following output −+-----------------------+--------------+------+-----+---------+-------+ | Field                 | Type         | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+-------+ | ClientId             ... Read More

You can use UPDATE with DATE_ADD() to update all dates. Let us first create a table −mysql> create table DemoTable    (    ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ClientProjectDueDate date    ); Query OK, 0 rows affected (1.19 sec)Insert records in the table using insert command −mysql> insert into DemoTable(ClientProjectDueDate) values('2018-01-21'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(ClientProjectDueDate) values('2019-03-25'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(ClientProjectDueDate) values('2013-11-01'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(ClientProjectDueDate) values('2015-06-14'); Query OK, 1 row affected (0.23 sec)Display all records from ... Read More

You can use a CASE statement for this. Let us first create a table −mysql> create table DemoTable    (    Number int    ); Query OK, 0 rows affected (0.71 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(490); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable values(310); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(540); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(123); Query OK, 1 row affected (0.60 sec) mysql> insert into DemoTable values(1230); Query OK, 1 row affected (0.15 sec) mysql> ... Read More

You can use COUNT(*) along with GROUP BY for this. Let us first create a table −mysql> create table DemoTable    (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentAge int    ); Query OK, 0 rows affected (0.59 sec)Insert records in the table using insert command −mysql> insert into DemoTable(StudentAge) values(16); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(StudentAge) values(17); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(StudentAge) values(18); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentAge) values(17); Query OK, 1 row affected (0.13 sec) mysql> insert into ... Read More

Let us first create a table −mysql> create table DemoTable    (    ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ClientName varchar(30)    ); Query OK, 0 rows affected (0.74 sec)Insert records in the table using insert command −mysql> insert into DemoTable(ClientName) values('John'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(ClientName) values('Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(ClientName) values('Robert'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(ClientName) values('David'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(ClientName) values('Bob'); Query OK, 1 row affected (0.15 sec) mysql> ... Read More

You can use STR_TO_DATE() function. Let us first create a table −mysql> create table DemoTable    (    AdmissionDate varchar(200)    ); Query OK, 0 rows affected (1.19 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('12-01-2019'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('14-12-2016'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('26-04-2018'); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable values('31-05-2013'); Query OK, 1 row affected (0.30 sec)Display all records from the table using select statement −mysql> select * from DemoTable;This will produce the ... Read More