SQL基础知识

最新推荐文章于 2024-01-15 15:56:07 发布

老子大威天龙

最新推荐文章于 2024-01-15 15:56:07 发布

阅读量204

点赞数

分类专栏： Oracle 数据库文章标签： sql oracle plsql

本文链接：https://blog.csdn.net/X_lsod/article/details/115447327

版权

Oracle 数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

SQL基础知识

主键不应该有业务意义

表空间
数据文件

一个表空间对应一个或者多个数据文件

CREATE USET XXX IDENTITIES BY XXXX

创建用户默认的表空间是USERS，同时表空间USERS对应的数据文件是users01.dbf（大小可以规定上限）

SQL语句关键字不区分大小写，可以分行写
实际写SQL语句时一定记得在末尾加上分号
在oracle数据库中，表名、列字段、函数名默认是不区分大小写的

SQL语句执行顺序：

FROM子句
WHERE子句
GROUP BY子句
聚集函数
HAVING子句
SELECT子句
ORDER BY子句

常用的聚合函数有COUNT，MAX， MIN，SUM，AVG。聚合函数的执行在group by之后，having之前

一、表格的增删查改

1、建表

CREATE TABLE SCORE
(
      ID  VARCHAR2(10) PRIMARY KEY,
      语文 NUMBER(4),
      数学 NUMBER(4),
      英语 NUMBER(4)
);


列级约束：
CREAT TABLE studetn
(
    id NUMBER(8) PRIMARY KEY,
    stu_no NUMBER(10),
    stu_name VARCHAR2(30),
);

表级约束：
CREAT TABLE studetn
(
    id NUMBER(8),
    stu_no NUMBER(10),
    stu_name VARCHAR2(30),
    CONSTRAINT student_pk PRIMARY KEY(id),
    CONSTRAINT student_major_fk FOREIGN KEY REFERENCES major(major_id),
    CONSTRAINT student_class_fk FOREIGN KEY REFERENCES class(class_id)
);

区别：列级约束不能自定义名称，而表级约束可以自定义名称

2、删除

--删除表
DROP TABLE SCORE;

--删除表的所有数据（不删除表）
DELETE FROM class;

--删除表的特定元组数据
DELETE FROM class
WHERE CLASS_ID=1;

3、插入信息

--插入所有属性的值
INSERT INTO SCORE VALUES(70,80,58);

--插入对应列的属性值
INSERT INTO STUDENT(ID,STU_NO) VALUES(2,1800354);

4、更新表

--更改表的结构，删除class表中列属性CLASS_NAME
ALTER TABLE CLASS DROP COLUMN CLASS_NAME;

--更改表的结构，为class表增加列属性CLASS_NAME
ALTER TABLE CLASS ADD CLASS_NAME VARCHAR2(30);

--没有更改表的结构，只是修改对应元组的属性值
UPDATE CLASS SET CLASS_NAME='混元内功' WHERE CLASS_ID=1;

5、查询表的结构：

DESCRIBE EMPLOYEES;
--或者
DESC EMPLOYEES;

关键字

SELSCT
FROM 等

子句

SELECT *
FROM employees

语句

SELECT * FROM employees;

查询特定列

SELECT employee_id,last_name,phone_number FROM employees;

查询所有的列

SELECT * FROM employees;          --实际项目禁止用*
SelecT * FrOM employees;          --不区分大小写

SQL支持数学运算

SELECT last_name,12*salary FROM employees;

列的别名

SELECT last_name,12*salary AS "年薪" FROM employees;

NULL值得列会参与数学运算，结果也是NULL

SELECT last_name,12*salary*commissioin_pct FROM employees;

使用函数可以将NULL的值置为1

SELECT last_name,12*salary*NVL(commissioin_pct,1) FROM employees;   
--相当于if else ，如果commission为NULL，则赋值为1，否则就为本身

输出XXX的年薪是XXX

SELECT last_name||'的年薪是'||12*salary*NVL(commissioin_pct,1) FROM employees;

去掉重复行，即查找唯一值

SELECT DISTINCT department_id FROM employees;

条件查询，查询出所有90号部门的员工的信息，WHERE子句

SELECT last_name,department_id
FROM employees
WHERE department_id=90;

查询所有2005年以前入职的员工的信息

SELECT * FROM employees WHERE hire_date<'01-1月-05';      --oracle默认日期格式DD-MON-RR，MON只能用中文

修改session的日期格式，session：会话

ALTER SESSION SET nls_date_format='yyyy-mm-dd';       
  --ALTER SESSION更改的是当前的会话，如果ALTER SYSTEM那就是修改全局了
SELECT * FROM employees WHERE hire_date<'2005-01-01';

数值区分大小写

SELECT * FROM employees WHERE email='PSULLY';        --这里如果PSULLY改为小写就查找不到匹配的内容了

