MySQL is a software SQL database management system (DBMS) is multithreaded, and multi-user. MySQL is the implementation of a relational database management system (RDBMS). MySQL dibuah by TcX and has been trusted to manage the system with database contains 10,000 pieces of 40 tables and 500 of them have 7 million rows.
MySQL AB is a Swedish commercial company that has sponsored and MySQL. Founder of MySQL AB is a Swedish two men named David Axmark, Allan Larsson and a Finnish man named Michael "Monty". Each MySQL users can use the freely distributed free under the GPL (General Public License), but may not make a derivative of a commercial nature.
At this time the database server MySQL is very famous in the world, all of it is because the basic language used to access the SQL database. SQL (Structured Query Language) was first applied to a research project at the San Jose Research Laboratory, IBM system called R. Then SQL is also cultivated by Oracle, Informix and Sybase. By using SQL, the process of accessing the database more user-friendly compared with others, such as dBase or Clipper because they are still using pure programming commands.
SQL can be used stand-alone as well as in the attach to the programming language like C + +, and Delphi.
1. SQL elements
Elements from the most basic SQL statements, among others, names, data types, expressions, constants and functions innate.
Command of SQL that is used to request an action to the DBMS.
Basic SQL statements, among others:
- ALTER: Changing the table structure
- COMMIT: Ending the transaction execution
- CREATE: Creating tables, indexes
- DELETE: Delete rows in a table
- DROP: Deletes table, index
- GRANT: Assign rights to the user database
- INSERT: Adding rows in table
- REVOKE: Cancel the rights to the database
- ROLLBACK: Restoring the original state if the transaction is not carried out
- SELECT: Selecting rows and columns in a table
- UPDATE: Changing the value on a table row
Name is used as an identity, the identity of the object in the DBMS. For example: tables, columns and users.
Existing data types in MySQL:
1. Numeric data types include:
- TINYINT: integer value is very small
- SMALLINT: a small integer value
- MEDIUMINT: integer value that is being
- INT: integer value to the standard value
- BEGINT: integer value with great value
- FLOAT: decimal Numbers with single-precission
- DOUBLE: decimal Numbers with double-precission
- DECIMAL (M, D): Numbers are expressed as strings float. M: number of digits stored, D: the number of digits after the decimal
2. String data types include:
- CHAR: The character has a fixed length is n
- VARCHAR: Characters that have not fixed the maximum length n
- TINYBLOB: BLOB with very small size
- BLOB: BLOB that has a small size
- MEDIUMBLOB: BLOB which has a medium-size
- LONGBLOB: BLOB that has a large size
- TINYTEXT: text with very small size
- TEXT: Text that has a small size
- MEDIUMTEXT: text which has a medium-size
- LONGTEXT: a large size text
- ENUM: column filled with an enumeration member
- SET: Columns can be filled with some members of the set value
3. Single data type and hours:
- DATE: date has the format year-month-date
- TIME: time has the format hours-minutes-seconds
- DATETIME: a combination of date and time formats
Expression used to generate / calculate the value.
For example: the number = price-discounted
Arithmetic expressions, among others:
- +: Added
- -: Less
- /: For
- *: Time
Value remains
Function is a subprogram that can produce a value when the function is called. Aggregate functions are functions that are used to do the summary, statistical performed on a table / query.
- AVG (expression): used to find the average value in the column of the table.
- COUNT (x): is used to count the number of rows of a column of the table
- MAX (expression): used to seek the greatest value of a column of the table
- MIN (expression): used to find the smallest value of a column of the table
- SUM (expression): used for counting the total number of a column of the table
2. Group SQL Statement
SQL statements can be classified into 3 groups, namely: DDL, DML and DCL.
1.Pernyataan group SQL DDL (Data Defination Language)
DDL is used to define the attributes of the database, table, attributes (columns), the limits of an attribute, as well as the relationships between tables. Commands that are used usually: CREATE, ALTER, and DROP
1. Creating Database
The syntax: CREATE DATABASE namaDatabase;
2. Deleting Database
The syntax: DROP DATABASE namaDatabase;
3. Creating a Table
The syntax: CREATE TABLE namatabel;
4. Deleting Database
The syntax: DROP DATABASE namaDatabase;
5. Removing Tables
The syntax: DROP TABLE namatabel;
6. Defining null / not null
The syntax: CREATE TABLE namatabel
(Field1 TipeData1 NOT NULL,
Field2 TipeData2
);
Defining Default Values
The syntax: CREATE TABLE namatabel
(Field1 TipeData1,
7. Field2 TipeData2 DEFAULT value
);
8. Defining a Primary Key on a Table
Can be done with 3 Syntax:
1) CREATE TABLE namatabel
(Field1 TipeData1 NOT NULL PRIMARY KEY,
Field2 TipeData2
);
2) CREATE TABLE namatabel
(Field1 TipeData1,
TipeData2 field2,
PRIMARY KEY (Field1)
);
3) ALTER TABLE ADD CONSTRAINT namaconstraint namatabel PRIMARY KEY (Name column)
9. Removing a Primary Key on Table
The syntax is 2:
1) ALTER TABLE DROP CONSTRAINT namatabel NAMACONSTRAINT;
2) NAMATABEL ALTER TABLE DROP PRIMARY KEY;
10. Adding New Column in Table
The syntax: ALTER TABLE ADD Newfield NAMATABEL type;
11. Changing Data Type or Column Width in table
The syntax: ALTER TABLE MODIFY COLUMN namatabel field type;
12. Changing Column Name
The syntax: ALTER TABLE CHANGE COLUMN namatabel namakolomlama namakolombaru type;
13. Deleting Columns in Table
The syntax: ALTER TABLE DROP COLUMN namatabel Name column;
14. Defining Foreign Key in Table
Can be done with 2 Syntax:
1) CREATE TABLE namatabel
(Field1 TipeData1,
TipeData2 field2,
FOREIGN KEY (Field2) REFERENCES namatabelinduk
(Namakolominduk) ON UPDATE CASCADE ON DELETE NO ACTION)
2) ALTER TABLE ADD CONSTRAINT namaconstraint namatabel FOREIGN KEY (Name column) REFERENCES namatabelinduk (namakolominduk) ON UPDATE CASCADE ON DELETE NO ACTION;
15. Removing Foreign Key
Syntak used: ALTER TABLE DROP FOREIGN KEY namatabel namaconstraint;
2. DML (Data Manipulation Language)
Memanipuladi DML functions to existing data in the database, for example for data retrieval, data insertion, modification and deletion of data data.
Commonly used commands are: INSERT, DELETE, UPDATE, and SELECT.
1. INSERT
add rows to the table. The most commonly used syntax: INSERT INTO namatabel VALUES (value1, value2, value-n);
2. DELETE
Delete rows in the table. Syntax: DELETE FROM namatabel [where condition];
3. UPDATE
Changing the contents of a column in the table. Syntax: SET UPDATE namatabel kolom1 = value1, value2 kolom2 = [where condition];
4. SELECT
Displays the contents of a table that can be linked to other tables;
MySQL excellence
- Program is multi-threaded, so it can be installed on a server that has the mulit-CPU
- Powered common programming languages such as C, C + +, Java, Perl, PHP, Python, TCL, Apls DLS.
- Working on a variety of platforms
- Have the kind of column that pretty much making it easier for system configuration database
- Have the kind of column that pretty much making it easier for system configuration database
- Have a pretty good security system with host verification
- ODBC support for OS Microsoft Windows
- Record supports that has a column with a fixed length
- Software that is free
- Integrated with each other with PHP
Sources: http://id.wikipedia.org/wiki/MySQL