数据库编程——Oracle MySQL SQL

在学习数据库编程总结了笔记，并分享出来。有问题请及时联系博主：Alliswell_WP，转载请注明出处。

09-数据库编程day03(oracle mysql sql)

目录：
一、学习目标
二、复习
三、Oracle SQL语句
1、表的创建和管理
2、oracle表的约束
3、sequence序列的使用
4、view视图
5、synonym同义词
6、index索引
7、创建一个表，把excel的数据导入
8、windows客户端创建新用户
四、MySQL SQL语句
1、MySQL基础
2、MySQL组合拳保证服务正常
3、MySQL库的操作
4、MySQL表的操作
5、MySQL数据的操作
6、MySQL组函数相关
7、MySQL日期函数、字符函数、数学相关函数
8、MySQL转换函数
9、MySQL多表查询
（1）多表查询准备
（2）MySQL内连接
（3）MySQL外连接
10、MySQL补充
（1）也可以这样查询
（2）取某几名

一、学习目标

1.oracle创建和管理表
2.oracle的视图,索引,同义词,序列(了解作用和创建方式)
3.mysql库的增删改查
4.mysql表的增删改查
5.mysql数据的增删改查

二、复习

》多表查询的理论基础：笛卡尔集

笛卡尔集行数= 表1的行数*表2的行数
列数= 相加 N张表的连接条件至少是N-1

》统计：部门编号，部门名称，人数

select  d.deptno,d.dname,count(e.empno)    
from emp e,dept d 
where e.deptno=d.deptno 
group by d.deptno,d.dname;

--右外连接统计各部门人数

select  d.deptno,d.dname,count(e.empno)    
from emp e,dept d 
where e.deptno(+)=d.deptno 
group by d.deptno,d.dname;

--自连接显示xxx的老板是yyy

select e.ename||'''s boss is '||b.ename
  from emp e,emp b 
where e.mgr = b.empno;

——显示大老板是他妻子

select e.ename||'''s boss is '||nvl(b.ename,'his wife!')  
 from emp e,emp b
 where e.mgr = b.empno(+);

》子查询可以放置的地方：

select
from
where
group by … err
having
order by … err

》子查询注意事项：
○ 书写规则，缩进，换行，加（）
○ by后不能放置子查询
○ from 后放置的是集合
○ 单行子查询使用单行操作符，多行子查询使用多行操作符（in，any，all）

》练习中条件表达式：

decode（column|expr，search1，res1，search2，res2， …,default)
case expr when expr1 then res1
when expr2 then res2
…
else …
end

三、Oracle SQL语句

1、表的创建和管理

》表的管理：
○ 创建前提条件：表空间+权限
○ 表名和列名注意事项：
▪ 长度不能超过30
▪ 第一个必须是字母

》数据类型

--创建一个表
create table t1(id number,name varchar2(30));

--增加一个列
alter table t1 add email varchar2(30);
--修改列属性
alter table t1 modify email varchar2(40);

--重命名列
alter table t1 rename column email to address;

--删除列
alter table t1 drop column address;

--重命名表

重命名表—不用加table！
SQL> rename t1 to t2;
SQL> drop table t2;
表已删除。

》oracle给提供了回收站（drop命令后，实际删除到回收站。show recyclebin可以查看回收站。）

回收站的表可以（恢复）闪回（10g开始支持）
SQL> flashback table t2 to before drop;

闪回完成。

purge的作用删除不经过回收站
drop table t2 purge;

--清空回收站
drop table emp10;
purge recyclebin;

flashback table emp10 to before drop;

注意：sys用户没有回收站！

》通过已有表创建新表：
create table newtable as select * from srctab where 1=2;
列可以指定default值，如果该列不显示插入，使用默认值。

2、oracle表的约束

》oracle表的约束（5种）：
○ 检查
○ 非空 Not Null
○ 唯一 Unique
○ 主键（非空+唯一）Primary Key
○ 外键 Foreign Key

例如：部门表dept和员工表emp，不应该存在不属于任何一个部门的员工。用来约束两张表的关系。
注意：如果父表的记录被子表引用的话，父表的记录默认不能删除。解决方法：
1）先将子表的内容删除，然后在删除父表。
2）将子表外键一列设置为NULL值，断开引用关系，然后删除父表。

