大数据学习笔记6-Mysql进阶

虚拟机无法连接成功处理方案:

1. 查看虚拟机是否启动.

2. 查看ip地址, 用户名, 密码登是否书写正确.

3. 在windows中ping一下linux主机地址 (192.168.88.161)

   • 如果能ping通: 重启datagrip或者finalshell 此时服务没问题, 应该时连接软件出问题了

   • 如果不能ping通: 使用ifconfig 查看linux中的网卡信息, 如果没有192.168.88.161地址的网卡则使用如下命令解决

     systemctl stop NetworkManager
     systemctl diable NetworkManager
     systemctl restart network

     执行上述linux 命令后, 如果网卡出现了, 则我们稍等一分钟重新连接即可.

4. 如果老师不在, 或者老师也解决不了, 则重新解压虚拟机即可.

知识点1: DML语句

insert into 表名 values(字段值1, 字段值2, .....),(字段值1, 字段值2, .....), ....;

insert into 表名(字段1, 字段2,...) values(字段值1, 字段值2,....), (字段值1, 字段值2,....),.....;

-- DML语言: 数据操作语言, 对于数据库进行增 删 改的操作  (可以理解为数据库的写入操作)
-- DML操作--增
-- 关键字  insert 数据插入时使用的关键字

-- 数据准备
USE test_db;
-- 创建数据表
CREATE TABLE student
(
    id     INT,
    name   VARCHAR(30),
    gender VARCHAR(20)
);

-- 1. 一次插入一条数据记录, 不指定被插入的字段
-- 格式: insert into 表名 value(字段值1, 字段值2, .....);
-- INSERT INTO student VALUE (1, '小明', '男');
-- Incorrect string value: '\xE5\xB0\x8F\xE6\x98\x8E' for column 'name' at row 1
-- 此时报错的原因是 test_db数据库, 没有指定字符集, 默认是latin1编码集, 不能保存中文数据.
INSERT INTO
    student VALUE (1, 'xiaoming', 'man');
-- 如果插入数据为英文字母,则可以插入成功

-- 如果想要插入中文信息 , 可以在编码集为utf8的数据库中插入
-- 数据准备
USE test_db_utf8;
-- 创建数据表
CREATE TABLE student
(
    id     INT,
    name   VARCHAR(30),
    gender VARCHAR(20)
);

-- 插入数据, 内部含有中文记录
INSERT INTO
    student VALUE (1, '小明', '男');

-- 2. 一次插入一条数据记录,指定被插入的字段名, 未指定的默认赋值为null
-- 格式: insert into 表名(字段1, 字段2,...) value(字段值1, 字段值2,....);
INSERT INTO
    student(id, name) VALUE (2, '小芳');

-- 3. 一次插入多条数据, 不指定被插入的字段
-- 格式: insert into 表名 values(字段值1, 字段值2, .....),(字段值1, 字段值2, .....), ....;
INSERT INTO
    student
VALUES
    (3, '小智', '男'),
    (4, '小刚', '男'),
    (5, '小霞', '女');

-- 4. 一次插入多条数据记录, 指定被插入的字段, 未指定的字段填充null (空值的意思)
-- 格式: insert into 表名(字段1, 字段2,...) values(字段值1, 字段值2,....), (字段值1, 字段值2,....),.....;
INSERT INTO
    student(id, name)
VALUES
    (6, '鲁智深'),
    (7, '唐伯虎'),
    (8, '蔡徐坤');

-- 注意事项:
-- 1. 插入数据时, 字段值数量 要与字段数量相匹配否则会报错
-- Column count doesn't match value count at row 1

-- 2. 一般我们在开发中共仅使用values一种形式, 因为values 既可以插入一条数据, 又可以插入多条数据.
INSERT INTO
    student
VALUES
    (9, '张三', '男');

-- 3. 插入数据时, 数据值和字段顺序必须匹配, 否则可能出现错误

