mysql建表常用的sql语句汇总

最近跟项目，写后台需要用到SQL语句，就整理了一下mysql建表常用sql语句，并写几个可执行SQL脚本，方便日后复习查看以及使用： 连接：mysql -h主机地址 -u用户名 －p用户密码 （注:u与

最近跟项目，写后台需要用到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 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语句内容请搜索每日运维以前的文章或继续浏览下面的相关文章希望大家以后多多支持每日运维！