异想天开

What's the true meaning of light, Could you tell me why

mysql的常用命令集

日期:2014-07-23 13:04:57
  
最后更新日期:2018-08-12 10:40:38
【技术文章,非码农勿入】
最后更新日期: 2018-06-16
数据库版本:5.1系列

1.数据库进入
[code lang="cpp"]
mysql -uroot -Pport -hlocalhost -p databaseName //tcp ip方式
mysql --socket=/var/lib/mysql/mysql.sock //用unix域套接字
use databaseName //选择数据库
[/code]

2. 数据库启动
一般使用server mysqld start命令启动,查看对应的/etc/init.d/mysqld脚本,发现启动命令。
[code lang="cpp"]
#mysqld脚本文件使用mysqld_safe启动
$bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &
wait_for_pid created $!; return_value=$?
[root@localhost ~]# ps -ef | grep mysql
root 2929 1 0 10:55 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid
mysql 3023 2929 0 10:55 pts/0 00:00:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock
[/code]

关闭数据库
[code ]
/usr/local/mysql/bin/mysqladmin -u root -p shutdown
[/code]
3.show类型
[code lang="cpp"]
show databases //显示当前用户的数据库
show tables //显示当前数据库的表
show create table tableName //显示数据库创建语句
show processlist //显示所有操作数据库的线程
show status //显示数据库的状态
show variables like "%char%" //查看匹配char的变量
[/code]

4.增删改查 记录
[code lang="cpp"]
insert into tablename(field1,field2,field3) values(value1,value2,value3),(value1,value2,value3),(value1,value2,value3)
insert into tablename(field1,field2,field3) values(value1,value2,value3) ON DUPLICATE KEY UPDATE field1=value1,field2=value2...
//另外一种插入方法
insert into tablename set field1=value1, field2=value2
delete from tablename where key=value
update tablename set field=value1 where key=value0
select * from tablename where key=value order by key desc/asc
注:
select distinct chaptertype from v_foo //结果去重,若查询字段多余一个,则distinct是结合所有字段判断唯一
//select distinct(id) as vid,name from v_foo 等价 select distinct id as vid,name from v_foo,而select id as vid,distinct(name) from v_foo这种判断是不合法的。
select hex(gcid) from movie_info limit 10 //hex化blob数据类型
select UNIX_TIMESTAMP(...)//unix time stamp
select FROM_UNIXTIME(...) //unix time stamp to '1970-01-01 00:00:00'格式
select * from students order by id limit 9,10;
select * from students limit 1 into outfile '/tmp/a.txt';
[/code]

5.增删改 字段
[code lang="cpp"]
alter table tablename add column field field propery after columnname
alter table tablename drop column field
alter table tablename modify column field
alter table table1 modify id int unsigned;//修改列id的类型为int unsigned
alter table table1 change id sid int unsigned;//修改列id的名字为sid,而且把属性修改为int unsigned
注:
一般数据量很大的时候,直接这样改变表结构非常耗时,可以用创建临时表的方法代替,见5,6
[/code]

6.创建数据表
[code lang="cpp"]
create table if not exists tablename(……) //创建表是先判断表是否存在
create table tablenameB like tablenameA
alter table table1 rename as table2 //对表重新命名
//创建临时表
CREATE TEMPORARY TABLE tmp_table (
id int(11) NOT NULL
)
[/code]

7.导入数据
[code lang="cpp"]
insert into tablename(field1,field2...) select field1,field2 ...
[/code]

8.备份数据表结构及数据库内容
[code lang="cpp"]
#加上-d参数,仅仅备份结构
mysqldump -uroot -ppassword databasename > ~/test.sql //备份数据库内容
mysqldump -uroot -ppassword databasename tablename > ~/test.sql //备份数据表内容
source ~/test.sql //导入到数据库
[/code]

9.创建索引
[code lang="cpp"]
alter table table1 add index ind_id (id);
alter table table1 add unique index i_time(stat_type,stat_time)
create index ind_id on table1 (id);
create unique index ind_id on table1 (id);//建立唯一性索引
[/code]

10. 删除索引
[code lang="cpp"]
drop index idx_id on table1;
alter table table1 drop index ind_id;
[/code]

11.联合字符或者多个列(将列id与":"和列name和"="连接)
[code lang="cpp"]
select concat(id,':',name,'=') from students;
[/code]

