Download
1 / 23
230 likes | 450 Views
介紹. 元智大學電機工程所 碩士班一年級 蕭觀華 學號 :917152. MySQL 介紹大綱. What is MySQL ? How to install on Linux Tutorial Introduction Database Administration MySQL Perl API Q&A. What is MySQL ?. MySQL, the most popular Open Source SQL database, is developed, distributed and supported by MySQL AB.
E N D
介紹 元智大學電機工程所 碩士班一年級 蕭觀華 學號:917152
MySQL介紹大綱 • What is MySQL ? • How to install on Linux • Tutorial Introduction • Database Administration • MySQL Perl API • Q&A
What is MySQL ? MySQL, the most popular Open Source SQL database, is developed, distributed and supported by MySQL AB.
How to Install on Linux • Quick Standard Installation of MySQL ( by RPM ) • Installing a MySQL Source Distribution
Quick Standard Installation of MySQL How to get RPM file: Go to here : http://www.mysql.com/downloads/ To see all files in an RPM package, run: shell> rpm -qpl MySQL-VERSION.i386.rpm To perform a standard minimal installation, run: shell> rpm -i MySQL-VERSION.i386.rpm MySQL-client-VERSION.i386.rpm To install just the client package, run: shell> rpm -i MySQL-client-VERSION.i386.rpm
Installing a MySQL Source Distribution How to get Source packages : http://www.mysql.com/Downloads/MySQL-3.23/mysql-3.23.52.tar.gz shell> groupadd mysql shell> useradd -g mysql mysql shell> gunzip < mysql-VERSION.tar.gz | tar -xvf shell> cd mysql-VERSION shell> ./configure --prefix=/usr/local/mysql shell> make shell> make install shell> scripts/mysql_install_db shell> chown -R root /usr/local/mysql shell> chown -R mysql /usr/local/mysql/var shell> chgrp -R mysql /usr/local/mysql shell> cp support-files/my-medium.cnf /etc/my.cnf shell> /usr/local/mysql/bin/safe_mysqld --user=mysql &
MySQL Data environment on Linux • RPM: The RPM places data in `/var/lib/mysql'. The RPM also creates the appropriate entries in `/etc/rc.d/' to start the server automatically at boot time. • Source: The data in `/usr/local/var/mysql'. We will creates the appropriate entries in `/etc/rc.d/init.d/rc.local' to start the server automatically at boot time. write this line in `/etc/rc.d/init.d/rc.local' file: /usr/local/mysql/bin/safe_mysqld --user=mysql &
MySQL Tutorial Introduction • Connecting to the Server • Entering Queries • Creating and Using a Database • Getting Information About Databases and Tables
Connecting to the Server shell> mysql -h host -u user -p Enter password: ******** The ******** represents your password; enter it when mysql displays the Enter password: prompt. If that works, you should see some introductory information followed by a mysql> prompt: shell> mysql -h host -u user -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 459 to server version: 3.22.20a-log Type 'help' for help. mysql>quit
Entering Queries Here's a simple command that asks the server to tell you its version number and the current date. Type it in as shown here following the mysql> prompt and press Enter: mysql> SELECT VERSION(), CURRENT_DATE; +--------------+--------------+ | VERSION() | CURRENT_DATE | +--------------+--------------+ | 3.22.20a-log | 1999-03-19 | +--------------+--------------+ 1 row in set (0.01 sec) mysql>
Creating and Using a Database(1) CREATE DATEBASE sample_DB USE sample_DB You can create the example table as: CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer)); INSERT INTO shop VALUES (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69), (3,'D',1.25),(4,'D',19.95);
Creating and Using a Database(2) Okay, so the example data is: mysql> SELECT * FROM shop; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | A | 3.45 | | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.45 | | 0003 | C | 1.69 | | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+
Getting Information About Databases and Tables(1) mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | menagerie | +------------+ mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | event | | pet | +---------------------+
Getting Information About Databases and Tables(1) mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
Database Administration • Configuring MySQL • MySQL User Account Management
Configuring MySQL(1) `my.cnf' Option Files :/etc/my.cnf Here is a typical global option file: [client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock set-variable = key_buffer_size=16M set-variable = max_allowed_packet=1M [mysqldump] quick
Configuring MySQL(2) Here is typical user option file: [client] # The following password will be sent to all standard MySQL clients password=my_password [mysql] no-auto-rehash set-variable = connect_timeout=2 [mysqlhotcopy] interactive-timeout
MySQL User Account Management • Global level • Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. (user logon to mysql server) • Database level • Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables. • Table level • Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. • Column level • Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table.
User Account Management (1) shell> mysql --user=root mysql mysql> INSERT INTO user (Host,User,Password) -> VALUES('localhost','custom',PASSWORD('stupid')); mysql> INSERT INTO user (Host,User,Password) -> VALUES('server.domain','custom',PASSWORD('stupid')); mysql> INSERT INTO user (Host,User,Password) -> VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
User Account Management (2) mysql> INSERT INTO db -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, -> Create_priv,Drop_priv) -> VALUES -> ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO db -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, -> Create_priv,Drop_priv) -> VALUES -> ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO db -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, -> Create_priv,Drop_priv) -> VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y'); mysql> FLUSH PRIVILEGES;
MySQL Perl API DBI with DBD::mysql DBI is a generic interface for many databases. That means that you can write a script that works with many different database engines without change. You need a DataBase Driver (DBD) defined for each database type. For MySQL, this driver is called DBD::mysql. For more information on the Perl5 DBI, please visit the DBI web page and read the documentation: http://dbi.perl.org/
Perl DBI with DBD::mysqlMethods Method : Description Connect : Establishes a connection to a database server. Disconnect : Disconnects from the database server. Prepare : Prepares a SQL statement for execution. Execute : Executes prepared statements. Do : Prepares and executes a SQL statement. Quote : Quotes string or BLOB values to be inserted. fetchrow_array : Fetches the next row as an array of fields. fetchrow_arrayref : Fetches next row as a reference array of fields. fetchrow_hashref : Fetches next row as a reference to a hashtable. fetchall_arrayref : Fetches all data as an array of arrays. Finish : Finishes a statement and lets the system free resources. Rows : Returns the number of rows affected. data_sources : Returns an array of databases available on localhost.
