MySQL 4.1.0 Chinese Reference Manual--6.1 language structure

mysql| Reference | Reference manual | Chinese MySQL 4.1.0 chinese reference manual---dog (heart sail) Translate MySQL Reference Manual for version 4.1.0-alpha.


6 MySQL Language Reference
MySQL has a complex, but intuitive, and easy to learn SQL interface. This section describes the various commands, types, and functions that you need to know to efficiently use MySQL. This section can also be considered as a reference to all the features contained in MySQL.
6.1 Language Structure 6.1.1 writings: How to write strings and numbers


This section describes various ways to write strings and numbers in MySQL. It also contains questions about the various differences and "gotchas" that you may get into when dealing with the basic types of MySQL.
6.1.1.1 string
A string is a sequence of characters consisting of multiple characters, surrounded by single quotes ("'") or double quotes ("") characters. (You can only use single quotes when running in ANSI mode.)

For example:

' A string ' "another string"

In a string, if a sequence has a special meaning, each sequence begins with a backslash ("\"), called an escape character. MySQL recognizes the following escape characters:
An ASCII 0 (NUL) character. \ ' An ASCII 39 single quotation mark ("'") character. \ "An ASCII 34 double quotation mark (" ") character. \b An ASCII 8 backspace. \ n an ASCII 10 line break. \ r An ASCII 13 back-car character. \ t an ASCII 9 tab (tab). \z ASCII (control-z). This character can be handled in Windows system ASCII (26) represents the end of a file problem. (ASCII (26) may cause problems when using MySQL database < filename. \ \ An ASCII 92 backslash ("\") character. \% an ASCII 37 "%" character. It is used to search for text instances of "%" in the body, otherwise this "%" will be interpreted as a wildcard character. View Chapter 6.3.2.1 string comparison functions. \_ an ASCII 95 "_" character. It is used to search for text instances of "_" in the body, otherwise this "_" will be interpreted as a wildcard character. View Chapter 6.3.2.1 string comparison functions.
Note If you use "\%" or "\_" in some body contexts, you will return the string "\%" and "\_" instead of "%" and "_".

There are several ways in which a string can contain quotes:
A string is referenced in single quotation marks "'", and the single quote "'" character in the string can be escaped with "". A string is referenced in double quotation marks "", and the character "" in the string can be escaped by using the "" ". You can also continue using the escape character "\" in front of the quotation marks. A string is quoted in double quotes "" "and the single quotation mark in the string" ' "does not require special treatment and does not have to be duplicated or escaped. Similarly, a string is referred to as a single quote "'", and the double quotation mark "" In the string does not require special treatment and does not have to be duplicated or escaped.
The following shows how the SELECT demo quotes and escapes work:

mysql> SELECT ' hello ', ' hello ' ', ', ' ' Hello ' ', ' hel ', ' lo ', ' hello '; +-------+---------+-----------+--------+----- ---+| Hello | "Hello" | "" Hello "" | Hel ' lo | ' Hello |+-------+---------+-----------+--------+--------+mysql> SELECT ' hello ', ' hello ' ', ' ' Hello ' ', ' hel ', ' lo ' , "Hello"; +-------+---------+-----------+--------+--------+| Hello | ' Hello ' | ' Hello ' | Hel "Lo | "Hello |+-------+---------+-----------+--------+--------+mysql> SELECT" this\nis\nfour\nlines "; +-------------- ------+| Thisisfourlines |+--------------------+



If you want to insert binary data into a field of a character type (for example, a blob), the following characters must be represented by an escape sequence: NUL ASCII 0, you should represent it with a backslash and an ASCII "0" character. \ ASCII 92, backslash. Need to be represented by "\". ' ASCII 39, single quotes. Needs to be represented by "\". "ASCII 34, double quotes." Needs to be represented by "\".
If you write C code, you can use the C API function mysql_real_escape_string () to escape characters for INSERT statements. View Chapter 8.1.2 C API Functions Overview. In Perl, you can use the quote method in the DBI package to convert these special characters into appropriate escape sequences. View Chapter 8.5.2 DBI interface.

You should use the Escape function in any string that may contain the special characters mentioned above!

In addition, many MySQL APIs provide placeholder processing capabilities that allow you to insert special tags in query statements and then bind data values to them when you execute the query. In this way, the API will automatically convert them for you from the numeric values.
6.1.1.2 Digital


An integer is represented as a sequence of numbers. Floating point numbers use "." As a decimal separator. These two types of numbers can be placed before '-' to represent a negative value.

Examples of valid integers:

12210-32

Examples of valid floating-point numbers:

294.42-32032.6809e+10148.00

An integer can be used in a floating-point context; it is interpreted as an equivalent floating-point number.
6.1.1.3 hexadecimal value


MySQL supports hexadecimal values. In the context of numbers, they behave like an integer (64-bit precision). In the context of strings, they behave like a binary string, and each pair of hexadecimal digits is converted to one character:

mysql> SELECT x ' 4d7953514c '; -> mysqlmysql> SELECT 0xa+0; -> 10mysql> SELECT 0x5061756c; -> Paul

Expression x ' hexstring ' (newly added in 4.0) is based on ANSI SQL, and the expression 0x is ODBC based. Hexadecimal strings are typically used by ODBC to assign values to a BLOB type field. You can convert a string or a numeric value to hexadecimal format by HEX ().
6.1.1.4 NULL Value


A null value does not imply "no data" and is different from an empty string of 0 or string types, such as a numeric type. View Chapter a.5.3 NULL value issues.

When using a text file to import and export a format (LOAD DATA infile,select ...). into outfile), NULL can be described with \ n. View Chapter 6.4.9 LOAD DATA INFILE syntax.
6.1.2 databases, tables, indexes, columns, and aliases


databases, tables, indexes, columns, and aliases are subject to the same rules as MySQL.

Note that the rule has changed since the MySQL 3.23.6, when the reference identifier (database, table, and field name) was introduced. If you are running in ANSI mode, "" "can also be used to refer to identifiers. View Chapter 1.8.2 running MySQL in ANSI mode.
Identifier maximum length allowed character database any characters allowed in 641 directory names except for "/", "\" or ".". Table 641 any characters allowed in the file name, except for "/" or ".". Column 64 all the characters. Alias 255 The character of the Hu.
Note that, in addition to the above, there can be no ASCII (0) or ASCII (255) or reference characters in an identifier.

Note that if the identifier is a restricted word or contains a special character, you must refer to it as a ' (Backtick) when you use it.

Mysql> SELECT * from ' SELECT ' WHERE ' select '. ID > 100;

View Chapter 6.1.7 is MySQL picky about reserved words?

In previous versions of MySQL 3.23.6, the naming rules were as follows:
A name can consist of text and numeric characters as well as "_" and "$" contained in the current character set. The default character set is Iso-8859-1latin1, which can be changed by changing the mysqld--default-character-set options. View Chapter 4.6.1 The character set used for data and sorting. A name can begin with any character that is valid in the name. In particular, a name can start with a number (unlike many other database systems!). )。 But a name cannot consist solely of numbers. You cannot use the "." character in a name because it is used to extend the format, and you can use it to refer to columns (see below).
It is not recommended to use names like 1e, since an expression 1e+1 is ambiguous. It can be interpreted as an expression of 1e + 1 or number 1e+1.

In MySQL, you can use any of the following tables to refer to a column:
Column reference meaning col_name column col_name the corresponding field tbl_name.col_name column from any table used by the query col_name from the table tbl_namedb_name.tbl_name.col_name column in the current database Col_name the table tbl_name from the database db_name. This form becomes available from MySQL 3.22 or later. ' column_name ' The field is a keyword or contains special characters.
In a column reference in a statement, there is no need to explicitly specify a tbl_name or db_name.tbl_name prefix unless the reference is ambiguous. For example, suppose that the table T1 and T2 both contain a field C when retrieving C with a SELECT that uses T1 and T2. In this case, C is ambiguous because it is not unique in the table used by this statement, so you must specify which table you want by writing t1.c or t2.c. Similarly, if you retrieve from the database DB1 table T and the database DB2 table T, you must use Db1.t.col_name and Db2.t.col_name to specify which library table's columns to refer to.

Syntax. Tbl_name means that the table tbl_name is in the current database. This syntax is intended to be compatible with ODBC because some ODBC programs prefix the table name with a "." character.
6.1.3-Letter case Sensitivity


In MySQL, databases and table pairs are located in directories and files in those directories. Thus, the sensitivity of the operating system determines the case sensitivity of database and table naming. This means that database and table names are case insensitive in Windows and are case-sensitive in most types of Unix systems. A special case is Mac OS X, when the default hfs+ file system is used. However, Mac OS X also supports UFS volumes, which are case-sensitive in Mac OS X as they are on any Unix. View Chapter 1.8.3 MySQL extensions to ANSI SQL92.

Note: Although database and table names are not case-sensitive in Windows, you should not use different capitalization in the same query to refer to a given database and table. The following query will not work because it references a table in my_table and my_table:

Mysql> SELECT * from my_table WHERE my_table.col=1;

The name of the column and the alias of the column are ignored in all cases.

The alias of the table is case-sensitive. The following query will not work because it refers to aliases with A and a:

Mysql> SELECT col_name from Tbl_name as a-> WHERE a.col_name = 1 OR a.col_name = 2;

If you have difficulty remembering the letter size of your database and table names, it is recommended that you use a consistent convention, such as always creating databases and tables in lowercase letters.

Another way to avoid this problem is to start mysqld with the-o lower_case_table_names=1 parameter. By default, this option is 1 in Windows and 0 in Unix.

If Lower_case_table_names is 1, MySQL converts all table names to lowercase when they are stored and found. (Starting with MySQL 4.0.2, this option also applies to database names.) Note that when you change this option, you must first convert the old table name to lowercase before starting mysqld.

If you move MyISAM from Windows to a Unix disk, in some cases you may need to use the "mysql_fix_extensions" tool to fix the file name extension (lowercase. frm ", uppercase letters") in the specified database directory. Myi "and". MyD "). "Mysql_fix_extensions" is stored in the "scripts" subdirectory.
6.1.4 User Variables


MySQL supports connection-specific (connection-specific) User variables, expressed in @variablename syntax. A variable name can consist of text and numeric characters as well as "_", "$", and "." that are contained in the current character set. The missing character set is Iso-8859-1 Latin1, which can be changed by changing the mysqld--default-character-set option. View Chapter 4.6.1 The character set used for data and sorting.

Variables do not have to be initialized. By default, their values are NULL and can store an integer, real, or string value. When the connection thread exits, all the variables for this thread are automatically freed.

You can set a variable by using the SET syntax:

SET @variable = {Integer expression | real expression | string expression}[, @variable = ...].

In a statement, you can assign a value directly to a variable in addition to the SET. In each case, however, the assignment operator is: = instead of =, because = is used for comparison in non-SET statements:

Mysql> SELECT @t1: = (@t2: =1) + @t3: =4, @t1, @t2, @t3, +----------------------+------+------+------+| @t1: = (@t2: =1) + @t3: =4 | @t1 | @t2 | @t3 |+----------------------+------+------+------+| 5 | 5 | 1 | 4 |+----------------------+------+------+------+

User variables can be used anywhere that an expression allows. Note that this does not apply in the context in which the number must be explicitly specified, for example, in the LIMIT clause of a SELECT or in the clause of the IGNORE number LINES of a LOAD DATA statement.

Note: In a SELECT statement, each expression can be evaluated only when it is sent to the client. This means that you cannot use an expression that contains a variable that is set in the SELECT part in a having, a GROUP by, or an ORDER BY clause. For example, the following statement will not work as expected:

Mysql> SELECT (@aa: =id) as A, (@aa +3) as B from TABLE_NAME has b=5;

The reason is because the @aa is not the value of the current row, but the ID value of the previous qualifying row.

A rule is to never assign values and use the same variable in the same statement.
6.1.5 System Variables


Starting with MySQL 4.0.3, we provide a better way to access a large number of system variables and connection variables. You can change the value of large variables without shutting down the server.

System variables can be divided into two types: thread-specific (thread-specific) or connection-specific (connection-specific) variables, which are unique to the current connection; Global variables that are used to set global events. The global variable is also used to set the initial value of the corresponding thread-specific variable for a new connection.

When Mysqld is started, all global variables are initialized with command line arguments and option file contents. These values can be changed through the SET GLOBAL command. When a new connection thread is established, thread-specific variables are initialized with global variable values, and thread-specific variables are not changed until you execute a new SET global command.

In order to set a global variable value, you can use any of the following syntax: (Here we take the sort_buffer_size variable as an example)

SET GLOBAL Sort_buffer_size=value; SET @ @global. Sort_buffer_size=value;

To set the value of a session variable, you can use any of the following syntax:

SET session Sort_buffer_size=value; SET @ @session. Sort_buffer_size=value; SET Sort_buffer_size=value;

If you do not explicitly specify GLOBAL or session, the default will be to set the session. View Chapter 5.5.6 SET syntax.

Local is a synonym for the session.

A global (global) variable value can be retrieved by any of the following commands:

SELECT @ @global. sort_buffer_size; Show GLOBAL VARIABLES like ' sort_buffer_size ';

You can retrieve a session variable value by using any of the following commands:

SELECT @ @session. sort_buffer_size; Show sessions VARIABLES like ' sort_buffer_size ';

When a value of a variable is retrieved using the @ @variable_name syntax, or when no GLOBAL or session is specified, MySQL returns it if the thread-specific (thread-specific) (session) value exists. If it does not exist, then MySQL returns the global variable value.

You need to use global to set global variables instead of retrieving them in order to refer to a thread-specific (thread-specific) variable of the same name later or to delete a thread-specific (thread-specific) with the same name Variable is not a problem. In this case, you may inadvertently change the state of the entire server rather than your own connection.

The following list is all the variables that you can change and retrieve using GLOBAL or session.
Variable name variable value type variable type autocommit BOOL sessionbig_tables bool sessionbinlog_cache_size num globalbulk_insert_buffer_size num GLOBAL | sessionconcurrent_insert bool Globalconnect_timeout num globalconvert_character_set string sessiondelay_key_write off | On | All globaldelayed_insert_limit num globaldelayed_insert_timeout num globaldelayed_queue_size num GLOBALerror_count num localflush bool Globalflush_time num globalforeign_key_checks bool sessionidentity num sessioninsert_id bool Sessioninteractive_timeout num GLOBAL | Sessionjoin_buffer_size num GLOBAL | Sessionkey_buffer_size num globallast_insert_id bool sessionlocal_infile BOOL globallog_warnings BOOL GLOBALlong_query _time num GLOBAL | sessionlow_priority_updates bool GLOBAL | Sessionmax_allowed_packet num GLOBAL | sessionmax_binlog_cache_size num globalmax_binlog_size num globalmax_connect_errors num globalmax_connections num Globalmax_error_count num GLOBAL | Sessionmax_delayed_threads num globalmax_heap_table_size num GLOBAL | SessionMAx_join_size num GLOBAL | Sessionmax_sort_length num GLOBAL | Sessionmax_tmp_tables num globalmax_user_connections num globalmax_write_lock_count num globalmyisam_max_extra_sort_ File_size num GLOBAL | Sessionmyisam_max_sort_file_size num GLOBAL | Sessionmyisam_sort_buffer_size num GLOBAL | Sessionnet_buffer_length num GLOBAL | Sessionnet_read_timeout num GLOBAL | Sessionnet_retry_count num GLOBAL | Sessionnet_write_timeout num GLOBAL | Sessionquery_cache_limit num globalquery_cache_size num globalquery_cache_type enum globalread_buffer_size num GLOBAL | Sessionread_rnd_buffer_size num GLOBAL | Sessionrpl_recovery_rank num globalsafe_show_database bool globalserver_id num GLOBALSLAVE_COMPRESSED_PROTOCOL bool Globalslave_net_timeout num globalslow_launch_time num globalsort_buffer_size num GLOBAL | sessionsql_auto_is_null BOOL sessionsql_big_selects BOOL sessionsql_big_tables BOOL Sessionsql_buffer_result BOOL sessionsql_log_binlog BOOL Sessionsql_log_off BOOL sessionsql_log_update BOOLsessionsql_low_priority_updates bool GLOBAL | Sessionsql_max_join_size num GLOBAL | sessionsql_quote_show_create BOOL sessionsql_safe_updates BOOL sessionsql_select_limit BOOL Sessionsql_slave_skip_ Counter num globalsql_warnings bool Sessiontable_cache num globaltable_type enum GLOBAL | Sessionthread_cache_size num globaltimestamp bool sessiontmp_table_size enum GLOBAL | sessiontx_isolation enum GLOBAL | sessionversion string Globalwait_timeout num GLOBAL | Sessionwarning_count NUM localunique_checks BOOL Session
A variable marked with NUM can set a numeric value. Variables marked with bool can be set to 0, 1, on, or off. A variable of type enum is usually set to one of the available values of the variable, but it can also be set to the corresponding number. (The first value of the enum is 0).

Here is a description of some of the variables:
Variable description Identity last_insert_id alias (Sybase compatible) sql_low_priority_updates low_priority_updates alias Sql_max_join_size max_ Join_size alias Delay_key_write_for_all_tables as it is set with Delay_key_write, then all newly opened MyISAM tables will use the delayed key writes.version Alias for VERSION () (Sybase (?) compatible)
A description of the other variables can be found in the Startup Options section, and show VARIABLES is described in the SET section. View Chapter 4.1.1 mysqld command line options. View Chapter 4.5.6.4 Show VARIABLES. View Chapter 5.5.6 SET syntax.
6.1.6 annotation Syntax




MySQL Server Support # to the end of the line 、--to the end of the line, as well as the/* line or multiple lines/* Comment squares:

Mysql> SELECT 1+1; # This annotation until the end of the line mysql> SELECT 1+1; --this comment until the end of the line mysql> Select 1/* This is a row in the middle of the comment/+ 1;mysql> SELECT 1+/* This is a multiline annotation form */1;

Note-(double dash) annotation style requires at least one space after two long strokes!

Although the server understands the annotation syntax just described, the syntax analysis for the MySQL client is limit in the/* ...
Single and double quotes are used to mark the beginning of a quoted string, even in a comment. If the quotation marks in the note are not matched with another quotation mark, then the parser does not think the comment is finished. If you run MySQL interactively, you'll get confused because the prompt changes from mysql> to ' > or ' >. A semicolon is used to indicate the end of the current SQL statement and anything that follows it represents the beginning of the next line.
Whether you are running MySQL interactively or placing commands in a file, and then using MySQL < Some-file to tell MySQL to read its input, this restriction exists.

MySQL supports '--' ANSI SQL annotation style, but must be followed by a space after two long strokes. View Chapter 1.8.4.7 '-' as the beginning of a comment.
6.1.7 is MySQL picky about reserved words?


A common problem is trying to create a data table, such as TIMESTAMP or GROUP, by using MySQL's built-in data type or function name as the field name for the table. However, you are allowed to do so (for example, ABS is a permitted column name), and when you use a function with a function name that is also a column name, there is no space allowed between the function name and the following "(").

The following words are explicitly retained in MySQL. Most of them are prohibited by ANSI SQL92 as columns or table names (for example, GROUP). Some are reserved because MySQL requires them and it uses a YACC parser:
Word word ADD all alteranalyze and Asasc auto_increment bdbbefore berkeleydb betweenbigint BINARY blobboth btree BYCASCADE Case Changechar CHARACTER checkcollate COLUMN columnsconstraint CREATE crosscurrent_date current_time Current_ Timestampdatabase DATABASES day_hourday_minute day_second decdecimal DEFAULT delayeddelete DESC describedistinct Distinctrow divdouble DROP elseenclosed ERRORS escapedexists EXPLAIN falsefields FLOAT forforce FOREIGN fromfulltext FUNCT ION grantgroup HASH havinghigh_priority hour_minute hour_secondif IGNORE inindex INFILE innerinnodb INSERT intinteger INTE Rval Intois JOIN keykeys KILL leadingleft like Limitlines LOAD localtimelocaltimestamp LOCK longlongblob longtext LOW_PRIO ritymaster_server_id MATCH mediumblobmediumint mediumtext middleintminute_second MOD mrg_myisamnatural not NULLNUMERIC On optimizeoption Optionally ororder OUTER outfileprecision PRIMARY privilegesprocedure PURGE readreal REFERENCES regexpr ename REPLACE requirerestrict RETURNS revokeright Rlike rtreeselect SET showsmallint SOME sonamespatial sql_big_result sql_calc_found_rowssql_small_result SSL Startingstraight_join striped Tabletables terminated thentinyblob TINYINT Tinytextto trailing TRUETYPES UNION Uniqueunlock UNSIGNED updateusage use user_resourcesusing VALUES Varbinaryvarchar varcharacter when Wherewith WRITE Xoryear_month Zerofill
The following symbols (from the previous table) are prohibited by ANSI SQL, but can be used by MySQL for column/table names. This is because it is natural to name them and a lot of people have used them.
Actionbitdateenumnotexttimetimestamp