经典sql-高级-实验三

实验三、

1．建立一个表（表名自定），表结构与EMP相同，没有任何记录。

create table my_emp as select * from emp;

2．用Insert语句输入5条记录，并提交。

3．扩大该表的记录数到约40条，并使雇员号不重复；每个雇员都有所属部门，雇员在同一部门的经理是同一人。

insert….

update…

commit

4．建立一个与DEPT表结构和记录完全相同的新表，并与前项新表建立参照完整性约束。

alter table my_dept add( constraint s1 primary key(deptno));

alter table my_emp add(constraint s2 foreign key(deptno) references dept(deptno));

5．对在‘NEW YORK’工作的雇员加工资，每人加200。

6． *如果雇员姓名与部门名称中有一个或一个以上相同的字母，则该雇员的COMM增加500。

update my_emp a

set comm=NVL(comm,0)+500

where a.ename<>(

select translate(a.ename,b.dname,CHR(27))

from my_dept b where b.deptno=a.deptno

);

--a.deptno与b.deptno必须有主外键连接，否则可能出错，为什么？commit;

7．删除部门号为30的记录，并删除该部门的所有成员。

delete from emp where deptno=30;

delete from dept where deptno=30;

commit

8．新增列性别SEX，字符型。

alter table emp add(sex char(2));

9．修改新雇员表中的MGR列，为字符型。

该列数据必须为空

alter table emp modify(mgr varchar2(20));

10．试着去删除新表中的一个列。

alter table my_emp drop (comm);

实验四、

1．查询部门号为30的所有人员的管理层次图。

select level,ename from emp

connect by mgr=prior empno

start with deptno=30and job='MANAGER';

2．查询员工SMITH的各个层次领导。

select level,ename from emp

connect by prior mgr= empno

start with ENAME='SMITH';

3．查询显示EMP表各雇员的工作类型，并翻译为中文显示

用decode函数

4． *查询显示雇员进入公司当年是什么属相年（不考虑农历的年份算法）用decode函数

5．建立一个视图myV_emp，视图包括myEMP表的empno、ename、sal，并按sal 从大到小排列。

create view myV_EMP as select empno,ename,sal from emp;

6．定义一个mySeq，对select mySeq.nextval,my_emp.* from my_emp的执行结果进行说明。

7．定义序列mySeq、myEMP、myV_emp的同义词，能否用同义词对上述对象进行访问。

8．在myEMP表中建立ename的唯一性索引。

9．如何在sql*plus中，运行sql的脚本（即后缀为.sql的文件）

实验五、

1．观察下列PL/SQL的执行结果

declare

s emp%rowtype;

begin

select * into s

from emp

where ename='KING';

DBMS_OUTPUT.PUT_LINE(s.empno||s.ename||s.job||s.sal);

END;

2．编写一个PL/SQL，显示ASC码值从32至120的字符。

begin

for i in32..120

loop

dbms_output.put_line(chr(i));

end loop;

end;

3．计算myEMP表中COMM最高与最低的差值，COMM值为空时按0计算。declare

var1 number;

var2 number;

val_comm number;

begin

select max(nvl(comm,0)) into var1 from myemp;

select min(nvl(comm,0)) into var2 from myemp;

val_comm:=var1-var2;

dbms_output.put_line(val_comm);

end;

4．根据表myEMP中deptno字段的值，为姓名为‘JONES’的雇员修改工资；若部门号为10，则工资加100；部门号为20，加200；其他部门加400。

declare

c1 number;

c2 number;

begin

select deptno into c1 from emp where ename=’JONES’;

if c1=10then

c2:=100;

elsif c1=20then

c2:=200;

else c2:=400;

end if;

update emp set sal=sal+c2 where ename=’JONES’;

commit;

end;

5．计算显示部门人数最多的部门号、人数、工资总和，以及部门人数最少的部门号、人数、工资总和。

6．计算myEMP中所有雇员的所得税总和。假设所得税为累进税率，所得税算法为：工资收入为0－1000为免税；收入1000－2000者，超过1000的部分税率10％；2000－3000者超过2000部分按20％税率计算；3000－4000者超过3000部分按30％税率计算；4000以上收入，超过4000部分按40％税率计算。（请查阅累进税率的概念）

declare

sum_xx number:=0;

xx number;

begin

--计算收入为1000－2000的所得税总额

select sum((sal-1000)*0.1) into xx from emp where sal >1000and sal<=2000; sum_xx:=sum_xx+xx;

--计算收入为2000－3000的所得税总额

select sum((sal-2000)*0.2+100) into xx from emp where sal >2000and sal<=3000;

sum_xx:=sum_xx+xx;

--计算收入为3000－4000的所得税总额

select sum((sal-3000)*0.3+300) into xx from emp where sal >3000and sal<=4000;

sum_xx:=sum_xx+xx;

--计算收入为4000以上的所得税总额

select sum((sal-4000)*0.4+600) into xx from emp where sal >4000; sum_xx:=sum_xx+xx;

dbms_output.put_line(sum_xx);

end;

7． *（可选做，难题）假设有个表如myEMP，未建立主键，含有多条记录重复（列值完全相同），试编制一个PL/SQL，将多余的重复记录删除。

实验六、

1．用外部变量，实现两个PL/SQL程序间的数据交换。

SQL> variable a1 number;

SQL> begin

2 :a1:=1000;

3end;

4 /

PL/SQL过程已成功完成。

SQL> begin

2 dbms_output.put_line(:a1);

3end;

4 /

1000

PL/SQL过程已成功完成。

2．插入myEMP表中的数据记录，考虑可能出现的例外，并提示。

主要的例外提示：唯一性索引值重复DUP_VAL_ON_INDEX

