SQL学习笔记

0. 资料：

SQL 3小时快速入门
日期：3.10 视频观看进度：1:32:34

1. 什么是SQL？

结构化查询语言

跟关联式资料库建立沟通用的~

操作界面：MySQL Workbench

2. 基础知识

2.1 key

1. primary key- 主键

• 可以对应唯一的项

——类比：身份证号可以当主键，姓名不是，因为可能重名

• works_with表中，有两个主键，因为不管哪个都没法唯一对应，只好同时用两个主键！

——本质上还是一个主键，只不过主键由两个值来对应

2. foreign key- 外键

• 可以通过外键把两个表串起来——外链接

——让外键对应主键，不是主键对应外键

• 外键也可以对应到自己的表格——自链接

2.2 数据类型

INT	整数
DECIMAL(m,n)	小数（m位，小数点后占n位）
VARCHAR(n)	字符串(长度为n)
BLOB	（binary large object）图片/影片/档案……
DATE	日期 'YYYY-MM-DD'
TIMESTAMP	时间 'YYYY-MM-DD HH:MM:SS'

2.3 条件判断表示方法

>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<>	不等于

2.4 注释

---

3. 语法

每个指令结尾处要加；，表示已经结束

关键字会变蓝色，通常习惯用大写

3.1 创建和删除资料库

CREATE DATABASE `a`;
SHOW databases;
DROP database `database`;

注意用的是反引号不是单引号

非数值型数据-单引号/双引号

变量（数据库里面的组件之类的？）-反引号

3.2使用资料库

资料的六种形态/数据的六种类型：

INT	整数
DECIMAL(m,n)	小数（m位，小数点后占n位）
VARCHAR(n)	字符串(长度为n)
BLOB	（binary large object）图片/影片/档案……
DATE	日期 'YYYY-MM-DD'
TIMESTAMP	时间 'YYYY-MM-DD HH:MM:SS'

3.2.1 创建表格-CREATE TABLE

目标：在资料库sql_tutorial中创建一个如下的表格——

student_id	name	major
1	小明	生物

代码：

USE `sql_tutorial`;

CREATE TABLE `student`(
    `student_id` INT,
    `name` VARCHAR(20),
    `major` VARCHAR(20),
     PRIMARY KEY(`student_id`)
);

3.2.2 显示表格-DESCRIBE

DESCRIBE `student`;

3.2.3 删除表格-DROP

DROP TABLE `student`;

3.2.4 更改表格-ALTER

加

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

减

ALTER TABLE `student` DROP COLUMN `gpa`;

3.3 写入、修改和删除资料

3.3.1 写入资料-INSERT

INSERT INTO `student` VALUES(1,"小白","历史");
SELECT * FROM `student`;

注意：

1. SELECT和DESCRIBE的区别~此处的*指的是“全部”的意思，从student表格中选择全部

2. insert的value个数=属性个数，比如说(1,"小白")就会报错，因为缺少了major的值

3. 上面那种情况，可以用NULL占位（primary key不能是null哈）：

INSERT INTO `student` VALUES(2,"小黑", NULL);
SELECT * FROM `student`;

另一种不按顺序的写入方法：

INSERT INTO `student` (`name`,`major`,`student_id`) VALUES("小绿", "生物", 3);

如果说我漏掉了“name”属性？

INSERT INTO `student` (`major`,`student_id`) VALUES( "英语", 4);
INSERT INTO `student` VALUES(4,NULL,"英语");

这两个效果是一样的~

constraints 约束和限制

直接把条件添加在变量后面，语法很简单~

CREATE TABLE `student`(
`student_id` INT,
    `name` VARCHAR(20) NOT NULL,
    `major` VARCHAR(20) UNIQUE,
     PRIMARY KEY(`student_id`)
);

1 NOT NULL限制

上述指令中，对`name`的限制是NOT NULL；

此时如果试图添加一条没有姓名的记录，就会报错：

INSERT INTO `student` VALUES(1,NULL,"历史");

2 UNIQUE限制

INSERT INTO `student` VALUES(1,"小白","历史");
INSERT INTO `student` VALUES(2,"小黑","历史");

对`major`的限制是UNIQUE，这里小黑和小白的专业撞了，也会报错~

