一、mysql常用操作
1、登录数据库
命令格式:mysql -u 用户名 -p -P 端口 -D 数据库 -e “SQL内容要查询的内容”
输入数据库密码,即可查询到相应的记录
一般执行的操作:mysql -u 用户名 -p,然后输入密码,即可进入数据界面进行相应的数据库操作
2、修改密码
(1)linux终端修改:
例如:将root的密码由helloworld 改成nihao,执行如下命令:
mysqladmin -u root -p password nihao,输入初始命令helloworld即可。
如果最开始root没有设置密码,需给root设置一个helloworld的密码,则执行如下命令:
mysqladmin -u root password helloworld
(2)数据库的交互界面修改密码的命令如下:
例如:将root的密码改为mysql;
mysql>set password=password("mysql");(该方法既可用于修改密码,也可用于数据未初始化密码时设置初始密码)
(3)为用户添加密码
格式:grant select on 数据库名.* to 用户名@登录的主机 identified by \"密码\"
例如:
增加一个用户test密码为hello,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改,删除的权限。首先以root用户连上MySQL,然后执行:
mysql>grant select,insert,update,delete on *.* to test@localhost identified by "hello";
如果你不想test有密码,则执行:
mysql>grant select,insert,update,delete on *.* to test@localhost identified by "";
3、查看数据库及表格操作
创建数据库:mysql>create database 数据库名
选择数据库:mysql>use 数据库名
列出当前mysql的所有数据库名:mysql>show databases;
列出某个数据库下的所有表格:
mysql>use 数据库名;
mysql>show tables;
列出表格的结构字段:
mysql>desc 表名;
删除表格:
msql>drop table 表名;
从表格中删除数据:
mysql>delete from 表名;(可以加约束条件)
4、导出数据库
(1) 数据库终端导出数据库:
mysql -u root -p 数据库名 > data.sql
(2)linux shell终端导出数据库
导出整个数据库结构和数据:mysqldump -u root -p 数据库名 > data.sql
导出整个数据库结构:mysqldump -u root -p -d 数据库名 > data.sql
(3)linux终端导出表结构和表数据
mysqldump -u root 数据库名 表名1 [表名2] > data.sql
(4)linux终端导出表结构
mysql -u root -p -d 数据库名 表名 > data.sql
5、导入数据库:
(1)数据库终端导入数据库:
use databasename;
source /data/data.sql;
(2)linux shell终端导入数据库:
先进入数据库交互界面创建名为test的数据库名:mysql>create database test
然后返回Linux终端执行:mysqldump -u root -p test < data.sql
6、删除数据库
直接删除数据库: drop database 数据库名;
删除数据库前有提示: mysqladmin drop database 数据库名;
7、创建表
create table department
(
id int not null auto_increment,
name varchar(20) not null default 'system', #设定默认值system
description varchar(100),
primary key PK_department (id) #设定主键
);
create table depart_pos
(
department_id int not null,
position_id int not null,
primary key PK_depart_pos(department_id,position_id) #设定复和主键
);
create table staffer
(
id int not null auto_increment primary key, #设定主键
name varchar(20) not null default 'undefined', #设定默认值
department_id int not null,
position_id int not null,
unique (department_id,position_id)#设定唯一值,改值在表中是唯一的,否则错误。
);
8、修改表结构:(修改表结构用alter(根据修改的属性不同分别用modify add alter drop等关键词),修改数据用update)
(1)给表增加列test:(关键词add)
alter table 表名 add(level int default 1);
(2)修改列test的数据类型(关键词modify):
alter table 表名 modify test varchar(20) not null;
(3)修改列test的默认值(关键词alter):
alter table 表名 alter test set default 'system';
(4)去掉列test默认值(关键词alter):
alter table 表名 alter test drop default;
(5)去掉列test(关键词drop column);
alter table 表名 drop column test;
(6)删除主键;
alter table 表名 drop primary key;
(7)增加主键:
alter table 表名 add primary key PK_depart_pos(主键名);
9、操作数据:
#插入表department
插入一条记录:
insert into department(name,description)values('system','system');
同时插入多条记录:
insert into department(name,description) values("hr", "resouce"),("rd", "research");
#插入表staffer
insert into staffer(name,department_id,position_id) values('陈达治',1,1);
insert into staffer(name,department_id,position_id)values('李文宾',1,2);
10、查询、删除及修改操作
#显示系统部的人数
select count(*) from staffer a,department b where a.department_id=b.id and b.name='system'
#显示各部门的人数
select count(*),b.name from staffer a,department b where a.department_id=b.id group by b.name;
#删除客服部
delete from department where name='客服部';
#将财务部修改为财务一部
update department set name='财务一部' where name='财务部';
11、索引
(1)增加索引:
alter table table1 add index ind_id (id);
create index ind_id on table1 (id);
create unique index ind_id on table1 (id);//建立唯一性索引
(2)删除索引
drop index idx_id on table1;
alter table table1 drop index ind_id;
12、重命名表:
alter table t1 rename t2;
alter table table1 rename as table2;
13、增加一个字段或者多个字段
alter table tabelName add column fieldName dateType;
alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;
多行命令输入:注意不能将单词断开;当插入或更改数据时,不能将字段的字符串展开到多行里,否则硬回车将被储存到数据中;
14、创建临时表
create temporary table zengchao(name varchar(10));(临时表用命令show tables;看不到)
15、创建表时先判断表是否存在
create table if not exists students(……);
16、从已有的表中复制表
只复制表的结构:
create table table2 select * from table1 where 1<>1;
复制表结构和表数据:
create tabletable2 select * from table1;
17、联合字符或者多个列(将列id与":"和列name和"="连接)
select concat(id,':',name,'=') from students;
18、limit(限定返回的记录条数)
select * from students order by id limit 9,10;
19、Mysql触发器
举例如下:
现有表tab1和tab2,要求触发器具有如下功能,当对tab1插入一条记录时,tab2也插入相同的记录。
mysql>delimiter //
mysql>create trigger t_afterinsert_on_tab1 after insert on tab1 for each row
->begin
->insert into tab2(tab2_id) values(new.tab1_id);
->end;
->//
执行完以上语句后,恢复设置
mysql> delimiter ;
其中delimiter作用: 告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了。默认情况下,delimiter是分号;。在命令行客户端中,如果有一行命令以分号结束,那么回车后,mysql将会执行该命令。但有时候,不希望MySQL这么做。在为可能输入较多的语句,且语句中包含有分号。使用delimiter //,这样只有当//出现之后,mysql解释器才会执行这段语句。 也可以使用批处理创建触发器.
delimiter //
create trigger t_afterinsert_on_tab1 after insert on tab1 for each row
begin
insert into tab2(tab2_id) values(new.tab1_id);
end;
mysql> delimiter ;
将上面内容保存为sql文件执行它,注意前面也要用delimiter来改变语句结束标志。删除触发器:
drop
trigger
t_afterinsert_on_tab1;(其中
t_afterinsert_on_tab1是触发器名字)
20、使用索引的缺点
1) 增删改数据的速度;
2)占用磁盘空间;
3)增加查询优化器的负担;当查询优化器生成执行计划时,会考虑索引,太多的索引会给查询优化器增加负担
21、分析索引效率:
方法:在一般的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的建议;
22、使用较短的定长列
1)尽可能使用较短的数据类型;
2)尽可能使用定长数据类型;
a)用char代替varchar,固定长度的数据处理比变长的快些;
b)对于频繁修改的表,磁盘容易形成碎片,从而影响数据库的整体性能;
c)万一出现数据表崩溃,使用固定长度数据行的表更容易重新构造。使用固定长度的数据行,每个记录的开始位置都是固定记录长度的倍数,可以很容易被检测到,但是使用可变长度的数据行就不一定了;
d)对于MyISAM类型的数据表,虽然转换成固定长度的数据列可以提高性能,但是占据的空间也大;
23、使用not null 和enum
尽量将列定义为not null,这样可使数据的出来更快,所需的空间更少,而且在查询时,MySQL不需要检查是否存在特例,即null值,从而优化查询;
如果一列只含有有限数目的特定值,如性别,是否有效或者入学年份等,在这种情况下应该考虑将其转换为enum列的值,MySQL处理的更快,因为所有的enum值在系统内都是以标识数值来表示的;
24、使用optimize table
对于经常修改的表,容易产生碎片,使在查询数据库时必须读取更多的磁盘块,降低查询性能。具有可变长的表都存在磁盘碎片问题,这个问题对blob数据类型更为突出,因为其尺寸变化非常大。可以通过使用optimize table来整理碎片,保证数据库性能不下降,优化那些受碎片影响的数据表。 optimize table可以用于MyISAM和BDB类型的数据表。实际上任何碎片整理方法都是用mysqldump来转存数据表,然后使用转存后的文件并重新建数据表;
25、使用procedure analysis()
可以使用procedure analyse()显示最佳类型的建议,使用很简单,在select语句后面加上procedureanalyse()就可以了;例如:
select * from students procedure analyse();
select * from students procedure analyse(16,256);
第二条语句要求procedure analyse()不要建议含有多于16个值,或者含有多于256字节的enum类型,如果没有限制,输出可能会很长;
26、使用查询缓存
1)查询缓存的工作方式:
第一次执行某条select语句时,服务器记住该查询的文本内容和查询结果,存储在缓存中,下次碰到这个语句时,直接从缓存中返回结果;当更新数据表后,该数据表的任何缓存查询都变成无效的,并且会被丢弃。
2)配置缓存参数:
变量:query_cache_type,查询缓存的操作模式。有3种模式,0:不缓存;1:缓存查询,除非以 select sql_no_cache开头;2:根据需要只缓存那些以select sql_cache开头的查询; query_cache_size:设置查询缓存的最大结果集的大小,比这个值大的不会被缓存。
27、调整硬件:
1)在机器上装更多的内存;
2)增加更快的硬盘以减少I/O等待时间;
寻道时间是决定性能的主要因素,逐字地移动磁头是最慢的,一旦磁头定位,从磁道读则很快;
3)在不同的物理硬盘设备上重新分配磁盘活动;
如果可能,应将最繁忙的数据库存放在不同的物理设备上,这跟使用同一物理设备的不同分区是不同的,因为它们将争用相同的物理资源(磁头)。
28、查询mysql指定数据库的大小:
use information_schema ;
select concat(round(sum(DATA_LENGTH/1024/1024),2), 'MB') as data from TABLES where table_schema= 'game1' ;
29、查询数据库中表的大小:
select concat(round(sum(DATA_LENGTH/1024/1024),2), 'MB') as data from TABLES where table_schema= 'game1' and table_name = 'game_server_brief_tbl' ;
30、数据库查询重复记录的数量(统计gameid的重复次数)
select count(gameid) from game_server_tbl group by gameid order by gameid;
查询数据库表格vips中eip_id不为0的ipaddr的数目统计:
select ipaddr, count(ipaddr), eip_id as count from vips where eip_id != 0 group by ipaddr;
二、python使用mysql数据库
1、安装mysql
yum install mysql
2、安装MySQL-python
要想使python可以操作mysql 就需要MySQL-python驱动,它是python 操作mysql必不可少的模块。
下载地址:https://pypi.python.org/pypi/MySQL-python/
下载MySQL-python-1.2.5.zip 文件之后直接解压。进入MySQL-python-1.2.5目录:
>>python setup.py install
3、测试
测试非常简单,检查MySQLdb模块是否可以正常导入。
linux终端运行python, 看是否能导入MySQLdb模块即可,没有报错提示MySQLdb模块找不到,说明安装OK 。
4、代码示例及解释
#coding=utf-8
import MySQLdb
conn= MySQLdb.connect(
host='localhost',
port = 3306,
user='root',
passwd='123456',
db ='test',
)
cur = conn.cursor()
#创建数据表
#cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")
#插入一条数据
#cur.execute("insert into student values('2','Tom','3 year 2 class','9')")
#修改查询条件的数据
#cur.execute("update student set class='3 year 1 class' where name = 'Tom'")
#删除查询条件的数据
#cur.execute("delete from student where age='9'")
cur.close()
conn.commit()
conn.close()
>>> conn = MySQLdb.connect(host='localhost',port = 3306,user='root',passwd='123456',db ='test');
Connect() 方法用于创建数据库的连接,里面可以指定参数:用户名,密码,主机等信息。
这只是连接到了数据库,要想操作数据库需要创建游标。
>>> cur = conn.cursor()
通过获取到的数据库连接conn下的cursor()方法来创建游标。
>>> cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")
通过游标cur 操作execute()方法可以写入纯sql语句。通过execute()方法中写如sql语句来对数据进行操作。
>>>cur.close()
cur.close() 关闭游标
>>>conn.commit()
conn.commit()方法在提交事物,在向数据库插入一条数据时必须要有这个方法,否则数据不会被真正的插入。
>>>conn.close()
Conn.close()关闭数据库连接
5、几个函数说明:
fetchone() 返回一条查询记录;
fetchmany(num)返回num条查询记录;
fetchall() 将查询到的所有查询记录均返回;
python mySQL返回的不是所查询的结果,其返回的是查询的记录数。
本文参考:
http://www.cnblogs.com/fnng/p/3565912.html
http://blog.csdn.net/lxh090821/article/details/9410943
http://www.cnblogs.com/xiao-cheng/archive/2011/10/03/2198380.html