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

總結

以上是生活随笔為你收集整理的mysql 查询姓张或者姓王_mysql查询练习的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。