MySQL

Brief explanation SQL structured query language (SQL) in MySQL

Here will be brief in order to explain the basic commands SQL which is often in use on the following MySQL:

a.      CREATE DATABASE

b.      DROP DATABASE

c.       CREATE TABLE

d.      DESRIBE

e.      ALTER TABLE

f.        DROP TABLE

g.      DELETE

h.      GRANT

i.        LOCK TABLES

j.        UNLOCK TABLES

k.      INSERT INTO

l.        LOAD DATA INFILE

m.    SELECT

n.      UPDATE

1.      CREATE DATABASE : create a new database

Syntax:

CREATE DATABASE database_name

2.      DROP DATABASE: delete database

Syntax:

DROP DATABASE database_name

3.      CREATE TABLE: create a new table

Syntax:

CREATE TABLE table_name (create_definition,..)

Where create_definition shaped like:

Column_name type [DEFAULT default_value]
  [NOT NULL   |  NULL] [PRIMARY KEY] [reference_definition] Or  PRIMARY KEY (index_column_name,..)
 Or  KEY [key_name] KEY [index_column_name,…)
Or  Index [index_name] (index_column_name,..)
Or UNIQUE [index_name] (index_column_name, …)
Or FOREIGN KEY  index_name (index_column_name, …)
[reference_definition]
Or CHECK (expr)
Index_column_name   shaped like:
Column_name [(length)]
 Reference_definition:
 REFERENCES table_name [(index_column_name,..)]
[MATCH FULL |MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]
Reference_option shaped like:
RESTRICT |CASCADE |SET NULL|NO ACTION|SET DEFAULT

4.   DESC TABLE: table or column description

Syntax:

DESCRIBE |DESC) table [column]

5.   ALTER TABLE: modify tables

Syntax:

ALTER [IGNORE] TABLE table_name

Alter_spesification [,alter_spesification…]

Alter_specification   shaped like:

ADD [COLUMN] create_definition

Or CHANGE [COLUMN] old_column_name

Create_definition

Or ALTER [column] column_name {SET default |DROP DEFAULT}
Or DROP [COLUMN] column_name
 Or DROP PRIMARY KEY
Or DROP INDEX key_name
Or DROP FOREIGN KEY key_name

6.      DROP TABLE: delete table

Syntax:

DROP TABLE table_name [table_name …]

7.      DELETE: remove from the table record

Syntax:

DELETE FROM table_name WHERE where_definition where  where_definition  shaped like:

Where_expr

Or  where_expr [AND|OR] where_expr

Where_expr is as follows:

Column_name [>|>=|<>|<=|<] column_name

Or constant

Or column_name LIKE column_name or constant

Or column_name IS NULL

Or column_name IS NOT NULL

Or (where definition)

8.    GRANT: provide access to users privilege to the table, also can be used to make new user

Syntax:

GRANT (ALL PRIVILLEGES |(SELECT, INSERT, UPDATE, DELETE, REFERENCES (column list), USAGE)) ON table TO user, … [WITH GRANT OPTION]

9.      LOCK TABLES: closed user access to the table

LOCK TABLES table_name [AS alias] READ|WRITE [, table_name READ|WRITE]

10. UNLOCK TABLES:  open access to the table before him in the key

Syntax:

UNLOCK TABLES

11.  INSERT INTO: enter data into a table

Syntax:

INSERT INTO table [(column_name, …)] VALUES expression, …)

Or INSERT INTO table [(column_name, …)] SELECT …

12.  LOAD DATA INFILE: command to read data from text files

Syntax:

LOAD DATA INFILE syntax

13.  SELECT: statement used to select the query to the database

Syntax:

SELECT [STRAIGHT_JOIN] [DISTINCT |ALL]

Select_expression, …[INTO OUTFILE ‘file_name’ …]

[FROM  tables …]

[WHERE where_definition]

[GROUP BY column, …] [ ORDER BY column [ASC|DESC],

…]

HAVING where_definition [LIMIT [offset,] rows]

[PROCEDURE procedure_name]]

[INTO OUTFILE ‘file_name’ …]

14. UPDATE: to update the table field-field

Syntax:

UPDATE table SET column  = expression, … WHERE  

Where_definition

Simple example use the command line mysql utility on the client

mysql> show databases;

mysql> create database dataakademik;

mysql> create database testing;

mysql> show databases;

mysql> drop database testing;

mysql> show databases;

mysql> use dataakademik;

mysql> create table mahasiswa (

    -> nim int (3) primary key,

    -> nama varchar (30),

    -> alamat vachar (12),

    -> telepon varchar (12));

mysql> show tables;

mysql> desc mahasiswa;

 mysql> alter table mahasiswa change nim nim int (8);

mysql> desc mahasiswa;

mysql> create table percobaan(nomor int(2));

mysql> show tables;

mysql> drop table percobaan;

mysql> show table;

mysql> insert into mahasiswa values (13593032,’Mardian Hardipto’,’Haiteu

  Utara’,’Bandung’,’6013853’);

mysql> insert into mahasiswa(nim,nama,alamat) values (13593029,’Agus

  Sutiawan’,’Bagusrangin’,’Bandung’,’2505050’);

mysql> select*from mahasiswa;

mysql> select nim, nama from mahasiswa;

mysql> select nama from mahasiswa where nim = 13293029;

mysql> select * from mahasiswa where nama like ‘%Mardi%’;

mysql> select * from mahasiswa;

mysql> update mahasiswa set telepon=’2503645’where nim=13593029;

mysql> select*from mahasiswa;

mysql> delete from mahasiswa where nama=’Mardian Hardipto’;

mysql> select*from mahasiswa;

mysql> grant insert,select on data.*to suto@localhost identified by ‘berbahaya’;

mysql> exit;