1.創建student和score表

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)

);

2.為student表和score表增加記錄

向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表的所有記錄

mysql>?SELECT?*?FROM?student;

+-----+--------+------+-------+------------+--------------+

|?id??|?name???|?sex??|?birth?|?department?|?address??????|

+-----+--------+------+-------+------------+--------------+

|?901?|?張老大?|?男???|??1985?|?計算機系???|?北京市海淀區?|

|?902?|?張老二?|?男???|??1986?|?中文系?????|?北京市昌平區?|

|?903?|?張三???|?女???|??1990?|?中文系?????|?湖南省永州市?|

|?904?|?李四???|?男???|??1990?|?英語系?????|?遼寧省阜新市?|

|?905?|?王五???|?女???|??1991?|?英語系?????|?福建省廈門市?|

|?906?|?王六???|?男???|??1988?|?計算機系???|?湖南省衡陽市?|

+-----+--------+------+-------+------------+--------------+

4.查詢student表的第2條到4條記錄

mysql>?SELECT?*?FROM?student?LIMIT?1,3;

+-----+--------+------+-------+------------+--------------+

|?id??|?name???|?sex??|?birth?|?department?|?address??????|

+-----+--------+------+-------+------------+--------------+

|?902?|?張老二?|?男???|??1986?|?中文系?????|?北京市昌平區?|

|?903?|?張三???|?女???|??1990?|?中文系?????|?湖南省永州市?|

|?904?|?李四???|?男???|??1990?|?英語系?????|?遼寧省阜新市?|

+-----+--------+------+-------+------------+--------------+

5.從student表查詢所有學生的學號(id)、姓名(name)和院系(department)的信息

mysql>?SELECT?id,name,department?FROM?student;

+-----+--------+------------+

|?id??|?name???|?department?|

+-----+--------+------------+

|?901?|?張老大?|?計算機系???|

|?902?|?張老二?|?中文系?????|

|?903?|?張三???|?中文系?????|

|?904?|?李四???|?英語系?????|

|?905?|?王五???|?英語系?????|

|?906?|?王六???|?計算機系???|

+-----+--------+------------+

6.從student表中查詢計算機系和英語系的學生的信息

mysql>?SELECT?*?FROM?student?WHERE?department?IN?('計算機系','英語系');

+-----+--------+------+-------+------------+--------------+

|?id??|?name???|?sex??|?birth?|?department?|?address??????|

+-----+--------+------+-------+------------+--------------+

|?901?|?張老大?|?男???|??1985?|?計算機系???|?北京市海淀區?|

|?904?|?李四???|?男???|??1990?|?英語系?????|?遼寧省阜新市?|

|?905?|?王五???|?女???|??1991?|?英語系?????|?福建省廈門市?|

|?906?|?王六???|?男???|??1988?|?計算機系???|?湖南省衡陽市?|

+-----+--------+------+-------+------------+--------------+

7.從student表中查詢年齡18~22歲的學生信息

mysql>?SELECT?id,name,sex,2013-birth?AS?age,department,address

->?FROM?student

->?WHERE?2013-birth?BETWEEN??18?AND?22;

+-----+------+------+------+------------+--------------+

|?id??|?name?|?sex??|?age??|?department?|?address??????|

+-----+------+------+------+------------+--------------+

|?905?|?王五?|?女???|???22?|?英語系?????|?福建省廈門市?|

+-----+------+------+------+------------+--------------+

mysql>?SELECT?id,name,sex,2013-birth?AS?age,department,address

->?FROM?student

->?WHERE?2013-birth>=18?AND?2013-birth<=22;

+-----+------+------+------+------------+--------------+

|?id??|?name?|?sex??|?age??|?department?|?address??????|

+-----+------+------+------+------------+--------------+

|?905?|?王五?|?女???|???22?|?英語系?????|?福建省廈門市?|

+-----+------+------+------+------------+--------------+

8.從student表中查詢每個院系有多少人

mysql>?SELECT?department,?COUNT(id)?FROM?student?GROUP?BY?department;

+------------+-----------+

|?department?|?COUNT(id)?|

+------------+-----------+

|?計算機系???|?????????2?|

|?英語系?????|?????????2?|

|?中文系?????|?????????2?|

+------------+-----------+

9.從score表中查詢每個科目的最高分

mysql>?SELECT?c_name,MAX(grade)?FROM?score?GROUP?BY?c_name;

+--------+------------+

