数据库基础知识---------------------------（1）

数据库分类

关系型数据库以表格方式存储数据

例子： MySQL、Oracle、DB2、SQLserver等

特点： SQL结构程度较高、安全性高、查询效率较低

非关系型数据库以键值方式存储数据

例子： Redis、Hbase、MongoDB等

特点：查询效率高、安全性不高

数据类型

整数 int

小数 double

字符串 varchar()

时间日期 date datetime

其他图片音频存放路径

SQL语句的分类

DDL 数据定义语言

创建库

        create database if not exists 库名 charset utf8;

删除库

        drop database 库名;

查看库

        show databases 显示所有的库

        select database() 显示正在使用的库

        show create database 库名显示创建该库的语句

创建表

        create table if not exists 表名(
         字段名类型约束,
         ...... ..... ....
        );

查看表

        show tables 查看有哪些表

        show create table 表名查看数据表的创建语句

        desc 表名查看表结构

删除表

        drop table 表名

        truncate table 表名清空数据有自动增加字段可以重置为0

        delete 不会重置且效率低

修改表（列操作）

给表添加一列

        alter table 表名 add 字段名类型 first/after 其他字段名

修改列

        alter table 表名 modify 要修改的字段名类型约束

修改列名

        alter table 表名 change 旧字段名新字段名类型约束

删除列

        alter table 表名 drop 列名

修改表名

        rename table 表名 to 新表名

DML 数据操作语言

插入数据

        insert into 表名(字段名1，字段名2，...) values (值1，值2...)

修改数据

        update 表名 set 字段名1 = 值，字段名2 = 值... where 条件

删除数据

        delete from 表名 where 条件

DQL 数据查询语言

查询分类

去重 select distinct 字段名 from 表名

比较查询  >,<,>=,<=,<>,!=

范围查询

        select * from 表名 where 字段名 in （范围值）

        select * from 表名 where 字段名 between A and B

逻辑查询 and / or / not

模糊查询

        select * from 表名 where 字段名 like '%字%' %通配符表示占0-n个位置

        select * from 表名 where 字段名 like ‘_’  _通配符表示占一个位置

非空查询 is null / is not null

排序查询

        select * from 表名 where 条件 order by 字段名 [asc|desc];

聚合查询  聚合函数会忽略空值计算

        select 函数(...) from 表名 [where 条件]; -- count\max\min\avg\sum

分页查询

        select 字段名1,字段名2,... from 表名 limit M, N;

        M 表示从第几个索引开始，默认为0
        计算：(当前页码-1 )*每页显示的数据

        N 表示查询多少个数据

分组查询

        select 字段名1,字段名2,... from 表名 group by 分组字段名 [having 分组条件];

        select 字段名1,字段名2,聚合函数(...) from 表名 group by 分组字段名;

查询顺序

                select
                 字段名，聚合函数 as 别名
                from
                  表名1 join 表名2 on 关联条件
                where
                  条件
                group by
                 分组字段名、sum(聚合函数)
                having
                  过滤条件
                order by
                  字段名
                limit m, n；

FROM：首先执行FROM子句，确定要从哪些表中检索数据。

ON：接着执行ON子句，用于表之间的连接条件。

JOIN：然后是JOIN操作，根据ON子句的条件合并表。

WHERE：之后是WHERE子句，用于对结果集进行过滤。

GROUP BY：GROUP BY子句用于将结果集分组，通常与聚合函数（如SUM, AVG）一起使用。

HAVING：HAVING子句用于筛选分组后的结果集。

SELECT：SELECT子句用于选择从结果集中返回哪些列。

DISTINCT：DISTINCT用于去除重复的行。

ORDER BY：ORDER BY子句用于对结果集进行排序。

LIMIT：最后，LIMIT子句用于限制返回的结果数量。

DCL 数据控制语言

SQL约束

一般在创建数据表时进行设定，且可以同时对字段设定多个SQL约束

主键 primary key

        添加主键约束

        alter table 表名 add primary key（字段名）

        删除主键约束

        alter table 表名 drop primary key

非空 not null

唯一 unique

默认值 default

外键 foreign key

※※MYSQL常见引擎引擎起到一个启动器的作用

InnoDB：支持事务处理支持外键支持崩溃恢复处理和并发控制

MyISAM：插入数据快,空间和内存使用比较低以查询插入为主，基本上可以用这个引擎来创建表

