Found 4219 Articles for MySQLi

To rearrange the MySQL columns, check the column arrangement with the help of show create command. The syntax is as follows −show create table yourTableName;The syntax to rearrange the MySQL columns is as follows −alter table yourTableName change column yourColumnName yourColumnName dataType firstFor the same purpose, you can use the after keyword. The syntax is as follows −alter table yourTableName change column yourColumnName yourColumnName dataType after yourSpecificColumnName;Let us first check the column arrangement for the already created table “AddColumn” −mysql> show create table AddColumn; The following is the output −+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table     | Create Table       ... Read More

To add more than 1 column with ALTER table command, you can use MODIFY column command. The syntax is as follows −alter table yourTableName modify column yourColumnName1 dataType, modify column yourColumnName2 dataType, . . . modify column yourColumnNameN dataTypeTo understand the above syntax, let us create a table. The following is the query −mysql> create table AddColumn    −> (    −> StudentID int,    −> StudentName varchar(200)    −> ); Query OK, 0 rows affected (0.49 sec)Above we have two columns in the table “AddColumn”. In this we will see how to modify more than one column datatype −mysql> ... Read More

To alter a MySQL table column defaults, you can use the CHANGE command. The syntax is as follows −alter table yourTableName change yourCoumnName youColumnName datatype not null default Value;To understand the above syntax, let us create a table. The following is the query −mysql> create table DefaultDemo −> ( −> ArrivalTime timestamp −> ); Query OK, 0 rows affected (0.65 sec)Here is the query that describes the table with default column −mysql> desc DefaultDemo;The following is the output −+-------------+-----------+------+-----+---------+-------+ | Field | Type ... Read More

To create a cumulative sum column in MySQL, you need to create a variable and set to value to 0. Cumulative sum increments the next value step by step with current value.Firstly, you need to create a variable with the help of SET. The syntax is as follows −set @anyVariableName:= 0;The syntax to create a cumulative sum column in MySQL is as follows −select yourColumnName1, yourColumnName2, ........N, (@anyVariableName := @anyVariableName + yourColumnName2) as anyVariableName from yourTableName order by yourColumnName1;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table ... Read More

To get the creation date of a MySQL table, use create_time from information_schema.tables. The syntax is as follows −SELECT create_time FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'yourDatabaseName' AND table_name = 'yourTableName';My table name is 'skiplasttenrecords' and database is ‘test’.Implement the above syntax for your database and table name. The query is as follows −mysql> SELECT create_time FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'test' −> AND table_name = 'skiplasttenrecords';The following is the output displaying the creation date −+---------------------+ | CREATE_TIME | +---------------------+ | 2018-11-29 15:47:14 | +---------------------+ 1 row in set (0.00 sec)

To calculate age in MySQL from Date of Birth, you can use the following syntax −SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(yourColumnName) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(yourColumnName, 5)) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query to create a table.mysql> create table AgeCalculatesDemo −> ( −> YourDateOfBirth datetime −> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table with the help of insert command. These records are the DOBs through which we will calculate the age. The following is the ... Read More

To add a day to a DATETIME format value, you can use DATE_ADD() function from MySQL.The syntax is as follows −select date_add(now(), interval 1 day) as anyVariableName;Now you can implement the above syntax in order to add a day to a datetime format.mysql> select date_add(now(), interval 1 day) as Adding1DayDemo;The following is the output −+---------------------+ | Adding1DayDemo | +---------------------+ | 2018-12-07 20:06:59 | +---------------------+ 1 row in set (0.00 sec)If you want to add a day only to a date, you can use curdate() function. The query is as follows −mysql> select date_add(curdate(), interval 1 ... Read More

In order to get all characters before space in MySQL, you can use left() function from the MySQL. The syntax is as follows −select left(yourColumnName, LOCATE(' ', yourColumnName) - 1) as anyVariableName from yourTableName;To understand the above concept, let us create a table.The query to create a table is as follows −mysql> create table AllCharacterBeforeSpace −> ( −> FirstNameAndLastName varchar(200) −> ); Query OK, 0 rows affected (0.51 sec)Now you can insert some records in the table.The query to insert records is as follows −mysql> insert into AllCharacterBeforeSpace values('John Smith'); Query ... Read More

To get today’s date, use in-built function CURDATE(). The CURDATE() gives only current date not time. With that, to get the records for the same day, you can try the following syntax −select yourColumnName1, yourColumnName2, ......, yourColumnNameN, DATE_FORMAT(yourDateColumnName, '%Y-%m-%d') from yourTableName WHERE DATE(yourDateColumnName) = CURDATE();To understand the above concept, let us create a table. The query to create a table is as follows. One of these columns will have datetime datatype to display dates −mysql> create table GmailSignIn −> ( −> UserId int, −> UserName varchar(200), −> DateOfSignIn ... Read More

To create a table in MySQL that matches with another table, use CREATE TABLE command with LIKE operator. The syntax is as follows −create table yourNewTableName like yourOldTableName;The above syntax creates structure of the table.If you want all records then use INSERT INTO…...SELECT *FROM command. The syntax is as follows −insert into yourNewTableName select *from yourOldTableName.I have an old table and some data −mysql> create table WholeWordMatchDemo −> ( −> Words varchar(200) −> ); Query OK, 0 rows affected (0.84 sec)First, we will create a table structure. The query is as ... Read More