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
MySQL Articles
Page 109 of 355
Set 'alias' for all the column names in a single MySQL query
To set alias for column names, the syntax is as follows −select yourColumnName1 anyAliasName1, yourColumnName2 anyAliasName2 from yourTableName anyAliasName;To understand the above syntax, let us create a table −mysql> create table DemoTable2014 -> ( -> FirstName varchar(20), -> LastName varchar(20) -> ); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2014 values('John', 'Smith'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable2014 values('David', 'Miller'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable2014 values('John', 'Doe'); Query OK, ...
Read MoreSet MySQL select in a custom variable
Let us first create a table −mysql> create table DemoTable2013 -> ( -> Name varchar(20) -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2013 values('Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2013 values('David'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2013 values('Mike'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable2013 values('Sam'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable2013 values('Bob'); Query OK, 1 row affected (0.12 sec)Display all ...
Read MoreFormat date while inserting records in MySQL
To format date while inserting records, use DATE_FORMAT() in the MySQL INSERT statement. Let us first create a table −mysql> create table DemoTable2012 -> ( -> ShippingDate varchar(20) -> ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2012 values(date_format(curdate(), '%d.%m.%Y')); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2012 values(date_format(now(), '%d.%m.%Y')); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2012 values(date_format('2014-01-21', '%d.%m.%Y')); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from ...
Read MoreHow to add a specific character to any empty space in MySQL table values?
For this, use REPLACE() function and replace empty space with the character. Let us first create a table −mysql> create table DemoTable (Subject text); Query OK, 0 rows affected (0.86 sec)ExampleInsert some records in the table using insert command −mysql> insert into DemoTable values('Introduction to MySQL'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Java in depth'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('Data Structure and Algorithm'); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+------------------------------+ | Subject | +------------------------------+ | Introduction ...
Read MoreMySQL query to select records from a table on the basis of a particular month number?
You can select specific month with the help of MONTH() function. The syntax is as follows −SELECT yourColumnName FROM yourTableName WHERE MONTH(yourColumnName) = yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UserLoginTimeInformation -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserLoginDatetime datetime -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UserLoginTimeInformation(UserLoginDatetime) values(date_add(now(), interval 3 month)); Query OK, 1 row affected (0.14 sec) ...
Read MoreWhat happens when a negative value is inserted to UNSIGNED column in MySQL?
Error occurs when you set a negative value to UNSIGNED column in MySQL. For example, let us first create a table with an UNSIGNED field −mysql> create table UnsignedDemo -> ( -> Id int UNSIGNED -> ); Query OK, 0 rows affected (0.79 sec)The error is as follows whenever you insert negative value to column Id which is declared as UNSIGNED −mysql> INSERT INTO UnsignedDemo VALUES(-100); ERROR 1264 (22003): Out of range value for column 'Id' at row 1ExampleHowever, positive values work well for UNSIGNED. The same is shown in the example below. Insert some records in ...
Read MoreSelect and filter the records on month basis in a MySQL table?
You can use aggregate function SUM() with GROUP BY clause to achieve this.Let us create a table. The query to create a table is as follows −mysql> create table SelectPerMonthDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Price int, -> PurchaseDate datetime -> ); Query OK, 0 rows affected (2.34 sec)ExampleInsert some records in the table using insert command with one of them would be the date of purchase. The query is as follows −mysql> insert into SelectPerMonthDemo(Price, PurchaseDate) values(600, date_add(now(), interval -1 month)); Query OK, 1 row affected (0.42 sec) ...
Read MoreHow do I re-format datetime in MySQL?
To re-format datetime in MySQL, you can use DATE_FORMAT(). MySQL gives in the format yyyy-mm-dd.Let us first create a table −mysql> create table DemoTable1558 -> ( -> EmployeeJoiningDate datetime -> ); Query OK, 0 rows affected (1.10 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1558 values(CURDATE()); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1558 values(NOW()); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1558 values('2018-01-10'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1558 values('2017-12-31'); Query OK, 1 row affected (0.18 sec)Display all records from ...
Read MoreHow can we create a MySQL view by using data from multiple tables?
MySQL UNION operator can combine two or more result sets hence we can use UNION operator to create a view having data from multiple tables. To understand this concept we are using the base tables ‘Student_info’ and ‘Student_detail’ having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 125 | Raman | Shimla | Computers | | 130 | Ram ...
Read MoreSelect equal or nearest greater number from table in MySQL
Let us first create a table −mysql> create table DemoTable -> ( -> Value int -> ); Query OK, 0 rows affected (1.33 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(25); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(75); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select * from DemoTable;This will produce the following output −+-------+ | Value | +-------+ | 25 | | ...
Read More