- 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
229 Views
As we have checked the size of the MySQL database, similarly we can also check the size of tables in a particular database. It can be done as follows −mysql> SELECT -> table_name AS "Table", -> round(((data_length + index_length) / 1024 / 1024), 2) as SIZE -> FROM information_schema.TABLES -> WHERE table_schema = "SAMPLE" -> ORDER BY SIZE; +-------------+-------+ | Table | SIZE | +-------------+-------+ | employee | 0.02 | | student | 0.02 | | new_student | 0.02 | +-------------+-------+ 3 rows in set (0.00 sec)Here this output gives the size of three tables in the Sample database.
98 Views
Actually, the behavior of MySQL depends upon the allow_invalid_dates mode. If this mode is enabled then MySQL will accept the invalid date and it would perform the date arithmetic as it performs with a valid date. Otherwise, if this mode is inactive then it will not accept the invalid date and would produce NULL as output. mysql> select '2017-02-30' + INTERVAL 7 day; +-------------------------------+ | '2017-02-30' + INTERVAL 7 day | +-------------------------------+ | NULL ... Read More
65 Views
We can check the size of all MySQL databases with the help of the following statementmysql> SELECT table_schema "Database", -> SUM(data_length + index_length)/1024/1024 "Size in MB" -> FROM information_schema.TABLES GROUP BY table_schema; +--------------------+----------------+ | Database | Size in MB | +--------------------+----------------+ | gaurav | 1.04785156 | | information_schema | 0.00878906 | | mysql | 0.68551350 | | performance_schema | 0.00000000 | | sample | 0.04687500 | | tutorial | 0.03125000 | +--------------------+----------------+ 6 rows in set, 1 warning (0.33 sec)
1K+ Views
As the name suggests CURDATE() function will return the current date. In simple words, we can say that it would return only the date not time.mysql> select CURDATE(); +------------+ | CURDATE() | +------------+ | 2017-10-28 | +------------+ 1 row in set (0.00 sec)In contrast, NOW() function will return current date and time.mysql> Select NOW(); +---------------------+ | NOW() | +---------------------+ | 2017-10-28 09:10:16 | +---------------------+ 1 row in set (0.00 sec)
70 Views
The following statement shows the list of two tables having a column ‘email’ in Sample database −mysql> SELECT DISTINCT TABLE_NAME -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE COLUMN_NAME IN('EMAIL') -> AND TABLE_SCHEMA = 'SAMPLE'; +---------------+ | TABLE_NAME | +---------------+ | employee | | new_student | +---------------+ 2 rows in set (0.04 sec)
322 Views
As we know that the default MySQL database would be the database that is currently in use for subsequent queries. We can get the list of tables in that database by using SHOW TABLES statement. mysql> SHOW TABLES; +------------------+ | Tables_in_sample | +------------------+ | employee | | new_student | | student | +------------------+ 3 rows in set (0.00 sec) The above statement shows the list of table in Sampledatabase.
414 Views
MySQL can perform date arithmetic with addition and subtraction operators by adding together INTERVAL keyword with a unit of time, date or datetime.Example1Adding 2 days to a particular date.mysql> Select '2017-05-20' + INTERVAL 2 day; +-------------------------------+ | '2017-05-20' + INTERVAL 2 day | +-------------------------------+ | 2017-05-22 | +-------------------------------+ 1 row in set (0.00 sec)Example2Subtracting 2 days from a particular date.mysql> Select '2017-05-20' - INTERVAL 2 day; +-------------------------------+ | '2017-05-20' - INTERVAL 2 day | +-------------------------------+ | 2017-05-18 | +-------------------------------+ 1 row in ... Read More
122 Views
When we try to do such kind of operations with date values stored in the table then MySQL is assuming the date values as the number and perform the arithmetic.Suppose we have a table named ‘example’ having a date value in ‘orderdate’ column then following arithmetic operation will clarify the above −mysql> select * from example; +------------+ | orderdate | +------------+ | 2017-05-25 | +------------+ 1 row in set (0.00 sec) mysql> select orderdate+10 from example; +--------------+ | orderdate+10 | +--------------+ | 20170535 | +--------------+ 1 row in set (0.00 sec) mysql> select orderdate*10 from example; ... Read More
90 Views
With the help of CREATE DATABASE db-name command, we can check the statement of creating any MySQL database.mysql> SHOW CREATE DATABASE Sample; +----------+-------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------+ | sample | CREATE DATABASE `sample` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-------------------------------------------------------------------+ 1 row in set (0.00 sec)The output shows how MySQL database named Sample has been created.
66 Views
We can display the name of MySQL database that is currently in use by Select Database() command.mysql> select database(); +------------+ | database() | +------------+ | tutorial | +------------+ 1 row in set (0.00 sec)This command shows that we currently use tutorial database.