基本的SQL结构化查询语言

root
233
文章
0
评论
更多
 2020年3月13日14:47:39 评论 11452字阅读38分10秒

基本的SQL结构化语言

包含了四类语言：

  • DML 数据操作语言-----select，insert，updata，delete（增删改查）
  • DDL 数据定义语言------create，alter，drop（对用户的操作）
  • DCL 数据控制语言 ------grant，revoke（权限）
  • TCL 事物控制语言 -------commit，rollback

事物：一系列的操作，要莫这些操作全部生效，要么全部不生效。

如何书写sql：

  • 大小写不敏感，单单引和双引内的大小写是敏感的
  • 关键字不能缩写select不能写成sel
  • 字符串用单引号 比如ename='SEKER'
  • 列的别名含特殊字符用双引号
  • 可跨行，但不要将关键字和单引号内的内容跨行
  • 跨行是为了可读性，一般我们都把select字句和from字句分行写
  • 不要再自定义参数部分使用sql关键字

为了方便后续的练习，我要创建一个kk用户，添加一些表

  • grant connect，resource to kk identified by kk123；
SQL> grant connect,resource to kk identified by kk123;

Grant succeeded.
  • alter user kk quato unlimited on users;
SQL> alter user kk quota unlimited on users;

User altered.

使用kk用户连接

  • conn kk/kk123;
SQL> conn kk/kk123;
Connected.
SQL> show user;
USER is "KK"

新建一个终端，使用root用户登录，创建脚本kk.sql

 

[root@oracle data]# vim kk.sql
DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES 
(40,'OPERATIONS','BOSTON'); 
INSERT INTO EMP VALUES 
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); 
INSERT INTO EMP VALUES 
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); 
INSERT INTO EMP VALUES 
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); 
INSERT INTO EMP VALUES 
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); 
INSERT INTO EMP VALUES 
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); 
INSERT INTO EMP VALUES 
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); 
INSERT INTO EMP VALUES 
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); 
INSERT INTO EMP VALUES 
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20); 
INSERT INTO EMP VALUES 
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); 
INSERT INTO EMP VALUES 
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); 
INSERT INTO EMP VALUES 
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20); 
INSERT INTO EMP VALUES 
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); 
INSERT INTO EMP VALUES 
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); 
INSERT INTO EMP VALUES 
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); 
DROP TABLE BONUS; 
CREATE TABLE BONUS 
( 
ENAME VARCHAR2(10) , 
JOB VARCHAR2(9) , 
SAL NUMBER, 
COMM NUMBER 
) ; 
DROP TABLE SALGRADE; 
CREATE TABLE SALGRADE 
( GRADE NUMBER, 
LOSAL NUMBER, 
HISAL NUMBER ); 
INSERT INTO SALGRADE VALUES (1,700,1200); 
INSERT INTO SALGRADE VALUES (2,1201,1400); 
INSERT INTO SALGRADE VALUES (3,1401,2000); 
INSERT INTO SALGRADE VALUES (4,2001,3000); 
INSERT INTO SALGRADE VALUES (5,3001,9999); 
COMMIT;

将表导入kk用户

  • @/data/kk.sql
SQL> @/data/kk.sql

查看当前用户，还有当前用户有哪些表

  • show user；
  • select table_name from user_tables;
SQL> show user;
USER is "KK"
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE

 

@和？的含义

  • @是加载OS中的SQL文件
  • ？是$ORACLE_HOME的代替

select 简易语法构成

  • select
  • from
  • where
  • order by

上述语法中不区分大小写有大小写只是为了便于描述

实际书写时只有字符串是大小写敏感的，而且要用单引号包含起来

描述中的大写为关键字

描述中的小写为表或者选择列，别名，表达式

一个完整的SQL命令叫语句，每个关键词和后面跟着的选项叫子句

  • select   指定查询的列
  • from     指定查询的表
  • where   过滤的条件
  • order  by  排序的列
  • desc|asc    排序方法

基本的SQL查询语言

查询当前用户所有的表

  • select * from tab；
SQL> select * from tab;

TNAME			       TABTYPE	CLUSTERID
------------------------------ ------- ----------
BONUS			       TABLE
DEPT			       TABLE
EMP			       TABLE
SALGRADE		       TABLE

查询所有用户

  • select * from all_users;
SQL> select * from all_users;

