SQL入门

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 中可以同步查看这里不再一一列举。


__EOF__

本文作者：bumper
本文链接：https://www.cnblogs.com/bumper/p/17985883.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！
posted @   bumper  阅读(80)  评论(0)  编辑  收藏  举报
相关博文：
·  python初阶语法
·  指针进阶（C语言）
·  SQL 快速入门
·  sql修炼笔记——入门篇2
·  011_我阿嬷都会_MySql
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 使用C#创建一个MCP客户端
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列1：轻松3步本地部署deepseek，普通电脑可用
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示
AI IDE Trae