mysql常用操作 & 数据类型 & 函数

最新推荐文章于 2024-06-14 06:26:07 发布

xiha_zhu

最新推荐文章于 2024-06-14 06:26:07 发布

阅读量267

点赞数

分类专栏： db 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/xiha_zhu/article/details/89065646

版权

db 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

常用sql

连接数据库

-- 查看mysql版本
    select version();
-- 连接
    mysql -h 192.0.0.0 -u root -p root
-- 查看数据库
    show databases;
-- 切换database
    use mysql;
-- 查看表
    show tables;
-- 查看表结构
    desc user;
-- 查看索引的语法格式
    show index from user;
-- 查看当前时间
    now()

-- 查询分析格式：explain  ……  \G;

虚拟列、索引操作

-- 添加虚拟列 
    alter table user add column abc varchar(32) generated always as (data->'$.abc');
    alter table user add first_name varchar(2) as (substr(name,1,1)); 
-- 修改虚拟列 
    alter table tab modify column abc varchar(64) generated always as (data->'$.abc');
-- 删除虚拟列  alter table drop column abc;

-- 添加索引
    alter table t1 add index idx_name(name);
	create index idx_name on tab(name);
-- 添加唯一索引
    alter table t1 add unique uni_name(name);
-- 删除索引
    alter table t1 drop index idx_name;

查看库、表空间

-- mysql查看数据库所占用空间
SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size,
       CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size,
       CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free,
       CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size
FROM information_schema.tables WHERE TABLE_SCHEMA = '数据库名';

-- mysql查看表所占空间
SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size,
       CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size,
       CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free,
       CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size
FROM information_schema.tables WHERE TABLE_NAME = '表名';

-- 查询数据库指定库的表大小，按大小排序
SELECT TABLE_NAME, CONCAT(TRUNCATE(data_length/1024/1024,2),' MB') AS data_size,
      CONCAT(TRUNCATE(index_length/1024/1024,2),' MB') AS index_size
FROM information_schema.tables WHERE TABLE_SCHEMA = '数据库名'
GROUP BY TABLE_NAME
ORDER BY data_length DESC;

数据类型

数字类型

TINYINT 1 个字节
SMALLINT 2 个字节
MEDIUMINT 3 个字节
INT 4 个字节
INTEGER 4 个字节
BIGINT 8 个字节
FLOAT(X) 4 如果 X < = 24 或 8 如果 25 < = X < = 53
FLOAT 4 个字节
DOUBLE 8 个字节
REAL 8 个字节
DECIMAL(M,D) M字节(D+2 , 如果M < D)
NUMERIC(M,D) M字节(D+2 , 如果M < D)

日期和时间类型

DATE 3 个字节
TIMESTAMP 4 个字节
DATETIME 8 个字节

串类型

CHAR(M) M字节，1 <= M <= 255
VARCHAR(M) L+1 字节, 在此L <= M和1 <= M <= 255
TINYBLOB, TINYTEXT L+1 字节, 在此L< 2 ^ 8
BLOB, TEXT L+2 字节, 在此L< 2 ^ 16
ENUM('value1','value2',...) 1 或 2 个字节, 取决于枚举值的数目(最大值65535）

text与blob的区别在于: text不能存储图片;

blob是二进制流，text是非二进制。

mysql 的二进制数据类型 BINARY, VARBINARY, BLOB 都没有字符集的概念。

varchar能存多少汉字、数字？

mysql的vachar字段的类型虽然最大长度是65535
具体还是要看版本的，一个字符占用3个字节
4.0版本以下，varchar(100)，指的是100字节，如果存放UTF8汉字时，只能存33个（每个汉字3字节）
5.0版本以上，varchar(100)，指的是100字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放100个。
UTF8编码中一个汉字（包括数字）占用3个字节
GBK编码中一个汉字（包括数字）占用2个字节

字符、字节、位，之间的关系？

位：数据存储的最小单位。每个二进制数字0或者1就是1个位；
字节：8个位构成一个字节；
1 byte (字节)= 8 bit(位)；
1 KB = 1024 B(字节)；
1 MB = 1024 KB; (2^10 B)
1 GB = 1024 MB; (2^20 B)
1 TB = 1024 GB; (2^30 B)

mysql函数

字符函数

length("abc"); //3 返回字节长度
instr("abc","bc"); //2 返回子串第一次出现的索引，没有返回0
substr/substring("abc",2); //bc 截取从指定索引处后面的所有字符
sbustr/substring("abcde",2,3); //bcd 截取从指定索引指定字符长度的字符
lower("ABC"); //abc | upper("abc") //ABC 小写/大写
concat("ab","cd","e",...); //abcde... 拼接字符串
trim(" abc "); //abc 去前后空格
trim("a" from "abca"); //bc 去除前后a
lpad/rpad("abc'',3,"*"); //***abc | abc*** 在字符串左/右边用指定的字符填充指定的长度
replace("abcda","a","b"); //bbcdb 替换

数学函数

round(1.2); //1 四舍五入
round(1.23,1); //1.2
ceil(1.2); //2
floor(1.2); //1
truncate(1.23,1); //1.2 截断小数点后1位
mod(10/3); //1 取余

日期函数

now();
curdate(); 当前日期不包括时间
curtime(); 当前时间不包括日期
year/month(now()); 返回指定部分的年/月/日
str_to_date('2019-06-21 5:21:00','%Y-%m-%d %H:%i:%s'); //2019-06-21 05:21:00 字符转日期
DATE_FORMAT(NOW(),'%Y年%m月%d日'); //2019年03月21日日期转字符串