MY-SQL-----数据库（前篇）

数据库

操作数据库
创建数据库：crate database 数据库名称
丢弃数据库：drop database 数据库名称
显示数据库：show databases
使用数据库：use 数据库名称

操作表
创建表：create table 表名称(列名 列数据类型 约束类型
修改表名称：alter table 原表名 rename 新表名
添加列：alter table 表名称 add 列名称
修改列：alter table 表名称 change 原列名 新列名 列数据类型
删除列：alter table 表名称 drop 列名称
显示所有表：show tables
显示表详细结构信息：desc 表名称.

主键约束
    添加主键约束
create table t_stu(id int primary key,......);
create table t_stu(id int,......，primary key(id))；
primary key 表示添加主键约束

唯一约束
    添加唯一约束：
create table t_stu(id int unique not null,......)；
unique表示唯一约束，代表本字段是唯一不可重复的；
unique后面加上not null 后，字段将变为主键约束

非空约束
    create table t_stu(id int not null,......)
not null为非空约束，添加后，该字段代表的内容不能为控空

默认约束
    添加默认约束：create table t_stu(id int,age char(2) default(男)，......)

检查约束(mysql不支持)
    create table t_stu(id int not null,age int check(age>18 age<10),......)

外键约束
    建表是先创建主表，再创建从表
create table t_stu(id int ,stu_classid int,......,constraint foreign key(字段名称) references
主表名称(主键名称));
在constraint后面可以加自定义外键名称，不写系统为默认自动加上
alter table 表名 add constraint foreign key() references 主表名(主表主键约束字段)；其中
constraint可以不写

自定义完整性：自己定义一系列约束条件.

数据类型 有五种。

整数数据类型

TINYINT     1字节   （-128,127）                     （0,255）                小整数值
SMALLINT    2字节    (0, 65535)                       (-32 768, 32767)        大整数值
MEDIUIMINT  3字节    (-8 388 608, 8588 607)            (0, 16 777 215)        大整数值
INT         4字节    (-2 147483 648, 2 147 483 647)    (0. 4294967 295)       大整数值
BIGINT      8字节    (-9233372036 854 775 808, 9223 372 036854 775 807)     (o, 18
446744075709 551 615)      极大整数值

浮点数据类型
FLOAT         4字节       7位小数         单精度浮点数
DOUBIE        8字节       15位小数        双精度浮点数
DECIMAL(M.D)  17字节      30位小数        小数值
DECIMAL (M,D) M表示精度,数据的总长度, D表示标度,小数点后的长度, Mx须大于D.
decimal(5,2)表示保留5位数,保留小数点2位数

字符串数据类型
CHAR(n)       0-255              定长字符串
VARCHAR(n)    0-65535            变长字符串
CHAR(n)表示定长长度
VARCHAR(n)表示变成长度
例如:char (5)和varchar (5)
当我们存入abc三个字符时, char(5)实际占用5个字符,
而varchar (5) 实际只占用3个字符.

TINYTEXT      0-255              短文本字符串
TEXT          0-65 555           长文本数据
MEDIUMTEXT    0-16 777 215       中等长度文本数据
LONGTEXT      0-4 294 967 295    极大文本数据

时间日期数据类型

DATE      3字节     YYYY-MM-DD(2000-10-10)     日期值
TIME      3字节     HH:MM:SS(12:10:05)         时间值或持续时间
YEAR      1字节     YYYY (2000)                年份值
DATETIME  8字节     YYYYMMDD HH:MM:SS (2000-10-10 12:10:05)    混合日期和时间值
TIMESTAMP 4字节     YYMMDD HHMMSS             混合日期和时间值,时间戳

二进制数据类型
TINYBLOB      0-255字节             不超过255个字符的二进制字符串
BLOB          0-65535字节           二进制形式的长文本数据
MEDIUMBLOB    0-16 777 215字节      二进制形式的中等长度文本数据
LONGBLOB      0-4294 967295字节     二进制形式的极大文本数据

存储模式
1、层次模型

2、网状模型

3、关系模型
存的时候需要将数据拆分，取出来需要将数据组装.
二维表格方式进行存储.

4、对象模型
将整个对象作为一个整体存储到数据库中.

主键和外键

主键：
1、某一个属性能唯一表示一条记录（具有业务功能的属性不能作为主键）
2、一般定义在"一"上

外键:
1、用于另一张表的关联，是能确定另以张表记录的字段，外键是另一个表的主键，可以重复，可以有多个
，也可以是空值，定义外键主要是为了保持数据的一致性
2、指向主键，外键一般定义在"多"上

数据库与实体之间的关联关系
1、一对一
2、一对多
3、多对多
4、自身关联

修改数据完整性
    添加主键约束
        alter table add primary key(主键名）;
    
    添加唯一约束
        alter table add unique(列名);

    删除唯一约束
        alter table 表名 drop index 索引名

    查看表信息(查看表的创建语法)
        show create table 表名；
show create table 表名\G；

    创建索引
        create index 索引名 on 表名(列名)
            create index idx_age on student(age);

    查看索引
        show index from 表名

    删除索引
        alter table 表名 drop index 索引名

    实现非空约束
        alter table 表名 change 原列名 新列名 数据类型 not null；

    删除非空约束
        alter table 表名 change 原列名 新列名 数据类型；

    实现默认约束
        alter table 表名 change 原列名 新列名 数据类型 default 默认值

    删除默认约束
        alter table 表名 change 原列名 新列名 数据类型

    实现外键约束
        alter table 表名 add constraint foreign key(外键列名) references 主表名(主表
主键列名)；

    删除外键约束
        alter table 表名 drop foreign key 外键约束名称

使用insert语句：插入单行数据
insert into 表名（列名列表）values（值列表）
例：insert into employee（id，name，...）values（1，‘张三'，...）

创建自动增长:auto_increment
    create table t_stu(id int primary key auto_increment);

采用自动增长插入单行数据
    insert into 表名 （id,......） values（null,......）

修改自动增长的值
    alter table 表名 auto_increment = 值

delete语句:删除一行数据信息语句
delete form 表名 where 过滤条件

删除所有数据
    delete
        delete from
            一行一行删除，会在数据库内以二进制形式记录操作日志删除一行记录一行，
文件可恢复
    truncate
        truncate table 表名
            一次性整体删除，不会记录操作日志

简单查询的投影操作
    select
        查询列：select 列1，列2，...... form 表名
        查询所有列：select * form 表名
        表前缀：select 表名.列名，...... form 表名
        列别名：select 列名 as 新列名，...... form 表名; as可以忽略，使用空格代替
        排除重复的数据
            distinct
                列别名：select distinct 列名 as 新列名，...... form 表名; 以
一个或多个列名为准去重
        返回限定行数的查询
            select 列1，列2，...... form 表名 limit 返回的行数
            分页操作
                select 列1，列2，...... form 表名 limit 开始序列号，返回的行
数
                开始序列号 = （当前页数-1）*每页显示条数

简单查询中的选择操作
单条件选择查询：select 列1，...... from 表名 where 列=值
多条件选择操作：select 列1，...... from 表名 where 列=值 and（或or）列=值......
范围查询：select  * form 表名 where 列名 between 下限 and 上限
定义几个关系：in 和 not in     select * form 表名 列名 in（值1，值2......）；in代表值在这个集
合里面
select * form 表名 列名 not in（值1，值2......）；not in代表值不在这个集合里面

模糊查询：like       
    通配符
        _表示任意单个字符
            select  * form 表名 where name like‘李_’;查询姓名是两个字的姓李的人
信息
            select  * form 表名 where name like‘李%’;查询姓名是姓李的人的信息
        %表示包含0个或多个字符

处理空值数据
    判断是否为空应该用 is null 或 is not null;
    数据库中用任何比较运算符匹配null 得到的结果都为false
简单查询中的排序操作
    多列排序：select 列1，列2，...... form 表名 order by 列[asc desc]，......;
    多列排序是有优先级的，先以第一个列进行排序，然后在此基础上挨个以后面列进行排序
    select 列1，列2，...... form 表名 order by 列[asc desc];
列后面什么都不写，表示默认排序（升序排序则）
desc代表降序排序
asc代表升序

聚合函数:是运行在组上，计算和返回单个值的函数，用来对数据进行统计和分析的。

count  统计行的数量
    select count（） form 表名
    （*）代表所有选择的行，包括null值
    （all 列名）计数指定列的非空值行
    （distinct 列名）计数制定列的所有唯一非空值行

    select count（*） form 表名 where 过滤条件.

sum  计算某个列的合计值
    select sum（计算规范） form 表名
    （all 列名）计数指定列的非空值行
    （distinct 列名）计数制定列的所有唯一非空值行

avg   计算某个列的平均值
    select avg（计算规范） form 表名
    （all 列名）计数指定列的非空值行
    distinct 列名）计数制定列的所有唯一非空值行

    select sum（计算规范）/count（*） form 表名；
       值与select avg（计算规范） form 表名；
       两者不一定一样，前者计算了空值，后者未计算

select avg（ifnull（表达式1，表达式2）） form 表名结果才与select sum（计算规范）/count（*）
form 表名；一样
select ifnull（age，0），列名 form 表名；如果为空值则返回0，不为空值则返回age 

round（A,B）
保留小数点位数，A为需要计算的数据计算结果B为要保留的小数的位数
select round（avg（计算规范），2） form 表名

max   计算某个列的最大值
    select max（列名） form 表名

min   计算某个列的最小值
    select min（列名） form 表名

数据分组   group by 子句

1、对数据进行分组

2、select 列A 聚合函数 form 表名 where 过滤条件 group by 列A
   select 列A 聚合函数 form 表名 group by 列A

3、多列分组
select 列A，列B 聚合函数 form 表名 where 过滤条件 group by 列A，列B
select 列A，列B 聚合函数 form 表名 group by 列A，列B

4、having语句
执行 在投影之后，对投影后的数据再进行过滤
select 列A 聚合函数 form 表名 where 过滤条件 group by 列A having 聚合函数过滤条件
having和where都是过滤条件，where运行在分组前，不能执行任何聚合函数，
having执行在分组之后

    SQL执行顺序
    1.执行form
    2.where条件过滤
    3.group by分组
    4.执行select投影列
    5.having条件过滤
    6.执行order by排序

     子查询
当一个查询是另一个查询的条件时，称为子查询（嵌套在查询语句中的查询语句）

在select中嵌套
    select 列1，（select......） as 列别名 from 表名

在from中嵌套
    通常用于SQLServer,OrcleServer分页
    select 列1，列2...... from（子查询） as 表别名

where
    select 列1，列2......  as 表别名 where 列 运算符（子查询）

    in运算符，允许我们在where子句中规定多个值：
select 列1，列2......  where 列 in （值1，值2......）
    not in 表示值不在集合内
select 列1，列2......  where 列 not in （值1，值2......）

    all 运算符
        和子查询的结果逐一比较，必须全部满足时值才为真（相当于与最大值相比）
select 列1，列2...... from 表名 where 列 运算符 all （子查询）

    any运算符
        和子查询的结果逐一比较，只要满足任意一条则为真（相当于与最小值比）
select 列1，列2...... from 表名 where 列 运算符 any（子查询）

    exists运算符
        判断子查询是否存在数据，存在为真，反之为假
        select 列1，列2...... from 表名 where exists （子查询）
        exists与in用法一样，可以互换，exists效率要高一点，因为in是不走索引的
但是要根据实际情况使用，
in适合用于外表大，内表小的情况
exists则适用于内表大，外表小的情况，需要对外表进行限制（如内外id相等）

group by中嵌套与select中嵌套相似
having中嵌套与where中嵌套相似
order by中嵌套中嵌套与select中嵌套相似
且他们只能返回单值

子查询分类

    相关子查询
主查询查一次，相关子查询查一次

    非相关子查询
在主查询中，子查询只需要执行一次，子查询结果不再变化，供主查询使用

联合查询
    union运算符
    将两个查询结果拼接
    select 列1，列2...... form 表1 union select  列1，列2.....，from 表2

union得到的结果会去重
unionall得到的结果就是查询得到的所有结果

表连接
    内连接
        select 列名列表 form 表1 inner join 表2 on 表1.列名 = 表2.列名
        直接用逗号隔开多个表格：select 列名 form 表1，表2...... where 过滤条件

    外连接
        左外连接
        左外连接的结果集包括left outer join子句中指定的左表的所有行，不仅仅是连接列所匹配的行。如果左表某行在右表中没有匹配值，则在相关的结果集行中右表的所有选择列表均为空值
        left outer join 或left join
        select 列名 form 表1 left outer join 表2...... on 过滤条件

         右外连接
        与左外连接相反
        right outer join 或 right join
        返回右表的所有行，如果右表某行在左表中没有匹配值，则在相关的结果集行中左表的所有选择列表均为空值

        全外连接
        用法：full outer join 或full join

    自连接
        自己与自己连接
        select 列名 form 表1 left outer join 表1 on 过滤条件

      数据库建模

良好的数据库设计
    1.节省数据的空间
    2.保证数据的完整性
    3.方便进行数据库引用系统的开发

糟糕的数据库设计
    1.数据冗余，存储空间浪费
    2.内存空间浪费
    3.数据更新和插入的异常

数据库设计步骤
    1.需求分析
    2.概念设计
    E-R模型：实体-关系图
    矩形表示实体
    椭圆表示属性
    菱形表示实体间的关系
    3.逻辑设计
    4.物理设计
    5.数据库的实现
    6.数据库的运行与维护工作

数据库规范化：范式理论
    好处
消除重复数据，减少冗余数据
消除异常
让数据组织的更加和谐