Found 4219 Articles for MySQLi

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 More

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 More

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 More

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 More

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 More

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 More

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 More

To calculate the total time duration in MySQL, you need to use SEC_TO_TIME(). Let us see an example by creating a tablemysql> create table AddTotalTimeDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > LoginTime time - > ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into AddTotalTimeDemo(LoginTime) values('05:05:00'); Query OK, 1 row affected (0.10 sec) mysql> insert into AddTotalTimeDemo(LoginTime) values('07:20:00'); Query OK, 1 row affected (0.16 sec) mysql> insert ... Read More

To exclude entries with “0”, you need to use NULLIF() with function AVG().The syntax is as followsSELECT AVG(NULLIF(yourColumnName, 0)) AS anyAliasName FROM yourTableName;Let us first create a tablemysql> create table AverageDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > StudentName varchar(20),    - > StudentMarks int    - > ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into AverageDemo(StudentName, StudentMarks) values('Adam', NULL); Query OK, 1 row affected (0.12 sec) mysql> insert into AverageDemo(StudentName, StudentMarks) values('Larry', 23); Query OK, ... Read More

To group by range in MySQL, let us first create a table. The query to create a table is as followsmysql> create table GroupByRangeDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > YourRangeValue int    - > ); Query OK, 0 rows affected (0.78 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into GroupByRangeDemo(YourRangeValue) values(1); Query OK, 1 row affected (0.14 sec) mysql> insert into GroupByRangeDemo(YourRangeValue) values(7); Query OK, 1 row affected (0.15 sec) mysql> insert into GroupByRangeDemo(YourRangeValue) values(9); Query OK, 1 ... Read More