一、 理论练习
1、约束有哪些分类?各自的作用是什么?
主键约束,primary key 一行数据的唯一标识,要求非空且唯一
非空约束,not null 保证列中所有的数据不能有null值
唯一约束,unique 保证列中所有数据各不相同。
检查约束,check 保证列中的值满足某一条件
默认约束,default 保存数据时,未指定值则采用默认值
外键约束,foreign key 外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性
2、多表关系有哪些?每种关系的建表原则是什么?
一对一,一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另一张表,用于提升查询性能
一对多,在多的一方(从表)建立外键,指向一(主表)的一方的主键
多对多,建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
3、内连接查询的SQL格式是?
-- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;
-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
4、外连接查询分类有哪些?SQL格式是?
-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
二、编程
一、练习1
环境准备:
CREATE TABLE product(
pid INT PRIMARY KEY,#主键ID
pname VARCHAR(20),#商品名称
price DOUBLE,#商品价格
category_name VARCHAR(32)#商品分类名称
);
#添加如下数据
(1,'联想电脑',5000,'电脑办公');
(2,'海尔电脑',3000,'电脑办公');
(3,'雷神电脑',5000,'电脑办公');
(4,'JACK JONES',800,'服装');
(5,'真维斯',200,'服装');
(6,'花花公子',440,'服装');
(7,'劲霸',2000,'服装');
(8,'香奈儿',800,'女士用品');
(9,'相宜本草',200,'女士用品');
(10,'面霸',5,'女士用品');
(11,'雪碧',56,'饮料饮品');
(12,'香飘飘奶茶',1,'饮料饮品');
(13,'iPhone9',8000,NULL);
需求:
#查询product表中所有记录
select * from product;
#查询product表中pid和pname字段
select pid,pname from product;
#查询product表中所有的电脑办公记录
select * from product where category_name='电脑办公';
#查询结果是表达式(运算查询):将所有商品的价格+10元进行显示.(字段可进行加减运算)
select pname,price+10 as new_price from product group by pname;
#查询商品名称为“花花公子”的商品所有信息:
select * from product where pname='花花公子';
#查询价格为800商品
select * from product where price=800;
#查询价格不是800的所有商品 <>也表示不等于
select * from product where price!=800;
#查询商品价格大于60元的所有商品信息
select * from product where price>60;
#查询商品价格在200到1000之间所有商品
select * from product where price between 200 and 1000;
#查询商品价格是200或800或者2000的所有商品
select * from product where price in(200,800,2000);
#查询含有'霸'字的所有商品
select * from where pname like '%霸%';
#查询以'香'开头的所有商品
select * from where pname like '香%';
#查询第二个字为'想'的所有商品
select * from where pname like '_想%';
#商品没有分类的商品
select * from product where category_name is null;
#查询有分类的商品
select * from product where category_name is not null;
二、练习2
环境准备
CREATE TABLE s (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50)
);
INSERT INTO s VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO s VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO s VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO s VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO s VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO s VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO s VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO s VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO s VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO s VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO s VALUES('S_1011', 'xxx', NULL, NULL);
需求:
-- 查询性别为女,并且年龄大于等于50的记录
select * from s where age>=50;
-- 查询学号为S_1001,或者姓名为liSi的记录
select * from s where sid='S_1001' or sname='lisi';
-- 查询学号为S_1001,S_1002,S_1003的记录
select * from s where sid in(S_1001,S_1002,S_1003);
-- 查询学号不是S_1001,S_1002,S_1003的记录
select * from s where sid!=S_1001 or sid!=S_1002 or sid!=S_1003;
-- 查询年龄为null的记录
select * from s where age is null;
-- 查询年龄在20到40之间的学生记录
select * from s where age between 20 and 40;
-- 查询性别非男的学生记录
select * from s where gender!='male' or gender is null;
-- 查询姓名不为null的学生记录
select * from s where sname is not null;
-- 查询姓名由5个字母构成的学生记录
select * from s where sname like '_____';
-- 查询姓名由5个字母构成,并且第5个字母为“i”的学生记录
select * from s where sname like '____i';
-- 查询姓名以“z”开头的学生记录
select * from s where sname like 'z%';
-- 查询姓名中第2个字母为“i”的学生记录
select * from s where sname like '_i%';
-- 查询姓名中包含“a”字母的学生记录
select * from s where sname like '%a%';
三、练习3
环境准备
##员工表
#DROP IF EXISTS TABLE EMP;
CREATE TABLE EMP(
EMPNO INT PRIMARY KEY, #员工编号
ENAME VARCHAR(10), #员工姓名
JOB VARCHAR(9), #员工工作
MGR INT, #员工直属领导编号
HIREDATE DATE, #入职时间
SAL DOUBLE, #工资
COMM DOUBLE, #奖金
DEPTNO VARCHAR(10) #部门名称
);
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,'开发');
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,'测试');
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,'测试');
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,'开发');
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,'前端');
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,'开发');
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,'前端');
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,'开发');
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,'前端');
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,'运维');
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,'开发');
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,'运维');
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,'运维');
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,'测试');
需求
#1.查找部门是开发的员工详细信息。
select * from EMP where DEPTNO='开发';
#2.找出从事clerk工作的员工的编号、姓名、部门号。
select EMPNO,ENAME,DEPTNO from EMP where JOB='clerk';
#3.检索出奖金多于基本工资的员工信息。
select * from EMP where COMM>SAL;
#4.检索出奖金多于基本工资60%的员工信息。
select * from emp where COMM>(SAL*0.6);
#5.找出开发部部门的职员、测试部门的职员 的员工信息。
select * from emp where DEPTNO='开发' or DEPTNO='测试';
#7.找出获得奖金的员工的信息。
select * from emp where COMM is not null;
#8.找出奖金少于100或者没有获得奖金的员工的信息。
select * from emp where COMM is not null or COMM<100;
#9.找出姓名以A、B、S开始的员工信息。
select * from emp where ENAME like 'A%' or ENAME like 'B%' or ENAME like 'S%';
#10.找到名字长度为6个字符的员工信息。
select * from emp where ename like '______';
#11.名字中不包含R字符的员工信息。
select * from emp ename not like '%R%';
#12.返回员工的详细信息并按姓名排序。
select * from emp order by name asc;
#13.返回员工的信息并按工作降序工资升序排列。
select * from emp order by job asc,asl desc;
#14.计算员工的日薪(按30天)。
select ename,sal/30 as per_diem from emp group by ename;
#15.找出姓名中包含A的员工信息。
select * from emp where name like '%A%';
四、练习4
根据需求图写出sql语句
1、查询所有学生的学号,姓名,选课数,总成绩
select
st.sid,st.sname,count(sc.course_id) courses ,sum(sc.num) allnum
FROM student st,score sc,course c
where st.sid=sc.student_id and sc.course_id=c.cid
group by sc.student_id ;
2、查询平均成绩大于八十分的同学的姓名和平均成绩
select student.sname,avg(score.num) avgnum
from student, score
where student.sid= score.student_id
GROUP BY score.student_id
having avgnum>=80;
3、查询没有报东方不败老师课的学生姓名(先查出东方不败老师的学生,之后在排除那些学生)
3.1
select sname from student where sid not in(
select
distinct sc.student_id
from
score sc
course c
teacher t
where
sc.`course_id` = c.`cid`
and c.`teacher_id` = t.`tid`
and t.`tname` = "东方不败老师"
)
3.2
select student.sname, score.course_id
from score,student where score.course_id not in (select cid from course where course.teacher_id = (select tid from teacher where teacher.tname='东方不败老师'))
and score.student_id=student.sid
group by student.sname;
4、查询挂科超过两门(包括两门)的学生姓名和班级
select student.sname,class.caption
from score,student,class
where score.num<60 and student.sid=student_id and student.class_id=class.cid
GROUP BY student_id
having COUNT(student_id)>=2;
5、查询选修了所有课程的学生姓名
select
from
score sc
where
s.sid = sc.student_id
group by
s.sid
having
count(course_id) =(select count(cid) from course);
五、练习5
环境准备:
创建 stu 表:
CREATE TABLE `stu` (
`sid` int(11) DEFAULT NULL,
`sname` varchar(25) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(6) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
`groupLeaderId` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
往 stu 表中添加数据:
INSERT INTO `stu` VALUES ('1001', '张三', '20', '男', '72', '1', '1003');
INSERT INTO `stu` VALUES ('1002', '李四', '15', '女', '78', '1', '1003');
INSERT INTO `stu` VALUES ('1003', '王五', '95', '男', '99', '1', '1010');
INSERT INTO `stu` VALUES ('1004', '赵六张', '65', '女', '60', '1', '1007');
INSERT INTO `stu` VALUES ('1005', '周七', '55', '男', '78', '3', '1007');
INSERT INTO `stu` VALUES ('1006', '茅十八', '75', '女', '96', '3', '1007');
INSERT INTO `stu` VALUES ('1007', '张三丰', '40', '男', '85', '3', '1010');
INSERT INTO `stu` VALUES ('1008', '李四方', '45', '女', '90', '2', '1010');
INSERT INTO `stu` VALUES ('1009', '艾三弗森', '45', '', '35', '4', '1008');
INSERT INTO `stu` VALUES ('1010', '三欧文', '35', '女', '49', '2', '1008');
创建 class 表:
CREATE TABLE `class` (
`cid` int(11) DEFAULT NULL,
`cname` varchar(255) COLLATE utf8_bin NOT NULL,
`caddress` varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
在 class 中添加数据:
INSERT INTO `class` VALUES ('1', 'BigData', '102');
INSERT INTO `class` VALUES ('2', 'HTML', '103');
INSERT INTO `class` VALUES ('3', 'VR', '104');
INSERT INTO `class` VALUES ('4', 'Java', '105');
需求:
查询 stu 表中记录数:
select count(*) from stu;
将stu中sid为1001的score的值改为null:
update stu set score=null where sid=1001;
计算stu中sex出现的次数:
select count(sex) from stu;
在列名后使用as给列取别名, as可以省略:
select sname name,sex gender from stu;
查询stu表中有成绩的人数:
select count(score) from stu;
统计stu表中成绩大于60的平均分:
select avg(score) from stu where score>60 group by score;
查询最高成绩和最低成绩:
selec max(score) maxscore,min(score) minscore from stu;
统计stu 表中成绩大于60的最高成绩和最低成绩:
select max(score) maxscore,min(score) from stu where score>60;
查询最高成绩,以及学生姓名:
select max(score) maxscore,sname from stu;
与聚合函数一同出现的列名,必须出现在group by 后,反之,如果出现一个没有在group by 后出现的字段,那么查询结果不正常!
查询年纪总和:
select sum(age) from stu;
查询成绩总和:
select sum(score) from stu;
计算总年龄与总成绩的和:
select sum(age)+sum(score) agescnum from stu;
查询年龄不重复的共有多少人
select count(*) from stu group by age;
查询男生多少人,女生多少人?
select sex,count(*) from stu group by sex;
查询每个班级的班级编号和每个班级的成绩和:
select s.cid,sum(score)
from stu s,class c
where s.cid = c.cid group by c.cid;
查询每个班级的班级编号以及每个班级的人数 .
select c.cid, count(*)
from stu s,class c
where s.cid=c.cid
group by c.cid;
查询成绩总和大于200的班级编号以及成绩和 .
select c.cid,sum(s.score) sum
from stu s,class c
where s.cid=c.cid
group by c.cid
having sum>=200;
查询成绩总和大于200的班级编号以及成绩和并根据成绩总和降序 .
select c.cid,sum(s.score) sum
from stu s,class c
where s.cid=c.cid
group by c.cid
having sum>=200
order by sum desc;
输出前两条数据
select * from stu limit 0,2;
从第四条开始,输出三条学生信息
select * from stu limit 0,2;
select * from stu limit 2,2;
select * from stu limit 2,3;
六、练习6
-- 创建db6数据库
CREATE DATABASE db6;
-- 使用db6数据库
USE db6;
-- 创建user表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id
NAME VARCHAR(20), -- 用户姓名
age INT -- 用户年龄
);
-- 添加数据
INSERT INTO USER VALUES (1,'张三',23);
INSERT INTO USER VALUES (2,'李四',24);
INSERT INTO USER VALUES (3,'王五',25);
INSERT INTO USER VALUES (4,'赵六',26);
-- 订单表
CREATE TABLE orderlist(
id INT PRIMARY KEY AUTO_INCREMENT, -- 订单id
number VARCHAR(30), -- 订单编号
uid INT, -- 外键字段
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (1,'hm001',1);
INSERT INTO orderlist VALUES (2,'hm002',1);
INSERT INTO orderlist VALUES (3,'hm003',2);
INSERT INTO orderlist VALUES (4,'hm004',2);
INSERT INTO orderlist VALUES (5,'hm005',3);
INSERT INTO orderlist VALUES (6,'hm006',3);
INSERT INTO orderlist VALUES (7,'hm007',NULL);
-- 商品分类表
CREATE TABLE category(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品分类id
NAME VARCHAR(10) -- 商品分类名称
);
-- 添加数据
INSERT INTO category VALUES (1,'手机数码');
INSERT INTO category VALUES (2,'电脑办公');
INSERT INTO category VALUES (3,'烟酒茶糖');
INSERT INTO category VALUES (4,'鞋靴箱包');
-- 商品表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品id
NAME VARCHAR(30), -- 商品名称
cid INT, -- 外键字段
CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (1,'华为手机',1);
INSERT INTO product VALUES (2,'小米手机',1);
INSERT INTO product VALUES (3,'联想电脑',2);
INSERT INTO product VALUES (4,'苹果电脑',2);
INSERT INTO product VALUES (5,'中华香烟',3);
INSERT INTO product VALUES (6,'玉溪香烟',3);
INSERT INTO product VALUES (7,'计生用品',NULL);
-- 中间表
CREATE TABLE us_pro(
upid INT PRIMARY KEY AUTO_INCREMENT, -- 中间表id
uid INT, -- 外键字段。需要和用户表的主键产生关联
pid INT, -- 外键字段。需要和商品表的主键产生关联
CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);
需求
答案:
1、select u.*,o.id oid
from user u,orderlist o
where u.id=o.uid;
2、select u.*,o.id
from user u left join orderlist o
on u.id=o.uid;
3、select o.*,u.*
from orderlist o left join user u
on u.id=o.uid;
4、select u.*,o.id oid
from orderlist o,user u
where o.uid in (select id from user where user.age>23) and o.uid=u.id;
5、select u.*,o.id oid
from orderlist o ,user u
where o.uid in (select id from user u where u.name='张三' or u.name='李四') and o.uid=u.id;
6、select c.*,p.name
from product p,category c
where p.cid=c.id ;
7、select c.*,p.*
from category c left JOIN product p
on p.cid=c.id;
8、select p.*,c.name
from product p left JOIN category c
on p.cid=c.id;
9、select u.*,p.*
from user u,product p,us_pro us
where us.uid=u.id and us.pid=p.id ;
10、select u.*,p.name
from us_pro us,product p,user u
where us.uid in (1,2) and us.pid=p.id and us.uid=u.id;
七、练习7
环境准备
CREATE TABLE student (
id INT(10) NOT NULL UNIQUE PRIMARY KEY ,
name VARCHAR(20) NOT NULL ,
sex VARCHAR(4) ,
birth YEAR,
department VARCHAR(20) ,
address VARCHAR(50)
);
创建score表。SQL代码如下:
CREATE TABLE score (
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,
stu_id INT(10) NOT NULL ,
c_name VARCHAR(20) ,
grade INT(10)
);
-- 向student表插入记录的INSERT语句如下:
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
向score表插入记录的INSERT语句如下:
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);
需求
3.查询student表的所有记录
select * from student
4.查询student表的第2条到4条记录
select * from student in(2,4);
5.从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息
select id,name,department from student;
6.从student表中查询计算机系和英语系的学生的信息
select * from student where department in ('计算机系','英语系');
7.从student表中查询年龄18~22岁的学生信息
select * from student where age between 18 and 22;
8.从student表中查询每个院系有多少人
select department,count(*) from student group by department;
9.从score表中查询每个科目的最高分
select s.c_name,max(grade) maxgrade
from score s
GROUP BY s.c_name;
10.查询李四的考试科目(c_name)和考试成绩(grade)
select t.id,s.c_name,s.grade
from (select s.id ,s.department from student s WHERE s.name='李四') t,score s
where t.id=s.stu_id ;
11.用连接的方式查询所有学生的信息和考试信息
select stu.*,sc.c_name,sc.grade
from student stu LEFT JOIN score sc
on sc.stu_id = stu.id;
12.计算每个学生的总成绩
select st.name,sum(sc.grade) sumgrade
from student st,score sc
where st.id=sc.stu_id
group by st.id;
13.计算每个考试科目的平均成绩
select sc.c_name,avg(sc.grade)
from score sc
GROUP BY sc.c_name;
14.查询计算机成绩低于95的学生信息
select stu.*
from (select sc.*
from score sc where sc.c_name='计算机' and sc.grade<95) t,student stu
where t.stu_id = stu.id;
15.查询同时参加计算机和英语考试的学生的信息
select stu.*
from student stu,score sc
where sc.c_name in ('计算机','英语') and sc.stu_id = stu.id;
16.将计算机考试成绩按从高到低进行排序
select sc.grade
from score sc
where sc.c_name='计算机'
ORDER BY sc.grade desc;
17.从student表和score表中查询出学生的学号,然后合并查询结果
select stu.*,sc.*
from student stu,score sc
where stu.id=sc.stu_id;
18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
select t.`name`,t.department,sc.c_name,sc.grade
from score sc,(select stu.id,stu.name,stu.department from student stu where stu.name like '张%' or stu.name like '王%') t
where sc.stu_id = t.id;
19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
select t.`name`,t.birth,t.department,sc.c_name,sc.grade
from score sc,(select stu.id,stu.name,stu.birth,stu.department from student stu where stu.address like '湖南%') t
where sc.stu_id = t.id;