数据库

目录:

　　一. 初识数据库 

　　二. mysql数据库管理系统

一. 初识数据库

　　　　数据库 是一个可以在一台机器上独立工作的，并且可以给我们提供高效、便捷的方式对数据进行增删改查的一种工具。

　　　　数据库的好处:

　　　　　　1.程序稳定性 ：这样任意一台服务所在的机器崩溃了都不会影响数据和另外的服务。
　　　　　　2.数据一致性 ：所有的数据都存储在一起，所有的程序操作的数据都是统一的，就不会出现数据不一致的现象
　　　　　　3.并发 ：数据库可以良好的支持并发，所有的程序操作数据库都是通过网络，而数据库本身支持并发的网络操作，不需要我们自己写socket
　　　　　　4.效率 ：使用数据库对数据进行增删改查的效率要高出我们自己处理文件很多

　　　　数据(data):　　　　　　

　　　　　　描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，
　　　　　　数据由多种表现形式，它们都可以经过数字化后存入计算机

　　　　数据库(database 简称DB):

　　　　　　数据库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的

　　　　　　数据库是长期存放在计算机内、有组织、可共享的数据集合。
　　　　　　数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、
　　　　　　较高的数据独立性和易扩展性，并可为各种 用户共享

　　　　

　　　　数据库管理系统（DataBase Management System 简称DBMS）:

　　　　　　如何科学地组织和存储数据，如何高效获取和维护数据成了关键
　　　　　　这就用到了一个系统软件---数据库管理系统
　　　　　　如MySQL、Oracle、SQLite、Access、MS SQL Server
　　　　　　mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，
　　　　　　因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。

　　　　数据库管理软件分类:
　　

　　　　关系型数据库管理系统：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
　　　　非关系型数据库管理系统：mongodb，redis，memcache

　　　　关系型数据库需要有表结构，非关系型数据库是key-value存储的，没有表结构

　　SQL : 结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，
　　　　　　是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统

　　SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：

　　1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER

　　2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT

　　3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

二. mysql数据库管理系统

　　　　mysql的常用存储引擎:

InnoDB:

MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。
InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。
为了保证数据的完整性，InnoDB 还支持外键约束。

数据存在硬盘上,存两个文件,表结构,(数据和搜索目录)

 

MyISAM:

MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。

数据存在硬盘上,存三个文件,表结构,数据,和搜索目录

Memory:

在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory类型的表访问数据非常快，
因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失

数据存在内存中,存一个文件,表结构(在硬盘上)

　　　　常用存储引擎的使用场景:

nnoDB

用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。

MyISAM

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。

Memory

将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。

建表的时候指定存储引擎:

mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; 

mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;

也可以使用alter table语句，修改一个已经存在的表的存储引擎。

mysql> alter table ai engine = innodb;



#my.ini配置文件中修改默认存储引擎
[mysqld]
default-storage-engine=INNODB

　　　　mysql支持的数据类型:

https://www.cnblogs.com/Eva-J/articles/9683316.html

　　mysql表的完整性约束:　

　　　　 int unsigned 　　 设置整形无符号(非负) 
　　　　default     　　　　设置默认值   
　　　　not null    　　　　设置非空
　　　　unique      　　　　设置唯一     
　　　　auto_increment 　　自增        
　　　　primary key 　　　　主键
　　　　foreign key 　　　　外建       

　　各个约束的使用方法:

not null  非空

表结构 : id,name,phone,sex
create table stu1(
id int,
name char(12) not null,
phone char(11),
sex enum('male','female')
);


not null + default 非空+默认值

create table stu2(
id int,
name char(12) not null,
phone char(11),
sex enum('male','female') not null default 'male'
)

唯一 unique


unique只是约束在char数据类型内不能重复,但是不能约束null
id name ident
create table stu3(
    id int,
    name char(12),
    ident char(18) unique
)

联合唯一  unique
一台机器上跑着多少个服务,把每一个正在运行的应用程序的信息都统计下来

ip + port
192.168.16.13  mysql 3306
192.168.16.13  kugou 8080
192.168.16.13  flask 5000
192.168.16.15  mysql 3306
192.168.16.16  mysql 3306

