MySQL is a database management system software (DBS) or multithread, multi-user DBMS, with around 6 million installations worldwide. MySQL AB makes MySQL available as free software under the GNU General Public License (GPL) license, but they also sell under a commercial license for cases where its use does not match the use of the GPL.
Unlike projects like Apache, where software is developed by the general community, and copyright to source code is owned by their respective authors, MySQL is owned and sponsored by a Swedish commercial company MySQL AB, which holds the copyright almost over All the source code. The two Swedes and one Finn who founded MySQL AB were: David Axmark, Allan Larsson, and Michael "Monty" Widenius. [Wikipedia]
Here's a collection of Commands on MySQL
1. On the Database
A. Create a database
To create a new database, so it does not apply if the database already exists or you have no privileges.
The syntax:
CREATE DATABASE nama_db
B. Delete the database
To delete the database and all the tables inside it. This command does not apply if the database does not exist or you have no privileges. The syntax:
DROP DATABASE nama_db
C. Using a database
To make the database the default and reference of the table you will use later. This command does not apply if the database does not exist or you have no privileges. The syntax:
USE nama_db
D. Displays the database
To display the list in the current system. The syntax:
SHOW DATABASES
The view is:
+ ----------------- +
| Database |
+ ----------------- +
| Contoh_db |
| Mysql |
| Test |
| Exam |
+ ----------------- +
4 rows in set (0.00 sec)
2. In Table
A. Create table
To create a table at least you need to specify the name and type of column you want. The simplest syntax (without any other definition) is:
CREATE TABLE nama_tbl
(Column1 tipekolom1 (), columnek tipekolom2 (), ...)
Example: You want to create a table with a profile name that has a name field (char type, width 20), age column (integer type), sexy type column (enum type, contains M and F). The syntax:
CREATE TABLE profile (
Name of CHAR (20), age INT NOT NULL,
ENUM type ('F', 'M'));
While a rather complete command in creating a table is to include a particular definition. For example a command like this:
CREATE TABLE participants (
No SMALL INT UNSIGNED NOT NULL AUTO_INCREMENT,
CHAR Name (30) NOT NULL,
Fields ENUM ('TS', 'WD') NOT NULL,
PRIMARY KEY (No),
INDEX (Name, Field of Study));
The above command means to create a table of participants with the column No. as PRIMARY KEY is a unique table index that can not be duplicated with the AUTO_INCREMENT attribute is a column that can automatically sort the numbers filled in to it. While the Name and Field column is used as a regular index.
B. Create an index in the table
Adding an index to an existing table is either unique or common.
The syntax:
CREATE INDEX nama_index ON nama_tbl (nama_kolom)
CREATE UNIQUE INDEX nama_index ON nama_tbl (nama_kolom)
C. Delete table
To delete a table in a particular database. If done then all contents, indexes and other attributes will be erased. The syntax:
DROP TABLE nama_tbl
D. Delete index
To delete an index on a table. The syntax:
DROP INDEX name-index ON nama_tbl
E. View table information
To see what tables exist in a particular database. The syntax:
SHOW TABLES FROM nama_db
As for viewing the table description or information about the column use the syntax:
DESC nama_tbl nama_kolom
Or SHOW COLUMNS FROM nama_tbl FROM nama_db
Example for example above will be displayed:
+ ----------------------------- +
| Tables_in_contoh_db |
+ ----------------------------- +
| Participants |
| Profile |
+ ----------------------------- +
2 rows in set (0.00 sec)
+ ------------------------- + ----------------------- - + ------- + -------- + --------------- + ------ +
| Field | Type | Null | Key | Default | Extra |
+ ------------------------- + ----------------------- - + ------- + -------- + --------------- + ------ +
| Name | Char (20) | YES | | NULL | |
| Age | Int (11) | | 0 | | |
| Jenis_kelamin | Enum ('F', 'M') | YES | | NULL | |
+ ------------------------- + ----------------------- - + ------- + -------- + --------------- + ------ +
3 rows in set (0.02 sec)
F. Obtain or display information from the table
To display the contents of the table with certain options. For example to display the entire contents of the table is used:
SELECT * FROM nama_tbl
To display only certain columns:
SELECT column1, column2, ... FROM nama_tbl
To display the contents of a column with certain conditions
SELECT column1 FROM name_tbl WHERE column2 = isikolom
G. Modify table structure
Can be used to rename a table or change its structure such as adding columns or indexes, deleting columns or indexes, changing column types etc. Common syntax:
ALTER TABLE nama_tbl action
To add a new column in a specific place you can use:
ALTER TABLE nama_tbl
ADD column_new type () definition
To add a new_name of integer type after column1 is used:
ALTER TABLE nama_tbl
ADD column_new INT NOT NULL AFTER column1
To add a new index to a particular table both unique and ordinary:
ALTER TABLE nama_tbl ADD INDEX nama_index (nama_kolom)
ALTER TABLE nama_tbl ADD UNIQUE name_index (nama_kolom)
ALTER TABLE nama_tbl ADD PRIMARY KEY nama_indeks (nama_kolom)
To change the column names and their definitions, for example rename new_name with integer type to new_kolom with char type with width 30 used:
ALTER TABLE nama_tbl
CHANGE column_new new_kolom CHAR (30) NOT NULL
To delete a column and all its attributes, for example deleting column1:
ALTER TABLE nama_tbl DROP column1
To remove indices either unique or commonly used:
ALTER TABLE nama_tbl DROP nama_index
ALTER TABLE nama_tbl DROP PRIMARY KEY
H. Modify the information in the table.
To add a new record or row in the table, the syntax is:
INSERT INTO nama_tbl (nama_kolom) VALUES (isi_kolom)
Or INSERT INTO nama_tbl SET nama_kolom = 'isi_kolom'
For example to add two rows in the profile table with the content name = deden & ujang and content age = 17 & 18 are:
INSERT INTO profile (name, age) VALUES (deden, 17), (ujang, 18)
Or INSERT INTO profile SET name = 'deden', age = '17 ';
INSERT INTO profile SET name = 'ujang', age = '18 ';
To modify an existing record or row corresponding to a column. For example to change the deden life to 18 in the example above can be used syntax:
UPDATE profile SET age = 18 WHERE name = 'deden';
To delete a particular record or row in a table. For example to delete the existing row of the named name used syntax:
DELETE FROM profile WHERE name = 'ujang';
If WHERE is not included then all contents in the profile table will be deleted.
So a collection of database commands and tables in MySQL, sorry if there are errors, hopefully useful.
