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


topN查询
select * from emp where rownum=1 or rownum=2;
select * from (select * from emp order by sal desc) where rownum<5;
分页查询
1,
select * from (select rownum no,e.* from(select * from emp order by sal desc) e where rownum<=5) where no>=3;
2,
select * from (select rownum no,e.* from(select * from emp order by sal desc)e) where no>3 and no<=5;
exist
select * from t1 where exists (select null from t2 where y=x);
for x in (select * from t1)
loop
if(exist(select null from t2 where y=x.x))

then
output the record
end if;
end loop;
select 1 from dual where null in (0,1,2,null);

多行子查询
select * from emp where sal>any(select avg(sal) from emp group by deptno);
select * from emp where sal>all(select avg(sal) from emp group by deptno);
select * from emp where job in (select job from emp where ename='MARTIN' or ename='SMITH');

随机返回6条记录
select * from (select ename,job from emp order by dbms_random.value()) where rownum<=6;


处理空值排序 last(first)
select * from emp order by comm desc nulls last;

查询跳过表中的偶数行
select ename from (select row_number() over (order by ename) rn,ename from emp) x where mod(rn,2)=1;
查找员工信息与其工资最高最低员工
select ename,sal,max(sal) over(),min(sal) over() from emp;
连续求和
select ename,sal,sum(sal) over(),sum(sal) over(order by ename) from emp;
sum(sal) over(order by ename) 指的是连续求和,是以ename来排序的,若有两个这样的窗口函数,以后面的排序为主。

分部门连续求和
select deptno,sal,sum(sal) over (partition by deptno order by ename) as s from emp;

得到上一行或下一行的数据
select ename,sal,lead(sal) over (order by sal) aaa,lag(sal) over(order by sal) bbb from emp;
统计每月及上个月和下个月的总收入 月份month 人员person 收入income
select [month] ,sum([income]),lead(sum ([income])) over(order by sum([income])) from [table] group by [month];

根据子串分组
select to_char(hiredate,'yyyy'),avg(sal) from emp group by to_char(hiredate,'yyyy');
确定一年的天数
select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') as 天数 from dual;

查询emp员工表下每个部门工资前二名的员工信息
1.
select deptno,ename,sal from emp e1 where (select count(1) from emp e2 where e2.deptno=e1.deptno and e2.ename!=e1.ename and e2.sal>e1.sal)<2 order by deptno, sal desc;
2.
select * from (select deptno ,ename,sal,row_number() over (partition by deptno order by sal desc) rn from emp) where rn<3;


数据字典
查询某用户下所有的表
select table_name from all_tables where owner='SCOTT';
查询emp表中所有字段(列)
select * from all_tab_columns where table_name='TEMP';
列出表的索引列
select *from sys.all_ind_columns where table_name='TEMP';
select * from sys.all_ind_columns where upper(table_name)='CAREUSERHAM';
列出表中约束
select * from all_constraints where table_name='TEMP';
在oracle中描述数据字典视图
select table_name,comments from dictionary where table_name like '%TABLE%';
分享到
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

--1.同义词
--访问scott用户下的emp表
SELECT * FROM SCOTT.EMP;

--创建私有同义词
CREATE SYNONYM NEWEMP FOR SCOTT.EMP;

--操作同义词
select * from newemp;

--创建公有同义词
CREATE PUBLIC SYNONYM NEWEMP1 FOR SCOTT.EMP;

select * from newemp1;

--2.序列
--创建序列
CREATE SEQUENCE SEQU1
START WITH 10
INCREMENT BY 1
MAXVALUE 20
MINVALUE 5
CYCLE
nocache;
--删除序列
DROP SEQUENCE SEQU1;
--从序列取值
SELECT SEQU1.NEXTVAL FROM DUAL;
SELECT SEQU1.CURRVAL FROM DUAL;


delete from score where stuid=109;
--提交
COMMIT;