查询工资在3000（包含）到5000（包含）之间的所有员工的信息

SELECT last_name,salary FROM employees WHERE salary BETWEEN 3000 AND 5000;
SELECT last_name,salary FROM employees WHERE salary >= 3000 AND  salary<= 5000;

查询出50和90号部门的所有员工

SELECT last_name ,department_id FROM employees WHERE department_id=50 OR department_id=90;
SELECT last_name ,department_id FROM employees WHERE department_id IN(50,90);

查询除了50和90以外的部门的员工信息

SELECT last_name ,department_id FROM employees WHERE department_id NOT IN(50,90);

查询出没有奖金的员工的信息

SELECT last_name ,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

二、模糊查询和排序

LIKE模糊查询

--  %代表0或多个字符
SELECT * FROM employees WHERE last_name LIKE '%ra%';  

--  _表示单个字符         
SELECT * FROM employees WHERE last_name LIKE '_ra%'; 

-- 转义字符          
SELECT * FROM employees WHERE job_id LIKE 'SA\_%' ESCAPE '\';

排序：ORDER BY (默认升序)


--别名排序
SELECT 12*salary AS 年薪 FROM employees order by 年薪;   

--多列排序
SELECT * FROM employees order by employee_id;

降序排列（在需要降序的列后面加上 DESC即可，其余列仍是升序排列）：

SELECT * FROM employees order by employee_id desc;

三、函数

1、单行函数

字符函数：

--LOWER
SELECT last_name,LOWER(email) FROM employees; 

--dual表是个虚表，不需要创建，主要用于测试演示
SELECT CONCAT('guolin','山水甲天下') FROM dual; 

--下标从1开始，可以为负值
SELECT SUBSTR('桂林山水甲天下',1,1) FROM dual;  

--判断第二个串是不是第一个的子串
SELECT INSTR('桂林山水甲天下'，'天下') FROM dual;

--字段不分大小写
SELECT last_name,RPAD(JOB_ID,10,' ') FROM employees;  

--去掉前后空格
SELECT TRIM('    ABCD  EF G  ') FROM dual;  

--去点前后的'H'
SELECT TRIM('H' FROM 'HelloHWorldH') FROM dual;

数字函数：

--四舍五入，保留两位小数
SELECT ROUND(49.568,2) FROM dual;   

 --截断          
SELECT TRUNC(49.568,2) FROM dual;   

 --求余数         
SELECT MOD(49,2) FROM dual;

日期函数：查看及基本运算

--更改会话的日期格式
ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YY HH24:MI:SS';

--查看日期
SELECT sysdate FROM dual;   

 --日期+1，返回日期
SELECT sysdate+1 FROM dual;               

--日期相减
SELECT sysdate - to_date('2020-03-01','yyyy-mm-dd') FROM dual;     
SELECT sysdate - 5/24  FROM dual;            --5小时以前
SELECT sysdate MONTHS_BETWEEN(sysdate,to_date('2020-03-01','yyyy-mm-dd')) FROM dual; 
SELECT NEXT_DAY(sysdate,'星期五') FROM dual;
SELECT LAST_DAY(to_date('2020-02-15','yyyy-mm-dd')) FROM dual;     --查询月份最后一天
SELECT ROUND(to_date('2020-07-01','yyyy-mm-dd'),'MONTH') FROM dual; --超过15天直接算下一个月

转换函数：
1. 隐式转换（自己转换了）
2. 显式转换：

SELECT LENGTH(SYSDATE) FROM DUAL;
SELECT LAST_NAME,TO_CHAR(SALARY,'L99,999.0') FROM EMPLOYEES;
SELECT TO_NUMBER('￥4,200.0','L99,999.0') FROM DUAL;

常规函数：

--NVL(expr1,expr2)如果expr1是null，则返回expr2，否则返回ezpr1；
SELECT NVL(NULL,33) FROM DUAL;

--NVL2(expr1,expr2,expr3)如果expr1是null，则返回expr2，否则返回ezpr3；
SELECT NVL2(NULL,33,44) FROM DUAL;

SELECT NULLIF(33,44) FROM DUAL;

--返回第一个非空值
SELECT COALESCE(NULL,NULL,33,NULL,22) FROM DUAL;    

SELECT CASE WHEN 语文 >= 80 THEN '优秀'
			WHEN 语文
			
--如果abc==abcD，则取值11，如果abc==CCC，则取值33			
SELECT DECODE('abc','abcD',11,'CCC',22,33) FROM DUAL;


