MySQL Data Type and operation data table, mysql DATA type data

MySQL Data Type and operation data table, mysql DATA type data

Review in the previous section

I. Data Types

What is a data type?

Data type refers to the data features of columns, stored procedure parameters, expressions, and local variables. It determines the data storage format and represents different information types.

The most direct understanding of the so-called data type is that some of us store numbers, while some of the numbers are sometimes stored as integers, and some may be stored as decimal places, it is also possible to store data in the date and week type, which is the most intuitive and straightforward data type.

In mysql, there are mainly the following types of data:

Integer:

Floating Point Type:

Float stores up to seven decimal places. Generally, float is used, and double occupies a large storage space. For optimization, You need to select the most appropriate, not necessarily the largest.

Datetime type:

Start Time and end time of each date type:
1. YEAR: 1970 to 2069
2. TIME:-838: 59: 59 to 838: 59: 59
3. DATE: 1000-1-1 to 9999-12-31
4. DATETIME: 1000-1-1 00:00:00 to 9999-12-31 23:59:59
5. TIMESTAMP storage range: 00:00:00 to 2037-12-31 23:59:59

The date and time types are rarely used in practice because they involve cross-Time Zone issues. If you really want to use the time type, it is generally represented in numbers.

Character Type:

(1) fixed type: char (M) is of fixed length type. For example: char (5) When we store "abc", the remaining two digits are filled with spaces. Range: 0 ~ 255
(2) varchar (M) is of variable length type. For example: varchar (5) When we store "abc", only abc is saved in varchar without spaces. Range: 0 ~ 65535

(3) 1 Byte = 8bit, 11111111 = 2E8 = 255.

(4) enum ('value1 ', 'value2',...) can only be selected from the given enum Enumeration
(5) SET ('value1 ', 'value2', 'value3'...) is equivalent to a SET in which values are arranged and combined. You can select multiple values.

Ii. Data Table operations

A data table (or table) is one of the most important components of a database and the basis of other objects.

Our relational database is actually a two-dimensional table, which strictly refers to our data tables. In this two-dimensional table, rows are called records and columns are called fields. If we only create databases without data tables, we only have an empty shelf and there is no place to actually store data. to store data, we must design tables.

1. Create a data table

You can USE the USE command to open a database (USE Database Name );

Display the database opened by the current user: select batabase ();

Create table: create table [if not exists] table_name (
Column_name data_type,
.........
)

For example:

2. View data tables

Show tables [FROM db_name] [LIKE 'pattern' | WHERE expr]
Show tables; // View TABLES in the current database
Show tables from mysql; // View TABLES in the mysql database
Select database (); // view the current DATABASE

Note: 1. Check whether the data table exists: show tables [FROM db_name] [LIKE 'pattern' | WHERE expr]
2. FROM db_name: You can view tables in other databases.

3. view the data table structure

4. insert and search records

5. null and non-null values