Related Database (MySQL and CMD)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Dedicated to my mother, father, older brothers, husband and sons/ daughters in the future, lectures, and friends who always support me in any situation.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

PREFACE

 

Nowadays, technology has grown up rapidly, it forces programmers to program and develop sophisticated and useful software for users. Many programming languages that we can choose according to our necessary, such as Delphi, C++, Java, PHP, Visual Basic, and many many more.

For building dynamic software {office program, school program, commerce program, cashier program, etc.}, we need integrate our program with database. Like water in a container, the water is data, and the container is database. Data which has executed through an interface will be proceeded to a database for processing. Each database has tables, and each table has relationships.

In this book, I will show you how to create a relational database using MySQL through Command Prompt (CMD). As we have known, there are many kinds of database such as MS. Access, SQL Server, MySQL, Postgre, Oracle, and many many more, but in this book we use MySQL. Why? Because MySQL’s more popular and open source database. We could create a database through PHPMyAdmin easily, but as a programmer we have to know how to create a database manually.

 

Why I Wrote This Book?

I want to share my science that I have learned from my lectures at campus, STMIK Subang, Indonesia. Also I want to tie it through writing, as my Idol (Muhammad S. A. W) said “tie science by writing!”.

 

Whom Is This Book For?

Especially this book’s written for all programmers, both web and desktop programmers and also for IT student who are building and developing dynamic programs.

Well, enjoy it! 

 

 

 

 

 

 

 

Chapter I

Introducing MySQL

MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation.