--decode(expr,expr1,expr2,expr3,expr4,expr5)    
--如果expr==expr1，则表达式取值expr2，如果expr==expr3，则表达式取值expr4，若都不相等则取默认值expr5
--sign(x)   x为正数返回1，负数返回-1，0返回0
SELECT DECODE(SIGN(语文-79.9),1,'优秀',DECODE(SIGN(语文-59.9),1,'及格','不及格')) AS 语文,
       DECODE(SIGN(数学-79.9),1,'优秀',DECODE(SIGN(数学-59.9),1,'及格','不及格')) AS 数学,
       DECODE(SIGN(英语-79.9),1,'优秀',DECODE(SIGN(英语-59.9),1,'及格','不及格')) AS 英语
       FROM SCORE;

2、多行函数

COUNT
AVG
MIN
MAX
SUM

---练习3 180~186

--1、显示当前日期
SELECT TO_CHAR(SYSDATE,'DD-MON-YY') AS "DATE" FROM DUAL;

--2、
SELECT EMPLOYEE_ID,LAST_NAME,SALARY,SALARY*1.15 AS "New Salary" FROM EMPLOYEES;

--3、显示2中的查询

--4、
SELECT EMPLOYEE_ID,LAST_NAME,SALARY,
       SALARY*1.15 AS "New Salary",
       SALARY*1.15-SALARY AS "Increase" 
       FROM EMPLOYEES;

--5、
SELECT REPLACE(LAST_NAME,SUBSTR(LAST_NAME,2),LOWER(SUBSTR(LAST_NAME,2))) AS "NAME",LENGTH(LAST_NAME) AS "LENGTH" 
       FROM EMPLOYEES 
       WHERE LAST_NAME LIKE 'J%' OR LAST_NAME LIKE 'A%' OR LAST_NAME LIKE 'M%'
       ORDER BY LAST_NAME;
       
--6、
SELECT LAST_NAME,ROUND((SYSDATE-HIRE_DATE)/30) AS "MONTHS_WORKED"
       FROM EMPLOYEES ORDER BY MONTHS_WORKED;       --这里别名加不加双引号都能查

四、GROUP BY–分组

--1、在SELECT语句中的列如果不在聚集函数中，那就一定要在GROUOP BY字句中
--2、在GROUOP BY字句中的列不一定需要出现在SELECT语句中

--求每个部门的平均工资
SELECT DEPARMENT_ID,AVG(SALARY) FROM EMPLOYEES GROUP BY DEPARMENT_ID ORDER BY DEPARMENT_ID;

--求平均工资大于8000的组
SELECT DEPARMENT_ID,AVG(SALARY) 
	FROM EMPLOYEES GROUP BY DEPARMENT_ID HAVING AVG(SALARY)>8000;
	
SELECT * FROM(SELECT DEPARMENT_ID,AVG(SALARY) FROM 
              EMPLOYEES GROUP BY DEPARMENT_ID HAVING AVG(SALARY)>8000) 
              WHERE 

SELECTTOWARD,COUNT(TOWARD) FROM ROMINFO GROUP BY ROWARD HAVING COUNT(TOWARD)>1;

SELECT MANAGER_ID,MIN(SALARY) 
	FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL 
	GROUP BY MANAGER_ID HAVING MIN(SALARY)>6000 
	ORDER BY MIN(SALARY) DESC;

GROUP BY 和 ORDER BY 一起使用

--1、ORDER BY 子句一定是出现在GROUOP BY 子句后面
--2、ORDER BY 中的列要出现在GROUOP BY子句或者聚集函数中

--行转列：
SELECT T.姓名,SUM(语文) 语文,SUM(数学) 数学,SUM(英语) 英语
       FROM(SELECT I.STU_NO,I.STU_NAME 姓名,DECODE(G.SUBJECT,'语文',G.GRADE,0) 语文,
                   DECODE(G.SUBJECT,'数学',G.GRADE,0) 数学,
                   DECODE(G.SUBJECT,'英语',G.GRADE,0) 英语
                   FROM STU_INFO I,STU_GRADE G
                   WHERE G.STU_NO=I.STU_NO) T
       GROUP BY T.STU_NO,T.姓名          
       ORDER BY T.STU_NO;       
       
       --这里不能缺少T.STU_NO和T.姓名
       --ORDER BY一定是在GROUOP BY 之后，且它的列必须要出现在GROUP BY 中

--例子：
--学生信息表
--学生信息表

CREATE TABLE STU_INFO
(
      STU_NO   NUMBER(10),
      STU_NAME VARCHAR2(10),
      STU_AGE  NUMBER(10)
);          

--学生成绩表
CREATE TABLE STU_GRADE
(
      STU_NO  NUMBER(10),
      SUBJECT VARCHAR2(10),
      GRADE   NUMBER(3)
);


ALTER TABLE STU_INFO  ADD CONSTRAINTS STU_INFO_PK  PRIMARY KEY(STU_NO);
ALTER TABLE STU_GRADE ADD CONSTRAINTS STU_GRADE_PK PRIMARY KEY(STU_NO,SUBJECT);
ALTER TABLE STU_GRADE ADD CONSTRAINTS STU_GRADE_FK FOREIGN KEY(STU_NO) REFERENCES STU_INFO(STU_NO);

