数据库、SQL、MySQL学习

前言

SQL 学习记录
https://www.youtube.com/watch?v=gvRXjsrpCHw&t=40s

---

一、资料库是什么？SQL是什么？

只要是能存放资料的地方就是资料库。

资料 资料库管理系统(DBMS) 资料库

资料库分为关联式资料库(SQL)和非关联式资料库(no SQL)；关联式资料库用关联式资料库管理系统进行操作(RDBMS)，如MySQL，Oracle；非关联式资料库用非关联式资料库管理系统进行操作(NRDBMS)，如MongoDB，Redis。

结构化询问语言(Structured Query Language, SQL)，SQL是一门语言，用来和关联式资料库管理系统“交流”

二、安装MySQL

1.搜mysql community download
2.mysql installer for windows
3.点上面2.4M的
4.no，thanks
5.custum 只选sever，workbench，shell

三、MySQL使用

3.1简单使用–学生资料库

1.table 和 key

关联式资料库是将资料设置成一个个表格(table)，对他们做关联
资料都含有不同的属性

设置成主键(primary_key)的那个属性可以唯一的表示那个资料

通过设置外键(foreign key)来在表格和表格间产生关联，foreign key只能对应到primary_key

可以同时把一个属性设置为主键和外键

2.创建资料库

--创建资料库
CREATE DATABASE `sql_01`;
--展示所有的资料库
SHOW DATABASES;
--删除资料库
DROP DATABASE `sql-01`;

在每个指令结尾加 ； 表示指令已经结束
关键字 通常大写，显示为蓝色
自己命名的名称通常用 包起来，防止和关键字冲突

3.创建资料库的表格

--选择使用哪个资料库
USE `sql_01`;
--创建表格，同时对属性设置资料形态
CREATE TABLE `student`(
	`student_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `major` VARCHAR(20)
);
--展示创建的表格
DESCRIBE `student`;
--删除表格
DROP TABLE `student`; 
--对表格新增属性
ALTER TABLE `student` ADD gpa DECIMAL(3,2);
--删除表格属性
ALTER TABLE `student` DROP COLUMN gpa;

MySQL的资料形态

 INT                          --整数
 DECIMAL(m, n)                --有小数点的数，需要传入两个参数，m：总共的位数，n：小数的位数
 VARCHAR                      --字串，要传入长度的参数
 BLOB                         --二进制的资料(图片、档案、影片...)
 DATE                         --'YYYY-MM-DD'  日期
 TIMESTAMP                    --'YYYY-MM-DD HH:MM:SS' 记录时间

4.在表格中存入资料

--选择使用哪个资料库
USE `sql_01`;
--创建表格，同时对属性设置资料形态
CREATE TABLE `student`(
	`student_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `major` VARCHAR(20)
);
--在表格中存入资料
INSERT INTO `student` VALUES(1, '小白', '历史');
INSERT INTO `student` VALUES(2, '小黑', '牛物');
INSERT INTO `student` VALUES(3, '小lv', NULL);
INSERT INTO `student`  (`name` , `major`, `student_id`) VALUES('小慌', '英语', 4);
INSERT INTO `student`  ( `major`, `student_id`) VALUES( '英语', 5);
--列出表格中的全部资料
SELECT * FROM `student`;

某个属性不填就相当于填了NULL

5.在创建表格时对属性增加约束

--选择使用哪个资料库
USE `sql_01`;
--创建表格，同时对属性设置资料形态
CREATE TABLE `student`(
	`student_id` INT AUTO_INCREMENT,
    `name` VARCHAR(20) NOT NULL,
    `major` VARCHAR(20) UNIQUE,
    PRIMARY KEY(`student_id`)
);
--在表格中存入资料
INSERT INTO `student` VALUES(1, '小白', '历史');
INSERT INTO `student` VALUES(2, '小黑', '牛物');
INSERT INTO `student` VALUES('小lv', NULL);
INSERT INTO `student`  (`name` , `major`, `student_id`) VALUES('小慌', '英语', 4);
INSERT INTO `student`  ( `major`, `student_id`) VALUES( '英语', 5);
--列出表格中的全部资料
SELECT * FROM `student`;

NOT NULL 不可以为空
UNIQUE 不可以重复
DEFAULT ‘xxx’ 如果添加时这个属性没有加入，就使用预设的xxx
AUTO_INCREMENT 自动加一

