数据库基本操作命令

SQL 增删改查

1增

1.1【插入单行】

insert [into] (列名) values (列值)

例:insert into Strdents (姓名,性别,出生日期) values ('开心朋朋','男','1980/6/15')

1.2将【现有表数据添加到一个已有表】

insert into (列名) select from

例：insert into tongxunlu ('姓名','地址','电子邮件')select name,address,email from Strdents

1.3直【接拿现有表数据创建一个新表并填充】

select into from

例：select name,address,email into tongxunlu from strdents

1.4使【用union关键字合并数据进行插入多行】

insert select union select

例：insert Students (姓名,性别,出生日期)

select '开心朋朋','男','1980/6/15' union（union表示下一行）

select '蓝色小明','男','19**/**/**'

2删

2.1【删除行】

delete from [where ]

例：delete from a where name='开心朋朋'（删除表a中列值为开心朋朋的行）

2.2【删除整个表】

truncate table

truncate table tongxunlu

注意：删除表的所有行，但表的结构、列、约束、索引等不会被删除；不能用语有外建约束引用的表

3改

update set [where ]

例：update tongxunlu set 年龄=18 where 姓名='蓝色小名'

4查

4.1精确（条件）查询

select from [where ] [order by [asc或desc]]

4.1.1【查询所有数据行和列】

例：select * from a

说明：查询a表中所有行和列

4.1.2【查询部分行列--条件查询】

例：select i,j,k from a where f=5

说明：查询表a中f=5的所有行，并显示i,j,k３列

4.1.3【在查询中使用ＡＳ更改列名】

例：select name as 姓名 from a where xingbie='男'

说明：查询a表中性别为男的所有行，显示name列，并将name列改名为（姓名）显示

4.1.4【查询空行】

例：select name from a where email is null

说明：查询表a中email为空的所有行，并显示name列；SQL语句中用is null或者is not null来判断是否为空行

4.1.5【在查询中使用常量】

例：select name, '唐山' as 地址 from Student

说明：查询表a，显示name列，并添加地址列，其列值都为'唐山'

4.1.6【查询返回限制行数(关键字：top percent)】

例１：select top 6 name from a

说明：查询表a，显示列name的前６行，top为关键字

例２：select top 60 percent name from a

说明：查询表a，显示列name的60%，percent为关键字

4.1.7【查询排序（关键字：order by , asc , desc）】

例：select name

from a

where chengji>=60

order by desc

说明：查询a表中chengji大于等于60的所有行，并按降序显示name列；默认为ＡＳＣ升序

4.2模糊查询

4.2.1【使用like进行模糊查询】

注意：like运算副只用于字符串，所以仅与char和varchar数据类型联合使用

例：select * from a where name like '赵%'

说明：查询显示表a中，name字段第一个字为赵的记录

4.2.2【使用between在某个范围内进行查询】

例：select * from a where nianling between 18 and 20

说明：查询显示表a中nianling在18到20之间的记录

4.2.3【使用in在列举值内进行查询】

例：select name from a where address in ('北京','上海','唐山')

说明：查询表a中address值为北京或者上海或者唐山的记录，显示name字段

4.3分组查询

4.3.1【使用group by进行分组查询】

例：select studentID as 学员编号,AVG(score) as 平均成绩 (注释:这里的score是列名)from score (注释:这里的score是表名)group by studentID

说明：在表score中查询，按strdentID字段分组，显示strdentID字段和score字段的平均值；

select语句中只允许被分组的列和为每个分组返回的一个值的表达式，例如用一个列名作为参数的聚合函数

4.3.2【使用having子句进行分组筛选】

例：select studentID as 学员编号,AVG(score) as 平均成绩 (注释:这里的score是列名)from score (注释:这里的score是表名)group by studentID having count(score)>1

说明：接上面例子，显示分组后count(score)>1的行，由于where只能在没有分组时使用，分组后只能使用having来限制条件。

4.4多表联接查询

4.4.1内联接

4.4.1.1【在where子句中指定联接条件】

例：select a.name,b.chengji from a,b where a.name=b.name

说明：查询表a和表b中name字段相等的记录，并显示表a中的name字段和表b中的chengji字段

4.4.1.2【在from子句中使用join…on】

