Mysql数据库笔记整理（练习）

创建三张表：
学生表:
create table studentinfo(
stuno int,
stuname varchar(20),
stubirth date,
stusex int,
stuaddr varchar(30),
stutel char(11)
);

insert into studentinfo values(05001,‘张三’,‘1988-12-12’,0,‘江苏南京’,‘12345’);
insert into studentinfo values(05002,‘李四’,‘1987-06-05’,1,‘上海’,‘12346’);
insert into studentinfo values(05003,‘王五’,‘1987-12-01’,0,‘北京’,‘12347’);
insert into studentinfo values(05004,‘赵六’,‘1986-02-23’,1,‘广东深圳’,‘12348’);
insert into studentinfo values(05005,‘张三’,‘1988-04-01’,0,‘重庆’,‘12349’);
insert into studentinfo values(05006,‘孙七’,‘1988-07-03’,1,‘湖北武汉’,null);

课程表:
create table classinfo(
classno int,
classname varchar(10)
);
insert into classinfo values(001,‘计算机’);
insert into classinfo values(002,‘日语’);
insert into classinfo values(003,‘英语’);

成绩表:
create table scoreinfo(
stuno int,
classno int,
score double(3,1)
);
insert into scoreinfo values(05001,001,95);
insert into scoreinfo values(05001,002,90);
insert into scoreinfo values(05001,003,88);
insert into scoreinfo values(05002,001,91);
insert into scoreinfo values(05002,002,93);
insert into scoreinfo values(05002,003,88);
insert into scoreinfo values(05003,001,95);
insert into scoreinfo values(05003,002,73);
insert into scoreinfo values(05003,003,58);
insert into scoreinfo values(05004,001,47);
insert into scoreinfo values(05004,003,61);
insert into scoreinfo values(05005,002,59);
insert into scoreinfo values(05005,003,47);

1、查询全部学生信息：
select * from studentinfo;
select * from classinfo;
select * from scoreinfo;

2、查询’张三’学生信息
select * from studentinfo where stuname=‘张三’;
select stuno,stuname,stubirth,stusex,stuaddr,stutel from studentinfo where stuname=‘张三’;

3、查询学生的姓名和联系方式，没有联系方式的提示’No Stutel’
select stuname,ifnull(stutel,‘No Stutel’) stutel from studentinfo;

4、查询学生的姓名和家庭住址，数据显示使用连接符
select concat(stuname,’(’,stuaddr,’)’) message from studentinfo;

5、查询课程号和成绩
select distinct classno,score from scoreinfo;

6、查询001课程成绩是91或95学生的学号
select stuno,classno,score from scoreinfo where classno=001 and score in(91,95);
select stuno,classno,score from scoreinfo where classno=001 and score=91 or score=95;

7、查询所有姓名包含’孙’的学生信息
select * from studentinfo where stuname like ‘%孙%’;

8、查询所有没联系方式的学生信息
select * from studentinfo where stutel is null;

9、查询所有成绩优秀(大于90)和成绩不及格(低于60)的学生学号和课程号。
select stuno,classno,score from scoreinfo where score>90 or score<60;

10、查询哪些学生有联系方式
select * from studentinfo where stutel is not null;

11、查询学生姓名的长度
select stuname,length(stuname) from studentinfo;

12、查询87年以后出生的学生信息
select * from studentinfo where stubirth>=‘1988-01-01’;

select * from studentinfo where date_format(stubirth,’%X’)>‘87’;

1、给定一个数据123.456,按照指定格式显示,要求分别用四舍五入保留2位，截取到小数点后1位
select round(123.456,2);
select truncate(123.456,1);

2、获取系统当前时间
select now();

3、按照指定要求显示时间’%X-%m-%d %H:%i:%s’
select date_format(now(),’%X-%m-%d %H:%i:%s’);

4、查询所有学生信息,按照出生日期从大到小排序
select * from studentinfo order by stubirth desc;

5、按课程号升序，同一个课程按成绩降序排序
select * from scoreinfo order by classno,score desc;

6、计算每门课程的最高分和最低分
select classno,max(score),min(score) from scoreinfo group by classno;

7、计算课程平均分大于80的课程号
select classno,avg(ifnull(score,0)) from scoreinfo group by classno having avg(ifnull(score,0))>80;

8、计算每个学生的成绩总和
select stuno,sum(score) from scoreinfo group by stuno;

