Mysql - 数据库操作

show databases;

create DATABASE newdb;

show create database newdb;

create database newdb2 default character set utf8

drop database newdb;

use test;

show tables;

create table tch_contact2 select * from tch_contact;

CREATE TABLE `contact` (
  `Id` int(11) NOT NULL AUTO_INCREMENT, -- 不为空, 自动增长
  `TId` int(11) DEFAULT NULL,
  `QQ` varchar(15) DEFAULT NULL,
  `Weixin` varchar(50) DEFAULT NULL,
  `Phone` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`Id`), -- 设置主键
  KEY `Index_TId` (`TId`) USING BTREE, -- 索引
  CONSTRAINT `key_tid` FOREIGN KEY (`TId`) REFERENCES `tch_teacher` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION -- 设置外键
) COMMENT='就是这个表' -- 表注释

`Id` int(11) NOT NULL  PRIMARY key AUTO_INCREMENT,
-- 或者
`Id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`Id`),

-- 单个索引
KEY `Index_Weixin` (`Weixin`) USING BTREE,
UNIQUE KEY `Index_TId` (`TId`) USING HASH,
FULLTEXT KEY `Index_QQ` (`QQ`),
-- 复合索引
KEY `Index_fuhe` ('QQ', `Weixin`) USING BTREE,

CONSTRAINT `key_tid` FOREIGN KEY (`TId`) REFERENCES `tch_teacher` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION

show COLUMNS from tch_contact;

alter table tch_contact add createby int comment '创建人';

alter table tch_contact MODIFY createby varchar(1);

alter table tch_contact change createby createby1 varchar(2);

alter table tch_contact drop  createby;

-- 新增一个索引
create index index_tid on tch_contact2(tid) using BTREE;

alter table tch_contact2 add index index_weixin(weixin);

-- 如果要修改一个索引, mysql里面, 只有先删除然后重建

-- 删除一个索引
drop index index_tid on tch_contact2;

-- 新增一个外键
alter table tch_contact2 add CONSTRAINT 
`key_tid` FOREIGN KEY (`TId`) REFERENCES `tch_teacher` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION;

-- 删除一个外键
alter table tch_contact2  DROP FOREIGN KEY key_tid ;

rename table tch_contact to tch_contact1;

ALTER TABLE tch_contact1 RENAME tch_contact;

drop table tch_contactt;