MySQL Tutorial

MySQL Tutorial Prepared By Nadil Sundarapperuma Part I The Database The database has become an integral part of almost every human's life. Without it, many things we do would become very tedious, perhaps impossible tasks. Banks, universities, and libraries are three examples of organizations that depend heavily on some sort of database system. On the Internet, search engines, online shopping, and even the website naming convention (http://www...) would be impossible without the use of a database. A database that is implemented and interfaced on a computer is often termed a database server. Why MySQL? Its Fast According to the MySQL Benchmarks , its faster than PostgreSQL, AdaBas D, Empress, and Solid, on Linux systems. Its also faster than Access 2000, DB2, Informix, MS -SQL, Solid, Sybase, and Oracle 8.0.3, on Windows platforms. Its Easy In spite of its high performance, MySQL is relatively simple to download and install. Its easy to interact with, and beginners will have no trouble learning to use it. Its Free (in most cases) The client programs and client programming library are free on all platforms. That means you can have the tools to access a MySQL server from any platform, including Windows. The server is free for non-Windows platforms unless you want to sell it or any software/services requiring it. There is a small fee if you want to use the MySQL server on Windows. Why? Because that platform and its development tools aren't free, and that makes it more expensive to develop MySQL. If you want to run the server, but don't want to have to pay for the license you can run the shareware version of the program for 30 days. Also, if you are using the server for educational purposes, for government use, or in universities, you can ask the developers to waive the license. Or, you can connect to an existing MySQL server on another machine. Remember, the client programs are always free. It Understands SQL You can interact with MySQL using Structured Query Language (SQL), as well as by using applications that support Open Database Connectivity (ODBC). Both of these are industry standards. It can be Secured The MySQL server is fully networked, and therefore it can be accessed from anywhere on the Internet. MySQL has access control so you can determine the level of access you'd like users to have. Its Portable Because MySQL is available on so many platforms, its easy to develop an application or work with a database that can be moved to a different platform. PHP and Perl/DBI are two easy ways to develop crossplatform applications, and we'll be learning more about them in upcoming articles. 1 Its Accessible Because its an open distribution, MySQL is easy to obtain. You can download it from your Web browser, and if you'd like, you can get the source code and see how things are done. And, if you wanted to, you could change the code to suit you. Who's Using MySQL? This database server has gained enormous popularity within corporate circles, including the following organizations: • • Silicon Graphics (http://www.sgi.com) ? Siemens (http://www.siemens.com) MySQL Data Types MySQL is capable of many of the data types that even the novice programmer has probably already been exposed to. Some of the more commonly used include: CHAR (N) CHAR's are used to represent fixed length strings. A CHAR string can range from 1 -255 characters. In later table creation, an example CHAR data type would be declared as follows: Eg: car_model CHAR(10); VARCHAR (N) VARCHAR is a more flexible form of the CHAR data type. It also represents data of type String, yet stores this data in variable length format. Again, VARCHAR can hold 1-255 characters. VARCHAR is usually a wiser choice than CHAR, due to it's variable length format characteristic. Although, keep in mind that CHAR is much faster than VARCHAR, sometimes up to 50%. (A CHAR stores the whole length of the declared variable, regardless of the size of the data contained within, whereas a VARCHAR only stores the length of the data, thus reducing size of the database file.) Eg: car_model VARCHAR(10); INT (N) [Unsigned] The INT data type stores integers ranging from -2147483648 to 2147483647. An optional "unsigned" can be denoted with the declaration, modifying the range to be 0 to 4294967295 Eg: light_years INT ; Valid integer: '-24567'. Invalid integer: '3000000000'. Valid integer: '3000000000'. Invalid integer: '-24567'. FLOAT [(N,D)] A FLOAT represents small decimal numbers, used when a somewhat more precise representation of a number is required. 2 Eg: rainfall FLOAT (4,2); This could be used to represent rainfall average in centimetres per year, which could be a decimal value. More specifically, FLOAT (4,2) states the fact that rainfall can hold up to four characters and two decimal places. Thus, 42.35 is valid, accurately represented. 324.45 is invalid, rounded to 324.5. 2.2 is valid, accurately represented. 34.542 is invalid, rounded to 34.54. Note: Due to the fact that FLOAT is rounded, those wishing to represent money values would find it wise to use DECIMAL, a data type found within MySQL that does not round values. Consult the documentation for a complete explanation. DATE Stores date related information. The default format is 'YYYY-MM-DD', and ranges from '0000-00-00' to '9999 -12-31'. MySQL provides a powerful set of date formatting and manipulation commands, too numerous to be covered within this article. However, one can find these functions covered in detail within the MySQL documentation. Eg: the_date DATE; TEXT / BLOB The text and blob data types are used when a string of 255 - 65535 characters is required to be stored. This is useful when one would need to store an article such as the one you are reading. However, there is no end space truncation as with VARCHAR AND CHAR. The only difference betw een BLOB and TEXT is that TEXT is compared case insensitively, while BLOB is compared case sensitively. SET A data type of type string that allows one to choose from a designated set of values, be it one value or several values. One can designate up to 64 values. Eg: transport SET ("truck", "wagon") NOT NULL; From the above declaration, the following values can be held by transport: "" "truck" "wagon" "truck,wagon" ENUM A data type of type string that has the same characteristics as the SET data type, but only one set of allowed values may be chosen. Usually only takes up one byte of space, thus saving time and space within a table. Eg. transport ENUM ("truck", "wagon") NOT NULL; From the above declaration, the following values can be held by transport: "" "truck" "wagon" 3 Part II Managing Your Databases Starting the MySQL Client To start working with your database, you'll need to run MySQL from the command line, which is from a Linux prompt, or from a MS-DOS prompt window on Windows. Use it like this o n Linux % mysql [options] If you're using Windows, you'll find the MS-DOS prompt from Start Menu|Programs|MS -DOS Prompt. When it's open you'll type something like this, depending on the path to MySQL. C:\mysql \bin\mysql [options] There are options which will allow you to tailor your connection to a MySQL server, but we won't be discussing them here. They're optional, so we'll cover them in future articles. If you find that you need them to connect to a server, please ask your MySQL administrator for the specific parameters you'll need to use. Once MySQL is started, you'll see a message similar to Welcome to the MySQL monitor. Commands end with ; or \g . Your MySQL connection id is 2 to server version: 3.22.32 Type 'help' for help. mysql> Creating and Using a Database mysql> CREATE DATABASE temp; Query OK, 1 row affected (0.03 sec) The first thing to note is that each command has to end with a semicolon. Since you can have multi-line input, pressing the Enter key will just move you to another line, like this mysql> CREATE DATABASE temp -> The more times you press Enter, expecting miracles, the more lines with '->' you'll get. If this happens to you, just give yourself a patient moment and type the semicolon. Use the SHOW statement to find out w hat databases currently exist on the server: mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | | temp | +----------+ 4 The list of databases is probably different on your machine, but the MySQL and test databases are likely to be among them. The MySQL database is required because it describes user access privileges. The test database is often provided as a workspace for users to try things out. Creating a database doesn't mean that it's automatically selected as the current database. The next step is to make it current with the USE statement. mysql> USE temp; Creating a Table Creating the database is the easy part, but at this point it’s empty, as SHOW TABLES will tell you: mysql> SHOW TABLES; Empty set (0.00 sec) Creating a database is not the same thing as creating a table. In fact, when you create a database in MySQL all that happens is that a new subdirectory with the database name is created under mysql/data. Even so, the distinction is important. A database is a name for an information repository. It can be a silly name, like 'temp', but more likely it will be the name of a project, company, application, or something more meaningful. Tables are where and how the information is actually stored. Each database has to have at least one table, although it frequently has more than that. The tables contain columns, or fields, that hold specific types of information. Tables within a database can interrelate to each other in ways that we will be exploring in detail throughout this series. But for now, we'll create a table just to see how it's done. We'll be naming the table 'pet'. Since this article is intended to be used as a reference, we'll create a column for each of the more common data types available. That way you'll see how to create tables and columns that will be much more useful to you down the road. Here's how to create the table using multi-line input. Type each line as you see it printed, then press the Enter key for the next line. Don't forget the semicolon when you're all done. mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); Now that you have created a table, SHOW TABLES should produce some output: mysql> SHOW TABLES; +---------------------+ | Tables in temp | +---------------------+ | pet | +---------------------+ To verify that your table was created the way you expected, use a DESCRIBE statement: mysql> DESCRIBE pet; You can use DESCRIBE any time, for example, if you forget the names of the columns in your table or what types they are. +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra| +---------+-------------+------+-----+---------+-------+ | name | va rchar(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 | | +---------+-------------+------+-----+---------+-------+ 5 Inserting New Data Now let's load some data into our table. We'll use the insert statement, which has the syntax INSERT INTO TABLE (column_name1, column_name2, ...) VALUES (data, 'data',...); If the data values are text, then they need to be surrounded by quotes. Numeric values don't need them. Here's our first row of data: mysql> INSERT INTO pet (name, owner, species, sex, birth, death) -> VALUES ('Puffball','Diane','hamster','f','1999 -0 3-30',NULL); You could create a text file ‘pet.txt’ containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), you can use NULL values. To represent these in your text file, use \N . For example, the record for Whistler the bird would look like this (where the white space between values is a single tab character): name owner species sex birth death Whistler Gwen bird \N 1997-12-09 \N To load the text file ‘pet.txt’ into the pet table, use this command: mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet; Updating Existing Records Modifying data in your tables is on e of the most common things you'll do when you work with databases. Here's the SQL statement to do that. UPDATE tablename SET COLUMN =value, ... [WHERE clause] The WHERE clause is an optional statement which narrows the set of records to update. It's also used in SELECT statements, but wasn't necessary in this example because we only have one record. But when using statements like UPDATE (and DELETE), you'll need to be extra careful that you're working with the exact set of records that you want. By using the WHERE clause, you'll be able to specify a value for the columns you want. Let's see how all of this works. mysql> UPDATE temp SET owner=”Lee”; mysql> UPDATE pet SET birth = "1989-0 8-31" WHERE name = "Bowser"; Deleting Records The same principles apply for deleting records as for updating them. You want to be darn sure that you're working with a specific set of data. Here's how to use the DELETE statement. DELETE FROM tablename [WHERE clause] In this case, the WHERE clause is optional, as it is for SELECT and UPDATE. However, if you leave it out you'll delete ALL of the records in the table. Oops. Do yourself a favor and use the SELECT statement first. When you've verified that the WHERE clause will return the desired data, then you can use it in your DELETE statement. mysql> DELETE FROM pet; (delete all the records) 6 Retrieving Information The SELECT statement is used to retrieve information from a table. The general form of the statement is: SELECT what_to_select FROM which_table WHERE conditions _to_satisfy what_to_select indicates what you want to see. This can be a list of columns, or * to indicate “all columns.” which_table indicates the table from which you want to retrieve data. The WHERE clause is optional. If it’s present, conditions_to_satisfy specifies conditions that rows must satisfy to qualify for retrieval. The simplest form of SELECT retrieves everything from a table: mysql> SELECT * FROM pet; Selecting Particular Rows You can select only particular rows from your table. mysql> SELECT * FROM pet WHERE name = "Bowser"; +-------- +-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +-------- +-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1989 -08 -31 | 1995 -07 -29 | +-------- +-------+---------+------+------------+------------+ You can specify conditions on any column, not just name. For example, if you want to know which animals were born after 1998, test the birth column: mysql> SELECT * FROM p et WHERE birth >= "1998-1-1"; Selecting Particular Columns If you don’t want to see entire rows from your table, just name the columns in which you’re interested, separated by commas. For example, if you want to know when your animals were born, select the name and birth columns: mysql> SELECT name, birth FROM pet; +---------- +------------+ | name | birth | +---------- +------------+ | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1990-08-27 | | Bowser | 1989-08-31 | | Chirpy | 1998 -09-11 | | Whistler | 1997-12-09 | | Slim | 1996 -04-29 | | Puffball | 1999-03-30 | 7 Advanced MySQL Commands What we have covered so far is but a small part of what MySQL is capable of. Let's delve a little deeper into the language, exploring some of the more advanced commands of the language. First we will create a table called test. mysql> CREATE TABLE test ( mysql> name VARCHAR (15), mysql> email VARCHAR (25), mysql> phone_number INT, mysql> ID INT NOT NULL AUTO_INCREMENT, mysql> PR IMARY KEY (ID)); Other Table Options The following options can be placed after any data type, adding other characteristics and capabilities to them. • • • PRIMARY KEY Used to differentiate one record from another. No two records can have the same . primary key. This is obviously useful when it is imperative that no two records are mistaken to be the other. AUTO_INCREMENT A column with this function is automatically incremented one value (previous + . 1) when an insertion is made into the record. The data type is a utomatically incremented when 'NULL' is inserted into the column. NOT NULL. Signifies that the column can never be assigned a NULL value. Eg: soc_sec_number INT PRIMARY KEY; No two soc_sec_number records can hold the same value. ID_NUMBER INT AUTO_INCREMENT; Automatically increments in value, starting at '1', with every subsequent insertion. Logical Operations MySQL includes full support of all basic logical operations. AND (&) mysql> SELECT * FROM test WHERE mysql> (name = "Bugs Bunny") AND mysql> (phone_number = 5554321); Result: All records containing the name "Bugs Bunny" AND the phone number '5554321' will be displayed to the screen. OR ( || ) mysql> SELECT * FROM test WHERE mysql> (name = "Bugs Bunny") OR mysql> (phone_number = 5554321); Result: All records containing the name "Bugs Bunny" OR the phone number '5554321' will be displayed to the screen. NOT ( ! ) mysql> SELECT * FROM test WHERE mysql> (name != "Bugs Bunny"); Result: 8 All records NOT containing the name "Bugs Bunny" will be displayed to the screen. Order By mysql> SELECT * FROM test WHERE mysql> (name = "Bugs Bunny") ORDER BY mysql> phone_number; Result: All records containing the name "Bugs Bunny" will be displayed to the screen, ordered in respect to the phone_number. Search Functions MySQL offers the user the ability to perform both general and specific searches on data. mysql> SELECT * FROM test WHERE mysql> (name LIKE "%gs Bunny"); Result: All records containing the partial string "gs Bunny" will be displayed to the screen. This would include such names as: "Bugs Bunny", "ags Bunny", "gs Bunny", and "234rtgs Bunny". Notice that "LIKE" has been used instead of the equals sign (=). "LIKE" signifies that one is searching for an estimate of the data requested, and not necessarily an exact copy. The '%' sign could be placed anywhere within the string. The method in which the server searches for a string is dependent upon where one places the '%' sign. mysql> SELECT * FROM test WHERE mysql> (name LIKE "Bugs Bunny%"); Result: All records containing the partial string "Bugs Bunny" will be displayed to the screen. This would include such names as: "Bugs Bunnys", "Bugs Bunnyyyy453", "Bugs Bunnytrtrtrtrtr", but not "gs Bunny". Focused Search Results One can also perform searches and display only certain columns. mysql> SELECT name FROM test WHERE mysql> (name = "Bugs Bunny"); Result: name Alter T able Another very important function of MySQL is the ability to modify previously created tables. This is accomplished via the ALTER statement. This function allows one to add, modify, and delete columns, as well as rename the table, among other functions. Example: Rename the table mysql> ALTER table test RENAME mytest; Example: Add a column mysql> ALTER table mytest ADD birthday DATE; Exam ple: Modify a column mysql> ALTER table mytest CHANGE mysql> name newname VARCHAR (25); Example: Delete a column mysql> ALTER table mytest DROP newname; Executing the above four functions would modify test, creating the following table: mysql> TABLE mytest ( > email VARCHAR (25), > phone_number INT, > ID INT AUTO_INCREMENT, > birthday DATE ); 9