/* 说明：我阿嬷都会 MySql 目录: 一 创建表 二 插入数据 三 查询 四 关联删除 五 python */
一 创建表
-- 创建表 : 员工 CREATE TABLE employee( emp_id INT PRIMARY KEY, name VARCHAR(20), birth_data DATE, sex VARCHAR(1), salary INT, branch_id INT, sup_id INT )CHARACTER SET utf8 COLLATE utf8_general_ci; -- 创建表 : 部门 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 )CHARACTER SET utf8 COLLATE utf8_general_ci; -- 关联外键 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; -- 创建表 : 客户 CREATE TABLE client( client_id INT PRIMARY KEY, client_name VARCHAR(20), phone VARCHAR(20) )CHARACTER SET utf8 COLLATE utf8_general_ci; -- 创建表 : 工作 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 )CHARACTER SET utf8 COLLATE utf8_general_ci;
二 插入数据
-- 插入数据: 部门表 -- 表直接互相外键，外键先设置为NULL 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-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); -- 恢复外键: 重新设置 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, '阿貓', '25633899'); INSERT INTO client VALUES(402, '旺來', '45354345'); INSERT INTO client VALUES(403, '露西', '54354365'); INSERT INTO client VALUES(404, '艾瑞克', '18783783'); -- 插入数据: 工作 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 employee ORDER BY salary DESC LIMIT 3
-- 聚合函数 -- 查员工数量 SELECT COUNT(*) FROM employee -- 查询出生1970-01-01之后的女员工人数 SELECT COUNT(emp_id) FROM employee WHERE birth_data > "1970-01-01" AND sex = "M" -- 所有员工平均薪水 SELECT avg(salary) FROM employee -- 所有员工薪水总和 SELECT sum(salary) FROM employee -- 薪水最高的员工 SELECT * FROM employee ORDER BY salary DESC LIMIT 1 SELECT max(salary) FROM employee -- 薪水最低的员工 SELECT * FROM employee ORDER BY salary LIMIT 1 SELECT min(salary) FROM employee
-- 模糊查询 -- % : 任意一个或多个字符 -- - : 任意一个字符 -- 查找尾号335的客户 SELECT * FROM client WHERE phone LIKE "%355"; -- 查找月份12的员工 SELECT * FROM employee WHERE birth_data LIKE "%-12-%" SELECT * FROM employee WHERE birth_data LIKE "____-12-__"
-- union -- 员工姓名 union 客户姓名 SELECT `name` FROM employee UNION SELECT client_name FROM client -- 员工id 员工姓名 union 客户id 客户姓名 SELECT emp_id, `name` FROM employee UNION SELECT client_id, client_name FROM client -- 员工薪水 union 销售金额 SELECT salary FROM employee UNION SELECT total_sales FROM works_with
内连接: inner join 1 别名: 等值连接 2 join: 默认是inner JOIN 3 交叉连接: (1) CROSS JOIN (2) 本质: 笛卡尔积 4 自连接 外连接: 左, 右, 全外 1 LEFT JOIN 2 RIGHT JOIN 3 full join; 也叫"完全连接"
-- branch: 3列4行; -- employee: 7列5行 -- 10列20行 : 两表列相加，行数乘积 SELECT * from branch, employee -- 10列5行 : 两表列相加，行数外键连接数量 SELECT * from branch, employee WHERE employee.branch_id = branch.branch_id SELECT * from branch as b JOIN employee as e on b.branch_id = e.branch_id -- 10列3行 : 两表列相加，行数外键连接数量(有效) SELECT * from branch, employee WHERE employee.emp_id = branch.manager_id SELECT * from branch as b JOIN employee as e on b.manager_id = e.emp_id -- 左连接 : 左边表不管连接条件是否成功，全部回传。 SELECT * from branch as b LEFT JOIN employee as e on b.manager_id = e.emp_id -- 右连接 : 右边表不管连接条件是否成功，全部回传。 SELECT * from branch as b RIGHT JOIN employee as e on b.manager_id = e.emp_id
-- join -- 获取所有部门经理名字 INSERT INTO branch VALUES(4, '偷懒', NULL); -- 插入新经理 -- 显示两表连接后: 所有属性 SELECT * FROM branch JOIN employee ON manager_id = emp_id -- 显示两表连接后: 部门id， 经理，部门名称 -- ERROR: 'branch_id' 两张表都有 SELECT branch_id, branch_name, `name` FROM branch JOIN employee ON branch.manager_id = employee.emp_id -- OK: 明确branch_id来自那张表 SELECT branch.branch_id, branch_name, `name` FROM branch JOIN employee ON branch.manager_id = employee.emp_id -- OK: 明确所有字段，来自那张表 SELECT branch.branch_id, branch.branch_name, employee.`name` FROM branch JOIN employee ON branch.manager_id = employee.emp_id -- OK: 给表取别名 SELECT b.branch_id, b.branch_name, e.`name` FROM branch as b JOIN employee as e ON b.manager_id = e.emp_id
-- 子查询 -- 查询: 研发部门经理名称 SELECT e.`name` FROM employee as e WHERE e.emp_id = (SELECT manager_id as m FROM branch as b WHERE b.branch_name = "研发"); -- 查询: 客户销售金额超过5000的员工名称 SELECT employee.`name` FROM employee WHERE employee.emp_id in (SELECT emp_id FROM works_with WHERE total_sales > 50000); -- 连表查询 + 子查询 -- 查询: 研发部门经理名称, 经理id, 经理姓名 SELECT e.emp_id, e.`name` as "员工姓名", k.`name` as '部门名称' FROM employee as e JOIN (SELECT manager_id as 'id', branch_name as 'name' FROM branch as b WHERE b.branch_name = "研发") k ON e.emp_id = k.id
四 关联删除
-- ON DELETE SET NULL -- Empoyee表中员工离职，branch表处理 当依赖的主键被删除后，该外键值设置为NULL -- ON DELETE CASCADE -- Empoyee表中员工离职，Works_With表处理 当依赖的主键被删除后，该外键对应记录删除。
五 python
# 选择安装包 mysql-connector-python
# 连接时: 指定库 import mysql.connector if __name__ == '__main__': connection = mysql.connector.connect(host='192.168.101.238', port='3306', user='root', password='mysql', database = 'test') # 获取游标 cursor = connection.cursor() # 查询数据 sql = "SELECT * FROM employee;" cursor.execute(sql) # 获出数据 : 全部 records = cursor.fetchall() for i in records: print(i) # 收尾操作 cursor.close() # 关闭游标 connection.close() # 关闭连接
# 连接后: 选择库 import mysql.connector if __name__ == '__main__': connection = mysql.connector.connect(host='192.168.101.238', port='3306', user='root', password='mysql') # 获取游标 cursor = connection.cursor() # 选择数据库 sql = "USE test" cursor.execute(sql) # 查询数据 sql = "SELECT * FROM employee;" cursor.execute(sql) # 获出数据 : 全部 records = cursor.fetchall() for i in records: print(i) # 收尾操作 cursor.close() # 关闭游标 connection.close() # 关闭连接
# 修改操作 import mysql.connector if __name__ == '__main__': connection = mysql.connector.connect(host='192.168.101.238', port='3306', user='root', password='mysql') # 获取游标 cursor = connection.cursor() # 选择数据库 sql = "USE sql_tutorial" cursor.execute(sql) # 插入数据 sql = "INSERT INTO student VALUES(45, 'zz', NULL);" cursor.execute(sql) # 修改数据 sql = "UPDATE student SET NAME = '小明' WHERE student_id = 99;" cursor.execute(sql) # 刪除数据 sql = "DELETE FROM student WHERE student_id = 5;" cursor.execute(sql) # 收尾操作 cursor.close() # 关闭游标 connection.commit() connection.close() # 关闭连接