Kamis, 11 Desember 2014

MEMBANGUN DATABASE

mysql> show databases;
+----------+
| Database |
+----------+
| Kampus   |
+----------+
1 row in set (0.00 sec)

mysql> create database mahasiswa;
Query OK, 1 row affected (0.00 sec)

mysql> use mahasiswa;
Database changed
mysql> create table mhs(Nim varchar(10) not null, Nama varchar(30) not null, TglLahir date, Alamat text, Primary key(Nim));
Query OK, 0 rows affected (0.02 sec)

mysql> desc mhs;
+------------+----------------+-------+------+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+------------+----------------+-------+------+---------+-------+
| Nim        | varchar(10) |          | PRI |              |       |
| Nama     | varchar(30) |          |        |              |       |
| TglLahir  | date            | YES  |        | NULL    |       |
| Alamat    | text             | YES  |        | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table mhs add Agama varchar(15) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mhs;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim      | varchar(10) |      | PRI |         |       |
| Nama     | varchar(30) |      |     |         |       |
| TglLahir | date        | YES  |     | NULL    |       |
| Alamat   | text        | YES  |     | NULL    |       |
| Agama    | varchar(15) |      |     |         |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table mhs change agama agama varchar(10);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc mhs;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim      | varchar(10) |      | PRI |         |       |
| Nama     | varchar(30) |      |     |         |       |
| TglLahir | date        | YES  |     | NULL    |       |
| Alamat   | text        | YES  |     | NULL    |       |
| agama    | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

mysql> alter table mhs modify agama char(2) not null;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mhs;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim      | varchar(10) |      | PRI |         |       |
| Nama     | varchar(30) |      |     |         |       |
| TglLahir | date        | YES  |     | NULL    |       |
| Alamat   | text        | YES  |     | NULL    |       |
| agama    | char(2)     |      |     |         |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table mhs drop agama;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0




mysql> desc mhs;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim      | varchar(10) |      | PRI |         |       |
| Nama     | varchar(30) |      |     |         |       |
| TglLahir | date        | YES  |     | NULL    |       |
| Alamat   | text        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> rename table mhs to anggota;
Query OK, 0 rows affected (0.00 sec)

mysql> desc anggota;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim      | varchar(10) |      | PRI |         |       |
| Nama     | varchar(30) |      |     |         |       |
| TglLahir | date        | YES  |     | NULL    |       |
| Alamat   | text        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table anggota rename to mhs;
Query OK, 0 rows affected (0.00 sec)

