一:模糊查询
模糊查询中的两个通配符:
1 % 代表任意多个任意字符
2 _ 代表任意一个字符
模糊查询的SQL语法:
select 字段列表 from 表名称 where 字段名 like 匹配条件;
创建worker表
create table worker(
worker_id int auto_increment primary key,
name varchar(20),
job varchar(20),
salary float
);
insert into worker(name,job,salary)values('张二强','修车',5000),
('王小刚','美容',3500),('李刚','经理',6000),('王娟','老板娘',7000);
1.查询所有姓王的员工
select * from worker where name like '王%';
2.查询名字中带有‘刚’的工人
select * from worker where name like '%刚%';
insert into worker(name,job,salary)values('刚强','门卫',3000);
3.查询名字中第二个字是‘刚’的工人
select * from worker where name like '_刚%';
二 外键(foreign key)
外键用来约束子表的记录与父表对应;
constraint 约束名 foreign key(用来关联父表的字段)
references 父表名(父表中用来关联字表的字段,一般为主键);
创建学校表
create table school(
schoolid int auto_increment primary key,
schoolname varchar(30) not null,
note text
);
insert into school(schoolname,note)values('清华大学','清华大学很好'),
('北京大学','北大不错'),('交大','交大也很好');
创建学生表
create table student(
stuid int auto_increment primary key,
name varchar(20) not null,
score double not null,
sex varchar(10),
school_id int,
constraint fk_student foreign key(school_id)references school(schoolid)
on delete cascade on update cascade
);
insert into student(name,score,sex,school_id)values('张三',92,'男',1),
('张三丰',96,'男',1),('李娟',88,'女',2),('张红',86,'女',2),('李芬',85,'女',3);
三 多表查询
1 等值连接
select 字段列表 from 表A,表B 。。。。where连接条件【其他过滤条件】
使用‘等值连接’查询学生姓名,学生成绩,学校名称
select student.name,student.score,school.schoolname from school,
student where student.school_id=school.schoolid;
2使用等值连接查询出张红所在的学生姓名,学生成绩,学校名称
select student.name,student.score,school.schoolname from school,
student where student.school_id=school.schoolid and student.name='张红';
2 内连接
select 字段列表 from 表A inner join 表B on 连接条件 【其他过滤条件】;
3 使用‘内连接’查询学生姓名,学生成绩,学校名称
select student.name,student.score,school.schoolname from school inner join
student where student.school_id=school.schoolid;
4 使用‘内连接’查询张红,学生姓名,学生成绩,学校名称
select student.name,student.score,school.schoolname from school,
student where student.school_id=school.schoolid and student.name='张红';
外连接
左外连接:
select 字段列表 from 表A left join 表B on 连接条件 【其他过滤条件】;
注意:左外连接的查询结果不但包括了符合连接条件的查询记录,
而且还包括了左表不符合连接条件的记录
5 先插入一条学校记录 再使用‘左外连接’查询学生姓名,学生成绩,
要求显示所有学校的记录,
insert into school(schoolname,note)values('东北师范',null);
select student.name,student.score,school.schoolname from school left join
student on student.school_id=school.schoolid;
右外连接
select 字段列表 from 表A right join 表B on 连接条件 【其他过滤条件】;
注意:右外连接的查询结果不但包括了符合连接条件的查询记录,
而且还包括了右表不符合连接条件的记录
select student.name,student.score,school.schoolname from student right join
school on student.school_id=school.schoolid;
四;子查询(嵌套查询)
在外部查询sql语句中,可以包含其他内部查询的语句,这个内部查询(也叫嵌套查询)
就是子查询,子查询需要使用括号括起来,子查询的结果一般作为外部查询语句的条件
子查询经常使用的关键字
1.in
判断某字段的值是否在子查询结果集中
2.all
判断某字段的值是否满足操作符对子查询结果集所有值的比较
3. any
判断某字段的值是否满足操作符对子查询结果集任意一个值的比较
五,一对多,与多对多关系表设计
1 一对多在关系表设计:
在‘多’的一方中,设置外键,关联‘一’的一方,(eg:学校表(‘一’)与学生表(‘多’))
2,多对多关系表设计
多对多关系表的设计需要引入一个中间表,中间表负责维护多对多关系表
中间表至少需要包含两个字段:这两个字段分别用来关联这两个多对多的关系
通常会将这两个字段设置成外键,关联两个‘多’表的主键。
可以通过将这两个字段设置成联合主键的方式,用避免组合值重复
多对多关系表sql;多对多关系表设计:
创建person表
cerate table person(
perid int auto_increment primary key,
pername varchar(20),
sex varchar(10),
age int
);
添加person表
insert into person(pername,sex,age)values('令狐冲','男',25),
('郭靖','男',23),('黄蓉','女',21),('东方不败','女',20);
创建hobby_group表
cerate table hobby_group(
gid int auto_increment primary key,
gname varchar(20)
);
添加兴趣组记录:
insert into hobby_group(gname)values('乒乓球组'),('围棋组'),('篮球组');
创建中间关联表:
create table person_hobby(
person_id int,
group_id int,
foreign key(person_id) references person(perid),
foreign key(group_id) references hobby_group(gid)
primary key(person_id,group_id)
);
添加关联信息:
insert into person_hobby(person_id,group_id)values(1,1),(1,3),(2,3),
(3,3),(4,1),(4,2);
1.查询名字为‘令狐冲’的人加入了那些组?
select person.perid,person.pername,hobby_group.gname
from person,hobby_group,person_hobby where
person.perid=person_hobby.person_hobby.person_id and hobby_group.
select gname from hobby_group where gid in
(select group_id from person_hobby where person_id
in (select perid from person where pername='令狐冲'));
模糊查询中的两个通配符:
1 % 代表任意多个任意字符
2 _ 代表任意一个字符
模糊查询的SQL语法:
select 字段列表 from 表名称 where 字段名 like 匹配条件;
创建worker表
create table worker(
worker_id int auto_increment primary key,
name varchar(20),
job varchar(20),
salary float
);
insert into worker(name,job,salary)values('张二强','修车',5000),
('王小刚','美容',3500),('李刚','经理',6000),('王娟','老板娘',7000);
1.查询所有姓王的员工
select * from worker where name like '王%';
2.查询名字中带有‘刚’的工人
select * from worker where name like '%刚%';
insert into worker(name,job,salary)values('刚强','门卫',3000);
3.查询名字中第二个字是‘刚’的工人
select * from worker where name like '_刚%';
二 外键(foreign key)
外键用来约束子表的记录与父表对应;
constraint 约束名 foreign key(用来关联父表的字段)
references 父表名(父表中用来关联字表的字段,一般为主键);
创建学校表
create table school(
schoolid int auto_increment primary key,
schoolname varchar(30) not null,
note text
);
insert into school(schoolname,note)values('清华大学','清华大学很好'),
('北京大学','北大不错'),('交大','交大也很好');
创建学生表
create table student(
stuid int auto_increment primary key,
name varchar(20) not null,
score double not null,
sex varchar(10),
school_id int,
constraint fk_student foreign key(school_id)references school(schoolid)
on delete cascade on update cascade
);
insert into student(name,score,sex,school_id)values('张三',92,'男',1),
('张三丰',96,'男',1),('李娟',88,'女',2),('张红',86,'女',2),('李芬',85,'女',3);
三 多表查询
1 等值连接
select 字段列表 from 表A,表B 。。。。where连接条件【其他过滤条件】
使用‘等值连接’查询学生姓名,学生成绩,学校名称
select student.name,student.score,school.schoolname from school,
student where student.school_id=school.schoolid;
2使用等值连接查询出张红所在的学生姓名,学生成绩,学校名称
select student.name,student.score,school.schoolname from school,
student where student.school_id=school.schoolid and student.name='张红';
2 内连接
select 字段列表 from 表A inner join 表B on 连接条件 【其他过滤条件】;
3 使用‘内连接’查询学生姓名,学生成绩,学校名称
select student.name,student.score,school.schoolname from school inner join
student where student.school_id=school.schoolid;
4 使用‘内连接’查询张红,学生姓名,学生成绩,学校名称
select student.name,student.score,school.schoolname from school,
student where student.school_id=school.schoolid and student.name='张红';
外连接
左外连接:
select 字段列表 from 表A left join 表B on 连接条件 【其他过滤条件】;
注意:左外连接的查询结果不但包括了符合连接条件的查询记录,
而且还包括了左表不符合连接条件的记录
5 先插入一条学校记录 再使用‘左外连接’查询学生姓名,学生成绩,
要求显示所有学校的记录,
insert into school(schoolname,note)values('东北师范',null);
select student.name,student.score,school.schoolname from school left join
student on student.school_id=school.schoolid;
右外连接
select 字段列表 from 表A right join 表B on 连接条件 【其他过滤条件】;
注意:右外连接的查询结果不但包括了符合连接条件的查询记录,
而且还包括了右表不符合连接条件的记录
select student.name,student.score,school.schoolname from student right join
school on student.school_id=school.schoolid;
四;子查询(嵌套查询)
在外部查询sql语句中,可以包含其他内部查询的语句,这个内部查询(也叫嵌套查询)
就是子查询,子查询需要使用括号括起来,子查询的结果一般作为外部查询语句的条件
子查询经常使用的关键字
1.in
判断某字段的值是否在子查询结果集中
2.all
判断某字段的值是否满足操作符对子查询结果集所有值的比较
3. any
判断某字段的值是否满足操作符对子查询结果集任意一个值的比较
五,一对多,与多对多关系表设计
1 一对多在关系表设计:
在‘多’的一方中,设置外键,关联‘一’的一方,(eg:学校表(‘一’)与学生表(‘多’))
2,多对多关系表设计
多对多关系表的设计需要引入一个中间表,中间表负责维护多对多关系表
中间表至少需要包含两个字段:这两个字段分别用来关联这两个多对多的关系
通常会将这两个字段设置成外键,关联两个‘多’表的主键。
可以通过将这两个字段设置成联合主键的方式,用避免组合值重复
多对多关系表sql;多对多关系表设计:
创建person表
cerate table person(
perid int auto_increment primary key,
pername varchar(20),
sex varchar(10),
age int
);
添加person表
insert into person(pername,sex,age)values('令狐冲','男',25),
('郭靖','男',23),('黄蓉','女',21),('东方不败','女',20);
创建hobby_group表
cerate table hobby_group(
gid int auto_increment primary key,
gname varchar(20)
);
添加兴趣组记录:
insert into hobby_group(gname)values('乒乓球组'),('围棋组'),('篮球组');
创建中间关联表:
create table person_hobby(
person_id int,
group_id int,
foreign key(person_id) references person(perid),
foreign key(group_id) references hobby_group(gid)
primary key(person_id,group_id)
);
添加关联信息:
insert into person_hobby(person_id,group_id)values(1,1),(1,3),(2,3),
(3,3),(4,1),(4,2);
1.查询名字为‘令狐冲’的人加入了那些组?
select person.perid,person.pername,hobby_group.gname
from person,hobby_group,person_hobby where
person.perid=person_hobby.person_hobby.person_id and hobby_group.
select gname from hobby_group where gid in
(select group_id from person_hobby where person_id
in (select perid from person where pername='令狐冲'));