SQL 入门

发表于 更新于 分类于 阅读次数： 阅读时长 ≈ 10 分钟

创建
怎么创建?
数据类型
表的列,字段
创建表

SQL*Plus命令行工具

1
2
sqlplus /nolog

只是启动了一个客户端进程，并没有与服务器连接，连接到 Oracle服务器的命令是：

conn 用户名/密码 as 连接身份

1
2
conn system/admin as sysdba

Oracle服务开关闭用命令：

1
2
3
startup open
shutdown immediate

1
2
3
4
5
6
7
8
9
10
11
12
13
--注释
--鼠标选中执行语句

create table table_name --创建表
(xuehao number,
name var(20),
English mubmer,
shuxue number,
yuwen mubmer,
dili mubmer,
wuli mubmer
)

查询列,逐步添加条件

1
2
3
4
5
6
7
8
9
10
11
select * from table_name --查询该表*(所有列)

select yuwen from table_name --查询指定的列名(yuwen)

select yuwen from table_name where yingyu<'90' --查询英yingyu于90的yuwen数据

select yuwen from table_name where yingyu>'90' and yingyu<='120' --查询英yingyu大于90小于等于120 的yuwen数据

select yuwen from table_name where yingyu>'90' and yingyu<='120' order by yuwen desc/asc;
-- 查询英yingyu大于90小于等于120 的yuwen数据并且按(降序/升序)排列

修改表中数据

更新数据

1
2
3
4
5
6
7
8
update table_name set yuwen='150' where yingyu='120'
--插入,修改,更新数据,yuwen=120,条件是yingyu=120该行

delete from table_name
--删除该表所有数据

delete from table_name where yingyu='120'

插入数据

1
2
3
insert into table_name values('001','zhangsan','120','150','135','60','120') --插入数据
--插入一行xuehao=001,name=zhangsan....wuli=120

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create table s_test
(id number,        --编号
name varchar2(20),  --姓名
addr varchar2(50),  --地址
score number        --分数
);
--向s_test 表插入如下数据
--   id    name         addr               score
--   1    zhangsan
--   2    lishi
--   3    wangwu     shenzhen futian
insert into s_test values('1','zhangsan',null,null);
insert into s_test values('2','lishi',null,null);
insert  into s_test values('3','wangwu','futian ',null);
update s_test set addr='china guandon' where id='2';--3. 将s_test中id等于2的addr内容更改为 china guandon;
update s_test set  addr='american' where  id='3';-- 将s_test中id等于3的addr内容更改为 american;
update s_test set score='99' where id='1';-- 将s_test中id等于1的score内容更改为99;
update s_test set score='80' where id='2';-- 将s_test中id等于2的score内容更改为80;
update s_test set score='100' where  id='3';-- 将s_test中id等于3的score内容更改为100;
commit;--提交
--select * from s_test;
select * from s_test where score>'80';--查询出t_test中socre大于80的所有信息
select * from s_test where score>'80' and score<'100';--查询出t_test中socre大于80，小于100的所有信息。
select * from s_test order by id desc;--将s_test中的记录按id降序输出
delete from s_test where ID='1'; -- 将s_test中的id等于1的记录删除
commit;--提交

说明：

  • 连接身份：表示该用户连接后拥有的权限。
    • sysdba： 即数据库管理员，权限包括：打开数据库服务器、关闭数据库服务器、备份数据库、恢复数据库、日志归档、会话限制、管理功能、创建数据库。sys用户必须用 sysdba身份才能登录，system用户可以用普通身份登录。
    • sysoper：即数据库操作员，权限包括：打开数据库服务器、关闭数据库服务器、备份数据库、恢复数据库、日志归档、会话限制。
    • normal：即普通用户，权限只有查询某些数据表的数据。默认的身份是 normal用户。

创建用户

CREATE USER 用户名 IDENTIFIED BY 口令 [ACCOUNT LOCK|UNLOCK]

1
2
CREATE USER admin IDENTIFIED BY account lock

1
2
3
4
SQL> CREATE USER jerry
IDENTIFIED BY tom
ACCOUNT UNLOCK;

<font color=red>创建后并不能马上使用该用户访问数据库</font>

还有一个概念就是数据库角色（role），数据库角色就是若干个系统权限的集合。下面介绍几个常用角色：

Ø CONNECT角色，主要应用在临时用户，特别是那些不需要建表的用户，通常只赋予他们 CONNECT role。CONNECT是使用 Oracle的简单权限，拥有 CONNECT角色的用户，可以与服务器建立连接会话（session，客户端对服务器连接，称为会话）。

