SQL入门

基础结构

primary key (主键) 橙色
foreign key (外键) 绿色

创建资料库

CREATE DATABASE `sql_tutorial`;  --创建资料库
SHOW DATABASES;  --查看资料库
DROP DATABASE `sql_tutorial`;  --删除资料库

创建表格

-- 数据类型
INT    --整数
DECIMAL(3,2)    --有小数点的数 2.33
VARCHAR(10)    --字串
BLOB    --(Binary Large Object)图片 影片 档案...
DATE    --'YYYY-MM-DD' 日期 2024-1-21
TIMESTAMP   --'YYYY-MM-DD HH:MM:SS' 记录时间 2024-1-21 21:50:29

CREATE TABLE student(               --创建表格
    `student_id` INT PRIMARY KEY,
    `name` VARCHAR(20) NOT NULL,
    `major` VARCHAR(20) UNIQUE
);

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

DESCRIBE `student`;    --绘制表格
DROP TABLE `student`;   --删除表格

ALTER TABLE `student` ADD gpa DECIMAL(3,2);    --给表格新增gpa属性
ALTER TABLE `student` DROP COLUMN gpa ;    --删除gpa属性

存储资料

INSERT INTO `student` VALUE(2, '小黑','生物');    --给表格中插入数据

SELECT * FROM `student`;    --取得表格中的资料

INSERT INTO `student`(`name`,`major`,`student_id`) VALUE('小蓝', '英语',4);        --按给定顺序填写

INSERT INTO `student`(`name`,`major`,`student_id`) VALUE(NULL,'英语',5);
--等价于
INSERT INTO `student`(`major`,`student_id`) VALUE('英语',5);

constraints 限制 约束

CREATE TABLE student(
    `student_id` INT PRIMARY KEY,
    `name` VARCHAR(20) NOT NULL,    --限制不为空
    `major` VARCHAR(20) UNIQUE        --限制不重复
);

INSERT INTO `student` VALUE(1, NULL, '英语');
--INSERT INTO `student` VALUE(3, NULL, '英语')    Error Code: 1048. Column 'name' cannot be null

INSERT INTO `student` VALUE(1, '小白', '英语');
INSERT INTO `student` VALUE(2, '小黑', '英语');
--INSERT INTO `student` VALUE(2, '小黑', '英语')    Error Code: 1062. Duplicate entry '英语' for key 'student.major'

CREATE TABLE student(
    `student_id` INT PRIMARY KEY,
    `name` VARCHAR(20) NOT NULL,
    `major` VARCHAR(20) DEFAULT '历史'    --默认值为历史
);
INSERT INTO `student`(`name`,`student_id`) VALUE('小白',1);

CREATE TABLE student(
    `student_id` INT PRIMARY KEY AUTO_INCREMENT,    --自动补全+1
    `name` VARCHAR(20),
    `major` VARCHAR(20)
);

修改、删除资料

这里我们进行修改资料时选择先设置语句SET SQL_SAFE_UPDATES = 0;以避免不必要的报错。
SET SQL_SAFE_UPDATES = 0;这个语句用于在MySQL中禁用安全更新模式。当SQL_SAFE_UPDATES设置为1时（这是默认值），某些类型的UPDATE和DELETE语句如果缺少WHERE子句，或者带有一个影响多行的LIMIT子句，将不会被执行。这是一种安全特性，旨在防止意外更新或删除表中的所有行。
通过将SQL_SAFE_UPDATES设置为0，实际上是关闭了这个安全特性，允许执行没有WHERE子句或带有影响多行的LIMIT子句的更新和删除。这应该谨慎使用，因为这增加了意外数据修改的风险。
在使用SQL_SAFE_UPDATES = 0进行查询之前，建议确保对查询的后果有很好的了解，以避免意外数据丢失。在执行需要此设置的查询后，最好将其恢复为默认值，以确保安全性。

--修改表格中 `major` 中的 英语->英语文学
UPDATE `student`
SET `major` = '英语文学'
WHERE `major` = '英语';
--同理

UPDATE `student`
SET `major` = '生化'
WHERE `major` = '生物' OR `major` = '化学';

UPDATE `student`
SET `name` = '小灰',`major` = '物理'
WHERE `student_id` = 1;

UPDATE `student`
SET `major` = '物理';

--删除`student_id` = 4 的一行
DELETE FROM `student`
WHERE `student_id` = 4;
--同理
DELETE FROM `student`
WHERE `name` = '小灰' AND `major` = '物理';

DELETE FROM `student`
WHERE `score` < 60;        --< > <= >= = <> 

DELETE FROM `student`;    --删除所有资料

取得资料

