Mysql ppt

MY SQL MySQL is the world's most popular open source database software, with over 100 million copies of its software downloaded or distributed throughout it's history. With its superior speed, reliability, and ease of use, MySQL has become the preferred choice for Web, Web 2.0, SaaS, ISV, Telecom companies and forward-thinking corporate IT Managers because it eliminates the major problems associated with downtime, maintenance and administration for modern, online applications. Many of the world's largest and fastest-growing organizations use MySQL to save time and money powering their high-volume Web sites, critical business systems, and packaged software — including industry leaders such as Yahoo!, Alcatel-Lucent, Google, Nokia, YouTube, Wikipedia, and Booking.com. The flagship MySQL offering is MySQL Enterprise, a comprehensive set of production-tested software, proactive monitoring tools, and premium support services available in an affordable annual subscription. MySQL is a key part of LAMP (Linux, Apache, MySQL, PHP / Perl / Python), the fast-growing open source enterprise software stack. More and more companies are using LAMP as an alternative to expensive proprietary software stacks because of its lower cost and freedom from platform lock-in.

Features of MY SQL * The best and the most-used database in the world for online applications * Available and affordable for all * Easy to use * Continuously improved while remaining fast, secure and reliable * Fun to use and improve * Free from bugs

INSTALLING MYSQL IN LINUX It's simple to install MySQL on Linux using the RPM file. 1. Become the superuser if you are working in your account. (Type "su" and the prompt and give the root password). 2. Change to the directory that has the RPM download. 3. Type the following command at the prompt: rpm -ivh "mysql_file_name.rpm" Similarly you can also install the MySQL client and MySQL development RPMs if you've downloaded them. Alternatively, you can install the RPMs through GnoRPM (found under System). 4. Now we'll set a password for the root user. Issue the following at the prompt. mysqladmin -u root password mysqldata where mysqldata is the password for the root. (Change this to anything you like).

5. It is now time to test the programs. Typing the following at the prompt starts the mysql client program. mysql -u root -p The system asks for the the password. Type the root password (mysqldata). If you don't get the prompt for password, it might be because MySQL Server is not running. To start the server , change to /etc/rc.d/init.d/ directory and issue the command ./mysql start (or mysql start depending on the value of the PATH variable on your system). Now invoke mysql client program. 6. Once MySQL client is running, you should get the mysql> prompt. Type the following at this prompt: show databases; 7. You should now get a display similar to: +----------------+ | Database | +----------------+ | mysql | | test | +----------------+ 2 rows in set (0.00 sec) Okay, we've successfully installed MySQL on your system . Now let's look at some MySQL basics.

CONFIGURING MYSQL You have three main tasks to complete the basic configuration of your Linux MySQL server: 1. Create the grant tables 2. Start the server 3. Verify server function You first create MySQL's grant tables and fill them with the default data used to determine access privileges for the database server. This is accomplished using the mysql_install_db program. First, you need to locate this file. On most installations, you'll find it in either the /bin or /scripts directory under the MySQL root. Once you locate it, run it using the --user=mysql flag. IMPORTANT NOTE : The initial account settings created by mysql_install_db have no passwords associated with the accounts. After completing this configuration process, you should follow the instructions in the article Securing the Initial MySQL Accounts to secure your server.

Next, it's time to start the server. Simply execute the mysqld_safe program found in the /bin directory under the MySQL root. You should also explicitly provide MySQL with the base directory of the MySQL installation using the --basedir flag. For example, if your base directory is /usr/local/mysql, you'd start the server using the command mysqld --basedir=/usr/local/mysql. You can verify that the server is up and running using the mysqladmin administrative interface. Simply execute the command mysqladmin version and you'll get output similar to that shown below: ./mysqladmin Ver 8.40 Distrib 4.0.22, for pc-linux on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.22 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 45 sec

Threads: 1 Questions: 1 Slow Queries: 1 Opens: 6 Flush Tables: 1 Open Tables: 0 Queries per second avg: 0.022 Once the server is up and running, test it out! Put your SQL knowledge to work and ensure that you're able to create tables and work with data. Don't forget that one of your first tasks should be to add security to the default accounts created during the installation process.

Example MySQL config file for medium systems. This is for a system with little memory (32M - 64M) where MySQL plays an important part, or systems up to 128M where MySQL is used together with other programs (such as a web server) You can copy this file to /etc/my.cnf to set global options, mysql-data-dir/my.cnf to set server-specific options (in this installation this directory is /opt/lampp/var/mysql) or ~/.my.cnf to set user-specific options. In this file, you can use all long options that a program supports. If you want to know which options a program supports, run the program with the "--help" option. The following options will be passed to all MySQL clients [client] password = your_password port = 3306 socket = /opt/lampp/var/mysql/mysql.sock

Here follows entries for some specific programs The MySQL server [mysqld] port = 3306 socket = /opt/lampp/var/mysql/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M Where do all the plugins live Plugin_dir = /opt/lampp/lib/mysql/plugin/ Don't listen on a TCP/IP port at all. This can be a security enhancement, if all processes that need to connect to mysqld run on the same host. All interaction with mysqld must be made via Unix sockets or named pipes. Note that using this option without enabling named pipes on Windows (via the "enable-named-pipe" option) will render mysqld useless!