--3.视图
create or replace view stu_score_view
as
select student.*,scoreId,score from student,score where student.stuid=score.stuid
with read only;
;
--删除视图
drop view stu_score_view;
--基于视图查询
select * from stu_score_view;
--基于视图进行修改数据
update stu_score_view set score=0 where scoreid=3; --可以修改外键表的数据
--错误:update stu_score_view set stuSex='女' where stuid=105; 不能修改主键表的数据


--创建视图存放年龄大于20的学生信息
create or replace view stu_view
as
select * from student where stuage>20
with check option;


select * from stu_view;
update stu_view set stuAge=30 where stuid=102;
update STUDENT set stuAge=0 where stuid=102;


--4.索引
--(1)创建标准索引
CREATE INDEX IDX_STUNAME ON STUDENT(stuname);
--删除索引
DROP INDEX IDX_STUNAME;

--(2)C创建唯一索引(添加了唯一约束)
CREATE UNIQUE INDEX IDX_STUNAME ON STUDENT(stuname);


--(3)组合索引:在经常搜索的多列上添加一个索引
CREATE INDEX IDX_STUNAME ON STUDENT(stuname,stuage);


--(4)位图索引:在类别搜索的列上添加一个索引
CREATE BITMAP INDEX IDX_STUNAME ON STUDENT(stucourse);


--(5)反向索引:操作需要锁定字段时 索引
CREATE INDEX IDX_STUNAME ON STUDENT(stuid) REVERSE;


--(6)区分表索引:数据较多 用分区 索引
CREATE INDEX IDX_STUNAME ON STUDENT(stuid) LOCAL;
CREATE INDEX IDX_STUNAME ON STUDENT(stuid) GLOBAL;

SELECT * FROM STUDENT WHERE stuname like '%a%';
--INSERT INTO STUDENT VALUES(121,'QQ','男',20,1.63,sysdate);


SELECT * FROM SCORE;
update score set score=56 where scoreid=1;
分享到
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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262

1、开启服务:先开启Lisenterner服务 再开启ORCL服务
2、oracle登录
用户:管理员 sysDBA - 无密码
系统用户 system - manager
普通用户 scott - tiger

sqlplus scott/tiger DOS窗口中用sqlplus命令,以scott用户的身份登录到oracle
conn sys/sys as sysdba; 以无密码的系统管理员的身份重新连接(相当于SQLSERVER的windows身份验证)
alter user 用户名 identified by 密码; 更改用户的密码
show user; 查看当前登录的用户

3、ORCL 数据库
表空间(tablespace):在磁盘中形成磁盘文件,相关表的集合
表(table)
管理表中的数据(insert delete update select)

4、创建 create 删除drop 修改alter
创建表空间:
create tablespace 表空间名
datafile '物理存放路径.dbf'
size 大小;

create table 表名(
列名 数据类型 约束,
列名 数据类型 约束,
列名 数据类型 约束,
....
);
数据类型:
数字:number 可以存整数和小数,可以指定小数位数
number(P,S) P精度(小数+整数的位数) S小数位数
int 整数
float 小数
非数字:char(10) 固定 存储数据时,不够长度以空格填补,要存满10个字节
varchar(10) 可变 存储的内容实际的长度,不需要存满10个字节
varchar2(长度) 同 varchar 4000个字符
long 可变的字符串 2GB
日期:date

6、向表插入数据

7、序列:生成递增的数字

同义词的分类:同义词共有两种类型:
公有同义词可被所有的数据库用户访问。
私有同义词只能在其模式内访问,且不能与当前模式的对象同名。
CREATE (PUBLIC) SYNONYM 同义词名 FOR 模式名.表名;

注意:一个用户对应一个模式
create sequence 序列名
start with 初始值
increment by 递增;
maxvalue 最大值
minvalue 最小值
NOCYCLE/CYCLE
cache 缓存值;
缓存值不指定默认为20,nocache缓存预先不分配值,缓存的值必须小于循环的值
nimvalue与循环(cycle)一起使用,指下次循环的开始值
start with 起始值 :第一次取值的起始值
序列取值:
序列名.nextVal 下一个值
序列名.currVal 当前值

