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?
what database u want to learn?? mysql ??
if yes, i'm sure mysql help is good enough to start learning mysql.
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!!!
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
|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:
|mysql -u john_doe -h sql_server -p unknown |
|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:This will generate an output like:
The output will show you the names of the databases, the number of results and the time it took to run the query;
|mysql> show databases;
| Database |
| information_schema |
| mysql |
| portal |
| xu |
4 rows in set (0.11 sec)
Now, let's select a database: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:(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:
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.
|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)
Now, let's see what kind of data can a certain table contain (how it's defined);
This will show you a description of the table; The output should be something like:
|describe help_keyword; |
The output will tell you the fields name, their type and a few other infos;
|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)
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:
|select * from help_keyword; |
|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:
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!
|delete from help_keyword where name='test'; |
Now, let's create a simple table:
|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:
|drop table <table_name>; |
Also, the drop command can be used to drop a database (if you have privileges to do so)
|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!
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!