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 177 of 341
Get Nth weekday of the month from a column with DATE records in MySQL
We need to find the weekday i.e. week 1 from date 1 to 7, week 2 from date 8 to 14, etc. To get the day, use DAY() function in MySQL. Set the conditions to get the weekday (number) using CASE statement.Let us now see an example and create a table −mysql> create table DemoTable ( AdmissionDate date ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-09-12'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('2019-09-06'); Query OK, 1 row affected (0.16 sec) mysql> ...
Read MoreHow can I select every alternative row and display in descending order in SQL?
To fetch every alternative row, use MOD() under WHERE. Then use ORDER BY DESC to display the result in descending order −select *from yourTableName where mod(yourColumnName, 2)=1 order by yourColumnName DESC;Let us first create a table −mysql> create table DemoTable ( UniqueId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(40), ClientAge int ); Query OK, 0 rows affected (1.02 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ClientName, ClientAge) values('Chris', 34); Query OK, 1 row affected (0.47 sec) mysql> insert into DemoTable(ClientName, ClientAge) values('Tom', 45); Query OK, 1 row affected (0.19 sec) mysql> ...
Read MoreSelect all email addresses beginning with 5 numeric characters (regular expression) in MySQL
To get the email addresses beginning with 5 numeric characters, the optional solution is to use REGEXP −select *from yourTableName where yourColumnName regexp "^[0-9]{5}";Let us first create a table −mysql> create table DemoTable ( UserEmailAddress varchar(100) ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('6574John@gmail.com'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Carol23456@gmail.com'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('98989Chris_45678@gmail.com'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Mike12@gmail.com'); Query OK, 1 row affected (0.43 ...
Read MoreGet specific value of cell in MySQL
Let us first create a table −mysql> create table DemoTable ( Name varchar(40), Score int ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris Brown', 78); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('John Doe', 88); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Carol Taylor', 98); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('David Miller', 80); Query OK, 1 row affected (0.68 sec)Display all records from the table using select statement −mysql> select *from ...
Read MoreUsing the value of an alias inside the same MySQL SELECT statement
You cannot directly use an alias in the SELECT. Instead, use a user-defined variable. Following is the syntax. Here, @yourAliasName is our variable and alias −select @yourAliasName :=curdate() as anyAliasName, concat(‘yourValue.', yourColumnName, ' yourValue', @yourAliasName) as anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable ( Name varchar(40) ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John Smith'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Chris Brown'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('David Miller'); ...
Read MoreAdd some value to an int type column value in a table without knowing its current value in SQL?
For this, simply use UPDATE command along with SET. Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentScore int ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentScore) values(78); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable(StudentScore) values(89); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(StudentScore) values(67); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(StudentScore) values(95); Query OK, 1 row affected (0.25 sec)Display all records from the table ...
Read MoreMySQL query to check if multiple rows exist?
Let us first create a table −mysql> create table DemoTable1219 ( Id int, Name varchar(40) ); Query OK, 0 rows affected (0.43 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1219 values(100, 'Adam'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1219 values(101, 'John'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1219 values(102, 'Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1219 values(103, 'Bob'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable1219;This will produce the ...
Read MoreIncrement multiple Timestamp values by setting the incremented value in a user-defined variable in SQL
The incremented value can be set in a user-defined variable as shown below. Here, “yourValue” is the incremented value. After that, use MySQL UPDATE to update the column and increment timestamp values −set @anyVariableName :=yourValue; update yourTableName set yourColumnName=yourColumnName+interval (@yourVariableName) second;Let us first create a table −mysql> create table DemoTable ( DueDatetime timestamp ); Query OK, 0 rows affected (0.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-31 12 :30 :40'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values('2019-09-06 10 :00 :00'); Query OK, 1 row affected (0.73 sec) ...
Read MoreHow to ORDER BY grouped fields in MySQL?
To ORDER BY grouped fields, use ORDER BY CASE along with IN(). CASE evaluates different conditions whereas ORDER BY sort values in ascending or descending order. The MySQL IN() is used to find a match.Let us first create a table −mysql> create table DemoTable ( Value varchar(40) ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('100&101'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.09 sec) ...
Read MoreHow to check if value exists with MySQL SELECT 1?
Use SELECT 1 for this as in the below syntax −select 1 from yourTableName where yourColumnName=yourValue;If the above returns 1, that means value exists in the MySQL database. Let us first see an example and create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(40), StudentAge int ); Query OK, 0 rows affected (0.46 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentName, StudentAge) values('Chris', 21); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(StudentName, StudentAge) values('David', 20); Query OK, 1 row affected (0.16 ...
Read More