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 148 of 341
MySQL Select where timestamp is in the current hour?
For this, you can use CURTIME(). Let us first create a table −mysql> create table DemoTable -> ( -> ArrivalTime timestamp -> ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-10-26 17:55:55'); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable values('2019-10-26 18:00:00'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('2019-10-26 18:55:00'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('2018-10-26 16:00:10'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select ...
Read MoreHow to declare a variable inside a procedure in MySQL?
You can use the DECLARE command to declare a variable inside a MySQL procedure. Let us create a stored procedure in MySQL −mysql> DELIMITER // mysql> CREATE PROCEDURE DECLARE_VARIABLE_DEMO(IN value int) -> BEGIN -> DECLARE searchValue int; -> set searchValue=value; -> if searchValue=10 then -> select searchValue+100; -> else -> select searchValue; -> end if; -> END -> // Query OK, 0 rows affected (0.17 sec) mysql> DELIMITER ;Above, we have declared a variable. Now, let us call the stored procedure using CALL command −mysql> call DECLARE_VARIABLE_DEMO(10);This will ...
Read MoreWhere we should close a connection in JDBC and MySQL?
You need to close connection in finally block. Following is the Java code to close connection in JDBC and MySQL −import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class CloseConnectionDemoInFinallyBlock { public static void main(String[] args) { String JDBCURL = "jdbc:mysql://localhost:3306/web?useSSL=false"; Connection con = null; try { con = DriverManager.getConnection(JDBCURL, "root", "123456"); System.out.println("connection is open"); } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); } catch (SQLException sqlException) { sqlException.printStackTrace(); } } } }This will produce the following output −connection is openHere is the screenshot of the output −
Read MoreFiltering data in a table for a required condition to eliminate a specific record with MySQL
For this, you can use NOT LIKE operator. Let us first create a table −mysql> create table DemoTable -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20), -> StudentAdmissionYear varchar(20) -> ); Query OK, 0 rows affected (1.22 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentName, StudentAdmissionYear) values('Chris', '2017'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentName, StudentAdmissionYear) values('David', '2015'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentName, StudentAdmissionYear) values('Bob', '2019'); Query OK, 1 row affected (0.18 sec) mysql> insert ...
Read MoreFetch some words from the left in MySQL
For this, use LEFT in MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> Title text -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Java database connectivity to MySQL database'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Python with django framework'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('C with data structure and algorithm'); Query OK, 1 row affected (0.33 sec)Display all records from the table using select statement −mysql> select ...
Read MoreDifference between intvalue='1' and intvalue=1 in MySQL?
You need to use intvalue=1. The statement intvalue=’1’ is internally converted to cast(‘1’ as int) by MySQL.Let us first create a table −mysql> create table DemoTable1566 -> ( -> intvalue int -> ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1566 values(1); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1566 values(55); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable1566 values(75); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable1566 values(90); Query OK, 1 row affected (0.19 sec)Display all ...
Read MoreMySQL query not matching due to punctuation?
Use the MySQL LIKE operator to match even when punctuation is present. Let us first create a table−mysql> create table DemoTable -> ( -> Comments varchar(20) -> ); Query OK, 0 rows affected (1.10 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Good, Morning'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Nice'); Query OK, 1 row affected (0.51 sec) mysql> insert into DemoTable values('good, bye!'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following ...
Read MoreConvert from varchar to datetime and compare in MySQL?
For this, you can use STR_TO_DATE(). Let us first create a table −mysql> create table DemoTable1565 -> ( -> ArrivalDatetime varchar(40) -> ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1565 values('10/01/2019 21:29:35'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1565 values('10/13/2019 4:56:00'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1565 values('10/13/2018 12:40:46'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1565 values('10/13/2019 21:30:00'); Query OK, 1 row affected (0.58 sec)Display all records from the table using select ...
Read MoreCASE WHEN column1 IS NULL THEN NULL ELSE column2 END with MySQL
For this, you can use the CASE statement. Let us first create a table−mysql> create table DemoTable -> ( -> Name varchar(20), -> Marks1 int, -> Marks2 int -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command−mysql> insert into DemoTable values('Chris', 45, null); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('David', null, 78); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Bob', 67, 98); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> ...
Read MoreHow to make MySQL result set the same as specified?
For this, use MySQL FIND_IN_SET(). Let us first create a table −mysql> create table DemoTable1563 -> ( -> StudentId int, -> StudentName varchar(20) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1563 values(1001, 'Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1563 values(1010, 'Bob'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1563 values(1005, 'Chris'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1563 values(1015, 'David'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1563 ...
Read More