》外键删除方式：
无论哪种方法，都要在两个表进行操作。所以定义外键时，可以通过references指定如下参数：
——ON DELETE CASCADE：当删除父表时，如发现父表内容被子表引用，级联删除子表引用记录。
——ON DELETE SET NULL：当发现上述情况，先把子表中对应外键值置空，再删除父表。
——默认方式，主表删除的时候，如果子表引用了该字段的数据，不能删除。先删子表，再删主表。

多数情况下，使用SET NULL方法，防止子表列被删除，数据出错。

如：dept表delete删除默认方式：

》外键关系图：子表引用主表的主键

》练习：

--创建student表

create table student(
id number constraint pk_student primary key, （constraint是起别名用的）
name varchar2(30) not null,
email varchar2(30) unique,
sex varchar2(10) check(sex in ('男','女')),
sal number check(sal>10000),
deptno number(2) references dept(deptno) on delete set null（定义外键时，设置参数on delete set null）
);

--增加student表一列student
alter table student add hiredate date default sysdate;（default设置默认值为系统时间）

--违反检查约束

insert into student(id,name,email,sex,sal,deptno) values(1,'yekai','yekai@itcast.cn','xx',10001,52) ;

insert into student(id,name,email,sex,sal,deptno) values(1,'yekai','yekai@itcast.cn','男',1000,52);

注意：未起别名的oracle默认起了个编号！（建表时候最好都起别名，可以知道哪条出错）

insert into student(id,name,email,sex,sal,deptno) values(1,'yekai','yekai@itcast.cn','男',10020,52);

insert into student(id,name,email,sex,sal,deptno) values(1,'yekai','yekaison@itcast.cn','男',10020,52);

insert into student(id,name,email,sex,sal,deptno) values(2,'yekaison','yekaison@itcast.cn','男',10020,52);

insert into student(id,name,email,sex,sal,deptno) values(3,null,'yekaison@itcast.cn','男',10020,52);

insert into student(id,name,email,sex,sal,deptno) values(3,'fuhongxue','fuhongxue@itcast.cn','男',10020,60);

insert into student(id,name,email,sex,sal,deptno) values(3,'fuhongxue','fuhongxue@itcast.cn','男',10020,10);

commit;

delete from dept where deptno=52; （删除52部门，由于之前外键定义on delete set null，所以为nulll）

commit;

3、sequence序列的使用

》数据库的对象：表、视图、索引、序列、同义词
存储过程、存储函数、触发器、包、包体、数据库链路(datalink)、快照。(12个)

学习5种：
表基本的数据存储集合，由行和列组成。
视图从表中抽出的逻辑上相关的数据集合。
序列提供有规律的数值。
索引提高查询的效率
同义词给对象起别名

》序列：

可以理解成数组：默认，从[1]开始，长度[20]
[1, 2, 3, 4, 5, 6, …, 20]在内存中。
由于序列是被保存在内存中，访问内存的速率要高于访问硬盘的速率。所以序列可以提高效率。
》序列的使用：
1.初始状态下：指针*指向1前面的位置。欲取出第一个值，应该将*向后移动。每取出一个值指针都向后移。
2.常常用序列来指定表中的主键。
3.创建序列：create sequence myseq 来创建一个序列。

》创建序列：
CREATE SEQUENCE sequence （后边的不需要记忆，只需记忆此行就行。）
       [INCREMENT BY n]
       [START WITH n]
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE n | NOMINVALUE}]
       [{CYCLE | NOCYCLE}]
       [{CACHE n | NOCACHE}];
NOCACHE表示没有缓存，一次不产生20个，而只产生一个。

》序列内部实现图：

--创建序列
SQL> create sequence myseq;（创建序列myseq）
select myseq.currval from dual;（初始状态下，指针*指向1前面的位置，所以会报错）

序列的使用：给主表的主键字段使用，防止主键冲突
insert into dept values(50+myseq.nextval,myseq.nextval||'name',myseq.nextval||'-loc');

然后可以一直执行，不会有问题。

》思考：什么情况下主键不连续？
○ 删除数据
○ 给多多个表使用
○ 执行了rollback
○ 掉电，宕机，序列会从下一段直接加载

