Found 4219 Articles for MySQLi

Use the STR_TO_DATE() function from MySQL to set a date format for displaying DD/MM/YYYY date. The syntax is as follows −SELECT STR_TO_DATE(yourColumnName, ’%d/%m/%Y) as anyVariableName from yourTableName.To understand the above syntax, let us create a table −mysql> create table DateFormatDemo    −> (       −> IssueDate varchar(100)    −> ); Query OK, 0 rows affected (0.54 sec)Inserting some string dates into the table. The query to insert date is as follows −mysql> insert into DateFormatDemo values('26/11/2018'); Query OK, 1 row affected (0.14 sec) mysql> insert into DateFormatDemo values('27/11/2018'); Query OK, 1 row affected (0.18 sec) mysql> ... Read More

To fetch fields with multiple values, use LIKE with OR in MySQL −select *from yourTableName where yourColumnName like ‘%AnyStringValue’ or yourColumnName like ‘%AnyStringValue’ or yourColumnName like ‘%AnyStringValue’ ……...N;You can understand with the help of a table −mysql> create table LikeDemo    −> (       −> Hobby varchar(200)    −> ); Query OK, 0 rows affected (1.71 sec)Insert some records in the table with the help of insert command. The query to insert records in the table is as follows −mysql> insert into LikeDemo values('Reading Book'); Query OK, 1 row affected (0.13 sec) mysql> insert into LikeDemo values('Playing ... Read More

You can get timestamp with the help of current_timestamp, now() and current_timestamp().Case 1 − Using current_timestamp()The query is as follows −mysql> SELECT CURRENT_TIMESTAMP();The following is the output displaying timestamp −+---------------------+ | CURRENT_TIMESTAMP() | +---------------------+ | 2018-11-29 16:09:31 | +---------------------+ 1 row in set (0.00 sec)Case 2 − Using now()The query is as follows −mysql> select now();The following is the output −+---------------------+ | now()               | +---------------------+ | 2018-11-29 16:09:38 | +---------------------+ 1 row in set (0.00 sec)You can get integer UNIX timestamp with the help of below query −mysql> select unix_timestamp();The following is the ... Read More

To skip first 10 results, use “limit offset”. The syntax is as follows −select *from yourTableName limit 10 offset lastValue;Let us create a table to understand the above syntax. The following is the query to create a table −mysql> create table SkipFirstTenRecords    −> (       −> StudentId int,       −> StudentName varchar(200)    −> ); Query OK, 0 rows affected (0.53 sec)Now you can insert some records in the table with the help of insert command. The query is as follows −mysql> insert into SkipFirstTenRecords values(100, 'John'); Query OK, 1 row affected (0.12 sec) ... Read More

To generate a range of numbers in MySQL, you can use stored procedure. Firstly, we need to create a table. After that, we will create a stored procedure that generates a range of number from 10 to 1.The following is the query to create a table −mysql> create table GeneratingNumbersDemo    −> (       −> Number int    −> ); Query OK, 0 rows affected (0.55 sec)Now you can create a stored procedure that stores a range of numbers in the table. The following is the query to create a stored procedure −mysql> delimiter // mysql> CREATE PROCEDURE ... Read More

You can add unique key to existing table with the help of alter command. The syntax is as follows −ALTER TABLE yourTableName ADD CONSTRAINT yourConstraintName UNIQUE(yourColumnName1, yourColumnName2, ............N);To understand the above concept, let us create a table with some columns. The query to create a table −mysql> create table MovieCollectionDemo    −> (       −> MovieId int,       −> MovieDirectorName varchar(200),       −> NumberOfSongs int unsigned    −> ); Query OK, 0 rows affected (0.62 sec)Now you can check the table does not have any unique constraint. The query to check unique constraint is ... Read More

You can change table engine with the help of alter command. The syntax is as follows −alter table yourTableName ENGINE = yourEngineName;To understand the above syntax let us create a table with engine MyISAM. Later you can change any other engine. The following is the query to create a table.mysql> create table ChangeEngineTableDemo −> (    −> MovieId int,    −> MovieName varchar(100),    −> IsPopular bool −> )ENGINE = 'MyISAM'; Query OK, 0 rows affected (0.37 sec)Look at the above query, the table engine is MyISAM, now you can change it to any other engine. Here, we will change ... Read More

MySQL DAYOFWEEK() function returns 1 for Sunday, 2 for Monday and so on for day of week. Let us see an example by first creating a table −mysql> create table DayOfWeekDemo −> (    −> Issuedate datetime −> ); Query OK, 0 rows affected (0.52 sec)Inserting date in the table with the help of insert command. The query is as follows −mysql> insert into DayOfWeekDemo values(date_add(curdate(), interval 5 day)); Query OK, 1 row affected (0.52 sec) mysql> insert into DayOfWeekDemo values(date_add(curdate(), interval 6 day)); Query OK, 1 row affected (0.13 sec) mysql> insert into DayOfWeekDemo values(date_add(curdate(), interval 7 ... Read More

To insert the date with date formats, use the str_to_date() function with date in single quotes. The following is the syntax −insert into yourTableName values(Value1, value2, ......ValueN, str_to_date(‘anyDate’, ’%Y-%m-%d’));Here are the Date Formats in MySQL −FormatDescription%aAbbreviated weekday name (Sun to Sat)%bAbbreviated month name (Jan to Dec)%cNumeric month name (0 to 12)%DDay of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)%dDay of the month as a numeric value (01 to 31)%eDay of the month as a numeric value (0 to 31)%fMicroseconds (000000 to 999999)%HHour (00 to 23)%hHour (00 to 12)%IHour (00 to 12)%iMinutes (00 to 59)%jDay ... Read More

To delete all the records from a table in MySQL, use the TRUNCATE command. Let us fir see the syntax −TRUNCATE TABLE yourTableName.The above syntax will delete all the records from a table. Let us create a table to understand the above syntax −mysql> create table TruncateTableDemo −> (    −> BookId int    −> ,    −> BookName varchar(200) −> ); Query OK, 0 rows affected (0.54 sec)Inserting records in the table with the help of insert command. The query to insert records in the table are as follows −mysql> insert into TruncateTableDemo values(1001, 'C in Dept'); Query OK, ... Read More