MySQL foundation - properties of columns

Posted by wintallo on Thu, 11 Nov 2021 05:44:14 +0100

Simple queries and inserted statements

Simple query statement

If we want to see what data has been stored in a table, we can use the following statement:

SELECT * FROM Table name;

For example, we want to see the first created earlier_ The data in the table can be written as follows:

mysql> SELECT * FROM first_table;
Empty set (0.01 sec)

mysql>

Unfortunately, we have never inserted data into the table, so the query result shows Empty set, which means nothing is found ~

Simple insert statement

MySQL inserts data in behavioral units. The syntax format is as follows:

INSERT INTO Table name(Column 1, Column 2, ...) VALUES(Value of column 1, value of column 2, ...);

In other words, we can specify the column to insert data in the brackets after the table name, and then fill in the corresponding VALUES in the brackets after VALUES according to the specified column order. Let's call it first_ Insert the first row of data into the table:

mysql> INSERT INTO first_table(first_column, second_column) VALUES(1, 'aaa');
Query OK, 1 row affected (0.00 sec)

mysql>

This statement means that we want to report to first_ Insert a row of data into the table, first_ The value of column is 1, second_ The value of the column is' aaa '. Take a look at the data in the table:

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
+--------------+---------------+
1 row in set (0.00 sec)

mysql>

The first row of data is inserted successfully!

We can also specify only some columns. The value of the column that is not explicitly specified will be set to NULL. NULL means that the value of this column is uncertain. For example, write:

mysql> INSERT INTO first_table(first_column) VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO first_table(second_column) VALUES('ccc');
Query OK, 1 row affected (0.00 sec)

mysql>

These two statements mean:

  • In the first insert statement, we only specified first_ The value of column is 2 without specifying second_column, so second_ The value of column is NULL.

  • In the second insert statement, we only specified second_ The value of column is' ccc 'without specifying first_column, so first_ The value of column is NULL.

After executing these two statements, look at the data in the current table:

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|         NULL | ccc           |
+--------------+---------------+
3 rows in set (0.00 sec)

mysql>

Batch insert

It's not impossible to write a statement every time you insert a row of data, but it's too annoying for people. Moreover, every time you insert a row of data, you submit a request to the server, which is far from submitting all the inserted data to the server at once. Therefore, MySQL provides us with statements for inserting records in batches:

INSERT INTO Table name(Column 1,Column 2, ...) VAULES(Value of column 1, value of column 2, ...), (Value of column 1, value of column 2, ...), (Value of column 1, value of column 2, ...), ...;

That is, write more records after the original single insertion statement and separate them with commas. For example:

mysql> INSERT INTO first_table(first_column, second_column) VALUES(4, 'ddd'), (5, 'eee'), (6, 'fff');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|         NULL | ccc           |
|            4 | ddd           |
|            5 | eee           |
|            6 | fff           |
+--------------+---------------+
6 rows in set (0.01 sec)

mysql>

You can see that three rows of records are inserted successfully!

Column properties

When we talked about the table structure in the previous chapter, we said that each column in the table can have some attributes. What these attributes are and how to define them when creating the table are the next contents of this chapter. But we'll use first later_ Table is used as an example, so delete the table first:

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.01 sec)

Default value

We just said that when writing the INSERT statement to INSERT records, you can only specify some columns. The values of those columns that are not explicitly specified will be set to NULL. In other words, the DEFAULT value of the column is NULL. NULL means that the value of this column has not been set. If we don't want the DEFAULT value to be NULL, but set it to a meaningful value, we can add a DEFAULT attribute to the column when defining the column, like this:

Column name the type of the column DEFAULT Default value

For example, we put first_ Second of table_ The default value of column is specified as' abc '. Create the following table:

