MySQL04-增删改和约束-（提高篇）

增删改和约束

创建表

语法格式

create table 表名(
    字段名1 数据类型，
    字段名2 数据类型,
    字段名3 数据类型
    ...
);

表名在数据库中一般建议以t_或者tbl_开始

MySQL中的数据类型

数据类型	说明
int	整形
bigint	长整形
float	浮点型
char	定长字符串，最长255
varchar	可变长字符串
data	日期类型
BLOB	二进制大对象（存储图片，视频等流媒体信息），Binary Large OBject
CLOB	字符大对象（存储较大文本，比如，小说，评论等），Character Large OBject

char和varchar怎么选择？

在实际开发中，当某个字段中的数据长度不发生改变时候，是定长的，用char 例如：性别，生日

当一个字段的数据长度不确定，例如：简介,姓名等采用varchar

案例:创建一个学生的表

create table tbl_student(
  no int,
  name varchar(255),
  sex char(1),
  birthday char(10),
  classno char(255)
);

删除表

drop table if exists tbl_student

Insert 插入数据

语法格式

insert into 表名(
  字段名1,
  字段名2,
  字段名3
  ...
)
values
(
  数据1,
  数据2,
  数据3
  ...
);

2.省略字段，但对数据的数量和顺序要和字段严格匹配，否则会报错

insert into 表名 values(数据1，数据2，数据3...);

3.一次性插入多行

insert into 表名 values(数据1，数据2，数据3...),(..),(..);

案例:插入一条学生信息记录

insert into tbl_student(
   no,
   name,
   sex,
   birthday,
   classno
)
values
(
   1,
   'james',
   1,
   '1977-2-2',
   'grade1class3'
);

此时，表会多一条记录

mysql> select * from tbl_student;
+------+-------+------+----------+--------------+
| no   | name  | sex  | birthday | classno      |
+------+-------+------+----------+--------------+
|    1 | james | 1    | 1977-2-2 | grade1class3 |
+------+-------+------+----------+--------------+
1 row in set (0.00 sec)

如果子插入一个字段的信息

insert into tbl_student(name)values('jack');

+------+-------+------+----------+--------------+
| no   | name  | sex  | birthday | classno      |
+------+-------+------+----------+--------------+
|    1 | james | 1    | 1977-2-2 | grade1class3 |
| NULL | jack  | NULL | NULL     | NULL         |
+------+-------+------+----------+--------------+

表格会把其他字段改为NULL,如果想填充NULL的内容只能通过update语句修改，而不能使用insert语句

但如果语句中字段数与数据数不匹配就会报错error

案例插入多行学生记录

insert into tbl_student 
values
(
    4,
   'king',
   1,
   '1978-2-2',
   'grade1class6'
)
,
(
   6,
   'smith',
   1,
   '1970-2-2',
   'grade1class7'

)
,
(
   7,
   'lapich',
   0,
   '1971-2-2',
   'grade1class1'

);

表格变为

+------+--------+------+----------+--------------+
| no   | name   | sex  | birthday | classno      |
+------+--------+------+----------+--------------+
|    1 | james  | 1    | 1977-2-2 | grade1class3 |
| NULL | jack   | NULL | NULL     | NULL         |
|    2 | james  | 1    | 1977-2-2 | grade1class3 |
|    4 | king   | 1    | 1978-2-2 | grade1class6 |
|    6 | smith  | 1    | 1970-2-2 | grade1class7 |
|    7 | lapich | 0    | 1971-2-2 | grade1class1 |
+------+--------+------+----------+--------------+
6 rows in set (0.00 sec)

表的复制

语法格式

create table 表名 as select语句

将查询结果当做表创建出来

案例:复制一张学生的表

create table tbl02_student as select * from tbl_student;

mysql> select * from tbl02_student;
+------+--------+------+----------+--------------+
| no   | name   | sex  | birthday | classno      |
+------+--------+------+----------+--------------+
|    1 | james  | 1    | 1977-2-2 | grade1class3 |
| NULL | jack   | NULL | NULL     | NULL         |
|    2 | james  | 1    | 1977-2-2 | grade1class3 |
|    4 | king   | 1    | 1978-2-2 | grade1class6 |
|    6 | smith  | 1    | 1970-2-2 | grade1class7 |
|    7 | lapich | 0    | 1971-2-2 | grade1class1 |
+------+--------+------+----------+--------------+
6 rows in set (0.00 sec)

将查询结果插入到一张表中

insert into tbl02_student select * from tbl_student;

