| 操作 |
语句 |
| 创建数据库 |
CREATE DATABASE dbname/* DEFAULT CHARSET utf8 COLLATE utf8_general_ci;*/ |
| 删除数据库 |
DROP DATABASES dbname; |
| 显示数据库列表 |
SHOW DATABASES; |
| 显示表列表 |
SHOW TABLES; |
| 建表 |
CREATE TABLE `tablename` (`id` INT(3) NOT NULL AUTO_INCREMENT, `fieldname` type isnull default 'defaultvalue',PRIMARY KEY(`id`))AUTO_INCREMENT=1 ENGINE=MyISAM DEFAULT CHARSET utf8 COLLATE utf8_general_ci; |
| 切换数据库 |
USE dbname; |
| 当前使用的数据库 |
SELECT DATABASE(); |
| 查看建表信息 |
SHOW CREATE TABLE tablename; |
| 列表显示表的信息（含注释） |
SHOW FULL COLUMNS FROM tablename; |
| 修改表引擎 |
ALTER TABLE tablename TYPE=innodb; 建表默认myisam类型不支持事务，innodb支持事务insert和update时比myisam快，select比myisam慢。 |
| 查看支持的引擎 |
SHOW ENGINES; |
| 删除表 |
DROP TABLE tablename; |
| 清空(截断)表 |
TRUNCATE TABLE tablename; 会清空自动索引 |
| 查看表的索引 |
SHOW INDEX FROM tablename; |
| 添加主键 |
ALTER TABLE tablename ADD PRIMARY KEY(fieldname); |
| 删除主键 |
ALTER TABLE tablename DROP PRIMARY KEY(fieldname); |
| 添加唯一键 |
ALTER TABLE tablename ADD UNIQUE KEY(fieldname); |
| 添加普通键 |
ALTER TABLE tablename ADD INDEX(fieldname); |
| 添加字段 |
ALTER TABLE tablename ADD fieldname fieldtype ... AFTER fieldname; |
| 修改字段信息 |
ALTER TABLE tablename MODIFY fieldname fieldtype ...; |
| 修改字段名 |
ALTER TABLE tablename CHANGE old_field_name new_field_name fieldtype ...; |
| 增加自增属性(auto_increment) |
上一句 最后加AUTO_INCREMENT;前提该字段要是key。 |
| 删除字段 |
ALTER TABLE tablename DROP fieldname; |
| 字段加注释(comment) |
ALTER TABLE tablename MODIFY fieldname fieldtype ... COMMENT 'commentinfo'; |
| 表加注释 |
ALTER TABLE tablename COMMENT 'commentinfo'; |
| 增删改查 |
|
| 增 |
INSERT INTO tablename (`fieldname1`, `fieldname2`) VALUES ('value1', 'value2'); |
| 删 |
DELETE FROM `tablename` WHERE search_condition; |
| 改 |
UPDATE `tablename` set `fieldname1`='value1', `filedname2`='value2' WHERE search_condition; |
| 查 |
SELECT `fieldname1`, `fieldname2` FROM `tablename` WHERE search_condition; |
| 分组查询 |
SELECT `fieldname1`, count(`fieldname2`) FROM `tablename` GROUP BY (`fieldname`); |
| 范围查询（几个值中的一个） |
SELECT `fieldname1`, `fieldname2` FROM `tablename` WHERE `fieldname1` IN ('value1', 'value2', 'value3'); |
| 排除范围查询（不是这几个值的） |
SELECT `fieldname1`, `fieldname2` FROM `tablename` WHERE `fieldname1` NOT IN ('value1', 'value2', 'value3'); |
| 模糊查询 |
|
| 查找含有n个a |
SELECT * FROM `tablename` WHERE fieldname LIKE '%a%'; |
| 查找?bc? |
SELECT * FROM `tablename` WHERE fieldname LIKE '_bc_'; |
| 查找1a、2a、3a、4a |
SELECT * FROM `tablename` WHERE fieldname LIKE '[1-4]a'; |
| 查找[^1-8]a外的?a |
SELECT * FROM `tablename` WHERE fieldname LIKE '[^1-8]a'; |
| |
|
