Are there keywords in mysql?

There are keywords in mysql. Mysql provides a variety of keywords: 1. BETWEEN, used to query the time or number range after the WHERE statement; 2. CALL, used to call stored procedures; 3. CURRENT_DATE, to get the current date; 4. CURRENT_TIME, to get the current date Time; 5. CURRENT_USER, returns the user name of MYSQL; 6. CURSOR, used to declare the cursor in mysql; 7. DISTINCT, used to remove duplicate data.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

When using MySQL, generally try to avoid using keywords as table names. If you use keywords as table names, you need to add [] (or ") to the SQL statement according to standard writing to distinguish the field name and table name. .

All MySQL keywords are listed below, hoping to provide some reference help for friends who use MySQL.

ADD	ALL	ALTER
ANALYZE	AND	AS
ASC	ASENSITIVE	BEFORE
BETWEEN	BIGINT	BINARY
BLOB	BOTH	BY
CALL	CASCADE	CASE
CHANGE	CHAR	CHARACTER
CHECK	COLLATE	COLUMN
CONDITION	CONNECTION	CONSTRAINT
CONTINUE	CONVERT	CREATE
CROSS	CURRENT_DATE	CURRENT_TIME
CURRENT_TIMESTAMP	CURRENT_USER	CURSOR
DATABASE	DATABASES	DAY_HOUR
DAY_MICROSECOND	DAY_MINUTE	DAY_SECOND
DEC	DECIMAL	DECLARE
DEFAULT	DELAYED	DELETE
DESC	DESCRIBE	DETERMINISTIC
DISTINCT	DISTINCTROW	p
DOUBLE	DROP	DUAL
EACH	ELSE	ELSEIF
ENCLOSED	ESCAPED	EXISTS
EXIT	EXPLAIN	FALSE
FETCH	FLOAT	FLOAT4
FLOAT8	FOR	FORCE
FOREIGN	FROM	FULLTEXT
GOTO	GRANT	GROUP
HAVING	HIGH_PRIORITY	HOUR_MICROSECOND
HOUR_MINUTE	HOUR_SECOND	IF
IGNORE	IN	INDEX
INFILE	INNER	INOUT
INSENSITIVE	INSERT	INT
INT1	INT2	INT3
INT4	INT8	INTEGER
INTERVAL	INTO	IS
ITERATE	JOIN	KEY
KEYS	KILL	LABEL
LEADING	LEAVE	LEFT
LIKE	LIMIT	LINEAR
LINES	LOAD	LOCALTIME
LOCALTIMESTAMP	LOCK	LONG
LONGBLOB	LONGTEXT	LOOP
LOW_PRIORITY	MATCH	MEDIUMBLOB
MEDIUMINT	MEDIUMTEXT	MIDDLEINT
MINUTE_MICROSECOND	MINUTE_SECOND	MOD
MODIFIES	NATURAL	NOT
NO_WRITE_TO_BINLOG	NULL	NUMERIC
ON	OPTIMIZE	OPTION
OPTIONALLY	OR	ORDER
OUT	OUTER	OUTFILE
PRECISION	PRIMARY	PROCEDURE
PURGE	RAID0	RANGE
READ	READS	REAL
REFERENCES	REGEXP	RELEASE
RENAME	REPEAT	REPLACE
REQUIRE	RESTRICT	RETURN
REVOKE	RIGHT	RLIKE
SCHEMA	SCHEMAS	SECOND_MICROSECOND
SELECT	SENSITIVE	SEPARATOR
SET	SHOW	SMALLINT
SPATIAL	SPECIFIC	SQL
SQLEXCEPTION	SQLSTATE	SQLWARNING
SQL_BIG_RESULT	SQL_CALC_FOUND_ROWS	SQL_SMALL_RESULT
SSL	STARTING	STRAIGHT_JOIN
TABLE	TERMINATED	THEN
TINYBLOB	TINYINT	TINYTEXT
TO	TRAILING	TRIGGER
TRUE	UNDO	UNION
UNIQUE	UNLOCK	UNSIGNED
UPDATE	USAGE	USE
USING	UTC_DATE	UTC_TIME
UTC_TIMESTAMP	VALUES	VARBINARY
VARCHAR	VARCHARACTER	VARYING
WHEN	WHERE	WHILE
WITH	WRITE	X509
XOR	YEAR_MONTH	ZEROFILL

The following is a list of the functions of common mysql keywords

##ANALYZEThrough the Analyze Table statement The index can be repaired. Use "##BETWEENSELECT * FROM USER WHERE CREATE BETWEEN '2020-11-20' AND '2020-11-30'CALLCALL showUser()CASCADECONSTRAINT 'User information' FOREIGN KEY (USER_NAME) REFERENCES TASK.USER (NAME) ON DELETE CASCADE ON UPDATE CASCADECURRENT_DATESELECT CURRENT_DATECURRENT_TIMESELECT CURRENT_TIME##DEFAULT CURRENT_TIMESTAMPIf you add this code when creating a time field, it means that the current time will be inserted by default when adding new data. When creating a field: ""ON UPDATE CURRENT_TIMESTAMPIf you add this code when creating a time field, it means that the current time will be synchronized by default when modifying data. When creating a field: "”CURRENT_USERReturns the user name of MYSQLCURSOR is used to declare mysql The cursor in is used in stored procedures and serves as a pointer. Usage: ""DECLARE is used to declare variables in compound statements, such as stored procedures. You can use to reassign the value. In the example, result 1 is "test", result 1 ( 2) It is "Test 1" DELAYED is used to use delayed insertion operations. When executing the statement declared by DELAYED, the database will first return status information, and then The insertion operation is put into the queue, and the execution of the queue starts after waiting for instructions that do not operate the queue data. DESCRIBE is used to view the detailed design of the fields in the data table. There are two types Usage: 1.View all fields.2.DESCRIBE table name field name is used to view fields individually #XOR represents logical exclusive OR. When any operand is NULL, the return value is NULL. For non-NULL operands, if the two logical true and false values ​​are different, the return result is 1. , otherwise 0, such as: ZEROFILLWhen creating a table After adding this keyword to the numerical type, if there are not enough digits to insert, the corresponding digits will be automatically filled, such as: ##[Related recommendations:

Keyword	Function
	SHOW INDEX FROM table name" to view the index status in the table. When the number of different values ​​in the field is much greater than the number of CARDINALITY in the table, the index basically has no effect. In this case, use " ANALYZE TABLE table name" can repair the index. Use "SHOW INDEX FROM table name" again to see that the CARDINALITY index returns to normal
Use the time or number range query after WHERE, such as "	;" The query is For data between the 20th and the 30th, if the time type is DATETIME, the query range is 00:00:00. For example, in the above query statement, if CREATE_TIME is of DATETIME type, the query range is " 2020-11-20 00:00:00 to 2020-11-30 00:00:00”
is used to call stored procedures, such as:
is added after the constraint. After setting, the slave table will also be operated when the master table is deleted or updated, for example: "	", after adding, when the user name is modified in the user table, the user name in the slave table will also be modified.
is used to obtain today’s date. For example, “	” returns today’s date
is used to obtain the current time. For example, "	" returns the current hours, minutes and seconds.
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
update_time timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
DECLARE cursor name CURSOR FRO query result set
SET variable name = value
DESCRIBE table name
DISTINCT	removes duplicate data, and only deduplicated data can be returned. It must be the first field in the query. It is often used to query how many unique pieces of data there are in a certain field.For example, SELECT COUNT(DISTINCT name) FROM user
DISTINCTROW	is used to deduplicate joint table queries, and you can query the associations in the main table The appendix data does not include data that is not related to the main table queried, such as SELECT DISTINCTROW user.id, untitled.friend_name FROM user INNER JOIN untitled ON user.id = untitled.friend_id WHERE user.id = '1';You can query the user ID and the friend names in the associated untitled table, excluding the friends of the user who is not queried
DROP	Mainly used to delete structures, such as deleting database: DROP DATABASE database name, deleting table: DROP TABLE table name, deleting fields: ALTER TABLE table name DROP field name
DUAL	Virtual tables are available in mysql5.1 or above. I feel that they are only useful in ORACLE because the FROM table is required to use queries in ORACLE. name, but if there is no table name in mysql, no error will be reported. For example, in mysql, SELECT 1 1 will not cause a problem, but in ORACLE, an error will be reported. In this case, use SELECT 1 1 FROM DUAL, official statement: DUAL is entirely for the convenience of those who require that all SELECT statements should have FROM or other clauses
EXISTS	Used for judgment operation after WHERE. The return result is a Boolean value. The method of use is to substitute the existing rows into the inner query for verification. If one or more rows of data are returned in the inner query, the data of this row will be output, and vice versa. If there is no data in the query, the data of this row will not be output, such as: SELECT * FROM user u WHERE EXISTS(SELECT * FROM untitled n WHERE n.friend_id=u.id)What is returned is the friend field in the untitled table. For data associated with the id in the user table, you can also add NOT in front of the EXISTS keyword to return unrelated data.
EXPLAIN	Use to view one SQL execution, whether indexes are added or full table scans are performed, etc. How to use: add before SELECT, such as EXPLAIN SELECT * FROM user
FULLTEXT	Full-text index is used to optimize query speed. You can add FULLTEXT(Field 1, Field 2) when creating the table, or you can add ALTER separately after creating the table. TABLE table name ADD FULLTEXT INDEX index name (field name)It should be noted that when adding an index, the table engine must use MyISAM, otherwise an error will be reportedThe used table type doesn't support FULLTEXT indexes
GRANT	is used for user permission operations. It can grant user permissions or delete user permissions. There are many operation methods, such as: CREATE USER 'testUser'@'host' IDENTIFIED BY '123456';Used to create users, show grants for 'testUser'@'host';View user permissions and other operations
GROUP	is used for grouping operations, GROUP BY field name The fields to be queried after grouping operations must be added after GROUP BY otherwise an error will be reported. The more classic usage is as follows ：SELECT friend_name , COUNT(friend_name) FROM untitled GROUP BY friend_nameQuery the number of data items of a certain type
HAVING	Used in aggregation Then filter the data. The difference from WHERE is that WHERE filters the data before aggregation and cannot use aggregate functions such as COUNT, SUM, etc. HAVING filters the data after aggregation and you can use aggregate functions, for example: SELECT friend_name , COUNT(friend_name) FROM untitled GROUP BY friend_name HAVING COUNT(friend_name) >1The query is the number of data items with friend_name greater than one
HIGH_PRIORITY	Used for SELECT and INSERT statements to declare this operation as a high-priority operation. The database will perform such operations first, such as: SELECT HIGH_PRIORITY * FROM userWhen there are multiple operations at the same time, this statement takes precedence. implement
IGNORE	is often used to ignore error data when inserting in batches, such as: INSERT IGNORE INTO user (id,name,age) values(6,'pangqi',25);If the primary key id already exists, no error will be reported, but the next insertion operation will be performed.
INNER	is used for inner connection query. After setting the ON condition, it will Display all the data that meets the conditions. The difference from left join and right join is that INNER has no focus. MySQL will find all the data that meets the conditions, so be careful not to have a Cartesian product when using it. In addition, because INNER is the default connection method of mysql, so you can omit the INNER keyword such as: SELECT * FROM user JOIN untitled ON user.id = untitled.friend_id
INTERVAL	Mainly used for time intervals, such as: SELECT * FROM USER where create_time < NOW()-INTERVAL 4 DAY returns the data created four days ago, or DAY can be Replace with hours, minutes, years, months, etc., and can also be used to compare data such as: SELECT INTERVAL(6,1,2,3,4,7,8,9,10) will return 4, because When comparing, mysql will use the data with index 0 to compare the next data. When the next data is greater than the data with index 0, mysql will return the index of the previous data
IS	IS keyword in mysql is only used in IS NULL or IS NOT NULL such as: SELECT * FROM USER where update_time IS NULL
JOIN	is used to connect two tables. The connection methods are generally inner joinINNER JOIN and right joinRIGHT JOIN, left connectionLEFT JOIN
KEY	KEY is the physical structure of the database, which has two levels of meaning and function: 1: Constraints, 2: Index, such as the declaration of the primary key PRIMARY KEY (id),, which will have unique constraints and automatically add the index
KILL	is used to terminate the thread in mysql. You can use SHOW PROCESSLIST to view the current thread, and use KILL thread id to terminate the thread. There are two modifiers 1: CONNECTION default The modifier can terminate all connections related to the thread id. 2: QUERY terminates the currently executed statement, but will not terminate the connection
LABEL	Statement label
LEAVE	The Leave statement indicates exiting the flow control statement block of the specified label. It is usually used in begin...end, as well as loop, repeat, and while loop statements. Just like break in programming, the usage method is LEAVE loop name;
LEFT	The LEFT() function is a string function that returns The left part of the string with the specified length.For example: SELECT LEFT(name,3) FROM user
LIKE	is used for fuzzy search and can be used with % and _, % Represents querying one or more wildcard characters, and _ represents a character, such as: SELECT * FROM user WHERE name LIKE '%i'You can query all data ending with i in the name, replace % with _ After that, the query condition becomes querying the data whose name ends with i and has only one character in front
LIMIT	is used for paging query, such as: SELECT * FROM user LIMIT 4,5 The first parameter is the starting number. As in the above example, it returns from the 4th piece of data. The second parameter is the offset. As shown in the above example, if the parameter is 5, it means returning. From the last five pieces of data in Article 4, the previous offset can be -1 to represent all the data after the starting number of queries. However, this writing method is officially recognized by MySQL as an error, so newer versions of MySQL cannot be used. , the best way is to give a relatively large number
LOCALTIME	local time, which can be used as query result field, where condition, current time when inserting, update time, etc., mysql has a variety of time functions, you can choose different functions according to your needs, for example: SELECT LOCALTIME,NOW(),sysdate(), localtimestamp, localtimestamp();
LOCK	Locking tables and unlocking MySQL allows client sessions to explicitly acquire table locks to cooperate with other sessions to access the table, or to prevent other sessions from requiring mutually exclusive access when the session when modifying the table. A session can only acquire or release locks for itself. A session cannot acquire a lock from another session, nor can it release a lock held by another session. Example: LOCK table user read localAt this time, a table lock operation will be generated. All operations to modify the table will be rejected and prompt: Table 'user' was locked with a READ lock and can 't be updated, you can use the unlock tables;keyword to unlock
LOW_PRIORITY	low priority, MySQL allows you to change the statement Prioritization of scheduling, which allows queries from multiple clients to cooperate better so that a single client does not wait for a long time due to locks. Generally used in DELETE, INSERT, LOAD DATA, REPLACE and UPDATE statements, for example: update LOW_PRIORITY user set username = 'zhangsan'In this case, if the writer is waiting, the second When a reader arrives, the second reader is allowed to insert before the writer. Writers are allowed to start operations only when there are no other readers.
MOD	Remainder function, such as: select mod(12,5)Return remainder 2
NATURAL	Natural join is a type of JOIN. It is characterized by automatically matching fields with the same name in the table. The types of these fields with the same name can be different, so the types of fields in the table can be different. , can be applied in INNER, LEFT and other JOINs, for example: select * from user NATURAL LEFT JOIN user2 The difference between user and user2 here is that in addition to the id, 1 will be added after the user2 field, which will result in The id is automatically used for association when connecting, and the result is indeed the same
OPTIMIZE	Function: Optimize and organize table fragments and indexes, and the database will allocate data when storing data in the database Table space, and index to the data. When the data is deleted, the database will not reclaim these resources but wait for new data to fill these vacancies. Use the OPTIMIZE keyword to actively clear these occupied resources. Usage: optimize table table Name
RANGE	Partition: Based on a given interval range, the value of this field in the given interval is required to be continuous when performing the operation Knowing how to partition accordingly can greatly improve efficiency. The requirement is that if the table has a primary key, the partition field should also be in the row of the primary key. Usage: Create table statement PARTITION BY RANGE (xuehao) (PARTITION p0 VALUES LESS THAN (6 ),PARTITION p1 VALUES LESS THAN (11),PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21));
REFERENCES	Create Foreign key relationship, in the table creation statement: FOREIGN KEY(uid) REFERENCES users(id) means that the field uid will be connected to the primary key field id of the user table
REGEXP	Regular expression, you can use regular expressions in conditions to operate, use SELECT * FROM user WHERE email NOT REGEXP'^[A-Z0-9._%-] @[A-Z0-9.-] .[A-Z]{2,4}$'
RENAME	To modify the table name, use: rename table user2 to user3;
REPEAT	Copy the given data as a string a specified number of times, such as: select REPEAT(3,3)The result is "333"
REPLACE	Replace all specified strings, such as: select REPLACE('This is a string, or a character', 'one', 'two') ;
REVOKE	Revoke the permissions that have been granted to the MySQL user, such as: revoke all on *.* from testUser@host; Remember to refresh the user permission table after completionFLUSH PRIVILEGES;
RIGHT	Perform truncation operation on the string, such as:select RIGHT ('This is a string',5), will intercept the specified number of digits in the string in reverse order
RLIKE	Fuzzy query, different from like When , the content of rlike can be regular and may not match exactly, such as: select * from user where email RLIKE '^www.*$';
SEPARATOR	can concatenate the query results into one row with a string. SEPARATOR specifies the connector and needs to be used with GROUP BY, such as: SELECT *, GROUP_CONCAT(username order by username separator ';') SCORE FROM user GROUP BY email RLIKE '^www.*$'
USING	If the field names are the same during connection query, they can be used as connection conditions, using It can appear instead of on, such as: select * from user left join students USING(quanxian)
WHILE	Loop statement keywords, used in storage During the process, it needs to be used in combination with process control statements, such as: CREATE PROCEDURE fun() BEGIN SET @sum:=10; WHILE @sum > 0 DO SELECT @sum; SET @sum:=@sum-1; END WHILE; END CALL fun(); DROP PROCEDURE fun
	select true XOR null;select true XOR true;select true XOR false;
	CREATE TABLE student3 (id int(11) unsigned zerofill not null, xuehao int(50) NOT NULL );insert into student3 value(1,2); select * from student3;

mysql video tutorial

】

The above is the detailed content of Are there keywords in mysql?. For more information, please follow other related articles on the PHP Chinese website!