MySQL Tutorial: Basic Operations of SQL Tables

This article mainly introduces you to the basic operations and table operations of SQL. I hope it will be helpful to friends in need!

Recommended reference tutorial: "SQL Tutorial"

SQL basic operations

Basic operations: CURD, that isadd, delete, modify, query .

According to different operation objects, we can divide the basic operations of SQL into three categories: library operations, table (field) operations and data operations.

Table operation

1 Add a new table

Basic syntax:

create table [if not exists] + 表名(
    字段名称 数据类型,
    ……
    字段名称 数据类型   /* 最后后一行，不需要加逗号 */
)[表选项];

Among them, if not exists means

• If the table name does not exist, the creation code will be executed; if the table name exists, the creation code will not be executed.

Table options are used to control the presentation form of the table. There are three types, namely:

• Character set setting: charset/ character set Specific character set used to represent the encoding format of data storage. Commonly used character sets include GBK and UTF8, etc.

• Collation set setting: collate The specific collation set represents the rules of data comparison, which depends on the character set.

• Storage engine: engine Specific storage engine, the default is InnoDB, and commonly used ones are MyISAM.

Since any table belongs to a certain database, when creating a table, you must first specify the specific database. Here, there are two ways to specify the database, which are:

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

create table if not exists test.student(
    name varchar(10),
    age int,            /* 整型不需要指定具体的长度 */
    grade varchar(10)   /* 最后后一行，不需要加逗号 */
)charset utf8;

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

use test;               /* use + 数据库名称，表示切换到指定的数据库，这句命令其实不加分号也可以，但不建议这么做 */create table if not exists student(
    name varchar(10),
    age int,            /* 整型不需要指定具体的长度 */
    grade varchar(10)   /* 最后后一行，不需要加逗号 */
)charset utf8;

2 Query table

View all–> Basic syntax: show tables;

View part (fuzzy query)–> Basic syntax: show tables like 'pattern';

Among them, pattern is the matching pattern, there are two types, namely:

• ##%: Matches multiple characters;

• _: Matches a single character.

In addition, when matching table names containing underscores

_, you need to add a backslash \_ in front of the underscore to convert meaning operation.

Example:

show tables like '%t'; means matching all tables ending with t.

View the creation statement of the table –> Basic syntax:

show create table table name;

Here, we can also use

\g and \G replaces ; semicolon in the above statement, where \g is equivalent to semicolon, and \G is equivalent to semicolon At the same time, rotate the query table structure 90 degrees into a vertical structure.

View field information in the table–> Basic syntax:

desc/describe/show columns from table name;

3 Update table

Here, you need to pay attention: modification of the table is divided into modifying the table itself and modifying the fields in the table.

• Category 1: Modify the table itself

• Modify the table name, basic syntax:

  rename table old table name to new table name;

• Modify table options, basic syntax:

  alter table table name table option [=] value;

• Category 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 the field will clear all data in the field. It is irreversible, so use with caution.

• Among them, the position indicates the location where this field is stored, which is divided into

  first (the first position) and after field name (after the specified field, Default is the last position).

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

Among them, the position indicates the location where this field is stored, which is divided into
• first (the first position)

  and after field name (after the specified field, the default is the last position) .

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

where the position represents this The location where the field is stored 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] [position];

  
  

Modify the field, basic syntax:
• alter table table name modify field name data type [Column Properties][Position];

• 重命名字段，基本语法：alter table + 表名 + change + 旧字段名 + 新字段名 + 数据类型 + [列属性][位置];
  

• 删除字段，基本语法：alter table + 表名 + drop+ 字段名;
  

4 删除表

基本语法：

/** 可以一次删除多张表 */drop table + 表1, 表2 ... ;

在这里，需要注意：此删除为不可逆操作，希望大家谨慎使用。

温馨提示：符号[]括起来的内容，表示可选项；符号+，则表示连接的意思。

The above is the detailed content of MySQL Tutorial: Basic Operations of SQL Tables. For more information, please follow other related articles on the PHP Chinese website!