SOUNDEX(str) Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is 4 characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All ..
Find the values of a table column c1 for which there are a specified number of listed values in another column c2. To get an overview of the values of c2 for each value of c1: SELECT c1, GROUP_CONCAT(c2 ORDER BY c2) AS 'C2 values' FROM table GROUP BY c1; To retrieve a ..
Given a parent table and two child tables, a query which sums values in both child tables, grouping on a parent table column, returns sums that are exactly twice as large as they should be. In this example from the MySQL General Discussion list. DROP TABLE IF EXISTS packageItem,packageCredit,packageItemTax; CREATE TABLE ..
This is the simplest grouping query pattern. For column foo, display the first (smallest), last (largest) or average value of column bar. SELECT foo, MIN(bar) AS bar FROM tbl GROUP BY foo Return the highest bar value for each foo, ordering top to bottom by that value: SELECT foo, MAX(bar) AS Count ..
MySQL command to convert a string to lowercase In MYSQL we have a command LOWER() which help us to convert a string to lowercase. For Example: We have MYSQL database of keywords that are presently mixed-case. If we want to convert them all to lowercase we can use the command LOWER(). 1. Set ..
Mysql coalesce selects the first not-null value of its arguments. Syntax: coalesce(value,..) It returns the first non NULL value in the list. If there are no non-NULL values then it returns NULL. ..
To login (from unix shell) use -h only if needed. # [mysql dir]/bin/mysql -h hostname -u root -p Create a database on the sql server. mysql> create database [databasename]; List all databases on the sql server. mysql> show databases; Switch to a database. mysql> use [db name]; To see all the tables in the db. mysql> show tables; To ..
To execute recursive stored procedure in mysql, set the value for the mysql variable max_sp_recursion_depth to greater than zero or upto the number of recursions needed for your stored procedure. The maximum value for max_sp_recursion_depth is 255. Mysql Command to see mysql variable SHOW VARIABLES; Mysql Command to find max_sp_recursion_depth SHOW ..
Sql Injection attack or exploits What is sql injection? SQL Injection is when a visitor injects SQL code that manages to get processed by the SQL server. This problem usually arises when the programmer does not properly check the user input variables before throwing it to the SQL server. Examples of sql ..
How to find the size of a database and table You can find the find the database size, specific tables size using the following queries. Earlier I use to make blunder and assume the database size by calculating the /var/lib/mysql/dbfolder folder or directory. But actually you have to calculate the size ..
How do I find Mysql version Linux Mysql version can found by multiple ways, if you are an admin you can lookup mysql version by using mysqladmin command # mysqladmin version The above command will print the mysql version, which can be used to used to mysql version. The output will look ..