MariaDB 10.3.29 簡單備忘

-- 編碼方式 (如utf8mb4_bin)

show collation;
show collation where collation like 'utf8mb4%';
show collation where sortlen like '8' and charset like 'utf8%';

-- 查看既有資料庫的資訊

select * from information_schema.schemata;

-- 建立二進位編碼的資料庫，用 like 指令時，就會區分大小寫
create database test character set 'utf8mb4' collate 'utf8mb4_bin';

use test;

-- 簡略資料型態說明如下
--
-- 整數，如 int
-- 精確浮點數，如decimal(20,6)
-- 文字，varchar(16383) (utf8mb4的最大值為16383)
-- 超大文字，longtext (將近4GB)

-- 簡略要點說明如下
--
-- 兩個單引號等於一個單引號
-- like 條件用 % 當萬用字
-- limit 可以限定回傳筆數

create table tb1(i int primary key, nvc nvarchar(4000) not null);

insert into tb1 values(1,'It''s a big deal!');
insert into tb1 values(2,'Hello, maraidb!');
insert into tb1 values(3,'Marry is good.');
insert into tb1 values(4,'John is a boy!');

select * from tb1 as a where lcase(a.nvc) like 'it''s%';
select concat('asd','123','good') as yes;
select * from tb1 as a where a.i between 2 and 3;
select * from tb1 limit 3;

update tb1 set nvc='Hello, mariadb! So great!' where i=2;

delete from tb1 where i=7;

create table tb2(d2 decimal(20,6));
insert into tb2(d2) values(3.1415926);
insert into tb2(d2) values(1232133.37891273);
select * from tb2;

create table tb3(lt longtext not null);

insert into tb3(lt) values('aksdjhasjkdsakfjhsadjfkADhiuewryhweir');
insert into tb3(lt) values('-------ha1231232131231231DE23655787969hsadjfkhiuewryhweir');
insert into tb3(lt) values('a987651298432178564541321ADr');

select * from tb3 where lt like '%987%';
select * from tb3 where lt like '%--%';
select * from tb3 where lt like '%AD%';
select * from tb3 where lt like '%ad%';

insert into tb3(lt) values('大小寫許蓋功氷都測試一下');

select * from tb3 where lt like '%許%';
select * from tb3 where lt like '%蓋%';
select * from tb3 where lt like '%功%';
select * from tb3 where lt like '%氷%';

select * from tb3 where lt like '%a%';