【总结】MySQL1

MySQL

1. 关系型数据库

1.1 特点

• 理论基础：关系代数（集合论、一阶逻辑、关系运算)
• 具体表象：用二维表装数据
  • 表 - table / entity - relation
  • 列 - column / field - attribute
  • 行 - row / record - tuple
  • 列的数量 - degree
  • 行的数量 - cardinality
  • 主键 - primary key
  • 外键 - foreign key
• 编程语⾔：结构化查询语⾔（SQL）。
  • DDL：数据定义语⾔
  • DML：数据操作语⾔
  • DCL：数据控制语⾔
  • TCL：事务控制语⾔

1.2 SQL

• DDL（数据定义语言）

  • create / drop / alter

  • create database / drop database

  • create table / drop table / alter table

  • add column / drop column

  • modify column / change column

  • add constraint / drop constraint

  • primary key

  • foreign key

  • check

  • unique

  • rename to

• DML（数据操作语言）

  • insert / delete / update / select

• DCL（数据控制语言）-

  • grant to / revoke from

2. MySQL

2.1 基本命令

1）查看所有数据库

• show databases;

2）查看所有字符集

• show character set;

3）查看所有的排序规则

• show collation;

4）查看所有的引擎

• show engines;

5）查看所有⽇志⽂件

• show binary logs;

6）查看数据库下所有表

• show tables;

7） 帮助

• help ‘xxx’;（? show ）

8）使用表

• ~ use xxx;

9）查看表结构

• ~ desc xxx;

2.2 MySQL数据类型

1）整数：

• int / integer - 4字节
• tinyint - 1字节 --> -128 ~ 127
• smallint - 2字节 --> -32768 ~ 32767
• mediumint - 3字节
• bigint - 8字节
• unsigned / zerofill
• int unsigned —> 0 ~ 2^32-1
• bigint unsigned —> 0 ~ 2^64-1
• int(4) zerofill —> 0010

2）小数：

• float

• double / double precision

• decimal / numeric

  decimal(10, 2) —> 保留两位小时

3）布尔类型：

• boolean —> tinyint(1) —> 1 / 0

4）字符串：

• 不可变：char(255) / character
• 可变：varchar(16383) / character varying

例如：char(1) / varchar(1)的区别

char(1)只占一个字节，varchar(1)占两个字节，结束后面会有一个\0

5）字节串：

• binary
• varbinary / binary varying

6）日期时间：

• date：年月日
• time：具体时间
• datetime：年月日+具体时间
• timestamp 时间戳

7）其他：

• longblob --> blob --> binary large object
• longtext --> clob --> character large object
• json
• enum / set

2.3 表关系

1）一对一

例如：公民和身份证

2）多对一 / 一对多

学生和班级：一个学生只有一个班级，一个班级可以有多个学生

3）多对多

学生和课程：一个学生可以选多门课，一门课有多个学生

• 修改表关系
  • 多对一关系在多的一边添加外键列
  • 多对多通过中间表转换成两个多对一
  • 一对一关系是多对一关系的特例

-- 如果存在名为hrs的数据库就删除他
drop database if exists hrs;
-- 创建并指定默认字符集
create database hrs default charset utf8mb4;

-- 切换数据库
use hrs;

-- 创建部门表
-- commit后也是注释
create table tb_dept
(
dno integer not null comment '部门编号',
dname varchar(20) not null comment '部门名称',
dloc varchar(10) not null comment '部门所在地',
primary key (dno)
) engine innodb comment '部门表';

-- 创建员工表
create table tb_emp
(
eno integer not null comment '员工工号',
ename varchar(20) not null comment '员工姓名',
egend char(2) default '男' not null comment '性别',
 -- esex enum('男', '女'), 枚举
epos varchar(10) not null comment '职位',
edate date not null comment '出生日期',
primary key (eno)
) engine innodb comment '员工表';

-- 修改表添加一个salary列
alter table tb_emp add column salary decimal(10, 2) not null;

-- 删除名为epos的列
alter table tb_emp drop column epos;

-- 修改表修改egend列的数据类型
alter table tb_emp modify column egend boolean default 1 not null;

-- 修改表修改egend列的名字和数据类型
alter table tb_emp change column egend  gender char(1) default '男' not null;

-- 修改表添加一个约束限制gender字段只能取‘男’或者‘女’
-- 可以在check 前添加约束名ck_emp_gender 
alter table tb_emp add constraint check (gender in ('男', '女'));

-- 修改表添加一个检查约束edate字段要大于等于1960-1-1
alter table tb_emp add constraint ck_emp_edate check (edate >= '1960-1-1');

-- 删除表约束的约束条件
alter table tb_emp drop constraint ck_emp_edate;

-- 修改表名
alter table tb_dept rename to departments;
alter table tb_emp rename to employees;

-- -------------------------- 一对多 ---------------------------------
-- 建立多对一关系
-- 修改员工表添加一个维持员工和部门多对一关系的列dno
alter table employees add column dno integer not null;

-- 修改员工表添加一个外键约束限制员工表的dno参照部门表的dno
alter table employees add constraint fk_employees_dno 
foreign key (dno) references departments (dno);

-- -------------------------- 一对一 ---------------------------------

-- 建立一对一关系实例
create table tb_person
(
person_id integer not null,
person_name varchar(20) not null,
primary key (person_id)
);

create table tb_idcard
(
card_id char(18) not null,
police_station varchar(50) not null,
expire_date date not null,
person_id integer not null,
primary key (card_id)
);

-- 外键约束变成多对一
alter table tb_idcard add constraint fk_tb_idcard_person_id
foreign key (person_id) references tb_person (person_id);

-- 唯一约束变成一对一
alter table tb_idcard add constraint fk_tb_idcard_person_id
unique (person_id);
desc tb_idcard;

-- -------------------------- 多对多 ---------------------------------

create table tb_user
(
user_id integer not null comment '用户ID',
user_name varchar(50) not null comment '用户名',
user_birth date not null comment '出生日期',
user_level integer not null comment '用户等级',
primary key (user_id)
);

create table tb_bike
(
bike_id integer not null comment '自行车ID',
bike_status integer not null comment '状态',
online_date date not null comment '上线日期',
primary key (bike_id)
);

-- 创建用户使用共享单车记录表（维持用户和共享单车多对多关系中间表）
create table tb_record
(
record_id bigint not null auto_increment comment '流水号',
user_id integer not null,
bike_id integer not null,
start_time datetime not null,
end_time datetime,
pay_way integer,
payment decimal(10,2),
primary key (record_id),
constraint fk_record_user_id foreign key (user_id) references tb_user (user_id),
constraint fk_record_bike_id foreign key (bike_id) references tb_bike (bike_id),
constraint fk_record_start_end check (end_time > start_time)
);
desc tb_record;