MySql 

 
   

What is SQL?

SQL is an ANSI standard language for accessing databases. In our SQL tutorial you will learn how to use SQL to access, define, and manipulate the data in a database system, like MySQL, Oracle, DB2, Sybase, Informix, Microsoft SQL Server, Access, and others.

  • SQL stands for Structured Query Language

  • SQL allows you to access a database

  • SQL is an ANSI standard language

  • SQL can execute queries against a database

  • SQL can retrieve data from a database

  • SQL can insert new records in a database

  • SQL can delete records from a database

  • SQL can update records in a database

  • SQL is very easy to learn 

Database Tables

Databases contain objects called Tables.

Records of data are stored in these tables. Tables are identified by names (like "Persons", "Orders", "Suppliers").

Tables contain Columns and Rows with data. Rows contain records (like one record for each person). Columns contain data (like First Name, Last Name, Address, and City).

Here is an example of a Table called "Persons":
LastName  FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger

LastName, FirstName, Address, and City are table Columns. The Rows contain 3 records about 3 persons.

PHPMyAdmin

phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL over the WWW. phpMyAdmin can manage a whole MySQL-server (needs a super-user) but also a single database. Currently phpMyAdmin can:

  • create and drop databases

  • create, copy, drop and alter tables

  • delete, edit and add fields

  • execute any SQL-statement, even batch-queries

  • manage keys on fields

  • load text files into tables

  • create (*) and read dumps of tables

  • export (*) and import data to CSV values

  • administer multiple servers and single databases

  • check referential integrity

  • create complex queries automatically connecting required tables

  • create PDF graphics of your Database layout

  • communicate in more than 41 different languages

You can write and run your SQL commands from the "Run SQL query/queries on database" box and click on go.

Alternatively you can also run these commands from your command prompt using mysql (comes with MySQL server), but you need to have telnet access to your remote database.

Here are few of the SQL commands (few are specific to MySQL) which you may find useful.

Database Handling

Open the database

The following command can be used to open a database.
use database01

Create a table

To create a table involves creating the structure of the table, first. The command is:
create table table01 (field01 integer,field02 char(10));

Please note:

  1. Enclose entire list of field names between one pair of parentheses.

  2. Commas are used between each field

  3. A space may be used after the comma between fields

  4. A comma is not used after last field

  5. This, and all SQL statements, are concluded by a semicolon ";".

List the tables

To see all the tables available in the database, you can use the following command:
show tables;

List the fields in a table

To see the structure of a table the following command can be given:
show columns from table01;

Putting Data into a Table

Insert a record

To enter data into the table the following command can be used:
insert into table01 (field01,field02) values (1,'first');

  • Enclose entire list of field names between one pair of parentheses

  • Enclose the values to be inserted between another pair of parentheses

  • Commas are used between each field and between each value

  • A space may be used after the comma between fields

List all the records in a table

To view the content of the table the following command can be used:

select * from table01;

Adding Fields

...one field at a time

To change the structure of a table, following command can be used:
alter table table01 add column field03 char(20);
...more than one at a time

You can also change the structure of more than one column using:
alter table table01 add column field04 date,add column field05 time;

Please note:

  • The "add column" must be restated for each column

  • Commas are used between each add column statement

  • A space may be used after these commas.

You can identify the effect using "show columns" command as discussed earlier.

Insert Some More Records into the Table

Add this record

You can add a record into a table by using:
insert into table01 (field01,field02,field03,field04,field05) 
values(2,'second','another','1999-10-23','10:30:00');

Please note:

  • Quotes must go around text values

  • Standard date format is "yyyy-mm-dd"

  • Standard time format is "hh:mm:ss"

  • Quotes are required around the standard date and time formats, noted above

  • Dates may also be entered as "yyyymmdd" and times as "hhmmss". If entered in this format, values don't need to be quoted

  • Numeric values do not need to be quoted. This holds true regardless of the data type a column is formatted to contain (e.g. text, date, time, integer).

You can find out the effect using "select" command as discussed earlier.

Updating Existing Records

Modify one field at a time

Again, be careful with syntax. Quote marks need to go around text but not around numbers.
update table01 set field03='new info' where field01=1;

Change multiple fields at once

Remember to put commas between each field you're updating.
update table01 set field04=19991022, field05=062218 where field01=1;
Update multiple records in one stroke
update table01 set field05=152901 where field04>19990101;
Deleting Records

The delete command
delete from table01 where field01=3;
Top