MySQL基础知识-Day01

MySQL基础知识-Day01

1. 关系型数据库的特点

   理论基础：关系代数（集合论、一阶逻辑、关系运算）

   具体表象：用二维表装数据

   • 表 - table / entity - relation
     • 列 - column / field - attribute
     • 行 - row / record - tuple
     • 列的数量 - degree
     • 行的数量 - cardinality
     • 主键 - primary key

     • 外键 - foreign key

       编程语言：结构化查询语言（SQL）

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

3. MySQL命令

   show databases;

   show tables;

   show engine;

   show character set;

   help ‘xxx’;

   use xxx;

   desc xxx;

4. MySQL数据类型
   help ‘data types’;
   help ‘decimal’;
   ~ 整数：
   - int / integer / int unsigned
   - bigint / bigint unsigned
   ~ 小数：
   - decimal
   ~ 字符串：
   - char
   - varchar
   ~ 布尔：
   - boolean
   ~ 日期时间：
   - date / time
   - datetime
   ~ JSON
   - JSON数组
   - JSON对象

5. 表关系

   一对一

   多对一 / 一对多

   多对多

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

练习
create database hrs default charset utf8mb4;

use hrs;

create table tb_dept
(
dno int not null comment ‘部门编号’,
dname varchar(20) not null comment ‘部门名称’,
dloc varchar(10) not null comment ‘部门所在地’,
primary key (dno)
) engine innodb comment ‘部门表’;

show char set;
help ‘data types’;

drop table if exists tb_eemp;

create table tb_emp
(
eno int not null comment “工号”,
ename varchar(50) not null comment “姓名”,
sex char(1) default ‘男’ not null comment ‘性别’,
job varchar(10) not null comment ‘职位’,
birth date not null comment ‘出生日期’,
primary key (eno)
) engine innodb comment ‘员工表’;

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

– 修改表删除名为job的列
alter table tb_emp drop column job;

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

– 修改表sex列的名字和数据类型
alter table tb_emp change column sex gender char(1) default ‘男’ not null;

– 修改表添加一个约束限制gender字段只能取’男’或 ‘女’
alter table tb_emp add constraint ck_emp_gender check(gender in (‘男’,‘女’));

– 修改表添加一个检查约束限制birth字段
alter table tb_emp add constraint ck_emp_birth check(birth >= ‘1960-1-1’);

– 修改表删除约束条件
alter table tb_emp drop constraint ck_emp_birth;

– 修改表的名字
alter table tb_emp rename to employees;
alter table tb_dept rename to departments;

– 修改员工表添加一个维持员工和部门多对一关系的列dno
alter table employees add column dno int 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
foreign key (person_id) references tb_person (person_id);

alter table tb_idcard add constraint
unique (person_id);

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
foreign key (person_id) references tb_person (person_id);

alter table tb_idcard add constraint
unique (person_id);

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,
user_id int not null,
bike_id int not null,
start_time datetime not null,
end_time datetime not null,
pay_way int,
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 ck_record_start_end check (end_time > start_time)
);