9、查询学生成绩总分大于250
select stuno,sum(score) from scoreinfo group by stuno having sum(score)>250;

10、显示昨天，今天，明天的时间
昨天时间：
select adddate(now(),interval -1 day);
今天时间：
select now();
明天时间：
select adddate(now(),interval 1 day);

select adddate(now(),interval -1 day),now(),adddate(now(),interval 1 day);

1、查询每个课程最低分的学生学号
//查询每个课程最低分的学生学号
select classno,min(score) from scoreinfo group by classno;
//根据课程、分数找人

select stuno,score
from scoreinfo
where (classno,score)=any(

);

合并：
select stuno,score
from scoreinfo
where (classno,score)=any(
select classno,min(score) from scoreinfo group by classno
);

自己写：
select stuno,score
from scoreinfo
where score=any(
select min(score) from scoreinfo group by classno
);

2、查询001课程最高成绩的学生学号
select stuno,score
from scoreinfo
where (classno,score)=any(
select classno,max(score) from scoreinfo group by classno having calssno=001;
);//子查询先分组在过滤

自己写：
select stuno,score
from scoreinfo
where score=(
select max(score) from scoreinfo where classno=001
);

3、查询成绩总和比5003高的学生
5003学生成绩
select stuno,sum(score) from scoreinfo where stuno=5003;

成绩总和大于某数的学生
select sum(score) from scoreinfo group by stuno having sum(score)>();

合并：
select stuno,sum(score) from scoreinfo group by stuno having sum(score)>(
select sum(score) from scoreinfo where stuno=5003
);

4、查询’张三’有哪些课程号(若有两个’张三’)
select distinct classno from scoreinfo where stuno=any(
select stuno from studentinfo where stuname=‘张三’
);//老师，去重

获取张三学号：
select stuno from studentinfo where stuname=‘张三’;

张三学号对应课程号：
select stuno,classno from scoreinfo where stuno=‘张三学号’;

合并：
select stuno,classno from scoreinfo where stuno=any(
select stuno from studentinfo where stuname=‘张三’
);

5、查询学生有哪些课程(非关联子查询)
//满足课程表中数据能在成绩表中找到匹配记录
ps查询哪些部门有员工
select classname from classinfo where classno=any(
select distinct classno from scoreinfo where classno is not null
);

6、查询学生有哪些课程(关联子查询)
select classname from classinfo c where exists(
select 1 from scoreinfo where classno=c.classno
);

7、查询学生没有哪些课程
insert into classinfo values(004,‘网页’);
非关联：
select classname from classinfo where classno not in(
select classno from scoreinfo where classno is not null
);

关联：
select classname from classinfo c where not exists(
select 1 from scoreinfo where classno=c.classno
);

8、查询课程低于本课程平均成绩的学生
每门课平均成绩：
select avg(ifnull(score,0)) from scoreinfo group by classno;

课程低于平均成绩的学生：
select stuno,score from scoreinfo where score<

仿照：
select stuno,score,classno
from scoreinfo s
where score<(
select avg(ifnull(score,0))
from scoreinfo
where classno=s.classno
);

验证：
select classno，avg(ifnull(score,0)) from scoreinfo group by classno;

9、查询001课程低于本课程平均成绩的学生
select stuno from scoreinfo s where score<(
select avg(score) from scoreinfo where classno=s.classno and classno=001
);

自己写：
select stuno from scoreinfo where score<(
select avg(score) from scoreinfo where classno=001
) and classno=001;

10、查询课程人数比001课程人数多
select classno from scoreinfo group by classno having count(classno)>(
select count(classno) from scoreinfo where classno=1
);

mysql> select * from scoreinfo;
±------±--------±------+
| stuno | classno | score |
±------±--------±------+
| 5001 | 1 | 95.0 |
| 5001 | 2 | 90.0 |
| 5001 | 3 | 88.0 |
| 5002 | 1 | 91.0 |
| 5002 | 2 | 93.0 |
| 5002 | 3 | 88.0 |
| 5003 | 1 | 95.0 |
| 5003 | 2 | 73.0 |
| 5003 | 3 | 58.0 |
| 5004 | 1 | 47.0 |
| 5004 | 3 | 61.0 |
| 5005 | 2 | 59.0 |
| 5005 | 3 | 47.0 |
±------±--------±------+
13 rows in set (0.00 sec)

mysql> select * from classinfo;
±--------±----------+
| classno | classname |
±--------±----------+
| 1 | 计算机 |
| 2 | 日语 |
| 3 | 英语 |
±--------±----------+
3 rows in set (0.00 sec)

