[大数据]mysql学习总结，与oracle的差别

关系型数据库有：mysql，oracle, db2, sql server, sqlite(嵌入式，手机端)等

非关系型数据有：redis, hbase, mongodbdeng等

mysql在linux里登入方式为????????mysql -uroot -p123456

查看mysql的版本? ? ? ? mysql -V

sql分类：

????????数据定义语言：DDL? ? ? ? 关键字有DROP, CREATE, ALTER

? ? ? ? 数据操纵语言：DML? ? ? ? 关键字有insert， update， delete

? ? ? ? 数据查询语言：DQL? ? ? ? 关键字有select， from， where等

常用数据类型：

汉字在不用编码格式和系统下，一般占用2到3字节

创建数据库

注意：mysql的数据库名，表名区分大小写，列名与关键字则不区分，与ORACLE有点不同

create database 数据库名；#存在会报错

create database if not exists bigdata_db;? ? ? ? #如果数据库存在则不创建

create database 数据库名 character set 字符集；? ? ? ? #创建数据库时设置字符集

查看mysql所有数据库

show databases;

删除数据库

DROP database bigdata_db;

使用/切换数据库

use digdata_db;

查看正在使用的数据库

select database();

创建表

create table if not exists stu2(
cid varchar(20) primary key,????????#分类id为主键

cname varchar(50)            #分类名称

);? #建表时添加主键

create table stu1 as select age ,count(1) cn from stu group by age;? ? ? ? #直接把结果输出为一张表

查看表结构

desc 表名;

删除表

drop table 表名;

修改表结构

alter table 表名 add '列名' varchar(20);? ? ? ? #添加字段

alter table 表名 change 原列名?新列名 varchar(30);? ? ? ? #修改字段及类型

alter table 表名 drop?列名;? ? ? ? #删除列名

rename table 表名 to 新表名;? ? ? ? #修改表名

insert into用法

insert into stu(id,name)values(1,'张三');? ? ? ? #values可以不加s，单引号/双引号都行

insert into stu values(1,'李四',2);? ? ? ? #需把所有字段的value值都写上，并且顺序需要一一对应

insert into stu values(1,'李四',2),(1,'王五',2);? ? ? ? #一次插入多条数据

insert into stu1 select age, count(1) cn?from stu where sex='男' group by age;? ? ? ? #插入查询的结果集

update用法

update 表名 set?sal=200，name='youyou'? where id=1;? ? ? ? #建议加上where条件

delete from stu where id=1;? ? ? ? #建议加上where条件

truncate 表名;? ? ? ? #截断表，先删除表再重建，慎用

添加及删除主键约束

create table sut(id int primary key,name varchar(20));? ? ? ? ?or

create table sut(id int, name varchar(20),sal int,constraint pk_stu primary key(id,name));? ? ? ? or

alter table sut add primary key(id);

alter table stu drop primary key;

添加自动增长的列

create table stu(id int primary key atuo_increment,? ? ? ? #主键及自动增长

name varchar(20));

当要插入数据时，可以不为id设置值，或为null都行

INSERT INTO stu4(NAME) VALUES ('zhangsan');

INSERT INTO stu4(id,NAME) VALUES (null,'zhangsan');

修改自动增长值，默认值为1

ALTER TABLE stu AUTO_INCREMENT=100；

非空约束

create table sut(id int not null,name varchar(20));? ? ? ? ?or

ALTER?table?sut?modify?name?varchar(100)?not?null;

注意：mysql的‘’空值!=null,而oracle的‘’空值就是null，这是2者的差别

唯一约束

create table stu(id int unique,name varchar(20));? ? ? ? or

alter table stu6 modify name varchar(20) unique ;

外键约束

alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

alter table 从表 drop foreign key 外键名称

注意：添加外键约束后，插入数据应先插入主表，删除数据要先删除从表，否则会报错

条件查询

聚合函数

limit用法

select * from stu limit 5;? ? ? ? #显示查询的5条数据

select * from stu limit 0,5? ? ? ? #第1条数据开始，每页显示5条

注：oracle里没有这个函数，可以用伪例rownum代替

关联方式

inner join? ? ? ? ?内连接

left join? ? ? ? 左连接

right join? ? ? ? 右连接

full join? ? ? ? 全关联

问题：where和on的区别

where是过滤条件，on是关联条件，使用on查询速度更快

子查询

子查询可以作为（查询条件，查询结果，表等）

select * from stu where id=(select pid from where stu1);? ? ? ? #查询条件

select * from stu t2 inner join (select * from stu1 where id in(1,2,3))t1 on t2.id=t1.id;? ? ? ? #表

select * from stu where id in (select? id from stu1 where sex=‘男’ and age=‘18’);? ? ? ? #查询结果

索引

create table t_dept(
? ? no int not null primary key,
? ? name varchar(20) null,
? ? sex varchar(2) null,
? ? info varchar(20) null,
? ? index index_no(no)
? );????????#创建普通索引

create table t_dept(
? ?no int not null primary key,
? ?name varchar(20) null,
? ?sex varchar(2) null,
? ?info varchar(20) null,
? ?unique index index_no(no)
?);? ? ? ? #创建唯一索引