》“PL/SQL Developer”“New”下的“Command Window”输入命令ed 序列号（如：刚才创建的myseq：ed myseq）可视化查看序列：

点击右下角的“View SQL”可以查看SQL命令，所以之前太长的创建指令不需要记忆。

--删除序列

drop sequence myseq;

4、view视图

》视图： 从表中抽出的逻辑上相关的数据集合

视图的作用：简化复杂查询，隔离数据访问

--创建一个视图
create or replace view v_count （v_count为别名）
as
select d.deptno,d.dname,count(e.empno) count
from emp e,dept d
where d.deptno=e.deptno(+)
group by d.deptno,d.dname
order by 1;

注意：创建视图需要权限
赋权--登陆管理员
sqlplus sys/sys@orcl100 as sysdba （如果未配置orcl100，写ip）
SQL> grant create view to scott;

视图并不能提高查询效率，视图本身没有数据，视图依赖于表

--replace可以重新刷新视图，如果没有replace，再次执行创建相同的视图，会报错。

--删除视图
drop view v_count；

》总结：不过过视图做insert、update、delete等操作。因为视图提供的目的就是为了简化查询。

5、synonym同义词

》同义词：给表起的别名

作用：简化查询，隔离访问

切换到hr用户查看有什么表：

在scott用户下访问hr用户下的EMPLOYEES表：

原因没有权限：
可以在hr赋权
SQL> grant select on employees to scott;

然后在scott下即可访问。

为了简化表名，所以需要同义词：

--scott用户下创建同义词
create synonym employees for hr.EMPLOYEES;

需要登录管理员授权：

sqlplus sys/sys@orcl100 as sysdba （如果未配置orcl100，写ip）
SQL> grant create synonym to scott;

再在scott用户下创建同义词
create synonym employees for hr.EMPLOYEES;

这样可以在scott用户下访问hr下的表
select * from employees;

可以查看表的类型

select * from tab;

--删除同义词
drop synonym employees;

6、index索引

索引的作用：提高查询效率

索引提高查询效率的原因：数据是有序的（btree）

创建索引之后，系统会自动维护索引表

--创建索引：（给emp表的deptno创建索引，名字为myindex）
create index myindex on emp(deptno);

怎么在查询的过程中使用上索引？

必须使用索引字段作为查询条件。

所以，第1条没有用到索引，第2条用到了索引。

上述是一个字段的索引，也可以创建联合索引，多个字段
create index myindex2 on emp(deptno,ename);

》以下情况可以创建索引:
1）列中数据值分布范围很广
2）列经常在 WHERE 子句或连接条件中出现
3）表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%

》下列情况不要创建索引:
1）表很小
2）列不经常作为连接条件或出现在WHERE子句中
3）查询的数据大于2%到4%
4）表经常更新

--删除索引 :drop index myindex;
主键字段默认就是索引！

7、创建一个表，把下边Excel的数据导入

1）“PL/SQL Developer”的“New”下的“Command Window”，点击“Editor”输入下边命令：

2）点击“齿轮”，表创建好了。

3）“New”下的“SQL Window”，输入下面SQL语句：

4）点击“齿轮”，表头创建好了

5）点击查询数据任务栏上的“🔒”解锁，然后在excel中Ctrl+c复制数据，到查询的数据处先点击表格一下，再点击标签一下，然后Ctrl+v，点击“—”可以删除一条数据，然后再点击查询数据任务栏上“✔”，再点击查询数据任务栏上的“🔒”锁上，然后点击菜单栏上的“Commit 提交”按钮（看着像下载的按钮）。

6）输入如下SQL语句可以增加数据，（注意：，因为设置了主键，重复的主键不能添加），

然后再点击查询数据任务栏上“✔”，再点击查询数据任务栏上的“🔒”锁上，然后点击菜单栏上的“Commit 提交”按钮（看着像下载的按钮）。

7）可视化增加或修改表头字段

点击左下的“Window list”中“Command Window”，输入命令：SQL>ed biancheng，会打开可视化工具：

点击“Columns”可以增加列：

点击右下角的“View SQL”可以查看实际生成的指令。

8）可视化 ed命令查看序列、同义词

create synonym employees for hr.employees;

ed employees（查看同义词）

9）export 导出数据

export emp

选中“SQL inserts”，按住“Ctrl”键，可以多选几个表，然后在“SQL inserts”可以勾选需要导出的数据，更改“Output file”中的表名，然后点击export可以导出数据（变相相当于备份了数据。）

8、windows客户端创建新用户

Windows下使用命令创建一个新用户（表空间）

-- 01 创建表空间
-- 注意表空间的路径 根据实际安装环境进行调整

CREATE TABLESPACE ts_myscott 
    LOGGING 
    DATAFILE 'C:/app/Administrator/oradata/orcl/ts_myscott.dbf' SIZE 10M 
    EXTENT MANAGEMENT LOCAL;
    
CREATE TABLESPACE ts_myscott2 
    LOGGING 
    DATAFILE 'C:/app/Administrator/oradata/orcl/ts_myscott2.dbf' SIZE 20M
    EXTENT MANAGEMENT LOCAL;
    
ALTER DATABASE DATAFILE 'C:/app/Administrator/oradata/orcl/ts_myscott.dbf' AUTOEXTEND ON NEXT   10M MAXSIZE UNLIMITED;
ALTER DATABASE DATAFILE 'C:/app/Administrator/oradata/orcl/ts_myscott2.dbf' AUTOEXTEND ON NEXT  20M MAXSIZE UNLIMITED; 

commit;

-- 02 创建方案 (创建用户)
CREATE USER MYSCOTT  PROFILE DEFAULT 
    IDENTIFIED BY MYSCOTT DEFAULT TABLESPACE USERS 
    ACCOUNT UNLOCK;

-- 资源和登录权限
GRANT RESOURCE TO MYSCOTT;
GRANT create session TO MYSCOTT;


-- 03 创建表
-- 创建部门表 并赋值
CREATE TABLE MYSCOTT.DEPT(
    DEPTNO         NUMBER(2) PRIMARY KEY,
     DNAME       VARCHAR2(14) NOT NULL,
     LOC            VARCHAR2(13)
)TABLESPACE ts_myscott;

INSERT INTO MYSCOTT.dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO MYSCOTT.dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO MYSCOTT.dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO MYSCOTT.dept VALUES(40, 'OPERATIONS', 'BOSTON');

commit;

-- 创建员工表 并赋值
CREATE TABLE MYSCOTT.EMP(
    EMPNO           NUMBER(4) constraint emp_empno_pk PRIMARY KEY,
    ENAME              VARCHAR2(10) constraint emp_ename_notnull NOT NULL,
    JOB                VARCHAR2(9),
    MGR                NUMBER(4),
    HIREDATE           DATE,
    SAL                NUMBER(7,2) constraint emp_sal_check check (SAL>0),
    COMM               NUMBER(7,2),
    DEPTNO            NUMBER(2) constraint emp_deptno_fk references MYSCOTT.dept(deptno) 
)TABLESPACE ts_myscott;

--创建索引 在新的表空间上
CREATE  INDEX MYSCOTT.IX_CAtbAuditOperInfo_OT ON MYSCOTT.EMP(ENAME) TABLESPACE ts_myscott2;


INSERT INTO MYSCOTT.EMP VALUES(7369, 'SMITH', 'CLERK', 7902, '17-12月-80', 800, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '20-2月 -81', 1600, 300, 30);
INSERT INTO MYSCOTT.EMP VALUES(7521, 'WARD', 'SALESMAN', 7698, '22-2月 -81', 1250, 500, 30);

INSERT INTO MYSCOTT.EMP VALUES(7566, 'JONES', 'MANAGER', 7839, '02-4月 -81', 2975, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '28-9月 -81', 1250, 1400, 30);
INSERT INTO MYSCOTT.EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839, '01-5月 -81', 2850, NULL, 30);

INSERT INTO MYSCOTT.EMP VALUES(7782, 'CLARK', 'MANAGER', 7839, '09-6月 -81', 2450, NULL, 10);
INSERT INTO MYSCOTT.EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566, '19-4月 -87', 3000, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7839, 'KING', 'PRESIDENT', NULL, '17-11月-81', 5000, NULL, 10);

INSERT INTO MYSCOTT.EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698, '08-9月 -81', 1500, 0, 30);
INSERT INTO MYSCOTT.EMP VALUES(7876, 'ADAMS', 'CLERK', 7788, '23-5月 -87', 1100, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7900, 'JAMES', 'CLERK', 7698, '03-12月-81', 950, NULL, 30);

INSERT INTO MYSCOTT.EMP VALUES(7902, 'FORD', 'ANALYST', 7566, '03-12月-81', 3000, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7934, 'MILLER', 'CLERK', 7782, '23-1月 -82', 1300, NULL, 10);

commit;
    
-- 创建工资级别表 并赋值
CREATE TABLE MYSCOTT.SALGRADE(
     GRADE            NUMBER,
     LOSAL           NUMBER,
     HISAL           NUMBER
)TABLESPACE ts_myscott;

INSERT INTO MYSCOTT.SALGRADE VALUES(1, 700, 1200);
INSERT INTO MYSCOTT.SALGRADE VALUES(2, 1201, 1400);
INSERT INTO MYSCOTT.SALGRADE VALUES(3, 1401, 2000);
INSERT INTO MYSCOTT.SALGRADE VALUES(4, 2001, 3000);
INSERT INTO MYSCOTT.SALGRADE VALUES(5, 3001, 9999);
commit;

--创建奖金表
CREATE TABLE MYSCOTT.BONUS(
    ENAME      VARCHAR2(10),
     JOB       VARCHAR2(9),
     SAL          NUMBER,
     COMM      NUMBER
)TABLESPACE ts_myscott;

------停止-----

-- 04创建新用户方案     通过MYSCOTTUSER1来访问数据库, 权限配置演示  
CREATE USER "MYSCOTTUSER1"  PROFILE "DEFAULT" IDENTIFIED BY "123456" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "MYSCOTTUSER1";
GRANT SELECT ANY TABLE TO "MYSCOTTUSER1";

GRANT DELETE ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
GRANT INSERT ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
GRANT UPDATE ON MYSCOTT.DEPT TO "MYSCOTTUSER1";

GRANT DELETE ON MYSCOTT.EMP TO "MYSCOTTUSER1";
GRANT INSERT ON MYSCOTT.EMP TO "MYSCOTTUSER1";
GRANT UPDATE ON MYSCOTT.EMP TO "MYSCOTTUSER1";

commit;

oracle项目案例(Oracle客户端测试用).sql

注意：需要更改实际的安装目录！

练习：复制粘贴到“PL/SQL Developer”的“Command Window”的SQL命令中执行。

打开命令窗口，然后执行：sqlplus MYSCOTTUSER1/123456@orcl100（如果未配置orcl100，orcl100处改为ip）

SQL>select * from myscott.emp;

四、MySQL SQL语句

1、MySQL基础

瑞典MySQL AB公司开发，由SUN收购，而后SUN被甲骨文并购，目前属于Oracle公司

开源的，版本比较乱
Community社区版、Enterprise企业版

》下载压缩包（红帽），解压之后是rpm文件
需要先卸载之前的版本 rpm -aq | grep mysql rpm -aq | grep MySQL

》卸载命令
rpm -e mysql-connector-odbc-3.51.26r1127-1.el5 --nodeps --allmatches
注意：qt-mysql-4.6.2-25.el6.x86_64 不需要卸载

》解压之后的文件：
MySQL-client-advanced-5.6.20-1.el6.x86_64.rpm
MySQL-devel-advanced-5.6.20-1.el6.x86_64.rpm （头文件+库文件）
MySQL-server-advanced-5.6.20-1.el6.x86_64.rpm
MySQL-embedded-advanced-5.6.20-1.el6.x86_64.rpm
MySQL-shared-advanced-5.6.20-1.el6.x86_64.rpm
MySQL-shared-compat-advanced-5.6.20-1.el6.x86_64.rpm
MySQL-test-advanced-5.6.20-1.el6.x86_64.rpm
README.txt

》手动删除文件
#rm -f /etc/my.cnf
#rm -rf /var/lib/mysql
#rm -rf /var/share/mysql
#rm -rf /usr/bin/mysql*

