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 180 of 355
How to sort an alphanumeric column with different lengths in MySQL?
Let us first create a table −mysql> create table DemoTable1623 -> ( -> StudentCode varchar(20) -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1623 values('STU-MIT-143'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1623 values('STU-MIT-10'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1623 values('STU-MIT-150'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1623 values('STU-MIT-148'); Query OK, 1 row affected (0.22 sec)Display all records from the table using select statement −mysql> select * from DemoTable1623; This ...
Read MoreHow to search for the exact string value in MySQL?
To search for the exact string value, use the concept of COLLATE. Let us first create a table −mysql> create table DemoTable1620 -> ( -> Subject varchar(20) -> ); Query OK, 0 rows affected (0.42 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1620 values('mysql'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1620 values('MySql'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1620 values('mYSQL'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1620 values('MySQL'); Query OK, 1 row affected (0.26 sec) mysql> insert ...
Read MoreHow can I enhance my select query to make it faster in MySQL?
For quicker querying, use MySQL IN() because it uses indexing internally. Let us first create a table −mysql> create table DemoTable1618 -> ( -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientName varchar(20), -> ClientEmailId varchar(30) -> ); Query OK, 0 rows affected (1.53 sec)Insert some records in the table using insert command:mysql> insert into DemoTable1618(ClientName, ClientEmailId) values('Chris Brown', 'Brown323@gmail.com'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1618(ClientName, ClientEmailId) values('David Miller', 'MillerDavid@gmail.com'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1618(ClientName, ClientEmailId) values('John Doe', ...
Read MoreCreate a Stored Procedure with MySQL and set a limit to display only a specific number of records
Let us first create a table −mysql> create table DemoTable1368 -> ( -> ClientId int, -> ClientName varchar(20) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1368 values(101, 'Adam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1368 values(102, 'Bob'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1368 values(103, 'John'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1368 values(104, 'Sam'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1368 values(105, ...
Read MoreFind specific records which has whitespace on the second place in MySQL
For this, use SUBSTR() as in the below syntax −select * from yourTableName where substr(yourColumnName, 2, 1 ) = ' ';Let us first create a table −mysql> create table DemoTable1365 -> ( -> Value varchar(20) -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1365 values('9756757474'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1365 values('3 45322333'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1365 values('8974646363'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1365 values('9 ...
Read MoreUsing MySQL IN() for some column values with underscore
Let us first create a table −mysql> create table DemoTable1363 -> ( -> StudentId varchar(20) -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1363 values('901'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1363 values('702'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1363 values('901_John_Doe'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1363 values('1001_Carol_Taylor'); Query OK, 1 row affected (0.26 sec)Display all records from the table using select statement −mysql> select * from DemoTable1363;This will ...
Read MoreMySQL permissions to view all databases?
For this, the syntax is as follows −revoke show databases on *.* from 'yourUserName'@'yourHostName';Let us display all usernames along with host name −mysql> select user, host from MySQL.user;This will produce the following output −+------------------+-----------+ | user | host | +------------------+-----------+ | Bob | % | | Charlie | % | | Robert | % | | User2 | % ...
Read MoreCreate variables in MySQL stored procedure with DECLARE keyword
Use MySQL DECLARE for variables in stored procedure −DECLARE anyVariableName int DEFAULT anyValue;Let us implement the above syntax in order to create variables in stored procedure −mysql> DELIMITER // mysql> CREATE PROCEDURE variable_Demo() -> BEGIN -> DECLARE lastInsertedId int DEFAULT -1; -> select lastInsertedId; -> set @providedLastId=10001; -> select @providedLastId; -> END -> // Query OK, 0 rows affected (0.32 sec) mysql> DELIMITER ;Now you can call the above stored procedure using CALL command −mysql> call variable_Demo();This will produce the following output −+----------------+ | lastInsertedId | ...
Read MoreTreat a MySQL column field as NULL if it is blank?
Let us first create a table −mysql> create table DemoTable1362 -> ( -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientName varchar(40) -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1362(ClientName) values('Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1362(ClientName) values(' '); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable1362(ClientName) values('Bob'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1362(ClientName) values(' '); Query OK, 1 row affected (0.12 sec) mysql> insert ...
Read MoreDelete URLs with specific domains from MySQL database?
To delete URLs with specific domains, use DELETE and LIKE clause.Let us first create a table −mysql> create table DemoTable1361 -> ( -> URL text -> ) ; Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1361 values('Https://www.google.com//?id=1'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1361 values('Https://www.facebook.com//?id=2&name=John'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1361 values('Https://www.yahoo.com//?id=3'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable1361 values('Https://www.google.com//?id=1'); Query OK, 1 row affected (0.16 sec)Display all records from ...
Read More