3 DEFAULT限制（预设值）

CREATE TABLE `student`(
`student_id` INT,
    `name` VARCHAR(20) NOT NULL,
    `major` VARCHAR(20) DEFAULT "历史",
     PRIMARY KEY(`student_id`)
);

将major的默认值设置为历史，试试看四种不同的输入方式：

INSERT INTO `student` VALUES(1,"小白","历史");
INSERT INTO `student` VALUES(2,"小黑","数学");
INSERT INTO `student` VALUES(3,"小红", NULL);
INSERT INTO `student` (`student_id`, `name`) VALUES( 4, "小蓝");

注意小红的还是NULL哦~

4 AUTO_INCREMENT

CREATE TABLE `student`(
`student_id` INT AUTO_INCREMENT,
    `name` VARCHAR(20),
    `major` VARCHAR(20),
     PRIMARY KEY(`student_id`)
);

INSERT INTO `student` (`name`,`major`) VALUES("小白","英语");
INSERT INTO `student` (`name`,`major`) VALUES("小黑","数学");
INSERT INTO `student` (`name`,`major`) VALUES("小红", NULL);
SELECT * FROM `student`;

`student_id`实现了自动新增！

3.3.2 修改资料-UPDATE

练习环节先把系统预设的更新模式给删掉~（我也不知道具体为什么）

SET SQL_SAFE_UPDATES = 0;

准备的资料库长这样：

基本的语法be like：

UPDATE `student`--更新要用的表
SET `major` = '数据科学' --要做的改动
WHERE `major` = '数学'; --改动under什么条件

场景1 数学专业改名成数据科学了！

UPDATE `student`
SET `major` = '数据科学'
WHERE `major` = '数学';

场景2 小红的major要补上！同时分数是85！

UPDATE `student`
SET `major` = '生物', `score` = 85
WHERE `student_id` = '4';

场景3 英语100分的人全部黑幕成皇！

UPDATE `student`
SET `name` = '皇'
WHERE `major` = '英语' AND `score` = 100;

这里的WHERE也可以用OR之类的~

感觉就是python会用的while

3.3.3 删除资料-DELETE

1和2重复啦，删一个

DELETE FROM `student`
WHERE `student_id` = 2;

全部删光！——就不用选条件啦

DELETE FROM `student`;

3.4 获取资料-SELECT

3.4.1 获取全部！

SELECT * FROM `student`;

3.4.2 获取属性!

SELECT `name`, `major` FROM `student`;

3.4.3按分数排序！

SELECT * 
FROM `student`
ORDER BY `score` ASC;

ASC means ascend！（不加也ok，默认由小到大）

这里其实不换行也可以的！

由大到小排序呢？

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

DESC means descend~

多个参考？

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

可以看到8号在7号前面啦

3.4.4 获取前几条数据！

只想看分数最高的前三个人？

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

最低前三同理~

进一步筛选……

SELECT * 
FROM `student`
WHERE `major` IN ("生物" ,'英语','物理') AND `score` >= 81
ORDER BY `score` DESC, `student_id` DESC
LIMIT 3;

4. 创建公司资料库

目标：创建一个这样的资料库

第一步：

创建Employee表格

-- Employee Table
CREATE TABLE `Employee`(
`emp_id` INT AUTO_INCREMENT,
    PRIMARY KEY(`emp_id`),
    `name` VARCHAR (20),
    `birth_date` DATE,
    `sex` VARCHAR(1),
    `salary` INT,
    `branch_id` INT,
    `sup_id` INT
    );

由于还没有创建其他表格，所以暂时不能创建外键。外键只能之后再补啦~

第二步：

创建Branch表格

-- 部门表格
CREATE TABLE `Branch`(
    `branch_id` INT AUTO_INCREMENT,
    PRIMARY KEY(`branch_id`),
    `branch_name` VARCHAR (20),
    `manager_id` INT,
    FOREIGN KEY(`manager_id`) REFERENCES `Employee`(`emp_id`) ON DELETE SET NULL
    -- 先别管ODSN什么意思，后面会讲
    );

现在可以创建manager_id这个外键啦~

第三步：

同理创建剩下的两个表格