Home arrow MySQL arrow Page 4 - Beginning MySQL Tutorial

Part 3: Manipulating the Database - MySQL

One of the fastest SQL (Structured Query Language) database servers currently on the market is the MySQL server, developed by T.c.X. DataKonsultAB. MySQL, available for download at http://www.mysql.com, offers the data base programmer with an array of options and capabilities rarely seen in other database servers. What's more, MySQL is free of charge for those wishing to use it for private and commercial use.

TABLE OF CONTENTS:
  1. Beginning MySQL Tutorial
  2. Part 1: At First Glance
  3. Part 2: Datatypes and Tables
  4. Part 3: Manipulating the Database
  5. Part 4: Advanced MySQL Commands
By: W.J. Gilmore
Rating: starstarstarstarstar / 293
April 03, 1999

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

A database can be manipulated in four possible ways: addition, deletion, modification, and search. These topics will all be briefly covered in the following two sections. However, before we begin, I would like to highlight the fact that SQL, like many computer languages, is somewhat particular about command syntax. The slightest error in placement of a parentheses, comma, or semicolon will almost surely end in error. As a result, take care to be attentive of command syntax.

Insertion of records

Note: The originally created table, test, created in the last section will be used to illustrate the examples in this section. Here it is again, for quick reference:


mysql> CREATE TABLE test ( > name VARCHAR (15), > email VARCHAR (25), > phone_number INT, > ID INT NOT NULL AUTO_INCREMENT, > PRIMARY KEY (ID));

Insertion of data into the table is accomplished, logically enough, using the INSERT command.


mysql> INSERT INTO test VALUES mysql> ('Bugs Bunny', 'carrots@devshed.com', mysql> 5554321, NULL);

Result, assuming the command was correctly entered:


Query OK, 1 row affected (0.02 sec) mysql>

So what happened?

  • Single quotations were placed around the datatypes VARCHAR. All datatypes of type STRING (i.e. char, varchar, text, blob, etc.) must be surrounded in single quotes, or an error will occur.
  • There were no single quotes surrounding the phone number. Datatypes of type INT do not require single quotes.
  • NULL ? A NULL allows any datatype with the characteristic AUTO_INCREMENT to be automatically assigned a value. If it is the first record inserted into the database, it is assigned the value '1'. Otherwise, it is assigned the previously inserted value + 1 (i.e. if the previously inserted value was '2', then the next would be '3'). In addition, the insertion of NULL into a variable of type TIMESTAMP causes that variable to be given the value of the current date.

Note: It is of importance to remember that the same number of values must be inserted as datatypes are contained within a record. In the above example, if one attempted to insert only three values instead of four, the insertion would fail. The same result applies if one attempted to insert five values.

Example:


mysql> insert into test values('doggy'); ERROR 1058: Column count doesn't match value count mysql>

Note (2): One of the advantageous aspects of MySQL is it's ability to convert without trouble between datatypes. MySQL automatically converts between integers, strings, and dates without problems.

Selection

A database would not be much use if one was not able to search and extract data from it. In MySql terms, this is accomplished through the SELECT statement.


mysql> SELECT * FROM test mysql> WHERE (name = "Bugs Bunny");
Result:

nameemailphoneID   
Bugs Bunnycarrots@devshed.com55543211

Let's assume we have inserted four differing records, all bearing the same name of "Bugs Bunny", yet having different email addresses and phone numbers. The table test, would look somewhat like the following:

nameemailphoneID   
Bugs Bunnycarrots@devshed.com55543211
Bugs Bunnypeppers@devshed.com55543312
Bugs Bunnylettuce@devshed.com55543413
Bugs Bunnycelery@devshed.com55543514

Deletion

One can also delete records inserted into the table. This is accomplished through the DELETE command.


mysql> DELETE FROM test mysql> WHERE (name = "Bugs Bunny");

Result:
This would result in the deletion of all records within the table test containing name "Bugs Bunny".

Another example:


mysql> DELETE FROM test mysql> WHERE (phone_number = 5554321);

Result: (Using the previously illustrated example)

nameemailphoneID   
Bugs Bunnypeppers@devshed.com55543312
Bugs Bunnylettuce@devshed.com55543413
Bugs Bunnycelery@devshed.com55543514

Modification

MySQL also has the capability of modifying data already entered into the table. This is accomplished through the UPDATE command.


mysql> UPDATE test SET name = 'Daffy Duck' mysql> WHERE name = "Bugs Bunny";

nameemailphoneID   
Daffy Duckpeppers@devshed.com55543312
Daffy Ducklettuce@devshed.com55543413
Daffy Duckcelery@devshed.com55543514

This section, we covered the core MySQL database manipulation functions, basic insertion, deletion, modification, and search. The next section will elaborate on these capabilities, providing extended functioning and flexibility when manipulating the database.



 
 
>>> More MySQL Articles          >>> More By W.J. Gilmore
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: