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 265 of 341
How to generate a "create table" command based on an existing table in MySQL?
You can generate a create table command based on an existing table in MySQL with the help of SHOW CREATE command.The syntax is as followsSHOW CREATE TABLE yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table StudentInformation - > ( - > StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > StudentName varchar(20), - > StudentAge int DEFAULT 18, - > StudentRollNo int, - > StudentAddress varchar(200), - > StudentMarks int, - > StudentDOB datetime, - > StudentAdmissionDate datetime ...
Read MoreHow to order by date and time in MySQL?
You need to use ORDER BY clause for this. Let us first create a table. The query to create a table is as followsmysql> create table OrderByDateThenTimeDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > ShippingDate date, - > ShippingTime time - > ); Query OK, 0 rows affected (0.56 sec)Now you can insert some records in the table using insert command. Here, we have two similar dates, but different times i.e. 2018-01-24mysql> insert into OrderByDateThenTimeDemo(ShippingDate, ShippingTime) ...
Read MoreHow to get the second last record from a table in MySQL?
To get the record before the last one i.e. the second last record in MySQL, you need to use subquery.The syntax is as followsSELECT *FROM (SELECT *FROM yourTableName ORDER BY yourIdColumnName DESC LIMIT 2) anyAliasName ORDER BY yourIdColumnName LIMIT 1;Let us first create a table. The query to create a table is as followsmysql> create table lastRecordBeforeLastOne - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Name varchar(20) DEFAULT 'John', - > Age int DEFAULT 18 - > ); Query OK, 0 rows affected (0.79 sec)Now you can insert some ...
Read MoreAdd 6 hours to now() function without using DATE_ADD() in MySQL?
Let us first create a table wherein one of the columns is with datetime. The query to create a table is as followsmysql> create table Add6Hour - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > ArrivalTime datetime - > ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into Add6Hour(ArrivalTime) values(now()); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement.The query is as followsmysql> select ...
Read MorePrevent negative numbers in MySQL?
To prevent negative numbers in MySQL, you need to use INT UNSIGNED. Let’s say you created a table with a column as int i.e. UserGameScores heremysql> create table preventNegativeNumberDemo - > ( - > UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > UserName varchar(20), - > UserGameScores int - > ); Query OK, 0 rows affected (1.74 sec)Now if you need to prevent negative numbers in it, modify the same column with INT UNSIGNEDmysql> alter table preventNegativeNumberDemo modify column UserGameScores INT UNSIGNED NOT NULL; Query OK, 0 rows affected (3.32 sec) Records: 0 ...
Read MoreMySQL select distinct dates from datetime column in a table?
You need to use DISTINCT keyword to select distinct dates from datetime column in a table.For an example, let us create a tablemysql> create table PostMesssageDemo - > ( - > UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > UserMessage varchar(100), - > UserPost datetime - > ); Query OK, 0 rows affected (0.60 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into PostMesssageDemo(UserMessage, UserPost) values('Software Developer', now()); Query OK, 1 row affected (0.17 sec) mysql> insert into PostMesssageDemo(UserMessage, UserPost) values('Software Developer', date_add(now(), interval 3 ...
Read MoreHow to implement MySQL CASE with OR condition?
Here is the syntax of MySQL CASE OR conditionSELECT yourColumnName1, .....N , CASE WHEN yourColumnName2=0 or yourColumnName2IS NULL THEN 'yourMessage1' ELSE 'yourMessage2' END AS yourAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table ReservationSystems - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Name varchar(20), - > isSeatConfirmed tinyint - > ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into ReservationSystems(Name, isSeatConfirmed) ...
Read MoreHow to get the records of the last two days from the current date in MySQL?
To get the records of the last days from the current date, you need to use DATE_SUB(). We will also use NOW() to get the current date. The syntax for the same is as followsSELECT *FROM yourTableName WHERE yourDateTimeColumnName BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 2 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 1 DAY);Let us see an examplemysql> create table get2daysAgoDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Name varchar(20), - > PostDateTime datetime - > ); Query OK, 0 rows affected (1.70 ...
Read MoreHow do I remove ON UPDATE CURRENT_TIMESTAMP from an existing column in MySQL?
The ON UPDATE CURRENT_TIMESTAMP defines that an update without an explicit timestamp would result in an update to the current timestamp value.You can remove ON UPDATE CURRENT_TIMESTAMP from a column using ALTER command.The syntax is as followsALTER TABLE yourTableName CHANGE yourTimeStampColumnName yourTimeStampColumnName timestamp NOT NULL default CURRENT_TIMESTAMP;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table removeOnUpdateCurrentTimeStampDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Name varchar(20), - > UserUpdateTimestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ...
Read MoreHow to get the longest VarChar length in MySQL?
To get the longest varchar length, you need to use CHAR_LENGTH().The syntax is as followsSELECT Max(CHAR_LENGTH(yourColumnName)) AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table CharLengthDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Sentence varchar(255) - > ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into CharLengthDemo(Sentence) values('Java is an object oriented programming language' - > ); Query OK, 1 row ...
Read More