create table service(
  id int,
  ip char(15),
  name char(15),
  port int(5),
  unique(ip,port)
)

auto_increment 自增(这一列必须是数字,这一列必须是uniuqe 只能有一个自增序列)

create table userinfo(
  id int unique auto_increment,
  name char(12),
  password char(32)
)

insert into userinfo (name,password) values(-,-)   
插入数据的时候只表明要插入的字段(不包含自增字段) 这样就可以按顺序自增
如果正常插入自己写自增字段的序号也行 但是在接着自增的时候会从自增字段的最后一个序号(如果自己写的序号没有按着顺序添加)开始自增 
即使删除这个最大的序号也依然会从这个序号开始自增

not null 非空 + unique 唯一  = primary key


登录时候的用户名 一定是唯一的
create table userinfo3(
  id int unique,
  username char(18) not null unique,
  password char(32),
  ident char(18) not null unique
)

主键 primary key  :在约束中就是非空 + 唯一

    一般情况下,我们给id字段设置为主键,不允许一张表不设置主键
create table pri2(
  id1 int primary key,
  id3 int unique not null
)

一张表中只能有一个主键 : 主键从约束的角度上来说 就是非空且唯一的
只不过,非空+唯一可以设置多个字段,但是主键只能给一个表中的一个字段设置
如果没有设置主键 会默认第一个unique+not null 的字段为主键

联合主键 : 约束多个字段各自不能为空,并且联合唯一


create table pri4(
    id1 int,
    num int,
    primary key(id1,num)
);

外键
创建两张表

# 表2 班级表 cid class_name
create table clas(
    cid int primary key,
    class_name char(20)
)
表1 学生表 id name class_id
create table stu(
    id int primary key ,
    name char(18),
    class_id int,
    foreign Key(class_id) references clas(cid)
    on update cascade on delete cascade　　　　#联级跟新 联级删除
)

mysql的使用:

　　

1. 操作文件夹（库）
   增：create database db1 (默认utf -8 可以不写 charset utf8);
   查：show databases;
   改：alter database db1 charset latin1;
   删除: drop database db1;

2. 操作文件（表）
   先切换到文件夹下：use db1
   增：create table t1(id int,name char);
   查：show tables;
   改：alter table t1 modify name char(3);
      alter table t1 change name name1 char(2);
   删：drop table t1;

3. 操作文件中的内容（记录）
   增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
   查：select * from t1;
   改：update t1 set name='sb' where id=2;
   删：delete from t1 where id=1;

   清空表：
       delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
       truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，

*auto_increment 表示：自增
*primary key 表示：约束（不能重复且不能为空）；加速查找

　　

　　　　表的操作:

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
    1. 在同一张表中，字段名是不能相同
    2. 宽度和约束条件可选
    3. 字段名和类型是必须的

查看表结构:
    desc 表名=escribe 表名 效果相同；可以查看当前的表结构 
    show create table 表名 除了可以看到表定义之外，还可以看到engine（存储引擎）和charset（字符集）等信息


增加表：　　
    create table t1(id int,name char);
查所有的表：
    show tables;
删出表：　　
    drop table t1;


修改表结构:
1. 修改表名
      alter table 表名 
                      RENAME 新表名;

2. 增加字段
      alter table 表名
                      add 字段名  数据类型 [完整性约束条件…],
                      add 字段名  数据类型 [完整性约束条件…];
                            
3. 删除字段
      alter table 表名 
                      drop 字段名;

4. 修改字段
      alter table 表名 
                      modify  字段名 数据类型 [完整性约束条件…];
      alter table 表名 
                      change 旧字段名 新字段名 数据类型 [完整性约束条件…];

5.修改字段排列顺序/在增加的时候指定字段位置
    alter table 表名
                     add 字段名  数据类型 [完整性约束条件…]  first;
    alter table 表名
                     add 字段名  数据类型 [完整性约束条件…]  after 字段名;
    alter table 表名
                     change 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  first;
    alter table 表名
                     modify 字段名  数据类型 [完整性约束条件…]  after 字段名;