Ø RESOURCE角色，更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程（procedure）、触发器（trigger）、索引（index）等。

Ø DBA角色，DBA role拥有所有的系统权限—-包括无限制的空间限额和给其他用户授予各种权限的能力。用户 SYSTEM拥有DBA角色。

一般情况下，一个普通的用户（如 SCOTT），拥有 CONNECT和 RESOURCE两个角色即可进行常规的数据库开发工作。

可以把某个权限授予某个角色，可以把权限、角色授予某个用户。系统权限只能由 DBA用户授权，对象权限由拥有该对象的用户授权，授权语法是：

GRANT 角色|权限 TO 用户（角色）

1
2
3
4
5
6
SQL> GRANT CONNECT TO jerry;
授权成功。
SQL> GRANT RESOURCE TO jerry;
授权成功。
SQL>

其他语法

1
2
3
4
5
6
7
//回收权限
REVOKE 角色|权限 FROM 用户（角色）
//修改用户的密码
ALTER USER 用户名 IDENTIFIED BY 新密码
//修改用户处于锁定（非锁定）状态
ALTER USER 用户名 ACCOUNT LOCK|UNLOCK

删除表

TRUNCATE和DELETE都能把表中的数据全部删除，他们的区别是：

  • -TRUNCATE是DDL命令，删除的数据不能恢复,如果一个表中数据记录很多，TRUNCATE相对DELETE速度快。
  • -DELETE命令是DML命令，删除后的数据可以通过日志文件恢复。
1
2
3
4
drop table table_name
delete from Table_name where 条件
truncate table_name

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
create table table_name
(student_number number,
name varchar(10),
sex char(10),
birthday date
)
select * from table_name
insert into table_name values('001','Tom','man',to_date('2020-09-18','yyyy-mm-dd'));--插入date数据to_date('2020-09-18','yyyy-mm-dd')
insert into table_name values('002','Jack','man',to_date('2020-09-28','yyyy-mm-dd'));
delete from table_name where sex='man'
alter table table_name modify STUDENT_NUMBER NUMBER(2,2)--修改数据类型,列为空列才可以
commit;

alter table table_name rename to table_new_name--重命名表
alter table table_name add (scores number(3,2));--添加列
alter table table_name rename column scores to score--重命名列

create table day2
(studentid number(3) primary key,--主键,可以对某列进行限制重复数据
studdent_IdCard number(32) unique,--唯一约束
name varchar(16) not null,--非空约束
sex varchar(10),
scores number(5,2) check(scores>=0 and scores<=100),
birth date
)
insert into day2 values('0221','12372','hom','man','99.99',to_date('12','mm'));
insert into day2 values('003','0918','eee','man','99.99');
insert into day2 values('004','ert','man','99.99');
insert into day2 values('005','rel','man','99.99');
select scores*4 from day2--备份表结构
create table day2_bak2 as select * from day2 emp where emp

2021-9-15

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
---------------1------------------创建学生表定义学号,姓名性别年龄所在部门院系
create table Students
(Sno varchar(20) primary KEY,
Sname varchar(20) not null,
Ssex varchar(6) check(Ssex='男' or Ssex='女'),
Sage number check(Sage<=30 and Sage>=0),
Sdept varchar(20)
)

---------------2------------------

alter table Students add (BZR char(8));

--------------3--------------------

insert into Students values('9512101','李勇','男','19','计算机系','李老师');
insert into Students values('9512102','刘晨','男','20','计算机系','李老师');
insert into Students values('9512103','王敏','女','20','计算机系','李老师');
insert into Students values('9521101','张立','男','22','信息系','王老师');
insert into Students values('9521102','吴宾','女','21','信息系','王老师');
insert into Students values('9521103','张海','男','20','信息系','王老师');
insert into Students values('9531101','钱小平','女','18','数学系','周老师');
insert into Students values('9531102','王大力','男','19','数学系','周老师');
commit;

-----------------4----------------查询全体学生的学号与姓名。

select Sno,Sname from Students;

-----------------5----------------查询全体学生的姓名，学号和所在系。

select Sname,Sno,Sdept from Students;

-----------------6----------------查询计算机系全体学生的姓名。

select Sname from Students where Sdept='计算机系';

-----------------7----------------查询所有年龄在20岁以下的学生的姓名及年龄。

select Sname,Sage from Students where Sage<20;

-----------------8----------------查询年龄在20~23岁之间的学生的姓名，所在系和年龄。

