SQL入门一_10:33:57insert into student(name, birthdate, major-CSDN博客

本文链接：https://blog.csdn.net/qq_39705798/article/details/129142683
SQL:
1、sql快速入门，可以看这个视频 → 【【数据库】SQL 3小时快速入门 #数据库教程 #SQL教程 #MySQL教程 #database#Python连接数据库-哔哩哔哩】 https://b23.tv/yxVuFqS
CREATE DATABASE `sql_tutorial`;
SHOW DATABASES;
USE `sql_tutorial`; # 使用数据库

CREATE TABLE `student`(
	`student_id` INT AUTO_INCREMENT, # 自动+1，之后不需要写student_id
    `name` VARCHAR(20) NOT NULL, # 不可以为空
    # `major` VARCHAR(20) UNIQUE, # 值独一无二
    `major` VARCHAR(20) DEFAULT '历史', # 插入时必须指定列名，要不然显示null
     PRIMARY KEY(`student_id`)
);

DESCRIBE `student`;  # 打印表格
DROP TABLE `student`;  # 删除表格

ALTER TABLE `student` ADD gpa DECIMAL(3,2); # 插入一列
ALTER TABLE `student` DROP COLUMN gpa; # 删除gpa列

SELECT * FROM `student`; # 搜寻表格student全部信息

INSERT INTO `student` VALUE(1, '小白', '历史');
INSERT INTO `student` VALUE(2, '小黑', '生物');
INSERT INTO `student`(`student_id`,`name`) VALUE(4, '小红'); # NULL代表无 也可不填
INSERT INTO `student`(`name`, `major`, `student_id`) VALUE('小蓝', '英语', 4);


INSERT INTO `student` VALUE(1, NULL, '历史');
INSERT INTO `student`(`name`, `major`) VALUE('小白', '历史');

# --修改、删除资料
USE `sql_tutorial`; # 使用数据库
SET SQL_SAFE_UPDATES = 0; # 预设模式关闭
DROP TABLE `student`;
CREATE TABLE `student`(
	`student_id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(20),
    `major` VARCHAR(20),
    `score` INT
);

SELECT * FROM `student`;
INSERT INTO `student`(`name`, `major`, `score`) VALUE('小白', '英语', 50);
INSERT INTO `student`(`name`, `major`, `score`) VALUE('小黄', '生物', 90);
INSERT INTO `student`(`name`, `major`, `score`) VALUE('小绿', '历史', 70);
INSERT INTO `student`(`name`, `major`, `score`) VALUE('小蓝', '英语', 80);
INSERT INTO `student`(`name`, `major`, `score`) VALUE('小黑', '化学', 20);
SELECT * FROM `student`;

## 把英语替换为英语文学
UPDATE `student` # 更新student表格里的资料
SET `major` = '英语文学' # 英语文学这里是单引号
WHERE `major` = '英语'; # 结尾一定要有分号

## 把student_id = 3 的major改为生物
UPDATE `student`
SET `major` = '生物'
WHERE `student_id` = 3;
SELECT * FROM `student`;

## 把生物和化学合并为生化系
UPDATE `student`
SET `major` = '生化系'
WHERE `major` = '生物' OR `major` = '化学';
SELECT * FROM `student`;

## 把student_id =1 改为name=小灰，major=物理
UPDATE `student`
SET `name` = '小灰',`major` = '物理'
WHERE `student_id` = 1;
SELECT * FROM `student`;

# 让所有major=物理
UPDATE `student`
SET `major` = '物理'; # 不写where 这里要以分号结尾
SELECT * FROM `student`;

# 删除资料
# 删除`student_id` = 4
DELETE FROM `student`
WHERE `student_id` = 4;
SELECT * FROM `student`;

# 删除 `name` = '小灰' AND `major` = '物理'
DELETE FROM `student`
WHERE `name` = '小灰' AND `major` = '物理';
SELECT * FROM `student`;

# 删除`score` < 60
DELETE FROM `student`
WHERE `score` < 60;
SELECT * FROM `student`;

# 删除表里信息，表还存在
DELETE FROM `student`;
SELECT * FROM `student`;

# --取得资料
# 取name
SELECT `name` FROM `student`;
# 取name 和major
SELECT `name`,`major` FROM `student`;
# 取全部资料
SELECT * FROM `student`;
# 根据分数排序从低到高
SELECT * FROM `student` ORDER BY `score`;# 或者 SELECT * FROM `student` ORDER BY `score` ASC;
# 根据分数排序从高到低
SELECT * FROM `student` ORDER BY `score` DESC;

# 把小白的score改为20
UPDATE `student`
SET `score` = 20
WHERE `name` = '小白';
SELECT * FROM `student`;
# 以成绩排序，成绩相同以student_id排序
SELECT * 
FROM `student` 
ORDER BY `score`,`student_id`;
# 只要前3行资料
SELECT * 
FROM `student`
ORDER BY `score` 
LIMIT 3;
# 查找`name` = '小白' AND `major` = '英语'
SELECT *
FROM `student`
WHERE `name` = '小白' AND `major` = '英语';
# 查找分数不等于70 或者major=英语
SELECT *
FROM `student`
WHERE `score` <> 70 OR `major` = '英语'
LIMIT 2;

# 查找major为历史或英语或生物
SELECT *
FROM `student`
WHERE `major` IN('历史','英语','生物');


# 创建公司资料库表格
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
);
DESCRIBE `employee`;

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 # `employee`中的emp_id不存在时，`manager_id`将会被设为null
);
DESCRIBE `branch`;

# 设置foreign key
ALTER TABLE `employee`
ADD FOREIGN KEY(`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;
# 创建client表格
CREATE TABLE `client`(
	`client_id` INT PRIMARY KEY,
    `client_name` VARCHAR(20),
    `phone` VARCHAR(20)
);
# 创建work_with表格
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,  # `employee`中的emp_id不存在时，work_with中的emp_id将会跟着删除
    FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE
);

# 新增公司资料
# 1 先新增branch资料
INSERT INTO `branch` VALUES(1, '研发', NULL);
INSERT INTO `branch` VALUES(2, '行政', NULL);
INSERT INTO `branch` VALUES(3, '资讯', NULL);
# 2 新增员工资料
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);
# 3 更新branch表格里的manager_id
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;
# 4 新增client
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');
# 5 新增works_with
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`;
# 6 取得员工的性别，没有重合的用distinct
SELECT DISTINCT `sex`  FROM `employee`;

#聚合函数aggregate funciton
# 1 取得员工人数
SELECT COUNT(*) FROM `employee`;
SELECT COUNT(`sup_id`) FROM `employee`;
# 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 `name` 
FROM `employee`
ORDER BY `salary` DESC
LIMIT 1;
# 求最高薪水
SELECT MAX(`salary`)
FROM `employee`;
# 6 取得最低薪水
SELECT MIN(`salary`)
FROM `employee`;

-- wildcards 万用字元  % 代表多个字元，_ 代表一个字元
# 1 取得电话号码尾数是335的客户
SELECT * 
FROM `client`
WHERE `phone` LIKE '%335';
## 号码中间有354
SELECT * 
FROM `client`
WHERE `phone` LIKE '%354%';
# 2 取得姓艾的客户
SELECT * 
FROM `client`
WHERE `client_name` LIKE '艾%';
# 3 取得生日在12月的员工
SELECT * 
FROM `employee`
WHERE `birth_date` LIKE '%-12-%';
# 或者
SELECT * 
FROM `employee`
WHERE `birth_date` LIKE '_____12%';

-- union 联集
# 1 员工名字 union 客户名字
SELECT `name`
FROM `employee`
UNION
SELECT `client_name`
FROM `client`
UNION
SELECT `branch_name`
FROM `branch`
# 2 员工id + 员工名字 union 客户id + 客户名字
SELECT `emp_id`, `name`
FROM `employee`
UNION
SELECT `client_id`, `client_name`
FROM `client`;
## 改换名字
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 `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee`
JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`; # 连接条件
-- LEFT JOIN 不管判断条件成立与否，左边表格都回传回来
SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` LEFT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`; # 连接条件
-- 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的员工名字
## 因为返回的资料不止一行，所以不能用= ，要用in
SELECT `name`
FROM `employee`
WHERE `emp_id` IN (
	SELECT `emp_id`
	FROM `work_with`
	WHERE `total_sales` > 50000
);

-- on delete
SELECT * FROM `work_with`;
# 删除`emp_id` = 207
DELETE FROM `employee`
WHERE `emp_id` = 207;
SELECT * FROM `client`;