Beyond Computing: MySQL basic commands part 2

MySQL basic commands part 2

Published 21.3.06 by mayaTitan | E-mail this post

first, srry fot not posting for a few days, i have had few personal problems...
-----------------------------------------------------------------------------------

note: USE database command is one of commands that doesn't need ; sign for marking end of command.

-----

Now when we have selected specific database with command USE, we now that there is no tables in it. You should know that datas in databases like MySQL are stored in tables, where every atribut has its data type.

We'll create table, with command that is logical: CREATE name_of_table ()
Inside () we are putting atributed and defining its data types.
Here we will present few data types, through our example, but if you want to see all of them and their use, check MySQL specification reference.
For our students table we will put in there theirs: name, surname, height, weight, birth.
For name, surname we will use varchar() type of data. In () we are putting max number of characters that name or surname can have.
For height and weight we will use float (decimal) number type
For birth we will use date type of data where type of date is: YYYY-MM-DD by default.

In this table setting of primary keys is not covered because, here I'm tring to explain very basics. Primary key and relations will be covered after this and few more topics.

So here is our command:
CREATE TABLE person (name varchar(15), surname varchar(20), height float, weight float, birth date);
To see weather is everything ok about data types we'll use this command:
-> DESCRIBE name_of_table; or DESC name_of_table;

Now, we need to fill that table. We'll use technique of importing .txt file into our table. For that we need to create .txt file 'C:\file.txt' where will be few lines of our datas. Every row will have data orderd by atributes and separated by 1 TAB.

exemple of 1row of txt file:
name1 (1 TAB separation) surname1 (1 TAB separation) height (1 TAB separation) weight (1 TAB separation) birth (1 TAB separation) \r\n

\r\n is string that is used for end of row, so command can recognise end of 1 row

Syntax of commands that imports .txt file into table is:
LOAD DATA LOCAL INFILE 'C:/path_to_file' INTO TABLE name_of_table LINES TERMINATED BY '\r\n';

note: when putting path of file use sign / for separating between folders or drives, not \ as it is common in Windows Explorer

After this we'll check to make sure that all datas are correctly imported.

SELECT * FROM name_of_table;