6.在表格中修改、删除资料

--将workbench预设的更新模式关闭
SET SQL_SAFE_UPDATES = 0;
--创建表格，同时对属性设置资料形态
CREATE TABLE `student`(
	`student_id` INT,
    `name` VARCHAR(20),
    `major` VARCHAR(20),
    `score` INT
    PRIMARY KEY(`student_id`)
);
--列出表格中的全部资料
SELECT * FROM `student`;
--更新student表格中资料属性为major的资料，将英语更换为英语文学
UPDATE `student`
SET  `major` = '英语文学'
WHERE `major` = '英语';

WHERE `student_id` = 3

SET  `major` = '生化'
WHERE `major` = '生物' OR '化学'

SET  `name`='小会', `major` = '生化'
WHERE `student_id` = 3

--删除资料
DELETE FROM `student`
WHERE `student_id` = 3

WHERE `score` <> 60

--列出表格中的全部资料
SELECT * FROM `student`;

WHERE可判断多个条件
<> 不等于

7.搜寻资料

--列出表格中的全部资料
SELECT * FROM `student`;
--根相关属性的资料
SELECT `name` FROM `student`;
SELECT `name`,`score` FROM `student`;
--列出表格中的全部资料,并根据某个属性进行排序
SELECT * 
FROM `student` 
ORDER BY `score` DESC;

SELECT * 
FROM `student` 
ORDER BY `score`,`student_id` DESC;

SELECT * 
FROM `student` 
ORDER BY `score` DESC
LIMIT 3 DESC;

SELECT * 
FROM `student` 
WHERE `major` = '生物'
WHERE `major` IN('生物','历史','地理');

ASC是由低到高，预设的是ASC
DESC是由高到低
WHERE major IN(‘生物’,‘历史’,‘生物’) 和WHERE major = ‘生物’ OR major = ‘历史’ OR major = ‘地理’ 一样

3.2较为复杂使用–公司资料库

公司情况如下：

图1.公司职员、部门、客户、业绩表格

emp_id是员工id
super_id 是上级领导
manager_id是部门主管领导

1.创建资料库

--创建公司资料库
CREATE DATABASE `gongsi`;

--创建employee表格，同时对属性设置资料形态
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
);

--创建branch表格，同时对属性设置资料形态
CREATE TABLE `branch`(
	`branch_id` INT PRIMARY KEY,
    `branch_name` VARCHAR(20),
    `manager_id` INT,
    FOREIGN KEY (`manager_id`) REFERENCES `employe`(`emp_id`) ON DELETE SET NULL
);

--修改employee表格，补充foreign key
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;

--创建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 `employe`(`emp_id`) ON DELETE CASCADE,
    FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE
);


--在表格branch中存入资料
INSERT INTO `branch` VALUES(1, '研发', NULL);
INSERT INTO `branch` VALUES(2, '行政', NULL);
INSERT INTO `branch` VALUES(3, '咨询', NULL);

--在表格employee中存入资料
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', 25000, 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);

--修改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;

--在表格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');

--在表格work_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_witht` VALUES('209', 403, '24000');
INSERT INTO `work_with` VALUES('210', 404, '87940');

2.获取资料

--获取所有员工的资料
SELECT * FROM `employee`;

--获取所有客户的资料
SELECT * FROM `client`;

--按照薪水低到高获取员工资料
SELECT * FROM `employee` ORDER BY `salary`;

--获取薪水前三高的员工资料
SELECT * FROM `employee` 
ORDER BY `salary` DESC 
LIMIT 3;

--获取所有员工的名字
SELECT `name` FROM `employee`;

SELECT DISTINCT `branch_id` FROM `employee`;

DISTINCT 重复项只留一个

3.聚合函数

--获取员工的人数
SELECT COUNT(*) FROM `employee`;

--获取所有出生于1970-01-01之后的女性员工的人数
SELECT COUNT(*) 
FROM `employee` 
WHERE `birth-data` > '1970-01-01' AND `sex` = 'F';

--获取员工的平均薪水
SELECT AVG(`salary`) FROM `employee`;

--获取员工的薪水总和
SELECT SUM(`salary`) FROM `employee`;

--获取薪水最高、最低的员工
SELECT MAX(`salary`) FROM `employee`;
SELECT MIN(`salary`) FROM `employee`;

4.万能字符

%：代表多个字符
_：代表一个字符

--获取电话尾号为335的客户
SELECT * 
FROM `client` 
WHERE `phone` LIKE '%335';

--获取姓艾的客户
SELECT * 
FROM `client` 
WHERE `name` LIKE '艾%';

--获取生日在12月的员工
SELECT * 
FROM `employee` 
WHERE `birth-data` LIKE '_____12%';

5. 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` ;

