数据库从入门到删库跑路(一) - -SQL语句

最新推荐文章于 2024-04-20 10:04:29 发布

<-krush->

最新推荐文章于 2024-04-20 10:04:29 发布

阅读量1.9k

点赞数 1

分类专栏： ▼ 数据库技术文章标签：数据库技术

本文链接：https://blog.csdn.net/qq_33592583/article/details/86635079

版权

▼ 数据库技术专栏收录该内容

4 篇文章 0 订阅

订阅专栏

数据库技术(1)-SQL语句

(1)基本SQL语句
SQL语句分为一下三种类型:

DML:Data Manipulation Language:数据操纵语言
- 用于查询与修改数据记录
  - INSERT
  - UPDATE
  - DELETE
  - SELECT
DDL:Data Definition Language 数据定义语言
- 用于定义数据库的结构,比如创建,修改,或删除对象
  - CREATE TABLE
  - ALTER TABLE
  - DROP TABLE
  - CREATE INDEX
  - DROP INDEX
DCL:Data Control Language 数据控制语言
- 用来控制数据库的访问

基本SQL语句

查询表中所有列

    select * from employee;

查询表中的特定列

     select employee_id,lat_name,email from employees;

- 注:sql对大小写不敏感
- 日期可以进行加减,但是不可以进行乘除运算
- 空值是无效的,未指定的,未知或不可预知的值
- 空值不是空格或者0
- 包含空值的数学表达式的值都为空值
- 连接符
    - 把列与列,列与字符连接起来
    - 用 '||'表示,相当于连接符号+s

        - 可以用来合成列
        - eg.
            - select  last_name ||' `s job_id is '|job_id

查看表结构
desc [table_name]<=======sql plus的关键字
更改列名

    - select employee_id id,last_name name,12*salary annual_sal from [table_name]
    - select employee_id as id .... as可加可不加 
    - select employee_id as "id",last_name "Name",12*salary annual_sal from employee (当需要确定大小写时一定需要加上引号)

去除重复行

   - select distinct department_id from employees;

sql语句
- 一种语言
- ANSI标准
- 关键字不能缩写
sql*plus命令
- 一种环境
- Oracle的特性之一
- 关键词以缩写
- 命令不能改变数据库中数据的值
- 集中运行

(2) 过滤和排序数据

  - 过滤
  
     - eg1.
       - select last_name,hire_date from employees where to_char(hire_datem'yyyy-mm-dd')='1994-06-07'
     - 过滤条件
        - between and(适合连续值):包含边界
        - in(适合离散值)
           - select last_name,department_id,salary from employees
             where department_id in(70,80,90)
        - like查询
           - eg.员工中含有字符a的员工有哪些
               - select last_name,department_id,salary from employees where last_name like '%a%'
               - % 表示从0-无穷多个
           - eg.员工中名字的第二位是a的员工有哪些
               -  select last_name,department_id,salary from employees where last_name like '_a%'
           -  eg.员工中名字的第三位是a的员工有哪些
               -  select last_name,department_id,salary from employees where last_name like '__a%'
           - eg.查询员工中的名字含有下划线的人有哪些
               - 假设有个员工为'wgha_lanan'
               - select last_name like '%#_%' escape '#'
               - select last_name like '%\_%' escape '\'
        - is null  与 is not null
   - 排序 Order by
     - select last_name,department_id,salary from employees
       where department_id=80
       order by salary asc(升序)
    - select last_name,department_id,salary from employees
       where department_id=80
       order by salary asc,last_name asc

(3) 单行函数(dual对应一个虚表)

