mysql======基本的数据查询（1）
mysql 练习题***创建student表
mysql> create table student (
->> -> name varchar(20) not null,
-> sex varchar(4),
-> brith year,
-> deparment varchar(20),
-> address varchar(50)
-> );
Query OK, 0 rows affected (0.03 sec)
查看student表的结构
mysql> desc student
-> ;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
|> | name | varchar(20)| NO | | NULL | |
| sex | varchar(4)| YES| | NULL | |
| brith | year(4) | YES| | NULL | |
| deparment | varchar(20)| YES | | NULL | |
| address | varchar(50)| YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
***创建score表
mysql> create table score (
->> -> stu_id varchar(20),
-> c_name varchar(20),
-> grade int(10)
-> );
Query OK, 0 rows affected (0.02 sec)
查看score表的结构
mysql> desc score;
+--------+-------------+------+-----+---------+-------+
| Field| Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
|> | stu_id | varchar(20) | YES| | NULL | |
| c_name | varchar(20) | YES| | NULL | |
| grade| int(10) | YES| NULL |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
****向student表中插入数据
mysql> select * from student;
+-----+--------+------+-------+-----------+--------------+
|> +-----+--------+------+-------+-----------+--------------+
| 901 | 张老大 | 男 |1985 | 计算机系| 北京市海淀区 |
| 902 | 张老二 | 男 |1987 | 计算机系| 北京市昌平区 |
| 903 | 张三 | 女 |1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 |1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 |1991 | 英语系 | 福建厦门市 |
| 906 | 王六 | 男 |1998 | 计算机系| 湖南省衡阳市 |
+-----+--------+------+-------+-----------+--------------+
6 rows in set (0.03 sec)
****向score表中插入数据
mysql> select * from score;
+----+--------+--------+-------+
|> +----+--------+--------+-------+
|1 | 901 | 计算机 | 98 |
|2 | 901 | 英语 | 80 |
|3 | 902 | 计算机 | 65 |
|4 | 902 | 中文 | 88 |
|5 | 903 | 中文 | 95 |
|6 | 904 | 计算机 | 70 |
|7 | 904 | 英语 | 92 |
|8 | 905 | 英语 | 90 |
+----+--------+--------+-------+
8 rows in set (0.01 sec)
==============================================================
****题目1
查询student表中所有记录
mysql> select * from student;
****题目2
查询student表的第二条到第四条记录
mysql> select * from student limit 1,3；
****题目3
从student表中查询所有学生的学号（id）姓名（name）、和院系（deparment）的信息.
mysql> select> ****题目4
从student 表中查询计算机系和英语系的学生信息
mysql> select * from student where deparment='计算机系' ordeparment='英语系';
****题目5
从student表中查询出生年龄在26-30岁的学生信息
mysql> select> -> from student
-> where 2017-brith between 26 and 30;
****题目6
从student表中查询每个院系有多少人
mysql> select deparment,count(id)
-> from student
-> group by deparment;
****题目7
从score表中查询每个科目最高分
mysql> select c_name, max(grade)
-> from score
-> group by c_name;
****题目8
查询李四的考试（c_name）科目和考试成绩(grade)
mysql> select c_name,grade
-> from score
-> where stu_id=(
-> select> -> where name='李四'
-> );
****题目9
用链接的形式查询所有学生的信息和考试成绩
mysql> select student.id,name,sex,brith,deparment,address,c_name,grade
-> from student, score
-> where student.id=score.stu_id;
****题目10
计算每个学生的总成绩降序排列
mysql> select student.id,name,sex,brith,deparment,address,sum(grade)
-> from student,score
-> where student.id=score.stu_id
-> group by name
-> order by sum(grade) desc;
****题目11
计算每个科目的平均成绩
mysql> select student.id,name,sex,brith,deparment,address,avg(grade)
-> from student,score
-> where student.id=score.stu_id
-> group by> ****题目12
查询计算机成绩低于95的学生信息
mysql> select * from student
-> where> -> select stu_id from score
-> where c_name='计算机' and grade select * from student
-> where> -> (select stu_id from score
-> where stu_id in (
-> select stu_id from
-> score where c_name='计算机')
-> and c_name='英语');
****题目14
从student表和score表查询出学生的学号，然后合并查询结果
mysql> select> -> union
-> select stu_id from score;
****题目15
查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
mysql> select student.id,name,sex,brith,deparment,address,c_name,grade
-> fromstudent,score
-> where (name like '张%' or name like '王%')
-> and student.id=score.stu_id;
****题目16
查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
mysql> select student.id,name,sex,2017-brith as age,deparment,c_name,grade
-> from student,score
-> where address like '湖南%'
-> and
-> student.id=score.stu_id;
页:
[1]