深入浅出Mysql (一) Mysql的基础类型

远方黎明
228 阅读8分钟

最近在看《深入浅出MySQL++数据库开发、优化与管理维护+第2版+唐汉明》,做个记录，这里只列出了关键的内容。

1, Sql 基础语句

  • 查看所有数据库

    • show databases

  • 查看所有表

    • show tables

  • 查看表信息

    • show create table account (查看表的创建语句，account 是表名)
    • desc account (查看表定义，输出表的列信息，account 是表名)

  • 字段修改

    • alter column： 设置或删除列的默认值(Only)
      • alter table film alter column rental_duration set default 5;
      • alter talbe film alter column rental_duration drop default;
    • change column : 列的重命名、列类型的变更以及列位置的移动
      • alter table mytable change column old_column new_column varchar(32) not null first;
      • alter table mytable change column old_column new_column varchar(32) not null after baz;
    • modify column: 除了不能给列重命名之外，它干的活和change column 是一样的
      • alter talbe mytable modify column foo varchar(32) not null after name; 注意：change和modify都可以修改表的定义，不同的是change可以修改列名，modify则不行

  • 多表更新

    • 基础用法：

      原始数据：

image-20210120231507348.png

UPDATE emp a,
dept b 
SET a.dept_name = b.deptname,
b.BACKUP = 'test01' 
WHERE
    a.dept_no = b.deptno

执行多表更新后：

image-20210120231042514.png

可以看到 emp 表和 dept表都被更新了。

多表更新这种语法还可以用来做单表批量更新，比如

UPDATE emp,
( SELECT '张三' ename, '3000' sal UNION SELECT '李四', '3000' ) emp_list 
SET emp.sal = emp_list.sal 
WHERE
    emp.ename = emp_list.ename

运行后：

image-20210120231239337.png

可以发现，我们用一条update 语句就可以将 张三和李四的sal同时修改了，这就是使用一条语句的mysql批量更新

  • 多表删除

    delete a,b
from emp a, dept b 
where a.dept_no= b.deptno
and a.dept_no=2

    该操作会同时删除emp表和dept表中符合条件的数据，如果delete后面 只有a的话，则只删除emp表

  • 聚合

    • with rollup是可选语法，表明是否对分类聚合后的结果进行再汇总
    • having和where的区别在于，having是对聚合后的结果进行条件过滤，而where是在聚合前就对记录进行过滤， 如果逻辑允许，我们尽可能先用where过滤记录，这样因为结果集减少，将对聚合的效率大大提高，最后再根据逻辑看是否用having进行再过滤。

  • 连接

    • 内连接

      内连仅选出两张表中互相匹配的记录

    • 外连接

      • 左连接
      • 右连接

  • 子查询

    • exists、not exists

  • 记录联合

    • union 和 union all的区别，union去重，union all 不会去重

2.字段类型

  • 整数类型
整数类型字节最小值最大值
tinyint1有符号 -128 无符号 0有符号 127 无符号 255
smallint2有符号 -2^15 无符号 0有符号 2^15 -1 无符号 2^16-1
mediumint3有符号 -2^23 无符号 0有符号 2^23 -1 无符号 2^24 -1
int、integer4有符号 -2^31 无符号 0有符号 2^31 -1 无符号 2^32 -1
bigint8有符号 -2^63 无符号 0有符号 2^63 -1 无符号 2^64 -1