To configure this host as a replication slave, you can choose between two methods : 1) Use the CHANGE MASTER TO command (fully described in our manual) - the syntax is: CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, MASTER_USER=<user>, MASTER_PASSWORD=<password> ; where you replace <host>, <user>, <password> by quoted strings and <port> by the master's port number (3306 by default). Example: CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, MASTER_USER='joe', MASTER_PASSWORD='secret'; OR 2) Set the variables below. However, in case you choose this method, then start replication for the first time (even unsuccessfully, for example if you mistyped the password in master-password and the slave fails to connect), the slave will create a master.info file, and any later change in this file to the variables' values below will be ignored and overridden by the content of the master.info file, unless you shutdown the slave server, delete master.info and restart the slaver server. For that reason, you may want to leave the lines below untouched (commented) and instead use CHANGE MASTER TO (see above)

SETUP FOR MYSQL First, if you haven't done this already, set the root password for MySQL. You can do this by typing: mysqladmin -u root password 'passwordyouwant' Now that the root password is set, connect to your MySQL server: mysql -u root -p It will prompt you for a password. Make sure to enter the one you just/previously set. You should now be left at a prompt which looks like this: mysql> At this point, you will create basic permissions for a user and database. For my setup, I want to allow access to localhost to all databases, and a computer which is also on the network, which is referred to as "windowsbox" will have access to all databases. To access the user, host databases, etc... type this; mysql> use mysql; Database changed mysql>

To access the user, host databases, etc... type this; mysql> use mysql; Database changed mysql> To give localhost permission to access all databases, enter this: mysql> insert into -> host(host,db,Select_priv, Insert_priv, Update_priv, -> Delete_priv, Create_priv, Drop_priv) -> values('localhost','%','Y','Y','Y','Y','Y','Y'); Note, the '%' can be replaced with a database name. The '%' is a wildcard. Following the previous format, to allow access from another hostname (in this case "windowsbox") add this: mysql> insert into -> host(host,db,Select_priv, Insert_priv, Update_priv, -> Delete_priv, Create_priv, Drop_priv) -> values('windowsbox','%','Y','Y','Y','Y','Y','Y'); Again, '%' is used as a Wild-Card .

To create a user 'djg' who can access the MySQL server from localhost, type this: mysql> insert into -> user (host, user, password) -> values('localhost','djg',password('mypassword')); To give the user access from another hostname, domain, etc... add other entries accordingly. For example, to give user djg access from windowsbox: mysql> insert into -> user (host, user, password) -> values('windowsbox','djg',password('mypassword')); Now... to give the user permissions to access a database from localhost, add this entry and change with your appropriate information: mysql> insert into -> db (host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) -> values ('localhost','mydatabase','djg','Y','Y','Y','Y','Y','Y'); To give the user permissions from windowsbox, add this: mysql> insert into -> db (host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) -> values ('windowsbox','mydatabase','djg','Y','Y','Y','Y','Y','Y');

Now, type: quit and you will exit mysql. Finally, create the actual database (in this case, 'mydatabase') type this: mysqladmin -u root -p create mydatabase After prompting you for a password, it should create the database. At this point, you must reload MySQL. Type: mysqladmin -u root -p reload After prompting you for a password it should reload MySQL. Congratulations. If all goes well you have set up a user and database with MySQL. You may now create/edit/delete/etc... tables as much as you'd like. Also, please note that by default, MySQL will open up network port 3306 to allow remote requests. If you do not want this port open, append "--skip-networking" when running safe_mysqld to start the daemon. Debian users can edit /etc/init.d/mysqld and change this line: /usr/bin/safe_mysqld > /www.null 2>&1 & to this: /usr/bin/safe_mysqld --skip-networking > /www.null 2>&1 & Now whenever running /etc/init.d/mysql start, it will not open up port 3306

Importing MYSQL I f we want to import a file in the SQL database,first we have to go to the local host connection,then we have to pass on to PHPMYADMIN in that we have to select a button called IMPORT. Select a query which you want to import in to that database click GO and then the query table will be imported in to the database.

EXPORTING MYSQL MYSQL can be exported by selecting an existing database in the local host connection. Then click the export button,select the table which you want to export from the database and click go. Then the required table will be exported and this file will be saved as .sql file.

MYSQL QUERIES Data Definition Language (DDL) : DDL statements are used to define and modify the database structure of your tables or schema. When you execute a DDL statement, it takes effect immediately. Some commands of DDL are: * CREATE - to create table (objects) in the database * ALTER - alters the structure of the database * DROP - delete table from the database * TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed * COMMENT - add comments to the data dictionary * RENAME - rename a table