SELECT `emp_id` AS `total_id`, `name` AS `total_name`
FROM `employee` 
UNION
SELECT `client_id`, `client_name`
FROM `client` ;

--员工薪水 union 销售金额
SELECT `salary` 
FROM `employee` 
UNION
SELECT `total_sales`
FROM `works_with` ;

UNION 只能SELECT 一个属性，SELECT name，sex会报错，正常一个属性union一个属性、两个属性就要union两个属性；union的两个资料形态必须是同类型的

6. join-连接

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 `emp_id` = `manager_id`;

SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` 
JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;

SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` LEFT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;

SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` RIGHT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;

ON emp_id = manager_id 是连接条件
employee.emp_id 哪个表格的哪个属性
LEFT JOIN 不管连接条件是否成立，左边表格都输出，右边表格则条件成立才输出

7. subquery-子查询

子查询就是可以在一个查询语句中塞入另一个查询语句

--获取研发部门经理的名字
SELECT `name`
FROM `employee`
WHERE `emp_id` = (
	SELECT `manager_id`
	FROM `branch` 
	WHERE `branch_name` = '研发'
);

--获取销售金额超过50000的员工名字
SELECT `name`
FROM `employee`
WHERE `emp_id` IN (
	SELECT `manager_id`
	FROM `work_with` 
	WHERE `total_sales` > 50000
);

8. ON DELETE

子查询就是可以在一个查询语句中塞入另一个查询语句

ON DELETE SET NULL   
FOREIGN KEY (`manager_id`) REFERENCES `employe`(`emp_id`) ON DELETE SET NULL  当`manager_id`对应的`emp_id`删掉后，将`manager_id`对应的`emp_id`设置成NULL；即对应不到设置成NULL

ON DELETE CASCADE
FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE 当`client_id`对应在`client`表格中的`client_id`删掉后，`client_id`也同样删掉；即对应不到就整笔删掉

四、python 连接 MySQL

pip install mysql-connector-python

图2.连接参数来源

创建数据库

import mysql.connector

connection = mysql.connector.connect(host='localhost',
                                     port='3306',
                                     user='root',
                                     password='********')

cursor = connection.cursor()   # 开始使用

#创建资料库
cursor.execute("CREATE DATABASE `qq`;")   # 在括号内写sql指令    创建完资料库后要把这行代码注释掉，不然重复创建会报错

#取得所有资料库名称
cursor.execute("SHOW DATABASES;")
records = cursor.fetchall() #回传
for r in records:
    print(r)

#选择资料库
cursor.execute("USE `qq`;")

#创建表格
cursor.execute("CREATE TABLE `qq`(qq INT);")

cursor.close()    # 使用好后将cursor关闭
connection.close()   # 将连接也关闭

查看已存在数据库的内容

import mysql.connector

connection = mysql.connector.connect(host='localhost',
                                     port='3306',
                                     user='root',
                                     password='********',
                                     database='sql_01')

cursor = connection.cursor()   # 开始使用

#取得所有资料库名称
cursor.execute("SELECT * FROM `branch`;")

records = cursor.fetchall() #回传
for r in records:
    print(r)

cursor.close()    # 使用好后将cursor关闭
connection.close()   # 将连接也关闭

修改数据库的内容

import mysql.connector

connection = mysql.connector.connect(host='localhost',
                                     port='3306',
                                     user='root',
                                     password='********',
                                     database='sql_01')

cursor = connection.cursor()   # 开始使用

#新增
cursor.execute("INSERT INTO `branch` VALUES(5, 'qq', NULL);")

#修改
cursor.execute("UPDATE `branch` SET `manager_id` = NULL WHERE `branch_id` = 4;")

#删除
cursor.execute("DELETE FROM `branch`  WHERE `branch_id` = 4;")

cursor.close()    # 使用好后将cursor关闭
connection.commit()   # 凡是动到数据库内容的，要加这行代码使指令生效
connection.close()   # 将连接也关闭

---

总结

以上就是 sql相关内容，主要是一些介绍、用法和将数据库连接到python。