《SQL快速入门》Notes

资料来源：B站视频：【数据库】SQL 3小时快速入门

@Seymour0314

题外：

  • 登陆数据库

    1
    2
    mysql -uroot -p
    输入密码

  • MySQL 的常用变量（函数）

    1
    2
    3
    4
    Datebase()	// 查看当前数据库名
    User()		// 用户
    Version()	// mysql 版本
    @@basedir	// 安装路径

  • 常用符号

    1
    2
    3
    4
    逻辑运算符
    &	AND
    ||	OR
    ^	XOR

摘要：

  • 数据库基本操作

    1
    2
    3
    4
    建库、删库、用库
    CREATE DATABASE db_name;
    DROP DATABASE db_name;
    USE db_name;

  • 数据表基本操作

1
2
3
4
5
6
7
8
9
10
11
建表
CREATE TABLE table_name(
	`id` int(11),
	`name` varchar(255)
);
DESC table_name;
增删改查
INSERT INTO table_name VALUES('5','Seymour');
DELETE FROM table_name WHERE id=5;
UPDATE table_name SET name='Apple' WHERE id=5;
SELECT * FROM table_name;

第一课：认识MySQL

1
2
3
CREATE DATABASE `sql_tutorial`;
SHOW DATABASES;
DROP DATABASE `sql_tutorial`;

第二课：数据类型

1
2
3
4
5
6
INT				-- 整数
DECIMAL(3,2)	-- 小数，如2.33
VARCHAR(10)		-- 字串
BLOB			-- (Binary Large Object)图片、影片、档案
DATE			-- 'YYYY-MM-DD' 日期
TIMESTAMP		-- 'YYYY-MM-DD HH:MM:SS' 日期时间
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE DATABASE `sql_tutorial`;
SHOW DATABASES;
USE `sql_tutorial`;

CREATE TABLE `student`(
	`student_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `major` VARCHAR(20)
);

DESCRIBE `student`;

DROP TABLE `student`;

ALTER TABLE `student` ADD `gpa` DECIMAL(3,2);
ALTER TABLE `student` DROP COLUMN `gpa`;

第三课：写入数据

1
2
3
4
5
6
7
SELECT * FROM `student`;

INSERT INTO `student` VALUES(1,'小白','历史');
INSERT INTO `student` VALUES(2,'小黑','生物');
INSERT INTO `student` VALUES(3,'小绿',NULL);
INSERT INTO `student`(`name`,`major`,`student_id`) VALUES('小蓝','英语',4);
INSERT INTO `student` VALUES(5,'小黄','生物');
image-20230826160025214

第四课：限制约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `student`(
	`student_id` INT AUTO_INCREMENT,
    `name` VARCHAR(20) NOT NULL UNIQUE,
    `major` VARCHAR(20) DEFAULT '历史',
    PRIMARY KEY(`student_id`)
);

DROP TABLE `student`;

SELECT * FROM `student`;

INSERT INTO `student` VALUES(1,'小白','英语');
INSERT INTO `student`(`student_id`,`name`) VALUES(2,'小黑');
INSERT INTO `student`(`name`,`major`) VALUES('小白','生物');

第五课：修改删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
SET SQL_SAFE_UPDATES = 0;

CREATE TABLE `student`(
	`student_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `major` VARCHAR(20),
    `score` INT
);

DROP TABLE `student`;

SELECT * FROM `student`;
-- 1
INSERT INTO `student` VALUES(1,'小白','英语',50);
INSERT INTO `student` VALUES(2,'小黄','生物',90);
INSERT INTO `student` VALUES(3,'小绿','历史',70);
INSERT INTO `student` VALUES(4,'小蓝','英语',80);
INSERT INTO `student` VALUES(5,'小黑','化学',20);
-- 2
UPDATE `student`
SET `major` = '英语文学'
WHERE `major` = '英语';
-- 3
UPDATE `student`
SET `major` = '生物'
WHERE `student_id` = 3;
-- 4
UPDATE `student`
SET `major` = '生化'
WHERE `major` = '生物' OR `major` = '化学';
-- 5
UPDATE `student`
SET `name` = '小灰', `major` = '物理'
WHERE `student_id` = 1;
-- 6
UPDATE `student`
SET `major` = '物理';
-- 7
DELETE FROM `student`
WHERE `student_id` = 4;
-- 8
DELETE FROM `student`
WHERE `name` = '小灰' AND `major` = '物理';
-- 9
DELETE FROM `student`
WHERE `score` < 60;
-- 10
DELETE FROM `student`;

