mysql查姓名中既有a也有e的姓_mysql查询初级练习题（已更完）

这些初级题应该算是入了sql查询的门了吧？但是很多性能问题还是不清楚，太多知识有欠缺，一点一点来吧。

第一题

create table student(

Id int(10) not null auto_increment primary key,

Name varchar(20) not null ,

Sex varchar(4),

Birth year,

Department varchar(20) not null,

Address varchar(50)

)engine = InnoDB default charset = utf8

create table score(

Id int(10) not null auto_increment primary key,

Stu_id int(10) not null,

C_name varchar(20),

Grade int(10)

)engine = InnoDB default charset = utf8

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,’计算机系’, ‘湖南省衡阳市’);

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);

1) 查询student表的第2条到4条记录中计算机系和英语系的学生的信息

2) 从student表中查询每个院系年龄18~30岁的学生信息

3) 查询计算机成绩低于95的学生信息

4) 查询同时参加计算机和英语考试的学生的信息

#1) 查询student表的第2条到4条记录中计算机系和英语系的学生的信息

select * from (select * from student limit 1,3) as temTable where temTable.Department in (“计算机系”,”英语系”);

#2) 从student表中查询每个院系年龄18~30岁的学生信息

select * from student where (year(now()) – Birth) between 18 and 22;

#3) 查询计算机成绩低于95的学生信息

select * from student where Id in (select Stu_id from score where Grade < 95);

#4) 查询同时参加计算机和英语考试的学生的信息

SELECT * FROM student WHERE id in

(SELECT stu_id FROM score WHERE stu_id IN

(SELECT stu_id FROM score WHERE c_name= ‘计算机’)

AND c_name= ‘英语’ );#学着用any和把同一个表查两次！！！

第二题

create table customer(

c_id char(6) not null primary