mysql建表常用的sql语句汇总(2)-木庄网络博客

数据导入导出：mysql\bin\mysqlimport database tables.txt

以下为MySQL的可执行脚本示例：

1.创建用户表示例

//创建用户表示例
 
/*
Navicat MySQL Data Transfer
Source Server   : localhost_1111
Source Server Version : 50717
Source Host   : localhost:1111
Source Database  : maven
Target Server Type : MYSQL
Target Server Version : 50717
File Encoding   : 65001
Date: 2018-08-15 22:40:44
*/
 
SET FOREIGN_KEY_CHECKS=0;
 
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
 `pk_id` int(10) NOT NULL AUTO_INCREMENT,
 `username` varchar(30) NOT NULL,
 `password` char(32) NOT NULL,
 `age` int(3) DEFAULT NULL,
 `info` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
 `createtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 `modifytime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 `sex` char(1) DEFAULT NULL,
 PRIMARY KEY (`pk_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFA

2.创建公司网站主页栏目示例

//某公司网站主页栏目示例
 
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for home
-- ----------------------------
DROP TABLE IF EXISTS `home`;
CREATE TABLE `home` (
 `home_id` int(10) NOT NULL AUTO_INCREMENT,
 `profile` longtext comment '企业简介',
 `scope` longtext comment '经营范围',
 `product` longtext comment '产品介绍',
 `cooperate` longtext comment '校企合作',
 `extension` longtext comment '其他',
 
 PRIMARY KEY (`home_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;

3.创建店铺商品实例

//某电商平台店铺商品实例
 
create database store;          #创建数据库store
use store;
set names utf8;
 
drop table if exists goods;
create table goods
(
id mediumint unsigned not null auto_increment comment 'Id',
goods_name varchar(150) not null comment '商品名称',
market_price decimal(10,2) not null comment '市场价格',
shop_price decimal(10,2) not null comment '本店价格',
goods_desc longtext comment '商品描述',
is_on_sale enum('是','否') not null default '是' comment '是否上架',
is_delete enum('是','否') not null default '否' comment '是否放到回收站',
addtime datetime not null comment '添加时间',
logo varchar(150) not null default '' comment '原图',
sm_logo varchar(150) not null default '' comment '小图',
mid_logo varchar(150) not null default '' comment '中图',
big_logo varchar(150) not null default '' comment '大图',
mbig_logo varchar(150) not null default '' comment '更大图',
primary key (id),
key shop_price(shop_price),
key addtime(addtime),
key is_on_sale(is_on_sale)
)engine=InnoDB default charset=utf8 comment '商品';
 
drop table if exists brand;
create table brand
(
id mediumint unsigned not null auto_increment comment 'Id',
brand_name varchar(30) not null comment '品牌名称',
site_url varchar(150) not null default '' comment '官方网址',
logo varchar(150) not null default '' comment '品牌Logo图片',
primary key (id)
)engine=InnoDB default charset=utf8 comment '品牌';

4.餐厅点餐菜单示例

//餐厅点餐菜单示例
 
/*
SQLyog 企业版 - MySQL GUI v8.14 
MySQL - 5.5.27 : Database - db_food
*********************************************************************
*/
 
 
/*!40101 SET NAMES utf8 */;
 
/*!40101 SET SQL_MODE=''*/;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_food` /*!40100 DEFAULT CHARACTER SET utf8 */;
 
USE `db_food`;
 
/*Table structure for table `goods` */
 
DROP TABLE IF EXISTS `goods`;
 
CREATE TABLE `goods` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `goodsName` varchar(100) DEFAULT NULL,
 `price` float DEFAULT NULL,
 `goodsDesc` varchar(200) DEFAULT NULL,
 `imageLink` varchar(500) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
 
/*Data for the table `goods` */
 
insert into `goods`(`id`,`goodsName`,`price`,`goodsDesc`,`imageLink`) values (3,'宫保鸡丁',21,'宫保鸡丁哦','D:\\我的文档\\Desktop\\food\\1332059684_58.jpg'),(5,'青椒肉丝',22,'青椒肉丝不好吃','D:\\我的文档\\Desktop\\food\\1332059684_58.jpg'),(8,'21',2,'sd cd','D:\\我的文档\\Desktop\\food\\1332059994_53.jpg'),(9,'鱼香肉丝',9,'四川风味','D:\\我的文档\\Desktop\\food\\1332060047_92.jpg'),(10,'回锅肉',12,NULL,NULL),(11,'热狗肠',32,'说的','D:\\我的文档\\Desktop\\food\\1332060176_81.jpg');
 
/*Table structure for table `order_goods` */
 
DROP TABLE IF EXISTS `order_goods`;
 
CREATE TABLE `order_goods` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `orderId` varchar(50) DEFAULT NULL,
 `goodsTotalPrice` float DEFAULT NULL,
 `goodsId` int(10) DEFAULT NULL,
 `goodsPrice` float DEFAULT NULL,
 `goodsNum` int(10) DEFAULT NULL,
 `goodsName` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `FK_order_goods_2` (`orderId`),
 KEY `FK_order_goods_1` (`goodsId`),
 CONSTRAINT `FK_order_goods_1` FOREIGN KEY (`goodsId`) REFERENCES `goods` (`id`),
 CONSTRAINT `FK_order_goods_2` FOREIGN KEY (`orderId`) REFERENCES `order_info` (`orderId`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8;
 
/*Data for the table `order_goods` */
 
insert into `order_goods`(`id`,`orderId`,`goodsTotalPrice`,`goodsId`,`goodsPrice`,`goodsNum`,`goodsName`) values (23,'20130708001514',12,10,12,1,'回锅肉'),(28,'20130708021437',12,10,12,1,'回锅肉'),(31,'20130708110510',22,5,22,1,'青椒肉丝'),(32,'20130708110510',9,9,9,1,'鱼香肉丝'),(33,'20130708110513',12,10,12,1,'回锅肉'),(34,'20130708110513',32,11,32,1,'热狗肠'),(39,'20130708115503',2,8,2,1,'21'),(40,'20130708115508',12,10,12,1,'回锅肉'),(41,'20130708115508',32,11,32,1,'热狗肠'),(42,'20130708115512',22,5,22,1,'青椒肉丝'),(43,'20130708121456',9,9,9,1,'鱼香肉丝');
 
/*Table structure for table `order_info` */
 
DROP TABLE IF EXISTS `order_info`;
 
CREATE TABLE `order_info` (
 `orderId` varchar(50) NOT NULL,
 `orderStatus` int(10) DEFAULT NULL,
 `orderNum` int(10) DEFAULT NULL,
 `orderTotalMoney` float DEFAULT NULL,
 `userName` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `order_info` */
 
insert into `order_info`(`orderId`,`orderStatus`,`orderNum`,`orderTotalMoney`,`userName`) values ('20130708001514',3,1,12,'admin1'),('20130708021437',1,1,12,'admin1'),('20130708110510',2,2,31,'aaa'),('20130708110513',3,2,44,'aaa'),('20130708115503',1,1,2,'admin1'),('20130708115508',4,2,44,'admin1'),('20130708115512',3,1,22,'admin1'),('20130708121456',4,1,9,'admin1');
 
/*Table structure for table `user` */
 
DROP TABLE IF EXISTS `user`;
 
CREATE TABLE `user` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `userName` varchar(100) DEFAULT NULL,
 `password` varchar(50) DEFAULT NULL,
 `email` varchar(200) DEFAULT NULL,
 `rank` int(1) DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
 
/*Data for the table `user` */
 
insert into `user`(`id`,`userName`,`password`,`email`,`rank`) values (1,'admin','123',NULL,1),(8,'aaa','123','ad@1.com',0),(9,'admin1','123',NULL,0);
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

到此这篇关于mysql建表常用sql语句的文章就介绍到这了,更多相关mysql建表sql语句内容请搜索

标签：SQL

返回前面的内容