You must understand how MySQL creates a database and creates a data table

This article brings you relevant knowledge about creating databases and data tables in the mysql database. Mysql is a commonly used database, and creating data tables is also a common operation. I hope it will be helpful to everyone.

MySQL creates a database and creates a data table

MySQL is the most commonly used database. In database operations, basic They are all add, delete, modify and query operations, referred to as CRUD.

Before this, you need to install MySQL first, and then create a database, data table, and operating user.

1. Database operation language

When operating the database, you need to use special database operation rules and syntax. This syntax is SQL (Structured Query Language) structured query. language.

The main function of SQL is to establish a connection with the database and perform addition, deletion, modification and query operations. SQL is the standard language for relational database management systems.

The role of SQL language:

1. Data Definition Language DDL (Data Definition Language). Used to create databases and data tables.

2. Data Manipulation Language DML (Data Manipulation Language). Used to insert, modify, and delete data from the data table.

3. Data Query Language DQL (Data Query Language). Used to query data from data tables.

4. Data Control Language DCL (Data Control Language). Used to set or modify the permissions of database users or roles.

When using SQL to operate the database, all SQL statements end with a semicolon. (You don’t need a semicolon when switching databases)

In SQL statements, case is not sensitive. When writing SQL statements, you can use case distinction according to the situation to increase readability.

2. Create a database

1. Connect to MySQL