Memory：所有的数据都在内存中，数据的处理速度快，但是安全性不高

archive ：只支持select 和 insert语句，而且不支持索引。常应用于日志记录和聚合分析方面

外键 foreign key

约束两张表
在从表中约束本表中关联字段非主键和主表中的主键
foreign key (字段名) references 另外表名(主键列名)
alter table 表A add constraint xxxfk foreign key (外键字段名) references 另外表名(主键字段名);（了解）

关系：

一对一
就是在数据表A中的一条数据，在数据表B中都有一个与之对应的结果
1.主键对主键
2.在任意一张中建立外键连接另一个表的主键.外键必须加唯一约束.
一对多
在【多】的一方有个字段，作为外键字段指向【一】的一方的主键字段。
多对多
通常需要创建第三张表（综合表），并作为中间表，且该表至少有两个字段（都是外键），分别作为外键字段指向各自一方的主键。
就是将一个多对多关系拆分成：两个一对多关系的数据表

多表查询

交叉连接查询

笛卡尔积两个表并集
select 字段名 from A表名, B表名;
select 字段名 from A表名 cross join B表名;

内连接查询两个集合交集

隐式内连接
select 字段名 from A表名, B表名 where 条件;
显式内连接
select 字段名 from A表名别名 [inner] join B表名别名 on 条件 [where 查询条件];

外连接查询

如果根据条件查询，当表数据不存在时，则会使用null值来填充

左外连接以左表为主并根据条件来查询右表数据
select 字段名 from A表名别名 left [outer] join B表名别名 on 条件;
右外连接以右表为主并根据条件来查询左表数据
select 字段名 from A表名别名 right [outer] join B表名别名 on 条件;

自连接查询

select *|字段名1,字段名2,... from A表名 a join B表名 b on a.字段名 = b.字段名 [where 条件];
在进行连接查询操作时，必须为数据表定义别名
自连接查询常用于省市的多级关联处理又或者员工上级的多级关联

子查询

语法可以来源于一个子查询也可以条件是一个子查询的字段

select *|字段名1,字段名2,.. from 表名 where 字段名 = (另外select语句的结果);
select *|字段名1,字段名2,.. from 表名 where 字段名 in (另外select语句的结果);
select *|别名.字段名1,别名.字段名2,.. from (select * from 表名 ) 别名

常用函数

字符函数

length(str) 字符串求占用空间长度
concat(str1,str2...) 连接字符串
upper(str)、lower(str)函数转大小写
substr(str,start,len)函数从指定位置截取指定长度的字符串
instr(str,要查找的子串)函数判断是否存在返回索引值或者 0
trim(str)函数去除两端空格
lpad(str,len,填充字符)、rpad(str,len,填充字符)函数左右两边指定个数填充.
replace(str,子串,另一个字符串)函数字符串替换

数学函数

①round(x,保留位数)函数: 四舍五入保留小数位.
②ceil(x)函数 :天花板函数
③floor(x)函数 :地板函数
④truncate(x,D)函数 : 按照D的值截取小数部分.
⑤mod(被除数,除数)函数 : 求余数
⑥pow(x,D)函数 : x的D次方

时间与日期

②now()函数 :当前日期和时间
③curdate()函数 :当前日期
④curtime()函数 :当前时间
⑤year、month、day、hour、minute、second获取日期和时间中的年、月、日、时、分、秒
⑥weekofyear()函数 :一年的第几周
⑦ quarter()函数 :一年中的季度
⑧ str_to_date()函数 :字符串转时间类型
⑨date_format()函数 :格式化时间字符串
⑩date_add(日期,interval num 时间)函数 :添加日期还有一个date_sub同理
⑪last_day()函数 :月度最后一天
⑫datediff(end_date,start_date)函数 : 时间差
⑬timestampdiff(unit,start_date,end_date)函数计算两个时间返回的年/月/天数；

流程操作

if(expr,v1,v2)函数 : 判断数据给出返回值
ifnull()函数 : 判断空给出返回值
nullif(expr1,expr2) : 相同返回null 不同保留表达式1的原值.
case…when函数用法 : 多条件判断给出返回值.
 case…when函数用法     : 多条件判断给出返回值.
    #区间范围的判断.
    Case
        When condition1 Then result1
        When condition2 Then result2
        ...
        Else result_n
    End
    --------------------------------------
    固定值的判断.
    case 列名
        when 值 then 值
        when 值 then 值
        ....
        else 值
    end