MySQL database creation, usage, common data types

I. Usage

1. Use the show statement to find out the current database on the server: mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+ 3 rows in set (0.00 sec) 2. Create a database, abccs Mysql> Create Database abccs; Note that different operating systems are case sensitive. 3. Select the database you created Mysql> Use abccs Database changed Now you have entered the database abccs you just created. 4. Create a database table First, check what tables exist in your database: Mysql> show tables; Empty set (0.00 Sec) It indicates that no database table exists in the Database just created. Create a database table mytable: Create a birthday table for your employees. The table contains the employee name, gender, birth date, and city of birth. mysql> CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1), -> birth DATE, birthaddr VARCHAR(20)); Query OK, 0 rows affected (0.00 sec) Because the column values of name and birthadd change, varchar is selected and its length is not necessarily 20. You can choose any length from 1 to 255. If you need to change its font length in the future, you can use the alter table statement .); Gender can be expressed by only one character: "M" or "F". Therefore, char (1) is used, and date is used for the birth column. After creating a table, we can look at the results just now and use show tables to show which tables are in the database: mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | mytables | +---------------------+ Create a student table: Create Table student ( Id int not null auto_increment, Username varchar (200) not null, Password varchar (20) not null, Age int, Primary Key (ID) ) Engine = MyISAM default charset = GBK This is the table creation statement of the MySQL database. This table has an auto-increment ID column as the primary key. The user name, password, and age columns are specified in the last line "engine = MyISAM default charset = GBK ". The default Character Set of the table is a GBK Chinese character, which is unique to MySQL. 5. display table structure: mysql> DESCRIBE mytable; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | deathaddr | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) 6. Add records to the table We first use the SELECT command to view the data in the table: Mysql> select * From mytable; Empty set (0.00 Sec) This indicates that the table created just now has no records. Add a new record: mysql> insert into mytable -> values (′abccs′,′f′,′1977-07-07′,′china′); Query OK, 1 row affected (0.05 sec) Use the SELECT command above to check what has changed. We can add records of all employees to the table one by one using this method. 7. load data into a database table in text mode It is troublesome to input data one by one. We can add all records to your database table using text files. Create an example file named "“mysql.txt". Each line contains a record. Use a tab to separate the values and give them in the order of the columns listed in the create table statement. For example: abccs f 1977-07-07 china 　 mary f 1978-12-12 usa tom m 1970-09-02 usa Use the following command to load the parent file named mytable.txt to the mytable Table: mysql> load data local infile "D:/mytable.txt" into table database name. Table Name Run the following command to check whether the data has been input to the database table: mysql> select * From mytable;

Ii. Data Types

Char (m)
The Char data type is a string of a fixed length and can contain up to 255 characters. M indicates the length of the string.

Varchar (m)
Varchar is a more flexible data type than Char. It is also used to represent character data, but varchar can save strings of variable length. M indicates the maximum length of the string that can be saved by the data type. Strings whose length is smaller than the maximum value can be saved in the data type. Therefore, it is more wise to use varchar data types for data objects that are difficult to estimate the exact length. The varchar data type supports a maximum of 255 characters.
Note that, although varchar is more flexible to use, char data processing is faster in terms of system performance, sometimes it can even exceed 50% of the varchar processing speed. Therefore, users should consider all aspects of the database design in order to achieve the best balance.

INT (m) [unsigned]
The Int data type is used to store any integer data from-2147483647 to 2147483648. If you use the unsigned option, the valid data range is adjusted to 0-4294967295.

Float [(m, d)]
The float data type is used to indicate floating point data with a small value, which can provide more accurate data accuracy. M indicates the length of the floating point data (that is, the total length of the decimal point), and D indicates the number of digits of the floating point data on the right of the decimal point.

Date
The date data type is used to save date data. The default format is yyyy-mm-dd.

Text/blob
The text and BLOB data types can be used to save 255 to 65535 characters. If you need to save a large segment of text to the database, you can select the text or BLOB data type. The text and BLOB data types are basically the same. The only difference is that text is case insensitive, while blob is case sensitive to characters.

Set
The set data type is a combination of multiple data values. Any part or all of the data values are valid data of this data type. The Set Data Type can contain up to 64 specified data values.

Enum
The data type of Enum is basically the same as that of set. The only difference is that Enum can only select one valid data value.

4. Create a table

You can create a dedicated test database first.

Mysql> Create Database TMP; # create a database TMP

Mysql> show databases; # Show all databases

Mysql> Use TMP; # use this database

Mysql> show tables; # display tables in the database

Mysql> Create Table Test (name varchar (20), Id int not null auto_increment, primary key (ID ));

Mysql> show tables;

Mysql> drop database tmp; # Delete the database and the table test is also destroyed.

Primary Key
Fields with primary key constraints are used to differentiate different records in the same data table. Because the same data table does not have two primary key fields with the same value, the primary key plays a very important role for data tables that need to strictly distinguish different records.

Auto_increment
The field value with the auto_increment restriction starts from 1. Each time a new record is added, the value increases by 1 accordingly. Generally, we can use the auto_increment field as the identification field for each record in the data table.

Not null
The not null condition specifies that users cannot insert null values in this field.