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
-
Economics & Finance
MySQLi Articles
Page 41 of 341
How to use a single MySQL query to count column values ignoring null?
For this, you can COUNT() method, which does not include NULL value. Let us first create a table −mysql> create table DemoTable -> ( -> Name varchar(100), -> CountryName varchar(100) -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', null); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Chris', 'US'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Robert', null); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Bob', 'UK'); Query ...
Read MoreImplement MySQL LIMIT and OFFSET in a single query stating its difference
The LIMIT tells about how many records you want while OFFSET gives the records from the given position+1. Let us first create a table −mysql> create table DemoTable -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (1.33 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('Bob'); Query OK, 1 ...
Read MoreHow to extract the digit part from the string in MySQL?
Let us first create a table −mysql> create table DemoTable -> ( -> StudentId varchar(100) -> ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John19383'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Carol9999'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('David123456'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+-------------+ | StudentId | +-------------+ | John19383 ...
Read MoreSet a certain value first with MySQL ORDER BY?
Let us first create a table −mysql> create table DemoTable -> ( -> Number int -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(12); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(14); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(19); Query OK, 1 row affected (0.14 sec) ...
Read MoreHow to use special characters in column names with MySQL?
Using backticks around the column name will allow you to use special characters. Let us first create a table −mysql> create table DemoTable -> ( -> `Student-Id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> `Student-Name` varchar(100), -> `Student-Age` int -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(`Student-Name`, `Student-Age`) values('Chris', 21); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(`Student-Name`, `Student-Age`) values('Mike', 19); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(`Student-Name`, `Student-Age`) values('Bob', 18); Query OK, ...
Read MoreFetch middle part of a string surrounded by slash in MySQL
Let us first create a table −mysql> create table DemoTable -> ( -> Code varchar(100) -> ); Query OK, 0 rows affected (1.07 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('/101/102/106'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('/110/111/101'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('/111/114/201'); Query OK, 1 row affected (0.47 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+--------------+ | Code | +--------------+ | ...
Read MoreCan we use semicolon as a MySQL DEMILITER?
No, we cannot. If you still did it, then stored procedure won’t get created. Therefore, first you need to change your DELIMITER from semicolon(;) to others like (// ,??..etc). Following is the syntax −DELIMITER // CREATE PROCEDURE yourProcedureName() BEGIN yourStatement1, . . . . N END // DELIMITER ;Let us implement the above syntax in order to create a stored procedure −mysql> DELIMITER // mysql> CREATE PROCEDURE get_Message() -> BEGIN -> SELECT CONCAT("HELLO", " ", "MYSQL USERS"); -> END -> // Query OK, 0 rows affected (0.23 sec) mysql> DELIMITER ;Now you can call the stored ...
Read MoreCan we use PRIMARY KEY( column1, column2) in MySQL to make pairs?
Yes, you can use below syntax. Following is the syntax −PRIMARY KEY(yourColumnName1, yourColumnName2);Let us first create a table −mysql> create table DemoTable -> ( -> StudentFirstName varchar(100), -> StudentLastName varchar(100), -> StudentAge int, -> StudentCountryName varchar(100), -> PRIMARY KEY(StudentFirstName, StudentLastName) -> ); Query OK, 0 rows affected (0.74 sec)Let us check the description of the table −mysql> desc DemoTable;OutputThis will produce the following output −+--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+-------+ | StudentFirstName | ...
Read MoreImplement multiple COUNT() in a single MySQL query
For this, use CASE statement. Let us first create a table −mysql> create table DemoTable -> ( -> LastName varchar(100) -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Smith'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Brown'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Brown'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Smith'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Taylor'); Query OK, 1 ...
Read MoreHow to exclude a specific row from a table in MySQL?
Use i.e. not equal in MySQL to exclude a specific row from a table. Let us first create a table −mysql> create table DemoTable -> ( -> Id int, -> FirstName varchar(100) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1, 'John'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(2, 'Chris'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(3, 'Robert'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable ...
Read More