ORACLE
– 创建用户
create user 用户名 identified by 密码;
– 赋值权利
grant connect/resource to 【用户名】;
– 回收权利
revoke connect/resource from 【用户名】;
– 删除用户
drop user 【用户名】 cascade;
– ALTER语句总结
1:删除列
ALTER TABLE 【表名字】 DROP 【列名称】
2:增加列
ALTER TABLE 【表名字】 ADD 【列名称】 【类型】
3:修改列的类型信息
ALTER TABLE 【表名字】 CHANGE 【列名称】 BIGINT
4:重命名列
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 BIGINT
5:重命名表
ALTER TABLE 【表名字】 RENAME 【表新名字】
6:删除表中主键
Alter TABLE 【表名字】 drop primary key
7:添加主键
ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)
8:添加索引
ALTER TABLE sj_resource_charges add index INDEX_NAME (name);
9: 添加唯一限制条件索引
ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);
10: 删除索引
alter table tablename drop index emp_name;
– 创建表
create table 【表名】(
id int,
name varchar(20),
money float
);
– 查询 (*表示所有的列)
select * from 【表名】;
– 添加
insert into 【表名】(id,name,money) values(1,'jack',2000.2);
insert into 【表名】(id,name,money) values(2,'roce',1000);
– 修改
update 【表名】 set id=2, name='rose', money=1000 where id = 3;
update 【表名】 set name='jack' where money = 1000;
– 删除
delete from 【表名】 where id = 【id】;
delete from 【表名】;
– 手动提交事务
commit;
– 主键自增
create table mytable
(
id int primary key, -- 主键,唯一,不能重复,必填,不能不填写
name varchar(20),
money float
);
-- 使用序列让主键自增
-- 创建序列
create sequence myseq; --从0开始 每次加1
--使用序列
insert into mytable(id,name,money) values(myseq.nextval,'jack',10);
– 外键约束
FOREIGN KEY (ID) REFERENCES 表名 (ID)
/**
外键:
1、需要先创建主表,再创建子表
2、删除表:先删除子表,再删除主表
数据处理:
1、先添加主表数据,再添加子表数据
2、先删除子表数据,再删除主表数据
**/
DROP TABLE STUDENT;
DROP TABLE TEACHER;
-- 子表
CREATE TABLE STUDENT
(
SID INT PRIMARY KEY,
SNAME VARCHAR(20) NOT NULL,
SEX VARCHAR(20) DEFAULT '男' CHECK(SEX='男' or SEX='女') NOT NULL,
TID INT ,
FOREIGN KEY(TID) REFERENCES TEACHER(TID)
);
-- 主表
CREATE TABLE TEACHER
(
TID INT PRIMARY KEY,
TNAME VARCHAR(20) NOT NULL,
MONEY NUMBER(10,2)
);
--序列
CREATE SEQUENCE MYSEQ2;
--录入学生数据
INSERT INTO STUDENT(SID,SNAME,TID) VALUES(MYSEQ2.NEXTVAL,'A',4);
INSERT INTO STUDENT(SID,SNAME,TID) VALUES(MYSEQ2.NEXTVAL,'S1',4);
INSERT INTO STUDENT(SID,SNAME,TID) VALUES(MYSEQ2.NEXTVAL,'S2',4);
INSERT INTO STUDENT(SID,SNAME,TID) VALUES(MYSEQ2.NEXTVAL,'S3',4);
--录入老师数据
INSERT INTO TEACHER(TID,TNAME,MONEY) VALUES(MYSEQ2.NEXTVAL,'T1',1000);
SELECT * FROM STUDENT;
SELECT * FROM TEACHER;
-- 多对多
CREATE TABLE MTM_TEACHER
(
TID INT PRIMARY KEY,
TNAME VARCHAR(20)
);
CREATE TABLE MTM_STUDENT
(
SID INT PRIMARY KEY,
SNAME VARCHAR(20)
);
DROP TABLE T_S;
-- 子表
CREATE TABLE T_S
(
TID INT,
SID INT,
--联合主键
PRIMARY KEY(TID,SID),
FOREIGN KEY(TID) REFERENCES MTM_TEACHER(TID),
FOREIGN KEY(SID) REFERENCES MTM_STUDENT(SID)
);
-- 先录入主表数据
INSERT INTO MTM_STUDENT(SID , SNAME) VALUES(MYSEQ2.NEXTVAL,'亚索');
INSERT INTO MTM_STUDENT(SID , SNAME) VALUES(MYSEQ2.NEXTVAL,'塔姆');
INSERT INTO MTM_STUDENT(SID , SNAME) VALUES(MYSEQ2.NEXTVAL,'娑娜');
INSERT INTO MTM_TEACHER(TID ,TNAME) VALUES(MYSEQ2.NEXTVAL,'后羿');
INSERT INTO MTM_TEACHER(TID ,TNAME) VALUES(MYSEQ2.NEXTVAL,'亚瑟');
INSERT INTO MTM_TEACHER(TID ,TNAME) VALUES(MYSEQ2.NEXTVAL,'安其拉');
SELECT * FROM MTM_STUDENT;
SELECT * FROM MTM_TEACHER;
INSERT INTO T_S(TID,SID) VALUES(17,13);
INSERT INTO T_S(TID,SID) VALUES(17,14);
INSERT INTO T_S(TID,SID) VALUES(17,15);
INSERT INTO T_S(TID,SID) VALUES(18,13);
INSERT INTO T_S(TID,SID) VALUES(18,14);
--外键约束起到作用了
INSERT INTO T_S(TID,SID) VALUES(20,14);
SELECT * FROM T_S;
SELECT * FROM MTM_TEACHER INNER JOIN T_S
ON T_S.TID = MTM_TEACHER.TID
INNER JOIN MTM_STUDENT
ON T_S.SID = MTM_STUDENT.SID
– 虚拟表,仅仅是为了满足ORACLE的语法要求
SELECT 'wangyang' FROM DUAL;
–拼接字符
SELECT CONCAT('WANG','YANG') 拼接字符 FROM DUAL;
SELECT CONCAT(ENAME,JOB) 拼接 FROM EMP;
SELECT * FROM EMP;
– 字符长度
SELECT LENGTH('WANGYANG') FROM DUAL;
SELECT ENAME,LENGTH(ENAME) FROM EMP;
– REPLACE 替换
SELECT REPLACE('WANGYANG','A','Z') FROM DUAL;
SELECT ENAME, REPLACE(ENAME,'A','*') FROM EMP;
– 大小写
SELECT UPPER('wangyang') FROM DUAL;
SELECT LOWER('WANGYANG') FROM DUAL;
– 首字母大写
SELECT INITCAP('wangyang,renxiaodan,zhanglinjia') FROM DUAL;
SELECT INITCAP('wangyang renxiaodan;zhanglinjia') FROM DUAL;
SELECT INITCAP('1,一') FROM DUAL;
– 基础查询语句
SELECT * FROM 【表名】;
– 查看特定的列
SELECT EMPNO , ENAME, JOB , MGR , HIREDATE, SAL , COMM ,DEPTNO FROM EMP;
– 给表起别名,给列起别名
SELECT E.EMPNO AS 员工编号,
E.ENAME AS 员工姓名,
E.JOB 职位,
E.MGR 上级领导,
E.HIREDATE 入职时间,
E.SAL 工资;
– 条件查询
-- 工资为5000的员工信息
SELECT *FROM EMP E
WHERE E.SAL = 5000;
-- 查询工资在800 ~ 1500之间的员工信息
SELECT * FROM EMP E
WHERE
E.SAL BETWEEN 800 AND 1500;
SELECT * FROM EMP E
WHERE
E.SAL >= 800 AND E.SAL <= 1500;
-- 查询 员工名字叫JAMES的所有信息
SELECT * FROM EMP E
WHERE ENAME = 'JAMES';
-- 模糊查询 J开头的员工信息
SELECT * FROM EMP E
WHERE
ENAME LIKE 'J%';
-- 模糊查询 员工姓名S结尾的员工信息
SELECT * FROM EMP E
WHERE E.ENAME LIKE '%S';
-- 模糊查询 员工姓名中包含W和A的员工信息
SELECT * FROM EMP E
WHERE 1=1 and E.ENAME LIKE '%W%' OR E.ENAME'%A%';
-- 模糊查询 员工姓名中第二字为A的员工信息
SELECT * FROM EMP E
WHERE E.ENAME LIKE '_A%';
-- 查询没有奖金的员工
SELECT * FROM EMP E
WHERE E.COMM IS NULL
OR E.COMM = 0;
-- 查询有奖金的员工
SELECT * FROM EMP
WHERE
COMM IS NOT NULL
AND COMM <> 0;
-- 查询姓名J开头的或者S开头的员工信息
SELECT * FROM EMP E
WHERE E.ENAME LIKE 'J%'
OR E.ENAME LIKE 'S%';
-- 查询工资在800 ~3000 范围 的员工信息,且按照工资的降序排列,如果工资一样,则按照编号升序
SELECT * FROM EMP E
WHERE E.SAL >= 800 AND E.SAL <= 3000
ORDER BY E.SAL DESC , E.EMPNO ASC;
– 消除重复行
SELECT DISTINCT JOB FROM EMP;
SELECT DISTINCT * FROM EMP; -- 没有效果
-- ORACLE会将JOB ,SAL EMPNO组合成一条数据,如果这3列一样,就消除
SELECT DISTINCT JOB , SAL, EMPNO FROM EMP;
– 拷贝和备份
CREATE TABLE BACK_EMP AS SELECT * FROM EMP;
SELECT * FROM BACK_EMP;
– 排序(升序 ASC,降序 DESC)
-- 升序 ASC 默认不写
SELECT * FROM EMP E
ORDER BY SAL ASC;
-- 降序 DESC
SELECT * FROM EMP E
ORDER BY SAL DESC;
-- 按照工资降序,如果工资一样,在按照编号升序
SELECT * FROM EMP E
ORDER BY
E.HIREDATE DESC,
SAL DESC,
E.EMPNO ASC;
-- 条件排序 姓名中包含A 或者职位中包含A 的员工信息,且按照工资降序排列
SELECT * FROM EMP E
WHERE E.ENAME LIKE '%A%' OR E.JOB LIKE '%A%'
ORDER BY SAL DESC;
– 分组查询
-- 工资最高 当聚合函数与真实列同时存在时,必须对真实列进行分组
SELECT ENAME, MAX(SAL) FROM EMP
GROUP BY ENAME; --分组
-- 工资最低 当聚合函数与真实列同时存在时,必须对真实列进行分组
SELECT ENAME,MIN(SAL) FROM EMP
GROUP BY ENAME;
-- 插入一条名字,薪资相同的数据
INSERT INTO EMP(EMPNO,ENAME,SAL) VALUES(1,'ALLEN',1600);
UPDATE EMP SET SAL = 3200 WHERE EMPNO=1;
-- 每种职位的最高薪资是多少
SELECT DISTINCT JOB,MAX(SAL) 最高薪资 FROM EMP
WHERE JOB IS NOT NULL
GROUP BY JOB;
-- 每种职位的人数
SELECT JOB,COUNT(EMPNO) FROM EMP
GROUP BY JOB;
/**
GROUP BY 不是必须跟着聚合函数一起使用
1、GROUP BY后面写的的列的名字必须和查询真实列的名字,一模一样
2、除了查询的列,还可以写其他真实列
3、会将GROUP BY后面写的列的内容组合在一起,进行判断
如果内容一样则判定为同组,如果不一样分组
4、GROUP BY 写在WHERE后面
**/
SELECT E.DEPTNO,E.ENAME FROM EMP E
GROUP BY E.DEPTNO,E.ENAME,E.EMPNO;
-- 每个部门员工人数
SELECT DEPTNO,COUNT(EMPNO) FROM EMP
GROUP BY DEPTNO;
– having
-- having
-- 每个部门员工人数 超过5 HAVING 即可对真实列进行条件筛选,也可以对伪列进行筛选
SELECT DEPTNO ,COUNT(*) C FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 5 AND DEPTNO = 30;
– 子查询
-- 子查询可以多行 IN 可以 查询多行数据
SELECT * FROM EMP WHERE JOB in (
SELECT DISTINCT JOB FROM EMP)
SELECT * FROM EMP WHERE JOB LIKE '%SALE%' OR JOB LIKE '%MANAG%'
-- NOT IN 表示不在这个范围
SELECT * FROM EMP WHERE DEPTNO NOT IN(
SELECT DISTINCT DEPTNO FROM EMP WHERE DEPTNO = 10 OR DEPTNO = 20)
-- 查询至少有一个员工的部门信息
SELECT * FROM DEPT WHERE DEPTNO IN(
SELECT DEPTNO FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 0
)
-- 哪些部门有员工
SELECT * FROM DEPT WHERE DEPTNO IN (
SELECT DEPTNO FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 0);
-- 查询工资比SMITH员工工资高的所有员工信息
SELECT * FROM EMP WHERE SAL >
(
SELECT SAL FROM EMP WHERE ENAME LIKE '%SMITH%'
);
– 表连接查询
SELECT * FROM EMP;
SELECT * FROM DEPT;
-- 外连接:笛卡尔乘积 表A结果 * 表B结果
SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
-- 左连接查询
SELECT * FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
-- 右连接查询
SELECT * FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
– 视图
-- DDL语句 创建视图
CREATE OR REPLACE VIEW MYVIEW
AS
SELECT EMPNO 员工编号, ENAME 姓名,JOB 职位,HIREDATE 入职日期 FROM SCOTT.EMP;
-- 删除试图
DROP VIEW MYVIEW;
-- 查看试图
SELECT * FROM MYVIEW;
-- 插入数据到试图
UPDATE MYVIEW SET JOB = 'MANAGER123' WHERE EMPNO = 1;
-- 频繁使用某个很长的查询语句时,推荐使用视图
CREATE OR REPLACE VIEW SCOTTVIEW
AS
SELECT E.*,D.DNAME,D.LOC FROM SCOTT.EMP E LEFT JOIN SCOTT.DEPT D
ON E.DEPTNO = D.DEPTNO;
-- 查看试图中的薪资大于800的员工
SELECT * FROM SCOTTVIEW WHERE SAL > 800;
-- 表连接中,添加无效,修改无效
INSERT INTO SCOTTVIEW(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DNAME,LOC)
VALUES
(2,'WANGYANG','PRESIDENT',7788,TO_DATE('1999-9-9','YYYY-MM-DD'),10000,0,40,'财务部','郑州');
UPDATE SCOTTVIEW SET ENAME='ZHANGSAN' , LOC='ZHENGZHOU' WHERE EMPNO=1;
-- 删除有效
DELETE FROM SCOTTVIEW WHERE EMPNO=1;
– 索引
-- DDL索引
CREATE INDEX INDEX_EMP_ENAME ON EMP(ENAME);
SELECT EMPNO,ENAME FROM EMP;
DROP INDEX INDEX_EMP_ENAME;
SELECT * FROM USER_INDEXES
– 序列
-- 主要解决主键自增
CREATE SEQUENCE SEQ310
INCREMENT BY 1 --每次增加
START WITH 1 --从几开始
MAXVALUE 5 --最大值
MINVALUE 0
SELECT SEQ310.NEXTVAL 下一个值, SEQ310.CURRVAL 当前值 FROM DUAL;
SELECT SEQ310.CURRVAL FROM DUAL;
DROP SEQUENCE SEQ310;
CREATE SEQUENCE SEQ311;
INSERT INTO EMP(EMPNO,ENAME) VALUES(SEQ311.NEXTVAL,'ZHANGSHAN');
SELECT * FROM EMP;
– 分页
SELECT * FROM EMP;
-- ROWNUM
SELECT E.*,ROWNUM FROM EMP E WHERE ROWNUM <= 5;
SELECT E.*,ROWNUM FROM EMP E WHERE ROWNUM <= 10 AND ROWNUM >= 6;
SELECT MYEMP.*,ROWNUM FROM ( SELECT E.* , ROWNUM RW FROM EMP E ) MYEMP
WHERE MYEMP.RW >= 6 AND MYEMP.RW <= 10
-- 此时的rownum是伪列,不能在where中运算
SELECT E.*,ROWNUM RW FROM EMP E;
-- 将rownum变成真实的列
SELECT * FROM (SELECT E.*,ROWNUM RW FROM EMP E ORDER BY SAL) MYEMP
WHERE MYEMP.RW >= 10 AND MYEMP.RW <= 15
-- 优化后的SQL语句
SELECT * FROM (SELECT E.*,ROWNUM RW FROM EMP E WHERE ROWNUM <= 10) MYEMP
WHERE MYEMP.RW >= 5
-- 嵌套优化
-- sal 在 1500 ~ 3000
SELECT * FROM (
SELECT E2.*,ROWNUM R FROM
(
SELECT * FROM EMP E WHERE E.SAL BETWEEN 1500 AND 3000
) E2 WHERE ROWNUM <= 10
) E3 WHERE E3.R >= 5
– PLSQL语法
-- 没有定义变量 注意不能在PLSQL语句做查询
SELECT * FROM BONUS;
DECLARE
BEGIN
INSERT INTO BONUS(ENAME,JOB,SAL,COMM) VALUES('WANGYANG','TEACHER',1000,500);
INSERT INTO BONUS(ENAME,JOB,SAL,COMM) VALUES('WANGKANG','TEACHER',800,200);
INSERT INTO BONUS(ENAME,JOB,SAL,COMM) VALUES('LIUZHEN','TEACHER',600,100);
COMMIT;
END;
--输出语句
DECLARE
BEGIN
DBMS_OUTPUT.put_line('HELLO,PLSQL');
END;
DECLARE -- 声明变量
NAME VARCHAR(20) := '任晓丹';
BEGIN
-- 输出语句
DBMS_OUTPUT.put_line('HELLO,'||NAME);
END;
-- 查看工资800的员工姓名
DECLARE
NAME VARCHAR(20);
BEGIN
SELECT ENAME INTO NAME FROM EMP WHERE SAL = 800;
dbms_output.put_line(NAME);
END;
--输出参数
DECLARE
ENAME VARCHAR(20);
BEGIN
SELECT ENAME INTO ENAME FROM EMP WHERE SAL = &a;
dbms_output.put_line(ename);
END;
--多变量 1.%TYPE 2.%rowtype
DECLARE
ename EMP.ENAME%TYPE;
job EMP.JOB%TYPE;
comm EMP.COMM%TYPE;
BEGIN
SELECT ENAME ,JOB,COMM INTO ename, job, comm FROM EMP WHERE SAL = &SAL;
dbms_output.put_line(ename||','||job||','||comm);
END;
-- rowtype
DECLARE
emprow EMP%rowtype;
BEGIN
SELECT * INTO emprow FROM EMP WHERE SAL=&SAL;
dbms_output.put_line(emprow.ename||','||emprow.job);
END;
– 存储过程(转账案例)
-- 存储过程最简单版
-- 输出参数
CREATE OR REPLACE PROCEDURE PR_1 (NAME IN VARCHAR,OUT_SAL OUT EMP.SAL%TYPE,OUT_JOB OUT EMP.JOB%TYPE)
AS
-- 声明变量区域
BEGIN
SELECT SAL,JOB INTO OUT_SAL,OUT_JOB FROM EMP WHERE ENAME=NAME;
END;
DECLARE
SAL EMP.SAL%TYPE;
JOB EMP.JOB%TYPE;
BEGIN
PR_1('JONES',SAL,JOB);
DBMS_OUTPUT.PUT_LINE(SAL||','||JOB);
END;
SELECT * FROM EMP;
-- 创建用户金额表
CREATE TABLE BOUNS(
NAME VARCHAR(50),
BOUN NUMBER
);
INSERT INTO BOUNS VALUES('JACK',5000);
INSERT INTO BOUNS VALUES('ROSE',1000);
INSERT INTO BOUNS VALUES('JHIN',200);
SELECT * FROM BOUNS;
-- 创建转账记录表
CREATE TABLE BONUS_INFO
(
MY VARCHAR(255),
YOU VARCHAR(255),
MONEY NUMBER(10)
);
-- 向表结构中添加一个日期类型
ALTER TABLE BONUS_INFO ADD MYDATE DATE;
-- 查看BONUS_INFO数据
SELECT * FROM BONUS_INFO;
-- 删除转账存储过程表
DROP PROCEDURE ZHUANZHUANG;
--创建转账过程存储过程表
CREATE OR REPLACE PROCEDURE ZHUANZHUANG(
MY VARCHAR,YOU VARCHAR,MONEY BOUNS.BOUN%TYPE
) AS
-- 声明变量
MYBOUN BOUNS.BOUN%TYPE;
YOUBOUN BOUNS.BOUN%TYPE;
MYCOUNT INT;
BOUN_EXCEPTION EXCEPTION;
BEGIN
SELECT BOUN INTO MYBOUN FROM BOUNS WHERE NAME=MY;
DBMS_OUTPUT.PUT_LINE(MY||'账户余额:'||MYBOUN);
IF MYBOUN>=MONEY THEN -- 判断钱包金额是否大于桩长金额
DBMS_OUTPUT.PUT_LINE('转账开始...');
UPDATE BOUNS SET BOUN=BOUN-MONEY WHERE NAME=MY;
SELECT COUNT(*) INTO MYCOUNT FROM BOUNS WHERE NAME=YOU;
IF MYCOUNT >0 THEN -- 判断用户是否存在
UPDATE BOUNS SET BOUN=BOUN+MONEY WHERE NAME=YOU;
-- 转账记录存放在BONUS_INFO表中
INSERT INTO BONUS_INFO VALUES(MY,YOU,MONEY,SYSDATE);
-- 转账成功后显示现有的金额
DBMS_OUTPUT.PUT_LINE(MY||'账户余额:'||MYBOUN);
SELECT BOUN INTO YOUBOUN FROM BOUNS WHERE NAME=YOU;
DBMS_OUTPUT.PUT_LINE(YOU||'账户余额:'||YOUBOUN);
COMMIT;-- 提交事务
ELSE
-- 账户不存在回滚事务
DBMS_OUTPUT.PUT_LINE(YOU||'账户不存在');
ROLLBACK; -- 回归MY账户
END IF;
ELSE
RAISE BOUN_EXCEPTION;
END IF;
EXCEPTION
WHEN BOUN_EXCEPTION THEN
DBMS_OUTPUT.PUT_LINE('余额不足无法开始转账!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(MY||'账户不存在');
END;
-- 执行转账
DECLARE
BEGIN
ZHUANZHUANG('JACK','ROS',1000);
END;
– SQL优化
1:SELECT 子句中避免使用 ‘ * ‘
2:使用表的别名 (Alias)
当在 SQL 语句中连接多个表时 , 请使用表的别名并把别名前缀于每个 Column 上,这样一来 , 就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误 .
3:用 > = 替代 >
高效 : SELECT * FROM EMP WHERE DEPTNO >=4
低效 : SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于: 前者 DBMS 将直接跳到第一个 DEPT 等于 4 的记录而后者将首先定位到 DEPTNO=3 的记录并且向前扫描到第一个 DEPT 大于 3 的记录 .注意:项目中需根据实际情况而定
4:GROUP BY
提高 GROUP BY 语句的效率 , 可以通过将不需要的记录在 GROUP BY 之前过滤掉 . 下面两个查询返回相同结果但第二个明显就快了许多 .
低效 : SELECT JOB , AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'
高效 : SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP JOB
备注:避免使用HAVING子句,
HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销
5 SQL文大写
SQL语句在编译执行的时候先把SQL文转换为大写,然后再执行。所以开发过程中SQL大写,能提高执行效率
6 IN 操作符(和exists)
解释:用IN写出来的SQL的优点是比较容易写以及 清晰易懂,这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的。
推荐方案:EXISTS代替。子查询时,但要结合实际情况
如果查询的两个表大小相当,那么用in和exists差别不大
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in
7 UNION操作符
解释:UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。
推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。
注意:项目中根据实际情况而定,看是否需要去重
– MYSQL与ORACLE区别
提交方式:
mysql: mysql默认是自动提交。
oracle: oracle默认不自动提交,需要用户手动提交。
其他不同:
1. Oracle是大型数据库而Mysql是中小型数据库,Oracle市场占有率达40%,Mysql只有20%左右,同时Mysql是开源的而Oracle价格非常高。
2. Oracle支持大并发,大访问量,是OLTP(On-Line Transaction Processing联机事务处理系统)最好的工具。
3. 安装所用的空间差别也是很大的,Mysql安装完后才152M而Oracle有3G左右,且使用的时候Oracle占用特别大的内存空间和其他机器性能。
语法上区别:
1,自动增长的数据类型处理:
oracle使用序列对象
create sequence sequencename ;
Mysql使用主键自增长
设置自动递增
AUTO_INCREMENT
2,空字符串问题:
Oracle中空字符串''就是null(只有null,没有空字符)
MySQL是区分null和''的。
使用语句:select * from table1 where user_name <> ''来查询列user_name不为空(不为null且不为空字符)时,Oracle会查不出任何结果,而MySQL可以正常运行。这里MySQL之所以可以得到正确结果,还因为比较符号<>会先将列为null的内容进行过滤,然后再比较内容是否为空字符串。
这就要求在编写代码的时候,尽量保证不会往数据库插入空字符串''这样的值,要么保持有数据,要么保持为null。另外,对于MySQL中已经同时存在Null和''时,所有判断是否为null或者''的地方改为判断列的长度是否为0。
3,分页语句:
oracle使用rownum伪列
select t2.* from (select t1.*,rownum cnt from CQQ_TB_STUDENT t1 where rownum<=v_end) t2 where t2.cnt>v_start
注意rownum不能使用> >= =;
Mysql使用limit关键字
select * from table limit 5;--获取前5条数据
select * from table limit startindex,psize;
eg: select * from table limit startindex,psize;--获取前5条数据
4,分组语句:
MySQL和Oracle使用group by的区别
Oracle在使用group by时,查询字段必须是分组的依据或聚合函数。
MySQL没有此限制,会自动取第一行。
– 四表查询
-- 1.学生表
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
sage DATE,
ssex ENUM ('男','女')
);
-- 2.课程表中使用了外键教师编号,因而需要先建立教师表
CREATE TABLE teacher(
tid INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20)
);
-- 3.建立课程表
CREATE TABLE course(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20),
tid INT,
FOREIGN KEY (tid) REFERENCES teacher (tid)
);
-- 4.建立成绩表
CREATE TABLE sc(
sid INT,
cid INT,
score INT
);
-- 先给student表插入数据
INSERT INTO student VALUES (1,'赵雷','1990-01-01','男'),
(2,'钱电','1990-12-21','男'),
(3,'孙风','1990-05-20','男'),
(4,'李云','1990-08-06','男'),
(5,'周梅','1991-12-01','女'),
(6,'吴兰','1992-03-01','女'),
(7,'郑竹','1989-07-01','女'),
(8,'王菊','1990-01-20','女');
-- 给teacher表插入数据,这里不可以先给course表插入数据,因为course表外键连接到teacher的主键
INSERT INTO teacher VALUES(1,'张三'),(2,'李四'),(3,'王五');
-- 给course表插入数据
INSERT INTO course VALUES(1,'语文',2),(2,'数学',1),(3,'英语',3);
-- 最后给sc表插入数据
INSERT INTO sc VALUES(1,1,90),(1,2,80),(1,3,90),(2,1,70),(2,2,60),(2,3,80),(3,1,80),
(3,2,80),(3,3,80),(4,1,50),(4,2,30),(4,3,20),(5,1,76),(5,2,87),(6,1,31),(6,3,34),(7,2,89),(7,3,98);
-- (1)查询 1 课程比 2 课程成绩高的学生的信息及课程分数
SELECT s.sid,s.sname,s.sage,s.ssex,sc1.score,sc2.score FROM student s ,sc sc1,sc sc2 WHERE
sc1.`cid`=1 AND sc2.`cid`=2 AND sc1.`score`>sc2.`score` AND sc1.`sid`=s.`sid`AND sc2.`sid`=s.`sid`;
-- (2)查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s.sid,s.sname,AVG(sc1.`score`) AS 'avg_score' FROM student s ,sc sc1 WHERE s.sid=sc1.`sid`
GROUP BY s.sid HAVING avg_score>=60 ORDER BY avg_score DESC;
-- (3)查询名字中含有"风"字的学生信息
SELECT s.sid,s.sname,s.sage,s.ssex FROM student s WHERE sname LIKE '%风%';
-- (4)查询课程名称为"数学",且分数低于60的学生姓名和分数
SELECT s.sname,sc1.score FROM student s ,sc sc1 WHERE s.sid=sc1.sid AND cid=2 AND sc1.score<60;
-- (5)查询所有学生的课程及分数情况
SELECT sc1.sid,c.cname,sc1.score FROM course c,sc sc1 WHERE c.cid=sc1.cid;
-- (6)查询没学过"张三"老师授课的同学的信息
-- 第一种普通写法:
SELECT sc1.sid FROM sc sc1,course c,teacher t WHERE c.tid = t.tid AND c.cid = sc1.cid AND t.tname='张三';
-- 第二种子查询写法:
SELECT s.* FROM student s WHERE s.sid NOT IN
(SELECT sc1.sid FROM sc sc1,course c,teacher t WHERE c.tid = t.tid AND c.cid = sc1.cid AND t.tname='张三');
-- (7)查询学过"张三"老师授课的同学的信息
-- 第一种普通写法:
SELECT s.* FROM student s,sc sc1,course c,teacher t WHERE
sc1.sid=s.sid AND t.tid=c.tid AND c.cid=sc1.cid AND t.tname='张三';
-- 第二种子查询写法:
SELECT s.* FROM student s WHERE s.sid IN
(SELECT sc1.sid FROM sc sc1,course c,teacher t WHERE c.tid = t.tid AND c.cid = sc1.cid AND t.tname='张三');
-- (8)查询学过编号为 1 并且也学过编号为 2 的课程的同学的信息
SELECT s.* FROM student s,sc sc1,sc sc2 WHERE s.sid = sc1.sid AND sc1.sid = sc2.sid AND sc1.cid = 1 AND sc2.cid =2;
-- (9)查询学过编号为 1 但是没有学过编号为 2 的课程的同学的信息
-- 第一步:先用student表左连接查出学过课程1的和学过课程2的所有学生信息
SELECT s.* FROM student s
LEFT JOIN (SELECT * FROM sc WHERE cid=1) sc1 ON s.sid = sc1.sid
LEFT JOIN (SELECT * FROM sc WHERE cid=2) sc2 ON s.sid = sc2.sid;
-- 第二步:筛选学过编号为1但是没有学过编号为2的课程的同学的信息
SELECT s.* FROM student s
LEFT JOIN (SELECT * FROM sc WHERE cid=1) sc1 ON s.sid = sc1.sid
LEFT JOIN (SELECT * FROM sc WHERE cid=2) sc2 ON s.sid = sc2.sid
WHERE (sc1.cid=1 AND sc2.cid IS NULL);
-- (10)查询没有学全所有课程的同学的信息
-- 第一种写法:
-- 第一步:先用student表左连接查出学过课程1、2、3及其它的所有学生信息
SELECT s.* FROM student s
LEFT JOIN (SELECT * FROM sc WHERE cid=1) sc1 ON s.sid = sc1.sid
LEFT JOIN (SELECT * FROM sc WHERE cid=2) sc2 ON s.sid = sc2.sid
LEFT JOIN (SELECT * FROM sc WHERE cid=3) sc3 ON s.sid = sc3.sid;
-- 筛选没有学全所有课程的同学的信息
SELECT s.* FROM student s
LEFT JOIN (SELECT * FROM sc WHERE cid=1) sc1 ON s.sid = sc1.sid
LEFT JOIN (SELECT * FROM sc WHERE cid=2) sc2 ON s.sid = sc2.sid
LEFT JOIN (SELECT * FROM sc WHERE cid=3) sc3 ON s.sid = sc3.sid
WHERE (sc1.cid IS NULL OR sc2.cid IS NULL OR sc3.cid IS NULL);
-- 上面写法比较繁琐,建议用楼下写法
-- 第二种写法:
-- 第一步:先把三个课程都学的学员编号查出来
SELECT sc1.sid FROM sc sc1,sc sc2,sc sc3
WHERE (sc1.cid=1 AND sc2.cid =2 AND sc3.cid =3 AND sc1.sid=sc2.sid AND sc1.sid = sc3.sid);
-- 第二步:对立的查询思路,三个课程都学完的同学对立面是三个课程没学完或者一个都没学的
SELECT s.* FROM student s WHERE s.sid NOT IN
(SELECT sc1.sid FROM sc sc1,sc sc2,sc sc3
WHERE (sc1.cid=1 AND sc2.cid =2 AND sc3.cid =3 AND sc1.sid=sc2.sid AND sc1.sid = sc3.sid))
GROUP BY s.sid;
-- (11)查询至少有一门课与学号为"1"的同学所学相同的同学的信息
SELECT DISTINCT s.* FROM student s,sc sc1 WHERE
s.sid=sc1.sid AND sc1.cid IN(SELECT cid FROM sc WHERE sid=1) AND s.sid<> 1;
-- (12)查询和"1"号的同学学习的课程完全相同的其他同学的信息
-- 第一步:先查出1号同学学习的所有的课程编号
-- 当前数据设计1号同学选修了所有课程
SELECT cid FROM sc WHERE sid=1;
-- 第二步:查出选修了1号学生没有选修课程的学生编号,这一步很关键,我们用cid去过滤,所以后面再用not in的时候那个子集里一定有和1号学生选修的课程完全相同的同学
-- 因为1号同学选修了所有课程,所以没有符合条件的学生编号
SELECT sid FROM sc WHERE cid NOT IN (SELECT cid FROM sc WHERE sid=1);
-- 如果查询结果中有重复的sid,因为有的同学选修的1号同学没选的课程不止一门,可以使用distinct对sid进行去重处理
-- 第三步:查询选修的课程是1号学生选修课程的子集的学生编号
-- 因为第二步查出的是选修了1号学生没有选修课程的学生编号,逆向思维,再用not in,双重否定变肯定,查出的就是和1号同学有一门、多门、或者全部课程的同学编号
-- 可以使用distinct对sid进行去重处理
SELECT sid FROM sc WHERE sid NOT IN (SELECT sid FROM sc WHERE cid NOT IN (SELECT cid FROM sc WHERE sid=1));
-- 第四步:从上述查询结果中,筛选出选修的课程数量与1号学生选修的课程数量相等的其他学生的编号
SELECT sid FROM sc WHERE sid NOT IN
(SELECT sid FROM sc WHERE cid NOT IN (SELECT cid FROM sc WHERE sid=1))
GROUP BY sid
HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE sid =1) AND sid <>1;
-- COUNT(*):统计返回的行数 当sid=1时,有3行数据
SELECT COUNT(*) FROM sc WHERE sid=1; -- 3
-- 第五步:以上述查询结果为筛选条件,从student表中查询出与1号学生学习的课程完全相同的其他学生的信息
SELECT s.* FROM student s WHERE sid IN
(SELECT sid FROM sc WHERE sid NOT IN
(SELECT sid FROM sc WHERE cid NOT IN (SELECT cid FROM sc WHERE sid=1))
GROUP BY sid
HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE sid =1) AND sid <>1);
/*第十二题思路总结:
01号之外的其他学生可以分成两个大类,一类是选修了01号学生没有选修的课程的学生
另一类学生选修的课程是01号学生选修的课程的子集,这个子集是选修了和1号学生里的一门、多门、
采用逆向思维,可以先找出选修了01号学生没选课程的学生编号,然后以01号学生选修的课程数量为筛选条件,
从剩下的选修的课程是01号学生选修的课程的子集这类学生中筛选出与01号学生所选课程完全相同的学生编号,
此编号包含了01,以剔除了01之后的编号为筛选条件
从student表中选出和01号同学学习的课程完全相同的其他同学的信息*/
-- (13)查询没学过"张三"老师讲授的任一门课程的学生信息
-- 第一步:张三老师tid是1,cid是2,逆向思维,查询学过"张三"老师讲授的任一门课程的学生编号
SELECT sc1.sid FROM sc sc1,course c,teacher t WHERE
t.tid = c.tid AND sc1.cid = c.cid AND t.tname='张三';
-- 第二步:以上查询结果为筛选条件,从student表中查询出没学过"张三"老师讲授的任一门课程的学生信息
SELECT s.* FROM student s WHERE s.sid NOT IN
(SELECT sc1.sid FROM sc sc1,course c,teacher t WHERE
t.tid = c.tid AND sc1.cid = c.cid AND t.tname='张三');
-- (14)查询出只有两门课程的全部学生信息
SELECT s.* FROM student s,sc GROUP BY sc.sid HAVING COUNT(sc.sid)=2 AND s.sid=sc.sid;
-- (15)查询1990年出生的学生信息(注:student表中sage列的类型是datetime)
-- 第一种写法:
SELECT s.* FROM student s WHERE s.sage>='1990-01-01' AND s.sage<='1990-12-31';
-- 第二种写法:模糊查询
SELECT s.* FROM student s WHERE s.sage LIKE '1990-%';
-- (16)查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT sc.cid,AVG(score) AS avg_score FROM sc
GROUP BY sc.cid ORDER BY avg_score DESC,sc.cid ASC;
-- (17)查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT s.sname,c.cname,sc.score FROM student s,course c,sc
WHERE s.sid = sc.sid AND sc.cid = c.cid AND sc.score >=70;
-- (18)查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩,并按照平均成绩降序排列
SELECT s.sid,s.sname,AVG(score) AS avg_score FROM student s,sc
WHERE s.sid = sc.sid GROUP BY s.sid HAVING avg_score >=85 ORDER BY avg_score DESC;
-- (19)查询成绩不及格的课程和学生姓名
SELECT s.sname,c.cname,sc.score FROM student s,course c,sc
WHERE s.sid=sc.sid AND sc.cid=c.cid AND sc.score<60;
-- (20)查询课程编号为1且课程成绩在80分以上的学生的学号和姓名
SELECT s.sid,s.sname FROM student s,sc WHERE
s.sid=sc.sid AND sc.cid =1 AND sc.score>=80 GROUP BY s.`sid`;
-- (21)求每门课程的学生人数
SELECT cid AS '课程编号',COUNT(sid)AS '课程人数' FROM sc GROUP BY sc.cid;
-- (22)统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数
SELECT cid AS '课程编号',COUNT(sid)AS '课程人数' FROM sc GROUP BY sc.cid HAVING COUNT(sid)>5 ORDER BY COUNT(sid),sc.cid ASC;
-- (23)查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT s1.sid,sc1.cid,sc1.score,s2.sid,sc2.cid,sc2.score FROM student s1,student s2,sc sc1,sc sc2
WHERE s1.sid=sc1.sid AND s2.sid=sc2.sid AND sc1.cid <>sc2.cid AND sc1.score = sc2.score;
-- (24)检索至少选修两门课程的学生学号
SELECT sid FROM sc GROUP BY sc.sid HAVING COUNT(cid)>=2;
-- (25)查询选修了全部课程的学生信息
SELECT * FROM student s,sc WHERE s.sid =sc.sid GROUP BY s.sid HAVING COUNT(cid)=3;
-- (26)查询各学生的年龄(年龄保留整数)
SELECT s.sname,ROUND((TO_DAYS('2020-06-11')-TO_DAYS(s.sage))/365) AS age FROM student s;
-- (27)查询本月过生日的学生姓名和出生年月
-- _____ :五个下划线长度
SELECT s.sname,s.sage FROM student s WHERE s.sage LIKE '_____07%';
-- (28)查询下月过生日的学生
SELECT s.sname,s.sage FROM student s WHERE s.sage LIKE '_____08%';
-- (29)查询学全所有课程的同学的信息
SELECT s.* FROM student s,sc WHERE
s.sid=sc.sid GROUP BY s.sid HAVING COUNT(sc.cid)=3;