Oracle第五课（学习笔记）
连接查询
JUL12_CLASS JUL12_STUDENT
SELECT CNAME,SNMAE
FROM JUL12_CLASS C,JUL12_STUDENT S
WHERE C.CID = S.CID;
SELECT CNAME,SNAME
FROM JUL12_CLASS C
JOIN JUL12_STUDENT S ON C.CID= S.CID;
用JOIN 和 ON 连接
左外连接
SELECT CNAME,SNAME
FROM JUL12_CLASS C
LEFT JOIN JUL12_STUDENT S ON C.CID = S.CID;
右外连接
SELECT CNAME，SNAME
FROM JUL12_STUDENT S
RIGHT JOIN JUL12_CLASS C ON C.CID = S.CID;
**************************************************
CREATE TABLE JUL12_S_EMP
AS
SELECT * FROM S_EMP;
SELECT ID ,USERID ,DEPT_ID ,SALARY FROM JUL12_S_EMP;
--找出大于所在部门平均工资的于员工
SELECT ID ,USERID ,DEPT_ID ,SALARY
FROM JUL12_S_EMP A,
(
SELECT DEPT_ID D,AVG(SALARY) S FROM JUL12_S_EMP
GROUP BY DEPT_ID
) D
WHERE A.DEPT_ID = D.D AND A.SALARY > D.S
ORDER BY A.ID ;
SELECT ID ,USERID ,SALARY
FROM JUL12_S_EMP A
WHERE SALARY >
(
SELECT AVG(SALARY)
FROM JUL12_S_EMP B
WHERE A.DEPT_ID = B.DEPT_ID
)
ORDER BY A.ID ;
SELECT ID ,SALARY FROM JUL12_S_EMP A,
(SELECT DEPT_ID, AVG(SALARY) AVGS
FROM JUL12_S_EMP GROUP BY DEPT_ID
==================================================
登录mysql，，，，
转到bin目录下面执行mysql -u root -p password;
没有密码的就这么写：mysql -u root ;
show database;
CREATE DATABASE MYDB;
mysql> use mydb;
mysql> create table student(
id int (4),
name char(10),
age int (4),
address varchar(20)
);
mysql> insert into STUDENT values
(1001,'zhang',23,'jiangsu);
mysql> insert into STUDENT values
(1002,'xiexie',23,'zhejiang');
mysql> insert into STUDENT values
(1003,'aaaa',33,'asdfds');
mysql> insert into STUDENT values
(1004,'sdsd',44,'sdfsdeee');
mysql> insert into STUDENT values
(1005,'sss',32,'wwwssssss');
mysql> insert into STUDENT values
(1006,'zhao',33,'zhonglu');
mysql> insert into STUDENT values
(1007,'xie',33,'kehong');
mysql> insert into STUDENT values
(1008,'good',23,'keqi');
//发现表名 要区分大小写
select * from STUDENT limit 3 －－前三行数据
select * from STUDENT limit 2 OFFSET 3--第四行还是两行数据
SELECT first_name || ' ' || last_name FROM s_emp
where id in (select manager_id from s_emp group by manager_id having count(*) >1);
union联合查询.前后查询内容相加
若有重复则只显示一次,,,若要全部显示则可以使用union all
select 8 from jul12_student where age >12;
union
select * from jul12_student where age <12 and age >10;
intersect此方式为交集 ,即为两个查询的相同的部分进行显示出来
select * from jul12_student where age >12
intersect
select * from jul12_student where age>12 and age <32;
minus此方式也可以当作分页,但是效率最低
此例子的目的在于极少minus的用法,就是用第一个查询出的结果,减去第二个查询出来的结果.如下显示的就是第三个第四条记录.
select * from jul12_student where rownum <=5
minus
select * from jul12_student where rownum <=3;
************************Objectives*************
Add and modify columns;添加和修改列
删除表 drop table [tablename]
DDL 数据定义语言,不可ROOLBACK
----ADD COLUMN
ALTER TABLE JUL12_STUDENT
ADD ABC CHAR(10);
----DELETE COLUMN
ALTER TABLE JUL12_STUDENT
DROP COLUMN ABC;
----MODIFY
ALTER TABLE JUL12_STUDENT MODIFY AGE VARCHAR(10);
对表的约束的修改
alter table jul12_student
add not null (name)
外键的约束
sql> alter table s_emp
add constraint s_emp_manager_id_fk
foreign key (manager_id)
references s_emp(id);
关闭约束
sql> alter table
disable constraint
开启约束
sql>alter table
enable constraint
更改表名
rename tablename to newtablename
删除表内全部记录
sql>truncate table s_item;
于delete的区别就是不可以ROOLBACK
事务完成后输入
commit;执行
总结Oracle数据库
oracle:
openlabe open123
window:
1.启动和关闭oracle相关的服务;
1:oracle startup shutdown
启动和关闭 listener
1:isnrctl
2.
openlabe /open 123
$sqlplus/nolog
$connect/as sysdba;
-=-=-=-=-=-=-=-=-=-=-=-=
oracle中创建用户
create user abc(用户名) identified by efg(新密码);
例:
create user tarena01 identified by tarena01
给用户授权
grant connect ,resource to tarena01两个权限
收回权限
revoke connect from tarena01(管理员权限登录)
删除一个用户
drop user abc
-=-=-=-=-=-=-=-=-=-=-=-=
mysql 2:操作用户
mysql> show databases;(注意databases我为复数)
杀看用户(user表中存放了用户信息)
mysql> use mysql;
mysql> select user from user;
mysql> select password from user where user ='root';
mysql 2:
grant all on mydb.* to 'aa' identified by 'bb';
给用户授权
grant all on mydb.* to 'aaa' identified by 'bbb';
修改密码的条件
1.进入mysql
mysql> use mysql 进入数据库
mysql> select user,password from user; 查看表内数据
mysql> update user set password =password('ef')wehre user = 'aa'; 利用update更新数据 更新password
mysql> select user,password from user;
alter user 'aa' identified by open234;
flush privileges提交
mysql> GRANT USAGE ON *.* TO aa IDENTIFIED BY 'lrock'
二 :sql
1.create table person(
a int(3) primary key,
b varchar(10),
c datetime
);
insert into person values (1,'aa',now());
insert into person values (2,'bb',now());
insert into person values (3,'cc',now());
insert into person values (4,'dd',now());
2:
select insert delete update;
性能分析的依据
oracle: set autotrace on
set timing on
mysql: explain p312 16.3
多表查询:
推荐是哟你连接查询
in(exists)
1:
rownum limit offset
2:
求每个年龄段的人数 group by age/10
３:
删除表里重复的记录
rowid具有唯一性质,标识硬盘数据存储的位置 min max ...
4:
取出表中随机的三条记录
dbms_random.random
5:
取出工资大于本部门平均工资的人.
JUL12_CLASS JUL12_STUDENT
SELECT CNAME,SNMAE
FROM JUL12_CLASS C,JUL12_STUDENT S
WHERE C.CID = S.CID;
SELECT CNAME,SNAME
FROM JUL12_CLASS C
JOIN JUL12_STUDENT S ON C.CID= S.CID;
用JOIN 和 ON 连接
左外连接
SELECT CNAME,SNAME
FROM JUL12_CLASS C
LEFT JOIN JUL12_STUDENT S ON C.CID = S.CID;
右外连接
SELECT CNAME，SNAME
FROM JUL12_STUDENT S
RIGHT JOIN JUL12_CLASS C ON C.CID = S.CID;
**************************************************
CREATE TABLE JUL12_S_EMP
AS
SELECT * FROM S_EMP;
SELECT ID ,USERID ,DEPT_ID ,SALARY FROM JUL12_S_EMP;
--找出大于所在部门平均工资的于员工
SELECT ID ,USERID ,DEPT_ID ,SALARY
FROM JUL12_S_EMP A,
(
SELECT DEPT_ID D,AVG(SALARY) S FROM JUL12_S_EMP
GROUP BY DEPT_ID
) D
WHERE A.DEPT_ID = D.D AND A.SALARY > D.S
ORDER BY A.ID ;
SELECT ID ,USERID ,SALARY
FROM JUL12_S_EMP A
WHERE SALARY >
(
SELECT AVG(SALARY)
FROM JUL12_S_EMP B
WHERE A.DEPT_ID = B.DEPT_ID
)
ORDER BY A.ID ;
SELECT ID ,SALARY FROM JUL12_S_EMP A,
(SELECT DEPT_ID, AVG(SALARY) AVGS
FROM JUL12_S_EMP GROUP BY DEPT_ID
==================================================
登录mysql，，，，
转到bin目录下面执行mysql -u root -p password;
没有密码的就这么写：mysql -u root ;
show database;
CREATE DATABASE MYDB;
mysql> use mydb;
mysql> create table student(
id int (4),
name char(10),
age int (4),
address varchar(20)
);
mysql> insert into STUDENT values
(1001,'zhang',23,'jiangsu);
mysql> insert into STUDENT values
(1002,'xiexie',23,'zhejiang');
mysql> insert into STUDENT values
(1003,'aaaa',33,'asdfds');
mysql> insert into STUDENT values
(1004,'sdsd',44,'sdfsdeee');
mysql> insert into STUDENT values
(1005,'sss',32,'wwwssssss');
mysql> insert into STUDENT values
(1006,'zhao',33,'zhonglu');
mysql> insert into STUDENT values
(1007,'xie',33,'kehong');
mysql> insert into STUDENT values
(1008,'good',23,'keqi');
//发现表名 要区分大小写
select * from STUDENT limit 3 －－前三行数据
select * from STUDENT limit 2 OFFSET 3--第四行还是两行数据
SELECT first_name || ' ' || last_name FROM s_emp
where id in (select manager_id from s_emp group by manager_id having count(*) >1);
union联合查询.前后查询内容相加
若有重复则只显示一次,,,若要全部显示则可以使用union all
select 8 from jul12_student where age >12;
union
select * from jul12_student where age <12 and age >10;
intersect此方式为交集 ,即为两个查询的相同的部分进行显示出来
select * from jul12_student where age >12
intersect
select * from jul12_student where age>12 and age <32;
minus此方式也可以当作分页,但是效率最低
此例子的目的在于极少minus的用法,就是用第一个查询出的结果,减去第二个查询出来的结果.如下显示的就是第三个第四条记录.
select * from jul12_student where rownum <=5
minus
select * from jul12_student where rownum <=3;
************************Objectives*************
Add and modify columns;添加和修改列
删除表 drop table [tablename]
DDL 数据定义语言,不可ROOLBACK
----ADD COLUMN
ALTER TABLE JUL12_STUDENT
ADD ABC CHAR(10);
----DELETE COLUMN
ALTER TABLE JUL12_STUDENT
DROP COLUMN ABC;
----MODIFY
ALTER TABLE JUL12_STUDENT MODIFY AGE VARCHAR(10);
对表的约束的修改
alter table jul12_student
add not null (name)
外键的约束
sql> alter table s_emp
add constraint s_emp_manager_id_fk
foreign key (manager_id)
references s_emp(id);
关闭约束
sql> alter table
disable constraint
开启约束
sql>alter table
enable constraint
更改表名
rename tablename to newtablename
删除表内全部记录
sql>truncate table s_item;
于delete的区别就是不可以ROOLBACK
事务完成后输入
commit;执行
总结Oracle数据库
oracle:
openlabe open123
window:
1.启动和关闭oracle相关的服务;
1:oracle startup shutdown
启动和关闭 listener
1:isnrctl
2.
openlabe /open 123
$sqlplus/nolog
$connect/as sysdba;
-=-=-=-=-=-=-=-=-=-=-=-=
oracle中创建用户
create user abc(用户名) identified by efg(新密码);
例:
create user tarena01 identified by tarena01
给用户授权
grant connect ,resource to tarena01两个权限
收回权限
revoke connect from tarena01(管理员权限登录)
删除一个用户
drop user abc
-=-=-=-=-=-=-=-=-=-=-=-=
mysql 2:操作用户
mysql> show databases;(注意databases我为复数)
杀看用户(user表中存放了用户信息)
mysql> use mysql;
mysql> select user from user;
mysql> select password from user where user ='root';
mysql 2:
grant all on mydb.* to 'aa' identified by 'bb';
给用户授权
grant all on mydb.* to 'aaa' identified by 'bbb';
修改密码的条件
1.进入mysql
mysql> use mysql 进入数据库
mysql> select user,password from user; 查看表内数据
mysql> update user set password =password('ef')wehre user = 'aa'; 利用update更新数据 更新password
mysql> select user,password from user;
alter user 'aa' identified by open234;
flush privileges提交
mysql> GRANT USAGE ON *.* TO aa IDENTIFIED BY 'lrock'
二 :sql
1.create table person(
a int(3) primary key,
b varchar(10),
c datetime
);
insert into person values (1,'aa',now());
insert into person values (2,'bb',now());
insert into person values (3,'cc',now());
insert into person values (4,'dd',now());
2:
select insert delete update;
性能分析的依据
oracle: set autotrace on
set timing on
mysql: explain p312 16.3
多表查询:
推荐是哟你连接查询
in(exists)
1:
rownum limit offset
2:
求每个年龄段的人数 group by age/10
３:
删除表里重复的记录
rowid具有唯一性质,标识硬盘数据存储的位置 min max ...
4:
取出表中随机的三条记录
dbms_random.random
5:
取出工资大于本部门平均工资的人.