-- 插入顺序错误时,会进行自动数据类型转换, 转换成功则插入错误数据, 转换失败则报错
-- Incorrect integer value: '张三' for column 'id' at row 1
-- INSERT INTO student VALUES ('张三', 10, '男');
-- INSERT INTO student VALUES (10, '男', '张三');

-- 提示: null 是空值, 证明没有被赋值, 而不是赋值为0的意思

DML之数据的修改和删除操作

update 表名 set 字段名 = 值 [where 筛选条件];

delete from 表名 where [筛选条件];

注意: 修改和删除操作时,一定要增加where 条件,否则会修改所有的行

USE test_db_utf8;
-- 1. 修改数据记录
-- 关键字: update 修改已有数据记录中的指定字段的值.
-- 格式: update 表名 set 字段名 = 值 [where 筛选条件];
-- 需求1: 将id = 4的学员 性别修改为女
UPDATE student
SET
    gender = '女'
WHERE
    id = 4;

-- 需求2: 将id = 5的学员 性别修改为男, 姓名修改为李大炮;
UPDATE student
SET
    name   = '李大炮',
    gender = '男'
WHERE
    id = 5;

-- 需求3: 将所有性别为男的学员的性别修改为猛男
UPDATE student
SET
    gender = '猛男'
WHERE
    gender = '男';

-- 注意: 在修改时 一般都有where 如果没有则对于所有的数据记录进行修改(慎用)

-- 2. 删除数据记录
-- delete , 删除表中的数据记录,  记住不是删除表.
-- 格式: delete from 表名  where [筛选条件];
-- 需求: 删除表中id = 5的数据记录
DELETE
FROM
    student
WHERE
    id = 5;

-- 需求: 删除表中id=6的gender字段的值
UPDATE student
SET
    gender = NULL
WHERE
    id = 6;

-- 注意1: delete 需要一次删除整条数据记录, 如果需要删除的为某一个字段的值 依然需要使用update

-- 注意2: 如果想删除所有的数据记录, 可以不写where 或者直接给该where条件一个恒成立的条件即可

DELETE
FROM
    student;

DELETE
FROM
    student
WHERE
    1 = 1;

-- 扩展: 清空表数据的另外一种方法 truncate

TRUNCATE TABLE student;

知识点2：primary key 主键约束

什么是约束?

约束就是我们存储数据的规则,满足这个规则,就可以存储,不满足这个规则就无法存储

什么是主键约束?

主键约束,就是规定该列数据,既不重复,也不为空.

为什么要定义主键约束?

每一条主键都对应唯一的一条数据,每一条数据也有唯一一个主键相对应.

此时我们查找数据时,只要查找到主键信息,就可以获取该条记录的全部能容, 可以提升查询效率

什么样的字段可以作为主键出现

1. 主键一般都是无意义的数据.

2. 主键一般不会自己手动输入,而是借助计算机进行自增

3. 主键一般为数值型(整数)

4. 主键不会修改

5. 每一张表都建议有一个主键, 且只能有一个主键约束字段

-- 0.使用数据库
USE test_db_utf8;

-- 1. 数据准备, 在创建数据表时,增加主键约束
CREATE TABLE person
(
    id     INT PRIMARY KEY,
    name   VARCHAR(30),
    gender VARCHAR(20)
);

-- 使用desc 查看字段详情, 里边详细说明了哪个字段为主键
DESC person;

-- 2. 主键可以修饰各种数据类型,  不一定时整型,但是官方建议我们使用整数类型数据, 作为主键.
CREATE TABLE person2
(
    id     INT,
    name   VARCHAR(30) PRIMARY KEY,
    gender VARCHAR(20)
);

DESC person2;

-- 3. 主键字段中插入的数据值不能为空
-- 3.1 插入数据时如果主键字段你没有默认值, 则不能不给主键字段赋值
-- Field 'id' doesn't have a default value
INSERT INTO
    person(name, gender)