create index index_name on stu(name);????????#创建普通索引

create unique index index_name on stu(name);? ? ? ? #创建唯一索引

alter table stu4 add?index index_id(id);? ? ? ? #添加普通索引

alter table stu4 add unique index index_id(id);? ? ? ? #添加唯一索引

show index from stu;? ? ? ? #查看表中所有索引

drop index index_name on table_name;? ? ? ? #删除索引

alter table table_name drop index index_name;? ? ? ? #删除索引

开窗函数/分析函数

row()number over (partition by 列名 order by 列名);? ? ? ? #1,2,3,4,5

rank() over (partition by 列名 order by 列名);? ? ? ? #1,2,2,4,5

dense_rank() over (partition by 列名 order by 列名);? ? ? ? #1,2,2,3,4

注：partition by 和order by 后面可以接多个字段

问：having 和where的差别

where是分组之前对数据过滤，having一般是对分组之后的数据进行第二次过滤；

接下来讲下oracle，在mysql里大部分都有效

数字函数

ABS(x) ? ? ? ?x绝对值 ?ABS(-3)=3
MOD(x,y) ? ? ?x除以y的余数 ?MOD(8,3)=2
POWER(x,y) ? ?x的y次幂 ?POWER(2,3)=8
ROUND(x[,y]) ?x在第y位四舍五入 ?ROUND(3.456,2)=3.46
TRUNC(x[,y]) ?x在第y位截断 ?TRUNC(3.456,2)=3.45

TRUNC ?截断

---第二个参数是正数的时候，就在小数点后做截断
---第二个参数是负数的时候，就在小数点前做截断
SELECT ?TRUNC(1234.456,1) FROM DUAL ?---1234.4
SELECT ?TRUNC(1234.456,2) FROM DUAL ?---1234.45
SELECT ?TRUNC(1236.456,-1) FROM DUAL ?---1230
SELECT ?TRUNC(1234.456,-2) FROM DUAL --1200
SELECT ?TRUNC(1234.456,-3) FROM DUAL --1000
SELECT ?TRUNC(1234.456,-4) FROM DUAL --0

?||拼接函数

CONCAT(x,y)?? ?连接字符串x和y。

WM_CONCAT(参数字段)函数?

select WM_CONCAT(id,name) from emp;?? 将每一组里边每一行字段的数据都写在一起

DISTINCT 去重

select distinct id from emp;·? ? ? ? #去除重复数据

LENGTH函数?

SELECT LENGTH('列名') FROM DUAL;? ? ? ? #查看长度

SELECT LENGTHB('长度') FROM DUAL;? ? ? ? #两个中文占 4个字节

instr函数

SELECT INSTR('DGSJIA','S',1,1) FROM DUAL; --3? ? ? ? 查看出现在第几位，可以和where 字段 >1一起使用，查看是否在某个字段中出现

REPLACE ?替换函数

SELECT REPLACE(字符串,旧的值,新的值) FROM DUAL;

大小写转换函数

LOWER(x)?? ?x转换为小写。
UPPER(x)?? ?x转换为大写。

SELECT LOWER(ENAME) FROM EMP; ????????--将员工的名字转化为小写

截取函数

LTRIM(x[,trim_str])?? ?把x的左边截去trim_str字符串，缺省截去空格。
RTRIM(x[,trim_str])?? ?把x的右边截去trim_str字符串，缺省截去空格。
TRIM([trim_str FROM] x)?? ?把x的两边截去trim_str字符串，缺省截去空格。

LTRIM(参数1,参数2) 把参数1 的左边截去 参数2 字符串，参数2 不写的时候默认截去空格。

SELECT LTRIM('ABCDEFG','ABC') FROM DUAL; ?---截去 ABCDEFG 的左边的 ABC

SUBSTR函数

SUBSTR(x,start,length) ?返回x的字串，从staart处开始，截取length个字符，

缺省length，默认到结尾。
SUBSTR(参数1，参数2，参数3)
注意：截取都是从左往右截取
参数1 从哪个字符串中截
参数2 从这个字符串的哪个位置截取,如果参数2为负数，就是从倒数第几个位置开始截取
参数3 截取的长度

SELECT SUBSTR('DDSIADF',1,4)---从第一个位置开始截取，截取四个长度
FROM DUAL;

时间函数

TO_DATE

SELECT TO_DATE('20210722','YYYYMMDD') FROM DUAL;

SELECT TO_DATE('2021-07-22','YYYY/MM/DD') FROM DUAL;

SELECT TO_DATE('20210722152401','YYYYMMDD HH24MISS') FROM DUAL;

TO_CHAR

SELECT TO_CHAR(SYSDATE,'YYYYMMDD') FROM DUAL;--字符类型 '20210906'
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL; ---2021 年
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL; ---09 月
SELECT TO_CHAR(SYSDATE,'YYYYMM') FROM DUAL; --202109?
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL; ---06 日
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL; --时
SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL; --分
SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL; --秒
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD-HH24-MI-SS') FROM DUAL; --转换年月日时分秒
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') FROM DUAL; --转换年月日时分秒
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL; ---这个星期的第几天
SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;---星期几

