mySQL walkthrough
SSH to your account, and from the shell, type:
mysql -ppassword
(no space after -p ) that will get you in the SQL prompt, where the first command should be:
mysql> use dataBaseName; Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35795 to server version: 3.21.33b Type 'help' for help.
the database's name is the same as the username (= account name as well).
Then you can issue standard SQL commands.
for example:
mysql> show tables;
shows a list of all the tables in the database, or none if none. note that most SQL commands need a ; semicolon at the end.
mysql> describe some_table;
describes a table. who says SQL is difficult. you may not have any tables in the database at this point. let's create one:
mysql>create table my_table ( id INT, name CHAR(64), tel CHAR(16) );
it means we create a table to store 3 columns: an id for each people, a name for each of them, and a phone number. (however Patrick Mc Goohan may not like to be numbered, since he's a free man)
now we have a table structure, however the table is still empty, let's stuff some data in:
mysql>insert into my_table (id, name, tel) values ( 1, "Mr. Tux", "408-282-1540"); Query OK, 1 row affected (0.01 sec)
means the query you put went all right. if it wasn't all right, you may read something like:
ERROR 2000: parse error near at line 1
if you had a typo, you can recall the last type-in with the upward arrow key, just like in the shell. let's enter a couple more:
mysql> insert into my_table (id, name, tel) values ( 2, "Ms. Tuxinette", "408-282-1540"); mysql> insert into my_table (id, name, tel) values ( 3, "Squishy Junior", "123-456-7890");
I leave up to your imagination and finger aerobics to fill it up with more data. to see what is in the table:
mysql>select id,name,tel from my_table; +------+----------------+--------------+ | id | name | tel | +------+----------------+--------------+ | 1 | Mr. Tux | 408-282-1540 | | 2 | Ms. Tuxinette | 408-282-1540 | | 3 | Squishy Junior | 123-456-7890 | +------+----------------+--------------+ 3 rows in set (0.01 sec)
we could also have said:
mysql>select * from my_table;
gives the same answer, * stands for all the fields (= columns). this is the SELECT sql statement, the most used sql command. it retrieves data according to what you want (supposedly).
other example, we want the telephone and name only:
mysql> select tel,name from my_table; +--------------+----------------+ | tel | name | +--------------+----------------+ | 408-282-1540 | Mr. Tux | | 408-282-1540 | Ms. Tuxinette | | 123-456-7890 | Squishy Junior | +--------------+----------------+ 3 rows in set (0.02 sec)
let's get a bit more picky, we want only those whose name contains "ux":
mysql>select * from my_table where name regexp 'ux'; +------+---------------+--------------+ | id | name | tel | +------+---------------+--------------+ | 1 | Mr. Tux | 408-282-1540 | | 2 | Ms. Tuxinette | 408-282-1540 | +------+---------------+--------------+ 2 rows in set (0.03 sec)
mysql> select * from my_table where id > 1; +------+----------------+--------------+ | id | name | tel | +------+----------------+--------------+ | 2 | Ms. Tuxinette | 408-282-1540 | | 3 | Squishy Junior | 123-456-7890 | +------+----------------+--------------+ 2 rows in set (0.00 sec)
mysql> select id from mytable where name='Squishy Junior'; +------+ | id | +------+ | 3 | +------+ 1 row in set (0.01 sec)
now we have a small table.
The UPDATE sql command change a row in a table. You can change the table structure itself with the ALTER TABLE command. Also often SQL parts of the statement are in uppercase, but that doesn't matter for mysql. however for the records themse lves, that does matter.
