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
MySQL Articles
Page 192 of 355
MySQL query to replace a column value
Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, Score int ); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Score) values(56); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable(Score) values(78); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable(Score) values(34); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(Score) values(55); Query OK, 1 row affected (0.37 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce ...
Read MoreMySQL query to fetch records where decimal is a whole number
For this, use FLOOR() function. Here, we will be fetching records like 12.00, 35.00, etc. from a list with records like 5.23, 8.76, 12.00, 22.68, etc. Let us first create a table −mysql> create table DemoTable ( Value DECIMAL(4, 2) ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(54.20); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(55.0); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable values(7.8); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(9.0); Query OK, ...
Read MoreMySQL query to delete a row if two columns are equal
Use DELETE for this. Let us first create a table −mysql> create table DemoTable ( Name varchar(40), Score1 int , Score2 int ); Query OK, 0 rows affected (2.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', 56, 76); Query OK, 1 row affected (0.66 sec) mysql> insert into DemoTable values('Chris', 77, 77); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('David', 89, 98); 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 ...
Read MoreHow to get the difference between date records and the current date in MySQL?
Let’s say the current date is 2019-09-06. For our example, we will first create a table −mysql> create table DemoTable ( AdmissionDate date ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-08'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('2018-09-06'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('2016-10-26'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+---------------+ | AdmissionDate | +---------------+ | 2019-01-08 ...
Read MoreUpdate a column of text with MySQL REPLACE()
Let us first create a table −mysql> create table DemoTable ( Code varchar(100) ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('8565-9848-7474'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('9994-6464-8737'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('6574-9090-7643'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----------------+ | Code | +----------------+ | 8565-9848-7474 | | 9994-6464-8737 | | ...
Read MoreHow to convert MySQL DATETIME value to JSON format in JavaScript?
To convert, use JSON.stringify(). Following is the code to convert MySQL DATETIME value to JSON format in JavaScript − var mySQLDateTime = new Date("Fri Sep 06 2019 22 −54 −48 "); var yearValue = mySQLDateTime.getFullYear(); var dateValue = mySQLDateTime.getDate(); var monthValue=mySQLDateTime.getMonth(); var hour=mySQLDateTime.getHours(); var minutes=mySQLDateTime.getMinutes(); var second=mySQLDateTime.getSeconds(); jsonObject={"year" −yearValue,"month" :monthValue,"DateValue" :dateValue,"Hour" :hour ,"Minutes" :minutes,"Second" :second}; var dateJsonObject = JSON.stringify(jsonObject); document.write(dateJsonObject); The screenshot of the code is as follows −This will produce the following output −{"year" :2019,"month" :8,"DateValue" :6,"Hour" :22,"Minutes" :54,"Second" :48}The snapshot of the output is as follows −
Read MoreMySQL query to return a string as a result of IF statement?
Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, EmployeeSalary int ); Query OK, 0 rows affected (1.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeSalary) values(12000); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable(EmployeeSalary) values(20000); Query OK, 1 row affected (0.55 sec) mysql> insert into DemoTable(EmployeeSalary) values(11500); Query OK, 1 row affected (0.94 sec) mysql> insert into DemoTable(EmployeeSalary) values(15500); Query OK, 1 row affected (0.44 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce ...
Read MoreHow to update column values with date records and set 1 for corresponding records before the current date in SQL
Let’s say the current date is 2019-08-20. Now for our example, we will create a table −mysql> create table DemoTable ( ProductStatus tinyint(1), ProductExpiryDate date ); Query OK, 0 rows affected (1.03 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(0, '2019-06-12'); Query OK, 1 row affected (0.43 sec) mysql> insert into DemoTable values(0, '2019-10-11'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable values(0, '2018-07-24'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(0, '2018-09-05'); Query OK, 1 row affected (0.27 sec)Display all records from the table ...
Read MoreMySQL query for grouping and summing the values based on specific records
Use GROUP BY to group records, whereas SUM() function is used to add. Let us first create a table −mysql> create table DemoTable ( Name varchar(40), Subject varchar(40), Marks int ); Query OK, 0 rows affected (2.89 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 'MySQL', 76); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable values('Sam', 'MongoDB', 86); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable values('Mike', 'MySQL', 98); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable values('David', 'Java', 93); Query OK, ...
Read MoreUpdate the table by calculating the sum and display the result as last column value
Use a variable to store SUM(total) and update it with the UPDATE command. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value int ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value) values(70); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(Value) values(100); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Value) values(150); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Value) values(250); Query OK, 1 row affected (0.14 sec) mysql> ...
Read More