创新互联公司服务项目包括代县网站建设、代县网站制作、代县网页制作以及代县网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，代县网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到代县省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！
--建表FAMILYINF
重庆分公司
重庆分公司
CREATE TABLE FAMILYINFO(
FNO NUMBER CONSTRAINT FC001 PRIMARY KEY,--把字段fno约束为主键
FNAME VARCHAR2(20) CONSTRAINT FC002 NOT NULL,--把字段FNAME的约束条件设为“不能为空”
SEX VARCHAR2(20) DEFAULT 'MALE'CONSTRAINT FC003 CHECK(SEX IN('MALE','FEMALE')),--把字段’SEX‘的约束条件设为’性别默认条件下为’MALE‘，如果自己填写只能填写’MALE‘或’FEMALE‘
FAGE NUMBER, REL VARCHAR2(20),--家庭成员关系字段 HOBITNO NUMBER CONSTRAINT FC004 REFERENCES HOBITINFO(HNO) ON DELETE SET NULL);
--约束条件设为，表’HOBITINFO‘中字段’HNo‘的外键，删除引用表中字段时，设为空值
--建兴趣表HOBITINF
CREATE TABLE HOBITINFO( HNO NUMBER CONSTRAINT HC001 PRIMARY KEY, --将‘ HNO’设为主键 HNAME VARCHAR2(20) CONSTRAINT HC002 NOT NULL--将‘HNAME’约束条件设为‘不能为空’ );
--删除表HOBITINFO
DROP TABLE HOBITINFO;
--添加信息
INSERT INTO HOBITINFO VALUES(1,'MOVIE'); INSERT INTO HOBITINFO VALUES(2,'CG');;
--DDL ALTER TABLE (修改表)
ALTER TABLE FAMILYINFO RENAME TO PCFAMILY;--将FAMILYINFO的表名重命名为PCFAMILY ALTER TABLE PCFAMILY ADD(--添加列即字段及其属性 HEIGH VARCHAR2(5)， SX VARCHAR2(10) CONSTRAINT PC002 NOT NULL--字段SX创建“不能为空”的约束条件 ); ALTER TABLE PCFAMILY MODIFY(--修改字段的属性 HEIGH CHAR(20) ); ALTER TABLE PCFAMILY DROP COLUMN HEIGH;--删除字段HEIGH ALTER TABLE PCFAMILY RENAME COLUMN SX TO SX1;--修改约束字段名 ALTER TABLE PCFAMILY DROP COLUMN SX1;--删除约束字段 --DDL 之 DROP TABLE(删除表) DROP TABLE HOBITINFO CASCADE CONSTRAINTS; --DDL 之 TRUNCATE TABLE 清空表记录 TRUNCATE TABLE PCFAMILY;
--DML 之 INSERT(插入，添加记录）
INSERT INTO PCFAMILY(FNO,SEX,FNAME,FAGE) VALUES(4,'NANE','LIU'||'DAYE',45+20);
--如果指定插入字段顺序，则values里的信息就要对照前面指定的字段填写
INSERT INTO PCFAMILY VALUES();
--学生表
CREATE TABLE STUDENT(
SNO NUMBER CONSTRAINT SC001 PRIMARY KEY,
SNAME VARCHAR2(10) CONSTRAINT SC002 NOT NULL,
SSEX VARCHAR(10) CONSTRAINT S003 CHECK(SSEX IN ('M','F'))
);--老师表
CREATE TABLE TEACHER( TNO NUMBER CONSTRAINT TC001 PRIMARY KEY, TNAME VARCHAR2(10) CONSTRAINT TC002 NOT NULL );
--课程表
CREATE TABLE COURSE( CNO NUMBER CONSTRAINT CC001 PRIMARY KEY, CNAME VARCHAR2(20) CONSTRAINT CC002 NOT NULL, TNO NUMBER CONSTRAINT CC003 REFERENCES TEACHER(TNO)
);
--成绩表
CREATE TABLE SCORE( SNO NUMBER CONSTRAINT SSC001 REFERENCES STUDENT(SNO), CNO NUMBER CONSTRAINT SSC002 REFERENCES COURSE(CNO), GRADE VARCHAR2(20), CONSTRAINT SSC003 PRIMARY KEY(SNO,CNO) );
--查询表是否建立成功已经存在
SELECT * FROM STUDENT; SELECT * FROM TEACHER; SELECT * FROM COURSE; SELECT * FROM SCORE; DROP TABLE STUDENT; DROP TABLE TEACHER; DROP TABLE COURSE; DROP TABLE SCORE; INSERT INTO STUDENT VALUES(1,'aaaa','M'); INSERT INTO STUDENT VALUES(2,'bbbb','M'); INSERT INTO STUDENT VALUES(3,'cccc','F'); INSERT INTO TEACHER VALUES(1,'AAAA'); INSERT INTO TEACHER VALUES(2,'BBBB'); INSERT INTO TEACHER VALUES(3,'CCCC'); INSERT INTO COURSE VALUES(1,'yw',2); INSERT INTO COURSE VALUES(2,'sx',3); INSERT INTO COURSE VALUES(3,'yy',2); INSERT INTO SCORE VALUES(1,2,89); INSERT INTO SCORE VALUES(2,3,120); INSERT INTO SCORE VALUES(3,1,110); --DML 之UPDATE UPDATE SCORE SET GRADE=GRADE-1; UPDATE SCORE SET GRADE=GRADE+1 WHERE SNO=2; UPDATE SCORE SET GRADE=GRADE+1 WHERE SNO=2 AND CNO=3;
-------------------------------------------------------------------------------------------
DROP删除为物理删除，delete删除为逻辑删除。
-- DQL 之 SELECT
SELECT CHR(107) HAHA FROM DUAL;
SELECT CURRENT_DATE FROM DUAL;
SELECT TRUNC(ABS(MONTHS_BETWEEN(DATE'1998-08-08',CURRENT_DATE)/12)) AGE FROM DUAL;
SELECT SNO AS 学号, SNAME 学生姓名 FROM STUDENT;
SELECT ALL SSEX FROM STUDENT;
SELECT DISTINCT SSEX FROM STUDENT;
SELECT UNIQUE SSEX FROM STUDENT;
SELECT * FROM SCORE WHERE GRADE < 60;
SELECT ROWID,ROWNUM,STUDENT.* FROM STUDENT;
SELECT * FROM STUDENT WHERE ROWNUM = 1;
SELECT * FROM STUDENT WHERE ROWNUM <= 2;
SELECT * FROM (SELECT ROWNUM AS RN,STUDENT.* FROM STUDENT) WHERE RN = 2;
SELECT CNO,AVG(GRADE) FROM SCORE GROUP BY CNO;
SELECT CNO,AVG(GRADE) FROM SCORE GROUP BY CNO HAVING AVG(GRADE) > 80;
SELECT SSEX,COUNT(SNO) FROM STUDENT GROUP BY SSEX;
SELECT SNO FROM SCORE WHERE CNO = 'C001' AND GRADE = (SELECT MAX(GRADE) FROM SCORE WHERE CNO = 'C001');
SELECT SNO FROM SCORE WHERE CNO = 'C001' AND GRADE >= ALL(SELECT GRADE FROM SCORE WHERE CNO = 'C001');
SELECT * FROM SCORE ORDER BY CNO ASC,GRADE DESC;
SELECT * FROM STUDENT,SCORE WHERE STUDENT.SNO = SCORE.SNO;
SELECT SNAME FROM STUDENT,SCORE
WHERE STUDENT.SNO = SCORE.SNO AND CNO = 'C001' AND GRADE = (SELECT MAX(GRADE) FROM SCORE WHERE CNO = 'C001');
SELECT * FROM STUDENT INNER JOIN SCORE ON STUDENT.SNO = SCORE.SNO;
SELECT * FROM STUDENT INNER JOIN SCORE USING(SNO);
SELECT * FROM STUDENT CROSS JOIN SCORE WHERE STUDENT.SNO = SCORE.SNO;
SELECT * FROM STUDENT NATURAL INNER JOIN SCORE;
SELECT * FROM STUDENT LEFT OUTER JOIN SCORE ON STUDENT.SNO = SCORE.SNO;
SELECT * FROM STUDENT RIGHT OUTER JOIN SCORE USING(SNO);
SELECT * FROM STUDENT FULL OUTER JOIN SCORE USING(SNO);
-- 查询考试不及格的学生姓名和挂科的科目名称
SELECT SNAME,CNAME
FROM (SCORE LEFT JOIN STUDENT USING(SNO))
LEFT JOIN COURSE USING(CNO)
WHERE GRADE < 60;
-- 查询所有java考试成绩高于平均分的学生的姓名和成绩
SELECT SNAME,GRADE
FROM (SCORE LEFT JOIN STUDENT USING(SNO))
LEFT JOIN COURSE USING(CNO)
WHERE CNAME = 'JAVA'
AND GRADE > (
SELECT AVG(GRADE) FROM SCORE LEFT JOIN COURSE USING(CNO)
WHERE CNAME = 'JAVA'
);
select * from (SELECT * from test RIGHT JOIN t_grade using(ID) ) a where `数学` > 90
-- 查询同时参加了C001和C002科目考试的学生编号
SELECT SNO FROM
(SELECT SNO,CNO FROM SCORE WHERE CNO = 'C001')
INNER JOIN
(SELECT SNO,CNO FROM SCORE WHERE CNO = 'C002')
USING(SNO);
-- 查询lili同学参加的所有科目考试中成绩最高的那门科目的授课老师名字
SELECT TNAME,CNAME FROM
TEACHER LEFT JOIN COURSE USING(TNO)
WHERE CNO IN (
SELECT CNO FROM
SCORE LEFT JOIN STUDENT USING(SNO)
WHERE SNAME = 'LILI'
AND GRADE = ( SELECT MAX(GRADE) FROM
SCORE LEFT JOIN STUDENT USING(SNO)
WHERE SNAME = 'LILI'
)
);
-- 查询库存目前还有多少
CREATE TABLE CLOTHER_STORE(
CTYPE VARCHAR2(10),
STORENUM NUMBER
);
CREATE TABLE CLOTHER_SALE(
CTYPE VARCHAR2(10),
SALENUM NUMBER
);
INSERT INTO CLOTHER_STORE VALUES('T-SHIRT',600);
INSERT INTO CLOTHER_STORE VALUES('COAT',700);
INSERT INTO CLOTHER_SALE VALUES('T-SHIRT',140);
INSERT INTO CLOTHER_SALE VALUES('T-SHIRT',165);
INSERT INTO CLOTHER_SALE VALUES('COAT',90);
INSERT INTO CLOTHER_SALE VALUES('COAT',78);
SELECT CTYPE,STORENUM-SNUM AS KCSY FROM
CLOTHER_STORE LEFT JOIN
(SELECT CTYPE,SUM(SALENUM) SNUM FROM CLOTHER_SALE GROUP BY CTYPE)
USING(CTYPE);
-- 切换到scott用户
SELECT * FROM EMP;
SELECT * FROM DEPT;-----------------------------华丽分割线-----------------------------------
以下是SQL练习内容
--1． 列出至少有一个员工的所有部门。
SELECT DISTINCT DEPTNO,DNAME FROM EMP LEFT JOIN DEPT USING(DEPTNO);
--2． 列出薪金比"SMITH"多的所有员工。
SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');
--3． 列出所有员工的姓名及其直接上级的姓名。
SELECT E.ENAME 员工姓名,B.ENAME 上级姓名 FROM EMP E LEFT JOIN EMP B ON E.MGR = B.EMPNO;
--4． 列出受雇日期早于其直接上级的所有员工。
SELECT E.ENAME 员工姓名,E.HIREDATE 员工受雇日期,B.ENAME 上级姓名,B.HIREDATE 上级受雇日期 FROM EMP E LEFT JOIN EMP B ON E.MGR = B.EMPNO WHERE E.HIREDATE < B.HIREDATE;
--5． 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT DNAME,EMP.* FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
--6． 列出所有"CLERK"（办事员）的姓名及其部门名称。
SELECT ENAME,DNAME FROM EMP LEFT JOIN DEPT USING(DEPTNO) WHERE JOB = 'CLERK';
--7． 列出最低薪金大于1500的各种工作。
SELECT JOB FROM EMP GROUP BY JOB HAVING MIN(SAL) > 1500;
--8． 列出在部门"SALES"（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
SELECT ENAME FROM EMP LEFT JOIN DEPT USING(DEPTNO) WHERE DNAME = 'SALES';
--9． 列出薪金高于公司平均薪金的所有员工。
SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);
--10．列出与"SCOTT"从事相同工作的所有员工。
SELECT * FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'SCOTT') AND ENAME <> 'SCOTT';
--11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
SELECT ENAME,SAL FROM EMP WHERE SAL = ANY(SELECT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO <> 30;
--12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
SELECT ENAME,SAL FROM EMP WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO
<> 30;
--13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT DEPTNO,COUNT(EMPNO) 员工数量,ROUND(AVG(SAL),2) 平均工资, ROUND(AVG(MONTHS_BETWEEN(CURRENT_DATE,HIREDATE))/12,2) 平均服务年限 FROM EMP RIGHT JOIN DEPT USING(DEPTNO) GROUP BY DEPTNO;
--14．列出所有员工的姓名、部门名称和工资。
SELECT ENAME,DNAME,SAL FROM EMP LEFT JOIN DEPT USING(DEPTNO);
--15．列出所有部门的详细信息和部门人数。
SELECT DEPT.DEPTNO,DNAME,LOC,COUNT(EMPNO) FROM DEPT LEFT JOIN EMP ON EMP.DEPTNO = DEPT.DEPTNO GROUP BY DEPT.DEPTNO,DNAME,LOC;
--16．列出各种工作的最低工资。
SELECT JOB,MIN(SAL) FROM EMP GROUP BY JOB;
--17．列出各个部门的MANAGER（经理）的最低薪金。
SELECT MIN(SAL) FROM EMP WHERE JOB = 'MANAGER';
--18．列出所有员工的年工资,按年薪从低到高排序。
SELECT ENAME,SAL*12+NVL(COMM,0) 年薪 FROM EMP ORDER BY 年薪;
--19．列出经理人的名字。
SELECT ENAME FROM EMP WHERE JOB = 'MANAGER' OR JOB = 'PRESIDENT';
--20．不用组函数，求出薪水的最大值。
SELECT SAL FROM (SELECT SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM = 1;
--21．列出薪资第2高到第8高的员工。
SELECT SAL FROM( SELECT ROWNUM RN,SAL FROM (SELECT SAL FROM EMP ORDER BY SAL DESC)) WHERE RN >= 2 AND RN <= 8;
-- 切换回普通用户
-- union, INTERSECT,MINUS CREATE TABLE A1 ( V1 NUMBER, V2 VARCHAR2(10) ); CREATE TABLE A2 ( V3 NUMBER, V4 VARCHAR2(10), V5 VARCHAR2(10) ); INSERT INTO A1 VALUES(10,'A001'); INSERT INTO A1 VALUES(11,'A002'); INSERT INTO A2 VALUES(10,'A001','HAHA'); INSERT INTO A2 VALUES(12,'A003','HEIHEI'); INSERT INTO A2 VALUES(13,'A004','HOHO'); SELECT * FROM A1 UNION (SELECT V3，V4 FROM A2); SELECT * FROM A1 UNION ALL (SELECT V3，V4 FROM A2); SELECT * FROM A1 INTERSECT (SELECT V3，V4 FROM A2); SELECT * FROM A1 MINUS (SELECT V3，V4 FROM A2);
-- 带锁查询
UPDATE CLOTHER_STORE SET STORENUM = 600 WHERE CTYPE = 'T-SHIRT'; SELECT * FROM CLOTHER_STORE FOR UPDATE WAIT 5; UPDATE CLOTHER_STORE SET STORENUM = STORENUM - 400 WHERE CTYPE = 'T-SHIRT'; -- DCL 之 COMMIT SELECT * FROM STUDENT; DELETE FROM STUDENT WHERE SNO = 'S011'; COMMIT; -- DCL 之 ROLLBACK 和 SAVEPOINT SELECT * FROM CLOTHER_SALE; UPDATE CLOTHER_SALE SET SALENUM = SALENUM + 10; ROLLBACK; UPDATE CLOTHER_SALE SET SALENUM = SALENUM + 10; SAVEPOINT CPD; UPDATE CLOTHER_SALE SET SALENUM = SALENUM + 10; ROLLBACK TO SAVEPOINT CPD; -- DCL 之 GRANT GRANT CREATE VIEW TO LUYY124; GRANT UPDATE ANY TABLE TO LUYY124; -- DDL 之 REVOKE REVOKE CREATE VIEW FROM LUYY124; -- CREATE VIEW CREATE OR REPLACE VIEW STUDENT_VIEW AS SELECT * FROM (STUDENT LEFT JOIN SCORE USING(SNO)) LEFT JOIN COURSE USING(CNO); SELECT * FROM STUDENT_VIEW; SELECT SNAME,CNAME FROM STUDENT_VIEW WHERE GRADE < 60; SELECT * FROM SCORE; UPDATE SCORE SET GRADE = GRADE + 1 WHERE SNO = 'S001' AND CNO = 'C001'; CREATE OR REPLACE VIEW STUDENT_VIEW AS SELECT * FROM SCORE; UPDATE STUDENT_VIEW SET GRADE = GRADE - 1 WHERE SNO = 'S001' AND CNO = 'C001'; -- CREATE INDEX CREATE INDEX SNAME_INDEX ON STUDENT(SNAME ASC) ; SELECT * FROM STUDENT WHERE SNAME = 'LILI'; DROP INDEX SNAME_INDEX;
