- 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 6702 Articles for Database
![Jennifer Nicholas](https://www.tutorialspoint.com/assets/profiles/13569/profile/60_88876-1512715262.jpg)
421 Views
To not allow any duplicate entry to be entered in a MySQL table, you need to add unique key. The syntax is as follows −alter ignore table yourTableName add constraint unique key(yourColumName);The above syntax sets unique key. To understand the above syntax, let us create a table.The following is the query to create a table −mysql> create table RemoveDuplicateEntry −> ( −> Id int, −> Name varchar(100) −> )ENGINE = MyISAM; Query OK, 0 rows affected (0.15 sec)Now you can implement the syntax discussed in the beginning. The query is as follows −mysql> alter table ... Read More
![Anvi Jain](https://www.tutorialspoint.com/assets/profiles/13591/profile/60_98631-1512716973.jpg)
2K+ Views
You can check multiple columns for one value with the help of IN operator. The syntax is as follows −select *from yourTableName where value IN(yourColumnName1, yourColumnName2, ......N);To understand the above concept, let us create a table with some columns. The query to create a table is as follows −mysql> create table OneValueFromAllColumns −> ( −> StudentId int, −> StudentFirstname varchar(200), −> StudentLastname varchar(200), −> StudentAge int −> ); Query OK, 0 rows affected (1.41 sec)Insert some records in the table with the ... Read More
![Arjun Thakur](https://www.tutorialspoint.com/assets/profiles/13574/profile/60_74441-1560315595.jpg)
1K+ Views
To achieve this, the following is the syntax.UPDATE yourTableName set yourColumnName=concat(ifnull(yourColumnName, ””), ’anyValue1, anyValue2, anyValue);To understand the above syntax, let us first create a table. The query to create a table is as follows -mysql> create table AppendDataDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (1.54 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into AppendDataDemo values(101, 'John', 23); Query OK, 1 row affected (0.24 sec) mysql> insert into AppendDataDemo values(102, null, 24); Query OK, 1 row affected (0.74 sec) ... Read More
![Vrundesha Joshi](https://www.tutorialspoint.com/assets/profiles/13566/profile/60_88163-1512715024.jpg)
16K+ Views
You can use LIKE with OR operator which works same as IN operator.Let us see the syntax for both the cases −Case 1 − Using Like with OR operator.select *from yourTableName where yourColumnName Like ‘Value1’ or yourColumnName Like ‘Value2’ or yourColumnName Like ‘Value3’ . . . NCase 2 − Using IN operator.select *from yourTableName where IN(value1, value2, value3, .....N);To understand both the syntaxes, let us create a table. The query to create a table is as follows −mysql> create table LikeDemo −> ( −> Id varchar(20) −> ); Query OK, 0 rows affected (0.58 sec)Now you can insert records in ... Read More
![Ankith Reddy](https://www.tutorialspoint.com/assets/profiles/22328/profile/60_142162-1519038074.jpg)
1K+ Views
You can do this with the help of information_schema.tables. The syntax is as follows -SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024), 2) "MB Size" FROM information_schema.TABLES WHERE table_schema = "yourDatabaseName" ORDER BY (data_length + index_length) ASC;To understand the above syntax, let us implement it for any database. Here, I am using database TEST. Let us see the query for our database TEST.mysql> SELECT TABLE_NAME, table_rows, data_length, index_length, -> round(((data_length + index_length) / 1024 / 1024), 2) "MB Size" -> FROM information_schema.TABLES WHERE table_schema = "test" -> ORDER BY (data_length + index_length) ASC;The following is the ... Read More
![George John](https://www.tutorialspoint.com/assets/profiles/13511/profile/60_58410-1512537824.png)
4K+ Views
To avoid this type of error in MySQL stored procedure, you need to change the delimiter ; to //.Suppose if you are using stored procedure or triggers or even function then you need to change the delimiter. The syntax is as follows.DELIMITER // CREATE PROCEDURE yourProcedureName() BEGIN Statement1, . . N END; // DELIMITER ;To understand the above syntax, let us create a stored procedure. The query to create a stored procedure is ... Read More
![Anvi Jain](https://www.tutorialspoint.com/assets/profiles/13591/profile/60_98631-1512716973.jpg)
732 Views
To list non-empty tables in MySQL database, use “information_schema.tables”. The following is the query for all database tables −mysql> select table_type, table_name from information_schema.tables −> where table_rows >= 1;Above, we have considered only the table that have 1 or more than 1 rows i.e. non-empty table.The following is the output −+------------+------------------------------------------------------+ | TABLE_TYPE | TABLE_NAME | +------------+------------------------------------------------------+ | BASE TABLE | innodb_table_stats ... Read More
![Chandu yadav](https://www.tutorialspoint.com/assets/profiles/22330/profile/60_142811-1519038104.jpg)
8K+ Views
To re-order columns in MySQL, use the ALTER TABLE MODIFY COLUMN. The syntax is as follows -ALTER TABLE yourTableName MODIFY COLUMN yourColumnName data type after yourColumnName.To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table reOrderColumn -> ( -> ProductId int, -> DeliveryDate datetime, -> ProductName varchar(100) -> ); Query OK, 0 rows affected (0.76 sec)Now check the description of the table. The query is as follows.mysql> desc reOrderColumn;The following is the output.+--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | ... Read More
![Rishi Rathor](https://www.tutorialspoint.com/assets/profiles/13563/profile/60_89283-1512714754.jpg)
213 Views
To substring a MySQL table column, use the in-built SUBSTR() function from MySQL. The syntax is as follows −select substr(yourColumnName, AnyValue) as anyVariableName from yourTableName;To understand the function substr(), let us create a table. The query to create a table is as follows −mysql> create table SubStringDemo −> ( −> UserId varchar(200) −> ); Query OK, 0 rows affected (0.55 sec)Now insert some records in the table. The query to insert records is as follows −mysql> insert into SubStringDemo values('Bob10015'); Query OK, 1 row affected (0.29 sec) mysql> insert into ... Read More
![Jennifer Nicholas](https://www.tutorialspoint.com/assets/profiles/13569/profile/60_88876-1512715262.jpg)
2K+ Views
Alter multiple columns in a single statement with the help of CHANGE command. The syntax is as follows −alter table yourTableName change yourOldColumnName1 yourNewColumnName1 dataType, yourOldColumnName2 yourNewColumnName2 dataType, . . . NTo understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table AlterMultipleColumns −> ( −> Id int, −> Name varchar(200) −> ); Query OK, 0 rows affected (0.93 sec)Now we have two columns with Id and Name. We will alter both the columns.Here, we will alter ... Read More