Enter the mysql -u root -p command, press Enter, and then enter the MySQL password ( Don't forget the password), press Enter, and you will be connected to MySQL.

mysql -u root -p

Initially, I used the root user to log in. If I keep using the root user to log in at work, the risk is very high because the permissions are too large, so wait until the permissions are created. After selecting the appropriate user, do not log in to the root user frequently.

2. View the current database

Use show databases; to view which databases are in the currently installed MySQL.

show databases;

When you first install MySQL, there are four databases by default, information_schema, mysql, performance_schema, sys. Normally, we will not use these four databases directly, but do not delete these four databases, otherwise it will cause a lot of unnecessary trouble. If you delete it accidentally, it is recommended to reinstall MySQL, migrate your own data and back it up before reinstalling, or migrate an identical database from another server.

3. Create a database

Use create database database name; to create a database.

create database MyDB_one;

After the database is created successfully, the number of databases becomes 5, including the MyDB_one just created.

4. Set the character encoding when creating the database

Use create database database name character set utf8; to create the database and set the character encoding of the database.

create database MyDB_two character set utf8;

The database is directly created. The encoding method of the database is MySQL's default encoding method latin1 (single-byte encoding). Usually we store Chinese data in the database, so It is best to set the encoding method of the database to utf-8 so that Chinese can be displayed normally.

create database MyDB_three charset utf8;

character set can be abbreviated to charset and the effect is the same.

5. View and display the encoding method of the database

Use show create database database name; to display the creation information of the database.

show create database MyDB_one;
show create database MyDB_two;

If you don’t know the encoding method of a database, you can use show create database database name to view the encoding method of the database. You can see that the encoding method of MyDB_one just created is MySQL's default encoding latin1, and the encoding method of MyDB_two is utf-8.

Of course, this method cannot be displayed at the same time as creation, and can only view the encoding method of an existing database.

6. Use alter database database name character set utf8; modify the database encoding

alter database MyDB_one character set utf8;

如果一个数据库的编码方式不符合使用需求，可以进行修改。刚才创建的 MyDB_one 经过修改后，编码方式也变成了 utf-8 。

7. 进入或切换数据库

使用 use 数据库名 进入或切换数据库。

use MyDB_one
use MyDB_two;

刚连接上 MySQL 时，没有处于任何一个数据库中，如果要使用某一个数据库，就需要进入到这个数据库中。

use 数据库名 这个命令后面的分号可以省略，这是 SQL 语句中唯一可以省略分号的语句。

8. 显示当前数据库 select database();

select database();

进入数据库中，可以使用 select database(); 来查看当前处于哪个数据库中。长时间操作数据库时，在很多数据库中来回切换后，查看当前的数据库，避免操作错了数据库。

三、创建数据表

1. 查看当前数据库中的表

使用 show tables；查看当前数据库中有哪些表。

show tables;

在刚才创建的数据库 MyDB_one 中，还没有创建任何表，所以当前是空的。

2. 创建表

使用 create table 表名(字段1 字段类型,字段2 字段类型,字段3 字段类型,…); 来创建一张表。

create table Phone_table(pid INT, name CHAR(20), price INT);

在 MyDB_one 中创建了一个叫 Phone_table 的数据表，这张表有三个字段 pid，name，price 。为了增加 SQL 的可读性，字段名我用的是小写，字段类型用大写。

3. 显示表信息

用 show create table 表名; 来显示已创建的表的信息。

show create table Phone_table;

使用 show create table 表名; 可以显示表的字段信息， MySQL 的引擎，和默认的字符编码等信息。与显示数据库信息一样，show 只能显示已经创建了的数据表的信息，不能在创建的同时显示信息。

如果想更好地展示表的字段信息，可以使用 desc 表名; 来显示表的字段信息。

4. 给表增加字段

使用 alter table 表名 add 字段名 数据类型; 为已存在的表添加一个新字段。

alter table Phone_table add color CHAR(20);

添加后，刚才的表中多了一个字段，新增成功。

5. 删除表的字段

使用 alter table 表名 drop 字段名; 删除一个表中已存在的字段。

alter table Phone_table drop price;

删除字段后，表中不再有该字段。

6. 修改字段的数据类型

使用 alter table 表名 modify 字段名 数据类型; 修改表中现有字段的数据类型。

alter table Phone_table modify name VARCHAR(12);

修改之后，该字段的数据类型发生改变。

7. 修改字段的数据类型并且改名

使用 alter table 表名 change 原字段名 新字段名 数据类型; 修改表中现有字段的字段名和类型。

alter table Phone_table change name pname CHAR(18);

现在，将表的 name 改成了 pname ，同时修改了 pname 的数据类型。

四、MySQL 常用字段类型

一个数据表是由若干个字段组成的，一个表十几个字段也很正常，每个字段表示不同的信息，需要使用不同类型的数据。

所以在创建表的时候，要为每个字段指定适合的数据类型。

MySQL 中常用的字段类型有以下这些：

1. 整数类型

Data type	Data range
TINYINT	-128 -- 127
SMALLINT	-32768 -- 32767
MEDIUMINT	-2^23 -- 2^23-1
INT	-2^31 -- 2^31-1
BIGINT	- 2^63 -- 2^63-1

2. String type

##LONGTEXT0 -- 2^32-1 BytesVery large text dataBLOB0 -- 65535 bytesBinary long text dataLONGBLOB0 -- 2^32-1 bytesBinary extremely large text data

Data type	Byte range	Purpose
CHAR(n)	0 -- 255 bytes	Fixed length characters String
VARCHAR(n)	0 -- 65535 bytes	Variable length string
TEXT	0 -- 65535 bytes	Long text data

3. Decimal type

m represents the total length of the floating point number, n represents the number of significant digits after the decimal point.

Data TypeData UsageData RangeFloatFloat(m,n)7 significant digitsDoubleDouble(m,n)15 Significant digitsDecimalDecimal(m,n)28 significant digits4. Time type

Data typeFormatUse## DATETIMEYEARDATETIMETIMESTAMP5. Enumeration type

YYYY-MM-DD	Date
HH:MM:SS	Time
YYYY	Year
YYYY-MM- DD HH:MM:SS	Date and time
10 or 13 digit integer (seconds)	Timestamp

enum(enumeration value 1, enumeration value 2,...)

Enumeration types can only select one of the listed values, such as gender.

Recommended learning:

mysql video tutorial

The above is the detailed content of You must understand how MySQL creates a database and creates a data table. For more information, please follow other related articles on the PHP Chinese website!