第1页 / 共41页
第2页 / 共41页
第3页 / 共41页
第4页 / 共41页
第5页 / 共41页
第6页 / 共41页
第7页 / 共41页
第8页 / 共41页
mysql常用命令大全-最完整版.pdf
mysql 命令大全
启动：net start mySql;
进入：mysql -u root -p/mysql -h localhost -u root -p databaseName;
列出数据库：show databases;
选择数据库：use databaseName;
列出表格：show tables；
显示表格列的属性：show columns from tableName；
建立数据库：source fileName.txt;
匹配字符：可以用通配符_代表任何一个字符，％代表任何字符串;
增加一个字段：alter table tabelName add column fieldName dateType;
增加多个字段：alter table tabelName add column fieldName1 dateType,add columns
fieldName2 dateType;
多行命令输入:注意不能将单词断开;当插入或更改数据时，不能将字段的字符串展开到
多行里，否则硬回车将被储存到数据中;
增加一个管理员帐户：grant all on *.* to user@localhost identified by "password";
每条语句输入完毕后要在末尾填加分号';'，或者填加'\g'也可以；
查询时间：select now();
查询当前用户：select user();
查询数据库版本：select version();
查询当前使用的数据库：select database();
1、删除 student_course 数据库中的 students 数据表：
rm -f student_course/students.*
2、备份数据库：(将数据库 test 备份)
mysqldump -u root -p test>c:\test.txt
备份表格：(备份 test 数据库下的 mytable 表格)
mysqldump -u root -p test mytable>c:\test.txt
将备份数据导入到数据库：(导回 test 数据库)
mysql -u root -p test
3、创建临时表：(建立临时表 zengchao)
create temporary table zengchao(name varchar(10));
4、创建表是先判断表是否存在
create table if not exists students(„„);
5、从已经有的表中复制表的结构
create table table2 select * from table1 where 1<>1;
6、复制表
create table table2 select * from table1;
7、对表重新命名
alter table table1 rename as table2;
8、修改列的类型
alter table table1 modify id int unsigned;//修改列 id 的类型为 int unsigned
alter table table1 change id sid int unsigned;//修改列 id 的名字为 sid，而且把属性修改为
int unsigned
9、创建索引
alter table table1 add index ind_id (id);
create index ind_id on table1 (id);
create unique index ind_id on table1 (id);//建立唯一性索引
10、删除索引
drop index idx_id on table1;
alter table table1 drop index ind_id;
11、联合字符或者多个列(将列 id 与":"和列 name 和"="连接)
select concat(id,':',name,'=') from students;
12、limit(选出 10 到 20 条)<第一个记录集的编号是 0>
select * from students order by id limit 9,10;
13、MySQL 不支持的功能
事务，视图，外键和引用完整性，存储过程和触发器
14、MySQL 会使用索引的操作符号
<,<=,>=,>,=,between,in,不带%或者_开头的 like
15、使用索引的缺点
1)减慢增删改数据的速度；
2）占用磁盘空间；
3）增加查询优化器的负担；
当查询优化器生成执行计划时，会考虑索引，太多的索引会给查询优化器增加工作量，
导致无法选择最优的查询方案；
16、分析索引效率
方法：在一般的 SQL 语句前加上 explain；
分析结果的含义：
1）table：表名；
2）type：连接的类型，(ALL/Range/Ref)。其中 ref 是最理想的；
3）possible_keys：查询可以利用的索引名；
4）key：实际使用的索引；
5）key_len：索引中被使用部分的长度（字节）；
6）ref：显示列名字或者"const"（不明白什么意思）；
7）rows：显示 MySQL 认为在找到正确结果之前必须扫描的行数；
8）extra：MySQL 的建议；
17、使用较短的定长列
1）尽可能使用较短的数据类型；
2）尽可能使用定长数据类型；
a）用 char 代替 varchar，固定长度的数据处理比变长的快些；
b）对于频繁修改的表，磁盘容易形成碎片，从而影响数据库的整体性能；
c）万一出现数据表崩溃，使用固定长度数据行的表更容易重新构造。使用固定长度的
数据行，每个记录的开始位置都是固定记录长度的倍数，可以很容易被检测到，但是使用可
变长度的数据行就不一定了；
d）对于 MyISAM 类型的数据表，虽然转换成固定长度的数据列可以提高性能，但是占
据的空间也大；
18、使用 not null 和 enum
尽量将列定义为 not null，这样可使数据的出来更快，所需的空间更少，而且在查询时，
MySQL 不需要检查是否存在特例，即 null 值，从而优化查询；
如果一列只含有有限数目的特定值，如性别，是否有效或者入学年份等，在这种情况下
应该考虑将其转换为 enum 列的值，MySQL 处理的更快，因为所有的 enum 值在系统内都是
以标识数值来表示的；
19、使用 optimize table
对于经常修改的表，容易产生碎片，使在查询数据库时必须读取更多的磁盘块，降低查
询性能。具有可变长的表都存在磁盘碎片问题，这个问题对 blob 数据类型更为突出，因为
其尺寸变化非常大。可以通过使用 optimize table 来整理碎片，保证数据库性能不下降，优
化那些受碎片影响的数据表。 optimize table 可以用于 MyISAM 和 BDB 类型的数据表。实际
上任何碎片整理方法都是用 mysqldump 来转存数据表，然后使用转存后的文件并重新建数
据表；
20、使用 procedure analyse()
可以使用 procedure analyse()显示最佳类型的建议，使用很简单，在 select 语句后面加
上 procedure analyse()就可以了；例如：
select * from students procedure analyse();
select * from students procedure analyse(16,256);
第二条语句要求 procedure analyse()不要建议含有多于 16 个值，或者含有多于 256 字节
的 enum 类型，如果没有限制，输出可能会很长；
21、使用查询缓存
1）查询缓存的工作方式：
第一次执行某条 select 语句时，服务器记住该查询的文本内容和查询结果，存储在缓存
中，下次碰到这个语句时，直接从缓存中返回结果；当更新数据表后，该数据表的任何缓存
查询都变成无效的，并且会被丢弃。
2）配置缓存参数：
变量：query_cache _type，查询缓存的操作模式。有 3 中模式，0：不缓存；1：缓存查
询，除非与 select sql_no_cache 开头；2：根据需要只缓存那些以 select sql_cache 开头的查
询； query_cache_size：设置查询缓存的最大结果集的大小，比这个值大的不会被缓存。
22、调整硬件
1）在机器上装更多的内存；
2）增加更快的硬盘以减少 I/O 等待时间；
寻道时间是决定性能的主要因素，逐字地移动磁头是最慢的，一旦磁头定位，从磁道读
则很快；
3）在不同的物理硬盘设备上重新分配磁盘活动；
如果可能，应将最繁忙的数据库存放在不同的物理设备上，这跟使用同一物理设备的不
同分区是不同的，因为它们将争用相同的物理资源（磁头）。
10:53 浏览 (1442) 评论 (1) 分类: 数据库 相关推荐 评论
coolmist 2007-10-30
1. Mysql 培训
1.1. 培训目的
本文档是针对 MySQL 数据库方面的基础培训，为了使项目组成员能够达到使用 MySQL 数
据库的目的。
1.2. 培训对象
开发人员
1.3. 常用词及符号说明
常用词：
Mysql：一种免费的跨平台的数据库系统
E:\mysql：表示是在 dos 命令窗口下面
mysql> 表示是在 mysql 的命令行下
1.4. 参考信息
http://dev.mysql.com/doc/refman/5.0/en/index.html
2. MYSQL
2.1. 连接 MYSQL
格式： mysql -h 主机地址 -u 用户名 -p 用户密码
连接远程机器：
E:\mysql>mysql -h10.4.3.188 -uptsdb -p
等价写法
E:\mysql>mysql --host=10.4.3.188 --user=ptsdb --password
连接本地机器：
E:\mysql>mysql -uroot -p
等价写法
E:\mysql>mysql --user=root -password
（注:u 与 root 可以不用加空格，其它也一样）
注意事项：环境变量 path 里面要设定 mysql 的 bin 的路径：
all
on
*.*
"
to
test @localhost
C:\Program Files\MySQL\MySQL Server 5.0\bin
2.2. 修改密码
方法一：使用 mysqladmin
格式：mysqladmin -u 用户名 -p 旧密码 password 新密码
例 1：E:\mysql>mysqladmin -uroot password root
注：因为开始时 root 没有密码，所以-p 旧密码一项就可以省略了。
例 2：再将 root 的密码改为 root123。
E:\mysql>mysqladmin -uroot -proot password root123
方法二：直接更新 user 表
mysql>UPDATE user SET password=PASSWORD("test123") WHERE user='test';
mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR test=PASSWORD('test123');
mysql> FLUSH PRIVILEGES;
方法三：使用 grant
格式：grant 权限 on 数据库.表格| 其他 to 用户@主机 IDENTIFIED BY 口令
例 1：给 test 用户在本地 localhost 所有权限(除了 GRANT OPTION)，口令为 test
(相当于修改了 test 用户的口令)
mysql>grant all on *.* to test@localhost identified by "test";
等同于
mysql>grant
*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 ";
例 2、增加一个用户 test 密码为 abc，让他可以在任何主机上登录，并对 test 数据库有查询、
插入、修改、删除的权限。首先用以 root 用户连入 MYSQL，然后键入以下命令：
mysql>grant select,insert,update,delete on test.* to test@"%" Identified by "abc";
在 mysql.user 表中，有两个 test 用户
一个 test 用户，在本地有所有的权限
另外的 test 用户，在所有主机上有增删改查权限
同样可以取消部分权限（全部）
mysql>revoke insert,update,delete on test.* from test@"%"
mysql>REVOKE ALL PRIVILEGES, GRANT OPTION FROM test@"%"
然后 mysql> FLUSH PRIVILEGES;
Test 用户不再使用用了，也可以删除
mysql>Delete from user where user='test' and host='%'
mysql> FLUSH PRIVILEGES;
注意：例 2 增加的用户是比较危险的，你想如某个人知道 test 的密码，那么他就可以在 internet
上的任何一台电脑上登录你的 mysql 数据库并对你的数据库 test 为所欲为了(可以通过限定
主机)
mysql>grant select,insert,update,delete on test.* to test@"IP 地址" Identified by "abc";
2.3. 显示命令
identified
by
PASSWORD
显示数据库列表:
mysql>show databases;
mysql>show schemas; --mysql 5.0.2
显示表格
mysql>show tables from mydb;
显示表格状态
Mysql>SHOW TABLE STATUS;
显示字符集:
mysql> SHOW CHARACTER SET;
显示创建表:
mysql> show create table quote;
显示用户权限:
mysql> SHOW GRANTS FOR 'test'@'localhost';
mysql>SHOW GRANTS;
mysql>SHOW GRANTS FOR CURRENT_USER;
mysql>SHOW GRANTS FOR CURRENT_USER();
显示 index:
mysql>SHOW INDEX FROM mydb.mytable;
显示表结构:
mysql>desc mydb.tablename;
mysql>show columns from mydb.tablename;
显示 MySQL 数据库的版本：
mysql>select version();
显示函数
mysql>Select * from mysql.func;
显示存储过程
mysql>Select * from mysql.proc;
显示存储引擎
mysql> SHOW ENGINES;
显示变量:
mysql>SHOW VARIABLES;
显示状态:
Mysql> SHOW STATUS;
显示进程
Mysql>SHOW PROCESSLIST
显示 INNODB 状态
Mysql>SHOW INNODB STATUS
显示连接状态
Mysql>SHOW STATUS LIKE '%CONNECT%';
显示线程状态
Mysql>SHOW STATUS LIKE '%THREAD%';
等等..
2.4. 创建.修改.删除
2.4.1. 创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
例如:
CREATE DATABASE IF NOT EXISTS ddd --如果不存在,则创建.
CHARACTER SET 'ujis' --设定字符集
COLLATE 'ujis_japanese_ci';
2.4.2. 创建表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
例子:
CREATE TABLE if not exists `Admin_User` (
`id` int(11) NOT NULL auto_increment, --PRIMARY KEY，
`livedoorId` varchar(255) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`auth` int(11) default '0',
PRIMARY KEY (`id`) --设定主健
) ENGINE=MyISAM DEFAULT CHARSET=ujis ?设定字符集
ENGINE=MyISAM 默认存储引擎
The binary portable storage engine that is the default storage engine used by MySQL
对于每个 MyISAM 存储引擎的表,在硬盘上存在 3 个文件
File Purpose
tbl_name.frm Table format (definition) file
tbl_name.MYD Data file
tbl_name.MYI Index file
ENGINE= InnoDB
Transaction-safe tables with row locking and foreign keys.
ENGINE = BDB
Transaction-safe tables with page locking.
还有其他的内存引擎 MEMORY 归档 ARCHIVE 等等
ISAM 不再使用了
2.4.3. 创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
将 customer 表的 name 字段前十个字符做为索引
CREATE INDEX part_of_name ON customer (name(10));
MYSQL 5.0 特性
存储引擎为 MyISAM, InnoDB, or BDB 的表格上,可以在有 null 值的字段上创建索引
存储引擎为 MyISAM, InnoDB, or BDB 的表格上,可以在 BLOB TEXT 上创建索引
只有在 MyISAM 类型表格上,可以在 CHAR, VARCHAR, and TEXT 字段类型上创建 FULLTEXT 索
引
Storage Engine Allowable Index Types
MyISAM BTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
可以指定索引类型
Example:
CREATE TABLE testtable (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON testtable (id);
2.4.4. 修改表
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
IGNORE 忽略主健重复的错误,如果重复,采用第一条,其余删除
例子:同时多个操作
mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
例子, 重命名 INTEGER 字段,从 a 到 b:
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
例子 修改字段类型,仍然需要新旧字段名称,即使字段名称相同:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
也可以使用 modify
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
在 mysql 5.0 可以使用 FIRST or AFTER 字段来添加 add 字段,默认是在最后
modify change 也可以使用
在 mysql 5.0 InnoDB 存储引擎支持 ALTER TABLE 删除外健:
mysql>ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;
例子:
创建表
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
重命名表
mysql> ALTER TABLE t1 RENAME t2;
MODIFY a 字段为 TINYINT NOT NULL , 并且 change 字段 b,从 CHAR(10) 到 CHAR(20) 并
改名为 c:
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