3．删除myDEPT表中的数据记录一条，考虑例外情况，并提示。

主要的例外提示：违反完整约束条件

4．将下列PL/SQL改为FOR游标

declare

cursor cur_myemp is select * from emp;

r emp%rowtype;

begin

open cur_myemp;

fetch cur_myemp into r;

while cur_myemp%found

dbms_output.put_line(r.ename);

fetch cur_myemp into r;

end loop;

close cur_myemp;

end;

5．工资级别的表salgrade，列出各工资级别的人数。（用游标来完成）declare

v1 number;

cursor cur1 is select * from salgrade;

begin

for c1 in cur1

loop

select count(*) into v1 from emp where sal between c1.losal and c1.hisal;

dbms_output.put_line('grade'||c1.grade||' '||v1);

end loop;

end;

实验七、

1．在myEMP表中增加一个字段，字段名为EMPPASS，类型为可变长字符。2．建立一个存储过程，用于操作用户登录的校验，登录需要使用EMPNO和EMPPASS，并需要提示登录中的错误，如是EMPNO不存在，还是EMPNO存在而是EMPPASS错误等。

create or replace procedure p_login(

in_empno in emp.empno%type,

in_emppass in emp.emppass%type,

out_code out number,

out_desc out varchar2)

is

x1 emp.ename%type;

x2 number;

begin

select ename into x1 from emp where empno=in_empno;

select count(*) into x2 from emp where empno=in_empno and emppass=in_emppass;

if x2=1then

out_code:=0;

out_desc:=x1;

else

out_code:=2;

out_desc:=’用户登陆密码错误！’;

end if;

when NO_DATA_FOUND then

out_code:=1;

out_desc:=’该用户号存在!’;

when TOO_MANY_ROWS then

out_code:=3;

out_desc:=’该用户号有重复值!’;

when others then

out_code:=100;

out_desc:=’其他错误!’;

end;

3．建立一个存储过程，实现myEMP表中指定雇员的EMPPASS字段的修改，修改前必须进行EMPPASS旧值的核对。

Create or REPLACE PROCEDURE P_CHANGEPASS(

IN_EMPNO IN EMP.EMPNO%TYPE,

IN_OLDPASS IN EMP.EMPPASS%TYPE,

IN_NEWPASS IN EMP.EMPPASS%TYPE,

OUT_CODE OUT NUMBER,

OUT_DESC OUT VARCHAR2)

IS

X1 NUMBER;

BEGIN

Select COUNT(*) INTO X1 FROM EMP Where EMPNO=IN_EMPNO AND EMPPASS=IN_OLDPASS;

IF X1=1THEN

update emp set emppass=in_newpass where empno=in_empno;

commit;

OUT_CODE:=0;

OUT_DESC:=’修改口令成功’;

ELSE

OUT_CODE:=1;

OUT_DESC:=’修改口令不成功’;

END IF;

exception

when others then

out_code:=100;

out_desc:=’其他错误’;

END;

4．建立一个函数，输入一个雇员号，返回该雇员的所在同一部门的最高级别上司姓名。

create or replace function f_leader(

in_empno in emp.empno%type) return varchar2

is

v1 number;

v2 number;

v3 emp.ename%type;

v4 emp.deptno%type;

begin

v1:=in_empno;

v3:='未找到';

select deptno into v4 from emp where empno=v1;

loop

select mgr into v2 from emp where empno=v1;

select ename into v3 from emp where empno=v2 and deptno=v4;

v1:=v2;

end loop;

exception

when others then

return v3;

end;

5．试用上题函数，实现各雇员的同一部门最高级别上司的Select查询。select f_leader(7521) from dual;

6． *编写实验五中第六题，关于各雇员工资的所得税计算函数

实验八、

1．建立一个触发器，当myEMP表中部门号存在时，该部门不允许删除。create or replace trigger dept_line_delete

before delete on dept for each row

declare

v1 number;

begin

select count(*) into v1 from emp where deptno=:old.deptno;

if v1>=1then RAISE_APPLICATION_ERROR(-20000,’错误’);

end if;

end;

实验九、

1．建立一个示例包emp_mgmt中，新增一个修改雇员所在部门的过程。create or replace package emp_mgmt as

procedure change_dept(

in_newdept in emp.deptno%type,

out_code out number,

out_desc out varchar2);

mgmt_empno emp.empno%type;

procedure mgmt_login(

in_empno in emp.empno%type,

in_emppass in emp.emppass%type,

out_code out number,

out_desc out varchar2);

end;

create or replace package body emp_mgmt as

procedure change_dept(

in_newdept in emp.deptno%type,

out_code out number,

out_desc out varchar2)

is

begin

update emp set deptno=in_newdept where empno=mgmt_empno;

commit;

out_code:=0;

out_desc:=’ok’;

end;

procedure mgmt_login(

in_empno in emp.empno%type,

in_emppass in emp.emppass%type,

out_code out number,

out_desc out varchar2)

is

begin

--登陆过程见实验七第2题

mgmt_empno:=in_empno;

out_code:=0;

out_desc:=’ok’;

end;

end;

2．假设myEMP表中有口令字段password，试在包emp_mgmt中建立一个登录的过程，并将登录成功的雇员号存入包变量。

见前一题

3．示例包emp_mgmt中，将remove_emp操作设限，只有本部门经理操作才能删除本部门雇员记录，只有公司头头PRESIDENT才能删除部门经理的雇员记录。--

procedure remove_emp(

remove_empno emp.empno%type,

out_code number,

out_desc varchar2)

is

x emp.job%type;

y number;

begin

select job,deptno into x,y from emp where empno=mgmt_empno;

if x=’PRESIDENT’then

