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

JavaScript event loop case study

Event loop in js Because JavaScript is single-thr...

How to set up vscode remote connection to server docker container

Table of contents Pull the image Run the image (g...

How to install MySQL 8.0 and log in to MySQL on MacOS

Follow the official tutorial, download the instal...

How to configure Nginx to split traffic based on the last segment of the request IP

It is mainly the configuration jump of the if jud...

Summary of problems encountered in the implementation of Vue plug-ins

Table of contents Scene Introduction Plugin Imple...

A brief introduction to the general process of web front-end web development

I see many novice students doing front-end develop...

A comparison between the href attribute and onclick event of the a tag

First of all, let's talk about the execution ...

Understand the usage of Vue2.x and Vue3.x custom instructions and the principle of hook functions

Table of contents Vue2.x Usage Global Registratio...

Calling the search engine in the page takes Baidu as an example

Today, it suddenly occurred to me that it would be...

JavaScript to implement voice queuing system

Table of contents introduce Key Features Effect d...

jQuery plugin to implement floating menu

Learn a jQuery plugin every day - floating menu, ...

Example of nginx ip blacklist dynamic ban

When a website is maliciously requested, blacklis...

A simple way to restart QT application in embedded Linux (based on QT4.8 qws)

Application software generally has such business ...

Let's talk about the difference between containers and images in Docker

What is a mirror? An image can be seen as a file ...

JavaScript color viewer

This article example shares the specific code of ...