JavaWEB

mysql

Posted by lily on April 7, 2023

sql数据类型

数字类型

| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 | | — | — | — | — | — | | TINYINT | 1 Bytes | (-128，127) | (0，255) | 小整数值 | | SMALLINT | 2 Bytes | (-32 768，32 767) | (0，65 535) | 大整数值 | | MEDIUMINT | 3 Bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 | | INT或INTEGER | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 | | BIGINT | 8 Bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 | | FLOAT | 4 Bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 | | DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 | | DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |

日期和时间类型

| 类型 | 大小 ( bytes) | 范围 | 格式 | 用途 | | — | — | — | — | — | | DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 | | TIME | 3 | ‘-838:59:59’/’838:59:59’ | HH:MM:SS | 时间值或持续时间 | | YEAR | 1 | 1901/2155 | YYYY | 年份值 | | DATETIME | 8 | ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 | | TIMESTAMP | 4 | ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值，时间戳 |

字符串类型

| 类型 | 大小 | 用途 | | — | — | — | | CHAR(字符长度) | 0-255 bytes | 定长字符串 | | VARCHAR(字符长度) | 0-65535 bytes | 变长字符串 | | TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 | | TINYTEXT | 0-255 bytes | 短文本字符串 | | BLOB | 0-65 535 bytes | 二进制形式的长文本数据 | | TEXT | 0-65 535 bytes | 长文本数据 | | MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 | | MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 | | LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 | | LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |

MySQL命令

  1. DDL –操作数据库和表
  2. DML –操作表表中的数据
  3. DQL –查询表中数据

  4. DDL

    查询数据库、创建表

  5. 显示所有数据库名称

SHOW DATABASES;

  1. 创建数据库

CREATE DATABASES 数据库名;

  1. 删除数据库

DROP DATABASE 数据库名;

  1. 删除数据库如果存在

DROP DATABASE IF EXISTS 数据库名;

  1. 使用该数据库

USE dbName;

  1. 显示正在使用的数据库名称

SELECT DATABASE(); – 调用函数

  1. 显示正在使用数据库内所有表

SHOW TABLES;

  1. 展示表细节

desc dbname;

  1. 创建表

CREATE TABLE TBname ( 字段1 数据类型1, 字段2 数据类型2, 字段3 数据类型3 );

修改、删除表

  1. 删除表drop table tb_name;
  2. 删除存在的表drop table if exists tb_name;
  3. 修改表名alter table tb_name RENAME TO new_name;
  4. 新增列alter table tb_name ADD 列名 数据类型;
  5. 修改某列数据类型alter table tb_name MODIFY 列名 新数据类型;
  6. 修改列名和数据类型alter table tb_name CHANGE 列名 新列名 新数据类型;
  7. 删除列alter table tb_name DROP 列名;

    DML

    添加数据（insert）

  8. 给指定列添加数据INSERT INTO 表名(列名1，列名2，...) VALUES(v1, v2...);
  9. 给全部列添加数据INSERT INTO 表名 VALUES(v1, v2...);
  10. 批量添加数据INSERT INTO 表名(...) VALUES(),(),()...;INSERT INTO 表名 VALUES(),(),()...;

    删除数据（delete）

  11. delete from tb_name [where 条件];

    修改数据（update）

  12. 不添加修改条件则修改整列数据update tb_name set 列名=新列
  13. 修改单个值update tb_name set 某列名 = "新属性" where 某列名 = "某值"

例如 update student set gender = "girl" where name = "peter";

  1. 同时修改多个值例如：update student set gender = "boy", score = 99 where name = "peter";

DQL

基础查询

  1. select name, age from tb_name;
  2. select * from tb_name;
  3. select DISTINCT 列名 from tb_name;
  4. select name as new_name, math as new_name, english as new_name from tb_name;

    条件查询 where

  5. select 字段列表 from 表名 where 条件列表
  6. 条件 | 符号 | 功能 | | — | — | | >,<,>=,<=,= | 比较符号 | | <> 或 != | 不等于 | | between…and… | 在某个范围之内（都包含） | | IN(…) | 多选一 | | LIKE 占位符 | 模糊查询( _ 单个字符 %多个任意字符) | | IS NULL, IS NOT NULL | 是null， 不是null | | AND 或 && | 和 | | OR 或 || | 或者 | | NOT 或 ！ | 非，并不是 |

分组查询 group by

