(mysql操作.doc
文本预览下载声明
MYSQL常用命令
mysql修改表名，列名，列类型，添加表列，删除表列
alter table test rename test1; --修改表名
alter table test add column name varchar(10); --添加表列
alter table test drop column name; --删除表列
alter table test modify address char(10) --修改表列类型
||alter table test change address address char(40)
alter table test change column address address1 varchar(30)--修改表列名
编码设置：
查看系统的字符集和排序方式的设定可以通过下面的两条命令或mysql status 。
mysql SHOW VARIABLES LIKE character_set_%;
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)
mysql SHOW VARIABLES LIKE collation_%;
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
上面列出的值就是系统的默认值。(很奇怪系统怎么默认是latin1的瑞典语排序方式)...
当我们按照原来的方式通过PHP存取MySQL数据库时，就算设置了表的默认字符集为utf8并且通过UTF-8编码发送查询，你会发现存入数据库的仍然是乱码。问题就出在这个connection连接层上。解决方法是在发送查询前执行一下下面这句：
1. SET NAMES utf8;
它相当于下面的三句指令：
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
2. 创建数据库
mysql create database name character set utf8;
3. 创建表
CREATE TABLE `type` (
`id` int(10) unsigned NOT NULL auto_increment,
`flag_deleted` enum(Y,N) character set utf8 NOT NULL default N,
`flag_type` int(5) NOT NULL default 0,
`type_name` varchar(50) character set utf8 NOT NULL default ,
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
4. 修改数据库
显示全部