VALUES
    ('小明', '男');

-- 4. 主键字段中插入不能插入重复的值
-- Duplicate entry '1' for key 'PRIMARY'
INSERT INTO
    person
VALUES
    (1, '小明', '男');
INSERT INTO
    person
VALUES
    (1, '小芳', '女');

-- 5. 创建表后添加主键约束 (了解)
-- 创建没有主键约束的表
CREATE TABLE person3
(
    id     INT,
    name   VARCHAR(30),
    gender VARCHAR(20)
);

-- 建表后添加主键约束
ALTER TABLE person3 ADD PRIMARY KEY (id);
-- 查看主键约束是否添加完成
DESC person3;

-- 6. 删除主键约束 (了解)
-- 需求: 删除person2表中的主键约束
ALTER TABLE person2 DROP PRIMARY KEY ;
-- 查看主键约束是否删除完成
-- 注意: 删除主键约束后, 非空约束依然存在.
DESC person2;

知识点3：auto increment自动增长

自动增长并不是一个约束,而是一个扩展extra

自动增长的主要作用,就在不传值,或者传值为null 的时候,实现记录的自增赋值工作

自动增长只能修饰键,且只能修饰整型

下方表中, key列有值,就是一个键, 否则就不是键. (键的最基本要求: 不能重复)

-- 自动增长 : 是一个扩展功能, 在我们给数据传值时,如果该字段是自动增长字段,则会在不传值, 传0 或null 时自增后自动赋值
-- 关键字  auto_increment

-- 1. 创建一个数据表, 给字段增加自动增长约束
CREATE TABLE heros
(
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(20),
    skill VARCHAR(30)
);

-- 查看表结构, 验证自动增长是否添加成功
DESC heros;

-- 2. 添加自动增长的字段必须为键(在查询字段详情时, key列有值)

-- 3. 添加自动增长字段的列 必须为整数类型.

-- 4. 插入数据时, 给自动增长列插入null将会自动增长
-- 数据从0开始自增
INSERT INTO heros VALUES (null, '梅超风', '九阴白骨爪');

-- 5. 插入数据时, 给自动增长列插入0将会自动增长
INSERT INTO heros VALUES (0, '虚竹', '天山折梅手');

-- 6. 插入数据时, 不给自动增长字段插入数据, 将会默认插入一个自动增长值
INSERT INTO heros(name, skill) VALUES ('乔峰', '降龙十八掌');

-- 7. 如果我插入的数据为指定数值, 则保存相关数据.
INSERT INTO heros VALUES (9, '杨过', '养雕');

-- 8. 再次触发自动增长机制, 则根据哪一个数字进行自增呢?
-- 自动增长机制: 在当前列的最大值基础上自增1 添加到数据列中,  如果表中无任何数据, 则默认从0开始自增.
INSERT INTO heros VALUES (0, '段誉', '升龙拳');

我们向自增字段中添加null, 0 或者不添加数据,都会触发主键自增.

主键自增字段可以插入指定值,但是不能重复

主键自增的计算规则是在当前列最大值的基础上加1

自增只能修饰 整型 键.

• 主键自增扩展

-- 扩展: 有主键自增的数据列, 删除主键约束
-- 需求: 删除heros表中id列的主键约束
-- there can be only one auto column and it must be defined as a key
ALTER TABLE heros DROP PRIMARY KEY ;

-- 删除主键约束后, id列不再是键, 此时自动增长则修饰了一个非键列, 所以不允许修改.
-- 如果想要修改,需要先删除自增扩展, 再删除主键约束
ALTER TABLE heros CHANGE id id INT;

-- 查询表结构
DESC heros;
 
-- 删除主键约束
ALTER TABLE heros DROP PRIMARY KEY ;

-- 查询表结构
DESC heros;

知识点4：not null 非空约束

