Summary of commonly used SQL statements for creating MySQL tables

Summary of commonly used SQL statements for creating MySQL tables

Recently, I have been working on a project and need to use SQL statements to write the background. I have sorted out the common SQL statements for creating MySQL tables and written several executable SQL scripts for easy review and use in the future:

Connection: mysql -h host address -u username -p user password (Note: u and root do not need to have spaces, and the same applies to the others)

Disconnect: exit (Enter)

Create authorization: grant select on 數據庫.* to 用戶名@登錄主機identified by \"密碼\"

Change password: mysqladmin -u用戶名-p舊密碼password 新密碼

Delete authorization: revoke select,insert,update,delete om *.* from test2@localhost;

Show databases: show databases show databases;

show tables;

Display table structure: describe 表名;

Create a library: create database 庫名;

Delete database: drop database 庫名;

Use library (select library): use 庫名;

Create a table: create table 表名(字段設定列表);

Delete table: drop table 表名;

Modify the table: alter table t1 rename t2

Query table: select * from 表名;

Clear the table: delete from 表名;

Backup table: mysqlbinmysqldump -h(ip) -uroot -p(password) databasename tablename > tablename.sql

Restore table: mysqlbinmysql -h(ip) -uroot -p(password) databasename tablename < tablename.sql (delete the original table before the operation)

Add a column: ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD INDEX (c);

Modify the column: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE bc CHAR(20);

Delete a column: ALTER TABLE t2 DROP COLUMN c;

Back up the database: mysql\bin\mysqldump -h(ip) -uroot -p(password) databasename > database.sql

Restore database: mysql\bin\mysql -h(ip) -uroot -p(password) databasename < database.sql

Copy the database: mysql\bin\mysqldump --all-databases > all-databases.sql

Repair the database: mysqlcheck -A -o -uroot -p54safer

load data local infile \"文件名\" into table 表名;

Data import and export: mysql\bin\mysqlimport database tables.txt

The following is an example of an executable script for MySQL:

1. Create a user table example

//Create a user table example/*
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. Create a company website homepage column example

//Example of a company website homepage column 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 'Company Profile',
 `scope` longtext comment 'Business scope',
 `product` longtext comment 'Product Introduction',
 `cooperate` longtext comment 'school-enterprise cooperation',
 `extension` longtext comment 'other',
 
 PRIMARY KEY (`home_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;

3. Create a store product instance

//Create a database store; //Create a product instance of an e-commerce platform 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 'Product name',
market_price decimal(10,2) not null comment 'Market price',
shop_price decimal(10,2) not null comment 'Our shop price',
goods_desc longtext comment 'Goods description',
is_on_sale enum('yes','no') not null default 'yes' comment 'Is it on sale',
is_delete enum('yes','no') not null default 'no' comment 'whether to put in the recycle bin',
addtime datetime not null comment 'Add time',
logo varchar(150) not null default '' comment 'Original image',
sm_logo varchar(150) not null default '' comment 'Small picture',
mid_logo varchar(150) not null default '' comment '中图',
big_logo varchar(150) not null default '' comment 'Big picture',
mbig_logo varchar(150) not null default '' comment 'Bigger picture',
primary key (id),
key shop_price(shop_price),
key addtime(addtime),
key is_on_sale(is_on_sale)
)engine=InnoDB default charset=utf8 comment 'Product';
 
drop table if exists brand;
create table brand
(
id mediumint unsigned not null auto_increment comment 'Id',
brand_name varchar(30) not null comment 'Brand name',
site_url varchar(150) not null default '' comment 'Official website',
logo varchar(150) not null default '' comment 'Brand Logo Picture',
primary key (id)
)engine=InnoDB default charset=utf8 comment 'Brand';

4. Restaurant menu example

//Restaurant menu example/*
SQLyog Enterprise Edition - 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` */
 
: : : : : : : : : : : : : : :
 
/*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` */
 
: : : : : : : : : : : : : : :
 
/*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','[email protected]',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 */;

This is the end of this article about the commonly used sql statements for creating mysql tables. For more relevant mysql sql statements for creating tables, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySQL uses show status to view MySQL server status information
  • How to get table information in MySQL show table status
  • Record a pitfall of MySQL update statement update
  • Navicat Premium operates MySQL database (executes sql statements)
  • The difference between two MySQL delete user statements (delete user and drop user)
  • Summary of MySQL database like statement wildcard fuzzy query
  • Summary of methods for writing judgment statements in MySQL
  • MySQL data duplicate checking and deduplication implementation statements
  • Summary of MySQL's commonly used concatenation statements
  • Use of MySQL SHOW STATUS statement

<<:  Centos7.3 automatically starts or executes specified commands when booting

>>:  Reduce memory and CPU usage by optimizing web pages

Recommend

How to install common components (mysql, redis) in Docker

Docker installs mysql docker search mysql Search ...

The contents of the table in HTML are displayed horizontally and vertically in the center

Specify in CSS style file #class td /*Set the tab...

Configure selenium environment based on linux and implement operation

1. Using Selenium in Linux 1. Install Chrome Inst...

Example of Html shielding right-click menu and left-click typing function

Disable right-click menu <body oncontextmenu=s...

MySQL database operations and data types

Table of contents 1. Database Operation 1.1 Displ...

Vue implements time countdown function

This article example shares the specific code of ...

Mybatis statistics of the execution time of each SQL statement

background I am often asked about database transa...

MySQL joint index effective conditions and index invalid conditions

Table of contents 1. Conditions for joint index f...

How to understand the difference between computed and watch in Vue

Table of contents Overview computed watch monitor...

Two ways to visualize ClickHouse data using Apache Superset

Apache Superset is a powerful BI tool that provid...

Detailed explanation of the hierarchical relationship problem caused by CSS positioning

Absolute, relative and fixed in position position...

Detailed analysis of the principles and usage of MySQL views

Preface: In MySQL, views are probably one of the ...

How to lock a virtual console session on Linux

When you are working on a shared system, you prob...

Analysis of how to create a stored procedure in MySQL to add new fields to a data table

This article uses an example to describe how to c...

Detailed explanation of how to use several timers in CocosCreator

1. setTimeOut Print abc after 3 seconds. Execute ...