mysql> select * from tbl02_student;
+------+--------+------+----------+--------------+
| no   | name   | sex  | birthday | classno      |
+------+--------+------+----------+--------------+
|    1 | james  | 1    | 1977-2-2 | grade1class3 |
| NULL | jack   | NULL | NULL     | NULL         |
|    2 | james  | 1    | 1977-2-2 | grade1class3 |
|    4 | king   | 1    | 1978-2-2 | grade1class6 |
|    6 | smith  | 1    | 1970-2-2 | grade1class7 |
|    7 | lapich | 0    | 1971-2-2 | grade1class1 |
|    1 | james  | 1    | 1977-2-2 | grade1class3 |
| NULL | jack   | NULL | NULL     | NULL         |
|    2 | james  | 1    | 1977-2-2 | grade1class3 |
|    4 | king   | 1    | 1978-2-2 | grade1class6 |
|    6 | smith  | 1    | 1970-2-2 | grade1class7 |
|    7 | lapich | 0    | 1971-2-2 | grade1class1 |
+------+--------+------+----------+--------------+

update修改数据

语法格式

update 表名 set 字段名1=值1,字段名2=值2...where 条件;

注意：如果没有条件整张表全部更新。

案例把tbl02_student表中james的birthday改成1999-2-2，classno改成grade4class4

update tbl02_student set birthday='1999-2-2',classno='grade4class4' where name='james';

修改后

+------+--------+------+----------+--------------+
| no   | name   | sex  | birthday | classno      |
+------+--------+------+----------+--------------+
|    1 | james  | 1    | 1999-2-2 | grade4class4 |
| NULL | jack   | NULL | NULL     | NULL         |
|    2 | james  | 1    | 1999-2-2 | grade4class4 |
|    4 | king   | 1    | 1978-2-2 | grade1class6 |
|    6 | smith  | 1    | 1970-2-2 | grade1class7 |
|    7 | lapich | 0    | 1971-2-2 | grade1class1 |
|    1 | james  | 1    | 1999-2-2 | grade4class4 |
| NULL | jack   | NULL | NULL     | NULL         |
|    2 | james  | 1    | 1999-2-2 | grade4class4 |
|    4 | king   | 1    | 1978-2-2 | grade1class6 |
|    6 | smith  | 1    | 1970-2-2 | grade1class7 |
|    7 | lapich | 0    | 1971-2-2 | grade1class1 |
+------+--------+------+----------+--------------+
12 rows in set (0.00 sec)

删除数据

语法格式

delete from 表名 where 条件

注意:没有条件全部删除

案例:把jack全部删除

delete from tbl02_student where name='jack';

删除之后

+------+--------+------+----------+--------------+
| no   | name   | sex  | birthday | classno      |
+------+--------+------+----------+--------------+
|    1 | james  | 1    | 1999-2-2 | grade4class4 |
|    2 | james  | 1    | 1999-2-2 | grade4class4 |
|    4 | king   | 1    | 1978-2-2 | grade1class6 |
|    6 | smith  | 1    | 1970-2-2 | grade1class7 |
|    7 | lapich | 0    | 1971-2-2 | grade1class1 |
|    1 | james  | 1    | 1999-2-2 | grade4class4 |
|    2 | james  | 1    | 1999-2-2 | grade4class4 |
|    4 | king   | 1    | 1978-2-2 | grade1class6 |
|    6 | smith  | 1    | 1970-2-2 | grade1class7 |
|    7 | lapich | 0    | 1971-2-2 | grade1class1 |
+------+--------+------+----------+--------------+
10 rows in set (0.00 sec)

怎么删除大表？（永久删除，表被截断了，不可以通过回滚事务恢复，慎重）

truncate table 表名;

关于表结构的修改

在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行否定，即使需要修改结构，我们也可以通过工具操作，修改表结构的语句不会出现具体的业务代码中

我们经常使用的是CRUD操作：create retrieve update delete

约束

什么是约束？

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性,有效性，完整性、

常见的约束？

• 非空约束（not null）:约束的字段不能为NULL
• 唯一约束(unique):约束的字段不能重复
• 主键约束(primary key):约束的字段既不能为NULL,也不能重复（PK）
• 外键约束(foreign key):…(简称FK)
• 检查约束(check):注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束

非空约束 not null

drop table  if exists t_user;
create table t_user(
   id int,
   uname varchar(255) not null,
   password varchar(16) not null
);

insert into t_user(id,uname) values(2,'nico');

如果没有默认值的话,上面语句会报错，因为uname 不能为空

唯一性约束 unique

唯一性约束修饰的字段具有唯一性，不能重复但可以为NULL;

drop table if exists t_user;
create table t_user(
  id int,
  usercode varchar(255) unique,
  username varchar(255)
)

表示usercode字段不能重复，这个是列级约束

我们同时插入几条数据

insert into t_user values(1,'20001','admin');
insert into t_user values(1,'20002','admin');

