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
Articles by AmitDiwan
Page 751 of 840
Quickly search for a string in MySQL database?
Use FULLTEXT search to quickly search for a string. Let us first create a table −mysql> create table DemoTable1554 -> ( -> Title text -> ); Query OK, 0 rows affected (0.63 sec)Here is the query to create full text search −mysql> create fulltext index faster_title on DemoTable1554(Title); Query OK, 0 rows affected, 1 warning (7.09 sec) Records: 0 Duplicates: 0 Warnings: 1Insert some records in the table using insert command −mysql> insert into DemoTable1554 values('John is working on MySQL database'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable1554 values('Adam Smith is working on ...
Read MoreDisplay an error while inserting duplicate records in a MySQL table
For this, you can use UNIQUE KEY. Let us first create a table −mysql> create table DemoTable1553 -> ( -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> EmployeeName varchar(20), -> EmployeeSalary int -> ); Query OK, 0 rows affected (0.47 sec)Here is the query to add unique key −mysql> alter table DemoTable1553 add unique(EmployeeSalary); Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1553(EmployeeName, EmployeeSalary) values('Chris', 45000); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1553(EmployeeName, EmployeeSalary) ...
Read MoreWhy does the update command in MySQL insist on using slanted single quotes?
Use single quotes on string input value. If there is an identifier like table name or column name, then do not use single quotes (use backticks).Let us first create a table −mysql> create table DemoTable1552 -> ( -> `key` int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1552 values(101, 'Chris'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1552 values(102, 'David'); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable1552 values(103, 'Mike'); Query OK, 1 row ...
Read MoreHow to select only 3 ordered rows on a MySQL table?
For this, you can use ORDER BY clause along with LIMIT. Let us first create a table −mysql> create table DemoTable1551 -> ( -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> EmployeeName varchar(20) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1551(EmployeeName) values('Chris'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable1551(EmployeeName) values('Robert'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1551(EmployeeName) values('Mike'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1551(EmployeeName) values('Sam'); Query OK, 1 row ...
Read MoreMaintain the custom order of the IDs passed in MySQL
To maintain the custom order of IDs, use ORDER BY CASE statement. Let us first create a table −mysql> create table DemoTable1550 -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1550 values(101, 'Chris'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1550 values(110, 'Bob'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable1550 values(105, 'Carol'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1550 values(109, 'Mike'); Query OK, 1 row ...
Read MoreMySQL query to sort by certain last string character?
For this, you can use the CASE statement. To sort, use the ORDER BY clause. Let us first create a table −mysql> create table DemoTable -> ( -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientName varchar(20) -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command. Some records have certain last string like -D, etc −mysql> insert into DemoTable(ClientName) values('Mike'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(ClientName) values('John'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable(ClientName) values('John-D'); Query OK, 1 ...
Read MoreMySQL query to fetch specific records matched from an array (comma separated values)
To fetch records from comma separated values, use MySQL FIND_IN_SET(). Let us first create a table −mysql> create table DemoTable1548 -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20), -> ArrayListOfMarks varchar(100) -> ); Query OK, 0 rows affected (0.88 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1548(StudentName, ArrayListOfMarks) values('Chris', '56, 78, 90, 87'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable1548(StudentName, ArrayListOfMarks) values('Bob', '90, 78, 65'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1548(StudentName, ArrayListOfMarks) values('David', '91, 34, 56, ...
Read MoreFetch student records whose result declared 12 days before the current date in MYSQL
For this, you need to compare and find the difference between the current date and the result date of students. This can be done with AND operator along with DATEDIFF().Let us first create a table −mysql> create table DemoTable1547 -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20), -> StudentMarks int, -> StudentResultDeclareDate datetime -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1547(StudentName, StudentMarks, StudentResultDeclareDate) values('Chris', 56, '2019-10-13 13:00:00') -> ; Query OK, 1 row affected (0.16 ...
Read MoreMySQL query to format numbers which has space between digit?
Let us first create a table −mysql> create table DemoTable1546 -> ( -> Number varchar(20) -> ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1546 values('145 78 90'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1546 values('89 789 564 903'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1546 values('1345 7894 866 653534'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select * from DemoTable1546;This will produce the following output −+----------------------+ | Number ...
Read MoreMySQL procedure to display a "select" statement twice
To understand, let us create a stored procedure. Here, we have 2 select statements in the stored procedure −mysql> DELIMITER // mysql> CREATE PROCEDURE select_statement() -> BEGIN -> SELECT "HI" AS `FIRST VALUE`; -> SELECT "HELLO" AS `SECOND VALUE`; -> END -> // Query OK, 0 rows affected (0.09 sec) mysql> DELIMITER ;Call the stored procedure using CALL command −mysql> CALL select_statement();This will produce the following output −+-------------+ | FIRST VALUE | +-------------+ | HI | +-------------+ 1 row in set (0.00 sec) +--------------+ | SECOND VALUE | +--------------+ | HELLO | +--------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)
Read More