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

This article mainly gives you a brief introduction to the basic operation tutorial of SQL table in MySQL. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the basic operation tutorial of SQL table in MySQL can bring you some practical help.

Basic operation of SQL

Basic operation: CURD, that is, add, delete, change and check.

Depending on the object of operation, we can divide the basic operations of SQL into three categories: library operations, table (field) operations, and data operations.

Table operation

1 newly added table

Basic syntax:

Create table [if not exists] + table name (field name data type,... Field name data type / * last line, no comma required * /) [table options]

Where if not exists indicates

If the table name does not exist, the creation code is executed; if the table name exists, the creation code is not executed.

Table options are used to control the presentation of the table, a total of three, they are:

Character set setting: charset/ character set+ specific character set, which is used to represent the encoding format of data storage. Commonly used character sets include GBK and UTF8.

Proofreading set setting: collate+ specific proofreading set, which represents the rules of data comparison, which depends on the character set.

Storage engine: engine+ specific storage engine, default is InnoDB, commonly used is MyISAM.

Because any table belongs to a database, when you create a table, you must first specify a specific database. Here, there are two ways to specify a database:

Type 1: explicitly specify the database to which the table belongs, example

Create table if not exists test.student (name varchar (10), age int, / * Integer does not need to specify a specific length * / grade varchar (10) / * the last line, no need to add commas * /) charset utf8

Type 2: implicitly specify the database to which the table belongs, example

Use test; / * use + database name, indicating switching to the specified database. This command is fine without a semicolon, but it is not recommended * / create table if not exists student (name varchar (10), age int, / * Integer does not need to specify a specific length * / grade varchar (10) / * the last line, no comma * /) charset utf8

2 query table

View all-> basic syntax: show tables

View section (fuzzy query)-> basic syntax: show tables like 'pattern'

Among them, pattern is a matching pattern, and there are two, which are:

%: indicates that multiple characters are matched

_: means to match a single character.

In addition, when matching a table name with an underscore _, you need to add a backslash before the underscore to escape.

Example: show tables like'% tweak; means to match all tables that end in t.

View the table creation statement-> basic syntax: show create table + table name

Here, we can also replace the semicolon in the above statement with\ g and\ G, where\ g is equivalent to the semicolon, and\ G rotates the table structure 90 degrees to become a vertical structure.

View field information in the table-> basic syntax: desc/describe/show columns from + table name

3 update table

Here, it is important to note that the modification of the table is divided into modifying the table itself and modifying the fields in the table.

Type 1: modify the table itself

Modify the table name, basic syntax: rename table old table name to new table name

Modify table option, basic syntax: alter table + table name + table option [=] + value

Type 2: modify fields in the table, add, modify, rename and delete

Example: alter table student drop age

Note: if data already exists in the table, deleting this field will empty all data in the field and is irreversible, so use it with caution.

The location represents the location stored in this field, which is divided into first (the first location) and after + field name (after the specified field, the default is the last location).

Example: alter table student change grade class varchar (10)

The location represents the location stored in this field, which is divided into first (the first location) and after + field name (after the specified field, the default is the last location).

Example: alter table student modify name char (10) after id

The location represents the location stored in this field, which is divided into first (the first location) and after + field name (after the specified field, the default is the last location).

Example: alter table student add column id int first

New field, basic syntax: alter table + table name + add + [column] + field name + data type + [column attribute] [location]

Modify fields, basic syntax: alter table + table name + modify + field name + data type + [column properties] [location]

Rename field, basic syntax: alter table + table name + change + old field name + new field name + data type + [column attribute] [location]

Delete field, basic syntax: alter table + table name + drop+ field name

4 delete the table

Basic syntax:

/ * * multiple tables can be deleted at one time * / drop table + Table 1, Table 2.

Here, it is important to note that this deletion is an irreversible operation. I hope you will use it carefully.

Warm reminder: the content enclosed by the symbol [] indicates optional; the symbol + indicates the meaning of connection.

This is the end of the basic operation tutorial of SQL table in MySQL. If you want to know about other related issues, you can continue to follow our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.