mysql> desc mhs;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim      | varchar(10) |      | PRI |         |       |
| Nama     | varchar(30) |      |     |         |       |
| TglLahir | date        | YES  |     | NULL    |       |
| Alamat   | text        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into mhs values('0411500121','Achmad solichin','1982-06-05','Jakarta Selatan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mhs values('0411500123','Bajuri','1983-03-25','Jakarta Selatan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into mhs values('0411500111','Oneng','1980-03-22','Jakarta Utara');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mhs values('0411500115','Unyil','1980-08-29','Tangerang');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mhs values('0411500116','Harvei','1984-02-11','Medan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mhs;
+------------+-----------------+------------+-----------------+
| Nim        | Nama            | TglLahir   | Alamat          |
+------------+-----------------+------------+-----------------+
| 0411500121 | Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123 | Bajuri          | 1983-03-25 | Jakarta Selatan |
| 0411500111 | Oneng           | 1980-03-22 | Jakarta Utara   |
| 0411500115 | Unyil           | 1980-08-29 | Tangerang       |
| 0411500116 | Harvei          | 1984-02-11 | Medan           |
+------------+-----------------+------------+-----------------+
5 rows in set (0.00 sec)

mysql> update mhs set alamat='Tangerang' where nim='0411500116';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mhs;
+------------+-----------------+------------+-----------------+
| Nim        | Nama            | TglLahir   | Alamat          |
+------------+-----------------+------------+-----------------+
| 0411500121 | Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123 | Bajuri          | 1983-03-25 | Jakarta Selatan |
| 0411500111 | Oneng           | 1980-03-22 | Jakarta Utara   |
| 0411500115 | Unyil           | 1980-08-29 | Tangerang       |
| 0411500116 | Harvei          | 1984-02-11 | Tangerang       |
+------------+-----------------+------------+-----------------+
5 rows in set (0.00 sec)

mysql> update mhs set tgllahir='1986-03-11', alamat='Medan' where nim='0411500116';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mhs;
+------------+-----------------+------------+-----------------+
| Nim        | Nama            | TglLahir   | Alamat          |
+------------+-----------------+------------+-----------------+
| 0411500121 | Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123 | Bajuri          | 1983-03-25 | Jakarta Selatan |
| 0411500111 | Oneng           | 1980-03-22 | Jakarta Utara   |
| 0411500115 | Unyil           | 1980-08-29 | Tangerang       |
| 0411500116 | Harvei          | 1986-03-11 | Medan           |
+------------+-----------------+------------+-----------------+
5 rows in set (0.00 sec)

mysql> delete from mhs where nim='0411500116';
Query OK, 1 row affected (0.00 sec)






mysql> select * from mhs;
+------------+-----------------+------------+-----------------+
| Nim        | Nama            | TglLahir   | Alamat          |
+------------+-----------------+------------+-----------------+
| 0411500121 | Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123 | Bajuri          | 1983-03-25 | Jakarta Selatan |
| 0411500111 | Oneng           | 1980-03-22 | Jakarta Utara   |
| 0411500115 | Unyil           | 1980-08-29 | Tangerang       |
+------------+-----------------+------------+-----------------+
4 rows in set (0.00 sec)

mysql> select nim, nama from mhs;
+------------+-----------------+
| nim        | nama            |
+------------+-----------------+
| 0411500121 | Achmad solichin |
| 0411500123 | Bajuri          |
| 0411500111 | Oneng           |
| 0411500115 | Unyil           |
+------------+-----------------+
4 rows in set (0.00 sec)

mysql> select * from mhs where nim='0411500121';
+------------+-----------------+------------+-----------------+
| Nim        | Nama            | TglLahir   | Alamat          |
+------------+-----------------+------------+-----------------+
| 0411500121 | Achmad solichin | 1982-06-05 | Jakarta Selatan |
+------------+-----------------+------------+-----------------+
1 row in set (0.00 sec)

mysql> select * from mhs where alamat !='Jakarta Selatan';
+------------+-------+------------+---------------+
| Nim        | Nama  | TglLahir   | Alamat        |
+------------+-------+------------+---------------+
| 0411500111 | Oneng | 1980-03-22 | Jakarta Utara |
| 0411500115 | Unyil | 1980-08-29 | Tangerang     |
+------------+-------+------------+---------------+
2 rows in set (0.00 sec)

mysql> select * from mhs where alamat='Jakarta Selatan' && year(tgllahir)='1982'
;
+------------+-----------------+------------+-----------------+
| Nim        | Nama            | TglLahir   | Alamat          |
+------------+-----------------+------------+-----------------+
| 0411500121 | Achmad solichin | 1982-06-05 | Jakarta Selatan |
+------------+-----------------+------------+-----------------+
1 row in set (0.00 sec)




mysql> select * from mhs where alamat='Jakarta Selatan' && month(tgllahir)='03';

+------------+--------+------------+-----------------+
| Nim        | Nama   | TglLahir   | Alamat          |
+------------+--------+------------+-----------------+
| 0411500123 | Bajuri | 1983-03-25 | Jakarta Selatan |
+------------+--------+------------+-----------------+
1 row in set (0.00 sec)

mysql> select nim, nama, year(now())-year(tgllahir) as Umur from mhs;
+------------+-----------------+------+
| nim        | nama            | Umur |
+------------+-----------------------------    +------+
| 0411500121 | Achmad solichin  |   27 |
| 0411500123 | Bajuri                      |   26 |
| 0411500111 | Oneng                    |   29 |
| 0411500115 | Unyil                       |   29 |
+------------+-----------------------------    +------+
4 rows in set (0.00 sec)

mysql> select * from mhs where substring(nim,3,2)='11';
+------------+-----------------+------------+-----------------+
| Nim        | Nama            | TglLahir   | Alamat          |
+------------+-----------------+------------+-----------------+
| 0411500121 | Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123 | Bajuri          | 1983-03-25 | Jakarta Selatan |
| 0411500111 | Oneng           | 1980-03-22 | Jakarta Utara   |
| 0411500115 | Unyil           | 1980-08-29 | Tangerang       |
+------------+-----------------+------------+-----------------+
4 rows in set (0.00 sec)

mysql> select * from mhs order by nim desc;
+------------+-----------------+------------+-----------------+
| Nim        | Nama            | TglLahir   | Alamat          |
+------------+-----------------+------------+-----------------+
| 0411500123 | Bajuri          | 1983-03-25 | Jakarta Selatan |
| 0411500121 | Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500115 | Unyil           | 1980-08-29 | Tangerang       |
| 0411500111 | Oneng           | 1980-03-22 | Jakarta Utara   |
+------------+-----------------+------------+-----------------+
4 rows in set (0.01 sec)






mysql> select * from mhs order by nim asc;
+------------+-----------------+------------+-----------------+
| Nim        | Nama            | TglLahir   | Alamat          |
+------------+-----------------+------------+-----------------+
| 0411500111 | Oneng           | 1980-03-22 | Jakarta Utara   |
| 0411500115 | Unyil           | 1980-08-29 | Tangerang       |
| 0411500121 | Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123 | Bajuri          | 1983-03-25 | Jakarta Selatan |
+------------+-----------------+------------+-----------------+
4 rows in set (0.00 sec)

mysql> select * from mhs order by nim limit 0,5;
+------------+-----------------+------------+-----------------+
| Nim        | Nama            | TglLahir   | Alamat          |
+------------+-----------------+------------+-----------------+
| 0411500111 | Oneng           | 1980-03-22 | Jakarta Utara   |
| 0411500115 | Unyil           | 1980-08-29 | Tangerang       |
| 0411500121 | Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123 | Bajuri          | 1983-03-25 | Jakarta Selatan |
+------------+-----------------+------------+-----------------+
4 rows in set (0.00 sec)





Diposting oleh di Tidak ada komentar:

Minggu, 07 Desember 2014

Yuk! Belajar Pemrograman Visual Basic dot Net Di Mesaran.Net



Berikut ini coding pemograman nya :



Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        kode.Items.Add("TS001")
        kode.Items.Add("TS002")
        kode.Items.Add("VG001")
        kode.Items.Add("VG002")

        BUATTABEL()
    End Sub

    Sub BUATTABEL()
        lv.Columns.Add("No. Pembeian", 80, HorizontalAlignment.Center)
        lv.Columns.Add("Kode Barang", 80, HorizontalAlignment.Center)
        lv.Columns.Add("Nama Barang", 80, HorizontalAlignment.Center)
        lv.Columns.Add("Merk", 80, HorizontalAlignment.Center)
        lv.Columns.Add("Harga", 80, HorizontalAlignment.Center)
        lv.Columns.Add("Jumlah Beli", 80, HorizontalAlignment.Center)
        lv.Columns.Add("Total Harga", 80, HorizontalAlignment.Center)
        lv.View = View.Details
        lv.GridLines = True
        lv.FullRowSelect = True
    End Sub


    Sub ISITABEL()
        Dim L As New ListViewItem
        L.Text = no.Text
        L.SubItems.Add(kode.Text)
        L.SubItems.Add(nama.Text)
        L.SubItems.Add(merk.Text)
        L.SubItems.Add(harga.Text)
        L.SubItems.Add(jumlah.Text)
        L.SubItems.Add(toha.Text)
        lv.Items.Add(L)
    End Sub

    Sub smpn(ByVal t As Form)
        no.Text = ""
        kode.Text = ""
        nama.Text = ""
        merk.Text = ""
        harga.Text = ""
        jumlah.Text = ""
        toha.Text = ""
    End Sub

    Private Sub kode_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles kode.SelectedIndexChanged
        Dim j As String
        j = Microsoft.VisualBasic.Left(kode.Text, 2)
        Select Case j
            Case "TS" : merk.Text = "Toshiba"
            Case "VG" : merk.Text = "V-Gen"
        End Select
        Dim d As String
        d = Microsoft.VisualBasic.Right(kode.Text, 3)
        Select Case d
            Case "001" : nama.Text = "Flashdisk 4GB"
            Case "002" : nama.Text = "Flashdisk 2GB"
        End Select
        Dim c As String
        c = Microsoft.VisualBasic.Mid(kode.Text, 1)
        Select Case c
            Case "TS001" : harga.Text = "105000"
            Case "TS002" : harga.Text = "75000"
            Case "VG001" : harga.Text = "90000"
            Case "VG002" : harga.Text = "60000"
        End Select
    End Sub

    Private Sub btnhapussemuadata_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnhapussemuadata.Click
        lv.Items.Clear()
    End Sub

    Private Sub jumlah_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles jumlah.KeyPress

        If Asc(e.KeyChar) = 13 Then
            toha.Text = harga.Text * jumlah.Text
        End If
    End Sub

    Private Sub btnkeluar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnkeluar.Click
        Me.Close()
    End Sub

    Private Sub btnhapusdatayangdipilih_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnhapusdatayangdipilih.Click
        lv.Items.Remove(lv.SelectedItems(0))
    End Sub

    Private Sub btnbersih_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbersih.Click
        no.Text = ""
        kode.Text = ""
        nama.Text = ""
        merk.Text = ""
        harga.Text = ""
        jumlah.Text = ""
        toha.Text = ""
    End Sub

    Private Sub btnsimpankelistview_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsimpankelistview.Click
        ISITABEL()
        smpn(Me)
    End Sub
End Class


Hasil Nya 


Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)