之前也系统的学习过sql语句,不过因为工作中很少用到,也忘得差不多了,这次想重新温习一遍,又不想花费太长时间,所以就去找视频学习,下面这个视频长达约3个小时,感觉老师讲的特别好,通俗易懂,为了方便学习重复温习,便记录在这里。
【数据库】SQL 3小时快速入门 #数据库教程 #SQL教程 #MySQL教程 #database#Python连接数据库_哔哩哔哩_bilibili
sql基础
主键和外键
主键用于唯一标识表中的每一行数据,外键用于建立不同表之间的关系,确保数据的完整性和一致。
创建数据库
CREATE DATABASE `sql_tutorial`;
SHOW DATABASES;
DROP DATABASE `sql_tutorial`;
创建表格
USE `sql_tutorial`;
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY, # 设置student_id属性为主键
`name` VARCHAR(20),
`major` VARCHAR(20)
);
设置主键的另一种方式
USE `sql_tutorial`;
CREATE TABLE `student`(
`student_id` INT,
`name` VARCHAR(20),
`major` VARCHAR(20),
PRIMARY KEY(`student_id`) # 设置student_id属性为主键
);
DESCRIBE `student`;
DROP TABLE `student`;
修改表格
# 修改表格:在表格中新增gpa属性
ALTER TABLE `student` ADD gpa DECIMAL(3,2);
### 修改表格:在表格中删除gpa属性
ALTER TABLE `student` DROP COLUMN gpa;
在表格中插入数据
# 在表格中插入数据,按照表格中的属性顺序依次添加
INSERT INTO `student` VALUES(1,'小白','历史');
INSERT INTO `student` VALUES(2,'小黑','历史');
# 属性值可以为空
INSERT INTO `student` VALUES(3,'小绿',NULL);
# 列出表格中的全部数据
SELECT * FROM `student`; #从全部student表格搜索
# 可以自己定义顺序,如果没有写某个属性,就把该行资料的该属性定义为NULL
INSERT INTO `student`(`name`,`major`,`student_id`) VALUES('小红','英语',5);
# 不填某个属性的值,则默认为NULL
INSERT INTO `student`(`major`,`student_id`) VALUES('英语',5);
constraint 限制
CREATE TABLE `student`(
`student_id` INT AUTO_INCREMENT, #id自动增加,不用在输入时手动写id
`name` VARCHAR(20) NOT NULL, #NOT NULL为限制,该限制为属性值不为NULL
`major` VARCHAR(20) UNIQUE #UNIQUE表示该属性值要唯一,
# `major` VARCHAR(20) DEFAULT '历史' 表示major属性的默认值为'历史',插入数据时没有输入具体的major属性值时,默认插入的属性值为'历史'。
PRIMARY KEY(`student_id`)
);
修改、删除资料 UPDATE
SET SQL_SAFE_UPDATES = 0; #关闭预设自动更新
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY,
`name` VARCHAR(20),
`major` VARCHAR(20),
`score` INT
);
SELECT * FROM `student`;
# 更新student表格:将major属性为英语的更新为英语文学
UPDATE `student`
SET `major` = '英语文学'
WHERE `major` = '英语';
# 将student_id为3的学生的major改为生物
UPDATE `student`
SET `major` = '生物'
WHERE `student_id` = 3;
# where支持使用逻辑操作符OR AND NOT等,也支持使用比较操作符<、>、>=、<=、<>不等于,比如下面的将major值为生物和化学的学生更新为生化
UPDATE `student`
SET `major` = '生化'
WHERE `major` = '生物' OR `major`= '化学';
# 可以同时改变多个属性值,比如修改student_id为1的学生的name值为小灰,major值为物理
UPDATE `student`
SET `name` = '小灰', `major` = '物理'
WHERE `student_id` = 1;
#不写条件where,就会把所有的major都修改为物理
UPDATE `student`
SET `major` = '物理';
# 删除 DELETE 把编号为4的学生删掉
DELETE FROM `student`
WHERE `student_id` = 4;
# 不写条件where即全部删除
DELETE FROM `student`
取得资料 SELECT
# 取得所有资料*表示所有
SELECT * FROM `student`;
SELECT `name`, `major` FROM `student`;
#排序 ORDER BY 由低到高;ORDER BY 属性 DESC 由高到低;ORDER BY 属性1,属性2 先按属性1排,如果1一样,再按2排序
SELECT *
FROM `student`
ORDER BY `score`; # 默认升序由低到高排序ASC, 如果希望降序排列需要加关键词DESC # ORDER BY `score` DESC;
SELECT *
FROM `student`
ORDER BY `score`, `student_id`; # 排序也可根据多个属性进行排序,比如先按照score属性排序,当score属性值相同时,再在score排序基础上按照student_id属性排序,比如当小白(student_id为1)和小绿(student_id为3)的得分都为20时,则根据student_id排序,最终排序结果小白<小绿
# LIMIT n 限制搜索返回的结果集中的行数,n为正整数,表示返回前n行数据
SELECT *
FROM `student`
ORDER BY `score`
LIMIT 2;
#用WHERE条件筛选,比如筛选满足条件为`major`= '英语'和`score`不等于20的两行数据
SELECT *
FROM `student`
WHERE `major`= '英语' AND `score` <> 20
LIMIT 2;
SELECT * FROM `student`
WHERE `major` IN('英语' , '历史' , '生物');#等价于用or连接WHERE `major`= '英语' OR `major`='历史' OR `major`='生物';
ORDER BY `score`;
LIMIT 2;
sql进阶
创建公司资料表格
# 创建员工表格
CREATE TABLE `employee`(
`emp_id` INT PRIMARY KEY, # 设置为主键
`name` VARCHAR(20),
`brith_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 #FOREIGN KEY表示外键;REFERENCES 表示外键接的表格名,再加外接的表格的primary key
);
# 对员工表格补上外键foreign key,
ALTER TABLE `employee`
ADD FOREIGN KEY(`branch_id`) # 加外键 branch_id属性
REFERENCES `branch`(`branch_id`) #对应到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)
);
# 创建works_with表格
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
);
新增公司资料
# 有foreign key时,对应的表没有相应资料(此时`employee`(`emp_id`)并不存在),直接输入会报错,要用NULL代替
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);
#把原本branch的manager_id=NULL改回来
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(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`;
# SELECT DISTINCT `sex` FROM `employee`; DISTINCT 对返回的数据行去重,即不会返回重复的数据行
聚合函数 aggregate functions
1 取得员工数目
SELECT COUNT(*) FROM `employee`; # 员工表格中有几行数据
SELECT COUNT(`sup_id`) FROM `employee`; # 员工表格中sup_id属性值不为NULL的有几行
2 取得所有出生于 1970-01-01 之后的女性员工人数
SELECT COUNT(*)
FROM `employee`
WHERE `brith_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'; # %335表示尾号为335,335%表示开头为335,%335%表示中间包含数字335
2 取得姓艾的员工
SELECT *
FROM `client`
WHERE `client_name` LIKE '艾%';
3 取得生日在12月的员工
SELECT *
FROM `employee`
WHERE `brith_date` LIKE '_____12%'; #_表示一个字元,_____12%表示前面有5个字元,接着为12,后面有任意个字元,可以匹配到brith_date中的2000-12-19
union 并集
1 员工名字union客户名字
SELECT `name`
FROM `employee`
UNION
SELECT `client_name`
FROM `client`;
# 合并时属性数目一致,当employee中的两个属性(`name`, `sex`)和client_name中的一个属性(`name`)合并会报错
# 合并时属性的数据类型也应该相同,不能把字符串的name和int类型的salary合并
2 员工id+员工名字union客户id+客户名字
SELECT `emp_id`, `name`
FROM `employee`
UNION
SELECT `client_id`, `client_name`
FROM `client`;
# 返回的数据中的属性名称为`emp_id`, `name`
# 修改返回的属性名称
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 works_with;
join 连接
取得所有部门经理的名字
SELECT *
FROM employee
JOIN `branch`
ON `emp_id` = `manager_id`;
# 上面代码会返回两个表中所有的属性
# 只返回 `emp_id`, `bname`, `branch_name`
SELECT `emp_id`, `bname`, `branch_name`
FROM employee
JOIN `branch`
ON `emp_id` = `manager_id`;
# 当使用join链接两个表格时,有时两个表格中会有相同属性,需要用表格.属性名称进行区分,区分属性来源于哪个表格
SELECT `employee`.`emp_id`, `employee`.`bname`, `branch`.`branch_name`
FROM employee
JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
# LEFT JOIN 不管条件是否成立,会把左边表格中的全部数据回传,右边表格只回传条件成立的数据,不成立的地方就显示NULL
SELECT `employee`.`emp_id`, `employee`.`bname`, `branch`.`branch_name`
FROM employee LEFT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
# RIGHT JOIN 不管条件是否成立,会把右边表格中的全部数据回传,左边表格只回传条件成立的数据,不成立的地方就显示NULL
SELECT `employee`.`emp_id`, `employee`.`bname`, `branch`.`branch_name`
FROM employee RIGHT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
subquery 子查询(在一个查询语句嵌入一个查询语句)
1 找出研发部门的经理名字:先找研发部门的manager_id(放在括号里面的,就是子查询的部分),再用id进行查找名字
SELECT `name`
FROM `employee`
WHERE `emp_id` = (
SELECT `manager_id`
FROM `branch`
WHERE `branch_name` = '研发'
);
# 查询的执行是先执行子查询(查找研发部门的manager_id),获取其结果,然后将该结果用于主查询条件的匹配,最终返回满足条件的数据
2 找出对单一位客户销售金额超过50000的员工名字
SELECT `name`
FROM `employee`
WHERE `emp_id` IN (
SELECT `emp_id`
FROM `works_with`
WHERE `total_sales` > 50000
);
# 子查询回传结果不止一个,需要用in
ON DELETE
ON DELETE SET NULL
# 创建部门表格
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 #FOREIGN KEY表示外键;REFERENCES 表示外键接的表格名,再加外接的表格的primary key
# ON DELETE SET NULL:当manager_id中的对应的employee表中的emp_id的某行被删除后,manager_id对应值被设置为NULL
);
ON DELETE CASCADE
# 创建works_with表格
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:employee表中的emp_id的某行被删除后,works_with中的emp_id也会被删除,注意当emp_id为主键同时为外键时时不能设置为ON DELETE SET NULL,因为主键不能为NULL
FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE
);
测试ON DELETE
DELETE FROM `employee`
WHERE `emp_id` = 207;
SELECT *
FROM `branch`;# 会发现该表格中对应值变为NULL
SELECT *
FROM `works_with`;# 会发现该表格中对应行已被删除
python 连接mysql库
创建数据库
# creat_db.py
# 安装 MySQL Connector/Python 库
# pip install mysql-connector-python
import mysql.connector
# 连接 MySQL 数据库
connection = mysql.connector.connect(
host='localhost',
port='3306',
user='root',
password='password'
)
# 创建游标对象
cursor = connection.cursor()
# 创建数据库
cursor.execute("CREATE DATABASE G9")
# 显示所有数据库
cursor.execute("SHOW DATABASES")
records = cursor.fetchall()
for r in records:
print(r)
# 切换到创建的数据库
cursor.execute("USE G9")
# 创建表
cursor.execute('CREATE TABLE G9 (GG INT)')
# 关闭游标和连接
cursor.close()
connection.close()
查询数据
# select_data.py
import mysql.connector
# 连接 MySQL 数据库
connection = mysql.connector.connect(
host='localhost',
port='3306',
user='root',
password='password',
database='sql_tutorial'
)
# 创建游标对象
cursor = connection.cursor()
# 执行查询
cursor.execute('SELECT * FROM branch;')
records = cursor.fetchall()
for r in records:
print(r)
# 关闭游标和连接
cursor.close()
connection.close()
修改数据
# update_data.py
import mysql.connector
# 连接 MySQL 数据库
connection = mysql.connector.connect(
host='localhost',
port='3306',
user='root',
password='password',
database='sql_tutorial'
)
# 创建游标对象
cursor = connection.cursor()
# 插入数据
cursor.execute("INSERT INTO branch VALUES (5, 'gg', NULL)")
# 修改数据
cursor.execute('UPDATE branch SET manager_id = NULL WHERE branch_id = 4')
# 删除数据
cursor.execute("DELETE FROM branch WHERE branch_id = 5")
# 提交事务
connection.commit() # 插入修改删除必须要有这个commit才能修改表格成功
# 关闭游标和连接
cursor.close()
connection.close()