Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 359 of 547
How to prevent MySQL GROUP BY from collapsing NULL values into a single row?
Fir this, you can use IFNULL() along with ORDER BY clause. Let us first create a table table −mysql> create table DemoTable1511 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> FirstName varchar(20) -> ); Query OK, 0 rows affected (1.97 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1511(FirstName) values('John'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1511(FirstName) values('Robert'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable1511(FirstName) values('Mike'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1511(FirstName) values('Robert'); Query OK, 1 ...
Read MoreMySQL update datetime column values and add 10 years 3 months 22 days and 10 hours, 30 minutes to existing data?
For this, you can use INTERVAL in MySQL. Let us first create a table −mysql> create table DemoTable1509 -> ( -> ArrivalTime datetime -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1509 values('2018-01-21 10:20:30'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1509 values('2019-04-01 11:00:00'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1509 values('2015-12-12 05:45:20'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select * from DemoTable1509;This will produce the following output ...
Read MoreShow a MySQL user-defined variables values in the result table?
Use @ for variable and concat_ws() to display concatenated result in the table. Let us first create a table −mysql> create table DemoTable1508 -> ( -> StudentFirstName varchar(20), -> StudentLastName varchar(20) -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1508 values('Chris', 'Brown'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1508 values('David', 'Miller'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1508 values('John', 'Doe'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> ...
Read MoreHow to make MySQL display results in a single line?
For this, you can use group_concat(). Let us first create a table −mysql> create table DemoTable1507 -> ( -> Name varchar(20), -> PaperSet int -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1507 values('Chris', 111); Query OK, 1 row affected (0.37 sec) mysql> insert into DemoTable1507 values('David', 112); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1507 values('Mike', 111); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1507 values('Bob', 113); Query OK, 1 row affected (0.14 sec)Display all records from ...
Read MoreRemove space between two words in MySQL?
For this, you can use REPLACE(). Let us first create a table −mysql> create table DemoTable1506 -> ( -> Title text -> ); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1506 values('This is MySQL'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1506 values('This is Java language'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1506 values('This is MongoDB NoSQL database'); Query OK, 1 row affected (0.60 sec)Display all records from the table using select statement −mysql> select * from DemoTable1506;This will ...
Read MoreHow do I force the column alias to be of specific data type in MySQL?
For this, you can use CASE statement. Let us first create a table −mysql> create table DemoTable1505 -> ( -> Value integer unsigned, -> Status tinyint(1) -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1505 values(20, 0); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1505 values(45, 1); Query OK, 1 row affected (0.08 sec)Display all records from the table using select statement −mysql> select * from DemoTable1505;This will produce the following output −+-------+--------+ | Value | Status | +-------+--------+ | ...
Read MoreSwap a specific column value in MySQL
Let us first create a table table −mysql> create table DemoTable1504 -> ( -> Id int, -> FirstName varchar(20) -> ); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1504 values(101, 'Chris'); Query OK, 1 row affected (0.63 sec) mysql> insert into DemoTable1504 values(102, 'Bob'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable1504 values(103, 'David'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1504 values(104, 'Mike'); Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement ...
Read MorePassing NULL to MySQL for auto increment?
Yes, we can pass NULL as in the below syntax −insert into yourTableName values(NULL, yourValue1, yourValue2, ...N);Let us first create a table −mysql> create table DemoTable1503 -> ( -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientName varchar(20), -> ClientAge int -> ); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using insert command. Since we have set NOT NULL above, it won’t affect auto_increment −mysql> insert into DemoTable1503 values(NULL, 'Chris', 25); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1503 values(NULL, 'David', 28); Query OK, 1 row ...
Read MoreMySQL query for sorting on a columns partial value like number in "John_120
For this, you can use SUBSTRING_INDEX() along with ORDER BY. Let us first create a table −mysql> create table DemoTable1502 -> ( -> StudentId varchar(40) -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1502 values('John_120'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1502 values('John_201'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1502 values('Mike_178'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1502 values('Bob_198'); Query OK, 1 row affected (0.36 sec)Display all records from the table using select ...
Read MoreMySQL query to select multiple rows effectively?
You need to use index to select multiple rows effectively. Let us first create a table −mysql> create table DemoTable1501 -> ( -> Id int NOT NULL PRIMARY KEY, -> URL text -> ); Query OK, 0 rows affected (0.62 sec)Here is the query to create index −mysql> create index id_index on DemoTable1501(Id); Query OK, 0 rows affected (0.23 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1501 values(101, 'www.facebook.com'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1501 values(110, 'www.google.com'); Query OK, 1 ...
Read More