MySQL Developing And Programming Blog

Working with MySQL SQL 36 TINYINT An 8-bit integer represented as one byte. SMALLINT A 16-bit integer represented as 2 bytes. MEDIUMINT A 24-bit integer represented as 3 bytes. INT, INTEGER A 32-bit integer represented as 4 bytes. BIGINT A 64-bit integer represented as 8 bytes. FLOAT A floating-point number; 8-digit precision represented as 4 bytes. DOUBLE A floating-point number; 16-digit precision represented as 8 bytes. DECIMAL(p, s) A fixed-point number, saved as a character string; an arbitrary number of digits represented as 1 byte per digit + 2 bytes overhead. DATE The date in the form 2001-12-31, in the range 1000-01-01 to 9999-12-31, represented as 3 bytes. TIME The time in the form 23:59:59, represented as 3 bytes. DATETIME A combination of DATE and TIME in the form 2002-10-05 23:59:59, represented as 8 bytes. YEAR The year (1900 2155), represented as 1 byte. TIMESTAMP The date and time in the form 20011231325959 for times between 1970 and 2038, represented as 4 bytes. CHAR(n) A character string with a specified length; a maximum of 255 characters represented as n bytes. VARCHAR(n) A character string with variable length; a maximum of n characters (n < 256), represented as 1 byte per character or (actual length) + 1. TINYTEXT A character string with variable length; a maximum of 255 characters, represented as n + 1 bytes. TEXT A character string with variable length; a maximum of 216 - 1 char acters represented as n + 2 bytes. MEDIUMTEXT A character string with variable length; a maximum of 224 - 1 characters, represented as n + 3 bytes. LONGTEXT A character string with variable length, maximum of 232 - 1 characters, represented as n + 4 bytes. TINYBLOB Binary data with variable length; a maximum of 255 bytes. BLOB Binary data with variable length; a maximum of 216 - 1 bytes. MEDIUMBLOB Binary data with variable length; a maximum of 224 - 1 bytes. LONGBLOB Binary data with variable length; a maximum of 232 - 1 bytes.

Hint: This post is supported by Gama web hosting php services

Introducing MySQL SQL 35 The accounts database is now available on the MySQL system. Note that on a Windows platform, the database name isn t case-sensitive, but it is on Unix. After a database is created, it will need to be used specifically. To use a database, you execute the USE command: mysql> use accounts; Database changed The USE command moves the focus of all commands entered into the client tool to the specified database. We now have a database, and it is the focus of our client tool. The next step is to add tables where we can store data. Creating Tables The table is where all of the data is stored in a particular database. Because we are working with a relational database system, the data is stored in rows and columns. Our goal in creating a table is to determine what will be stored in each table column. Once this information has been established, we can decide on the column types and potential sizes. You can create a MySQL table based on a number of table types: BDB A table that supports transactions; includes crash recovery. HEAP A memory-based table that uses a hashed index. ISAM An original but deprecated MySQL table. InnoDB A table that supports transactions, row-level locking, foreign key constraints, and multiversioning. MERGE A group of MyISAM tables used as one. Allows tables to be stored in different locations. MYISAM A default nontransactional table type for MySQL. Each table type has a specific characteristic that determines whether it is appropriate for your application. Note that MySQL allows you to alter the table type after you ve created a table even if you ve populated it with data. A large number of options are available, among them the maximum number of rows, the physical location of the table, and the use of a password for the table. However, the most important options are the column type definitions. First, you need to lay out the data to be stored in the table. In our example, we want to create a table that will hold the username and password for an account in our application. Associated with each username and account is an account ID. The account ID will be used to access specific data within other tables by creating a relationship with the ID. Each of these pieces of data will have a specific data type. The data types available in MySQL are as follows:
Hint: If you are looking for good and high quality web space to host and run your java application check Vision java web hosting services

Working with MySQL SQL 34 Creating Databases As you learned earlier, a database is just a container for components called tables. A DBMS can have as many databases as needed for a given application. For the most part, you create a database when your application needs a place to store data. In most cases, you need a single database with numerous tables to hold the data. The MySQL server already has its own database, called mysql. We want to create a new one instead of using the mysql database because we plan to use ours for a different purpose. In order to manipulate a MySQL system, you can use a client tool called mysql. This client tool can be found in the /bin directory of an installation. You execute the tool by entering mysql at a command prompt or terminal window. The client tool contacts the local MySQL installation and returns a prompt as shown here: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 139 to server version: 4.0.1-alpha Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer. mysql> Using the mysql client tool, you can determine what databases are currently defined in the local MySQL system. This is accomplished with the following command: mysql> show databases; +———-+ | Database | +———-+ | mysql | | files | | products | | test | | users | +———-+ 5 rows in set (0.00 sec) In the test database we used, there are currently five databases being managed by MySQL. Two of the tables, mysql and test, are created by the MySQL system when it is first installed. The other three have been added by users of the system. In our example, we want to create a new database called accounts that will hold numerous tables all related to accounts needed by some application. In the most basic form, the database is created with the following command: mysql> create database accounts; Query OK, 1 row affected (0.00 sec)