mysql> select * from studentinfo;
±------±--------±-----------±-------±---------±-------+
| stuno | stuname | stubirth | stusex | stuaddr | stutel |
±------±--------±-----------±-------±---------±-------+
| 5001 | 张三 | 1988-12-12 | 0 | 江苏南京 | 12345 |
| 5002 | 李四 | 1987-06-05 | 1 | 上海 | 12346 |
| 5003 | 王五 | 1987-12-01 | 0 | 北京 | 12347 |
| 5004 | 赵六 | 1986-02-23 | 1 | 广东深圳 | 12348 |
| 5005 | 张三 | 1988-04-01 | 0 | 重庆 | 12349 |
| 5006 | 孙七 | 1988-07-03 | 1 | 湖北武汉 | NULL |
±------±--------±-----------±-------±---------±-------+
6 rows in set (0.05 sec)

1、查询课程成绩最高分和课程名，按照课程名排序
select c.classname,max(score)
from classinfo c inner join scoreinfo s
where c.classno=s.classno
group by classname
order by convert(classname using gbk);

2、查询平均分大于80分的学生的姓名，按照姓名排序
select stuname,avg(ifnull(score,0))
from studentinfo s inner join scoreinfo sc
on s.stuno=sc.stuno
group by s.stuno
having avg(ifnull(score,0))>80
order by convert(stuname using gbk);

自己写：
select stuname
from studentinfo
where stuno=any(
select stuno from scoreinfo group by stuno having avg(ifnull(score,0))>80
)
order by convert(stuname using gbk);

3、'英语’课的平均分数，最低，最高分数
select classname,avg(ifnull(score,0)),max(score),min(score)
from classinfo c inner join scoreinfo s
on c.classno=s.classno
where classname=‘英语’;//关联再过滤

select classname,avg(ifnull(score,0)),max(score),min(score)
from classinfo c inner join scoreinfo s
on c.classno=s.classno
and classname=‘英语’;//带两个条件关联

自己写：
select classno from classinfo where classname=‘英语’;
//
select avg(ifnull(score,0)),max(score),min(score)
from scoreinfo
where classno=(
select classno from classinfo where classname=‘英语’
);

4、最低分比最高分低的超过40分的课程名
select classname,max(score),min(score)
from classinfo c join scoreinfo s
on c.classno=s.classno
group by c.classno
having max(score)-min(score)>40;

5、'日语’课不及格的人数
select classname,count(*)
from classinfo c join scoreinfo s
on c.classno=s.classno
where classname=‘日语’ and score<60;

自己写：
select count(*) from scoreinfo where calssno=(
select classno from classinfo where classname=‘日语’
)
and score<60;

6、查询’李四’的考试总分数
select stuname,sum(score)
from studentinfo s1 join scoreinfo s2
on s1.stuno=s2.stuno
where stuname=‘李四’;//and

自己写：
select sum(score)
from scoreinfo
where stuno=(
select stuno from studentinfo where stuname=‘李四’
);

7、查询没参加过考试的学生的姓名、性别
select stuname,stusex
from studentinfo s left outer join scoreinfo sc
on s.stuno=sc.stuno
where score is null;

自己写：
select stuname,stusex
from studentinfo s
where not exists(
select 1 from scoreinfo where stuno=s.stuno
);

8、查询每个学生的最高分数
from stuname,max(score)
from studentinfo s inner join scoreinfo sc
on s.stuno=sc.stuno
group by s.stuno;

9、查询每个学科的最低分数
select classname,min(score)
from classinfo c inner join scoreinfo s
on c.classno=s.classno
group by c.classno;

10、查询全部学生的’001’课程成绩
分析:
考试 001 85
考试 没考001 null
没有考试 null

外连接：学生表做驱动表
select stuname,classno,score
from studentinfo s left outer join scoreinfo sc
on s.stuno=sc.stuno and classno=001;

1、将员工的姓名按首字母排序，并列出名字的长度
select ename,length(ename) from emp_zhang order by convert(ename using gbk);

2、现有数据表Customer,其结构如下：
cust_id int primary key,
cname varchar(25) not null,
birthday date,
account int

