MySQL
Running MySQL can be done through the Windows menu: Start -> Programs -> MySQL -> MySQL Server 5.0 -> MySQL Command Line Client. Then you enter the password you created during installation of MySQL.
Enter password: ******
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 60
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\ h' for help. Type '\ c' to clear the buffer.
mysql>
Displaying database
We try with the command "SHOW DATABASES" which will feature a database that is in our MySQL system.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Mysql |
| Test |
---------------------+
3 rows in set (0.00 sec)
Note: The term database needs to be well understood. In the MySQL database is a collection of tables. The number of tables at least one fruit, and the maximum is unlimited. More and more tables, then the greater the size of your database. Which limits the database is the ability of our operating system, and also the amount of space in haarddisk capacity and memory of your computer. More details about this can be seen on the MySQL site (http://www.mysql.com).
Create a new database
Already there are 3 pieces in the system database in MySQL. Now we will create a database for our practice. Use the command "CREATE DATABASE" to create a database.
mysql> create database latihan1;
Query OK, 1 row affected (0.02 sec)
You notice from the two above MySQL command, that every command always ends with ";" (semicolon). Indeed, in general MySQL commands terminated by a sign ";" this. Note this command when written without a semicolon ";".
mysql> create database latihan2
->
MySQL The system will display an arrow '->' which states that the MySQL command is considered not finished (because it has not ended with a semicolon';').
Now we complete the previous command with a semicolon ';'
mysql> create database latihan2
->;
Query OK, 1 row affected (0.02 sec)
Well, everything is normal right? :) Let us continue the tutorial ...
We check again the result of the above command with "SHOW DATABASES".
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Latihan1 |
| Latihan2 |
| Mysql |
| Test |
+--------------------+
5 rows in set (0.00 sec)
Deleting databases
We do not need a database latihan2, then we can remove it with DROP DATABASE command. Be careful in using the DROP DATABASE command, because the database and all its contents will disappear from our earth without being able to return again! Worse yet, the system of MySQL does not provide confirmation to your questions before making the removal process this database!
mysql> drop database latihan2;
Query OK, 0 rows affected (0.02 sec)
You can check it out again the result of the above command with "SHOW DATABASES".
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Latihan1 |
| Mysql |
| Test |
+--------------------+
4 rows in set (0.00 sec)
You notice, the database latihan2 already disappeared. Again, be careful in using the DROP DATABASE command!
Selecting and opening a database
Now we select database "latihan1" and we open with the command "USE"
mysql> use latihan1;
Database change
Viewing contents of a database
To view the contents of a database, we use the command "SHOW TABLES". Let's try.
mysql> show tables;
Empty set (0.00 sec)
The results of the SHOW TABLES command above is "Empty Set", which means there is no any table in the database latihan1.
Create a new table
We will create a new table by using the command "CREATE TABLE". Examples are as follows ..
mysql> create table employee;
ERROR 1113 (42000): A table must have at least 1 column
Apparently there are errors that occur. To create a table in MySQL, we must specify at least one field / column in it. Sekrang we change the above command is as follows ...
mysql> create table employee
-> (Nopeg unsigned INT PRIMARY KEY AUTO_INCREMENT,
-> Name VARCHAR (50) NOT NULL)
->;
Query OK, 0 rows affected (0:14 sec)
Long enough so the change orders. Perhaps the command syntax a little confusing at first. It's okay, then we will discuss what it means. In general, we will create an Employee table with 2 of the column / field. The first column is NOPEG with integer data type (integer), without a negative sign (unsigned), which will increase in value automatically (AUTO_INCREMENT), and the main column is a column NOPEG (PRIMARY KEY).
Then in the second column, NAME will contain the names of employees, with variable data type Character, width of the data can accommodate a maximum of 50 characters, and should not be left blank (NOT NULL). Less is more like that's the story .. :)
We see again what the contents of the database latihan1:
mysql> show tables;
+--------------------+
| Tables_in_latihan1 |
+--------------------+
| Employee |
+--------------------+
1 row in set (0.00 sec)
From the results of the above command, we see that the database latihan1 already have a table named employees. Next we'll see what the structure of the employee table.
Looking at the table structure
To see the structure of a table can use the command "DESCRIBE" or can also use the command "SHOW COLUMNS FROM". The following example ...
mysql> DESCRIBE employees;
+-------+------------------+------+-----+--------- +----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+--------- +----------------+
| Nopeg | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar (50) | NO | | | |
+-------+------------------+------+-----+--------- +----------------+
2 rows in set (0.02 sec)
Or use the command "SHOW COLUMNS FROM ..."
mysql> show columns from employee;
+-------+------------------+------+-----+--------- +----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+--------- +----------------+
| Nopeg | int (10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar (50) | NO | | | |
+-------+------------------+------+-----+--------- +----------------+
2 rows in set (0.00 sec)
There was no difference in the results of the two commands above, is not it? Now we create a new table again, we call it a table example 1.
mysql> create table example 1
-> (Paranoid INT)
->;
Query OK, 0 rows affected (0:13 sec)
Now let's see how many tables that exist in the database latihan1:
mysql> show tables;
+--------------------+
| Tables_in_latihan1 |
+--------------------+
| Example 1 |
| Employee |
+--------------------+
2 rows in set (0.00 sec)
Deleting tables
Table example 1 that we just created it will be removed again. Command to delete a table in MySQL is "DROP TABLE". Quite similar to the command to delete a database, right? We must use the command "DROP" with high caution. MySQL system will not provide early warning or confirmation for the removal process table. And when it is removed, the table we can no longer return. So, be careful!
mysql> drop table example 1;
Query OK, 0 rows affected (0.03 sec)
We refer to another existing table in the database latihan1:
mysql> show tables;
+--------------------+
| Tables_in_latihan1 |
+--------------------+
| Employee |
+--------------------+
1 rows in set (0.00 sec)
Changing the structure of a table
There are times when we need to change the table structure that we have previously made. Changing the structure can be in terms of adding columns (ADD), changing the width and type of columns (modify), or could be the elimination of the column and index (DROP), replacing the name of the column (CHANGE), substitute the table name (RENAME), and so forth. Whatever you do in that column would have a direct impact on the data that already exists. Well, now we need to add some new columns, namely columns gender, city, date of birth and postcode on the employee table.
The command to change the table structure is "ALTER TABLE". Let's try ...
mysql> alter table employee
-> ADD jenkelamin CHAR (2) NOT NULL,
-> ADD city VARCHAR (25) NOT NULL,
-> ADD postcode CHAR (5) NOT NULL,
-> ADD DATE tgllahir
->;
Query OK, 0 rows affected (0:20 sec)
Records: 0 Duplicates: 0 Warnings: 0
Now we see the results:
mysql> DESCRIBE employees;
+------------+-------------+------+-----+--------- +----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+--------- +----------------+
| Nopeg | int (10) | NO | PRI | NULL | auto_increment |
| Name | varchar (50) | NO | | | |
| Jenkelamin | char (2) | YES | | NULL | |
| City | varchar (25) | NO | | | |
| Postcode | char (5) | NO | | | |
| Tgllahir | date | YES | | NULL | |
+------------+-------------+------+-----+--------- +----------------+
6 rows in set (0.00 sec)
Maybe it is better if we change the column name nopeg aja become paranoid. Likewise with jenkelamin column name, we change its name to jenkel only. In the conversion of this column should be 'the properties' original column remains to be rewritten. Eg if the column has a characteristic nopeg 'auto_increment', so long as those characteristics were maintained, then he (auto_increment) should be rewritten. Here's how ...
Changing column jenkelamin be jenkel, as well as changing the data type of CHAR (2) becomes CHAR (1):
mysql> alter table employee
-> Change jenkelamin jenkel char (1);
Query OK, 0 rows affected (0:24 sec)
Records: 0 Duplicates: 0 Warnings: 0
Changing column nopeg become paranoid, without changing its data type (fixed INT (10), and remained auto_increment):
mysql> alter table employee
-> Change nopeg paranoid int (10) auto_increment
->;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
Now we see the table structure after the conversion:
mysql> DESCRIBE employees;
+----------+-------------+------+-----+---------+- ---------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+- ---------------+
| Paranoid | int (10) | NO | PRI | NULL | auto_increment |
| Name | varchar (50) | NO | | | |
| Jenkel | char (1) | YES | | NULL | |
| City | varchar (25) | NO | | | |
| Postcode | char (5) | NO | | | |
| Tgllahir | date | YES | | NULL | |
+----------+-------------+------+-----+---------+- ---------------+
6 rows in set (0.00 sec)
How, is in conformity with changes in the structure we want, right? Well, now what if we want to change the name of the employee table into table employee? Please try the following:
mysql> alter table employee
-> Rename the employee;
Query OK, 0 rows affected (0:09 sec)
We'll see more results:
mysql> show tables;
+--------------------+
| Tables_in_latihan1 |
+--------------------+
| Employee |
+--------------------+
1 row in set (0.00 sec)
Now we're back again employees become employees of the table name. But with a different command, namely "RENAME TABLE".
mysql> rename table employee
-> To employees
->;
Query OK, 0 rows affected (0:06 sec)
Do not forget to check the results:
mysql> show tables;
+--------------------+
| Tables_in_latihan1 |
+--------------------+
| Employee |
+--------------------+
1 row in set (0.00 sec)
Well, so far it is not difficult to learn MySQL? Now we continue with the ways the data filling. Yuuukk ..
Filling the data into the table
We will begin to fill the employee data into the table. The command used is "INSERT INTO". Do the following:
mysql> insert into employee
-> (Name, jenkel, city, postcode, tgllahir)
-> Values
-> ("Ahmad Zobari", "L", "Bandung", "41,011", "1977-10-02")
->;
Query OK, 1 row affected (0:17 sec)
You notice that in the data entry type character, is always enclosed by double quotes ("). It could also use single quotes ('). But do not be mixed with double quotes and single quotation marks, eg:" Ahmad Sobari'. Note also the writing of the date of birth, using the format "year-month-date". It's rather odd. But that is the standard MySQL format for writing dates. If you look more closely, why we did not include data for column "paranoid"? This is because the nature of the auto_increment column paranoid, so he will automatically contain the numbers 1, and counting 1, along with additional data.
Well, we'll enter 4 pieces record again by means of:
mysql> insert into employee
-> (Name, jenkel, city, postcode, tgllahir)
-> Values
-> ("Sundariwati", "P", "Bandung", "40,123", "1978-11-12"),
-> ("Ryan's Cute", "L", "Jakarta", "12,111", "1981-03-21"),
-> ("Zukarman", "L", "Bekasi", "17,211", "1978-08-10"),
-> ("Yuliawati", "P", "Bogor", "00,000", "1982-06-09")
->;
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
Now we try to enter data in another way again:
mysql> insert into employee
-> Set name = "Rose",
-> Jenkel = "P",
-> City = "Bogor",
-> Postcode = "12345",
-> Tgllahir = "1985-07-07"
->;
Query OK, 1 row affected (0.05 sec)
We have entered some data. How to see the data that we entered earlier?
Viewing data in the table
We can see the data that is on the table by using the command "SELECT". SELECT command is a command that will often be used later. We start with the simplest way first yaa ..
mysql> select * from employee;
+------+--------------+--------+---------+-------- -+------------+
| Paranoid | name | jenkel | town | postcode | tgllahir |
+------+--------------+--------+---------+-------- -+------------+
| 1 | Ahmad Sobari | L | Bandung | 41 011 | 1977-10-02 |
| 2 | Sundariwati | P | Bandung | 40 123 | 1978-11-12 |
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 |
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 |
+------+--------------+--------+---------+-------- -+------------+
6 rows in set (0.02 sec)
The above command displays all existing data in the table of employees, because using the asterisk sign "*" in the SELECT command. What if we only want to display the column names and sex alone?
mysql> select name, jenkel from employees
->;
+--------------+--------+
| Name | jenkel |
+--------------+--------+
| Ahmad Sobari | L |
| Sundariwati | P |
| Ryan Cute | L |
| Zukarman | L |
| Yuliawati | P |
| Roses | P |
+--------------+--------+
6 rows in set (0.00 sec)
If we only want to display the data that female employees only, how? Simply by adding the command "WHERE" on "SELECT"
mysql> select name, jenkel from employees
-> Nowhere jenkel = "P"
->;
+-------------+--------+
| Name | jenkel |
+-------------+--------+
| Sundariwati | P |
| Yuliawati | P |
| Roses | P |
+-------------+--------+
3 rows in set (0.00 sec)
We show the data in order of employee names by adding the command "ORDER BY" on "SELECT":
mysql> select * from employee
-> Order by name;
+------+--------------+--------+---------+-------- -+------------+
| Paranoid | name | jenkel | town | postcode | tgllahir |
+------+--------------+--------+---------+-------- -+------------+
| 1 | Ahmad Sobari | L | Bandung | 41 011 | 1977-10-02 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 |
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 |
| 2 | Sundariwati | P | Bandung | 40 123 | 1978-11-12 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 |
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 |
+------+--------------+--------+---------+-------- -+------------+
6 rows in set (0.00 sec)
Or sorted by city:
mysql> select * from employee
-> Order by city;
+------+--------------+--------+---------+-------- -+------------+
| Paranoid | name | jenkel | town | postcode | tgllahir |
+------+--------------+--------+---------+-------- -+------------+
| 1 | Ahmad Sobari | L | Bandung | 41 011 | 1977-10-02 |
| 2 | Sundariwati | P | Bandung | 40 123 | 1978-11-12 |
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 |
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 |
+------+--------------+--------+---------+-------- -+------------+
6 rows in set (0.00 sec)
Or sorted by date of birth:
mysql> select * from employee
-> Order by tgllahir;
+------+--------------+--------+---------+-------- -+------------+
| Paranoid | name | jenkel | town | postcode | tgllahir |
+------+--------------+--------+---------+-------- -+------------+
| 1 | Ahmad Sobari | L | Bandung | 41 011 | 1977-10-02 |
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 |
| 2 | Sundariwati | P | Bandung | 40 123 | 1978-11-12 |
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 |
+------+--------------+--------+---------+-------- -+------------+
6 rows in set (0.00 sec)
Well if that is now sorted by name, but in reverse order (descending). Simply by adding the command "DESC" in the SELECT:
mysql> select * from employee
-> Order by name DESC;
+------+--------------+--------+---------+-------- -+------------+
| Paranoid | name | jenkel | town | postcode | tgllahir |
+------+--------------+--------+---------+-------- -+------------+
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 |
| 2 | Sundariwati | P | Bandung | 40 123 | 1978-11-12 |
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 |
| 1 | Ahmad Sobari | L | Bandung | 41 011 | 1977-10-02 |
+------+--------------+--------+---------+-------- -+------------+
6 rows in set (0.00 sec)
Can also if the diurutnya is in reverse order of birth date (descending):
mysql> select * from employee
-> Order by tgllahir DESC;
+------+--------------+--------+---------+-------- -+------------+
| Paranoid | name | jenkel | town | postcode | tgllahir |
+------+--------------+--------+---------+-------- -+------------+
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 |
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 |
| 2 | Sundariwati | P | Bandung | 40 123 | 1978-11-12 |
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 |
| 1 | Ahmad Sobari | L | Bandung | 41 011 | 1977-10-02 |
+------+--------------+--------+---------+-------- -+------------+
6 rows in set (0.00 sec)
Apparently we need to add a column to another field, namely salary column. Salary column is a numeric column that holds data the employees' basic salary per month. So, what we need is the type of integer data with the data width of 12 digits. Its application as follows by using the ALTER command.
mysql> alter table employee
-> ADD salary INT (12) NOT NULL default 0
->;
Query OK, 6 rows affected (0.25 sec)
Records: 6 Duplicates: 0 Warnings: 0
We check the table structure first:
mysql> DESCRIBE employees;
+----------+-------------+------+-----+---------+- ---------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+- ---------------+
| Paranoid | int (10) | NO | PRI | NULL | auto_increment |
| Name | varchar (50) | NO | | | |
| Jenkel | char (1) | YES | | NULL | |
| City | varchar (25) | NO | | | |
| Postcode | char (5) | NO | | | |
| Tgllahir | date | YES | | NULL | |
| Salary | int (12) | NO | | 0 | |
+----------+-------------+------+-----+---------+- ---------------+
7 rows in set (0.01 sec)
Yes, salary column has been added to the employee table. Now we will add data to each salary of existing employees. To simplify, we first show all the data available in the employee table:
mysql> select * from employee;
+------+--------------+--------+---------+-------- -+------------+------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+--------------+--------+---------+-------- -+------------+------+
| 1 | Ahmad Sobari | L | Bandung | 41 011 | 1977-10-02 | 0 |
| 2 | Sundariwati | P | Bandung | 40 123 | 1978-11-12 | 0 |
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 | 0 |
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 | 0 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 | 0 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 | 0 |
+------+--------------+--------+---------+-------- -+------------+------+
6 rows in set (0.00 sec)
Updating data in tables
Now we enter the data each employee's salary by using the UPDATE command. We start from Ahmad Sobari, with paranoid = 1:
mysql> update employee
-> Set salary = 1000000
-> Nowhere paranoid = 1;
Query OK, 1 row affected (0:09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Check first result:
mysql> select * from employee
-> Nowhere paranoid = 1;
+------+--------------+--------+---------+-------- -+------------+---------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+--------------+--------+---------+-------- -+------------+---------+
| 1 | Ahmad Sobari | L | Bandung | 41 011 | 1977-10-02 | 1000000 |
+------+--------------+--------+---------+-------- -+------------+---------+
1 row in set (0.00 sec)
We continue with other employees, such as Sundariwati with paranoid = 2, Ryan Cute with paranoid = 3, and so on. Unfortunately, this command can only be done one at a time. So you have to wait to run the command below yaa ..:
mysql> update employee
-> Set salary = 1250000 WHERE paranoid = 2;
Query OK, 1 row affected (0:39 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update employee
-> Set salary = 1500000 WHERE paranoid = 3;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update employee
-> Set salary = 1750000 WHERE paranoid = 4;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update employee
-> Set salary = 2000000 WHERE paranoid = 5;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update employee
-> Set salary = 2250000 WHERE paranoid = 6;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
We check all the results:
mysql> select * from employee;
+------+--------------+--------+---------+-------- -+------------+---------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+--------------+--------+---------+-------- -+------------+---------+
| 1 | Ahmad Sobari | L | Bandung | 41 011 | 1977-10-02 | 1000000 |
| 2 | Sundariwati | P | Bandung | 40 123 | 1978-11-12 | 1250000 |
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 | 1500000 |
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 | 1750000 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 | 2000000 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 | 2250000 |
+------+--------------+--------+---------+-------- -+------------+---------+
6 rows in set (0.00 sec)
Easy enough right? Well, that's the basics of using MySQL command. Now we need more data to our practice. Yes, at least about an 30-data again. But is there an easier way than having to type the data one by one? Kan if we enter one by one, risk factors typographical errors caused by fatigue, and so forth. Fortunately for mass data entry we could use an easier way.
Mass data entry
For mass data entry, we use data that has been written in a plain text file. This file we call tambahdata.txt, and for this example we save in the folder C: \ Data \. You can download (downloading) files tambahdata.txt of this site. Please just click here to download the file tambahdata.txt.
The command we use is "".
mysql> load data local INFILE 'C: \ \ data \ \ tambahdata.txt'
-> Into table employee
-> Fields terminated by ','
-> Lines terminated by '\ n'
->;
Query OK, 36 rows affected, 36 warnings (0:47 sec)
Records: 36 Deleted: 0 Skipped: 0 Warnings: 0
Now we see the result in the employee table:
mysql> select * from employee;
+------+--------------+--------+------------+----- ----+------------+---------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+--------------+--------+------------+----- ----+------------+---------+
| 1 | Ahmad Sobari | L | Bandung | 41 011 | 1977-10-02 | 1000000 |
| 2 | Sundariwati | P | Bandung | 40 123 | 1978-11-12 | 1250000 |
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 | 1500000 |
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 | 1750000 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 | 2000000 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 | 2250000 |
| 7 | Sobari | L | Jakarta | 41 011 | 1976-10-02 | 1100000 |
| 8 | Melia | P | Bandung | 40 123 | 1979-11-12 | 1200000 |
| 9 | Zanda Cute | L | Jakarta | 12 111 | 1980-03-21 | 1300000 |
| 10 | Maman | L | Bekasi | 17 211 | 1977-08-10 | 1400000 |
| 11 | Yenny | P | Bogor | 00 000 | 1985-06-09 | 1150000 |
| 12 | Ross | P | Jakarta | 12 345 | 1987-07-07 | 1350000 |
| 13 | Dada | L | Bandung | 41 011 | 1975-10-02 | 1450000 |
| 14 | Henry | P | Semarang | 40 123 | 1971-11-12 | 1600000 |
| 15 | The Cute | L | Jakarta | 12 111 | 1977-03-21 | 1700000 |
| 16 | Marpaung | L | Surabaya | 17 211 | 1988-08-10 | 1800000 |
| 17 | Yono | P | Bogor | 00 000 | 1989-06-09 | 1900000 |
| 18 | Diana | P | Jakarta | 12 345 | 1980-07-07 | 1650000 |
| 19 | donno | L | Bandung | 41 011 | 1971-10-02 | 1850000 |
| 20 | Queen | P | Yogyakarta | 40 123 | 1972-11-12 | 1950000 |
| 21 | Bambang | L | Jakarta | 12 111 | 1982-03-21 | 2100000 |
| 22 | Dada | L | Surabaya | 17 211 | 1977-08-10 | 2200000 |
| 23 | Yuliawati | P | Bogor | 00 000 | 1974-06-09 | 2300000 |
| 24 | Miranda | P | Bogor | 12 345 | 1980-07-07 | 2400000 |
| 25 | Fertile | L | Bandung | 41 011 | 1977-10-02 | 2150000 |
| 26 | Banowati | P | Malang | 40 123 | 1978-11-12 | 2350000 |
| 27 | Gungun | L | Jakarta | 12 111 | 1981-03-21 | 2450000 |
| 28 | Gunadi | L | Bekasi | 17 211 | 1978-08-10 | 2125000 |
| 29 | Yossy | P | Bogor | 00 000 | 1982-06-09 | 2225000 |
| 30 | Melia | P | Malang | 12 345 | 1981-07-07 | 2325000 |
| 31 | Anwar | L | Purwakarta | 41 011 | 1972-10-02 | 2425000 |
| 32 | Susilowati | P | Bandung | 40 123 | 1973-11-12 | 1125000 |
| 33 | Grace | L | Jakarta | 12 111 | 1977-03-21 | 1225000 |
| 34 | Zamzam | L | Bekasi | 17 211 | 1974-08-10 | 1325000 |
| 35 | Nenny | P | Medan | 00 000 | 1972-06-09 | 1425000 |
| 36 | Mardiatun | P | Bogor | 12 345 | 1975-07-07 | 1625000 |
| 37 | Andie | L | Bandung | 41 011 | 1978-10-02 | 1725000 |
| 38 | Siti | P | Medan | 40 123 | 1988-11-12 | 1825000 |
| 39 | Rohimat | L | Jakarta | 12 111 | 1980-03-21 | 1925000 |
| 40 | Beno | L | Bekasi | 17 211 | 1978-08-10 | 1175000 |
| 41 | Yanti | P | Jakarta | 00 000 | 1981-06-09 | 1275000 |
| 42 | Miranti | P | Medan | 12 345 | 1975-07-07 | 1375000 |
+------+--------------+--------+------------+----- ----+------------+---------+
42 rows in set (0.00 sec)
We already have more data. Suffice it to material subsequent exercises ...
Comparison Operators and Logical Operators
It is time we step into a more exciting game data again by using two operators, namely Comparison Operators and Logical Operators. Both types of these operators will often be used in the process of "query" data.
Comparative Operators
Comparison Operator Description
Larger>
Smaller <= Greater than or equal to> =
Less than or equal to <= Same as = Not equal to <>
Logic Operators
Description Logic Operators
And AND or & &
Or OR or | |
Greater than or equal to NOT or!
Less than or equal to <= Not equal to <>
Here is the implementation of both the operator above:
We show the employee data that the birth date before January 1, 1980, and display the data sorted by name. Quite simply column names, gender and birth date are displayed:
mysql> SELECT name, jenkel, tgllahir
-> From employees
-> Nowhere tgllahir <"1980-01-01" -> Order by name;
+--------------+--------+------------+
| Name | jenkel | tgllahir |
+--------------+--------+------------+
| Ahmad Sobari | L | 1977-10-02 |
| Andie | L | 1978-10-02 |
| Anwar | L | 1972-10-02 |
| Banowati | P | 1978-11-12 |
| Beno | L | 1978-08-10 |
| Dada | L | 1975-10-02 |
| Dada | L | 1977-08-10 |
| Donno | L | 1971-10-02 |
| Gunadi | L | 1978-08-10 |
| Maman | L | 1977-08-10 |
| Mardiatun | P | 1975-07-07 |
| Melia | P | 1979-11-12 |
| Miranti | P | 1975-07-07 |
| Nenny | P | 1972-06-09 |
| Grace | L | 1977-03-21 |
| Queen | P | 1972-11-12 |
| Sobari | L | 1976-10-02 |
| Fertile | L | 1977-10-02 |
| Sundariwati | P | 1978-11-12 |
| Susilowati | P | 1973-11-12 |
| The Cute | L | 1977-03-21 |
| Wawa | P | 1971-11-12 |
| Yuliawati | P | 1974-06-09 |
| Zamzam | L | 1974-08-10 |
| Zukarman | L | 1978-08-10 |
+--------------+--------+------------+
25 rows in set (0.00 sec)
MySQL has a looseness in the writing of the date for the format to follow the rules of "year-month-date". Eg "1971-11-12" can be written 1971-11-12, or 1971 # 11 # 12, or 19,711,112, or 711,112.
We see the example below where the date "1980-01-01" written by 19,800,101
mysql> SELECT name, jenkel, tgllahir
-> From employees
-> Nowhere tgllahir <19800101 -> And jenkel = "L"
-> Order by name;
+--------------+--------+------------+
| Name | jenkel | tgllahir |
+--------------+--------+------------+
| Ahmad Sobari | L | 1977-10-02 |
| Andie | L | 1978-10-02 |
| Anwar | L | 1972-10-02 |
| Beno | L | 1978-08-10 |
| Dada | L | 1975-10-02 |
| Dada | L | 1977-08-10 |
| Donno | L | 1971-10-02 |
| Gunadi | L | 1978-08-10 |
| Maman | L | 1977-08-10 |
| Grace | L | 1977-03-21 |
| Sobari | L | 1976-10-02 |
| Fertile | L | 1977-10-02 |
| The Cute | L | 1977-03-21 |
| Zamzam | L | 1974-08-10 |
| Zukarman | L | 1978-08-10 |
+--------------+--------+------------+
15 rows in set (0.00 sec)
We see the example below when the date "1980-01-01" written in a way 800 101.
mysql> SELECT name, jenkel, tgllahir
-> From employees
-> Nowhere tgllahir <800 101 -> And jenkel = "L"
-> Order by name;
+--------------+--------+------------+
| Name | jenkel | tgllahir |
+--------------+--------+------------+
| Ahmad Sobari | L | 1977-10-02 |
| Andie | L | 1978-10-02 |
| Anwar | L | 1972-10-02 |
| Beno | L | 1978-08-10 |
| Dada | L | 1975-10-02 |
| Dada | L | 1977-08-10 |
| Donno | L | 1971-10-02 |
| Gunadi | L | 1978-08-10 |
| Maman | L | 1977-08-10 |
| Grace | L | 1977-03-21 |
| Sobari | L | 1976-10-02 |
| Fertile | L | 1977-10-02 |
| The Cute | L | 1977-03-21 |
| Zamzam | L | 1974-08-10 |
| Zukarman | L | 1978-08-10 |
+--------------+--------+------------+
15 rows in set (0.00 sec)
We see the example below when the date "1980-01-01" written by "1980 # 01 # 01".
mysql> SELECT name, jenkel, tgllahir
-> From employees
-> Nowhere tgllahir <"1980 # 01 # 01" -> And jenkel = "L"
-> Order by name;
+--------------+--------+------------+
| Name | jenkel | tgllahir |
+--------------+--------+------------+
| Ahmad Sobari | L | 1977-10-02 |
| Andie | L | 1978-10-02 |
| Anwar | L | 1972-10-02 |
| Beno | L | 1978-08-10 |
| Dada | L | 1975-10-02 |
| Dada | L | 1977-08-10 |
| Donno | L | 1971-10-02 |
| Gunadi | L | 1978-08-10 |
| Maman | L | 1977-08-10 |
| Grace | L | 1977-03-21 |
| Sobari | L | 1976-10-02 |
| Fertile | L | 1977-10-02 |
| The Cute | L | 1977-03-21 |
| Zamzam | L | 1974-08-10 |
| Zukarman | L | 1978-08-10 |
+--------------+--------+------------+
15 rows in set (0.00 sec)
We see the example below when the date "1980-01-01", written by way of "1/1/1980".
mysql> SELECT name, jenkel, tgllahir
-> From employees
-> Nowhere tgllahir <"01/01/1980" -> And jenkel = "L"
-> Order by name;
+--------------+--------+------------+
| Name | jenkel | tgllahir |
+--------------+--------+------------+
| Ahmad Sobari | L | 1977-10-02 |
| Andie | L | 1978-10-02 |
| Anwar | L | 1972-10-02 |
| Beno | L | 1978-08-10 |
| Dada | L | 1975-10-02 |
| Dada | L | 1977-08-10 |
| Donno | L | 1971-10-02 |
| Gunadi | L | 1978-08-10 |
| Maman | L | 1977-08-10 |
| Grace | L | 1977-03-21 |
| Sobari | L | 1976-10-02 |
| Fertile | L | 1977-10-02 |
| The Cute | L | 1977-03-21 |
| Zamzam | L | 1974-08-10 |
| Zukarman | L | 1978-08-10 |
+--------------+--------+------------+
15 rows in set (0.00 sec)
Notice all the above results the same way of writing dates although different (but still follow the format "year-month-date").
Now we show that the date of birth of employee data between 1 January 1980 and December 31, 1985, and display the data sorted by name. Quite simply column names, gender and birth date are displayed:
mysql> SELECT name, jenkel, tgllahir
-> From employees
-> Nowhere tgllahir> = "1980-01-01"
-> And tgllahir <= "1985-12-31" -> Order by name;
+------------+--------+------------+
| Name | jenkel | tgllahir |
+------------+--------+------------+
| Bambang | L | 1982-03-21 |
| Diane | P | 1980-07-07 |
| Gungun | L | 1981-03-21 |
| Roses | P | 1985-07-07 |
| Melia | P | 1981-07-07 |
| Miranda | P | 1980-07-07 |
| Rohimat | L | 1980-03-21 |
| Ryan Cute | L | 1981-03-21 |
| Yanti | P | 1981-06-09 |
| Yenny | P | 1985-06-09 |
| Yossy | P | 1982-06-09 |
| Yuliawati | P | 1982-06-09 |
| Zanda Cute | L | 1980-03-21 |
+------------+--------+------------+
13 rows in set (0.00 sec)
Now we show that the date of birth of employee data between 1 January 1980 and December 31, 1985, and display the data sorted by name. Quite simply column names, gender and date of birth only, and only the male sex that is displayed:
mysql> SELECT name, jenkel, tgllahir
-> From employees
-> Nowhere tgllahir> = "1980-01-01"
-> And tgllahir <= "1985-12-31" -> And jenkel = "L"
-> Order by name;
+------------+--------+------------+
| Name | jenkel | tgllahir |
+------------+--------+------------+
| Bambang | L | 1982-03-21 |
| Gungun | L | 1981-03-21 |
| Rohimat | L | 1980-03-21 |
| Ryan Cute | L | 1981-03-21 |
| Zanda Cute | L | 1980-03-21 |
+------------+--------+------------+
5 rows in set (0.00 sec)
How about it, the more interesting right? We proceed to show all employees with at this age. For this problem the solution is quite long. It's okay, we just try it. Here we need the help of some of the functions already provided by MySQL. We'll see ya ..:
mysql> SELECT name, tgllahir,
-> CURRENT_DATE AS NOW,
-> (Year (CURRENT_DATE) - year (tgllahir))
-> - (Right (CURRENT_DATE, 5) From employee;
+--------------+------------+------------+------+
| Name | tgllahir | NOW | AGE |
+--------------+------------+------------+------+
| Ahmad Sobari | 1977-10-02 | 2007-08-30 | 29 |
| Sundariwati | 1978-11-12 | 2007-08-30 | 28 |
| Ryan Cute | 1981-03-21 | 2007-08-30 | 26 |
| Zukarman | 1978-08-10 | 2007-08-30 | 29 |
| Yuliawati | 1982-06-09 | 2007-08-30 | 25 |
| Rose | 1985-07-07 | 2007-08-30 | 22 |
| Sobari | 1976-10-02 | 2007-08-30 | 30 |
| Melia | 1979-11-12 | 2007-08-30 | 27 |
| Zanda Cute | 1980-03-21 | 2007-08-30 | 27 |
| Maman | 1977-08-10 | 2007-08-30 | 30 |
| Yenny | 1985-06-09 | 2007-08-30 | 22 |
| Ross | 1987-07-07 | 2007-08-30 | 20 |
| Dada | 1975-10-02 | 2007-08-30 | 31 |
| Henry | 1971-11-12 | 2007-08-30 | 35 |
| The Cute | 1977-03-21 | 2007-08-30 | 30 |
| Marpaung | 1988-08-10 | 2007-08-30 | 19 |
| Yono | 1989-06-09 | 2007-08-30 | 18 |
| Diane | 1980-07-07 | 2007-08-30 | 27 |
| Donno | 1971-10-02 | 2007-08-30 | 35 |
| Queen | 1972-11-12 | 2007-08-30 | 34 |
| Bambang | 1982-03-21 | 2007-08-30 | 25 |
| Dada | 1977-08-10 | 2007-08-30 | 30 |
| Yuliawati | 1974-06-09 | 2007-08-30 | 33 |
| Miranda | 1980-07-07 | 2007-08-30 | 27 |
| Fertile | 1977-10-02 | 2007-08-30 | 29 |
| Banowati | 1978-11-12 | 2007-08-30 | 28 |
| Gungun | 1981-03-21 | 2007-08-30 | 26 |
| Gunadi | 1978-08-10 | 2007-08-30 | 29 |
| Yossy | 1982-06-09 | 2007-08-30 | 25 |
| Melia | 1981-07-07 | 2007-08-30 | 26 |
| Anwar | 1972-10-02 | 2007-08-30 | 34 |
| Susilowati | 1973-11-12 | 2007-08-30 | 33 |
| Grace | 1977-03-21 | 2007-08-30 | 30 |
| Zamzam | 1974-08-10 | 2007-08-30 | 33 |
| Nenny | 1972-06-09 | 2007-08-30 | 35 |
| Mardiatun | 1975-07-07 | 2007-08-30 | 32 |
| Andi | 1978-10-02 | 2007-08-30 | 28 |
| Siti | 1988-11-12 | 2007-08-30 | 18 |
| Rohimat | 1980-03-21 | 2007-08-30 | 27 |
| Beno | 1978-08-10 | 2007-08-30 | 29 |
| Yanti | 1981-06-09 | 2007-08-30 | 26 |
| Miranti | 1975-07-07 | 2007-08-30 | 32 |
+--------------+------------+------------+------+
42 rows in set (0.00 sec)
We continue by showing the data of employees whose age is equal to or below 25 years. Well how?:
mysql> SELECT name, tgllahir,
-> CURRENT_DATE AS NOW,
-> (Year (CURRENT_DATE) - year (tgllahir))
-> - (Right (CURRENT_DATE, 5) AS AGE
-> From employees
-> WHERE ((year (CURRENT_DATE) - year (tgllahir))
-> - (Right (CURRENT_DATE, 5) <= 25; +-----------+------------+------------+------+ | Name | tgllahir | NOW | AGE | +-----------+------------+------------+------+ | Yuliawati | 1982-06-09 | 2007-08-30 | 25 | | Rose | 1985-07-07 | 2007-08-30 | 22 | | Yenny | 1985-06-09 | 2007-08-30 | 22 | | Ross | 1987-07-07 | 2007-08-30 | 20 | | Marpaung | 1988-08-10 | 2007-08-30 | 19 | | Yono | 1989-06-09 | 2007-08-30 | 18 | | Bambang | 1982-03-21 | 2007-08-30 | 25 | | Yossy | 1982-06-09 | 2007-08-30 | 25 | | Siti | 1988-11-12 | 2007-08-30 | 18 | +-----------+------------+------------+------+ 9 rows in set (0.00 sec) Long enough so ordered. Here we use the CURRENT_DATE function that takes the value of the current date on your computer system. YEAR is a function that takes the value of the year. Then the U.S. is short of Alias, which seemed to give another name (alias name) in the column or the result of a process. While the RIGHT is a function that takes the value of all the characters from the right side of a target. Eg RIGHT ("TOMATO", 3) it will generate the character "MAT". Whether we continue this tutorial with other commands. Mari ... We will show employees that the city of his birth at the "Bandung": mysql> select * from employee
-> WHERE city = "Bandung";
+------+--------------+--------+---------+-------- -+------------+---------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+--------------+--------+---------+-------- -+------------+---------+
| 1 | Ahmad Sobari | L | Bandung | 41 011 | 1977-10-02 | 1000000 |
| 2 | Sundariwati | P | Bandung | 40 123 | 1978-11-12 | 1250000 |
| 8 | Melia | P | Bandung | 40 123 | 1979-11-12 | 1200000 |
| 13 | Dada | L | Bandung | 41 011 | 1975-10-02 | 1450000 |
| 19 | donno | L | Bandung | 41 011 | 1971-10-02 | 1850000 |
| 25 | Fertile | L | Bandung | 41 011 | 1977-10-02 | 2150000 |
| 32 | Susilowati | P | Bandung | 40 123 | 1973-11-12 | 1125000 |
| 37 | Andie | L | Bandung | 41 011 | 1978-10-02 | 1725000 |
+------+--------------+--------+---------+-------- -+------------+---------+
8 rows in set (0.03 sec)
We show the employee that her hometown is not in Bandung:
mysql> select * from employee
-> Nowhere town! = "Bandung";
+------+------------+--------+------------+------- --+------------+---------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+------------+--------+------------+------- --+------------+---------+
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 | 1500000 |
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 | 1750000 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 | 2000000 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 | 2250000 |
| 7 | Sobari | L | Jakarta | 41 011 | 1976-10-02 | 1100000 |
| 9 | Zanda Cute | L | Jakarta | 12 111 | 1980-03-21 | 1300000 |
| 10 | Maman | L | Bekasi | 17 211 | 1977-08-10 | 1400000 |
| 11 | Yenny | P | Bogor | 00 000 | 1985-06-09 | 1150000 |
| 12 | Ross | P | Jakarta | 12 345 | 1987-07-07 | 1350000 |
| 14 | Henry | P | Semarang | 40 123 | 1971-11-12 | 1600000 |
| 15 | The Cute | L | Jakarta | 12 111 | 1977-03-21 | 1700000 |
| 16 | Marpaung | L | Surabaya | 17 211 | 1988-08-10 | 1800000 |
| 17 | Yono | P | Bogor | 00 000 | 1989-06-09 | 1900000 |
| 18 | Diana | P | Jakarta | 12 345 | 1980-07-07 | 1650000 |
| 20 | Queen | P | Yogyakarta | 40 123 | 1972-11-12 | 1950000 |
| 21 | Bambang | L | Jakarta | 12 111 | 1982-03-21 | 2100000 |
| 22 | Dada | L | Surabaya | 17 211 | 1977-08-10 | 2200000 |
| 23 | Yuliawati | P | Bogor | 00 000 | 1974-06-09 | 2300000 |
| 24 | Miranda | P | Bogor | 12 345 | 1980-07-07 | 2400000 |
| 26 | Banowati | P | Malang | 40 123 | 1978-11-12 | 2350000 |
| 27 | Gungun | L | Jakarta | 12 111 | 1981-03-21 | 2450000 |
| 28 | Gunadi | L | Bekasi | 17 211 | 1978-08-10 | 2125000 |
| 29 | Yossy | P | Bogor | 00 000 | 1982-06-09 | 2225000 |
| 30 | Melia | P | Malang | 12 345 | 1981-07-07 | 2325000 |
| 31 | Anwar | L | Purwakarta | 41 011 | 1972-10-02 | 2425000 |
| 33 | Grace | L | Jakarta | 12 111 | 1977-03-21 | 1225000 |
| 34 | Zamzam | L | Bekasi | 17 211 | 1974-08-10 | 1325000 |
| 35 | Nenny | P | Medan | 00 000 | 1972-06-09 | 1425000 |
| 36 | Mardiatun | P | Bogor | 12 345 | 1975-07-07 | 1625000 |
| 38 | Siti | P | Medan | 40 123 | 1988-11-12 | 1825000 |
| 39 | Rohimat | L | Jakarta | 12 111 | 1980-03-21 | 1925000 |
| 40 | Beno | L | Bekasi | 17 211 | 1978-08-10 | 1175000 |
| 41 | Yanti | P | Jakarta | 00 000 | 1981-06-09 | 1275000 |
| 42 | Miranti | P | Medan | 12 345 | 1975-07-07 | 1375000 |
+------+------------+--------+------------+------- --+------------+---------+
34 rows in set (0.00 sec)
The above command can also use the sign "<>', and the result remained the same as above:
mysql> select * from employee
-> Nowhere city <> "bandung";
+------+------------+--------+------------+------- --+------------+---------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+------------+--------+------------+------- --+------------+---------+
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 | 1500000 |
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 | 1750000 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 | 2000000 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 | 2250000 |
| 7 | Sobari | L | Jakarta | 41 011 | 1976-10-02 | 1100000 |
| 9 | Zanda Cute | L | Jakarta | 12 111 | 1980-03-21 | 1300000 |
| 10 | Maman | L | Bekasi | 17 211 | 1977-08-10 | 1400000 |
| 11 | Yenny | P | Bogor | 00 000 | 1985-06-09 | 1150000 |
| 12 | Ross | P | Jakarta | 12 345 | 1987-07-07 | 1350000 |
| 14 | Henry | P | Semarang | 40 123 | 1971-11-12 | 1600000 |
| 15 | The Cute | L | Jakarta | 12 111 | 1977-03-21 | 1700000 |
| 16 | Marpaung | L | Surabaya | 17 211 | 1988-08-10 | 1800000 |
| 17 | Yono | P | Bogor | 00 000 | 1989-06-09 | 1900000 |
| 18 | Diana | P | Jakarta | 12 345 | 1980-07-07 | 1650000 |
| 20 | Queen | P | Yogyakarta | 40 123 | 1972-11-12 | 1950000 |
| 21 | Bambang | L | Jakarta | 12 111 | 1982-03-21 | 2100000 |
| 22 | Dada | L | Surabaya | 17 211 | 1977-08-10 | 2200000 |
| 23 | Yuliawati | P | Bogor | 00 000 | 1974-06-09 | 2300000 |
| 24 | Miranda | P | Bogor | 12 345 | 1980-07-07 | 2400000 |
| 26 | Banowati | P | Malang | 40 123 | 1978-11-12 | 2350000 |
| 27 | Gungun | L | Jakarta | 12 111 | 1981-03-21 | 2450000 |
| 28 | Gunadi | L | Bekasi | 17 211 | 1978-08-10 | 2125000 |
| 29 | Yossy | P | Bogor | 00 000 | 1982-06-09 | 2225000 |
| 30 | Melia | P | Malang | 12 345 | 1981-07-07 | 2325000 |
| 31 | Anwar | L | Purwakarta | 41 011 | 1972-10-02 | 2425000 |
| 33 | Grace | L | Jakarta | 12 111 | 1977-03-21 | 1225000 |
| 34 | Zamzam | L | Bekasi | 17 211 | 1974-08-10 | 1325000 |
| 35 | Nenny | P | Medan | 00 000 | 1972-06-09 | 1425000 |
| 36 | Mardiatun | P | Bogor | 12 345 | 1975-07-07 | 1625000 |
| 38 | Siti | P | Medan | 40 123 | 1988-11-12 | 1825000 |
| 39 | Rohimat | L | Jakarta | 12 111 | 1980-03-21 | 1925000 |
| 40 | Beno | L | Bekasi | 17 211 | 1978-08-10 | 1175000 |
| 41 | Yanti | P | Jakarta | 00 000 | 1981-06-09 | 1275000 |
| 42 | Miranti | P | Medan | 12 345 | 1975-07-07 | 1375000 |
+------+------------+--------+------------+------- --+------------+---------+
34 rows in set (0.00 sec)
Now we show the employee with the city of birth is not in Bandung, Jakarta and Bekasi. Display data sorted by city name. What form will the command?
mysql> select * from employee
-> Nowhere city <> "bandung"
-> And city <> "Jakarta"
-> And city <> "Bekasi"
-> Order by city;
+------+-----------+--------+------------+-------- -+------------+---------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+-----------+--------+------------+-------- -+------------+---------+
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 | 2000000 |
| 36 | Mardiatun | P | Bogor | 12 345 | 1975-07-07 | 1625000 |
| 29 | Yossy | P | Bogor | 00 000 | 1982-06-09 | 2225000 |
| 24 | Miranda | P | Bogor | 12 345 | 1980-07-07 | 2400000 |
| 23 | Yuliawati | P | Bogor | 00 000 | 1974-06-09 | 2300000 |
| 17 | Yono | P | Bogor | 00 000 | 1989-06-09 | 1900000 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 | 2250000 |
| 11 | Yenny | P | Bogor | 00 000 | 1985-06-09 | 1150000 |
| 30 | Melia | P | Malang | 12 345 | 1981-07-07 | 2325000 |
| 26 | Banowati | P | Malang | 40 123 | 1978-11-12 | 2350000 |
| 38 | Siti | P | Medan | 40 123 | 1988-11-12 | 1825000 |
| 35 | Nenny | P | Medan | 00 000 | 1972-06-09 | 1425000 |
| 42 | Miranti | P | Medan | 12 345 | 1975-07-07 | 1375000 |
| 31 | Anwar | L | Purwakarta | 41 011 | 1972-10-02 | 2425000 |
| 14 | Henry | P | Semarang | 40 123 | 1971-11-12 | 1600000 |
| 16 | Marpaung | L | Surabaya | 17 211 | 1988-08-10 | 1800000 |
| 22 | Dada | L | Surabaya | 17 211 | 1977-08-10 | 2200000 |
| 20 | Queen | P | Yogyakarta | 40 123 | 1972-11-12 | 1950000 |
+------+-----------+--------+------------+-------- -+------------+---------+
18 rows in set (0.00 sec)
Almost similar to the one above, but in addition sorted by city, name of employee, too sorted. We try with the command below:
mysql> select * from employee
-> Nowhere city <> "bandung"
-> And city <> "Jakarta"
-> And city <> "Bekasi"
-> Order by city and name;
+------+-----------+--------+------------+-------- -+------------+---------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+-----------+--------+------------+-------- -+------------+---------+
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 | 2000000 |
| 38 | Siti | P | Medan | 40 123 | 1988-11-12 | 1825000 |
| 36 | Mardiatun | P | Bogor | 12 345 | 1975-07-07 | 1625000 |
| 35 | Nenny | P | Medan | 00 000 | 1972-06-09 | 1425000 |
| 31 | Anwar | L | Purwakarta | 41 011 | 1972-10-02 | 2425000 |
| 30 | Melia | P | Malang | 12 345 | 1981-07-07 | 2325000 |
| 29 | Yossy | P | Bogor | 00 000 | 1982-06-09 | 2225000 |
| 26 | Banowati | P | Malang | 40 123 | 1978-11-12 | 2350000 |
| 24 | Miranda | P | Bogor | 12 345 | 1980-07-07 | 2400000 |
| 23 | Yuliawati | P | Bogor | 00 000 | 1974-06-09 | 2300000 |
| 22 | Dada | L | Surabaya | 17 211 | 1977-08-10 | 2200000 |
| 20 | Queen | P | Yogyakarta | 40 123 | 1972-11-12 | 1950000 |
| 17 | Yono | P | Bogor | 00 000 | 1989-06-09 | 1900000 |
| 16 | Marpaung | L | Surabaya | 17 211 | 1988-08-10 | 1800000 |
| 14 | Henry | P | Semarang | 40 123 | 1971-11-12 | 1600000 |
| 11 | Yenny | P | Bogor | 00 000 | 1985-06-09 | 1150000 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 | 2250000 |
| 42 | Miranti | P | Medan | 12 345 | 1975-07-07 | 1375000 |
+------+-----------+--------+------------+-------- -+------------+---------+
18 rows in set (0.00 sec)
Take a look at the results. Is this the result we want? Keliatannya something was wrong ... We try again to add parentheses (and) on the command "ORDER BY", who knows that success:
mysql> select * from employee
-> Nowhere city <> "bandung"
-> And city <> "Jakarta"
-> And city <> "Bekasi"
-> Order by (city and name);
+------+-----------+--------+------------+-------- -+------------+---------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+-----------+--------+------------+-------- -+------------+---------+
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 | 2000000 |
| 38 | Siti | P | Medan | 40 123 | 1988-11-12 | 1825000 |
| 36 | Mardiatun | P | Bogor | 12 345 | 1975-07-07 | 1625000 |
| 35 | Nenny | P | Medan | 00 000 | 1972-06-09 | 1425000 |
| 31 | Anwar | L | Purwakarta | 41 011 | 1972-10-02 | 2425000 |
| 30 | Melia | P | Malang | 12 345 | 1981-07-07 | 2325000 |
| 29 | Yossy | P | Bogor | 00 000 | 1982-06-09 | 2225000 |
| 26 | Banowati | P | Malang | 40 123 | 1978-11-12 | 2350000 |
| 24 | Miranda | P | Bogor | 12 345 | 1980-07-07 | 2400000 |
| 23 | Yuliawati | P | Bogor | 00 000 | 1974-06-09 | 2300000 |
| 22 | Dada | L | Surabaya | 17 211 | 1977-08-10 | 2200000 |
| 20 | Queen | P | Yogyakarta | 40 123 | 1972-11-12 | 1950000 |
| 17 | Yono | P | Bogor | 00 000 | 1989-06-09 | 1900000 |
| 16 | Marpaung | L | Surabaya | 17 211 | 1988-08-10 | 1800000 |
| 14 | Henry | P | Semarang | 40 123 | 1971-11-12 | 1600000 |
| 11 | Yenny | P | Bogor | 00 000 | 1985-06-09 | 1150000 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 | 2250000 |
| 42 | Miranti | P | Medan | 12 345 | 1975-07-07 | 1375000 |
+------+-----------+--------+------------+-------- -+------------+---------+
18 rows in set (0.00 sec)
Hm, still not right, too. We'll try again:
mysql> select * from employee
-> Nowhere city <> "bandung"
-> And city <> "Jakarta"
-> And city <> "Bekasi"
-> Order by city, name;
+------+-----------+--------+------------+-------- -+------------+---------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+-----------+--------+------------+-------- -+------------+---------+
| 36 | Mardiatun | P | Bogor | 12 345 | 1975-07-07 | 1625000 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 | 2250000 |
| 24 | Miranda | P | Bogor | 12 345 | 1980-07-07 | 2400000 |
| 11 | Yenny | P | Bogor | 00 000 | 1985-06-09 | 1150000 |
| 17 | Yono | P | Bogor | 00 000 | 1989-06-09 | 1900000 |
| 29 | Yossy | P | Bogor | 00 000 | 1982-06-09 | 2225000 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 | 2000000 |
| 23 | Yuliawati | P | Bogor | 00 000 | 1974-06-09 | 2300000 |
| 26 | Banowati | P | Malang | 40 123 | 1978-11-12 | 2350000 |
| 30 | Melia | P | Malang | 12 345 | 1981-07-07 | 2325000 |
| 42 | Miranti | P | Medan | 12 345 | 1975-07-07 | 1375000 |
| 35 | Nenny | P | Medan | 00 000 | 1972-06-09 | 1425000 |
| 38 | Siti | P | Medan | 40 123 | 1988-11-12 | 1825000 |
| 31 | Anwar | L | Purwakarta | 41 011 | 1972-10-02 | 2425000 |
| 14 | Henry | P | Semarang | 40 123 | 1971-11-12 | 1600000 |
| 22 | Dada | L | Surabaya | 17 211 | 1977-08-10 | 2200000 |
| 16 | Marpaung | L | Surabaya | 17 211 | 1988-08-10 | 1800000 |
| 20 | Queen | P | Yogyakarta | 40 123 | 1972-11-12 | 1950000 |
+------+-----------+--------+------------+-------- -+------------+---------+
18 rows in set (0.00 sec)
Well, it turns out it's only successful. Try it once again consider his request: "show the employee with the city of birth is not in Bandung, Jakarta and Bekasi. Display data sorted by the name of the city and also the name of the employee." Although there is the word "and" here, but not solely that we can use the logical AND operator. It takes foresight and trial and error in this logic game.
Basic Statistical Functions
Now anyone whose salary is between USD 1,500,000 and USD 2.5 million? Display data sorted by employee name and salary columns
mysql> select * from employee
-> WHERE salary> = 1500000
-> And salary <= 2500000 -> Order by salary, name;
+------+------------+--------+------------+------- --+------------+---------+
| Paranoid | name | jenkel | town | postcode | tgllahir | salary |
+------+------------+--------+------------+------- --+------------+---------+
| 3 | Ryan Cute | L | Jakarta | 12 111 | 1981-03-21 | 1500000 |
| 14 | Henry | P | Semarang | 40 123 | 1971-11-12 | 1600000 |
| 36 | Mardiatun | P | Bogor | 12 345 | 1975-07-07 | 1625000 |
| 18 | Diana | P | Jakarta | 12 345 | 1980-07-07 | 1650000 |
| 15 | The Cute | L | Jakarta | 12 111 | 1977-03-21 | 1700000 |
| 37 | Andie | L | Bandung | 41 011 | 1978-10-02 | 1725000 |
| 4 | Zukarman | L | Bekasi | 17 211 | 1978-08-10 | 1750000 |
| 16 | Marpaung | L | Surabaya | 17 211 | 1988-08-10 | 1800000 |
| 38 | Siti | P | Medan | 40 123 | 1988-11-12 | 1825000 |
| 19 | donno | L | Bandung | 41 011 | 1971-10-02 | 1850000 |
| 17 | Yono | P | Bogor | 00 000 | 1989-06-09 | 1900000 |
| 39 | Rohimat | L | Jakarta | 12 111 | 1980-03-21 | 1925000 |
| 20 | Queen | P | Yogyakarta | 40 123 | 1972-11-12 | 1950000 |
| 5 | Yuliawati | P | Bogor | 00 000 | 1982-06-09 | 2000000 |
| 21 | Bambang | L | Jakarta | 12 111 | 1982-03-21 | 2100000 |
| 28 | Gunadi | L | Bekasi | 17 211 | 1978-08-10 | 2125000 |
| 25 | Fertile | L | Bandung | 41 011 | 1977-10-02 | 2150000 |
| 22 | Dada | L | Surabaya | 17 211 | 1977-08-10 | 2200000 |
| 29 | Yossy | P | Bogor | 00 000 | 1982-06-09 | 2225000 |
| 6 | Roses | P | Bogor | 12 345 | 1985-07-07 | 2250000 |
| 23 | Yuliawati | P | Bogor | 00 000 | 1974-06-09 | 2300000 |
| 30 | Melia | P | Malang | 12 345 | 1981-07-07 | 2325000 |
| 26 | Banowati | P | Malang | 40 123 | 1978-11-12 | 2350000 |
| 24 | Miranda | P | Bogor | 12 345 | 1980-07-07 | 2400000 |
| 31 | Anwar | L | Purwakarta | 41 011 | 1972-10-02 | 2425000 |
| 27 | Gungun | L | Jakarta | 12 111 | 1981-03-21 | 2450000 |
+------+------------+--------+------------+------- --+------------+---------+
26 rows in set (0.00 sec)
Now how many employees whose salary is under $ 2 million?
mysql> select count (*) from employee
-> WHERE salary <2000000; +----------+ | Count (*) | +----------+ | 29 | +----------+ 1 row in set (0.01 sec) What is the average salary of employees? mysql> select avg (salary) from employee;
+--------------+
| Avg (salary) |
+--------------+
| 1719642.8571 |
+--------------+
1 row in set (12:41 sec)
What is the value of the largest salary?
mysql> select max (salary) from employee;
+-----------+
| Max (salary) |
+-----------+
| 2450000 |
+-----------+
1 row in set (0.00 sec)
What is the value of the smallest salary?
mysql> select min (salary) from employee;
+-----------+
| Min (salary) |
+-----------+
| 1000000 |
+-----------+
1 row in set (0.00 sec)
And what is the total salaries of all employees?
mysql> select sum (salary) from employee;
+-----------+
| Sum (salary) |
+-----------+
| 72225000 |
+-----------+
1 row in set (0.00 sec)
Operator Precedence
Operator precedence is the level of the hierarchy in the process a series of operators.
Levels of hierarchy Type Operators
Highest BINARY
NOT!
- (Unary minus)
* /%
+ -
<<>>
&
|
<<= = <=>! = <>> => IS IN LIKE regexp RLIKE
BETWEEN
AND & &
Best Low OR | |
The more upward position of the operator, the higher the level of the hierarchy of operators. Vice versa, the lower the position the more weak hierarchies.
For an equally strong operator, eg + and - combined with the operator * /%, then the hierarchy will be determined depending on the position where the most left / earliest found. And fortunately the position of this hierarchy can be changed with the help of parentheses "(" and ")". Now we see its application.
mysql> select 10 +15-11 * 2, (10 +15-11) * 2,
-> 2 * 6-5, 2 * (6-5);
+------------+--------------+-------+---------+
| 10 +15-11 * 2 | (10 +15-11) * 2 | 2 * 6-5 | 2 * (6-5) |
+------------+--------------+-------+---------+
| 3 | 28 | 7 | 2 |
+------------+--------------+-------+---------+
1 row in set (0.00 sec)
You may notice that, although the same numbers and operators, but the results can be different. And that's because of the role of the parentheses "(" and ")" which will change the map position of the hierarchy of operators ....
The operator LIKE, NOT LIKE, regexp
The operator LIKE, NOT LIKE, regexp will be many we use primarily in the operation character.
Now we're trying to use the LIKE operator. LIKE operator is used to find data that "resemble" or "almost equal" with certain criteria. Usually to find the data string / text. The symbol "%" is used to help implement the LIKE operator. Position "%" very influential in determining the criteria. We live with his examples more clearly yes let the user ...
Show data of employees whose names beginning with the letter "a": (note the position of the symbol percent "%")
mysql> select paranoid, name
-> From employees
-> WHERE name LIKE 'a%';
+------+--------------+
| Paranoid | name |
+------+--------------+
| 1 | Ahmad Sobari |
| 31 | Anwar |
| 37 | Andie |
+------+--------------+
3 rows in set (0.00 sec)
Show data of employees whose names beginning with the letter "d":
mysql> select paranoid, name
-> From employees
-> WHERE name LIKE 'd% ";
+------+--------+
| Paranoid | name |
+------+--------+
| 13 | Dada |
| 18 | Diane |
| 19 | donno |
| 22 | Dada |
+------+--------+
4 rows in set (0.00 sec)
Show data of employees whose names end with the letter "i". Note the position of the sign writing "%".:
mysql> select paranoid, name
-> From employees
-> WHERE name LIKE "% i";
+------+--------------+
| Paranoid | name |
+------+--------------+
| 1 | Ahmad Sobari |
| 2 | Sundariwati |
| 5 | Yuliawati |
| 7 | Sobari |
| 23 | Yuliawati |
| 26 | Banowati |
| 28 | Gunadi |
| 32 | Susilowati |
| 38 | Siti |
| 41 | Yanti |
| 42 | Miranti |
+------+--------------+
11 rows in set (0.00 sec)
Show data of employees whose names end in "pendonor":
mysql> select paranoid, name
-> From employees
-> WHERE name LIKE '% pendonor ";
+------+-------------+
| Paranoid | name |
+------+-------------+
| 2 | Sundariwati |
| 5 | Yuliawati |
| 23 | Yuliawati |
| 26 | Banowati |
| 32 | Susilowati |
+------+-------------+
5 rows in set (0.00 sec)
How do I get the LIKE operator to distinguish between upper and lower ... Quite simply, simply by adding the word just after the command LIKE BINARY (so he ordered to be LIKE BINARY). We see, for example ...
mysql> select paranoid, name
-> From employees
-> WHERE name LIKE BINARY 'a%';
Empty set (12:34 sec)
Why are the results into "Empty set"? We try to change the command becomes:
mysql> select paranoid, name
-> From employees
-> WHERE name LIKE BINARY 'A%';
+------+--------------+
| Paranoid | name |
+------+--------------+
| 1 | Ahmad Sobari |
| 31 | Anwar |
| 37 | Andie |
+------+--------------+
3 rows in set (0.00 sec)
Yes by using the LIKE BINARY, writing the letter "a" will distinguish it means by "A". Obviously right?
Now what if we want to display the data, the criterion is not the beginning or end of sentences, but in between a word / sentence? For example how many names of employees who have the word "Honor"?
mysql> select paranoid, name
-> From employees
-> WHERE name LIKE BINARY '% lia%';
+------+-----------+
| Paranoid | name |
+------+-----------+
| 5 | Yuliawati |
| 8 | Melia |
| 23 | Yuliawati |
| 30 | Melia |
+------+-----------+
4 rows in set (0.00 sec)
Or has the word "On" in their name?
mysql> select paranoid, name
-> From employees
-> WHERE name LIKE BINARY '% In% ";
+------+------+
| Paranoid | name |
+------+------+
| 18 | Diane |
+------+------+
1 row in set (0.00 sec)
Operator regexp (short for regular expressions) is another form of the LIKE operator, with more enhanced functions. Regexp operators usually accompanied also by certain symbols in performing their duties, such as:
Symbol Description
. One dot (.) To represent a single character
[?] To represent a character or a specified range.
^ For the initial position of a prescribed criteria
$ To the end of a specified criteria
We live it in for example. Display employee names beginning with the letter 'a':
mysql> select paranoid, name
-> From employees
-> Nowhere name regexp "^ a";
+------+--------------+
| Paranoid | name |
+------+--------------+
| 1 | Ahmad Sobari |
| 31 | Anwar |
| 37 | Andie |
+------+--------------+
3 rows in set (0.00 sec)
Show data of employees whose names beginning with the letter "d".
mysql> select paranoid, name
-> From employees
-> Nowhere name regexp "^ d";
+------+--------+
| Paranoid | name |
+------+--------+
| 13 | Dada |
| 18 | Diane |
| 19 | donno |
| 22 | Dada |
+------+--------+
4 rows in set (0.00 sec)
Display employee names beginning with the letter 'a' to letter 'd':
mysql> select paranoid, name
-> From employees
-> Nowhere name regexp "^ [a-d]"
-> Order by name;
+------+--------------+
| Paranoid | name |
+------+--------------+
| 1 | Ahmad Sobari |
| 37 | Andie |
| 31 | Anwar |
| 21 | Bambang |
| 26 | Banowati |
| 40 | Beno |
| 13 | Dada |
| 22 | Dada |
| 18 | Diane |
| 19 | donno |
+------+--------------+
10 rows in set (0.00 sec)
Show data of employees whose names end with the letter "i":
mysql> select paranoid, name
-> From employees
-> Nowhere regexp name "i $"
-> Order by name;
+------+--------------+
| Paranoid | name |
+------+--------------+
| 1 | Ahmad Sobari |
| 26 | Banowati |
| 28 | Gunadi |
| 42 | Miranti |
| 38 | Siti |
| 7 | Sobari |
| 2 | Sundariwati |
| 32 | Susilowati |
| 41 | Yanti |
| 5 | Yuliawati |
| 23 | Yuliawati |
+------+--------------+
11 rows in set (0.00 sec)
Show data of employees whose names end in "pendonor":
mysql> select paranoid, name
-> From employees
-> Nowhere name regexp "pendonor $"
-> Order by name;
+------+-------------+
| Paranoid | name |
+------+-------------+
| 26 | Banowati |
| 2 | Sundariwati |
| 32 | Susilowati |
| 5 | Yuliawati |
| 23 | Yuliawati |
+------+-------------+
5 rows in set (0.00 sec)
Show names of employees that are 10 characters:
mysql> select paranoid, name
-> From employees
-> Nowhere "^..........$" regexp name;
+------+------------+
| Paranoid | name |
+------+------------+
| 3 | Ryan Cute |
| 9 | Zanda Cute |
| 32 | Susilowati |
+------+------------+
3 rows in set (0.00 sec)
Or the command above could also be written by:
mysql> select paranoid, name
-> From employees
-> Nowhere name regexp "^. {10} $";
+------+------------+
| Paranoid | name |
+------+------------+
| 3 | Ryan Cute |
| 9 | Zanda Cute |
| 32 | Susilowati |
+------+------------+
3 rows in set (0.00 sec)
Well, that's about the basics of MySQL. This is just the introductory tutorial. We will meet again with the next tutorial, the basics of database relations. Then proceed with the application of a simple tutorial "relation database with 2 tables."
