🚀进修MySQL~(壹)

参考
简介
安装&配置
连接 MySQL
DDL-数据定义
DML-数据操作

借物表

狂神说 MySQL 学习笔记

简介

DB 数据库 ( DataBase )
- 关系型数据库 ( SQL ),比如 MySQL , Oracle , SQL Server , SQLite
- 非关系型数据库 ( NOSQL ),比如 Redis , MongoDB
数据库排行榜
DBA 数据库管理员（Database Administrator)
DBMS 数据库管理系统 ( DataBase Management System )
操作分类,对应下文的大标题

安装&配置

安装

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

设置密码

(也可以不设置密码,跳过.)
在 MySQL/bin/下打开终端
进入 MySQL 管理
1
mysql –u root –p

更新密码

1	update mysql.user set authentication_string=password('2333') where user='root'and Host = 'localhost';

刷新权限
1
flush privileges;
重启 MySQL 服务
1
2
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 扩展(随便选一个)
安装插件后只需要填写需要的信息,就可以连接上了.

SQLyog

跟上面一样

DBeaver

个人在后续学习中发现的数据库管理工具,比我用过的(包括 MYSQL workbench,IDEA,VScode,SQLYog) 都要好用,优点:
- 社区版免费,没广告 (SQLYog 略带广告)
- 中文界面,很友好,操作手感好
- 有方便的 ER 图工具,这个非常非常非常赞!!!
- …不再赘述,建议用这个,如下图:

常用操作

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

DDL-数据定义

列类型

标红的为常用的.

数值

字符串

日期

NULL 值

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

字段属性

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 常用的七种表类型（转）

有十多种类型了,但常用的就两种:MyISAM 与 InnoDB,对比:
MyISAM InnoDB
事务处理不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
空间占用较小较大(约两倍)
适用场景:
- MyISAM : 节约空间及相应速度
- InnoDB : 安全性 , 事务处理及多用户操作数据表

	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 类型)
- 在外键存在的情况下,主表无法被删除,主从表的关联索引也无法被删除,但可以修改(类型).
建立方法
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');

省略字段的话,值得顺序必须与数据表中的字段顺序一致
1
2
3
4
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] 表名;

清空表,表的结构和索引不变
1
TRUNCATE `student`

二者都能删除数据,不影响表结构
- DELETE 删除数据后不影响自动增量,会记录日志.
- TRUNCATE 清空表之后会把自动增量归零,不会记录日志.
使用 DELETE 清空不同引擎的数据库表数据.重启数据库服务后
- InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
- MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)

🚀进修MySQL~(壹)

借物表

简介

安装&配置

安装

设置密码

配置

连接 MySQL

VScode

SQLyog

DBeaver

常用操作

DDL-数据定义

列类型

数值

字符串

日期

NULL 值

字段属性

阿里规范

命名规范

数据表

表类型

存放位置

编码

操作数据表

新建表-例子

DML-数据操作

外键

添加 INSERT

修改 UPDATE

删除 DELETE/TRUNCATE

-> 欢迎,锟斤拷! <-

低情商：代码一坨屎
高情商：兼容性极强

借物表

简介

安装&配置

安装

设置密码

配置

连接 MySQL

VScode

SQLyog

DBeaver

常用操作

DDL-数据定义

列类型

数值

字符串

日期

NULL 值

字段属性

阿里规范

命名规范

数据表

表类型

存放位置

编码

操作数据表

新建表-例子

DML-数据操作

外键

添加 INSERT

修改 UPDATE

删除 DELETE/TRUNCATE

-> 欢迎,锟斤拷! <-

低情商：代码一坨屎高情商：兼容性极强

低情商：代码一坨屎
高情商：兼容性极强