创建旅游网所用的种据库语句-优快云博客

本文链接：https://blog.youkuaiyun.com/RedeLego/article/details/89398417

这篇博客详细介绍了创建旅游网站所用的数据库设计，包括创建数据库、定义各个表结构如tab_category、tab_route、tab_user等，并设置了外键约束。此外，还展示了插入初始数据的操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

– 创建数据库
CREATE DATABASE travel;
– 使用数据库
USE travel;

//
/ DBMS name: MySQL 5.0 /
/ Created on: 2018/2/14 23:10:29 /
//
SET NAMES utf8;

DROP TABLE IF EXISTS tab_favorite;
DROP TABLE IF EXISTS tab_route_img;
DROP TABLE IF EXISTS tab_route;
DROP TABLE IF EXISTS tab_category;
DROP TABLE IF EXISTS tab_seller;
DROP TABLE IF EXISTS tab_user;

//
/ Table: tab_category /
//
CREATE TABLE tab_category
(
cid INT NOT NULL AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL,
PRIMARY KEY (cid),
UNIQUE KEY AK_nq_categoryname (cname)
);

//
/ Table: tab_favorite /
//
CREATE TABLE tab_favorite
(
rid INT NOT NULL,
DATE DATE NOT NULL,
uid INT NOT NULL,
PRIMARY KEY (rid, uid)
);

//
/ Table: tab_route /
//
CREATE TABLE tab_route
(
rid INT NOT NULL AUTO_INCREMENT,
rname VARCHAR(500) NOT NULL,
price DOUBLE NOT NULL,
routeIntroduce VARCHAR(1000),
rflag CHAR(1) NOT NULL,
rdate VARCHAR(19),
isThemeTour CHAR(1) NOT NULL,
COUNT INT DEFAULT 0,
cid INT NOT NULL,
rimage VARCHAR(200),
sid INT,
sourceId VARCHAR(50),
PRIMARY KEY (rid),
UNIQUE KEY AK_nq_sourceId (sourceId)
);

//
/ Table: tab_route_img /
//
CREATE TABLE tab_route_img
(
rgid INT NOT NULL AUTO_INCREMENT,
rid INT NOT NULL,
bigPic VARCHAR(200) NOT NULL,
smallPic VARCHAR(200),
PRIMARY KEY (rgid)
);

//
/ Table: tab_seller /
//
CREATE TABLE tab_seller
(
sid INT NOT NULL AUTO_INCREMENT,
sname VARCHAR(200) NOT NULL,
consphone VARCHAR(20) NOT NULL,
address VARCHAR(200),
PRIMARY KEY (sid),
UNIQUE KEY AK_Key_2 (sname)
);

//
/ Table: tab_user /
//
CREATE TABLE tab_user
(
uid INT NOT NULL AUTO_INCREMENT,
username VARCHAR(100) NOT NULL,
PASSWORD VARCHAR(32) NOT NULL,
NAME VARCHAR(100),
birthday DATE,
sex CHAR(1),
telephone VARCHAR(11),
email VARCHAR(100),
STATUS CHAR(1) ,
CODEVARCHAR(50),

PRIMARY KEY (uid),
UNIQUE KEY AK_nq_username (username),
UNIQUE KEY AK_nq_code (CODE)
);

ALTER TABLE tab_favorite ADD CONSTRAINT FK_route_favorite FOREIGN KEY (rid)
REFERENCES tab_route (rid) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE tab_favorite ADD CONSTRAINT FK_user_favorite FOREIGN KEY (uid)
REFERENCES tab_user (uid) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE tab_route ADD CONSTRAINT FK_category_route FOREIGN KEY (cid)
REFERENCES tab_category (cid) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE tab_route ADD CONSTRAINT FK_seller_route FOREIGN KEY (sid)
REFERENCES tab_seller (sid) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE tab_route_img ADD CONSTRAINT FK_route_routeimg FOREIGN KEY (rid)
REFERENCES tab_route (rid) ON DELETE RESTRICT ON UPDATE RESTRICT;

INSERT INTO tab_category(cid,cname) VALUES (8,‘全球自由行’),(5,‘国内游’),(4,‘处境游’),(7,‘抱团定制’),(6,‘港澳游’),(2,‘酒店’),(1,‘门票’),(3,‘香港车票’);
INSERT INTO tab_seller(sid,sname,consphone,address) VALUES (1,‘黑马程序员’,‘12345678901’,‘传智播客javaEE学院’);

/*Data for the table tab_route */

《算法导论第三版英文版》_高清中文版

《深度学习入门：基于Python的理论与实现》_高清中文版

《深入浅出数据分析》_高清中文版

《Python编程：从入门到实践》_高清中文版

《Python科学计算》_高清中文版

《深度学习入门：基于Python的理论与实现》_高清中文版

《深入浅出数据分析》_高清中文版

《Python编程：从入门到实践》_高清中文版