1、查询
条件查询、聚合函数、分组查询、连接查询、子查询

1、基本查询
select 列信息 from 表名 where 条件 group by 分组列 having 分组后筛选 order by 排序列 acs/desc

2、多表查询:内连接、外连接

3、子查询:作为条件、列、表
select ... from ... where ...
update 表 set 列=更新值 where (子查询)
delete from 表 where (子查询)

4、视图:简化SQL语句,实际存放的是查询语句,不是数据,数据存放在源表中
CREATE [OR REPLACE] VIEW 视图名
as
查询语句
[with check option] 主要针对视图的查询语句带条件,对视图执行修改操作时,不允许使视图的查询结果行数变少
[with read only] 只读


5.索引:提高查询的速度
主键列默认添加索引
(1).标准的索引:create index 索引名 on 表名(列名);
索引添加在经常搜索的列上

(2).唯一索引:索引列值不重复
create unique index 索引名 on 表名(列名);

(3)、组合索引:经常按照多列搜索,则可以在多列上创建一个索引
create index 索引名 on 表名(列名1,列名2);

(4)、反向键索引:列值连续
通常索引是建立在连续增长的列上,使数据均匀地分布在整个索引上,索引存储在硬盘上是以块的形式存储的,
如果数据的修改导致顺序发生改变,则系统会锁定修改记录所在的数据块,这样同一个块中的数据要发生操作必须等待,
大大降低了并发性
反向键索引就是反转索引列中的每一个字节,使数据分散的存放到磁盘不同的块上,提高数据访问的并发性
将反转后的键按照常规索引存储
语法:CREATE INDEX 索引名 ON 表名 (列名) REVERSE;
(5)、位图索引:列值大量重复
语法:CREATE BITMAP INDEX 索引名 ON 表名 (列名);

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言
PL/SQL 是对 SQL 的扩展
支持多种数据类型,如大对象和集合类型,可使用条件和循环等控制结构
可用于创建存储过程、触发器和程序包,给SQL语句的执行添加程序逻辑
与 Oracle 服务器和 Oracle 工具紧密集成,具备可移植性、灵活性和安全性

优点:
1、可以编写逻辑代码
2、PL/SQL中的代码自动形成一个事物,出现异常就会一起回滚
3、更佳的性能,减少客户端对ORACLE服务器端的访问量,PL/SQL 经过编译执行的
*/

/*
存储过程(procedure):命名的PL/SQL代码快,类似JAVA的方法
一个功能定义到一个存储过程中,需要使用时直接调用

条件结构
IF 条件 THEN
语句;
ELSIF 条件 THEN
语句;
ELSIF 条件 THEN
语句;
....
ELSE
语句N;
END IF;
*/
/*
转账:
1、修改 对方账户 余额增加
2、修改 本账户 余额减少
3、添加 一条 交易记录
*/

--3.根据指定的学号查询的学生的信息,如果存在输出学生信息,不存在提示对应的信息
--带参数的存储过程
CREATE OR REPLACE PROCEDURE PROC1(id student.stuid%type)
AS
--变量与表中的一行数据进行映射,语法:变量名 表名%rowtype;
stu student%rowtype;
num int; --存放根据学号查询的记录行数
begin
select count(*) into num from student where stuid=id;
--判断
if num=0 then
dbms_output.put_line('学号不存在!');
else
select * into stu from student where stuid=id;
dbms_output.put_line(stu.stuid||'-'||stu.stuname);
end if;
end proc1;
--调用存储过程并传参数
call proc1(0);

--循环:
--LOOP循环
/*
LOOP
EXIT WHEN 退出循环的条件表达式;
执行语句;
END LOOP;
*/
--循环输出1-10
create or replace procedure proc1
as
i int:=1; --定义变量并赋初值
begin
LOOP
EXIT WHEN I>10;
dbms_output.put_line(I);
I:=I+1;
END LOOP;
end proc1;

--
CALL PROC1();