》安装具体包，上面红的都需要安装
rpm -ivh MySQL-client-advanced-5.6.20-1.el6.x86_64.rpm
rpm -ivh MySQL-devel-advanced-5.6.20-1.el6.x86_64.rpm
rpm -ivh MySQL-server-advanced-5.6.20-1.el6.x86_64.rpm

默认初始一个root用户（数据库用户，非操作系统），密码存放在/root/.mysql_secret

》安装后登陆mysql

mysql -uroot -p123456
修改密码

set password=password('123');
退出 quit

》扩：ubuntu安装mysql
1）sudo apt-get install mysql-server
2）sudo apt-get install mysql-client
3）sudo apt-get install libmysqlclient-dev
提示：安装过程中会提示设置密码什么的，注意设置了不要忘了，安装完成之后可以使用如下命令来检查是否安装成功。

2、MySQL组合拳保证服务正常

》组合拳

○ 当mysql不能登陆的时候使用

○ 可以用root切换到mysql用户（su mysql），是一个虚拟用户，mysql 安装的时候初始化，在home找不到，ps -u mysql 可以查看进程（打开另一个终端，输入ps -u mysql 查看进程的PID），用kill杀掉进程（kill -9 PID）

○ service mysql start 重启mysql服务

3、MySQL库的操作

》mysql与oracle组织结构：

--查看所有数据库
mysql> show databases;

注意：上述4个库不要删除！

--创建数据库
create database mydb1;
--查看mydb1的创建方式
mysql> show create database mydb1;

注意：字符集默认是latin1 ，不支持中文。

--创建mydb2库，指定字符集为utf8
create database mydb2 character set utf8;

--修改mydb1的字符集为utf8
alter database mydb1 character set utf8;

--要求对输入数据进行检查，要求为utf8格式
create database mydb3 character set utf8 collate utf8_general_ci;

--删除数据库
mysql> drop database mydb3;
mysql> drop database mydb2;

4、MySQL表的操作

--创建表

mysql> create table t1(id number, name varchar2(30));

报错原因：未选库。建表需要先选库：
mysql> use mydb1

mysql> create table t1(id number, name varchar2(30));

报错原因：数据类型不识别。需要查看MySQL的数据类型：

create table t1(id int,name varchar(30));

--增加一个字段
alter table t1 add sal int;

alter table t1 add column comm float;

--修改字段属性
alter table t1 modify column name varchar(40);

--改名同时改类型
alter table t1 change comm comm2 double;

--删除列
alter table t1 drop column comm2;

》查看表的创建方式：

engine=InonoDB 是默认引擎，高速模式，支持事务，默认情况下不开启。所以，打开几个客户端，只要表结构或内容更改（无需commit），从其他客户端查看，表结构或内容已经更改。

》表名大小写敏感

》字段不区分大小写

》库名大小写敏感

》重命名表，需要加table关键字（注意和Oracle的区别！）
rename table t1 to t2;

》mysql不支持回收站，直接删除，不能加purge

--删除表
drop table t2 ;

》查看当前库下有哪些表
mysql> show tables;

5、MySQL数据的操作

--创建表

create table employee(id int,
        name varchar(20),
        sex int,
        birthday date,
        salary double,
        entry_date date,
        resume text);

注意：两个字段中间不能使用Tab键，只能使用空格！（使用Tab键-创建employee2显示如下：）

（两张表的对比如下）

所以，使用Tab键创建的表的字段乱套，而且数据乱套！

--插入数据
insert into employee values(1,'叶开',1,'1983-04-27',15000,'2012-06-24','一个大牛');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'傅红雪',1,'1984-02-22',10000,'2012-07-24','一个中牛');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'陆小佳',0,'1985-08-28',7000,'2012-08-24','一个小虾');

--更改数据

update employee set resume='一个小牛' where id=3;

--删除数据

delete from employee where name='陆小佳';

（打开另一个客户端，登录，选择数据库（use mydb1）查看表（show tables）（select * from employee;），发现MySQL自动提交！）

6、MySQL组函数相关

》创建student表，练习：

