初学MySQL必备SQL语句-操作数据表

在上一篇文章里,主要记录了数据库的一些操作,主要包括创建、查询、修改、删除等操作。
这一篇的内容则是围绕着数据库中的表进行展开,嘿嘿,其实这些内容是应该放在一篇来写的,但是。。。我懒啊。又不愿意写一半留着下次续写,所以只能分着写啦。见谅!

1.数据表特性

在MySQL中,数据表是以二维表格形式展示,一行代表一条完整数据,一列代表一种特定属性。

2.创建数据表

2.1.创建空数据表

  • 语法
CREATE TABLE [IF NOT EXISTS] 表名(
字段1,数据类型,[约束条件][默认值],
字段2,数据类型,[约束条件][默认值])
  • 创建一个包含序号、姓名和年龄的数据表
mysql> use test_new;
mysql> create table test_01 (
    -> id int,
    -> name varchar(20),
    -> age varchar(30)
    -> );
Query OK, 0 rows affected (0.07 sec)
注意:
在MySQL 8.0中,不再推荐为INT类型指定显示长度
如需要表名称不区分大小写,需要在my.cnf中添加以下配置:
lower_case_table_names=1
  • 查看数据表,可以看到刚创建的数据表
mysql> show tables;
+--------------------+
| Tables_in_test_new |
+--------------------+
| test_01            |
+--------------------+
1 row in set (0.01 sec)

2.2.创建数据表时指定主键

主键又称作主码,在表中包含一列或多列,能够唯一标识表中的一行记录。
主键列的数据必须唯一,且不为空。
主键可分为单列主键和多列联合主键。
  • 单列主键

      单列主键只包含数据表中一个字段
    
语法:
字段 数据类型 PRIMARY KEY [默认值]
  • 创建test_02数据表,将id字段作为主键
#定义列的同时指定主键
mysql> create table test_02 (
    -> id int primary key,
    -> name varchar(20),
    -> age varchar(20)
    -> );
Query OK, 0 rows affected (0.05 sec)
#也可以定义完所有列后指定主键
mysql> create table test_03 (
    -> id int,
    -> name varchar(20),
    -> age varchar(20),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.05 sec)
  • 多列联合主键

      多列联合主键只能在定义完表所有列后指定
    
语法:
PRIMARY KEY [字段1],[字段2],...[字段n]
用法与定义完所有列后指定主键相似,就不举例了。

2.3.创建数据表时指定外键

外键可以关联数据库的两张表
一张表的外键可以为空,也可以不为空,当外键不为空时,则外键的值必须对应另一张表主键某个值
一张表的外键可以不是本表主键,但是一定对应另一表主键
一张表定义了外键后不允许删除另一表具有关联关系的数据行
由外键引出两个概念,主表和从表
两表具有关联关系时,关联字段中主键所在的表为主表,外键所在的表为从表
  • 创建外键的语法格式
[CONSTRAINT 外键名] FOREIGN KEY 字段1 REFERENCES 主表名 主键列1
#以上内容含义
外键名:定义外键时数据表指定的外键名称。同一表中,外键名唯一
FOREIGN KEY:外键包含哪些字段
REFERENCES:指定关联的主表名
将其加到创建的表字段后

2.4.创建数据表时指定字段非空

  • 语法格式:
字段名称 数据类型 NOT NULL
  • 创建一个例子可以直观体现出来
