Perintah MySQL

Beberapa perintah MySQL yang menarik

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;

Creating a Simple Database and Displaying its Structure

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';

Create a table

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 ";".

List the tables

mysql 5.0.51b> show tables;

+----------------------+
| Tables in database01 |
+----------------------+
| table01              |
| table02              |
+----------------------+

List the fields in a table

mysql 5.0.51b> show columns from table01;

+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| field01 | int(11)  | YES  |     |         |       |
| field02 | char(10) | YES  |     |         |       |
+---------+----------+------+-----+---------+-------+

Insert a 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.

List all the records in a table

mysql 5.0.51b> select * from table01;

+---------+---------+
| field01 | field02 |
+---------+---------+
|       1 | first   |
+---------+---------+

Adding 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.

iStandard date format is "yyyy-mm-dd".
iStandard time format is "hh:mm:ss".
!Quotes are required around the standard date and time formats, noted above.
iDates 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 |
+---------+-----------+------------------+------------+----------+

Deleting 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 |
+---------+---------+----------+------------+----------+

Time to Call it Quits

mysql 5.0.51b> quit
Bye

Referensi

Pranala Menarik