//创建表：
create table customer(
cust_id int primary key,
cname varchar(25) not null,
birthday date,
account int
);
1)构造SQL语句，列出Customer数据表中
每个客户的信息。
如果客户生日未提供，则该列值
显示’not available’。
如果没有余额信息，则显示’no account’
insert into customer(cust_id,cname,birthday,account) values(10001,‘钱一鸟’,‘1987-06-16’,100);
insert into customer(cust_id,cname,birthday,account) values(10002,‘赵二傻’,‘1987-06-16’,300);
insert into customer(cust_id,cname,birthday,account) values(10003,‘张三丰’,‘1987-06-16’,500);
insert into customer(cust_id,cname,birthday,account) values(10004,‘李四狗’,‘1987-12-23’,600);
insert into customer(cust_id,cname,birthday,account) values(10005,‘王五猫’,‘1999-10-20’,250);
insert into customer(cust_id,cname,birthday,account) values(10006,‘王五猫’,null,250);
insert into customer(cust_id,cname,birthday,account) values(10007,‘王五猫’,‘2000-01-21’,null);

select cust_id,cname,ifnull(birthday,‘not available’),ifnull(account,‘not available’) from customer;

2)构造SQL语句，列出生日在1987年客户的全部信息
select cust_id,cname,birthday,account from customer where birthday like’1987%’;

3)构造SQL语句，列出客户账户的余额总数
select sum(ifnull(account,0)) sum_account from customer;

3、按照’2009-4-11 20:35:10’格式显示系统时间
select date_format(now(),‘2009-4-11 20:35:10’);

4、构造SQL语句查询员工表中员工编号、姓名，以及月收入(薪水+奖金)，注意有的员工可能
没有奖金或薪水
select empno,ename,ifnull(salary,0)+ifnull(bonus,0) money from emp_zhang;

5、列出每个员工的姓名、薪水、涨薪后工资(涨幅为8%)，元为单位进行四舍五入
select empno,ename,salary,round(salary*1.08,0) from emp_zhang;

6、查询各个职位的员工人数
select position,count(position) from emp_zhang where position is not null group by position;

7、查询员工最高薪水和最低薪水的差距，列名为DIFFERENCE
select max(salary),min(salary),max(salary)-min(salary) DIFFERENCE from emp_zhang;

8、哪一子句可实现SELECT语句查询员工平均薪水小于5000的部门信息？
A.GROUP BY dept_id WHERE
AVG(salary)<5000
B.GROUP BY AVG(salary)
HAVING AVG(salary)<5000
C.GROUP BY dept_id
HAVING AVG(salary)<5000
D.GROUP BY AVG(salary)<5000
答案：C

9、试图使用下面句子查询数据
select 100/ifnull(quantity,0)
from inventory;
quantity为空值时，将导致出错，
其原因是？
A、除数表达式为空值
B、函数参数数据类型不一致
C、空值不能被转成实际值
D、除数表达式为零除数0
答案：D

10、Student表的表结构
sid_id int
start_date date
end_date date
在start_date列上使用哪些函数是正确的？
A.sum(start_date)
B.count(start_date)
C.avg(start_date,end_date)
D.min(start_date)
答案：BD

11、当执行以下查询语句
SELECT empno,ename
from emp_xu
where empno=7782 or empno=7876;
在WHERE语句中，可以实现与OR相同功能
的操作符是：
A.IN
B.BETWEEN…AND…
C.LIKE
D.AND
答案：A

03
1、显示所有员工的姓名、部门号和部门名称
select e.ename,e.deptno,d.dname
from emp_zhang e inner join dept_zhang d
on e.deptno=d.deptno;

2、查询在’南京’工作的员工的姓名、职位、部门号、部门名称
select ename,position,e.deptno,dname,location
from emp_zhang e right outer join dept_zhang d
on e.deptno=d.deptno
where d.location=‘南京’;

3、查询所有员工的员工号、姓名以及他的领导的员工号和姓名
select e1.empno,e1.ename,e1.leader,e2.ename,e2.empno
from emp_zhang e1 inner join emp_zhang e2
on e1.leader=e2.empno;

4、查询各部门员工姓名和他们所在位置
select d.dname,e.ename,d.location
from dept_zhang d inner join emp_zhang e
on e.deptno=d.deptno;

5、查询出’张无忌’的领导是谁
select ename
from emp_zhang
where empno in(
select leader from emp_zhang where ename=‘张无忌’
);

6、'张三丰’领导谁
select ename
from emp_zhang
where empno in(
select leader from emp_zhang where ename=‘张三丰’
);

