of {$slidecount} ½ {$title} ATZJG.NET {$author}

首页






World_x 数据库
— 数据库原理及应用实验 —


Haifeng Xu


(hfxu@yzu.edu.cn)

References:

目录

导入 world 数据库

导入 world 数据库

world_x_for_sqlite.sql 这个文件修改自 world_x.sql. 后者是使用 phpMyAdmin 备份 MySQL 中的数据库 world_x 而来的.

将 world_x_for_sqlite.sql 中的内容导入到 world.db 中. 

sqlite3 world2.db < world_x_for_sqlite.sql

注意事项

SQLite 和 MySQL 的数据库模式定义语言不一定通用, 比如下面这些语句是 MySQL 中的语句, 并不适用于SQLite. 

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

另外

world 数据库的基本结构

查询

.database

使用命令 .database 或 .databases 列出文件world.db中的数据库名. 

sqlite> .database
main: D:\work\cs\Database\sqlite\world_x\world.db r/w

注意数据库名默认是 main.

.schema

.schema 用于显示数据库模式, 如果显示某个表的结构, 则后面跟表名. 例如显示city表的结构可使用 .schema city. 

sqlite> .schema city
CREATE TABLE `city` (
  `ID`INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Info` json DEFAULT NULL
);

下面列出所有表的结构. 

sqlite> .schema
CREATE TABLE `city` (
  `ID`INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Info` json DEFAULT NULL
);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
);
CREATE TABLE `countryinfo` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS (json_extract(`doc`,'$._id')) STORED NOT NULL,
  `_json_schema` json GENERATED ALWAYS AS ('{"type":"object"}') VIRTUAL
);
CREATE TABLE IF NOT EXISTS "_countrylanguage_old" (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` TEXT CHECK(`IsOfficial` IN ('T','F')),
  `Percentage` decimal(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`)
);
CREATE TABLE `countrylanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` TEXT CHECK(`IsOfficial` IN ('T','F')),
  `Percentage` decimal(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`),
  CONSTRAINT `countrylanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
);

.table

.table 命令列出当前数据库中所有的表. 

sqlite> .table
_countrylanguage_old  country               countrylanguage
city                  countryinfo

若想列出包含country字符串的所有表, 则使用 .table %country% 

.tables ?TABLE?          List names of tables matching LIKE pattern TABLE

sqlite> .table %country%
_countrylanguage_old  countryinfo
country               countrylanguage

这里 % 匹配任意多个字符.

select count(*) from

查询 city 表总共有多少条记录. 

sqlite> select count(*) from city
   ...> ;
count(*)
--------
4079
sqlite>

World数据库的具体内容

查询录入的中国城市

查询city中收录的中国城市信息.

首先通过命令 .schema city 获知city表的数据库基本模式为 city(ID,Name,CountryCode,District,Info). 中国的CountryCode 为CHN. 因此, 输入下面的代码列出录入的所有中国城市. 

sqlite> select * from city where CountryCode='CHN';

由于总数有363个, 我们列出前10个城市如下. 只需加上limit 10. 

sqlite> select * from city where CountryCode='CHN' limit 10;
ID    Name                CountryCode  District      Info
----  ------------------  -----------  ------------  -----------------------
1890  Shanghai            CHN          Shanghai      {"Population": 9696300}
1891  Peking              CHN          Peking        {"Population": 7472000}
1892  Chongqing           CHN          Chongqing     {"Population": 6351600}
1893  Tianjin             CHN          Tianjin       {"Population": 5286800}
1894  Wuhan               CHN          Hubei         {"Population": 4344600}
1895  Harbin              CHN          Heilongjiang  {"Population": 4289800}
1896  Shenyang            CHN          Liaoning      {"Population": 4265200}
1897  Kanton [Guangzhou]  CHN          Guangdong     {"Population": 4256300}
1898  Chengdu             CHN          Sichuan       {"Population": 3361500}
1899  Nanking [Nanjing]   CHN          Jiangsu       {"Population": 2870300}

备份与恢复

数据的备份与恢复

备份

使用命令 .output 指定备份到某个文件, 然后使用 .dump 执行备份. 

sqlite> .output world_bak_2024-9-23.sql
sqlite> .dump

或者在未启动 SQLite 时用下面的方式备份 

$ sqlite3 world.db .dump > world_bak_2024-9-23.sql

恢复

当要恢复数据时, 用下面的命令. 

$ sqlite3 world.db .dump < world_bak_2024-9-23.sql

SQL

字面文本括在单引号中, 若文本中本身有单引号时, 则用连续两个单引号代表. 例如 'O''Reilly' . 双引号括起来的是标识符, 例如表名、属性名等. C-风格的转义符 \' 并不是 SQL 标准, 在 SQLite 中并不被支持. BLOB(二进制数据)可以用以 x 或 X 开头并以单引号括起来的十六进制字符表示. 例如 x'AC2E9AED'.

SQL 语句中属性与属性之间使用逗号分隔, 最后一个属性不再跟逗号.

跨数据库查询

跨数据库查询(cross-database queries)时, 必须指定数据库的名称. 语法是 

[[database_name.]table_name.]column_name

若未给定数据库名, 则假设我们正在使用默认连接的main数据库.

若表名或表的别名都省略, 则系统会猜出正在使用该列名(属性名)的表, 但是当发生歧义时会返回错误.

三值逻辑

三值逻辑(Three-Valued Logic)

SQLite中的变量

SQLite中的变量

sqlite 不认识 ^ 运算符. 

sqlite> select (x+y)^2;
Parse error: unrecognized token: "^"
  select (x+y)^2;
              ^--- error here

C/C++

参考

[...]

End






Thanks very much!