MySql02：增删改查

1.外键索引(外键约束)

外键：用于约束主表与从表之间的关联关系

# 主表  父表
create table dep(
	depid int unsigned  primary key auto_increment,
	depname varchar(20)
)；

# 从表 子表
create table emp(
	empid int primary key auto_increment,
	empname varchar(20) not null,
	empaddress varchar(50),
	did int not null
)；

# 外键索引(外键约束) foreign key 
# 外键 用于约束主表与从表之间的关联关系 
alter table emp add constraint FK_did  foreign key(did) references dep(depid);

# 删除外键 mysql在帮我们创建外键索引的同时 还帮我们创建了一个普通索引 所以 我们也要把普通索引删除掉
alter table emp drop foreign key FK_did;
# 普通索引的名字与外键索引的名字相同
# 删除普通索引
alter table emp drop index FK_did;

create table grade (
	gid int primary key auto_increment,
	gname varchar(20) not null
);

create table student (
	stuid int primary key auto_increment,
	stuname varchar(20) not null,
	stuaddress varchar(50) ,
	stuage int ,
	gradeid int not null,
	constraint FK_gradeid foreign key(gradeid) references grade(gid)
);

# 实际开发中不是用物理外键
# 如果需要两个表产生关联关系  我们可以使用"逻辑外键"
# 因为在分布式开发场景中 我们通常需要分库分表 而外键是无法分库分表的

2.DML - 添加 、修改 、删除

2.1添加 insert

# #添加 insert 
# 方式1  
insert into dep(depid,depname) values(4,'人事部');

# 方式2 
insert into dep(depname) values('技术部');
insert into emp(empname,did) values('小沈阳',2);

# 方式3  可以不指定列名 直接添加 values小括号中的值必须与原表中列的顺序保持一致
# 注意：insert into dep values('营销部',7); 会报错 因为顺序不一致
insert into dep values(6,'市场部');

# 方式4 一次添加多条数据  
insert into dep(depname) values('后勤部'),('安保部'),('宣传部');

2.2修改 update

# # 修改 update
update dep set depname = '人力资源部' where depid = 4;

update emp set empaddress = '铁岭' where empname = '赵四';

update emp set empaddress = '象牙山',empname = '尼古拉斯·赵四' where empid = 1;

update emp set empage = empage + 20 where empage = 22 and empname = '大拿';

update emp set empname = '赵四';

2.3删除 delete

# #删除 DELETE
delete from dep where depid = 1;

delete from emp where empname = '赵四' && empage = 22;

delete from emp where empname = '广坤' or empaddress = '铁岭';

delete from emp;

2.4删除的三种方式

# 删除的三种方式:
#1# drop table 表名----删除数据、删除表结构
drop table city;

#2# truncate table 表名
truncate table city;# 删除整个表数据 不影响表结构 索引 约束 会清空主键的编号

#3# delete from 表名
delete from city; # 删除数据 不影响表结构 索引 约束 删除以后不会清空主键的编号

# drop > truncate > delete 

3.DQL - 查询关键字

3.1 普通查询

# 查询所有 * 代表所有列  
select * from emp;

# 指定查询其中某些列
select empname from emp;
select empname,empage from emp;

3.2 as 关键字

# as 关键字 * 实现 给列 或者 表 取别名 
# as 关键字可以省略不写

# 给列取别名 
select empname as '员工姓名',empage as '员工年龄' from emp;
# 给表取别名
select e.empname,e.empage from emp as e;

3.3 distinct 去除重复的内容

select distinct empsex from emp;

3.4 where 条件

# 查询后加上 where条件 
select * from emp;

# 查询年龄为20的人 信息
select * from emp where empage = 20;
# 查询年龄为25  并且性别为男的人信息
select * from emp where empsex = '男' and empage = 25;
# 查询年龄为26  或者 地址为象牙山的人信息
select * from emp where empage = 26 or empaddress = '象牙山';

# 查询地址不为null的人的信息 
# is null 查询为null
# is not null 查询不为null的
 select * from emp where empaddress is  null;

3.5 between and 关键字