delete from emp where empno=remove_empno and job=’MANAGER’;

else

delete from emp where empno=remove_empno and deptno=y and x=’MANAGER’; end if

if sql%found then

out_code:=0;

out_desc:=’ok’;

else

out_code:=1;

out_desc:=’未删除记录’;

end if;

commit;

end;

4． *用DELPHI＋ORACLE实现上题的软件功能。

实验十

1．编写一段PL/SQL，利用系统工具包，实现对SERVER端数据文件D:\DATA\A.TXT的读取输出至缓冲区。

2．编写一个存储过程，就myEMP表，输入参数为字段名和匹配值（字符型），对符合匹配条件的工资加100。

3．编写一个存储过程，输入参数为一个表名，通过存储过程处理将该表删除Drop，并返回是否成功的信息。

实验十一

1．以雇员作为对象类型，试根据myEMP表结构设计其属性，方法主要有雇员更换部门、更换工种、MAP排序的定义。

2．编制一个雇员类型的对象表myOBJ_EMP。

3．添加对象表myOBJ_EMP的数据10条。

4．试对对象表排序输出。

5．给对象表中部门号为20的记录的工资增加10％。

6．显示每个雇员所在的雇员名、部门名称。

常用SQL语句大全

常用SQL语句大全 一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 DROP database dbname 3、说明：备份sql server --- 创建备份数据的device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2…from tab_old definition only 5、说明：删除新表 DROP table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col) 说明：删除主键：Alter table tabname DROP primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：DROP index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：DROP view viewname 10、说明：几个简单的基本的sql语句 选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ’%value1%’---like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 [desc] 总数：select count as totalcount from table1 求和：select sum(field1) as sumvalue from table1 平均：select avg(field1) as avgvalue from table1 最大：select max(field1) as maxvalue from table1 最小：select min(field1) as minvalue from table1 11、说明：几个高级查询运算词

数据库实验3答案

实验三：交互式SQL语句的使用 1、实验目的 （1）掌握数据库对象的操作过程，包括创建、修改、删除 （2）熟悉表的各种操作，包括插入、修改、删除、查询 （3）熟练掌握常用SQL语句的基本语法 2、实验平台 使用SQL Server提供的Microsoft SQL Server Management Studio工具，交互式使用SQL语句。 3 实验容及要求 选择如下一个应用背景之一： ●学生选课系统 ●习题3、4、和5中使用的数据库 ●其它你熟悉的应用 （1）建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作。 （2）要求认真进行实验，记录各实验用例及执行结果。 （3）深入了解各个操作的功能。 实验要求包括如下方面的容： 3.1 数据定义 1．基本表的创建、修改及删除 2．索引的创建 3．视图的创建 3.2 数据操作 完成各类更新操作包括： 1．插入数据

2．修改数据 3. 删除数据 3.3 数据查询操作 完成各类查询操作 1．单表查询 2．分组统计 3. 连接查询 4. 嵌套查询 5. 集合查询 3.4 数据操作 1．创建视图 2．视图查询 参考示例： 建立一个学生选课数据库，练习对表、视图和索引等数据库对象的各种操作。 一、数据定义 创建学生选课数据库ST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表： 表1. Student表结构 表2. Course表结构

表3. SC表结构 1．创建、修改及删除基本表 （1）创建Student表 CREATE TABLE Student (Sno CHAR(8)PRIMARY KEY, Sname CHAR(8), Ssex CHAR(2)NOT NULL, Sage INT, Sdept CHAR(20) ); （2）创建Course表 CREATE TABLE Course (Cno CHAR(4)PRIMARY KEY, Cname CHAR(40)NOT NULL, Cpno CHAR(4), Ccredit SMALLINT, ); （3）创建SC表 CREATE TABLE SC (Sno CHAR(8)FOREIGN KEY (Sno)REFERENCES Student(Sno), Cno CHAR(4), Grade SMALLINT, ); （4）创建员工表Employee

SQL常用语法及例子精简——快速入手

sql语言 库表的增删改查常用语及语法 (1)数据记录筛选： sql="select*from 数据表where 字段名=字段值orderby字段名[desc]" sql="select*from 数据表where 字段名like'%字段值%'orderby字段名[desc]" sql="selecttop10*from 数据表where 字段名orderby字段名[desc]" sql="select*from 数据表where 字段名in('值1','值2','值3')" sql="select*from 数据表where 字段名between 值1and 值2" (2)更新数据记录： sql="update 数据表set 字段名=字段值where 条件表达式" sql="update 数据表set 字段1=值1,字段2=值2……字段n=值nwhere条件表达式" (3)删除数据记录： sql="de letefrom数据表where 条件表达式" sql="de letefrom数据表"(将数据表所有记录删除) (4)添加数据记录： sql="insertinto数据表(字段1,字段2,字段3…)values(值1,值2,值3…)" sql="insertinto目标数据表select*from 源数据表"(把源数据表的记录添加到目标数据表) (5)数据记录统计函数： AVG(字段名)得出一个表格栏平均值 COUNT(*|字段名)对数据行数的统计或对某一栏有值的数据行数统计 MAX(字段名)取得一个表格栏最大的值 MIN(字段名)取得一个表格栏最小的值 SUM(字段名)把数据栏的值相加 引用以上函数的方法： sql="selectsum(字段名)as 别名from 数据表where 条件表达式" setrs=conn.excute(sql) 用rs("别名")获取统的计值，其它函数运用同上。 (6)数据表的建立和删除： CREATETABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度)……) 例：CREATETABLEtab01 (namevarchar (50), datetimedefaultnow ()) DROPTABLE 数据表名称(永久性删除一个数据表) 4.记录集对象的方法： rs.movenext将记录指针从当前的位置向下移一行 rs.moveprevious将记录指针从当前的位置向上移一行 rs.movefirst将记录指针移到数据表第一行 rs.movelast将记录指针移到数据表最后一行 rs.absoluteposition=N 将记录指针移到数据表第N 行 rs.absolutepage=N 将记录指针移到第N 页的第一行 rs.pagesize=N 设置每页为N 条记录 rs.pagecount根据pagesize的设置返回总页数 rs.recordcount返回记录总数 rs.bof返回记录指针是否超出数据表首端，true 表示是，false 为否

