MySQL SUM function The SUM() function returns the total sum of a numeric column. The SUM() function is an aggregate function that allows you to calculate the sum of a set of values or an expression. Syntax for SUM function would be as below: SELECT SUM(column_name) FROM table_name WHERE condition; You can also use distinct value while sum function SELECT SUM(distinct column_name) FROM table_name WHERE condition; If … Read more
MySQL MIN() and MAX() Functions The MIN() function returns the smallest value of the selected column and the MAX() function returns the largest value of the selected column. MIN() Syntax SELECT MIN(column_name) FROM table_name WHERE condition; MAX() Syntax SELECT MAX(column_name) FROM table_name WHERE condition;
Install mysql on centos linux Reference : https://blog.eduguru.in/linux-2/install-lamp-server-apache-mysql-php-on-rhel-centos-scientific-linux-6-56-4 Install MySQL MySQL is an enterprise class, open source, world’s second most used database. MySQL is a popular choice of database for use in web applications, and is a central component of the widely used LAMP open source web application software stack. To install MySQL, enter the following … Read more
MySQL conditional expression CASE MySQL CASE expression is a conditional expression that allows you to construct conditions inside a query such as SELECT or WHERE clause. CASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 … ELSE result END The CASE expression returns the result such as result_1, result_2, etc., if the condition is true. If all conditions are false, then the result in the ELSE part … Read more
Very often we need to extract last 30 days, 7 days data from mysql DB. Here we will show easy way to get the data of given interval. SELECT DATE_FORMAT(create_date, ‘%m/%d/%Y’) FROM mytable WHERE create_date BETWEEN CURDATE() – INTERVAL 30 DAY AND CURDATE() Also note that CURDATE() returns only the DATE portion of the date, so … Read more
Very often we need to extract last 1 month, 2 months data from mysql DB. Here we will show easy way to get the data of given interval. SELECT * from testtable WHERE startdate BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND DATE_SUB(NOW(), INTERVAL 2 MONTH) The above query will return records whose order date is between one and … Read more
The myisamchk utility gets information about your database tables or checks, repairs, or optimizes them. myisamchk works with MyISAM tables (tables that have .MYD and .MYI files for storing data and indexes). The use of myisamchk with partitioned tables is not supported. How to run this Utility – myisamchk myisamchk [options] tbl_name The options specify … Read more
MySQL SUBSTRING() returns a specified number of characters from a particular position of a given string. Syntax: SUBSTRING(str, pos, len) or SUBSTRING(str FROM pos FOR len) Arguments Name Description str A string. pos Starting position. len … Read more
The idea behind partitioning isn’t to use multiple servers but to use multiple tables instead of one table. You can divide a table into many tables so that you can have old data in one sub table and new data in another table. Then the database can optimize queries where you ask for new data … Read more
The busiest file in the InnoDB infrastructure is /var/lib/mysql/ibdata1 This file normally houses many classes of information (when innodb_file_per_table is 0) Table Data Table Indexes MVCC (Multiversioning Concurrency Control) Data Rollbacks Segments Undo Tablespace Table Metadata Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help. Unfortunately, OPTIMIZE TABLE against … Read more