分割线

借物表

狂神说 MySQL 学习笔记

简介

  • DB 数据库 ( DataBase )

    • 关系型数据库 ( SQL ),比如 MySQL , Oracle , SQL Server , SQLite

    • 非关系型数据库 ( NOSQL ),比如 Redis , MongoDB

    数据库排行榜

  • DBA 数据库管理员(Database Administrator)

  • DBMS 数据库管理系统 ( DataBase Management System )

  • 操作分类,对应下文的大标题

    20210305104453

安装&配置

安装

  • 使用 scoop 安装

    scoop install mysql
  • 然后注册系统服务

    • (这条信息在安装后会提示,需要在管理员模式的命令行下执行)
    mysqld --install MySQL --defaults-file="D:\Game\Scoop\apps\mysql\current\my.ini"
  • 然后重启电脑,MySQL 服务会自己跑起来.


设置密码

  • (也可以不设置密码,跳过.)

  • MySQL/bin/下打开终端

  • 进入 MySQL 管理

    mysql –u root –p
  • 更新密码

    update mysql.user set authentication_string=password('2333') where user='root'and Host = 'localhost';
  • 刷新权限

    flush privileges;
  • 重启 MySQL 服务

    net stop mysql
    net start mysql

配置

找到 MySQL/my.ini ,这是 MySQL 配置文件

MySQL 配置文件-my.ini

  • 我的配置

    #[client]和[mysql]都是客户端,[mysqld]是服务端

    [mysqld]
    datadir=D:/Game/Scoop/persist/mysql/data
    character-set-server=utf8
    #port=3306

    [client]
    user=root
    #port=3306

    [mysql]
    default-character-set=utf8

分割线

连接 MySQL

VScode

  • VScode 连接,需要安装 SQL 扩展(随便选一个)

  • 安装插件后只需要填写需要的信息,就可以连接上了.

    20210304094732

SQLyog

  • 跟上面一样

    20210304233850

DBeaver

  • 个人在后续学习中发现的数据库管理工具,比我用过的(包括 MYSQL workbench,IDEA,VScode,SQLYog) 都要好用,优点:

    • 社区版免费,没广告 (SQLYog 略带广告)

    • 中文界面,很友好,操作手感好

    • 有方便的 ER 图工具,这个非常非常非常赞!!!

    • …不再赘述,建议用这个,如下图:

20210405144227

常用操作

update user set password=password('123456')where user='root'; --修改密码
flush privileges; --刷新数据库权限

show databases; --显示所有数据库
use dbname; --打开某个数据库
create database [if not exists] 数据库名; --创建数据库
drop database [if exists] 数据库名; --删除数据库

show tables; --显示数据库 mysql 中所有的表
desc user; --显示表user 表的信息
create database name; --创建数据库
use databasename; --选择数据库

-- 单行注释
/* 多行注释 */
  • 使用 SQLyog

    20210305111225 20210305111244

分割线

DDL-数据定义

列类型

  • 标红的为常用的.

数值

20210305115939

字符串

20210305115957

日期

20210305120011

NULL 值

  • 不要用 NULL 进行算术运算 , 结果仍为 NULL

分割线

字段属性

20210305155250
  • UnSigned

    • 无符号的
    • 声明该数据列不允许负数 .
  • ZEROFILL

    • 0 填充的
    • 不足位数的用 0 来填充 , 如 int(3),5 则为 005
  • Auto_InCrement

    • 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
    • 通常用于设置主键 , 且为整数类型
    • 可定义起始值和步长,当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
    • SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
  • NOT NULL

    • 默认为 NULL , 即没有插入该列的数值
    • 如果设置为 NOT NULL , 则该列必须有值
  • DEFAULT

    • 默认的,用于设置默认值
    • 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

阿里规范

  • 每个表必须有的五个字段属性

    • id 主键
    • version 乐观锁
    • is_delete 伪删除
    • gmt_create 创建时间
    • gmt_update 修改时间