sql实验5(一)

实验报告专用纸 实验5 索引和视图 三、实验内容 1．分别使用企业管理器和Transact-SQL语句为studentsdb数据库的student_info和curriculum创建主键索引。 ①、使用企业管理器为studentsdb数据库的student_info创建主键索引。 ②使用企业管理器为studentsdb数据库的curriculum创建主键索引。（将课程表的课程编号属性设为主键，系统将自动创建主键索引） ③使用Transact-SQL语句为studentsdb数据库的student_info创建主键索引。（将先前创建好的主键移除） create clustered index student_info_idx on student_info(学号)

④使用Transact-SQL语句为studentsdb数据库的curriculum创建主键索引。（将先前创建好的主键移除） create clustered index curriculum_idx on curriculum(课程编号) 2．使用企业管理器按curriculum的课程编号列创建唯一性索引。

3．分别使用企业管理器和Transact-SQL语句为studentsdb数据库的grade表的“分数”字段创建一个非聚集索引，命名为grade_index

①使用企业管理器为studentsdb数据库的grade表的“分数”字段创建一个非聚集索引。 ②使用Transact-SQL语句为studentsdb数据库的grade表的“分数”字段创建一个非聚集索引。（删除之前所创建的索引）

SQL语句实例

表操作 例1 对于表的教学管理数据库中的表STUDENTS，可以定义如下：CREATE TABLE STUDENTS, ( SNO NUMERIC (6, 0) NOT NULL, SNAME CHAR (8) NOT NULL, AGE NUMERIC(3,0), SEX CHAR(2), BPLACE CHAR(20), PRIMARY KEY(SNO) ) 例2 对于表的教学管理数据库中的表ENROLLS，可以定义如下：CREATE TABLE ENROLLS ( SNO NUMERIC(6,0) NOT NULL, CNO CHAR(4) NOT NULL, GRADE INT, PRIMARY KEY(SNO,CNO), FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO), FOREIGN KEY(CNO) REFERENCES COURSES(CNO), CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100)) ) 例3 根据表的STUDENTS 表，建立一个只包含学号、姓名、年龄的女学生表。CREATE TABLE GIRL AS SELECT SNO, SNAME, AGE FROM STUDENTS WHERE SEX='女'; 例4 删除教师表TEACHER。 DROP TABLE TEACHER 例5 在教师表中增加住址列。 ALTER TABLE TEACHERS ADD (ADDR CHAR(50))

例6 把STUDENTS表中的BPLACE列删除，并且把引用BPLACE列的所有视图和约束也一起删除。 ALTER TABLE STUDENTS DROP BPLACE CASCADE 例7 补充定义ENROLLS表的主关键字。 ALTER TABLE ENROLLS ADD PRIMARY KEY (SNO,CNO)； 视图操作（虚表） 例9 建立一个只包括教师号、姓名和年龄的视图FACULTY。(在视图定义中不能包含ORDER BY子句) CREATE VIEW FACULTY AS SELECT TNO, TNAME, AGE FROM TEACHERS 例10 从学生表、课程表和选课表中产生一个视图GRADE_TABLE，它包括学生姓名、课程名和成绩。 CREATE VIEW GRADE_TABLE AS SELECT SNAME,CNAME,GRADE FROM STUDENTS,COURSES,ENROLLS WHERE STUDENTS.SNO＝ENROLLS.SNO AND https://www.wendangku.net/doc/7e13766250.html,O=https://www.wendangku.net/doc/7e13766250.html,O 例11 删除视图GRADE_TABLE DROP VIEW GRADE_TABLE RESTRICT 索引操作 例12 在学生表中按学号建立索引。 CREATE UNIQUE INDEX ST ON STUDENTS (SNO,ASC) 例13 删除按学号所建立的索引。 DROP INDEX ST 数据库模式操作 例14 创建一个简易教学数据库的数据库模式TEACHING_DB，属主为ZHANG。 CREATE SCHEMA TEACHING_DB AUTHRIZATION ZHANG 例15 删除简易教学数据库模式TEACHING_DB。（（1）选用CASCADE，即当删除数据库模式时，则本数据库模式和其下属的基本表、视图、索引等全

SQL语句大全实例

SQL语句实例 表操作 例 1 对于表的教学管理数据库中的表STUDENTS ，可以定义如下：CREATE TABLE STUDENTS (SNO NUMERIC (6, 0) NOT NULL SNAME CHAR (8) NOT NULL AGE NUMERIC(3,0) SEX CHAR(2) BPLACE CHAR(20) PRIMARY KEY(SNO)) 例 2 对于表的教学管理数据库中的表ENROLLS ，可以定义如下： CREATE TABLE ENROLLS (SNO NUMERIC(6,0) NOT NULL CNO CHAR(4) NOT NULL GRADE INT PRIMARY KEY(SNO,CNO) FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO) FOREIGN KEY(CNO) REFERENCES COURSES(CNO) CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100))) 例 3 根据表的STUDENTS 表，建立一个只包含学号、姓名、年龄的女学生表。 CREATE TABLE GIRL

