DBMS,DB,SQL简介

DBMS(Datebase Management System)可以分为两类——关系型数据库管理系统(RDBMS)和非关系型数据库管理系统(NRDBMS)，关系型DB(Database)是指通过表格和表格间关系来存储数据的数据库，RDBMS则是使用sql(structured query language)来实现这类DB中数据增删查改的软件。

Fig1. DBMS分类
Fig2. sql是和管理员和RDBMS交流的语言

表格之间的关系

  • primary key：主键是能够唯一确定表中一个条目的属性或属性的组合。
  • foreign key：外键是对应着本表主键或其它表主键的属性（不一定取相同的属性名），是建立表与表之间关系的桥梁。

SQL中的数据类型

  1. INT：32位整数数据类型，能表示的数据范围是~。
  2. DECIMAL(p, s)：精确数值类型，类似与python中decimal模块的Decimal数据类型，其中p表示总位数，s表示小数部分的位数。
  3. VARCHAR(n)：变长字符串，其中n表示最多允许存储的字符数量。
  4. DATETIME：日期类型，格式通常为 YYYY-MM-DD HH:MM:SS[.fraction]。
  5. BOOL：布尔类型，存储逻辑值0-1。
  6. VARBINARY：变长二进制数据。
  7. BLOB (Binary Large Object)：用于存储非常大的二进制对象（如图片、文件）。

目前列举出的都是些常用的数据类型，sql中还有许多其它数据类型，以后如果有机会用到再补充。

SQL常用语法

数据库操作

创建数据库
CREATE DATABASE `name_of_your_database`;
展示所有已经创建数据库
SHOW DATABASES;
选择当前要操作的数据库
USE `name_of_your_database`;
删除数据库
DROP DATABASE `name_of_your_database`;

