SQL笔记

创建资料库

SET SQL_SAFE_UPDATES = 0;

CREATE DATABASE `wode`;
# SHOW DATABASES;
# DROP DATABASE `wode`;
USE `wode`;

DROP TABLE `student`;
CREATE TABLE `student`(
    `student_id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20) NOT NULL,
    `major` VARCHAR(20) , # UNIQUE
    `score` INT
);
DESCRIBE `student`;


# ALTER TABLE `student` ADD gpa DECIMAL(3,2);
# ALTER TABLE `student` DROP COLUMN gpa;

SELECT * FROM `student`;

# 增
INSERT INTO `student` VALUES(1,'小白','历史',30);
INSERT INTO `student` VALUES(2,'小黑','历史',34);
INSERT INTO `student` VALUES(3,'小绿',NULL,25);
INSERT INTO `student`(`name`,`major`,`score`) VALUES('小白','英语',45);
INSERT INTO `student`(`name`,`major`,`score`) VALUES('小绿','英语',23);

# 改
SELECT * FROM `student`;
UPDATE `student`
SET `major` = '英语文学', `score` = 23 
WHERE `major` = '英语文学';
# 删
DELETE FROM `student`
WHERE `student_id` = 4;
DELETE FROM `student`
WHERE `score`<=23;

# 取得资料select
SELECT `name`,`major` FROM `student`;
SELECT * FROM `student` 
ORDER BY `score` DESC;
SELECT * FROM `student` 
ORDER BY `score`,`student_id`;
SELECT * FROM `student` LIMIT 2;  # limit限制回传数目
SELECT * FROM `student`
WHERE `major` IN('历史','英语文学');

创建简单公司资料

# 创建简单公司资料库，两张表
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
);
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` RENAME COLUMN `brach_id` TO `branch_id`;

DESCRIBE  `employee`;
DESCRIBE `branch`;

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;

# 新增资料,先将branch_id中对应外键写为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-10-08','M',29000,2,206);
INSERT INTO `employee` VALUES(208,'小黑','2000-10-08','M',35000,3,206);
INSERT INTO `employee` VALUES(209,'小白','1997-10-08','F',39000,3,207);
INSERT INTO `employee` VALUES(210,'小兰','1925-10-08','F',84000,1,207);

SELECT * FROM `branch`;
SELECT * FROM `employee`;

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;

# 取得公司资料
SELECT * FROM `employee` ORDER BY `salary`DESC LIMIT 3;
SELECT DISTINCT `sex` FROM `employee`;  # 去重复

其他

# 聚合函数
SELECT COUNT(*) FROM `employee`;
SELECT COUNT(`sup_id`) FROM `employee`; # 去NULL后数量
SELECT COUNT(*)FROM `employee` 
WHERE `birth_date`>'1990-07-01' AND `sex` = 'F';
SELECT AVG(`salary`) FROM `employee`; # 平均数
# SUM() MAX() MIN()

# 万用字元
# LIKE 可用%代表多个字元,用一个_代表一个字元

# union 合并,合并属性类型要一样,可以多对多,对应数目要相同
SELECT `name` FROM `employee`
UNION
SELECT `branch_name` FROM `branch`;
# SELECT xx  AS xxx

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

# subquery 子查询
SELECT `name`
FROM `employee`
WHERE `emp_id` = (
    SELECT `manager_id`
    FROM `branch`
    WHERE `branch_name` = '研发'
);

# on delete 
# on delete set null 对应不到就设置NULL,同时为主键的不能如此用
# on delete cascade 对应不到就删除

与python连接

import mysql.connector

connection = mysql.connector.connect(host='127.0.0.1',
                                     port='3306',
                                     user='root',
                                     password='YBYB24689')
cursor = connection.cursor()

# 创建资料库
# cursor.execute("CREATE DATABASE `qq`;")

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

#关闭
cursor.close()
connection.commit()
connection.close()