Difference between revisions of "mySQL walkthrough"

From HE FAQ
Jump to: navigation, search
(Created page with '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 dataB…')
 
(Made more professional)
 
Line 1: Line 1:
 
SSH to your account, and from the shell, type:
 
SSH to your account, and from the shell, type:
  
−
  mysql -ppassword
+
  mysql -p username
  
−
(no space after -p )
+
That will get you in the SQL prompt:
−
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.
 
  Welcome to the MySQL monitor.  Commands end with ; or \g.
 
  Your MySQL connection id is 35795 to server version: 3.21.33b
 
  Your MySQL connection id is 35795 to server version: 3.21.33b
 
  Type 'help' for help.
 
  Type 'help' for help.
  
 +
Then you can issue standard SQL commands.  SQL commands are traditionally shown in all uppercase, but in reality they can be lowercase just as well.  Table and column names, however, are case-sensitive.
  
−
the database's name is the same as the username (= account name as well).
+
For example:
−
Then you can issue standard SQL commands.
+
−
for example:
+
  
 
  mysql> show tables;  
 
  mysql> show tables;  
  
 
shows a list of all the tables in the database, or none if none.
 
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.
+
Note that most SQL commands need a ; semicolon at the end.
  
 
  mysql> describe some_table;  
 
  mysql> describe some_table;  
  
−
describes a table. who says SQL is difficult.
+
describes a table.
−
you may not have any tables in the database at this point.
+
You may not have any tables in the database at this point. Let's create one:
−
let's create one:
+
  
−
  mysql>create table my_table ( id INT, name CHAR(64), tel CHAR(16) );
+
  mysql> create table my_table ( id INT, name CHAR(64), tel CHAR(16) );
  
−
it means we create a table to store 3 columns:
+
This means we create a table to store 3 columns: an id for each person, a name for each of them, and a phone number.
−
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,
+
Now we have a table structure, however the table is still empty.  Let's stuff some data in:
−
let's stuff some data in:
+
  
−
  mysql>insert into my_table (id, name, tel) values ( 1, "Mr. Tux", "408-282-1540");
+
  mysql> insert into my_table (id, name, tel) values ( 1, "Mr. Tux", "408-282-1540");
 
   
 
   
 
  Query OK, 1 row affected (0.01 sec)  
 
  Query OK, 1 row affected (0.01 sec)  
  
−
means the query you put went all right.
+
This means that the command you entered went all right. If it wasn't all right, you may read something like:
−
if it wasn't all right, you may read something like:
+
  
 
  ERROR 2000: parse error near '' at line 1
 
  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,
+
If you had a typo, you can recall the last type-in with the upward arrow key, just like in the shell.
−
just like in the shell.
+
Let's enter a couple more:
−
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 ( 2, "Ms. Tuxinette", "408-282-1540");
 
  mysql> insert into my_table (id, name, tel) values ( 3, "Squishy Junior", "123-456-7890");
 
  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:
−
to see what is in the table:
+
  
−
  mysql>select id,name,tel from my_table;  
+
  mysql> select id,name,tel from my_table;  
 
   
 
   
 
  +------+----------------+--------------+
 
  +------+----------------+--------------+
Line 67: Line 56:
 
  3 rows in set (0.01 sec)
 
  3 rows in set (0.01 sec)
  
−
we could also have said:
+
We could also have said:
  
 
  mysql>select * from my_table;
 
  mysql>select * from my_table;
  
 
gives the same answer, * stands for all the fields (= columns).
 
gives the same answer, * stands for all the fields (= columns).
−
this is the SELECT sql statement, the most used sql command.
+
This is the SELECT sql statement, the most used sql command. It retrieves data according to what you ask for.
−
it retrieves data according to what you want (supposedly).
+
  
−
other example, we want the telephone and name only:
+
As another example, if we want the telephone and name only:
  
 
  mysql> select tel,name from my_table;
 
  mysql> select tel,name from my_table;
Line 89: Line 77:
  
  
−
let's get a bit more picky, we want only those whose name contains "ux":
+
Let's get a bit more picky, we want only those whose name contains "ux":
  
−
  mysql>select * from my_table where name regexp 'ux';
+
  mysql> select * from my_table where name like '%ux%';
 
   
 
   
 
  +------+---------------+--------------+
 
  +------+---------------+--------------+
Line 111: Line 99:
 
  2 rows in set (0.00 sec)
 
  2 rows in set (0.00 sec)
  
−
  mysql> select id from mytable where name='Squishy Junior';
+
  mysql> select id from my_table where name='Squishy Junior';
 
   
 
   
 
  +------+
 
  +------+
Line 121: Line 109:
  
  
−
now we have a small table.
+
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.
+
The UPDATE sql command changes a row in a table. You can change the table structure itself with the ALTER TABLE command.

Latest revision as of 11:13, 6 May 2014

SSH to your account, and from the shell, type: 

mysql -p username

That will get you in the SQL prompt: 

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.

Then you can issue standard SQL commands. SQL commands are traditionally shown in all uppercase, but in reality they can be lowercase just as well. Table and column names, however, are case-sensitive.

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. 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) );

This means we create a table to store 3 columns: an id for each person, a name for each of them, and a phone number.

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)

This means that the command you entered 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");

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 ask for.

As another example, if 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 like '%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 my_table where name='Squishy Junior';

+------+
| id   |
+------+
|    3 |
+------+
1 row in set (0.01 sec)


Now we have a small table.

The UPDATE sql command changes a row in a table. You can change the table structure itself with the ALTER TABLE command.

Retrieved from "https://faq.he.net/index.php?title=mySQL_walkthrough&oldid=651"