数据库学习笔记以及基本语法总结（一）

数据库基本原理

原始数据：文件、表格
                  问题：丢失、数据类型不好分类、不好查找
                  存储数据容量有限制
数据库：存数据
              本质：软件，特定格式文件，excel
数据类型：列：字段 field 属性
                  行：数据

关系型数据库：
    Oracle         - 甲骨文       
    MySql          - 甲骨文      
    SqlServer    - 微软
    DB2             - IBM

非关系型数据库：NoSQL    
远程连接：Linux - 命令
                 1.连接到Oracle安装所在电脑 - 服务器
                 2.Oracle官方提供客户端 sqlplus 连接数据库

SQlDeveloper - Oracle官方提供可视化连接工具 - sqlplus
Navicat - 第三方可视化连接工具
         部分sql语句的兼容性不一样
         工具 - 选项 - 环境 - oci 
         可视化设计表 - DDL
         可视化管理数据 - DML
         导出SQL语句 

SID - 连接Oracle数据使用的唯一标示。orcl
port - 1521 

SQL语句：99标准     database     DBA
    DDL：数据定义语言 - 表
         create    drop truncate rename alter
    DML：数据操纵语言 - 数据 DQL
         配合TCL
         insert update delete
    DCL：数据控制语言 - 数据库用户、权限 - 不讲
         create user 、grant
    TCL：事务控制语言 - 事务 - 后面讲
         commit rollback savepoint
    DQL：数据查询语言
         select

 

数据库基础语法

Oracle 数据类型：
    number(2)    整数
    number(5,2)    小数, 999.99
    char(20)    字符\字符串 - 定长 20。 lucy+16个空格
    varchar     不用
    varchar2(20)字符\字符串 - 最大长度20.lucy - 4
    date         时间 - 年月日
    timestamp    时间戳 - 时分秒
    datetime

 

-- 创建表
CREATE table user_bao (
    -- 字段 类型
    id number(6),
    name varchar2(20),
    className varchar2(20),
    score number(5, 1)
);

-- 删表 不可逆
drop table user_bao;
-- user_bao 查看表结构
desc user_bonnie;

-- 修改表结构 
-- 添加字段 出生日期
alter table user_bao ADD (
	-- 建表写法一样
	birthday date
);
alter table user_bonnie ADD (
	-- 建表写法一样
	score number(4,1)
);
-- 删除字段
alter table user_bao drop (score);
-- 修改字段 修改类型，不能修改字段名
alter table student.user_bao MODIFY (
	className varchar2(10) not null
);   	
-- 默认值是java1906
alter table student.user_bonnie MODIFY (
	className varchar2(10) default 'java1906'
);
-- 修改表名
rename user_bao TO user_bonnie;
-- 删除表中所有数据 不可逆
truncate table user_bonnie;

-- DML：数据操纵语言
-- 插入数据
insert into user_bonnie values
	(1, 'rose', 'java1906', sysdate, 92.3); -- 值的个数和顺序必须按照表中定义
-- 即使设置默认值，也不能主动添加null值
insert into user_bonnie values
	(null, 'lucy', null, sysdate, 92.3);

-- 指定部分字段添加值, 值的顺序按照指定顺序添加
insert into user_bonnie(name, BIRTHDAY, score) 
	values ('rose', sysdate, 93);
-- 批量插入数据语法，oracle不支持
insert into user_bonnie(name, BIRTHDAY, score) 
	values ('jack', sysdate, 92.3),
	('jack', sysdate, 92.3),
	('jack', sysdate, 92.3),
	('jack', sysdate, 92.3);
-- 修改指定表中数据
-- 案例：修改班级为java1907
update user_bonnie set classname = 'java1907';
-- 同时修改多个字段
update user_bonnie 
	set classname = 'java1907',
			score = 100;
-- 筛选：where 条件 所有字段都可以作为条件
update user_bonnie set classname = 'java1906'
	where name = 'lucy';

-- 删除指定表中所有数据
delete from user_bonnie;
-- 条件删除
delete from user_bonnie where id = 1;

/** TCL 事务管理 rollback - 回滚
 * delete 和 truncate 区别：
 * truncate属于DDL，不可逆
 * delete属于DML，可逆
 */
-- 查询指定表中所有数据
select * from user_bonnie; -- 后面重点讲的

-- TCL事务管理 commit rollback savepoint
-- 只有DML需要配合TCL来使用，TCL是让DML对于数据的操作生效或者撤销
-- 不同的会话 - 连接，都有单独的事务，如果DML没有进行事务提交，操作是不生效
-- 提交事务 - 当前会话的DML操作生效
commit;

-- 回滚事务 - 当前会话的DML操作从上一次提交后的地方撤销
rollback;

-- 一次事务中，可以执行多次SQL语句
insert into user_bonnie(id) values(2);
insert into user_bonnie(id) values(3);
update user_bonnie set score = 90;

-- 保存一个事务节点，事务没有结束
savepoint a;
update user_bonnie set name = 'allen';

-- 回滚到指定保存的节点
rollback to a;
rollback;
-- 事务结束的途径：提交、回滚、关闭连接

查询语言与相关函数

-- || 字符串拼接，多次拼接
select 'ha' || 'hei' || 'heng' from dual;
-- concat只能传2个参数
select concat('ha','hei') from dual;
-- 函数可以嵌套
select concat(concat('ha','hei'),'heng') from dual;
-- 字符串长度
select length('lucy') from dual;
-- 字符串补齐  StringUtils
select lpad('haha', 10, '*') from dual;
select rpad('haha', 10, '*') from dual;
-- 字符串切割 指定字符串 从第n个字符开始(n从1开始数) 取m个字符
select substr('hi! how are u! i am fine.', 3, 11) from dual;
-- n可以为负数，意味着倒着数第n个字符开始
select substr('hi! how are u! i am fine.', -3, 11) from dual;

-- 全变大写、小写、首字母变大写
select LOWER('rOse') from dual;
select Upper('rOse') from dual;
select initcap('rOse') from dual;

SElect * from USER_bonnie 
    where upper(name)='ROSE';

-- trim 截取, 去除左右两边指定的字符串
select trim('h' from 'hhhi! how are u! i am fine.hhh') from dual;
select ltrim('hhhi! how are u! i am fine.hhh', 'h') from dual;
select rtrim('hhhi! how are u! i am fine.hhh', 'h') from dual;

select rtrim('    i! how are u! i am fine.hhh    ') from dual;

-- indexOf 功能 , 不包含子串，返回0
select instr('hi! how are u! i am fine.', 'i') from dual;
select instr('hi! how are u! i am fine.', 'i', 10) from dual;
-- 指定位置为负数，倒着数
select instr('hi! how are u! i am fine.', 'i', -5) from dual;

-- 数字有关函数 Math.round ceil floor mod
-- 四舍五入，可以保留小数点，也可以从整数部分四舍五入
select round(44.56789, 0) from dual;
select round(44.56789, 2) from dual;
select round(46.56789, -1) from dual;

-- 向上、向下取整，只有一个参数
select ceil(44.56789) from dual;
select floor(44.56789) from dual;
-- 截取，直接舍弃
select TRUNC(15.79, 1) FROM DUAL;
select TRUNC(15.79, -1) FROM DUAL;
-- 取余数 取模
select mod(10, 3) from dual;

-- 时间 date timestamp
select sysdate from dual; -- 2019-07-17
select systimestamp from dual;


select * from user_bonnie;
insert into user_bonnie (birthday)
    values('20-NOV-20');

insert into user_bonnie (birthday)
    values(to_date('2019-02-28', 'yyyy-mm-dd'));

-- 变成日期
select to_date('2019-02-28', 'yyyy-mm-dd') from dual;  
-- 变成字符串
select to_char(sysdate, 'yyyy-MM-dd') from dual;
select to_char(systimestamp, 'yyyy"年"MM"月"dd"日" hh24:mi:ss day') from dual;
-- 所在日期当月最后一天
select last_day(sysdate) from dual;
-- 提取 Calendar.get() day month year
select extract(day from sysdate)from dual;
-- 在指定时间上添加月份
select add_months(sysdate, -2) from dual;

select months_between(
    to_date('2019-07-17', 'yyyy-mm-dd'),
    to_date('2019-02-28', 'yyyy-mm-dd')
) from dual;

select round(months_between(
    to_date('2019-07-17', 'yyyy-mm-dd'),
    to_date('2019-02-28', 'yyyy-mm-dd')
)) from dual;