Commands on Databases and Tables with MYSQL

  No comments

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.

No comments :

Post a Comment