定义:
- 一行记录对应好一个结果
  - 字符函数
    - 大小写控制
      - lower(‘ADFAD’) 小写
      - upper('ADAjj) 大写
      - initcap(‘sdsSDS’) 首字母大写
    - 字符控制函数:
      - CONCAT 连接
      - SUBSTR 取子串(下角标从1开始)
      - LENGTH
      - INSTR
      - LPAD:左对齐:LPAD(SALARY,10,’’):左边填不满用补齐
      - RPAD:右对齐
      - TRIM:将一个字符从一个字符串去除,只可以去除首和尾
      - REPLACE
  - 数字函数
    - ROUND:四舍五入函数
    - TRUNC:截断函数
    - MOD():取余函数
  - 日期函数
    - 可以用数字除以24来向日期加上或减去天数
    - MONTHS_BETWEEN:两个日期相差的月数
    - ADD_MONTHS:向制定日期中加上若干月数
    - NEXT_DAY:指定日期的下一个星期’对应的日期’
    - LAST_DAY:本月的最后一天
    - ROUND:日期四舍五入
    - TRUNC:日期截断
  - 转换函数
    - 数据类型转换
      - 隐式
        date<---->varchar2<----->number
      - 显示
        字符与日期(char–date):
        where hire_date=‘7-6月-94’
        where to_char(‘hire_date’,‘yyyy-mm-dd’)=‘1997-06-07’
        where to_date(‘1997-06-07’,‘yyyy-mm-dd’)=hire_date
        
        字符与数字
        to_number
        to_char
  - 通用函数
    - NVL:将空值转换成一个已知的值
    - NVL2(expr1,expr2,expr3)
    - NULLIF(expr1,expr2):相等返回NULL,不等返回expr1
- 条件表达式
  - case表达式

           select last_name "last_name",job_id "job_id" when 'AD_PERS' then 'A'
                                                         when 'ST_MAN'  then 'B'
                                                         when 'IT_PROG' then 'C'
                                                         when 'SA_REP'  then 'D'
                                                         when 'ST_CLERK' then 'E' end "Grade"
          from employees

     - decode表达式

            select last_name,job_id,decode(job_id,'AD_PERS','A',
                                                   'ST_MAN','B',
                                                   'IT_PROG','C',
                                                   'SA_REP','D',
                                                   'ST_CLERK','E') "Grade"
            from employees

(4)多表查询

等值连接

查询出公司员工的last_name,department_name,city:
  select last_name,department_name,city
  from department d,employees e,location l
  where d.department_id=e.department_id and d.location_id=l.location_id;


查询出last_name 为'Chen'的manager的信息 (员工的employee_id等于员工的manager_id)

0) 例如:老张的员工号为:"1001",我的员工号为:"1002"
   我的manager_id为 "1001"-----我的manager是"老张"
1) 通过两条sql语句进行查询

   select manager_id
   from employees
   where lower(last_name)='chen' --- 返回的结果为108

   select *
   from employees
   where employee_id=108
2) 同过一条sql查询(自连接)
   select m.*
   from employees e,employees m
   where e.manager_id=m.employee_id and e.last_name='Chen'
3) 通过一条sql查询(子查询)
    select * 
    from employees
    where employee_id=(
        select manager_id
        from employees
        where last_name='Chen'
    )

非等值连接

查询每个员工的last_name 和 GRADE_LEVEL (在JOB_GRADES 表中)-----非等值连接
  select last_name,GRADE_LEVEL
  from employees e,job_grades j
  where e.salary>=j.lowest_sal and  e.salary<=j.hightest_sal

外连接
- 左外连接

   select last_name,e.department_id,department_name
   from employee e,department d
   where e.department_id=d.department_id(+)

右外连接

      select last_name.e.department_id,department_name
      from employee e,department d
      where e.department(+)=d.departemnt_id

理解(+)的位置: 以左外连为例,因为在左表需要返回更多的记录,右表就需要加上更多的
记录,因此在右表的链接条件上加上"(+)"

sql 99 的左外连接,右外连接,满外连接

1) 左外连接
    select last_name,department_name
    from employees e left join departments d
    on e.department_id=d.department_id
2) 右外连接
    select last_name,department_name
    from employees e right join in departments d
    on e.departemnt_id=d.department_id
3) 满外连接
    select last_name,departemnt_name
    from employees e full join in departemnnts d
    on e.departemnt_id=d.departemnt_id

sql 99 中连接 Employees 和 Departement表

1) 缺点:要求两个表中的必须拥有一样会的别名
   select * 
   from employees join departments
   using(department_id)
2) 
   select *
   from employees e join departments d
   on e.department_id=d.department_id
3) 多表连接
   select e.last_name,d.departemnt_name,l.city
   from employees e join departments 
   on e.department_id=d.department_id
   join locations l
   on d.location_id=l.location_id

(5) 分组函数

分组函数作用于一组数据,并对一组数据返回一个值
不能在WHERE子句中使用组函数
可以在HAVING子句中使用组函数
组函数类型
- AVG
  - 只能存放number类型