7、查询和’张三丰’相同部门的员工姓名和雇佣日期
select ename,hiredate
from emp_zhang
where deptno=(
select deptno from emp_zhang where ename=‘张三丰’
);

8、查询薪水比公司平均薪水高的所有员工的员工号、姓名和薪水
select empno,ename,salary
from emp_zhang
where salary>(
select avg(ifnull(salary,0)) from emp_zhang
);

9、查询和姓名中包含’张’的员工在相同部门的员工的员工号和姓名
select empno,ename
from emp_zhang
where deptno=any(
select deptno from emp_zhang where ename like’%张%’ and deptno is not null
);

10、查询领导是’张三丰’的员工姓名和薪水
select ename,salary from emp_zhang where leader=(
select empno from emp_zhang where ename=‘张三丰’
);

11、显示’研发部’有哪些职位
select distinct position from emp_zhang where deptno=(
select deptno from dept_zhang where dname=‘研发部’
);

12、各个部门中薪水大于5000的员工人数
select deptno,count(*) from emp_zhang where salary>5000 group by deptno having deptno is not null;

13、所在部门平均薪水高于5000的员工名字
select ename from emp_zhang where deptno=any(
select deptno from emp_zhang where deptno is not null group by deptno having avg(salary)>5000
);

14、列出各个部门中薪水最高的员工的姓名、部门号和薪水
select ename,deptno,salary from emp_zhang where deptno is not null group by deptno having max(salary);

15、下列哪个组合查询返回两个查询所选择的所有的行
A.Union
B.Union all
C.Union only
D.connect by
答案：B

16、从员工表的’姓名’字段中找出名字包含’玛丽’的人，下面哪条SELECT语句正确
A.Select * from 员工 where 姓名=‘玛丽’;
B.Select * from 员工 where 姓名=’%玛丽_’;
C.Select * from 员工 where 姓名 LIKE ‘_玛丽%’;
D.Select * from 员工 where 姓名 LIKE ‘%玛丽%’;
答案：D

17、检查下列数据表中的数据
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5500
下面子查询正确的是
A.select * from emp where salary>(select min(salary) from emp group by department_id);
B.select * from emp where salary=(select avg(salary) from emp group by department_id);
C.select department_id from emp where salary>all(select avg(salary) from emp group by department_id);
D.select last_name from emp where salary>any(select max(salary) from emp group by department_id);
答案：C

04
1、创建表employee,字段为：
id int
first_name varchar(20)
last_name varchar(20)
mgrid int
job varchar(20)
salary double(7,2)

创建表：
create table employee(
id int,
first_name varchar(20),
last_name varchar(20),
mgrid int,
job varchar(20),
salary double(7,2)
);

2、向表中插入下列数据，并提交，查询数据
id first_name last_name mgrid salary
1 Rose Tyler 4 1500
2 Matha Jones 4 2200
3 Donna Noble 4 1300
4 Doctor Who 3500
5 Jack Harkness 1 3000

插入数据：
insert into employee values(1,‘Rose’,‘Tyler’,4,null,1500);
insert into employee values(2,‘Matha’,‘Jones’,4,null,2200);
insert into employee values(3,‘Donna’,‘Noble’,4,null,1300);
insert into employee values(4,‘Doctor’,‘Who’,null,null,3500);
insert into employee values(5,‘Jack’,‘Harkness’,1,null,3000);

commit;
select * from employee;

3、将3号员工的last_name修改为’Tate’,并提交查询数据
update employee set last_name=‘Tate’ where id=3;

4、将所有工资小于5000的员工的工资修改为5000(不提交)，并设置保存点，查询数据
set autocommit=0;
update employee set salary=5000 where salary<5000;
savepoint A;
select * from employee;

5、删除employee表中所有数据(不提交)查询数据
delete from employee;
select * from employee;

6、回滚到设置的保存点，查询数据
rollback to A;
select * from employee;

7、删除表employee中所有数据，并提交，查询数据
drop table employee;

8、现有数据Customer,其结构如下所示：
cust_id int primary key,客户编码
cname varchar(25) not null,客户姓名
csex char(6),客户性别
birthday date,客户生日
account double 客户账户余额
1)创建表，客户编码为主键，姓名不能为空
create table Customer(
cust_id int primary key,
cname varchar(25) not null,
csex char(6),
birthday date,
account double
);

2)构造SQL语句，向Customer数据表中插入一条记录，其信息
如下：客户编码为1031，客户姓名为sean,
性别为’男’，
生日为’1987-11-17’,
账户余额为12345元
insert into employee values(1031,‘sean’,‘男’,‘1987-11-17’,12345);

