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
Articles on Trending Technologies
Technical articles with clear explanations and examples
MySQL query to return the entire date and time based on a string and format
Let us first create a table −mysql> create table DemoTable -> ( -> AdmissionDate varchar(100) -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Wed, 19 Jun 2019 04:10:20'); Query OK, 1 row affected (0.22 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+---------------------------+ | AdmissionDate | +---------------------------+ | Wed, 19 Jun 2019 04:10:20 | +---------------------------+ 1 row in set (0.00 sec)Following is ...
Read MoreCreate a table named "select" in SQL databases?
Since “select” is a reserved word in MySQL, we cannot create a table name with it. But, if you still want to create such a table, surround the word select with quote.Let us first create a table −mysql> create table `select` -> ( -> Number int -> ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command −mysql> insert into `select` values(10); Query OK, 1 row affected (0.16 sec) mysql> insert into `select` values(20); Query OK, 1 row affected (0.11 sec) mysql> insert into `select` values(30); Query OK, 1 row affected (0.12 ...
Read MoreHow do you append a carriage return to a value in MySQL?
You need to use CONCAT_WS() function from MySQL to append a carriage return. If you are looking for a new line, then append in the beginning. The syntax is as follows −SELECT CONCAT_WS(‘’, yourColumnName) as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table NewLineDemo -> ( -> CountryName varchar(10) -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into NewLineDemo values('US'); Query OK, 1 row affected (0.15 sec) ...
Read MoreHow to add subtotal to a table column displaying NULL in MySQL?
Let us first create a table −mysql> create table DemoTable -> ( -> Amount int, -> SubTotal int -> ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −ysql> insert into DemoTable(Amount) values(50); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Amount) values(60); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(Amount) values(70); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Amount) values(80); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce ...
Read MoreHow to set default Field Value in MySQL?
To set default field value, use the “default”. Let us first create a table −mysql> create table DemoTable -> ( -> Age int -> ); Query OK, 0 rows affected (0.58 sec)Here is the query to set default field value in MySQL −mysql> alter table DemoTable MODIFY Age int default 18; Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0Now you can check the table description −mysql> desc DemoTable;OutputThis will produce the following output −+-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | Age ...
Read MoreHow to select everything before @ in an email-id with in MySQL?
Use SUBSTRING_INDEX to select everything before @ in an email-id −select substring_index(yourColumnName, '@', 1) from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> EmployeeMailId varchar(100) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Larry123@gmail.com'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('987Sam@hotmail.com'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('123456David_98@gmail.com'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the ...
Read MoreHow to add current date to an existing MySQL table?
To update an existing table, use UPDATE. With that, to set the current date, use the CURDATE() method −update yourTableName set yourCoumnName=CURDATE();Let us first create a table −mysql> create table DemoTable -> ( -> DueDate datetime -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-10') ; Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('2019-03-31'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+---------------------+ | DueDate ...
Read MoreHow to get row count of two tables in different databases in a single query?
For this, you can use aggregate function COUNT(*). Let us first create a table in let’s say database “web” −mysql> create table DemoTable1 -> ( -> Value int -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(10); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1 values(20); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;OutputThis will produce the following output −+-------+ | Value | +-------+ | 10 | | ...
Read MoreHow to order an alphanumeric column in MySQL?
To order an alphanumeric column with values like “100X, “2Z”, etc. use the ORDER BY. Let us first create a table −mysql> create table DemoTable -> ( -> StudentId varchar(100) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2X'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('100Y'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('100X'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('2Z'); Query OK, 1 row affected (0.14 sec) mysql> ...
Read MoreSystem variables vs Local Variables in MySQL?
The local variable has the scope for only a set of statements or block of statement. Whenever a set of statements or block of statement has completed then local variable goes out of scope.For ExampleLocal variable can be used in stored procedure, function etc. It is used with the DECLARE keyword.The syntax is as follows to local variables.DECLARE yourVariableName dataType;The global variables or system variables has the scope across connections until server restart. It is set using GLOBAL keyword. The syntax is as follows −SET GLOBAL max_connections=value; OR SET @@global.max_connections=value;If you know port number then you can use system variable ...
Read More