不是知识的生产者，只是教程的搬运工。原B站视频链接如下，建议1.5倍速使用。
【数据库】SQL 3小时快速入门 #数据库教程 #SQL教程 #MySQL教程 #database#Python连接数据库_哔哩哔哩_bilibili
根据视频所敲代码如下，有一些自己添加的注释，纯小白
-- https://www.bilibili.com/video/BV1PT4y1e7UU/?share_source=copy_web&vd_source=db6a78f582ae44efdbe2dd6d7395bc83
CREATE DATABASE `database` ; -- 创建数据库
SHOW DATABASES; -- 显示所有数据库
DROP DATABASE `database`; -- 删除数据库、表
-- constraints 限制、约束
CREATE DATABASE `sql_tutorial`; -- ctrl+enter 快速运行，` Tab上面的键
USE `sql_tutorial`;
CREATE TABLE `student`(
`student_id` INT AUTO_INCREMENT, -- AUTO_INCREMENT自动增加序号
`name` VARCHAR(20), -- 可以加特定约束，比如 not null就限制了`name`里必须要有内容，UNIQUE属性限制唯一性，DEFAULT后可跟预设值
`major` VARCHAR(20),
PRIMARY KEY(`student_id`)
);
DROP TABLE `student`;
DESCRIBE `student`; -- 查看表格
ALTER TABLE `student` ADD gpa DECIMAL(3,2); -- 新增表格属性，删除将ADD换成DROP COLUMN 即可
ALTER TABLE `student` DROP COLUMN gpa; -- DECIMAL(3,2) 3位数，小数点后有2位
SELECT * FROM `student`; -- 搜寻表格所有资料
INSERT INTO `student` VALUES(1, '小白', '历史'); -- 表格填值
INSERT INTO `student` VALUES(2, '小黑','生物'); -- 也可以自己决定填入顺序，在`student`后加(`name`,`major`,`student_id`)
INSERT INTO `student` VALUES(3, '小绿', NULL);-- 不填默认null
-- 修改、删除资料
SET SQL_SAFE_UPDATES = 0; -- 删除预设更新
-- DROP TABLE `student`;
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20),
`major` VARCHAR(20),
`score` INT
);
-- INSERT INTO `student`(`name`,`major`,`score`) VALUES('小白', '英语', 50);
-- INSERT INTO `student`(`name`,`major`,`score`) VALUES('小黄', '生物', 90);
-- INSERT INTO `student`(`name`,`major`,`score`) VALUES('小绿', '历史', 70);
-- INSERT INTO `student`(`name`,`major`,`score`) VALUES('小蓝', '英语', 80);
-- INSERT INTO `student`(`name`,`major`,`score`) VALUES('小黑', '化学', 20);
SELECT * FROM `student`; -- 搜寻表格所有资料;
UPDATE `student`
SET `major`='生化'
WHERE `major` = '生物' OR `major` = '化学'; -- WHERE 是限定条件属性，不加一列均变成生化
UPDATE `student`
SET `name`= '小灰', `major`='物理'
WHERE `student_id`=1;
DELETE FROM `student`
WHERE `student_id` = 4; -- 删除资料，WHERE同样可以写多个条件，不写则删除所有资料
DELETE FROM `student`
WHERE `score` < 60;
-- 取得资料
SELECT `name`,`major` FROM `student`; -- 选择性获取资料
SELECT * FROM `student` ORDER BY `score` DESC; -- 获取资料并排序，DESC由高到低 ASC由低到高，ORDER BY后也可跟多个属性，
SELECT * FROM `student` ORDER BY `score` DESC LIMIT 1; -- 由高到低取第一个资料
SELECT * FROM `student` WHERE `major` = '英语' OR `score` <> 70; -- <>表示不等于
-- 创建公司资料库表格
DROP TABLE `student`;
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 -- ON DELETE SET NULL:当某个emp_id不存在时，manager_id为NULL
);
ALTER TABLE `employee`
ADD FOREIGN KEY (`branch_id`)
REFERENCES `branch`(`branch_id`)
ON DELETE SET NULL; -- `employee`表中外键连接`branch`中的值
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, -- ON DELETE CASCADE:当某个emp_id不存在时，整笔资料都删掉
FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE -- FOREIGN KEY这里不能用 ON DELETE NULL
);
-- 新增公司资料
INSERT INTO `branch` VALUES(1,'研发',NULL); -- 如果这里直接新增employee数据会因为外键无值而报错，所以要先填branch，并且外键部分填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,2,207);
INSERT INTO `employee` VALUES(210,'小蓝','1925-11-10','F',84000,1,207);
-- DELETE FROM `employee`; -- 清除`employee`所有资料
-- SELECT * FROM `employee`;
UPDATE `branch` SET `manager_id` =206 WHERE `branch_id` = 1;-- 将branch中外键的null值改过来
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');
-- 取得所有员工性别且不重复
SELECT DISTINCT `sex` FROM `employee`; -- 不重复通过DISTINCT实现
-- aggregate function 聚合函数
-- 1.取得员工人数
SELECT COUNT(*) FROM `employee`; -- COUNT()里面也可以加属性
-- 2.取得所有出生于1970-01-01之后的女性员工人数
SELECT * 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'; -- LIKE是模糊查询，254%也可以，%代表多个字元
-- 2.取得姓艾的客户
SELECT * FROM `client` WHERE `client_name` LIKE '艾%';
-- 3.取得生日在12月的员工
SELECT * FROM `employee` WHERE `birth_date` LIKE '_____12%'; -- 月份之前有5个字符
-- union 联集
-- 1.员工名字 union 客户名字
SELECT `name` FROM `employee`
UNION
SELECT `client_name` FROM `client` ; -- union两边的属性应该是一样多的，并且类型相同
-- 2.员工id + 员工名字 union 客户id +客户名字
SELECT `emp_id` AS `total_id`,`name` AS `total_name` FROM `employee` -- AS后为重命名属性名
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 连接
INSERT INTO `branch` VALUES(4,'偷懒',NULL);
-- 取得所有部门经理的名字
SELECT `emp_id`,`name`,`branch_name` FROM `employee`
JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`; -- 在属性前面加上表格名称可以区分不同表格有同一属性名称的情况
SELECT `emp_id`,`name`,`branch_name`
FROM `employee` LEFT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`; -- LEFT JOIN会把左边`employee`表格所有的内容都输出，右边`branch` 满足条件才输出，不满足为NULL
-- 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
); -- 这里用IN因为判断里面结果可能不止一个
最后python和MySQL连接的代码：
# import mysql.connector
# connection = mysql.connector.connect(host='localhost',
# port='3306',
# user='root',
# password='257101pyD@') # 对应内容在MySQL Workbench home页面
# 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`;") # 也可直接在connection中connect（）加入database，如下所示
# cursor.execute("CREATE TABLE `qq`(qq INT);")
#
# cursor.close() # 关闭连接
# connection.close()
import mysql.connector
connection = mysql.connector.connect(host='localhost',
port='3306',
user='root',
password='257101pyD@',
database='sql_tutorial') # 对应内容在MySQL Workbench home页面
cursor = connection.cursor()
# 取得部门表格所有资料
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()
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