-- 非空约束 关键字: not null   此列数据,不能填写空值.
-- 1. 建表时,添加非空约束
CREATE TABLE students (
    id INT PRIMARY KEY ,
    name VARCHAR(20) NOT NULL ,
    gender VARCHAR(20)
);

-- 2. 查看表结构, 查看所有的字段详情
DESC students;

-- 3. 非空约束修饰的字段, 不能存在null值
-- Column 'name' cannot be null
-- INSERT INTO students VALUES (1, null, '男');

-- Field 'name' doesn't have a default value
-- INSERT INTO students(id, gender) VALUES (1, '男');

-- 4. 非空约束的字段, 可以重复
INSERT INTO students VALUES (1, '小强', '男');
INSERT INTO students VALUES (2, '小强', '女');

-- 扩展: 删除非空约束的方法
ALTER TABLE students CHANGE name name VARCHAR(30);

-- 查看字段详情
DESC students;

知识点5：unique 唯一约束

• 唯一约束修饰的字段,不能重复

• 扩展: 唯一约束也是一个键,可以增加查询效率,且可以使用自动增长修饰.

-- 1. 创建数据表, 添加唯一约束
CREATE TABLE device (
    id INT PRIMARY KEY ,
    price DOUBLE ,
    description VARCHAR(20) UNIQUE
);

-- 2. 查看表的字段详情, 检验唯一约束添加成功
-- 唯一约束的列 也是一个键.
DESC device;

-- 3. 唯一约束不能插入重复的值
INSERT INTO  device VALUES (1, 20.8, '这个机器真便宜');
-- Duplicate entry '这个机器真便宜' for key 'description'
INSERT INTO  device VALUES (2, 6000000.4, '这个机器真便宜');

-- 4. 唯一约束可以插入空值, 且空值不计入重复数据中, 也就是可以有无数个空值.
INSERT INTO device VALUES (3, 12, NULL);
INSERT INTO device(id, price)  VALUES (4, 15);

注意: 一个表中 可以有多个非空且唯一的字段, 但是主键字段只能有一个.

知识点6：default 默认值

-- 1. 在建表时添加默认值
CREATE TABLE student_info
(
    id     INT PRIMARY KEY,
    name   VARCHAR(30) DEFAULT '无名氏',
    gender VARCHAR(20)
);

-- 2. 查看字段详情, 验证默认值添加成功
DESC student_info;

-- 3. 当未给name字段赋值时, 默认使用其默认值进行填充
INSERT INTO
    student_info(id, gender)
VALUES
    (1, '男');

-- 4. 当给name赋值时,将会把用户传入的数据填充到该字段位置.
INSERT INTO
    student_info
VALUES
    (2, '小芳', '女');

-- 5. 填写0 或者 null 等 不会触发默认值.
INSERT INTO
    student_info
VALUES
    (3, NULL, '男');
INSERT INTO
    student_info
VALUES
    (4, 0, '男');

-- 注意: 自动增长本质上就是一个特殊规则的默认值. 

知识点7：DQL之简单查询

SQL准备工作:

