mysql建表常⽤的sql语句汇总最近跟项⽬，写后台需要⽤到SQL语句，就整理了⼀下mysql建表常⽤sql语句，并写⼏个可执⾏SQL脚本，⽅便⽇后复习查看以及使⽤：连接：mysql -h主机地址 -u⽤户名－p⽤户密码（注:u与root可以不⽤加空格，其它也⼀样）
断开：exit （回车）
创建授权：grant select on 数据库.* to ⽤户名@登录主机 identified by \"密码\"
修改密码：mysqladmin -u⽤户名 -p旧密码 password 新密码
删除授权: revoke select,insert,update,delete om *.* from test2@localhost;
显⽰数据库：show databases;
显⽰数据表：show tables;
显⽰表结构：describe 表名;
创建库：create database 库名;
删除库：drop database 库名;
使⽤库(选中库)：use 库名;
创建表：create table 表名 (字段设定列表);
删除表：drop table 表名;
修改表：alter table t1 rename t2
查询表：select * from 表名;
清空表：delete from 表名;
备份表: mysqlbinmysqldump -h(ip) -uroot -p(password) databasename tablename > tablename.sql
恢复表: mysqlbinmysql -h(ip) -uroot -p(password) databasename tablename < tablename.sql（操作前先把原来表删除）
增加列：ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD INDEX (c);
修改列：ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
删除列：ALTER TABLE t2 DROP COLUMN c;
备份数据库：mysql\bin\mysqldump -h(ip) -uroot -p(password) databasename > database.sql
恢复数据库：mysql\bin\mysql -h(ip) -uroot -p(password) databasename < database.sql
复制数据库：mysql\bin\mysqldump --all-databases > all-databases.sql
修复数据库：mysqlcheck -A -o -uroot -p54safer
⽂本数据导⼊：load data local infile \"⽂件名\" into table 表名;
数据导⼊导出：mysql\bin\mysqlimport
以下为MySQL的可执⾏脚本⽰例：
1.创建⽤户表⽰例
//创建⽤户表⽰例
/*
Navicat MySQL Data Transfer
Source Server : localhost_1111
Source Server Version : 50717
Source Host : localhost:1111
Source Database : maven
mysql面试题sql语句多表联查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.创建公司⽹站主页栏⽬⽰例
//某公司⽹站主页栏⽬⽰例
html网站logoSET 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 '产品介绍',
content读音`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 '是否上架',
instrument的复数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 '商品';最简单c语言入门
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:\\我/*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,'201307081105 /*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,'admin /*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',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语句内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论