mysql> CREATE TABLE first_table (
    ->     first_column INT,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>

Then insert a piece of data and see if the default value works:

mysql> INSERT INTO first_table(first_column) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | abc           |
+--------------+---------------+
1 row in set (0.00 sec)

mysql>

Our insert statement does not specify second_column, but you can see that the insertion result is set according to the default value 'abc' specified by us.

If we do not set the default value, it is equivalent to the specified default value is NULL, such as first_table does not have first set_ The default value of column is NULL, that is, the table definition statement above is equivalent to the table definition statement below:

CREATE TABLE first_table (
    first_column INT DEFAULT NULL,
    second_column VARCHAR(100) DEFAULT 'abc'
);

This can also be seen from the SHOW CREATE TABLE statement:

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column` varchar(100) DEFAULT 'abc'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

NOT NULL property

Sometimes we need to require that some columns in the table must have values and cannot store NULL. Then we can define this column with this syntax:

Column name the type of the column NOT NULL

For example, we put first_ First of table_ Column column defines a NOT NULL property. Of course, we need to delete the original table before redefining the table:

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE first_table (
    ->     first_column INT NOT NULL,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>

In this case, we can't insert NULL value into this field, for example:

mysql> INSERT INTO first_table(first_column, second_column) VALUES(NULL, 'aaa');
ERROR 1048 (23000): Column 'first_column' cannot be null
mysql>

When you see an error, you will be prompted first_column column cannot store NULL.

In addition, once the NOT NULL attribute is defined for a column, the default value of the column is NOT NULL. Top first_column does not specify the default value, which means that when inserting rows with INSERT, we must explicitly specify the value of this column and cannot omit it. For example, an error will be reported:

mysql> INSERT INTO first_table(second_column) VALUES('aaa');
ERROR 1364 (HY000): Field 'first_column' doesn't have a default value
mysql>

You can see the execution result and prompt us first_column has no default value, so you can't omit the value of the column when inserting records with the INSERT statement.

Primary key

Sometimes in our table, we can determine a unique record through a column or some columns, and we can call this column or these columns candidate keys. For example, in the student information table student_info, as long as we know a student's student number, we can determine a unique student information, that is, a record. Of course, we can also confirm the only student information record by the ID number, so the student ID and ID number can be used as the candidate key for student information table. On the student transcript_ In score, we can determine the only grade record by the combination of student number and subject, so the combination of student number and subject can be used as a candidate key for student grade sheet.

A table may have multiple candidate keys. We can select one candidate key as the primary key of the table. A table can only have one primary key at most. The primary key value cannot be duplicate. A unique record can be found through the primary key. If there is a requirement to define a primary key in our table, you can specify the primary key in one of the following two ways:

  1. If the PRIMARY KEY is only a single column, you can declare the PRIMARY KEY directly after the column. For example, we put the student information table student_ The student number column of info is declared as the PRIMARY KEY, which can be written as follows:

    CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('male', 'female'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
);

  2. We can also extract the declaration of the primary key separately and declare it in this form:

    PRIMARY KEY (Column name 1, Column name 2, ...)

    Then put the primary key declaration behind the column definition. For example, student_ The student number column of info can also be declared as a primary key:

    CREATE TABLE student_info (
    number INT,
    name VARCHAR(5),
    sex ENUM('male', 'female'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    PRIMARY KEY (number)
);

    It is worth noting that when a combination of multiple columns is used as the primary key, this form of separate declaration must be used, such as student_ The student number and account column combination in the score table are used as the primary key, which can be written as follows:

    CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject)
);

If the primary key is declared when we create the table, MySQL will verify the records we insert. If the primary key value of the newly inserted record already exists in the table, an error will be reported.

In addition, the primary key column has NOT NULL attribute by default, that is, it is required. If NULL value is filled in, an error will be reported (delete the original student_info table, recreate the table using one of the two methods just mentioned, and then execute the following statement):

mysql> INSERT INTO student_info(number) VALUES(NULL);
ERROR 1048 (23000): Column 'number' cannot be null
mysql>

So when you insert data, at least don't forget to assign a value to the primary key column

UNIQUE attribute

For other candidate keys that are not primary keys, if MySQL also wants to help us check whether the values of a column or column combination are duplicate when we insert new records into the table, we can add a UNIQUE attribute to this column or column combination to indicate that the values of this column or column combination are not allowed to be duplicate. Similar to the way we declare the primary key in the table building statement, there are two ways to declare the UNIQUE attribute for a column:

  1. If we want to declare the UNIQUE attribute for a single column, we can fill in UNIQUE or UNIQUE KEY directly after the column, such as student in the student information table_ In info, we do not allow the ID number of two student basic information records to be the same, so we can id_. Add the UNIQUE attribute to the number column:

    CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('male', 'female'),
    id_number CHAR(18) UNIQUE,
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
);

  2. We can also extract the declaration of UNIQUE attribute separately and declare it in this form:

    UNIQUE [Constraint name] (Column name 1, Column name 2, ...)

    Or:

    UNIQUE KEY [Constraint name] (Column name 1, Column name 2, ...)

    In fact, whenever we add a UNIQUE attribute to a column, it is like putting a hoop curse on the monkey king's head. From then on, the values of the column of the records we insert cannot be repeated. Therefore, adding a UNIQUE attribute to a column can also be regarded as adding a constraint to the table, which is called a UNIQUE constraint. Each constraint can have a name. For example, a PRIMARY key is also a constraint. Its name is the default PRIMARY. However, multiple UNIQUE attributes can be added to different columns in a table, that is, multiple UNIQUE constraints can be added. For each UNIQUE constraint, we can give it a name, which is also the meaning of the constraint name above. However, the constraint name is expanded by brackets [], which means that we can write it or not. If not, MySQL will name it for us. In fact, it's like giving birth to a child. If you don't name yourself, the police uncle of the public security bureau must also give the child a name and a hukou.

    After understanding the naming of constraints, we can just put the declaration of the unique attribute behind the column definition. For example, we are students_ ID of info table_ Number (ID number) column can be written with the addition of UNIQUE attribute:

    CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('male', 'female'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    UNIQUE KEY uk_id_number (id_number)
);

    As you can see, the name we give to this UNIQUE constraint is uk_id_number.

    However, it is worth noting that this form of separate declaration must be used when a combination of multiple columns has the UNIQUE attribute.

If the UNIQUE attribute is defined for a column or column combination in the table, MySQL will verify the record we inserted. If the value of the newly inserted record in the column or column combination already exists in the table, an error will be reported!

The difference between primary key and UNIQUE constraint

Both primary key and UNIQUE constraints can ensure the uniqueness of a column or column combination, but:

  1. Only one primary key can be defined in a table, but multiple UNIQUE constraints can be defined!

  2. Regulations: primary key columns are not allowed to store NULL, while columns with UNIQUE attribute declared can store NULL, and NULL can appear repeatedly in multiple records!

Tip: if a column of a table declares the UNIQUE attribute, the value of this column cannot be repeated. Why is NULL so special? Ha ha, NULL is so special. In fact, NULL is not a value. It represents uncertainty. We usually say that the value of a column is NULL, which means that the value of this column has not been filled in.

Foreign key

Insert student into student transcript_ The value in the number column in the score must be in the student basic information table_ Info. Otherwise, if a student number only appears in the grade table and the corresponding record cannot be found in the basic information table, it is equivalent to inserting the grade of a student who does not know which student, which is obviously absurd. In order to prevent such an absurd situation, MySQL provides us with a foreign key constraint mechanism. The syntax for defining foreign keys is as follows:

CONSTRAINT [Foreign key name] FOREIGN KEY(Column 1, Column 2, ...) REFERENCES Parent table name(Parent column 1, Parent column 2, ...);

The foreign key name is optional. It's just a name. If we don't name it ourselves, MySQL will help us name it.

If A column or some columns in table A depend on A column or some columns in table B, table A is called A child table and table B is the parent table. Child tables and parent tables can be associated using foreign keys. In the above example, student_ The number column of the score table depends on student_ The number column of info, so student_info is A parent table, student_score is the sub table. We can be in student_ Define A foreign key in the table creation statement of score:

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject),
    CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
);

In this way, the student_ When inserting data into the score table, MySQL will check whether the inserted student number can be in the student_ It is found in the info table. If it is not found, an error will be reported.

Tips:

The column or column combination in the parent table that is dependent on the child table must be indexed if the column or column combination is already a primary key or has a primary key UNIQUE Property, they are indexed by default. In the example student_score Table dependent stuent_info Tabular number Column, and number Column is again stuent_info Primary key of (note the definition in the previous chapter) student_info There is no in the structure number A column is defined as a primary key in this chapter. If it is not defined as a primary key on your machine, modify the table structure quickly～)，So in student_score There is no problem creating foreign keys in tables.

Of course, as for what is an index, we don't start from zero MySQL What students need to care about, wait until you finish learning this book< MySQL How it works: fundamentally understand MySQL>I see.

AUTO_INCREMENT attribute

AUTO_INCREMENT translates into Chinese, which can be understood as automatic growth, or auto increment for short. If the data type of a column in a table is integer type or floating-point type, the AUTO_INCREMENT property can be set for this column. After setting the AUTO_INCREMENT property for a column, if we do not specify the value of the column when inserting a new record, or If the value is explicitly specified as NULL or 0, the value of the newly inserted record on the column is the value after the current maximum value of the column plus 1 (a little bit around, you will understand in one action example later). We can define this column with this syntax:

Column name the type of the column AUTO_INCREMENT

For example, we want to be at first_ Set a column named id in the table. Set this column as the primary key to uniquely mark a record, and then let it have auto_ The increment attribute can be written as follows:

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE first_table (
    ->     id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->     first_column INT,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>

First delete the original table, and then add a non negative INT type id column in the new table. Set it as the primary key and have auto_ For the increment attribute, we can ignore this column when inserting a new record, or explicitly specify the column value as NULL or 0, but its value will increase. See:

mysql> INSERT INTO first_table(first_column, second_column) VALUES(1, 'aaa');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO first_table(id, first_column, second_column) VALUES(NULL, 1, 'aaa');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO first_table(id, first_column, second_column) VALUES(0, 1, 'aaa');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM first_table;
+----+--------------+---------------+
| id | first_column | second_column |
+----+--------------+---------------+
|  1 |            1 | aaa           |
|  2 |            1 | aaa           |
|  3 |            1 | aaa           |
+----+--------------+---------------+
3 rows in set (0.01 sec)

mysql>

As you can see, the column id is incremented from 1. Defining auto for columns in_ Please pay attention to the following points when using the increment attribute:

  1. At most one table has auto_ The column of the increment property.

  2. With auto_ The column of the increment property must be indexed. Primary keys and columns with UNIQUE attributes are automatically indexed. However, as for what is an index, it will be introduced when learning advanced MySQL.

  3. Have auto_ The column of the increment attribute can no longer specify the DEFAULT value by specifying the DEFAULT attribute.

  4. Generally have auto_ The columns of the increment attribute are attributes that are used as primary keys to automatically generate primary key values that uniquely identify a record.

Comments for columns

In the previous chapter, we said that a COMMENT statement can be added at the end of the statement under construction to add comments to the table. In fact, we can also add a COMMENT statement at the end of each column to add comments to the column, for example:

CREATE TABLE first_table (
    id int UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Auto increment primary key',
    first_column INT COMMENT 'First column',
    second_column VARCHAR(100) DEFAULT 'abc' COMMENT 'Second column'
) COMMENT 'First table';

The ZEROFILL property that affects the appearance of the presentation

Below are three ways to write positive integer 3:

  • Writing method 1: 3

  • Writing 2: 003

  • Writing method 3: 00000 3

Some students laughed. Didn't they take off their pants and fart? I added 10000 zeros in front of 3. The final value is also 0. What's the use? The students who ask such questions certainly have no artistic cells. They look different - some numbers have no 0 in front, some have less 0 in front, and some have more than 0 in front. Maybe some people think it's nice to fill a pile of 0 in front of the numbers?

For columns of unsigned integer type, we can fill 0 in the left of the number when querying data. If we want to achieve this effect, we need to add a ZEROFILL attribute to the column (which can also be understood as an attribute of data type), like this:

mysql> CREATE TABLE zerofill_table (
    ->     i1 INT UNSIGNED ZEROFILL,
    ->     i2 INT UNSIGNED
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>

We're at ZEROFILL_ Two unsigned integer columns are created in the table. The difference is that column i1 has the ZEROFILL attribute. Next, we insert a record for this table:

mysql> INSERT INTO zerofill_table(i1, i2) VALUES(1, 1);
Query OK, 1 row affected (0.00 sec)

mysql>

Then we use the query statement to show the data just inserted:

mysql> SELECT * FROM zerofill_table;
+------------+------+
| i1         | i2   |
+------------+------+
| 0000000001 |    1 |
+------------+------+
1 row in set (0.00 sec)

mysql>

For the i1 column with ZEROFILL attribute, a pile of zeros are added in front of the number when displaying. After careful counting, it is found that there are 9 zeros, while the i2 column without ZEROFILL attribute is not added in front of the number when displaying. Why does the i1 column fill in 9 zeros? Let's take a look at zerofill_table structure of table:

mysql> SHOW CREATE TABLE zerofill_table\G
*************************** 1. row ***************************
       Table: zerofill_table
Create Table: CREATE TABLE `zerofill_table` (
  `i1` int(10) unsigned zerofill DEFAULT NULL,
  `i2` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql>

You can see that in fact, a (10) is added after the type INT of columns i1 and i2, which is the so-called display width. The display width is in the result displayed in the query statement. If it is declared   ZEROFILL   When the number of digits of the actual value of the integer column of the property is less than the display width, 0 will be added to the left of the actual value, so that the sum of the number of digits of 0 and the number of digits of the actual value is exactly equal to the display width. We can also specify the display width ourselves, for example:

mysql> DROP TABLE zerofill_table;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE zerofill_table (
    ->     i1 INT(5) UNSIGNED ZEROFILL,
    ->     i2 INT UNSIGNED
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO zerofill_table(i1, i2) VALUES(1, 1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM zerofill_table;
+-------+------+
| i1    | i2   |
+-------+------+
| 00001 |    1 |
+-------+------+
1 row in set (0.00 sec)

mysql>

In the newly created table, the display width of i1 field is 5, so four zeros are added in the final display result. However, when using the ZEROFILL attribute, you should pay attention to the following points:

  • When displaying query results, the conditions for automatically adding 0 to a column of data are as follows:

    • The column must be of type integer
    • The column must have the attribute UNSIGNED ZEROFILL
    • The number of digits of the actual value of this column must be less than the display width

  • When creating a table, if the column with ZEROFILL attribute declared does not declare the UNSIGNED attribute, MySQL will automatically generate the UNSIGNED attribute for the column.

    That is, if we create a table statement like this:

    CREATE TABLE zerofill_table (
    i1 INT ZEROFILL,
    i2 INT UNSIGNED
);

    MySQL will automatically add the UNSIGNED attribute to the i1 column, that is:

    CREATE TABLE zerofill_table (
    i1 INT UNSIGNED ZEROFILL,
    i2 INT UNSIGNED
);

    That is to say, MySQL now only supports the operation of automatically adding 0 to unsigned integers.

  • Each integer type has a default display width.

    For example, the default display width of TINYINT is 4, and the default display width of INT is (11)... If the UNSIGNED attribute is added, the display width of this type will be reduced by 1. For example, the display width of TINYINT UNSIGNED is 3, and the display width of INT UNSIGNED is 10.

  • The display width does not affect the actual storage space of the actual type.

    The display width only works when the number of digits of the integer is not enough to display the query results, and does not affect the storage space required by the data type and the data range that can be stored by the type, that is, INT(1) and INT(10) may be different only during display, but there is no difference in other aspects. For example, zerofill_ The display width of column i1 in table is 5, and the number of digits of number 12345678 is 8. It can still be filled in column i1:

    mysql> INSERT INTO zerofill_table(i1, i2) VALUES(12345678, 12345678);
Query OK, 1 row affected (0.01 sec)

mysql>

  • 0 will be added only when the number of digits of the actual value of the column is less than the display width. When the number of digits of the actual value is greater than the display width, it will be output as it is.

    For example, we just saved 12345678 in column i1. When displaying this value, the displayed data will not be truncated, but output as it is:

    mysql> SELECT * FROM zero_table;
+----------+----------+
| i1       | i2       |
+----------+----------+
|    00001 |        1 |
| 12345678 | 12345678 |
+----------+----------+
2 rows in set (0.00 sec)

mysql>

  • For columns that do not declare the ZEROFILL attribute, the display width is not used for a dime.

    The display width will only work when querying the column with the ZEROFILL attribute declared. Otherwise, the existence of the display width will be ignored.

A column has multiple attributes at the same time

Each column can have multiple attributes at the same time. The order of attribute declaration doesn't matter. It's good to separate each attribute with a blank ~

Tips:

Note that some attributes are conflicting. A column cannot have two conflicting attributes,. For example, a column cannot be declared as both PRIMARY KEY，Also declared as UNIQUE KEY，Cannot both declare as DEFAULT NULL，Also declared as NOT NULL. You should pay attention to this in the process of use.

Column properties when viewing table structure

In the previous chapter, we nagged about some statements that can show the table structure in the form of a table, but ignored some columns about the attributes of columns. Now let's look at student again_ Structure of info table:

mysql> DESC student_info;
+-----------------+-------------------+------+-----+---------+-------+
| Field           | Type              | Null | Key | Default | Extra |
+-----------------+-------------------+------+-----+---------+-------+
| number          | int(11)           | NO   | PRI | NULL    |       |
| name            | varchar(5)        | YES  |     | NULL    |       |
| sex             | enum('male','female')   | YES  |     | NULL    |       |
| id_number       | char(18)          | YES  | UNI | NULL    |       |
| department      | varchar(30)       | YES  |     | NULL    |       |
| major           | varchar(30)       | YES  |     | NULL    |       |
| enrollment_time | date              | YES  |     | NULL    |       |
+-----------------+-------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql>

You can see:

  • The NULL column indicates whether the column can store NULL. When the value is NO, it means that NULL is not allowed to be stored. If the value is YES, it means that NULL can be stored.

  • The Key column stores information about the so-called Key. When the value is PRI, it is the abbreviation of PRIMARY KEY and represents the PRIMARY KEY; UNI is the abbreviation of UNIQUE KEY and represents the UNIQUE attribute.

  • The Default column represents the Default value for that column.

  • The Extra column shows some additional information. For example, if a column has auto_ The increment attribute will be displayed in this column.

Naming of identifiers

Like database name, table name, column name, constraint name or other names we will encounter later, these names are all called identifiers. Although there are few restrictions on the naming of identifiers in MySQL, the following naming methods are not welcome:

  1. The name is full of numbers.

    Because numbers are also used in some MySQL statements. If all your names are numbers, the MySQL server will not know which is the name and which is the number. For example, the name 1234567 is illegal.

  2. There are white space characters in the name

    MySQL commands separate words by white space characters. For example, the following two lines are equivalent:

    CREATE DATABASE xiaohaizi;
CREATE   DATABASE   xiaohaizi;

    However, if the name you define has white space characters, it will be treated as two words, which will cause ambiguity. For example, the name word1 word2 word3 is illegal.

  3. The name uses reserved words in MySQL

    For example, create, DATABASE, INT, DOUBLE, DROP, TABLE and other words are used internally in MySQL and are called reserved words. If you use these words in your own defined name, it will also lead to ambiguity. For example, the name create is illegal.

Although some names may cause ambiguity, it is not impossible if you insist on using them. You can use backquotes to expand the name you define, so that the MySQL server can detect that you provide a name instead of anything else. For example, adding backquotes to several illegal names above will become legal names:

`1234567`
`word1 word2    word3`
`create`

Let's watch first_ The definition of table can enclose all the identifiers in it with back quotation marks, so that the semantics is clearer:

CREATE TABLE `first_table` (
    `id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `first_column` INT,
    `second_column` VARCHAR(100) DEFAULT 'abc'
);

Although the back quotation marks are powerful, we still recommend that you do not use all kinds of non mainstream names, and do not use names with all numbers, blank characters or MySQL reserved words. Since MySQL is implemented in C language, try to follow the specification of C language in name definition, that is, use lowercase letters, numbers, underscores, dollar symbols, etc. as names. If there are multiple words, each word is connected with underscores, such as student and student_info or something ~

Topics: Database MySQL SQL

Hot Topics

©2024 Programmer Think