MySql General

Update and Delete in MySQL

 Print E-mail
MySql General
Current revision posted to MySQL Tutorials by admin on 12/19/2008 7:34:58 PM

 

Delete Data

The DELETE statement deletes rows from a table that satisfy the condition given by the WHERE clause. For example to delete a record from species table where id equals 1

mysql> DELETE FROM species WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM species;
+----+-------+
| id | name  |
+----+-------+
| 2  | Bird  |
| 3  | Fish  |
| 4  | Dog   |
+----+-------+
3 rows in set (0.01 sec)


Read Full Article
 

Retrieve Data from MySQL Table

 Print E-mail
MySql General
Current revision posted to MySQL Tutorials by admin on 12/19/2008 7:31:13 PM

Retrieving the table data is easy, just use the SELECT statement like this

mysql> SELECT * FROM species;
+----+--------+
| id | name   |
+----+--------+
| 1  | Cat    |
| 2  | Bird   |
| 3  | Fish   |
| 4  | Turtle |
+----+--------+
4 rows in set (0.00 sec)

The * from the SELECT statement means select all columns. If you only want the names you can write

mysql> SELECT name FROM species;
+--------+
| name   |
+--------+
| Cat    |
| Bird   |
| Fish   |
| Turtle |
+--------+
4 rows in set (0.00 sec)

To select only the records that interest you, you can use WHERE statement followed by the definition. For example to select a record from species table where id equals 4 you can do this :

mysql> SELECT * FROM species WHERE id = 4;
+----+--------+
| id | name   |
+----+--------+
| 4  | Turtle |
+----+--------+
1 row in set (0.59 sec)

If you want to order the returned rows by a criteria you can use ORDER BY like this :

mysql> SELECT * FROM species ORDER BY name;
+----+--------+
| id | name   |
+----+--------+
| 2 | Bird    |
| 1 | Cat     |
| 3 | Fish    |
| 4 | Turtle  |
+----+--------+
4 rows in set (0.00 sec)

By default the result is sorted in ascending order. So this query will give the same result :

mysql> SELECT * FROM species ORDER BY name ASC;
+----+--------+
| id | name   |
+----+--------+
| 2  | Bird   |
| 1  | Cat    |
| 3  | Fish   |
| 4  | Turtle |
+----+--------+
4 rows in set (0.00 sec)

The ASC means ascending order. To get a descending order you just change the ASC with DESC like this :

mysql> SELECT * FROM species ORDER BY name DESC;
+----+--------+
| id | name   |
+----+--------+
| 4  | Turtle |
| 3  | Fish   |
| 1  | Cat    |
| 2  | Bird   |
+----+--------+
4 rows in set (0.00 sec)


Read Full Article
 

Add Data to a MySQL Database

 Print E-mail
MySql General
Current revision posted to MySQL Tutorials by admin on 12/19/2008 7:27:50 PM

You can insert data to the tables directly from mysql> prompt or by loading a file containing the data. The values will be tab separated one line represent one record.

To insert the data directly from mysql use the INSERT statement. The format for INSERT is INSERT INTO <table name> (column1, column2, ....) values ( 'value1', 'value2', ...). For example to insert a species name into the table species the command is like this :

mysql> INSERT INTO species (name) values ('Cat');
Query OK, 1 row affected (0.00 sec)

Remember to put single quotes around a value it is a string.

Notice that i don't have to set the value of id because id have AUTO_INCREMENT attribute. Whenever you insert a new record to the table the value of id is set automatically by mysql with increasing values. The AUTO_INCREMENT attribute is commonly used to create unique identity for new rows

Next example will show how to insert data from a text file, you can get the file here and try in on your computer.

mysql> LOAD DATA LOCAL INFILE "insert.txt" INTO TABLE species;
Query OK, 3 rows affected (0.00 sec)

You can also run SQL queries directly from the DOS prompt. Assuming insert.txt is in C: you can run the query in insert.txt like this

C:mysql < insert.txt


Read Full Article
 

Create a Table in MySQL

 Print E-mail
MySql General
Current revision posted to MySQL Tutorials by admin on 12/19/2008 7:23:38 PM

