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>