CREATE TABLE examples

Basic CREATE TABLE statementA very basic CREATE TABLE statement which should work in any SQL database:

mysql> CREATE TABLE example (
         id INT,
         data VARCHAR(100)
       );
Query OK, 0 rows affected (0.03 sec)

Creating a table with a particular storage engine 

MySQL provides a variety of different table types with differing 
levels of functionality. The usual default, and most widely used, is MyISAM. Other storage types must be explicitly defined:

mysql>  CREATE TABLE example_innodb (
          id INT,
          data VARCHAR(100)
        ) TYPE=innodb;
Query OK, 0 rows affected (0.03 sec)

Note that beginning with MySQL 4.1 ENGINE=innodb is the preferred method of defining the storage type.
Use SHOW CREATE TABLE (see below) to check that MySQL has created the table as you defined it.


Creating a table with auto_incrementOften you'll want to be able to automatically assign a sequential value to a column:

mysql> CREATE TABLE example_autoincrement (
         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         data VARCHAR(100)
       );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO example_autoincrement (data)
    ->      VALUES ('Hello world');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM  example_autoincrement;
+----+-------------+
| id | data        |
+----+-------------+
|  1 | Hello world |
+----+-------------+
1 row in set (0.01 sec)

Creating a table with the current timestampOften it's useful to have an automatic timestamp on each record. The MySQL special datatype TIMESTAMP enables you to keep track of changes to a record:

mysql> CREATE TABLE example_timestamp (
         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         data VARCHAR(100),
         cur_timestamp TIMESTAMP(8)
       );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO example_timestamp (data)
            VALUES ('The time of creation is:');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM example_timestamp;
+----+--------------------------+---------------------+
| id | data                     | cur_timestamp       |
+----+--------------------------+---------------------+
|  1 | The time of creation is: | 2004-12-01 20:37:22 |
+----+--------------------------+---------------------+
1 row in set (0.00 sec)

mysql> UPDATE example_timestamp 
          SET data='The current timestamp is: ' 
        WHERE id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM example_timestamp;
+----+---------------------------+---------------------+
| id | data                      | cur_timestamp       |
+----+---------------------------+---------------------+
|  1 | The current timestamp is: | 2004-12-01 20:38:55 |
+----+---------------------------+---------------------+
1 row in set (0.01 sec)

The column cur_timestamp is automagically updated every time the record is changed.


Creating a table with TIMESTAMP DEFAULT NOW()MySQL supports the construct TIMESTAMP DEFAULT NOW() only from verson 4.1:

CREATE TABLE example_default_now (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  data VARCHAR(100),
  created TIMESTAMP DEFAULT NOW()
);

In this case the column created retains its initial value and is not changed during subsequent updates.
For versions prior to 4.1, the only workaround is to create two timestamp columns in a table, and explicitly set the second one when inserting the record. Remember: the first TIMESTAMP will be automagically updated on each record update.


Viewing a table definitionFor basic information on table columns, use DESC tablename:

mysql> DESC example;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| data  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Exact definition of the table:

mysql> SHOW CREATE TABLE example;
+---------+------------------------------------------------+
| Table   | Create Table                                   |
+---------+------------------------------------------------+
| example | CREATE TABLE `example` (
  `id` int(11) default NULL,
  `data` varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------+
1 row in set (0.00 sec)

news source: http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html 

Understanding SQL

SQL stands for QUERY LANGUAGE STRUCTURE is a universal language that can be used to modify and access data and information stored in the database, SQL is a language that is supported by all DBMS (database management system).
SQL language is the de facto standard language used in relational database management. Now, almost all the existing database server support sql language to perform data management.
SQL consists of:
- Data Definition Language (DDL) is used to define, modify, and delete databases and objects that are required in the data base.
- Data Manipulation Language (DML) is used to manipulate the data in a table.
SQL statements can be SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE.

news source: http://temukanpengertian.blogspot.com/2013/07/pengertian-sql.html

Understanding Database administrator

The database administrator is a person responsible for handling database within an organization, his responsibilities include:

 - Responsible for database security.
- The design and overall coordination of the database.
- Develop schemes.
- Create a document and use the system.
- Define data organization.
- Implementation of the DBMS.
- Testing and maintenance of database systems.
- Being a mediator between users and management.
- Responsible for the entire operation of the data base system.
- Implement emergency procedures in Kasis system failure or malfunction database.
- Train and educate personnel associated with the database.

news source:http://temukanpengertian.blogspot.com/2013/07/pengertian-database-administrator.html

Understanding DBMS

DBMS is Database Management System sigkatan of Indonesian interpreted in a database management system (DBMS). DBMS is the software provided by the database provision for:

 
- Manage and maintain data.
- Moving data to and from the required physical data files.
- Managing concurrent data access by multiple users.
- Oversee the renewal data and prevent conflict data changes.
- Managing the transaction so that data changes occur in full or no change if the transaction canceled.
- Supports query language.
- Monitoring database backup and recovery from errors.
- Security mechanism.
 DBMS is used to store data in a file and write code for the particular application set.
Examples of DBMS is Oracle, MySQL, SQL server 2000/2003, MS Access, and others.
The stored data illustrate various aspects such as:
- Entities (for example: students, subjects).
- Relationships between entities / relationships (eg: Andi took a course Database).
With a database management system (DBMS) allows a user mempu define, create, maintain and provide controlled access to the data.

news source:http://temukanpengertian.blogspot.com/2013/07/pengertian-dbms.html