For this example we'll create two tables. The first one describe the species of animals available in a pet store and the second will store the data of a pet in the store. The table names will be species and pet

The species table will consist of the id and the animal species, and the pet table will consist of animal id, species, sex, and price

mysql> CREATE TABLE species (id INT NOT NULL AUTO_INCREMENT, species varchar(30) NOT NULL, primary key(id));
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE pet(id INT NOT NULL AUTO_INCREMENT, sp_id INT NOT NULL, sex CHAR(1) NOT NULL, price DECIMAL(4,2) NOT NULL, primary key(id));
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW tables;
+--------------------+
| Tables_in_petstore |
+--------------------+
| pet                |
| species             |
+--------------------+
2 rows in set (0.00 sec)

mysql> DESCRIBE species;
+---------+-------------+----+-----+---------+---------------+
| Field   | Type        |Null| Key | Default | Extra         |
+---------+-------------+----+-----+---------+---------------+
| id      | int(11)     |    | PRI | NULL    | auto_increment|
| name    | varchar(30) |    |     |         |               |
+---------+-------------+----+-----+---------+---------------+
2 rows in set (0.05 sec)

mysql> DESC pet;
+-------+--------------+----+-----+---------+----------------+
| Field | Type         |Null| Key | Default | Extra          |
+-------+--------------+----+-----+---------+----------------+
| id    | int(11)      |    | PRI | NULL    | auto_increment |
| sp_id | int(11)      |    |     | 0       |                |
| sex   | char(1)      |    |     |         |                |
| price | decimal(4,2) |    |     | 0.00    |                |
+-------+--------------+----+-----+---------+----------------+
4 rows in set (0.00 sec)

The SQL sytax to create table is : CREATE TABLE <tablename> (<list of fields>)

The DESCRIBE or DESC statement is used to show a description of a table. You can also use EXPLAIN or SHOW COLUMNS

mysql> EXPLAIN pet;
+-------+--------------+----+-----+---------+----------------+
| Field | Type         |Null| Key | Default | Extra          |
+-------+--------------+----+-----+---------+----------------+
| id    | int(11)      |    | PRI | NULL    | auto_increment |
| sp_id | int(11)      |    |     | 0       |                |
| sex   | char(1)      |    |     |         |                |
| price | decimal(4,2) |    |     | 0.00    |                |
+-------+--------------+----+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM pet;
+-------+--------------+----+-----+---------+----------------+
| Field | Type         |Null| Key | Default | Extra          |
+-------+--------------+----+-----+---------+----------------+
| id    | int(11)      |    | PRI | NULL    | auto_increment |
| sp_id | int(11)      |    |     | 0       |                |
| sex   | char(1)      |    |     |         |                |
| price | decimal(4,2) |    |     | 0.00    |                |
+-------+--------------+----+-----+---------+----------------+
4 rows in set (0.00 sec)


Read Full Article
 

Create New MySQL Database

 Print E-mail
MySql General
Current revision posted to MySQL Tutorials by admin on 12/19/2008 7:22:08 PM

You need to use mysqladmin to create MySQL database. The command is simple just write mysqladmin in a dos window followed by the database name you want to create

C:>mysqladmin create petstore

C:>mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3 to server version: 4.0.18-nt

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> SHOW databases;
+----------+
| Database |
+----------+
| mysql    |
| petstore |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql>

You can also type the query in mysql> prompt like this

mysql> CREATE database petstore;
Query OK, 1 row affected (0.00 sec)

To show available databases in mysql use the command show databases on mysql> prompt. Now use the database by typing USE petstore and then type SHOW tables to see what tables are available in the database

mysql> USE petstore;
Database changed
mysql> SHOW tables;
Empty set (0.00 sec)

Next I will show you how to create table in mysql database


Read Full Article
 
More Articles...
« StartPrev1234NextEnd »

Page 1 of 4

Subscribe By Email

Enter your email address:

Delivered by FeedBurner

Donate

Development & maintainance needs time & money.
With your donation you can help us to keep this project alive
Donate:
  Monthly Monthly
Currency
Amount

Translate

Earn For Skills

Copyright @ 2010 | Tutorialsforu.info | Developed by Open Source Coders | Add your link.