数据库之mysql约束条件、表关系、记录操作等相关内容-44
# not null default
create table t1(x int not null);
insert into t1 values(); --失败，不为空但是没有默认值
create table t2(x int not null default 111);
insert into t2 values(); --成功，有默认值111
# unique
# 单列唯一
create table t3(name varchar(10) unique);
insert into t3 values("egon");
insert into t3 values("tom");
mysql> insert into t3 values("egon");
ERROR 1062 (23000): Duplicate entry 'egon' for key 'name'
# 联合唯一
create table server(
id int,
name varchar(10),
ip varchar(15),
port int,
unique(ip,port),
unique(name)
);
insert into server values (1,"web1","10.10.0.11",8080);
insert into server values (2,"web2","10.10.0.11",8081);
mysql> insert into server values(4,"web4","10.10.0.11",8081);
ERROR 1062 (23000): Duplicate entry '10.10.0.11-8081' for key 'ip'
mysql>
# not null 和unique的化学反应=>会被识别成表的主键
create table t4(id int,name varchar(10) not null unique);
create table t5(id int,name varchar(10) unique);
# 主键primary key
# 特点
# 1、主键的约束效果是not null+unique
# 2、innodb表有且只有一个主键，但是该主键可以是联合主键
create table t6(
id int primary key auto_increment,
name varchar(5)
);
insert into t6(name) values
("egon"),
("tom"),
("to1"),
("to2");
# 联合主键（了解）
create table t7(
id int,
name varchar(5),
primary key(id,name)
);
2.表之间的三种关系
# 引入
# 先创建被关联表
create table dep(
id int primary key auto_increment,
name varchar(6),
comment varchar(30)
);
# 再创建关联表
create table emp(
id int primary key auto_increment,
name varchar(10),
gender varchar(5),
dep_id int,
foreign key(dep_id) references dep(id) on delete cascade on update cascade
);
# 先往被关联表插入数据
insert into dep(id,name) values
(1,'技术部'),
(2,'人力资源部'),
(3,'销售部');
# 先往关联表插入数据
insert into emp(name,gender,dep_id) values
('egon',"male",1),
('alex1',"male",2),
('alex2',"male",2),
('alex3',"male",2),
('李坦克',"male",3),
('刘飞机',"male",3),
('张火箭',"male",3),
('林子弹',"male",3),
('加特林',"male",3)
;
# 多对一
# 多对多
create table author(
id int primary key auto_increment,
name varchar(10)
);
create table book(
id int primary key auto_increment,
name varchar(16)
);
create table author2book(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id) on delete cascade on update cascade,
foreign key(book_id) references book(id) on delete cascade on update cascade
);
# 一对一
create table customer(
id int primary key auto_increment,
name varchar(16),
phone char(11)
);
create table student(
id int primary key auto_increment,
class varchar(10),
course varchar(16),
c_id int unique,
foreign key(c_id) references customer(id) on delete cascade on update cascade
);
3.记录相关操作
# 插入
mysql> create table user(name varchar(16),password varchar(10));
Query OK, 0 rows affected (0.29 sec)
mysql>
mysql> insert into user select user,password from mysql.user;
# 删除
delete from 表 where 条件;
# 更新
update 表 set 字段=值 where 条件;
# 单表查询语法
select distinct 字段1,字段2,字段3,... from 表名
where 过滤条件
group by 分组的条件
having 筛选条件
order by 排序字段
limit n;
# 简单查询
select name,sex from emp;
select name as 名字,sex 性别 from emp;
select * from emp;
# 避免重复（针对的是记录）
select distinct post from emp;
# 进行四则运算
select name as 名字,salary*12 as 年薪 from emp;
# concat()拼接记录的内容
select name ,concat(salary*12,"$") from emp;
select name ,concat("annual_salary",':',salary*12) as 年薪 from emp;
select name ,concat("annual_salary",':',salary*12,':','$') as 年薪 from emp;
select name ,concat_ws(":","annual_salary",salary*12,'$') as 年薪 from emp; --放前面自动拼接
select (
case
when name="egon" then
name
when name="alex" then
concat(name,"_dsb")
else
concat(name,"_sb")
end
) as 名字 from emp;
===========================================where
select * from emp where id >= 3 and id <= 5;
select * from emp where id between 3 and 5;
select * from emp where id not between 3 and 5;
select * from emp where id=3 or id=5 or id=7;
select * from emp where id in (3,5,7);
select * from emp where id not in (3,5,7);
select * from emp where id=3 or id=5 or id=7;
select * from emp where name like 'jin%';
select * from emp