INSERT INTO STU_INFO VALUES(0001,'张三',18);
INSERT INTO STU_INFO VALUES(0002,'李四',20);
INSERT INTO STU_INFO VALUES(0003,'王五',19);

INSERT INTO STU_GRADE VALUES(0001,'语文',100);
INSERT INTO STU_GRADE VALUES(0001,'数学',93);
INSERT INTO STU_GRADE VALUES(0001,'英语',80);
INSERT INTO STU_GRADE VALUES(0002,'语文',80);
INSERT INTO STU_GRADE VALUES(0002,'数学',90);
INSERT INTO STU_GRADE VALUES(0002,'英语',100);
INSERT INTO STU_GRADE VALUES(0003,'语文',90);
INSERT INTO STU_GRADE VALUES(0003,'数学',85);
INSERT INTO STU_GRADE VALUES(0003,'英语',80);


     STU_NO STU_NAME       STU_AGE
----------- ---------- -----------
          1 张三                18
          2 李四                20
          3 王五                19
          
          

     STU_NO SUBJECT    GRADE
----------- ---------- -----
          1 语文         100
          1 数学          93
          1 英语          80
          2 语文          80
          2 数学          90
          2 英语         100
          3 语文          90
          3 数学          85
          3 英语          80
--------------------------------------------------------

--1、计算学生的总分和平均分，按总分降序排序，结果如下显示

姓名             总分     平均分
---------- ---------- ----------
张三              273         91
李四              270         90
王五              255         85

SELECT I.STU_NAME 姓名,NVL(SUM(GRADE),0) 总分,NVL(AVG(GRADE),0) 平均分
       FROM STU_INFO I,STU_GRADE G
       WHERE I.STU_NO=G.STU_NO
       GROUP BY I.STU_NAME
       ORDER BY 总分 DESC;

--2、查询张三的信息和成绩，结果如下显示

姓名              年龄 科目         得分
---------- ----------- ---------- ----
张三                18 数学         93
张三                18 英语         80
张三                18 语文        100

SELECT I.STU_NAME 姓名,I.STU_AGE 年龄,G.SUBJECT 科目,G.GRADE 得分
       FROM STU_INFO I,STU_GRADE G
       WHERE I.STU_NO=G.STU_NO AND I.STU_NAME='张三';


--3、使学生姓名和科目成绩在同一行，要求如下格式显示（本体需要使用行转列的方法）

姓名             语文        数学         英语
---------- ---------- ---------- ----------
张三              100         93         80
李四               80         90        100
王五               90         85         80

SELECT T.姓名,SUM(语文) 语文,SUM(数学) 数学,SUM(英语) 英语
       FROM(SELECT I.STU_NO,I.STU_NAME 姓名,DECODE(G.SUBJECT,'语文',G.GRADE,0) 语文,
                   DECODE(G.SUBJECT,'数学',G.GRADE,0) 数学,
                   DECODE(G.SUBJECT,'英语',G.GRADE,0) 英语
                   FROM STU_INFO I,STU_GRADE G
                   WHERE G.STU_NO=I.STU_NO) T
       GROUP BY T.STU_NO,T.姓名                                       
       ORDER BY T.STU_NO;                                            
       --这里不能缺少T.STU_NO和T.姓名，因为：
       --1、ORDER BY一定是在GROUOP BY 之后，且它的列要出现在GROUP BY或者聚集函数中
       --2、使用了聚集函数SUM，那SELECT中的列如果不在聚集函数中，那就一定要出现在GROUP BY中。

五、多组查询

1、内连接（等值连接）

SELECT FIRST_NAME,DEPARTMENT_NAME FROM EMPLOYEES E ,DEPARTMENTS D
	WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

SELECT FIRST_NAME,DEPARTMENT_NAME FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

SELECT D.DEPARTMENT_NAME NAME,LOCATION_ID LOCATION,COUNT(E.EMPLOYEE_ID),ROUND(AVG(SALARY),2)
	FROM EMPLOYEES E,DEPARTMENTS D 
	WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
	GROUP BY D.DEPARTMENT_NAME,LOCATION_ID;

2、外链接

左连接

SELECT P.NAME,SUM(NVL(P.PRICE*O.AMOUNT),0) 销售额 FROM PRODUCT P LEFT JOIN ORDER O
	ON P.ID=O.ID
	GROUP BY P,NAME
	ORDER BY 销售额;
	
	
SELECT * FROM(
    SELECT P.NAME,SUM(NVL(P.PRICE*O.AMOUNT,0)) 销售额 FROM PRODUCT P LEFT JOIN ORDERS O
	ON P.ID=O.ID
	GROUP BY P.NAME
	ORDER BY 销售额)
	WHERE ROWNUM<=3;