- 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
194 Views
We can fetch all the record from a MySQL table by using SELECT * from table_name; query. An example is as follows, fetched all the records from ‘Employee’ table −mysql> Select * from Employee; +------+--------+ | Id | Name | +------+--------+ | 100 | Ram | | 200 | Gaurav | | 300 | Mohan | +------+--------+ 3 rows in set (0.00 sec)
50 Views
We can create a function, which accepts the date values as its argument and returns the difference in year, month and days, as followsmysql> CREATE FUNCTION date_difference(Date1 DATE, date2 DATE) RETURNS VARCHAR(30) -> RETURN CONCAT( -> @years := TIMESTAMPDIFF(YEAR, date1, date2), IF (@years = 1, ' year, ', ' years, '), -> @months := TIMESTAMPDIFF(MONTH, DATE_ADD(date1, INTERVAL @years YEAR), date2), IF (@months = 1, ' month, ', ' months, '), -> @days := TIMESTAMPDIFF(DAY, DATE_ADD(date1, INTERVAL @years * 12 + @months MONTH), date2), IF (@days = 1, ' day', ' days')) ; Query OK, 0 ... Read More
124 Views
The SELECT command is used to fetch data from the MySQL database. You can use this command at mysql> prompt as well as in any script like PHP.SyntaxHere is generic syntax of SELECT command to fetch data from the MySQL table −SELECT field1, field2, ...fieldN FROM table_name1, table_name2... [WHERE Clause] [OFFSET M ][LIMIT N]Some important points about SELECT statement are as follows −We can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.We can fetch one or more fields in ... Read More
1K+ Views
As we know that DATEDIFF() function is used to get the difference in a number of days between two dates. Hence, it is quite possible that it returns negative value as well.mysql> select * from differ; +------------+-------------+ | OrderDate | WorkingDate | +------------+-------------+ | 2017-10-22 | 2017-10-29 | | 2017-10-25 | 2017-10-30 | | 2017-10-25 | 2017-11-30 | +------------+-------------+ 3 rows in set (0.00 sec)Above query will return the values from table ‘differ’. Now, if someone wants to get the difference between OrderDate and WorkingDate then the output would be negative as follows −mysql> Select DATEDIFF(OrderDate, WorkingDate)AS 'DIFFERENCE IN DAYS' ... Read More
149 Views
As we know that we can copy the data and structure from an existing table by CTAS script. Use of WHERE clause is demonstrated in the example belowmysql> Create table EMP_BACKUP2 AS SELECT * from EMPLOYEE WHERE id = 300 AND Name = 'Mohan'; Query OK, 1 row affected (0.14 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> Select * from EMP_BACKUP2; +------+-------+ | Id | Name | +------+-------+ | 300 | Mohan | +------+-------+ 1 row in set (0.00 sec)In the example above, we have created a table named EMP_BACKUP1 from table ‘Employee’ with some conditions. MySQL creates the table with only one row based on those conditions.
65 Views
With the help of following example we can understand that how we can use MySQL INTERVAL keyword with EXTRACT() function −mysql> Select StudentName, RegDate, EXTRACT(YEAR from RegDate+INTERVAL 2 year) AS 'Two Year Interval' from testing where StudentName = 'Gaurav'; +-------------+---------------------+-------------------+ | StudentName | RegDate | Two Year Interval | +-------------+---------------------+-------------------+ | Gaurav | 2017-10-29 08:48:33 | 2019 | +-------------+---------------------+-------------------+ 1 row in set (0.02 sec)Above query is showing how we can use INTERVAL keyword with EXTRACT() function used in MySQL table query.mysql> Select EXTRACT(Year from '2017-10-22 ... Read More
425 Views
CTAS i.e. “Create Table AS Select” script is used to create a table from an existing table. It copies the table structure as well as data from the existing table. Consider the following example in which we have created a table named EMP_BACKUP from an already existing table named ‘Employee’mysql> Select * from Employee; +------+--------+ | Id | Name | +------+--------+ | 100 | Ram | | 200 | Gaurav | | 300 | Mohan | +------+--------+ 3 rows in set (0.00 sec)The query above shows the data in table ’Employee’ and the query below ... Read More
58 Views
While inserting the values in a row, we can use the value of self-computed output from any expression, function etc. Here is an example to demonstrate it −mysql> Insert into employee(id, emp_name)Select 1+1, Concat_ws(' ','Gaurav', 'Kumar'); Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> Select * from employee; +------+--------------+ | id | emp_name | +------+--------------+ | 2 | Gaurav Kumar | +------+--------------+ 1 row in set (0.00 sec)
6K+ Views
We can use conditional insert i.e. WHERE clause with INSERT INTO command in the case of new row insertion. It can be done with following ways −With the help of dummy tableIn this case, we insert the value from dummy table along with some conditions. The syntax can be as follows −INSERT INTO table_name(column1, column2, column3, …) Select value1, value2, value3, … From dual WHERE [conditional predicate];Examplemysql> Create table testing(id int, item_name varchar(10)); Query OK, 0 rows affected (0.15 sec) mysql> Insert into testing (id, item_name)Select 1, 'Book' From Dual Where 1=1; Query OK, 1 row affected (0.11 sec) ... Read More
114 Views
In MySQL, we can simply specify the SELECT conditions all alone to get the self-computed output. Following example will demonstrate it −mysql> Select 1+1; +-----+ | 1+1 | +-----+ | 2 | +-----+ 1 row in set (0.02 sec) mysql> Select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)