更新

image-20230826162736012 image-20230826162938618image-20230826163039175

image-20230826163220106 image-20230826163425615image-20230826163513969

删除

image-20230826163624192 image-20230826163736016 image-20230826163817816

image-20230826164149684 
1
2
3
>大于			<小于
>=大于等于		<=大于等于
=等于			<>不等于

第六课：查询资料

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
CREATE TABLE `student`(
	`student_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `major` VARCHAR(20),
    `score` INT
);
INSERT INTO `student` VALUES(1,'小白','英语',50);
INSERT INTO `student` VALUES(2,'小黄','生物',90);
INSERT INTO `student` VALUES(3,'小绿','历史',70);
INSERT INTO `student` VALUES(4,'小蓝','英语',80);
INSERT INTO `student` VALUES(5,'小黑','化学',20);
-- 1
SELECT * FROM `student`;
-- 2
SELECT `name` FROM `student`;
-- 3
SELECT `name`, `major` FROM `student`;
-- 4
SELECT * FROM `student`
ORDER BY `score`;
-- 5
SELECT * FROM `student`
ORDER BY `score` ASC;
-- 6
SELECT * FROM `student`
ORDER BY `score` DESC;
-- 7
SELECT * FROM `student`
ORDER BY `score`, `student_id`;
-- 8
SELECT *
FROM `student`
LIMIT 3;
-- 9
SELECT *
FROM `student`
ORDER BY `score`
LIMIT 3;
-- 10
SELECT *
FROM `student`
ORDER BY `score` DESC
LIMIT 3;
-- 11
SELECT * 
FROM `student`
WHERE `major` = '英语' AND `student_id` = 1;
-- 12
SELECT * 
FROM `student`
WHERE `major` = '英语' OR `score` >= 50;
-- 13
SELECT *
FROM `student`
WHERE `major` IN('历史','英语','生物');

image-20230826164440940 image-20230826164455473 image-20230826164544342

image-20230826164641146 image-20230826164817335 image-20230826164710202

image-20230826164907796 image-20230826165004212 image-20230826165232954

image-20230826165319053 image-20230826165504879 image-20230826165629443

image-20230826165849144

第七课：创建公司资料库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
-- 员工
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(20),
    `manager_id` INT,
    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;

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(20)
);

-- 工作
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
);

-- 新增资料
-- 部门
INSERT INTO `branch` VALUES(1, '研发', NULL);
INSERT INTO `branch` VALUES(2, '行政', NULL);
INSERT INTO `branch` VALUES(3, '资讯', NULL);
-- 员工
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);
-- 部门更新内容
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;
-- 客户
INSERT INTO `client` VALUES(400, '阿猫', '254354335');
INSERT INTO `client` VALUES(401, '阿狗', '25633899');
INSERT INTO `client` VALUES(402, '旺来', '45354345');
INSERT INTO `client` VALUES(403, '露西', '54354365');
INSERT INTO `client` VALUES(404, '艾瑞克', '18783783');
-- 工作
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(208, 403, '24000');
INSERT INTO `works_with` VALUES(210, 404, '87940');
image-20230826170429151

第八课：练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 1.取得所有员工资料
SELECT * FROM `employee`;

-- 2.取得所有客户资料
SELECT * FROM `client`;

-- 3.按照薪水低到高取得员工资料
SELECT * FROM `employee` ORDER BY `salary`;

-- 4.取得薪水前三的员工名字、薪水
SELECT `name`, `salary` FROM `employee` ORDER BY `salary` DESC LIMIT 3;

-- 5.取得所有性别，不重复
SELECT DISTINCT `sex` FROM `employee`;

image-20230826174427828 image-20230826174440581

image-20230826174620092 image-20230826174917541 image-20230826175123281

第九课：聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- aggregate functions 聚合函数
-- 1. 员工人数
SELECT COUNT(*) FROM `employee`;
SELECT COUNT(`sup_id`) FROM `employee`;

-- 2. 所有出生于1970-01-01之后的女性员工人数
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 MAX(`salary`) FROM `employee`;
SELECT MIN(`salary`) FROM `employee`;

image-20230826175521720 image-20230826175547437 image-20230826175726742 image-20230826175822463 image-20230826175934137

image-20230826180047403 image-20230826180059087

第十课：通配符

1
2
3
通配符
_ 单个字元
% 多个字元
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
USE `sql_tutorial`;

-- 1.全部客户
SELECT * FROM `client`;

-- 2.尾号335
SELECT * FROM `client` WHERE `phone` LIKE '%335';

-- 3.开头25
SELECT * FROM `client` WHERE `phone` LIKE '25%';

-- 4.中间54
SELECT * FROM `client` WHERE `phone` LIKE '%54%';

-- 5.姓氏为艾
SELECT * FROM `client` WHERE `client_name` LIKE '艾%';

-- 6.生日在12月的员工
SELECT * FROM `employee` WHERE `birth_date` LIKE '_____12%';
image-20230826203100174

image-20230826202905883 image-20230826203134508 image-20230826203155832

image-20230826203325962 image-20230826203600624

第十一课：联集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 1. 员工、客户 
SELECT `name` 
FROM `employee`
UNION
SELECT `client_name`
FROM `client`;

-- 2. 员工、客户、部门名 
SELECT `name` 
FROM `employee`
UNION
SELECT `client_name`
FROM `client`
UNION
SELECT `branch_name`
FROM `branch`;

-- 3. ID+名字 
SELECT `emp_id`,`name` 
FROM `employee`
UNION
SELECT `client_id`,`client_name`
FROM `client`;

-- 4. 导出名字自定义 
SELECT `emp_id` AS `ID`,`name` AS `Name` 
FROM `employee`
UNION
SELECT `client_id`,`client_name`
FROM `client`;

image-20230826203945825 image-20230826204115508 image-20230826204308275 image-20230826204536942

第十二课：连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
INSERT INTO `branch` VALUES(4, '偷懒', NULL);

-- 1. 取得所有部门经理信息
SELECT *
FROM `employee` JOIN `branch`
ON `emp_id` = `manager_id`;

-- 2. 简短显示 
SELECT `emp_id`, `name`, `branch_name`
FROM `employee` JOIN `branch`
ON `emp_id` = `manager_id`;

-- 3.防止表格冲突，更加完善的写法 
SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;

-- 4. 左表全连 
SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` LEFT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;

-- 5. 右表全连 
SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` RIGHT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
image-20230826205130194

image-20230826205315790 image-20230826205515468 image-20230826205651640 image-20230826205805280

第十三课：子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- subquery 子查询 
-- 1. 研发部门的经理的名字 
SELECT `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
);

