mysql建表常用的sql语句汇总
最近跟项目，写后台需要用到SQL语句，就整理了一下mysql建表常用sql语句，并写几个可执行SQL脚本，方便日后复习查看以及使用：
连接：mysql-h主机地址-u用户名－p用户密码（注:u与root可以不用加空格，其它也一样）
断开：exit（回车）
创建授权：grantselecton数据库.*to用户名@登录主机identifiedby\"密码\"
修改密码：mysqladmin-u用户名-p旧密码password新密码
删除授权:revokeselect,insert,update,deleteom*.*fromtest2@localhost;
显示数据库：showdatabases;
显示数据表：showtables;
显示表结构：describe表名;
创建库：createdatabase库名;
删除库：dropdatabase库名;
使用库(选中库)：use库名;
创建表：createtable表名(字段设定列表);
删除表：droptable表名;
修改表：altertablet1renamet2
查询表：select*from表名;
清空表：deletefrom表名;
备份表:mysqlbinmysqldump-h(ip)-uroot-p(password)databasenametablename>tablename.sql
恢复表:mysqlbinmysql-h(ip)-uroot-p(password)databasenametablename 增加列：ALTERTABLEt2ADDcINTUNSIGNEDNOTNULLAUTO_INCREMENT,ADDINDEX(c); 修改列：ALTERTABLEt2MODIFYaTINYINTNOTNULL,CHANGEbcCHAR(20); 删除列：ALTERTABLEt2DROPCOLUMNc; 备份数据库：mysql\bin\mysqldump-h(ip)-uroot-p(password)databasename>database.sql 恢复数据库：mysql\bin\mysql-h(ip)-uroot-p(password)databasename 复制数据库：mysql\bin\mysqldump--all-databases>all-databases.sql 修复数据库：mysqlcheck-A-o-uroot-p54safer 文本数据导入：loaddatalocalinfile\"文件名\"intotable表名; 数据导入导出：mysql\bin\mysqlimportdatabasetables.txt 1.创建用户表示例 2.创建公司网站主页栏目示例 3.创建店铺商品实例 4.餐厅点餐菜单示例 到此这篇关于mysql建表常用sql语句的文章就介绍到这了,更多相关mysql建表sql语句内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！以下为MySQL的可执行脚本示例：
//创建用户表示例
/*
NavicatMySQLDataTransfer
SourceServer:localhost_1111
SourceServerVersion:50717
SourceHost:localhost:1111
SourceDatabase:maven
TargetServerType:MYSQL
TargetServerVersion:50717
FileEncoding:65001
Date:2018-08-1522:40:44
*/
SETFOREIGN_KEY_CHECKS=0;
------------------------------
--Tablestructureforuser
------------------------------
DROPTABLEIFEXISTS`user`;
CREATETABLE`user`(
`pk_id`int(10)NOTNULLAUTO_INCREMENT,
`username`varchar(30)NOTNULL,
`password`char(32)NOTNULL,
`age`int(3)DEFAULTNULL,
`info`varchar(255)CHARACTERSETutf8mb4DEFAULTNULL,
`createtime`timestampNULLDEFAULTNULLONUPDATECURRENT_TIMESTAMP,
`modifytime`timestampNULLDEFAULTNULLONUPDATECURRENT_TIMESTAMP,
`sex`char(1)DEFAULTNULL,
PRIMARYKEY(`pk_id`)
)ENGINE=InnoDBAUTO_INCREMENT=34DEFA
//某公司网站主页栏目示例
SETFOREIGN_KEY_CHECKS=0;
------------------------------
--Tablestructureforhome
------------------------------
DROPTABLEIFEXISTS`home`;
CREATETABLE`home`(
`home_id`int(10)NOTNULLAUTO_INCREMENT,
`profile`longtextcomment'企业简介',
`scope`longtextcomment'经营范围',
`product`longtextcomment'产品介绍',
`cooperate`longtextcomment'校企合作',
`extension`longtextcomment'其他',
PRIMARYKEY(`home_id`)
)ENGINE=InnoDBAUTO_INCREMENT=34DEFAULTCHARSET=utf8;
//某电商平台店铺商品实例
createdatabasestore;#创建数据库store
usestore;
setnamesutf8;
droptableifexistsgoods;
createtablegoods
(
idmediumintunsignednotnullauto_incrementcomment'Id',
goods_namevarchar(150)notnullcomment'商品名称',
market_pricedecimal(10,2)notnullcomment'市场价格',
shop_pricedecimal(10,2)notnullcomment'本店价格',
goods_desclongtextcomment'商品描述',
is_on_saleenum('是','否')notnulldefault'是'comment'是否上架',
is_deleteenum('是','否')notnulldefault'否'comment'是否放到回收站',
addtimedatetimenotnullcomment'添加时间',
logovarchar(150)notnulldefault''comment'原图',
sm_logovarchar(150)notnulldefault''comment'小图',
mid_logovarchar(150)notnulldefault''comment'中图',
big_logovarchar(150)notnulldefault''comment'大图',
mbig_logovarchar(150)notnulldefault''comment'更大图',
primarykey(id),
keyshop_price(shop_price),
keyaddtime(addtime),
keyis_on_sale(is_on_sale)
)engine=InnoDBdefaultcharset=utf8comment'商品';
droptableifexistsbrand;
createtablebrand
(
idmediumintunsignednotnullauto_incrementcomment'Id',
brand_namevarchar(30)notnullcomment'品牌名称',
site_urlvarchar(150)notnulldefault''comment'官方网址',
logovarchar(150)notnulldefault''comment'品牌Logo图片',
primarykey(id)
)engine=InnoDBdefaultcharset=utf8comment'品牌';
//餐厅点餐菜单示例
/*
SQLyog企业版-MySQLGUIv8.14
MySQL-5.5.27:Database-db_food
*********************************************************************
*/
/*!40101SETNAMESutf8*/;
/*!40101SETSQL_MODE=''*/;
/*!40014SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,UNIQUE_CHECKS=0*/;
/*!40014SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0*/;
/*!40101SET@OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/;
/*!40111SET@OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0*/;
CREATEDATABASE/*!32312IFNOTEXISTS*/`db_food`/*!40100DEFAULTCHARACTERSETutf8*/;
USE`db_food`;
/*Tablestructurefortable`goods`*/
DROPTABLEIFEXISTS`goods`;
CREATETABLE`goods`(
`id`int(10)NOTNULLAUTO_INCREMENT,
`goodsName`varchar(100)DEFAULTNULL,
`price`floatDEFAULTNULL,
`goodsDesc`varchar(200)DEFAULTNULL,
`imageLink`varchar(500)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=12DEFAULTCHARSET=utf8;
/*Dataforthetable`goods`*/
insertinto`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,'sdcd','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');
/*Tablestructurefortable`order_goods`*/
DROPTABLEIFEXISTS`order_goods`;
CREATETABLE`order_goods`(
`id`int(10)NOTNULLAUTO_INCREMENT,
`orderId`varchar(50)DEFAULTNULL,
`goodsTotalPrice`floatDEFAULTNULL,
`goodsId`int(10)DEFAULTNULL,
`goodsPrice`floatDEFAULTNULL,
`goodsNum`int(10)DEFAULTNULL,
`goodsName`varchar(100)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`FK_order_goods_2`(`orderId`),
KEY`FK_order_goods_1`(`goodsId`),
CONSTRAINT`FK_order_goods_1`FOREIGNKEY(`goodsId`)REFERENCES`goods`(`id`),
CONSTRAINT`FK_order_goods_2`FOREIGNKEY(`orderId`)REFERENCES`order_info`(`orderId`)
)ENGINE=InnoDBAUTO_INCREMENT=44DEFAULTCHARSET=utf8;
/*Dataforthetable`order_goods`*/
insertinto`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,'鱼香肉丝');
/*Tablestructurefortable`order_info`*/
DROPTABLEIFEXISTS`order_info`;
CREATETABLE`order_info`(
`orderId`varchar(50)NOTNULL,
`orderStatus`int(10)DEFAULTNULL,
`orderNum`int(10)DEFAULTNULL,
`orderTotalMoney`floatDEFAULTNULL,
`userName`varchar(100)DEFAULTNULL,
PRIMARYKEY(`orderId`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
/*Dataforthetable`order_info`*/
insertinto`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');
/*Tablestructurefortable`user`*/
DROPTABLEIFEXISTS`user`;
CREATETABLE`user`(
`id`int(10)NOTNULLAUTO_INCREMENT,
`userName`varchar(100)DEFAULTNULL,
`password`varchar(50)DEFAULTNULL,
`email`varchar(200)DEFAULTNULL,
`rank`int(1)DEFAULT'0',
PRIMARYKEY(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=10DEFAULTCHARSET=utf8;
/*Dataforthetable`user`*/
insertinto`user`(`id`,`userName`,`password`,`email`,`rank`)values(1,'admin','123',NULL,1),(8,'aaa','123','ad@1.com',0),(9,'admin1','123',NULL,0);
/*!40101SETSQL_MODE=@OLD_SQL_MODE*/;
/*!40014SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;
/*!40014SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS*/;
/*!40111SETSQL_NOTES=@OLD_SQL_NOTES*/;