《SQL必知必会》学习笔记（sqlite）

第0课 准备数据

下载了书中提供的sqlite数据库内容。

sqlite学习

runoob提供的sqlite教程

打开书中的例子的方法

sqlite3 tysql.sqlite

常用命令

.help

.show 配置输出样式

.schema 显示create语句

格式化输出

您可以使用下列的点命令来格式化输出

sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>

更多mode样式参考文章

sqlite_master 表格

主表中保存数据库表的关键信息，并把它命名为 sqlite_master。

查看sqlite_master的create语句。

sqlite>  .schema sqlite_master
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

查看sqlite_master中所有的table(书中例子)

sqlite> select * from sqlite_master where type="table";
type        name        tbl_name    rootpage    sql
----------  ----------  ----------  ----------  --------------------------------------------------------------------------------------------------------------------
table       Customers   Customers   2           CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KE
table       OrderItems  OrderItems  4           CREATE TABLE OrderItems
(
  order_num  int          NOT NULL                      ,
  order_item int          NOT NULL                      ,
  prod_id    char(10)     NOT NULL                      ,
  quantity   int          NOT NULL                      ,
  item_price decimal(8,2) NOT NULL                      ,
  PRIMARY KEY (or
table       Orders      Orders      7           CREATE TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL ,
  cust_id    char(10) NOT NULL ,
  PRIMARY KEY (order_num)      ,
  FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)
)
table       Products    Products    10          CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL     ,
  PRIMARY KEY (prod_id)             ,
  FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id)
)
table       Vendors     Vendors     12          CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL ,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL     ,
  vend_city    char(50) NULL     ,
  vend_state   char(5)  NULL     ,
  vend_zip     char(10) NULL     ,
  vend_country char(50) NULL     ,
  PRIMARY KEY (vend_id)
)
Run Time: real 0.001 user 0.000166 sys 0.000119

sqlite_mater中还存储了什么？

sqlite> SELECT DISTINCT type FROM sqlite_master;
type
----------
table
index
Run Time: real 0.002 user 0.000127 sys 0.000171


sqlite> select * from sqlite_master where type="index";
type        name                          tbl_name    rootpage    sql
----------  ----------------------------  ----------  ----------  ----------
index       sqlite_autoindex_Customers_1  Customers   3
index       sqlite_autoindex_OrderItems_  OrderItems  5
index       sqlite_autoindex_Orders_1     Orders      9
index       sqlite_autoindex_Products_1   Products    11
index       sqlite_autoindex_Vendors_1    Vendors     13
Run Time: real 0.000 user 0.000116 sys 0.000068

例子的数据

假想 婉拒经销商 订单录入系统

1.Vendors表

销售产品的供应商

sqlite>  .schema Vendors
CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL ,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL     ,
  vend_city    char(50) NULL     ,
  vend_state   char(5)  NULL     ,
  vend_zip     char(10) NULL     ,
  vend_country char(50) NULL     ,
  PRIMARY KEY (vend_id)
);

所在城市，所在州（state），邮编（zip）

2.Products表

产品目录

sqlite>  .schema Products
CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL     ,
  PRIMARY KEY (prod_id)             ,
  FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id)
);

外键

3.Customers表

顾客信息

sqlite> .schema customers
CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL