MySQL in a Nutshell [61]
ALTER TABLE clients
ROW_FORMAT = FIXED;
The RAID_TYPE option is used to specify the type of Redundant Arrays of Independent Disks (RAID) to be used. However, support for RAID has been removed from MySQL as of version 5.0. This SQL statement is also used to permit the options RAID_CHUNKS and RAID_CHUNKSIZE. They have been deprecated, as well.
For MERGE tables in which you want to change the tables that make up the merged table, use the UNION option:
ALTER TABLE sales_national
UNION = (sales_north, sales_south, sales_east, sales_west);
See the CREATE TABLE statement later in this chapter for more information and examples regarding many of the options for the ALTER TABLE statement.
Name
ALTER VIEW
Synopsis
ALTER
[ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
[DEFINER = {'user'@'host'|CURRENT_USER}]
[SQL SECURITY {DEFINER|INVOKER }]
VIEW view [(column, ...)]
AS SELECT...
[WITH [CASCADED|LOCAL] CHECK OPTION]
Use this statement to change a view. Views are available as of version 5.0.1 of MySQL.
The statement is used primarily to change the SELECT statement that determines the view, which you can do simply by placing the new SELECT statement for the view after the AS keyword.
Change the column names provided by the view queries by providing the new column names in a comma-separated list within the parentheses following the view’s name. Don’t include either the old SELECT statement or the old column names in the statement.
The ALGORITHM parameter changes algorithmic methods to use for processing a view: the choices are MERGE or TEMPTABLE. TEMPTABLE prevents a view from being updatable.
The DEFINER clause can change the user account considered to be the view’s creator. This clause is available as of version 5.1.2 of MySQL. The same version introduced the related SQL SECURITY clause. It instructs MySQL to authorize access to the view based on the privileges of either the user account of the view’s creator (DEFINER) or the user account of the user who is querying the view (INVOKER). This can help prevent some users from accessing restricted views.
The WITH CHECK OPTION clause can change the restrictions on the updating of a view to only rows in which the WHERE clause of the underlying SELECT statement returns true. For a view that is based on another view, if you include the LOCAL keyword, this restriction will be limited to the view in which it’s given and not the underlying view. If you specify CASCADED instead, underlying views will be considered as well.
Here is an example of this statement’s use:
ALTER VIEW student_directory(ID, Name, Cell_Telephone, Home_Telephone)
AS SELECT student_id,
CONCAT(name_first, SPACE(1), name_last),
phone_dorm, phone_home
FROM students;
If you look at the example for CREATE VIEW later in this chapter, you’ll see that we’re adding an extra column to the view created in that example. The other settings remain unchanged.
You cannot change the name of an existing view. Instead, use the DROP VIEW statement and then create a new view with the CREATE VIEW statement.
Name
CREATE DATABASE
Synopsis
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] database [options]
This statement creates a new database with the name given. As of version 5.0.2 of MySQL, the keyword DATABASE is synonymous with SCHEMA wherever used in any SQL statement. You can use the IF NOT EXISTS flag to suppress an error message when the statement fails if a database with the same name already exists.
A database name cannot be longer than 64 bytes (not characters) in size. The system uses Unicode (UTF-8), so any character that is part of the UTF-8 character set may be used. The name