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 247 of 355
Select last day of current year in MySQL?
In order to get last day of current year, you can use LAST_DAY() from MySQL. The syntax is as follows−SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 12-MONTH(CURDATE()) MONTH));Let us implement the above syntax to know the last day of current year−mysql> SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 12-MONTH(CURDATE()) MONTH));This will produce the following output −+-------------------------------------------------------------------+ | LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 12-MONTH(CURDATE()) MONTH)) | +-------------------------------------------------------------------+ | 2019-12-31 | +-------------------------------------------------------------------+ 1 row in set (0.00 sec)
Read MoreHow do I exclude a specific record in MySQL?
You can exclude a specific record in SQL using not equal to operator(!=). Let us first create a table−mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(20), ClientCountryName varchar(10) ); Query OK, 0 rows affected (0.64 sec)Insert records in the table using insert command −mysql> insert into DemoTable(ClientName, ClientCountryName) values('John', 'US'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(ClientName, ClientCountryName) values('David', 'AUS'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable(ClientName, ClientCountryName) values('Mike', 'UK'); Query OK, 1 row affected (0.14 sec)Display all records from the ...
Read MoreCan we get total number of rows in a MySQL database?
To get the total number of rows in a MySQL database, you can use aggregate function SUM() along with inbuilt column TABLE_ROWS from INFORMATION_SCHEMA.TABLES.The syntax is as follows−SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = database();Let’s say we are using the database with the name ‘sample’.Now we will get the total number of rows in a MySQL database−mysql> SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = database();This will produce the following output−+-----------------+ | SUM(TABLE_ROWS) | +-----------------+ | 2043 | +-----------------+ 1 row in set (22.11 sec)
Read MoreMySQL select to convert numbers to millions and billions format?
You can use FORMAT() from MySQL to convert numbers to millions and billions format. Let us first create a table−mysql> create table DemoTable ( Value BIGINT ); Query OK, 0 rows affected (0.74 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(78000000000); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(10000000000); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(90000000000); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(450600000000); Query OK, 1 row affected (0.41 sec)Display all records from the table using select statement ...
Read MoreHow to update MySQL column with random value?
To update column with random value, you can use the below syntax−update yourTableName set yourColumnName =round(1+rand()*100);The above syntax will generate a value between 1 to 100. Let us see an example and create a table−mysql> create table DemoTable ( Number int ); Query OK, 0 rows affected (0.46 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(100000); ...
Read MoreCan we use WHERE, AND & OR in a single MySQL query?
Yes, we can use all of them in a single query. Let us first create a table −mysql> create table DemoTable ( StudentId int, StudentFirstName varchar(20), StudentLastName varchar(20), StudentAge int ); Query OK, 0 rows affected (0.53 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(100, 'John', 'Smith', 23); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(101, 'Carol', 'Taylor', 24); Query OK, 1 row affected (0.62 sec) mysql> insert into DemoTable values(103, 'John', 'Doe', 22); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable ...
Read MoreMySQL query to divide column by 100?
Let us first create a table −mysql> create table DemoTable ( Number float ); Query OK, 0 rows affected (0.47 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(1000); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable values(390); Query OK, 1 row affected (0.09 sec)Display all records from the table using select statement −mysql> ...
Read MoreHow to select all distinct filename extensions from a table of filenames in MySQL?
You can use DISTINCT along with SUBSTRING_INDEX() to extract the filename extensions. Let us first create a table−mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FileName text ); Query OK, 0 rows affected (0.75 sec)Insert records in the table using insert command −mysql> insert into DemoTable(FileName) values('AddTwoValue.java'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(FileName) values('Image1.png'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(FileName) values('MultiplicationOfTwoNumbers.java'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(FileName) values('Palindrome.c'); Query OK, 1 row affected (0.16 sec) mysql> insert ...
Read MoreHow to get last 12 digits from a string in MySQL?
You can use RIGHT() function from MySQL to get the last 12 digits from a string. Let us first create a table −mysql> create table DemoTable ( Number varchar(200) ); Query OK, 0 rows affected (0.59 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('7437647847847474374747464647484949959958484'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('9990000399494959697080800007007070808080808'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('1211111212344554444443333345555554433333333333333'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select * from DemoTable;This will produce the following ...
Read MoreHow to create a database on command line in MySQL?
First, you need to open the command prompt. You can open using shortcut windows+R key.The screenshot is as follows −Now type CMD and press OK button −Now the following command prompt would be visible −Now reach the MySQL bin directory. The screenshot is as follows −Following is the query to run MySQL in the command line to create a database −Now you can check the database is created or not using SHOW DATABASES command −mysql> SHOW DATABASES;This will produce the following output −+---------------------------+ | Database | +---------------------------+ | bothinnodbandmyisam ...
Read More