sql常用命令

原文:http://blog.youkuaiyun.com/wozaixiaoximen/article/details/48286041

创建utf-8格式的数据库

CREATE DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

---

修改数据库字符集

ALTER DATABASE dbname DEFAULT CHARACTER SET 'utf8' COLLATE utf8_general_ci

---

删除数据库

drop database dbname

---

创建utf-8格式的表

CREATE TABLE mysqlcode ( 
id int auto_increment primary key, 
content varchar(32) 
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci

---

修改数据表字符集

alter table table_name default character set 'utf8' COLLATE utf8_general_ci

---

修改表名

rename table 原表名 to 新表名;
alter table 原表名 rename 新表名;

---

删除表

DROP TABLE table_name

---

清空表数据

delete from 表名
truncate table 表名

---

查看表结构

desc 表名;
show columns from 表名;
describe 表名;

---

查看创建表的语句

show create table 表名;

---

修改字段类型

alter table table_name modify [column] column_name VARCHAR(10)

---

修改字段名及其类型

alter table table_name change [column] column_name another_column_name int

不能仅修改字段名

alter table tablename change old_field_name new_field_name old_type

---

删除表字段

alter table table_name drop [column] column_name

---

添加表字段

 alter table table_name add [column] column_name varchar(20) not null after/before column_name

---

向表中插入数据

insert into table_name(column_name, column_name) values(value, value)