|?c_name?|?MAX(grade)?|

+--------+------------+

|?計算機?|?????????98?|

|?英語???|?????????94?|

|?中文???|?????????95?|

+--------+------------+

10.查詢李四的考試科目(c_name)和考試成績(grade)

mysql>?SELECT?c_name,?grade

->??????FROM?score?WHERE?stu_id=

->??(SELECT?id?FROM?student

->????WHERE?name=?'李四'?);

+--------+-------+

|?c_name?|?grade?|

+--------+-------+

|?計算機?|????70?|

|?英語???|????92?|

+--------+-------+

11.用連接的方式查詢所有學生的信息和考試信息

mysql>?SELECT?student.id,name,sex,birth,department,address,c_name,grade

->?FROM?student,score

->??WHERE?student.id=score.stu_id;

+-----+--------+------+-------+------------+--------------+--------+-------+

|?id??|?name???|?sex??|?birth?|?department?|?address??????|?c_name?|?grade?|

+-----+--------+------+-------+------------+--------------+--------+-------+

|?901?|?張老大?|?男???|??1985?|?計算機系???|?北京市海淀區?|?計算機?|????98?|

|?901?|?張老大?|?男???|??1985?|?計算機系???|?北京市海淀區?|?英語???|????80?|

|?902?|?張老二?|?男???|??1986?|?中文系?????|?北京市昌平區?|?計算機?|????65?|

|?902?|?張老二?|?男???|??1986?|?中文系?????|?北京市昌平區?|?中文???|????88?|

|?903?|?張三???|?女???|??1990?|?中文系?????|?湖南省永州市?|?中文???|????95?|

|?904?|?李四???|?男???|??1990?|?英語系?????|?遼寧省阜新市?|?計算機?|????70?|

|?904?|?李四???|?男???|??1990?|?英語系?????|?遼寧省阜新市?|?英語???|????92?|

|?905?|?王五???|?女???|??1991?|?英語系?????|?福建省廈門市?|?英語???|????94?|

|?906?|?王六???|?男???|??1988?|?計算機系???|?湖南省衡陽市?|?計算機?|????90?|

|?906?|?王六???|?男???|??1988?|?計算機系???|?湖南省衡陽市?|?英語???|????85?|

+-----+--------+------+-------+------------+--------------+--------+-------+

12.計算每個學生的總成績

mysql>?SELECT?student.id,name,SUM(grade)?FROM?student,score

->?WHERE?student.id=score.stu_id

->?GROUP?BY?id;

+-----+--------+------------+

|?id??|?name???|?SUM(grade)?|

+-----+--------+------------+

|?901?|?張老大?|????????178?|

|?902?|?張老二?|????????153?|

|?903?|?張三???|?????????95?|

|?904?|?李四???|????????162?|

|?905?|?王五???|?????????94?|

|?906?|?王六???|????????175?|

+-----+--------+------------+

13.計算每個考試科目的平均成績

mysql>?SELECT?c_name,AVG(grade)?FROM?score?GROUP?BY?c_name;

+--------+------------+

|?c_name?|?AVG(grade)?|

+--------+------------+

|?計算機?|????80.7500?|

|?英語???|????87.7500?|

|?中文???|????91.5000?|

+--------+------------+

14.查詢計算機成績低于95的學生信息

mysql>?SELECT?*?FROM?student

->?WHERE?id?IN

->?(SELECT?stu_id?FROM?score

->?WHERE?c_name="計算機"?and?grade<95);

+-----+--------+------+-------+------------+--------------+

|?id??|?name???|?sex??|?birth?|?department?|?address??????|

+-----+--------+------+-------+------------+--------------+

|?902?|?張老二?|?男???|??1986?|?中文系?????|?北京市昌平區?|

|?904?|?李四???|?男???|??1990?|?英語系?????|?遼寧省阜新市?|

|?906?|?王六???|?男???|??1988?|?計算機系???|?湖南省衡陽市?|

+-----+--------+------+-------+------------+--------------+

15.查詢同時參加計算機和英語考試的學生的信息

mysql>?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=?'英語'?);

+-----+--------+------+-------+------------+--------------+

|?id??|?name???|?sex??|?birth?|?department?|?address??????|

+-----+--------+------+-------+------------+--------------+

|?901?|?張老大?|?男???|??1985?|?計算機系???|?北京市海淀區?|

|?904?|?李四???|?男???|??1990?|?英語系?????|?遼寧省阜新市?|

