MySQL基本语句

目录

• 一、约束条件相关语句
  • 1.1 主键约束
    • ① 创建列时便指定主键
    • ② 所有列创建完成后指定主键
  • 1.2 非空约束
  • 1.3 设置值的唯一性
  • 1.4 设置列的默认值
  • 1.5 设置自增值
  • 1.6 设置自增的起始值
• 二、ALTER指令的使用
  • 2.1 修改值的数据长度
  • 2.2 修改字段名称
  • 2.3 向表中插入一个新的字段
    • ① 在末尾插入新列
    • ② 在开头插入
    • ③ 在指定列后插入新列
    • ④ 添加字段时添加约束
    • ⑤ 添加一个外键
    • ⑥ 删除外键
    • ⑦ 删除列
    • ⑧ 修改列的顺序
• 三、删除表
  • 3.1 使用delete删除
  • 3.2 伪删除(修改表结构)
  • 3.3 使用drop删除
  • 3.4 delete，drop，truncate区别？

一、约束条件相关语句

1.1 主键约束

简单来说就是要求主键列的数据唯一，并且不允许为空！

① 创建列时便指定主键

create database t1;
#创建数据库，名称为t1
use t1;
#切换到t1数据库
create table t1(
id int(10),
name varchar(10) primary key,
sex varchar(5),
info varchar(200)
);
#创建一个表，并指定name字段为主键
desc t1;
# 查看数据的详细信息
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(10)  | NO   | PRI | NULL    |       |
| sex   | varchar(5)   | YES  |     | NULL    |       |
| info  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
#确认name字段有PRI字样

② 所有列创建完成后指定主键

create table t2(
id int(10),
name varchar(10),
sex varchar(5),
info varchar(200),
primary key(id)
);
desc t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | NO   | PRI | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
| sex   | varchar(5)   | YES  |     | NULL    |       |
| info  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
#确认id字段有PRI字样

1.2 非空约束

简单来说就是不允许列的值为空！

create table t3(
id int(6) not null,
name varchar(10)
);
#创建表，指定id字段不允许为空
desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(6)      | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
#确认id的一行上NULL字段为NO

1.3 设置值的唯一性

简单来说就是不允许有重复数据，可以为空，单只能有一个空，否则就会被视为重复！

create table t4(
id int not null unique,
name varchar(20)
);
#创建一个表，指定id列不能为空，且不能重复
desc t4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
#可以看出其被标识为主键，但是在创建时并没有指定它是主键，而是这一列的属性基本满足了主键的要求，如唯一、不可为空！

1.4 设置列的默认值

顾名思义就是如果没有给列赋予值，则直接使用默认值！

create table t5(
id int not null,
name varchar(20),
project varchar(20) default 'mysql'
);
#创建一个表，指定project列的默认值为mysql
desc t5;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| project | varchar(20) | YES  |     | mysql   |       |
+---------+-------------+------+-----+---------+-------+
#可以看出project列的Default字段为msyql

1.5 设置自增值

一般用于用于id列，自增列必须设置为主键！

注：mysql只允许设置初始值，而不允许设置自增值，也就是说，可以设置为第一个值为5，然后依次递增，如：5、6、7.....但不可以设置其一次递增2个数，比如：5、7、9......

create table t6(
id int not null primary key auto_increment,
name varchar(20)
);
#创建表，指定id列为自增值
desc t6;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
#查看表的详细信息，确认id列的Extra字段为auto_increment
insert into t6(name)
values('zhangsan'),('lisi');
#插入数据进行测试
select * from t6;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
#查看表中存储的数据

从上面的测试可以看出，只插入了两个name的值，并没有插入id的值，但是查看表数据时，id已经有值了，说明自增生效。

1.6 设置自增的起始值

create table t7(
id int primary key auto_increment,
name varchar(20)
) auto_increment = 5;
#新建表，指定id列为自增值，并且初始值为5
insert into t7(name)
value('xiaozhang'),('xiaolisi');
#插入数据
select * from t7;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | xiaozhang |
|  6 | xiaolisi  |
+----+-----------+
#验证

二、ALTER指令的使用

2.1 修改值的数据长度

desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(10)  | NO   | PRI | NULL    |       |
| sex   | varchar(5)   | YES  |     | NULL    |       |
| info  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
#查看t1表的详细信息，注意name字段的字符长度为10
alter table t1 modify name varchar(20);
#更改name字段的字符长度为20
desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(20)  | NO   | PRI | NULL    |       |
| sex   | varchar(5)   | YES  |     | NULL    |       |
| info  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
#验证

2.2 修改字段名称

在修改字段名称的同时，还可以修改其新字段名的数据类型及数据长度！

desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(20)  | NO   | PRI | NULL    |       |
| sex   | varchar(5)   | YES  |     | NULL    |       |
| info  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
#查看t1表的各个字段(注意info字段)
alter table t1 change info infoinfo char(20);
#更改info字段名称及数据类型
desc t1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | YES  |     | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| sex      | varchar(5)  | YES  |     | NULL    |       |
| infoinfo | char(20)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
#验证

2.3 向表中插入一个新的字段

① 在末尾插入新列

desc t1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | YES  |     | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| sex      | varchar(5)  | YES  |     | NULL    |       |
| infoinfo | char(20)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
alter table t1 add tel int(13);
#添加一列，名称为tel，并指定数据类型
desc t1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | YES  |     | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| sex      | varchar(5)  | YES  |     | NULL    |       |
| infoinfo | char(20)    | YES  |     | NULL    |       |
| tel      | int(13)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
#验证

② 在开头插入

alter table t3 add sex char(1) first;

③ 在指定列后插入新列

alter table t3 add loc varchar(255) after name;

④ 添加字段时添加约束

alter table t3 add hobyy varchar(255) default 'work';

⑤ 添加一个外键

进行此操作之前，需要自行找一个有主键的表（这里使用t1表为例）。

desc t1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | YES  |     | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| sex      | varchar(5)  | YES  |     | NULL    |       |
| infoinfo | char(20)    | YES  |     | NULL    |       |
| tel      | int(13)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
desc t3;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| sex   | char(1)      | YES  |     | NULL    |       |
| id    | int(6)       | NO   |     | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
| loc   | varchar(255) | YES  |     | NULL    |       |
| tel   | int(13)      | YES  |     | NULL    |       |
| hobyy | varchar(255) | YES  |     | work    |       |
+-------+--------------+------+-----+---------+-------+
#查看t1和t3的表结构
alter table t3 add constraint t3_t1_name foreign key(name) references t1(name);
#现在将t3表的name列添加为t1表的name列的外键（其中t3_t1_name为自定义的约束名称）
desc t3;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| sex   | char(1)      | YES  |     | NULL    |       |
| id    | int(6)       | NO   |     | NULL    |       |
| name  | varchar(10)  | YES  | MUL | NULL    |       |
| loc   | varchar(255) | YES  |     | NULL    |       |
| tel   | int(13)      | YES  |     | NULL    |       |
| hobyy | varchar(255) | YES  |     | work    |       |
+-------+--------------+------+-----+---------+-------+
#注意name字段的Key为MUL

⑥ 删除外键

将刚才添加的外键删除，t3_t1_name是外键的名称。

alter table t3 drop foreign key t3_t1_name;
alter table t3 drop  key t3_t1_name;
desc t3;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| sex   | char(1)      | YES  |     | NULL    |       |
| id    | int(6)       | NO   |     | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
| loc   | varchar(255) | YES  |     | NULL    |       |
| tel   | int(13)      | YES  |     | NULL    |       |
| hobyy | varchar(255) | YES  |     | work    |       |
+-------+--------------+------+-----+---------+-------+
#验证

⑦ 删除列

alter table t3 drop hobyy;

注意：如果要删除的列和其他表中的列有关联关系，则需要先删除关系，再删除列。否则当以后再创建了相同名称的列时，会自动将其建立关系。

⑧ 修改列的顺序

alter table t3 modify name varchar(10) first;
#将name字段移动到列表开头

三、删除表

3.1 使用delete删除

删除指定数据行的值（必须要明确要更改哪一行，一般delete语句都有where的条件）

delete from student where id=7;

3.2 伪删除(修改表结构)

alter table student add column state tinyint not null default 1;
#添加state状态列
update student set state=0 where id=1;
select * from student where state=1;
#指定条件进行查看

3.3 使用drop删除

drop table t5;            
#直接删除
drop table t5;            
ERROR 1051 (42S02): Unknown table 'test01.t5'
#再次删除，由于已经删除了，所以表不存在，会报错
drop table if exists t5;  
#进行判断后删除，if exists表示如果存在就删除  
Query OK, 0 rows affected, 1 warning (0.00 sec)
#可以看到上述返回的信息有1个warning事项，可以执行以下命令进行查看
show warnings;    
+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Note  | 1051 | Unknown table 'test01.t5' |
+-------+------+---------------------------+
#记录的信息时不知道test01库中的t5表

3.4 delete，drop，truncate区别？

• delete
  逻辑上，逐行删除，数据行过多，耗时最长。并没有真正从磁盘上删除，只是在存储层打标机，磁盘空间不立即删除。不会覆盖原本的自增值；
• drop
  将表结构(元数据)和数据行物理层次删除；
• truncate
  清空表、段中的所有数据页，物理层次将表中的数据进行删除，磁盘空间立即删除。覆盖原本的自增值；

注意：同样，当执行删除表操作时，如果存在关联关系，则需要先删除关联关系，再删除表。