-- 0. 数据准备
-- 创建数据库
DROP DATABASE IF EXISTS bigdata_db;
CREATE DATABASE IF NOT EXISTS bigdata_db CHARSET = utf8;
USE bigdata_db;
-- 创建商品表：
CREATE TABLE IF NOT EXISTS product
(
    pid         INT PRIMARY KEY,
    pname       VARCHAR(20),
    price       DOUBLE,
    category_id VARCHAR(32)
);
-- 插入数据
INSERT INTO product(pid, pname, price, category_id)
VALUES (1, '联想', 5000, 'c001');
INSERT INTO product(pid, pname, price, category_id)
VALUES (2, '海尔', 3000, 'c001');
INSERT INTO product(pid, pname, price, category_id)
VALUES (3, '雷神', 5000, 'c001');
INSERT INTO product(pid, pname, price, category_id)
VALUES (4, '杰克琼斯', 800, 'c002');
INSERT INTO product(pid, pname, price, category_id)
VALUES (5, '真维斯', 200, 'c002');
INSERT INTO product(pid, pname, price, category_id)
VALUES (6, '花花公子', 440, 'c002');
INSERT INTO product(pid, pname, price, category_id)
VALUES (7, '劲霸', 2000, 'c002');
INSERT INTO product(pid, pname, price, category_id)
VALUES (8, '香奈儿', 800, 'c003');
INSERT INTO product(pid, pname, price, category_id)
VALUES (9, '相宜本草', 200, 'c003');
INSERT INTO product(pid, pname, price, category_id)
VALUES (10, '面霸', 5, 'c003');
INSERT INTO product(pid, pname, price, category_id)
VALUES (11, '好想你枣', 56, 'c004');
INSERT INTO product(pid, pname, price, category_id)
VALUES (12, '香飘飘奶茶', 1, 'c005');
INSERT INTO product(pid, pname, price, category_id)
VALUES (13, '海澜之家', 1, 'c002');

-- 1. 查看表中所有的数据内容
-- 格式: select * from 表名;
SELECT *
FROM
    product;

-- 2. 我们可以查询指定的数据列
SELECT
    pname,
    price
FROM
    product;

-- 3. 我们可以在整列数据值的基础上进行计算
SELECT
    pname,
    price,
    price * 0.5
FROM
    product;

-- 4. 我们可以在查询时插入常数列
SELECT
    pname,
    price,
    price,
    '体力人甄选',
    10
FROM
    product;

-- 注意1: 我们在select 后边写什么, 就查询到什么, 可以将一个字段输出多次, 也可以不使用.
-- 注意2: 无论select 后边的数据内容如何变化, 数据条目数始终不变.
-- 注意3: 无论如何查询, 数据库中的原数据,不会发生任何改变

知识点8：DQL之条件查询

比较查询

-- 条件查询: 将查询出来的结果,根据一定的规则进行过滤, 满足条件则保留,不满足条件则忽略.
-- 关键字 : where
-- 格式: select 列名1, 列名2.... from 表名 where 条件;
-- 条件表达式的计算结果为布尔类型数据, 如果数据的值为 false 则不成立,数据忽略,如果值为true 则成立,数据保留

-- 1. 比较查询
-- > 等号左侧的数据大于等号右侧则返回true 否则返回false
-- < 等号左侧的数据小于等号右侧则返回true 否则返回false
-- <= 等号左侧的数据小于等于等号右侧则返回true 否则返回false
-- >= 等号左侧的数据大于等于等号右侧则返回true 否则返回false
-- = 等号左右两侧的数据相等则返回true, 否则返回false
-- != 或 <> 等号左右两侧的数据不相等则返回true, 否则返回false
-- 在sql中 0 = false  1 = true

SELECT 2 > 3; -- 0
SELECT 0 = FALSE;
-- 等价

-- 需求1: 获取所有商品中,商品价格大于600 的商品信息
SELECT *
FROM product
WHERE price > 600;

-- 需求2: 获取所有商品中,商品价格小于等于800的商品价格和商品名称
SELECT pname, price FROM product WHERE price <= 800;

-- 需求3: 获取所有商品中商品类别为c001的商品信息
SELECT * FROM product WHERE category_id = 'c001';

-- 需求4: 获取所有商品中商品类别不为c002的商品id 和商品价格
SELECT category_id,pid,price FROM product WHERE category_id != 'c002';
SELECT category_id,pid,price FROM product WHERE category_id <> 'c002';

范围查询

-- 2. 范围查询
-- 2.1 连续范围查询 : 在连续范围内,任意数值均会使条件成立
-- 关键字: between ... and ...

-- 需求1: 查询所有商品中, 价格区间在800-2000范围内的商品信息
-- 注意: 该取值范围包含边界值, 800 和 2000 都包含在内
SELECT *
FROM product
WHERE price BETWEEN 800 AND 2000;

