MySQL experiences 6 -- MySQL Language Structure -- constants and variables

1. In the MySQL database, the SQL language consists of the following parts. (1) Data Definition Language (DDL ). It is used to execute database tasks and perform create, drop, alter, and other operations on the database and various objects in the database. As mentioned above, database objects mainly include tables, default constraints, rules, views, triggers, and stored procedures. The syntax of statements such as create and drop for different database objects is different (2) data manipulation language (DML ). It is used to manipulate various objects in the database and retrieve and modify data. The following table lists the main statements and functions of DML. The syntax indicates that the SELECT statement to retrieve data from a table or view is one of the most frequently used SQL statements. INSERT the data into a table or view at www.2cto.com UPDATE to modify the data in the table or view. modifies a row of data in a table or view, you can also modify a group or all of the data deleted from a table or view. You can DELETE the specified data according to the condition. (3) Data Control Language (DCL ). It is used for security management to determine which users can view or modify data in the database. The main statements and functions of DCL are shown in the following table. It can be said that grant permission can GRANT statement or object permission to other users and roles revoke to revoke permissions, which is different from grant's function, however, this user or role is not affected to inherit permission permissions from other roles as members (4) language elements added by MySQL. This part is not included in the SQL standard, but a language element that is added for the convenience of User Programming. These language elements include constants, variables, operators, functions, process control statements, and annotations. Each SQL statement ends with a semicolon, and the SQL processor ignores spaces, tabs, and carriage returns. 2. Constant 1). A String constant string is a character sequence enclosed by single or double quotation marks. It can be divided into ASCII string constants and Unicode string constants. An ASCII string constant is a string of ASCII characters enclosed in single quotes. Example: 'hello' How are you! 'Www.2cto.com Unicode String constant is similar to an ASCII string constant, but it is preceded by an N sign (N stands for the international Language in the SQL-92 standard )). The N prefix must be in uppercase. Strings can only be enclosed in single quotes. For example, each character in the N 'hello' Unicode data is stored in two bytes, and each ASCII character is stored in one byte. Character strings can contain not only common characters, but also escape sequences. They are used to represent special characters. See the following table. Each escape sequence starts with a backslash ("\"), indicating that the subsequent characters are interpreted using escape characters rather than common characters. Note that NUL bytes are different from NULL values. NUL is a zero-value byte, and NULL indicates no value. An ASCII 0 (NUL) character \ n a linefeed \ r a carriage return character (\ r \ n is used as a new line sign in Windows) \ t a location character \ B a space character \ Z an ASCII 26 character (CTRL + Z) www.2cto.com \ 'a single quotation mark (' ") \" a double quotation mark (") \ A backslash ("\") \ % A "%" character. It is used to search for the text instance of "%" in the body. Otherwise, "%" is interpreted as a wildcard \ _ and a "_" character. It is used to search for the text instance of "_" in the body. Otherwise, "_" is interpreted as a wildcard in the following ways: ● The single quotation mark (') referenced by single quotation marks (') in a string can be written as "'" (two single quotation marks ). ● The double quotation marks "" referenced by double quotation marks "" in a string can be written as "(two double quotation marks ). ● Escape characters ("\") can be added before quotation marks. ● The single quotation mark (') referenced by double quotation marks ("") in a string does not require special processing or double characters or escape. Similarly, the double quotation mark "" referenced by the single quotation mark "'" in the string does not require special processing. 2). Numeric constants can be divided into Integer constants and floating point constants. An integer constant is a decimal number without a decimal point, for example, 2, + 1453,-2147483648. A floating point constant is a numerical constant that uses the decimal point, for example,-1.39, 1.5E5, 0.5E-2. 3). The hexadecimal constant MySQL supports hexadecimal values. A hexadecimal value is usually specified as a String constant. Each pair of hexadecimal numbers is converted to a character, there is an uppercase letter "X" or a small letter "x" at the top ". You can only use numbers "0" to "9" and letters "a" to "f" or "A" to "F" in quotation marks ". '4d7953514c' indicates the MySQL string. Hexadecimal values are case-insensitive. the prefix "X" or "x" can be replaced by "0x" without quotation marks. That is, you can replace '41' with 0X41. Note: x must be lowercase in '0x. The default type of the hexadecimal value is string. If you want to make sure that the value is processed AS a number, you can use cast (... as unsigned ). For example, run the following statement: SELECT0x41, CAST (0x41 as unsigned). to convert a string or number to a string in hexadecimal format, you can use the hex () function. For example, convert the CAT string to hexadecimal notation. Select hex ('cat'); www.2cto.com 4). Date and Time Constant: Enclose the string representing the date and time in single quotes. A date type constant includes year, month, and day. The data type is DATE, which indicates a value such. TIME Constants include the number of hours, minutes, seconds, and microseconds. The data type is TIME, which indicates a value such as "12:30:43. 00013. MySQL also supports combination of date and time. The data type is DATETIME or TIMESTAMP, for example, "12:30:43 ". The difference between DATETIME and TIMESTAMP is that the year of DATETIME ranges from 1000 ~ Between 9999, while the TIMESTAMP year is between 1970 and ~ And TIMESTAMP is ignored when inserting a date time with microseconds. TIMESTAMP also supports the time zone, that is, the time zone is converted to the corresponding time. Note that MySQL represents the date in the order of year, month, and day. You can also use special characters such as "\", "@", or "%. The following is an example of a date and time constant: '2017-05-12 14: 28: 24: 00' the value of a date and time constant must comply with the date and time standards, A date like this is incorrect: '2017-02-31 '. 5) The bit field value can be written using the B 'value' symbol. Value is a binary value written as 0 and 1. Directly displaying the value of B 'value' may be a series of special symbols. For example, B '0' is displayed as blank, while B '1' is displayed as a smiling face icon. You can use the bin function to display bitfield constants in binary format. You can use the oct function to display constant bitfield values in numeric format. Example: select bin (B '000000' + 0), OCT (B '000000' + 0); 6 ). A boolean value contains only two possible values: TRUE and FALSE. The value of FALSE is "0", and the value of TRUE is "1 ". For example, obtain values of TRUE and FALSE. Select true, FALSE; www.2cto.com 7 ). the NULL value is applicable to various column types. It is usually used to indicate "no value", "No data", and so on, and it is different from the numeric type "0" or a string type empty string. 3. Variable 1). User variables users can use their own Defined variables in expressions. Such variables are called user variables. You can save the value in the user variable and reference it later. This allows you to pass the value from one statement to another. Before using user variables, you must define and initialize them. If no initialization variable is used, its value is NULL. User variables are related to connections. That is to say, variables defined by one client cannot be viewed or used by other clients. When the client exits, all variables connected to the client are automatically released. You can use the SET statement to define and initialize a variable. The syntax format is SET @ user_variable1 = expression1 [, user_variable2 = expression2,…]. User_variable1 and user_variable2 are user variable names. Variable names can be composed of character numbers, ".", "_", and "$" in the current character set. When the variable name needs to contain some special characters (such as spaces and #), you can use double quotation marks or single quotation marks to enclose the entire variable. Expression1 and expression2 are the values to be assigned to the variable. They can be constants, variables, or expressions. For example, create a user variable name and assign it to "Wang Lin ". SET @ name = 'wang line'; Note: The @ symbol must be placed before a user variable to distinguish it from the column name. "Wang Lin" is the value specified for the variable name. The data type of name is automatically assigned according to the following value assignment expression. That is to say, the data type of name is the same as that of 'wang line', and the character set and verification rules are the same. If you re-assign different types of values to the name variable, the data type of name will also change. Www.2cto.com (1_1). You can also define multiple variables separated by commas. For example, create user1 and assign it to 1, user2 to 2, and user3 to 3. SET @ user1 = 1, @ user2 = 2, @ user3 = 3; (1 limit 2). A time-varying value of a user variable can be an expression. For example, create user4, and Add 1 to user3. SET @ user4 = @ user3 + 1; (1_3). After a user variable is created, it can be used in other SQL statements in a special form. The variable name must also be preceded by the symbol @. Example 1: query the value of the variable name created in the previous example. SELECT @ name; Example 2: assign values to variables using queries. Use xscj; SET @ student = (SELECT name fromxs where student ID = '000000'); Example 3: query the student information whose name is equal to the student value in example 2 in Table XS. SELECT student ID, name, Major name, date of birth from xs where name = @ student; note: In the SELECT statement, the expression is sent to the client for calculation. This indicates that the HAVING, group by, or ORDERBY clause cannot use an expression that contains the variables set in the SELECT list. For the SET statement, you can use "=" or ": =" as the distributor. The value assigned to each variable can be an integer, a real number, a string, or a NULL value. You can also use other SQL statements instead of the SET statement to assign a value to user variables. In this case, the distributor must be ": =", instead of "=", because "=" is considered a comparison operator in a non-SET statement. For example, run the following statement. The result t2 is 7. SELECT @ t2: = (@ t2: = 2) + 5 AS t2; 2 ). the system variable MySQL has some specific settings. When the MySQL database server is started, these settings are read to determine the next step. For example, some settings define how data is stored, some settings affect processing speed, and some are related to dates. these settings are system variables. Like user variables, system variables are also a value and a data type, but the difference is that system variables are introduced and initialized as the default value when the MySQL server is started. Appendix G lists the vast majority of system variables. For example, obtain the current MySQL version. SELECT @ VERSION; when most of the system variables are used in other SQL statements, you must add two @ symbols before the name, to be consistent with other SQL products, some specific system variables must omit the two @ symbols. For example, CURRENT_DATE (system date), CURRENT_TIME (system time), CURRENT_TIMESTAMP (system date and time), and CURRENT_USER (SQL user name ). For example, obtain the current system time. SELECT CURRENT_TIME; 3) in MySQL, the values of some system variables cannot be changed, such as VERSION and system date. Some system variables can be modified using the SET statement, such as SQL _WARNINGS. Syntax format: SET system _ var_name = expr www.2cto.com | [global | session] system_var_name = expr |@@ [global. | session.] system_var_name = expr Description: system_var_name is the system variable name, And expr is the new value set for the system variable. You can add keywords such as GLOBAL or SESSION before the name. The GLOBAL or @ global. keyword is the GLOBAL system variable ). If the SESSION or @ session. keyword is specified, the SESSION system variable (local systemvariable) is used ). SESSION and @ session. There is also a synonym for LOCAL and @ local .. If you do not specify a keyword when using system variables, the session system variable is used by default. (3_1). Global System variables when MySQL is started, the global system variables are initialized and applied to each started session. If you use GLOBAL (SUPER permission required) to set system variables, this value is remembered and used for new connections until the server is restarted. For example, change the value of the global system variable sort_buffer_size to 25000. SET @ global. sort_buffer_size = 25000; Note: if you use a variable that can only be used with SETSESSION when using set global, or if you do not specify GLOBAL (or @) when setting a GLOBAL variable, MySQL will generate an error. Www.2cto.com (3_2). session system variable only applies to the current session. Most session system variables have the same name as global system variables. When a session is started, each session system variable has the same value as the global system variable with the same name. The value of a session system variable can be changed, but the new value is only applicable to running sessions and not to all other sessions. For example, set the SQL _WARNINGS variable of the current session to TRUE. SET @ SQL _WARNINGS = ON; Note: This system variable indicates whether MySQL should return a warning if incorrect data is added to a table through an INSERT statement. By default, this variable is disabled. If it is set to ON, a warning is returned. For example, for the current session, set the value of the system variable SQL _SELECT_LIMIT to 10. This variable determines the maximum number of rows in the result set of the SELECT statement. SET @ SESSION. SQL _SELECT_LIMIT = 10; SELECT @ LOCAL. SQL _SELECT_LIMIT; note: In this example, the keyword SESSION is placed before the name of the system variable (SESSION and LOCAL can be used universally ). This explicitly indicates that the session system variable SQL _SELECT_LIMIT is consistent with the value specified by the SET statement. However, the value of the global system variable named SQL _SELECT_LIMIT remains unchanged. Similarly, the value of the global system variable is changed, and the value of the session system variable with the same name remains unchanged. MySQL has default values for most system variables. These values are used when the database server is started. You can also modify these values in the my. ini option file under the MYSQL folder on disk C. When the database server is started, the file is automatically read. (1) If you want to set a system variable value to the default MySQL value, you can use the default keyword. For example, restore the SQL _SELECT_LIMIT value to the default value. SET @ LOCAL. SQL _SELECT_LIMIT = DEFAULT; (2). Use the SHOWVARIABLES statement to obtain the list of system variables. Show global variables returns all GLOBAL system VARIABLES, while show session variables returns all SESSION system VARIABLES. If no keyword is added, showsession variables is used by default. Example of www.2cto.com: Get a list of system variables. Show variables; (3) to obtain a list of variable names or names that match the style, use the LIKE clause. The statement is as follows: show variables like 'max _ join_size '; show global variables like 'max _ join_size '; (4) to get a list of VARIABLES matching the name and style, use the wildcard "%", for example: show variableslike 'character % ';
Author tianyazaiheruan