- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 4378 Articles for MySQL
125 Views
MySQL DELETE command is used to delete the row/s from a table. It is used with WHERE clause.SyntaxDELETE From Table_name WHERE Condition;ExampleIn the example below, we have deleted the rows from table ‘employee’ where id >=100.mysql> select * from employee; +------+--------+ | Id | name | +------+--------+ | 100 | Ram | | 200 | Gaurav | | 300 | MOHAN | +------+--------+ 3 rows in set (0.00 sec) mysql> delete from employee where id >=100; Query OK, 3 rows affected (0.06 sec) mysql> select * from employee; Empty set (0.00 sec)
200 Views
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
95 Views
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
95 Views
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
209 Views
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
162 Views
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
57 Views
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
150 Views
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
3K+ Views
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
5K+ Views
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