数据库操作习题二——35道题

习题二——35道题"/>

数据库操作习题二——35道题

 目录：

 参考结构：

 建表-只复制表结构到新表：

 建表-复制表结构及数据到新表： 

更新- 数据更新命令一般格式：

         null:

 删除- 删除记录命令的一般格式：

         and和or:

查询-数据查询命令的一般格式：

         like:模糊匹配运算符:

         asc升序和desc降序：

         distinct去掉查询结果集的重复行：

         as给查询结果集的列取别名：

         limit：

查询- 多表连接查询命令的一般格式：

多表连接的分类：

子查询：

---

 参考结构：

select  查询目标
from  表名
where  筛选记录的条件
group  by  分组字段
having  筛选分组的条件
order  by  排序字段名
limit  m,n    限制查询结果集输出的行数

use  lianxi;

例1：请将如下的学生信息插入学生表
姓名：李二，性别：男,出生日期：1995-1-1，联系电话：11111111111，其他信息未知

    格式一：   

 insert  into  stu(stuname,sex,birthday,phone)  values('李二','男','1995-1-1','11111111111');

 格式二：

  insert  into  stu  values(null,'李二','男','1995-1-1',null,'11111111111',null,null,null);

例2:MySQL用命令行复制表的方法

 建表-只复制表结构到新表：

1.只复制表结构到新表

 CREATE TABLE 新表 LIKE 旧表 ;

例：请将stu表结构复制一份并取名为stu1

   create  table  stu1  like  stu;

 建表-复制表结构及数据到新表： 

   2.复制表结构及数据到新表
   CREATE TABLE 新表 SELECT * FROM 旧表

   例：请将stu表复制一份并取名为stu2
 

 create  table  stu2  select  *  from  stu;

更新- 数据更新命令一般格式：

update  表名
set  列名1=表达式1,列名2=表达式2……
[where  条件表达式]；

例3：将所有学生的性别均改为男。

update  stu
set sex='男';

例4：将刘雪梅的性别改为女。

update  stu
set  sex='女'
where  stuname='刘雪梅';

例5：将没有联系电话的学生的联系电话改为中文的'未知'

update  stu
set  phone='未知'
where  phone is  null;

 null:

null:表示一个不确定的值 

例6：将A001号课程的课程名改为大学英语,任课教师改为李刚 

update  course
set couname='大学英语',teachername='李刚'
where  couid='A001';

例7：将20070103学生的各科成绩均提高10%。

update  scores
set  score=score*1.1
where  stuid=20070103;

 删除- 删除记录命令的一般格式：

delete  from  表名

 [where  条件表达式]；

例8：请删除学生表中1001系的学生记录

delete  from  stu
where  depid='1001';

例9：请删除成绩表的所有记录

delete  from  scores;

例10：请删除学生表中1005系且1989年1月1日以后出生的记录

delete  from  stu
where  depid='1005'  and  birthday>'1989-1-1';

and和or:

and:并且
or:或者

例11：请删除学生表中1005系或1989年1月1日以后出生的记录

delete  from  stu
where  depid='1005'  or  birthday>'1989-1-1';

查询-数据查询命令的一般格式：

select  列名1,列名2……
from  表名
[where  查询条件];

例12.请查询出所有学生的学号、姓名、出生日期

select  stuid,stuname,birthday
from  stu;

例12.请查询出1990-1-1到2000-1-1之间出生的学生信息(包含边界值)

select  *
from  stu
where  birthday  between  '1990-1-1'  and   '2000-1-1';
或：
select  *
from  stu
where  birthday>='1990-1-1'  and   birthday<='2000-1-1';

例12.请查询出1001、1002、1005号系的学生信息

select  *
from  stu
where  depid='1001' or  depid='1002'  or  depid='1005';或
select  *
from  stu
where  depid in('1001','1002','1005');not  in：

例12.请从学生表中查询出其姓名中含有”扬 ”字的所有学生的学号和姓名.

select  stuid,stuname
from  stu
where  stuname  like  '%扬%';

例13:请从学生表中查询出所有姓王或姓李的且单名的学生学号和姓名.
 

select  stuid,stuname
from  stu
where  stuname  like  '王_'  or  stuname  like  '李_';

like:模糊匹配运算符:

通配符：%    _

