unit–ii open source database -...
TRANSCRIPT
Unit – IIOpen Source Database
MYSQL
MySQL: Introduction
MySQL is a SQL (Structured Query Language) database server. SQL is the most populardatabase language in the world. MySQL is a client server implementation that consists of aserver daemon mysql and many different client programs/libraries. The main goals ofMySQL are speed and robustness. The base upon which MySQL is built is a set ofroutines that have been used in a highly demanding production environment for manyyears. While MySQL is currently still in development it already offers a rich and highlyuseful function set.
Features of MySQL
• Multi-threaded.• Lots of column types like: signed/unsigned integers 1,2,3,4,8 bytes long, FLOAT,
CHAR, VARCHAR, TEXT, BLOB, DATE, SET and ENUM types.• Join optimizer with one-sweep multi-join (all joins made in one pass).• Full function support in the SELECT and WHERE parts. Example: select
column1+column2 from table where column1/column2 > 0• Full support for SQL GROUP BY and ORDER BY. Support for group functions
(SUM, MAX and MIN).• A privilege and password system with is very flexible and secure. Allows host
based verification.• All password traffic on the net is encrypted.
• The first step in learning MySQL is to understand the difference between adatabase and a database management system (DBMS).
• The term database refers to the entity that stores the actual data (such asID numbers, names and addresses for example) in a structured way.
• A database management system (DBMS) on the other hand, refers to thesoftware used to store, access and manipulate the data stored in thedatabase.
• All interactions with the database are always performed via the DBMS.• There are two flavors of Database Management System (DBMS) known
as shared-file and client-server.• A shared file based DBMS consists of a database access application which
interacts directly with the underlying database files.• These types of database are typically designed for less demanding data
storage needs and are used almost exclusively on desktop computers.• Microsoft Access is a typical example of this category of DBMS. Such
database systems are never used in distributed or enterprise levelenvironments.
• MySQL falls into the client-server DBMS category. A client-server DBMSis split into two components.
• The server component typically resides on the same physical computer asthe database files and is responsible for all interactions with thedatabase.
• The second component is the client. The client sends all database requeststo the server which in turn processes the request and returns the results ofthe request back to the client.
• There are a couple of key advantages to the client-server architectureDBMS.
• Firstly, there is no need for the client to be running on the samecomputer system as the server.
• Instead, requests can be sent by the client over a network or internetconnections to the server on a remote host.
• The fact that the server resides on a remote computer is invisible to theclient user.
• This makes the database available to greater numbers of users than ashared-file DBMS offers.
• Valid clients can be the MySQL tools, applications written in otherprogramming languages such as C, C++ or Java, or web basedapplications developed using languages such as PHP or JSP).
Mysql command-line utility:• mysql is a command-line client tool that is installed as standard with the MySQL
package.• From the mysql command-prompt it is possible to issue a wide range of commands
to the database server such as creating and deleting databases and tables,searching for data, adding new rows and much more.
• For example, if database server requires a username and password to gain accessthe -u and -p command-line options may be used respectively:
root@host# mysql -u root -p ;Enter password :****mysql>
• The above command will prompt for the password for user root before allowingaccess to the database management system.
• If the mysql client is running a different system to the MySQL server, the -h flagmay be used to specify the name of the remote host together with -P to specify theport:
$ mysql -h myDBServer -p 5678;
NOTE: MySQL does not terminate a command until you give a semi colon (;) at theend of SQL command.
Setting Up Account:• You can create user accounts in MySQL by one of two methods: Using the GRANT
statement or using the INSERT statement.• For the purposes of this, will be concentrating solely on the GRANT statement and
its opposite statement, REVOKE.• If you choose to use an INSERT or UPDATE statement for working with the grants
database, you must use the FLUSH PRIVILEGES statement as well.• If you use a GRANT or REVOKE statement, the database server sees the changes
immediately.• If you change a user’s access to a database while they are connected to that
database, the change will not take effect immediately for that user.• In addition, if you change a user’s global privileges or change their password it
will not take effect until the next time they connect.• Therefore, you should kill the user’s process in order for the changes to take effect
for these instances.• The ALL PRIVILEGES macro can be substituted in a GRANT or REVOKE statement
instead of having to enumerate each of the privileges.• The USAGE macro simply creates the user account with no privileges.• In other words, think of the USAGE macro as a placeholder.• It allows the administrator to create the user account and come back to it later to
set privileges.
Starting, terminating and writing your own SQL programs:Administrative MySQL Command:• Here is the list of important MySQL command which you will use time to time to
work with MySQL database:
USE Databasename : This will be used to select a particular database inMySQL workarea.
SHOW DATABASES : Lists the databases that are accessible by theMySQL DBMS.
SHOW TABLES : Shows the tables in the database once a databasehas been selected with the use command.
SHOW COLUMNSFROM tablename : Shows the attributes, types of attributes, key
information, whether NULL is permitted,defaults, and other information for a table.
SHOW INDEX FROMtablename : Presents the details of all indexes on the table,
including the PRIMARY KEY.
SHOW TABLE STATUS : Reports details of the MySQL DBMSperformance and statistics.
USE/CONNECT : Both the USE and CONNECT statements performthe same action; the USE statement was added toMySQL for Sybase compatibility. Use theCONNECT/USE statement to connect to anotherdatabase from in the CLI. The syntax for theCONNECT or USE statements isCONNECT | USE <databasename>
DESCRIBE : The DESCRIBE statement provides much of thesame functionality as the SHOW COLUMNSstatement. The DESCRIBE statement, which isprovided to for Oracle compatibility, can beabbreviated as DESC and can also be utilized withwildcard characters such as underscore (_) andpercent (%). The syntax for the DESCRIBEstatement isDESCRIBE | DESC <tablename> (<columnname> |
<wildcard>)
KILL : The KILL statement destroys a specified thread thatis using the MySQL database server. The syntax forthe KILL statement is KILL <threadnumber>
Getting Information about Users:• The first step securing a database is to find out which users already have access. .• This information is stored, not surprisingly, in a MySQL database called mysql.• The mysql database contains a table called user which in turn contains a number of
columns including the user login name and the users various privileges andconnection rights.
mysql> show databases;mysql> use mysql;mysql> show tables;
• To obtain a list of users run the following command:mysql> SELECT user FROM user;
+----------+| user |+----------+| root |+----------+7 rows in set (0.00 sec )
• To obtain all the information about user table.mysql> SELECT * FROM user;
• A database that is more established will likely contain more users, some ofwhich will have been manually created, and others, as in the case below,created as a result of installing a third-party which uses MySQL.
mysql> select user from user;+----------+| user |+----------+| remote || venkad || phptest || root |+----------+7 rows in set (0.00 sec)
Creating a New MySQL User :• In order to add a new user account it is necessary to use the CREATE USER
statement. The creation of a new user account requires the user loginname and an optional password. Regardless of the fact that the passwordis optional, it is unwise to add a new account without a password.
• The syntax for creating a user account is as follows:mysql>CREATE USER user name IDENTIFIED BY 'password';
• For example, to create a new account for a user called student which is protectedby a password we can issue the following statement:
mysql> CREATE USER ‘student'@'localhost' IDENTIFIED BY ‘cse';• We can verify the new user has been added by querying the user table:
mysql> SELECT host, user, password FROM user WHERE user=‘student';+-----------+-------+------------------+| host | user | password |+-----------+-------+------------------+| localhost | student | 2c7ed55a48a81f36 |+-----------+-------+------------------+1 row in set (0.00 sec)
• As we can see, the password is not stored in plain text in the user table and hasinstead been encrypted by MySQL so that it cannot be obtained simply byperforming a SELECT query on the table.
• You may have noted that we specified that student could only connect from'localhost', in other words the same system on which the MySQL server is running.
• This means that if student tries to connect to the MySQL server from a clientrunning on a remote system, the connection will fail.
• In order to create an account that can connect from a particular host, simply specifythe host name or IP address in place of the localhost in the above example.
• Alternatively, to allow a user to connect to the MySQL server from any remotehost, simply use the '%' character in place of the host name:
mysql> CREATE USER ‘student'@'%' IDENTIFIED BY ‘cse';
Deleting a MySQL User:• An existing user account may be deleted using the DROP USER statement, the
syntax for which is:mysql>DROP USER user name;
• For example:mysql>DROP USER ‘student'@'localhost';
Renaming a MySQL User• The account name of a MySQL user can be changed using the RENAME USER
statement, the syntax of which is:mysql> RENAME USER user name TO new user name;
• For example:mysql> RENAME USER ‘student'@'localhost' TO ‘studentcse'@'localhost';
Changing the Password for a MySQL User:• The password assigned to a user account can be changed using the SET PASSWORD
statement.• To change the password for your own account, use the following syntax:
mysql> SET PASSWORD = Password('newpassword');• To alter the password for another user, simply include the user account name in a
SET PASSWORD FOR statement.• For example, to change the password of our example account:
mysql>SET PASSWORD FOR ‘student'@'localhost' = Password('newpassword');
User Privileges:• A newly created user can log into the MySQL server but by default has no
privileges to do anything once connected.• The next task after creating a new user account, therefore, is to add privileges to
the account.• This is achieved using the GRANT statement.• Before modifying a user's privileges it can be helpful to see what privileges are
already set.• This can be performed using the SHOW GRANTS statement in conjunction with the
user's account name.
• For example:mysql> SHOW GRANTS FOR ‘student'@'localhost';+------------------------------------------------------------------------+| Grants for student@localhost |+------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘student'@'localhost' IDENTIFIED BY
PASSWORD '3a9eb1070a0130ca' |+------------------------------------------------------------------------+1 row in set (0.00 sec)
• The statement 'USAGE ON *.*' indicates that the user has no privileges on anydatabase or table.
• Simply put, the user cannot do anything once logged into the database server.• To add a privilege, for example permission to query any table in a database named
MySampleDB, we would issue the following command:• The syntax is
GRANT privilege [(<columnlist>)] [, privilege [(<columnlist>)] ...] ON{<tablename> | * | *.* | <databasename>.*} TO username(@<host>)[IDENTIFIED BY ‘password’] [REQUIRE [{SSL| X509}] [CIPHER cipher [AND]][ISSUER issuer [AND]] [SUBJECT subject]] [WITH GRANT OPTION]
• For examplemysql> GRANT SELECT on MySampleDB.* TO 'johnB'@'localhost';
• Once executed, the above statement will enable user ‘student' to perform SELECTstatements on any table contained in the MySampleDB database.
• Similarly we could enable student to INSERT rows into a table called productcontained in the MySampleDB database as follows:mysql>GRANT INSERT on MySampleDB.product TO ‘student'@'localhost';
• It is also perfectly valid to specify multiple privileges in a single GRANT statement,for example:
mysql>GRANT INSERT, UPDATE on MySampleDB.product TO‘student'@'localhost';
Deleting users and revoking privilege:• The REVOKE statement is used to remove privileges from a user.• The syntax for the REVOKE statement is as follows:
REVOKE privilege [(<columnlist>)] [, privilege [(<columnlist>)] ...] ON[<tablename> | * | *.* | <databasename>.*] FROM username [, username ...]
• For example,mysql>REVOKE INSERT on MySampleDB.product TO 'johnB'@'localhost';
• The syntax is quite similar to that of the GRANT statement.• The ALL PRIVILEGES macro works with the REVOKE statement the same as with
the GRANT statement.• Issuing a REVOKE statement does not delete the user from the MySQL grants
database.• To delete a user from the grants database you must specifically issue a DELETE
statement to remove them from the grants database.
Setting Up a MySQL User Accounts:
For adding a new user to MySQL you just need to add a new entry to user table indatabase mysql. Below is an example of adding new user guest with SELECT, INSERTand UPDATE privileges with the password guest123 the SQL query is :mysql> use mysql;Database changed
mysql> INSERT INTO user (host, user, password, select_priv, nsert_priv,update_priv) VALUES ('localhost', 'guest', PASSWORD('guest123'), 'Y', 'Y', 'Y');
mysql> FLUSH PRIVILEGES;
mysql> SELECT host, user, password FROM user WHERE user = 'guest';+-----------+---------+------------------+| host | user | password |+-----------+---------+------------------+| localhost | guest | 6f8c114b58f2ce9e |+-----------+---------+------------------+
When adding a new user remember to encrypt the new password using PASSWORD()function provided by MySQL. As you can see in the above example the password mypassis encrypted to 6f8c114b58f2ce9e. Notice the FLUSH PRIVILEGES statement. This tells theserver to reload the grant tables.
If you don't use it then you won't be able to connect to mysql using the new user accountat least until the server is rebooted. You can also specify other privileges to a new user bysetting the values of following columns in user table to 'Y' when executing the INSERTquery or you can update them later using UPDATE query.
Another way of adding user account is by using GRANT SQL command; Followingexample will ada user zara with password zara123 for a particular database calledTUTORIALS.
This will also create an entry in mysql database table called user.NOTE: MySQL does not terminate a command until you give a semi colon (;) at the endof SQL command.
The /etc/my.cnf File Configuration:Most of the cases you should not touch this file. By default it will have following entries:Here you can specify a different directory for error log, otherwise you should not changeany entry in this table.
• MySQL supports a wide range of privileges which are outlined in the followingtable:
Enable the use of SHOW CREATE VIEWSHOW VIEW
Enable privileges to be grantedGRANT OPTION
Synonym for no privilegesUSAGE
Enable the use of UPDATEUPDATE
Enable the use of mysqladmin shutdownSHUTDOWN
SHOW DATABASES shows all databasesSHOW DATABASES
Enable the use of SELECTSELECT
Enable the use of the FLUSH statementRELOAD
Enable the use of INSERTINSERT
Enable the use of CREATE INDEX and DROP INDEX statementsINDEX
Enable the use of SELECT INTO OUTFILE and LOAD DATA INFILEFILE
Enable the user to execute stored routinesEXECUTE
Enable the use of DROP TABLE statementDROP
Enable the use of DELETE statementDELETE
Enable the use of CREATE TABLE statementCREATE
Enable the use of ALTER TABLE statementALTER
Sets all simple privileges except GRANT OPTIONALL [PRIVILEGES]
DescriptionSetting
MySQL Data Types
In MySQL there are three main types :1. text2. number3. Date/Time
Data Definition Language
Data Definition Language (DDL) refers to the statements and functions used to create theframework and rules for working with data. Using a DDL statement, you don’t work withdata directly; rather, you create or alter the database itself (or the tables in the database).
Creating tables
The syntax for the CREATE TABLE statement is
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <tablename>[(<create_statement>,...)] [table_options] [select_statement]
As you can see the statement is quite simple on its face. However numerous options, manyof which are beyond the scope of this chapter, make the CREATE TABLE statementpowerful. The create_statement portion of the statement is where you include specificinformation such as column names and types, primary keys, indexes, and constraints. Thesample CREATE TABLE statements for the ecommerce database (shown later in thechapter) give you a look at column types and some of their options. One frequently askedquestion—and source of confusion—is the AUTO_INCREMENT table option. You canadd the AUTO_INCREMENT keyword as a column option when creating a table. A tablecan have only one AUTO_INCREMENT column; that column must be a key as well.
When you insert data into a table with an AUTO_INCREMENT column, the databaseautomatically adds 1 to the last value of the auto-incremented column. By default, theAUTO INCREMENT value starts with 1. If you want the database to start with a differentinitial value, you can add this preference as a table option when you create the table.
CREATE TABLE example (ID INT AUTO_INCREMENT PRIMARY KEY)AUTO_INCREMENT = 10;
In the previous example, the table would be created and the first value inserted into thetable would receive a value of 10 in the ID column.
Deleting tables
The syntax for dropping a table isDROP TABLE [IF EXISTS] <tablename> (, <tablename>)
The IF EXISTS keywords can be given to prevent an error if the table doesn’t exist. Youcan also delete more than one table with the command, as indicated by the(, <tablename>) syntax.
Delete From
Syntax:DELETE [LOW PRIORITY or QUICK] FROM table_name [WHEREwhere_definition] [ORDER BY order_definition] [LIMIT row_value]
The DELETE FROM command removes data from table_name. If the LOW PRIORITYargument is specified, the delete process waits until there is no client access to the table. Ifthe QUICK argument is specified, there will be no merging of table leaves during theprocess. DELETE commands with no WHERE arguments delete all data housed in thegiven table. The ORDER BY clause can be issued in conjunction with the LIMIT clause toremove a certain number of records according to their order within the table.Altering tablesInevitably there comes in time in all databases’ lives when their tables will need to bealtered. This can happen for any number of reasons—including a change in business rules,an upgrade to an application or an oversight during the design of the database. The syntaxfor the ALTER TABLE statement is as follows:ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]alter_specification:ADD [COLUMN] create_definition [FIRST | AFTER column_name ]or ADD PRIMARY KEY (index_col_name,...)or CHANGE [COLUMN] old_col_name create_definitionor MODIFY [COLUMN] create_definitionor DROP [COLUMN] col_name or DROP PRIMARY KEY/INDEX
Data Manipulation Language
INSERT INTO
Syntax
INSERT [LOW_PRIORITY or DELAYED] [IGNORE] INTO table_name (column_list)VALUES (value_list)
The INSERT INTO command is the basic mechanism for inserting data into a table. If theLOW PRIORITY argument is specified, the insert will be delayed until there is no clientaccess to the designated table. If the DELAYED argument is specified, clients are allowedto continue as the inserts are bundled for one mass insert. The DELAYED option mayimprove speed for large inserts. If the IGNORE argument is specified, inserts that wouldduplicate the insertion of a primary key are ignored. Without the IGNORE option (given aduplicate primary key), the insert terminates.
Example:
insert into manufacturer (ID,name,address,zip,area_code,telephone_number) values(1,’Small Widget Manufacturer’,’4 Warner Blvd’,’91120’,’818’,’5551212’);
Query Record Selection Technology
By and large, the SELECT statement is used more frequently than the INSERT statement.Normally you would use the INSERT statement to put data into the database only once; aquery for looking for data uses the SELECT statement more than once. Theactual ratio of queries to inserts is largely determined by the application.
The syntax for the SELECT statement is as follows:
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT][SQL_BUFFER_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL]select_expression,... [INTO {OUTFILE | DUMPFILE} ‘file_name’ export_options][FROM table_references [WHERE where_definition] [GROUP BY {unsigned_integer |col_name | formula} [ASC | DESC], ...] [HAVING where_definition]
Example:
SELECT * FROM <tablename>;
SELECT * FROM user WHERE user = ‘sing’;
SELECT * FROM user WHERE user LIKE ‘sue%’;
UNION
SyntaxSELECT select_clause UNION [ALL] SELECT select_clause [UNIONSELECT select_clause ...]
The UNION command allows combinations of multiple SELECT statements intoa single return. In the select_clause syntax, only the last statement may contain a declaredORDER BY or INTO OUTFILE modifier. Identical row data can be returned unless the ALLoption is specified.
UPDATE
Synatx
UPDATE [LOW_PRIORITY] [IGNORE] table_name SET column1=value1 [,column2=value2, ...] [WHERE where_definition] [LIMIT row_value]
Example:
UPDATE product SET name = ‘Van Halen - Balance’ WHERE ID = 1;
STRING FUNCTION
• ASCII(C) Returns the ASCII value of the given character, C.
• CHAR (N, N) Returns the actual character when given an ASCII number(s),N. CONCAT (S1, S2...) Concatenates (joins) the strings, S1, S2 and so on.
• CONCAT_WS (S, S1, S2 ...) concatenates the strings S1, S2 and so on usingseparator, S.
• FIELD (S, S1, S2,) Returns the number indicating the position of a string, S,as located in the field list, S1, S2, and so on.
• LCASE (S) Converts string, S, into lowercase.
• LENGTH (S) Returns the character length of string, S.
• LIKE Compares strings via pattern matching.
• LOAD_FILE (F) Loads the contents of file, F, as a string.
• LPAD (S, N, C) Left-pads the string, S, with the number, N, of characters, C.
• LTRIM (S) Trims whitespace from the left of string, S.
• POSITION (S in S1) Determines the first position of substring, S, in string, S1.• REGEXP Compares strings with regular expression pattern matching.• REPLACE(S,O,N) Replaces old string, O, with new string, N, in a given string, S.• REVERSE(S) Reverses string, S.• RPAD(S,N,C) Right-pads the string, S, with the number, N, of characters, C.• RTRIM(S) Trims whitespace from the right of string, S.• SPACE(N) Returns a string consisting of N spaces.• STRCMP(S1,S2) Compares the two strings, S1 and S2.• TRIM((RS) from S) Remove string, RS, from string, S (see example following this
list)• UCASE(S) Converts string, S, into uppercase.
DATE AND TIMEMySQL includes many functions to work with dates and times. These include functions forconversion of dates and times and functions to determine where a given date falls in ayear.
Date and time functions with the SELECT statement
Function Name PurposeCURDATE() Returns the current date.CURTIME() As with CURDATE, this returns the current
time.DATE_ADD(D, interval) Adds <interval> onto date, D.DATE_FORMAT(D, format) Returns the date in the format specified,
Function Name PurposeDATE_SUB(D, interval) Subtracts <interval> from date, D.DAYNAME(D) Returns the day of the week for a given
date in time.DAYOFMONTH(D) Returns the day of month for a given
date, D.DAYOFWEEK(D) Returns the day of the week that a given
date falls upon.DAYOFYEAR(D) Returns the day of the year for a given
date.FROM_DAYS(D) Returns the actual date that is a number
of days, D, away.FROM_UNIXTIME(S) Converts a Unix/Linux timestamp from
native seconds to a date.
HOUR(T) Return the hour from a given time value,T.
MINUTE(T) Returns the minute value from a giventime, T.
MONTH(D) Returns the month value from a givendate, D.
MONTHNAME (D) Returns the name of the month from agiven date, D.
NOW() Returns the current date and timestamp.
Function Name Purpose
PERIOD_ADD(D,M) Adds the number of months, M, to thegiven date, D.
PERIOD_DIFF(D1, D2) Subtracts the two dates, D1 and D2.QUARTER(D) Returns the quarter of the year for the
given date, D.SECOND(T) Returns the second value from a given
time, T.SEC_TO_TIME(S) Converts seconds, S, to a time value.TIME_TO_SEC(T) Converts a given time, T, to seconds.
MySQL Sorting ResultsWe have seen SQL SELECT command to fetch data from MySQL table. When
you select rows, the MySQL server is free to return them in any order, unless you instructit otherwise by saying how to sort the result. But you sort a result set by adding an ORDERBY clause that names the column or columns you want to sort by.Syntax:
Here is generic SQL syntax of SELECT command along with ORDER BY clause tosort data from MySQL table:
SELECT field1, field2,...fieldN table_name1, table_name2.ORDER BY field1,[field2...] [ASC [DESC]]
• You can sort returned result on any field provided that filed is being listed out.• You can sort result on more than one field.• You can use keyword ASC or DESC to get result in ascending or descending
order. By default its ascending order.• You can use WHERE...LIKE clause in usual way to put condition.
WORKING WITH METADATA:
To manipulate data, you need information about it—metadata. Database metadata forexample, is information about the database and the data it contains—for example, thenumber of columns returned from a query.
<?
//getting metadata on MySQL databases//output the structure of a table
$connection_1 = mysql_connect("localhost");
$fields = mysql_list_fields("cmphp","rights");
for($i=0;$i<mysql_num_fields($fields);$i++){
echo mysql_field_name($fields,$i)." (".mysql_field_len($fields,$i).") –
".mysql_field_type($fields,$i)."<br>";}mysql_close;
//show the structure of ALL tables in ALL databases on the server$server_connection_1 = mysql_connect("localhost");$databases = mysql_query("SHOW DATABASES");while($database = mysql_fetch_row($databases)){
echo '<h2>DATABASE: '.$database[0].'</h2>';$database_connection_1 = mysql_select_db($database[0]);$tables = mysql_query("SHOW TABLES");while($table = mysql_fetch_row($tables)){
echo '<table border="1" cellpadding="5" width="500">';echo '<tr><td colspan="3" bgcolor="silver">TABLE:
'.$table[0].'</td></tr>';$fields = mysql_list_fields($database[0],$table[0]);
for($i=0;$i<mysql_num_fields($fields);$i++){
echo '<tr>';echo '<td>'.mysql_field_name($fields,$i)."</td>";echo '<td>'.mysql_field_len($fields,$i)."</td>";echo '<td>'.mysql_field_type($fields,$i)."</td>";
echo '</tr>';}echo '</table><br>';
}}mysql_close;
//show the nice structure of a particular database$server_connection_1 = mysql_connect("localhost");$the_database = "cmphp";echo '<h2>DATABASE: '.$the_database.'</h2>';$database_connection_1 = mysql_selectdb($the_database);$tables = mysql_query("SHOW TABLES");while($table = mysql_fetch_row($tables)){
echo '<table border="1" cellpadding="5" width="600">';echo '<tr><td colspan="4" bgcolor="silver"><b>TABLE: '.$table[0].'</b></td></tr>';echo '<tr><td bgcolor="silver">NAME</td><td bgcolor="silver">SIZE</td><td>bgcolor="silver">TYPE</td><td bgcolor="silver">EXAMPLE</td></tr>';$fields = mysql_list_fields($the_database,$table[0]);for($i=0;$i<mysql_num_fields($fields);$i++){
echo '<tr>';echo '<td>'.mysql_field_name($fields,$i)."</td>";echo '<td>'.mysql_field_len($fields,$i)."</td>";echo '<td>'.mysql_field_type($fields,$i)."</td>";$rows = mysql_query("SELECT ".mysql_field_name($fields,$i)." FROM
".$table[0]." LIMIT 1");$row = mysql_fetch_array($rows);echo '<td bgcolor="eeeeee">'.$row[0].' </td>';
echo '</tr>';}
echo '</table><br>';}mysql_close;?>
Using Sequences
Sequence is a set of integers 1, 2, 3, ... that are generated in order on demand. Sequencesare frequently used in databases because many applications require each row in a table tocontain a unique value, and sequences provide an easy way to generate them. This chapterdescribes how to use sequences in MySQL.
Using AUTO_INCREMENT column:The simplest way in MySQL to use Sequences is to define a column asAUTO_INCREMENT and leave rest of the things to MySQL to take care.
Example:Try out following example. This will create table and after that it will insert few rows inthis table where it is not required to give record ID because its auto incremented byMySQL.
mysql> CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id), name VARCHAR(30) NOT NULL, date DATE NOT NULL,origin VARCHAR(30) NOT NULL );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES (NULL,'housefly','2001-09-10','kitchen'),
mysql> INSERT INTO insect (id,name,date,origin) VALUES (NULL,'millipede','2001-09-10','driveway'),
Query OK, 3 rows affected (0.02 sec)
mysql> SELECT * FROM insect ORDER BY id;+----+-------------+------------+------------+| id | name | date | origin |+----+-------------+------------+------------+| 1 | housefly | 2001-09-10 | kitchen || 2 | millipede | 2001-09-10 | driveway || 3 | grasshopper | 2001-09-10 | front yard |+----+-------------+------------+------------+
Obtain AUTO_INCREMENT Values:
LAST_INSERT_ID( ) is a SQL function, so you can use it from within any client thatunderstands how to issue SQL statements. otherwise PERL and PHH scripts provideexclusive functions to retrieve auto incremented value of last record. Renumbering anExisting Sequence:There may be a case when you have deleted many records from a table and you want toresequence all the records. This can be done by using a simple trick but you should be verycareful to do so if your table is having join with other table. If you determine thatresequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to dropthe column from the table, then add it again. The following example shows how torenumber the id values in the insect table using this technique:
mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect -> ADD id INT UNSIGNED NOT NULLAUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);
Starting a Sequence at a Particular Value:
By default MySQL will start sequence from 1 but you can specify any other number as wellat the time of table creation. Following is the example where MySQL will start sequencefrom 100.
mysql> CREATE TABLE insect ( id INT UNSIGNED NOT NULL AUTO_INCREMENT =100,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,originVARCHAR(30) NOT NULL);
Alternatively, you can create the table and then set the initial sequence value with ALTERTABLE.
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
MySQL Date Functions
The following table lists the most important built-in date functions in MySQL:
Displays date/time data in different formatsDATE_FORMAT()Returns the number of days between two datesDATEDIFF()Subtracts a specified time interval from a dateDATE_SUB()Adds a specified time interval to a dateDATE_ADD()Returns a single part of a date/timeEXTRACT()Extracts the date part of a date or date/time expressionDATE()Returns the current timeCURTIME()Returns the current dateCURDATE()Returns the current date and timeNOW()
DescriptionFunction
YEAR_MONTH
DAY_HOUR
DAY_MINUTE
DAY_SECOND
DAY_MICROSECOND
HOUR_MINUTE
HOUR_SECOND
HOUR_MICROSECOND
MINUTE_SECOND
MINUTE_MICROSECOND
SECOND_MICROSECOND
YEAR
QUARTER
MONTH
WEEK
DAY
HOUR
MINUTE
SECOND
MICROSECOND
Unit Value
Seconds (00-59)%s
Seconds (00-59)%S
Time, 12-hour (hh:mm:ss AM or PM)%r
AM or PM%p
Month, numeric (00-12)%m
Month name%M
Hour (1-12)%l
Hour (0-23)%k
Day of year (001-366)%j
Minutes, numeric (00-59)%i
Hour (01-12)%I
Hour (01-12)%h
Hour (00-23)%H
Microseconds%f
Day of month, numeric (0-31)%e
Day of month, numeric (00-31)%d
Day of month with English suffix%D
Month, numeric%c
Abbreviated month name%b
Abbreviated weekday name%a
DescriptionFormat
Year, two digits%y
Year, four digits%Y
Year of the week where Monday is the first day of week, four digits, used with %v%x
Year of the week where Sunday is the first day of week, four digits, used with %V%X
Day of the week (0=Sunday, 6=Saturday)%w
Weekday name%W
Week (01-53) where Monday is the first day of week, used with %x%v
Week (01-53) where Sunday is the first day of week, used with %X%V
Week (00-53) where Monday is the first day of week%u
Week (00-53) where Sunday is the first day of week%U
Time, 24-hour (hh:mm:ss)%T
Databases and Web Interfaces(What you need to get started)
A. Requirements for a Database WebInterface
B. Where to Put Your Database and ScriptsC. Server-Side Scripting Languages
• ASP• Cold Fusion• Perl• PHP
A. Requirements for a DatabaseWeb Interface
• Your database (Access, MySQL)• A Web server with appropriate RDBMS• A way of connecting the two
(Common Gateway Interface – CGI –scripts and SQL)
• Security concerns
Active Server Pages (ASP)
• When a browser calls an ASP document, theASP Server reads the .asp document and
1. Substitutes appropriate files for the (server-side)include statements
2. Runs the ASP code (Visual Basic Script …)3. Returns the resulting HTML code to the browser
ASP Key Points• ASP code enclosed in: <% VBScript code %>• Everything outside is HTML• The result of the combined HTML and ASP code
must be a “standard” HTML document, e.g.– <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Final//EN">
<html><head><title>Miracle Drug Study</title><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"><meta name="Description" content=""><meta name="Keywords" content=""><link rel=STYLESHEET type="text/css" href=""></head><body></body></html>
ASP Key Points
• Connect with database:– Create connection object:
• set conn = Server.CreateObject("ADODB.Connection")
– Open connection:• conn.open("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=f:\web\database\rescomp\study.mdb")
• Submit a (read-only) Query:– Generate SQL statement:
• SQL = "SELECT FirstName, LastName, DOB, Gender FROM PatientsWHERE Gender = '" & Gender & "' ORDER BY FirstName DESC"
– set Patients = conn.execute(SQL)
ASP Key Points (4)• Add to or edit table (continued):
– Create new record, Edit, & Update:• RS.AddNew
RS(“Dosage”) = 200RS.Update
– Or Find desired record, Edit, & Update :• do while NOT RS.eof
if RS(“ID”) = 7 thenRS(“Dosage”) = 200RS.Update
elseRS.MoveNext
end ifloop
ASP Security
• Apart from various Internet InformationServices (IIS – Window’s Web service)security holes (for viruses and worms),security is quite good.
• Use https:// if you want to protect contentover the internet – provides Secure SocketLayer (SSL) security
ColdFusion• Easy-to-learn Server-Side Scripting Language:
CFML, or Cold Fusion Markup Language, isembedded in HTML code
• CF code is enclosed in or by CF tags:– <CFtagname CF code >– <Cftagname > CF Code </Cftagname >
• Documents must end in .cfm• ColdFusion is Case Insensitive
ColdFusion Key Points
• All #variables# are enclosed in # signs• HTML output which includes of CF
variables must be surrounded by CFoutput tags; e.g.:– <Cfset height = “tall”>
<CFoutput>The <B>#height#</B> boy fell.<Cfoutput>
ColdFusion Key Points
• Connect with database and run querysimultaneously:
– <CFQUERY Name="Patients" dbtype="dynamic"connectstring="#DBdriver# #DBfile#">SELECT ID, FirstName, LastNameFROM PatientsORDER BY FirstName</CFQUERY>
Where the variables are defined beforehand:– <CFset Dbdriver = "Driver={MICROSOFT ACCESS DRIVER (*.mdb)};
UID=admin; PWD=; dbq=">– <CFset Dbfile = "f:\web\database\rescomp\study.mdb">
ColdFusion Key Points• Access Query Results
– <SELECT name="PatientID"><CFoutput QUERY="Patients">
<OPTION value=#ID#>#FirstName# #LastName#</CFoutput></SELECT>
• Insert Data from a Form– If a HTML form submits variables to be inserted, do so directly
using CFinsert:• <CFinsert tablename="Treatment" dbtype="dynamic"
connectstring="#DBdriver# #DBfile#">All variables in the form object (e.g. Form.var1) that matchattributes in the table are inserted into the table automatically
ColdFusion Key Points
• Insert Data using Cfquery (SQL):– <CFquery name="Treatment" dbtype="dynamic"
connectstring="#DBdriver# #DBfile#">INSERT into TreatmentVALUES (#PatientID#, #EventID#, Now(), #Dosage(mg)#,#Severity#, #Time#)</CFquery>
Practical Extraction andReport Language (Perl)
• Ubiquitous– Originally designed to be a better general
purpose tool than a Unix shell, it has grownand spread to be supported from Windowsto Macintosh to VMS.
• Powerful but Cryptic
Perl Key Points
• The file itself must end in “.cgi” or “.pl”• First line must specify the location of the
Perl engine (The DBI module will not workfor “#!/usr/local/bin /perl[5]” – seebelow):– #!/uva/bin/perl -w
• First printed line must be the following ifyou want its response to go to a browser:– print "Content-type: text/html\n\n";
Perl Key Points
Modules• You *must* use the DBI module which
allows you to interface with the database(DBI link 1 & DBI link 2)– use DBI;
• You can (should?) also make use of theCGI module– use CGI;
Perl Key Points
• Set the usual parameters:– my $hostname = "dbm1.itc.virginia.edu";
my $username = "dld5s"; # "my" defines a local variablemy $password = "ias!";my $database = $username . "_study"; # = dld5s_studymy $data_source = "DBI:mysql:$database:$hostname";
• Connect to the database:– my $dbh = DBI->connect($data_source, $username, $password)
or die "Can't connect to $data_source: $DBI::errstr\n";
Perl Key Points
• Define the SQL statement and execute– my $SQL = "SELECT FirstName, LastName, DOB, Gender
FROM PatientsWHERE Gender = '$Gender‘ORDER BY FirstName DESC";
my $sth = $dbh->prepare($SQL)or die "Unable to prepare $SQL: dbh->errstr\n";$sth->execute or die "Unable to execute query: $dbh->errstr\n";
• Clean up– $sth->finish;
$dbh->disconnect;
Perl Security• Perl/MySQL can be made secure apart from
one serious flaw as implemented at UVa:– Because web files must be readable by the world
(unix permissions), anyone with an account on theserver where you run the php code can see thecode, including your MySQL $password!
– A couple of exceptions to this flaw are as follows:• If you secure your server so that there are no other users
on it• (It may** be possible to compile Perl scripts into binary
executables using perlcc. Then you must hide or removeyour source code containing the MySQL password)
• One other possible poor to fair workaround: use .htaccessto password protect your php directory (limited access)
– See the passwords link, security links page
PHP: Hypertext Preprocessor(PHP)
• HTML embedding scripting language(see the PHP online manual
• When a browser calls a PHP document,the Server reads the PHP document and– Runs the PHP code– Returns the resulting HTML code to the
browser• Example (code)
PHP Key Points
• Filename must end in .php or .phtml• PHP code enclosed in <?php PHP code ?>
or <? PHP code ?>• Everything outside is HTML• Output is (generally) to a browser
requiring standard HTML
PHP Key Points
Connecting with RDBMS and editing, adding, anddeleting databases therein are all done throughPHP functions
• Connect with MySQL RDBMS– mysql_connect($hostName, $userName, $password)
or die("Unable to connect to host $hostName");
• Connect with database– mysql_select_db($dbName) or die("Unable to select
database $dbName");
PHP Key PointsQueries: Nearly all table interaction and
management is done through queries:• Basic information searches
– $SQL = "SELECT FirstName, LastName, DOB, GenderFROM Patients WHERE Gender = '$Gender‘ ORDERBY FirstName DESC";$Patients = mysql_query($SQL);
• Editing, adding, and deleting records and tables– $SQL = "INSERT INTO Patients (FirstName,
LastName) VALUES('$firstName', '$lastName')";$Patients = mysql_query($SQL);
PHP Key Points• Cleaning up: close the database connection
– mysql_close();
PHP/MySQL Security• The same problems as PHP occur with Perl if you run it as a Perl
or CGI script.– See the passwords link
END