image-20230826210452530 image-20230826210809503

第十四课：参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 部门
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
);
-- 工作
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
);

  • ON DELETE SET NULL

    当指向的 employee 被删除时，此处的 manager_id 则设为 NULL。

  • ON DELETE CASCADE

    当指向的 employee 被删除时，此处的 emp_id 跟着一起删除。

可以思考得知：工作关系的删除方式是不能为第一种的。

为什么？

因为works_with的这两个值同时还是主键，若是删除设为NULL，会导致出错。

第十五课：Python 连接 MySQL

  • Python 安装库
1
pip install mysql-connector-python
  • 创建方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import mysql.connector

connection = mysql.connector.connect(
    host = 'localhost',
	port = '3306',
    user = 'root',
	password = 'password')

cursor = connection.cursor()

# 创建数据库
cursor.excute("CREATE DATABASE `sql_tutorial`;")

# 查看所有数据库
cursor.excute("SHOW DATABASES;")
records = cursor.fetchall()
for r in records:
    print(r)

# 使用数据库
cursor.excute("USE `sql_tutorial`;")

# 创建表格
cursor.excute("CREATE TABLE `qq`(`qq` INT);")

cursor.close()
connection.close()
  • 查询方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import mysql.connector

connection = mysql.connector.connect(
    host = 'localhost',
	port = '3306',
    user = 'root',
	password = 'password',
	database = 'sql_tutorial')

cursor = connection.cursor()

# 取得部门资料
cursor.excute("SELECT * FROM `branch`;")
records = cursor.fetchall()
for r in records:
    print(r)

cursor.close()
connection.close()
  • 修改资料
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import mysql.connector

connection = mysql.connector.connect(
    host = 'localhost',
	port = '3306',
    user = 'root',
	password = 'password',
	database = 'sql_tutorial')

cursor = connection.cursor()

# 新增
cursor.excute("INSERT INTO `branch` VALUES(5, 'qq', NULL);")

# 修改
cursor.excute("UPDATE `branch` SET `manager_id` = NULL WHERE `branch_id` = 4;")

# 删除
cursor.excute("DELETE FROM `branch` WHERE `branch_id` = 5;")

cursor.close()
cursor.commit()		# 修改资料的最后都要进行提交

connection.close()