有这样一种写法 id int(5) 这里的字段id占用空间还是4个字节，这里的5是显示宽度，在字段设置无符号且填充零时，当数值位数未达到设置的显示宽度时，会在数值前面补充零直到满足设定的显示宽度

  • 浮点数

    浮点数类型字节构成
    float41个符号位，8位二进制指数，23位尾数
    double81个符号位，11位二进制指数，52位尾数

  • 定点数

    定点数类型字节描述
    dec(P,D) decimal(P,D)M+2P表示有效数字数的精度（165），D表示小数点后的位数，D的范围是030，mysql要求 D 小于或等于P

  • bit 位类型

    位类型字节最小值最大值
    bit(M)1~8bit(1)bit(64)

  • 日期类型：

    日期和时间类型字节最小值最大值备注
    DATE41000-01-019999-12-31
    DATETIME81000-01-01 00:00:009999-12-31 23:59:59
    TIME3-838:59:59838:59:59
    TIMESTAMP419700101080012038年的某个时刻和时区相关
    YEAR119012155

  • 字符串类型：

    类型说明
    char(n)定长，char(n)中的n表示字符数，最大长度是255个字符； 如果是utf8编码方式， 那么char类型占255 * 3个字节。 数据超出后用空格替代，查询的时候会自动去掉空格
    varchar(n )变长, varchar(n)中的n表示字符数。最大空间是65535个字节，存放字符数量跟字符集有关系。VARCHAR类型的实际长度是它的值的实际长度+1。为什么"+1"呢？这一个字节用于保存实际使用了多大的长度
    text变长，跟varchar基本相同， 理论上最多保存65535个字符， 实际上text占用内存空间最大也是65535个字节； 考虑到字符编码方式， 一个字符占用多个字节， text并不能存放那么多字符； 跟varchar的区别是text需要2个字节空间记录字段的总字节数。

    注意：

    Mysql 5.0版本以下：

    varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）

    Mysql 5.0版本以上：

    varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个，最大大小是65532字节

  • ENUM类型

    • create table t( gender enum('M','F'));
      • 忽略大小写，插入不在ENUM指定范围的数据时，不返回警告，而是插入第一个值，并且插入的时候只允许从值集合中选取单个值，不能一次取多个值
      • 1-255个成员的枚举需要1个字节存储，255-65535个成员需要两个字节存储，最多允许65535个成员

  • SET类型

    • 和ENUM最主要的区别就是SET类型一次可以选区多个成员，ENUM只能选一个

5. 常用函数

5.1 字符串函数

函数功能
CONCAT(S1,S2,...Sn)连接S1,S2,...Sn 为一个字符串
INSERT(str,x,y,instr)将字符串str从第x位置开始，y个字符长的子串替换为字符串instr
LOWER(str)将字符串str中所有字符变为小写
UPPER(str)将字符串str中所有字符变为大写
LEFT(str,x)返回字符串str最左边的 x 个字符
RIGHT(str,x)返回字符串str最右边的 x 个字符
LPAD(str,n,pad)用字符串pad对str最左边进行填充，直到长度为 n
RPAD(str,n,pad)用字符串pad对str最右边进行填充，直到长度为 n
LTRIM(str)去掉字符串 str 左侧的空格
RTRIM(str)去掉字符串 str 右侧的空格
REPEAT(str,x)返回 str 重复 x 次的结果
REPLACE(str,a,b)用字符串 b 替换字符串 str 中所有出现的字符串 a
STRCMP(s1,s2)比较字符串 s1 和 s2 （返回 -1,0,1）
TRIM(str)去掉字符串行尾和行头的空格
SUBSTRING(str,x,y)返回从字符串 str x 位置起 y 个字符长度的字符串

5.2 数值函数

函数功能
ABS(x)返回 x 的绝对值( -1 和 1 的绝对值都是1)
CEIL(x)返回大于 x 的最小整数值
FLOOR(x)返回 小于 x 的最大整数值
MOD(x,y)返回 x/y 的模
RAND()返回0~1内的随机值
ROUND(x,y)返回参数 x 的四舍五入的有 y 位小数的值
TRUNCATE(x,y)返回数字 x 截断为 y 位小数的结果

5.3 日期和时间函数

函数功能
CURDATE()返回当前日期
CURTIME()返回当前时间
NOW()返回当前的日期和时间 now(3)表示带毫秒且毫秒精确到3位 (最高6位)
UNIX_TIMESTAMP(date)将日期类型转换为时间戳类型
FROM_UNIXTIME(timestamp)将时间戳类型转换为日期类型
WEEK(date)返回日期为一年中的第几周
YEAR(date)返回日期date的年份
HOUR(date)返回日期date的小时值
MINUTE(time)返回time的分钟值
MONTHNAME(date)返回date 的月份名
DATE_FORMAT(date,fmt)返回按字符串fmt 格式化 日期date值
DATE_ADD(date,INTERVAL expr type)返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)返回起始时间expr 和 结束时间 expr2之间的天数

例子：

获取当前时间，当前时间31天后的时间，当前时间一年零两个月后的时间

image-20210124231245252.png

5.4 流程函数

函数功能
IF(value,t,f)如果value是真，返回 t ；否则返回 f
IFNULL(value1,value2)如果value1不为空，返回 value1，否则返回 value2
CASE WHEN [value1] THEN [result]...ELSE [default] END如果value1是真，返回result，否则返回default
CASE [expr] WHEN [value1] THEN[result1]... ELSE[default] END如果expr等于value1，返回result1，否则返回default

6 存储引擎的选择

8 选择合适的数据类型

10 索引的设计和使用

拷贝表：

create table address_1 like address; (拷贝表结构和索引,不包含数据)

insert into address_1 select *from address; (将address的数据拷贝到address_1中)

pageNum:202

avatar
文章
阅读
粉丝