mysql有关表操作实例

在student表和score表上进行查询

1 创建student表和score表

2 为student表和score表增加记录

3 查询student表的所有记录

4 查询student表的第2条到第4条记录

5 从student表查询所有学生的学号id、姓名name和院系deparement的信息

6 从student表中查询计算机系和英语系的学生的信息

7 从student表中查询年龄为18-22岁的学生的信息

8 从student表中查询每个院系有多少人

9 从score表中查询每个科目的最高分

10 查询李四的考试科目和考试成绩

11 用连接查询方式查询所有学生的信息和考试信息

12 计算每个学生的总成绩

13 计算每个考试科目的平均成绩

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

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

16 将计算机考试成绩按从高到低进行排序

17 从student表和score表中查询出学生的学号，然后合并查询结果

18 查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

19 查询都是湖南的同学的姓名、年龄、院系和考试科目及成绩

（1）创建student表。SQL代码如下：

CREATE TABLE student (

id INT(10) NOT NULL UNIQUE PRIMARY KEY ,

name V ARCHAR(20) NOT NULL ,

sex V ARCHAR(4) ,

birth YEAR,

department V ARCHAR(20) ,

address V ARCHAR(50)

);

创建score表。SQL代码如下：

CREATE TABLE score (

id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT , stu_id INT(10) NOT NULL ,

c_name V ARCHAR(20) ,

grade INT(10)

);

（2）为进行查询，需要向student表和score表中插入一些数据。

向student表插入记录的INSERT语句如下：

INSERT INTO student V ALUES( 901,'张老大', '男',1989,'计算机系', '北京市海淀区'); INSERT INTO student V ALUES( 902,'张老二', '男',1990,'中文系', '北京市昌平区');

INSERT INTO student V ALUES( 903,'张三', '女',1994,'中文系', '湖南省永州市'); INSERT INTO student V ALUES( 904,'李四', '男',1994,'英语系', '辽宁省阜新市'); INSERT INTO student V ALUES( 905,'王五', '女',1995,'英语系', '福建省厦门市'); INSERT INTO student V ALUES( 906,'王六', '男',1992,'计算机系', '湖南省衡阳市');

向score表插入记录的INSERT语句如下：

INSERT INTO score V ALUES(NULL,901, '计算机',98);

INSERT INTO score V ALUES(NULL,901, '英语', 80);

INSERT INTO score V ALUES(NULL,902, '计算机',65);

INSERT INTO score V ALUES(NULL,902, '中文',88);

INSERT INTO score V ALUES(NULL,903, '中文',95);

INSERT INTO score V ALUES(NULL,904, '计算机',70);

INSERT INTO score V ALUES(NULL,904, '英语',92);

INSERT INTO score V ALUES(NULL,905, '英语',94);

INSERT INTO score V ALUES(NULL,906, '计算机',90);

INSERT INTO score V ALUES(NULL,906, '英语',85);

（3）查询student表的所有记录。

方法一：

SELECT * FROM student;

方法二：

SELECT id, name, sex, birth, department, address FROM student;

（4）查询student表的第二条到第四条记录。

SELECT * FROM student LIMIT 1,3;

（5）从student表查询所有学生的学号、姓名和院系的信息。

SELECT id, name, department FROM student;

（6）查询计算机系和英语系的学生的信息。

第一种方法是使用IN关键字。

SELECT * FROM student

WHERE department IN ('计算机系','英语系');

第二种方法是使用OR关键字。

SELECT * FROM student

WHERE department='计算机系' OR department='英语系';

（7）从student表中查询年龄为18到22岁的学生的信息。

第一种方法是使用BETWEEN AND关键字来查询，其SQL代码如下：SELECT id, name, sex, 2009-birth AS age, department, address

FROM student

WHERE 2009-birth BETWEEN 18 AND 22;

第二种方式使用AND关键字和比较运算符。SQL代码如下：SELECT id, name, sex, 2009-birth AS age, department, address FROM student

WHERE 2009-birth>=18 AND 2009-birth<=22;

（8）student表中查询每个院系有多少人。

SELECT department, COUNT(id)

FROM student GROUP BY department;

可以为COUNT(id)取别名sum_of_department，SQL语句如下：SELECT department, COUNT(id) AS sum_of_department

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）用连接查询的方式查询所有学生的信息和考试信息。SELECT student.id, name, sex, birth, department, address, c_name, grade FROM student, score

WHERE student.id=score.stu_id;

也可以为student表和score表取别名，SQL代码如下：

SELECT s1.id, name, sex, birth, department, address, c_name, grade FROM student s1, score s2

WHERE s1.id=s2.stu_id;

（12）计算每个学生的总成绩。

SELECT stu_id, SUM(grade)

FROM score GROUP BY stu_id;

如果需要显示学生姓名，执行下面的SQL代码：

SELECT student.id, name, SUM(grade)

FROM student, score

WHERE student.id=score.stu_id

GROUP BY student.id;

（13）计算每个考试科目的平均成绩。

SELECT c_name, A VG(grade)

FROM score GROUP BY c_name;

（14）查询计算机成绩低于95的学生的信息。

SELECT * FROM student

WHERE id IN

( SELECT stu_id FROM score

WHERE c_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= '英语' );

（16）将计算机成绩按从高到低进行排序。

SELECT stu_id, grade

FROM score WHERE c_name= '计算机'

ORDER BY grade DESC;

（17）从student表和score表中查询出学生的学号，然后合并查询结果。SELECT id FROM student

UNION

SELECT stu_id FROM score;

（18）查询姓张或者姓王的同学的姓名、院系、考试科目和成绩。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 ;

（19）查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。SELECT student.id, name,sex,birth,department, address, c_name,grade FROM student, score

WHERE address LIKE '湖南%' AND

student.id=score.stu_id;