聚合函数

  1. 定义：将一列数据作为一个整体，进行纵向的计算

  2. 分类： | 函数名 | 功能 | | — | — | | count(列名) | 统计数量，一般选用不为null的列 | | max() | 最大值，返回该列最大值 | | min() | 最小值，返回该列最小值 | | sum() | 求和，得到该列数据总值 | | avg() | 得到该列平均值 |

  3. 语法select 聚合函数名（列名） from 表名;
  4. null 值不参与所有聚合函数运算

分组查询

  1. select 展示的字段 from 表名 group by 根据的分组字段名
    1. 例如：查询男女同学各自的数学平均分， 以及各自人数
    2. select sex avg(math), count(*) from stu group by sex;其中count*表示参与的所有列名
  2. select 字段列表 from 表名[where 分组前条件限定] group by 分组字段名[having 分组条件过滤]
  3. 分组之后，查询的字段为聚合函数和分组字段， 查询其他字段无任何意义
  4. where 和 having 的差别
    1. 执行时机不同，不满足where条件不参与分组；having条件则是对分组之后的结果进行过滤
    2. where不能对聚合函数进行判断（依据聚合函数来分组），having可以筛选聚合函数的出来的结果
  5. 执行顺序：where> 聚合函数 >having

    排序查询 order by

  6. 查询出来的数据按某列的某个条件排序
  7. select 字段列表 from 表名 order by 排序字段名1[排序方式1] ,排序字段名2[排序方式2]...;
    1. 有多个排序条件时，当第一个条件值一样才会根据第二条件进行排序
  8. 排序方式： ASC 升序排列（默认值）， DESC 降序排列

    分页查询 limit

  9. 语法select 字段列表 from 表名 LIMIT 起始索引, 查询条目数
    1. 起始索引从0起
  10. 计算公式： 起始索引 = （当前页码-1) * 每页显示的条数

tips

  • 分页查询limit是mysql专有语法
  • Oracle 分页查询使用 rownumber
  • SQL server 分页查询使用 top

    数据库设计

    约束

  1. 约束定义
  2. 分类

image.pngimage.png

  1. 多表设计1

  2. 多表关系
    1. 一对一
    2. 多对一
    3. 多对多
  3. 创建物理关系
    1. 将逻辑链接变为DBMS中的实际物理连接
  4. sql语法

    多表关系

    sql语法

    多对一

  5. alter table tb_name1 ADD CONSTRAINT key_name FOREIGN KEY(列名1) REFERENCES tb_name2(列名2)
  6. CONSTRAINT 表名1 FOREIGN KEY(列名1) REFERENCES 表名2(列名2)

mysql启动服务

  1. NET START MYSQL57 NET STOP MYSQL57
  2. mysql登录mysql -uroot -p***

    InnoDB

  • 自增值保存在内存，MySQL 8.0后，才有了“自增值持久化”能力，即才实现了“若重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：
  • ≤5.7，自增值保存在内存，无持久化。每次重启后，第一次打开表时，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。
  • 若一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时，我们删除id=10的行，AUTO_INCREMENT还是11。但若马上重启实例，重启后，该表的AUTO_INCREMENT就会变成10。
  • 即MySQL重启可能会修改一个表的AUTO_INCREMENT值。
  • MySQL 8.0将自增值的变更记录在redo log，重启时依靠redo log恢复重启之前的值。
  • 理解了MySQL对自增值的保存策略以后，我们再看看自增值修改机制。

    自增值的修改策略

    若字段id被定义为AUTO_INCREMENT，在插入一行数据时，自增值的行为如下：

  1. 若插入数据时id字段指定为0、null 或未指定值，则把该表当前AUTO_INCREMENT值填到自增字段
  2. 若插入数据时id字段指定了具体值，则使用语句里指定值

根据要插入的值和当前自增值大小关系，假设要插入值X，而当前自增值Y，若：

  • X<Y，则该表的自增值不变
  • X≥Y，把当前自增值修改为新自增值

    自增值生成算法

  • auto_increment_offset（自增的初始值）开始
  • 以auto_increment_increment（步长）持续叠加

直到找到第一个大于X的值，作为新的自增值。 两个系统参数默认值都是1。 某些场景使用的就不全是默认值。比如，双M架构要求双写时，可能设置成auto_increment_increment=2，让一个库的自增id都是奇数，另一个库的自增id都是偶数，避免两个库生成的主键发生冲突。 所以，默认情况下，若准备插入的值≥当前自增值：

  • 新自增值就是“准备插入的值+1”
  • 否则，自增值不变
CATALOG
    FEATURED TAGS
    JAVASE JavaWEB Spring python基础 学习工具 数据结构与算法 爬虫学习 计算机网络 springBoot BugNotice springSecurity projectLearning SQL
    FRIENDS