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
Database Articles
Page 512 of 546
Setup the format of DATETIME to 'DDMM- YYYY HH:MM:SS' with MySQL SELECT?
Whenever you retrieve datetime from a table, the datetime gives ‘YYYY-MM-DD’ format. If you want to change the output, then you need to use in-built date_format() from MySQL.The syntax is as follows −SELECT DATE_FORMAT(yourDatetimeColumnName, yourFormat) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table UserDateFormat -> ( -> ProductId int, -> ProductDeliverDate datetime -> ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert command. The ...
Read MoreMySQL UPDATE the corresponding column with random number between 1-3?
For random numbers in a range, you need to use the RAND() method from MySQL. The syntax is as follows for update −UPDATE yourTableName set yourColumnName=value where yourColumnName2=(SELECT FLOOR(1+RAND()*3));In the above query, the statement FLOOR(1+RAND()*3) generates the number between 1-3 and update the column.To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table updateRowWith1To3 -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the ...
Read MoreMySQL SELECT IF statement with OR?
You can use SELECT IF statement with OR. To understand select with OR, let us create a table. The query to create a table is as follows −mysql> create table EmployeeInformation -> ( -> EmployeeId int, -> EmployeeName varchar(100), -> EmployeeStatus varchar(100) -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into EmployeeInformation values(1, 'Sam', 'FullTime'); Query OK, 1 row affected (0.23 sec) mysql> insert into EmployeeInformation values(2, 'Mike', 'PartTime'); Query OK, 1 row affected (0.14 sec) mysql> ...
Read MoreWhat is the equivalent of Java long in the context of MySQL variables?
The equivalent of Java long in the context of MySQL variables is BigInt.In Java, the long datatype takes 8 bytes while BigInt also takes the same number of bytes.Demo of Java longHere is the demo of Java long −public class JavaLongDemo { public static void main(String[]args) { long kilometer = 9223372036854775807L; System.out.println("The largest positive value for long range:"+kilometer); } }The following is the output −Demo of BigIntLet us see an example of BigInt type in MySQL. The following is the query to ...
Read MoreMySQL Query to find tables modified in the last hour?
You can achieve this with the help of INFORMATION_SCHEMA.TABLES. Use the date_sub() with interval. The syntax is as follows −SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE DATE_SUB(NOW(), INTERVAL -1HOUR) < ‘UPDATE_TIME’;Now you can check the above syntax. Here is the query to find the tables modified in the last hour −mysql> select table_name from `INFORMATION_SCHEMA`.`TABLES` -> WHERE -> DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;Output+---------------------+ | TABLE_NAME | +---------------------+ | innodb_table_stats | | innodb_index_stats | | employeeinformation | +---------------------+ 3 rows in set (0.37 sec)The above query selects only table name. If you want information like table schema, table ...
Read MoreCount how many rows have the same value in MySQL?
To count how many rows have the same value using the function COUNT(*) and GROUP BY. The syntax is as follows −SELECT yourColumName1, count(*) as anyVariableName from yourTableName GROUP BY yourColumName1;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table RowWithSameValue −> ( −> StudentId int, −> StudentName varchar(100), −> StudentMarks int −> ); Query OK, 0 rows affected (0.55 sec)Insert some records with same value. Here, we have added same marks for more than one student for our example. The query ...
Read MoreSearch for a string within text column in MySQL?
You can search for a string within text column in MySQL with the help of LIKE clause. The syntax is as follows −select *from yourTableName where yourColumnName like '%anyStringValue%';To use the above syntax, let us first create a table −mysql> create table SearchTextDemo −> ( −> BookName TEXT −> ); Query OK, 0 rows affected (0.55 sec)Insert some strings in the table. The query is as follows −mysql> insert into SearchTextDemo values('Let us C'); Query OK, 1 row affected (0.28 sec) mysql> insert into SearchTextDemo values('C in Depth'); Query OK, 1 row affected (0.14 sec) ...
Read MoreSelect records from MySQL NOW() -1 Day?
To get records from NOW()-1 Day, you can use the following syntax −select *from yourTableName where yourColumnName >=now()-interval 1 day;To understand the above syntax, let us first create a table. The query to create a table.mysql> create table GetRecordsFromNow −> ( −> YourDateTime datetime −> ); Query OK, 0 rows affected (1.76 sec)Now insert some dates into the fields. The query to insert records are as follows −mysql> insert into GetRecordsFromNow values(date_add(now(), interval 3 day)); Query OK, 1 row affected (0.28 sec) mysql> insert into GetRecordsFromNow values(date_add(now(), interval -1 day)); ...
Read MoreHow to change the column position of MySQL table without losing column data?
You can change the column position of MySQL table without losing data with the help of ALTER TABLE command. The syntax is as follows −ALTER TABLE yourTableName MODIFY yourColumnName1 data type AFTER yourColumnName2;To understand the above concept, let us create a table. The query to create a table with some columns is as follows −mysql> create table changeColumnPositionDemo −> ( −> StudentId int, −> StudentAddress varchar(200), −> StudentAge int, −> StudentName varchar(200) −> ); Query OK, 0 rows affected (0.72 sec)Let us insert some data in the table. The query to insert records is as follows -.mysql> insert into changeColumnPositionDemo ...
Read MoreDelimiters in MySQL?
Delimiters can be used when you need to define the stored procedures, function as well as to create triggers. The default delimiter is semicolon.You can change the delimiters to create procedures and so on. However, but if you are considering multiple statements, then you need to use different delimiters like $$ or //.Here we have a table “GetRecordFromNow” wherein the following are the records −+---------------------+ | YourDateTime | +---------------------+ | 2018-12-07 22:30:18 | | 2018-12-03 22:30:31 | | 2018-12-02 22:30:41 | | 2018-12-01 22:30:56 | | 2018-12-03 22:31:04 | +---------------------+ 5 rows in ...
Read More