-- 在使用between and 时要注意,保证前边的数值小于后边的数值, 否则将没有任何数据被筛选出来
SELECT *
FROM product
WHERE price BETWEEN 2000 AND 800;
-- 为空

-- 2.2 非连续范围查询 : 仅在多个取值中,进行判断,如果等于其中任何一个值,则条件成立
-- 关键字 : in

-- 需求2: 查询所有商品中, 价格为 1  200  800 的商品信息
SELECT *
FROM product
WHERE price IN (1, 200, 800);

模糊查询

-- 3. 模糊查询
-- 就是根据一定的规则筛选符合规则的字符信息, 如果符合规则, 则返回true, 否则返回false
-- 格式: select 列名... from 表名 where 字段 like '规则';
-- 关键字: like
-- 规则: % 代表任意多个任意字符, 可以为0个,  _ 代表一个任意字符.

-- 举例: 如果想获取姓张的同学信息, 则需要设置的规则为  张% , 如果想获取两个字的姓张的同学  规则为 张_

-- 需求1: 查询名字以"香"开头的商品全部信息
SELECT * FROM product WHERE pname LIKE '香%';

-- 需求2: 查询名字以香开头, 且名字是3个字符的商品等信息
SELECT * FROM product WHERE pname LIKE '香__';

-- 需求3: 查询所有商品中,商品名称为4个字的商品信息
SELECT * FROM product WHERE pname LIKE '____';

-- 需求4: 查询所有商品中,商品名称中带有'霸'字的商品信息
SELECT * FROM product WHERE pname LIKE '%霸%';

非空查询

-- 非空查询
-- is null 判断指定字段的值是否为空, 如果为空则返回true 否则返回 false
-- is not null 判断指定字段的值是否为空, 如果为空则返回false 否则返回 true

-- 需求1: 获取所有商品中 价格不为空的商品信息
-- SELECT * FROM product WHERE price != NULL;
SELECT *
FROM
    product
WHERE
    price IS NOT NULL;

-- 需求2: 获取所有商品中 价格为空的商品信息
SELECT *
FROM
    product
WHERE
    price IS NULL;

逻辑查询

-- 5. 逻辑查询
-- 逻辑查询 : 与 或 非的逻辑关系
-- 关键字 : and or not

-- and 逻辑与  同真即真
-- 举例: 今天我们来教室上课, 同时必须带电脑,才能学会.   上课 and 带电脑
SELECT TRUE AND TRUE;
SELECT FALSE AND TRUE;
SELECT TRUE AND FALSE;
SELECT FALSE AND FALSE;

-- or 逻辑或  同假即假
-- 举例: 今天打车或者坐公交车,都可以来上课   打车  or  坐公交
SELECT TRUE OR TRUE;
SELECT FALSE OR TRUE;
SELECT TRUE OR FALSE;
SELECT FALSE OR FALSE;

-- not 逻辑非  真假互换
-- 举例: 如果今天没有生病 我就去上课   not 生病
SELECT NOT FALSE;
SELECT NOT TRUE;

-- 需求1: 获取所有商品中价格大于200且价格小于等于2000的商品信息
SELECT *
FROM
    product
WHERE
      price > 200
  AND price <= 2000;

-- 需求2: 获取所有商品中,价格大于2000或者价格小于200的全部商品
SELECT *
FROM
    product
WHERE
     price > 2000
  OR price < 200;

-- 需求3: 获取商品中价格不等于 2000的全部商品
SELECT *
FROM
    product
WHERE
    NOT price = 2000;

-- 需求4: 获取商品中价格不为空的所有商品信息
SELECT *
FROM
    product
WHERE
    price IS NOT NULL;
SELECT *
FROM
    product
WHERE
    NOT price IS NULL;

今天的学习笔记就到这里 欢迎大家点赞评论 互相交流
祝大家在程序员的道路上越走越远