MySQL table operation

 

CREATE TABLE BY SQL

1. Create a table by sql syntax

CREATE DATABASE IF NOT EXISTS TESTDB01;
CREATE TABLE IF NOT EXISTS `students`(
`id` int(4) not null auto_increment,
`name` varchar(30) not null default 'anonymous',
`sex` varchar(10) not null default 'female',
`pwd` varchar(20) not null default '123456',
`birthday` datetime default null,
`address` varchar(100) default null,
`email` varchar(50) default null,
primary key(`id`)
)engine = innodb default charset = utf8;

2. Some of important commands - sometimes you may use below code to get how wordbench generate table

show create database school; -- common commands
-- 'school', 'CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION=\'N\' */'
show create table students;
-- 'students', 'CREATE TABLE `students` (\n  `id` int NOT NULL AUTO_INCREMENT,\n  `name` varchar(30) NOT NULL DEFAULT \'anonymous\',\n  `sex` varchar(10) NOT NULL DEFAULT \'female\',\n  `pwd` varchar(20) NOT NULL DEFAULT \'123456\',\n  `birthday` datetime DEFAULT NULL,\n  `address` varchar(100) DEFAULT NULL,\n  `email` varchar(50) DEFAULT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3'
desc students;

ALTER TABLE BY SQL

-- alter table name
ALTER TABLE student RENAME AS studentInformation
-- add table field 
ALTER TABLE studentInformation ADD age INT(11)
-- alter table field (Rename, modify constraint)
-- Modify:
ALTER TABLE studentInformation MODIFY age VARCHAR(11)
-- Rename:
ALTER TABLE studentInformation CHANGE age age01 INT(1)
-- Remove table field 
ALTER TABLE studentInformation DROP age01
-- Remove table
DROP IF EXISTS studentInformation