select Sname,Sdept,Sage from Students where Sage>=20 and Sage<=23;
select Sname,Sdept,Sage from Students where sage between 20 and 23;
-----------------9----------------查询年龄不在20~23之间的学生的姓名，所在系和年龄。

select Sname,Sdept,Sage from Students where Sage<20 or Sage>23;

-----------------10----------------查询 数学系和计算机系学生的姓名和性别。

select Sname,Ssex from Students where Sdept='数学系' or Sdept='计算机系';
---用in操作
select * from students where sdept in('计算机系','信息系')

-----------------11----------------查询既不属于信息系，也不属于计算机系的学生的姓名和性别。

select Sname,Ssex from Students where Sdept!='数学系' and Sdept!='计算机系';

-----------------12----------------查询计算机系年龄在20岁以下的学生的姓名。

select Sname from Students where Sdept='计算机系' and Sage<20;

--题13：将学生信息按年龄升序排序输出。

select * from Students order by Sage asc;

--题14：建一张和学生表一样的表，只包含计算机系学生的数据

create table PC_s as select * from Students where Sdept='计算机系';

--题15：将王大力的年龄修改成20

update Students set Sage='20' where Sname='王大力';

--题16：删除学号是‘9531102’的信息

delete from Students where Sno='9531102';

commit;

select * from students;
create table scores
(Term varchar(2) check(term='s1' or term='s2'),
Sno varchar(8) not null,
Examno varchar(7) not null,
writtenscore number(4,1) not null,
labscore number(4,1) not null
)
alter table scores add constraint test_yyds foreign key(sno) references students(sno);--添加外键约束
insert into scores values('s1','9512101','001','200','200');
insert into scores values('s1','9512222','001','200','200');

select * from emp;
select * from dept t
select ename 姓名, sal 月薪, sal*12 年薪 from emp;
select * from emp order by hiredate;
select ename||'`s job is'||job||',salary is'||sal||',annual salary is'||sal*12||'.' from emp
----???删除相同行
create table emp_bak as select * from emp;
select * from emp_bak;
insert into emp_bak select * from emp where deptno=10;
select distinct * from emp_bak;
select distinct deptno from emp_bak;
---in操作
select * from students where sdept in('计算机系','信息系')
----

select Sname,Sdept,Sage from Students where Sage>=20 and Sage<=23;
select Sname,Sdept,Sage from Students where sage between 20 and 23;
--集合-----
--交集
select * from emp
intersect
select *from emp_bak
--并集
select * from emp
union
select *from emp_bak

select * from emp
union all
select *from emp_bak
--补集(数据对比用途)务必相互查询对比
select * from emp
minus
select *from emp_bak

select *from emp_bak
minus
select * from emp

表连接

1
2
3
4
5
6
select * from emp;
select * from dept
--查询员工姓名,所在部门
select ename 姓名,dname 部门名称 from emp join dept on emp.deptno=dept.deptno;
select * from emp join dept on emp.deptno=dept.deptno

查询

1
2
3
4
5
6
7
---模糊查询
select * from students where sname like '%刘%' ;
--名字包含小字
select * from students where sname like '_小%' ;
--名字第二个字是小字
select * from emp where ename like '_A%S' ;

day 4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT e.ENAME,e.JOB,e.SAL,d.DNAME FROM EMP e JOIN DEPT d ON e.DEPTNO=d.DEPTNO WHERE e.SAL>2000;
select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno where sal>2000;
SELECT e.ENAME,e.JOB,e.SAL,d.DNAME FROM EMP e RIGHT OUTER JOIN DEPT d ON e.DEPTNO=d.DEPTNO
select * from emp  join dept on emp.deptno=dept.deptno;
select * from emp;
select * from dept
select deptno 部门 from emp where ename='BLAKE'

select ename 姓名,hiredate 受雇日期,EMP.deptno 部门 from EMP join DEPT on emp.deptno=dept.deptno where EMP.deptno=(select deptno from emp where ename='BLAKE') AND ENAME!='BLAKE';

SELECT ename 姓名,JOB 工作 from EMP join DEPT on emp.deptno=dept.deptno where LOC='DALLAS';

select ename 姓名,SAL 工资 FROM EMP WHERE MGR=(SELECT EMPNO FROM EMP WHERE ENAME='KING');

select ename 姓名,hiredate 受雇日期,SAL 工资 from EMP join DEPT on emp.deptno=dept.deptno where SAL=(SELECT SAL FROM EMP WHERE ENAME='SCOTT');
select rownum line,e.ename from emp e;
select e.sal from emp e order by sal desc
select s from (select rownum line,s from (select e.sal s from emp e order by sal desc)) where line between 5 and 10;