Dari IgosCenter
Pendahuluan
MySQL (My Structured Query Language) adalah sistem manajemen relasi database (RDBMS) yang berjalan di banyak platform. Program ini berjalan sebagai server menyediakan multi-user mengakses ke sejumlah database.
Source code tersedia di bawah ketentuan GNU General Public License, serta dalam berbagai perjanjian eksklusif. MySQL dimiliki dan disponsori oleh sebuah perrusahaan, MySQL AB di Swedia, yang kini merupakan anak perusahaan dari Sun Microsystems, yang memegang hak cipta untuk sebagian besar source code-nya.
MySQL umumnya digunakan oleh perangkat lunak bebas yang memerlukan fitur penuh sistem manajemen database, seperti Wordpress, phpBB dan perangkat lunak lain yang dibangun pada perangkat lunak LAMP. Ia juga digunakan dalam skala sangat tinggi World Wide Web, termasuk produk-produk Google dan Facebook. [1]
Instalasi MySQL
Instalasi MySQL di Ubuntu [2] relatif sederhana hanya menggunakan perintah,
# apt-get install mysql-server libmysqlclient15-dev mysql-client-5.0
Untuk menjalankan MySQL Server cukup menggunakan perintah
# /etc/init.d/mysql restart
Ada baiknya menambahkan beberapa file tambahan untuk memudahkan proses pembuatan Content Management System (CMS) di Web Server dengan cara menginstalasi aplikasi PHP, adodb, GD, PEAR melalui perintah,
# apt-get install libphp-adodb libgd2-xpm libgd2-xpm-dev php5-mysql php5-gd # apt-get install php5-curl php-image-graph php-image-canvas php-pear
Mungkin yang agak pusing kepala adalah menset MySQL Server. Sebetulnya tidak banyak yang harus dikerjakan untuk mensetup database di MySQL. Biasanya pertama kali kita perlu menset password root dari MySQL database. Setting password root MySQL dapat dilakukan dengan cara masuk ke aplikasi administrator MySQL dengan perintah,
# mysql
Kemudian set PASSWORD root agar sesuai 'password' melalui perintah dengan,
mysql> SET PASSWORD FOR root@localhost=PASSWORD('password');
Beberapa langkah selanjutnya yang sering digunakan untuk menset user, password user, database biasanya saya menggunakan perintah berikut,
# mysql -u root -p
Enter password:
mysql> create database moodle;
mysql> grant INSERT,SELECT on root.* to user@localhost;
mysql> SET PASSWORD FOR user@localhost=PASSWORD('passworduser');
mysql> grant CREATE, INSERT, SELECT, DELETE, UPDATE on databasekita.* to user@localhost;
mysql> grant CREATE, INSERT, SELECT, DELETE, UPDATE on databasekita.* to user;
mysql> exit
Perintah MySQL
Start Client & Log In
mysql -u root -p then enter your password when prompted. You will then see the following output:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.51b Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql -u root mysql
update user set Password=password('NewPw') where User='root';
flush privileges;
Membuat Database sederhana dan Melihat Strukturnya
mysql 5.0.51b> create database database01; Database "database01" created.
mysql 5.0.51b> use database01 Database changed
Beri Akses ke Database
grant CREATE, INSERT, SELECT, DELETE, UPDATE on namadatabase.* to username@localhost IDENTIFIED BY 'passworduser';
Membuat tabel
mysql 5.0.51b> create table table01 (field01 integer, field02 char(10)); Query OK, 0 rows affected (0.00 sec)
!Enclose entire list of field names between one pair of parentheses. !Commas are used between each field. iA space may be used after the comma between fields. !A comma is not used after last field. !This, and all SQL statements, are concluded by a semicolon ";".
Daftar tabel
mysql 5.0.51b> show tables; +----------------------+ | Tables in database01 | +----------------------+ | table01 | | table02 | +----------------------+
Daftar fields dalam tabel
mysql 5.0.51b> show columns from table01; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | field01 | int(11) | YES | | | | | field02 | char(10) | YES | | | | +---------+----------+------+-----+---------+-------+
Masukkan record
mysql 5.0.51b> insert into table01 (field01, field02) values (1, 'first'); Query OK, 1 row affected (0.00 sec)
!Enclose entire list of field names between one pair of parentheses. !Enclose the values to be inserted between another pair of parentheses. !Commas are used between each field and between each value. iA space may be used after the comma between fields.
Daftar semua records dalam tabel
mysql 5.0.51b> select * from table01;
+---------+---------+ | field01 | field02 | +---------+---------+ | 1 | first | +---------+---------+
Menambah fields
mysql 5.0.51b> alter table table01 add column field03 char(20); Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0
mysql 5.0.51b> alter table table01 add column field04 date, add column field05 time; Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0
!The "add column" must be restated for each column. !Commas are used between each add column statement. iA space may be used after these commas.
mysql 5.0.51b> select * from table01;
+---------+---------+---------+---------+---------+ | field01 | field02 | field03 | field04 | field05 | +---------+---------+---------+---------+---------+ | 1 | first | NULL | NULL | NULL | +---------+---------+---------+---------+---------+
Multi-line Command Entry
Single Line Entry
mysql 5.0.51b> create table table33 (field01 integer,field02 char(30));
Multiple Line Entry
mysql 5.0.51b> create table table33 -> (field01 -> integer, -> field02 -> char(30));
!Don't break up words: Valid Invalid mysql 5.0.51b> create table table33 -> (field01 -> integer, -> field02 -> char(30)); mysql 5.0.51b> create table table33 -> (field01 inte -> ger, -> field02 -> char(30));
!When inserting or updating records, do not spread a field's string across multiple lines, !otherwise the line breaks are stored in the record:
Standard Operation
mysql 5.0.51b> insert into table33 (field02)
-> values
-> ('Who thought of foo?');
Line Break Stored in Record
mysql 5.0.51b> insert into table33 (field02)
-> values
-> ('Pooh thought
-> of foo.');
mysql 5.0.51b> select * from table33;
+---------+---------------------+ | field01 | field02 | +---------+---------------------+ | NULL | Who thought of foo? | | NULL | Pooh thoughtof foo. | +---------+---------------------+
Insert Some More Records into the Table
mysql 5.0.51b> insert into table01 (field01,field02,field03,field04,field05) values -> (2, 'second', 'another', '1999-10-23', '10:30:00'); Query OK, 1 row affected (0.00 sec)
!Quotes must go around text values.
!Standard date format is "yyyy-mm-dd". !Standard time format is "hh:mm:ss". !Quotes are required around the standard date and time formats, noted above. !Dates may also be entered as "yyyymmdd" and times as "hhmmss". !If entered in this format, values don't need to be quoted.
Numeric values do not need to be quoted. This holds true regardless of the data type a column is formatted to contain (e.g. text, date, time, integer).
MySQL has a useful command buffer. The buffer stores the SQL statements you've entered thus far. Using it keeps you from having to retype the same commands over and over. Let's use this next step as an example. Add another record using the command buffer (and optional date and time formats)
- Hit the up arrow key twice.
- Hit the ENTER key.
- Type in the new values between a pair parentheses and stick a closing semicolon on the end.
(3, 'a third', 'more foo for you', 19991024, 103004);
- Hit the ENTER key.
Voilà! Is it in there? mysql 5.0.51b> select * from table01; +---------+-----------+------------------+------------+----------+ | field01 | field02 | field03 | field04 | field05 | +---------+-----------+------------------+------------+----------+ | 1 | first | NULL | NULL | NULL | | 2 | second | another | 1999-10-23 | 10:30:00 | | 3 | a third | more foo for you | 1999-10-24 | 10:30:01 | +---------+-----------+------------------+------------+----------+
It's in there!
Now, we're almost done... Updating Existing Records Modify one field at a time
!Again, be careful with syntax. Quote marks need to go around text but not around numbers. mysql 5.0.51b> update table01 set field03='new info' where field01=1;
Query OK, 1 row affected (0.00 sec)
Change multiple fields at once
!Remember to put commas between each field you're updating. mysql 5.0.51b> update table01 set field04=19991022, field05=062218 where field01=1;
Query OK, 1 row affected (0.00 sec)
mysql 5.0.51b> select * from table01;
+---------+-----------+------------------+------------+----------+ | field01 | field02 | field03 | field04 | field05 | +---------+-----------+------------------+------------+----------+ | 1 | first | new info | 1999-10-22 | 06:22:18 | | 2 | second | another | 1999-10-23 | 10:30:00 | | 3 | third one | more foo for you | 1999-10-24 | 10:30:01 | +---------+-----------+------------------+------------+----------+
Update multiple records in one stroke
mysql 5.0.51b> update table01 set field05=152901 where field04>19990101; Query OK, 3 rows affected (0.00 sec)
mysql 5.0.51b> select * from table01;
+---------+-----------+------------------+------------+----------+ | field01 | field02 | field03 | field04 | field05 | +---------+-----------+------------------+------------+----------+ | 1 | first | new info | 1999-10-22 | 15:29:01 | | 2 | second | another | 1999-10-23 | 15:29:01 | | 3 | third one | more foo for you | 1999-10-24 | 15:29:01 | +---------+-----------+------------------+------------+----------+
Menghapus Records
mysql 5.0.51b> delete from table01 where field01=3; Query OK, 1 row affected (0.01 sec)
mysql 5.0.51b> select * from table01;
+---------+---------+----------+------------+----------+ | field01 | field02 | field03 | field04 | field05 | +---------+---------+----------+------------+----------+ | 1 | first | new info | 1999-10-22 | 15:29:01 | | 2 | second | another | 1999-10-23 | 15:29:01 | +---------+---------+----------+------------+----------+
Keluar MySQL
mysql 5.0.51b> quit Bye
Kontributor: Estiyand