5

3：表的基本操作-MySQL

 2 years ago
source link: https://blog.51cto.com/yeatsliao/5936320
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

3.1 提出问题，引入“表“的概念与思维模式 table

表的概念：

数据库类似于厂库，而表呢就是对数据进行抽象分类的货架

注意：在创建数据库的时候一定要记得设置字符编码

3.2 引用数据库和查看数据库中的表

1. 引用数据库

mysql> use student;
Database changed

2. 查看数据库中的表

mysql> show tables;
Empty set (0.00 sec)

3.3 创建表

mysql> create table student(
    -> id int,
    -> name varchar(30),
    -> age int,
    -> salary int
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql> show tables
    -> ;
+-----------------------+
| Tables_in_student |
+-----------------------+
| empolyee              |
+-----------------------+

注意：在MySQL中字符串的语句是不使用String的，而是使用varchar，需要表明长度，同时一级一级的写语句能更加整洁明了

3.4 创建表（企业用，有B格）

mysql> create table if not exists teacher(
    -> id int auto_increment primary key comment'主键id',
    -> name varchar(30) not null comment '老师的名字',
    -> phone varchar(20) comment '电话号码',
    -> address varchar(100) default '暂时未知' comment '住址'
    -> )engine=innodb;
Query OK, 0 rows affected (0.03 sec)

注意：在创建表的时候，在default单引号中间的内容要和所定义的数据类型一样，最后一个属性写完不要用“,”

关键词解释

  • auto_increment: 对字段进行自动增长
  • primary key: 主键，是关系型数据库连接桥梁，必须填写不能空着
  • comment: 注释
  • not null: 不能为空，必须填写
  • default: 如果为空那么表中这个数据默认为’ '中的内容，默认值
  • engine = innodb: 表示数据库引擎

两种方式创建的有什么区别呢？
简单的

mysql> create table empolyee(
    -> id int,
    -> name varchar(30),
    -> age int,
    -> salary int
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show create table empolyee;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| empolyee | CREATE TABLE `empolyee` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> create table staff(
    -> id int auto_increment primary key comment'主键id',
    -> name varchar(30) not null,
    -> age int comment'年龄',
    -> salary int default '0' comment'薪水'
    ->  )engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table staff;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| staff | CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(30) NOT NULL,
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `salary` int(11) DEFAULT '0' COMMENT '薪水',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

即使没有写default，但还是会默认default

3.5 查看表结构

1. 显示出表的sql语句

show create table teacher;

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

mysql> show create table teacher;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table



                                              |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(30) NOT NULL COMMENT '老师的名字',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话号码',
  `address` varchar(100) DEFAULT '暂时未知' COMMENT '住址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

2. 查看表的结构

 desc teacher;

mysql> desc teacher;
+---------+--------------+------+-----+----------+----------------+
| Field   | Type         | Null | Key | Default  | Extra          |
+---------+--------------+------+-----+----------+----------------+
| id      | int(11)      | NO   | PRI | NULL     | auto_increment |
| name    | varchar(30)  | NO   |     | NULL     |                |
| phone   | varchar(20)  | YES  |     | NULL     |                |
| address | varchar(100) | YES  |     | 暂时未知 |                |
+---------+--------------+------+-----+----------+----------------+
4 rows in set (0.01 sec)
  • Null ：代表的是否能为空
  • Key PRI ：代表是唯一值
  • Default ：说明是否设立default
  • Extra ：代表的是规则，上述文件是自动增加

3.6 删除表

1. 删除单个表

drop table if exists staff ;

2. 删除多个表

drop table if exists staff,empolyee;

3.7 修改表

1. 添加一个新的字段

alter table 表名 add 字段名 字段类型

mysql> alter table student add name varchar(30);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2. 指定位置添加一个新的字段

在某字段之后

alter table 表名 add 字段名 字段类型 after 字段名

mysql> alter table student add age int after id;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

在第一行添加

alter table 表名 add 字段 字段类型 first

mysql> alter table student add phone int(20) first;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| phone | int(20)     | YES  |     | NULL    |       |
| id    | int(4)      | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3. 删除字段

alter table 表名 drop 字段

mysql> alter table student drop phone;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4. 修改字段（名、类型)

修改名和类型（完全修改）

alter table 表名 change 字段名 新的字段名 字段类型

mysql> alter table student change age phone varchar(20);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

alter table 表名 modify 字段名 要改成的字段类型

mysql> alter table student modify phone int(20);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| phone | int(20)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5. 修改表名

alter table 表名 rename to 新表名

mysql> alter table student rename to empolyee;
Query OK, 0 rows affected (0.02 sec)

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

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK