SQL数据库入门学习

一.基本概念

1.资料库的分类

关联式资料库：把资料用一个个的表储存起来，再做关联

2.什么是SQL

SQL是一种语言，是用来跟对应的关联式资料管理系统做交互的语言

3.SQL表中的重点概念

（1）Primary key（主键）

能够唯一区分每一笔资料的属性，如果单一的主键没有办法唯一区分每一笔资料，则可以设定两个或两个以上的主键来区分每一笔资料

例如下图，可以把mail地址设定为主键

（2）Foreign Key（外键）

可以对应另外一个表的primary key主键的属性，让表格与表格之间关联。也可以对应自己这个表的primary key

二.命令

1.编辑数据库命令

CREATE DATABASE `sql`;     # 创建数据库，资料库名字需要用''括起来，以防和关键字冲突
​
SHOW DATABASES;             # 查看当前系统的数据库
​
DROP DATABASE `sql`;       # 删除当前的'database'数据库
​

2.创建数据库表格

（1）了解数据格式

数据类型	解释
INT	整数
DECIMAL(m,n)	小数 #m表示有几位数，n表示小数点在第几位
VARCHAR(n)	字符串 #n表示最多存放n个字符串
BLOB	用二进制存放图片、影片、档案
DATE	‘YYYY-MM-DD’ 日期
TIMESTAMP	‘YYYY-MM-DD HH:MM:SS’ 记录时间

（2）表格增加/删除/修改命令(CREATE/DROP/ALTER)

USE `sql`;      #选定使用的数据库
​
#创建表格
CREATE TABLE `student`(
    'student_id' INT PRIMARY KEY,
    'name' VARCHAR(20),
    'major' VARCHAR(20)
    #或者PRIMARY KEY('student_id')
);                              
​
#删除表格
DROP TABLE `student`;
​
#增加表格属性
ALTER TABLE `student` ADD gpa DECIMAL(3,2);
​
#删除表格属性
ALTER TABLE `student` DROP COLUMN gpa;
​
#添加某一个属性的自动填充功能，让其每次都自动填充
​
CREATE TABLE `student`(
    'student_id' INT AUTO_INCREMENT,
     PRIMARY KEY ('student_id')
);
​

（3）在表格中存入资料(INSERT INTO)

#根据上方创建表格时创建的属性的顺序依次填入：
INSERT INTO `student` VALUES(1,'小白','历史');
INSERT INTO `student` VALUES(2,'小黑','数学');
INSERT INTO `student` VALUES(3,'小绿','NULL')
​
#也可以手动设置填入资料的顺序
INSERT INTO `student`('name','major','student_id')

（4）限制、约束表格内容(constraints)

#使用"NOT NULL"（不能有空值）,"UNIQUE"（必须唯一）,"DEFAULT '默认值'"（默认值）
​
CREATE TABLE `student`(
    'student_id' INT PRIMARY KEY,
    'name' VARCHAR(20) NOT NULL;
    'major' VARCHAR(20) UNIQUE;
    'score' INT DEFAULT 60
);      

（5）修改资料(UPDATE/SET)

#使用'where'增加条件，如果不加where的话，则SET会把这一列的所有都替换掉
#修改资料，假设需要把major中的“英语”改成“英语文学”
​
UPDATE `student`
SET `major` = '英语文学'
WHERE `major` = '英语';
​
#或者想把小绿的历史改成数学
UPDATE `student`
SET `major` = '历史'
WHERE `student_id` = 3;
​
#或者想把生物和化学系合并成“生化”，可以用“or”
UPDATE `student`
SET `major` = '生化'
WHERE `major` = '生物' or '化学'; 

（6）删除表资料(DELETE)

#也可以使用where来限定条件，假设想删除第2行资料
DELETE FROM `student`
where `student_id` = 2;
​
#或者删除“小黑”和“90分”
DELETE FROM `student`
where `name` = '小黑' or `score` = 90

（7）搜寻资料和信息并进行处理（ORDER/LIMIT/WHERE）

SELECT * FROM `student`  # “*”是取得所有属性的意思
​
#如果要限定属性，
SELECT `student_id`, `name` FROM `student`; #取得student这个表中，student_id和name的列的信息
​
​
#如果想进行排序：
​
#由低到高（增序）：
SELECT * FROM `student`
ORDER BY `score` ASC（#默认）;
#由高到低，使用“DESC”
SELECT * FROM `student`
ORDER BY `SCORE` DESC;
​
#如果想进行多个属性的排序，例如先进行分数的排序，如果分数排序一样，再进行学生id排序：
SELECT * FROM `student`
ORDER BY `score`,`student_id`;
​
#如果想限定查询返回的资料数量,使用:'LIMIT'
SELECT * FROM `student`
ORDER BY `score`,`student_id`
LIMIT 3;
​
#如果想找特定的表，可以配合'where'使用
SELECT * FROM `stuent`
WHERE `major` = '历史' or `major` = '英语';
#上边的where也可以写成下边：
WHERE `major` IN('历史','英语')

（8）添加Foreign key

#命令
FOREIGN KEY (`foreign key的属性名字`) REFERENCES `primary key的表名`（`primary key的属性名`） ON DELETE SET NULL;

（9）实战演练

如上表，我们来在数据库中创建表

①.建表

USE `sql`;      #选定数据库
CREATE TABLE `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 `branch`(
    `branch_id` INT PRIMARY KEY,
    'branch_name' VARCHAR(3),
    `manager_id` INT,
    FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL #设置brach这个表中的manager_id为employee这表中的`emp_ip`的外键
);
​
#添加employee这个表中的外键
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 `client`(
    `client_id` INT PRIMARY KEY,
    `client_name` VARCHAR(20),
    `phone` varchar(12)
);
​
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;
);
​
#然后开始添加资料，首先添加branch表，由于表中`manager_id`的外键关联的employee表未建立，所以先用NULL替代，后续创建了`employee`后再补回来
#branch表
INSERT INTO `branch` VALUES(1,'研发','NULL');
INSERT INTO `branch` VALUES(2,'行政','NULL');
INSERT INTO `branch` VALUES(3,'资讯','NULL');
​
#employee表
INSERT INTO `employee` VALUES(206,'小黄','1998-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);
​
#修改branch表的'manager_id'
UPDATE `branch`
SET `manager_id` = 206
WHERE `branch_id` = 1;
​
UPDATE `branch`
SET `manager_id` = 207
WHERE `branch_id` = 2;
​
UPDATE `branch`
SET `manager_id` = 208
WHERE `branch_id` = 3;
​
#client表
INSERT INTO `client` VALUES(400,'阿狗','254354335');
INSERT INTO `client` VALUES(401,'阿猫','254354336');
INSERT INTO `client` VALUES(402,'旺来','254354337');
INSERT INTO `client` VALUES(403,'露西','254354338');
INSERT INTO `client` VALUES(404,'奥瑞克','254354339');
​
#works_with表
INSERT INTO `works_with` VALUES(206,400,70000);
INSERT INTO `works_with` VALUES(207,401,24000);
INSERT INTO `works_with` VALUES(208,402,9800);
INSERT INTO `works_with` VALUES(209,403,24000);
INSERT INTO `works_with` VALUES(210,404,87940);
​
​

②.查询

#1.取得所有员工的资料
SELECT * FROM `employee`;
​
#2.取得所有客户的资料
SELECT * FROM `client`;
​
#3.按薪水低到高取得员工资料
SELECT * FROM `employee`
ORDER BY `salary`;
​
#4.取得薪水前3高的员工
SELECT * FROM `employee`
ORDER BY `salary` DESC
LIMIT 3;
​
#5.取得所有员工的名字
SELECT `name` FROM `employee`;
​
#6.取得所有员工的性别，但是只需要一个简略的，不需要重复，可以在属性前添加'distinct';
SELECT DISTINCT `sex` FROM `employee`;
​

三.聚合函数(aggregate funcitons)

以（9）实战演练的表格，我们继续学习一些常用的函数

（1）count函数（计数函数，统计一列的数量，NULL不算）

#1.计算员工数量的总和
SELECT COUNT(*) FROM `employee`;
​
#2.计算sup_id的数量
SELECT COUNT(`sup_id`) FROM `employee`;
​
#3.取得所有出生于1970-01-01之后的女性员工的数量
SELECT COUNT(*) FROM `employee` 
WHERE `birth_date` > '1970-01-01' AND `SEX` = 'F';
​

（2）avg函数（平均函数，计算平均值）

#1.统计员工薪水的平均值
SELECT AVG(`salary`) FROM `employee`；

（3）SUM函数

#1.统计员工薪水的总和
SELECT SUM(`salary`) FROM `employee`；

（4）MAX函数（最大值函数）

#1.看薪水最高是多少
SELECT MAX(`salary`) FROM `employee`;
​
#2.看薪水最多的人的名字是谁
SELECT `name` FROM `employee`
WHERE 'salary' = (SELECT MAX(`salary`) FROM `employee`);

（5）MIN函数（最小值函数）

#1.看薪水最少是多少
SELECT MIN(`salary`) FROM `employee`;

四.通配符（wildcards）

在SQL中，''%''可以代表多个字符，'_'代表1个字符

#1.取得电话号码尾数是335的客户，这里需要使用'like'进行模糊匹配
SELECT * FROM `client`
WHERE `phone` LIKE '%335';
​
#2.取得姓旺的客户
SELECT * FROM `client`
where `client_name` LIKE '旺%';
​
#3.取得生日在12月的员工
SELECT * FROM `employee`
WHERE `birth_date` LIKE '%-12-%';

五.合并集（union)

union指令可以把几个搜寻出来的结果合并起来，要求是：1.搜寻出来要合并的结果的数量需要一样，假设第一个条件搜索出两个结果，第二个条件搜索出一个结果，这样是没办法合并的。

#1.将员工名字和客户名字弄在一列里
SELECT `name` FROM `employee`
UNION
SELECT `client_name` FROM `employee`;
​
#2.员工ID + 员工名字 UNION 客户ID + 客户名字
SELECT `emp_id`,`name` FROM `employee`
UNION
SELECT `client_id`,`client_name` FROM `client`;
#合并后的列名默认由第一个表的列名来决定，也可以使用‘AS’来指定列名，例如：
SELECT `emp_id` AS `client_id`,`name` AS `client_name` FROM `employee`
UNION
SELECT `client_id`,`client_name` FROM `client`;
​

未指定列名：

已指定列名：

六.连接（join on）

如果我们想用外键来做媒介连接两个表格的内容，就可以使用JOIN指令

例如我想知道每个部门对应的经理的名字，我可以这样写：

SELECT * FROM `employee`
JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`; #ON代表条件

但是这样拿到的数据太多，我只想要员工ID和名字还有部门名字，那我应该怎么写？

SELECT `employee`.`emp_id`,`employee`.`name`,`branch`.`branch_name` 
FROM `employee` JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
#`表名`.`列名`这种格式的写法可以避免不同表格中的同名属性出现冲突

此时我还可以在JOIN的前边加入“LEFT”或“RIGHT”来指定是左边还是右边的表**无论是否达成“ON”的条件，都回传所有的资料给我们，而另一边的表，则必须要达成条件才能回传资料，不然的话就回传一个"NULL"。

LEFT JOIN：

SELECT `employee`.`emp_id`,`employee`.`name`,`branch`.`branch_name` 
FROM `employee` LEFT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
#此时左边的表'employee'无论条件怎么样，都会显示所有数据

RIGHT JOIN：

SELECT `employee`.`emp_id`,`employee`.`name`,`branch`.`branch_name` 
FROM `employee` RIGHT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
#此时右边的表'branch'无论条件怎么样，都会显示所有数据

七.子查询/嵌套查询（subquery）

子查询其实就是嵌套查询，可以在需要查询多个条件的时候进行查询

练习1：

#1.找出研发部门的经理名字
#思路：这里我们可以找出每个部门对应的manager_id，在根据manager_id去找`employee`表中对应的name
#先写第一个查询语句，找出研发部门对应的manager_id：
SELECT `manager_id` FROM `branch`
WHERE `branch_name` = '研发';
​
#然后写第二个查询语句，找出上边查出的manager_id对应的名字
SELECT `name` FROM `employee`
WHERE `emp_id` = ()
​
#此时把第一个查询语句放入()内：
SELECT `name` FROM `employee`
WHERE `emp_id` = (SELECT `manager_id` FROM `branch`
WHERE `branch_name` = '研发');

练习2：

#找出对单一客户销售金额超过50000的员工名字
#思路：先找出对单一客户销售金额超过50000的员工的emp_id，在通过emp_id找name
#第一个查询语句：
SELECT `emp_id` FROM `works_with`
WHERE `total_sales` > 50000;
查询得到两个结果
​
#第二个查询语句
SELECT `name`,`emp_id FROM `employee`
WHERE `emp_id` IN ()
#由于第一个查询查询到的有多个结果，所以这里不用'='而是用'in'
​
#合并两个查询语句
SELECT `name`,`emp_id` FROM `employee`
WHERE `emp_id` IN (SELECT `emp_id` FROM `works_with`
WHERE `total_sales` > 50000);
​
#如果想把对应的销售额也加上，配合JOIN使用，此时用查出来的名字再去对应emp_id
#此时有个重要的地方：当你在查询中使用子查询时，需要给子查询的结果集（AS）起一个别名，以便在主查询中引用。
SELECT `name`,`total_sales` FROM (SELECT `name`,`emp_id` FROM `employee`
WHERE `emp_id` IN (SELECT `emp_id` FROM `works_with`
WHERE `total_sales` > 50000)) AS subquery
JOIN `works_with`
ON subquery.`emp_id` = `works_with`.`emp_id`;

八.ON DELETE(关联第二章第九节的foreign key的建立)

我们在第二章第九节在创建foreign key时还有，我们是这样设定的：

FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL #ON DELETE SET NULL
​
FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE #ON DELETE CASCADE

在这里ON DELETE的目的是设定，当外键对应的主键被删除时，表格采取什么动作。

例如“ON DELETE SET NULL”就是当对应的主键被删除时，外键的值自动变为“NULL”；

“ON DELETE CASCADE”就是当对应的主键被删除时，自动删除外键对应的这一行；

不过这里要注意，如果对应的外键也是一个表中的主键的话，这个外键是不能设定成“ON DELETE SET NULL”的，因为主键不能为“NULL”