常用语句
DDL
show databases;
create database if not exists test default charset utf8 COLLATE utf8_general_ci;
use test;
show tables;
create table if not exists student (
id int primary key ,
name varchar(20) not null,
age int default 0);
drop table student;
alter table student add stu_no varchar(20) not null after id;
alter table student drop column stu_no;
mysqldump -uroot -p
mysql -uroot -p<test.sql
source test.sql
DML
create table if not exists testdata (
id int unsigned not null primary key auto_increment,
payload json default null,
response json default null)
engine=InnoDB default charset=utf8;
insert into testdata(payload, response) values (
'{"id": 1, "name": "zhangsan", "age": 18}', '{"code": "0000", "data": "zhangsan", "msg": "OK"}');
insert into testdata(payload, response) values (
json_object("id", 2, "name","lisi", "age", 33), json_array(1,2,3,4,5));
select id, payload->'$.id' no,payload->'$.name' name,response->'$.code' code from testdata where payload->'$.age' >20;
>
+
| id | no | name | code |
+
| 2 | 2 | "lisi" | NULL |
+
1 row in set (0.00 sec)