12.分析索引效率
方法:在一般的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的建议;

13.使用较短的定长列
1)尽可能使用较短的数据类型;
2)尽可能使用定长数据类型;
a)用char代替varchar,固定长度的数据处理比变长的快些;
b)对于频繁修改的表,磁盘容易形成碎片,从而影响数据库的整体性能;
c)万一出现数据表崩溃,使用固定长度数据行的表更容易重新构造。使用固定长度的数据行,每个记录的开始位置都是固定记录长度的倍数,可以很容易被检测到,但是使用可变长度的数据行就不一定了;
d)对于MyISAM类型的数据表,虽然转换成固定长度的数据列可以提高性能,但是占据的空间也大;

14.使用not null和enum
尽量将列定义为not null,这样可使数据的出来更快,所需的空间更少,而且在查询时,MySQL不需要检查是否存在特例,即null值,从而优化查询;
如果一列只含有有限数目的特定值,如性别,是否有效或者入学年份等,在这种情况下应该考虑将其转换为enum列的值,MySQL处理的更快,因为所有的enum值在系统内都是以标识数值来表示的;

15.使用optimize table
对于经常修改的表,容易产生碎片,使在查询数据库时必须读取更多的磁盘块,降低查询性能。具有可变长的表都存在磁盘碎片问题,这个问题对blob数据类型更为突出,因为其尺寸变化非常大。可以通过使用optimize table来整理碎片,保证数据库性能不下降,优化那些受碎片影响的数据表。 optimize table可以用于MyISAM和BDB类型的数据表。实际上任何碎片整理方法都是用mysqldump来转存数据表,然后使用转存后的文件并重新建数据表;


16.更改权限或改root密码
[code lang="cpp"]
mysql> INSERT INTO mysql.user (Host,User,Password) VALUES(%,jeffrey,PASSWORD(iscuit));
mysql> FLUSH PRIVILEGES
//回收或查看权限
mysql> REVOKE DELETE ON zjwdb_11668.* FROM great@127.0.0.1 ;
[/code]
增加一个管理员帐户:grant all on *.* to user@localhost identified by "明文密码";
改root密码: 修改mysql库的user表,不过别忘了使用PASSWORD函数。修改完权限后,记得flush privileges刷新权限。
每条语句输入完毕后要在末尾填加分号';',或者填加'\g'也可以;
查询时间:select now();
查询当前用户:select user();
查询数据库版本:select version();
查询当前使用的数据库:select database();

17.数据库操作一般方法
[code lang="cpp"]
//1.建库。建库的时候,定义好字符集以及字符序
CREATE DATABASE IF NOT EXISTS dbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
//2.使用mysql客户端工具连接mysql server时,记得set names=utf8,否则插入或建表语句里面的中文,对mysql server而言是show variables like “%char%”里面客户端的字符集,而linux环境里面输入的中文是系统环境相关,比如当前系统为zh_CN.UTF-8,如果使用mysql server默认值,mysql server会认为传输进来的字符集是latin,然后转换为mysql server的字符集,这样就造成了乱码。
//3.建表
手动指定字符集以及使用的存储引擎
CREATE TABLE `memo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`studentid` int(11) NOT NULL,
`content` varchar(256) NOT NULL COMMENT '备忘内容,格式为xml',
`remindtime` datetime NOT NULL COMMENT '提醒时间',
`modifytime` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `studentid` (`studentid`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
//4.导出数据表
mysqldump -h 127.0.0.1 -P 3307 -u username --default-character-set=utf8 -p databasename > dumpfile.txt
注意:
--skip参数不仅仅是不加建表语句,同时还会去掉一些属性,比如自增属性
[/code]
执行上述几项,那么可以避免存储字符乱码问题。

18.mysql表锁
mysql现在版本支持行锁,查看mysql状态,
[code lang="cpp"]
show status
Table_locks_immediate | 559129100 |
Table_locks_waited | 214049 |
[/code]
559129100/214049 = 2612表示每2612次请求锁有一次等待。
网上的看法时该值小于5000时用InnoDB引擎,如果原先使用MyISAM表锁。

19.命令执行
[code lang="cpp"]
方式1:
echo "select * from bar where 1=1" | mysql -uroot -hlocalhost -pbar
方式2:
mysql -uroot -hlocalhost -pbar -e"select * from bar where 1=1"
[/code]