数字类型
-
工作中常用的int为bigint,通常使用有符号
有符号的意思是有正负数,通常大家觉得基本都不用负数,但是在如字段a-字段b的情况下出现负数,查询就会报错
-
int(num) 问题,这个num代表显示位数,当数值不足这个num的位数时,则前面补0,否则全显示
只有在命令行中才能看到效果
create table test (a int auto_increment primary key);
auto_increment关键字必须加在主键上
insert into test values (null), (100), (null), (10), (null);
最终从查询结果为1,10,100,101,102 如果自增主键,null,0都是递增,其他有效值正确
字符类型
除了CHAR和VARCHAR,其他都是字节
select ‘a’ = “A” 为1,如用户名Lisa 和 LIAS
- CHAR,VARCHAR
Create table test (a varchar(10), key(a));
Insert into test values("a");
Insert into test values("A");
select * from test where a = "a";
查询结果为两条。
决定这个的是在创建数据库时的排序规则所定的
后缀ci :
后缀bin:二进制存储,区分大小写
- TEXT
时间类型
-
DATETIME 和 TIMESTAMP的区别
主要是时区的区别
DATE,没有时区,有日期,没时间
DATETIME没有时区,有日期和时间
TIMESTAMP有时区,有日期和时间
JSON
sql操作
@json:局部变量
@@conf:全局变量(环境变量信息等)
show VARIABLES like "%datadir%"; select @@datadir; 两个执行结果相同
-- 创建表,和json字段
create table json (
id bigint auto_increment,
data json,
primary key(id)
);
-- 插入数据
insert into json values (
null,
'{
"name": "张三",
"age": "123",
"addr": "北京"
}'
);
-- 查询数据
select * from json;
-- json基础查询
select json_extract ("[1,2,3,[1,2]]", "$[1]");
-- 返回结果2
select json_extract ("[1,2,3,[4,5]]", "$[3][1]");
-- 返回结果5
-- json数据查询,返回指定字段
select
json_extract(data, "$.addr"),
json_extract(data, "$.name")
from json;
-- json_object函数
select json_object ("addr", "北京");
-- 返回:将()中的内容转成json,两两对应。例:(key1,key1value,key2,key2value,...)
-- 使用json_object函数插入数据
insert into json values (
null,
json_object("name", "王五", "addr", "上海", "age", "455")
);
-- json_insert
set @json = '{"a", 100, "b": [1,2]}';
select json_insert(@json, "$.a", 1, "$.c", "[true, false]");
-- 结果为:'{"a", 100, "b": [1,2], "c": "[true, false]"}'
-- 格式化数据,并对变量数据和新增数据做处理,变量中已有的值不会被替换,没有的会追加,如$.a,1不会提替换变量中a:100
-- json_merge
select json_merge('{"a": 1}', '{"b", 2}');
-- 合并数据,结果为{"a": 1, "b", 2}
-- 如果字段key一样,将合并为数组。例:
-- select json_merge('{"a": 1}', '{"a", 2}');
-- 结果为:{"a": [1, 2]}
json 索引
json是不能直接创建索引
通过虚拟列创建索引
创建虚拟列
-- 虚拟列使用
-- 使用函数json_extract创建的虚拟列查询参数时,需要带""。如:select json_extract(data, "$.name") from test_index where gen_col = '"张三"';
-- 使用函数
create table test_index(
data json,
gen_col varchar(10) generated always as (json_extract(data, "$.name")),
index idx(gen_col)
);
-- 插入数据
insert into test_index(data) values('{"name": "张三", "age": "15"}');
insert into test_index(data) values('{"name": "李四", "age": "15"}');
-- 通过索引查询数据
select json_extract(data, "$.name") from test_index where gen_col = "张三";