6. 修改主键
    如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。
        先删除主键
            alter table 表名 drop primary key;
        然后再增加主键
            alter table 表名 add primary key(字段);
        注:在增加主键之前,必须先把反复的id删除掉。
7. 
    表添加外键
        alter table 表名 add constraint fk_id foreign key(外键字段名) references 父表(字段名);

    删除外键
        alter table 表名 drop foreign key fk_id;

　　单表查询:

关键字执行的优先级
from
where
group by
select
distinct
having
order by
limit

简单查询
    select 字段名  from 表名
    select 字段名,字段名...  from 表名
    select * from 表

    去重 distinct
    select distinct 某个字段 from 表  # 对某个字段去重

    对筛选的结果进行四则运算
    select 字段*12 from 表

    在显示查询结果的时候临时重命名
    select 字段名 新名字 from 表;
    select 字段名 as 新名字 from 表;

    concat函数
    concat('你想拼接的内容',字段名,'你想拼的其他内容','字段名')
    concat_ws('连接符号',字段1,字段2,....)

    case语句
    SELECT
        (case
        when emp_name = 'jingliyang' then
            emp_name
        when emp_name = 'alex' then
            concat(emp_name,'_bigsb')
        else
            concat(emp_name,'_sb')
        end) as new_name
    FROM employee;

where条件
    select 字段 from 表 where 条件
    条件语句:
        1. 比较运算符  > < >= <= <> !=
        2. between a and b  查询a,b之间的所有内容
        3. in (a,b,c)   查询值为a或者b或者c的所有内容
        4. like
            select * from 表 where emp_name like '金%'
            %是一个通配符,表示任意长度的任意內容
            select * from 表 where emp_name like '金三_'
        _    也是一个通配符,表示一个长度的任意内容
        5. 逻辑运算符
            and or not
        6.身份运算符 
            is null/ is not null
            emp_name is null
        7.正则匹配
            所有人的身份证号,匹配所有身份证号是15位的居民  ^d{15}$
            select 字段 from 表 where age regexp '^d{15}$';

group by 分组
    根据分组的字段自动的做去重
    其他重复的项目都不会在结果中显示
    但是可以使用count来计算每个组中的项，也可以使用group_concat来查看组内的名字
            
having  
    总是和group by 连用,where中不能出现聚合函数(count,sum,avg,max,min),
    所以和聚合函数有关的条件筛选也只能用having对分组进行条件过滤            
            
order by
    默认从小到大排序  升序
    从大到小排序 desc 降序

limit 取前n个或者web开发中做分页功能
    显示前n条 limit n
    从第m+1条开始,显示n条   limit m,n
    从第m+1条开始,显示n条 limit n offset m
            
https://www.cnblogs.com/Eva-J/articles/9688313.html?tdsourcetag=s_pctim_aiomsg            
            
            
            
            
            

 多表查询

　　

　　1 交叉连接：不适用任何匹配条件。生成笛卡尔积
　　　　select * from employee,department;
　　2 内连接：只连接匹配的行
　　　　找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
　　　　select * from 表1 inner join 表2 on 表1.字段=表2.字段;
　　3 外链接之左连接：优先显示左表全部记录 空的会显示null
　　　　select * from 表1 left join 表2 on 表1.字段=表2.字段;
　　4 外链接之右连接：优先显示右表全部记录 空的会显示null
　　　　select * from 表1 right join 表2 on 表1.字段=表2.字段;
　　5 全外连接：显示左右两个表全部记录
　　　　全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
　　　　#注意：mysql不支持全外连接 full JOIN
　　　　#强调：mysql可以使用此种方式间接实现全外连接 左连接 union 右连接
　　　　select * from employee left join department on employee.dep_id = department.id
　　　　union
　　　　select * from employee right join department on employee.dep_id = department.id

　　子查询:
　　　　1：子查询是将一个查询语句嵌套在另一个查询语句中。
　　　　2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
　　　　3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
　　　　4：还可以包含比较运算符：= 、 !=、> 、<等

　　带EXISTS关键字的子查询
　　　　EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
　　　　而是返回一个真假值。True或False
　　　　当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

索引