-- 默认值约束(缺省)对默认值字段插入null是可以的 对默认值字段可以插入非null
CREATE table suser(
uid int,
uname varchar(10),
address varchar(30) default'山东淄博'
);
INSERT INTO suser values(1,'海绵宝宝','大海');
INSERT INTO suser (uid,uname) values(2,'潘海星');
INSERT INTO suser values(3,'章鱼哥',null);
SELECT *from suser;
-- 非空约束 1)非空字符必须赋值 2)非空字符不能赋null
DROP TABLE suser;
CREATE table suser(
uid int,
uname varchar(10),
idcard varchar(20) not null,
address varchar(30) default'山东淄博'
);
INSERT into suser values(1,'派大星','3703030303xxxxxxx','大海');
INSERT INTO suser (uid,uname) values(2,'潘海星');
INSERT INTO suser (uid,uname,idcard) values(2,'潘海星',NULL);
INSERT INTO suser (uid,uname,idcard) values(2,'潘海星','');
SELECT * from suser;
CREATE table suser(
uid int,
uname varchar(10),
address varchar(30) default'山东淄博'
);
INSERT INTO suser values(1,'海绵宝宝','大海');
INSERT INTO suser (uid,uname) values(2,'潘海星');
INSERT INTO suser values(3,'章鱼哥',null);
SELECT *from suser;
-- 非空约束 1)非空字符必须赋值 2)非空字符不能赋null
DROP TABLE suser;
CREATE table suser(
uid int,
uname varchar(10),
idcard varchar(20) not null,
address varchar(30) default'山东淄博'
);
INSERT into suser values(1,'派大星','3703030303xxxxxxx','大海');
INSERT INTO suser (uid,uname) values(2,'潘海星');
INSERT INTO suser (uid,uname,idcard) values(2,'潘海星',NULL);
INSERT INTO suser (uid,uname,idcard) values(2,'潘海星','');
SELECT * from suser;
-- 唯一约束 对字段的值不能重复 注意:1)唯一字段可以插入null 2)唯一字段可以插入多个null 但不能插入多个''
DROP TABLE suser;
CREATE table suser(
uid int,
uname varchar(10) UNIQUE,
idcard varchar(20) not null,
address varchar(30) default'山东淄博'
);
INSERT into suser values(1,'派大星','3703030303xxxxxxx','章鱼包');
SELECT * from suser;
INSERT into suser values(2,NULL,'3703030303xxxxxx','章鱼');
INSERT into suser values(3,NULL,'3703030303xxxxxx','章鱼');
INSERT into suser values(4,NULL,'3703030303xxxxxx','章鱼');
INSERT into suser values(5,'','3703030303xxxxxx','章鱼');
INSERT into suser values(6,' ','3703030303xxxxxx','章鱼');/*加不上*/
DROP TABLE suser;
CREATE table suser(
uid int,
uname varchar(10) UNIQUE,
idcard varchar(20) not null,
address varchar(30) default'山东淄博'
);
INSERT into suser values(1,'派大星','3703030303xxxxxxx','章鱼包');
SELECT * from suser;
INSERT into suser values(2,NULL,'3703030303xxxxxx','章鱼');
INSERT into suser values(3,NULL,'3703030303xxxxxx','章鱼');
INSERT into suser values(4,NULL,'3703030303xxxxxx','章鱼');
INSERT into suser values(5,'','3703030303xxxxxx','章鱼');
INSERT into suser values(6,' ','3703030303xxxxxx','章鱼');/*加不上*/
-- 主键 非空+唯一
/*注意:
1)通常情况下,每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。
2)建议不要选择表的包含业务含义的字段作为主键,建议给每张表独立设计一个非业务含义的id字段。
*/
DROP TABLE suser;
CREATE table suser(
uid int PRIMARY key,
uname varchar(10) UNIQUE,
idcard varchar(20) not null,
address varchar(30) default'山东淄博'
);
insert into suser values(1,'钢铁侠','111110000','美国');
-- 1364 - Field 'uid' doesn't have a default value
insert into suser(uname,idcard) values('jingqi','111110000');
-- 1048 - Column 'uid' cannot be null
insert into suser values(null,'钢','11110000','美国');
-- 1062 - Duplicate entry '1' for key 'PRIMARY'
insert into suser values(1,'钢','11110000','美国');
-- 自增长 作用: 自动递增 必须设定为主键 (自增时 错误也自增1) 自增长,从0开始 ZEROFILL 零填充需要设定int 的值
/*注意:
1)通常情况下,每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。
2)建议不要选择表的包含业务含义的字段作为主键,建议给每张表独立设计一个非业务含义的id字段。
*/
DROP TABLE suser;
CREATE table suser(
uid int PRIMARY key,
uname varchar(10) UNIQUE,
idcard varchar(20) not null,
address varchar(30) default'山东淄博'
);
insert into suser values(1,'钢铁侠','111110000','美国');
-- 1364 - Field 'uid' doesn't have a default value
insert into suser(uname,idcard) values('jingqi','111110000');
-- 1048 - Column 'uid' cannot be null
insert into suser values(null,'钢','11110000','美国');
-- 1062 - Duplicate entry '1' for key 'PRIMARY'
insert into suser values(1,'钢','11110000','美国');
-- 自增长 作用: 自动递增 必须设定为主键 (自增时 错误也自增1) 自增长,从0开始 ZEROFILL 零填充需要设定int 的值
DROP TABLE suser;
CREATE table suser(
uid int PRIMARY key auto_increment,
uname varchar(10) UNIQUE,
idcard varchar(20) not null,
address varchar(30) default'山东淄博'
);
insert into suser(uname,idcard) values('美国队长','111110000');
insert into suser(uname,idcard) values('绿巨人','111110000');
DELETE from suser where uid=2;
select * from suser;
CREATE table suser(
uid int PRIMARY key auto_increment,
uname varchar(10) UNIQUE,
idcard varchar(20) not null,
address varchar(30) default'山东淄博'
);
insert into suser(uname,idcard) values('美国队长','111110000');
insert into suser(uname,idcard) values('绿巨人','111110000');
DELETE from suser where uid=2;
select * from suser;
-- 全表删除 删除的是约束产生效果
delete from suser;/*不能删除约束产生效果*/
TRUNCATE table suser;/*删除的是约束产生效果*/
-- 自增长中的零填充 int(5) ZEROFILL必须放在一起
DROP TABLE suser;
CREATE table suser(
uid int(5) ZEROFILL PRIMARY key auto_increment,
uname varchar(10) UNIQUE,
idcard varchar(20) not null,
address varchar(30) default'山东淄博'
);
-- 外键 作用:约束两种表的数据,一张表的主键拿到另一个表中当外键
/*有外键得表叫副标 */
create table dept(
did int primary key auto_increment,
dname varchar(10),
mgr varchar(10),
phone varchar(12)
);
create table emp02(
eid int primary key auto_increment,
ename varchar(10),
sex varchar(10),
did int,
constraint fk_emp02_dept foreign key(did) references dept(did)
)
/*添加数据: 先填主表 再添副标*/
INSERT INTO dept(dname,mgr,phone)values ('开发部','钢铁侠','1333');
INSERT INTO dept(dname,mgr,phone)values ('测试部','美国队长','1833');
INSERT INTO emp02(ename,sex,did)values('小兰','女',1);
INSERT INTO emp02(ename,sex,did)values('小红','女',1);
INSERT INTO emp02(ename,sex,did)values('小绿','男',2);
-- 修改表 先修改副表,再修改主表
-- 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mydb2`.`emp02`, CONSTRAINT `fk_emp02_dept` FOREIGN KEY (`did`) REFERENCES `dept` (`did`))
UPDATE dept set did=3 where did=2;
UPDATE emp02 set did=1 where eid=4;
SELECT *from dept;
SELECT *from emp02;
-- 删除数据:先删除 副表 再删主表
-- 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mydb2`.`emp02`, CONSTRAINT `fk_emp02_dept` FOREIGN KEY (`did`) REFERENCES `dept` (`did`))
DELETE FROM dept where did=1;
DELETE FROM emp02 where did=1;
/*有外键得表叫副标 */
create table dept(
did int primary key auto_increment,
dname varchar(10),
mgr varchar(10),
phone varchar(12)
);
create table emp02(
eid int primary key auto_increment,
ename varchar(10),
sex varchar(10),
did int,
constraint fk_emp02_dept foreign key(did) references dept(did)
)
/*添加数据: 先填主表 再添副标*/
INSERT INTO dept(dname,mgr,phone)values ('开发部','钢铁侠','1333');
INSERT INTO dept(dname,mgr,phone)values ('测试部','美国队长','1833');
INSERT INTO emp02(ename,sex,did)values('小兰','女',1);
INSERT INTO emp02(ename,sex,did)values('小红','女',1);
INSERT INTO emp02(ename,sex,did)values('小绿','男',2);
-- 修改表 先修改副表,再修改主表
-- 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mydb2`.`emp02`, CONSTRAINT `fk_emp02_dept` FOREIGN KEY (`did`) REFERENCES `dept` (`did`))
UPDATE dept set did=3 where did=2;
UPDATE emp02 set did=1 where eid=4;
SELECT *from dept;
SELECT *from emp02;
-- 删除数据:先删除 副表 再删主表
-- 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mydb2`.`emp02`, CONSTRAINT `fk_emp02_dept` FOREIGN KEY (`did`) REFERENCES `dept` (`did`))
DELETE FROM dept where did=1;
DELETE FROM emp02 where did=1;