Let us understand how to search on two keys in MySQLSearching on two keys can be achieved using the ‘OR’ with the help of single key which is well optimized or using ‘AND’ which is well optimized. Let us see how searching on two different keys can be done combining it with ‘OR’ operation −SELECT field1_index, field2_index FROM tableName WHERE field1_index = '1' OR field2_index = '1'This is an optimized version of the query. It can also be done efficiently using the ‘UNION’ that combines the output of two separate ‘SELECT’ statements. Every ‘SELECT’ statement searches only for one key ... Read More

Let us understand what user variables are and how they can be used in MySQL. We will also see the rules −User variables are written as @var_name. Here, the ‘var_name’ refers to variable name, which consists of alphanumeric characters, ., _, and $.A user variable name can contain other characters if they are quoted as a string or identifier.User-defined variables are session specific.A user variable which is defined by one client can’t be seen or used by other clients.But the only exception is that if a user has access to the Performance Schema user_variables_by_thread table, then that user can see ... Read More

Let us understand how to find the rows that hold the group wise maximum of a specific column in MySQL −The syntax to find the rows that hold the group-wise maximum of a specific column in MySQL is as follows −SELECT colName1, colName2, colName3 FROM tableName s1 WHERE colName3=(SELECT MAX(s2. colName3) FROM tableName s2 WHERE s1. colName1= s2. colName1) ORDER BY colName1;Let’s say we have the following PRODUCT Table −+---------+----------+--------+ | Article | Warehouse| Price  | +---------+----------+--------+ | 1       | North    | 255.50 | | 1       | North    | 256.05 | | ... Read More

Let us understand how to find the maximum of a column per group in MySQL −SELECT colName1, MAX(colName2) FROM tableName GROUP BY colName1 ORDER BY colName1;We will now see a live example. Let’s say we have a table PRODUCT −+---------+--------+ | Article | Price  | +---------+--------+ | 1       | 255.50 | | 1       | 256.05 | | 2       | 90.50  | | 3       | 120.50 | | 3       | 123.10 | | 3       | 122.10 | +---------+--------+Following is the query to get the maximum of column per group −QuerySELECT Article, MAX(Price) AS MaxPrice FROM Product GROUP BY Article ORDER BY Article;Output+--------------+--------------+ | Article      | MaxPrice | +--------------+--------------+ | 0001         | 256.05 | | 0002         | 90.50 | | 0003 | 123.10 | +--------------+--------------+

Let us understand how to find the row that holds the maximum of a specific column in MySQL −Note: We assume we have created a database named ‘DBNAME’ and a table named ‘tableName’.Let us see how to fetch the row that holds the maximum value of a specific column using a MySQL query −This can be done using the subquery. Here, we are fetching the maximum value of colName3 −QuerySELECT colName1, colName2, colName3 FROM tableName WHERE colName3=(SELECT MAX(colName3) FROM tableName);Output+--------------+--------------+--------------+ | colName1 | colName2 | colName3 | +--------------+--------------+--------------+ ... Read More

We need to use the MAX(columnName) to find the Maximum value in a column, whereas use the MIN(columnName) to find the Maximum value in a column.Let’s say following is the syntax to find the highest and lowest value in a specific column −mysql> SELECT @min_val:=MIN(columnName), @max_val:=MAX(columnName) FROM tableName; mysql> SELECT * FROM tableName WHERE columnName=@min_val OR columnName=@max_val;Note: Let’s say we have a database named ‘StudentsRecords’ and a table named ‘STUDENT.Following is our table −StudentIdStudentMarksS00190S00297S00372We will now write the query −Querymysql> SELECT @min_val:=MIN(StudentMarks), @max_val:=MAX(StudentMarks) FROM STUDENT; mysql> SELECT * FROM STUDENT WHERE StudentMarks =@min_val OR StudentMarks =@max_val;Output+---------------------+ | StudentMarks   ... Read More

We need to use the MAX(columnName) to find the Maximum value in a column. But, at first, we will understand about database and tables in MySQL.Before installing MySQL, it is important to determine which version and which distribution format (it could be a binary file or from source files) should be used. If the database was newly created, it is obvious that there would be no tables in it.One of the most important part is to decide the structure of the database, the tables that would be needed, the columns in every table and the relationship between these tables. Once ... Read More

Let us understand how MySQL can be connected to the database using the command-line. This is done for clients like mysql or mysqldump.The below command invokes mysql without specifying any explicit connection parameters −mysqlSince there are no parameter options, the default values will be applied −The default host name is localhost.The default user name is ODBC on Windows.No password is sent because neither --password nor -p has been mentioned.For mysql, the first non-option argument is considered the name of the default database. Since there is no such argument, mysql selects no default database.To specify the host name, user name and ... Read More

Let us understand how to list down all the tables in a MySQL database −Once a database is created, we can access and use a specific database, using the following query −Querymysql> USE databaseName Database changedThe ‘USE’ statement doesn’t require a semi-colon. This is similar to the ‘QUIT’ statement. Even if semi-colon is used, it does no harm. We can create and use a database of our own, but before that, MySQL administrator’s permission is required.The MySQL administrator can execute a command as shown below to provide permissions −mysql> GRANT ALL ON tableName.* TO ‘your_mysql_name’@’your_client_host’;Here, ‘your_mysql_name’ refers to the MySQL ... Read More

Let us understand how to use MySQL wth Apache −Apache is a web server software which is developed and maintained by Apache software foundation. It is a software that takes requests from user to access a web page.A few security checks are performed on the HTTP request and then takes the user to the web page. There are many programs that allow authentication of the users from a MySQL database. These programs can also be used to write the log files into a MySQL table.The Apache logging format can be easily changed into readable mode by using MySQL, and putting ... Read More