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
MySQLi Articles
Page 37 of 341
Add a new column and set values in it on the basis of conditions in MySQL?
To set values on the basis of conditions, use IF() method. Let us first create a table −mysql> create table DemoTable -> ( -> Age int -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(19); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(16); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(17); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(22); Query OK, 1 row affected (0.19 sec)Display all records from ...
Read MoreHow to order by timestamp in MySQL?
To order by timestamp, use the ORDER BY as in the following syntax −select *from yourTableName ORDER BY STR_TO_DATE(`yourColumnName`, '%m/%d/%Y%h:%i:%s %p');Let us first create a table −mysql> create table DemoTable -> ( -> `timestamp` varchar(100) -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('06/22/2019 01:10:20 PM'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('06/22/2019 12:00:27 PM'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values('06/22/2019 06:56:20 AM'); Query OK, 1 row affected (0.23 sec) ...
Read MoreFetch a single ordered date from a column with MySQL LIMIT
To fetch a single date from a column, use “LIMIT 1. To order it, use ORDER BY clause. Let us first create a table −mysql> create table DemoTable -> ( -> DueDate varchar(100) -> ); Query OK, 0 rows affected (1.16 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10-06-2019'); Query OK, 1 row affected (0.42 sec) mysql> insert into DemoTable values('01-12-2016'); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable values('31-01-2018'); Query OK, 1 row affected (0.58 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis ...
Read MoreHow to update only day portion of MySQL Date?
Let us first create a table −mysql> create table DemoTable -> ( -> AdmissionDate date -> ); Query OK, 0 rows affected (1.38 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-05-12'); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable values('2019-05-18'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('2019-04-19'); Query OK, 1 row affected (0.42 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+---------------+ | AdmissionDate | +---------------+ | 2019-05-12 | | 2019-05-18 ...
Read MoreUpdate column data without using temporary tables in MySQL?
For this, use CASE statement. This will work even without using temporary tables. Let us first create a table −mysql> create table DemoTable -> ( -> UserName varchar(100), -> UserStatus varchar(100) -> ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', 'Active'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values('Chris', 'Inactive'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Bob', 'Inactive'); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable values('Robert', 'Active'); ...
Read MoreGet the maximum count of distinct values in a separate column with MySQL
Use COUNT() function along with GROUP BY clause for this. Let us first create a table −mysql> create table DemoTable -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Mike'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('John'); Query ...
Read MoreTruncate results in decimal to integer value with MySQL
Use truncate() for this, for example, 190.245 to 190. Following is the syntax −select truncate(yourColumnName, 0) from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> Value DECIMAL(10, 4) -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(45.567); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(100.0000); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(15.89000); Query OK, 1 row affected (0.25 sec)Display all records from the table using select statement −mysql> ...
Read MoreSelecting records within a range and with a condition set on two columns in MySQL?
For this, use where clause. Let us first create a table −mysql> create table DemoTable -> ( -> Number1 int, -> Number2 int -> ); Query OK, 0 rows affected (3.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(40, 50); Query OK, 1 row affected (0.60 sec) mysql> insert into DemoTable values(100, 59); Query OK, 1 row affected (0.56 sec) mysql> insert into DemoTable values(400, 500); Query OK, 1 row affected (0.40 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+---------+---------+ | ...
Read MoreHow to perform update in MySQL to disallow incrementing all the values above a specific value?
Let us first create a table −mysql> create table DemoTable -> ( -> Value int -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(150); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(180); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(200); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+-------+ | Value ...
Read MoreFind out the records of students with more than a specific score in MySQL?
Set it with WHERE and get the records of students more than a specific score. Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(100), -> StudentScore int -> ); Query OK, 0 rows affected (1.65 secInsert some records in the table using insert command −mysql> insert into DemoTable(StudentName, StudentScore) values('John', 43); Query OK, 1 row affected (0.81 sec) mysql> insert into DemoTable(StudentName, StudentScore) values('Sam', 48); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(StudentName, StudentScore) values('Chris', 33); ...
Read More