smallseo.info

mysql interview questions

Top mysql frequently asked interview questions

How to reset AUTO_INCREMENT in MySQL?

How can I reset the auto-increment of a field? I want it to start counting from 1 again.


Source: (StackOverflow)

Would you recommend PostgreSQL over MySQL? [closed]

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)

How to find all the tables in MySQL with specific column names in them?

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)

INNER JOIN ON vs WHERE clause

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)

How do I specify unique constraint for multiple columns in MySQL?

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)

How to output MySQL query results in CSV format?

Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format?

Here's what I'm doing now:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ

It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.


Source: (StackOverflow)

UTF-8 all the way through

I'm setting up a new server, and want to support UTF-8 fully in my web application. I have tried in the past on existing servers and always seem to end up having to fall back to ISO-8859-1.

Where exactly do I need to set the encoding/charsets? I'm aware that I need to configure Apache, MySQL and PHP to do this - is there some standard checklist I can follow, or perhaps troubleshoot where the mismatches occur?

This is for a new Linux server, running MySQL 5, PHP 5 and Apache 2.


Source: (StackOverflow)

How to get a list of MySQL user accounts

I'm using the MySQL command line utility and can navigate through a database. Now I need to see a list of user accounts. How can I do this?

I'm using MySQL version 5.4.1.


Source: (StackOverflow)

Why shouldn't I use mysql_* functions in PHP?

What are the technical reasons why I shouldn't use mysql_* functions? (e.g. mysql_query(), mysql_connect() or mysql_real_escape_string())?

Why should I use something else even if they work on my site?


Source: (StackOverflow)

How can I prevent SQL-injection in PHP?

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)

What's the difference between utf8_general_ci and utf8_unicode_ci

Between utf8_general_ci and utf8_unicode_ci, are there any differences in terms of performance?


Source: (StackOverflow)

How do I connect to a MySQL Database in Python?

How do I connect to a MySQL database using a python program?


Source: (StackOverflow)

Find duplicate records in MySQL

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)

Installing specific package versions with pip

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)

How to shrink/purge ibdata1 file in MySQL

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)