例14:请从学生表中查询出联系电话不确定的行.

select  *
from  stu
where  phone  is  null;

例15.请查询出学生表所有学生的信息，其查询的结果先按性别升序排列，当性别相同时再按出生日期降序排列。

select  *
from  stu
order  by  sex  asc,birthday  desc;

 asc升序和desc降序：

asc:升序
desc:降序

例16.请查询出所有学生的性别，要求去掉查询结果集的重复行

select   distinct sex
from  stu;

 distinct去掉查询结果集的重复行：

distinct:去掉查询结果集的重复行

例17.请查询出所有学生的stuid和stuname,其查询结果集显示的列名为中文的学号和姓名。

select  stuid as  学号,stuname  as  姓名
from  stu;

as给查询结果集的列取别名：

as:用于给查询结果集的列取别名。

例18.请查询出入学成绩最高的学生记录。

select  *
from  stu
order  by  rx_score  desc
limit  1;

limit：

limit  n:用于输出查询结果集的前n条记录

查询- 多表连接查询命令的一般格式：

select  列名1，列名2……
from  表1  [inner|left|right]join   表2  on  连接表达式
where  条件表达式;

 多表连接的分类：

内部连接：inner  join(默认）
左外连接：left  join
右外连接：right  join

例19：请查询出所有男生的学号、姓名以及他们所选修的课程号和成绩

select  stu.stuid,stuname,couid,score
from  stu  join  scores  on  stu.stuid=scores.stuid
where  sex='男';

例20：请查询出所有男生的学号、姓名以及他们所选修的课程名

select  stu.stuid,stuname,couname
from  stu  join  scores  on  stu.stuid=scores.stuidjoin   course  on  scores.couid=course.couid
where  sex='男';

例21：请查询出一门课程都没选修的学生的学号和姓名。

select  *
from  stu left  join  scores  on  stu.stuid=scores.stuid
where  couid  is  null;或
select  stuid,stuname
from  stu
where  stuid  not in(select  stuidfrom  scores)

例22：请查询出所有学生入学成绩的平均分

select  avg(rx_score)  as  入学成绩的平均分
from  stu;

例23：请查询出‘20070101’学生所选各门课程的总分

select  sum(score)  as  总分
from  scores
where  stuid='20070101';

例24：请查询出所有女生入学成绩的最高分和最低分

select  max(rx_score)  as  最高分,min(rx_score)  as  最低分
from  stu
where  sex='女';

例25：请查询出所有学生的人数

select  count(*)  as  人数
from  stu

例26：请查询出男女生各自的人数

select  sex,count(*)  as  人数
from  stu
group  by  sex;

例27：请查询出1001系男女生各自的人数

select sex,count(*) as 男女各自人数统计 
from stu 
where depid=1001 
group by sex;

例28：请从学生表中查询出各系的学生人数，格式如下：
系号        人数
……        ……

select depid,count(*)
from stu
group by depid

例29:请查询出各系男女生各自的人数，格式如下：
系号        性别     人数
……        ……     ……

select depid,sex,count(*) as  人数
from stu
group by depid,sex;

例30:请查询出各系男女生各自的人数，格式如下：
系名        性别     人数
……        ……     ……

select  depname,sex,count(*)  as  人数
from  stu  join  dep  on  stu.depid=dep.depid
group  by  depname,sex;

例31：请查询出各科成绩的平均分高于90分的学生学号

select  stuid
from  scores
group  by  stuid
having  avg(score)>90

例32：请查询出选修的课程门数超过了两门的学生学号。

select  stuid
from  scores
group  by  stuid
having  count(couid)>2

子查询：

子查询：被嵌套在其他select、update、delete、insert语句中的select语句被称为子查询

例33：请查询出选修的课程门数超过了两门的学生个人信息。

select  *
from  stu
where  stuid  in(select  stuidfrom  scoresgroup  by  stuidhaving  count(couid)>2)

例34：请查询出20070103号学生选修了而20070102号学生没选修的课程号.

select  couid
from  scores 
where  stuid='20070103'  and  couid not in(select  couid  from  scoreswhere  stuid='20070102')

例35：请查询出20070103号和20070102号学生都选修了的课程号.

select  couid
from  scores 
where  stuid='20070103'  and  couid  in(select  couid from  scores where  stuid='20070102')