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 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
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 INDEX key_name
Or DROP FOREIGN KEY key_name
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;
