mysql 查询姓张或者姓王_mysql查询练习

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

2.为student表和score表增加记录

create table Student (

id int(10) primary key not null unique key auto_increment COMMENT "学号",

name varchar(20) not null comment '姓名',

sex varchar(4) comment '性别',

birth year comment '出生月份',

department varchar(20) not null comment '院系',

address varchar(50) COMMENT '家庭住址'

)engine=InnoDB  comment = '学生表' auto_increment=1;

create table Score(

id int(10) PRIMARY KEY not null unique key auto_increment comment '编号',

stu_id int(10) not null COMMENT '学号',

c_name varchar(20) comment '课程名',

grade varchar(20) comment '分数',

constraint student_id foreign key (stu_id) references Student(id)

)engine=InnoDB comment='分数表'3.查询student表的所有记录SELECT * FROM student;4.查询student表的第2条到4条selec记录SELECT * FROM student LIMIT 1,3;5.从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息SELECT id,name,department FROM student;6.从student表中查询slex计算机系和英语系的学生的信息SELECT * FROM student WHERE department IN ('计算机系','英语系');7.从student表中查询年龄18~22岁的学生信息SELECT id,name,sex,2013-birth AS age,department,address FROM student WHERE 2018-birth BETWEEN  18 AND 22;8.从student表中查询每个院系有多少人SELECT department, COUNT(id) FROM student GROUP BY department;9.从score表中查询每个科目的最高分SELECT c_name,MAX(grade) FROM score GROUP BY c_name;10.查询李四的考试科目(c_name)和考试成绩(grade)SELECT c_name, grade FROM score WHERE stu_id= (SELECT id FROM student  WHERE name= '李四' );11.用连接的方式查询所有学生的信息和考试信息SELECTstudent.id,name,sex,birth,department,address,c_name,grade FROM student,score WHERE student.id=score.stu_id;12.计算每个学生的总成绩SELECT student.id,name,SUM(grade) FROM student,score WHERE student.id=score.stu_id GROUP BY id;13.计算每个考试科目的平均成绩SELECT c_name,AVG(grade) FROM score GROUP BYc_name;14.查询计算机成绩低于95的学生信息SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHEREc_name="计算机" and grade<95);15.查询同时参加计算机和英语考试的学生的信息SELECT * FROM student  WHERE id =ANY  ( SELECT stu_id FROM score  WHERE stu_id IN (          SELECT stu_id FROM          score WHERE c_name=  '计算机')  AND c_name= '英语' );SELECT a.* FROM student a ,score b ,score c WHEREa.id=b.stu_id ANDb.c_name='计算机' ANDa.id=c.stu_id ANDc.c_name='英语';16.将计算机考试成绩按从高到低进行排序SELECT stu_id, grade  FROM score WHERE c_name= '计算机'  ORDER BY grade DESC;17.从student表和score表中查询出学生的学号，然后合并查询结果SELECT id FROM student UNION SELECTstu_id  FROM score;18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩SELECT student.id, name,sex,birth,department,address, c_name,grade FROMstudent, score WHERE  (name LIKE '张%'  OR name LIKE  '王%')  AND  student.id=score.stu_id ;19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩SELECT student.id, name,sex,birth,department,address, c_name,grade FROMstudent, score WHEREaddress LIKE '湖南%'   AND  student.id=score.stu_id;