# 以下两条语句功能相同
select * from emp where empage between 25 and 27;
select * from emp where empage>= 25 and empage<=27;

3.6 like 实现模糊查询

#1# % 表示匹配 '0个'或者'n个' 任意字符
# 查询名字中带 '四' 的人信息
select * from emp where empname like '%四%';

# 查找名字以  '四'  结尾的人的信息
select * from emp where empname like '%四';

# 查找名字以 '四'开头的人的信息
select * from emp where empname like '四%';


#2# _ 一个下划线表示一个任意字符
# 查找名字以 '四' 开头 名字一共两个字符的人的信息
select * from emp where empname like '四_';

# 查找名字以 '四' 开头 名字一共三个字符的人的信息
select * from emp where empname like '四__';

3.7 in 范围查询

# 在WHERE子句中使用IN进行范围查询
# SELECT  字段列1，字段2 ,…  
#	FROM 表名  
#	WHERE  字段x  
#	IN  ( 值1,值2,值3…)
#1.查询的字段x的值，至少与括号中的一个值相同
#2.多个值之间用英文逗号隔开

#普通处理方式
SELECT * FROM subject 
	where ClassHour = 100 OR ClassHour =110 OR ClassHour = 120;
	
#使用IN进行查询方式，更为简洁，效率更高
SELECT * FROM subject 
	where ClassHour IN ( 100,110,120 );

3.8 null 空值查询

1.NULL代表 ‘无值’
2.区别于零值0和空符串""
3.只能出现在定义允许为NULL的字段
4.须使用 is null 或者 is not null 比较操作符去比较

4.连接查询 (多表查询)

select   [all | distinct] 
{ * | table.* |  [table.field1 [as alias1] [,table.field2[as alias2]][,…]]  }
from  table_name  [as table_ alias ]
    [left|out|inner  join  table_name2 ]    #联合查询
    [where  …]   	#指定结果需满足的条件
    [group by …]	#指定结果按照哪几个字段来分组
    [having …]	#过滤分组的记录必须满足的次要条件
    [order by …]	#指定查询记录按一个或者多个条件排序
    [limit {[offset,] row_count|row_count offset offset} ];  
  #指定查询的记录从哪条至哪条

4.1内连接

select * from emp;

#1# 两表等值连接查询: 取交集
select * from emp,dep where emp.did = dep.depid;

select e.empname as '姓名',e.empage '年龄',e.empsex '性别',d.depname '部门' 
from emp as e,dep as d 
where e.did = d.depid;

#2# 内连接查询 inner join 取交集 
select * from emp inner join dep on emp.did=dep.depid;

select e.empname as '姓名',e.empage '年龄',e.empsex '性别',d.depname '部门'  
from emp inner join dep on emp.did=dep.depid;

4.2外连接 ( out join )

# 左连接 左外连接：left join  
# 以左边的表为主表：取交集。如果不匹配左边表的数据 以null填充
SELECT * FROM emp 
LEFT JOIN dep ON emp.did = dep.depid;

# 右连接 右外连接：right join  
# 以右边的表为主表：取交集。如果不匹配匹配的项 以null填充
SELECT * FROM emp RIGHT JOIN dep ON emp.did = dep.depid;

# 三表连接
select emp.empname,emp.empage,emp.empsex,city.cityname,dep.depname 
FROM emp,city,dep 
WHERE emp.did = dep.depid 
AND emp.cid = city.cityid;

# inner join 
select emp.empname,emp.empage,emp.empsex,city.cityname,dep.depname
from city 
inner join emp on city.cityid = emp.cid 
inner join dep on emp.did = dep.depid;

5.自连接

# 自连接 (自己与自己连接) 将一个表 作为两张表来使用
# 查询出每个课程所对应的专业
select * from course;
select c1.profession,c2.cname 
from course as c1 ,course as c2 
where c1.cid = c2.profession;

select c1.profession,c2.cname 
from course as c1 
inner join course as c2 on c1.cid = c2.profession;

#右连接
select c1.profession,c2.cname 
from course as c1 
right join course as c2 on c1.cid = c2.profession;