MySQL

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

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

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;

Clause

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

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

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

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;

count employees

select count(id) from employee;

select all employee but only show 5 records

select * from employee limit 5;

Where clause

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

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

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:

%>mysql --help

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

To start

$ /sbin/service mysqld start

To stop

$ /sbin/service mysqld stop

or

$ pkill mysqld

To restart

$ /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');
Technologies: