FRIHOSTFORUMSSEARCHFAQTOSBLOGSCOMPETITIONS
You are invited to Log in or Register a free Frihost Account!


SQL.....Where to start?





The World is Yours
Hello everyone, I would like to learn about Databases and SQL (unrelated to frihost, just in general), but I don't know where to start!!!! I have looked at a few tutorials but all it's showing me is commands!!! Where do I input these commands? What program(s) will I need (on linux, preferably open-source, not freeware but if that's all I can get...)? Can someone shed a bit of light on this for me?
dwikristianto
what database u want to learn?? mysql ??
if yes, i'm sure mysql help is good enough to start learning mysql.

or, googling. Very Happy
The World is Yours
Yes, I googled before I came here because I know that's what everyone considers helping is "try google". However, (as stated in the above post) I have looked at a few tutorials but all it's showing me is commands! I'm not having a problem learning the language, but how to use the language that I am learning!!!
BlueVD
Well, I sugest MySQL if you want to learn databases;
To explain things a bit... SQL = structured query language.
It's exactley what it sais. A Query language; The commands themselves are used to retrieve data that you stored in the database;

I suggest you start with the tools that you get from mysql (when you install it); Depending on your linux distro you might already have it installed;
If no, you'll find plenty of tuts out there on getting it to run.

Once you have it installed it's time to play with it.
Start a command prompt (shell) and type
Code:
mysql -u root [-h hostname] [-p <password>]

Explanation: mysql is the client program; the parameters: -u root the username which will be used for the connection; the other two are optional (depending on the target host for the connection and if the username with which you are connecting must use a password);
So, lets say you are on your pc and you want to connect to a different machine with user "john_doe" and with the password "unknown"; the mysql server has the hostname "sql_server" and the ip "192.168.1.2"; here are the connection params:
Code:
mysql -u john_doe -h sql_server -p unknown
Code:
mysql -u john_doe -h 192.168.1.2 -p unknown

Both of the connection strings are valid. In one of them you use the hostname and in the other the ip;
Now, let's see what's under the hood of the mysql server.
Note: after you connect, you must use a database to make data i/o operations; otherwise, you can make only a limited set of queries (all interrogations/commands issued are called queries);
Before you select a database, lets see how many there are and their names:
Code:
show databases;
This will generate an output like:
Code:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| portal             |
| xu                 |
+--------------------+
4 rows in set (0.11 sec)
The output will show you the names of the databases, the number of results and the time it took to run the query;
Now, let's select a database:
Code:
use portal;
This will select the database (note that database if different than a database server; the database can be seen like a storage house where all tables, reports, etc are stored in;) after the use statement you should get a response like: "Database changed"; Now, lets see what tables exist in that database:
Code:
show tables;
(note: I used the mysql database for this query; it holds all the informations about users, privileges and such; I'll explain them later). The output will look something like:
Code:
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.01 sec)
This will show you what tables exist in the current db, and again, the numbers of results (17 in this case) and the time it took to execute the query.
Now, let's see what kind of data can a certain table contain (how it's defined);
Code:
describe help_keyword;
This will show you a description of the table; The output should be something like:
Code:
mysql> describe help_keyword;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| help_keyword_id | int(10) unsigned | NO   | PRI |         |       |
| name            | char(64)         | NO   | UNI |         |       |
+-----------------+------------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
The output will tell you the fields name, their type and a few other infos;
Now, you might want to know how to retrieve and insert data in your db (in a table);
The syntax is more complicated, but you can look it up in the help files; The commands are insert and select;
Simple select and insert commands would be:
Code:
select * from help_keyword;
Code:
insert into help_keyword(help_keyword_id,name) values(100555,'test');

Also, you will end up (sooner or later) trying to delete some data from a table:
Code:
delete from help_keyword where name='test';
Note: the where clause will make sure you only delete what you want and nothing else. IF YOU DON'T SPECIFY IT ALL THE ENTRIES IN THE TABLE WILL BE DELETED!
Now, let's create a simple table:
Code:
create table test(id tinyint(2) NOT NULL AUTO_INCREMENT UNIQUE, input_field tinytext);

This will create a table with 2 fields: id and input_field; The id field will be the key of the table and it will be auto incremented; and the input_fields is a simple column that will store some text in it; I sugest you read some of the docs to get used with data types (int, tinyint, tinytext, blob, etc);
Also, to delete a table (and all of the data it contains) use:
Code:
drop table <table_name>;

Also, the drop command can be used to drop a database (if you have privileges to do so)
Code:
drop database <database_name>;
;
When you are done learning the basics, you might also want to learn about privileges and other things.
Until then, cheers!
The World is Yours
WOW.... Yes that cleared a few things up. At first I was under the impression that it was server software and I needed something to communicate with it. I am coming along just fine now, I have a bit of knowledge about creating and deleting databases, creating tables, loading data into the tables by commandline or by importing a .txt file.....

Thank you very much!! I appreciate it more than you know!
Related topics
SQL WHERE
AJUDA INSTRUÇÃO SQL WHERE!
Pagination
Create Function to create SQL dynamically
Muslims worldwide protests over cartoons
Do you dream? How to stop.?
How to resolve these URIs?
Where can I start to make my own CMS?
How should I start learning PHP and SQL?
Where to start?
Want to make my own design, but not sure where to start!
Flash - where to start...??
where to start in php
Strange SQL error
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.