--WHILE循环
/*
WHILE循环语法:
WHILE 进行循环条件
LOOP
执行语句;
END LOOP;

*/
create or replace procedure proc1
as
i int:=1; --定义变量并赋初值
begin
WHILE i<=10
LOOP
dbms_output.put_line(I);
I:=I+2;
END LOOP;
end proc1;
--
CALL PROC1();

--for循环
/*
FOR 变量 IN 起始值..结束值
LOOP
执行语句;
END LOOP;
*/
create or replace procedure proc1
as
begin
for i in 1..10
loop
dbms_output.put_line(I);
end loop;
end;
--
call proc1();
--定义存储过程向学生信息表添加20行测试数据
create or replace procedure proc1
as
begin
for i in 1..20
loop
insert into student values(i,'测试'||i,'男',20,1.68,sysdate);
end loop;
end;
--
call proc1();

--判断某张表是否存在,如果存在就删除
CREATE OR REPLACE PROCEDURE CHECKTABLE(TABLENAME VARCHAR)
AS
NUM INT;
BEGIN
SELECT COUNT(*) INTO NUM FROM USER_TABLES WHERE TABLE_NAME=TABLENAME;
IF NUM>0 THEN
--删除表
--DROP TABLE TABLENAME;
EXECUTE IMMEDIATE 'DROP TABLE '||TABLENAME;
END IF;
END;
--注意:PL/SQL中不支持DDL语句:create drop,需要使用命令 EXECUTE IMMEDIATE 'DDL语句' 执行
CALL CHECKTABLE('ABC');
CREATE TABLE ABC
(
EMPID INT
);


SELECT * FROM USER_TABLES WHERE TABLE_NAME='ABC';


SELECT * FROM STUDENT;
SELECT * FROM SCORE;

commit;
分享到
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

--创建表空间
create tablespace myDB
datafile 'E:\xxs\Oracle学习笔记\tablespaceFile\myDB.dbf'
size 5M;
--更改表空间名称
alter tablespace myDB
rename to myODB
--在表空间下添加数据文件
alter tablespace myODB
add datafile'E:\xxs\Oracle学习笔记\tablespaceFile\myODB.dbf'
size 5M;
--修改表空间下的数据文件
alter database
datafile 'E:\xxs\Oracle学习笔记\tablespaceFile\myODB.dbf'
resize 10M;
--修改表空间下的数据文件 :允许自动扩展,每次增长5M,最大容量为50M
alter database
datafile 'E:\xxs\Oracle学习笔记\tablespaceFile\myODB.dbf'
autoextend on next 5M Maxsize 50M;
--删除表空间
--drop tablespace myODB;--查询表空间
select * from user_tablespaces where tablespace_name ='MYODB'
--查询表
select * from user_tables where table_name='SYSTEM'


/*
create tablespace myODB
datafile 'E:\xxs\oracleDBFile\myodb.dbf'
size 5M
autoextend on next 5M maxsize 50M;
*/
--在表空间myODB下创建部门信息表

--drop table deptInfo;
create table deptInfo
(
--deptId number(8,2) --整数+小数的位数为8位,小数2位
deptId int primary key,
deptName varchar2(20) unique not null, --unique唯一约束
deptDesp varchar2(100)
)tablespace myODB;

--drop table deptInfo;
/*
insert into deptInfo values(1);
insert into deptInfo values(1.5);
insert into deptInfo values(0.96);
insert into deptInfo values(0.968593);
insert into deptInfo values(123456.96);
*/

--注意:创建表时,不指定表空间,默认表创建到system表空间下
--系统表:
--user_tables:当前登录用户下的所有的表
--user_tablespaces:当前登录用户下的所有的表空间
select * from user_tables where TABLE_NAME='DEPTINFO';
select * from user_tablespaces;

--在表空间empDB2下创建员工信息表
create table empInfo
(
empId int primary key,
empName varchar2(20) not null,
empSex char(2) check(empSex='男' or empSex='女'),
empAge int check(empAge>=18 and empAge<=60),
empAdd varchar2(20) default('湖北武汉'),
empDate date default(sysdate), --sysdate获取系统当前时间
deptId int references deptInfo(deptId)
)tablespace myODB;

