- Start the MySQL server by issuing the command mysqld-shareware --standalone at the prompt in c:\mysql\bin. Refer the previous session Installing MySQL on Windows for further details.
- Now invoke the mysql client program by typing mysql at the prompt.
- The prompt is changed to a mysql> prompt. Type:
create database employees;(Note: The command ends with a semi-colon).
- The MySQL server responds with something like:
Query OK, 1 row affected (0.00 sec)
- This means that you have sucessfully created the database. Now, let's see how many databases you have on your system. Issue the following command.
show databases;The server responds with the list of databases.
+----------------+ | Database | +----------------+ | employees | | mysql | | test | +----------------+ 3 rows in set (0.00 sec)Here we have three databases, two created by MySQL during installation and our employees database.
- To come back to the DOS prompt, type quit at the mysql prompt.
- I assume that you are working from your account and not the root. Start a terminal session and become the superuser (Type su at the prompt and then enter the root password).
- Now we'll access the MySQL server. Type:
mysql -u root -pThe system prompts for the MySQL root password that you set up in Installing MySQL on Linux.
Note: This is not the Linux root password but the MySQL root password
Enter the password, which is not displayed for security reasons.
Once you are successfully logged in, the system prints a welcome message and displays the mysql prompt ... something like
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.22.32 Type 'help' for help. mysql>
- Now we are ready for creating the employees database. Issue the command:
create database employees;(Note: The command ends with a semi-colon)
- An important point to note is that this database is created by the root and so will not be accessible to any other user unless permitted by the root. Thus, in order to use this database from my account (called edith), I have to set the permissions by issuing the following command:
GRANT ALL ON gestrack.* TO edith@localhost IDENTIFIED BY "edith";
(Note: The command ends with a semi-colon and password with "")The above command grants my account (edith@localhost) all the permissions on employees database and sets my password to edith You should replace edith with your user name and choose an appropriate password.
- Close the mysql session by typing quit at the prompt. Exit from superuser and come back to your account. (Type exit).
- To connect to MySQL from your account, type:
mysql -u user_name -pType in the password when prompted. (This password was set by the GRANTS ALL... command above) . The system displays the welcome message once you have successfully logged on to MySQL. Here is how your session should look like:
[edith@localhost edith]$ mysql -u edith -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 3.22.32 Type 'help' for help. mysql>
- Typing the command SHOW DATABASES; will list all the databases available on the system. You should get a display similar to:
mysql> SHOW DATABASES; +----------------+ | Database | +----------------+ | employees | | mysql | | test | +----------------+ 3 rows in set (0.00 sec)
- Enter quit at the mysql> prompt to come out of the mysql client program.
Databases store data in tables. So what are these tables?
In simplest terms, tables consist of rows and columns. Each column defines data of a particular type. Rows contain individual records.
Consider the following:
CREATE TABLE employee_data ( emp_id int unsigned not null auto_increment primary key, f_name varchar(20), l_name varchar(20), title varchar(30), age int, yos int, salary int, perks int, email varchar(60) );
The CREATE TABLE keywords are followed by the name of the table we want to create, employee_data. Each line inside the parenthesis represents one column. These columns store the employee id, first name, last name, title, age, years of service with the company, salary, perks and emails of our employees and are given descriptive names emp_id, f_name, l_name, title, age, yos, salary, perks and email, respectively.
Each column name is followed by the column type. Column types define the type of data the column is set to contain. In our example, columns, f_name, l_name, title and email would contain small text strings, so we set the column type to varchar, which means varriable characters. The maximum number of characters for varchar columns is specified by a number enclosed in parenthesis immediately following the column name. Columns age, yos, salary and perks would contain numbers (integers), so we set the column type to int.
Our first column (emp_id) contains an employee id. Its column type looks really mean, yeh?. Let's break it down.
int: specifies that the column type is an integer (a number).
unsigned: determines that the number will be unsigned (positive integer).
not null: specifies that the value cannot be null (empty); that is, each row in the column would have a value.
auto_increment: When MySQl comes across a column with an auto_increment attribute, it generates a new value that is one greater than the largest value in the column. Thus, we don't need to supply values for this column, MySQL generates it for us! Also, it follows that each value in this column would be unique. (We'll discuss the benefits of having unique values very shortly).
primary key: helps in indexing the column that help in faster searches. Each value has to be unique.
Our company Bignet has grown tremendously over the past two years. We've recruited thousands. Don't you think there is a fair chance that two employees might have the same name? Now, when that happens, how can we distinguish the records of these two employees unless we give them unique identification numbers? If we have a column with unique values, we can easily distinguish the two records. The best way to assign unique numbers is to let MySQL do it!
A casual example of creating tables:
CREATE TABLE `users` (
`id` int(3) NOT NULL auto_increment,
`login` varchar(8) default NULL,
`password` varchar(8) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;