Found 4219 Articles for MySQLi

The most significant difference between MySQL TRUNCATE and DROP command is that TRUNCATE command will not destroy table’s structure but in contrast DROP command will destroy table’s structure too.Examplemysql> Create table testing(id int PRIMARY KEY NOT NULL AUTO_INCREMENT, Name Varchar(20)); Query OK, 0 rows affected (0.24 sec) mysql> Insert into testing(Name) Values('Ram'), ('Mohan'), ('John'); Query OK, 3 rows affected (0.12 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> Select * from testing; +----+-------+ | id | Name  | +----+-------+ | 1  | Ram   | | 2  | Mohan | | 3  | John  | +----+-------+ 3 rows ... Read More

As the name explains, it is used to completely remove the table from the database.SyntaxDrop table table_name;Examplemysql> Drop table Student; Query OK, 0 rows affected (0.09 sec)The query above completely removes the ‘Student’ table from the database. We can observe as MySQL returns an error message after running the following query −mysql> Select * from Student; ERROR 1146 (42S02): Table 'query.student' doesn't exist

Eventually, we cannot remove all the columns from a table by using ALTER TABLE command with DROP keyword. In this case, MySQL will return an error message. It is demonstrated with the help of the following exampleSuppose in table ‘Employee’ we have two columns ‘name’ and ‘id’, now if we will use ALTER TABLE to remove both the columns then MySQL returns an error as followsmysql> ALTER TABLE employee drop column name, drop column id; ERROR 1090 (42000): You can't delete all columns with ALTER TABLE; use DROP TABLE instead

We can use keyword CHANGE to modify the column/s of an existing table. With CHANGE keyword we can change the name of the column and its definition both. Its syntax would be a bit different from the syntax of ALTER TABLE with MODIFY keyword.SyntaxAlter table table_name CHANGE old_columnname1 new_columnname1 datatype, CHANGE old_columnname2 new_columnname2 datatype… CHANGE old_columnnameN new_columnname datatype);ExampleIn the example below, with the help of CHANGE keyword in ALTER Command, the name and size of the columns ‘City’ and ‘RollNo’ have been modified.mysql> Alter table Student CHANGE Rollno Id int, CHANGE City Place Varchar(10); Query OK, 5 rows affected (0.40 ... Read More

Different date format characters used by MySQL DATE_FORMAT() function are as follows −Date Format CharacterMeaning %aIt is used to abbreviate the names of the weekdays like Sun, Mon, Tue, Wed, Thu, Fri and Sat.%bIt is used to abbreviate the names of the month like Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and Dec.%cIt is used to abbreviate the number of the month like 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 and 12. There will be no leading zero.%DIt is used to abbreviate the day of the month with an ordinal suffix like 1st, ... Read More

We need to use DATE_FORMAT() function to display the date in other formats. There would be two arguments of this function, first would be the date and second would be the format string.Example − Suppose in the table ‘date_testing we have three dates in the following formatmysql> Select * from date_testing; +------------+ | Date       | +------------+ | 2017-03-15 | | 2017-03-25 | | 2017-04-05 | +------------+ 3 rows in set (0.00 sec)Now DATE_FORMAT() function will change the format of the above dates in the format given by the user as follows −mysql> Select DATE_FORMAT(Date, '%W %D ... Read More

By using MODIFY keyword with ALTER TABLE command we can modify the column/s of MySQL table. The syntax for the same is as follows;SyntaxAlter table table_name MODIFY column_name1 datatype, MODIFY column_name2 datatype… MODIFY column_nameN datatype);ExampleIn the example below, with the help of MODIFY keyword in ALTER Command, the size of column ‘Address’ and ‘Grade’ has been modified.mysql> Alter table student modify address varchar(45),Modify Grade Varchar(15); Query OK, 5 rows affected (0.38 sec) Records: 5 Duplicates: 0 Warnings: 0

While adding columns to an existing table with the help of ALTER command we can specify the default value also.SyntaxAlter table table-name ADD (column-name datatype default data);ExampleIn the example below, with the help of ALTER Command, column ‘City’ is added with default value ‘DELHI’ to the table ‘Student’.mysql> Alter table Student ADD(City Varchar(10) Default 'DELHI'); Query OK, 5 rows affected (0.33 sec) Records: 5 Duplicates: 0 Warnings: 0Now from DESCRIBE command, we can check the default value of ‘City’ column.mysql> describe Student\g +---------+--------------+------+-----+---------+-------+ | Field   | Type         | Null | Key | Default ... Read More

We can also add multiple columns to an existing table with the help of ALTER command. The syntax for it would be as follows −SyntaxAlter table table-name ADD (column-name1 datatype, column-name2 datatype,… column-nameN datatype);ExampleIn the example below, with the help of ALTER Command, columns ‘Address’, ‘Phone’ and ‘Email’ are added to the table ‘Student’.mysql> Alter Table Student ADD(Address Varchar(25), Phone INT, Email Varchar(20)); Query OK, 5 rows affected (0.38 sec) Records: 5 Duplicates: 0 Warnings: 0

Suppose we want to store the date such as February 30 in a MySQL table then we must have to first set ALLOW_INVALID_DATES mode enabled.For example, I am trying to add, without enabling ALLOW_INVALID_DATES mode, such kind of date in a table then MySQL will give an error as follows −mysql> Insert into date_testing(date) values('2017-02-30'); ERROR 1292 (22007): Incorrect date value: '2017-02-30' for column 'Date' at row1Now we need to enable ALLOW_INVALID_DATES mode enabled as follows −mysql> SET sql_mode = 'ALLOW_INVALID_DATES'; Query OK, 0 rows affected (0.00 sec) mysql> Insert into date_testing(date) values('2017-02-30'); Query OK, 1 row affected (0.14 ... Read More