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
Indexes are used to find rows with a specific value of one column fast. Without an index MySQL has to start with the first record and then read through the whole table until it finds the relevant rows. The bigger the table, the more this costs. If the table has an index for the colums in question, MySQL can quickly get a position to seek to in the middle of the data file without having to look at all the data. If a table has 1000 rows, this is at least 100 times faster than reading sequentially. Note that if you need to access almost all 1000 rows it is faster to read sequentially because we then avoid disk seeks.
All MySQL indexes (PRIMARY, UNIQUE, and INDEX) are stored in B-trees. Strings are automatically prefix- and end-space compressed.
Indexes are used to:
Quickly find the rows that match a WHERE clause. Retrieve rows from other tables when performing joins. Find the MAX() or MIN() value for a specific indexed column. This is optimized by a preprocessor that checks if you are using WHERE key_part_# = constant on all key parts < N. In this case MySQL will do a single key lookup and replace the MIN() expression with a constant. If all expressions are replaced with constants, the query will return at once: SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
Sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part_1,key_part_2 ). The key is read in reverse order if all key parts are followed by DESC. The index can also be used even if the ORDER BY doesn't match the index exactly, as long as all the unused index parts and all the extra are ORDER BY columns are constants in the WHERE clause. The following queries will use the index to resolve the ORDER BY part: SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3; SELECT * FROM foo WHERE column=constant ORDER BY column, key_part1; SELECT * FROM foo WHERE key_part1=const GROUP BY key_part2;
In some cases a query can be optimized to retrieve values without consulting the data file. If all used columns for some table are numeric and form a leftmost prefix for some key, the values may be retrieved from the index tree for greater speed: SELECT key_part3 FROM table_name WHERE key_
We start with the system level things since some of these decisions have to be made very early. In other cases a fast look at this part may suffice because it not that important for the big gains. However, it is always nice to have a feeling about how much one could gain by changing things at this level.
The default OS to use is really important! To get the most use of multiple CPU machines one should use Solaris (because the threads works really nice) or Linux (because the 2.2 kernel has really good SMP support). Also on 32-bit machines Linux has a 2G file size limit by default. Hopefully this will be fixed soon when new filesystems are released (XFS/Reiserfs). If you have a desperate need for files bigger than 2G on Linux-Intel 32 bit, you should get the LFS patch for the ext2 file system.
Because we have not run MySQL in production on that many platforms, we advice you to test your intended platform before choosing it, if possible.
After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. The mysql_install_db script starts up the mysqld server, then initializes the grant tables to contain the following set of privileges:
The MySQL root user is created as a superuser who can do anything. Connections must be made from the local host. NOTE: The initial root password is empty, so anyone can connect as root without a password and be granted all privileges. An anonymous user is created that can do anything with databases that have a name of 'test' or starting with 'test_'. Connections must be made from the local host. This means any local user can connect without a password and be treated as the anonymous user. Other privileges are denied. For example, normal users can't use mysqladmin shutdown or mysqladmin processlist. NOTE: The default privileges are different for Windows.
Because your installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user. You can do this as follows (note that you specify the password using the PASSWORD() function):
shell> mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; mysql> FLUSH PRIVILEGES;
You can, in MySQL Version 3.22 and above, use the SET PASSWORD statement:
shell> mysql -u root mysql mysql> SET PASSWORD FOR root=PASSWORD('new_password');
Another way to set the password is by using the mysqladmin command:
shell> mysqladmin -u root password new_password
Only users with write/update access to the mysql database can change the password for others users. All normal users (not anonymous ones) can only change their own password with either of the above commands or with SET PASSWORD=PASSWORD('new password').
Note that if you update the password in the user table directly using the first method, you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES), because the change will go unnoticed otherwise.
Once the root password has been set, thereafter you must supply that password whe
you can add users two different ways: by using GRANT statements or by manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements, because they are more concise and less error-prone.
The examples below show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the insert privilege for the mysql database and the reload administrative privilege. Also, if you have changed the root user password, you must specify it for the mysql commands below.
You can add new users by issuing GRANT statements:
shell> mysql --user=root mysql mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysqlgt; GRANT RELOAD,PROCESS ON *.* TO admin@localhost; mysqlgt; GRANT USAGE ON *.* TO dummy@localhost;
These GRANT statements set up three new users:
monty A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order. admin A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin process list . No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.) dummy A user who can connect without a password, but only from the local host. The global privileges are all set to 'N' -- the USAGE privilege type allows you to create a
When you run mysqladmin status, you'll see something like this:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
This can be somewhat perplexing if you only have 6 tables.
MySQL is multithreaded, so it may have many queries on the same table simultaneously. To minimize the problem with two threads having different states on the same file, the table is opened independently by each concurrent thread. This takes some memory and one extra file descriptor for the data file. The index file descriptor is shared between all threads.
A Great Tutorials Portal
Aquarian Infotech System
Copyright © 2008 interviewmaterial.com. All rights reserved.