建表如下
例4-4 mysql> create table customer(
->cno char(4) primary key,
->cname varchar(20) not null,
->birthdate datetime,
->sex char(2),
->address varchar(50) default'北京市'
->);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into customer values
->("C101","马栋梁","1990-05-20","男","辽宁大连"),
->("C102","田宇农","1988-12-09","女","北京市"),
->("C103","李欣洋",NULL,"女","湖南长沙"),
->("C201","王刚","1978-03-12","男","北京市"),
->("C202","杨光","1992-09-29",null,"浙江杭州");
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
例4-5 mysql> create table goods(
->gno char(4) primary key,
->gname varchar(20) not null,
->price float check(price>0),
->category varchar(50),
->provider varchar(20),
->storage int check(storage>0)
->);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into goods values
->("G001","佳洁士牙膏",8.00,"日常用品","宝洁",98),
->("G002","舒肤佳香皂",4.50,"日常用品","宝洁",92),
->("G003","ONLY牛仔裤",238.00,"服装","天津绫致",23),
->("G004","休闲装",735.00,"服装","李宁公司",9),
->("G005","IPHONE手机",5180.00,"数码产品","苹果公司",20),
->("G006","Canon相机",1087.50,"数码产品","佳能公司",16),
->("G007","数据库系统概论",33.50,"图书","高等教育出版社",43),
->("G008","JSP实用案例教程",35.00,"图书","清华大学出版社",50);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
例4-6 mysql> create table orders(
->cno char(4),
->foreign key(cno) references customer(cno),
->gno char(4),
->foreign key(gno) references goods(gno),
->orderdate datetime,
->amount int check(amount>0),
->primary key(cno,gno)
->);
Query OK, 0 rows affected (0.08 sec）
mysql> insert into orders values
->("C101","G002","2007-12-19",3),
->("C101","G004","2008-10-3",1),
->("C102","G001","2008-11-25",2),
->("C103","G002","2009-1-10",5),
->("C201","G003","2009-3-23",2),
->("C201","G008","2009-4-8",10),
->("C201","G007","2009-5-27",7);
Query OK, 7 rows affected (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from customer;
+------+--------+---------------------+------+----------+
| cno | cname | birthdate | sex | address |
+------+--------+---------------------+------+----------+
| C101 | 马栋梁 | 1990-05-20 00:00:00 | 男 | 辽宁大连 |
| C102 | 田宇农 | 1988-12-09 00:00:00 | 女 | 北京市 |
| C103 | 李欣洋 | NULL | 女 | 湖南长沙 |
| C201 | 王刚 | 1978-03-12 00:00:00 | 男 | 北京市 |
| C202 | 杨光 | 1992-09-29 00:00:00 | NULL | 浙江杭州 |
+------+--------+---------------------+------+----------+
5 rows in set (0.00 sec)
mysql> select * from goods;
+------+-----------------+--------+----------+----------------+---------+
| gno | gname | price | category | provider | storage |
+------+-----------------+--------+----------+----------------+---------+
| G001 | 佳洁士牙膏 | 8 | 日常用品 | 宝洁 | 98 |
| G002 | 舒肤佳香皂 | 4.5 | 日常用品 | 宝洁 | 92 |
| G003 | ONLY牛仔裤 | 238 | 服装 | 天津绫致 | 23 |
| G004 | 休闲装 | 735 | 服装 | 李宁公司 | 9 |
| G005 | IPHONE手机 | 5180 | 数码产品 | 苹果公司 | 20 |
| G006 | Canon相机 | 1087.5 | 数码产品 | 佳能公司 | 16 |
| G007 | 数据库系统概论 | 33.5 | 图书 | 高等教育出版社 | 43 |
| G008 | JSP实用案例教程 | 35 | 图书 | 清华大学出版社 | 50 |
+------+-----------------+--------+----------+----------------+---------+
8 rows in set (0.00 sec)
mysql> select * from orders;
+------+------+---------------------+--------+
| cno | gno | orderdate | amount |
+------+------+---------------------+--------+
| C101 | G002 | 2007-12-19 00:00:00 | 3 |
| C101 | G004 | 2008-10-03 00:00:00 | 1 |
| C102 | G001 | 2008-11-25 00:00:00 | 2 |
| C103 | G002 | 2009-01-10 00:00:00 | 5 |
| C201 | G003 | 2009-03-23 00:00:00 | 2 |
| C201 | G007 | 2009-05-27 00:00:00 | 7 |
| C201 | G008 | 2009-04-08 00:00:00 | 10 |
+------+------+---------------------+--------+
7 rows in set (0.00 sec)
查询所有顾客购买商品的信息，包括没购买商品的顾客和没被购买的商品
mysql> select customer.,goods.,orderdate,amount
-> from customer full join orders on customer.cno=orders.cno
-> full join goods on goods.gno=orders.gno;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full join goods on goods.gno=orders.gno' at line 3