USERNAME			  USER_ID CREATED
------------------------------ ---------- -------------------
KK				       84 2020-03-11 22:28:02
OWBSYS_AUDIT			       79 2020-03-11 06:09:05
OWBSYS				       78 2020-03-11 06:09:05
APEX_030200			       77 2020-03-11 06:04:29
APEX_PUBLIC_USER		       75 2020-03-11 06:04:29
FLOWS_FILES			       74 2020-03-11 06:04:29
MGMT_VIEW			       73 2020-03-11 06:04:01
SYSMAN				       71 2020-03-11 06:02:51
SPATIAL_CSW_ADMIN_USR		       69 2020-03-11 06:02:42
SPATIAL_WFS_ADMIN_USR		       66 2020-03-11 06:02:40
MDDATA				       64 2020-03-11 06:01:26
OLAPSYS 			       60 2020-03-11 06:01:06
MDSYS				       57 2020-03-11 05:59:26
SI_INFORMTN_SCHEMA		       56 2020-03-11 05:59:26
ORDPLUGINS			       55 2020-03-11 05:59:26
ORDDATA 			       54 2020-03-11 05:59:26
ORDSYS				       53 2020-03-11 05:59:26
ANONYMOUS			       46 2020-03-11 05:58:20
XDB				       45 2020-03-11 05:58:20
CTXSYS				       43 2020-03-11 05:58:08
EXFSYS				       42 2020-03-11 05:58:03
XS$NULL 		       2147483638 2020-03-11 05:59:20
WMSYS				       32 2020-03-11 05:55:00
APPQOSSYS			       31 2020-03-11 05:54:45
DBSNMP				       30 2020-03-11 05:54:44
ORACLE_OCM			       21 2020-03-11 05:52:13
DIP				       14 2020-03-11 05:51:50
OUTLN					9 2020-03-11 05:51:18
SYSTEM					5 2020-03-11 05:51:17
SYS					0 2020-03-11 05:51:17

30 rows selected.

查询指定表，还有映射格式调整

  • select * from emp;
  • set linesize 150
  • set pagesize 999

映射格式调整前：

SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL
---------- ---------- --------- ---------- ------------------- ----------
      COMM     DEPTNO
---------- ----------
      7369 SMITH      CLERK	      7902 1980-12-17 00:00:00	      800
		   20

      7499 ALLEN      SALESMAN	      7698 1981-02-20 00:00:00	     1600
       300	   30

      7521 WARD       SALESMAN	      7698 1981-02-22 00:00:00	     1250
       500	   30


     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL
---------- ---------- --------- ---------- ------------------- ----------
      COMM     DEPTNO
---------- ----------
      7566 JONES      MANAGER	      7839 1981-04-02 00:00:00	     2975
		   20

      7654 MARTIN     SALESMAN	      7698 1981-09-28 00:00:00	     1250
      1400	   30

      7698 BLAKE      MANAGER	      7839 1981-05-01 00:00:00	     2850
		   30


     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL
---------- ---------- --------- ---------- ------------------- ----------
      COMM     DEPTNO
---------- ----------
      7782 CLARK      MANAGER	      7839 1981-06-09 00:00:00	     2450
		   10

      7839 KING       PRESIDENT 	   1981-11-17 00:00:00	     5000
		   10

      7844 TURNER     SALESMAN	      7698 1981-09-08 00:00:00	     1500
	 0	   30


     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL
---------- ---------- --------- ---------- ------------------- ----------
      COMM     DEPTNO
---------- ----------
      7900 JAMES      CLERK	      7698 1981-12-03 00:00:00	      950
		   30

      7902 FORD       ANALYST	      7566 1981-12-03 00:00:00	     3000
		   20

      7934 MILLER     CLERK	      7782 1982-01-23 00:00:00	     1300
		   10


12 rows selected.

映射调整后：（文章格式问题）

SQL> set linesize 150
SQL> set pagesize 999
SQL> select * from emp;
     EMPNO ENAME      JOB	       MGR HIREDATE		              SAL	COMM	 DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 1980-12-17 00:00:00	      800		            20
      7499 ALLEN      SALESMAN    7698 1981-02-20 00:00:00	     1600	 300	            30
      7521 WARD       SALESMAN   7698 1981-02-22 00:00:00	     1250	 500	             30
      7566 JONES      MANAGER     7839 1981-04-02 00:00:00	     2975		             20
      7654 MARTIN     SALESMAN   7698 1981-09-28 00:00:00	     1250	1400	     30
      7698 BLAKE      MANAGER      7839 1981-05-01 00:00:00	     2850		             30
      7782 CLARK      MANAGER      7839 1981-06-09 00:00:00	     2450		             10
      7839 KING       PRESIDENT      1981-11-17 00:00:00	             5000		             10
      7844 TURNER     SALESMAN  7698 1981-09-08 00:00:00	     1500	   0	              30
      7900 JAMES      CLERK	      7698 1981-12-03 00:00:00	      950		              30
      7902 FORD       ANALYST	      7566 1981-12-03 00:00:00	     3000		              20
      7934 MILLER     CLERK	      7782 1982-01-23 00:00:00	     1300		              10

12 rows selected.

指定表格字段进行展示

  • select empno,ename from emp;
SQL> select empno,ename from emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7839 KING
      7844 TURNER
      7900 JAMES
      7902 FORD
      7934 MILLER

12 rows selected.

同一字段可以展示多次

SQL> select empno,ename,ename from emp;

     EMPNO ENAME      ENAME
---------- ---------- ----------
      7369 SMITH      SMITH
      7499 ALLEN      ALLEN
      7521 WARD       WARD
      7566 JONES      JONES
      7654 MARTIN     MARTIN
      7698 BLAKE      BLAKE
      7782 CLARK      CLARK
      7839 KING       KING
      7844 TURNER     TURNER
      7900 JAMES      JAMES
      7902 FORD       FORD
      7934 MILLER     MILLER

12 rows selected.

要是全部展示字段的同时又想指定一个字段再次展示（文章格式原因理解一哈）

  • select 表名.*,字节名 from 
SQL> select emp.*,job from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO JOB
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------
      7369 SMITH      CLERK	      7902 1980-12-17 00:00:00	      800		     20 CLERK
      7499 ALLEN      SALESMAN	      7698 1981-02-20 00:00:00	     1600	 300	     30 SALESMAN
      7521 WARD       SALESMAN	      7698 1981-02-22 00:00:00	     1250	 500	     30 SALESMAN
      7566 JONES      MANAGER	      7839 1981-04-02 00:00:00	     2975		     20 MANAGER
      7654 MARTIN     SALESMAN	      7698 1981-09-28 00:00:00	     1250	1400	     30 SALESMAN
      7698 BLAKE      MANAGER	      7839 1981-05-01 00:00:00	     2850		     30 MANAGER
      7782 CLARK      MANAGER	      7839 1981-06-09 00:00:00	     2450		     10 MANAGER
      7839 KING       PRESIDENT 	   1981-11-17 00:00:00	     5000		     10 PRESIDENT
      7844 TURNER     SALESMAN	      7698 1981-09-08 00:00:00	     1500	   0	     30 SALESMAN
      7900 JAMES      CLERK	      7698 1981-12-03 00:00:00	      950		     30 CLERK
      7902 FORD       ANALYST	      7566 1981-12-03 00:00:00	     3000		     20 ANALYST
      7934 MILLER     CLERK	      7782 1982-01-23 00:00:00	     1300		     10 CLERK

12 rows selected.

在sql中使用算数表达式

  • select 字段，字段*2，字段/3，字段+1，（字段+2）*3 from 表名；
SQL> select empno/2,empno+1000,empno*3,(empno+200)*2 from emp;

   EMPNO/2 EMPNO+1000	 EMPNO*3 (EMPNO+200)*2
---------- ---------- ---------- -------------
    3684.5	 8369	   22107	 15138
    3749.5	 8499	   22497	 15398
    3760.5	 8521	   22563	 15442
      3783	 8566	   22698	 15532
      3827	 8654	   22962	 15708
      3849	 8698	   23094	 15796
      3891	 8782	   23346	 15964
    3919.5	 8839	   23517	 16078
      3922	 8844	   23532	 16088
      3950	 8900	   23700	 16200
      3951	 8902	   23706	 16204
      3967	 8934	   23802	 16268

12 rows selected.

添加别名的方式

字段可添加别名(字段后面加上空格在写别名）

  • select 字段 别名，字段 别名 from 表名；
SQL> select empno x,ename c from emp;

	 X     C
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7839 KING
      7844 TURNER
      7900 JAMES
      7902 FORD
      7934 MILLER

12 rows selected.

表，字段同时添加别名

  • select 字段，字段 from 表名 别名；
SQL> select e.*,empno x,ename c from emp e;

特殊字符使用别名（特殊字符要加双引号）

  • select 别名.*，字段 "特殊别名" from 表名 别名;
SQL> select empno "a v" from emp;    

       a v
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7839
      7844
      7900
      7902
      7934

12 rows selected.

 

连接操作符

合并显示，可以连接多列

  • select empno，empno，empno||ename from emp；
SQL> select empno,ename,empno||ename from emp;

     EMPNO ENAME      EMPNO||ENAME
---------- ---------- --------------------------------------------------
      7369 SMITH      7369SMITH
      7499 ALLEN      7499ALLEN
      7521 WARD       7521WARD
      7566 JONES      7566JONES
      7654 MARTIN     7654MARTIN
      7698 BLAKE      7698BLAKE
      7782 CLARK      7782CLARK
      7839 KING       7839KING
      7844 TURNER     7844TURNER
      7900 JAMES      7900JAMES
      7902 FORD       7902FORD
      7934 MILLER     7934MILLER

12 rows selected.

合并显示，可以连接多列，还可以添加字符串，别名功能

  • select empno,ename,empno||'this is kk you'||ename from emp;
SQL> select empno,ename,empno||'this is kk you'||ename from emp;

     EMPNO ENAME      EMPNO||'THISISKKYOU'||ENAME
---------- ---------- ----------------------------------------------------------------
      7369 SMITH      7369this is kk youSMITH
      7499 ALLEN      7499this is kk youALLEN
      7521 WARD       7521this is kk youWARD
      7566 JONES      7566this is kk youJONES
      7654 MARTIN     7654this is kk youMARTIN
      7698 BLAKE      7698this is kk youBLAKE
      7782 CLARK      7782this is kk youCLARK
      7839 KING       7839this is kk youKING
      7844 TURNER     7844this is kk youTURNER
      7900 JAMES      7900this is kk youJAMES
      7902 FORD       7902this is kk youFORD
      7934 MILLER     7934this is kk youMILLER

12 rows selected.
  • select empno,ename,empno||'this is kk you'||ename x from emp;
SQL> select empno,ename,empno||'this is kk you'||ename x from emp;

     EMPNO ENAME      X
---------- ---------- ----------------------------------------------------------------
      7369 SMITH      7369this is kk youSMITH
      7499 ALLEN      7499this is kk youALLEN
      7521 WARD       7521this is kk youWARD
      7566 JONES      7566this is kk youJONES
      7654 MARTIN     7654this is kk youMARTIN
      7698 BLAKE      7698this is kk youBLAKE
      7782 CLARK      7782this is kk youCLARK
      7839 KING       7839this is kk youKING
      7844 TURNER     7844this is kk youTURNER
      7900 JAMES      7900this is kk youJAMES
      7902 FORD       7902this is kk youFORD
      7934 MILLER     7934this is kk youMILLER

12 rows selected.

 

字段去重复

  • select distinct 字段 from 表名；
SQL> select distinct deptno from emp;

    DEPTNO
----------
	30
	20
	10

 

调整日期格式

  • alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 1980-12-17 00:00:00	      800		     20
      7499 ALLEN      SALESMAN	      7698 1981-02-20 00:00:00	     1600	 300	     30
      7521 WARD       SALESMAN	      7698 1981-02-22 00:00:00	     1250	 500	     30
      7566 JONES      MANAGER	      7839 1981-04-02 00:00:00	     2975		     20
      7654 MARTIN     SALESMAN	      7698 1981-09-28 00:00:00	     1250	1400	     30
      7698 BLAKE      MANAGER	      7839 1981-05-01 00:00:00	     2850		     30
      7782 CLARK      MANAGER	      7839 1981-06-09 00:00:00	     2450		     10
      7839 KING       PRESIDENT 	   1981-11-17 00:00:00	     5000		     10
      7844 TURNER     SALESMAN	      7698 1981-09-08 00:00:00	     1500	   0	     30
      7900 JAMES      CLERK	      7698 1981-12-03 00:00:00	      950		     30
      7902 FORD       ANALYST	      7566 1981-12-03 00:00:00	     3000		     20
      7934 MILLER     CLERK	      7782 1982-01-23 00:00:00	     1300		     10

12 rows selected.

 

虚表

  • 只为输出结果，添补语法规则使其合理
  • select 1+1 from dual;
SQL> select 1+1 from dual;

       1+1
----------
	 2
  • select user from dual;
SQL> select user from dual;

USER
------------------------------
KK
  • select sysdate from dual;
SQL> select sysdate from dual;

SYSDATE
-------------------
2020-03-12 09:01:47
  • select dbms_random.random from dual;(获取一个随机数）
SQL> select dbms_random.random from dual;

    RANDOM
----------
1527778996

SQL>

 

继续阅读
weinxin
我的微信
这是我的微信扫一扫
  • 文本由 发表于 2020年3月13日14:47:39
  • 除非特殊声明，本站文章均为原创，转载请务必保留本文链接
Oracle 11g单实例安装部署 Oracle

Oracle 11g单实例安装部署

Oracle 11g单实例安装部署 Oracle安装部署比较繁琐，一部出错就会导致安装失败，我这里把安装部署分为三部分，一类是服务器自身系统参数的设置，一部分是Oracle服务的部署，还有一类是Ora...
03月11日 评论
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: