表的创建、修改与删除

创建表

• 创建语法

  CREATE TABLE 表名称(
      字段名 类型(长度) primary key,
      字段名 类型(长度),
      ...
  );

  • 数据类型

    类型	说明
    CHAR	固定长度的字符类型，最多存储2000字节 固定长度的意思是，比如设置字段类型为CHAR(10），那么当存储的内容长度达不到10，那么ORACLE会用空格补齐，当取值的时候也是含有空格的
    VARCHAR2	可变长度的字符类型，最多存储4000字节
    LONG	大文本类型，最大可存储2个G
    NUMBER	ORACLE中只有一个数值类型就是NUMBER，如果不指定长度，默认是NUMBER(18) 举例： NUMBER(5)：最大可以存的数为99999 NUMBER(5,2)：最大可以存的数为999.99
    DATE	日期时间型，精确到秒
    TIMESTAMP	相对DATE更加精确，精确到秒的小数点后9位
    CLOB	二进制型，存储字符，最大可以存储4个G
    BLOG	二进制型，存储图像、声音、视频等二进制数据，最多可以存储4个G

  • VARCHAR2类型的长度单位解释

    • 通常写法是VARCHAR2(10)，这种写法也是默认写法，是以字节为单位的长度设置，所以字段最多插10个字节，以插入汉字（1汉字 = 1字符 = 3字节）为例，最多插入3个汉字

    • 还可以指定字符为单位长度，写法是VARCHAR(10 char)，这种写法就允许字段最多插入10个字符，以插入汉字为例（1汉字 = 1字符），最多插入10个汉字

    • 代码示例

      CREATE TABLE T_USER(
         USERNAME VARCHAR2(10 char),
         PASSWORD VARCHAR2(10)
      );

      

      

修改表

• 增加字段语法

  ALTER TABLE 表名 ADD (
      列名 类型,
      列名 类型
      ...
  )

• 修改字段语法

  ALTER TABLE 表名 MODIFY(
      列名 类型,
      列名 类型
      ...
  )

• 修改字段名语法

  ALTER TABLE 表名 RENAME COLUMN 原字段名 TO 新字段名

• 删除字段名语法

  ALTER TABLE 表名 DROP COLUMN 字段名1,字段名2,...

删除表

• 删除表语法

  DROP TABLE 表名

数据的插入、删除与修改

插入数据

• 插入数据语法

  -- 插入某几个字段
  INSERT INTO 表名 (字段1,字段2,...) VALUES (值1,值2,...)

  -- 不声明字段,插入的是全部字段
  INSERT INTO 表名 VALUES (值1,值2,...)

修改数据

• 修改数据语法

  UPDATE 表名 SET 字段1=值1,字段2=值2,... WHERE ...

删除数据

• 删除数据语法

  DELETE FROM 表名 WHERE ...

数据的导出和导入

整库导出和导入

• 整库导出语法

  EXP 拥有DBA权限的用户名/密码 [FILE=文件名] FULL=Y
  
  -- 示例: 
  -- EXP SYSTEM/ORACLE FULL=Y
  -- EXP SYSTEM/ORACLE FILE=BACKUP FULL=Y

  • 执行命令后，会在当前目录下生成一个EXPDAT.dmp的文件，如果想指定导出文件的名称，使用FILE参数即可

• 导入语法

  IMP 拥有DBA权限的用户名/密码 FULL=Y 
  IMP 拥有DBA权限的用户名/密码 FULL=Y [FILE=文件名.dmp]
  
  -- 示例: 
  -- IMP SYSTEM/ORACLE FULL=Y
  -- IMP SYSTEM/ORACLE FULL=Y EXPDAT.dmp

  • 导入时也可以通过FILE参数指定导入哪个库
  • 对象（表、序列等）如果存在的话，就会跳过

按用户导出和导入

• 按用户导出语法：与整库导出语法无异，不过是去掉了全库导出的标识然后增加了OWNER参数指定用户

  EXP 拥有DBA权限的用户名/密码 OWNER=用户名
  EXP 拥有DBA权限的用户名/密码 OWNER=用户名 [FILE=文件名]

• 按用户导入语法

  IMP 拥有DBA权限的用户名/密码 FROMUSER=用户名
  IMP 拥有DBA权限的用户名/密码 FROMUSER=用户名 [FILE=文件名.dmp]

  • TY话：既然这个语句是按照用户导入，也就是说，导入时是找FROMUSER下的数据进行导入，所以FILE参数值可以是整库导出的文件也可以是按用户导出的文件，只要包含该FROMUSER的数据即可

按表导出和导入

• 按表导出语法

  EXP 拥有要导出的表的用户名/密码 [FILE=文件名] TABLES=表1,表2,...

• 按表导入语法

  IMP 要导入表到哪个用户的用户名/密码 [FILE=文件名] TABLES=表1,表2,...

单表查询

简单条件查询

• 条件查询

  SELECT * FROM USER WHERE USERNAME = 'zhangsan'

• 模糊查询

  SELECT * FROM USER WHERE USERNAME LIKE '%zhang%' -- 匹配的是xxxzhangxxx
  SELECT * FROM USER WHERE USERNAME LIKE 'zhang%' -- 左匹配,匹配的是zhangxxx
  SELECT * FROM USER WHERE USERNAME LIKE '%zhang' -- 右匹配,匹配的是xxxzhang

• AND运算符

  -- 查询用户名是张三并且性别是男的数据
  SELECT * FROM USER WHERE USERNAME = 'zhangsan' AND SEX = 'M'

• OR运算符

  -- 查询用户名是张三或者性别是男的数据
  SELECT * FROM USER WHERE USERNAME = 'zhangsan' AND SEX = 'M'

  • TY话：如果某一条数据同时满足这两个条件，也不会出现重复的情况，因为这个语句是将表中的每一行遍历检查一遍，满足条件了才会被查出来，出现重复的情况只有是表中有两条一摸一样的数据

• AND和OR混合使用：AND优先级高于OR！！！

  -- 案例: 查询业主中包含"刘"的或者门牌号包含5的业主记录,并且地址编号为3的记录
  
  -- 分析: 根据语义,"包含"刘"的或者门牌号包含5的业主记录"和"地址编号为3的记录"是并行条件,而"包含"刘"的或者门牌号包含5的业主记录"中又有或者条件,所以第一时间认为的写法是下面的写法
  SELECT * FROM USER WHERE USERNAME LIKE '%刘%'     OR     HOUSENUM LIKE '%5%' AND ADDRESSNUM = '3'
  -- 但是,因为AND优先级高,所以上述写法中的条件就变成了查找用户名包含"刘" 或者 门牌号包含5同时地址编号是3 的记录,所以要加上括号来改变优先级
  
  -- 正确写法
  SELECT * FROM USER WHERE (USERNAME LIKE '%刘%' OR HOUSENUM LIKE '%5%') AND ADDRESSNUM = '3'

• 范围查询

  -- BETWEEN AND 包含边界
  SELECT * FROM USER WHERE AGE >= 18 AND AGE <= 25
  SELECT * FROM USER WHERE AGE BETWEEN 18 AND 25
  
  -- NOT BETWEEN AND 不包含边界
  SELECT * FROM USER WHERE AGE > 18 AND AGE < 25
  SELECT * FROM USER WHERE AGE NOT BETWEEN 18 AND 25

• 空值查询

  SELECT * FROM USER WHERE ADDRESS IS NULL
  
  SELECT * FROM USER WHERE ADDRESS IS NOT NULL

去掉重复记录

• 去掉重复记录语法

  -- 张三 18
  -- 张三 18
  -- 张三 20
  -- 李四 18
  
  SELECT DISTINCT USERNAME,AGE FROM USER
  
  -- 如果DISTINCT作用的是多个字段,那么只会去掉多个字段都重复的结果
  -- 张三 18
  -- 张三 20
  -- 李四 18

排序查询

• 升序排序（默认）

  SELECT * FROM USER ORDER BY AGE [ASC]

• 降序排序

  SELECT * FROM USER ORDER BY AGE DESC

基于伪列的查询

聚合统计

函数

• sysdate

  • 作用：获取当前时间

    SELECT sysdate FROM DUAL

  • 用法

    SQL	含义
    sysdate + 1	加 1 天
    sysdate + 1/24	加 1 小时
    sysdate + 1/(24 * 60)	加 1 分钟
    sysdate + 1/(24 * 60 * 60)	加 1 秒钟
    sysdate - 1	减 1 天
    sysdate - 1/24	减 1 小时
    sysdate - 1/(24 * 60)	减 1 分钟
    sysdate - 1/(24 * 60 * 60)	减 1 秒钟

常用操作

查找重复数据

• 查找重复的单个字段

  SELECT 字段A, COUNT(*)
  FROM 表
  GROUP BY 字段A
  HAVING COUNT(*) > 1

• 查找多个字段组合的重复数据

  SELECT 字段A, 字段B, COUNT(*)
  FROM 表
  GROUP BY 字段A, 字段B
  HAVING COUNT(*) > 1