--添加数据
insert into deptInfo values(1,'人事部','无');
insert into deptInfo values(2,'财务部','无');
insert into deptInfo values(3,'市场部','无');

insert into empInfo values(101,'matos','男',20,default,default,1);
insert into empInfo values(102,'admin','男',23,default,default,2);
insert into empInfo values(103,'root','女',20,default,default,2);
insert into empInfo values(104,'sa','女',20,default,'8-9月-2016',2);
insert into empInfo values(105,'李易峰','男',20,default,sysdate,2);
--查询
select * from deptInfo;
select * from empInfo;

--序列(sequence):产生连续递增的数字
/*
create sequence 序列名
start with 起始值
increment by 增长值;
*/
create sequence seq_empid
start with 1001
increment by 2;
--dual虚拟表,为了完善语法
--查询序列的下一个值
select seq_empid.nextval from dual;


--删除员工表的数据
delete from empInfo;

--添加员工信息表的数据,用序列作为编号
insert into empInfo values(seq_empid.nextval,'八十','女',20,default,default,1);
insert into empInfo values(seq_empid.nextval,'艾斯比','女',20,default,default,2);
insert into empInfo values(seq_empid.nextval,'老子','女',20,default,default,2);
insert into empInfo values(seq_empid.nextval,'少女時代','女',20,default,'8-9月-2016',2);
insert into empInfo values(seq_empid.nextval,'是否','女',20,default,sysdate,2);


select * from deptInfo;
分享到
1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE FUNCTION F_MAIN(PARAM1 in int,PARAM2 in int)
RETURN INT
IS
BEGIN
RETURN PARAM1+PARAM2;
END;


BEGIN
DBMS_OUTPUT.PUT_LINE('使用函数玩一波==>'||F_MAIN(099,88));
END;
分享到
1
2
3
4
5
6
7
8
9
10
11
12

conn system/manager
1. 数据库database
物理存储结构
数据文件,重做日志文件,空值文件

desc v$logfile;
select member from v$logfile;
v$controlfile
v$datafile;
逻辑存储结构
表空间,段,区,块
分享到

–房屋出租管理系统 表空间名FwDB

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

create tablespace FwDB
datafile 'E:\xxs\oracleDBFile\FwDB.dbf'
size 5M
autoextend on next 1M maxsize 30M



--用户信息表(发表出租房屋信息的用户)UserInfo:
--用户编号UserId int 主键
--用户姓名UserName varchar(30) 不能为空
--联系电话UserTel varchar(11) 不能为空
create table userInfo(
userId int primary key ,
userName varchar(30) not null,
userTel varchar(11) not null
)tablespace FwDB;

--房屋类型表(FwLx):
--类型编号LxId int 主键
--类型名LxName varchar(20) 不能为空 唯一约束 (列值为别墅、公寓、平房、地下室等等)
create table FwLx
(
lxId int primary key,
lxName varchar(20) not null unique,
)tablespace FwDB

--房屋信息表(FwXx)
--房屋编号FwId int 主键
--房屋类型编号LxId int 外键 引用类型表的类型编号
--房屋地址FwDiZhi varchar(60) 不能为空
--室shi int 不能为空 值大于等于0
--厅ting int 不能为空 值大于等于0
--租金ZuJin int 不能为空 值大于等于0
--房屋说明FwDesp varchar(500)
--联系人编号userId 外键 引用用户信息表的用户编号
create table FwXx(
fwId int primary key,
lxId int references FwLx(Lxid),
fwDiZhi varchar(60) not null,
shi int not null check(shi>0),
ting int not null check(ting>0),
zuJin int not null check(zuJin>0),
fwDesp varchar(500),
userId references userInfo(userId)
)tablespace FwDB;


insert into userInfo values(0,'admin',110);
insert into userInfo values(1,'system',11234);
insert into userInfo values(2,'matos',1313123);
insert into userInfo values(3,'AMT',3123231);
insert into userInfo values(4,'张三',12323231);
insert into FwLx values(1,'别墅');
insert into FwLx values(2,'公寓');
insert into FwLx values(3,'平房');
insert into FwLx values(4,'地下室');