TRUNC 对时间的截取

SELECT TRUNC(SYSDATE,'YYYY')FROM DUAL; ---截取到时间所在年的一月一号
SELECT TRUNC(SYSDATE,'MM')FROM DUAL; ---截取到时间所在月一号
SELECT TRUNC(SYSDATE,'DD')FROM DUAL; --截取到当天的0点0分0秒
SELECT TRUNC(SYSDATE,'D') FROM DUAL; ?--截取到这个时间所在星期的第一天
SELECT TRUNC(SYSDATE,'Q') FROM DUAL; ?--截取到这个时间所在季度的第一天

LAST_DAY

SELECT LAST_DAY(SYSDATE) FROM DUAL;????????---查找这个时间所在月份的最后一天

SELECT LAST_DAY(SYSDATE)+1 FROM DUAL;--当前系统时间下一个月的第一天

SELECT LAST_DAY(LAST_DAY(SYSDATE)+1) FROM DUAL; ?--当前系统时间下一个月的最后一天

ADD_MONTHS 月份加减

SELECT ADD_MONTHS(SYSDATE,1)FROM DUAL; --当前系统时间加1个月

SELECT ADD_MONTHS(SYSDATE,-12)FROM DUAL; --当前系统时间减12个月

SELECT ADD_MONTHS(LAST_DAY(SYSDATE),1) FROM DUAL;--下月的最后一天

MONTHS_BETWEEN?月份差值

MONTHS_BETWEEN(参数1，参数2)? ? ? ? ?参数1减去参数2 的月份

SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE(20210501,'YYYYMMDD')) FROM DUAL

DECODE函数

DECODE（字段，
? ? ? ? 判断值1，返回值1，
? ? ? ? 判断值2，返回值2，...，默认值）

SELECT T.DEPTNO
? ? ? ,DECODE(T.DEPTNO
? ? ? ? ? ? ?,10,'ACCOUNTING'
? ? ? ? ? ? ?,20,'RESEARCH'
? ? ? ? ? ? ?,30,'SALES'
? ? ? ? ? ? ?,'OPERATIONS'
? ? ? ? ? ? ?);? ? ? ? ? ? ? ? --部门表，这里没有else

case?when

CASE WHEN 条件1 THEN 返回值1
? ? ?WHEN 条件2 THEN 返回值2
? ? ?ELSE 默认值 ?---默认值 可以写也可以不写
?END? ? ? ? --end一定要写，后面可以接别名

WITH AS 优化器

如果WITH AS短语所定义的表名被调用两次以上，
则优化器会自动将WITH AS短语所获取的数据
放入一个临时表（TEMP）表里，如果只是被调用一次，则不会。

查询出来的结果集会被放入到临时表中，注意临时表的空间，
数据量太大（几千万的数据量）可能会报内存不足的错误

WITH 自定义结果集名称 AS(
? ? ? ? ? ? ? ? ? ? ? ? ?具体的查询逻辑
? ? ? ? ? ? ? ? ? ? ? ? ?)

集合运算

INTERSECT(交集)，返回两个查询共有的记录。
UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
UNION(并集)，返回各个查询的所有记录，不包括重复记录。
MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。--（数据效验的时候常用）

ROWID? 用法

ROWID 是一个物理地址，每一条数据所对应的 ROWID 都是唯一的

---删除重复数据
---公式
DELETE FROM 表名 别名
WHERE ROWID NOT IN (SELECT MIN(ROWID)?
? ? ? ? ? ? ? ? ? ? ? FROM 表名 别名?
? ? ? ? ? ? ? ? ? ? ?GROUP BY 列名);

SELECT *?
FROM DEPT T?
WHERE ROWID IN(
? ? ? ? ? ? ? ? SELECT MIN(ROWID)?
? ? ? ? ? ? ? ? FROM DEPT T
? ? ? ? ? ? ? ? GROUP BY T.DEPTNO
? ? ? ? ? ? ? ?); - -----这是看有哪些数据重复相当于加了个DISTINCT，ROWIN NOT IN 则把所有重复的列出来

ROWNUM? ?伪例

在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，
第一行返回1，第二行返回2，以此类推。
通过ROWNUM伪列可以限制查询结果集中返回的行数;

只能用小于或者小于等于，
不能用大于或者大于等于,即：rownum < 或者 rownum <=

SELECT ROWNUM,T.*
FROM EMP T
WHERE ROWNUM <=3;

创建序列

CREATE SEQUENCE sequence_name
[START WITH num]
[INCREMENT BY increment]
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]
----
CREATE SEQUENCE sequence_TEST_0910
START WITH 1 ? ---从1开始
INCREMENT BY 1 ?---每次增长1
MAXVALUE 10 ? ? ---最大值
MINVALUE 1 ? ?--最小值
CYCLE ? ? --循环
CACHE 5;

删除序列

DROP SEQUENCE 序列名称

其他的Oracle内容后面再分享吧，其实2个数据库大部分都是相同的，有什么错误的地方也麻烦指出，让我能够改正，也能为后面的阅读者提供正确的内容，ths