SQL 快速入门
1. Sql：跟关联式资料库管理系统（用表格做关联）沟通的语言
2. Key
Primary key主键：唯一的表示每一笔资料 （某一属性无法唯一的表示时，可以设定2个或两个以上的主键，不能为NUALL）
Foreign key外键：对应到另一张表格的主键/对应回自己的表格
3. 数据类型
INT 整数
DECIMAL(m,n) 有小数点的数（m：共有几位数n：小数点后占几位）
VARCHAR(n) 字符串
BLOB (Binary Large Object)二进制资料图片 影片 档案
DATE ‘YYYY-MM-DD’日期
TIMESTAMP ‘YYYY-MM-DD HH:MM:SS’记录时间
4. 创建资料库
CREATE DATABASE sql_tutorial; 关键字
CREATE DATABASE ‘database’; 避免跟关键字冲突
SHOW DATABASES; 显示所有资料库
DROP DATABASE ‘sql_tutorial’; 删除资料库
5. 创建资料库表格
USE ‘sql_tutorial’;
CREATE TABLEstudent(
‘student_id’ INT PRIMARY KEY, AUTO_INCREMENT(自动加一，后面不用输入1、2、3…)
‘name’ VARCHAR(20) NOT NULL, 非空
‘major’ VARCHAR(20) UNIQUE, 唯一值
‘score’ INT
#‘major’ VARCHAR(20) DEFAULT ‘历史’, 预设值为历史
#PRIMARY(‘student_id’)
);
DESCRIBE ‘student’;
DROP TABLE ‘student’;
6. 增加属性
ALTER TABLE ‘student’ ADD gpa DECIMAL(3,2);
7. 删除属性
ALTER TABLE ‘student’ DROP COLUMN gpa;
8. 存录资料
INSERT INTO ‘student’ VALUES(1,’小白’,’历史’);
INSERT INTO ‘student’(‘name’,’major’,’student_id’) VALUES(’小白’,’历史’,1);
SELECT * FROM ‘student’; 搜寻表格里的全部资料
9. 修改资料
UPDATE ‘student’ SET ‘major’ =’生化’ WHERE ‘major’ =‘生物’ OR ‘major’=‘化学’; 把所有生物和化学改为生化
UPDATE ‘student’ SET ‘major’ =’英文’,’name’=’小灰’ WHERE ‘student_id’ = 3; 学号为3的学科改为英语，名改为小灰
10. 删除资料
DELEFE FROM ‘student’
WHERE ‘name’=’小灰’ AND ‘score’ <=60;
11. 查询资料
SELECT * FROM ‘student’;
SELECT ‘name’,’major’ FROM ‘student’;
SELECT * FROM ‘student’ ORDER BY ‘student’ ,’student_id’; 按id排序（DESC由高到低 ASC反之）
SELECT * FROM ‘student’ ORDER BY ‘score’ DESC LIMIT 2;返回前两名
SELECT * FROM ‘student’ WHERE ‘major’ =’英语’ OR ‘score’ <>70; 不等于70
SELECT * FROM ‘student’WHERE ‘major’ IN(‘历史’,’英语’,’生物’); 历史英语生物学的人
12. 建立公司资料库
CREATE TABLE ‘employee’(
‘emp_id’ INT PRIMARY KEY,
‘name’ VARCHAR(20),
‘birth_date’ DATE,
‘sex’ VARCHAR(1),
‘salary’ INT,
‘brach_id’ INT,
‘sup_id’ INT
);
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
);
新增外键
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)
);
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
);
新增公司资料
先新增branch表的内容，若先新增employee，外键branch_id无对应内容，会报错。同理，新branch表的内容前要先把manager_id的值设为null，新增employee表的内容后再将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’);
13. 取得公司资料
取得所有员工的资料
SELECT * FROM ‘employee’;
取得所有客户的资料
SELECT * FROM ‘client’;
按照薪水高到低取得员工资料
SELECT * FROM ‘employee’ ORDER BY ‘salary’ DESC;
取得薪水前3的员工
SELECT * FROM ‘employee’ ORDER BU ‘salary’ DESC LIMIT 3;
取得所有员工的名字
SELECT ‘name’ FROM ‘employee’;
取得所有员工中存在的性别（DISTINCT去重）
SELECT DISTINCT ‘branch_id’ FROM ‘employee’;
14. 聚合函数aggregate function
取得员工人数
SELECT COUNT(*) FROM ‘employee’;
取得所有出生年龄1970-01-01之后的女性员工人数
SELECT COUNT(*) FROM ‘employee’ WHERE ‘birth_date’>’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’;
15. 万用字元（%代表多个字元，_代表一个字元）
取得电话号码尾数是225的客户
SELECT * FROM ‘client’ WHERE ‘phone’ LIKE ‘%335’;
取得姓艾的客户
SELECT * FROM ‘client’ WHERE ‘name’ LIKE ‘艾%’;
取得生日在12月的客户
SELECT % FROM ‘client’ WHERE ‘birth_date’ LIKE ‘_____12%’;
16. 联集 union
员工名字union客户名字
SELECT ‘name’ FROM ‘employee’ UNION SELECT ‘client_name’ FROM ‘client’;
员工id+员工名字union客户id+客户名字
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’;
18. 连接 join
INSERT INTO ‘branch’ VALUES(4,’偷懒’,NULL);
取得所有部门经理的名字
SELECT ‘employee’. ‘emp_id’, ‘employee’.’name’, ‘employee’.’branch_name’ FROM ‘employee’ JOIN ‘branch’ ON ‘employee’.‘emp_id’=’branch’.’manager_id’;
左连接（LEFT JOIN）返回左边所有值，右边没有的为NULL
右连接(RIGHT JION) 返回右边所有值，左边没有的为NULL
19. 子查询subquery在一个查询里面使用另一个查询的结果
找出研发部门的经理名字
SELECT ‘name’ FROM ‘emplyee’ WHERE ‘emp_id’=(
SELECT ‘manager_id’ FROM ‘branch’ WHERE ‘branch_name’=’研发’);
找出对单一位客户销售金额超过50000的员工名字
SELECT ‘name’ FROM ‘emplyee’ WHERE ‘emp_id’ IN (
SELECT ‘emp_id’ FROM ‘works_with’ WHERE ‘total_sales’>50000);
20. On delete
ON DELETE SET NULL 外键对应不到时设为NULL
ON DELETE CASCADE 外键对应不到时删去该行
21. Python连接MySQL
pip install mysql-connector-python
import mysql.connector
connector = mysql.connector.connect(host=’localhost’,
port=’3306’,
user=’root’,
password=’password’,
database=’sql_tutorial’)
cursor = connection.cursor()
#创建资料库
cursor.execute(“CREAYE DATABASE ‘qq’;”)
#取得所有资料库名称
cursor.execute(“SHOW DATABASES;”)
records = cursor.fetchall() #回传
for r in records:
print(r)
#选择资料库
cursor.execute(“USE ‘sql_tutorial’;”)
#创建表格
cursor.execute(“CREATE TABLE ‘qq’(qq INT);”)
#新增
cursor.execute(“INSERT INTO ‘branch’ VALUES(5,’qq’,NULL);”)
cursor.close()
connection.commit()
connection.close()
教学视频：https://www.bilibili.com/video/BV1PT4y1e7UU?t=9967.1
