MySQL、SQL简单入门

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
);
# 将查询的结果作为外部查询的条件