create table student(id int,name varchar(20),chinese int,english int,math int);
insert into student(id,name,chinese,english,math) values(1, '黄真',80,85,90);
insert into student(id,name,chinese,english,math) values(2,'归辛树',90,95,95);
insert into student(id,name,chinese,english,math) values(3,'李寻欢',80,96,96);
insert into student(id,name,chinese,english,math) values(4,'叶开',81,97,85);
insert into student(id,name,chinese,english,math) values(5,'袁承志',85,84,90);
insert into student(id,name,chinese,english,math) values(6,'何红药',92,85,87);
insert into student(id,name,chinese,english,math) values(7,'何铁手',75,81,80);
insert into student(id,name,chinese,english,math) values(8,'夏雪宜',77,80,79);
insert into student(id,name,chinese,english,math) values(9,'任我行',95,85,85);
insert into student(id,name,chinese,english,math) values(10,'岳不群',94,85,84);

1）统计列数

select count(*) from student;

2）给学生表增加一列，class_id（班级） int类型

alter table student add class_id int;

3）将学生id为1-5的修改为1班，将学生id为6-10的修改为2班—一个语句修改完成。

update student set class_id=ceil(id/5);

4）求各个班英语的平均分

select avg(english),class_id from student group by class_id;

——注意：mysql group 语法检查不严格，仍然按照oracle的记忆
mysql> select avg(english),class_id,id from student group by class_id;

5）求各个班的总成绩

select sum(chinese+math+english),class_id from student group by class_id;

6）求总成绩大于1300的班级

select sum(chinese+math+english),class_id from student group by class_id having sum(chinese+math+english)>1300;

7、MySQL日期函数、字符函数、数学相关函数

》日期函数：

mysql> select 3+5*20 from dual;

--查看当前时间

mysql> select now() from dual;

--求昨天，今天，明天（注意与Oracle中的日期加减操作有所不同。）
mysql> select now()-1,now(),now()+1 from dual;
mysql> select date_add(now(),interval -1 day),now(),date_add(now(),interval 1 day) from dual;

--求明年的今天
mysql> select date_add(now(),interval 1 year) from dual;

--加11s
mysql> select addtime(now(),11),now() from dual;

mysql> select addtime(now(),'0:1:0'),now() from dual;

》字符函数：

--字符串拼接

Oracle的 || 在MySQL无法使用

select concat('hello ' , 'mysql ') from dual;
mysql> select concat('hello ' , 'mysql ','haha','hehe') from dual;

--utf字符串，汉字占3个字节
mysql> select length('hello中国') from dual;

》数学相关函数：

--进制转换

mysql> select conv(10,10,2),conv(10,10,16) from dual;

8、MySQL转换函数

日期转字符串：在MySQL中没有to_date函数，进行日期转换需使用date_format()来代替。

》mysql日期格式
select date_format('1982-11-17','%Y-%m-%d') from dual;（无效）

select date_format('1982-01-17','%Y-%c-%d') from dual;

select str_to_date('2013-6-04 05:14:15' , '%Y-%c-%d %h:%i:%s') from dual;

select addtime(str_to_date('2013-6-04 05:14:15' , '%Y-%c-%d %h:%i:%s'),'0:1:0') from dual;

9、MySQL多表查询

（1）多表查询准备

》文件准备：scott_data.sql（放入/home/oracle/sql/）

create database if not exists scott character set utf8;

use scott;

create table bonus
(
  ename VARCHAR(10),
  job   VARCHAR(9),
  sal   int,
  comm  int
);

create table dept
(
  deptno int not null,
  dname  VARCHAR(14),
  loc    VARCHAR(13)
);
alter table dept
  add constraint PK_DEPT primary key (deptno);

create table emp
(
  empno    int not null,
  ename    VARCHAR(10),
  job      VARCHAR(9),
  mgr      int,
  hiredate DATE,
  sal      int,
  comm     int,
  deptno   int
)
;
alter table emp
  add constraint PK_EMP primary key (EMPNO);
alter table emp
  add constraint FK_DEPTNO foreign key (DEPTNO)
  references DEPT (DEPTNO);


create table salgrade
(
  grade int,
  losal int,
  hisal int
)
;


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20),
(7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal)
values (1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);

scott_data.sql

数据导入

mysql>source /home/oracle/sql/scott_data.sql（警告的原因是38行DEPT大写，不过不影响！）

mysql>show tables;

mysql>edit

