1. string

It can be single or double quotes

'a string'
"another string"
Copy the code

When the ANSI_QUOTES mode is turned on, only single quotes are allowed, and “is considered an identifier. When multiple characters are written together, they are automatically merged

'a string'
'a' ' ' 'string'
Copy the code

You can specify character sets and verification types

[_charset_name]'string' [COLLATE collation_name]
SELECT _latin1'string';
SELECT _binary'string';
SELECT _utf8'string' COLLATE utf8_danish_ci;
Copy the code

A ‘ inside a string quoted with ‘ may be written as ”.

mysql> SELECT 'hello'.'"hello"'.'""hello""'.'hel''lo'.'\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
Copy the code

Identifier Length Limits 64

Identifier format, underscore format, case insensitive

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
Copy the code

The table alias is case-sensitive on Unix but insensitive on Windows and MAC:

mysql> SELECT col_name FROM tbl_name AS a
       WHERE a.col_name = 1 OR A.col_name = 2;
Copy the code

This can be set by setting the system variable lower_case_table_names

A foreign key

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id));CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt'.'polo'.'dress') NOT NULL,
    color ENUM('red'.'blue'.'orange'.'white'.'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id));INSERT INTO person VALUES (NULL.'Antonio Paz');

SELECT @last: =LAST_INSERT_ID(a);INSERT INTO shirt VALUES
(NULL.'polo'.'blue'The @last),
(NULL.'dress'.'white'The @last),
(NULL.'t-shirt'.'blue'The @last);

INSERT INTO person VALUES (NULL.'Lilliana Angelovska');

SELECT @last: =LAST_INSERT_ID(a);INSERT INTO shirt VALUES
(NULL.'dress'.'orange'The @last),
(NULL.'polo'.'red'The @last),
(NULL.'dress'.'blue'The @last),
(NULL.'t-shirt'.'white'The @last);

Copy the code