MYSQL Questions
1.Stored procetureBenefits of Stored Procedures
Why should you use stored procedures? Let's take a look at the key benefits of this technology:
Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire. Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
- Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.
functions and stored procedures seem identical. However, there are several subtle, yet important differences between the two:
Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures, rather than permission to access the underlying tables. Stored procedures provide the ability to restrict user actions at a much more granular level than standard SQL Server permissions. For example, if you have an inventory table that cashiers must update each time an item is sold (to decrement the inventory for that item by 1 unit), you can grant cashiers permission to use a decrement_item stored procedure, rather than allowing them to make arbitrary changes to the inventory table.
- Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.
E-G:
Create proceture
2.CursorCursor can be created inside the stored procedures, functions and triggers. Cursors are used for rows iteration returned by a query on a row-by-row basis. It is different from typical SQL commands that operate on all the rows in the set returned by a query at one time.
There are following types of properties of MySQL cursor:
1. Asensitive : Server may or may not make a copy of result table.
2. Read only: Can not be updated.
3. Non-scrollable: Can traverse only in one direction and can not skip rows.
3.Associate primary key
It is same as primary key, but it's act as two columns together with identical value
4.Indexing and it's typesMySQL allows four general types of indexes (keys). These indexes can be created either on single column or multi-columns however both single column index and multi columns index have some different behaviors.
Primary Key Indexes
Unique Key Indexes
Normal Indexes also known as "Non-Unique Indexes", "Ordinary Indexes", or "Indexes without constraints"
Full-Text Indexes
What are advantages and disadvantages of indexes in MySQL?
Select queries in my database and also search area of my site seems working. I assume indexing is required to add into my tables. I am new to MySQL and on learning ways. So I need to know about the MySQL indexing. Can the indexing can improve my select queries performance. Also, is any disadvantage of MySQL indexing? Please explain!
Answer No: 69
Indexing is one of the more useful features of MySQL. MySQL allows several types of indexes like primary key index, unique index, normal index also known as ("non-unique index", ordinary index, index without constraints") and full-text index. Of course, the indexes improve SELECT queries speed tremendously. but, they do have some considerable disadvantages as well.
Advantages of MySQL Indexes
Generally speaking, MySQL indexing into database gives you three advantages:
* Query optimization: Indexes make search queries much faster.
* Uniqueness: Indexes like primary key index and unique index help to avoid duplicate row data.
* Text searching: Full-text indexes in MySQL version 3.23.23, users have the opportunity to optimize searching against even large amounts of text located in any field indexed as such.
Disadvantages of MySQL indexes
When an index is created on the column(s), MySQL also creates a separate file that is sorted, and contains only the field(s) you're interested in sorting on.
Firstly, the indexes take up disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file would grow much more quickly than the data file. In the case when a table is of large table size, the index file could reach the operating system’s maximum file size.
Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, so there is a performance price to pay in case of above said writing queries because every time a record is changed, the indexes must be updated. However, you may be able to write your queries in such a way that do not cause the very noticeable performance degradation.
5.Stored proceture and triggersDefinition of stored procedure
A stored procedure, by definition, is a segment of declarative SQL code which is stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure.
A stored procedure, which calls itself, is recursive stored procedure. Almost RDMBS supports recursive stored procedure but MySQL does not support it well. Check your version of MySQL before using recursive stored procedure.
Stored Procedure in MySQL
MySQL certainly is the most open source RDBMS which is widely used by both community and enterprise but during the first decade of its existence, it did not support stored procedure, trigger, event…Since MySQL version 5.0, those features has been added to MySQL database engine to allow MySQL to be more flexible and powerful. Therefore before start the tutorial series about stored procedure, it is required that you have MySQL version > 5.x installed in your computer.
Stored Procedures Advantages
Stored procedure increases performance of application. Once created, stored procedure is compiled and stored in the database catalog. It runs faster than uncompiled SQL commands which are sent from application.
Stored procedure reduced the traffic between application and database server because instead of sending multiple uncompiled lengthy SQL commands statements, application only has to send the stored procedure name and get the data back to manipulate it futher.
Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developers don't have to program the functions which are already supported in stored procedure in all external applications.
- Stored procedure is secured. Database administrator can grant the access right to application which wants to access stored procedures in database catalog without granting any permission on the underlying database tables.
Beside those advantages, stored procedure still has its own disadvantages which are below:
Stored Procedures Disadvantages
Stored procedures make the database server high load in both memory for and processors. Instead of being focused on the storing and retrieving data, you could be asking the database server to perform a number of logical operations or a complex of business logic which is not the well designed in database server.
Stored procedure only contains declarative SQL so it is very difficult to write a procedure with complexity of business logic like other languages in application layer such as Java, C#, C++…
You cannot debug stored procedure in almost RDMBSs and in MySQL also. There are some workarounds on this problem but it still not good enough to do so.
- Writing and maintaining stored procedure usually required specialized skill set that not all developers possess. This introduced the problem in both application development and maintain phase.
Stored procedure has it own advantages and disadvantages as mentioned above. Therefore when developing application, you should balance between them to choose whether to use stored procedure or not. The following tutorial we will guide you how to leverage stored procedure in your database programming tasks with a couple of practical examples.
Triggers
SQL trigger is an SQL statements or a set of SQL statements which is stored to be activated or fired when an event associating with a database table occurs. The event can be any event including INSERT, UPDATE and DELETE.
Sometimes a trigger is referred as a special kind of stored procedure in term of procedural code inside its body. The difference between a trigger and a stored procedure is that a trigger is activated or called when an event happens in a database table, a stored procedure must be called explicitly. For example you can have some business logic to do before or after inserting a new record in a database table.
Before applying trigger in your database project, you should know its pros and cons to use it properly.
Advantages of using SQL trigger
SQL Trigger provides an alternative way to check integrity.
SQL trigger can catch the errors in business logic in the database level.
SQL trigger provides an alternative way to run scheduled tasks. With SQL trigger, you don’t have to wait to run the scheduled tasks. You can handle those tasks before or after changes being made to database tables.
- SQL trigger is very useful when you use it to audit the changes of data in a database table.
Disadvantages of using SQL trigger
SQL trigger only can provide extended validation and cannot replace all the validations. Some simple validations can be done in the application level. For example, you can validate input check in the client side by using javascript or in the server side by server script using PHP or ASP.NET.
SQL Triggers executes invisibly from client-application which connects to the database server so it is difficult to figure out what happen underlying database layer.
- SQL Triggers run every updates made to the table therefore it adds workload to the database and cause system runs slower.
6.joins and its types
Ref: http://mysqljoin.com/
* INNER JOIN / JOIN
* OUTER JOIN
* LEFT OUTER JOIN / LEFT JOIN
* RIGHT OUTER JOIN / RIGHT JOIN
* STRAIGHT JOIN
* NATURAL JOIN
* CROSS JOIN
* INNER JOIN / JOIN
The most common join operation MySQL supports is an inner join. It identifies and combines only matching rows which are stored in two related tables. A join condition which indicates how the tables are related, is added with the keywords ON or USING :
* ON is used when the relationship column has a different name
* USING is used when the relationship column has the same name in both tables
Take a look at the examples:
-- INNER JOIN with ON clause
SELECT *
FROM tableA a
INNER JOIN tableB b
ON a.someColumn = b.otherColumn
-- INNER JOIN with USING clause
SELECT *
FROM tableA a
INNER JOIN tableB b
USING(columnName)
* OUTER JOIN
MySQL supports outer joins. They identify matching rows that are stored in two different tables and add the selected columns to the result set. That’s exactly what an inner join does as well.
The difference between inner and outer join is: An outer join can identify rows without a match in the joined table. When no match was found, MySQL sets the value of columns from the joined table to NULL. This is important for example when you want to select all records from a reference table that have no related data in another:
* select all orderids from a table “orders” where no entry in a table “shipping” exists
* delete records from a table if they have no related records in another table
* and things like that.
To understand which table is treated as the reference table, we first need to understand which table is called the “left table” and which table is called the “right table”. It’s actually as easy as:
* The left table is listed on the left side of the OUTER JOIN keywords
* The right table is listed on the right side of the OUTER JOIN keywords
Here is an example:
SELECT *
FROM leftTable LEFT JOIN rightTable
USING(id)
* STRAIGHT JOIN
A STRAIGHT_JOIN identifies and combines matching rows which are stored in two related tables. This is what an inner join also does. The difference between an inner join and a straight join is that a straight join forces MySQL to read the left table first.
To see if the STRAIGHT_JOIN hint would make sense, you have to use EXPLAIN to analyze your queries. When you feel that another join order could improve the performance, use a straight join.
Left table?
The STRAIGHT_JOIN keyword or hint, forces MySQL to read the left table first. And what is the left table exactly? When you join two tables, you always have a left and a right table:
* left table is listed on the left side of the STRAIGHT_JOIN keyword
* right table is listed on the right side of the STRAIGHT_JOIN keyword
SELECT *
FROM leftTable l STRAIGHT_JOIN rightTable r
ON l.id = r.id * NATURAL JOINWhen you combine (join) two tables using a natural join, MySQL compares the columns of both tables to find columns with the same name. If the tables have nothing in common, MySQL builds the Cartesian product (see INNER JOIN for more information). If there is at least one common column, it’s used to build a join condition. To make things clear, take a look at the following statements. They generate the same result set. The only assumption we make is that they both have a column named ‘id’:
SELECT *
FROM tableA
NATURAL JOIN tableB
SELECT *
FROM tableA a, tableB b
WHERE a.id = b.id
This also works when there is more than one common column. MySQL simply implies another condition. In this example we assume there are two common columns: ‘id and ‘email’:
SELECT *
FROM tableA
NATURAL JOIN tableB
SELECT *
FROM tableA a, tableB b
WHERE a.id = b.id
AND a.email = b.email
It may look convenient to write shorter statements, but you should avoid using natural joins if possible. A natural join Let’s take a look at the drawbacks a natural join
* CROSS JOIN
In MySQL, the keywords CROSS JOIN and INNER JOIN are synonymous. ANSI SQL defines a CROSS JOIN as a join without a condition which builds the Cartesian product of two tables. In that case, MySQL combines every row in the left table with every row in the right table and returns the result set.
-- inner join without a condition: cross join
SELECT *
FROM <firstTable> CROSS JOIN <anotherTable>
When you have to build the Cartesian product of two tables, use the CROSS JOIN keywords to indicate your intensions. It makes it easy to read your statement and of course, keeps your code more portable.
7.Default Mysql port3306
8.Mysql and mysqli differenceThere is no big difference between Mysql and Mysqli. Mysqli is nothing but an improved extension of Mysql which supports OOP. mysqli extension allows you to access the functionality provided by MySQL 4.1 and above
9.n'th maximum value in mysqlUsing limit keyword
10.cursersCursors are essentially a named SQL statement which we can define in our procedures. We can then easily look at the contents of the cursor by fetching the records from it. Its a way for us to get information from our tables in one big chunk and then work on it. As always there is no better way to find out about them than actually doing one but because cursors require a little more code than things we have looked at previously we will need to look at the various stages before writing a procedure which uses cursors.
11.views
By definition, a database view or view is a virtual or logical table which is composed of result set of a SELECT query. Because the database view is similar to the database table which consists of row and column so you can retrieve and update data on it in the same way with table. The database view is dynamic because it is not related to the physical schema. Database view is stored as view definition as SELECT statements. When the tables which are the source data of a view changes; the data in the view change also.
Views Advantages
Database views provide several advantages as follows:
Simplify complex query. A view is defined by an SQL statement with join on many underlying tables with complex business logic. The view now can be used to hides the complexity of underlying tables to the end users and external applications. Only simple SQL statement is used to work with view.
Limited access data to the specific users. You may don’t want a subset of sensitive data can be retrievable by all users (both human and applications). You can use view to expose what data to which user to limit the access.
Provide extra security. Security is vital parts of any relational database management system.Views provide extra security the database management system. View allows you to create only read-only view to expose read-only data to specific user. User can only retrieve data in read-only view but cannot update it.
Computed column. The database table should not have the calculated columns in it so you can use views to create computed columns. Suppose in the orderDetails table you have quantityOrder (number of ordered product) and priceEach (price per product item) columns but it does not have computed column to get the total cost for each line of order. In this case, you can create computed column call total which is equal to quantityOrder multiple with priceEach to storedthe computed result.
- Backward compatibility. You have a center database and all applications use that database as their sources of data. One day you redesign the database to make it more clean and normalized. You then move the entire legacy data into the new database but you don’t want affect the legacy application in accessing it. In this case, you can use views to make them like the legacy database schema and return the same data so the legacy application won’t be affected.
12.Query performance tuningUse EXPLAIN to profile the query execution plan
Don't use DISTINCT when you have or could use GROUP BY
Insert performance
Batch INSERT and REPLACE
Use LOAD DATA instead of INSERT
Don't use ORDER BY RAND() if you have > ~2K records
Use SQL_NO_CACHE when you are SELECTing frequently updated data or large sets of data
Avoid wildcards at the start of LIKE queries
Avoid correlated subqueries and in select and where clause (try to avoid in)
No calculated comparisons -- isolate indexed columns
ORDER BY and LIMIT work best with equalities and covered indexes
Separate text/blobs from metadata, don't put text/blobs in results if you don't need them
Derived tables (subqueries in the FROM clause) can be useful for retrieving BLOBs without sorting them. (Self-join can speed up a query if 1st part finds the IDs and uses then to fetch the rest)
ALTER TABLE...ORDER BY can take data sorted chronologically and re-order it by a different field -- this can make queries on that field run faster (maybe this goes in indexing?)
Know when to split a complex query and join smaller ones
Delete small amounts at a time if you can
Make similar queries consistent so cache is used
Have good SQL query standards
Don't use deprecated features
Turning OR on multiple index fields (<5.0) into UNION may speed things up (with LIMIT), after 5.0 the index_merge should pick stuff up.
Don't use COUNT * on Innodb tables for every search, do it a few times and/or summary tables, or if you need it for the total # of rows, use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS()
Use INSERT ... ON DUPLICATE KEY update (INSERT IGNORE) to avoid having to SELECT
use groupwise maximum instead of subqueries
- Avoid using IN(...) when selecting on indexed fields, It will kill the performance of SELECT query.
13.caching
High-volume web applications require caching to improve application performance and scalability. Integrating a caching tier into Web application architectures can:
Memcached for MySQL
Memcached is a high-performance, open source distributed memory caching system that is used by leading web sites like YouTube, Facebook, LiveJournal and Wikipedia. Memcached can help organizations manage exponential growth by scaling their sites to handle millions of users and billions of page views.
14.MyIsam & Innodb differenceInnodb vs MyISAM:
Innodb support non-locking ANALYZE TABLE and is only required when the server has been running for a long time since it dives into the index statistics and gets the index information when the table opens.
Innodb never fragments short rows.
Innodb does not have separate index files so they do not have to be opened.
Innodb builds its indexes one row at a time in primary key order (after an ALTER), which means index trees aren’t built in optimal order and are fragmented.
There is currently no way to defragment InnoDB indexes, as InnoDB can’t build indexes by sorting in MySQL 5.0. Even dropping and recreating InnoDB indexes may result in fragmented indexes, depending on the data.
By using DATA DIRECTORY='/path/to/data/directory' or INDEX DIRECTORY='/path/to/index/directory' you can specify where the MyISAM storage engine should put a table's data file and index file. The directory must be the full path name to the directory, not a relative path.
MyISAM requires a file descriptor open for each client for the same table data file. Index file descriptors are shared b/w clients on the other hand. This causes a high usage of open files and causes the database to reach the open files limit allowed for the process (ulimit for Linux external to MySQL or open_files_limit variable inside MySQL).
MyISAM can possibly cause information_schema to respond way too late (not confirmed through Innodb yet).
For MyISAM tables, table header has to be modified each time the table is opened. (http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/comment-page-1/#comment-773324) In some cases, MyISAM supports building idexes by sorting (after ALTERs).
- MyISAM databases can be read from readonly media eg. CD.
15.Mysql - Storage engines
MySQL Native Storage Engines
MySQL currently offers a number of its own native Storage Engines, including:
InnoDB MyISAM Cluster Federated Archive
|
16.Mysql - examples
-- Stored Procedure--
DROP PROCEDURE IF EXISTS trainee.productpricing;
CREATE PROCEDURE trainee.productpricing()
BEGIN
SELECT Name FROM trainee.City;
END;
DROP PROCEDURE IF EXISTS trainee.siva_sp_ucallist;
CREATE PROCEDURE trainee.siva_sp_ucallist(IN vUserId INT )
COMMENT 'List the user information like team,child,user playing team'
BEGIN
SELECT plg_team.team_name,0 FROM plg_team WHERE plg_team.team_user_id=vUserId;
END;
-- Function--
DROP FUNCTION IF EXISTS trainee.fun_grosspay;
CREATE FUNCTION trainee.fun_grosspay(pmid INT) RETURNS decimal(10,2)
BEGIN
DECLARE vBasic , vIncr , vNetPay DECIMAL(10,2);
SELECT emps_basicpay , emps_increment INTO vBasic , vIncr FROM trne_emp_salary WHERE emps_generated_id = pmid;
SET vNetPay = vBasic + vIncr ;
RETURN vNetPay ;
# SELECT fun_grosspay(101);
END;
-- View--
CREATE OR REPLACE VIEW viw_name AS
SELECT column_name from tab_name;
1.What?s the default port for MySQL Server?
3306
2.What is SERIAL data type in MySQL?
BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
3.Describe the use of PL/SQL tables
PL/SQL tables are scalar arrays that can be referenced by a
binary integer. They can be used to hold values for use in later queries
or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.
4.Explain about database design?
Database design is also called as Data modeling. It is used for long-term management of database. This process is used to store information and to keep data for long term. Creating an efficient structure helps you to channelize information into good channels.
5.Which version of MySQL supports Subquery?
mysql 4.0 onwards support subquery.
6.Explain the difference between MyISAM Static and MyISAM Dynamic?
MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.
7.What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?
It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.
8.What does myisamchk do?
It compressed the MyISAM tables, which reduces their disk usage.
9.Can you save your connection settings to a conf file?
Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it?s not readable by others.
10.Explain about MYSQL and its features?
MYSQL is a relational data base model and most widely RDBMS all over the world. It provides multi user access to databases. Source code for this database is available under General public license. It became popular for web applications and this is widely used. Performance and reliability became its features.
11.How will retrieve nth level categories from one query in mysql ?
SELECT c1.CategoryID c1.Name c2.CategoryID c2.Name
FROM category AS c1 category AS c2
WHERE c1.CategoryID c2.ParentID
ORDER BY c1.CategoryID c2.CategoryID
12.what is difference between candidate key and primary key?
Any attribute that is uniquely identify a row in a table is candidate key for the table. We select one of the candidate key as Primary key. All candidate keys which are not chosen as primary key are Alternate keys. The key which uniquely identify the rows of the table and which is made up of more than one attribute is called Composite key. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key one of them will become the primary key and the rest are called alternate keys
13.What does "tee" command do in MySQL?
"tee" followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.
14.State two considerations which can improve the performance of MYSQL?
Two considerations which can improve the performance of MYSQL are as follows: -
1) Fixed length fields take up more space than variable length fields but they are a bit faster.
2) Size of the field should be restricted to the smallest possible value based upon the largest input value.
15.How do you start and stop MySQL on Windows?
net start MySQL, net stop MySQL
16.Describe the use of %ROWTYPE and %TYPE in PL/SQL?
%ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.
17.How do you use Outer Join in MySQL?
select sname,phone,feebal,feepaid from student s left outer join fees f
on s.sid=f.sid;
union
select sname,phone,feebal from student s right outer join fees f on s.si
d=f.sid;
outer join main purpose it is retrieve matching and unmatching records from data base
it is classified 3 types
lift outer join
right outer join
full outer join
18.What is the Oracle rowid counterpart in MySQL?
As row id is in oracle same as id is in mysql but in mysql there is no concept of rownum.
19.Use mysqldump to create a copy of the database?
mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
20.State some security recommendations while using MYSQL?
Some of the security recommendations which should be followed while using MYSQL are as follows: -
1) Minimal privileges to users in the network.
2) Super and process privileges should be granted minimally.
3) File privileges should be granted minimally to administrators.
4) Validation of data and queries should be thoroughly checked.
21.When is a declare statement needed ?
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.
22.What are the advantages of Mysql comparing with oracle?
MySql has many advantages in comparison with Oracle.<br><br>1 - MySql is Open source, which can be available any time <br><br>2 - MySql has no cost of development purpose.<br><br>3 - MySql has most of features , which oracle provides <br><br>4 - MySql day by day updating with new facilities.<br><br>5 - Good for small application.<br><br>6 - easy to learn and to become master.<br><br>7 - MySql has a good power these days.<br><br>even though MySql having so many advantages, Oracle is best database ever in Software development.<br>
mysql is the famour batabase in the real time. it can be used for various forms.mysql is opensource and easy to learn and apply in our medium time project
23.How to display nth highest record in a table for example?How to display 4th highest (salary) record from customer table?
Query: SELECT sal FROM `emp` order by sal desc limit (n-1),1If the question: "how to display 4th highest (salary) record from customer table."The query will SELECT sal FROM `emp` order by sal desc limit 3,1
select sal from emp order by descending where rownum=4
24.Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.
25.What are some good ideas regarding user security in MySQL?
There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.
26.Explain the difference between mysql and mysqli interfaces in PHP?
mysqli is the object-oriented version of mysql library functions.
27.Explain the difference between BOOL, TINYINT and BIT.?
Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.
28.How we can count duplicate entery in particular table against Primary Key ? What are constraints?
The syntax in the previous answer (where count(*) > 1) is very questionable. suppose you think that you have duplicate employee numbers. there's no need to count them to find out which values were duplicate but the followin SQL will show only the empnos that are duplicate and how many exist in the table:
Select empno, count(*)
from employee
group by empno
having count(*) > 1
Generally speaking aggregate functions (count, sum, avg etc.) go in the HAVING clause. I know some systems allow them in the WHERE clause but you must be very careful in interpreting the result. WHERE COUNT(*) > 1 will absolutely NOT work in DB2 or ORACLE. Sybase and SQLServer is a different animal.
primary key will not take duplicate entries.
29.How do you control the max size of a HEAP table?
MySQL config variable max_heap_table_size.
30.Explain about the rules which should be followed while assigning a username?
A username has a maximum length of 16 characters. Spaces should be avoided while creating username because they are case sensitive. Hostname will be the computer from which you are going to connect. The best way to specify a username is to connect through a local host.
31.Explain about normalization?
Applying specific rules (normal forms) to the database is the primary process. These rules should be applied in the order specified starting with the first normal form. These rules should be adhered by every database they are
1) Each column should have only one value
2) Repeating columns of data cannot be done.
It is a process of analyzing the given relation schema based on their functional dependencies and primary key to minimize
1. the redundancy
2. the insertion , deletion , updation anomaly
32.How do you change a password for an existing user via mysqladmin?
mysqladmin -u root -p password "newpassword"
33.What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?
On initialization places a zero in that column, on future updates puts the current value of the timestamp in.
On every update on that table timestamp vale will change it will be current timestamp of server.
34.How to create MYSQL new users?
There are many different ways to establish users and privileges in MYSQL. Client and GRANT command assure you about a safe connection. The syntax for establishing new users and privileges is as follows
GRANT privileges ON database.* TO
?username?@?hostname? This can be identified by the password. Privileges can be assigned one by one or by specifying all.
35.Explain some of the uses of MYSQL?
Mysql is widely used for web applications. This acts as a database component for many languages such as BAMP, WAMP, LAMP and MAMP. PHP and ruby added to popularity to the language. PHP and MYSQL are very much used for managing database and web applications.
36.What are the advantages of mysql comparing with oracle?
MySql has many advantages in comparison to Oracle.
1 - MySql is Open source, which can be available any time
2 - MySql has no cost of development purpose.
3 - MySql has most of features , which oracle provides
4 - MySql day by day updating with new facilities.
5 - Good for small application.
6 - easy to learn and to become master.
7 - MySql has a good power these days.
even though MySql having so many advantages, Oracle is best database ever in Software development.
37.What are HEAP tables in MySQL?
HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.
38.Explain about HEAP table?
This type of table is stored in the memory. Speed of execution of this table is very commendable. There are associated disadvantages associated with this table the primary one being loss of stored memory which occurs when there is power failure and can cause the server to run out of memory. Columns with AUTO_INCREMENT, TEXT characteristics and BLOB are not supported.
39.How to see the database architecture in MySQL?
mysql_stat();
40.Explain federated tables?
Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
41.Explain about MyISAM table?
This feature is a default type for tables. This table is not so much considered for transactions because it is not considered as safe but this kind of table is very fast in execution. The maximum key length is 1024 bytes and 64 keys per table. Size of this table entirely depends upon the operating system.
42.Explain about creating database?
CREATE DATABASE command will create you a database with the assigned name by the user. This is an optional statement but when you actually assign a name it checks for similarity and gives error if it encounters one. CREATE DATABASE models help you to create classic models.
43.How do you configure mysql on linux?
For the configuration of MySql these dependencies are required as:
mysql-5.0
mysql-server-5.0
mysqlclient9-5.0
mysql-devel-5.0
php-mysql-5.0
install by rpm -i vh mysql-server-5.0.rpm and others also same
If configured in RHEL the .rpm are needed.
if configured in debian the .deb are needed and also one can do configuration as
apt-get install mysql-server-5.0
apt-get install mysql-client-5.0
After installing
start the Mysql as
/etc/rc.d/init.d/mysqld start (RHEL)
/etc/init.d/mysql start (Debian)
The default configuration file is in /etc/my.cnf
Ans ALso reset or assign a password as
mysqladmin -u root password 'new-password
and do the rest.
44.How MySQL is different from SQL?
1.SQL is structural quary language but mysql is database package.
2.MySQL is an opensource database but SQL is an microsoft product.
MySQL is a open source database where as oracle pl/sql is structural query language and Ms sql server is developed by Microsoft.
45.State some of the features of MYSQL?
Some of the features presented by MYSQL are
1) Multiple storage engines, Native storage engines, Partner developed storage engines, custom storage engines, community developed storage engines and commit grouping.
Some of these features present in MYSQL are not present in most popular RDBMS.
46.How do you start MySQL on Linux?
/etc/init.d/mysql start
47.Explain about the time stamp field?
TIMESTAMP filed occurs when an INSERT and UPDATE field occurs when there is no value specified for the field. There are many behaviors for TIMESTAMP field and it depends upon the version of MYSQL.
48.What happens when we don?t use Console option?
If console option is not present or used during the start of MYSQL then all the output will be written to the error log file. It is always better to use the console option because it reduces problem on the server. Error log file will be stored with an extension .err. Any text editor can read this file.
49.Explain about primary keys?
MYSQL allows only one primary key. This primary key can be used on multiple tables. There are many rules which should be followed such as it shouldn?t be null and it can never change. Primary key assigned should be unique it cannot be matched with any other keys.
a primary should have unique values.
a primary does not contain null values.
a primary key has clustred index
50.How many drivers in MYSQL?
There are eleven drivers in MYSQL .Six of them from MySQL AB and five by MYSQL Communities.They are1.PHP Driver2.ODBC Driver3.JDBC Driver4.ado.net5.mxj6.CAPI1PHP DRIVER2.PERL DRIVER3.PYTHON DRIVER4.RUBY DRIVER5.C++ WRAPPER
51.What are CSV tables?
Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.
52.Explain about a security flaw which is present while creating a username?
Naming MYSQL databases has to be very careful because any database starting with the test name can be accessed by every one on the network. Make sure that you don?t start the databases naming with test. It should be used only for experimental purposes only.
53.State some of the disadvantages present in MYSQL?
MYSQL standard has diverted on the standards of implementation of Null and default values. Arithmetic operations are susceptible to integer over flow. Mode of operation of SQL is set to unusual tolerant state which developers hate.
54.What are the limitations of mysql in Comparison of Oracle?
Every column in a database table is assigned a data type. The data type maybe native to the database management system or may be a custom data type. Each Oracle9i Database native data type belongs to one of six categories: character number date large object (LOB) raw and row identifier. Oracle date data types only store valid dates support automatic time zone conversion and store sub-second precision. MySQL categorizes data types into string numeric and date and time types. Unlike Oracle MySQL date data types can store invalid dates do not support time zones and the precision of time values is limited to one second
User ROLES functionality not supported in MySQL:
Oracle supports ROLES functionality (grouping the privileges under one name as a ROLE) for assigning the privileges to the similar user accounts. In MySQL, this is not possible, and even if we have 1000 user accounts to granted a new privilege, or a new usre to be created, we have to do it manually for each user.
55.What happens if a table has one column defined as TIMESTAMP?
That field gets the current timestamp whenever the row gets altered.
56.How To Present a Past Time in Hours, Minutes and Seconds?
If you want show an article was posted "n hours n minutes and n seconds ago", you can use the TIMEDIFF(NOW(), pastTime) function as shown in the following tutorial exercise:
SELECT TIMEDIFF(NOW(), '2006-07-01 04:09:49') FROM DUAL;
06:42:58
SELECT TIME_FORMAT(TIMEDIFF(NOW(), '2006-06-30 04:09:49'),
'%H hours, %i minutes and %s seconds ago.') FROM DUAL;
30 hours, 45 minutes and 22 seconds ago.
57.How To Drop an Existing View in MySQL?
If you have an existing view, and you don't want it anymore, you can delete it by using the "DROP VIEW viewName" statement as shown in the following script:
mysql> DROP VIEW faqComment;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM faqComment;
ERROR 1146 (42S02): Table 'ggl.faqcomment' doesn't exist
58.What Is "mysqladmin" in MySQL?
"mysqladmin" is a command-line interface for administrators to perform server administration tasks. It support a number of commonly used commands like:
► "mysqladmin shutdown" - Shuts down the server.
► "mysqladmin ping" - Checks if the server is alive or not.
► "mysqladmin status" - Displays several important server status values.
► "mysqladmin version" - Displays version information of the server.
► "mysqladmin create databaseName" - Creates a new database.
► "mysqladmin drop databaseName" - Drops an existing database.
59.How To Create a New View in MySQL?
You can create a new view based on one or more existing tables by using the "CREATE VIEW viewName AS selectStatement" statement as shown in the following script:
mysql> CREATE TABLE comment (faqID INTEGER,
message VARCHAR(256));
Query OK, 0 rows affected (0.45 sec)
mysql> INSERT INTO comment VALUES (1, 'I like it');
Query OK, 1 row affected (0.00 sec)
mysql> CREATE VIEW faqComment AS SELECT f.id, f.title,
f.description, c.message FROM faq f, comment c
WHERE f.id = c.faqID;
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT * FROM faqComment;
60.What Is MySQL?
MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com.
61.What Is Primary Key?
A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications.
62.What Is Foreign Key?
A foreign key is a single column or multiple columns defined to have values that can be mapped to a primary key in another table.
63.What Is Index?
An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.
64.What Is View?
A view is a logical table defined by a query statement.
65.What Is Join?
Join is data retrieval operation that combines rows from multiple tables under certain matching conditions to form a single row.
66.What Is Union?
Join is data retrieval operation that combines multiple query outputs of the same structure into a single output.
67.What Is ISAM?
ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.
68.What Is MyISAM?
MyISAM is a storage engine used as the default storage engine for MySQL database. MyISAM is based on the ISAM (Indexed Sequential Access Method) concept and offers fast data storage and retrieval. But it is not transaction safe.
69.What Is InnoDB?
InnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).
70.What Is BDB (BerkeleyDB)?
BDB (BerkeleyDB) is transaction safe storage engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, Inc. (an Oracle company now).
71.What Is CSV?
CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.
72.What Is Transaction?
A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).
73.What Is Commit?
Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.
74.What Is Rollback?
Rollback is a way to terminate a transaction with all database changes not saving to the database server.
75.How Do You Know If Your MySQL Server Is Alive?
If you want to know whether your MySQL server is alive, you can use the "mysqladmin" program in a command window as shown in the following tutorial:
>cd mysql_in
>mysqladmin -u root ping
mysqld is alive
The "mysqld is alive" message tells you that your MySQL server is running ok. If your MySQL server is not running, you will get a "connect ... failed" message.
76.How Do You Know the Version of Your MySQL Server?
If you want to know the version number of your MySQL server, you can use the "mysqladmin" program in a command window as shown in the following tutorial:
>cd mysql_in
>mysqladmin -u root version
mysqladmin Ver 8.41 Distrib 5.0.24, for Win32 on ia32
Copyright (C) 2000 MySQL AB & MySQL Finland AB
& TCX DataKonsult AB
...
Server version 5.0.24-community
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 25 min 9 sec
Threads: 1 Questions: 2 Slow queries: 0 Opens: 12
Flush tables: 1 Open tables: 6
Queries per second avg: 0.001
The output in the above example tells you that the version number is 5.0.24.
77.What Is "mysqld"?
"mysqld" is MySQL server daemon program which runs quietly in background on your computer system. Invoking "mysqld" will start the MySQL server on your system. Terminating "mysqld" will shutdown the MySQL server. Here is a tutorial example of invoking "mysqld" with the "--console" option:
>cd mysql_in
>mysqld --console
... 21:52:54 InnoDB: Started; log sequence number 0 43655
... 21:52:54 [Note] mysqld: ready for connections.
Version: '5.0.24-community' socket: '' port: 3306
MySQL Community Edition (GPL)
The returning message indicates that "mysqld" running now, and your MySQL server is ready to take client connections. To know about "mysqld", read other parts of this FAQ collection.
78.How To Shutdown MySQL Server?
If you want to shutdown your MySQL server, you can run the "mysqladmin" program in a command window as shown in the following tutorial:
>cd mysql_in
>mysqladmin shutdown
79.What Tools Available for Managing MySQL Server?
MySQL comes with the following programs as administration tools for you to manage your MySQL server:
► mysqld - MySQL server daemon. You can use "mysqld" to start your MySQL server.
► mysqladmin - A command-line interface for administrators to perform server administration tasks.
► mysql - A command-line interface for end users to manage user data objects.
► mysqlcheck - A command-line interface for administrators to check and repair tables.
► mysqlshow - A command-line interface for end users to see information on tables and columns.
► mysqldump - A command-line interface for administrators or end users to export data from the server to files.
► mysqlimport - A command-line interface for administrators or end users to load data files into tables program tool to load data into tables.
80.How To Show All Tables with "mysql"?
If you want to see all the tables in a database, you run the non-SQL command "SHOW TABLES" at the "mysql" prompt. See the following tutorial exercise for example:
>cd mysql_in
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14 to server version: 5.0.24
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| links |
+----------------+
1 row in set (0.01 sec)
The output shows you that there is only one table in the "test" database.
81.What Is "mysqlcheck"?
"mysqlcheck" is a command-line interface for administrators to check and repair tables. Here are some sample commands supported by "mysqlcheck":
► "mysqlcheck databaseName tableName" - Checks the specified table in the specified database.
► "mysqlcheck databaseName" - Checks all tables in the specified database.
► "mysqlcheck --all-databases" - Checks all tables in all databases.
► "mysqlcheck --analyze databaseName tableName" - Analyzes the specified table in the specified database.
► "mysqlcheck --repair databaseName tableName" - Repairs the specified table in the specified database.
82.How To Analyze Tables with "mysqlcheck"?
If you want analyze tables with "mysqlcheck", you need to use the "--analyze" option. The following tutorial exercise shows you how to analyze all tables in "mysql" database:
>cd mysql_in
>mysqlcheck -u root --analyze mysql
mysql.columns_priv Table is already up to date
mysql.db Table is already up to date
mysql.func Table is already up to date
mysql.help_category Table is already up to date
mysql.help_keyword Table is already up to date
mysql.help_relation Table is already up to date
mysql.help_topic Table is already up to date
mysql.host Table is already up to date
mysql.proc Table is already up to date
mysql.tables_priv Table is already up to date
mysql.time_zone Table is already up to date
mysql.time_zone_leap_second Table is already up to date
mysql.time_zone_name Table is already up to date
mysql.time_zone_transition Table is already up to date
mysql.time_zone_transition_type Table is already up to date
mysql.user Table is already up to date
83.What Is "mysqlshow"?
"mysqlshow" is a command-line interface for end users to see information on tables and columns. Here are some sample commands supported by "mysqlshow":
► "mysqlshow" - Shows all the databases.
► "mysqlshow databaseName" - Shows all the tables in the specified database.
► "mysqlshow databaseName tableName" - Shows all the columns in the specified table.
► "mysqlshow --verbose" - Shows all the databases with extra information.
► "mysqlshow --verbose my%" - Shows all the databases who's names match the pattern "my%" with extra information.
► "mysqlshow --verbose mysql time%" - Shows all the tables who's names match the pattern "time%" in "mysql" database with extra information.
84.What Is "mysqldump"?
"mysqldump" - A command-line interface for administrators or end users to export data from the server to files. Here are some sample commands supported by "mysqldump":
► "mysqldump databaseName tableName" - Dumps the specified table in the specified database.
► "mysqldump databaseName" - Dumps all the tables in the specified database.
85.How To Dump a Table to a File with "mysqldump"?
If you want to dump all rows in a table from the server to a file, you can use "mysqldump" with the "-f fileName" option as show in the following tutorial exercise:
>cd mysql_in
>mysqldump -u root -r emplinks.txt test links
>type emplinks.txt
>type emplinks.txt | more
-- MySQL dump 10.10
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.0.24-community...
The dump file contains SQL statements that you can use to restore the table and its data content.
86.What Is "mysqlimport"?
"mysqlimport" - A command-line interface for administrators or end users to load data files into tables program tool to load data into tables. Here is a sample commands supported by "mysqlimport":
► "mysqlimport databaseName fileName" - Imports the data from the specified file to the specified database. The data will be loaded into the table who's name matches the specified file name.
87.How To Load Data Files into Tables with "mysqlimport"?
If you want to load a data file directly into a table, you need to prepare the data file as one line per data row, and use tab character as the column delimiter. The data file name should match the target table name. The following is a good tutorial exercise on using "mysqlimport":
>cd mysql_in
>type emplinks.tab
www.GlobalGuideLine.com
www.mysql.com
>mysqlimport -u root test emplinks.tab
test.links: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
>mysql -u root -e "SELECT * FROM links" test
+-------------------------+
| name |
+-------------------------+
| www.vyomtechnosoft.com |
| www.vyomtechnosoft.com |
| www.mysql.com |
+-------------------------+
88.What Is the Command Line End User Interface - mysql?
"mysql", official name is "MySQL monitor", is a command-line interface for end users to manage user data objects. "mysql" has the following main features:
* "mysql" is command line interface. It is not a Graphical User Interface (GUI).
* "mysql" supports all standard SQL Data Definition Language (DDL) commands for the server to execute.
► "mysql" supports all standard SQL Data Manipulation Language (DML) commands for the server to execute.
► "mysql" supports many of non-SQL commands that "mysql" will execute by itself.
► "mysql" provides access to the server-side help system.
► "mysql" allows command files to be executed in a batch mode.
► "mysql" allows query output to be formatted as HTML tables.
► "mysql" allows query output to be formatted as XML elements.
89.What Are the "mysql" Command Line Arguments?
"mysql" supports only one optional command line argument, "database". But "mysql" allows the operating system to redirect input and output streams at the command line level. Here are some good examples:
► "mysql databaseName" - Starts "mysql" in interactive mode and use the specified database.
► "mysql < fileName" - Starts "mysql" in batch mode and executes all commands in the specified file.
► "mysql < fileName > fileName" - Starts "mysql" in batch mode, executes all commands in the specified file, and write output to the specified file.
Here is a tutorial exercise of how to use the command line argument to specify the database to use:
>cd mysql_in
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4 to server version: 5.0.24
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| links |
+----------------+
1 row in set (0.00 sec)
mysql> quit;
Bye
90.How Many SQL DDL Commands Are Supported by "mysql"?
There are 4 SQL Data Definition Language (DDL) commands that are supported by "mysql". They are listed below with short descriptions:
► "CREATE dataObjectType dataObjectName" - Creates new databases, tables, views, triggers, indexes, and other data objects.
► "RENAME dataObjectType dataObjectName" - Renames existing databases, tables, views, triggers, indexes, and other data objects.
► "ALTER dataObjectType dataObjectName" - Alters properties of existing databases, tables, views, triggers, indexes, and other data objects.
► "DROP dataObjectType dataObjectName" - Drops existing databases, tables, views, triggers, indexes, and other data objects.
Here is a tutorial exercise of how to use DDL commands to create a database and a table:
>cd mysql_in
>mysql -u root
mysql> CREATE DATABASE ggl;
Query OK, 1 row affected (0.50 sec)
mysql> CREATE TABLE articles (name VARCHAR(80));
Query OK, 0 rows affected (0.25 sec)
mysql> DROP DATABASE ggl;
Query OK, 0 rows affected (0.41 sec)
91.What Are the Non-Standard SQL Commands Supported by "mysql"?
There are many non-standard SQL commands that are supported by "mysql". Here is short list of some commonly used commands:
► "SHOW infoName" - Shows basic information of based on the specified information name.
► "SHOW infoName" - Shows basic information of based on the specified information name.
► "SET ..." - Sets new values to server or connection session variables.
► "GRANT ..." - Grants access privileges to users.
► "REVOKE ..." - Revokes access privileges from users.
► "CHECK TABLE tableName" - Checks the specified table for errors.
► "ANALYZE TABLE tableName" - Analyzes the specified table.
► "REPAIR TABLE tableName" - Repairs the specified table.
► "BACKUP TABLE tableName" - Backs up the specified table.
► "RESTORE TABLE tableName" - Restores the specified table.
► "USE databaseName" - Uses the specified database as the current database.
► "HELP topicName" - Returns help information on the specified topic.
Here is a tutorial exercise of how to use SHOW, USE and ANALYZE commands in "mysql":
>cd mysql_in
>mysql -u root
mysql> USE test;
Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| articles |
| links |
+----------------+
2 rows in set (0.00 sec)
mysql> ANALYZE TABLE links;
+----------+-------+--------+---------------------------+
|Table |Op |Msg_type|Msg_text |
+----------+-------+--------+---------------------------+
|test.links|analyze|status |Table is already up to date|
+----------+-------+--------+---------------------------+
1 row in set (0.14 sec)
92.How To Run "mysql" Commands from a Batch File?
If you have group of "mysql" commands that need to be executed repeatedly, you can put them into a file, and run them from the file in "mysql" batch mode. Here is a batch file, emplinks.sql, contains following commands:
USE test;
INSERT INTO links VALUES (10, 'www.GlobalGuideLine.com');
SELECT * FROM links;
To run this batch file, you need to follow this tutorial:
>cd mysql_in
>mysql -u root < emplinks.sql
id name
1 www.vyomtechnosoft.com
10 www.vyomtechnosoft.com
93.How To Return Query Output in HTML Format?
By default, "mysql" returns query output in text table format. If you want to receive query output in HTML format, you need to use the "-H" command option. Here is a good tutorial exercise:
>cd mysql_in
>mysql -u root -H test
mysql> SELECT * FROM links;
<TABLE BORDER=1><TR><TH>id</TH><TH>name</TH></TR>
<TR><TD>1</TD><TD>www.vyomtchnosoft.com</TD></TR>
<TR><TD>10</TD><TD>www.vyomtchnosoft.com</TD></TR></TABLE>
2 rows in set (0.00 sec)
94.How To Return Query Output in XML Format?
By default, "mysql" returns query output in text table format. If you want to receive query output in XML format, you need to use the "-X" command option. Here is a good tutorial exercise:
>cd mysql_in
>mysql -u root -X test
mysql> SELECT * FROM links;
<?xml version="1.0"?>
<resultset statement="SELECT * FROM links">
<row>
<field name="id">1</field>
<field name="name">www.vyomtechnosoft.com</field>
</row>
<row>
<field name="id">10</field>
<field name="name">www.vyomtechnosoft.com</field>
</row>
</resultset>
2 rows in set (0.00 sec)
95.What Are the Differences between CHAR and NCHAR?
Both CHAR and NCHAR are fixed length string data types. But they have the following differences:
► CHAR's full name is CHARACTER.
► NCHAR's full name is NATIONAL CHARACTER.
► By default, CHAR uses ASCII character set. So 1 character is always stored as 1 byte.
► By default, NCHAR uses Unicode character set. NCHAR data are stored in UTF8 format. So 1 character could be stored as 1 byte or upto 4 bytes.
► Both CHAR and NCHAR columns are defined with fixed lengths in units of characters.
The following column definitions are the same:
CREATE TABLE faq (Title NCHAR(80));
CREATE TABLE faq (Title NATIONAL CHAR(80));
CREATE TABLE faq (Title NATIONAL CHARACTER(80));
CREATE TABLE faq (Title CHAR(80) CHARACTER SET utf8);
CREATE TABLE faq (Title CHARACTER(80) CHARACTER SET utf8);
96.What Are the Differences between CHAR and VARCHAR?
CHAR and VARCHAR are both ASCII character data types by default. But they have the following major differences:
► CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
► VARCHAR stores values in variable lengths. Values are not padded with any characters. But 1 or 2 extra bytes are added to store the length of the data.
The table below shows you a good comparison of CHAR and VARCHAR data types:
Value CHAR(4) Length
'' ' ' 4 bytes
'ab' 'ab ' 4 bytes
'abcd' 'abcd' 4 bytes
Value VARCHAR(4) Length
'' '' 1 byte
'ab' 'ab' 3 bytes
'abcd' 'abcd' 5 bytes
97.What Are the Differences between CHAR and VARCHAR?
CHAR and VARCHAR are both ASCII character data types by default. But they have the following major differences:
► CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
► VARCHAR stores values in variable lengths. Values are not padded with any characters. But 1 or 2 extra bytes are added to store the length of the data.
The table below shows you a good comparison of CHAR and VARCHAR data types:
Value CHAR(4) Length
'' ' ' 4 bytes
'ab' 'ab ' 4 bytes
'abcd' 'abcd' 4 bytes
Value VARCHAR(4) Length
'' '' 1 byte
'ab' 'ab' 3 bytes
'abcd' 'abcd' 5 bytes
98.What Are the Differences between BINARY and VARBINARY?
Both BINARY and VARBINARY are both binary byte data types. But they have the following major differences:
► BINARY stores values in fixed lengths. Values are padded with 0x00.
► VARBINARY stores values in variable lengths. Values are not padded with any bytes. But 1 or 2 extra bytes are added to store the length of the data.
99.What Are Numeric Data Types?
MySQL supports the following numeric data types:
► BIT(n) - An integer with n bits.
► BOOL same as BOOLEAN - Boolean values stored in 1 bit.
► TINYINT - A small integer stored in 1 byte.
► SMALLINT - A small integer stored in 2 bytes.
► MEDIUMINT - A medium integer stored in 3 bytes.
► INT same as INTEGER - An integer stored in 4 bytes.
► BIGINT - An integer stored in 8 bytes.
► FLOAT - A single-precision floating-point number stored in 4 bytes.
► DOUBLE same as DOUBLE PRECISION - A double-precision floating-point number stored in 8 bytes.
► REAL - Same DOUBLE by default.
► DECIMAL(m,d) - A fixed-point number with m as the precision (total number of digits) and d as the scale (number of digits after the decimal point).
► Date and Time Data Types - DATE, DATETIME, TIMESTAMP, TIME, YEAR.
100.How To Use Regular Expression in Pattern Match Conditions?
If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern condition: REGEXP. The following tutorial exercise provides you some good examples:
SELECT 'GlobalGuideLine.com' REGEXP '.*ggl.*' FROM DUAL;
1
SELECT 'GlobalGuideLine.com' REGEXP '.*com$' FROM DUAL;
1
SELECT 'GlobalGuideLine.com' REGEXP '^F.*' FROM DUAL;
1
101.How To Use CASE Expression?
There are 2 ways to use the CASE expression. The first way is to return one of the predefined values based on the comparison of a given value to a list of target values. The second way is to return one of the predefined values based on a list of conditions. Here is the syntax of both types of CASE expressions:
CASE value WHEN target_value THEN result
WHEN target_value THEN result
WHEN target_value THEN result
...
ELSE result
END
CASE WHEN condition THEN result
WHEN condition THEN result
WHEN condition THEN result
...
ELSE result
END
The tutorial exercise below gives two good examples:
SELECT CASE 'Sun' WHEN 'Mon' THEN 'Open'
WHEN "Fri" THEN "Open" ELSE 'Closed' END FROM DUAL;
Closed
SELECT CASE WHEN HOUR(CURRENT_TIME())<9 THEN 'Closed'
WHEN HOUR(CURRENT_TIME())>17 THEN 'Closed'
ELSE 'Open' END FROM DUAL;
Closed
102.What Are Date and Time Data Types in MySQL?
MySQL supports the following date and time data types:
► DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD".
► DATETIME - A date with the time of day in the range of '1000-01-01 00:00:00' and '9999-12-31 23:59:59'. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIMESTAMP - A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► YEAR - A year in 4 digits in the range of 1901 and 2155. Default YEAR format is "YYYY".
103.How To Write Date and Time Literals?
MySQL offers a number of formats for you to use to enter date and time literals:
► ANSI standard format: "YYYY-MM-DD HH:MM:SS".
► Non-standard limiters. Like: "YYYY/MM/DD HH^MM^SS" or "YYYY.MM.DD HH-MM-SS".
► No limiters. Like: "YYYYMMDD" for a date or "HHMMSS" for a time.
► Decimal numbers. Like: 8 digits dddddddd for a date or 6 digits dddddd for a time.
The tutorial exercise below gives you some good examples:
SELECT DATE('1997-01-31') FROM DUAL;
1997-01-31
SELECT DATE('1997-01-31 09:26:50') FROM DUAL;
1997-01-31
SELECT TIME('1997-01-31 09:26:50') FROM DUAL;
09:26:50
SELECT DATE('1997/01/31 09^26^50') FROM DUAL;
1997-01-31
SELECT TIME('1997/01/31 09^26^50') FROM DUAL;
09:26:50
SELECT DATE('19970131') FROM DUAL;
1997-01-31
SELECT TIME('092650') FROM DUAL;
09:26:50
SELECT DATE(19970131) FROM DUAL; -- Crazy format
1997-01-31
SELECT TIME(092650) FROM DUAL; -- Crazy format
09:26:50
104.How To Enter Microseconds in SQL Statements?
If you want to enter microseconds in a SQL statements, you can enter them right after the time string as a 6-digit number delimited with '.'. '0' will be padded to right if not enough digits. Here are some good examples:
SELECT TIME('1997/01/31 09^26^50.123') FROM DUAL;
09:26:50.123000
SELECT TIME('1997/01/31 09^26^50.000123') FROM DUAL;
09:26:50.000123
105.How To Convert Dates to Character Strings?
You can convert dates to character strings using the DATE_FORMAT(date, format) function. MySQL supports the following basic formatting codes:
► %a Abbreviated weekday name (Sun..Sat)
► %b Abbreviated month name (Jan..Dec)
► %c Month, numeric (0..12)
► %D Day of the month with English suffix (0th, 1st, 2nd, 3rd,...)
► %d Day of the month, numeric (00..31)
► %e Day of the month, numeric (0..31)
► %f Microseconds (000000..999999)
► %H Hour (00..23)
► %h Hour (01..12)
► %I Hour (01..12)
► %i Minutes, numeric (00..59)
► %j Day of year (001..366)
► %k Hour (0..23)
► %l Hour (1..12)
► %M Month name (January..December)
► %m Month, numeric (00..12)
► %p AM or PM
► %r Time, 12-hour (hh:mm:ss followed by AM or PM)
► %S Seconds (00..59)
► %s Seconds (00..59)
► %T Time, 24-hour (hh:mm:ss)
► %W Weekday name (Sunday..Saturday)
► %w Day of the week (0=Sunday..6=Saturday)
► %Y Year, numeric, four digits
► %y Year, numeric (two digits)
The tutorial exercise below shows you some good examples:
SELECT DATE_FORMAT('1997-01-31', '%W, %M %e, %Y') FROM DUAL;
Friday, January 31, 1997
SELECT DATE_FORMAT('1997-01-31 09:26:50.000123',
'%W, %M %e, %Y, at %h:%i:%s %p') FROM DUAL;
Friday, January 31, 1997, at 09:26:50 AM |
SELECT DATE_FORMAT('1997-01-31 09:26:50.000123',
'%d-%b-%Y %H:%i:%s.%f') FROM DUAL;
31-Jan-1997 09:26:50.000123
106.How To Convert Character Strings to Dates?
If you have a character string that represents a date, and you want to convert it into a date value, you can use the STR_TO_DATE(string, format) function. STR_TO_DATE() shares the same formatting codes with DATE_FORMAT() function. The tutorial exercise below shows you some good examples:
SELECT STR_TO_DATE('Friday, January 31, 1997',
'%W, %M %e, %Y') FROM DUAL;
1997-01-31
SELECT STR_TO_DATE('Friday, January 31, 1997, 09:26:50 AM',
'%W, %M %e, %Y, %h:%i:%s %p') FROM DUAL;
1997-01-31 09:26:50
SELECT STR_TO_DATE('31-Jan-1997 09:26:50.000123',
'%d-%b-%Y %H:%i:%s.%f') FROM DUAL;
1997-01-31 09:26:50.000123
107.How To Increment Dates by 1 in MySQL?
If you have a date, and you want to increment it by 1 day, you can use the DATE_ADD(date, INTERVAL 1 DAY) function. You can also use the date interval add operation as "date + INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:
SELECT DATE_ADD(DATE('1997-01-31'), INTERVAL 1 DAY)
FROM DUAL;
1997-02-01
SELECT DATE('1997-01-31') + INTERVAL 1 DAY FROM DUAL;
1997-02-01
108.How To Decrement Dates by 1 in MySQL?
If you have a date, and you want to decrement it by 1 day, you can use the DATE_SUB(date, INTERVAL 1 DAY) function. You can also use the date interval subtraction operation as "date - INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:
SELECT DATE_SUB(DATE('1997-03-01'), INTERVAL 1 DAY)
FROM DUAL;
1997-02-28
SELECT DATE('1997-01-31') - INTERVAL 1 DAY FROM DUAL;
1997-02-28
109.How To Calculate the Difference between Two Dates?
If you have two dates, and you want to know how many days between them, you can use the DATEDIFF(date1, date2) function as shown below:
SELECT DATEDIFF(DATE('1997-02-28'), DATE('1997-03-01'))
FROM DUAL;
-1
110.How To Present a Past Time in Hours, Minutes and Seconds?
If you want show an article was posted "n hours n minutes and n seconds ago", you can use the TIMEDIFF(NOW(), pastTime) function as shown in the following tutorial exercise:
SELECT TIMEDIFF(NOW(), '2006-07-01 04:09:49') FROM DUAL;
06:42:58
SELECT TIME_FORMAT(TIMEDIFF(NOW(), '2006-06-30 04:09:49'),
'%H hours, %i minutes and %s seconds ago.') FROM DUAL;
30 hours, 45 minutes and 22 seconds ago.
111.How To Extract a Unit Value from a Date and Time?
If you want to extract a specific date or time unit value out of a date or a time, you can use the EXTRACT(unit FROM expression) function. The tutorial exercise below gives you some good examples:
ELECT EXTRACT(DAY FROM NOW()) FROM DUAL;
28
ELECT EXTRACT(HOUR FROM NOW()) FROM DUAL;
23
ELECT EXTRACT(SECOND FROM NOW()) FROM DUAL;
36
112.What Are Date and Time Functions in MySQL?
MySQL offers a number of functions for date and time values:
► ADDDATE(date, INTERVAL expr unit) - Adding days to a date. Same as DATE_ADD().
► ADDTIME(time1, time2) - Adding two time values together.
► CURDATE() - Returning the current date. Same as CURRENT_DATE().
► CURTIME() - Returning the current time. Same as CURRENT_TIME().
► DATE(expression) - Returning the date from the expression.
► DATEDIFF(date1, date2) - Returning dates difference in days.
► DATE_ADD(date, INTERVAL expr unit) - Adding days to a date.
► DATE_SUB(date, INTERVAL expr unit) - Subtracting days from a date.
► DATE_FORMAT(date, format) - Returning a character string representing a date.
► DAY(date) - Returning an integer representing the day of the month. Same as DAYOFMONTH()
► DAYNAME(date) - Returning the name of week day.
► DAYOFMONTH(date) - Returning an integer representing the day of the month.
► DAYOFWEEK(date) - Returning an integer representing the day of the week.
► DAYOFYEAR(date) - Returning an integer representing the day of the year.
► EXTRACT(unit FROM date) - Returning a unit value of a date and time.
► HOUR(time) - Returning the hour value of a time
► LAST_DAY(date) - Returning a date representing the last day of the month based on the given date.
► LOCALTIME() - Returning the current date and time. Same as NOW().
► MAKETIME(hour,minute,second) - Returning a date based on the given time unit values.
► MICROSECOND(time) - Returning the microsecond unit value of the given time.
► MINUTE(time) - Returning the minute unit value of the given time.
► MONTH(date) - Returning the month unit value of the given date.
► MONTHNAME(date) - Returning the month name of the given date.
► NOW() - Returning the current date and time.
► SECOND(time) - Returning the second unit value of the given time.
► SEC_TO_TIME(seconds) - Converting a second value to a time
► STR_TO_DATE(str,format) - Converting a character string to a date.
► SUBDATE(date,INTERVAL expr unit) - Subtracting days from a date.
► SUBTIME(expr1,expr2) - Subtracting a time from another time.
► SYSDATE() - Returning current date and time.
► TIME(expr) - Returning a time from the given date and time.
► TIMEDIFF(expr1,expr2) - Returning the difference between two times.
► TIME_TO_SEC(time) - Converting a time to a second value.
► WEEKDAY(date) - Returning the weekday unit value of a given date.
► YEAR(date) - Returning the year unit value of a given date.
113.How Many Ways to Get the Current Time?
There are 8 ways to get the current time:
SELECT NOW() FROM DUAL;
2006-07-01 10:02:41
SELECT CURRENT_TIME() FROM DUAL;
10:02:58
SELECT SYSDATE() FROM DUAL;
2006-07-01 10:03:21
mysql> SELECT CURRENT_TIMESTAMP() FROM DUAL;
2006-07-01 10:04:03
SELECT LOCALTIME() FROM DUAL;
2006-07-01 10:07:37
mysql> SELECT LOCALTIMESTAMP() FROM DUAL;
2006-07-01 10:08:08
mysql> SELECT UTC_TIME() FROM DUAL;
14:09:22
mysql> SELECT UTC_TIMESTAMP() FROM DUAL;
2006-07-01 14:09:49
114.What Happens If You No CREATE Privilege in a Database?
In order to create tables in a database, your user account must have the CREATE privilege for that database. Otherwise you will get an error as shown in the following tutorial exercise:
>cd mysql_in
>mysql -u guest -ppub
mysql> use ggl;
Database changed
mysql> CREATE TABLE test (id integer);
ERROR 1142 (42000): CREATE command denied to user
'guest'@'localhost' for table 'test'
If you get this error, you need to see the DBA to obtain the CREATE privilege.
115.How To See the CREATE TABLE Statement of an Existing Table?
If you want to know how an existing table was created, you can use the "SHOW CREATE TABLE" command to get a copy of the "CREATE TABLE" statement back on an existing table. The following tutorial script shows you a good example:
mysql> SHOW CREATE TABLE tip;
+-------+-------------------------------
| Table | Create Table
+-------+-------------------------------
| tip | CREATE TABLE `tip` (
`id` int(11) NOT NULL,
`subject` varchar(80) NOT NULL,
`description` varchar(256) NOT NULL,
`create_date` date default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------
1 row in set (0.38 sec)
Comparing with the original "CREATE TABLE" statement used in the previous tutorial, the output tells you that:
► INTEGER data type was replaced by "int(11)".
► Default database engine "MyISAM" was used for the table.
► Default character set "latin1" was used for the table.
116.How To Get a List of Columns in an Existing Table?
If you have an existing table, but you don't remember what are the columns used in the table, you can use the "SHOW COLUMNS FROM tableName" command to get a list of all columns of the specified table. You can also use the "DESCRIBE tableName" command, which gives you the same output as "SHOW COLUMNS" command. The following tutorial script shows you a good example:
mysql> SHOW COLUMNS FROM tip;
+-------------+--------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+-------------+--------------+------+-----+---------+-------
| id | int(11) | NO | PRI | |
| subject | varchar(80) | NO | | |
| description | varchar(256) | NO | | |
| create_date | date | YES | | NULL |
+-------------+--------------+------+-----+---------+-------
4 rows in set (0.04 sec)
117.How To Create a New Table by Selecting Rows from Another Table in MySQL ?
mysql> INSERT INTO tip VALUES (1, 'Learn MySQL',
'Visit www.GlobalGuideLine.com','2006-07-01');
Query OK, 1 row affected (0.62 sec)
mysql> CREATE TABLE tipBackup SELECT * FROM tip;
Query OK, 1 row affected (0.49 sec)
Records: 1 Duplicates: 0 Warnings: 0
118.How To Add a New Column to an Existing Table in MySQL?
mysql> ALTER TABLE tip ADD COLUMN author VARCHAR(40);
119.How To Delete an Existing Column in a Table?
ALTER TABLE tip DROP COLUMN create_date;
Query OK, 1 row affected (0.48 sec)
120.How To Rename an Existing Column in a Table?
ALTER TABLE tip CHANGE COLUMN subject
title VARCHAR(60);
121.How To Rename an Existing Table in MySQL?
ALTER TABLE tip RENAME TO faq;
122.How To Drop an Existing Table in MySQL?
DROP TABLE tipBackup
123.How To Create a Table Index in MySQL?
mysql> CREATE TABLE tip (id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE NULL);
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE INDEX tip_subject ON tip(subject);
Query OK, 0 rows affected (0.19 sec)
124.How To Get a List of Indexes of an Existing Table?
If you want to see the index you have just created for an existing table, you can use the "SHOW INDEX FROM tableName" command to get a list of all indexes in a given table. The tutorial script below shows you a nice example:
SHOW INDEX FROM TIP;
125.How To Drop an Existing Index in MySQL?
If you don't need an existing index any more, you should delete it with the "DROP INDEX indexName ON tableName" statement. Here is an example SQL script:
mysql> DROP INDEX tip_subject ON tip;
29 MySQL interview questions
By admin | September 5, 2006 How do you start and stop MySQL on Windows? - net start MySQL, net stop MySQL
How do you start MySQL on Linux? - /etc/init.d/mysql start
Explain the difference between mysql and mysqli interfaces in PHP? - mysqli is the object-oriented version of mysql library functions.
What’s the default port for MySQL Server? - 3306
What does tee command do in MySQL? - tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.
Can you save your connection settings to a conf file? - Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.
How do you change a password for an existing user via mysqladmin? - mysqladmin -u root -p password "newpassword"
Use mysqldump to create a copy of the database? - mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
Have you ever used MySQL Administrator and MySQL Query Browser? Describe the tasks you accomplished with these tools.
What are some good ideas regarding user security in MySQL? - There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.
Explain the difference between MyISAM Static and MyISAM Dynamic. - In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.
What does myisamchk do? - It compressed the MyISAM tables, which reduces their disk usage.
Explain advantages of InnoDB over MyISAM? - Row-level locking, transactions, foreign key constraints and crash recovery.
Explain advantages of MyISAM over InnoDB? - Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
What are HEAP tables in MySQL? - HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.
How do you control the max size of a HEAP table? - MySQL config variable max_heap_table_size.
What are CSV tables? - Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.
Explain federated tables. - Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
What is SERIAL data type in MySQL? - BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table? - It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.
Explain the difference between BOOL, TINYINT and BIT. - Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.
Explain the difference between FLOAT, DOUBLE and REAL. - FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.
If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table? - 999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.
What happens if a table has one column defined as TIMESTAMP? - That field gets the current timestamp whenever the row gets altered.
But what if you really want to store the timestamp data, such as the publication date of the article? - Create two columns of type TIMESTAMP and use the second one for your real data.
Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP - The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.
What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do? - On initialization places a zero in that column, on future updates puts the current value of the timestamp in.
Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44′ ON UPDATE CURRENT_TIMESTAMP. - A default value is used on initialization, a current timestamp is inserted on update of the row.
If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table? - CHAR(3), since MySQL automatically adjusted the data type.
31 more MySQL questions
By admin | September 30, 2006 What is DDL, DML and DCL? - If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.
How do you get the number of rows affected by query? - SELECT COUNT (user_id) FROM users would only return the number of user_id’s.
If the value in the column is repeatable, how do you find out the unique values? - Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;
How do you return the a hundred books starting from 25th? - SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.
You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user? - SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase "Found 13,450,600 results, displaying 1-10". Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.
How would you write a query to select all teams that won either 2, 4, 6 or 8 games? - SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)
How would you select all the users, whose phone number is null? - SELECT user_name FROM users WHERE ISNULL(user_phonenumber);
What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id) - It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id
How do you find out which auto increment was assigned on the last insert? - SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.
What does –i-am-a-dummy flag to do when starting MySQL? - Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.
On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do? - What it means is that so of the data that you’re trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.
When would you use ORDER BY in DELETE statement? - When you’re not deleting by row ID. Such as in DELETE FROM techinterviews_com_questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table techinterviews_com_questions.
How can you see all indexes defined for a table? - SHOW INDEX FROM techinterviews_questions;
How would you change a column from VARCHAR(10) to VARCHAR(50)? - ALTER TABLE techinterviews_questions CHANGE techinterviews_content techinterviews_CONTENT VARCHAR(50).
How would you delete a column? - ALTER TABLE techinterviews_answers DROP answer_user_id.
How would you change a table to InnoDB? - ALTER TABLE techinterviews_questions ENGINE innodb;
When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables? -
VARCHARs with length less than 4 become CHARs
CHARs with length more than 3 become VARCHARs.
NOT NULL gets added to the columns declared as PRIMARY KEYs
Default values such as NULL are specified for each column
How do I find out all databases starting with ‘tech’ to which I have access to? - SHOW DATABASES LIKE ‘tech%’;
How do you concatenate strings in MySQL? - CONCAT (string1, string2, string3)
How do you get a portion of a string? - SELECT SUBSTR(title, 1, 10) from techinterviews_questions;
What’s the difference between CHAR_LENGTH and LENGTH? - The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.
How do you convert a string to UTF-8? - SELECT (techinterviews_question USING utf8);
What do % and _ mean inside LIKE statement? - % corresponds to 0 or more characters, _ is exactly one character.
How do you get the month from a timestamp? - SELECT MONTH(techinterviews_timestamp) from techinterviews_questions;
How do you offload the time/date handling to MySQL? - SELECT DATE_FORMAT(techinterviews_timestamp, ‘%Y-%m-%d’) from techinterviews_questions; A similar TIME_FORMAT function deals with time.
How do you add three minutes to a date? - ADDDATE(techinterviews_publication_date, INTERVAL 3 MINUTE)
What’s the difference between Unix timestamps and MySQL timestamps? - Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.
How do you convert between Unix timestamps and MySQL timestamps? - UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.
What are ENUMs used for in MySQL? - You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);
How are ENUMs and SETs represented internally? - As unique integers representing the powers of two, due to storage optimizations.
My SQL Interview Questions and Answers
What's MySQL ?MySQL (pronounced "my ess cue el") is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility. ...
What is DDL, DML and DCL ?
If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.
How do you get the number of rows affected by query?
SELECT COUNT (user_id) FROM users would only return the number of user_id’s.
If the value in the column is repeatable, how do you find out the unique values?
Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;
How do you return the a hundred books starting from 25th?
SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.
You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?
SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase "Found 13,450,600 results, displaying 1-10". Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.
How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)
How would you select all the users, whose phone number is null?
SELECT user_name FROM users WHERE ISNULL(user_phonenumber);
What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id) ?
It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id
How do you find out which auto increment was assigned on the last insert?
SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.
What does –i-am-a-dummy flag to do when starting MySQL?
Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.
On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do?
What it means is that so of the data that you’re trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.
When would you use ORDER BY in DELETE statement?
When you’re not deleting by row ID. Such as in DELETE FROM techpreparation_com_questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table techpreparation_com_questions.
How can you see all indexes defined for a table?
SHOW INDEX FROM techpreparation_questions;
How would you change a column from VARCHAR(10) to VARCHAR(50)?
ALTER TABLE techpreparation_questions CHANGE techpreparation_content techpreparation_CONTENT VARCHAR(50).
How would you delete a column?
ALTER TABLE techpreparation_answers DROP answer_user_id.
How would you change a table to InnoDB?ALTER TABLE techpreparation_questions ENGINE innodb;
When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables?
1. VARCHARs with length less than 4 become CHARs
2. CHARs with length more than 3 become VARCHARs.
3. NOT NULL gets added to the columns declared as PRIMARY KEYs
4. Default values such as NULL are specified for each column
How do I find out all databases starting with ‘tech’ to which I have access to?
SHOW DATABASES LIKE ‘tech%’;
How do you concatenate strings in MySQL?
CONCAT (string1, string2, string3)
How do you get a portion of a string?
SELECT SUBSTR(title, 1, 10) from techpreparation_questions;
What’s the difference between CHAR_LENGTH and LENGTH?
The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.
How do you convert a string to UTF-8?
SELECT (techpreparation_question USING utf8);
What do % and _ mean inside LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character.
What does + mean in REGEXP?
At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.
How do you get the month from a timestamp?
SELECT MONTH(techpreparation_timestamp) from techpreparation_questions;
How do you offload the time/date handling to MySQL?
SELECT DATE_FORMAT(techpreparation_timestamp, ‘%Y-%m-%d’) from techpreparation_questions; A similar TIME_FORMAT function deals with time.
How do you add three minutes to a date?
ADDDATE(techpreparation_publication_date, INTERVAL 3 MINUTE)
What’s the difference between Unix timestamps and MySQL timestamps?
Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.
How do you convert between Unix timestamps and MySQL timestamps?
UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.
What are ENUMs used for in MySQL?
You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);
How are ENUMs and SETs represented internally?
As unique integers representing the powers of two, due to storage optimizations.
How do you start and stop MySQL on Windows?
net start MySQL, net stop MySQL
How do you start MySQL on Linux?
/etc/init.d/mysql start
Explain the difference between mysql and mysql interfaces in PHP?
mysqli is the object-oriented version of mysql library functions.
What’s the default port for MySQL Server?
3306
What does tee command do in MySQL?
tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command note.
Can you save your connection settings to a conf file?
Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.
How do you change a password for an existing user via mysqladmin?
mysqladmin -u root -p password "newpassword"
Use mysqldump to create a copy of the database?
mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
Have you ever used MySQL Administrator and MySQL Query Browser?
Describe the tasks you accomplished with these tools.
What are some good ideas regarding user security in MySQL?
There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.
Explain the difference between MyISAM Static and MyISAM Dynamic. ?
In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.
What does myisamchk do?
It compressed the MyISAM tables, which reduces their disk usage.
Explain advantages of InnoDB over MyISAM?
Row-level locking, transactions, foreign key constraints and crash recovery.
Explain advantages of MyISAM over InnoDB?
Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
What are HEAP tables in MySQL?
HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.
How do you control the max size of a HEAP table?
MySQL config variable max_heap_table_size.
What are CSV tables?
Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.
Explain federated tables. ?
Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
What is SERIAL data type in MySQL?
BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?
It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.
Explain the difference between BOOL, TINYINT and BIT. ?
Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.
Explain the difference between FLOAT, DOUBLE and REAL. ?
FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.
If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?
999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.
What happens if a table has one column defined as TIMESTAMP?
That field gets the current timestamp whenever the row gets altered.
But what if you really want to store the timestamp data, such as the publication date of the article?
Create two columns of type TIMESTAMP and use the second one for your real data.
Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ?
The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.
What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?
On initialization places a zero in that column, on future updates puts the current value of the timestamp in.
Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44? ON UPDATE CURRENT_TIMESTAMP. ?
A default value is used on initialization, a current timestamp is inserted on update of the row.
If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table?
CHAR(3), since MySQL automatically adjusted the data type.
General Information About MySQL
MySQL is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server.
MySQL is free software. It is licensed with the GNU GENERAL PUBLIC LICENSE http://www.gnu.org/.
What Is MySQL
MySQL, the most popular Open Source SQL database, is provided by MySQL AB. MySQL AB is a commercial company that builds is business providing services around the MySQL database. See section 1.2 What Is MySQL AB.
ySQL is a database management system.
A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL. Since computers are very good at handling large amounts of data, database management plays a central role in computing, as stand-alone utilities, or as parts of other applications.
MySQL is a relational database management system.
A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. The tables are linked by defined relations making it possible to combine data from several tables on request. The SQL part of MySQL stands for "Structured Query Language" - the most common standardized language used to access databases.
MySQL is Open Source Software.
Open source means that it is possible for anyone to use and modify. Anybody can download MySQL from the Internet and use it without paying anything. Anybody so inclined can study the source code and change it to fit their needs. MySQL uses the GPL (GNU General Public License) http://www.gnu.org, to define what you may and may not do with the software in different situations. If you feel uncomfortable with the GPL or need to embed MySQL into a commercial application you can buy a commercially licensed version from us.
Why use MySQL?
MySQL is very fast, reliable, and easy to use. If that is what you are looking for, you should give it a try. MySQL also has a very practical set of features developed in very close cooperation with our users. You can find a performance comparison of MySQL to some other database managers on our benchmark page. See section 12.7 Using Your Own Benchmarks. MySQL was originally developed to handle very large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years. Though under constant development, MySQL today offers a rich and very useful set of functions. The connectivity, speed, and security make MySQL highly suited for accessing databases on the Internet.
The technical features of MySQL
For advanced technical information, see section 7 MySQL Language Reference. MySQL is a client/server system that consists of a multi-threaded SQL server that supports different backends, several different client programs and libraries, administrative tools, and a programming interface. We also provide MySQL as a multi-threaded library which you can link into your application to get a smaller, faster, easier to manage product. MySQL has a lot of contributed software available.
It is very likely that you will find that your favorite application/language already supports MySQL. The official way to pronounce MySQL is ``My Ess Que Ell'' (not MY-SEQUEL). But we try to avoid correcting people who say MY-SEQUEL.
Database Basics
Databases are managed by a relational database management system (RDBMS). An RDBMS supports a database language to create and delete databases and to manage and search data. The database language used in almost all DBMSs is SQL, a set of statements that define and manipulate data. After creating a database, the most common SQL statements used are INSERT, UPDATE, DELETE, and SELECT, which add, change, remove, and search data in a database, respectively.
Database
A repository to store data.
Table
The part of a database that stores the data. A table has columns or attributes, and the data stored in rows.
Attributes
The columns in a table. All rows in table entities have the same attributes. For example, a customer table might have the attributes name, address, and city. Each attribute has a data type such as string, integer, or date.
Rows
The data entries in a table. Rows contain values for each attribute. For example, a row in a customer table might contain the values "Matthew Richardson," "Punt Road," and "Richmond." Rows are also known as records.
Relational model
A model that uses tables to store data and manage the relationship between tables.
Relational database management system
A software system that manages data in a database and is based on the relational model. DBMSs have several components described in detail in Chapter 1.
SQL
A query language that interacts with a DBMS. SQL is a set of statements to manage databases, tables, and data.
Constraints
Restrictions or limitations on tables and attributes. For example, a wine can be produced only by one winery, an order for wine can't exist if it isn't associated with a customer, having a name attribute could be mandatory for a customer.
Primary key
One or more attributes that contain values that uniquely identify each row. For example, a customer table might have the primary key of cust ID. The cust ID attribute is then assigned a unique value for each customer. A primary key is a constraint of most tables.
Index
A data structure used for fast access to rows in a table. An index is usually built for the primary key of each table and can then be used to quickly find a particular row. Indexes are also defined and built for other attributes when those attributes are frequently used in queries.
Entity-relationship modeling
A technique used to describe the real-world data in terms of entities, attributes, and relationships.
Normalized database
A correctly designed database that is created from an ER model. There are different types or levels of normalization, and a third-normal form database is generally regarded as being an acceptably designed relational database.
MySQL - SELECT INTO TABLE
MySQL doesn't yet support the Oracle SQL extension: SELECT ... INTO TABLE .... MySQL supports instead the ANSI SQL syntax INSERT INTO ... SELECT ..., which is basically the same thing.
Alternatively, you can use SELECT INTO OUTFILE... or CREATE TABLE ... SELECT to solve your problem.
MySQL - Transactions
As MySQL does nowadays support transactions, the following discussion is only valid if you are only using the non-transaction-safe table types.
The question is often asked, by the curious and the critical, ``Why is MySQL not a transactional database?'' or ``Why does MySQL not support transactions?''
MySQL has made a conscious decision to support another paradigm for data integrity, ``atomic operations.'' It is our thinking and experience that atomic operations offer equal or even better integrity with much better performance. We, nonetheless, appreciate and understand the transactional database paradigm and plan, within the next few releases, to introduce transaction-safe tables on a per table basis. We will be giving our users the possibility to decide if they need the speed of atomic operations or if they need to use transactional features in their applications.
How does one use the features of MySQL to maintain rigorous integrity and how do these features compare with the transactional paradigm?
First, in the transactional paradigm, if your applications are written in a way that is dependent on the calling of ``rollback'' instead of ``commit'' in critical situations, then transactions are more convenient. Moreover, transactions ensure that unfinished updates or corrupting activities are not committed to the database; the server is given the opportunity to do an automatic rollback and your database is saved.
MySQL, in almost all cases, allows you to solve for potential problems by including simple checks before updates and by running simple scripts that check the databases for inconsistencies and automatically repair or warn if such occurs. Note that just by using the MySQL log or even adding one extra log, one can normally fix tables perfectly with no data integrity loss.
Moreover, fatal transactional updates can be rewritten to be atomic. In fact,we will go so far as to say that all integrity problems that transactions solve can be done with LOCK TABLES or atomic updates, ensuring that you never will get an automatic abort from the database, which is a common problem with transactional databases.
Not even transactions can prevent all loss if the server goes down. In such cases even a transactional system can lose data. The difference between different systems lies in just how small the time-lap is where they could lose data. No system is 100% secure, only ``secure enough.'' Even Oracle, reputed to be the safest of transactional databases, is reported to sometimes lose data in such situations.
To be safe with MySQL, you only need to have backups and have the update logging turned on. With this you can recover from any situation that you could with any transactional database. It is, of course, always good to have backups, independent of which database you use.
The transactional paradigm has its benefits and its drawbacks. Many users and application developers depend on the ease with which they can code around problems where an abort appears to be, or is necessary, and they may have to do a little more work with MySQL to either think differently or write more. If you are new to the atomic operations paradigm, or more familiar or more comfortable with transactions, do not jump to the conclusion that MySQL has not addressed these issues. Reliability and integrity are foremost in our minds. Recent estimates indicate that there are more than 1,000,000 mysqld servers currently running, many of which are in production environments. We hear very, very seldom from our users that they have lost any data, and in almost all of those cases user error is involved. This is, in our opinion, the best proof of MySQL's stability and reliability.
Lastly, in situations where integrity is of highest importance, MySQL's current features allow for transaction-level or better reliability and integrity. If you lock tables with LOCK TABLES, all updates will stall until any integrity checks are made. If you only obtain a read lock (as opposed to a write lock), then reads and inserts are still allowed to happen. The new inserted records will not be seen by any of the clients that have a READ lock until they release their read locks. With INSERT DELAYED you can queue inserts into a local queue, until the locks are released, without having the client wait for the insert to complete.
``Atomic,'' in the sense that we mean it, is nothing magical. It only means that you can be sure that while each specific update is running, no other user can interfere with it, and there will never be an automatic rollback (which can happen on transaction based systems if you are not very careful). MySQL also guarantees that there will not be any dirty reads. You can find some example of how to write atomic updates in the commit-rollback section.
We have thought quite a bit about integrity and performance, and we believe that our atomic operations paradigm allows for both high reliability and extremely high performance, on the order of three to five times the speed of the fastest and most optimally tuned of transactional databases. We didn't leave out transactions because they are hard to do. The main reason we went with atomic operations as opposed to transactions is that by doing this we could apply many speed optimizations that would not otherwise have been possible.
Many of our users who have speed foremost in their minds are not at all concerned about transactions. For them transactions are not an issue. For those of our users who are concerned with or have wondered about transactions vis-a-vis MySQL, there is a ``MySQL way'' as we have outlined above. For those where safety is more important than speed, we recommend them to use the BDB tables for all their critical data.
One final note: We are currently working on a safe replication schema that we believe to be better than any commercial replication system we know of. This system will work most reliably under the atomic operations, non-transactional, paradigm. Stay tuned.
MySQL - Speed of INSERT Queries ?
The time to insert a record consists approximately of:
Connect: (3)
Sending query to server: (2)
Parsing query: (2)
Inserting record: (1 x size of record)
Inserting indexes: (1 x number of indexes)
Close: (1)
where the numbers are somewhat proportional to the overall time. This does not take into consideration the initial overhead to open tables (which is done once for each concurrently running query).
The size of the table slows down the insertion of indexes by N log N (B-trees).
Some ways to speed up inserts:
If you are inserting many rows from the same client at the same time, use multiple value lists INSERT statements. This is much faster (many times in some cases) than using separate INSERT statements.
If you are inserting a lot of rows from different clients, you can get higher speed by using the INSERT DELAYED statement.
Note that with MyISAM you can insert rows at the same time SELECTs are running if there are no deleted rows in the tables. When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using a lot of INSERT statements.
It is possible with some extra work to make LOAD DATA INFILE run even faster when the table has many indexes. Use the following procedure:
Optionally create the table with CREATE TABLE. For example, using mysql or Perl-DBI.
Execute a FLUSH TABLES statement or the shell command mysqladmin flush-tables.
Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name. This will remove all usage of all indexes from the table.
Insert data into the table with LOAD DATA INFILE. This will not update any indexes and will therefore be very fast.
If you are going to only read the table in the future, run myisampack on it to make it smaller.
Re-create the indexes with myisamchk -r -q /path/to/db/tbl_name. This will create the index tree in memory before writing it to disk, which is much faster because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced. Execute a FLUSH TABLES statement or the shell command mysqladmin flush-tables.
This procedure will be built into LOAD DATA INFILE in some future version of MySQL.
You can speed up insertions by locking your tables:
mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;
The main speed difference is that the index buffer is flushed to disk only once, after all INSERT statements have completed. Normally there would be as many index buffer flushes as there are different INSERT statements. Locking is not needed if you can insert all rows with a single statement. Locking will also lower the total time of multi-connection tests, but the maximum wait time for some threads will go up (because they wait for locks). For example:
thread 1 does 1000 inserts
thread 2, 3, and 4 does 1 insert
thread 5 does 1000 inserts
If you don't use locking, 2, 3, and 4 will finish before 1 and 5. If you use locking, 2, 3, and 4 probably will not finish before 1 or 5, but the total time should be about 40% faster. As INSERT, UPDATE, and DELETE operations are very fast in MySQL, you will obtain better overall performance by adding locks around everything that does more than about 5 inserts or updates in a row. If you do very many inserts in a row, you could do a LOCK TABLES followed by an UNLOCK TABLES once in a while (about each 1000 rows) to allow other threads access to the table. This would still result in a nice performance gain. Of course, LOAD DATA INFILE is much faster for loading data.
To get some more speed for both LOAD DATA INFILE and INSERT, enlarge the key buffer.
MySQL - Speed of UPDATE Queries ?
Update queries are optimized as a SELECT query with the additional overhead of a write. The speed of the write is dependent on the size of the data that is being updated and the number of indexes that are updated. Indexes that are not changed will not be updated.
Also, another way to get fast updates is to delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time if you lock the table.
Note that, with dynamic record format, updating a record to a longer total length may split the record. So if you do this often, it is very important to OPTIMIZE TABLE sometimes.
MySQL - Speed of DELETE Queries ?
If you want to delete all rows in the table, you should use TRUNCATE TABLE table_name.
The time to delete a record is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the index cache.
MySQL - Other Optimization Tips
Unsorted tips for faster systems:
Use persistent connections to the database to avoid the connection overhead. If you can't use persistent connections and you are doing a lot of new connections to the database, you may want to change the value of the thread_cache_size variable.
Always check that all your queries really use the indexes you have created in the tables. In MySQL you can do this with the EXPLAIN command.
Try to avoid complex SELECT queries on tables that are updated a lot. This is to avoid problems with table locking. The new MyISAM tables can insert rows in a table without deleted rows at the same time another table is reading from it. If this is important for you, you should consider methods where you don't have to delete rows or run OPTIMIZE TABLE after you have deleted a lot of rows.
Use ALTER TABLE ... ORDER BY expr1,expr2... if you mostly retrieve rows in expr1,expr2.. order. By using this option after big changes to the table, you may be able to get higher performance.
In some cases it may make sense to introduce a column that is 'hashed' based on information from other columns. If this column is short and reasonably unique it may be much faster than a big index on many columns. In MySQL it's very easy to use this extra column: SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND col_1='constant' AND col_2='constant' For tables that change a lot you should try to avoid all VARCHAR or BLOB columns. You will get dynamic row length as soon as you are using a single VARCHAR or BLOB column.
It's not normally useful to split a table into different tables just because the rows gets 'big'. To access a row, the biggest performance hit is the disk seek to find the first byte of the row. After finding the data most new disks can read the whole row fast enough for most applications. The only cases where it really matters to split up a table is if it's a dynamic row size table (see above) that you can change to a fixed row size, or if you very often need to scan the table and don't need most of the columns.
If you very often need to calculate things based on information from a lot of rows (like counts of things), it's probably much better to introduce a new table and update the counter in real time. An update of type UPDATE table set count=count+1 where index_column=constant is very fast! This is really important when you use databases like MySQL that only have table locking (multiple readers / single writers). This will also give better performance with most databases, as the row locking manager in this case will have less to do.
If you need to collect statistics from big log tables, use summary tables instead of scanning the whole table. Maintaining the summaries should be much faster than trying to do statistics 'live'. It's much faster to regenerate new summary tables from the logs when things change (depending on business decisions) than to have to change the running application! If possible, one should classify reports as 'live' or 'statistical', where data needed for statistical reports are only generated based on summary tables that are generated from the actual data.
Take advantage of the fact that columns have default values. Insert values explicitly only when the value to be inserted differs from the default. This reduces the parsing that MySQL need to do and improves the insert speed. In some cases it's convenient to pack and store data into a blob. In this case you have to add some extra code in your appliction to pack/unpack things in the blob, but this may save a lot of accesses at some stage. This is practical when you have data that doesn't conform to a static table structure.
Normally you should try to keep all data non-redundant (what is called 3rd normal form in database theory), but you should not be afraid of duplicating things or creating summary tables if you need these to gain more speed.
Stored procedures or UDF (user-defined functions) may be a good way to get more performance. In this case you should, however, always have a way to do this some other (slower) way if you use some database that doesn't support this. You can always gain something by caching queries/answers in your application and trying to do many inserts/updates at the same time. If your database supports lock tables (like MySQL and Oracle), this should help to ensure that the index cache is only flushed once after all updates.
Use INSERT /*! DELAYED */ when you do not need to know when your data is written. This speeds things up because many records can be written with a single disk write.
Use INSERT /*! LOW_PRIORITY */ when you want your selects to be more important.
Use SELECT /*! HIGH_PRIORITY */ to get selects that jump the queue. That is, the select is done even if there is somebody waiting to do a write.
Use the multi-line INSERT statement to store many rows with one SQL command (many SQL servers supports this).
Use LOAD DATA INFILE to load bigger amounts of data. This is faster than normal inserts and will be even faster when myisamchk is integrated in mysqld.
Use AUTO_INCREMENT columns to make unique values.
Use OPTIMIZE TABLE once in a while to avoid fragmentation when using dynamic table format.
Use HEAP tables to get more speed when possible.
When using a normal Web server setup, images should be stored as files. That is, store only a file reference in the database. The main reason for this is that a normal Web server is much better at caching files than database contents. So it it's much easier to get a fast system if you are using files.
Use in memory tables for non-critical data that are accessed often (like information about the last shown banner for users that don't have cookies).
Columns with identical information in different tables should be declared identical and have identical names. Before Version 3.23 you got slow joins otherwise. Try to keep the names simple (use name instead of customer_name in the customer table). To make your names portable to other SQL servers you should keep them shorter than 18 characters.
If you need REALLY high speed, you should take a look at the low-level interfaces for data storage that the different SQL servers support! For example, by accessing the MySQL MyISAM directly, you could get a speed increase of 2-5 times compared to using the SQL interface. To be able to do this the data must be on the same server as the application, and usually it should only be accessed by one process (because external file locking is really slow). One could eliminate the above problems by introducing low-level MyISAM commands in the MySQL server (this could be one easy way to get more performance if needed). By carefully designing the database interface, it should be quite easy to support this types of optimization. In many cases it's faster to access data from a database (using a live connection) than accessing a text file, just because the database is likely to be more compact than the text file (if you are using numerical data), and this will involve fewer disk accesses. You will also save code because you don't have to parse your text files to find line and column boundaries. You can also use replication to speed things up.
Declaring a table with DELAY_KEY_WRITE=1 will make the updating of indexes faster, as these are not logged to disk until the file is closed. The downside is that you should run myisamchk on these tables before you start mysqld to ensure that they are okay if something killed mysqld in the middle. As the key information can always be generated from the data, you should not lose anything by using DELAY_KEY_WRITE.
MySQL Interview Questions and Answers - II
How are ENUMs and SETs represented internally?
As unique integers representing the powers of two, due to storage optimizations.
How do you start and stop MySQL on Windows?
net start MySQL, net stop MySQL
How do you start MySQL on Linux?
/etc/init.d/mysql start
Explain the difference between mysql and mysql interfaces in PHP?
mysqli is the object-oriented version of mysql library functions.
What's the default port for MySQL Server?
3306
What does tee command do in MySQL?
tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command note.
Can you save your connection settings to a conf file?
Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it's not readable by others.
How do you change a password for an existing user via mysqladmin?
mysqladmin -u root -p password "newpassword"
Use mysqldump to create a copy of the database?
mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
Have you ever used MySQL Administrator and MySQL Query Browser?
Describe the tasks you accomplished with these tools.
What are some good ideas regarding user security in MySQL?
There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.
Explain the difference between MyISAM Static and MyISAM Dynamic. ?
In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.
What does myisamchk do?
It compressed the MyISAM tables, which reduces their disk usage.
Explain advantages of InnoDB over MyISAM?
Row-level locking, transactions, foreign key constraints and crash recovery.
Explain advantages of MyISAM over InnoDB?
Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
What are HEAP tables in MySQL?
HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.
How do you control the max size of a HEAP table?
MySQL config variable max_heap_table_size.
What are CSV tables?
Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.
Explain federated tables. ?
Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
What is SERIAL data type in MySQL?
BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?
It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.