Hint: This post is supported by Gama web hosting php services

Introducing MySQL SQL 33 need to build databases and tables, populate the databases with data, and retrieve the data. Overview of MySQL MySQL is a DBMS designed as open source software. It is a relationship DBMS because it supports the idea of building multiple tables and linking those tables using columns within the tables. The application is considered open source because you can download the binaries of the system or the source code. The MySQL system is entry-level SQL92 compliant, and the developers are constantly striving to expand their support of SQL92 as well as SQL99 while maintaining speed and efficiency. Some of the featured highlights include the following: Speed and efficiency MySQL is written in C/C++ using the latest compil ers on the various support platforms. The code is multithreaded and takes advantage of kernel threads for extreme efficiency on systems with multiple CPUs. All of the code is highly optimized and makes us of B-trees, in- memory hash tables, and class libraries. Column types These include signed/unsigned integers 1, 2, 3, 4, and 8 bytes long; FLOAT; DOUBLE; CHAR; VARCHAR; TEXT; BLOB; DATE; TIME; DATETIME; TIMESTAMP; YEAR; SET; and ENUM types. We demon strate many of these column types throughout the book in code examples. A full-featured command set All of the standard SQL commands, such as SELECT, INSET, DELETE, as well as JOINs, are supported. Support includes the SHOW command for obtaining information about the system. Aliases on table and columns are supported per SQL92. Functions A wide range of functions are available, including AVG(), SUM(), MAX(), and many others. Security A full privilege and password system gives the database unparal leled security. Scalability You can build databases with tens of thousands of tables. Row counts can be in the millions and even billions. Indexes are supported up to 32 per table. Character sets MySQL supports many different characters sets and can output errors messages in appropriate languages. Tools A full complement of client tools is available for administrative and other uses. With that small introduction, let s dive into the fundamentals of using MySQL to build storage systems for our Java applications.
Note: If you are looking for cheap and quality provider to host and run your java application check Astra java hosting services

Working with MySQL SQL 32 We now have two tables for all of our sample data. The first table, called name, holds just the name of our contact as well as an ID for each name in the table. There won t be any duplicate names in this table. The second table, called telephone, holds all of the contact information for each name in the name table. Of particular important in the telephone table is the use of the telephone_id column. This column is considered a foreign key and links the name table to the telephone table. The ID column in the name table is copied to each of the telephone table rows as appropriate. If we need to find each of the telephone numbers for John Doe, we look up the ID in the row associated with John Doe. This ID is used as a reference value in the name_id columns of each row in the telephone table. Those rows that have the same ID value are returned. The telephone number value can be pulled from each row and displayed. Third Normal Form The last normal form we consider is called Third Formal Form and it is the goal for most database designers. There is a single rule in this form: Columns that are not directly related to the primary key must be eliminated (that is, transplanted into a table of their own). In the table called telephone we created earlier, we have to examine the use of the telephone_id column and the data within the table itself. The Third Normal Form rule tells us that the city and state columns shouldn t be part of the telephone table because that data doesn t relate to the primary key of the table. This calls for a new table to hold the city and state information. For example, we might create a table called address to hold this information: ID address_id city state 301 101 Chicago IL 302 102 Atlanta GA We ve provided a brief introduction to database design and the use of Normal Forms to achieve a good design. There is, of course, much more to consider when designing databases, and we recommend you consult a good database theory book for additional information. Introducing MySQL SQL The majority of this chapter concentrates on the specifics of the MySQL database and its representation of SQL. In this section, we examine the basics you
Note: If you are looking for cheap and quality provider to host and run your java application check Astra java hosting services

What Is a Database? 31 It isn t necessary to apply all of these rules to achieve First Normal Form, but they should be attempted nevertheless. For our database, the first and third rules can be applied. Rule two isn t valid for our data because all of the pieces of data are associated with each other. Rule number 1 is the one that will make the most difference in the database. Here s our data after we ve applied rules 1 and 3: ID Name City State Telephone 101 John Doe Chicago IL 217-333-3333 102 John Doe Chicago IL 800-333-3333 103 Jani Smith Atlanta GA 403-222-2223 We won t include Bill in the example to keep it small. Notice how John Doe s information is being duplicated so we can handle additional telephone numbers. If John Doe gets another telephone number, we just add a new record to the table with duplicate name, city, and state values. The third rule doesn t really help with our telephone number problem, but in order for our table to be in First Normal Form, it needs to be applied. Second Normal Form Of course, all of this data duplication simply cannot be a good thing because it is clearly wasting space in the database. We can get some help with the duplicated data using Second Normal Form and its associated rules: If the contents of columns repeat, the table needs to be divided into multiple tables. Multiple tables from rule 1 need to be linked by foreign keys or their derivative. Since we have repeating data in the sample table, we apply rules 1 and 2 to create a second table just for the city, state, and telephone information. For example, the following table might be called the name table: ID Name 101 John Doe 102 Jani Smith The telephone table would look like this: ID telephone_id city state telephone 201 101 Chicago IL 217-333-3333 202 101 Chicago IL 800-333-3333 203 102 Atlanta GA 403-222-2223
Note: If you are looking for best hosting provider to host and run your tomcat application check Astra tomcat hosting services