mysql> create table test_04 (
    -> id int primary key not null,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

2.5.创建表时指定默认值

  • 语法格式
字段名称 数据类型 DEFAULT 默认值
mysql> create table test_05 (
    -> id int default 1,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)
#当向表中插入数据时,没有指定id的值,则id字段的默认值为1

2.6. 创建数据表时指定主键默认递增

MySQL支持将整数类型主键设置为默认递增类型
这样向表中插入数据时,不用指定整数类型主键的值
MySQL会将该表整数类型的主键值自动加 1
  • 语法格式
字段名称 数据类型 AUTO_INCREMENT
mysql> create table test_06 (
    -> id int primary key auto_increment,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.03 sec)
此时表中主键id为自动递增类型。在向表中插入数据时,
主键id默认值从1开始每加入一条新数据,id字段的值会自动加1.

2.7.创建数据表时指定存储引擎

  • 语法格式
ENGINE=存储引擎名称
创建表时在语句后加入'ENGINE=存储引擎‘即可为当前表指定存储引擎
mysql> create table test_07 ( id int primary key auto_increment, name varchar(20) not null )engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

2.8.创建数据库时指定编码

  • 语法格式
DEFAULT CHARACTER SET 编码 COLLATE 校对规则
或
DEFAULT CHARSET=编码 COLLATE=校对规则
  • 和2.7一样,加在表后就可以了,就不举例了

3.查看数据表结构

3.1.使用DESCRIBE/DESC查看表结构

使用此语句查看表结信息,包含字段名称、数据类型及主键等信息
  • 语法格式
DESCRIBE/DESC 表名称
mysql> desc test_06;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
以上信息说明:
Field:表中的字段
Type:字段中的数据类型
Null:表中当前字段能否为空
key:表中字段是否存在索引。PRI表示主键列,UNI表示当前列是UNIQUE标识的唯一索引列,或是其中一部分;MUL表示在当前列中某个值可以出现几次
Default:当前列是否由默认值,同时显示默认值是多少
Extra:表示与当前列相关的附件信息

3.2.使用SHOW CREATE TABLE语句查看表结构

通过这种方式,不仅可以查看创建表的SQL语句,
还可以看存储引擎和字符集等信息
  • 语法格式:
SHOW CREATE TABLE 表名 \G
mysql> show create table test_06 \G
*************************** 1. row ***************************
       Table: test_06
Create Table: CREATE TABLE `test_06` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

4.修改数据表

4.1.修改数据表名称

  • 语法格式:
ALTER TABLE 原表名 RENAME [TO] 新表名
#TO可以省略
mysql> alter table test_06 rename to test_60;
Query OK, 0 rows affected (0.03 sec)

4.2.为数据表添加新字段

  • 语法格式:
ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [NOT NULL DEFAULT 默认值]
  • 例,为表test_60添加新字段
mysql> alter table test_60 add column age varchar(20);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 添加字段时指定位置,在表的第一列添加字段
ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [NOT NULL DEFAULT 默认值] FIRST
#FIRST关键字表示要添加的字段位于当前表的第一列
  • 在指定字段后添加新字段
ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [NOT NULL DEFAULT 默认值] ALTER 原有字段名

4.3.修改字段名称

  • 语法格式
ALTER TABLE 表名 CHANGE 原字段名 新字段名 新数据类型
在修改字段名时也可以不修改数据类型
可以将新字段的数据类型设置成与原由字段数据类型一样
但是新字段名称的数据类型不能为空。
新字段名称后加上DEFAULT即为保持数据类型不变

4.4.修改字段的数据类型

  • 语法格式
ALTER TABLE 表名 MODIFY 字段名 新数据类型 [DEFAULT 默认值]
使用MODIFY关键字指定字段的新数据类型
mysql> alter table test_60 modify age tinyint(2);
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 1
咳咳,举例暂时忽略警告

4.5.修改字段位置

  • 将字段的位置修改为数据表的第一个字段
ALTER TABLE 表名 MIDIFY 字段名 数据类型 FIRST
使用FIRST关键字将当前字段修改为数据表的第一个字段
  • 将选中字段修改到某字段之后
ALTER TABLE 表名 MODIFY 字段1名称 字段1数据类型 AFTER 字段2名称

4.6.删除字段

  • 删除表中某个字段的语法:
ALTER TABLE 表名 DROP 字段名
mysql> desc test_60;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| age   | tinyint     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> alter table test_60 drop age;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.7.修改已有表的存储引擎

  • 语法格式:
ALTER TABLE 表名 ENGINE=存储引擎名称
mysql> alter table test_05 engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.8.取消数据表的外键约束

  • 语法格式:
ALTER TABLE 表名 DROP FOREIGN KEY 外键名

5.删除数据表

5.1.删除没有关联关系的数据表

  • 语法格式:
DROP TABLE [IF EXISTS] 数据表
mysql> drop table test_07;
Query OK, 0 rows affected (0.03 sec)

5.2.删除有外键约束的主表

  • 可以先删除有外键约束从表,在删除主表
  • 先解除外键约束,再删除主表

6.临时表

6.1.创建临时表

  • 语法格式:
CREATE TEMPORARY TABLE [IF NOT EXISTS] 表名
注:临时表通过DESC或者SHOW CREATE TABLE语句来查看

6.2.删除临时表

  • 语法格式
DROP TABLE [IF EXISTS] 表名
#与正常表删除方式一样

本文写了数据表的一些操作,当然,有很多地方只给出了语法,没有示例,因为数据表大的主体时相同的,不同的只是使用关键词上,所有只给出了语法,将语法代入即可。

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页