- 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
141 Views
CASE statement with the WHEN clause is used to work around conditions. Following is the syntax−select *, case when yourCondition then yourStatement when yourCondition then yourStatement . . else yourStatement from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> StudentName varchar(20), -> StudentMarks int -> ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 78); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('Robert', 88); Query OK, 1 row affected (0.14 sec) ... Read More
58 Views
To replace duplicate records and avoid any error while inserting, use INSERT ON DUPLICATE KEY UPDATE. Let us first create a table −mysql> create table DemoTable -> ( -> Id int, -> Name varchar(20), -> UNIQUE(Id, Name) -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(101, 'Chris') on duplicate key update Id=10001, Name='Robert'; Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(102, 'Mike') on duplicate key update Id=10001, Name='Robert'; Query OK, 1 row affected (0.17 sec) mysql> insert into ... Read More
2K+ Views
Yes, we can turn a column records into a list using the MySQL GROUP_CONCAT(). Let us first create a table −mysql> create table DemoTable -> ( -> ClientId int, -> ClientName varchar(20) -> ); Query OK, 0 rows affected (0.88 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 'Chris'); Query OK, 1 row affected (0.54 sec) mysql> insert into DemoTable values(100, 'Robert'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(100, 'Adam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(100, 'David'); Query OK, ... Read More
580 Views
To create a temporary table with dates, use the CREATE TEMPORARY TABLE in MySQL. Following is the syntax −Syntaxcreate temporary table yourTableName( yourColumnName datetime );Let us first create a table −mysql> create temporary table DemoTable -> ( -> DueDate datetime -> ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(now()); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable values(curdate()); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable values('2018-01-21'); Query OK, 1 row affected (0.00 sec) mysql> insert into ... Read More
697 Views
To increase item value for multiple items in a single query, you can use the CASE statement in MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> ProductName varchar(20), -> ProductPrice int -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Product-1', 700); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Product-2', 1000); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Product-3', 3000); Query OK, 1 row affected (0.10 sec)Display all records from ... Read More
1K+ Views
For this, you can use GROUP BY HAVING along with the COUNT(*) function. Let us first create a table −mysql> create table DemoTable -> ( -> Value int -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable ... Read More
375 Views
It would be good to use IN() instead of multiple OR statements. Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> FirstName varchar(20) -> ); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(FirstName) values('John'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(FirstName) values('Adam'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable(FirstName) values('David'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable(FirstName) values('Mike'); Query OK, 1 ... Read More
1K+ Views
To implement custom sort order in MySQL, you need to use ORDER BY FIELD(). Let us first create a table −mysql> create table DemoTable -> ( -> Designation varchar(100) -> ); Query OK, 0 rows affected (1.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Software Engineer'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Associate Software Engineer'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values('Software Development Engineer'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Product Manager'); Query OK, 1 row ... Read More
179 Views
To subtract a day in MySQL, use the DATE_SUB() method. Let us first create a table −mysql> create table DemoTable -> ( -> AdmissionDate timestamp -> ); Query OK, 0 rows affected (1.05 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-01'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('2018-12-31'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('2017-03-13'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('2019-01-02'); Query OK, 1 row affected (0.08 sec)Display all records from the table using select ... Read More
238 Views
No, we cannot. To still work it out, use backticks around the field name. Let us first create a table with column name with asterisk, `Name*` −mysql> create table DemoTable -> ( -> `Name*` varchar(20) -> ); Query OK, 0 rows affected (2.03 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(`Name*`) values('Chris Brown'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(`Name*`) values('David Miller'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(`Name*`) values('John Doe'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(`Name*`) values('John Smith'); ... Read More