insert into FwXx values(1,1,'湖北武汉',2,1,1205,'好好好',3);
insert into FwXx values(2,2,'湖北武汉光谷广场',4,2,2146,'好好好',1);
insert into FwXx values(3,2,'湖北武汉江夏',2,2,1008,'好好好',3);
insert into FwXx values(4,3,'湖北武汉藏龙岛',3,2,645,'好好好',2);
insert into FwXx values(5,4,'湖北武汉软件工程学院',1,1,205,'好好好',2);
insert into FwXx values(6,4,'湖北武汉软件工程学院',1,2,705,'好好好',2);
insert into FwXx values(7,3,'湖北武汉软件工程学院',1,2,605,'好好好',4);

--.按要求建库、建表、建约束

--2.向每张表中添加数据,每张表不少于3行

--3.将编号为1的用户的联系电话修改为13088888888
update userInfo set userTel=13088888888 where userId=1;
--4.将所有房屋的租金加200
update FwXx set zuJin=zuJin+200 ;
--5.删除租金大于8000的房屋信息
--delete FwXx where zuJin>8000;
--6.查询所有1室2厅的房屋信息
select * from FwXx where shi=1 and ting=2 ;
--7.查询租金在200-500之间的房屋信息
select * from FwXx where zuJin >=200 and zuJin<=500;
--8.查询房屋类型为‘地下室’的房屋信息
select * from FwXx where lxId=(select lxId from FwLx where lxName='地下室');
--9.查询房屋说明中有“好”字的房屋编号、租金和联系人姓名以及电话
select fwId,zuJin,u.userName,u.userTel from FwXx f,userInfo u where fwDesp like '%好%' and f.userId=u.userId;
--select fwId,zuJin,(select * from userInfo u where u.userId=f.userId ) as userName from FwXx f where fwDesp like '%好%' ;
--10.查询房屋类型为“公寓”或“别墅”的房屋信息
--select * from FwXx where lxId in(1,2);select lxId from FwLx where lxName='公寓',select lxId from FwLx where lxName='别墅'
select * from FwXx where lxId in(select lxId from FwLx where lxName in('公寓','别墅'));
--11.查询房屋类型为“平房”的房屋的编号、租金、联系人姓名以及电话
select fwId,zujin,u.userName,u.userTel from FwLx l,userInfo u,FwXx f where l.lxname='平房' and f.lxid=l.lxid and u.userId=f.userid;
--12.查询房屋地址在光谷的房屋信息,并按租金降序显示查询结果
select * from fwxx where fwdizhi like '%光谷%' order by zujin desc;
--13.查询所有房屋的数量,平均租金,最高租金,以及最低租金
select sum(zujin)总数,avg(zujin)平均租金,max(zujin)最高租金,min(zujin)最低租金 from fwxx;
--14.查询房屋类型是“平房”的最高租金
select max(zujin) 最高租金 from fwxx where lxid=(select lxId from FwLx where lxName='平房');
--15.查询每种类型的房屋的数量,平均租金,最高租金,以及最低租金
select lxid, sum(zujin) 房屋数量,avg(zujin) 平均租金,max(zujin) 最高租金 ,min(zujin)最低租金 from fwxx group by lxid
--16.查询房屋的编号,房号地址,房屋租金,类型名称、发布用户的姓名和联系电话
select f.fwid,fwdizhi,zujin,l.lxName,u.userName,u.userTel from fwxx f,fwlx l,userinfo u where f.lxid=l.lxid and f.userId=u.userid;
--17.查询联系人姓名为“张三”的所有的房屋信息
select * from fwxx where userId=(select userId from userInfo where userName='张三');
--18.查询没有发布房屋信息的联系人信息
select * from userinfo where userid not in(select userid from fwxx);

----
select * from userInfo;
select * from FwLx;
select * from FwXx;

分享到

