知名的设计公司网站,深圳网站建设找哪家公司好,新手做销售怎么开发客户,网站首页建设建议本文章主要内容
1、表的管理#xff1a;创建表#xff0c;修改表结构#xff0c;删除字段#xff0c;修改字段#xff0c;添加字段#xff0c;删除表#xff0c;添加表约束#xff1b;
2、数据管理#xff1a;新增记录#xff0c;修改记录#xff0c;删除记录创建表修改表结构删除字段修改字段添加字段删除表添加表约束
2、数据管理新增记录修改记录删除记录查询数据
3、查询数据普通程序条件查询各种查询条件的灵活应用
4、多表连接内连接左外连接右外连接完全连接自然连接交叉连接
5、子查询
目标
•SQL语言分类DDLDMLDCLTCL•Oracle中的数据类型•Oracle数据定义语言•表完整性与约束•Oracle操纵语言SQL
•SQL 是 Structured Query Language结构化查询语言的首字母缩写词••SQL 是数据库语言它可以对数据库中的数据进行查询、插入、更新和删除并对数据库中的对象进行控制。••SQL已经成为RDBMS的标准语言最新标准是ANSI-99并支持面向对象的数据库。••关系型数据库系统对SQL也进行了一些扩展使之成为过程性语言如oralce中的PL/SQL 与微软的Transact-SQLSQL分类
SQL 支持下列类别的命令 数据定义语言DDL数据操纵语言DML事务控制语言TCL数据控制语言DCL Oracle 数据类型
创建表时必须为各个列指定数据类型以下是 Oracle 数据类型的类别字符类型 CHAR 固定长度的字符串列长度可以是 1 到 2000 个字节 VARCHAR2 支持可变长度字符串,大小在1至4000个字节范围 LONG 存储可变长度字符数据,最多能存储 2GB 数值类型 NUMBER NUMBER [( p[, s])]存储整数、浮点数和实数最高精度为 38 位 日期类型 DATE 存储日期和时间部分精确到整个的秒 TIMESTAMP 存储日期、时间和时区信息秒值精确到小数点后6位 RAW RAW 存储二进制数据最多能存储 2000 字节 LONG RAW 存储可变长度的二进制数据最多能存储 2 GB 大对象 LOB LOB 类型可以存储 4GB内容LOB 数据类型允许对数据进行高效、随机、分段的访问 CLOB 即 Character LOB它能够存储大量字符数据 BLOB 即 Binary LOB二进制 LOB可以存储较大的二进制对象如图形、视频剪辑和声音文件 BFILE 即 Binary File用于将二进制数据存储在数据库外部的操作系统文件中 伪列 伪列就像一个表列但是它并没有存储在表中伪列可以从表中查询但不能插入、更新和删除它们的值 ROWID 表中行的存储地址可以使用 ROWID 伪列快速地定位表中的一行 ROWNUM ROWNUM 是查询返回的结果集中行的序号可以使用它来限制查询返回的行数
数据定义语言
•数据定义语言用于改变数据库结构包括创建、更改和删除数据库对象•用于操纵表结构的数据定义语言命令有CREATE TABLEALTER TABLETRUNCATE TABLEDROP TABLE数据定义语言举例 创建表
CREATE TABLE DEPT ( DEPTNO NUMBER (2) NOT NULL, DNAME VARCHAR2 (14), LOC VARCHAR2 (13), PRIMARY KEY ( DEPTNO )
)修改表
修改LOC字段
ALTER TABLE DEPT MODIFY LOC VARCHAR2(20);
添加字段
ALTER TABLE DEPT ADD MGR NUMBER(4);
添加约束
ALTER TABLE DEPT ADD CONSTRAINT FK_EMPFOREIGN KEY (MGR) REFERENCES EMP (EMPNO)
删除字段
ALTER TABLE DEPT DROP COLUMN MGR;删除表
DROP TABLE DEPT;
DML – 利用现有的表创建表
语法CREATE TABLE new_table_name ASSELECT column_names FROM old_table_name;SQL CREATE TABLE NEWEMPAS SELECT * FROM EMP;SQL CREATE TABLE NEWEMPAS SELECT EMPNO, ENAME, SAL FROM EMP;不复制原表数据到新表
SQLCREATE TABLE NEWEMPAS SELECT EMPNO, ENAME, SAL FROM EMP WHERE 1 2;表完整性与约束
•完整性实体完整性•主键约束保证数据唯一性域完整性•字段规则如性别必须是男或女年龄在0-200参照完整性•外键约束,外键对应的记录必须存在主键约束alter table DEPT add constraint pk_DEPT primary key(deptno);
外键约束alter table EMP add constraint fk_d_m foreign key(deptno) references DEPT(deptno);
Check 约束Alter table emp add constraint ck_emp_sex check(sex‘男’ or sex‘女’)
Unique唯一约束
Alter table emp add constraint uk_emp unique(fname,lname)数据操纵语言
数据操纵语言用于检索、插入和修改数据数据操纵语言是最常见的SQL命令数据操纵语言命令包括SELECTINSERTUPDATEDELETEDML – INSERT
插入日期类型的值日期数据类型的默认格式为“DD-MON-YY”使用日期的默认格式使用TO_DATE函数转换INSERT INTO emp VALUES(1000,PETER,7369,12-5月-05,2800,1);INSERT INTO emp VALUES(1001,‘MIKE,7369, TO_DATE(2005-10-18, YYYY-MM-DD),2800,1);INSERT INTO emp (empno,ename,sal) VALUES(1000,PETER,2800 );插入来自其它表中的记录
语法INSERT INTO table_name [(cloumn_list)] SELECT column_names FROM other_table_name;
SQL INSERT INTO NEWEMPSELECT * FROM EMP; SQL INSERT INTO NEWEMP(EMPNO,ENAME,SAL) SELECT EMPNO,ENAME,SAL FROM EMP; DML – update
INSERT INTO emp VALUES(1001,PETER,7369,12-5月-05,2800,1);UPDATE emp SET salsal100; --所有员工工资加100UPDATE emp SET ename‘PETER ZHANG’ where empno‘1001’;UPDATE emp SET ename‘PETER CHEN’,mgr‘7876’,hiredateto_date(‘2004/10/21’,’YYYY/MM/DD’),sal3200,deptno2
WHERE empno‘1001’;SQL DELETE EMP WHERE deptno 1 AND hiredateto_date(‘2005/10/10’,‘YYYY/MM/DD’); --删除符合条件的数据
SQL DELETE EMP --删除所有行数据控制语言
数据控制语言为用户提供权限控制命令 用于权限控制的命令有GRANT 授予权限REVOKE 撤销已授予的权限SQL GRANT SELECT, UPDATE ON EMP TO user;SQL GRANT SELECT ON EMP TO user WITH GRANT OPTION;SQL GRANT UPDATE(SAL, HIREDATE) ON EMP TO user;SQL REVOKE SELECT, UPDATE ON EMP FROM user;SQL 操作符
Oracle 支持的 SQL 操作符分类如下SELECT
where子句中的运算符 查询条件 运算符 比较 ,,,,,,!,NOT 范围 between and,not between and 集合 In,not in 字符匹配 like,not like 空值 is null,is not null 多重条件 and,or Oracle中的用例表
SQLconn sys/change_on_install as sysdba
SQLalter user hr identified by hr account unlock; sys登录解锁hr用户并修改密码为hr
SQLconn hr/hr
SQLselect table_name from user_tables;
Departments 部门信息表
Employees 雇员信息表
Jobs 职位信息表
Jobs_history 工作历史信息表
Locations 地址信息表
Regions 地区信息表
可以通过Desc tableName语句查看表的基本信息SELECT
Order By用于制定查询结果的排列循序后面可以跟多个字段名越在前面的排序优先权越高。默认为升序,Desc(降序)和Asc升序
select empno,ename,sal from emp order by sal asc;select empno,ename,sal from emp order by deptno asc,hiredate desc;between andnot between and可以进行某一范围的查询 ,范围包含最大值和最小值 。Select * from emp where sal between 2000 and 5000;Select * from emp where sal not between 2000 and 5000;innot in是集合运算符只要查询的值等于集合中的一个元素表达式为真。Select * from emp where empno in (select mgr from EMP)Select * from emp where empno not in(7389,7367,7782)likenot like
模糊匹配“_”表示任意一个字符“%”表示任意多个字符如果字符串中有与匹配符相同的字符需要使用逃逸字符串escape 。select * from emp where ename like ‘S%’;select * from emp where empno not in(7389,7367,7782);select * from emp where ename like MULS#_I_ escape #‘And 多个条件同时成立时条件表达式才为真select * from emp where empno7600 and sal2000Or多个条件中只要有一个条件为真时条件表达式就为真select * from emp where empno7600 or sal2000
连接查询
•内连接•外连接左外连接(LEFT JOIN) 返回左边表的所有行右外连接(RIGHT JOIN) 返回右边表的所有行实现全外连接(FULL JOIN) 返回连个表的所有数据交叉连接(CROSS JOIN) 返回连接表中所有数据行的笛卡尔积CREATE TABLE DEPT ( DEPTNO NUMBER (2) NOT NULL, DNAME VARCHAR2 (14), LOC VARCHAR2 (13), PRIMARY KEY ( DEPTNO )
);CREATE TABLE EMP ( EMPNO NUMBER (4) NOT NULL, ENAME VARCHAR2 (10), MGR NUMBER (4), HIREDATE DATE, SAL NUMBER (7,2), DEPTNO NUMBER (2), CONSTRAINT PK_EMPPRIMARY KEY ( EMPNO )
);
•内连接
查询所有员工及其所在的部门信息SELECT e.*, d.DNAME from EMP e, DEPT d WHERE e.DEPTNO d.DEPTNO AND e.SAL 2000
查询部门信息SELECT d.*, e.ENAME from DEPT d, EMP e WHERE e.MGR e.EMPNO and d.deptnoe.deptno•左外连接
查询所有部门及其部门下的员工(没有员工的部门也要查询出来)
select d.*, e.ename from dept d left join emp e on d.deptnoe.deptno
select d.*, e.ename from dept d,emp e where d.deptno e.deptno;
查询所有部门编号大于20及其部门下的员工
select d.*, e.ename from dept d left join emp e on d.deptnoe.deptno WHERE d.deptno 20全外连接SELECT e.empno,e.ename,d.dname FROM EMP e FULL JOIN dept d ON e.deptnod.deptno•交叉连接SELECT e.empno, e.ename,d.name FROM EMP e CROSS JOIN DEPT对自身的连接查询 在Emp表中每个雇员都对应一个经理如何查询所有雇员及其经理的名称通过给其中一个表取不同的别名然后使用对自身的联合查询来实现
select manager.ename as m_name, e.ename as e_name from EMP e, EMP managerwhere e.mgr manager.empno;查询sales 部门和Marketing部门的经理信息以及经理对应的雇员信息select d.dname , manager.ename as m_name, e.enamefrom EMP e, EMP manager, DEPT dwhere d.deptno e. deptno --部门表与员工表连接and d. deptno manager. deptno --部门表与经理表连接// and d.mgr manager. empno --部门与经理的连接条件and e.mgr manager. empno --员工与经理连接and (d.dname Sales or d.dname Marketing);select manager.ename,e.ename
from dept d inner join emp manager on d.deptnomanager.deptnoinner join emp e on e. mgr manager.empno
where d.dname in(SALES, Marketing’)
order by manager.ename替代变量
Select * from emp where deptnoaa
替代变量的使用变量名
Select empno,ename,v1 from t1 where w1;Define a 10 //定义替代变量
Define a //查看变量
Select * from emp where deptnoa;
Define //查所有替代变量
Undefine a //删除替代变量Select empno,ename,a from emp where a like ‘S%’ //重复引用Delete与TRUNCATE
•Delete from emp•Delete是DML语句将会做大量日志记录•TRUNCATE emp•TRUNCATE是DDL语句不会做日志记录效率会高不能回滚rename
重命名表、视图、序列、同义词必须是对象的拥有者才能重名Rename dept to new _deptGroup by with rollup and cube
用带rollup或cube的group by 实现超级聚合即对group进行再聚合 Rollup and cube
•Rollup实现 从右往左再聚合•Cube除了形成rollup的结果 还会按相反的方向形成结果GROUPING SETS
两个group语句以union的形式组合在一起 高级子查询
成对比较select * from empwhere (deptno,mgr) in (select deptno,mgr from emp where job like %CLERK%)Where字句中的子查询查询工资比部门平均工资高的员工Select a.empno,a.ename,a.deptno,a.sal,b.avg_salfrom emp a, (select deptno,avg(sal) avg_sal from empgroup by deptno) bwhere a.deptno b.deptno and a.sal b.avg_sal标量子查询scalar subquery
查询员工并显示工资等级工资大于4000为白领大于2000为蓝领小于2000为打工仔SQLselect empno,ename,sal, (casewhen sal4000 then 白领when sal2000 then 蓝领else 打工仔end) 工薪阶层from emp;查询员工按部门名称排序 select empno,ename
from emp e
order by (select dname from dept d where e.deptnod.deptno)exists
•查询有员工的部门或有某个员工的部门select * from dept dwhere exists (select * from emp e where e.deptnod.deptno)With语句
•With语句定义一个变量来表示一个语句通过引用变量来引用子句实现重用提高效率•查询部门总工资大于所有部门工资的平均值的部门SQLwith
dept_costs AS(select deptno,sum(sal) as dept_totalfrom empgroup by deptno),
avg_cost AS(select sum(dept_total)/count(*) as dept_avgfrom dept_costs)
select * from dept_costs
where dept_total(select dept_avg from avg_cost)
order by deptno
/本人从事软件项目开发20多年2005年开始从事Java工程师系列课程的教学工作录制50多门精品视频课程包含java基础jspweb开发SSH,SSMSpringBootSpringCloud,人工智能在线支付等众多商业项目每门课程都包含有项目实战上课PPT及完整的源代码下载有兴趣的朋友可以看看我的在线课堂
讲师课堂链接https://edu.csdn.net/lecturer/893
