数据库
资料库分为了开放式资料库(SQL)和非开放式资料库(noSQL / not just SQL)
开放式资料库管理系统(RDBMS)
- MySQL
- Oracle
- PostgreSQL
- SQL Server
非开放式资料库管理系统(NRDBMS)
- MogoDB
- Redis
- DynamoDB
- Elaticsearch
tables and key
设置成主键(primary key)的那个属性可以唯一的表示每一笔资料,eg:id。
主键的值不能为NULL
外键(foreign key)使表格与表格之间产生关联。外键对应到另一个表格的主键。
外键除了可以对应到其他表格,同时也可以对应到自身。
某个属性可以既是主键也是外键。
创建数据库CREATE DATABASE `sql_name`;
一般要将自己命名的数据库名字用 ` `
括起来,防止与预设关键字冲突;
显示所有数据库:
SHOW DATABASES;
删除数据库:
DROP DATABASE sql_name; # 删除数据库sql_name;
创建表格之前需要先使用数据库USE `sql_name`;
在创建表格的时候需要设置每一个属性的资料形态,以下为常用的六种资料形态:
-
INT --整形
-
DECIMAL(m,n) --有小数点的数
DECIMAL(3,2) – 2.33总共有三位小数,小数点占了两位
-
VARCHAR(n) --字串
VARCHAR(10) – 最多可以存放字的长度,10表示最多可以存放长度为10
-
BLOB --(Binary Large Object)图片 影片 档案. . . ,存放二进制资
-
DATE --‘YYYY-MM-DD’ 日期
-
TIMESTAMP --‘YYYY-MM-DD HH:MM:SS’ 记录时间
创建表格:
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY,
`name` VARCHAR(20),
`major` VARCHAR(20)
);
###############################################
CREATE TABLE `student`(
`student_id` INT,
`name` VARCHAR(20),
`major` VARCHAR(20),
PRIMARY KEY(`student_id`)
);
# 上下都可以
显示表格:
DESCRIBE `student`; # 显示student表格
删除表格:
DROP TABLE `student`; # 删除student表格
增加表格属性:
ALTER TABLE `student` ADD gpa DECIMAL(3,2);
# 给student表格中增加gpa属性,格式为有小数点的数,3位数,小数点后两位
删除表格属性:
ALTER TABLE `student` DROP COLUMN gpa;
# 删除student表格中的gpa属性
像表格中存入资料:
INSERT INTO `student` VALUES(1, '小白', '历史');
# 填入顺序和创建表格时的顺序保持一致
# 可以将不确定的信息设置成 null
INSERT INTO `student`('name', 'major', 'student_id') VALUES('小白', '历史', 1);
# 也可以自定义顺序填写
INSERT INTO `student`('major', 'student_id') VALUES('历史', 1); 与 INSERT INTO `student`('name', 'major', 'student_id') VALUES(null, '历史', 1); 等价,在'name'这栏显示为null
在创建表格的时候可以给需要的属性增加constraints(限制、约束):
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY AUTO_INCREMENT, # 新增资料时自动+1
`name` VARCHAR(20) NOT NULL, # 不能为空
`major` VARCHAR(20) UNIQUE # 不能重复
# `major` VARCHAR(20) DEFAULT `历史` # 设置默认值为历史
);
修改表格中属性内容:
UPDATE `student`
SET `major` = '英语文学'
# 多条件时 SET `name` = '小灰', `major` = '物理'
WHERE `major` = '英语';
# 多条件时 WHERE `major` = '生物' OR `major` = '化学';
# 如果不加 WHERE,那么就是所有
删除表格中的某项:
DELETE FROM `student`
WHERE `student_id` = 4;
# 多条件时 WHERE `name` = `小灰` AND `major` = '物理';
# WHERE `score` < 60;
# 如果不加 WHERE,那么就是所有
'''
> 大于
< 小于
>= 大于等于
<= 小于等于
= 等于
<> 不等于
'''
取得资料:
SELECT * FROM `student`; # 取得所有属性
SELECT `name`, `major` FROM `student`; # 取得name和major属性
SELCET DISTINCT `sex` FROM `employee`; # 不重复地返回sex属性
SELECT * FROM `student` ORDER BY `score` ASC; # 根据score由小到大取出
# ASC 为默认,可以不写
SELECT * FROM `student` ORDER BY `score` DESC; # 根据score由大到小取出
SELECT * FROM `student` ORDER BY `score`, `student_id`; # 优先根据score排序,相同时按student_id排序
LIMIT 3; # 取得前3笔资料
SELECT * FROM `student` ORDER BY `score` DESC LIMIT 2; # 回传分数最高的前2个资料
SELECT * FROM `student` WHERE `major` = '英语' AND `student_id` = 1; # 回传major是英语且student_id为1的资料
SELECT * FROM `student` WHERE `major` IN('历史', '英语', '生物');
# 等价于下面这个
SELECT * FROM `student` WHERE `major` = '历史' OR `major` = '英语' OR `major` = '生物';
设置外键:
# 创建表格时设置
create table `branch`(
···,
···,
foreign key (`manager_id`) references `employee`(`emp_id`) on delete set null,
···
);
# 随后添加
alter table `employee` add foreign key (`branch_id`) references `branch`(`branch_id`) on delete set null;
如果在给A表格添加内容的时候,其中的外键对应B表格的主键,但此时B表格还没有内容,此时是添加不上去的,可以优先考虑添加B表格或者把对应缺失的外键先设置为null之后在根据需要设置回正常内容。
聚合函数:
-- 1.取得员工人数
SELECT COUNT(*) FROM `employee`; # 取得所有员工人数
SELECT COUNT(`sup_id`) FROM `employee`; # 取得有sup_id属性的人数
-- 2.取得所有出生于1970-01-01之后的女性员工人数
SELECT COUNT(*) FROM `employee` WHERE `birth_data` > '1970-01-01' AND `sex` = 'F';
# COUNT()函数就是计数函数
-- 3.取得所有员工的平均薪水
SELECT AVG(`salary`) FROM `employee`;
# AVG()函数是计算平均值
-- 4.取得所有员工薪水的总和
SELECT SUM(`salary`) FROM `employee`;
# SUM()函数是计算总和
-- 5.取得薪水最高的员工
SELECT MAX(`salary`) FROM `employee`;
# MAX()函数可以取得指定属性的最大值
-- 6.取得薪水最低的员工
SELECT MIN(`salary`) FROM `employee`;
# MIN()函数可以取得指定属性的最小值
wildcards
万用字元(字符?),%
代表多个字元,_
代表一个字元。
个人理解:%
有点像*
,意思是若干个字符;_
则只能代表一个字符
-- 1.取得电话号码尾数是335的客户
SELECT * FROM `client` WHERE `phone` LIKE '%335';
SELECT * FROM `client` WHERE `phone` LIKE '254%'; # 取得开头是254的
# LIKE 是模糊查询, = 是精准查询
-- 2.取得姓艾的客户
SELECT * FROM `client` WHERE `client_name` LIKE '艾%';
-- 3.取得生日在12月的员工
SELECT * FROM `employee` WHERE `birth_data` LIKE '_____12%';
# 五个下划线,因为 data 的格式是 xxxx-xx-xx, _____ 代表了xxxx-, % 代表了 -xx
union
联集,可以把两个搜寻的结果合并在一起
-- 1.员工名字 union 客户名字
SELECT `name` FROM `employee` UNION SELECT `client_name` FROM `client`;
# 也可以再多合并,比如:
SELECT `name` FROM `employee`
UNION
SELECT `client_name` FROM `client`
UNION
SELECT `branch_name` FROM `branch`;
'''
UNION 只能属性的数目是一样的,一个和一个做合并,两个和两个做合并,如果属性的数目存在不一致则不能使用;另外,做合并的属性数据要是一致的,比如都是INT或者都是VARCHAR,否则是不能合并的。
'''
-- 2.员工id + 员工名字 union 客户id + 客户名字
SELECT `emp_id`, `name` FROM `employee`
UNION
SELECT `client_id`, `client_name` FROM `client`;
'''
回传的属性名称用的是第一个属性的名称,以上述为例,为`emp_id`和`name`,当然也可以改掉:
SELECT `emp_id` AS `total_id`, `name` AS `total_name `FROM `employee`
UNION
SELECT `client_id`, `client_name` FROM `client`;
'''
-- 3.员工薪水 union 销售金额
SELECT `salary` AS `total_money` FROM `employee`
UNION
SELECT `total_sales` FROM `works_with`;
join
连接,可以将两个表格连接在一起
-- 取得所有部门经理的名字
SELECT `emp_id`, `name`, `branch_name` FROM `employee` JOIN `branch` ON `emp_id` = `manager_id`;
'''
branch表格中的外键连接到employee表格中,可以通过branch中的外键manager_id对应到employee中的emp_id查询
SELECT * FROM `employee` JOIN `branch` ON `emp_id` = `manager_id`;
employee 中有7个属性,branch 中有3个属性,最后取得的是相加起来10个属性
'''
如果两个表格中存在相同名称的属性,比如employee中的name和branch中的branch_name都是name,可以用以下方式来处理
SELECT `emp_id`, `name`, `branch_name`
FROM `employee`
JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
通过 . 来表示是属于哪个表格中的属性
-- LEFT JOIN
SELECT `emp_id`, `name`, `branch_name`
FROM `employee` LEFT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
# LEFT JOIN 无论条件是否成立,都会将左边(`employee`)的属性回传,右边的表格(`branch`)只有条件成立时才会回传,否则回传 NULL
# LEFT JOIN 和 RIGHT JOIN 用法相同,意思相反
subquery
子查询,意思是可以在一个查询语句里使用另一个查询语句的结果
-- 1.找出研发部门的经理名字
SELECR `name`
FROM `employee`
WHERE `emp_id` = (
SELECT `manager_id`
FROM `branch`
WHERE `branch_name` = '研发'
);
-- 2.找出对单一位客户销售金额超过50000的员工名字
SELECT `name`
FROM `employee`
WHERE `emp_id` IN (
SELECT `emp_id`
FROM `works_with`
WHERE `total_sales` > 50000
);
# 当 WHERE 条件后的 KEY 可取值不只有一个时,用 IN 而不是 =
ON DELETE
之前创建表格,在设置外键时:
create table `branch`(
`branch_id` int primary key,
`branch_name` varchar(20),
`manager_id` int,
foreign key (`manager_id`) references `employee`(`emp_id`) on delete set null
);
# 当 `employee`中`emp_id` 删除时,`branch`中`manager_id` 设置为 NULL
create table `works_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
);
# 当 `employee`中`emp_id` 删除时, `works_with`中对应`emp_id`的那一笔资料一起删除
# 当 `client`中`client_id`删除时, `works_with`中对应`client_id`的那一笔资料一起删除