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 143 of 341
Finding total number of rows of tables across multiple databases in MySQL?
To fetch total number of table rows across databases, use aggregate function SUM() along with INFORMATION SCHEMA. Let us first create a table, which is in “web” database −mysql> create table DemoTable1568 -> ( -> Name varchar(20) -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1568 values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1568 values('Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1568 values('David'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select ...
Read MoreGetting the last 30 rows in MySQL
To get the last 30 rows in MySQL, you need to use ORDER BY DESC and then LIMIT 30. The syntax is as follows −select * from yourTableName order by yourColumnName DESC LIMIT 30;Let us first create a table −mysql> create table DemoTable1567 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY -> ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1567 values(), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), ...
Read MoreUsing CASE statement in MySQL to display custom name for empty value
For this, you can use CASE WHEN statement. Let us first create a table −mysql> create table DemoTable -> ( -> Name varchar(20) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(''); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(''); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> ...
Read MoreMySQL query to display custom text for empty columns
Let us first create a table −mysql> create table DemoTable -> ( -> FirstName varchar(20) -> ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(''); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Bob'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(''); Query OK, 1 row affected (0.13 sec)Display all records from the table ...
Read MoreWrap around to first value and implement MySQL ORDER BY ASC and DESC in a single query
Let us first create a table −mysql> create table DemoTable -> ( -> Value int -> ); Query OK, 0 rows affected (3.21 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.78 sec) mysql> insert into DemoTable values(40); Query OK, 1 row affected (0.94 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.41 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values(90); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(70); ...
Read MoreFind second max in a table using MySQL query?
You can use LIMIT 1 OFFSET 1. Let us first create a table −mysql> create table DemoTable -> ( -> Value int -> ); Query OK, 0 rows affected (0.92 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(2); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(4); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(204); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(5); Query OK, 1 row affected ...
Read MoreMySQL TINYINT type to return <>1 or IS NULL records
Let us first create a table −mysql> create table DemoTable -> ( -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY , -> EmployeeName varchar(20), -> isMarried tinyint -> ); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeName, isMarried) values('Chris', NULL); Query OK, 1 row affected (0.76 sec) mysql> insert into DemoTable(EmployeeName, isMarried) values('David', 1); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable(EmployeeName, isMarried) values('Mike', 0); Query OK, 1 row affected (0.69 sec) mysql> insert into DemoTable(EmployeeName, isMarried) values('Sam', NULL); Query OK, ...
Read MoreERROR 1064 (42000): You have an error in your SQL syntax at zero fill column?
Following is the error and it occurs when you implement ZEROFILL incorrectly−mysql> create table DemoTable -> ( -> StudentCode int(10) NOT NULL ZEROFILL AUTO_INCREMENT PRIMARY KEY -> ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ZEROFILL AUTO_INCREMENT PRIMARY KEY )' at line 3For correct implementation, use the below syntax −SyntaxyourColumnName int(10) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEYLet us first create a table −mysql> create table DemoTable -> ( -> StudentCode int(10) ZEROFILL NOT NULL ...
Read MoreFind integer in text data (comma separated values) with MySQL?
Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> DoubleValue varchar(20) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(DoubleValue) values('80.2, 90.5, 88.90'); Query OK, 1 row affected (0.44 sec) mysql> insert into DemoTable(DoubleValue) values('78.56, 45.80, 88, 45.6'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(DoubleValue) values('12.34, 90.06, 89.90'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will ...
Read MoreHow to sum a comma separated string (string with numbers) in MySQL?
You can create a custom function to sum a comma-separated string in MySQL. Let us first create a table. Here, we have a varchar column, wherein we will add numbers in the form of strings −mysql> create table DemoTable -> ( -> ListOfValues varchar(50) -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('20, 10, 40, 50, 60'); 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 output −+----------------+ | ListOfValues ...
Read More