Found 4219 Articles for MySQLi

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 More

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 More

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 More

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 More

You can select most recent date out of a set of several possible timestamps with the help of ORDER BY clause.The syntax is as followsSELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName ORDER BY yourTimestampColumnName DESC LIMIT 1;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table MostRecentDateDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Name varchar(10),    - > ShippingDate timestamp    - > ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command. ... Read More

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 More

Yes, both of these methods convert a degree value to radian. Let us create a table to understand MySQL radians. The query to create a table is as followsmysql> create table RadiansDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Value int    - > ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into RadiansDemo(Value) values(0); Query OK, 1 row affected (0.14 sec) mysql> insert into RadiansDemo(Value) values(45); Query OK, 1 row affected (0.17 sec) mysql> insert into ... Read More

As you know, order is a keyword in MySQL, you cannot give table name order directly. You need to use backtick around the table name order. Backtick allow a user to consider the keyword as table or column name.The syntax is as followsCREATE TABLE `order` (    yourColumnName1 dataType,    yourColumnName2 dataType,    yourColumnName3 dataType,    .    .    .    .    N );Let us create a table. The query to create a table is as followsmysql> create table `order`    - > (    - > Id int,    - > Price int    - > ); ... Read More

You can combine date and time from different MySQL columns to compare with the entire date time with the help of CONCAT() function. The syntax is as follows −SELECT *FROM yourTableName WHERE CONCAT(yourDateColumnName, '', yourTimeColumnName) > 'yourDateTimeValue';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table DifferentDateTime     -> (     -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,     -> ArrivalDate date,     -> ArrivalTime time     -> ); Query OK, 0 rows affected (1.53 sec)Insert some records in the table using ... Read More

To get total in the last row of MySQL result, use the following syntax −(    SELECT yourColumnName1,    yourColumnName2,    yourColumnName3,    .    .    N    FROM yourTableName ) UNION (    SELECT "yourMessage" AS anyAliasName1,    SUM(yourColumnName1) AS anyAliasName2,    SUM(yourColumnName2) AS anyAliasName3,    .    .    N    FROM yourTableName );To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ProductDemo    -> (    -> ProductId varchar(10),    -> ProductQuantity int,    -> ProductValue int    -> ); Query OK, 0 ... Read More