sql语法不区分大小写，但建议关键字都大写。在mysql中建议将标识符（数据库名、表名、字段名等）用一对反引号`包裹起来，从而避免和关键字冲突，例如某个db名是database，那sql可能把它误当作关键字。

数据表操作

创建数据表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名[(create_definition,...)] [table_options] [select_statement]
  • TEMPORARY：使用该关键字创建一个临时表，临时表只在当前连接可见，当关闭连接时，MySQL 会自动删除表并释放所有空间。
  • IF NOT EXISTS ：用于避免表存在时MySQL报错
  • create_definition：表的列属性部分，至少一列。它的语法是col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition]
    • col_name：字段名
    • type：字段类型
    • NOT NULL | NULL：指出该列是否允许是空值
    • DEFAULT default_value：默认值
    • AUTO_INCREMENT：是否为自动编号，表中只能有一个AUTO_INCREMENT列，并且必须被索引
    • PRIMARY KEY：是否为主键，表中只能有一个PRIMARY KEY
    • reference_definition：为字段添加注释
  • table_options：表的一些特性参数
  • select_statement：SELECT语句描述部分，用它可以快速的创建表
查看数据表属性
DESCRIBE 数据表名称 [列名];
修改数据表结构
ALTER[IGNORE] TABLE 数据表名 alter_spec[,alter_spec]...
常用alter_spec
-- 添加新列
ADD COLUMN create_definition
-- 添加主键
ADD PRIMARY KEY (index_col_name,...)
-- 添加外键，on delete set null 表示如果对应表条目被删除，本张表对应外键被设置位null
add foreign key (外键名) references 对应的另一张表 (相对应的属性) on delete set null
-- 删除现有列
DROP COLUMN 列名
-- 修改列的数据类型
MODIFY COLUMN 列名 新数据类型
修改表名
RENAME TABLE 数据表名1 To 数据表名2;
删除表
DROP TABLE 数据表名;
插入数据
insert into 数据表名[(column_name1,column_name2,...)] values(v1,v2,...)[,(v1,v2,...)];
--后面给定多个括号进行批量添加，中间使用逗号隔开，[(column_name1,column_name2,...)]是可选的，用于指定数据顺序
查询数据
SELECT [DISTINCT] [CONCAT (col1,":",col2) as col] selection_list --要查询的内容,选择哪些列
FROM 数据表名tb_list											--指定数据表
WHERE primary_constraint									--查询时需要满足的内容
GROUP BY grouping_columns									--如何对结果进行分组
ORDER BY sorting_columns									--如何对结果进行排序
HAVING secondary_constraint									--查询时满足的第二条件
LIMIT count													--限定输出的查询结果
修改数据
UPDATE 数据表名 SET column_name1 = new_v1,column_name2 = new_v2,... where condition;
删除数据
DELETE FROM 数据表名 WHERE condition;
使用聚合函数
-- 1.统计员工人数
select count(*) from `employee`;
-- 2.出生于1970年前的女性员工人数 
select count(*) from `employee` where `birth_date` > '1970-01-01' and `sex` = 'F';
-- 3.平均薪水
select avg(`salary`) from `employee`;
-- 4.薪水和 
select sum(`salary`) from `employee`;
-- 5.最低最高薪水
select min(`salary`) from `employee`;
select max(`salary`) from `employee`;
模糊查询
-- 1.找手机尾号11的客户
select * from `client` where `phone` like '%11';-- %表示任意数量的字符
-- 2.找手机尾号第3位是1的客户
select * from `client` where `phone` like '__1%';-- _表示单个字符
联合查询
-- 使用联合查询（将搜寻结果合在一起）
-- 1.员工名字和客户名字 
select `name` from `employee` 
union
select `client_name` from `client`;
-- 2.员工id和名字 客户id和名字 
select `emp_id` as `total_id`,`name` as `total_name` from `employee` union select `client_id`,`client_name` from `client`;
使用连接
-- 使用连接（把两张表连接到一起）
-- 获取部门经理的名字 
select `emp_id`,`name`,`branck_name` from `employee` join `branch` on `employee`.`emp_id` = `branch`.`manage_id`; -- 把`emp_id` = `manager_id`相同的条目拼接到一起
-- left是不管条件是否成立，左边表数据都会返回，right类似
使用子查询
-- 使用子查询 subquery
-- 获取研发部门经理的名字
select `name` from `employee` where `emp_id` = (select `manage_id` from `branch` where `branck_name`='研发');

一个案例

-- 创建公司数据库表格
use sql_tutorial;
create table if not exists `employee`(
	`emp_id` int primary key,
    `name` varchar(20),
    `birth_date` date,
    `sex` varchar(1),
    `salary` int,
    `branch_id` int,
    `sup_id` int
); 
create table if not exists `branch`(
	`branch_id` int primary key,
    `branck_name` varchar(20),
    `manage_id` int,
    foreign key (`manage_id`) references `employee` (`emp_id`) on delete set null -- 如果一张表条目被删，对应不到，另一张表对应条目设置nulll
);
alter table `employee` add foreign key (`branch_id`) references `branch` (`branch_id`) on delete set null; -- 补充第一张表的外键
alter table `employee` add foreign key (`sup_id`) references `employee` (`emp_id`) on delete set null; -- 补充第一张表的外键 如果一张表条目被删，另一张表对应条目也删除
create table if not exists `client`(
	`client_id` int primary key,
	`client_name` varchar(20),
    `phone` varchar(20)
);
create table if not exists `work_with`(
	`emp_id` int,
    `client_id` int,
    `total_sales` int,
    primary key (`emp_id`,`client_id`),
    foreign key (`emp_id`) references `employee` (`emp_id`) on delete cascade,
    foreign key (`client_id`) references `client` (`client_id`) on delete cascade
);
-- 填入数据，注意先得把外键设置为null，因为branch还没有创建，会报错
insert into `branch` values(1,'研发',null);
insert into `branch` values(2,'行政',null);
insert into `branch` values(3,'咨询',null);
insert into `employee` values(206,'小黄','1999-10-08','F',50000,1,null);
insert into `employee` values(207,'小绿','1985-09-16','M',29000,2,206);
insert into `employee` values(208,'小黑','2000-12-19','M',35000,3,206);
insert into `employee` values(209,'小白','1997-01-22','F',39000,3,207);
insert into `employee` values(210,'小蓝','1925-11-10','F',84000,1,207);
update `branch` set `manage_id` = 206 where `branch_id` = 1;
update `branch` set `manage_id` = 207 where `branch_id` = 2;
update `branch` set `manage_id` = 208 where `branch_id` = 3;
insert into `client` values(400,'阿猫','1111');
insert into `client` values(401,'阿狗','2222');
insert into `client` values(402,'路西','3333');
insert into `work_with` values(206,400,7000);
insert into `work_with` values(207,401,24000);
insert into `work_with` values(208,402,10000);

-- 聚合函数
-- 1.统计员工人数
select count(*) from `employee`;
-- 2.出生于1970年前的女性员工人数 
select count(*) from `employee` where `birth_date` > '1970-01-01' and `sex` = 'F';
-- 3.平均薪水
select avg(`salary`) from `employee`;
-- 4.薪水和 
select sum(`salary`) from `employee`;
-- 5.最低最高薪水
select min(`salary`) from `employee`;
select max(`salary`) from `employee`;

-- 使用通配符 
-- 1.找手机尾号11的客户
select * from `client` where `phone` like '%11';-- %表示任意数量的字符
-- 2.找手机尾号第3位是1的客户
select * from `client` where `phone` like '__1%';-- _表示单个字符

-- 使用联合查询（将搜寻结果合在一起）
-- 1.员工名字和客户名字 
select `name` from `employee` 
union
select `client_name` from `client`;
-- 2.员工id和名字 客户id和名字 
select `emp_id` as `total_id`,`name` as `total_name` from `employee` union select `client_id`,`client_name` from `client`;

-- 使用连接（把两张表连接到一起）
-- 获取部门经理的名字 
select `emp_id`,`name`,`branck_name` from `employee` join `branch` on `employee`.`emp_id` = `branch`.`manage_id`; -- 把`emp_id` = `manager_id`相同的条目拼接到一起
-- left是不管条件是否成立，左边表数据都会返回，right类似

-- 使用子查询 subquery
-- 获取研发部门经理的名字
select `name` from `employee` where `emp_id` = (select `manage_id` from `branch` where `branck_name`='研发'); 

-- on delete
describe `employee`;

参考