例：select a.name,b.chengji from a inner join b on (a.name=b.name)

说明：同上

4.4.2外联接

4.4.2.1【左外联接查询】

例：select s.name,c.courseID,c.score from strdents as s left outer join score as c on s.scode=c.strdentID

说明：在strdents表和score表中查询满足on条件的行，条件为score表的strdentID与strdents表中的scode相同

4.4.2.2【右外联接查询】

例：select s.name,c.courseID,c.score

from strdents as s

right outer join score as c

on s.scode=c.strdentID

说明：在strdents表和score表中查询满足on条件的行，条件为strdents表中的sconde与score表的strdentID相同

SQL练习题

习题一、

练习1、请查询表DEPT中所有部门的情况。

select * from dept;

练习2、查询表DEPT中的部门号、部门名称两个字段的所有信息。

select deptno,dname from dept;

练习3、请从表EMP中查询10号部门工作的雇员姓名和工资。

select ename,sal from emp where deptno=10;

练习4、请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。

select ename,sal from emp where job='CLERK' or job='MANAGER';

练习5、请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。

select ename,deptno,sal,job from emp where deptno between 10 and 30;

练习6、请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。

select ename,sal,job from emp where ename like 'J%';

练习7、请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。

select ename,job,sal from emp where sal

练习8、请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。

select ename,sal,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno and job=’CLERK’;

练习9、查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。

select a.ename,b.ename from emp a,emp b where a.mgr=b.empno(+) and a.sal>=2000;

练习10、在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。

select ename,job,sal from emp where sal>(select sal from emp where ename=’JONES’);

练习11、列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。

select ename,job,deptno from emp where deptno not in (select deptno from dept);

练习12、查找工资在1000～3000之间的雇员所在部门的所有人员信息

select * from emp where deptno in (select distinct deptno from emp where sal between 1000 and 3000);

练习13、雇员中谁的工资最高。

select ename from emp where sal=(select max(sal) from emp);

select ename from (select * from emp order by sal desc) where rownum

练习14、雇员中谁的工资第二高（考虑并列第一的情况，如何处理）。

select ename,sal from (select ename ,sal from emp where sal

习题二、

练习1、查询所有雇员的姓名、SAL与COMM之和。

select ename,sal+nvl(comm,0) “sal-and-comm” from emp;

练习2、查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字

select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and hiredate

练习3、查询各部门中81年1月1日以后来的员工数

select deptno,count(*) from emp where hiredate>=to_date(‘1981-01-01’,’yyyy-mm-dd’) group by deptno;

练习4、查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名、工资

select ename,sal from emp where (job=’MANAGER’ or job=’SALES’) and deptno in (select deptno from dept where loc=’CHICAGO’);

练习5、查询列出来公司就职时间超过24年的员工名单

select ename from emp where hiredate

练习6、查询于81年来公司所有员工的总收入（SAL和COMM）

select sum(sal+nvl(comm,0)) from emp where to_char(hiredate,’yyyy’)=’1981’;

练习7、查询显示每个雇员加入公司的准确时间，按××××年××月××日 时分秒显示。

select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;

练习8、查询公司中按年份月份统计各地的录用职工数量

select to_char(hiredate,'yyyy-mm'),loc,count(*) from emp,dept where emp.deptno=dept.deptno group by to_char(hiredate,'yyyy-mm'),loc;

练习9、查询列出各部门的部门名和部门经理名字

select dname,ename from emp,dept where emp.deptno=dept.deptno and job=’MANAGER’;

练习10、查询部门平均工资最高的部门名称和最低的部门名称

select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) ) where rownum

union all select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) desc ) where rownum

练习11、查询与雇员号为7521员工的最接近的在其后进入公司的员工姓名

select ename from (select ename from(select ename from emp where hiredate>(select hiredate from emp where empno=7521) order by hiredate ) where rownum

练习12、emp表中查询每个部门工资前三的员工信息：

select rk, sal, deptno

from (select dense_rank() over(partition by t1.deptno order by t1.sums desc) rk,t.sal,t.deptno,t.empno

from scott.emp t,

(select deptno,empno,sum(sal) over(partition by deptno,empno) sums from scott.emp ) t1

where t.deptno=t1.deptno

and t.empno=t1.empno )

where rk