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 或 .tables 命令列出当前数据库中所有的表. 

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

这里 % 匹配任意多个字符. 所以 .table %%country%%% 的效果是一样的.

下划线 _ 匹配单个字符. 

sqlite> .table cit_
city

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数据库.

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

触发器

触发器

打开 xk2301.db, 这里已经有了 studentInfo 这张表. 现在需要记录每次更改此表时所作的操作. 例如, 对于表中的 name 属性进行更新之后, 在日志中记录 updated studentInfo: new name= ... 

sqlite> CREATE TEMP TABLE log(x);
sqlite> CREATE TEMP TRIGGER studentInfo_update_log
   ...> AFTER UPDATE OF name ON studentInfo
   ...> BEGIN
   ...>   INSERT INTO log VALUES('updated studentInfo: new name=' || new.name);
   ...> END;

sqlite> SELECT id, name FROM studentInfo limit 49,1;
id  name
--  ----
50  张宇青
sqlite> BEGIN;
sqlite> UPDATE studentInfo SET name='张雨晴' WHERE name='张宇青';
sqlite> SELECT * FROM log;
x
---------------------------------
updated studentInfo: new name=张雨清
sqlite> ROLLBACK;

这里 ROLLBACK 表示回滚到之前的状态. 此时事务结束, 不需要写 END; 

sqlite> .tables
studentInfo  temp.log

由于log表是临时表, 因此退出后就自动删除了.

sqlite> .q
PS D:\work\cs\Database\sqlite\students> shell46 .\xk2301.db
-- Loading resources from C:\Users\haife/.sqliterc
SQLite version 3.46.1 2024-08-13 09:16:08 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite> .table
studentInfo

视图

视图

在 MySQL 中, 对于数据库 test_students 中创建了 user 表. 创建语句如下. 

CREATE TABLE `user` (
  `id` char(9) NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `password` char(41) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
)

现在在 SQLite 中也创建该表, 并导入数据. 

sqlite> CREATE TABLE `user` (
(x1...>   `id` char(9) NOT NULL,
(x1...>   `username` varchar(20) DEFAULT NULL,
(x1...>   `password` char(41) DEFAULT NULL,
(x1...>   `timestamp` timestamp NULL DEFAULT NULL,
(x1...>   PRIMARY KEY (`id`)
(x1...> )
   ...> ;
sqlite> .table
studentInfo  user

SQLite中的变量

SQLite中的变量

sqlite 不认识 ^ 运算符. 

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

C/C++

参考

[...]

End






Thanks very much!