（然后输入下列SQL语句）

select e.empno,e.ename,e.deptno,d.dname  
from dept d,emp e 
where e.deptno=d.deptno;

可以显示：：员工号，员工姓名，部门编号，部门名称

发现上述写法与oracle没有区别！

（2）MySQL内连接

sql99的标准，内连接写法，口诀：
1）“，”替换-> inner join
2） where 替换--> on

select e.empno,e.ename,e.deptno,d.dname 
 from dept d inner join emp e 
on e.deptno=d.deptno;

--显示：员工号，姓名，薪水，薪水级别

select e.empno,e.ename,e.sal,s.grade  
from emp e,salgrade s 
where e.sal >= s.losal   and e.sal <= s.hisal

（3）MySQL外连接

--显示：部门编号，部门名称，人数

select d.deptno,d.dname,count(e.empno) 
 from emp e,dept d 
where e.deptno = d.deptno 
group by d.deptno,d.dname；

上述结果仍然缺少40部门

sql99不支持（+）连接写法！

》右外连接写法，口诀：

1）"," 替换-->right outer join
2） where 替换-> on

select d.deptno,d.dname,count(e.empno) 
 from emp e right outer join  dept d 
on e.deptno = d.deptno 
group by d.deptno,d.dname;

》左外连接写法，口诀：
1） "," -->left outer join
2）where -> on

select d.deptno,d.dname,count(e.empno)  
from dept d left outer join emp e 
on e.deptno = d.deptno 
group by d.deptno,d.dname

注意：表的顺序也要改变！

》inner,outer可以省略！

》xx'boss is xx

select concat(e.ename,'''s boss is ',b.ename)  
 from emp e,emp b  
where e.mgr = b.empno;

上述结果缺少大老板？

select concat(e.ename,'''s boss is ',b.ename)   
from emp e left join  emp b  
on e.mgr = b.empno

大老板是null

select concat(e.ename,'''s boss is ',nvl(b.ename,'his wife'))  
 from emp e left join  emp b  
on e.mgr = b.empn

报错：ERROR 1305 (42000): FUNCTION scott.nvl does not exist

select concat(e.ename,'''s boss is ',ifnull(b.ename,'his wife'))  
 from emp e left join  emp b  
on e.mgr = b.empno

10、MySQL补充

（1）也可以这样查询
select * from emp \g
select * from emp \G

（2）取某几名

》取前3名
select * from emp order by sal desc limit 3;

》取5-8名 limit后边的数字含义：m代表跳过的记录数，n代表取的记录数
select * from emp order by sal desc limit m,n;
select * from emp order by sal desc limit 4,4;

》帮助window和linux之间传输文件

1）扩展：zmodem使用方法

无论有xshell还是secureCRT连接linux的时。
默认都用一个zmodem可以帮助window和linux之间传输文件
很方便和实用的工具。
不过默认是无法使用的需要安装lrzsz软件
直接云安装就可以了 yum install lrzsz
安装完之后，如果windows要给linux传文件。只需要点住文件，拖动到xshen窗口即可传输
如果linux要给window传文件，那就直接 sz filename 后
window就会自动弹出文件接收存放地址的窗口。

2）扩展：UltraEdit使用方法

1）点击“文件”，选择“FTP/Telnet”—>点击“账号管理”，打开“FTP/SFTP账号管理器”，然后输入账号（如：redhat6.5）协议选择“SFTP”，服务器输入ip（如：192.168.5.100），端口（默认：22），用户名称（oracle），密码（oracle），勾选“保存密码”，然后点击“应用”

2）打开文件：在左侧“文件视图”中“资源管理器”可以打开文件。

3）点击“文件”，选择“FTP/Telnet”—>点击“浏览”，打开“UltraEdit FTP浏览器”，在账号后选择“redhat6.5”，点击后边“连接”，然后即可看到左侧是本地文件，右侧为服务器端文件。分别打开要传输的目录，然后点击“→”即可发送文件。

在学习数据库编程总结了笔记，并分享出来。有问题请及时联系博主：Alliswell_WP，转载请注明出处。

posted on 2020-07-18 22:39 Alliswell_WP 阅读(239) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

数据库编程——Oracle MySQL SQL

导航

公告