This part is important part of the MySQL manual and you should be aware of all the parts will be organized focusing briefly describes the content is not supposed to. Included in the actual distribution of the large amount of manual boasts more than 600 pages and is made of very alchage. Even in English, but my spare time when you have time to read difficult aneuni will help you a lot. Appendix CD-ROM's / MySQL / manual are included in the original manual.
Four. The main SQL syntax MySQL
▶ CREATE DATABASE
Syntax: CREATE DATABASE db_name
The name given to a new database is created. The available names are mentioned in the first part of this chapter. Ten thousand days of the same name, the database already exists when you get errors. MySQL is a database file to a directory to manage the table just because the database was created, if the table does not exist and is actually the given name of the CREATE DATABASE command is responsible for creating an empty directory.
Also use the mysqladmin utility, you can create a database.
#. / Mysqladmin create school
▶ DROP DATABASE
Syntax: DROP DATABASE [IF EXISTS] db_name
DROP DATABASE given the first drop all tables in the database and the database is deleted. Once again, to recover a deleted because there is no way to be careful with. This command existed in the database directory to tell the number of files typically three times the number of tables that actually exist. This table is an index of the file `. ISD ', which stores the actual data`. ISM' files and the schema of a table (structure) to store `. Frm 'file consists of, because ...
Since version 3.22 from the IF EXISTS keyword was added if the database does not exist is to prevent errors. Of course, also use the mysqladmin utility, you can delete the database.
#. / Mysqladmin drop school
▶ CREATE TABLE: table creation
Syntax: CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition, ...) [table_options] [select_statement]
create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] KEY(index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or CHECK (expr)
type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY]
or VARCHAR(length) [BINARY]
or DATE
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or LONGTEXT
or ENUM(value1,value2,value3,...)
or SET(value1,value2,value3,...)
index_col_name:
col_name [(length)]
reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
type = [ISAM | MYISAM | HEAP]
or max_rows = #
or min_rows = #
or avg_row_length = #
or comment = "string"
or auto_increment = #
select_statement:
[ | IGNORE | REPLACE] SELECT ... (Some legal select statement)
CREATE TABLE that are currently in use to create new tables in the database. If the data is not selected database tables with the same name exists, or if an error occurs.
MySQL 3.22 and later versions, such as db_name.tbl_name specify the name of the table to be able to give the selected database is not required. Starting with version 3.23 also provides IF NOT EXISTS keywords to use it only if the table does not exist generates an error occurs'm in does not already exist.
Each table tbl_name of the database, the following three files in the directory exists.
File function
tbl_name.frm table structure definition file
tbl_name.ISD data file
tbl_name.ISM index file
Ten thousand days NULL or NOT NULL column is not defined as NULL columns are made automatically.
BLOB and TEXT columns, you can specify a NOT NULL, NULL is defined as always. AUTO_INCREMENT integer column you can use keywords. AUTO_INCREMENT columns defined as NULL or 0 if you enter in the appropriate column of the input than the maximum value is increased by 1. AUTO_INCREMENT column should begin with 1. Row with the maximum value of ten thousand days to be deleted, enter new rows, the value is used again. Of a table can be only one AUTO_INCREMENT column and must be used only as an index must be defined.
Ten thousand and one DEFAULT option is used, the column is not NULL, NULL if the column is NOT NULL column values in one of the following values are assigned automatically.
AUTO_INCREMENT columns, 0 if non-numeric
If a non-TIMESTAMP type column of the type naljjahyeong zero value (zero value)
Column is an empty string literal
UNIQUE column must have a different value for each row. Equal to the value of ten thousand and one existing row is entered, an error is due to an error,
KEY is another name for the INDEX.
A multiple-column PRIMARY KEY can be used. Later in the column definition can not just use PRIMARY KEY (index_col_name, ...) should write as another line.
The index if you do not specify a name in the first number in the index column is automatically created in the form attached,
The index can not use the keyword NULL column. So you do not declare the NOT NULL or an error occurs
BLOB and TEXT columns cannot be indexed.
Only part of a CHAR or VARCHAR column col_name (length) in the same way as the index is the index of the file size smaller.
TEXT or BLOB column using ORDER BY or GROUP BY clause at the beginning of the column max_sort_length variable is used only in the defined length. FOREIGN KEY, CHECK, REFERENCES syntax compatibility with other database exists for holding grudges and not actually have any effect.
▶ ALTER TABLE
Syntax: ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_spec:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
or MODIFY [COLUMN] create_definition
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX key_name
or RENAME [AS] new_tbl_name
or table_option
The ALTER TABLE command to change the structure of the table that is created allows. For example, add or delete columns, can be changed and the index can be created or deleted
ALTER TABLE command to copy the original table into a temporary table to apply the changes and rename the new table and delete the original table will be. By doing so, change the structure of a table that occurs during the update is done without fail. Changed the table until ready to write new and renewal will be delayed for a while.
In order to use the ALTER TABLE command on a table select, insert, delete, update, create, drop must have permission.
IGNORE keyword is a MySQL extension. Using this index, based on existing data in the column can be controlled using the IGNORE that changes in the tables that exist in the index column as the index column is deleted, all rows except the first row.
Several of the ADD, ALTER, DROP, CHANGE syntax of an ALTER TABLE command can be used in
CHANGE col_name, DROP col_name, DROP INDEX is a MySQL extension.
About the ALTER TABLE MODIFY is an extension of Oracle.
COLUMN keyword can be omitted
Without any options, ALTER TABLE tbl_name RENAME AS new_name syntax you can simply change the name of the table.
Also use CHANGE old_col_name create_definition statements can change the column name. The old and new column names and column names are the types of columns is to write. For example, the INTEGER column named b a If you want to be as follows.
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
Ten thousand days without changing the name of the column type to replace only two times consecutively the same name, is to write, which is the same old name and new name is meant.
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
MODIFY MySQL 3.22.16 and later versions support syntax without specifying a new name as follows: the column was able to change people.
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
Ten thousand days using CHANGE or MODIFY the column index of the ten thousand and two took some part to reduce the length of the column when the size of the index lower than that small can not be changed.
DROP INDEX removes an index. MySQL does not support the ANSI SQL92, which is an extension
That serve as the index column is deleted, the index created on that column is also automatically supports.
DROP PRIMARY KEY will remove the primary key index. Ten thousand days if there is no primary key exists in the table earlier in the UNIQUE index deletes.
FOREIGN KEY, CHECK REFERENCES option is actually no action. These things do exist only for backward compatibility only. On the use of ALTER TABLE below are some examples.
Create a table t1.
mysql> CREATE TABLE t1 (a INTEGER, b CHAR (10));
Changes to table t1, t2 is the name of.
mysql> ALTER TABLE t1 RENAME t2;
The column a from INTEGER to TINYINT NOT NULL column type change only CHAR (10) column b, CHAR (20) and put in the name of the c.
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE bc CHAR (20);
add a new TIMESTAMP column named d.
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
Create an index on column d and column a the primary key made.
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
Column c is removed.
mysql> ALTER TABLE t2 DROP COLUMN c;
Add an AUTO_INCREMENT column c. NOT NULL AUTO_INCREMENT column is the index of the fact that the Notice must be created.
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);
▶ OPTIMIZE TABLE
Grammar: OPTIMIZE TABLE tbl_name
OPTIMZE TABLE on a table worth of data deleted or VARCHAR, BLOB, TEXT, such as if the event of significant changes in variable width columns are used. Deleted data Linked INSERT command followed by the remaining space, so storage space reuse is discontinuous. OPTIMIZE TABLE command will return the unused space.
OPTIMIZE TABLE to create a copy of the original table will work. Previously unused space on the table sum up the new table is created, the previous table is deleted and the name of a temporary table is deleted, as the table changes. In this way, the update is successful because all the OPTIMIZE TABLE command is executed even while the original table that can read and write data updates, and the newly created table will be a delay until you are ready.
▶ DROP TABLE
Syntax: DROP TABLE [IF EXISTS] tbl_name [, tbl_name, ...]
DROP TABLE to delete one or more tables. All the data and the structure of the table within a table definition for use in an instant gone, be careful. From future versions of MySQL 3.22 with the addition of IF EXISTS keyword if you do not have a table was able to prevent errors caused.
▶ DELETE
Syntax: DELETE [LOW_PRIORITY] FROM tbl_name [WHERE where_definition] [LIMIT rows]
DELETE deletes a row that satisfies the condition after a given number of rows deleted is returned.
If you omit the WHERE clause, ten thousand days, all rows are deleted. In this case, MySQL is the first to create a new empty table with the same name, this method is faster than erasing each row. The number of rows deleted will return 0.
We have some time, although it may take a few more if you want to know if the row is deleted, do not omit the WHERE clause as follows: Give a written description is always true.
mysql> DELETE FROM tbl_name WHERE 1> 0;
Of course, without a WHERE clause is omitted in this case both are much slower than clear.
Ten thousand days LOW_PRIORITY using keywords used by all users of that table, the execution of the DELETE is delayed until the end.
The data area data being erased by adding a new discrete filled because the OPTIMIZE TABLE command and isamchk utility allows you to speed is increased. Isamchk the other hand, OPTIMIZE TABLE, easy-to-use utility recover faster.
MySQL only possible using the LIMIT keyword to specify the number of rows to be deleted at once, can give. How much data it can not be predicted whether the deletion can be used to save time. If you use LIMIT as well as repeat any material that meets the conditions can be deleted.
▶ SELECT
Syntax: SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [DISTINCT | ALL]
select_expression,...
[INTO OUTFILE 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
SELECT more than one table is used to import data from.
select_expression column indicates the data is imported. SELECT statement, rather than the columns of the table represent the result of a simple operation or function can be used even if. Here's an example:
mysql> SELECT 1 + 1;
-> 2
All keywords have to come in the order shown in the above usage. For example, the back and ORDER BY GROUP BY HAVING clause always has to come in between.
With AS alias column name (alias) can be written as: ORDER BY, or HAVING clause aliases can be used.
mysql> select concat (last_name, ',', first_name) AS full_name from mytable ORDER BY full_name;
FROM table_references table indicates the data is imported. Ten thousand days when using two or more tables will have a join.
The column name col_name, tbl_name.col_name, db_name.tbl_name.col_name in the same way as you can see. However, two or more columns in a table that is common to use unless you can safely skip.
Data for the alignment of the ORDER BY, GROUP BY section, column names and aliases of the columns, the column index (starting from 1) may be used.
mysql> select college, region, seed from tournament
ORDER BY region, seed;
mysql> select college, region AS r, seed AS s from tournament
ORDER BY r, s;
mysql> select college, region, seed from tournament
ORDER BY 2, 3;
In order to sort in descending order using the DESC keyword is To sort in ascending order by default to use the ASC keyword can be omitted because it is.
Select_expression shown in the HAVING clause column, columns, and can refer to the alias. This section just before the output data is processed in the last. Thus, within the WHERE HAVING clause must be used in the column should be writing. The following example is incorrect.
mysql> select col_name from tbl_name HAVING col_name> 0;
We will have the following.
mysql> select col_name from tbl_name WHERE col_name> 0;
MySQL 3.22.5 and later versions can be written as follows.
mysql> select user, max (salary) from users group by user HAVING max (salary)> 10;
Version, instead of nine cases and write something like this:
mysql> select user, max (salary) AS sum from users group by user HAVING sum> 10;
Data represented by the LIMIT clause in the SELECT statement allows you to adjust the number of. LIMIT one or two factors may have When used with two arguments in front of ten thousand and one factor is the number of rows to skip. Import into the argument after the maximum number of rows. If there is only one factor used is considered as the first argument is zero is to bring as many rows as the given value. In other words, LIMIT n is LIMIT 0, n. And is the same.
mysql> select * from table LIMIT 5,10; # 6-15 rows
If one argument is given, it indicates the maximum number of rows to return.
mysql> select * from table LIMIT 5; # The first five rows
SELECT ... INTO OUTFILE 'file_name' to save the imported data to a file, the file is created on the server and file of the same name must not already exist. In addition, the user must have to have file permissions.
▶ JOIN
Within a SELECT statement, MySQL supports the following JOIN statement.
Grammar: table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{Oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr}
At the end of any ODBC-compliant LEFT OUTER JOIN syntax is there for.
See the table tbl_name AS alias_name or tbl_name alias_name the same way as can be.
mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
ON condition for being used in a WHERE clause is not allowed.
LEFT JOIN on a table by ten thousand and one right hand that meets the conditions in the absence of data is NULL, all columns of the table ohreunpyeo imaginary line is processed. Using these characteristics of a table that exists only in the number of data are available.
mysql> select table1. * from table1 LEFT JOIN table2 ON table1.id = table2.id where table2.id is NULL; data in the above example of table1 table2 id value does not exist in the data brings all.
USING column_list section must be used both tables, column names must exist in both. Below is an example:
A LEFT JOIN B USING (C1, C2, C3, ...)
This statement can be written as follows.
A.C1 = B.C1 AND A.C2 = B.C2 AND A.C3 = B.C3, ...
That only a few examples include the following:
mysql> select * from table1, table2 where table1.id = table2.id;
mysql> select * from table1 LEFT JOIN table2 ON table1.id = table2.id;
mysql> select * from table1 LEFT JOIN table2 USING (id);
mysql> select * from table1 LEFT JOIN table2 ON table1.id = table2.id LEFT JOIN table3 ON table2.id = table3.id;
▶ INSERT
Syntax: INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name, ...)] VALUES (expression, ...), (...), ... or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name, ...)] SELECT ... or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name SET col_name = expression, col_name = expression, .
INSERT inserts new rows in the table.
The INSERT ... SELECT from another table, a SELECT statement is used to enter data. The row is inserted, the table tbl_name people. Column name or the SET clause is used to specify the column data is inserted.
Ten thousand and one INSERT ... VALUES or INSERT ... If the columns in the SELECT statement is specified, all columns of data each corresponding VALUES (), or by the SELECT statement must be provided. Ten thousand days of the order of columns in the table if you do not know if you use DESCRIBE tbl_name can be recognized easily.
expression values shown in part before the VALUES list are available. Consider the following example.
mysql> INSERT INTO tbl_name (col1, col2) VALUES (15, col1 * 2);
However, if an error occurs. You can refer to values from the front, please note that.
mysql> INSERT INTO tbl_name (col1, col2) VALUES (col2 * 2,15);
Ten thousand days LOW_PRIORITY using keywords being used by all other users to read from the table to finish the job will be delayed until the execution of an INSERT statement.
IGNORE keyword, such as the data that you insert a new key (PRIMARY or UNIQUE key) with a value of data is ignored, then the data is entered. Ten thousand days if you do not use INSERT IGNORE stops running and turns. The mysql_info () function used in the table can see how many rows are inserted.
If NOT NULL is entered in the column, NULL is ignored and the default value is entered.
Numeric values are entered in the column, if used beyond the scope has been changed to the nearest number that can be displayed are inserted.
CHAR, VARCHAR, TEXT, BLOB column position is greater than the maximum input that can be displayed if data is received, whichever is truncated input.
Enter the date in the column is not appropriate when the value of zero value (zero value) is entered into.
DELAYED option to run the INSERT statement can not wait to be very useful for users if there is. In this case, ten thousand days to use the SELECT statement if you give the priority to run first and then execute the INSERT statement is processed. This is not a MySQL extension to ANSI SQL92 is
Another advantage of the DELAYED option by many users when an INSERT happens once they collect the data to be inserted by the write operation is that In this case, when you insert each of the data is faster than the job.
▶ REPLACE
Syntax: REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name, ...)] VALUES (expression, ...) or REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name, ...)] SELECT . or REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name = expression, col_name = expression, ... REPLACE command with the INSERT command behaves similarly to a conventional one difference - the same as the value of the index column of the row to delete the existing row is entered, enter a new line made.
▶ LOAD DATA INFILE
Syntax: LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\ t'] [OPTIONALLY] ENCLOSED BY "] [ESCAPED BY '\ \']] [LINES TERMINATED BY '\ n'] [IGNORE number LINES] [(col_name, ...)] LOAD DATA INFILE command to read data from a text file, enter the table gives a very fast pace. LOCAL keyword is used on the client's host existing file should be used. If not, MySQL, the running files that exist on the server is used. files that exist on the server using the LOAD DATA INFILE command to execute the file must have permissions and security reasons that exist on the server When reading a file that exists in the directory data base that can be read by anyone, or should have permission.
Files that exist on the server using the following rules have to.
If the name is given as an absolute path as the path follows the
If you are using a relative path is given ten thousand days of the database is recognized as a relative path to the directory.
Image name without the path of the database you are using to find the files in the directory
REPLACE and IGNORE keywords entered into the index column, a row of data is controlled. Ten thousand days to use REPLACE the existing row is deleted and a new value is entered, IGNORE intimate all use the new value is ignored and the existing value is preserved. If there are ten thousand days ohruga keyword occurs.
If you use the keyword FIELDS TERMINATED BY, [OPTIONALLY] ENCLOSED BY, ESCAPED BY option should be used more than one of the ten thousand and one must not use the FIELDS keyword is the default if you like, and then gave the order.
FIELDS TERMINATED BY '\ t' ENCLOSED BY "ESCAPED BY '\ \'
If you do not use the LINES keyword, and then gave the orders is the same as the default value.
LINES TERMINATED BY '\ n'
In other words, the default value of LOAD DATA INFILE command, as follows:
The return value of each line (newline) is divided into.
Each column is separated by tabs.
Each data does not use quotation marks.
Line as the `\ 'character is used ▶ UPDATE
Syntax: UPDATE [LOW_PRIORITY] tbl_name SET col_name1 = expr1, col_name2 = expr2, ... WHERE where_definition] UPDATE command for each column in the table shall be updated with a new value. SET statement is used to specify a new value column and change. Ten thousand days, when given the WHERE clause is updated only those rows that satisfy the condition is omitted, all rows are updated.
LOW_PRIORITY option is used, all other clients on that table read operation is delayed until the end of the execution of UPDATE.
SET When you use column names in the syntax of the table is stored in the corresponding column values are used in the column under the age of increasing the current value as a example.
mysql> UPDATE persondata SET age = age +1;
UPDATE statement, each operation is run from left to right. Column under the age of the current value increases by 1 to 2 times, then examples.
mysql> UPDATE persondata SET age = age * 2, age = age +1;
Wait: If the current value, MySQL ignores it and the same value if the update does not run.
▶ USE
Grammar: USE db_name
USE command to change the database to use.
mysql> USE db1;
mysql> SELECT count (*) FROM mytable; # selects from db1.mytable
mysql> USE db2;
mysql> SELECT count (*) FROM mytable; # selects from db2.mytable
USE command to the database with a specific database by name give an existing table in another database can be used.
The following example database db1 and db2 that exist in the author table in a database table editor shows how to use.
mysql> USE db1;
mysql> SELECT author_name, editor_name FROM author, db2.editor WHERE author.editor_id = db2.editor.editor_id; USE command is provided for compatibility with Sybase.
▶ FLUSH
Grammar: FLUSH flush_option [, flush_option]
Used by MySQL Clears the internal cache is used to read the commands. To use this command must have the reload privilege.
flush_option have the following options.
HOSTS
The host table is read again. You want to connect to the host's IP has changed or if you add a new host, and "Host ... is blocked" error message or the host table when you must re-reads. More than up to the host access error if they attempt to connect (max_connect_errors), and this occurs when something goes wrong, MySQL, and that the judgment is registered in the host table the host even more will be denied access. To prevent these errors, such as the following by a very large number are ttuiwoomyeon daemon.
shell> mysqld-O max_connection_errors = 999999999
Creates a new empty log file is LOGS. Ten thousand days to specify the name of the log file without an extension give that old log files automatically in the name of the file name is incremented by 1 is formed.
PRIVILEGES mysql rights management table in the database is read again.
TABLES Closes all open tables.
STATUS all the state variables (status variables) to zero makes
You can also use the mysqladmin utility as follows: the same thing could be done.
shell> mysqladmin flush-hosts, flush-logs, reload, flush-tables
▶ KILL
Syntax: KILL thread_id
Each connection to mysqld by will operate as an independent thread. By SHOW PROCESSLIST command threads can be seen running by the KILL thread_id command can kill a thread. If you have the PROCESS privilege can see all the threads, otherwise you can only see their own threads.
Check the thread below and in the same way as you are able to kill.
shell> mysqladmin processlist and shell> mysqladmin kill thread_id
▶ SHOW
Tables, columns, and other information on the system.
Syntax: SHOW DATABASES [LIKE wild] or SHOW TABLES [FROM db_name] [LIKE wild] or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild] or SHOW INDEX FROM tbl_name [FROM db_name] or SHOW STATUS or SHOW VARIABLES [LIKE wild ] or SHOW PROCESSLIST or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
SHOW command, the database, table, column, MySQL server, information about shows. If you use a LIKE statement `% 'and` _' wildcard characters can be used
db_name.tbl_name in the same format as the tbl_name FROM db_name can be used. Therefore, the following two sentences are the same function.
mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;
SHOW DATABASES - MySQL database that exists on the server host, showing a list. Mysqlshow command shell prompt, the same function is over.
SHOW TABLES - that exist within the selected database table shows Mysqlshow db_name shell prompt are over.
Note: If you do not have permission to nothing on the table, the results do not appear.
SHOW COLUMNS displays information about the columns in the table. Also plays a role similar to the DESCRIBE command.
SHOW TABLE STATUS - 3.23 version has been added. SHOW STATUS and similar features, but detailed information on each table can be obtained. Shell prompt mysqlshow - status db_name command can achieve the same result through.
The following information is shown.
Name the table name
Type Table Type (NISAM, MYISAM, HEAP)
Rows of columns
The average length of the column Avg_row_length
Data_length the size of a data file
The maximum size of data files Max_data_length
The size of the index file Index_length
Data_free in the data file size of allocated but unused space
Auto_increment Auto_increment the following values of the column
Create_time table creation time of
Update_time table's last modified time
Hours last checked Check_time table
When the table was created Create_min_rows "min_rows" option whether to use
When the table was created Create_max_rows "max_rows" option whether to use
When the table was created Create_avg_row_length "avg_row_length" option whether to use
When the table was created, you entered a comment Comment
SHOW FIELDS - SHOW COLUMNS and SHOW KEYS is or columns of the table shows, or index information.
SHOW INDEX - The following index shows the information
Table table name
Non_unique index equal to zero if not stored
The name of the index Key_name
Seq_in_index first column in the index is a few, starting with 1.
The column name Column_name
Collation index is stored, but how. A (Sort), NULL (Not sorted).
Cardinality The number of index only data that exist within
If only part of the column to the index Sub_part size. NULL is used, the entire ten thousand and one columns
SHOW STATUS - displays information about the status of the server. Sweb mysqladmin extended-status command at the prompt as. The resulting value will be similar to that shown below.
+ -------------------------- + -------- +
| Variable_name | Value |
+ -------------------------- + -------- +
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Created_tmp_tables | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 2 |
| Handler_delete | 2 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 0 |
| Handler_read_rnd | 35 |
| Handler_update | 0 |
| Handler_write | 2 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 1 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1 |
| Open_files | 2 |
| Open_streams | 0 |
| Opened_tables | 11 |
| Questions | 14 |
| Running_threads | 1 |
| Slow_queries | 0 |
| Uptime | 149111 |
+ -------------------------- + -------- +
Each entry shows the following information.
Aborted_clients client does not exit the normal number of connections is broken
Aborted_connects MySQL tries to connect but failed seobeone number of connections
Created_tmp_tables SQL statement is executed, the number of tables generated during the interim
INSER Delayed_insert_threads delay your door (delayed insert handler) Wed
By Delayed_writes INSERT DELAYED rows written.
Failure by Delayed_errors INSERT DELAYED rows
Flush_commands FLUSH command, the number of executions
Wed teyibeulnaehaeng Handler_delete delete command
First, the number of read commands Handler_read_first table
Handler_read_key index-based table read instruction count
Sort Handler_read_next according to the index number of commands executed to read the next row
Read command is executed on a table row Handler_read_rnd Wed monospace
Handler_update be running the table the UPDATE command
Handler_write be running the table INSERT command
Key_blocks_used index cache blocks used by
Key_read_requests number of commands processed by the index cache
Key_reads physical disk reads
Key_write_requests number of commands stored in the cache index blocks
Key_writes number of commands stored in physical disk blocks
The maximum number of connections that can be processed simultaneously Max_used_connections
Not_flushed_key_blocks index information in the cache has changed, but not yet saved to disk blocks
Not_flushed_delayed_rows INSERT DELAY The number of rows processed by the pending
Open_tables number of tables currently open for use
Open_files number of files opened
Log number of connections connected to Open_streams
Until now, the number of tables open Opened_tables
Questions Questions requested by the server
Connections are connected to Running_threads Thread Wed
Slow_queries long_query_time defined in the run-time than the time exceeded the number of requests
Uptime server start elapsed time after
SHOW VARIABLES
MySQL is the output of the system variables. Mysqladmin variables command from the system prompt, the same information can be seen through. To change the default values of some variables to mysqld is run by giving the command-line options.
The results will be similar to the following.
+ ------------------------ + ------------------------ - +
| Variable_name | Value |
+ ------------------------ + ------------------------ - +
| Back_log | 5 |
| Connect_timeout | 5 |
| Basedir | / my / monty / |
| Datadir | / my / monty / data / |
| Delayed_insert_limit | 100 |
| Delayed_insert_timeout | 300 |
| Delayed_queue_size | 1000 |
| Join_buffer_size | 131072 |
| Flush_time | 0 |
| Key_buffer_size | 1048540 |
| Language | / my / monty / share / english / |
| Log | OFF |
| Log_update | OFF |
| Long_query_time | 10 |
| Low_priority_updates | OFF |
| Max_allowed_packet | 1048576 |
| Max_connections | 100 |
| Max_connect_errors | 10 |
| Max_delayed_threads | 20 |
| Max_heap_table_size | 16777216 |
| Max_join_size | 4294967295 |
| Max_sort_length | 1024 |
| Max_tmp_tables | 32 |
| Net_buffer_length | 16384 |
| Port | 3306 |
| Protocol-version | 10 |
| Record_buffer | 131072 |
| Skip_locking | ON |
| Socket | / tmp / mysql.sock |
| Sort_buffer | 2097116 |
| Table_cache | 64 |
| Thread_stack | 131072 |
| Tmp_table_size | 1048576 |
| Tmpdir | / machine / tmp / |
| Version | 3.23.0-alpha-debug |
| Wait_timeout | 28800 |
+ ------------------------ + ------------------------ - +
MySQL administration and tuning in the next chapter with a description of each parameter tuning method discussed.
SHOW PROCESSLIST
Show threads currently connected.
shell> mysqladmin processlist command can also use the same information can be obtained. By default, the information can only see their own thread and if you have the PROCESS privilege gives you information about all threads. In this case, use the KILL command to kill the thread can be
▶ LOCK TABLES / UNLOCK TABLES
Grammar: LOCK TABLES tbl_name [AS alias] READ | [LOW_PRIORITY] WRITE [, tbl_name READ | [LOW_PRIORITY] WRITE ...] ... UNLOCK TABLES LOCK TABLES command on the table for the current thread lock (lock) and hangs. Sseuredeuran connect to the database can be thought of as the access unit. UNLOCK TABLES all locks taken by the current thread contents. Locked by a thread of all the tables into another table lock this thread that locks automatically when disconnected, or is revealed.
If one thread, a table read (READ) locks the user only dials the thread can read the table.
Also, write a thread (WRITE), if a lock that only the user table of the thread will be able to read and write. The table lock is taken on other threads will wait until it disengages.
In general, the priority is rakboda nopeunde read write lock when it updates as soon as possible to ensure that progress is In other words, to read a thread lock thing on the table while another thread writes to a table lock, the lock dials thing before reading the thread will wait until the write to be unlocked.
In general, only one table to update me if you do not need to lock. Because of a thread if you are running SQL statements from the other thread because the thread can interfere with. Sometimes it may be necessary locks exposed.
Number of consecutive series of ten thousand and one on the table if you need to work more if you want to use the lock on the table can be faster. MySQL is a transaction (transaction) does not provide functionality between ten thousand and one SELECT and UPDATE not cut to prevent other threads must use LOCK TABLES command.
This series of cases to handle the job safely is below.
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum (value) from trans where customer_id = some_id;
mysql> update customer set total_value = sum_from_previous_statement
where customer_id = some_id;
mysql> UNLOCK TABLES;
In this case, ten thousand days if you do not use LOCK TABLES command, SELECT and UPDATE command is inserted between the threads of other users can.
▶ GRANT / REVOKE
Grammar:
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON tbl_name | * | *. * | Db_name. *
TO user_name [IDENTIFIED BY 'password']
[, User_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON tbl_name | * | *. * | Db_name. *
FROM user_name [, user_name ...]
GRANT and REVOKE commands to the system administrator database user of the following four steps will help grant or revoke permissions.
commands allow system administrators to grant and revoke rights to MySQL users at four privilege levels:
Global Level
This privilege for all databases on the server with the privileges of the mysql database is stored in the user table.
Database Level
That exist in a specific database privilege for all tables in the mysql database db, host are stored in tables.
Table level
For all columns in a table in the mysql database tables_priv privileges are stored in tables.
Column level
Specific for a single column in a table with the privileges of the mysql database table is stored in columns _priv.
Their privileges a user to grant other users (GRANT) to wrest authority is as follows.
REVOKE GRANT OPTION ON priv FROM user;
Permission is available for tables SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, INDEX, ALTER. Include:
▶ CREATE INDEX
The index is created.
Syntax: CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name [(length]), ...)
CREATE INDEX syntax for MySQL 3.22. In previous versions do not, no action. Since version 3.22 the CREATE INDEX ALTER TABLE syntax to create an index, such as plays a role.
In general, all the indexes using the CREATE TABLE statement to create a table at the time of creation, but the CREATE INDEX syntax that already exists, you can add an index on a table. A list of columns in parentheses (col1, col2, ...) and listed as a multi-column index can be made by. The index value is generated by a combination of the two columns is created.
The index generated by a subset of the columns in CHAR and VARCHAR columns col_name (length) specifying length as giving only a portion of the column can be used.
The beginning of the name column in the example below to make the index shows that only 10 digits.
mysql> CREATE INDEX part_of_name ON customer (name (10));
In this case, the full name search will be slower than creating index, but can save a lot of disk space and speeds up when INSERT is performed.
▶ DROP INDEX
Syntax: DROP INDEX index_name
The index is deleted. DROP INDEX syntax for MySQL 3.22. In previous versions do not, no action. 3.22 In a future version to delete the index DROP INDEX and ALTER TABLE syntax is the same role.