+------+----------+----------+
| id   | usercode | username |
+------+----------+----------+
|    1 | 20001    | admin    |
|    1 | 20002    | admin    |
+------+----------+----------+
2 rows in set (0.00 sec)
#如果再插入以下就会报错
insert into t_user values(3,'20001','admin');
---------------------------------------------
ERROR 1062 (23000): Duplicate entry '20001' for key 'usercode'

表级约束，表示联合起来不能重复

drop table if exists t_user;
create table t_user(
  id int,
  usercode varchar(255),
  username varchar(255),
  unique(usercode,username)
)

再次插入几条数据

insert into t_user values(1,'20001','admin');
insert into t_user values(1,'20002','admin');
#以上么有报错
因为20001admin与20002admin他们没有重复

主键约束 primary key

当一个字段添加了主键约束之后，既不能重复也不能为NULL

演示

drop table if exists t_user;
create table t_user(
    id int primary key,
    uname varchar(255),
    password varchar(255)
)

或者

drop table if exists t_user;
create table t_user(
   id int,
   uname varchar(255),
   password varchar(255),
   primary key(id)
);

插入几条数据

insert into t_user values(1,'zhangsan','1234');
insert into t_user values(2,'zhangsan','1234');
#-------------------再提交下面代码会报错
insert into t_user values(1,'zhangsan','1234');
insert into t_user(uname,password) values('li','344');

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

主键的分类

根据主键字段的字段数量来划分

• 单一主键(推荐，常用)
• 复合主键(多个字段联合起来添加一个主键约束，它是不被推荐使用，因为符合主键违背三范式)

根据主键性质来划分

• 自然主键：主键值最好是一个和业务没有任何关系的自然数（这种方式是推荐的）
• 业务主键：主键值和业务挂钩，例如：用银行卡后作为主键，用身份证号作为主键（不推荐使用）

​ 最好不要和业务挂钩，防止业务变化导致主键值重复。

一张表的主键约束只能有一个

主键的自增

系统自动生成主键

drop table if exists t_user;
create table t_user(
   id int primary key auto_increment,
   username varchar(255),
   password varchar(255)
)

insert into t_user(username,password) values('lisi','qwe');
insert into t_user(username,password) values('wsi','qwe');

可以看到系统自动生成了id字段数据，从1开始，以1递增

+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | lisi     | qwe      |
|  2 | wsi      | qwe      |
+----+----------+----------+
2 rows in set (0.00 sec)

外键约束foreign key

基本术语

• 外键约束：foreign key
• 外键字段：添加有外键约束的字段
• 外键值：外键字段中的每一个值

语法结构

foreign key(字段名) references 另一张表的表名(引用的字段)

业务背景：要求设计两张表

第一个张是英雄，另一张是职业

t_class

classno classname
--------------------
101        tank
102        warrior
103        shooter

t_hero

herono  hname   classno
-------------------------
 1       yase     101
 2       pango    102
 3       menya    103
 4        luban    103

要求用写出SQL语句

顺序要求：

• 删除数据的时候，先删除子表，再删除父表
• 添加数据的时候，先添加父表，再添加子表
• 创建表的时候，先创建父表，再创建子表
• 删除数据是，先删除子表，再删除父表

此时，t_hero 中classno字段 引用t_class表中classno字段，此时t_hero叫做子表，t_class叫父表

写法如下

drop table if exists t_hero;
drop table if exists t_class;

create table t_class(
  classno int,
  classname varchar(255),
  primary key(classno)
);

create table t_hero(
  herono int,
  hname varchar(255),
  classno int,
  primary key(herono),
  foreign key(classno) references t_class(classno)
);

insert into t_class values(101,'tank');
insert into t_class values(102,'warrior');
insert into t_class values(103,'shooter');

insert into t_hero values(1,'yase',101);
insert into t_hero values(2,'pangu',102);
insert into t_hero values(3,'menya',103);
insert into t_hero values(4,'luban',103);

select * from t_class;
select * from t_hero;

mysql> select * from t_class;
+---------+-----------+
| classno | classname |
+---------+-----------+
|     101 | tank      |
|     102 | warrior   |
|     103 | shooter   |
+---------+-----------+
3 rows in set (0.00 sec)

mysql> select * from t_hero;
+--------+-------+---------+
| herono | hname | classno |
+--------+-------+---------+
|      1 | yase  |     101 |
|      2 | pangu |     102 |
|      3 | menya |     103 |
|      4 | luban |     103 |
+--------+-------+---------+
4 rows in set (0.00 sec)

如果向t_hero插入

insert into t_hero values(5,'gongsunli',109);

就会报错

mysql> insert into t_hero values(5,'gongsunli',109);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.`t_hero`, CONSTRAINT `t_hero_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`classno`))

因为在t_class的classno中找不到这个值

外键值可以为NULL?

外键可以为NULL;

外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？

注意：被引用的字段不一定是主键，但至少具有unique约束。