关系模型和SQL

关系模型和SQL

为了介绍关系模型，以MySQL数据为例。

• 安装MariaDB

#安装mariadb
> yum install mariadb-server
#启动服务
> systemctl start mariadb.service
#开机启动
> systemctl enble mariadb.service
# 为了安全设置Mysql服务
> mysql_secure_installation
# 数据库密码登录
> mysql -u root -p
# 显示所有数据库
mysql> show databases;
# 创建用户并授权
mysql> grant all on *.* to 'xdd'@'%' identified by 'xdd';
mysql> flush privileges;
# 导入测试脚本
> mysql -u root -p < test.sql

SQL语句

• SQL是结构化查询语言Structured Query Language。1987年被ISO组织标准化。

• 所有主流的关系型数据库都支持SQL，NoSQL也有很大一部分支持SQL。

• SQL语句分为：

  1. DCL数据库控制语言
     • 负责数据库权限访问控制，由GRANT和REVOKE两个指令组成
  2. DDL数据定义语言
     • 负责数据库定义，数据库对象定义，由CREATE,ALTER与DROP三种语句组成
  3. DML数据库操作语言
     • 负责对数据库对象的操作，CRUD增删改查
  4. TCL事务控制语言
     • 负责处理ACID事务，支持commit、rollback指令

• 语言规范

  1. SQL语句大小写不敏感
     • 一般建议，SQL的关键字、函数等大写
  2. SQL语句末尾应该使用分号结束
  3. 注释
     • 多行注释 /*注释内容*/
     • 单行注释 -- 注释内容
     • MySQL注释可以使用#
  4. 使用空格或缩进来提高可读性
  5. 命名规范
     • 必须以字母开头
     • 可以使用数字、#、$和_
     • 不可使用关键字

DDL命令

DCL数据库控制语言

GRANT授权，REVOKE撤销

GRANT ALL ON employees.* TO 'xdd'@'%' IDENTIFIED by 'xdd';
REVOKE ALL ON *.* FROM xdd;

1. 常用特殊符号
   • *为通配符，代指任意库或者任意表。
   • *.*所有库的所有表。
   • employess.*表示employees库下所有的表
   • %为通配符，它是SQL语句的通配符，匹配任意长度字符串

DDL数据库定义语言

1. 删除用户(慎用)

   DROP USER xdd;
   

2. 创建数据库

   • 库是数据的集合，所有数据按照数据模型组织在数据库中。

   CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
   CREATE DATABASE IF NOT test CHARACTER SET utf8;
   

   • CHARACTER SET指定数据库字符集。
     1. utf8mb4是utf8的扩展。支持4字节utf8mb4,需要MySQL5.5.3+。
   • COLLATE指定字符集的校对规则，用来做字符串的比较。

3. 删除数据库

   DROP DATABASE IF EXISTS gogs;
   

4. 创建表

   • 表分为行和列，MySQL是行存数据库。数据是一行行存的，列必须固定多少列。
     • 行Row,也称为记录Record，元组。
     • 列Column,也称为字段Field、属性。
   • 字段的取值范围叫做域Domain。例如gender字段的取值就是M或者F两个值。
   • 反引号标注的名称，会被认为是非关键字，使用反引号避免冲突。

   CREATE TABLE `xddtable` (
       `emp_no` int(11) NOT NULL,
       `birth_date` date NOT NULL,
       `first_name` varchar(14) NOT NULL,
       `last_name` varchar(16) NOT NULL,
       `gender` enum('M','F') NOT NULL,
       `hire_date` date NOT NULL,
       PRIMARY KEY (`emp_no`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   

5. DESC查看列信息

   • 语法{DESCRIBE | DESC} tb|_name [col_name | wild]

   DESC xddtable;
   DESC xddtable '%name'
   

• 练习 设计一张表，记录登录账户的注册信息，应该存储用户的姓名、登录名、密码

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
use test;
CREATE TABLE `reg` (
    `id` int(11) NOT NULL,
    `loginname` varchar(50) NOT NULL,
    `name` varchar(64) DEFAULT NULL,
    `password` varchar(128) NOT NULL,
    PRIMARY KEY (`id`)
) Engine = InnoDB

数据类型

• MySQL中的数据类型

类型	含义
tinyint	1字节，带符号的范围是[-128,127]。无符号的范围是[0,255]。 bool或boolean，就是tinyint，0表示假,非0表示真
smallint	2字节，带符号的分为是[-32768,32767]。无符号的范围是[0,65535]
int	整形，4字节，同Integer,带符号的范围是[-21474483648,2147483647]。无符号的范围是[0,4294967295]
bigint	长整形，8字节，同Integer,带符号的范围是[-9223372036854775808,9223372036854775807]。无符号的范围是[0,18446744073709551615]
float	单精度浮点数精确到大约7位小数位
double	双精度浮点数精度确到大约15位小数位
DATE	日期。支持的范围为’1000-01-01’到’9999-12-31’
DATETIME	支持的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’
TIMESTAMP	时间戳。范围是’1970-01-01 00:00:00’到2037年
char(M)	固定长度，右边填充空格以达到长度要求。M为长度，范围为[0-255]。 M指代字符个数
varchar(M)	变长字符串。M表示最大列长度。M的范围是[0-65535]。但不能突破行最大字节数65535
text	大文本。最大长度为65535($2$