使用mysql语句操作数据表(table)

1 前提

1.1 分析

选择你要操作的数据库,That is, you need to add a new table in that database、删除表等操作

1.2 基本语法

USE 需要操作的数据库名称;

1.3 示例mysql语句

USE classTest01;
/*这个mysql的指令,不是标准sql语句*/

1.4 示例sqlA screenshot of the execution of the statement

2 sqlstatement to create a new data table

2.1 基本语法

CREATE TABLE [IF NOT EXISTS] `数据表名字`  (
    列名1 数据类型 该列所需约束,
    列名2 数据类型 该列所需约束,
    .....
    列名n 数据类型 该列所需约束,
)character set 字符集 collate 校对规则 engine (存储)引擎;
/*
每个字段/Separate columns with commas
Tables can also specify character sets and collation rules,若没有写,The default is to inherit the character set and collation rules of the database where it is located
*/

2.2 创建一个名称为student的数据表(Character set and collation are inherited from the data table by default

2.2.1 navicatGraphical interface display

a Select the database where you want to create the table,并且双击它

b Right-click on the table,Then double-click New Table

c Set the field properties of the data table

d 点击保存按钮

e Give the new data table a name

f 查看是否新建成功

2.2.2 DOS界面

a mysql对应的sql语句

CREATE TABLE IF NOT EXISTS student02 (
   name varchar(10) PRIMARY KEY;
);      

b mysql对应的sqlA screenshot of the statement running example

2 mysqlThe statement queries the current structure of a data table

2.1 基本语法

 SHOW CREATE TABLE 数据表名称;

2.2 示例mysql语句

SHOW CREATE TABLE student02;

2.3 示例mysqlA screenshot of the statement in action

3 sqlThe statement queries which data tables are under the current database

3.1 基本语法

 SHOW TABLES;
 /*
 SELECT database()You can get which database is currently operating
 */

3.2 示例sql语句

 SHOW TABLES;

3.3 示例mysqlA screenshot of the statement in action

4 sql语句修改数据表

4.1 sqlStatement operations modify field properties

4.1.1基本语法

ALTER TABLE 数据表名称 MODIFY COLUMN 字段名称 The part you need to modify;
/*
The part you need to modify can be the length of the data type、数据类型、默认值,约束等
*/

4.1.2 示例mysql语句

ALTER TABLE student02 MODIFY COLUMN age int(15) DEFAULT 0;
/*
更改student02这个表中的age字段的int类型的长度为15 去掉非空,Added default values0
*/

4.1.3 示例mysqlA screenshot of the statement in action

a 修改前

b 修改后

4.2 sqlThe statement modifies the current character set of the data table

4.2.1基本语法

ALTER TABLE 数据表名称 CHARACTER SET 修改后的字符集;

4.2.2 示例mysql语句

ALTER TABLE student02 CHARACTER SET gbk;

4.2.3 示例mysqlA screenshot of the statement in action

执行alter语句之前

执行alter语句之后

5 sqlThe statement deletes an existing data table

5.1 基本语法

DROP TABLE [IF EXISTS] The name of an existing data table;
/*
  []The content in parentheses is optional,一般加上
  如果不加上,Then delete the non-existing data table will appearERROR 1051 (42S02): Unknown table 'classtest01.student03'
  Plus only a warning will appear
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  当时0 rowsrow affected,Indicates that the statement was not deleted successfully(There is no such table in the currently operating database)
  If a prompt appears after deletionQuery OKIt means the deletion is successful
*/

5.2 示例sql语句

DROP TABLE IF EXISTS student02;

5.3 示例sqlA screenshot of the statement in action