ä¸ãDATABASESæ°æ®åºç¸å ³æä½
-- æ¥çæå¤å°ä¸ªæ°æ®åº
SHOW DATABASES;
-- å é¤æ°æ®åº
DROP DATABASE åºå;
-- å建æ°æ®åº
CREATE DATABASE åºå;
-- æ¥çMySQLæ°æ®åºå符éç¼ç
show variables like "%char%";
show variables like 'character_set_database';
-- ä¿®æ¹æ°æ®åºå符éç¼ç
SET character_set_database='utf8';
-- æ¥çæ°æ®åºæ¯æçå¼æ
show engines;
-- æ¥çmysqlå½åé»è®¤çåå¨å¼æ
show variables like '%storage_engine%';
-- æ¥çmysqlæ°æ®åºè¿æ¥æ°
show full processlist;
-- æ¥çæ°æ®åºç¶æ
show status;
äºãTable表ç¸å ³æä½
-- å建表
CREATE TABLE `tb_user` (
`userId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ç¨æ·id',
`userName` varchar(10) NOT NULL COMMENT 'ç¨æ·å称',
`isDeleted` char(1) NOT NULL DEFAULT 'N' COMMENT 'é»è¾å é¤æ å¿',
PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ç¨æ·è¡¨';
-- ä¿®æ¹è¡¨å
rename table å表å to æ°è¡¨å;
-- æ¥ç表信æ¯ï¼å
æ¬ç¼ç ï¼æ°æ®åºå¼æç
show table status from åºå like '表å';
show table status from åºå WHERE NAME = '表å';
-- ä¿®æ¹è¡¨çå符éç¼ç
ALTER TABLE 表å DEFAULT CHARACTER SET utf8;
-- æ´æ¹è¡¨çå¼æ
alter table table_name engine=innodb;
alter table table_name engine=myisam;
-- æ¥ç表çå建SQLè¯å¥
show create table '表å';
-- æ¥ç表çææå
show full columns from 表å;
-- å é¤è¡¨
DROP TABLE 表å;
DROP TABLE IF EXISTS 表å;
-- æ¸
空表ä¸æ°æ®è®°å½
DELETE FROM 表å;
ä¸ãColumnå段ç¸å ³æä½
-- æ·»å å段
ALTER TABLE 表å ADD COLUMN æ°æ·»å çå段å称 å段å±æ§ AFTER å段å称;
-- ä¾å¦ï¼å¨tb_user表ä¸æ°å ä¸ä¸ªemailå段ï¼emailå段添å å¨userNameåé¢ã
ALTER TABLE `tb_user` ADD COLUMN `email` varchar(30) NULL AFTER `userName`;
-- ä¿®æ¹å段å±æ§
ALTER TABLE `tb_user` MODIFY COLUMN `email` varchar(36) NOT NULL;
-- ä¿®æ¹å段å称åå±æ§
ALTER TABLE `tb_user` CHANGE COLUMN `email` `userEmail` varchar(50) NOT NULL AFTER `userName`;
-- å é¤å段
ALTER TABLE `tb_user` DROP COLUMN `email`;
-- 为å段å¢å èªå¢å±æ§
alter table 表å modify å段 int auto_increment;
-- ä¿®æ¹èªå¢çå¼
alter table 表å auto_increment = 10;
Â