Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Oct 11, 1999

Dabbling in Live Databases: MySQL - Page 5

By Aaron Weiss

Step 2: Create the structure for each table, in this case, billing and invoices. Consider billing -- we can describe its structure as follows:

billing: table description
Field
Name
Field
Type
Can be
null?
ClientName text no
ClientEmail text no
Invoice integer no
Total real no
Paid boolean yes

Now, to actually create these structures in MySQL we need to concoct an SQL statement which defines the above:

CREATE TABLE billing
(ClientName CHAR(255) NOT NULL,
ClientEmail CHAR(255) NOT NULL,
Invoice INTEGER NOT NULL,
Total DECIMAL(6,2) NOT NULL,
Paid TINYINT NULL,
INDEX IDX_INVOICE (Invoice)
)

You have quite a bit of discretion in specifying your table's structure. We've made some decisions here, because MySQL offers many different types of fields, where in our table description we were using general terms. For instance, we've decided to use a CHAR field with a width of 255 characters for our text fields; there are several possible field types that would work here, and the differences are subtle. Generally, you want to select a field type that minimizes the amount of memory required to hold the possible data values -- for instance, if we had a field which only contained text less than 10 characters long, it would be wasteful to choose a CHAR(255) field type. The Column Types section in the MySQL reference manual details the differences between the many field types available.

Lastly, we created an index of the "Invoice" field -- indexing a field can speed up future queries, at the expense of a larger database, if those queries involve that field. Creating an index for your most queried fields may be a good idea, but these performance issues are matters that may require some experimentation, and tasty snack foods.

We're now ready to actually send this SQL statement to the MySQL server, thus creating the structure for billing. We'll use the interactive mysql client, which you can launch from mysql/bin/mysql in your MySQL installation directory. This client offers a command line environment:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 3.22.20

Type 'help' for help.

mysql>

Tell the client to use the Clients database:

mysql> \u Clients

And now enter the SQL statement to prepare:

mysql> create table billing (ClientName CHAR(255) NOT NULL,
ClientEmail CHAR(255) NOT NULL,Invoice INTEGER NOT NULL,
Total DECIMAL(6,2) NOT NULL,Paid TINYINT NULL
,INDEX IDX_INVOICE (Invoice))

Note that we typed the above statement in one line and the linewraps above were added for legibility on this web page. If you miss commas or parenthesis in the right places the MySQL server will reject the statement with an error, so pay careful attention to syntax. Also note the use of case -- specifically, it is the case of your table and field names that matters. Our table is called "billing", which is not the same as "Billing". The same applies to the field names. Hit Enter after you type the above and you'll be back at the command-line: now you tell the client to execute the statement:

mysql> \g
Query OK, 0 rows affected (0.06 sec)

If all goes well, MySQL will return a terse report and we can sleep well tonight knowing that the billing table structure is in place. Moving along, we lather, rinse, and repeat for invoices.

mysql> CREATE TABLE invoices 
(Invoice INTEGER NOT NULL,Hours DECIMAL(8,2) NOT NULL,
Rate DECIMAL(6,2) NOT NULL,INDEX IDX_INVOICE (Invoice))
mysql> \g
Query OK, 0 rows affected (0.06 sec)

A hearty pat on the back! That's two table structures in the can, and we can finally get to the meat of this here feast, importing the raw data into these tables.



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date