SELECT * FROM `student`;    --取得表格中的资料，* 取得所有属性的资料
SELECT `name` FROM `student`;    --只会回传`name`属性
--同理
SELECT `name`,`major` FROM `student`;
--根据成绩排序回传资料
SELECT * FROM `student` ORDER BY `score`;    --默认ASC(由低到高，升序)
SELECT * FROM `student` ORDER BY `score` DESC;    --DESC(由高到低,降序)
SELECT * FROM `student` ORDER BY `score`,`student_id`;     --先按`score`再按`student_id`排序
--只回传前三个资料
SELECT * FROM `student` LIMIT 3; 
--同理
SELECT * FROM `student` ORDER BY `score` LIMIT 2; 
SELECT * FROM `student` ORDER BY `score` DESC LIMIT 3; 
--同理
SELECT * FROM `student` WHERE `major` = '英语' OR `score` > 20; 
SELECT * FROM `student` WHERE `major` = '英语' OR `score` <> 70 LIMIT 2; 

SELECT * FROM `student` WHERE `major` IN ('英语' ,'历史','生物');
--相当于
SELECT * FROM `student` WHERE `major` = '英语' OR `major` = '历史' OR `major` = '生物';

创建公司资料库表格

CREATE DATABASE `sql_tutorial`;
SHOW DATABASES;
USE `sql_tutorial`;

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 `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
);

新增公司资料

INSERT INTO `branch` VALUE(1, '研发',NULL);
INSERT INTO `branch` VALUE(2, '行政',NULL);
INSERT INTO `branch` VALUE(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 `work_with` VALUES(206,400,'70000');
INSERT INTO `work_with` VALUES(207,401,'24000');
INSERT INTO `work_with` VALUES(208,402,'9800');
INSERT INTO `work_with` VALUES(208,403,'24000');
INSERT INTO `work_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`;
若为性别，且不重复
SELECT DISTINCT `sex` FROM `employee`;    --DISTINCT 取消重复

aggregate functions 聚合函数

--1.取得员工人数
SELECT COUNT(*) FROM `employee`;    --5
SELECT COUNT(sup_id) FROM `employee`;    --4
--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`;
--6.取得薪水最低的员工
SELECT MIN(`salary`) FROM `employee`;

wildcards 万用字元

%代表多个字元，_代表一个字元

--1.取得电话号码位数是335的客户
SELECT * FROM `client` WHERE `phone` LIKE '%335';
--若开头是254
SELECT * FROM `client` WHERE `phone` LIKE '254%';
--若中间有354
SELECT * FROM `client` WHERE `phone` LIKE '%354%';
--2.取得姓艾的客户
SELECT * FROM `client` WHERE `client_name` LIKE '艾%';
--3.取得生日在12月的员工
SELECT * FROM `employee` WHERE `brith_date` LIKE '_____12%';

union 联集

这里或许可以叫做并集？

--1.员工名字 union 客户名字
SELECT `name` AS `total_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`;
--2.员工id + 员工名字 union 客户id + 客户名字
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 `work_with`;

join 连接

INSERT INTO `branch` VALUES(4,'偷懒',NULL);

--取得所有部门经理的名字
SELECT *
FROM `employee`
JOIN `branch`
ON `emp_id` = `manager_id`;
--这样取得的数据属性多达十种比较冗杂，我们可以这样筛选必要属性
SELECT `employee`.`emp_id`,`employee`.`name`,`branch`.`branch_name`    --这样出来的数据就只有三种
FROM `employee`
JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;    --为了防止两个表格连接的时候有相同属性导致重名，我们可以在属性前加上表格名称作为前缀

SELECT `employee`.`emp_id`,`employee`.`name`,`branch`.`branch_name`
FROM `employee` LEFT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
--LEFT JOIN 不管条件是否成立都会把左边表格的所有数据回传给我们，而右边的表格则是要条件成立才会回传给我们，不成立则都是NULL,在这里`employee`是左表格，`branch`是右表格
--RIGHT JOIN同理
SELECT `employee`.`emp_id`,`employee`.`name`,`branch`.`branch_name`
FROM `employee` RIGHT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;

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 `work_with`
    WHERE `total_sales` > 50000
);

ON DELETE

简单来说ON DELETE SET NULL就是对应不到就把他设置成NULL，ON DELETE CASCADE就是对应不到就一起删掉.

FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL    
--如果小绿离职，`emp_id`207不存在，`manager_id`设置为NULL
FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,
FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE
----如果小绿离职，`emp_id`207不存在，`work_with`表格中那一行一起删掉,整笔资料删掉

DELETE FROM `employee`
WHERE `emp_id` = 207;

python 链接 MySQL

这里我使用VS Code来连接，直接在终端输入python.exe -m pip install --upgrade pip先把pip更新到最新版,再输入python -m pip install mysql-connector-python安装mysql-connector。

import mysql.connector

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

cursor = connection.cursor()

# 创建资料库
# cursor.execute("CREATE DATABASE `qq`;")

# 取得所有资料库的名称
# cursor.execute("SHOW DATABASES;")
# records = cursor.fetchall()
# for r in records:
#     print(r)

# 选择资料库
# cursor.execute("USE `sql_tutorial`;")

#取得部门表格所有资料
# cursor.execute('SELECT * FROM `branch`;')

# records = cursor.fetchall()
# for r in records:
#     print(r)

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

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

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

cursor.close()
connection.commit() #若要修改资料则必须加上
connection.close()

这样我们就可以通过编写Python代码来修改我们的资料库了，当然MySQL Workbench 中可以同步查看这里不再一一列举。