命名规范

  • 为什么使用下划线命名法而非驼峰命名法?

    早期的 Oracle 会把所有字段属性名变为大写: lastName -> LASTNAME

    这样就看不出来了,所以用下划线命名法: last_name -> LAST_NAME

    这个惯性一直延续至今,一些数据库/框架也发展处相应的使用规则,就成了一种规范


分割线

数据表

表类型

MySQL 常用的七种表类型(转)

  • 有十多种类型了,但常用的就两种:MyISAMInnoDB,对比:

    MyISAMInnoDB
    事务处理不支持支持
    数据行锁定不支持支持
    外键约束不支持支持
    全文索引支持不支持
    空间占用较小较大(约两倍)

  • 适用场景:

    • MyISAM : 节约空间及相应速度
    • InnoDB : 安全性 , 事务处理及多用户操作数据表

存放位置

Mysql 安装目录/data/对应表名/


编码

  • 默认是 Latin1 编码,不支持中文

    • 可以在 MySQL 配置文件中修改默认编码
  • 也可以在创建表时指定编码为 utf8


操作数据表

  • 修改表名

    • ALTER TABLE 旧表名 RENAME AS 新表名
  • 删除数据表

    • DROP TABLE [IF EXISTS] 表名

  • 添加字段

    • ALTER TABLE 表名 ADD 字段名 列属性[属性值]
  • 修改字段

    • ALTER TABLE 表名 MODIFY 字段名 列类型[属性值]

    • ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性值]

  • 删除字段

    • ALTER TABLE 表名 DROP 字段名

新建表-例子

  • 板栗

    CREATE TABLE IF NOT EXISTS `student`(
    -- CREATE TABLE [IF NOT EXISTS] `表名`(
    id int NOT NULL primary key AUTO_INCREMENT comment 'primary key',
    create_time DATETIME COMMENT 'create time',
    update_time DATETIME COMMENT 'update time',
    -- `字段名` 列类型 [字段属性] [索引] [注释],
    -- `字段名` 列类型 [字段属性] [索引] [注释],
    -- `字段名` 列类型 [字段属性] [索引] [注释],
    ) default charset utf8 comment '';
    -- ) [表类型] [字符集] [注释];

  • 举例

    /*
    创建一个school数据库->创建学生表(列,字段)
    学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
    创建表之前 , 一定要先选择数据库
    */
    USE `school`;
    CREATE TABLE IF NOT EXISTS `stu_data` (
    `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `birthday` datetime DEFAULT NULL COMMENT '生日',
    `address` varchar(100) DEFAULT NULL COMMENT '地址',
    `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY (`id`)
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8;

分割线

DML-数据操作

外键

  • 一个表内的某引用另外一个表中的一,被引用的表是主表,引用的表是从表,从表通过建立列的索引来引用另一个表的索引,这个关系叫做外键.

    • 主表中被引用的列必须有索引(比如 PRIMARY,KEY 类型)
      20210307123305

    • 在外键存在的情况下,主表无法被删除,主从表的关联索引也无法被删除,但可以修改(类型).
      20210307124315


  • 建立方法

    1. 创建两张表
    2. 主从表各创建一个索引(PRIMARY 或者 KEY 都可以)
    3. 把从表的索引约束到主表的索引上
    4. 外键建立成功!

  • 代码形式

    -- 年级表 (id,年级名称)
    CREATE TABLE `grade` (
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
    ) ENGINE = INNODB DEFAULT CHARSET = utf8;

    -- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
    CREATE TABLE `student` (
    `studentid` INT(4) NOT NULL COMMENT '学号',
    `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
    `gradeid` INT(10) DEFAULT NULL COMMENT '年级',
    `phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
    `address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
    `borndate` DATETIME DEFAULT NULL COMMENT '生日',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentid`),
    -- 创 建 外 键 的 方 式 一: 创 建 子 表 同 时 创 建 外 键
    KEY `FK_gradeid` (`gradeid`), -- 创建一个KEY类型的索引
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) -- 把索引约束到主表索引
    ) ENGINE = INNODB DEFAULT CHARSET = utf8;

    -- 创建外键方式二 : 创建子表完毕后,修改子表添加外键
    ALTER TABLE
    `student`
    ADD
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
  • 以上是表的外键,不建议使用数据库级别的外键

    • 以后如需引用多表数据,常用程序实现

    • 阿里规范:不能使用外键和级联,在 delete 或 update 时需要考虑外键因素,增加负担


