增删改和约束
创建表
语法格式
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约束。