mysql interview questions
Top mysql frequently asked interview questions
If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:
$unsafe_variable = $_POST['user_input'];
mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");
That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:
INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')
What can be done to prevent this from happening?
Source: (StackOverflow)
I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. I don't need 100% accurate information at the time of read).
The task in question will be doing over 1 million database transactions an hour.
I've read a bunch of stuff on the web about the differences between MyISAM and InnoDB, and MyISAM seems like the obvious choice to me for the particular database/tables that I'll be using for this task. From what I seem to be reading, InnoDB is good if transactions are needed since row level locking is supported.
Does anybody have any experience with this type of load (or higher)? Is MyISAM the way to go?
Source: (StackOverflow)
I want to pull out duplicate records in a MySQL Database. This can be done with:
SELECT address, count(id) as cnt FROM list
GROUP BY address HAVING cnt > 1
Which results in:
100 MAIN ST 2
I would like to pull it so that it shows each row that is a duplicate. Something like:
JIM JONES 100 MAIN ST
JOHN SMITH 100 MAIN ST
Any thoughts on how this can be done? I'm trying to avoid doing the first one then looking up the duplicates with a second query in the code.
Source: (StackOverflow)
We are currently working with JavaEE and MySQL 5 in our company, but we have some queries, especially delete queries that take > 10 min to execute. We are considering a switch to PostgreSQL.
What are the advantages of PostgreSQL over MySQL, if there are any? Do you have experience with both DBs and give me a roundup about whether this is a good idea or does this fully depend on the needs of our server?
Source: (StackOverflow)
I have 2-3 different column names that I want to look up in the entire DB and list out all tables which have those columns. Any easy script?
Source: (StackOverflow)
I'm trying to install version 1.2.2 of the MySQL_python adaptor. The current version shown in PyPi is 1.2.3. Is there a way to install the older version? I found an article stating that this should do it:
pip install MySQL_python==1.2.2
When installed, however, it still shows MySQL_python-1.2.3-py2.6.egg-info in the site packages. Is this a problem specific to this package, or am I doing something wrong?
Source: (StackOverflow)
I am using MySQL in localhost as a "query tool" for performing statistics in R, that is, everytime I run a R script, I create a new database (A), create a new table (B), import the data into B, submit a query to get what I need, and then I drop B and drop A.
It's working fine for me, but I realize that the ibdata file size is increasing rapidly, I stored nothing in MySQL, but the ibdata1 file already exceeded 100 MB.
I am using more or less default MySQL setting for the setup, is there a way for I can automatically shrink/purge the ibdata1 file after a fixed period of time?
Source: (StackOverflow)
My table is:
id home datetime player resource
---|-----|------------|--------|---------
1 | 10 | 04/03/2009 | john | 399
2 | 11 | 04/03/2009 | juliet | 244
5 | 12 | 04/03/2009 | borat | 555
3 | 10 | 03/03/2009 | john | 300
4 | 11 | 03/03/2009 | juliet | 200
6 | 12 | 03/03/2009 | borat | 500
7 | 13 | 24/12/2008 | borat | 600
8 | 13 | 01/01/2009 | borat | 700
I need to select each distinct home holding the maximum value of datetime.
Result would be:
id home datetime player resource
---|-----|------------|--------|---------
1 | 10 | 04/03/2009 | john | 399
2 | 11 | 04/03/2009 | juliet | 244
5 | 12 | 04/03/2009 | borat | 555
8 | 13 | 01/01/2009 | borat | 700
I have tried:
-- 1 ..by the MySQL manual:
SELECT DISTINCT home, id, datetime as dt, player, resource
FROM topten t1
WHERE datetime = (SELECT MAX(t2.datetime) FROM topten t2
GROUP BY home )
GROUP BY datetime
ORDER BY datetime DESC
Doesn't work. Result-set has 130 rows although database holds 187.
Result includes some duplicates of home.
-- 2 ..join
SELECT s1.id, s1.home, s1.datetime, s1.player, s1.resource
FROM topten s1 JOIN
(SELECT id, MAX(datetime) AS dt
FROM topten
GROUP BY id) AS s2
ON s1.id = s2.id
ORDER BY datetime
Nope. Gives all the records.
-- 3 ..something exotic:
With various results.
Source: (StackOverflow)
For simplicity, assume all relevant fields are NOT NULL.
You can do:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1, table2
WHERE
table1.foreignkey = table2.primarykey
AND (some other conditions)
Or else:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1 INNER JOIN table2
ON table1.foreignkey = table2.primarykey
WHERE
(some other conditions)
Are those two worked on the same way by MySQL?
Source: (StackOverflow)
I have a table:
table votes (
id,
user,
email,
address,
primary key(id),
);
Now I want to make the columns user, email, address unique (together).
How do I do this in MySql?
- Of course the example is just... an example. So please don't worry about the semantics.
Source: (StackOverflow)
I have this table for documents (simplified version here):
+------+-------+--------------------------------------+
| id | rev | content |
+------+-------+--------------------------------------+
| 1 | 1 | ... |
| 2 | 1 | ... |
| 1 | 2 | ... |
| 1 | 3 | ... |
+------+-------+--------------------------------------+
How do I select one row per id and only the greatest rev?
With the above data, the result should contain two rows: [1, 3, ...] and [2, 1, ..]. I'm using MySQL.
Currently I use checks in the while loop to detect and over-write old revs from the resultset. But is this the only method to achieve the result? Isn't there a SQL solution?
Update
As the answers suggest, there is a SQL solution, and here a sqlfiddle demo.
Update 2
I noticed after adding the above sqlfiddle, the rate at which the question is upvoted has surpassed the upvote rate of the answers. That has not been the intention! The fiddle is based on the answers, especially the accepted answer.
Source: (StackOverflow)
I started by googling, and found this article which talks about mutex tables.
I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I want to insert does not already exist without using a pair of queries (ie, one query to check and one to insert is the result set is empty)?
Does a unique constraint on a field guarantee the insert will fail if it's already there?
It seems that with merely a constraint, when I issue the insert via php, the script croaks.
Source: (StackOverflow)