示例,对JSON数据进行update,insert,delete和追加数据元素

BULK INSERT
(Transact-SQL).aspx)

 查看数据表的结构的两种方式

示例,有表:dt_json,存储以下数据:

2,使用bulk insert
将txt数据导入到数据库中,文本数据使用Tab分割Column,使用换行符分割row。

这次的就到这,如果还缺少什么,或写错什么欢迎指出!

图片 1图片 2

图片 3

这种方法可以用change的新旧名字相同的方法来实现相同的效果,即change为小万能

1,使用Path模式,控制JSON结构的Path(层次)

如果有些Column没有值,设置 KEEPNULLS   选项,表示将该column设置为NULL。

一  拓展表的好助手

通过For JSON  Auto/Path,将关系表数据存储为JSON格式,

重要参数注释:

 

在嵌套的JSON上,使用JSON_Query(expression,path),返回数据,然后再对其进行JSON
格式:

  • BULK **INSERT**  [ schema_name ] .
    [ table_name ]    
  • FROM ‘data_file’    
  • WITH (Arguments)]

 

图片 4图片 5

--populate data 
bulk insert dbo.txt_staging
from N'D:\abc.csv'
WITH(
    FIELDTERMINATOR = N',',
    ROWTERMINATOR = N'\n',
    CODEPAGE =N'raw'
)

  

for json path,without_array_wrapper

3,在导入Unicode时,需要将.txt文档保存为Unicode 编码方式

 

2,OpenJSON
函数的另外一个功能是遍历数组,为数组中的每一个元素产生一个数据行

--create staging table
create table dbo.txt_staging
(
col1 nvarchar(255),
col2 nvarchar(255),
col3 nvarchar(255)
)
go

--populate data 
bulk insert dbo.txt_staging
from N'D:\test.txt'
WITH(
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'
)

喝杯茶,休息一下

1,空JSON

BULK INSERT schema_name . table_name 
FROM 'data_file' 
WITH 
(
FIELDTERMINATOR = 'field_terminator',
ROWTERMINATOR = 'row_terminator',
DATAFILETYPE=‘WideChar’
);

 

View Code

1,bulk insert命令,经过简化如下

 数据表字段的删除

在查询时,经常会返回JSON数组,使用[index]来遍历数组元素,数组下标从0开始,例如,以下JSON数组,及其查询示例:

在使用bulk insert导入时,必须设置DATAFILETYPE=’widechar’,选项可能值有:
‘char’ | ‘native’| ‘widechar’ | ‘widenative’ ,默认值是char。

 

select top 3 t.name
    ,o.object_id
    ,o.type
from sys.objects o 
inner join sys.tables t 
    on o.object_id=t.object_id
for json auto

参考doc:

图片 6

返回的数据格式是:

  • ‘ **data_file ‘**:指定数据文件的full path,bulk
    insert命令将数据从该文件导入到Target Table中
  • ROWTERMINATOR = ‘row_terminator’ : 指定分隔行的字符,使用该字符来分割行(Row);
  • FIELDTERMINATOR = ‘field_terminator’ :指定分隔字段的字符,使用该字符来分割字段(Field或Column);
  • DATAFILETYPE = { ‘char’ | ‘native’| ‘widechar’ | ‘widenative’ }:指定data
    file编码(Encoding)的类型,推荐使用widechar编码;
  • CODEPAGE = { ‘ACP’ | ‘OEM’ | ‘RAW’ | ‘code_page’ } :如果 data file
    中含有单字节(char或varchar)字符数据,使用CodePage参数指定字符列的CodePage;

  • BATCHSIZE = batch_size :指定一个batch包含的数据行数量,在将数据复制到Table中时,每一个Batch作为一个单独的事务,如果一个batch复制失败,那么事务回滚。默认情况下,data
    file中的所有数据作为一个batch。Specifies the number of rows
    in a batch. Each batch is copied to the server as one transaction.
    If this fails, SQL Server commits or rolls back the transaction for
    every batch. By default, all data in the specified data file is one
    batch. 

  • CHECK_CONSTRAINTS :指定在执行bulk
    insert操作期间,必须检查插入的数据是否满足Target
    Table上的所有约束。如果没有指定
    CHECK_CONSTRAINTS 选项,则所有 CHECK 和 FOREIGN KEY 约束都将被忽略,并且,在此操作之后,表上的所有约束将标记为不可信(not-trusted);
      
  • FIRE_TRIGGERS :指定是否启动Insert触发器,如果指定该选项,每个batch成功插入后,会执行Insert触发器;如果不指定该选项,不会执行Insert
    触发器;
  • KEEPIDENTITY :指定将data
    file中的标识值插入到标识列(Identity
    Column)中,如果不指定KeepIdentity选项,Target
    Table中的ID列会自动分配唯一的标识值;  
  • KEEPNULLS:指定在执行bulk
    insert操作期间,空列(Empty
    Columns)应保留NULL值,而不是插入列的默认值
  • TABLOCK :指定在执行bulk
    insert操作期间,获取一个表级锁,持有表级锁,能够减少锁竞争(Lock
    Contention),提高导入性能

查看一下我们数据库里新建的table表

declare @json nvarchar(max)
set @json = 
N'{
    "info":{  
      "type":1,
      "address":{  
        "town":"bristol",
        "county":"avon",
        "country":"england"
      },
      "tags":["sport", "water polo"]
   },
   "type":"basic"
}'

select
  json_value(@json, '$.type') as type,
  json_value(@json, '$.info.type') as info_type,
  json_value(@json, '$.info.address.town') as town,
  json_value(@json, '$.info.tags[0]') as tag
bulk insert dbo.txt_staging
from N'D:\abc.txt'
WITH(
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n',
    DATAFILETYPE ='widechar' ,
    KEEPNULLS
)

