Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to create a database and create a data table in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MySQL creates databases and data tables

MySQL is the most commonly used database, in the database operation, basically is the addition, deletion, change and search operation, referred to as CRUD.

Before that, you need to install MySQL, then create databases, data tables, and manipulate users.

1. Database operation language

When the database is operated, it needs to use special database operation rules and syntax, which is SQL (Structured Query Language) structured query language.

The main function of SQL is to establish a connection with the database and to add, delete, modify and check. SQL is the standard language of relational database management system.

The role of the SQL language:

1. Data definition language DDL (Data Definition Language). Used to create databases, data tables.

two。 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 a data table.

4. Data control language DCL (Data Control Language). Used to set or modify permissions for database users or roles.

When you use SQL to manipulate a database, all SQL statements end with a semicolon. (you can switch databases without semicolons)

In SQL statements, case-insensitive SQL statements can be written with case differences to increase readability.

Second, create a database

1. Connect MySQL

Enter the mysql-u root-p command, enter the MySQL password (don't forget the password), enter and enter to connect to MySQL.

Mysql-u root-p

At first, they all use root users to log in. If you always log in with root users at work, because the permissions are too large, the risk is very great, so after creating users with appropriate permissions, do not often log in to root users.

two。 View the current database

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

Show databases

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

3. Create a database

Use the create database database name; create the database.

Create database MyDB_one

After the database is created successfully, the number of databases becomes five, which is more than the MyDB_one you just created.

4. Set character encoding when creating a database

Create the database with the create database database name character set utf8; and set the character encoding of the database.

Create database MyDB_two character set utf8

Directly create the database, the database encoding method is MySQL default encoding method latin1 (single byte encoding), usually we will store Chinese data in the database, so it is best to set the database encoding method to utf-8, so that the 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 of the database

Use the show create database database name; displays the creation information of the database.

Show create database MyDB_one;show create database MyDB_two

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

Of course, this method cannot be displayed at the same time as it is created, but can only view the encoding of an existing database.

6. Modify the database encoding using the alter database database name character set utf8;

Alter database MyDB_one character set utf8

If the coding mode of a database does not meet the requirements, it can be modified. After the MyDB_one you just created has been modified, the encoding has also been changed to utf-8.

7. Enter or switch the database

Enter or switch the database using the use database name.

Use MyDB_oneuse MyDB_two

When you first connect to MySQL, you are not in any database, and if you want to use a database, you need to enter that database.

The semicolon after the command use database name can be omitted, which is the only statement in the SQL statement that can omit the semicolon.

8. Show the current database select database ()

Select database ()

When you enter the database, you can use select database (); to see which database you are currently in. When operating the database for a long time, after switching back and forth in many databases, check the current database to avoid operating the wrong database.

Third, create a data table

1. View tables in the current database

Use show tables; to see which tables are in the current database.

Show tables

In the database MyDB_one you just created, no tables have been created yet, so it is currently empty.

two。 Create a tabl

Use the create table table name (field 1 field type, field 2 field type, field 3 field type, …) To create a table.

Create table Phone_table (pid INT, name CHAR (20), price INT)

A data table called Phone_table is created in MyDB_one, which has three fields pid,name,price. To increase the readability of SQL, I use lowercase for field names and uppercase for field types.

3. Display table information

Use the show create table table name; to display information about the created table.

Show create table Phone_table

Use the show create table table name; you can display information such as the field information of the table, the MySQL engine, and the default character encoding. Like displaying database information, show can only display information about data tables that have been created, not at the same time as it is created.

If you want to better display the field information of the table, you can use the desc table name; to display the field information of the table.

4. Add fields to the table

Use alter table table name add field name data type; add a new field to an existing table.

Alter table Phone_table add color CHAR (20)

After being added, one more field has been added to the table, which has been successfully added.

5. Delete fields from a table

Use the alter table table name drop field name; delete a field that already exists in a table.

Alter table Phone_table drop price

After you delete a field, the field is no longer in the table.

6. Modify the data type of a field

Use alter table table name modify field name data type; modify the data type of existing fields in the table.

Alter table Phone_table modify name VARCHAR (12)

After modification, the data type of the field changes.

7. Modify the data type of the field and rename it

Use the alter table table name change original field name new field name data type; modify the field name and type of existing fields in the table.

Alter table Phone_table change name pname CHAR (18)

Now, the name of the table has been changed to pname, and the data type of pname has been changed.

4. MySQL commonly used field types

A data table is made up of several fields, and it is normal for a table to have more than a dozen fields. Each field represents different information and requires different types of data.

So when you create a table, specify the appropriate data type for each field.

The field types commonly used in MySQL are as follows:

1. Integer type

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

two。 String type

Data type byte range usage CHAR (n) 0-255byte fixed length string VARCHAR (n) 0-65535 byte variable length string TEXT0-65535 byte long text data LONGTEXT0-2 ^ 32-1 byte maximum text data BLOB0-65535 bytes binary long text data LONGBLOB0-2 ^ 32-1 byte binary maximum text data

3. Decimal type

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

Data type data usage data range FloatFloat (mline n) 7-digit significant number DoubleDouble (m-line n) 15-digit significant number DecimalDecimal (m-line n) 28-digit significant number

4. Time type

Data type format usage DATEYYYY-MM-DD date TIMEHH:MM:SS time YEARYYYY year DATETIMEYYYY-MM-DD HH:MM:SS date and time TIMESTAMP10 bits or 13-bit integer (seconds) timestamp

5. Enumerated type

Enum (enumerated value 1, enumerated value 2...)

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

That's all for "how to create databases and tables in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.