【数据库】SQL基础指令笔记

1. 创建和删除数库

create database `sql_tutorial`;	/*创建数据库*/
drop database `sql_tutorial`;	/*删除数据库*/
show databases;
use `sql_tutorial`;

2.创建公司资料库表格

2.1创建员工表格

-- 创建员工表格
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`;

2.2创建部门表格

-- 创建部门表格
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
);

2.3 表格关联，添加外键

-- 给员工表格添加外键
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;

2.4 创建客户表格

-- 创建客户表格
create table `client`(
	`client_id` int primary key,
    `client_name` varchar(20),
    `phone` varchar(20)
);

2.5 创建works_with表格

-- 创建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
);

3. 新增公司资料

show databases;
/*部门表格未创建，导致branch_id外键设定失败*/
insert into `employee` values(206, "小黄", "1998-10-08", "F", 5000, 1, NULL); 

3.1 插入部门数据

insert into `branch` values(1, "研发", 206);
insert into `branch` values(2, "行政", 207);
insert into `branch` values(3, "咨询", 208);

update `branch` set `manager_id` = 206 where `branch_id` = 1 and `branch_name` = "研发";
update `branch` set `manager_id` = 207 where `branch_id` = 2 and `branch_name` = "行政";
update `branch` set `manager_id` = 208 where `branch_id` = 3 and `branch_name` = "咨询";
select * from `branch`;

3.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);
select * from `employee`;

3.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");
select * from `client`;

3.4 插入客户关系数据

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 * from `works_with`;

4. 查询指令

4.1 常用查询指令示例

-- 取得所有员工的名字
select `name` from `employee`;

-- aggregate functions 聚合函数
-- 1.取得公司人数
select count(*) 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 max(`salary`) from `employee`;
-- 6.取得薪水最低的员工
select min(`salary`) from `employee`;

4.2 万用字元%和_

-- wildcards万用字元 %代表多个字元，_代表一个字元
-- 1.取得电话号码尾数时335的客户
select * from `client` where `phone` like "%335";
-- 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%";

4.3 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 `works_with`;

4.4 join连接

insert into `branch` values(4, "偷懒", null);
-- 取得所有部门经理的名字
select * from `employee` join `branch` on `emp_id` = `manager_id`;
select `emp_id`, `name`, `branch_name` from `employee` 
	join `branch` on `employee`.`emp_id` = `branch`.`manager_id`;

4.5 sbuquery 子查询

-- 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
);

原视频链接： #数据库教程 # SQL 3小时快速入门