AS SELECT SNO, SNAME, AGE FROM STUDENTS WHERE SEX=' 女'; 例 4 删除教师表TEACHER 。 DROP TABLE TEACHER 例 5 在教师表中增加住址列。 ALTER TABLE TEACHERS ADD (ADDR CHAR(50)) 例 6 把STUDENTS 表中的BPLACE 列删除，并且把引用BPLACE 列的所有视图和约束也一起删除。 ALTER TABLE STUDENTS DROP BPLACE CASCADE 例7 补充定义ENROLLS 表的主关键字。 ALTER TABLE ENROLLS ADD PRIMARY KEY (SNO,CNO) ； 视图操作（虚表） 例9 建立一个只包括教师号、姓名和年龄的视图FACULTY 。( 在视图定义中不能包含ORDER BY 子句) CREATE VIEW FACULTY AS SELECT TNO, TNAME, AGE FROM TEACHERS 例10 从学生表、课程表和选课表中产生一个视图GRADE_TABLE ，它包括学生姓名、课程名和成绩。 CREATE VIEW GRADE_TABLE AS SELECT SNAME,CNAME,GRADE FROM STUDENTS,COURSES,ENROLLS WHERE STUDENTS.SNO ＝ENROLLS.SNO AND https://www.wendangku.net/doc/7e13766250.html,O=https://www.wendangku.net/doc/7e13766250.html,O 例11 删除视图GRADE_TABLE DROP VIEW GRADE_TABLE RESTRICT 索引操作 例12 在学生表中按学号建立索引。 CREATE UNIQUE INDEX ST ON STUDENTS (SNO,ASC) 例13 删除按学号所建立的索引。 DROP INDEX ST 数据库模式操作

SQL常用语句+举例

SQL 常用语句+举例 相关表： 1. distinct: 剔除重复记录 例：select distinct stroe_name from Store_information 结果： 2. And / or: 并且/或 例：在表中选出所有sales 高于$1000或是sales 在$275及$500之间的记录 Select store_name ,sales from Store_information Where sales>1000 Or (sales>275 and sales <500) 3. 例：在表中查找store_name 包含 Los Angeles 或San Diego 的记录 Select * from Store_information where store_name in (‘Los Angeles ’,’San Diego ’) 结果： 4. Between : 可以运用一个范围抓出表中的值

与in 的区别：in 依照一个或数个不连续的值的限制抓出表中的值 例：查找表中介于Jan-06-1999 及Jan-10-1999 中的记录 Select * from Store_information where date between ‘Jan-06-1999’ and ‘Jan-10-1999’ 结果： 5. Like : 让我们依据一个套式来找出我们要的记录 套式通常包含： ’A_Z ’: 所有以A 开头，中间包含一个字符，以Z 结尾的字串 ’ABC%’: 所有以ABC 起头的字串 ’%XYZ ’: 所有以XYZ 结尾的字串 ’%AN%’: 所有包含AN 的字串 例：Select * from Store_information where store_name like ‘%An%’ 结果： 6. Order by: 排序，通常与ASC （从小到大，升序）、DESC （从大到小，降序）结合使用 当排序字段不止一个时，先依据字段1排序，当字段1有几个值相同时，再依据字段2排序 例：表中sales 由大到小列出Store_information 的所有记录 Select Store_name, sales,date from Store_information order by sales desc 结果： 7. 函数：AVG （平均值）、COUNT （计数）、MAX （最大值）、MIN （最小值）、SUM(求和) 语句：select 函数名(字段名) from 表名 例：求出sales 的总和 Select sum(sales) from Store_information 结果 8. COUNT （计数） 例：找出Store_information 表中 有几个store_name 值不是空的记录

实验3 SQL基本操作

实验3 SQL概述 一、实验目的 1.掌握使用企业管理器创建数据库、定义表结构、修改表、删除表的操作 2.创建视图，修改、删除视图。 二、实验内容 1.使用企业管理器创建数据库CPXS； 2.修改数据库、删除数据库； 3.在数据库CPXS中创建表； 4.创建查询产品库存量和销售时间的视图； 5.删除视图，修改视图。 三、实验步骤 1.使用企业管理器创建数据库 （1）进入企业管理器主界面，右击“数据库”文件夹，在弹出的快捷菜单中选择“新建数据库”命令。 （2）弹出“数据库属性”对话框，其中有“常规”、“数据文件”、“事务日志”3个选项卡。 （3）在“常规”选项卡中的“名称”对话框中输入要建立的数据库名称，这里只能输入字母不能输入汉字，如要建立产品销售数据库，可以输入“CPXS”。如图1所示。

图1 输入数据库名称cpxs （4）选择“数据文件”选项卡，对数据文件的逻辑名称、存储位置、容量大小、所属文件组名称和文件属性进行设置。例如，设置数据库的数据文件的逻辑名为cpxs_data，开始大小为1MB，所属文件组为Primary，文件按15%的比例自动增长，文件最大增长到15MB。

图2 设置cpxs属性 （5）选择“事务日志”选项卡，对事务日志文件的物理存储进行设置，可以输入事务日志文件的名称、存储位置、初始大小和文件属性等信息。如设置cpxs数据库的事务日志文件名是cpxs_Log，初始大小为 1MB，文件按5%的比例自动增长，文件最大增长到5MB。 （6）单击“确定”按钮，完成数据库的创建后，会在“数据库”文件夹内出现新建的cpxs的数据库。 2.修改数据库 （1）在企业管理器中右击cpxs，从弹出的快捷菜单中选择“属性”命令。 （2）弹出该数据库的属性对话框，在其中可以对常规、数据文件、事务日志、文件组、选项和权限6个选项进行设置，单击“确定”按钮，即可完成对选定数据库的修改。 3.删除数据库

