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 214 of 341
ORDER BY a specific word in MySQL
For this, use ORDER BY INSTR(). Let us first create a table −mysql> create table DemoTable822(Word text); Query OK, 0 rows affected (1.11 sec)Insert some records in the table using insert command −mysql> insert into DemoTable822 values('Forever'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable822 values('ever'); Query OK, 1 row affected (1.31 sec) mysql> insert into DemoTable822 values('every'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable822 values('everyday'); Query OK, 1 row affected (0.58 sec)Display all records from the table using select statement −mysql> select *from DemoTable822;This will produce the following output −+----------+ | Word ...
Read MoreMySQL query to exclude values having specific last 3 digits
For this, use NOT IN. Let us first create a table −mysql> create table DemoTable(Value int); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1234); Query OK, 1 row affected (0.54 sec) mysql> insert into DemoTable values(2345); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(7896); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(4321); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-------+ | Value | ...
Read MoreMySQL query to set current date in the datetime field for all the column values
Let us first create a table −mysql> create table DemoTable821(AdmissionDate datetime); Query OK, 0 rows affected (1.24 sec)Insert some records in the table using insert command −mysql> insert into DemoTable821 values('2019-01-21'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable821 values('2018-11-02'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable821 values('2016-12-31'); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable821 values('2015-03-19'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable821;This will produce the following output −+---------------------+ | AdmissionDate ...
Read MoreHow to limit records to only the last five results in MySQL
To fetch only the last five records below is the syntax −select *from yourTableName order by yourColumnName DESC LIMIT 5;Let us first create a table −mysql> create table DemoTable820( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, CustomerName varchar(100) ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable820(CustomerName) values('Chris'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable820(CustomerName) values('Robert'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable820(CustomerName) values('David'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable820(CustomerName) values('Bob'); Query OK, 1 ...
Read MoreCheck user rights before attempting to CREATE MySQL DATABASE?
To display grants, the syntax is as follows −show grants for yourUserName;Let us implement the above syntax in order to check user rights before attempting to create database.Case 1 −This is for current user which is root. Following is the syntax −mysql> show grants for current_user;This will produce the following output −+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@% ...
Read MoreHow to select from table where conditions are set for id and name in MySQL?
Let us first create a table −mysql> create table DemoTable819( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100) ); Query OK, 0 rows affected (0.88 sec)Insert some records in the table using insert command −mysql> insert into DemoTable819(StudentName) values('Chris'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable819(StudentName) values('Robert'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable819(StudentName) values('Adam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable819(StudentName) values('Mike'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable819(StudentName) values('Sam'); Query OK, 1 row affected (0.16 sec)Display all records from ...
Read MoreMySQL query to convert YYYY-MM-DD to DD Month, YYYY date format
Let us first create a table −mysql> create table DemoTable845(AdmissionDate date); Query OK, 0 rows affected (1.10 sec)Insert some records in the table using insert command −mysql> insert into DemoTable845 values('2018-01-21'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable845 values('2016-12-12'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable845 values('2019-08-05'); Query OK, 1 row affected (0.44 sec) mysql> insert into DemoTable845 values('2019-10-15'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable845;This will produce the following output −+---------------+ | AdmissionDate | +---------------+ | 2018-01-21 | ...
Read MoreCan we convert MD5 to SHA256 in a MySQL table with user password column?
Use SHA2() to convert the MD5 password to SHA256. It calculates the SHA-2 family of hash functions i.e. SHA-224, SHA-256, SHA-384, and SHA-512).Let us first create a table −mysql> create table DemoTable818(UserPassword text); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable818 values(MD5('John_123')); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable818 values(MD5('999Carol@22')); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable818;This will produce the following output −+----------------------------------+ | UserPassword ...
Read MoreHow to display only the column values whose sum is less than 150 in MySQL? Arrange the result in descending order
For this, you can use subquery. Let us first create a table −mysql> create table DemoTable844( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Amount int ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command −mysql> insert into DemoTable844(Amount) values(80); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable844(Amount) values(100); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable844(Amount) values(60); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable844(Amount) values(40); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable844(Amount) values(150); Query OK, 1 row ...
Read MoreSet value only for NULL values in a MySQL table
Use IFNULL to check for NULL values and set a value using the SET command. Let us first create a table −mysql> create table DemoTable817(Value int); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable817 values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable817 values(null); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable817 values(20); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable817 values(null); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from ...
Read More