note@mysql学习笔记!对用户账户管理

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

--创建用户,并分配权限;
1.通过分配权限创建一般分为四种情况

//创建用户名为matosiki密码为matosiki的用户,并分配最高权限,可用任何主机。
GRANT ALL PRIVILEGES ON *.* TO 'matosiki'@'%' IDENTIFIED BY 'matosiki' WITH GRANT OPTION;

//创建用户名为matosiki密码为matosiki的用户,并分配最高权限,只能在本机访问。
GRANT ALL PRIVILEGES ON *.* TO 'matosiki'@'localhost' IDENTIFIED BY 'matosiki' WITH GRANT OPTION;

// 一个账户有用户名admin,没有密码。该账户只用于从本机连接。授予了RELOAD和PROCESS管理权限。
//这些权限允许admin用户执行mysqladmin reload、mysqladmin refresh和mysqladmin flush-xxx命令,
//以及mysqladmin processlist。未授予访问数据库的权限。你可以通过GRANT语句添加此类权限。
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
// 一个账户有用户名dummy,没有密码。该账户只用于从本机连接。未授予权限。通过GRANT语句中的USAGE权限,
//你可以创建账户而不授予任何权限。它可以将所有全局权限设为'N'。假定你将在以后将具体权限授予该账户。
GRANT USAGE ON *.* TO 'dummy'@'localhost';

除了GRANT,你可以直接用INSERT语句创建相同的账户,然后使用 FLUSH PRIVILEGES告诉服务器重载授权表:
2.
分配数据库wb_map的所有操作权限给 admin账户
GRANT all ON wb_map.* TO 'admin'@'%' identified by 'admin' with grant option;

3.查看数据用户
> use mysql
> select * from user;
4.删除用户
@>mysql -u root -p

   @>密码

  mysql>Delete FROM user Where User='test' and Host='localhost';

  mysql>flush privileges;

  mysql>drop database testDB; //删除用户的数据库

删除账户及权限:>drop user 用户名@'%';

        >drop user 用户名@ localhost;

5.修改指定用户密码

  @>mysql -u root -p

  @>密码

  mysql>update mysql.user set password=password('新密码') where User="test" and Host="localhost";

  mysql>flush privileges;


5. 列出所有数据库

  mysql>show database;



6. 切换数据库

  mysql>use '数据库名';



7. 列出所有表

  mysql>show tables;



8. 显示数据表结构

  mysql>describe 表名;

分享到
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
1. SQLServer连接--helper工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/*
* 负责数据库的开关
*/
public class DBHelper {
//获得连接的方法 将连接返回
public static Connection openConn(){
Connection conn = null;
try {
//注册驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//获得连接
conn = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;database=newsDB","sa","123456");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}

//关闭连接 连接对象作为参数
public static void closeConn(Connection conn){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

}
1. oracle连接--helper工具类
public class DBHelper {
public static Connection getConn() {
Connection conn=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","system","manager");

} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void colseConn(Connection conn){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

}
}
3. mysql连接--helper工具类
public class DBHelper {
public static Connection getConn() {
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/lightnote", "root", "123456");

} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void colseConn(Connection conn){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

}
}
分享到
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT PASSWORD("do one thing and do it well!");
SELECT MD5("do one thing and do it well!");
SELECT ENCODE("do one thing and do it well!","matosiki");
SELECT DECODE(ENCODE("73516260","matosiki"),"matosiki");
SELECT AES_ENCRYPT("whats fuck","haha");
SELECT AES_DECRYPT(AES_ENCRYPT("whats fuck","haha"),"haha");

-- 我自己创建的插入加密触发器

DROP TRIGGER /*!50032 IF EXISTS */ `insert_encode_matosiki`$$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `insert_encode_matosiki` BEFORE INSERT ON `matos_code`
FOR EACH ROW BEGIN
SET new.u_pwd = AES_ENCRYPT(new.u_pwd,"matosiki"); -- 其中这个加密参数自己改。我最崇拜的技术大牛。
END;
$$

DELIMITER ;
分享到