SQL数据库 数据类型和基本命令

Database
用数据结构来组织、管理数据的存储仓库
数据库管理软件(DBMS)：Database Managerment system
用于管理数据的软件
关系型数据库：Oracle、MySQL、db2、sql server、SQLit 非关系型数据库：mangodb
关系 是一个数据的概念。关系型数据是二维表(table)的形式来存储数据
表头：每一列称为一个字段(Field) ； 每一行称为一条记录(Record) 。由行(row)和列(column)构成的表称为二维表

SQL：structured query language 结构化查询语言

DDL：data definition language 数据定义语句
TCL：transaction control language 事务控制语句
DML：data manipulation language 数据操作语句
DQL：data query language 数据查询语句
DCL：data control language 数据控制语句

Oracle数据类型
1、字符串 char(n) ，varchar2(n)。 字符串用单引号’a’ ‘zff’ ，n和定义数值长度是一样的
char定长字符串，char(40)会自动补齐为40个字节长度。汉字GBK是2字节，utf-8是2到4个字节。n省略默认为1，最大取值为2000
varchar2可变长字符串，name varchar2(40) 'jack’只占4字节。n不可以省略，最大取值为4000。
long 最多可以2G的长度；clob最多可以4G的长度。
long的限制：1.一个表中最多有一个long字段。2.long字段不能作为主键。3.long字段不能建索引。4.long字段不能使用在where、group by、distinct中
2、数值 number(p,s)。 p表示总位数，s表示小数有几位，十进制位数。p最大取值38。p默认38。
3、时间 date ，timestamp
date有7个字节： 世纪，年，月，日，时，分，秒
timestamp有8到11个字节，前面7个字节和date一样，后面表示毫秒数
系统时间 select sysdate,systimestamp from table_name;

注意：sql语句中不区分大小写
DDL：数据定义语句 = 创建、删除、修改数据对象(二维表)
1.建表 CREATE TABLE table_name(col_name col_type,…); 名字放前面，类型放后面
create table stuinfo_zf(name varchar2(40),age number(2),gender varchar2(4),score number(3));
2.查看表结构 DESC table_name
desc stuinfo_zf
3.删除表结构 DROP TABLE table_name 删除表结构连同表数据一起删除
4.修改表结构 ALTER TABLE
（1）增加字段 ALTER TABLE table_name ADD col_name col_type; (col_name1 col_type1,col_name2 col_type2)同时增加多个字段要加括号
（2）删除字段 ALTER TABLE table_name DROP (col_name)；或者COLUMN col_name
（3）修改字段名 ALTER TABLE table_name RENAME COLUMN col_name1 TO col_name2 ; //name1变成name2
（4）修改字段类型ALTER TABLE table_name MODIFY col_name col_type; //name变成type类型
（5）修改表名 RENAME table_name1 TO table_name2; // name1改成name2
5.清空表数据 TRUNCATE TABLE table_name
6.快速建表 复制表结构和表数据 CREATE TABLE table_name AS select * from table_name2;
只要执行就会影响到数据库，不需要提交，不能回退

DML：数据操作语句 = 插入、删除、修改表对象中的数据
1.插入 在表对象中产生一条新的记录(record)
INSERT INTO table_name(col_name,…) VALUES(val,…); //往表中插入value。val和name要一一对应，类型一致
INSERT INTO table_name VALUES(col_type,…);//不写类型，顺序必须和建表顺序一致
没有插入值的字段值为null，除非该字段有默认值则有默认值
2.删除
DELETE table_name [where_condition]; //如果没有where_condition是全表删除
delete stuinfo_zff where id=110; //删除id为110的记录
3.修改
UPDATE table_name SET col_name,… [where_condition]; //没有where_condition是全表更新
update stuinfo_zff set ename=‘新名字’ where empno=7935;//修改多个字段用逗号，
查看数据 SELECT * FROM table_name;
DML语句在未提交(commit)之前都可以回退(rollback)，提交之后影响到数据库。
delete 和 truncate 的区别
1、delete是DML语句；truncate是DDL语句
2、delete删除的数据可以回退；truncate删除的数据不可以
3、delete可以选择性地删除数据；truncate只能全表删除
4、delete删除效率比较慢，是一条一条删除；truncate直接截断，效率高

TCL：事务控制语句 = 事务 DML
提交事务：commit; 一次性提交所有事务
回退事务：rollback; 回退所有未提交的事务
设置保存点(回退点)：SAVEPOINT p1; rollback to p1;
事务的性质：原子性：要么全表执行成功，要么全部不执行
一致性：
隔离性：数据库为每一个用户开启事务，不能被其他事务操作干扰
持久性：当事务正确完成之后对数据的改变是永久性的

DCL：数据控制语句 = 创建数据库用户、给用户赋予权限、回收用户权限
CREATE USER
CRANT TO
REVOKE FROM

DQL：数据查询语句
SELECT 重点难点
求字符串中字符的个数：select length(col_name),length(name2) from table_name;
（c/c++中的strlen求的是字节数，一个中文不止一个字节） char(10) insert ‘中文’ ，length有2个字符并补6个字符，共8个字符
拼接字符串：select concat(‘hello’,‘heleasta’) from table_name;
select ‘字符串’||‘字符串’||‘字符串’ from table_name; //||是字符串连接运算符，可以无限连接
单引号是特殊字符’，要在字符串里输出单引号要’’。
3、select lower(‘全变小写’),upper(‘全变大写’),initcap(‘单词首字母大写其余小写’);
4、select trim(c from s) from dual; 去除字符串s左右两边所有的c
select ltrim(str,s); 去除str左边重复的s串
select rtrim(str,s);
5、lpad(str,s,n); 用s字符串添加在str左边使字符串最终有n个值
rpad
如果n小于str本身长度则截取前面n个字符
6、instr 查找子串在字符串中的位置 select instr(‘被查找的字符串’,‘要查找的子串’) from table_name; //返回位置，从1开始。没有找到则返回0
select instr(‘被查找的字符串’,‘要查找的子串’,从第几个字符开始(为负则从后往前找),找第几个子串) from table_name; //后两个值默认是1
7、substr 截取子串 select substr(‘被截取的字符串’,从第几个字符开始截取(可以是负数),[取几个字符]) from table_name;
8、replace 替换子串 select replace(‘字符串’,‘要替换的字符串’,‘替换成的字符串’) from table_name;
9、translate 翻译字符 select translate(‘字符串’,‘erl’,‘ERL’) from table_name; //把字符 替换成 对应位置的字符
number数值处理函数
1、round 四舍五入 select round(num,n) from table_name; //如果n>0表示四舍五入到小数点后第n位，n<0表示四舍五入到小数点第|n|+1位。默认是0。
2、trunc 舍弃 select trunc(num,n) from table_name; //参数和round一样
3、mod 求余 select mod(num,n) //mun%n
4、ceil 向上取整 floor 向下取整 。select ceil(9.5),floor(9.5)
5、greatest 取最大值 least 取最小值 select greatest(1,2,3,-1,-2) from table_name;//只要里面值的类型相同就行
date时间处理函数
to_date(str,fmt) 把字符串按格式字符串转换成日期
to_char(date,fmt) 把时间格式化为字符串
fmt：yyyy四位年，yy两位年，MM月份，dd日，HH24二十四小时制的小时，hh十二小时制的小时，mi分钟，ss秒，FF…几位毫秒几个F。不区分大小写。
insert int testdate(d) values(to_date(‘2019.9.25’,‘yyyy.mm.dd’)); //插入一条时间类型
fmt：9表示一位数值，0显示前导0和小数点后无用的0，.小数点，$货币符号，L本地货币符号
select to_char(99877,‘99999’) //数字转化为字符串
select to_number(‘3997.52’,‘9999,99’) //字符串转化为数字

默认输入输出时间 25-9月-2000
时间加减一个整数相当于加减这个值的天数 sysdate-10 。两个时间相减得到相差的天数。
add_months(日期,加几个月) 日期加上一个月份，能为负
months_between(日期，日期) 两个日期相差多少月。
next_day(日期,下一个星期几) 得到 下一个星期几 的日期，取值1到7，1表示星期日，2表示星期一
last_day(d) 当前月份的最后一天
extract(year|month|day from 日期） fmt里加中文用双引号""

（1）(null)和任何值运算都将得到null
（2）任何字段的取值都可以为null
（3）插入时没有指定字段的值为null
默认值 default val
创建表时 creat table tab_name(col_name col_type default val,…)
空值处理函数
nvl(expr,expr1) 如果expr的表达式为null则结果为expr1，不为null则为expr。相当于expr!=null？expr：expr1
nvl2(expr,expr1,expr2) 如果expr为null则结果为expr2，不为null则expr1。 expr!=null？expr1：expr2

SELECT 按照条件来获得数据
select 字段名,… from 表名,…
select * from table_name; --全表查询 显示所有字段。*表示所有字段。效率慢。
select sal12 “year_sal” from emp; 给字段起别名，加"“变小写,”"保留字符串原样
FROM 表名；
where语句过滤数据
select 字段,… from 表名 where 条件; --判断用等式和不等式比较 =,!=,>,< ，表示区间 between 1000 and 2000;–[1000,2000]
模糊匹配 LIKE 。where ename like ‘K’; --开头为K的。where ename like ‘%K%’ ;–名字里有K的。where ename like ‘%K_’ --倒数第二个字符为K的
% 代表任意多个任意字符
_ 代表任意一个字符
where ename like ‘%_%’ escape ‘’; --把\看做转义字符，可以查询_字符。
多个条件 AND OR
select * from emp where deptno=20 and job=‘manage’;
连续空间 BETWEEN v1 and v2; 相当于[v1,v2]
条件取反 NOT ，where enamel not like ‘字符’; not between 1000 and 2000;
离散的值 IN where sal in(1000,1500,1800); --sal为in里面的值。 not in() 不在这里面的 。null不能用=和!=null比较 In()里面是 = or；not in()里面是 != and。
x != null 和 x=null 一直是false。
空值 IS NULL,IS NOT NULL 判断是否为null
条件中可以使用函数。 nvl(com,0);
ALL,ANY 。>all(…) 大于最大值，>any(…) 大于最小值， <all(…) 小于最小值，<any(…) 小于最大值，
去除重复记录 distinct 。select distinct 字段,… --有多个字段时，组合去重，所有字段都相等才认为是重复的