home

articles

broadband

browsers

fraud001

javascript001

javascript002

javascript003

links

mysql01

mysql02

mysql03

mysql04

php001

php002

php003

problem001

spyware

virus01

virus02

web_programming

Sponsor001

 

My SQL

Part 2 - Basic SQL - Page 4

Continued from page 3. This page is about adding content to the database.

USE DATABASE

The syntax is "use database-name;", the database-name is 'idiots', so you type "use idiots;"

mysql> use idiots;

Database changed

mysql>

CREATE TABLES

The syntax is "create table table-name(column1-name column1-type, column2-name column2-type, column3 etc, other_data);", the table-name is 'people', so type the following, very carefully:

mysql> create table people(id int(4) auto_increment not null, first_name text, last_name text, PRIMARY KEY(id));

Query OK, 0 rows affected (0.00 sec)

mysql>

SHOW TABLES

Type at the prompt:

C:\mysql\bin> show tables;

And you will see:

Tables_in_idiots

people

1 row in set (0.00 sec)

mysql>

EXPLAIN PEOPLE

Type at the prompt:

mysql> explain people;

Field

Type

Null

Key

Default

Extra

id
first_name
last_name

int(4)
text
text

 
YES
YES

PRI

NULL
NULL
NULL

auto_increment

3 rows in set (0.00 sec)

mysql>

INSERTING DATA

Type at the prompt:

mysql> insert into people(first_name, last_name) values("Tony", "Blair"),("George","Bush");

Query ok, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql>

Type at the prompt:

mysql> select * from people;

id

first_name

last_name

1
2

Tony
George

Blair
Bush

2 rows in set (0.00 sec)

mysql>

MORE IDIOTS

mysql> insert into people(first_name, last_name) values("Gordon", "Brown"),("John","Prescot");

Query ok, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql>

Type at the prompt:

mysql> select * from people;

id

first_name

last_name

1
2
3
4

Tony
George
Gordon
John

Blair
Bush
Brown
Prescot

4 rows in set (0.06 sec)

mysql>

ALTER TABLE

mysql> alter table people add nick_name text;

Query ok, 4 rows affected (0.17 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql>

Type at the prompt:

mysql> select * from people;

id

first_name

last_name

nick_name

1
2
3
4

Tony
George
Gordon
John

Blair
Bush
Brown
Prescot

NULL
NULL
NULL
NULL

4 rows in set (0.06 sec)

mysql>

UPDATE

mysql> update people set nick_name="Big C" where first_name="Tony";

mysql> update people set nick_name="Big W" where last_name="Bush";

mysql> update people set nick_name="Waiter" where id=3;

mysql> update people set nick_name="Boxer" where first_name="John";

mysql> select * from people;

id

first_name

last_name

nick_name

1
2
3
4

Tony
George
Gordon
John

Blair
Bush
Brown
Prescot

Big C
Big W
Waiter
Boxer

4 rows in set (0.00 sec)

mysql>