o Published a month agoAlaSQL is an open source project used on more than one million page views per month - and we appreciate any and all contributions we can get. .
Got a question? Ask on
and tag with &alasql&.
AlaSQL - (
) [aelae ?skju:?l] - is an open source SQL database for Javascript with a strong focus on query speed and data source flexibility for both relational data and schemaless data. It works in your browser, Node.js, and Cordova.
This library is designed for:
Fast in-memory SQL data processing for BI and ERP applications on fat clients
Easy ETL and options for persistencey by data import / manipulation / export of several formats
All major browsers, Node.js, and mobile applications
We focus on
by taking advantage of the dynamic nature of JavaScript when building up queries. Real-world solutions demand flexibility regarding where data comes from and where it is to be stored. We focus on flexibility by making sure you can
and query directly on data stored in Excel (both .xls and .xlsx), CSV, JSON, TAB, IndexedDB, LocalStorage, and SQLite files.
The library adds the comfort of a full database engine to your JavaScript app. No, really - it's working towards a full database engine complying with
spiced up with an additional syntax for handling NoSQL (schema-less) data and graph networks.
A) Traditional SQLalasql(&CREATE TABLE cities (city string, population number)&); alasql(&INSERT INTO cities VALUES ('Rome',2863223),('Paris',2249975),('Berlin',3517424),('Madrid',3041579)&); var res = alasql(&SELECT * FROM cities WHERE population & 3500000 ORDER BY population DESC&); console.log(res);
B) SQL on array of objectsvar data = [{a:1,b:10}, {a:2,b:20}, {a:1,b:30}]; var res = alasql('SELECT a, SUM(b) AS b FROM ? GROUP BY a',[data]);
console.log(res);
[{&a&:1,&b&:40},{&a&:2,&b&:20}]
C) Read from file must be async (Promise returned when SQL given as array)alasql(['SELECT * FROM XLS(&./data/mydata&) WHERE lastname LIKE &A%& and city = &London& GROUP BY name '])
.then(function(res){
console.log(res);
output depends on mydata.xls
}).catch(function(err){
console.log('Does the file exist? There was an error:', err);
D) Cheat and load your data directly alasql(&CREATE TABLE example1 (a INT, b INT)&); alasql.tables.example1.data = [
Insert data directly from JavaScript object...
{a:2,b:6},
{a:3,b:4}]; alasql(&INSERT INTO example1 VALUES (1,5)&);
...or insert data with normal SQL var res = alasql(&SELECT * FROM example1 ORDER BY b DESC&); console.log(res);
[{a:2,b:6},{a:1,b:5},{a:3,b:4}]
jsFiddle with
If you are familiar with SQL it should come as no surprise that proper use of indexes on your tables is essential to get good performance.
npm install --save alasql
node bower install --save alasql
bower import alasql from 'alasql';
meteor npm install -g alasql
command line
For the browser: include
src=&https://cdn.jsdelivr.net/npm/alasql@0.4&&/
Get started
The wiki has a great section on
When you feel you've gotten a grip, you can check out the wiki section about
or get inspired by the
Documentation:
Library CDN:
Try online:
Please note
All contributions are extremely welcome and greatly appreciated(!) -
The project has never received any funding and is based on unpaid voluntary work:
AlaSQL project is very young and still in an active development phase, therefore it may have .
Please, submit any bugs and suggestions .
AlaSQL uses
so please note that the major version is zero (0.y.z) and the API can not be considered 100% stable. Consider this before using the library in production and please check out the
Performance
AlaSQL is very focused on speed, and we make sure to use all the tricks we can find to make JavaScript spit out your results as quickly as possible. For example:
Queries are cached as compiled functions
Joined tables are pre-indexed
WHERE expressions are pre-filtered for joins
The results are good. Check out AlaSQL vs. other JavaScript SQL databases:
selecting with SUM, JOIN, and GROUP BY.
selecting with SUM, JOIN, and GROUP BY (in-memory operations for WebSQL - see )
for GROUP BY on 1,048,576 rows
Please remember to set indexes on your tables to speed up your queries.
page if you are unfamiliar with this concept (this is a part of ).
Features you might like
Traditional SQL
Use &good old& SQL on your data with multiple levels of: JOIN, VIEW, GROUP BY, UNION, PRIMARY KEY, ANY, ALL, IN, ROLLUP(), CUBE(), GROUPING SETS(), CROSS APPLY, OUTER APPLY, WITH SELECT, and subqueries. See the wiki to .
User defined functions in your SQL
You can use all benefits of SQL and JavaScript together by defining your own custom functions. Just add new functions to the alasql.fn object:
alasql.fn.myfn = function(a,b) {
return a*b+1;}var res = alasql('SELECT myfn(a,b) FROM one');
You can also define your own aggregator functions (like your own SUM(...)). See more
Compiled statements and functions
var ins = alasql.compile('INSERT INTO one VALUES (?,?)');ins(1,10);ins(2,20);
SELECT directly on your JavaScript data
Group your JavaScript array of objects by field and count number of records in each group:
var data = [{a:1,b:1,c:1},{a:1,b:2,c:1},{a:1,b:3,c:1}, {a:2,b:1,c:1}];var res = alasql('SELECT a, COUNT(*) AS b FROM ? GROUP BY a',[data]);console.log(res);
See more ideas for creative data manipulation
JavaScript Sugar
AlaSQL extends &good old& SQL to make it closer to JavaScript. The &sugar& includes:
Write Json objects - {a:'1',b:@['1','2','3']}
Acesss object propertires - obj-&property-&subproperty
Access Ooject and arrays elements - obj-&(a*1)
Access JavaScript functions - obj-&valueOf()
Format query output with SELECT VALUE, ROW, COLUMN, MATRIX
ES5 multiline SQL with var SQL = function(){/*select 'MY MULTILINE SQL'*/} and pass instead of SQL string (will not work if you compress your code)
Read and write Excel, and raw data files
You can import from and export to CSV, TAB, TXT, and JSON files. File extensions can be omitted. Calls to files will always be [[async]] so the approach is to chain the queries if you have more than one:
var tabFile = 'mydata.tab' alasql.promise([
&select * from txt('MyFile.log') where [0] like 'M%'&,
[&select * from tab(?) order by [1]&, [tabFile]],
note how to pass parameter when promises are chained
&select [3] as city,[4] as population from csv('./data/cities')&,
&select * from json('../config/myJsonfile')&
]).then(function(results){
console.log(results)
}).catch(console.error)
Read SQLite database files
AlaSQL can read (but not write) SQLite data files if you include the
src=&alasql.js&&/
src=&sql.js&&/
alasql(['ATTACH SQLITE DATABASE Chinook(&Chinook_Sqlite.sqlite&)',
'USE Chinook',
'SELECT * FROM Genre'
]).then(function(res){
console.log(&Genres:&,res.pop());
sql.js calls will always be async.
AlaSQL works in the console - CLI
After globally installing AlaSQL npm install alasql -g you can access AlaSQL via the commandline
& alasql &SET @data = @[{a:'1',b:?},{a:'2',b:?}]; SELECT a, b FROM @& 10 20[ 1, [ { a: 1, b: 10 }, { a: 2, b: 20 } ] ] & alasql &VALUE OF SELECT COUNT(*) as abc FROM TXT('README.md') WHERE LENGTH([0]) & ?& 140// Number of lines with more than 140 characters in README.md
Features you might love
AlaSQL ? D3.js
AlaSQL plays nice with d3.js and gives you a convenient way to integrate a specific subset of your data with the visual powers of D3. See more about
AlaSQL ? Excel
AlaSQL can export data to both
formats with coloring of cells and other Excel formatting functions.
AlaSQL ? Meteor
Meteor is amazing. You can query directly on your Meteor collections with SQL - simple and easy. See more about
AlaSQL ? Angular.js
Angular is great. In addition to normal data manipulation, AlaSQL works like a charm for exporting your present scope to Excel. See more about
AlaSQL ? Google Maps
Pinpointing data on a map should be easy. AlaSQL is great to prepare source data for Google Maps from, for example, Excel or CSV, making it one unit of work for fetching and identifying what's relevant. See more about
AlaSQL ? Google Spreadsheets
AlaSQL can query data directly from a Google spreadsheet. A good &partnership& for easy editing and powerfull data manipulation. See more about
Miss a feature?
Take charge and
to be implemented:
Limitations
Please be aware that AlaSQL has . Beside having some bugs, there are a number of limitations:
AlaSQL has a (long) list of keywords that must be escaped if used for column names. When selecting a field named key please write SELECT `key` FROM ... instead. This is also the case for words like `value`, `read`, `count`, `by`, `top`, `path`, `deleted`, `work` and `offset`. Please consult the .
It is OK to SELECT 1000000 records or to JOIN two tables with 10000 records in each (You can use streaming functions to work with longer datasources - see ) but be aware that the workload is multiplied so SELECTing from more than 8 tables with just 100 rows in each will show bad performance. This is one of our top priorities to make better.
Limited functionality for transactions (supports only for localStorage) - Sorry, transactions are limited, because AlaSQL switched to more complex approach for handling PRIMARY KEYs / FOREIGN KEYs. Transactions will be fully turned on again in a future version.
A (FULL) OUTER JOIN and RIGHT JOIN of more than 2 tables will not produce expected results. INNER JOIN and LEFT JOIN are OK.
Please use aliases when you want fields with the same name from different tables (SELECT a.id as a_id, b.id as b_id FROM ?).
At the moment AlaSQL does not work with JSZip 3.0.0 - please use version 2.x.
JOINing a sub-SELECT does not work. Please use a with structure () or fetch the sub-SELECT to a variable and pass it as an argument ().
AlaSQL uses the
library for saving files locally from the browser. Please be aware that it does not save files in Safari 8.0.
There are probably many others. Please help us fix them by . Thank you!
Use AlaSQL to convert data from CSV to Excel
ETL example:
alasql(['CREATE TABLE IF NOT EXISTS geo.country',
'SELECT * INTO geo.country FROM CSV(&country.csv&,{headers:true})',
'SELECT * INTO XLSX(&asia&) FROM geo.country WHERE continent_name = &Asia&'
).then(function(res){
results from the file asia.xlsx
Use AlaSQL as a Web Worker
AlaSQL can serve as a Web Worker. Please be aware that all interaction with AlaSQL when running must be async.
In the browser you can include alasql-worker.min.js instead of alasql.min.js and AlaSQL will figure out the rest:
src=&alasql-worker.min.js&&/var arr = [{a:1},{a:2},{a:1}];
alasql([['SELECT * FROM ?',[arr]]]).then(function(data){
console.log(data);
Another option is to include alasql.min.js as usual but call alasql.worker() as the first thing yourself:
src=&alasql.min.js&&/
alasql.worker();
var res = alasql(['select value 10']).then(function(res){
console.log(res);
}).catch(console.error);
Try this .
If using AlaSQL as Web Worker, you can import it traditionally as a script:
importScripts('alasql.min.js');
Webpack, Browserify, Vue and React (Native)
To use AlaSQL within a create-react-app (CRA) setup without ejecting it is: Please .
When targeting the browser, several code bundlers like Webpack and Browserify will pick up modules you might not want.
Here's a list of modules that AlaSQL may require in certain enviroments or for certain features:
React Native
react-native
react-native-fs
react-native-fetch-blob
XLSX/XLS support
es6-promise
There are several ways to handle AlaSQL with Webpack:
IgnorePlugin
Ideal when you want to control which modules you want to import.
var IgnorePlugin =
require(&webpack&).IgnorePlugin; module.exports = {
Will ignore the modules fs, path, xlsx, request, vertx, and react-native modules
plugins:[new IgnorePlugin(/(^fs$|cptable|jszip|xlsx|^es6-promise$|^net$|^tls$|^forever-agent$|^tough-cookie$|cpexcel|^path$|^request$|react-native|^vertx$)/)]};
module.noParse
As of AlaSQL 0.3.5, you can simply tell Webpack not to parse AlaSQL, which avoids all the dynamic require warnings and avoids using eval/clashing with CSP with script-loader.
...Don't parse alasql{module:noParse:[/alasql/]}
script-loader
If both of the solutions above fail to meet your requirements, you can load AlaSQL with .
Load alasql in the global scope with script-loaderimport &script!alasql&
This can cause issues if you have a CSP that doesn't allow eval.
Browserify
Read up on , , and
Example (using excluding)
var browserify = require(&browserify&);var b = browserify(&./main.js&).bundle();Will ignore the modules fs, path, xlsx[&fs&,&path&,&xlsx&,
... ].map(ignore =& b.ignore(ignore));
For some frameworks (lige Vue) alasql cant access XLSX by it self. We recommend handeling it by including AlaSQL the following way:
import XLSX from 'xlsx';alasql.utils.isBrowserify =alasql.utils.global.XLSX = XLSX;
Please remember to send the original event, and not the jQuery event, for elements. (Use event.originalEvent instead of myEvent)
JSON-object
You can use JSON objects in your databases (do not forget use == and !== operators for deep comparision of objects):
alasql& SELECT VALUE {a:'1',b:'2'} {a:1,b:2} alasql& SELECT VALUE {a:'1',b:'2'} == {a:'1',b:'2'} true alasql& SELECT VALUE {a:'1',b:'2'}-&b 2 alasql& SELECT VALUE {a:'1',b:(2*2)}-&b 4
Try AlaSQL JSON objects in
Experimental
Useful stuff, but there might be dragons
AlaSQL is a multi-paradigm database with support for graphs that can be searched or manipulated.
Who loves lovers of Alice?var res = alasql('SEARCH / ANY(&& && #Alice) name');console.log(res)
['Olga','Helen']
localStorage and DOM-storage
You can use browser localStorage and
as a data storage. Here is a sample:
alasql('CREATE localStorage DATABASE IF NOT EXISTS Atlas');alasql('ATTACH localStorage DATABASE Atlas AS MyAtlas');alasql('CREATE TABLE IF NOT EXISTS MyAtlas.City (city string, population number)');alasql('SELECT * INTO MyAtlas.City FROM ?',[[{city:'Vienna', population:1731000},
{city:'Budapest', population:1728000}]]);var res = alasql('SELECT * FROM MyAtlas.City');console.log(res);
Try this sample in . Run this sample
two or three times, and AlaSQL store more and more data in localStorage. Here, &Atlas& is
the name of localStorage database, where &MyAtlas& is a memory AlaSQL database.
You can use localStorage in two modes: SET AUTOCOMMIT ON to immediate save data
to localStorage after each statement or SET AUTOCOMMIT OFF. In this case, you need
to use COMMIT statement to save all data from in-memory mirror to localStorage.
AlaSQL supports plugins
AlaSQL supports plugins. To install a plugin you need to use the REQUIRE statement. See more
Alaserver - simple database server
Yes, you can even use AlaSQL as a very simple server for tests.
To run enter the command:
alaserver [port]
then type in browser something like &&
Warning: Alaserver is not multi-threaded, not concurrent, and not secured.
Regression tests
AlaSQL currently has over 1200 regression tests, but they only cover
of the codebase.
AlaSQL uses mocha for regression tests. Install mocha and run
& npm test
for in-browser tests (Please serve via localhost with, for example, http-server).
Tests with AlaSQL ASSERT from SQL
You can use AlaSQL's
operator to test the results of previous operation:
CREATE TABLE one (a INT);
INSERT INTO one VALUES (1),(2),(3);
SELECT * FROM one ORDER BY a DESC;
ASSERT [{a:3},{a:2},{a:1}];
SQLLOGICTEST
AlaSQL uses SQLLOGICTEST to test its compatibility with SQL-99. The tests include about 2 million queries and statements.
The testruns can be found in the .
Bleeding edge
If you want to try the most recent development version of the library please download
or visit the
to play around in the browser console.
Main contributors
AlaSQL is an OPEN Open Source Project. This means that:
Individuals making significant and valuable contributions are given commit-access to the project to contribute as they see fit. This project is more like an open wiki than a standard guarded open source project.
We appreciate any and all contributions we can get. If you feel like contributing, have a look at ._
Many thanks to Zach Carter for
parser generator, to the author of FileSaver.js, Andrew Kent for his ,
authors of
and other people for useful tools, which make our work much easier.
Related projects that have inspired us
- Export to Excel with colors and formats
- WebSQL shim over IndexedDB (work in progress)
- JavaScript MDX OLAP library (work in progress)
- list of databases on JavaScript
(C) , Andrey Gershun () & Mathias Rangel Wulff ()I have a .xlsx and .html files. How can I import the xlsx file to my html file using JavaScript, I already watched Lynda: JavaScript Essential Training by Simon Allardice and didn't get any clue how to do this! Googled a lot and still here.
Tried the similar questions...
Just need a script to copy it into my html file, and then my html file can read the xlsx and display it in the browser!
解决方案 You can use
JavaScript SQL library, which has special functionality to read XLSX files and put in HTML page. It uses
library to read XLSX files.
Disclaimer: I am the author of Alasql.
In this example:
Alasql takes Excel file from cdn.rawgit.com (you can replace it with your address
Alasql put result table to DIV with id="res" - you can change the name or add CSS to prettify result table
{headers:true} flag means that Alasql reads and writes headers
You can read CSV files as well - just replace XLSX() to CSV() in the example
For production purpose download alasql.min.js and xlsx.core.min.js to your server or use CDN.
See the working code snippet below:
alasql('select * into html("#res",{headers:true}) \
from xlsx("https://cdn.rawgit.com/agershun/alasql/version-0.0.36/test/test168.xlsx",\
{headers:true})')
&script src="https://cdn.jsdelivr.net/alasql/0.3/alasql.min.js"&&/script&
&script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.12/xlsx.core.min.js"&&/script&
&div id="res"&&/div&
本文地址： &
H！ 我有一个.xlsx和.html文件。如何使用JavaScript将xlsx文件导入我的html文件，我已经观看了Lynda：JavaScript Essential Training by Simon Allardice，没有得到任何线索如何做到这一点！ Googled很多，还在这里。 尝试类似的问题... 只需要一个脚本将其复制到我的html文件，然后我的html文件可以读取xlsx并显示在浏览器中！
解决方案 您可以使用 JavaScript SQL库，它有特殊的功能读取XLSX文件并放入HTML页面。它使用库读取XLSX文件。
免责声明：我是Alasql的作者。
在此示例中：
Alasql从cdn.rawgit.com获取Excel文件（您可以将其替换为您的地址
Alasql将结果表放到DIV，id =“res”
{headers：true}标志表示Alasql读取和写入标题
您可以将CSV文件 -
出于生产目的，将alasql.min.js和xlsx.core.min.js下载到您的服务器或使用CDN。只需将xlsx（）替换为CSV / li>
查看下面有效的代码段：
本文地址： &
扫一扫关注IT屋
微信公众号搜索 “ IT屋 ” ，选择关注
与百万开发者在一起
(window.slotbydup = window.slotbydup || []).push({
id: '5828425',
container: s,
size: '300,250',
display: 'inlay-fix'读取本地数据库文件
有时候我们可以将类似省份城市的文件存放在assets/文件夹名/××.sql下，当程序在创建数据库时读取该数据库文件，并执行其内的sql语句
在SqLiteOpenHelper类的onCreate方法中调用 executeAssetsSQL(db, "××.sql");
vcD4KPHByZSBjbGFzcz0="brush:">
* 读取数据库文件（.sql），并执行sql语句
private void executeAssetsSQL(SQLiteDatabase db, String schemaName)
BufferedReader in =
in = new BufferedReader(new InputStreamReader(AppApplication
.getIns().getAssets().open("aiyeqiaodb/" + schemaName)));
System.out.println("路径:aiyeqiaodb/" + schemaName);
String buffer = "";
while ((line = in.readLine()) != null)
if (line.trim().endsWith(";"))
db.execSQL(buffer.replace(";", ""));
buffer = "";
catch (IOException e)
LogUtil.i("db-error" + e.toString());
if (in != null)
in.close();
catch (IOException e)
LogUtil.i("db-error" + e.toString());
下面附上省份城市的数据库文件 ——××.sql
DROP TABLE IF EXISTS city_
create table IF NOT EXISTS
city_table
(_id integer primary key autoincrement, city varchar(20), cityID varchar(10), father varchar(10));
INSERT INTO city_table (_id, cityID, city, father) VALUES
(1, ';, '市辖区', ';),
(2, ';, '县', ';),
(3, ';, '市辖区', ';),
(4, ';, '县', ';),
(5, ';, '石家庄市', ';),
(6, ';, '唐山市', ';),
(7, ';, '秦皇岛市', ';),
(8, ';, '邯郸市', ';),
(9, ';, '邢台市', ';),
(10, ';, '保定市', ';),
(11, ';, '张家口市', ';),
(12, ';, '承德市', ';),
(13, ';, '沧州市', ';),
(14, ';, '廊坊市', ';),
(15, ';, '衡水市', ';),
(16, ';, '太原市', ';),
(17, ';, '大同市', ';),
(18, ';, '阳泉市', ';),
(19, ';, '长治市', ';),
(20, ';, '晋城市', ';),
(21, ';, '朔州市', ';),
(22, ';, '晋中市', ';),
(23, ';, '运城市', ';),
(24, ';, '忻州市', ';),
(25, ';, '临汾市', ';),
(26, ';, '吕梁市', ';),
(27, ';, '呼和浩特市', ';),
(28, ';, '包头市', ';),
(29, ';, '乌海市', ';),
(30, ';, '赤峰市', ';),
(31, ';, '通辽市', ';),
(32, ';, '鄂尔多斯市', ';),
(33, ';, '呼伦贝尔市', ';),
(34, ';, '巴彦淖尔市', ';),
(35, ';, '乌兰察布市', ';),
(36, ';, '兴安盟', ';),
(37, ';, '锡林郭勒盟', ';),
(38, ';, '阿拉善盟', ';),
(39, ';, '沈阳市', ';),
(40, ';, '大连市', ';),
(41, ';, '鞍山市', ';),
(42, ';, '抚顺市', ';),
(43, ';, '本溪市', ';),
(44, ';, '丹东市', ';),
(45, ';, '锦州市', ';),
(46, ';, '营口市', ';),
(47, ';, '阜新市', ';),
(48, ';, '辽阳市', ';),
(49, ';, '盘锦市', ';),
(50, ';, '铁岭市', ';),
(51, ';, '朝阳市', ';),
(52, ';, '葫芦岛市', ';),
(53, ';, '长春市', ';),
(54, ';, '吉林市', ';),
(55, ';, '四平市', ';),
(56, ';, '辽源市', ';),
(57, ';, '通化市', ';),
(58, ';, '白山市', ';),
(59, ';, '松原市', ';),
(60, ';, '白城市', ';),
(61, ';, '延边朝鲜族自治州', ';),
(62, ';, '哈尔滨市', ';),
(63, ';, '齐齐哈尔市', ';),
(64, ';, '鸡西市', ';),
(65, ';, '鹤岗市', ';),
(66, ';, '双鸭山市', ';),
(67, ';, '大庆市', ';),
(68, ';, '伊春市', ';),
(69, ';, '佳木斯市', ';),
(70, ';, '七台河市', ';),
(71, ';, '牡丹江市', ';),
(72, ';, '黑河市', ';),
(73, ';, '绥化市', ';),
(74, ';, '大兴安岭地区', ';),
(75, ';, '市辖区', ';),
(76, ';, '县', ';),
(77, ';, '南京市', ';),
(78, ';, '无锡市', ';),
(79, ';, '徐州市', ';),
(80, ';, '常州市', ';),
(81, ';, '苏州市', ';),
(82, ';, '南通市', ';),
(83, ';, '连云港市', ';),
(84, ';, '淮安市', ';),
(85, ';, '盐城市', ';),
(86, ';, '扬州市', ';),
(87, ';, '镇江市', ';),
(88, ';, '泰州市', ';),
(89, ';, '宿迁市', ';),
(90, ';, '杭州市', ';),
(91, ';, '宁波市', ';),
(92, ';, '温州市', ';),
(93, ';, '嘉兴市', ';),
(94, ';, '湖州市', ';),
(95, ';, '绍兴市', ';),
(96, ';, '金华市', ';),
(97, ';, '衢州市', ';),
(98, ';, '舟山市', ';),
(99, ';, '台州市', ';),
(100, ';, '丽水市', ';),
(101, ';, '合肥市', ';),
(102, ';, '芜湖市', ';),
(103, ';, '蚌埠市', ';),
(104, ';, '淮南市', ';),
(105, ';, '马鞍山市', ';),
(106, ';, '淮北市', ';),
(107, ';, '铜陵市', ';),
(108, ';, '安庆市', ';),
(109, ';, '黄山市', ';),
(110, ';, '滁州市', ';),
(111, ';, '阜阳市', ';),
(112, ';, '宿州市', ';),
(113, ';, '巢湖市', ';),
(114, ';, '六安市', ';),
(115, ';, '亳州市', ';),
(116, ';, '池州市', ';),
(117, ';, '宣城市', ';),
(118, ';, '福州市', ';),
(119, ';, '厦门市', ';),
(120, ';, '莆田市', ';),
(121, ';, '三明市', ';),
(122, ';, '泉州市', ';),
(123, ';, '漳州市', ';),
(124, ';, '南平市', ';),
(125, ';, '龙岩市', ';),
(126, ';, '宁德市', ';),
(127, ';, '南昌市', ';),
(128, ';, '景德镇市', ';),
(129, ';, '萍乡市', ';),
(130, ';, '九江市', ';),
(131, ';, '新余市', ';),
(132, ';, '鹰潭市', ';),
(133, ';, '赣州市', ';),
(134, ';, '吉安市', ';),
(135, ';, '宜春市', ';),
(136, ';, '抚州市', ';),
(137, ';, '上饶市', ';),
(138, ';, '济南市', ';),
(139, ';, '青岛市', ';),
(140, ';, '淄博市', ';),
(141, ';, '枣庄市', ';),
(142, ';, '东营市', ';),
(143, ';, '烟台市', ';),
(144, ';, '潍坊市', ';),
(145, ';, '济宁市', ';),
(146, ';, '泰安市', ';),
(147, ';, '威海市', ';),
(148, ';, '日照市', ';),
(149, ';, '莱芜市', ';),
(150, ';, '临沂市', ';),
(151, ';, '德州市', ';),
(152, ';, '聊城市', ';),
(153, ';, '滨州市', ';),
(154, ';, '荷泽市', ';),
(155, ';, '郑州市', ';),
(156, ';, '开封市', ';),
(157, ';, '洛阳市', ';),
(158, ';, '平顶山市', ';),
(159, ';, '安阳市', ';),
(160, ';, '鹤壁市', ';),
(161, ';, '新乡市', ';),
(162, ';, '焦作市', ';),
(163, ';, '濮阳市', ';),
(164, ';, '许昌市', ';),
(165, ';, '漯河市', ';),
(166, ';, '三门峡市', ';),
(167, ';, '南阳市', ';),
(168, ';, '商丘市', ';),
(169, ';, '信阳市', ';),
(170, ';, '周口市', ';),
(171, ';, '驻马店市', ';),
(172, ';, '武汉市', ';),
(173, ';, '黄石市', ';),
(174, ';, '十堰市', ';),
(175, ';, '宜昌市', ';),
(176, ';, '襄樊市', ';),
(177, ';, '鄂州市', ';),
(178, ';, '荆门市', ';),
(179, ';, '孝感市', ';),
(180, ';, '荆州市', ';),
(181, ';, '黄冈市', ';),
(182, ';, '咸宁市', ';),
(183, ';, '随州市', ';),
(184, ';, '恩施土家族苗族自治州', ';),
(185, ';, '省直辖行政单位', ';),
(186, ';, '长沙市', ';),
(187, ';, '株洲市', ';),
(188, ';, '湘潭市', ';),
(189, ';, '衡阳市', ';),
(190, ';, '邵阳市', ';),
(191, ';, '岳阳市', ';),
(192, ';, '常德市', ';),
(193, ';, '张家界市', ';),
(194, ';, '益阳市', ';),
(195, ';, '郴州市', ';),
(196, ';, '永州市', ';),
(197, ';, '怀化市', ';),
(198, ';, '娄底市', ';),
(199, ';, '湘西土家族苗族自治州', ';),
(200, ';, '广州市', ';),
(201, ';, '韶关市', ';),
(202, ';, '深圳市', ';),
(203, ';, '珠海市', ';),
(204, ';, '汕头市', ';),
(205, ';, '佛山市', ';),
(206, ';, '江门市', ';),
(207, ';, '湛江市', ';),
(208, ';, '茂名市', ';),
(209, ';, '肇庆市', ';),
(210, ';, '惠州市', ';),
(211, ';, '梅州市', ';),
(212, ';, '汕尾市', ';),
(213, ';, '河源市', ';),
(214, ';, '阳江市', ';),
(215, ';, '清远市', ';),
(216, ';, '东莞市', ';),
(217, ';, '中山市', ';),
(218, ';, '潮州市', ';),
(219, ';, '揭阳市', ';),
(220, ';, '云浮市', ';),
(221, ';, '南宁市', ';),
(222, ';, '柳州市', ';),
(223, ';, '桂林市', ';),
(224, ';, '梧州市', ';),
(225, ';, '北海市', ';),
(226, ';, '防城港市', ';),
(227, ';, '钦州市', ';),
(228, ';, '贵港市', ';),
(229, ';, '玉林市', ';),
(230, ';, '百色市', ';),
(231, ';, '贺州市', ';),
(232, ';, '河池市', ';),
(233, ';, '来宾市', ';),
(234, ';, '崇左市', ';),
(235, ';, '海口市', ';),
(236, ';, '三亚市', ';),
(237, ';, '省直辖县级行政单位', ';),
(238, ';, '市辖区', ';),
(239, ';, '县', ';),
(240, ';, '市', ';),
(241, ';, '成都市', ';),
(242, ';, '自贡市', ';),
(243, ';, '攀枝花市', ';),
(244, ';, '泸州市', ';),
(245, ';, '德阳市', ';),
(246, ';, '绵阳市', ';),
(247, ';, '广元市', ';),
(248, ';, '遂宁市', ';),
(249, ';, '内江市', ';),
(250, ';, '乐山市', ';),
(251, ';, '南充市', ';),
(252, ';, '眉山市', ';),
(253, ';, '宜宾市', ';),
(254, ';, '广安市', ';),
(255, ';, '达州市', ';),
(256, ';, '雅安市', ';),
(257, ';, '巴中市', ';),
(258, ';, '资阳市', ';),
(259, ';, '阿坝藏族羌族自治州', ';),
(260, ';, '甘孜藏族自治州', ';),
(261, ';, '凉山彝族自治州', ';),
(262, ';, '贵阳市', ';),
(263, ';, '六盘水市', ';),
(264, ';, '遵义市', ';),
(265, ';, '安顺市', ';),
(266, ';, '铜仁地区', ';),
(267, ';, '黔西南布依族苗族自治州', ';),
(268, ';, '毕节地区', ';),
(269, ';, '黔东南苗族侗族自治州', ';),
(270, ';, '黔南布依族苗族自治州', ';),
(271, ';, '昆明市', ';),
(272, ';, '曲靖市', ';),
(273, ';, '玉溪市', ';),
(274, ';, '保山市', ';),
(275, ';, '昭通市', ';),
(276, ';, '丽江市', ';),
(277, ';, '思茅市', ';),
(278, ';, '临沧市', ';),
(279, ';, '楚雄彝族自治州', ';),
(280, ';, '红河哈尼族彝族自治州', ';),
(281, ';, '文山壮族苗族自治州', ';),
(282, ';, '西双版纳傣族自治州', ';),
(283, ';, '大理白族自治州', ';),
(284, ';, '德宏傣族景颇族自治州', ';),
(285, ';, '怒江傈僳族自治州', ';),
(286, ';, '迪庆藏族自治州', ';),
(287, ';, '拉萨市', ';),
(288, ';, '昌都地区', ';),
(289, ';, '山南地区', ';),
(290, ';, '日喀则地区', ';),
(291, ';, '那曲地区', ';),
(292, ';, '阿里地区', ';),
(293, ';, '林芝地区', ';),
(294, ';, '西安市', ';),
(295, ';, '铜川市', ';),
(296, ';, '宝鸡市', ';),
(297, ';, '咸阳市', ';),
(298, ';, '渭南市', ';),
(299, ';, '延安市', ';),
(300, ';, '汉中市', ';),
(301, ';, '榆林市', ';),
(302, ';, '安康市', ';),
(303, ';, '商洛市', ';),
(304, ';, '兰州市', ';),
(305, ';, '嘉峪关市', ';),
(306, ';, '金昌市', ';),
(307, ';, '白银市', ';),
(308, ';, '天水市', ';),
(309, ';, '武威市', ';),
(310, ';, '张掖市', ';),
(311, ';, '平凉市', ';),
(312, ';, '酒泉市', ';),
(313, ';, '庆阳市', ';),
(314, ';, '定西市', ';),
(315, ';, '陇南市', ';),
(316, ';, '临夏回族自治州', ';),
(317, ';, '甘南藏族自治州', ';),
(318, ';, '西宁市', ';),
(319, ';, '海东地区', ';),
(320, ';, '海北藏族自治州', ';),
(321, ';, '黄南藏族自治州', ';),
(322, ';, '海南藏族自治州', ';),
(323, ';, '果洛藏族自治州', ';),
(324, ';, '玉树藏族自治州', ';),
(325, ';, '海西蒙古族藏族自治州', ';),
(326, ';, '银川市', ';),
(327, ';, '石嘴山市', ';),
(328, ';, '吴忠市', ';),
(329, ';, '固原市', ';),
(330, ';, '中卫市', ';),
(331, ';, '乌鲁木齐市', ';),
(332, ';, '克拉玛依市', ';),
(333, ';, '吐鲁番地区', ';),
(334, ';, '哈密地区', ';),
(335, ';, '昌吉回族自治州', ';),
(336, ';, '博尔塔拉蒙古自治州', ';),
(337, ';, '巴音郭楞蒙古自治州', ';),
(338, ';, '阿克苏地区', ';),
(339, ';, '克孜勒苏柯尔克孜自治州', ';),
(340, ';, '喀什地区', ';),
(341, ';, '和田地区', ';),
(342, ';, '伊犁哈萨克自治州', ';),
(343, ';, '塔城地区', ';),
(344, ';, '阿勒泰地区', ';),
(345, ';, '省直辖行政单位', ';);
DROP TABLE IF EXISTS province_
create table IF NOT EXISTS
province_table
integer primary key autoincrement, province varchar(20), provinceID
varchar(10));
INSERT INTO province_table (_id, provinceID, province) VALUES
(1, ';, '北京市'),
(2, ';, '天津市'),
(3, ';, '河北省'),
(4, ';, '山西省'),
(5, ';, '内蒙古自治区'),
(6, ';, '辽宁省'),
(7, ';, '吉林省'),
(8, ';, '黑龙江省'),
(9, ';, '上海市'),
(10, ';, '江苏省'),
(11, ';, '浙江省'),
(12, ';, '安徽省'),
(13, ';, '福建省'),
(14, ';, '江西省'),
(15, ';, '山东省'),
(16, ';, '河南省'),
(17, ';, '湖北省'),
(18, ';, '湖南省'),
(19, ';, '广东省'),
(20, ';, '广西壮族自治区'),
(21, ';, '海南省'),
(22, ';, '重庆市'),
(23, ';, '四川省'),
(24, ';, '贵州省'),
(25, ';, '云南省'),
(26, ';, '西藏自治区'),
(27, ';, '陕西省'),
(28, ';, '甘肃省'),
(29, ';, '青海省'),
(30, ';, '宁夏回族自治区'),
(31, ';, '新疆维吾尔自治区'),
(32, ';, '台湾省'),
(33, ';, '香港特别行政区'),
(34, ';, '澳门特别行政区');