-
数据库 —> 解决数据持久化问题 —> 不仅仅能够保存数据,更重要的是对数据的管理
-
- 按照一定的结构组织和存储数据
-
- 通过自定义的条件获取想要的数据
-
- 不同的用户可以获取到不同的数据
关系型数据库 —> 非关系型数据库
1972 ---> Codd
~ 1. 理论基础:关系代数 ---> 谓词逻辑/集合论
~ 2. 具体表象:用二维表(行和列)组织和保存数据
行 ---> 记录(元组)tuple
列 ---> 字段(属性)attribute
~ 3. 编程语言:SQL ---> Structured Query Language ---> 结构化查询语言
不区分大小写!!!
~ DDL ---> Data Definition Language ---> create / drop / alter
~ DML ---> Data Manipulation Language ---> insert / delete / update
~ DQL ---> Data Query Language ---> select
~ DCL ---> Data Control Language ---> grant / revoke
-
关系型数据库的产品
- Oracle
- MySQL —> MariaDB
- SQLServer
- DB2
- PostgreSQL
- SQLite
去IOE运动 ---> IBM / Linux Server
Oracle / MySQL
EMC / Fast-DFS
MySQL命令
- 显示所有数据库:show databases;
- 切换数据库:use mysql;
- 显示所有二维表:show tables;
- 创建数据库:
create database school default charset utf8mb4;
- 删除数据库:
drop database if exists school;
~ 创建表:
create table tb_student
(
stu_id int not null comment ‘学号’,
stu_name varchar(20) not null comment ‘姓名’,
stu_sex char(1) default ‘男’ comment ‘性别’,
stu_birth date comment ‘出生日期’,
primary key (stu_id)
) engine=innodb comment ‘学生表’;
~ 修改表:
添加列:
alter table tb_student add column stu_addr varchar(200)
default ‘’ comment ‘家庭住址’;
删除列:
alter table tb_student drop column stu_addr;
修改列(只修改数据类型,不改列名):
alter table tb_student modify column stu_sex boolean default 1;
修改列(需要修改列名):
alter table tb_student change column stu_sex stu_gender
varchar(1) not null default 'F';
添加约束条件:
alter table tb_student add constraint ck_student_gender
check (stu_gender='M' or stu_gender='F');
删除约束条件:
alter table tb_student drop constraint ck_student_gender;
修改表名:
alter table tb_student rename to tb_teacher;
create database school default charset utf8mb4;
use school;
create table tb_student
(
stu_id int not null comment ‘学号’,
stu_name varchar(20) not null comment ‘姓名’,
stu_sex char(1) default ‘M’ comment ‘性别’,
stu_birth date comment ‘出生日期’,
primary key (stu_id)
) engine=innodb comment ‘学生表’;
alter table tb_student add column stu_addr varchar(200)
default ‘’ comment ‘家庭住址’;
alter table tb_student add constraint ck_student_sex
check (stu_sex=‘M’ or stu_sex=‘F’);
insert into tb_student values
(1001,‘周唯一’,‘M’,‘1995-01-10’,‘山东’);
insert into tb_student(stu_id ,stu_name) values
(1002,‘李天明’);
insert into tb_student(stu_id ,stu_name,stu_addr) values
(1004,‘武则天’,‘广元’),
(1005,‘张三’,‘湖南’),
(1006,‘李四’,‘北京’);