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
MySQL Articles
Page 281 of 355
How to specify exact order with WHERE `id` IN (...) in MySql?
To specify exact order with where id IN, you need to use find_in_set() function.The syntax is as followsSELECT *FROM yourTableName WHERE yourColumnName IN (yourValue1, yourValue2, yourValue3, ....N) ORDER BY FIND_IN_SET(yourColumnName , ‘yourValue1, yourValue2, yourValue3, ....N’');Let us first create a tablemysql> create table FindInSetDemo - > ( - > Id int, - > Name varchar(20), - > Age int - > ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into FindInSetDemo values(10, 'John', 23); Query OK, 1 row affected (0.20 sec) mysql> insert ...
Read MoreFilter the records of current day, month and year in MySQL?
Let’s say you have a table with UserLoginTime column wherein we have stored some values for sample. This is the login time of users and we want to filter all these records on the basis of current day, month and year i.e. the current date. We will beLet us now create the table we discussed abovemysql> create table userLoginInformation - > ( - > UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > UserName varchar(20), - > UserLoginTime datetime - > ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using ...
Read MoreIs it possible to enforce data checking in MySQL using Regular Expression?
Yes, it is possible to enforce data checking in MySQL using regular expression. First, you need to create a table. After that you need to create a trigger before insert in table. Here, we will be checking the Phone Number format.The query to create a table is as followsmysql> create table enforceDataUsingRegularExpression - > ( - > yourPhoneNumber varchar(60) - > ); Query OK, 0 rows affected (0.59 sec)The query to create a trigger is as followsmysql> DELIMITER // mysql> CREATE TRIGGER enforce_phone_check BEFORE INSERT ON enforceDataUsingRegularExpression - ...
Read MoreAdd a positive integer constraint to an integer column in MySQL?
You need to use unsigned for this because it won’t allow you to enter a negative number.The syntax is as followsCREATE TABLE yourTableName ( yourColumnName INT UNSIGNED );To understand the concept, let us create a table. The query to create a table is as followsmysql> create table OnlyPositiveValue - > ( - > Marks int UNSIGNED - > ); Query OK, 0 rows affected (0.58 sec)Before inserting data in the table, use the below query.The query is as followsmysql> SET @@SESSION.sql_mode = 'STRICT_TRANS_TABLES'; Query OK, 0 rows affected, ...
Read MoreGet the last record from a table in MySQL database with Java?
To get data from MySQL database, you need to use executeQuery() method from java. First create a table in the MySQL database. Here, we will create the following table in the ‘sample’ databasemysql> create table javaGetDataDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > FirstName varchar(10), - > LastName varchar(10) - > ); Query OK, 0 rows affected (0.80 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into javaGetDataDemo(FirstName, LastName) values('John', 'Smith'); Query OK, 1 row affected (0.19 sec) mysql> insert into javaGetDataDemo(FirstName, LastName) values('Carol', ...
Read MoreSplit a string and insert it as individual values into a MySQL table?
You can achieve this with the help of prepared statement in MySQL. First you need to create a table. The query to create a table is as followsmysql> create table University - > ( - > UserId int, - > UniversityId int - > ); Query OK, 0 rows affected (0.64 sec)At first, let us set values in the above-mentioned columns. Here, we have set a string with comma separated value for UserId column. We will split this and insert in the tablemysql> SET @userId = '8, 9, ...
Read MoreGet only the file extension from a column with file names as strings in MySQL?
For this, use the substring_index() function.The syntax is as followsselect substring_index(yourColumnName, '. ', -1) AS anyAliasNamefrom yourTableName;Let us first create a table. The query to create a table is as followsmysql> create table AllFiles - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > UserName varchar(10), - > FileName varchar(100) - > ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into AllFiles(UserName, FileName) values('Larry', 'AddTwoNumber.java'); Query OK, 1 ...
Read MoreCount two different columns in a single query in MySQL?
You can use CASE statement to count two different columns in a single query. To understand the concept, let us first create a table. The query to create a table is as follows.mysql> create table CountDifferentDemo - > ( - > ProductId int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > ProductName varchar(20), - > ProductColor varchar(20), - > ProductDescription varchar(20) - > ); Query OK, 0 rows affected (1.06 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into CountDifferentDemo(ProductName, ProductColor, ProductDescription) values('Product-1', 'Red', 'Used'); Query OK, 1 row ...
Read MoreHow to select ID column as null in MySQL?
Let us first create a table. The query to create a table is as followsmysql> create table selectAllDemo - > ( - > Name varchar(100), - > Age int - > ); Query OK, 0 rows affected (1.90 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into selectAllDemo values('John', 25); Query OK, 1 row affected (0.99 sec) mysql> insert into selectAllDemo values('Carol', 26); Query OK, 1 row affected (0.42 sec) mysql> insert into selectAllDemo values('Bob', 30); Query OK, 1 row affected (1.57 ...
Read MoreIs it possible to calculate a correlation in a MySQL query?
Yes, it is possible to calculate a correlation in a query. To understand the correlation in a query, you need to first create a table. The query to create a table is as followsmysql> create table correlationDemo - > ( - > value float not null, - > value2 float not null - > ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table with the help of insert command. The query is as follows to insert records in the tablemysql> insert into correlationDemo values(1, ...
Read More