Shulou(Shulou.com)06/01 Report--

MySQL database

MySQL data type

Defining the type of data field in MySQL is very important for optimizing your database.

MySQL supports many types, which can be roughly divided into three types: numeric, date / time, and string (character) types.

Numerical type

MySQL supports all standard SQL numeric data types.

These types include strict numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC) and approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).

The keyword INT is synonymous with INTEGER, and the keyword DEC is synonymous with DECIMAL.

The BIT data type holds bit field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.

As an extension of the SQL standard, MySQL also supports integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the storage and range of each integer type required.

Types

Size range (signed) range (unsigned)

Use

TINYINT1 byte (- 128127) (0255) small integer value SMALLINT2 byte (- 32 768 532 767) (0meme 65 535) large integer value

MEDIUMINT

3 bytes (- 8 388 608) (0388 608) (0388 677 215) large integer values of INT or INTEGER4 bytes (- 2 147 483 648)

(0pr 4 294 967 295)

Large integer value

BIGINT

8 bytes (- 9 233 372 036 854 775 808 revising 9 223 372 036 854 775 807) (0meme 18 446 744 073 709 551,615) maximal integer value FLOAT4 bytes (- 3.402 823 466 Ecolors 38 words 1.175 494 351 Ecolor 38), 0, (1.175 494,351 E38 3.402 823 466 351 Ecolors 38) 0, (1.175 494 351 Elysees 38 3.402 823 466 Ecolors 38)

Single precision

Floating point value

DOUBLE8 bytes (- 1.797 693 134 862 315 7 Ecolors 308 words 2.225 073 858 507 201 4 Elyle 308), 0, (2.225 073 858 507 201 4 Elyle 308 Lines 1.797 693 134 862 315 7 Ecolors 308) 0, (2.225 073 858 507 201 4 Elysees 308 1.797 693 134 862 315 7)

Double precision

Floating point value

DECIMAL for DECIMAL (M < D), if M > D, it is M > D, otherwise it is M = D; otherwise, the values of M and D depend on the decimal values of M and D.

Create a database

After logging in to the MySQL service, we can use the create command to create the database. The syntax is as follows:

CREATE DATABASE database name

The following command simply demonstrates the process of creating a database, named mood:

Root@localhost ~] # mysql-uroot-pabc123 # # Log in mysql > create database mood; # # to create Query OK, 1 row affected (0.00 sec) mysql > show databases # # verify +-+ | Database | +-+ | information_schema | | mood | | mysql | | performance_schema | | sys | +-+ 5 rows in set (0.00 sec) Select a database

After you connect to the MySQL database, there may be multiple operational databases, so you need to select the database you want to operate on.

The following example selects the database mood:

[root@host] # mysql-u root-pEnter password:*mysql > use mood;Database changed

After executing the above command, you have successfully selected the RUNOOB database, which will be executed in the RUNOOB database in subsequent operations.

Note: all database names, table names, and table fields are case sensitive. So you need to enter the correct name when using the SQL command.

Delete database

Be careful when deleting a database, because all data will disappear after the delete command is executed.

Delete the database mood:

Drop database; # # format mysql > drop database mood; # # Delete database Query OK, 0 rows affected (0.00 sec) mysql > show databases # # View database +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | +-+ 4 rows in set (0.00 sec) MySQL data sheet

The following information is required to create an MySQL data table:

Table name

Table field name

Define each table field

Grammar

The following is the general SQL syntax for creating MySQL data tables

CREATE TABLE table_name (column_name column_type)

Create a table named meed in the database mood:

Mysql > use mood; # # enter the database Database changedmysql > create table meed (id int not null primary key auto_increment,name char) not null,size decimal, age int (2); # # create table Query OK, 0 rows affected (0.01 sec) mysql > desc meed # # View table structure +-+ | Field | Type | Null | Key | Default | Extra | +- -- +-+ | id | int (11) | NO | PRI | NULL | auto_increment | | name | char (10) | NO | | NULL | | size | decimal (10Magin2) | YES | | | NULL | | age | int (2) | YES | | NULL | | +-+-+ 4 rows in set (0.00 sec) |

Delete table meed:

Mysql > drop table meed; # # Delete Query OK, 0 rows affected (0.01 sec) mysql > show tables; # # View the table Empty set in the data space (0.00 sec)

MySQL insert data

INSERT INTO SQL statements are used in the MySQL table to insert data.

You can insert data into the datasheet through the mysql > command prompt window, or through the PHP script.

Grammar

Insert data into the meed table:

Mysql > insert into meed (name,size,age) values ('xuyan',175.3,27); # # insert Query OK, 1 row affected (0.01 sec) mysql > select * from meed # # View the data of the table +-+ | id | name | size | age | +-+ | 1 | xuyan | 175.30 | 27 | +-+-- -+-+ 1 row in set (0.00 sec)

Update the age field in the meed table:

Mysql > update meed set age=28 where id=1; # # change 27 to 28Query OK, 1 row affected (0.01sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from meed # # View the table +-+ | id | name | size | age | +-+ | 1 | xuyan | 175.30 | 28 | +-+- -+ 1 row in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.