第二周：认识 sql 并学习数据库的基础操作

发表于 更新于 分类于 阅读次数：
信安之路WEB安全第二周学习报告：认识 sql 并学习数据库的基础操作

认识 sql 并学习数据库的基础操作

Date：第二周
Auther：胖花
Blog：https://peana.github.io/2019/08/24/second-week-work/

一、什么是关系型和非关系型数据库

1.1 关系型数据库：

（资料来源：维基百科）

  • 概念：依靠关系模型来创建的数据库，关系模型可简单理解成“一对一”，“一对多”，“多对多”的关系。

关系模型由关系数据结构（二维表格模型）、关系操作集合（sql语句）、关系完整性约束（数据类型约束等）三部分组成

  • 关系型数据库特点
    • 优点
      1、容易理解：二维表结构复合数学逻辑，关系 模型相对网状、层次等，较其他模型容易理解
      2、数据安全：由于数据存储在磁盘而不是内存中，数据不受断电影响
      3、易于维护：由于关系模型中关系完整性约束，大大降低了数据冗余和数据不一致的概率
    • 缺点
      1、性能欠佳：多表的关联查询过于复杂，复杂的数据分析类型的复杂SQL报表查询，
      2、维护复杂：数据库难以进行横向扩展，无法像其他应用服务以用通过简单的硬件堆叠和服务节点增加以达到扩展性能和负载的目的。若应用系统的用户量和访问量逐渐增加后，数据库往往需要通过停机维护、数据迁移的方式完成升级、扩展。
  • 常见关系型数据库
    Oracle，Microsoft SQL Server，MySQL，PostgreSQL，DB2，
    Microsoft Access， SQLite，Teradata，MariaDB(MySQL的一个分支)，SAP

1.2 非关系型数据库

  • 概念：又称NoSQL，是除了关系型数据库的其他类型数据库。主要是基于非关系模型的数据库
  • 非关系型模型
    • 列模型：存储的数据是一列列的。关系型数据库以一行作为一个记录，列模型数据库以一列为一个记录。（这种模型，数据即索引，IO很快，主要是一些分布式数据库）
    • 键值对模型：可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
    • 文档类模型：以一个个文档来存储数据，有点类似“键值对”
  • 非关系型数据库特点
    • 优点：
      1、读取快，效率高：因为数据存放在内存中
      2、灵活，可适用于SNC（Social Networking Services）中：例如facebook、微博等。系统升级、功能增加往往意味着数据结构的巨大变动，关系型数据库在此时需要新的结构化数据存储，而非关系型数据库算是数据结构化存储方式的集合，恰能完美解决
      3、定制化程度高：用户可根据需求添加字段，获取信息时，只需要根据id取出相应的value值即可
    • 缺点：
      1、不安全：因为数据存放在内存中，断电会丢失数据
      2、只适合简单数据
      3、不适合持久存储海量数据

二、mysql的基础知识

2.1 MySQL基础设置

  • 基础操作
    1
    2
    3
    4
    5
    6
    mysql -u root -p
    #按照提示输入mysql密码
    #以下命令在mysql交互中进行
    show databases;#查看所有数据库
    use mysql;#使用mysql
    show tables;#查看所选数据库下的数据表
  • 创建数据库，数据表
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    #创建study数据库
    create DATABASE study;
    use study;
    #创建user表,UNSIGNED为非负数
    CREATE TABLE IF NOT EXISTS `user`(
       `id` INT UNSIGNED AUTO_INCREMENT,
       `name` VARCHAR(100) NOT NULL,
       `sex` VARCHAR(40) NOT NULL,
       `phone` INT UNSIGNED,
       PRIMARY KEY ( `id` )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 创建用户并最小化赋权
    • 创建用户：
      • 命令：
        CREATE USER 'username'@'host' IDENTIFIED BY 'password';
      • 说明：
        username：你将创建的用户名
        host：指定用户可以在哪个主机上登陆此mysql，本地用户可使用localhost，使用通配符%表示该用户可以从任意远程主机登陆
        password：用户登陆密码
    • 授权与撤销权限：
      — 授权：
      GRANT privileges ON databasename.tablename TO 'username'@'host';
      — 撤销权限：
      REVOKE privilege ON databasename.tablename FROM 'username'@'host';
      • 说明：
        privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
        databasename：数据库名
        tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
    • 查看mysql所有用户：
      select * from mysql.user\G;
    • 删除用户:
      DROP USER 'username'@'host';

2.2 MySQL数据类型

此处只列举常用数据类型
|类型|大小(字节)|范围|用途|
|-|-|-|-|
|INT|4|(0，4 294 967 295)|大整数值|
|DATE|3|1000-01-01/9999-12-31|日期值(YYYY-MM-DD)|
|TIME|3|’-838:59:59’/‘838:59:59’|时间值(HH:MM:SS)|
|CHAR|0-255||定长字符串|
|VARCHAR|0-65535||变长字符串|

三、mysql增删改查

  • 增
    1
    INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
  • 删
    1
    DELETE FROM table WHERE id=1;
  • 改
    1
    UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
  • 查
    SELECT column_name,column_name FROM table_name [WHERE Clause][LIMIT N][ OFFSET M]
    • 方括号内为可选项
    • 用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
    • 用 WHERE 语句来包含任何条件
    • 用 LIMIT 属性来设定返回的记录数
    • 通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0

where子句过滤：

  • 操作符：
    • =,<>,!=,<,<=,!<,>,>=,!>,BETWEEN,IS NULL
    • 不匹配检查
    • 范围检查
    • 空值检查
  • 高级数据过滤
    • AND
    • OR
    • IN:字段中的值与in后面的集合匹配则选出
    • NOT
  • 使用通配符过滤：like
    • %：任意字符出现任意次数
    • _ ：匹配单一字符
    • [ ] : 用来指定一个字符集，匹配括号内的字符