|
|
MySQL is an open source,[3] full-featured relational database management system and has been ported to most platforms, including Linux, Windows, OS/X, HP-UX, AIX, etc. MySQL is portable (runs on Linux, Windows, OS/X, HP-UX, etc.), fast, reliable, scalable, and easy to use.
[3] MySQL is free use for those who are 100% GPL. See http://www.mysql.com/company/legal/licensing/opensource-license.html for details.
It claims to be installed in more than 10 million computers all over the world, including Antarctica! There are two versions, one where you buy a commercial license and one that is free, free meaning you can use MySQL in any application as long as you don't copy, modify, or distribute the MySQL software. MySQL supports a number of APIs (application programming interfaces), including Perl, PHP, TCL, Python, C/C++, Java, etc.
When working with MySQL, a number of like-name terms are used. Table 17.2 is provided to help clarify the use of these terms.
Here we assume you have installed a database server and it is running. Downloading and installing MySQL is usually a straightforward process. You can get MySQL from the mysql.com Web site or use integrated applications, such as XAMPP or WAMP.
XAMPP (for Windows, Linux, Mac OS, and Solaris) is a free, easy-to-install Apache distribution containing MySQL, PHP, and Perl. All you have to do is download, extract, and start it up. For details, go to http://www.apachefriends.org/en/xampp.html.
For complete installation instructions, go to this Web page.
The MySQL database system uses the client/server model described in Section 17.2.1. You are the client connecting to the database from the command line, a graphical user interface, or from a program. Before connecting to the database from a Perl program, we will first use the the MySQL command-line client.
The MySQL command-line client comes with the MySQL installation and is universally available. It is a mysql.exe program located in the bin folder of your MySQL installation.
To run this command-line application, you must start the command-line prompt. On Windows, go to the Start menu, choose the Run... option, and then type cmd in the "run" window. On Mac OS X, go to the Applications folder in your Finder and then navigate to Utilities. You will find the Terminal application there. You should navigate to the location where you installed MySQL and find the bin folder. With UNIX, type commands at the shell prompt in a terminal window.
The MySQL client executable is normally located in the bin folder.
To connect to a database using this client, you will enter information similar to the following line:
mysql --user=root --password=my_password --host=localhost
Regardless of the type of client you choose, you may be required to specify the user name and the host machine to which you are connecting. Most configurations expect you to have a password, although if just working by yourself, it is not required. You have the option to specify the default database as well.
Once you are successfully connected, you will get the mysql> prompt instead of your standard DOS/UNIX prompt. This means you are now sending commands to the MySQL database server and not to your local computer's operating system.
MySQL supports input-line editing. The up-arrow and down-arrow keys allow you to move up and down through previous input lines, and the left-arrow and right-arrow keys to move back and forth within a line. The backspace and Delete keys are used to erase characters from the line and type in new characters at the cursor position. To submit an edited line, press Enter. For UNIX users, MySQL also supports tab completion, allowing you to enter part of a keyword or identifier and complete it using the Tab key.
When you download MySQL, on some installations, you may be asked to enter a password for user "root." Even if you are not forced to create a password, it is a good idea to set one to protect the security of your database. To set a password, go to the MySQL console and type the following MySQL command. Replace 'MyNewPassword' with your password.
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');After setting the password, you can stop the MySQL server and then restart it in normal mode again. If you run the server as a service, start it from the Windows Services window. If you start the server manually, use whatever command you normally use. You should be able to connect using the new password.
Setting the Password at the MySQL Prompt
1 $ mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) $ $ mysql -u root -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 91 to server version: 5.0.21-community-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. |
The MySQL Query Browser is a graphical user interface (GUI) client available from mysql.com used to connect to the MySQL database server. Once you download it and follow the simple installation wizard, you can start the application from the Start menu under Windows.
The MySQL Query Browser then displays a connection dialog box. You must specify the MySQL server where you want to connect, the credentials needed for authorization on that server, which machine that server runs on (and which port it listens to), and the default database (called the "Schema") you will be using. There are also a number of additional options you can specify if necessary.
You must choose a default database in order to issue queries. Although it is possible to choose a default database after connecting to the server, setting the default from the connection dialog can save time on subsequent connections.
The information to enter is very similar to the command-line client: user name, password, and the server host where the database server is running. You can optionally enter the database name and port number (3306 is the default for MySQL) and save the connection information as a bookmark under the Stored Connection section.
By using the familiar tree-like navigation structure on the right-hand side of the application window, you can also navigate the various databases in the MySQL Query Browser.
The phpMyAdmin tool is written in PHP to handle the administration of MySQL over the Web. It is used to create and drop databases, manipulate tables and fields, execute SQL statements, manage keys on fields, manage privileges, and export data into various formats. See http://www.phpmyadmin.net/home_page/index.php.
With a driver's license, "authentication" is verifying that it is really you who owns the license by checking your picture and expiration date, and "authorization" is validating what type of vehicle you are authorized to drive, such as a car, a large truck, or a school bus.
Similarly, the primary purpose of the MySQL privilege system is to authenticate that the user and password are valid to connect to the specified host, as demonstrated in the previous examples in both the command line and graphical client. The second purpose of the privilege system is to specify what the user, once connected to the database, is authorized to do. For example, some users may only be authorized to select and view the data from a specific table. When MySQL is installed, the MySQL database is created with tables called grant tables that define the initial user accounts and privileges. The first account is that of a user named "root," also called the superuser. The superuser can do anything, meaning anyone logging on to the database as root is granted all privileges. Initially, the root account has no password, making it easy for anyone to log on as the superuser. The other types of accounts created are anonymous-user accounts, also without a password. For both the root and anonymous accounts, Windows gets one each and UNIX gets two. Either way, to avoid security problems, the first thing you should do, once the MySQL server starts, is to set a password on the root account and the anonymous accounts.
For administration purposes, you should have root access rights to your server. The mysqladmin utility is useful for creating passwords and performing other MySQL administrative tasks. In the next example it is used to set the password for the user "root".
The database server keeps a list of available databases, which can be displayed as a table by issuing the show command at the mysql prompt, as shown in the following example. Typically when you install MySQL, it comes with two databases: test and mysql. test is an empty database used for practicing and testing various features. You normally don't need to have any special permissions to be able work in the test database. The mysql database is a special database where the MySQL server stores various access permissions. For now, you should not worry about this database unless you need to administer privileges. See the "GRANT" command in the MySQL manual.
Creating a database is simple. Designing it is another story and depends on your requirements and the model you will use to organize your data. With the smallest database, you will have to create a table. The next section will discuss how to create and drop both databases and tables. Assuming you have been granted permission to create a database, you can do it at the mysql command line or with the mysqladmin tool as follows:
|
Code View: 1 mysql> create database my_sample_db; Query OK, 1 row affected (0.00 sec) 2 mysql> use my_sample_db; Database changed 3 mysql> show tables; Empty set (0.00 sec) 4 mysql> create table test( -> field1 INTEGER, -> field2 VARCHAR(50) -> ); Query OK, 0 rows affected (0.36 sec) 5 mysql> show tables; +------------------------+ | Tables_in_my_sample_db | +------------------------+ | test | +------------------------+ 1 row in set (0.00 sec) 6 mysql> drop table test; Query OK, 0 rows affected (0.11 sec) 7 mysql> drop database my_sample_db; Query OK, 0 rows affected (0.01 sec) Explanation
|
(At the MySQL Client Prompt) 1 mysql> select database(); +------------+ | database() | +------------+ | northwind | +------------+ 1 row in set (0.00 sec) 2 mysql> select now(); +---------------------+ | now() | +---------------------+ | 2007-05-12 16:09:57 | +---------------------+ 1 row in set (0.00 sec) |
The examples in this next section illustrate how to issue SQL commands from the MySQL client. These examples do not attempt to cover all the possible SQL statements supported by MySQL but are here to illustrate the basic syntax for creating and dropping databases and tables and how to insert, delete, edit, alter, and select data from the database tables. For a complete discription of all that you can do with MySQL, visit http://dev.mysql.com/doc/.
Now we are ready to create a database. This database is called sample_db. The CREATE DATABASE command creates the database, and the SHOW DATABASES statement demonstrates that it is now listed with the other databases. (You can also use the mysqladmin command to create and drop databases.)
1 mysql> CREATE DATABASE sample_db; Query OK, 1 row affected (0.03 sec) 2 mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | northwind | | phpmyadmin | | sample_db | | test | +--------------------+ 6 rows in set (0.00 sec) Explanation |
After creating the database, we need to open it so we can use it. This is done with the USE statement. We now have a database to work in.
mysql> USE sample_db;
Database changed |
Once the database is created, it is time to create some tables. In a real situation, the relational database will be designed with rules that put logic in the structure of the tables, a process called "normalization" and a topic beyond the scope of this book. In our sample database, we will create one table and put some data in it, just to show you how it is done. The data types define the structure of each field in the table. The CREATE TABLE statement defines each field, its name, and its data type.
First, we will have to decide what kind of data will be stored in the table: text, numbers, dates, photos, money, etc., and what to name the fields (columns) under which the data is stored. MySQL has specific data types to describe all the types of data that can be stored in the database. Most of the MySQL data types are listed in Table 17.3.
| Data Type | Description |
|---|---|
| Numbers | |
| TINYINT | Very small numbers; suitable for ages. Can store numbers between 0 and 255 if UNSIGNED clause is applied; else the range is between –128 and 127. |
| SMALLINT | Suitable for numbers between 0 and 65535 (UNSIGNED) or –32768 and 32767. |
| MEDIUM INT | 0 to 16777215 with UNSIGNED clause or -8388608 and 8388607. |
| INT | UNSIGNED integers fall between 0 and 4294967295 or –2147683648 and 2147683647. |
| BIGINT | Huge numbers (–9223372036854775808 to 9223372036854775807). |
| FLOAT | Floating point numbers (single precision). |
| DOUBLE | Floating point numbers (double precision). |
| DECIMAL | Floating point numbers represented as strings. |
| Text | |
| CHAR(x) | Where x can range from 1 to 255. Holds a fixed-length string (can contain letters, numbers, and special characters). The fixed size is specified in parentheses. |
| VARCHAR(x) | x ranges from 1 to 255. Holds a variable-length string (can contain letters, numbers, and special characters). The maximum size is specified in parentheses. |
| TINYTEXT | Small text, case insensitive. |
| TEXT | Slightly longer text, case insensitive. |
| MEDIUMTEXT | Medium-size text, case insensitive. |
| LONGTEXT | Really long text, case insensitive. |
| TINYBLOB | Blob means a Binary Large OBject. You should use blobs for case-sensitive searches. |
| Binary | |
| BLOB | Slightly larger blob, case sensitive. |
| MEDIUMBLOB | Medium-sized blobs, case sensitive. |
| LONGBLOB | Really huge blobs, case sensitive. |
| ENUM | Enumeration data type has fixed values, and the column can take only one value from the given set. The values are placed in parentheses following ENUM declaration. An example is a marital-status column—m_status ENUM("Y", "N"). |
| Dates | |
| DATE: YYYY-MM-DD | Four-digit year followed by two-digit month and date; e.g., 20071030 |
| TIME: hh:mm:ss | Hours:Minutes:Seconds |
| DATETIME | YYYY-MM-DD hh:mm:ss (Date and time separated by a space character) |
| TIMESTAMP | YYYYMMDDhhmmss |
| YEAR | YYYY (four-digit year); e.g., 2007 |
|
Code View: 1 mysql> CREATE TABLE teams( -> name varchar(100) not null, -> wins int unsigned, -> losses int unsigned); Query OK, 0 rows affected (0.09 sec) 2 mysql> SHOW TABLES; +---------------------+ | Tables_in_sample_db | +---------------------+ | teams | +---------------------+ 1 row in set (0.00 sec); 3 mysql> DESCRIBE teams; +--------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+-------+ | name | varchar(100) | NO | | | | | wins | int(10) unsigned | YES | | NULL | | | losses | int(10) unsigned | YES | | NULL | | +--------+------------------+------+-----+---------+-------+ | teams | +---------------------+ 1 row in set (0.00 sec) Explanation
|
In the next example, we will create another table and add a primary key. A primary key is used to uniquely identify the records in the database. A user's login name, UID, account number, or license plate are examples of unique IDs. A primary key is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY.
|
Code View: 1 mysql> CREATE TABLE coaches( -> id INT NOT NULL AUTO_INCREMENT, -> name VARCHAR(75), -> team VARCHAR(100), -> title VARCHAR(50), -> start_date date, -> PRIMARY KEY(id)); Query OK, 0 rows affected (0.38 sec) 2 mysql> DESCRIBE coaches; +------------+--------------+------+-----+---------+--------------- | Field | Type | Null | Key | Default | Extra +------------+--------------+------+-----+---------+--------------- | id | int(11) | NO | PRI | NULL |auto_increment | name | varchar(75) | YES | | NULL | | team | varchar(100) | YES | | NULL | | title | varchar(50) | YES | | NULL | | start_date | date | YES | | NULL | +------------+--------------+------+-----+---------+--------------- 5 rows in set (0.00 sec) 3 mysql> SHOW TABLES; +---------------------+ | Tables_in_sample_db | +---------------------+ | coaches | | teams | +---------------------+ 2 rows in set (0.00 sec) Explanation |
The SQL INSERT statement adds new records to a table. When you insert data, make sure you provide a value for each field name in the order the data is stored; otherwise, MySQL will send an error message. In the following example, data can be inserted with the SET clause where fields are assigned values, or the values can be specified with the VALUES list or by simply listing the values for each field in order. See the MySQL documentation for a complete list of ways to add new records to a table.
|
Code View: 1 mysql> INSERT INTO teams -> set name='Fremont Tigers', -> wins=24, -> losses=26; Query OK, 1 row affected (0.00 sec) 2 mysql> INSERT INTO teams -> set name='Chico Hardhats', -> wins=19, -> losses=25; Query OK, 1 row affected (0.00 sec) 3 mysql> INSERT INTO teams values -> ('Bath Warships',32,3); Query OK, 1 row affected (0.00 sec) 4 mysql> INSERT INTO teams values -> ('Bangor Rams', 22, 24); Query OK, 1 row affected (0.00 sec) 5 mysql> SELECT name FROM teams; +----------------+ | name | +----------------+ | Fremont Tigers | | Chico Hardhats | | Bath Warships | | Bangor Rams | +----------------+ 4 rows in set (0.00 sec) 6 mysql> INSERT INTO coaches values -> (" ",'John Doe','Chico Hardhats','Head Coach', 20021210); Query OK, 1 row affected, 1 warning (0.05 sec) 7 mysql> INSERT INTO coaches values -> (" ", 'Jack Mattsone','Chico Hardhats','Offensive Coach' , '20041005'); Query OK, 1 row affected, 1 warning (0.00 sec) 8 mysql> INSERT INTO coaches(name,team, title,start_date) -> values( 'Bud Wilkins', 'Fremont Tigers', 'Head Coach', '19990906'); Query OK, 1 row affected (0.03 sec) 9 mysql> INSERT INTO coaches(name, team, title,start_date) ->values( 'Joe Hayes', 'Fremont Tigers', 'Defensive Coach', '19980616'); Query OK, 1 row affected (0.02 sec Explanation
|
One of the most commonly used SQL commands is SELECT, mandatory when performing a query. The SELECT command is used to retrieve data from a table based on some criteria. It specfies a comma-separated list of fields to be retrieved, and the FROM clause specifies the table(s) to be accessed. The results are stored in a result table known as the result set, just a little table itself. The * symbol can be used to represent all of the fields.
In the following examples, data is retrieved for specific columns, each column (field) separated by a comma.
|
Code View: 1 mysql> SELECT name FROM teams; +----------------+ | name | +----------------+ | Fremont Tigers | | Chico Hardhats | | Bath Warships | | Bangor Rams | +----------------+ 4 rows in set (0.00 sec) 2 mysql> SELECT name, wins FROM teams; +----------------+------+ | name | wins | +----------------+------+ | Bangor Rams | 22 | | Bath Warships | 32 | | Fremont Tigers | 24 | | Chico Hardhats | 19 | +----------------+------+ 4 rows in set (0.00 sec) 3 mysql> SELECT id, name, title FROM coaches; +----+---------------+-----------------+ | id | name | title | +----+---------------+-----------------+ | 1 | John Doe | Head Coach | | 2 | Jack Mattsone | Offensive Coach | | 3 | Bud Wilkins | Head Coach | | 4 | Joe Hayes | Defensive Coach | +----+---------------+-----------------+ 4 rows in set (0.00 sec) Explanation
|
The * is a wildcard that is used to represent all of the columns in a table.
1 mysql> SELECT * FROM teams; +----------------+------+--------+ | name | wins | losses | +----------------+------+--------+ | Fremont Tigers | 24 | 26 | | Chico Hardhats | 19 | 25 | | Bath Warships | 32 | 3 | | Bangor Rams | 22 | 24 | +----------------+------+--------+ 2 mysql> SELECT * FROM coaches; +----+---------------+----------------+-----------------+-----------+ | id | name | team | title | start_date| +----+---------------+----------------+-----------------+-----------+ | 1 | John Doe | Chico Hardhats | Head Coach | 2002-12-10| | 2 | Jack Mattsone | Chico Hardhats | Offensive Coach | 2004-10-05| | 3 | Bud Wilkins | Fremont Tigers | Head Coach | 1999-09-06| | 4 | Joe Hayes | Fremont Tigers | Defensive Coach | 1998-06-16| +----+---------------+----------------+-----------------+-----------+ 4 rows in set (0.00 sec) Explanation
|
The WHERE clause is optional and specifies which data values or rows will be selected based on some condition, called selection criterion. SQL provides a set of operators to qualify the condition being set.
| Operator | Description | Example |
|---|---|---|
| = | Equal to | where country = 'Sweden' |
| <>, != | Not equal to[a] | where country <> 'Sweden' |
| > | Greater than | where salary > 50000 |
| < | Less than | where salary < 50000 |
| >= | Greater than or equal | |
| <= | Less than or equal | |
| IS [NOT] NULL | Is NULL (no value) or Not NULL | where birth = NULL |
| BETWEEN | Between an inclusive range | where last_name BETWEEN 'Doe' AND 'Hayes' |
| LIKE | Search for value like a pattern | where last_name LIKE 'D%' |
| NOT LIKE | Search for a value not like a pattern | where country NOT LIKE 'Sw%' |
| !, NOT | logical NOT for negation | where age ! 10; |
| ||, OR | logical OR | where order_number > 10 || part_number = 80 |
| &&, AND | logical AND | where age > 12 && age < 21 |
| XOR | Exclusive OR | where status XOR |
[a] In some versions of SQL, the <> operator may be written as !=.
|
Code View: 1 mysql> SELECT name, wins FROM teams WHERE wins > 25; +-------------------+------+ | name | wins | +-------------------+------+ | Bath Destroyers | 34 | | Portland Penguins | 28 | +-------------------+------+ 2 mysql> SELECT name FROM teams WHERE losses < wins; +---------------+ | name | +---------------+ | Bath Warships | +---------------+ 1 row in set (0.03 sec) 3 mysql> SELECT name, title FROM coaches WHERE team = 'Chico Hardhats'; +---------------+-----------------+ | name | title | +---------------+-----------------+ | John Doe | Head Coach | | Jack Mattsone | Offensive Coach | +---------------+-----------------+ 2 rows in set (0.00 sec) 4 mysql> SELECT name FROM coaches WHERE name LIKE 'J%'; +---------------+ | name | +---------------+ | John Doe | | Jack Mattsone | | Joe Hayes | +---------------+ 3 rows in set (0.00 sec) 5 mysql> SELECT name FROM teams WHERE wins > 10 && losses < 10; +---------------+ | name | +---------------+ | Bath Warships | +---------------+ 1 row in set (0.00 sec) 6 mysql> SELECT name FROM coaches WHERE id BETWEEN 1 AND 3; +---------------+ | name | +---------------+ | John Doe | | Jack Mattsone | | Bud Wilkins | +---------------+ 3 rows in set (0.00 sec) Explanation |
You can display the output of a query in a particular order by using the ORDER BY clause. Rows can be sorted either in ascending (the default) or descending (DESC) order where the values being sorted are either strings or numbers. You can limit the output of any query with the LIMIT clause.
|
Code View: 1 mysql> SELECT * FROM teams ORDER BY name; +----------------+------+--------+ | name | wins | losses | +----------------+------+--------+ | Bangor Rams | 22 | 24 | | Bath Warships | 32 | 3 | | Chico Hardhats | 19 | 25 | | Fremont Tigers | 24 | 26 | +----------------+------+--------+ 4 rows in set (0.00 sec) 2 mysql> SELECT * FROM teams ORDER BY name DESC; +----------------+------+--------+ | name | wins | losses | +----------------+------+--------+ | Fremont Tigers | 24 | 26 | | Chico Hardhats | 19 | 25 | | Bath Warships | 32 | 3 | | Bangor Rams | 22 | 24 | +----------------+------+--------+ 4 rows in set (0.00 sec) 3 mysql> SELECT name, wins FROM teams ORDER BY WINS LIMIT 2; +----------------+------+ | name | wins | +----------------+------+ | Chico Hardhats | 19 | | Bangor Rams | 22 | +----------------+------+ 2 rows in set (0.00 sec) Explanation |
When a database is designed properly, the tables relate to one another based on some criteria; for example, in our database, every team has a name and every coach has a name and a team name. A join allows two or more tables to be combined and return a result set based on the relationships they share. There are different types of join statements (inner joins, cross joins, left, joins, etc.), but they all follow the basic syntax of a SELECT statement with the addition of a JOIN clause.
|
Code View: 1 mysql> SELECT teams.name, coaches.name, teams.wins FROM teams, coaches WHERE teams.name = coaches.team && coaches.id = 4; +----------------+-----------+------+ | name | name | wins | +----------------+-----------+------+ | Fremont Tigers | Joe Hayes | 24 | +----------------+-----------+------+ 1 row in set (0.00 sec) 2 mysql> SELECT teams.name, coaches.name, teams.wins FROM teams, -> coaches WHERE teams.name = coaches.team && -> coaches.title = "Head Coach"; +----------------+-------------+------+ | name | name | wins | +----------------+-------------+------+ | Chico Hardhats | John Doe | 21 | | Fremont Tigers | Bud Wilkins | 24 | +----------------+-------------+------+ 2 rows in set (0.00 sec) 3 mysql> SELECT t.name, c.name, t.wins FROM teams t, coaches c -> WHERE t.name = c.team && c.title LIKE "Head%"; +----------------+-------------+------+ | name | name | wins | +----------------+-------------+------+ | Chico Hardhats | John Doe | 21 | | Fremont Tigers | Bud Wilkins | 24 | +----------------+-------------+------+ 2 rows in set (0.00 sec) Explanation
|
The DELETE command allows you to remove rows from a table. The only real difference between DELETE and SELECT is that the DELETE removes records based on some criteria, whereas the SELECT retrieves those records.
1 mysql> SELECT name FROM teams; +----------------+ | name | +----------------+ | Fremont Tigers | | Chico Hardhats | | Bath Warships | | Bangor Rams | +----------------+ 4 rows in set (0.00 sec) 2 mysql> DELETE FROM teams WHERE name = "Bath Warships"; Query OK, 1 row affected (0.20 sec) 3 mysql> SELECT name FROM teams; +----------------+ | name | +----------------+ | Fremont Tigers | Chico Hardhats | | Bangor Rams | +----------------+ 4 rows in set (0.00 sec) Explanation
|
The UPDATE command is used to edit a table; i.e., to modify or change the values in a table. This statement uses the SET clause to change the existing value to something else, as shown in Example 17.17.
1 mysql> SELECT * FROM teams; +----------------+------+--------+ | name | wins | losses | +----------------+------+--------+ | Fremont Tigers | 24 | 26 | | Chico Hardhats | 19 | 25 | | Bath Warships | 32 | 3 | | Bangor Rams | 22 | 24 | +----------------+------+--------+ 4 rows in set (0.00 sec) 1 mysql> UPDATE teams SET wins=wins + 2 WHERE name="Chico Hardhats"; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 2 mysql> UPDATE teams SET name="Bath Destroyers" -> where name="Bath Warships"; Query OK, 1 row affected (0.13 sec) Rows matched: 1 Changed: 1 Warnings: 0 3 mysql> SELECT * FROM teams; +------------------+------+--------+ | name | wins | losses | +-------------------+------+--------+ | Fremont Tigers | 24 | 26 | | Chico Hardhats | 21 | 25 | | Bath Destroyers | 32 | 3 | | Bangor Rams | 22 | 24 | +-------------------+------+--------+ 4 rows in set (0.00 sec) Explanation |
The ALTER TABLE command allows you to alter the structure of an existing table by adding and dropping columns. The ALTER statement has many possible clauses, such as CHANGE, MODIFY, RENAME, DROP, etc. Don't confuse ALTER with UPDATE. Altering a table changes the structure of how the table was described after it was created. You can use it to add primary keys, indexes, change the definition of a column or where it is positioned in the table, etc. Some of these alterations are demonstrated in the following examples.
1 mysql> ALTER TABLE teams ADD captain varchar(100); Query OK, 11 rows affected (0.64 sec) Records: 11 Duplicates: 0 Warnings: 2 mysql> select * from teams; +------------------------+------+--------+---------+ | name | wins | losses | captain | +------------------------+------+--------+---------+ | Fremont Tigers | 24 | 26 | NULL | | Bath Destroyers | 34 | 3 | NULL | | Chico Hardhats | 21 | 25 | NULL | | Bangor Rams | 23 | 5 | NULL | 4 rows in set (0.01 sec) Explanation |
mysql> ALTER TABLE teams DROP captain;
Query OK, 11 rows affected (0.34 sec)
Records: 11 Duplicates: 0 Warnings: 0 |
In Example 17.20, the teams table is altered by making the name field a primary key. This means that all new teams must have unique names.
1 mysql> ALTER TABLE teams MODIFY name VARCHAR(100) NOT NULL, --> ADD PRIMARY KEY(name); Query OK, 10 rows affected (0.06 sec) Records: 10 Duplicates: 0 Warnings: 0 2 mysql> DESCRIBE teams; +--------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+-------+ | name | varchar(100) | NO | PRI | NULL | | | wins | int(10) unsigned | YES | | NULL | | | losses | int(10) unsigned | YES | | NULL | | +--------+------------------+------+-----+---------+-------+ 3 rows in set (0.05 sec) |
To drop a table is relatively simple. Just use the DROP command and the name of the table.
mysql> DROP TABLE teams;
Query OK, 5 rows affected (0.11 sec) |
To drop a database, use the DROP DATABASE command.
mysql> DROP DATABASE sample_db;
Query OK, 1 row affected (0.45 sec) |
|
|