The first step in getting started with MySQL database is to create a table

Create a database

Right click - Create a new database

Enter the library name, select the character set and sorting rules, and click OK

Database created successfully

Create a new table

my-table-right click-create table

As shown in the figure above, in the first tab "Columns"

Name: The name of the field

Type: There are dozens of field types, the following are commonly used

• char, can store fixed-length strings
• varchar, can store variable-length strings (the difference between fixed-length and variable-length is introduced in length)
• int, can store numbers between -2^31 (-2,147,483,648) and 2^31 - 1 (2,147,483,647)
• datetime, can store date type data

Length: The maximum length of the data to be stored. The length has different meanings for different field types.

• When the field type is char, the length indicates the maximum length of the storage. For example, when it is 100, even if the stored data does not exceed 100 bits, it will occupy 100 bits of storage space.
• When the field type is varchar, this length indicates the maximum length of the data to be stored. For example, when it is 100, the space occupied by the data will be allocated according to the length of the stored data. The shorter the data, the less space it occupies.
• When the field type is int, the length is meaningless. It does not matter whether you enter 0 or 100, because the int type has a certain range.
• When the type is datetime, the length indicates the number of decimal places after the seconds. If it is 0, it can be saved as 2021-05-27 00:00:00, and if it is 3, it can be saved as 2021-05-27 16:48:41.000

Decimal point: Just use the default value, no need to fill it in, it is useless

Not null: The default is no, which means that you don't need to fill it in when inserting data. By default, null will be inserted, which means there is nothing, not even an empty string. If it is checked, you must fill in this field when querying data, otherwise an error will be reported.

There is a blank column behind it that is not null. After clicking it, a key will appear.

This is used to set the primary key. Setting a field as the primary key is equivalent to this field being the only data in the table, that is, non-repeated data. It is usually represented by a meaningless field, such as id.

Area below: Selecting different field types will load different content. Here are a few examples.

int: mainly focus on automatic increment. If it is checked, you don't need to insert the id field when inserting data. The database will add 1 each time to the table. Ignore other options.

varchar: There are three default options. When you do not insert this field when inserting data, the database will automatically insert data based on this option: when you select the first one, you can edit this input box and insert custom data; when you select the second one, null (non-string null, which means nothing) will be inserted; when you select the third one, an empty string will be inserted. You generally don't need to worry about other options. When you save the table, the fields will be filled with default values.

Regardless of the field type, there will be a comment input box below, where you can enter the meaning of the field. Although you don’t have to fill it in, it is necessary to fill it in to develop a good habit. Here I want to say that there is no regulation on what type to choose for a field. It mainly depends on the company's requirements at work. For example, Company A requires int for age, char for gender, and datetime for time, while Company B requires varchar for all fields. In the following tutorials, I will set all field types to varchar, so you don’t have to worry about why.

Next, I will create a user table and set up some commonly used fields for users. The field name is generally defined in English or full pinyin, and can be separated by underscores. How to define it depends on the company's requirements. Some companies say that using pinyin is unprofessional and they must use English. This is nonsense. As long as it is easy to understand, it's fine. When using pinyin, you need to avoid using the first letter. For example, the user name is yhmc, and no one knows what this field means. As long as you don't mix uppercase and lowercase letters, and don't mix English and pinyin, it's fine.

Please note that the "save" I will talk about in the future is not clicking the save button on the interface, but using the shortcut key Ctrl+S. This has the same effect as directly clicking the save button, but is more convenient.

As shown above, save the file, and then a box for entering the table name will pop up. Just click OK after entering the name. If an error message appears when you click OK, check whether the type is entered incorrectly or for other reasons. Sometimes, when you create a table, an error message may appear when you save it even if everything is correct. This is a bug in Navicat. You can turn it off and create the table again, or create a table with one field first, save it, and then modify the table structure and add the field.

You can double-click to open the table and manually add, modify, or delete data

It is recommended that you use all the options in the above menu to try out their functions. The next section will discuss adding, deleting, modifying and querying tables.

This is the end of this article about the first step of getting started with MySQL database: creating a table. For more information about creating a table in MySQL database, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!