Kamis, 30 September 2010

share kumpulan perintah SQL

CREATE DATABASE {nama_database];

USE [nama_database];
USE [nama_table];

CREATE TABLE nama_table(
Attribut_1 tipe_data (ukuran),
Attribut_2 tipe_data (ukuran),
Attribut_n tipe_data (ukuran));

CREATE TABLE nama_table(
Attribut_1 tipe_data (ukuran) [PRIMARY KEY] [AUTO_INCREMENT],
Attribut_n tipe_data (ukuran));

DESC nama_table

INSERT INTO [nama_table] VALUES (‘data_atribut1’,’data_atribut2’,’data_atributN’);

SELECT * FROM [nama_table];

ALTER TABLE [nama_table]
CHANGE [attribut_lama] [attribut_baru] [tipe_data];

ALTER TABLE [nama_table]
ADD [nama_attribut] [tipe_data];

ALTER TABLE [nama_table]
ADD [nama_attribut] [tipe_data] AFTER [attribut_n];

ALTER TABLE [nama_table]
DROP [nama_attribut];

SHOW DATABASES;

SHOW TABLES;

DROP TABLE [nama_table];

DROP DATABASE [nama_database];

UPDATE nama_table
SET attribut_1 = nilai_baru_1,
attribut_2 = nilai_baru_2,
...
attribut_n = nilai_baru_n
[WHERE kondisi];

DELETE
FROM nama_table
[ WHERE kondisi ];

CASE nilai_ekspresi
WHEN nilai_ekspresi
THEN nilai_ekspresi
[ WHEN nilai_ekspresi
THEN nilai_ekspresi ]
[ ELSE nilai_ekspresi ]
END

IF(ekspresi1,ekspresi2,ekspresi3)

IFNULL(ekspresi1,ekspresi2)

nama_table.nama_attribut

SELECT alias1.attribut, alias2.attribut
FROM table1 alias1, table2 alias2
WHERE kondisi;

SELECT CURDATE();

SELECT attribut FROM nama_table
WHERE DAYOFWEEK(attribut_tgl) = kondisi

SELECT attribut,
DATEDIFF(NOW(), attribut_tgl)/365 AS attribut_usia FROM nama_table

CREATE VIEW attribut_pemanggil AS
SELECT attribut1, attribut2
FROM nama_table;

CREATE USER nama_user
IDENTIFIED BY ‘password’;

GRANT hak akses
ON objek
TO pemakai;

REVOKE hak_akses
ON objek
FROM pemakai;

DROP USER pemakai

SET PASSWORD FOR pemakai = PASSWORD (‘suatu password’); //MENGGANTI PASS USER

SET PASSWORD = PASSWORD(‘suatu password’) //MENGGANTI PASS ROOT

RENAME USER nama_lama TO nama_baru;

mysql –u root –p
cd xampp, cd mysql, cd bin, mysql –u root -p

Tidak ada komentar:

Posting Komentar