Let's begin with some concepts:
- Database: A database is simply a structured set of data held in a computer. There are many different types of database but the most commonly used databases are relational databases.
- Relational databases are structured to recognize relations between stored pieces of data.
- Relational Database Management System (RDBMS) is a database management software that is based on the relational model. There are many different RDBMS such as MySQL, PostGreSQL, Microsoft SQL Server, and Oracle RDBMS. RDBMS understand SQL.
- Structured Query Language (SQL) is a language which allows users to pass commands to the RDBMS for data manipulation.
- MySQL is a widely used and easy to learn RDBMS
A database is a structure set of data. A relational database is a database where data is structured on relations. Relations are defined in SQL. We store data and run SQL in a software called RDBMS. Here, you will learn SQL in MySQL.
MySQL is a free RDBMS available at mysql.org. I strongly recommend that you install a tool such as phpMyAdmin,to make it easier to work with MySQL.
SQL is a language for querying and manipulating data in a RDBMS. SQL is a standard but each RDBMS has it's own variation. For a beginner, you need not worry about the differences.
SQL is not case-sensitive
select * from mytable;
is the same as
SELECT * FROM mytable;
SQL statements end with ;
select * from mytable;
Comments SQL comments begin with -- . Comments are not interpreted by RDBMS.
-- this is a comment select * from mytable;
A statement can have one or more clauses.
SELECT salary FROM employee WHERE employee_id = 12345;
- FROM clause specifies the table
- where clause specifies a condition. This condition must be satisfied.
Functions perform specific functions on data.
select distinct(city) from students;
This table contains list of all students. Naturally there will be multiple students a big cities. Instead of returning 500 rows for Toronto and 200 rows for Burlington, this table will return only one row for each city.
Expressions are used to derive information from data.
select stock_name, earnings / shares as eps from stocks;
This expression will take the earnings of each stock and divide it by its shares. The output would look like the following:
ABC 11.9 BCD 10.2 ...
Select statement is used to fetch data from the RDBMS. You learn SQL by writing SQL. Following are some examples;
List all employees
select * from employee;
List only employee ids and names
select id, name from employee;
list only employee ids and names but order the results by name
select id, name from employee order by name;
select count(id) from employee;
select all employee but only show 5 records
select * from employee limit 5;
Where clause allows you to be specific about the data you need.
select * from employee
will return all employees, but the following code will return only the employee we are looking for:
select * from employee where employee_name = 'Justin Trudeau';
Furthermore, we can also use comparison operators, where applicable:
select * from merchandise where price < 500;
This statement selects all items that costing less than $500.
select * from employee where name like 'Jo%';
% is a wildcard character that means select anything. So Jo% means any string beginning with Jo. So it will select Jones, John, Joseph, etc.
Insert statement is used to add rows into a table. See the following example.
insert into location (city, country) values ('Toronto', 'Canada');
This statement will insert one record, Toronto, Canada in the table location.
Update statement changes data in existing rows. Before you update, select the data, make sure the selection include all the required data and only the required data. Then write a relevant update statement.
select country from location where name = 'Burma'; update country set name = 'Myanmar' where name = 'Burma';
Note that the where clause has not changed between the two statements.
Connecting to the MySQL Server For the First Time
The MySQL client program, has the following command format.
%>mysql [options] [database]
where [options] can be a series of options, and [database] is the name of the database we wish to use. Both are optional parameters. A useful command for first time users is:
To start, type the following command.
%>mysql -u root
Here were defined that we would like to log in with the username root (-u root). Something like the following should appear:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 78 to server version: 5.0.22-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Congratulations, you are now connected to the MySQL monitor as the almighty root user. Your first official action as this supreme leader of the MySQL database server should be to ensure that nobody else can declare this position. Therefore, make it possible to only connect as root in the future by supplying a password. Change the password from its current blank (or null) value, to something difficult to guess using the following command:
mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secret_password');
The 'root', which is the username, and 'localhost', which is the hostname, constitute a unique user in MySQL. For those readers perhaps unfamiliar with networking terminology, 'localhost' is a name used to specify the local server; that is, the server upon which MySQL resides. Therefore, by stating 'root'@'localhost', this command is telling the MySQL server to set the password for a user named 'root' that will connect specifically from the local server (thus 'localhost'). More specifically, this command will change the password by updating what are commonly known as the MySQL privilege tables. These tables, collectively located in the mysql database, contain information regarding the connection and usage capabilities of all users intended to use the MySQL database server. More specifically, this command will update the user table, updating the password field of the row in which the user field's value is 'root'. The password field will be updated with the encrypted value of the string enclosed within the Password() function.
Of course, do not forget this password. Since it is stored in encrypted text on the database server, it cannot simply be looked up if forgotten. There is also an alternative method for updating a password:
%>mysqladmin -u root password 'secret_password'
This command will accomplish the same results as the one previously introduced.
Reconnecting and exiting MySQL
To connect to mysql, you need to do the following.
%>mysql -u root -p
This would prompt you for your password. Type your password and hit enter. You can also type your password in the mysql command as follows, but it is not a good idea.
%>mysql -u root -pMY_password
To quit MySQL you can type any of the following commands:
mysql>\q mysql>quit mysql>exit
Selecting MySQL Database
To get a list of database installed on your MySQL, you need to type the following command:
mysql> show databases;
To select a database, type the following command:
mysql> use xyzdatabase
Alternately, you can select a database when connecting to mysql
%> mysql -u root -p xyzdatabase
Starting stoping restarting MySQL Server
$ /sbin/service mysqld start
$ /sbin/service mysqld stop
$ pkill mysqld
$ /sbin/service mysqld restart
MySQL Create User
In mysql, there are many ways to create a user. Let's begin my logging in as root
mysql -u root -p
The following command adds a user to MySQL database.
CREATE USER 'me'@'localhost' IDENTIFIED BY 'mypass';
To check if a user has been added, check mysql.user table.
use mysql; select * from user;
You may have noticed that all privileges are set to no so the new user can pretty much do nothing. This leads us to your next question. How do I grant access to my new user? To grant privileges, we use the GRANT command as follows:
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'me'@'localhost';
Grants privileges to select, insert, update, and delete all tables of the database mydatabase.
GRANT ALL ON mydatabase.* TO 'me'@'localhost';
Grants all privileges on the database mydatabase.
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'me'@'localhost';
Grants privileges to select, insert, update, and delete on all databases.
There are several ways to add a user.
INSERT INTO user(Host,User,Password) VALUES ('localhost','me',PASSWORD('mypass'));
You can also grant privileges while creating a user.
INSERT INTO user(Host,User,Password,Select_priv,Insert_priv,Update_priv) VALUES ('localhost','me',PASSWORD('mypass'),'Y','Y','Y');
Clone MySQL Table
The following query will create a cloned copy of an existing table; structure, indexes, and triggers.
CREATE TABLE mynewtable LIKE myoldtable;
Copy entire table data
The following query copies all the data of one table to another. For this to work, both table must have the same name, number, and order of columns.
INSERT mynewtable SELECT * FROM myoldtable;