MySQL的表操作

#前言:我们说过,库相当于一个文件夹,表相当于文件夹里的一个个文件,表里面的一条记录相当于一行内容,表中的一条记录有对应的标题,称为表的字段

#直观表

 #id,name,sex,age为表的字段,其余一行内容称为一条记录

1.建表

#创建表语法格式

Create  table <表名><字段名1><类型1>,
        …..
    <字段名n><类型n>
);
#提示:
  1.其中create table是关键字,不能更改,但是大小写可以变化
  2.字段名和类型是必须的
  3.同张表中,字段名不能相同

 #查看帮助:help create table

 

#例子:

#1.创建db库
mysql> create database db;
Query OK, 1 row affected (0.00 sec)

mysql> use db;
Database changed

#2.创建一个student表
mysql> create table student (
    -> id int(4) not null auto_increment,
    -> name char(20) not null,
    -> age tinyint(2) not null default '0',
    -> dept varchar(16) default null,
    -> primary key(id));
Query OK, 0 rows affected (0.01 sec)
#解释表的字段意思:
  create table 表示创建表的固定关键字,student为表名,有四个字段,分别表示
  id:学号列, int:数字类型, 4:长度为4, not null:不为空值, auto_incement:自增长
  name:名字列,char:定长字符类型, 20:长度为20, not null:不为空值
  age:年龄列, tinyint:很小的数字类型,2:长度为2, not null:不为空值, default '0':默认为0值
  dept:系别列,varchar:可变长字符类型,16:长度为16,default null:默认为空

#3.查看建表语句 mysql
> show create table student\G; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.07 sec)

#数据类型 

#mysql中,有三种主要的类型:文本、数字和日期/时间类型

#数字类型

类型           大小        用途
TINYINT       1 字节     小整数值
SMALLINT      2 字节     大整数值
MEDIUMINT     3 字节     大整数值
INT或INTEGER  4 字节     大整数值
BIGINT        8 字节     极大整数值
FLOAT         4 字节     单精度
浮点数值
DOUBLE        8 字节  
View Code

#日期/时间类型

类型        大小(字节)        格式                    用途
DATE        3              YYYY-MM-DD              日期值
TIME        3              HH:MM:SS               时间值或持续时间
YEAR        1              YYYY                    年份值
DATETIME    8              YYYY-MM-DD HH:MM:SS    混合日期和时间值
TIMESTAMP   4              YYYYMMDD HHMMSS       混合日期和时间值,时间戳
View Code

#字符串类型

类型          大小                     用途
CHAR         0-255字节                定长字符串
VARCHAR      0-65535 字节             变长字符串
TINYBLOB     0-255字节                不超过 255 个字符的二进制字符串
TINYTEXT     0-255字节                短文本字符串
BLOB         0-65 535字节             二进制形式的长文本数据
TEXT         0-65 535字节             长文本数据
MEDIUMBLOB   0-16 777 215字节         二进制形式的中等长度文本数据
MEDIUMTEXT   0-16 777 215字节         中等长度文本数据
LONGBLOB     0-4 294 967 295字节      二进制形式的极大文本数据
LONGTEXT     0-4 294 967 295字节      极大文本数据
View Code

 

#创建表的时候指定存储引擎

mysql> create table test (
    -> id int(4) not null auto_increment,
    -> name char(20) not null,
    -> primary key(id)
    -> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)

 

2.查看建表结构及建表语句

#查看表结构命令语法:desc 表名  或者 show columns from 表名

#查看帮助:help desc

mysql> help desc;
Name: 'DESC'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query).

URL: https://dev.mysql.com/doc/refman/5.7/en/explain.html
View Code

 

#例子:

mysql> use db;
Database changed
mysql
> desc student; #查看表结构 +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> show columns from student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)

 

#查看建表语句

mysql> show create table student\G  #G以垂直方式显示
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

 

3.删除表

#语法格式:drop table <表名>

#例子:删除db库里面的student表

mysql> use db;
Database changed

mysql> show tables; #查看库中的表
+--------------+
| Tables_in_db |
+--------------+
| student      |
| test         |
+--------------+
2 rows in set (0.00 sec)

mysql> drop table student;  #删除student表
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;  #再次查看
+--------------+
| Tables_in_db |
+--------------+
| test         |
+--------------+
1 row in set (0.00 sec)

 

4.使用alter增删表的字段

#语法格式:alter table 表名 add 字段 类型 其他:

 #查看帮助:help alter table

 

#例子:在test表中添加字段sex,age,qq,类型分别为char(4),int(4),varchar(15)

#1.添加性别列, 默认语句,会默认添加到最后

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> alter table test  add sex char(4); #添加
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

#2.指定位置添加:指定添加年龄列到name后面的位置

mysql> alter table  test add age int(4) after name; #提示:after是后的意思,在name字段添加age
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| age   | int(4)   | YES  |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

#3.指定添加到第一列

mysql> alter table test add qq varchar(15) first; #first:第一
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| qq    | varchar(15) | YES  |     | NULL    |                |
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | int(4)      | YES  |     | NULL    |                |
| sex   | char(4)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

 

#更改字段类型

#例子:将qq字段的varchar(15)改成char(20)

mysql> alter table test modify column qq char(20); #更改
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| qq    | char(20) | YES  |     | NULL    |                |
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| age   | int(4)   | YES  |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

 

#更改字段名称

将qq字段名改为guoke

mysql> alter table test change qq guoke char(20)  first;  #first指定字段位置
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| guoke | char(20) | YES  |     | NULL    |                |
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| age   | int(4)   | YES  |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

 

#删除字段

#例子:将guoke字段删除

mysql> alter table test drop column guoke;  #删除guoke字段
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| age   | int(4)   | YES  |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

 

5.使用rename或alter更改表名

#使用rename更改表名

 #rename语法格式:rename table 原表名 to 新表名

#查看帮助:help rename

#例子:将test表改为student

mysql> show tables;  #查看原表名
+--------------+
| Tables_in_db |
+--------------+
| test         |
+--------------+
1 row in set (0.00 sec)

mysql> rename table test to student;  #更改
Query OK, 0 rows affected (0.01 sec)

mysql> show tables; #更改后再次查看
+--------------+
| Tables_in_db |
+--------------+
| student      |
+--------------+
1 row in set (0.00 sec)

 

#使用alter更改表名

#语法:alter table 原表名 rename to 新表名

#例子:将student表改为guoke

mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| student      |
+--------------+
1 row in set (0.00 sec)

mysql> alter table student rename to guoke; #更改表名
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| guoke        |
+--------------+
1 row in set (0.00 sec)

 

posted @ 2020-03-02 13:25  老油条IT记  阅读(212)  评论(0编辑  收藏  举报
levels of contents