The MySQL Web site (http://www.mysql.com/) provides the latest information about MySQL software.

• MySQL is a database management system.

A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server. Since computers are very good at handling large amounts of data, database management systems play a central role in computing, as standalone utilities, or as parts of other applications.

• MySQL databases are relational.

A relational database stores data in separate tables rather than putting all the data in one big storeroom. The database structures are organized into physical files optimized for speed. The logical model, with objects such as databases, tables, views, rows, and columns, offers a flexible programming environment. You set up rules governing the relationships between different data fields, such as one-to-one, one-to-many, unique, required or optional, and “pointers” between different tables. The database enforces these rules, so that with a well-designed database, your application never sees inconsistent, duplicate, orphan, out-of-date, or missing data.

The SQL part of “MySQL” stands for “Structured Query Language”. SQL is the most common standardized language used to access databases. Depending on your programming environment, you might enter SQL directly (for example, to generate reports), embed SQL statements into code written in another language, or use a language-specific API that hides the SQL command.

SQL is defined by the ANSI/ISO SQL Standard. The SQL standard has been evolving since 1986 and several versions exist. In this manual, “SQL-92” refers to the standard released in 1992, “SQL:1999” refers to the standard released in 1999, and “SQL:2003” refers to the current version of the standard. We use the phrase “the SQL standard” to mean the current version of the SQL Standard at any time.

 

 

 

• MySQL software is Open Source.

Open Source means that it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything. If you wish, you may study the source code and change it to suit your needs. The MySQL software uses the GPL (GNU General Public License), http://www.fsf.org/licenses/, to define what you may and may not do with the software in different situations. If you feel uncomfortable with the GPL or need to embed MySQL code into a commercial application, you can buy a commercially licensed version from us. See the MySQL Licensing Overview for more information (http://www.mysql.com/company/legal/licensing/).

• The MySQL Database Server is very fast, reliable, scalable, and easy to use.

If that is what you are looking for, you should give it a try. MySQL Server can run comfortably on a desktop or laptop, alongside your other applications, web servers, and so on, requiring little or no attention. If you dedicate an entire machine to MySQL, you can adjust the settings to take advantage of all the memory, CPU power, and I/O capacity available. MySQL can also scale up to clusters of machines, networked together.

You can find a performance comparison of MySQL Server with other database managers on our benchmark page. See Section 7.1.3, “The MySQL Benchmark Suite”.

MySQL Server was originally developed to handle large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years. Although under constant development, MySQL Server today offers a rich and useful set of functions. Its connectivity, speed, and security make MySQL Server highly suited for accessing databases on the Internet.

• MySQL Server works in client/server or embedded systems.

The MySQL Database Software is a client/server system that consists of a multi-threaded SQL server that supports different backends, several different client programs and libraries, administrative tools, and a wide range of application programming interfaces (APIs).

We also provide MySQL Server as an embedded multi-threaded library that you can link into your application to get a smaller, faster, easier-to-manage standalone product.

• A large amount of contributed MySQL software is available.

MySQL Server has a practical set of features developed in close cooperation with our users. It is very likely that your favorite application or language supports the MySQL Database Server.

 

 

 

 

Chapter II

Relational Database

 

A relational database is a type of database that organizes data into tables, and links them, based on defined relationships. These relationships enable you to retrieve and combine data from one or more tables with a single query.

 

 

 

 

 

 

 

 

Chapter III

Practicing Database

 

 

 

 

Before practice, I want you know that I am using MySQL that bundled in xampp 3. 2. 1.

At first, you must turn on server and MySQL at XAMPP control panel at folder XAMPP located in C partition. Look at the picture below!

 

Figure 1: Go to a path where Xampp control panel located to turn on server (Apache) and database (MySQL)

 

 

 

 

 

 

Click start MySQL and Apache!

Figure 2: Xampp control panel appearance then turn on Apache and MySQL

 

Look at the red rounds on the picture, if MySQL and server (Apache) have turned on, “status change detected running” will be appeared .

Figure 3: Detecting whether Apache and MySQL have turned on or not yet

 

 

 

 

 

 

Before create a database, we have to design all things in database that we are going to create. It called ERD (Entity Relationship Diagram).

I am about to use MS. Visio. In this book, I use MS. Visio 2007. Select the Basic Flowchart (US units).

Figure 4: Using MS. Visio to create ERD

 

 

Design a database like the picture below

Figure 5: ERD (Entity Relationship Diagram)

 

 

 

 

 

 

As we have seen, the database has three tables, ie student, borrow, and book tables.

Student table has no_id as primary key, name, gender, and address fields.

Borrow has no_id and book_id as foreign key, and date fields.

Book has book_id as primary key, title, and author fields.

Furthermore the database has degree relationship or cardinality ie: 1 -> n, n-> 1 which means that a student may borrow more than once, and also a book can be borrowed to many student.

Look at the tables below, I used MS. Excel to describe the tables.

 

Table 1: Table description

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

A field can be defined as the part of a record held in a database, containing specific information. It is a collection of information organized in a way that a computer program can quickly select desired pieces of data. Data type tells what kind of data that value can have. Size means how many characters or numbers can be included. Description means whether the field has pk, fk, or not both of them. PK or primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records.

A primary key’s main features are:

1. It must contain a unique value for each row of data.

2. It cannot contain null values.

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them. NOT NULL means you must provide a value for the fields.

Well, let’s create a database. Open command prompt (CMD) on your Windows!

 

 

Figure 6: Running Command Prompt (CMD) to make a database

 

 

 

 

 

 

 

 

 

 

 

 

If CMD has appeared, follow the commands below to go to MySQL directory!

 

C:\Users\Dewi>CD..

C:\Users>CD..

C:\>cd xampp

C:\xampp>cd mysql

C:\xampp\mysql>cd bin

C:\xampp\mysql\bin>mysql -u root –p

 

If your MySQL doesn’t have a password, just enter when MySQL request you to type a password.

Figure 7: CMD and MySQL commands

 

 

Then, create a database named ‘library’, follow the commands below.

mysql> create database library;

mysql> show databases;

 

 

 

 

 

 

Figure 8: MySQL commands and databases created

 

 

 

mysql> use library;

mysql> create table student(no_id int(10)primary key,name varchar(50) not null,gender enum('G','B'),address varchar(50) not null)type=innodb;

NOTE: YOU MAY DELETE ‘type=innodb’ ON YOUR COMMANDS BECAUSE SOME MySQL IN THE OTHER VERSION HAS INTEGRATED IT AUTOMATICALLY.

mysql> create table book(book_id varchar(5)primary key,title varchar(50) not null,author varchar(50) not null);

 

 

 

 

 

 

 

mysql> create table borrow(no_id int(10),book_id varchar(5),date varchar (10) not null,constraint fkno_id foreign key(no_id) references student(no_id)on update cascade on delete cascade,constraint fkbook_id foreign key(book_id) references book(book_id)on update cascade on delete cascade);

On update cascade on delete cascade means when you delete or update a record in the first table which has a primary key, automatically a record in the other table which has a foreign key and it related, the record in the other table, will be deleted or updated.

mysql> show tables;

‘show tables;’ used to list the tables we have created.

mysql> desc book;

mysql> desc student;

mysql> desc borrow;

Command ‘desc’ used to show structure of the table.

 

Figure 9: MySQL commands and describing each table structure

 

 

 

 

 

 

 

 

To check whether our tables have related, we must check it on a browser. Follow these instructions!

 

Figure 10: Using PHPMyAdmin to check database relationship

 

 

Figure 11: Using PHPMyAdmin to check database relationship

 

 

 

 

 

 

Don’t fill password column if your MySQL doesn’t have a password.

 

Figure 12: Using PHPMyAdmin to check database relationship

 

 

Figure 13: Using PHPMyAdmin to check database relationship

 

 

 

 

 

Figure 14: Using PHPMyAdmin to check database relationship

 

 

Now let’s try to insert a record to aour database, follow these commands.

 

mysql> insert into student(no_id,name,gender,address)values('1106700006','Dewi Mulyani','G','Subang');

mysql> insert into book(book_id,title,author)values('ABCD1','Relational Database (MySQL and CMD)','Dewi Mulyani');

mysql> insert into borrow(no_id,book_id,date)values('1106700006','ABCD1','05/21/2014');

mysql> select *from student;

mysql> select *from book;

mysql> select *from borrow;

 

 

 

 

 

 

 

 

 

Commands ‘select *from (table name);’ used to show data records in the table.

 

Figure 15: List of record in each table

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Chapter IV

List of MySQL Commands

 

USE database_name

     

Change to this database. You need to change to some database when you first connect to MySQL.

 

SHOW DATABASES

     

Lists all MySQL databases on the system.

 

SHOW TABLES [FROM database_name]

     

Lists all tables from the current database or from the database given in the command.

 

DESCRIBE table_name

SHOW FIELDS FROM table_name

SHOW COLUMNS FROM table_name

     

These commands all give a list of all columns (fields) from the given table, along with column type and other info.

 

SHOW INDEX FROM table_name

     

Lists all indexes from this tables.

 

 

 

 

 

 

 

 

 

 

 

SET PASSWORD=PASSWORD('new_password')

     

Allows the user to set his/her own password.

CREATE TABLE table_name (create_clause1, create_clause2, ...)

    

Creates a table with columns as indicated in the create clauses.

 

DROP TABLE table_name

     

Removes the table from the database. Permanently! So be careful with this command!

 

ALTER TABLE table_name ADD (create_clause1, create_clause2, ...)

     

Adds the listed columns to the table.

 

ALTER TABLE table_name DROP column_name

     

Drops the listed columns from the table.

 

ALTER TABLE table_name MODIFY create_clause

     

Changes the type or modifiers to a column. Using MODIFY means that the column keeps the same name even though its type is altered. MySQL attempts to convert the data to match the new type: this can cause problems.

 

ALTER TABLE table_name CHANGE column_name create_clause

     

Changes the name and type or modifiers of a column. Using CHANGE (instead of MODIFY) implies that the column is getting a new name.

 

ALTER TABLE table_name ADD INDEX [index_name] (column_name1, column_name2, ...)

CREATE INDEX index_name ON table_name (column_name1, column_name2, ...)

     

Adds an index to this table, based on the listed columns. Note that the order of the columns is important, because additional indexes are created from all subsets of the listed columns reading from left to write. The index name is optional if you use ALTER TABLE, but it is necesary if you use CREATE INDEX. Rarely is the name of an index useful (in my experience).

INSERT [INTO] table_name VALUES (value1, value2, ...)

     

Insert a complete row of data, giving a value (or NULL) for every column in the proper order.

 

 

 

 

 

 

 

 

 

 

 

INSERT [INTO] table_name (column_name1, column_name2, ...) VALUES (value1, value2, ...)

INSERT [INTO] table_name SET column_name1=value1, column_name2=value2, ...

     

Insert data into the listed columns only. Alternate forms, with the SET form showing column assignments more explicitly.

 

INSERT [INTO] table_name (column_name1, column_name2, ...) SELECT list_of_fields_from_another_table FROM other_table_name WHERE where_clause

     

Inserts the data resulting from a SELECT statement into the listed columns. Be sure the number of items taken from the old table match the number of columns they are put into!

 

DELETE FROM table_name WHERE where_clause

     

Delete rows that meet the conditions of the where_clause. If the WHERE statement is omitted, the table is emptied, although its structure remains intact.

 

UPDATE table_name SET column_name1=value1, column_name2=value2, ... [WHERE where_clause]

     

Alters the data within a column based on the conditions in the where_clause.

 

LOAD DATA LOCAL INFILE 'path to external file' INTO TABLE table_name

     

Loads data from the listed file into the table. The default assumption is that fields in the file are separated by tabs, and each data record is separated from the others by a newline. It also assumes that nothing is quoted: quote marks are considered to be part of the data. Also, it assumes that the number of data fields matches the number of table columns. Columns that are AUTO_INCREMENT should have NULL as their value in the file.

 

LOAD DATA LOCAL INFILE 'path to external file' [FIELDS TERMINATED BY 'termination_character'] [FIELDS ENCLOSED BY 'quoting character'] [LINES TERMINATED BY 'line termination character'] FROM table_name

     

Loads data from the listed file into the table, using the field termination character listed (default is tab \t), and/or the listed quoting character (default is nothing), and/or the listed line termination chacracter (default is a newline \n).

 

SELECT column_name1, column_name2, ... INTO OUTFILE 'path to external file' [FIELDS TERMINATED BY 'termination_character'] [FIELDS ENCLOSED BY 'quoting character'] [LINES TERMINATED BY 'line termination character'] FROM table_name [WHERE where_clause]

 

 

 

 

 

 

 

 

     

Allows you to move data from a table into an external file. The field and line termination clauses are the same as for LOAD above. Several tricky features:

1. Note the positions of the table_name and where_clause, after the external file is given.

2. You must use a complete path, not just a file name. Otherwise MySQL attempts to write to the directory where the database is stored, where you don't have permission to write.

3. The user who is writing the file is 'mysql', not you! This means that user 'mysql' needs permission to write to the directory you specify. The best way to do that is to creat a new directory under your home directory, then change the directory's permission to 777, then write to it. For example: mkdir mysql_output, chmod 777 mysql_output.

GRANT USAGE ON *.* TO user_name@localhost [IDENTIFIED BY 'password']

     

Creates a new user on MySQL, with no rights to do anything. The IDENTIFED BY clause creates or changes the MySQL password, which is not necessarily the same as the user's system password. The @localhost after the user name allows usage on the local system, which is usually what we do; leaving this off allows the user to access the database from another system. User name NOT in quotes.

 

GRANT SELECT ON *.* TO user_name@localhost

     

In general, unless data is supposed to be kept private, all users should be able to view it. A debatable point, and most databases will only grant SELECT privileges on particular databases. There is no way to grant privileges on all databses EXCEPT specifically enumerated ones.

 

GRANT ALL ON database_name.* TO user_name@localhost

     

Grants permissions on all tables for a specific database (database_name.*) to a user. Permissions are for: ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE.

 

FLUSH PRIVILEGES

     

Needed to get updated privileges to work immediately. You need RELOAD privileges to get this to work.

 

SET PASSWORD=PASSWORD('new_password')

     

Allows the user to set his/her own password.

 

 

 

 

 

 

 

 

 

 

REVOKE ALL ON [database_name.]* FROM user_name@localhost

     

Revokes all permissions for the user, but leaves the user in the MySQL database. This can be done for all databases using "ON *", or for all tables within a specific databse, using "ON database_name.*".

 

DELETE FROM mysql.user WHERE user='user_name@localhost'

     

Removes the user from the database, which revokes all privileges. Note that the user name is in quotes here.

 

 

 

 

 

 

 

UPDATE mysql.user SET password=PASSWORD('my_password') WHERE user='user_name'

     

Sets the user's password. The PASSWORD function encrypts it; otherwise it will be in plain text.

 

SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv FROM mysql.user

     

A good view of all users and their approximate privileges. If there is a password, it will by an encrytped string; if not, this field is blank. Select is a very general privlege; insert allows table manipulation within a database; shutdown allows major system changes, and should only be usable by root; the ability to grant permissions is separate from the others.

 

SELECT user, host, db, select_priv, insert_priv, grant_priv FROM mysql.db

     

View permissions for individual databases.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

REFERENCES

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Imprint

Publication Date: 09-21-2014

All Rights Reserved