50个常用sql语句实例(学生表 课程表 成绩表 教师表)

Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 create table Student(S# varchar(20),Sname varchar(10),Sage int,Ssex varchar(2)) 前面加一列序号： if exists(select table_name from information_schema.tables where table_name='Temp_Table') drop table Temp_Table go select 排名=identity(int,1,1),* INTO Temp_Table from Student go select * from Temp_Table go drop database [ ] --删除空的没有名字的数据库 问题： 1、查询“”课程比“”课程成绩高的所有学生的学号； select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') b where a.score>b.score and a.s#=b.s#; 2、查询平均成绩大于分的同学的学号和平均成绩； select S#,avg(score) from sc group by S# having avg(score) >60; 3、查询所有同学的学号、姓名、选课数、总成绩； select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student left Outer join SC on Student.S#=SC.S# group by Student.S#,Sname 4、查询姓“李”的老师的个数； select count(distinct(Tname)) from Teacher where Tname like '李%'; 5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.S#,Student.Sname from Student

实验3PLSQL编程

实验三PL/SQL编程 1．PL/SQL块处理 定义一个包含声明、执行和异常处理的块 查询EMP表中职工号7788的工资，输出工资的值并且如果工资小于3000那么把工资更改为3000，异常部分对NO_DATA_FOUND异常进行处理，输出没有该员工。 如果想运行缓冲区的内容，那么可以用RUN命令或者/命令；serveroutput需要设置为on 2．记录类型的使用 创建一个记录类型v_record，类型包含name，salary，job，deptno等分量，要求记录类型的分量的数据类型和emp表中列的数据类型一致（%type实现）。创建一个变量，变量类型为v_ record，读取EMP表中职工号为7788的ename，sal，job，deptno为该变量赋值，输出变量的分量。 3．条件语句的使用 分别用IF语句和CASE语句实现以下要求：输入一个员工号，修改该员工的工资，如果该员工职位是CLERK，工资增加100；若为SALESMAN，工资增加160；若为ANALYST，工资增加200；否则增加300。 4. 循环和显示游标的使用 分别用简单循环、WHILE循环、FOR循环以及显示游标统计并输出各个部门的人数以及平均工资 5．用隐式游标实现以下要求：修改部门号为50的部门地址为‘BEIJING’。如果该部门不存在，则向dept表中插入一个部门号为50，地址为‘BEIJING’的记录。 6．创建一个显示雇员总人数的存储过程emp_count,并执行该存储过程 7. 编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程 8. 创建函数，实现功能为：在scott.emp表和scott.dept表中查询出任意给定职工号的职工姓名及职工所在部门的名称。 9. 创建触发器，实现更新dept表中的deptno值，级联更新emp表中相应值。 10. 对存储过程、函数及触发器实现查看、修改、删除等基本操作。 主要算法和程序清单： 1. DECLARE v_empno emp.empno%TYPE:=7788; v_sal emp.sal%TYPE; v_add emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=v_empno; IF v_sal<3000 THEN v_add:=3000;

SQL查询语句例子

数据表的查询(select) select 字段列表[as 别名], * from 数据表名 [where 条件语句] [group by 分组字段] [order by 排序字段列表desc] [LIMIT startrow,rownumber] 1、Select 字段列表From 数据表 例：①、select id,gsmc,add,tel from haf (* 表示数据表中所有字段) ②、select 单价,数量,单价*数量as 合计金额from haf (As 设置字段的别名) 2、Select …from …Where 筛选条件式 筛选条件式：①、字符串数据：select * from 成绩单Where 姓名='李明' ②、万用字符：select * from 成绩单Where 姓名like '李%' select * from 成绩单Where 姓名like '%李%' select * from 成绩单Where 姓名like '%李_' ③、特殊的条件式： ⑴= / > / < / <> / >= / <= ⑵AND(逻辑与) OR(逻辑或) NOT(逻辑非) ⑶Where 字段名称in(值一，值二) ⑷Where 字段名称Is Null / Where 字段名称Is Not Null 3、Select …from …group by 字段 SQL函数： SELECT sex,count(id) as women from `user` group by 'sex'; 函数名描述函数名描述 AVG平均值Count计数 MAX最大值MIN最小值 Sum求和

数据库SQL语句实验报告

《数据库原理及应用》实验报告 SQL语句部分 总成绩：

实验一熟悉SQL SERVER，数据定义实验 实验一成绩： 一、实验目的 1、利用查询分析器创建数据库 2、利用查询分析器用SQL语言定义基本表、索引并进行相关操作 二、实验步骤及内容 在以下实验中，使用学生-课程数据库，它描述了学生的基本信息、课程的基本信息及学生选修课程的信息。 1．创建学生课程数据库 3．创建课程关系表C ： 5．将以上创建表S、C、SC的SQL命令以 .SQL文件的形式保存在磁盘上。 6、在表S上增加“出生日期”属性列。 7、删除表S的“年龄”属性列。 8、删除S姓名列的唯一值约束。 9、修改SC表成绩属性列为精确数字型。 10、在表S上，按“Sno”属性列的唯一值方式建立索引。 11、删除表SC，利用磁盘上保存的.SQL文件重新创建表SC。 12、创建教材P74页习题5中SPJ数据库中的四个关系：S、P、J、SPJ。 三、实验结果： 1．创建学生课程数据库 create database Studend; create table S ( Sno CHAR(9)PRIMARY KEY, Sname CHAR(20)UNIQUE, Ssex CHAR(2), Sage smallint, Sdept char(20), );

3．创建课程关系表C ： 课程号课程名先行课学分 Cno Cname Cpno ccredit create table C ( Cno char(4)primary key, Cname CHAR(40), Cpno char(4), Ccredit SMALLINT, ); 4．创建课程关系表SC ： 学号课程号成绩 Sno Cno grade create table SC ( Sno char(9), Cno char(4), Grade smallint, ); 5．将以上创建表S、C、SC的SQL命令以 .SQL文件的形式保存在磁盘上。 6、在表S上增加“出生日期”属性列。 alter table S add Sbirthday datetime; 7、删除表S的“年龄”属性列。 alter table S drop column Sage;

SQL实验三数据的更新操作和视图实验报告

实验三数据的更新操作和视图 本实验需要2学时。 一、实验目的 1、掌握视图的创建、修改和删除操作。 2、掌握数据库数据更新（增加、修改、删除）操作。 二、实验内容和步骤 1、视图 （1）建立名为UV_SC的视图。该视图可以让我们看到每个学生的姓名、选修课程名和成绩。（2）利用UV_SC视图，查看平均成绩在80分以上的学生姓名。 （3）建立名为UV_Course的视图，该视图显示每门课的课程名和选修该课程的学生人数。（4）修改视图UV_Course，在视图中增加一列，显示选修该课程所有学生的平均成绩。 （5）建立名为UV_IS_Depart的视图。该视图可以用来查看“信息系”的学生的学号、姓名、年龄和系名。并保证对该视图的修改满足系名为信息系的条件。 （6）建立名为UV_Student的加密视图。该视图可以用来查看每个学生选修的课程门数和平均成绩。 （7）利用UV_Student视图，查询平均成绩在80分以上的学生学号。 （8）能否利用视图UV_Student，修改某个学生的平均成绩？ （9）删除视图UV_Student。 2、数据更新 （1）向STUDENT表增加如下记录： （2）将学号'95011'，成绩80，以及课程表中的所有课程号插入到SC表中。 （3）建立与表STUDENT同结构的空表STUD。然后利用INSERT命令把表STUDENT中年龄大于19的学生记录添加到表STUD。（注意：可以使用select into语句实现同结构空表的创建，再使用insert命令插入记录；或者使用单条select into语句实现等同的功能） （4）为“95002”的学生增加选修“操作系统”和“数据结构”这两门课的选课记录。 （5）把课程名为“操作系统”的成绩从成绩表SC中删除。（试用三种方式完成）（6）将女生的成绩提高10%。（试用三种方式完成） （7）如果学生“张三”的数据结构的成绩低于65，则加上10分。 （8）将成绩最低的学生的成绩加上10分。 （9）将前3名成绩最高的学生的成绩减去10分。 （10）为没有选课的学生自动增加选修“01”课程的选课记录。 （11）把选修“数学”不及格的成绩全部改成空值。 （12）删除成绩低于总平均成绩的女生的选课记录。 三、实验环境 四、调试过程

sql基础语句

掌握SQL四条最基本的数据操作语句：Insert，Select，Update和Delete。 练掌握SQL是数据库用户的宝贵财富。在本文中，我们将引导你掌握四条最基本的数据操作语句—SQL的核心功能—来依次介绍比较操作符、选择断言以及三值逻辑。当你完成这些学习后，显然你已经开始算是精通SQL了。 在我们开始之前，先使用CREATE TABLE语句来创建一个表（如图1所示）。DDL语句对数据库对象如表、列和视进行定义。它们并不对表中的行进行处理，这是因为DDL语句并不处理数据库中实际的数据。这些工作由另一类SQL语句—数据操作语言（DML）语句进行处理。 SQL中有四种基本的DML操作：INSERT，SELECT，UPDA TE和DELETE。由于这是大多数SQL用户经常用到的，我们有必要在此对它们进行一一说明。在图1中我们给出了一个名为EMPLOYEES的表。其中的每一行对应一个特定的雇员记录。请熟悉这张表，我们在后面的例子中将要用到它。 INSERT语句 用户可以用INSERT语句将一行记录插入到指定的一个表中。例如，要将雇员John Smith的记录插入到本例的表中，可以使用如下语句： INSERT INTO EMPLOYEES V ALUES ('Smith','John','1980-06-10', 'Los Angles',16,45000); 通过这样的INSERT语句，系统将试着将这些值填入到相应的列中。这些列按照我们创建表时定义的顺序排列。在本例中，第一个值“Smith”将填到第一个列LAST_NAME中；第二个值“John”将填到第二列FIRST_NAME中……以此类推。 我们说过系统会“试着”将值填入，除了执行规则之外它还要进行类型检查。如果类型不符（如将一个字符串填入到类型为数字的列中），系统将拒绝这一次操作并返回一个错误信息。 如果SQL拒绝了你所填入的一列值，语句中其他各列的值也不会填入。这是因为SQL提供对事务的支持。一次事务将数据库从一种一致性转移到另一种一致性。如果事务的某一部分失败，则整个事务都会失败，系统将会被恢复（或称之为回退）到此事务之前的状态。 回到原来的INSERT的例子，请注意所有的整形十进制数都不需要用单引号引起来，而字符串和日期类型的值都要用单引号来区别。为了增加可读性而在数字间插入逗号将会引起错误。记住，在SQL中逗号是元素的分隔符。 同样要注意输入文字值时要使用单引号。双引号用来封装限界标识符。 对于日期类型，我们必须使用SQL标准日期格式（yyyy-mm-dd），但是在系统中可以进行定义，以接受其他的格式。当然，2000年临近，请你最好还是使用四位来表示年份。

sql 数据库 实验3答案

实验三：创建及管理数据表 1、创建表 依据数据表的结构创建相对应的数据表，表结构如下所示； 学生信息表（student ） CREATE (sno char (9) PRIMARY KEY , sname char (8) NOT NULL, ssex char (2), sage int , sdept varchar (20) ) 课程信息表（course ） CREATE (cno char (4) PRIMARY KEY , cname varchar (20) NOT NULL, cpno char (4), ccredit int ) 选课信息表（sc ）

CREATE TABLE sc (sno char(9), cno char(4), grade int, Constraint PK_sno PRIMARY KEY(sno,cno)) 2.修改表结构 1）在表student中增加新字段“班级名称（sclass）”字符类型为varchar(10)； use student ALTER TABLE student Add sclass nvarchar(10) 2）在表student中删除字段“班级名称（sclass）”； use student ALTER TABLE student Drop column sclass 3）修改表student中字段名为“sname”的字段长度由原来的6改为8； use student ALTER TABLE student ALTER COLUMN sname char(8) 4）修改表student中ssex字段默认值为‘男’； use student ALTER TABLE student add default'男'for ssex 5）修改表course中cname字段为强制唯一性字段； use student ALTER TABLE course Add constraint ix_course unique (cname) 6）修改表sc中grade字段的值域为0-100； use student ALTER TABLE sc Add constraint CK_grade CHECK(grade between 0 and 100) 7）删除数据表course的唯一性约束； use student ALTER TABLE course drop ix_course

50个常用的SQL语句练习

基本信息Student(`S#`,Sname,Sage,Ssex) 学生表 Course(`C#`,Cname,`T#`) 课程表 SC(`S#`,`C#`,score) 成绩表 Teacher(`T#`,Tname) 教师表 问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学号； select a.`S#` from (select `S#`,score from SC where `C#`='001') a,(select `S#`,score from SC where `C#`='002') b where a.score>b.score and a.`S#`=b.`S#`; ↑一张表中存在多对多情况的 2、查询平均成绩大于60分的同学的学号和平均成绩； 答案一：select `S#`,avg(score) from sc group by `S#` having avg(score) >60; ↑一对多，对组进行筛选 答案二：SELECT s ,scr FROM (SELECT sc.`S#` s,AVG(sc.`score`) scr FROM sc GROUP BY sc.`S#`) rs WHERE rs.scr>60 ORDER BY rs.scr DESC ↑嵌套查询可能影响效率 3、查询所有同学的学号、姓名、选课数、总成绩； 答案一：select Student.`S#`,Student.Sname,count(`C#`),sum(score) from Student left Outer join SC on Student.`S#`=SC.`S#` group by Student.`S#`,Sname ↑如果学生没有选课，仍然能查出，显示总分null（边界情况） 答案二：SELECT student.`S#`,student.`Sname`,COUNT(sc.`score`) 选课数,SUM(sc.`score`) 总分FROM Student,sc WHERE student.`S#`=sc.`S#` GROUP BY sc.`S#` ↑如果学生没有选课，sc表中没有他的学号，就查不出该学生，有缺陷！ 4、查询姓“李”的老师的个数； select count(distinct(Tname)) from Teacher where Tname like '李%'; 5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.`S#`,Student.Sname from Student where `S#` not in (select distinct(SC.`S#`) from SC,Course,Teacher where SC.`C#`=Course.`C#` and Teacher.`T#`=Course.`T#` and Teacher.Tname='叶平'); ↑反面思考Step1：先找学过叶平老师课的学生学号，三表联合查询 Step2：在用not in 选出没学过的 Step3：distinct以防叶平老师教多节课；否则若某同学的几节课都由叶平教，学号就会出现重复 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select Student.`S#`,Student.Sname from Student,SC where Student.`S#`=SC.`S#` and SC.`C#`='001'and exists( Select * from SC as SC_2 where SC_2.`S#`=SC.`S#` and SC_2.`C#`='002' ); ↑注意目标字段`S#`关联 exists subquery 可以用in subquery代替，如下 select Student.`S#`,Student.Sname from Student,Sc where Student.`S#`=SC.`S#` and SC.`C#`='001'and sc.`s#` in ( select sc_2.`s#` from sc as sc_2 where sc_2.`c#`='002' ); ↑不同之处，in subquery此处就不需要关联了

sql常用语句100例

--update phoneinfo set cityname = '克孜勒苏柯尔克孜' where cityname = '克孜勒苏柯尔克孜州' --update phoneinfo set cityname = '湘西' where pad1 = '湖南 吉首' select * from dbo.PhoneInfo --update dbo.PhoneInfo set provincename=b.provincename,cityname=b.cityname from dbo.PhoneInfo a,PhoneInfo_hl b --where a.phonebound=b.phonebound --select * from dbo.UnknowPhoneBound --select * from dbo.Area --select * from phoneinfo a, phoneinfo_old b, phoneinfo_hl c where a.phonebound = b.phonebound and a.phonebound = c.phonebound and (a.cityname <> b.cityname or a.cityname <> c.cityname) --select * from phoneinfo a, phoneinfo_hl b where a.phonebound = b.phonebound and a.cityname <> b.citynameselect * from phoneinfo a, phoneinfo_old b where a.phonebound = b.phonebound and a.cityname <> b.cityname --select * into phoneinfo_bak from phoneinfo --select * from phoneinfo_bak select * from phoneinfo a, phoneinfo_old b where a.phonebound = b.phonebound and a.cityname <> b.cityname --select * from dbo.PhoneInfo_Telecom --update PhoneInfo_Telecom set provincename = '内蒙古' where pad1 = '内蒙兴安盟' update PhoneInfo set cityname = '酒泉' where pad1 = '甘肃 酒泉嘉峪关' --update dbo.PhoneInfo_old set provincename=b.provincename,cityname=b.cityname from dbo.PhoneInfo_old a,PhoneInfo_Telecom b --where a.phonebound=b.phonebound create table client_all as