MySQL、SQL简单入门
内容来自某站视频,可以去看一下,我总结了一下。
目标:创建一下几张表,并进行相关操作。
创建表
# 创建数据库
CREATE DATABASE company;
# 创建表
USE company;
# 员工表
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 # 主管
);
DESC 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
);
DESC branch;
#在没有创建表employee和branch时,表employee的外键:branch_id和sup_id均在创建表employee时不能创建。
# 创建表employee的外键
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;
# ON DELETE SET NULL :sup_id对应在emp_id中的内容,如果emp_id的内容不存在或者删除了,就会将sup_id对应的内容设置为null
# 客户表
CREATE TABLE client(
client_id INT PRIMARY KEY,
client_name VARCHAR(20),
phone VARCHAR(20)
);
# 员工-客户交易表格
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,
FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
);
# ON DELETE CASCADE :外键对应不到其链接的表对应的内容,就会删除表中对应的内容,举例:删除id为404的客户,work_with表找不到与其对应的404客户,就会将work_with表中对应404的数据删除
插入数据
# 在插入数据时,要考虑到外键所对应的主键里面是否存在内容使得两者对应
# 以下语句会出现一个错误,因为此表中的外键branch_id在其为主键的表branch中并没有对应的值,所以我们插入时要先考虑一下顺序。
# INSERT INTO employee VALUES(206,'小黄','1998-10-06','F',50000,1,NULL);
# 我们先插入表branch中的数据,也是因为主键和外键的问题我们先将manage_id一列插入空值
INSERT INTO branch VALUE
(1,'研发',NULL),
(2,'行政',NULL),
(3,'咨询',NULL);
# 员工表数据插入,两种方式
INSERT INTO employee VALUES(206,'小黄','1998-10-06','F',50000,1,NULL);
INSERT INTO employee VALUES(207,'小绿','1985-10-06','M',29000,1,206);
INSERT INTO employee VALUES
(208,'小黑','2000-10-06','M',35000,1,206),
(209,'小白','1937-10-06','F',39000,1,207),
(210,'小蓝','1978-10-06','F',84000,1,207);
# 添加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;
# 客户表数据
INSERT INTO client VALUES
(400,'甲',254354335),
(401,'乙',254352682),
(402,'丙',453668522),
(403,'丁',543385211),
(404,'戊',158635574);
# 订单交易表数据
INSERT INTO work_with VALUES
(206,400,70000),
(207,401,24000),
(208,400,8569),
(208,403,24000),
(210,404,85623);
基础查询
# 所有员工资料
SELECT * FROM employee;
# 所有客户资料
SELECT * FROM client;
# 按薪水低到高获取员工信息(默认升序,降序后面加上DESC)
SELECT * FROM employee
ORDER BY salary;
# 薪水前三员工
SELECT * FROM employee
ORDER BY salary DESC
LIMIT 3;
# 第三高薪水的员工
SELECT * FROM employee
ORDER BY salary DESC
LIMIT 2,1; # LIMIT m,n 表示从第n+1开始,取n条数据
SELECT * FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 2; # LIMIT m OFFSET n 表示跳过n条数据,取m条数据
# 取得所有员工的名字
SELECT name from employee;
# 去重
SELECT DISTINCT sex FROM employee;
聚合函数
# 聚合函数(WHERE后不能直接用)
# 取得员工人数
SELECT COUNT(*) FROM employee;
# 取得所有出生于1970-01-01之后的女性员工人数
SELECT COUNT(*) FROM employee
WHERE sex = 'F' AND birth_date > '1970-01-01';
# 所有员工平均薪水
SELECT AVG(salary) FROM employee;
# 所有员工薪水总和
SELECT SUM(salary) FROM employee;
# 薪水最高的员工
SELECT * FROM employee
ORDER BY salary DESC
limit 1;
# 薪水最低的员工,可参考最高薪水例子,只需将排序的规则改变一下即可,也可子查询
SELECT * FROM employee
WHERE salary = (
SELECT MIN(salary)
FROM employee
);
通配符
# 通配符
# % 代表多个字符,_代表一个字符
# 手机尾号为335的客户
SELECT * FROM client
WHERE phone LIKE '%335';
# 姓迪的客户
UPDATE client SET client_name ='迪迦'
WHERE client_id=400;
SELECT * FROM client
WHERE client_name LIKE '迪%';
# 生日在十月的员工
SELECT * FROM employee
WHERE birth_date LIKE '%10%'; # _____10%也可以
union合并查询结果
-- UNION 合并结果,查找的内容数量,属性要一致
# 员工和客户的名字
SELECT `name` FROM employee
UNION
SELECT client_name FROM client;
#员工和客户的名字以及id
SELECT `name` ,emp_id AS id
FROM employee
UNION
SELECT client_name ,client_id
FROM client;
# 可以连接多个
JOIN
# JOIN 多表查询
INSERT INTO branch value(4,'偷懒',NULL);
# 取得所有部门经理的名字
SELECT emp_id,`name`,branch_name FROM employee
JOIN branch
ON emp_id = manager_id;
# 存在不同表的属性名称相同的情况,便于区分可以在属性前面加上表的名字
SELECT
employee.emp_id,employee.`name`,branch.branch_name
FROM employee
JOIN branch
ON employee.emp_id = branch.manager_id;
# LEFT JOIN 无论条件是否满足,都将左边全部信息返回,右表满足条件返回,不满足为null
# RIGHT JOIN 同理
子查询 subquery
# 研发部门经理名字
SELECT `name` FROM employee
WHERE emp_id = (
SELECT manager_id
FROM branch
WHERE branch_name = '研发'
);
# total_sales大于50000的员工名字,结果可能不唯一就要用in
SELECT `name` FROM employee
WHERE emp_id IN (
SELECT emp_id
FROM work_with
WHERE total_sales > 50000
);
# 将查询的结果作为外部查询的条件