Working with MySQL SQL 30 To illustrate simple design considerations, let s attempt to build the tables within a database to hold data for a simple telephone directory. The data we want to store includes the following: Name City State Telephone number First Normal Form If we were to place our data into a table, we might come up with the following: Name City State Telephone John Doe Chicago IL 217-333-3333 Of course, we immediately realize that John Doe has more than just one telephone number, so we expand the table to handle more numbers: Name City State Telephone1 Telephone2 Telephone3 John Doe Chicago IL 217-333-3333 800-333-3333 Jani Smith Atlanta GA 403-222-2223 In our new table, we ve added another entry. However, Jani Smith has only one telephone number, so we leave the columns Telephone2 and Telephone3 empty. Unfortunately, our friend Bill Simpson is one of those characters with a home telephone, a business telephone, a cell phone, a pager, and a phone just for messages. Since our table handles only three telephone numbers, we need to add two more columns just for Bill. Most people we add into the table won t have more than three telephone numbers, so the vast majority of Telephone4 and Telephone5 columns will be empty. Of course, just when we limit the table to five telephone numbers, Bill will get a summer cabin with a telephone in it as well. We cannot continue to add columns just to accommodate Bill s communication needs, especially when all of the added telephone columns will generally be empty. To solve this problem of multiple columns in the database, we apply rules associated with the First Normal Form. The First Normal Form is the first in a series of optimizations that should be applied to a database to produce a highly efficient system. The rules in First Normal Form are: Columns with similar content must be eliminated. A table must be created for each group of associated data. Each data record must be identifiable by means of a primary key.

Hint: If you are looking for very good and affordable webspace to host and run your j2ee hosting application check Sandzak.com j2ee web hosting services

What Is a Database? 29 Java. Instead of the data being broken up, the entire object is stored. Figure 3.7 shows how our sample data might look in an object model-based database. Account table smith John Smith Denver doej James Doe Chicago Figure 3.7 The object model. For the remainder of this blog, we assume the use of a relational database management system. MySQL just happens to be such a system. Data Types As we mentioned earlier, a database has tables consisting of columns. The columns aren t just names like city, state, and zip, but are created based on a data type such as string, integer, or float. Some of the more common data or column types available are int Represents an integer char Represents a string of a specific length varchar Represents a string of varied length blob Represents a large binary piece of data When you use a type to define a column, the database expects that kind of data when you place information into the table. Designing a Database Now let s spend some time on the subject of database design. We know that MySQL and many other databases are relational in nature and that we need to build databases, tables, and columns. However, if we neglect to give some initial thought to the layout or design of these components, the performance and integrity of the database server and the data itself will be suspect. Before diving into this subject, note that very large college textbooks have been written on the subject of database design. This section is just a small glance at the subject.
Note: If you are looking for cheap and quality provider to host and run your java application check Astra java hosting services

Working with MySQL SQL 28 username smith jsmith John Smith Denver Figure 3.4 The hierarchy model. username name city Figure 3.5 The network model. The Relational Model In the late 1960s, the relational model was developed. A relational database uses tables with rows and columns. The power of the relational model becomes clear when multiple tables are linked using a relationship. Figure 3.6 shows how our sample data might be put in separate tables and linked using the username. username smith jsmith doej name John Smith John Smith James Doe username smith jsmith doej city Denver Denver Chicago Figure 3.6 The relational model. The Object Model In the past few years, the object model has emerged. In this model, a database is created to hold the objects found in a common programming language like
Note: If you are looking for reliable and quality webspace company to host and run your servlet application check professional servlet hosting services

What Is a Database? 27 Each of the tables is further broken down into columns where individual pieces of information are stored. The address table has columns for information such as city, state, and zip. As data is put into the table, it is organized as a series of rows, with each row containing specific information in the various columns, as shown in Figure 3.3. MySQL Account Database acc_account table acc_address table ID fname Iname 0 Joe Smith 1 Jane Doe 2 James Shaw ID acc_ID State 0 0 CO 1 1 AZ 2 2 IL Figure 3.3 Database rows/columns. So in a nutshell, that is the definition of a database. In the remainder of this section, we examine these concepts in more detail. Database Models All databases model data in different ways. A database model is just a description of a container and how data is stored and retrieved from that container. Over the years, a few different models have been developed. Consider the following data that needs to be stored in a database: Name Username City John Smith smith Denver John Smith jsmith Denver James Doe doej Chicago James Smith jsmith Atlanta The Hierarchy Model The hierarchy model attempts to organize the data in a parent-child relationship where there is always some root data. Our sample data is modeled as shown in Figure 3.4. The data is contained within the hierarchy, but getting to it could be a problem since the data is found at different levels. The Network Model In the network model, the parent-child relationship is expanded so that children can have multiple parents and a logical layer is applied to the data. Figure 3.5 shows how our sample data is modeled.

Hint: If you are looking for high quality and reliable webspace provider to host and run your jsp hosting application check Sandzak jsp web hosting provider