|?906?|?王六???|?男???|??1988?|?計算機系???|?湖南省衡陽市?|

+-----+--------+------+-------+------------+--------------+

mysql>?SELECT?a.*?FROM?student?a?,score?b?,score?c

->?WHERE?a.id=b.stu_id

->?AND?b.c_name='計算機'

->?AND?a.id=c.stu_id

->?AND?c.c_name='英語';

+-----+--------+------+-------+------------+--------------+

|?id??|?name???|?sex??|?birth?|?department?|?address??????|

+-----+--------+------+-------+------------+--------------+

|?901?|?張老大?|?男???|??1985?|?計算機系???|?北京市海淀區?|

|?904?|?李四???|?男???|??1990?|?英語系?????|?遼寧省阜新市?|

|?906?|?王六???|?男???|??1988?|?計算機系???|?湖南省衡陽市?|

+-----+--------+------+-------+------------+--------------+

16.將計算機考試成績按從高到低進行排序

mysql>?SELECT?stu_id,?grade

->??FROM?score?WHERE?c_name=?'計算機'

->??ORDER?BY?grade?DESC;

+--------+-------+

|?stu_id?|?grade?|

+--------+-------+

|????901?|????98?|

|????906?|????90?|

|????904?|????70?|

|????902?|????65?|

+--------+-------+

17.從student表和score表中查詢出學生的學號，然后合并查詢結果

mysql>?SELECT?id??FROM?student

->?UNION

->?SELECT?stu_id??FROM?score;

+-----+

|?id??|

+-----+

|?901?|

|?902?|

|?903?|

|?904?|

|?905?|

|?906?|

+-----+

18.查詢姓張或者姓王的同學的姓名、院系和考試科目及成績

mysql>?SELECT?student.id,?name,sex,birth,department,?address,?c_name,grade

->?FROM?student,?score

->?WHERE

->??(name?LIKE??'張%'??OR?name?LIKE??'王%')

->??AND

->??student.id=score.stu_id?;

+-----+--------+------+-------+------------+--------------+--------+-------+

|?id??|?name???|?sex??|?birth?|?department?|?address??????|?c_name?|?grade?|

+-----+--------+------+-------+------------+--------------+--------+-------+

|?901?|?張老大?|?男???|??1985?|?計算機系???|?北京市海淀區?|?計算機?|????98?|

|?901?|?張老大?|?男???|??1985?|?計算機系???|?北京市海淀區?|?英語???|????80?|

|?902?|?張老二?|?男???|??1986?|?中文系?????|?北京市昌平區?|?計算機?|????65?|

|?902?|?張老二?|?男???|??1986?|?中文系?????|?北京市昌平區?|?中文???|????88?|

|?903?|?張三???|?女???|??1990?|?中文系?????|?湖南省永州市?|?中文???|????95?|

|?905?|?王五???|?女???|??1991?|?英語系?????|?福建省廈門市?|?英語???|????94?|

|?906?|?王六???|?男???|??1988?|?計算機系???|?湖南省衡陽市?|?計算機?|????90?|

|?906?|?王六???|?男???|??1988?|?計算機系???|?湖南省衡陽市?|?英語???|????85?|

+-----+--------+------+-------+------------+--------------+--------+-------+

19.查詢都是湖南的學生的姓名、年齡、院系和考試科目及成績

mysql>?SELECT?student.id,?name,sex,birth,department,?address,?c_name,grade

->?FROM?student,?score

->?WHERE?address?LIKE?'湖南%'???AND

->??student.id=score.stu_id;

+-----+------+------+-------+------------+--------------+--------+-------+

|?id??|?name?|?sex??|?birth?|?department?|?address??????|?c_name?|?grade?|

+-----+------+------+-------+------------+--------------+--------+-------+

|?903?|?張三?|?女???|??1990?|?中文系?????|?湖南省永州市?|?中文???|????95?|

|?906?|?王六?|?男???|??1988?|?計算機系???|?湖南省衡陽市?|?計算機?|????90?|

|?906?|?王六?|?男???|??1988?|?計算機系???|?湖南省衡陽市?|?英語???|????85?|

+-----+------+------+-------+------------+--------------+--------+-------+

總結

以上是生活随笔為你收集整理的mysql查询姓王的信息代码_MySQL查询语句练习题的全部內容，希望文章能夠幫你解決所遇到的問題。

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