目录
写在前面
本篇博客是笔者学习SQL时所记录的笔记,对应的学习视频链接我放在下面了。
【数据库】SQL 3小时快速入门 #数据库教程 #SQL教程 #MySQL教程 #database#Python连接数据库_哔哩哔哩_bilibili
由于老师讲解时时常会不断修改已经写好的代码,为了完整记录老师所写下的代码及笔记,我将笔记以视频的时间顺序记录下来。
数据类型
将自动补全改为默认大写字母的方法
MySQLWorkbench -> preferences -> SQL Editor -> Query Editor -> Use UPPERCASE keywords on completion
视频 0 ~ 1'11'30 对应代码及笔记
CREATE DATABASE `sql_tutorial`; #创建资料库 反引号:用于避免自定义的名字和关键字冲突
SHOW DATABASES ; # 一般关键字都是用大写来写,但用小写字母也不会错
-- DROP DATABASE `sql_tutorial`; # 删除资料库
USE `sql_tutorial` ; #使用当前资料库
# 创建名为student的表格
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY AUTO_INCREMENT, # 属性+存储数据的类型 AUTO_INCREMENT 每次编号自增1,因此不用每次都传入编号了
`name` VARCHAR(20) NOT NULL, #属性之间用逗号隔开 NOT NULL的意思是所有数据该属性的值不能为空
`major` VARCHAR(20) UNIQUE # UNIQUE的意思是所有数据该属性的值不能相同
# 其他的constraint还包括 DEFAULT 预设值
);
DESCRIBE `student` ; #显示student的表格
DROP TABLE `student` ; #删除student的表格
ALTER TABLE `student` ADD `gpa` DECIMAL(3,2) ; #向表格中添加新属性 属性+存储数据的类型
ALTER TABLE `student` DROP COLUMN `gpa` ; #删除表格中的属性
#向表格中存入资料
#按照属性的顺序传入
#VARCHAR类型的数据需要用单或双引号引起来,数据之间用逗号隔开
INSERT INTO `student` VALUES(1, '小白', '历史') ;
INSERT INTO `student`(`name`,`major`, `student_id`) VALUES('小黑', '生物', 2) ; # 自定义传入数据的顺序
INSERT INTO `student` VALUES(3, '小绿', NULL) ; # NULL代表该属性没有资料的意思
INSERT INTO `student`(`name`, `major`) VALUES('小白', '英语') ; # AUTO_INCREMENT 效果展示
#搜寻student表格中的全部资料
SELECT * FROM `student` ;
删除、修改资料
-- 删除,修改资料
SET SQL_SAFE_UPDATES = 0 ; # 关闭workbench预设的更新模式
# 创建名为student的表格
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) ;
SELECT * FROM `student` ;
# 修改student中major的属性 当数据的mjaor是英语时,统一改成英语文学
# 条件语句
UPDATE `student`
SET `major` = '英语文学' WHERE `major` = '英语' ;
# 条件 或
UPDATE `student`
SET `major` = '生化系' WHERE `major` = '生物' OR `major` = '化学' ;
# 删除数据
DELETE FROM `student`
WHERE `student_id` = 4 ;
# 如果不加WHERE语句 则会删除student表格中的所有内容
获得资料
-- 获取资料
SET SQL_SAFE_UPDATES = 0 ; # 关闭workbench预设的更新模式
# 创建名为student的表格
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) ;
DROP TABLE `student` ;
# 返回结果由分数较低到分数较高排列
SELECT * FROM `student` ORDER BY `score`;
-- 当在末尾加上DESC 则会降序排列
SELECT * FROM `student` ORDER BY `score` DESC;
UPDATE `student`
SET `score` = 20 WHERE `student_id` = 1 ;
-- 先根据score排序,若score一样则按student_id排序
SELECT * FROM `student` ORDER BY `score`, `student_id`;
-- 限制返回资料的数量 只返回前三笔资料
SELECT * FROM `student` LIMIT 3 ;
-- 只返回主修为英语的资料
SELECT * FROM `student` WHERE `major` = '英语' ;
-- 返回分数不等于20的资料 且只返回前两笔
SELECT * FROM `student` WHERE `score` <> 20
LIMIT 2;
-- 返回major等于括号内科目的资料
SELECT * FROM `student` WHERE `major` IN ('历史','英语','生物') ;
创建公司资料库表格
-- 创建公司资料库表格
# 创建employee表格
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
);
-- 创建branch表格
CREATE TABLE `branch` (
`branch_id` INT PRIMARY KEY,
`branch_name` VARCHAR(20),
`manager_id` INT,
# 创建foreign key的方法
foreign key (`manager_id`) references `employee`(`emp_id`) on DELETE SET NULL
);
-- 补上在employee表格中未设置的foreign key
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 ;
-- 创建client表格
CREATE TABLE `client` (
`client_id` INT PRIMARY KEY,
`client_name` VARCHAR(20),
`phone` VARCHAR(20)
);
-- 创建workwith表格
CREATE TABLE `workwith` (
`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-29','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) ;
SELECT * FROM `branch` ;
SELECT * FROM `employee` ;
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,'阿猫','254354336') ;
INSERT INTO `client` VALUES(402,'旺财','254354337') ;
INSERT INTO `client` VALUES(403,'露西','254354338') ;
INSERT INTO `client` VALUES(404,'艾瑞克','254354339') ;
INSERT INTO `workwith` VALUES(206,400,'70000');
INSERT INTO `workwith` VALUES(207,401,'24000');
INSERT INTO `workwith` VALUES(208,402,'9800');
INSERT INTO `workwith` VALUES(209,403,'24000');
INSERT INTO `workwith` VALUES(210,404,'87940');
获取公司资料
-- 取得公司资料
-- 取得所有员工资料
SELECT * FROM `employee` ;
-- 取得所有客户的资料
SELECT * FROM `client` ;
-- 按薪水从低到高取得员工资料
SELECT * FROM `employee` ORDER BY `salary` ;
-- 取得薪水前三高的员工
SELECT * FROM `employee` ORDER BY `salary` DESC LIMIT 3 ;
-- 取得所有员工的名字
SELECT `name` FROM `employee` ;
-- 取得该公司员工共有几种性别
SELECT DISTINCT `sex` FROM `employee` ;
聚合函数
-- aggregate function 聚合函数
-- 取得员工人数
SELECT COUNT(*) FROM `employee` ;
-- 取得所有生于1970-01-01之后的女性员工的数量
SELECT COUNT(*) FROM `employee` WHERE `birth_date` > '1970-01-01' AND `sex` = 'F' ;
-- 取得所有员工的平均薪水
SELECT AVG(`salary`) FROM `employee` ;
-- 取得所有员工的薪水总和
SELECT SUM(`salary`) FROM `employee` ;
-- 取得薪水最高的员工
SELECT MAX(`salary`) FROM `employee` ;
-- 取得薪水最低的员工
SELECT MIN(`salary`) FROM `employee` ;
万用字元
-- 万用字元
-- % 代表多个字元
-- _ 代表一个字元
-- 取得电话号码尾号是335的客户
SELECT * FROM `client` WHERE `phone` LIKE '%335' ;
-- 取得姓艾的客户
SELECT * FROM `client` WHERE `client_name` LIKE '艾%' ;
-- 取得生日在12月的员工
SELECT * FROM `employee` WHERE `Birth_date` LIKE '%12%' ;
联集
-- UNION 联集
-- 员工名字 union 客户名字
-- 允许使用union的前提:数据个数和数据类型要相同
SELECT `name` FROM `employee`
UNION
SELECT `client_name` FROM `client` ;
-- 员工ID + 员工名字 union 客户ID + 客户名字 并改名为 total_name 和 total_id
SELECT `name` as `total_name`, `emp_id` as `total_id` FROM `employee`
UNION
SELECT `client_name`, `client_id` FROM `client` ;
连接
-- 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 `name` FROM `employee`
WHERE `emp_id` = (
SELECT `manager_id` FROM `branch`
WHERE `branch_name` = '科研'
);
🔚