1. The create table statement (query) to create a table is given below: CREATE TABLE <table name> ( <attribute name 1> <data type 1>, ... <attribute name n> <data type n>); Example: CREATE TABLE STUDENT ( StudID NUMBER, Name VARCHAR); The data types that you will use most frequently are character strings, which might be called VARCHAR or CHAR for variable or fixed length strings; numeric types such as NUMBER or INTEGER, which will usually specify a precision; and DATE or related types. Data types are differ according to the databases software whatever you are using to your system.

2. The alter table statement to make modifications to the table structure such as Key constraints, Column size, etc. ALTER TABLE <table name> ADD CONSTRAINT <constraint name> PRIMARY KEY (<attribute list>); Example: ALTER TABLE STUDENT ADD CONSTRAINT NOT NULL PRIMARY KEY (StudID); 3. The delete table statement (query) to delete a table is given below: DROP TABLE <table name>; Example: DROP TABLE STUDENT;

Data Manipulation Language Data Manipulation Language (DML) statements are used for managing data within tables. Some commands of DML are: * SELECT - retrieve data from the a database * INSERT - insert data into a table * UPDATE - updates existing data within a table * DELETE - deletes all records from a table, the space for the records remain * MERGE - UPSERT operation (insert or update) * CALL - call a PL/SQL or Java subprogram * LOCK TABLE - control concurrency

1. The insert statement is used to add new row to a table. INSERT INTO <table name> VALUES (<value 1>, ... <value n>); Example: INSERT INTO STUDENT VALUES (1001, ‘ Ram’); The inserted values must match the table structure exactly in the number of attributes and the data type of each attribute. Character type values are always enclosed in single quotes; number values are never in quotes; date values are often (but not always) in the format ‘yyyy-mm-dd’ (for example, ‘2006-11- 30’). 2. The update statement is used to change values that are already in a table. UPDATE <table name> SET <attribute> = <expression> WHERE <condition>; Example: UPDATE STUDENT SET Name = ‘Amar’ WHERE StudID=1001;

3. The delete statement deletes row(s) from a table. DELETE FROM <table name> WHERE <condition>; Example: DELETE FROM STUDENT WHERE StudID=1001; If the WHERE clause is omitted, then every row of the table is deleted that matches with the specified condition. 4. The SELECT statement is used to form queries for extracting information out of the database. SELECT <attribute>, ….., <attribute n> FROM <table name>; Example: SELECT StudID, Name FROM STUDENT; * COMMIT - save work done. * SAVEPOINT - identify a point in a transaction to which you can later roll back. * ROLLBACK - restore database to original since the last COMMIT.

The SQL ORDER BY clause defines in what order to return a data set retrieved with a SQL SELECT statement. Here is an example of using SQL ORDER BY to order the rows in our Weather table by city: SELECT * FROM Weather ORDER BY City The result of using this SQL ORDER BY clause will be the following: City AverageTemperature Date New York 18 C 10/09/2005 New York 22C 10/10/2005 Seattle 20 C 10/09/2005 Seattle 21 C 10/10/2005 Washington 17 C 10/09/2005 Washington 20 C 10/10/2005

SQL aggregate functions are used to sum, count, get the average, get the minimum and get the maximum values from a column or from a sub-set of column values. To count the rows in the Weather table we can use the SQL COUNT aggregate function: SELECT COUNT(*) FROM Weather The SQL GROUP BY clause is used along with the SQL aggregate functions and specifies the groups where selected rows are placed. WHEN one or more aggregate functions are presented in the SQL SELECT column list, the SQL GROUP BY clause calculates a summary value for each group. The SQL HAVING keyword provides a search condition for a group or aggregate. The SQL HAVING clause works together with the SQL SELECT clause. The SQL HAVING clause is somewhat similar to the SQL WHERE clause, because it specifies a search condition. There is one important difference between SQL HAVING and SQL WHERE clauses. The SQL WHERE clause condition is tested against each and every row of data, while the SQL HAVING clause condition is tested against the groups and/or aggregates specified in the SQL GROUP BY clause and/or the SQL SELECT column list.

The SQL AND & SQL OR keywords are used together with the SQL WHERE clause to join two or more search conditions specified in the WHERE clause. Let's use the weather table to illustrate how to use SQL AND & SQL OR keywords: SELECT * FROM Weather WHERE City = 'Washington' OR City = 'New York' The SQL JOIN clause selects data from two or more tables tied together by matching table columns. To illustrate how to use the SQL JOIN clause we will use the already familiar Weather table and we will introduce a new one called State. SELECT Weather.City, Weather.AverageTemperature, Weather.Date, State.State FROM Weather, State WHERE Weather.City = State.City The SQL UNION clause merges the results of two or more SELECT SQL queries into one result set. When using SQL UNION, all the SQL expressions participating in the UNION must have the same structure (they have to have the same number of columns and same or compatible data types). You have to keep the column order of all unionized SQL SELECT expressions uniform, otherwise you’ll get an error SELECT City, AverageTemperature, Date FROM Weather UNION SELECT City, AverageTemperature, Date FROM WeatherArchive.

Mysql ppt

More Related Content

What's hot

Similar to Mysql ppt

More from Sanmuga Nathan

Recently uploaded

Mysql ppt