9、下面语句错误的原因是
update departments
set department_id=300
where department_id=60;
Error位于第1行：
ORA-02292:违反完整约束条件(
HR.EMP_DEPT_FK)-已找到
子记录日志
A.where条件语法错误
B.违反主键约束，department_id为300的记录已经存在
C.60号部门下已经有员工，
修改60号部门编号将违反完整性约束条件
D.60号部门不存在
答案：

10、下面创建表的语句错在哪里？
create table abc as
select last_name,salary12 from
employees;
A.create table abc
B.as
C.select last_name,salary12
D.from employees;
答案: B

11、下面修改表名字的命令是
A.ALTER TABLE RENAME
B.RENAME
C.ALTER TABLE MOVE
D.ALTER TABLE MODIFY
答案：A

12、下面哪个SQL命令是用于清空表中数据的DDL语句：
A.UPDATE
B.DELETE
C.TRUNCATE
D.SELECT
答案：C

13、表TEACHER包含如下字段
列名 可为空否？ 数据类型
TEACHER_ID NOT NULL int
NAME VARCHAR(25)
SALARY double(7,2)
SUBJECT_ID NOT NULL int
SUBJECT_DESCRIPTION VARCHAR(2)
现需要将理科教师的工资上浮8%，
理科教师的SUBJECT_ID是011，
需要哪一句实现？
A.update teacher salary=salary1.08 where subject_id=011
B.update teacher set salary=salary1.08 where subject_id==011
C.update teacher set salary=salary1.08 where subject_id=011
D.update teacher set salary=salary1.08 where subject_id like ‘%011%’
答案：c

14、有如下SQL片段
delete from emp e
where e.hiredate>‘14-Dec-09’
and e.salary<>‘1’
其含义为：
A.从表emp中删除hiredate不小于2009年12月14日，且salary不为1的记录，一定不可恢复
B.从表emp中删除hiredate和salary列
C.对表emp中hiredate不小于2009年12月14日，且salary不为1的记录实施删除
D.删除emp表的全部记录
答案：C

15、SQL语言中修改表结构的命令是？
A.MODIFY TABLE
B.MODIFY STRUCTURE
C.ALTER TABLE
D.ALTER STRUCTURE
答案：C

16、Delete和truncate都可以用来删除表内容，以下描述正确的是？
A.Truncate不需要rollback内存区域
B.Delete需要rollback segment
C.Truncate在提交commit之前仍可回滚
D.Truncate还可以删除表结构
答案：B

17、下列属于DML语句的是？
A.commit
B.update
C.delete
D.create
答案：C

18、EMPLOYEES表的结构如下：
employee_id int primary key
first_name varchar(25)
last_name varchar(25)
下面选项中可以向该表中插入一行数据的是
A.insert into employees(employee_id)
values(1000);
B.insert into employees(
first_name,last_name)
values(‘John’,‘Smith’);//auto_increment表示自增
C.insert into employees
values(1000,‘John’,‘null’);
D.insert into employees
values(‘1000’,‘John’);
答案：B

05
1、创建一个视图v_emp,内容是按部门分组，各个部门的薪水总和和员工人数；
视图字段定义为deptno,total_sal,
total_count
select deptno,sum(salary),count(*)
from emp_xu
where deptno is not null
group by deptno;

//创建视图
create view v_emp(
total_count
select deptno,sum(salary),count(*)
from emp_xu
where deptno is not null
group by deptno;
);

2、查询视图v_emp,列出全部记录
select * from v_emp;

3、删除视图v_emp
drop view v_emp;

4、下面约束只能定义在列级的是
A.CHECK
B.UNIQUE
C.NOT NULL
D.FOREIGN KEY
E.PRIMARY KEY
答案：B

5、下面不是SQL语句的是
A.DESC
B.ALTER TABLE ADD…
C.SELECT * FROM TABLE
D.CONNECT
答案：D

6、下面对视图的作用描述正确的是
A.视图可以加速数据访问
B.视图可以屏蔽掉队部分原始数据的访问
C.视图可以降低查询复杂度
D.视图可以代替原始数据表
答案：C

7、下列关于主键说法正确的是
A.一个表只允许一个主键
B.一个表可以有多个主键
C.数据库会为主键自动创建对应的唯一索引
D.主键只表示该字段不允许为空
答案：A