MySQL基本语句
目录
一、约束条件相关语句
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
清空表、段中的所有数据页，物理层次将表中的数据进行删除，磁盘空间立即删除。覆盖原本的自增值；
注意：同样，当执行删除表操作时，如果存在关联关系，则需要先删除关联关系，再删除表。
*************** 当你发现自己的才华撑不起野心时，就请安静下来学习吧！***************