二 备份表的好助手

Path表达式查询的数据是:

图片 7

方法一(适合查看此table数据表有什么列名和约束)

3,OpenJSON 函数搭配Apply使用,为表中的JSON数据转换成关系表形式

 

MariaDB [data_name]> show tables; #查看当前数据库的所有数据表(包括视图表)
+---------------------+
| Tables_in_data_name |
+---------------------+
| table_name          | # table数据表的名字
+---------------------+
1 row in set (0.00 sec)

1,Path Mode

附:Bulk Insert 命令的调用格式:

最后一个 懒人神器  ——模板建表

图片 8图片 9

将文本数据导入到数据库中的方法有很多,将文本格式(csv和txt)导入到SQL
Server中,bulk insert是最简单的实现方法

  

七,索引JSON数据

3,使用bulk insert 将csv的数据导入数据库中
CSV文件使用“,”作为列分隔符,使用“\n”作为行分隔符

 

[
{...}
]
--path expression
lax $[0]

查看数据库

 

MariaDB [data_name]> show create table table_name\G # 这里吧;改成了\G 发现显示的样式又不同了,更加养眼了(强迫症的福音) \G不光是这里,
                                所有显示的的SQL语句都可以用\G来代替;

*************************** 1. row ***************************
       Table: table_name
Create Table: CREATE TABLE `table_name` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

View Code

  

When you use OPENJSON with an
explicit schema, the function returns a table with the schema that you
defined in the WITH clause. In the WITH clause, you define columns,
their types, and the paths of the source properties for each
column.

 红色为命令 蓝色为自定义名

OPENJSON函数是一个行集函数(RowSet),能够将JSON数据转换为关系表,

 

九,编程注意事项

**************************************
我是动感光波****************************************

  • Auto 模式:根据select语句中column的顺序,自动生成JSON数据的格式;
  • Path 模式:使用column name的格式来生成JSON数据的格式,column
    name使用逗号分隔(dot-separated)表示组-成员关系;

数据表的字段修改 

图片 10图片 11

MariaDB [data_name]> show create table table_name; #查看table数据表的创建的SQL语句 (可以看出当时这个数据表创建而使用的SQL语句方便查阅约束名和设置的约束其他的细节)
+------------+-------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                  |
+------------+-------------------------------------------------------------------------------------------------------------------------------+
| table_name | CREATE TABLE `table_name` (      # ENGINE: 表示这个表类型,目前Mysql最常用的是MyISAM、InnoDB两种表类型 各有各优势 两表的区别点击这里
         |   `id` int(11) NOT NULL,        # CHARSET: 表示这个表的字符编码为Latin1(不支持中文)可以设置主流的gb2312 或utf8修改编码的具体方式点击这里
        |   `name` varchar(20) DEFAULT NULL  # 有关中文字符编码的一劳永逸具体的设定解决办法在这
        |    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • $:表示JSON的内容,是最外层大括号中的所有Item,本例是一个people数组,数组的下标是从0开始的;
  • $.people[0]:表示people数组的第一元素:{
    “name”: “Jane”, “surname”: null, “active”: true }
  • $.people[0].name
    :从people数组的第一个元素中,查询Key是Name的Item对应的数据,本例是John;
  • $.people[1].surname:people数组中部存在surname
    字段,由于该Path 表达式没有声明Path
    Mode,默认值是lax,当Path表达式出现错误时,返回NULL;

 

alter table dbo.dt_json
add category_type as (cast(json_value(category,'$.type') as int)) persisted;

create nonclustered index idx_dt_json_category_type
on dbo.dt_json
(
category_type
)
include(name);

有一天项目经理说忘了告诉你这个表缺少重要的一个字段,友谊的小船说翻就翻,但是别担心重做,SQL提供了反悔的机会可以任意添加或删除列

返回的数据格式是

MariaDB [(none)]> create database data_name; #创建数据库   data_name为创建的自定义数据库名字 
                                 (注:创建的database不加s后缀,新手可别把查看数据库命令的databases搞混了)
Query OK, 1 row affected (0.00 sec) #表示SQL语句执行成功
select t.*,sl.result,sl.time
from [dbo].[WebPages] sl 
cross apply openjson(JSON_QUERY(Parameters,'$.CategoryList'))
with
(
    ID varchar(64) '$.ID',
    name varchar(64) '$.Name',
    Type varchar(64) '$.Type'
)
 as t
where sl.action='New Product' and t.Type in('Blogs','Forums')
order by sl.time desc

如果是一个列的设置出错删除再修改一遍不会是很麻烦吗?这里有懒人办法 ,
删除添加二合一 小霸王修改机  

图片 12

  

[
  {
    "Id": 12,
    "JsonData": "[{\"UnitPrice\":12, \"OrderQty\":1}]"
  }
]
MariaDB [data_name]> describe table_name; #查看table数据表结构的方法一 (直观可以看清楚数据表的列与绑定的约束)
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra | #field :列名   type:插入数据类型  null:是否为非空约束 NO说明不能为空(插入数据如果为空,插入SQL会失败)
+-------+-------------+------+-----+---------+-------+ #KEY: 主键约束或外键约束 (空表示没有约束)     default:表示当插入数据为空时默认插入  (空代表w为null)
| id    | int(11)     | NO   |     | NULL    |       | #Extra: 中出现的信息一般不是太重要  对Extra等列意义深究的请查看别人的博客点击此处 
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

三,通过Path查询JSON数据

 

 

describe 可以用desc缩写来代替

View Code

 

2,嵌套JSON结构

 

{ "people":  
  [  
    { "name": "John", "surname": "Doe" },  
    { "name": "Jane", "surname": null, "active": true }  
  ]  
} 

发表评论

电子邮件地址不会被公开。 必填项已用*标注