MySQL Tutorial

Welcome to my SQL tutorial, I am going to focus this on MySQL since it is the most widely used database on the internet right now. Well, first there are some prerequisites to this tutorial.
1.) Have mysql server installed on your local machine.
2.) Basic command line knowledge
3.) Ability to understand.

Okay so first we need to download the MySQL Server program, anyone who is trying to learn SQL should have this.
http://www.soft32.com/Download/Free/MySQ...972-1.html
You can download it from that link, mind you it is a pretty big program. Install with all of the default options, but when it asks you to configure your root password use a password you will remeber.

Okay, so now we have the MySQL software installed on our local machine. First thing we want to do is load the program. To do this we open the Command Prompt by Start>Run>cmd.

Now in the CL type
mysql --user=root --password

You can also shorten the commands by just using:
mysql -uusernamehere -ppassword here

A prompt will open up asking for your password, enter it then hit enter.

Now it says:

Code:

Welcome to the MySQL monitor, Commands end with ; or \g.
Your MySQL Connection id is #
Server version: 5.1.34-community MySQL Community Servr

Type 'help; or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Note: If you didnt allow anonymous connections, which by default is off then just typing mysql to start the program will not work. You will get an error code like this:

Code:

$ mysql
ERROR 1045 (28000): Access denied for user 'blah'@'localhost'
(using password: NO)

To connect to an outside server you need to have the host name, username, and password to connect.

Code:

mysql -hhost.blah.com -uyourname -ppassword dbname

NOTE: Make sure there arent any spaces inbetween the -p and your password or it will not read correctly.

Now for our first command we want to know is how to show the databases.

We will enter:

Code:

mysql> show databases;

You don't type mysql>, I'm just showing you what it should look like in your command prompt.
Now the server will print out all databases on it. If running from local machince which you should be for this tutorial you will receive this:

Code:

mysql> Show Databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.01 sec)

Now to change the current selected database we will use the \u command.

Code:

mysql> \u test

Now you will see:

Code:

mysql> \u test
Database changed
[\code]

If you try to connect to a database that doesn't exist on the server you will recieve the following error:
[code]
mysql> \u randomdb
ERROR 1049 (42000): Unknown database 'randomdb'

You can also connect to a specific database on connection using this:

Code:

$ mysql -uusername -ppassword test

That will automatically connect you to your server with the 'test' database already selected.

At anytime while connected to a database you can show your connection status with the '\s' command.

Code:

mysql> \s
- - - - - - -
mysql Ver 14.14 Distrib 5.1.34, for Win32 (ia32)
Connection id: 7
Current database: test
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.1.34-community
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 15 min 3 sec

Threads: 1 Questions: 16 Slow queries: 0 Opens: 15
Flush tables: 1 Open tables: 8 Queries per second avg: 0.178

That gives you all available connection information.


Creating a new database.

To create a new database we use suprise! the CREATE DATABASE command.

Code:

mysql> create database newdb;
Query OK, 1 row affected (0.00 sec)

mysql>

If you try to create a database that already exists you will see the following error:

Code:

mysql> CREATE DATABASE test;
ERROR 1007 (HY000): Can't create database 'test';
database exists

A nifty trick now is to use SHOW WARNINGS it will show you all of the errors youve received while connected.

Code:

mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------+
| Note | 1007 | Can't create database 'test'; database exists |
+-------+------+--------------------------------------------------+
1 row in set (0.00 sec)

Deleting a database:

Code:

mysql> DROP DATABASE newdb;
Query OK, 0 rows affected (0.00 sec)

Now to the good stuff... Creating tables.
ok first we will recreate the database newdb then create a table

Code:

mysql> create database newdb;

Now we use CREATE TABLES to make a new table in our database.

Code:

mysql> CREATE TABLE people (
-> firstname VARCHAR(40),
-> lastname VARCHAR(40)
-> );
Query OK, 0 rows affected (0.00 sec)

VARCHAR(40) just places a restriction saying that the name can't be more than 40 characters long.

Now entering data into the column we have created 'people'.

Code:

mysql> insert into people (firstname)
-> values ('Boba');
Query OK, 1 row affected (0.00 sec)

mysql> insert into people (lastname)
-> values ('Gadoosh')
Query OK, 1 row affected (0.00 sec)

Now we have data stored in both the firstname and lastname columns in the table people.

To retrieving data from this table we use the SELECT FROM statement:

Code:

mysql> SELECT * FROM people;
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| Boba | Gadoosh |
+-----------+----------+
1 row in set (0.00 sec)

Using the asterisk * selects all values intered into the table.

Well, if i get any feedback from this I will write more, but now that you know the basics you can do most that you want, if you have any questions or requests post them and I will get the answer for you if I don't already know it. Also if you want a tutorial on any other database types Postgresql, oracle or anything just ask.

........... ........................................................................................................................................................................................

.........................................................................................................................................................................................................

..........................................................................................................................................................................................................