- COUNT
  - 返回记录中的总数
  - 只记录非空记录
  - AVG实际是由SUM/COUNT 而来,因此其计算时也忽略的空值
  - 可使用NVL函数使分组函数无法忽略空值
- MAX
- MIN
- STDDEV
- SUM
  - 只能存放number类型

组函数应用:
  select AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
  from employees;

NVL函数使分组隐函数无法忽略空值:
  select AVG(NVL(commission_pot,0))
  from employees;

COUNT(DISTINCE expr) 返回expr非空且不重复的记录总数
  select COUNT(DISTINCT department_id)
  from employees;
求出EMPLOYEES表中各部门的平均工资(按照不同部门进行分组)
   select department_id,AVG(salary)
   from employees
   where department_id in(40,60,80)
   group by department_id


在SELECT 列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中

HAVING:
  select department_id,avg(salary)
  from employees
  having avg(salary)>6000
  group by department_id
  order by department_id asc  
嵌套组函数
  select max(avg(salary))
  from employees
查询公司在1995-1998之间,每年雇佣的人数的格式
select count(*) "total"
       count(decode(to_char(hire_date,'yyyy').'1995',1,null)) "1995"
       count(decode(to_char(hire_date,'yyyy').'1996',1,null)) "1996" count(decode(to_char(hire_date,'yyyy').'1997',1,null)) "1997" count(decode(to_char(hire_date,'yyyy').'1998',1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in (1995,1996,1997,1998)

(6) 子查询

单行子查询
多行子查询
- IN: 等于列表中的任意一个
- ANY: 和子查询返回的某一个值进行比较
- ALL: 和子查询返回的所有值进行比较

--- 谁的工资比Abel高?
select last_name,salary
from employees
where salary>( select salary
               from employees
               where last_name='Abel')
--- 查询员工名为Chen的manager的信息
  select last_name,salary 
  from employees
  where employee_id=( select manager_id
                      from employees
                      where last_name='Chen')
--- 查询平均工资最高的job信息

7.创建和管理表(DDL)

常见的数据库对象:
- 表:基本的数据存储集合,由行和列组成
- 视图:从表中抽出的逻辑上相关的数据集合
- 序列: 提供有规律的数值
- 索引: 提高查询的效率
- 同义词: 给对象起别名
表名和列名:
- 必须以字母开头
- 必须在1-30个字符之间
- 必须只能包含A-Z,a-z,0-9,_,$,和#
- 必须不能和用户定义的其他对象重名
- 必须不能是Oracle的保留字
数据类型:
- VARCHAR2(size) 可变长字符数据
- CHAR(size) 定长字符数据
- NUMBER(p,s) 可变长数值数据
- DATE:日期型数据
- LONG:可变长字符数据,最大可达到2G
- CLOB:字符数据,最大可达到4G
- RAW(LONG RAW):原始的二进制数据
- BLOB:二进制数据,最大可达到4G
- BFILE: 存储外部文件的二进制数据最大可达到4G
- ROWID: 行地址
- 语句(以下DDL命令,操作之后皆不可回滚)
  - 创建表: create table
  - 修改表结构: alter table
  - 删除表: drop table
  - 重命名表: rename to
  - 删除表中的所有数据,并释放存储空间: truncate table

Oracle数据库中的表:

1.查看用户创建的表:
   select * from user_tables;

2.创建表的第一种方式(白手起家):
create table emp1(
  id number(10),
  name varchar2(20),
  salary number(10,2),  //整个10位,小数位2位
  hire_date date
)
3.显示表结构
desc emp1

4.创建表的第二种方式(依托于现有的表):
create table emp2
as
select employee_id id,last_name name,hire_date,salary
from employees

5. 修改表*(修改数据类型必须是空表):
alter table emp1
add(email varchar2(20))

alter table emp1
modify(id number(15))

6.删除表
drop tabel emp5;

7.清空表(只是清除表中的数据)
truncate table emp3(不能回滚)

8.改名
rename emp2 to employees2;

8.数据处理

创建空表
create table emp1
as 
select employee_id,last_name,hire_date,salary from employees
where 1=2  

插入一条数据(数据格式一一对应)
insert into emp1
values(1001,'AA',sysdate,10000)

insert into emp1
values(1002,'BB',tochar('1998-12-21','yyyy-mm-dd'),20000)

insert into emp1
values(1003,'CC',to_date('1999-12-21','yyyy-mm-dd'),null)


插入不足值的数据 要求类型与数据一一对应
insert into emp1(employee_id,last_name,hire_date)
values(1004,'DD',to_date('1999-12-21','yyyy-mm-dd'))


从其他表中拷贝数据:
insert into emp1(employee_id,hire_date,last_name,salary)
select employee_id,hire_date,last_name,salary
from employees

创建脚本:
insert into emp1(employee_id,,last_name,salry,hire_date)
values(&id,'&name',&salary,'&hire_date')

更改数据 UPDATE

update emp1
set salary=12000
where employee_id=179
提交
commit; 

删除数据:DELETE语句

从employees1表中删除dept1部门名称中含pubic字符的部门id
delete from employees1
where department_id=(

          select department_id
          from depaetments
          where department_name like='Public'
)

数据库事务
- 事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态
- commit:提交相当于保存
- rollback:
  - savepoint A;
  - rollback to savepoint A;
- 提交或回滚前的操作
  - 其他用户不能看到用户所做的改变,直到当前用户结束事务
  - DML语句所涉及到的行被锁定,其他用户不能操作
- 提交后的数据状态
  - 锁被释放,其他用户可以操作涉及到的数据
sql优化

9.约束

什么是约束:
- 约束是表级的强制规定
常见的约束:
- NOT NULL
- UNIQUE
- PRIMARY KEY:主键
  - 不能加上空值
  - 主键唯一
- FOREIGN KEY:外键
- CHECK
创建和修改约束
- 建表的同时
- 建表之后
表级约束和列级约束
- 列级约束只能作用在一个列上
- 表级约束:可以作用在多个列上
添加约束的语法:
- 添加或删除约束,但不能修改约束
- 有效化或无效化约束
- 添加 NOT NULL 约束要使用 modify语句

创建表:
create table emp2(
    id number(10) contraint emp2_id_nn  not null,  // 显示声明约束
    name varchar2(20) not null,
    salary(10,2)
)
显示表:
desc emp2


创建表
create table emp3(
---列级约束
  id number(10) contraint_emp3_id_uk unique,
  name varchar2(20) contraint emp3_name_nn not null,
  email varchar(20),
  salary number(10,2),
---表级约束
  contraint emp3_email_uk unqiue(email)
)

创建表
create table emp4(
---列级约束
  id number(10) contraint_emp4_id_pk primary key,
  name varchar2(20) contraint emp4_name_nn not null,
  email varchar(20),
  salary number(10,2),
---表级约束
  contraint emp3_email_uk unqiue(email)
  contraint emp6_id_fk foreign key(department_id) references departments(department_id)
)

10.视图:view

视图
- 视图是一种虚表
- 视图建立在已有表的基础上,视图赖以建立的这些表称为基表
为什么要使用视图:
- 控制数据访问
- 简化查询
- 避免重复访问相同的数据
简单视图和复杂视图
- 没使用分组函数的为简单视图
- 使用分组函数的为简单的视图
Top-N 分析:
- 传最大的几个值的Top-N分析
- 对ROW_NUM只能使用小于等于
- row_num:表示伪列,数据本身没有这样的列,是Oracle数据库为每个数据表加上的列可以标识行号,默认情况下,rownum按主索引进行排序,没有则自然排序

1.创建视图
create view empview
as
select employee_id,last_name,salary
from employees
where department_id=80
2.视图修改
3.视图中删除
delete from empview
where employee_id=176
4.基于多个表创建视图
create view empview2
as 
select employee_id,id,last_name,name,department_name
from employee e,department d
where e.department_id=d.department_id
5.修改视图
使用 create or replace view子句修改视图
6.屏蔽DML操作,可以使用with read only 选项屏蔽对视图的DML的操作
with read only
7.删除视图
drop view [view_names];
8.top-n分析:

查询工资排名前10的工资:
select rownum,employee_id,last_name,salary
from(
    select rownum,employee_id,last_name,salary
    from employees
    order by salary desc
)
where rownum<=10

查询40-50
select rn,employee_id,last_name,salary
from(
    select rownum rn,employee_id,last_name,salary
    from(
        select employee_id,last_name,salary
        from employees
        order by salary desc
    )
)
where rn>40 and rn<=50

9.对Orac对象le进行分列
select employee_id,last_name,salary
from(
    select rownum rn,employee_id,last_name,salary
    from employees
) e
where e.rn<=pageNo*pageSize and e.rn>(pageNo-1)*pageSize

11.其他数据库对象

序列
- 可以多个用户来产生唯一数值的数据库对象
- 自动提供唯一的数值
- 共享对象
- 主要用于提供主键值
- 将序列值装入内存中可以提高访问效率
修改序列的注意事项:
- 必须是序列的拥有者,或者对序列有ALTER权限
- 只有将来的序列值会被改变
- 改变序列的初始值只能通过删除序列之后重建序列的方法实现
使用序列:会出现"裂缝"现象
索引
- 一种独立于表中的模式对象,可以存储在与表不同的磁盘或表空间中
创建索引
- 自动索引
- 手动创建索引
什么时候创建索引
- 列中数据值分布范围很广
什么时候不要创建索引:
- 表很小
同义词

1.创建一个序列:
create sequence empseq
increment by 10  --每次增长10
start with 10    --从10开始增长
maxvalue 100     --提供的最大值
cycle            --需要循环
nocache          --不需要缓存
2.// 首次必须先进行nextval
select empseq.curval from dual
select empseq.nextval from dual
// 序列的使用
create table emp01
as
select employee_id,last_name,salary
from employees
where 1=2
3.// 修改序列
alter sequence empseq
increment by 12
nocycle
4.创建索引
create index emp01_id_ix
on emp01(employee_id)
5.删除索引

6.创建同义词
CREATE SYNONYM e FOR employees

12.控制用户权限

数据库安全性:
- 系统安全性
- 数据安全性
系统权限:对于数据库的权限
- 有一百多种权限
- 数据库管理员具有高级权限以完成管理任务
对象权限:操作数据库对象的权限

1.创建用户
create user xiaoyao
identified by xiaoyao
2.用户的系统权限-创建会话
grant create session
to xiaoyao

grant create table
to xiaoyao
3.创建用户表空间
alter user xioayao quota unlimted(5m)
on users;
4.更改用户账户密码
alter user xiaoyao
identified by xiaoyaotest
5.创建角色
create role my_role;
6.赋予角色权限
grant create session,create_tabe,create view to my_role

create user xiaoyao02
grant my_role to xiaoyao02
7.对象权限
grant select,update
on scott.employees
to xiaoyao
8.with grant options:表示可以可别人授予权限
  public 关键字:表示给所有用户权限  
9.revoke 语句表示收回已授予的权限

13.SET操作符

UNION/UNION ALL: 求并集
- 默认按照第一列顺序排序
INTERSECT: 求交集
MINUS: 求差集

列数必须对应相同 实现两个表的并集
select emplyee_id,department_id
from employees01
union all
select employee_id,department_id
from employee02

14.高级子查询

1.多列子查询

开始实现:
select employee_id,manager_id,department_id
from employees e1
where manager_id in (
    select manager_id
    from employees
    where employee_id in (141,174)
) and department_id in (
   select department_id
   from employees
   where employee_id in (141,174)
)
and empoyee_id not in(141,174)

多列子查询:
 select employee_id,manager_id,department_id
 from employees e1
 where (manager_id,department_id) in (
     select (manager_id,department_id)
     from employees
     where employee_id in (141,174)
 )
 and employee_id not in (141,174)

2.在FROM 子句中使用子查询

select last_name,department_id,salary
from employees e1
where salary> (
   select avg(salary)
   from employees e2
   where e1.department_id=e2.department_id
   group department_id
)
3. 相关子查询:按照一行接一行的顺序只能执行,主查询的每一行都执行一次子查询
select last_name,salary,department_id
from employees outer
where salary>(
    select avg(salary)
    from employees
    where department_id=outer.department_id
)
4.EXISTS 操作符
 - 检查在子查询中是否存在满足条件的行
5. NOT EXISTS 操作符
6.with子句

<-krush->

关注

1
点赞
踩
14

收藏

觉得还不错? 一键收藏
1
评论
数据库从入门到删库跑路(一) - -SQL语句

数据库技术(1)-SQL语句(1)基本SQL语句SQL语句分为一下三种类型:DML:Data Manipulation Language:数据操纵语言用于查询与修改数据记录INSERTUPDATEDELETESELECTDDL:Data Definition Language 数据定义语言用于定义数据库的结构,比如创建,修改,或删除对象CREATE TA...
复制链接

扫一扫