添加 INSERT

语法 : INSERT INTO 表名[(字段 1,字段 2,字段 3,…)] VALUES (‘值 1’,‘值 2’,‘值 3’)

  • 字段与值位置对应

    INSERT INTO
    grade(gradename, gradeid)
    VALUES
    ('大一', '2');
  • 省略字段的话,值得顺序必须与数据表中的字段顺序一致

    INSERT INTO
    grade
    VALUES
    ('1', '大二');
  • 一次插入多条数据

    INSERT INTO
    grade(gradename)
    VALUES
    ('大三'),('大四');

修改 UPDATE

UPDATE 表名 SET 列名 1=value 列名 2=value [WHERE 条件];

  • value 为修改后的数据 , 可以为变量 , 具体值 , 表达式或者嵌套的 SELECT 结果

  • 不指定 where 的话,会修改表内所有数据

  • 注意 value 用单引号’引起来,不是飘`

  • 例子(随机生成 20 条数据后多条件筛选)

    UPDATE
    `grade`
    SET
    `gradename` = '大一'
    WHERE
    gradeid > 1
    AND gradeid < 10
    OR gradeid = 15;
  • 结果(json)

    [
    {
    "gradeid": "1",
    "gradename": "lonrd"
    },
    {
    "gradeid": "2",
    "gradename": "大一"
    },
    {
    "gradeid": "3",
    "gradename": "大一"
    },
    {
    "gradeid": "4",
    "gradename": "大一"
    },
    {
    "gradeid": "5",
    "gradename": "大一"
    },
    {
    "gradeid": "6",
    "gradename": "大一"
    },
    {
    "gradeid": "7",
    "gradename": "大一"
    },
    {
    "gradeid": "8",
    "gradename": "大一"
    },
    {
    "gradeid": "9",
    "gradename": "大一"
    },
    {
    "gradeid": "10",
    "gradename": "fmtdq"
    },
    {
    "gradeid": "11",
    "gradename": "tvyoh"
    },
    {
    "gradeid": "12",
    "gradename": "ovhvu"
    },
    {
    "gradeid": "13",
    "gradename": "hipnh"
    },
    {
    "gradeid": "14",
    "gradename": "kjdxr"
    },
    {
    "gradeid": "15",
    "gradename": "大一"
    },
    {
    "gradeid": "16",
    "gradename": "xvlbp"
    },
    {
    "gradeid": "17",
    "gradename": "bgphq"
    },
    {
    "gradeid": "18",
    "gradename": "jcpmc"
    },
    {
    "gradeid": "19",
    "gradename": "gjuof"
    },
    {
    "gradeid": "20",
    "gradename": "owxri"
    }
    ]

删除 DELETE/TRUNCATE

DELETE FROM 表名 [WHERE condition];

  • 如不指定 where,会删除表中所有数据

  • 例子

    DELETE FROM
    grade
    WHERE
    gradeid = 5;

TRUNCATE [TABLE] 表名;

  • 清空表,表的结构和索引不变

    TRUNCATE `student`

  • 二者都能删除数据,不影响表结构

    • DELETE 删除数据后不影响自动增量,会记录日志.
    • TRUNCATE 清空表之后会把自动增量归零,不会记录日志.

  • 使用 DELETE 清空不同引擎的数据库表数据.重启数据库服务后

    • InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)

    • MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)