1.基本select语句
#创建数据表
create table t_person(
per_id int primary key auto_increment,
per_name varchar(20),
per_age int,
per_address varchar(20),
per_day datetime
);
insert into t_person values(null,'zhangsan',23,'西安','2021-12-01');
insert into t_person values(null,'lisi',24,'北京','2014-12-01');
insert into t_person values(null,'wangwu',25,'上海','2020-12-01');
insert into t_person values(null,'zhaosi',25,'深圳','2011-12-01');
insert into t_person values(null,'lisisi',26,'广州','2021-12-01');
insert into t_person values(null,'zhangsansan',24,'广州','2022-12-01');
insert into t_person values(null,'wangwuwu',23,'南昌','2021-11-01');
#1.查询所有
select*from t_person;
#2.查询指定列
select per_name,per_age from t_person;
#3.基本条件查询【= or and】
select*from t_person where per_name='lisisi';
select*from t_person where per_name='zhangsan'and per_age=23;
select*from t_person where per_name='lisi' or per_age=25;
#4.比较运算符查询
select*from t_person where per_age<25;
#5.区间查询 between 最小值 and 最大值
select*from t_person where per_day between '2014-11-2' and '2021-12-01';
#6.in(集合)条件查询
select*from t_person where per_age in(23,24,45,27);
#7.模糊查询
select*from t_person where per_name like '%wu%';
select*from t_person where per_name like '%si';
select*from t_person where per_name like 'wang%';
#8.null not null
select*from t_person where per_name is null;
select*from t_person where per_name null;
#9.排序查询 order by 列名 asc[升序] desc[降序]
select*from t_person order by per_age asc;
select*from t_person order by per_name desc;
#10.SQL聚合函数查询
#count(列名/*)----行的总数【总记录数】
select count(*) from t_person;
select count(per_name) from t_person;
#sum(列名)----求和
select sum(per_age) from t_person;
#avg(列名)----求平均数
select avg(per_age) from t_person;
#max(列名)----求最大值
#min(列名)----求最小值
select max(per_age) from t_person;
select min(per_age) from t_person;
#11.分页查询 limit 参数1,参数2
#参数1----当前页的第一条记录的开始位置【(当前页码-1)*每页记录数】
#参数2----每页记录数
#假设查询第2页,每页只能显示2条记录
select*from t_person limit 2,2;
#12.分组查询 group by 列名[先分组,后查询统计]
#根据年龄分组统计每一组的人数
select per_age,count(*) from t_person group by per_age;
#13.别名查询
#数据库表可以有别名 【表名称 as 表的别名 / 表名称 表的别名】
select*from t_person as 用户表;
#列也可以有别名【列名称 as 列的别名 / 列名称 列的别名】
select per_age as 年龄,count(per_age) as 人数 from t_person group by per_age;
#14.子查询
#查询年龄小于平均年龄的学生信息
select*from t_person where per_age<(select avg(per_age) from t_person);
2.多表查询
表与表之间的关系【一对一,一对多,多对多】
方法:通过外键建立表与表的关系。
#1.一对一主键关联关系【主键关联主键,主键既是主键也是外键】
#2.一对一外键关联关系【专门创建外键列,外键列关联到主键列】 外键列要添加unique
#3.一对多【外键的建立要放在多方表中维护】
#4.多对多【需要单独的创建一张表类维护关系】
#一对一主键关联关系【主键关联主键,主键既是主键也是外键】
#创建用户信息表
create table t_user(
user_id int primary key auto_increment,
user_name varchar(20),
user_hei double,
foreign key t_user(user_id) references t_card(card_id)
);
#创建用户身份证信息表
create table t_card(
card_id int primary key auto_increment,
card_name varchar(20),
card_address varchar(20)
);
#一对一外键关联关系【专门创建外键列,外键列关联到主键列】 外键列要添加unique
create table t_lock(
lock_id int primary key auto_increment,
lock_name varchar(20),
k_id int unique,
foreign key t_lock(k_id) references t_key(key_id)
);
create table t_key(
key_id int primary key auto_increment,
key_name varchar(20)
);
insert into t_key value(null,'钥匙1');
insert into t_key value(null,'钥匙2');
insert into t_lock value(null,'锁1',1);
insert into t_lock value(null,'锁2',3);
#一对多【外键的建立要放在多方表中维护】 把一对一中的unique去掉即可
create table t_student(
student_id int primary key auto_increment,
student_name varchar(20),
student_age int,
student_address varchar(20),
c_id int,
foreign key t_student(c_id) references t_class(class_id)
);
create table t_class(
class_id int primary key auto_increment,
class_name varchar(20)
);
insert into t_class values(null,'java班');
insert into t_student values(null,'zhangsan',23,'西安',1);
insert into t_student values(null,'lisi',24,'西安',1);
insert into t_student values(null,'wangwu',25,'西安',1);
insert into t_student values(null,'liuliu',26,'西安',1);
insert into t_class values(null,'前端班');
insert into t_student values(null,'zhangsansan',23,'西安',2);
insert into t_student values(null,'lisisi',24,'西安',2);
insert into t_student values(null,'wangwuwu',25,'西安',2);
insert into t_student values(null,'liuliuliu',26,'西安',2);
#多对多【需要单独的创建一张表类维护关系】
#创建角色表
create table t_role(
r_id int primary key auto_increment,
r_name varchar(10),
r_desc varchar(30)
);
#创建项目表
create table t_group(
g_id int primary key auto_increment,
g_name varchar(20),
g_desc varchar(30)
);
#创建中间表维护关联关系
create table t_rolegroup(
z_id int primary key auto_increment,
role_id int,
group_id int
);
##通过修改表结构的方式添加外键
alter table t_rolegroup add constraint fk1 foreign key t_rolegroup(role_id) references t_role(r_id);
alter table t_rolegroup add constraint fk2 foreign key t_rolegroup(group_id) references t_group(g_id);
#向角色表中添加测试数据
insert into t_role values(null,'java程序员','负责开发java程序');
insert into t_role values(null,'测试员','负责测试程序');
#向项目组表中添加测试数据
insert into t_group values(null,'CRM组','负责开发CRM系统');
insert into t_group values(null,'ERP组','负责开发ERP系统');
#向中间表中添加测试数据
insert into t_rolegroup values(null,1,1);
insert into t_rolegroup values(null,1,2);
insert into t_rolegroup values(null,2,1);
insert into t_rolegroup values(null,2,2);
3.联合查询
内连接查询 【inner join】
左连接查询 【left join】 左外连接查询 【left outer join】
右连接查询 【right join】 右外连接查询 【right outer join】
内连接与左连接和右连接查询的区别
--内连接:显示左边右边共有的
--左连接:左边有的显示,右边没有的为null
--右连接:右边有的显示,左边没有的为null
#语法格式
/*
select [col1,col2...coln来自多张表【最好使用别名】]
from table1
inner join/
left outer join/left join/
right outer join/right join
table2
on table1.col = table2.col
where 查询条件
*/
#根据锁名称,得到与锁相关的所有信息【包括锁对应的钥匙信息】
select lock_id,lock_name,key_id,key_name from t_lock inner join t_key on t_lock.k_id=t_key.key_id where t_lock.lock_name='锁1';
select lock_id,lock_name,key_id,key_name from t_lock right join t_key on t_lock.k_id=t_key.key_id where t_lock.lock_name='锁2';
#查询zhangsan的所有信息,即包括张三所在班级的信息
select student_id,student_name,student_age,student_address,class_id,class_name
from t_student inner join t_class on t_student.student_id=t_class.class_id where t_student.student_name='zhangsan';
select student_id,student_name,student_age,student_address,class_id,class_name
from t_student left join t_class on t_student.student_id=t_class.class_id where t_student.student_name='zhangsan';
#查询测试员的所有信息,即包括测试员所在项目的信息
#3表了
select r_id,r_name,r_desc,g_id,g_name,g_desc
from t_role
right join t_rolegroup on t_role.r_id=t_rolegroup.role_id
right join t_group on t_rolegroup.group_id=t_group.g_id
where t_role.r_name='测试员';
面试题1
CREATE TABLE test(
name varchar(20),
course varchar(20),
score varchar(20)
);
INSERT INTO test VALUES
('张三','语文',81),
('张三','数学',75),
('李四','语文',76),
('李四','数学',90),
('王五','语文',81),
('王五','数学',82);
请用一条Sql语句查询分数大于80的学生
-- 查询分数大于80的学生
select * from test where score>80;
面试题2
有如下表数据,表面(product, sale),请用一条sql语句查出201808月份商品销售额前2名,显示列为产品名称,销售总额。
product(产品)
id | product_name | price |
1 | 苹果 | 4.5 |
2 | 荔枝 | 6 |
3 | 杨桃 | 7 |
4 | 樱桃 | 20 |
5 | 板栗 | 8 |
6 | 猕猴桃 | 6.5 |
sale(销售)
id | product_id(产品id) | price(单价) | quantity(销售数量) | sale_date(销售日期) |
1 | 1 | 4.5 | 20 | 2018/9/28 |
2 | 1 | 3.5 | 10 | 2018/8/28 |
3 | 2 | 6 | 3 | 2018/8/28 |
4 | 4 | 20 | 2 | 2018/8/30 |
5 | 2 | 6 | 3 | 2018/8/26 |
create table product(
id int primary key,
product_name varchar(10),
price double
);
insert into product values(1,'苹果',4.5);
insert into product values(2,'荔枝',6);
insert into product values(3,'杨桃',7);
insert into product values(4,'樱桃',20);
insert into product values(5,'板栗',8);
insert into product values(6,'猕猴桃',6.5);
create table sale(
id int primary key,
product_id int,
price double,
quantity int,
sale_date date,
constraint sale_fk foreign key(product_id) references product(id)
);
insert into sale values(1,1,4.5,20,'2018-9-28');
insert into sale values(2,1,3.5,10,'2018-8-28');
insert into sale values(3,2,6,3,'2018-8-28');
insert into sale values(4,4,20,2,'2018-8-30');
insert into sale values(5,2,6,3,'2018-8-26');
-- 查出201808月份商品销售额前2名的产品名称和销售总额
select a.product_name '产品名称',sum(b.price*b.quantity) '销售额' from product a join sale b on a.id=b.product_id and b.sale_date>='2018-08-01' and b.sale_date<='2018-8-31' group by a.product_name limit 2;
-- 注意:分组group by使用后,select 后只能跟分组字段和聚合函数
-- 注意: 上述语句执行顺序为 两个表内连接——》按日期筛选——》按名称分组——》求和函数——》显示产品名称和销售额
面试题3
2.根据下面三张表,编写SQL语句
表1 学生信息表 表名:StudentInfo
id | StudentName | Age | Sex | Classname |
1 | 王小明 | 22 | 男 | 大一(1)班 |
2 | 李伟 | 23 | 男 | 大一(1)班 |
表2 课程表 表名:Course
id | CourseName | Types |
1 | 语文 | 必须 |
2 | 高数 | 必须 |
3 | 英语 | 必须 |
4 | 计算机原理 | 选修 |
表3 学生所选课程表 表名:Student _sel_Course
id | Studentid(对应StudentInfo表的id) | Courseid(对应Course表的id) |
1 | 1 | 1 |
2 | 1 | 3 |
3 | 1 | 4 |
4 | 2 | 2 |
5 | 2 | 3 |
1.) 查出王小明未选择的课程名称。
2.)查询选了选修课程的人员姓名。
-- 学生信息表
create table studentinfo(
id int primary key,
studentname varchar(10),
age int,
sex varchar(1),
classname varchar(10)
);
-- 添加学生信息
insert into studentinfo values(1,'王小明',22,'男','大一(1)班');
insert into studentinfo values(2,'李伟',23,'男','大一(1)班');
-- 课程表
create table course(
id int primary key,
coursename varchar(10),
types varchar(10)
);
-- 添加课程信息
insert into course values(1,'语文','必修');
insert into course values(2,'高数','必修');
insert into course values(3,'英语','必修');
insert into course values(4,'计算机原理','选修');
-- 中间表
create table student_sel_course(
id int,
studentid int,
courseid int,
foreign key(studentid) references studentinfo(id),
foreign key(courseid) references course(id)
);
-- 中间表添加信息
insert into student_sel_course values(1,1,1);
insert into student_sel_course values(2,1,3);
insert into student_sel_course values(3,1,4);
insert into student_sel_course values(4,2,2);
insert into student_sel_course values(5,2,3);
-- 查出王小明选择的课程名称
select studentname,coursename from student_sel_course c join studentinfo a on c.studentid =a.id join course b on c.courseid=b.id having a.studentname='王小明';
-- 查出王小明未选择的课程名称
select b.id from student_sel_course c join studentinfo a on c.studentid =a.id join course b on c.courseid=b.id and a.studentname='王小明';
select id,coursename from course where id not in(select b.id from student_sel_course c join studentinfo a on c.studentid =a.id join course b on c.courseid=b.id and a.studentname='王小明');
-- 查询选了选修课程的人员姓名
select studentname,coursename,types from student_sel_course c join studentinfo a on c.studentid =a.id join course b on c.courseid=b.id and types='选修';