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 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\32059684_58.jpg'),(5,'青椒肉丝',22,'青椒肉丝不好吃','d:\\我的文档\\desktop\\food\32059684_58.jpg'),(8,'21',2,'sd cd','d:\\我的文档\\desktop\\food\32059994_53.jpg'),(9,'鱼香肉丝',9,'四川风味','d:\\我的文档\\desktop\\food\32060047_92.jpg'),(10,'回锅肉',12,null,null),(11,'热狗肠',32,'说的','d:\\我的文档\\desktop\\food\32060176_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语句内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