MySQL使用
- MySQL使用
- mysql日志种类
- mysql常用操作
- 17查询及删除操作
- 18删除表
- 19清空表
- 20添加表字段
- 21修改某个表的字段类型及指定为空或非空
- 22删除某一字段
- 23添加唯一键
- 24修改主键
- 25增加索引
- 26删除索引
- 27重命名表
- 28增加一个字段
- 29增加多个字段
- 30创建临时表
- 31创建表时先判断表是否存在
- 32从已近有的表中复制表的结构
- 33复制表
- 34对表重新命名
- 35修改列的类型
- 36联合字符或者多个列将列id与和列name和连接
- 37limit选出10到20条第一个记录集的编号是0
- 38MySQL会使用索引的操作符号
- 39存储过程
- 40mysql存储过程修改
- 41mysql存储过程的删除
- 42存储过程基本函数
- 43使用索引的缺点
- 44分析索引效率
- 45使用较短的定长列
- 46使用not null和enum
- 47使用optimize table
- 48使用procedure analyse
- 49使用查询缓存
- 50调整硬件
mysql日志种类
- 错误日志:-log-err(记录信息包括:服务启动或关闭,运行过程的错误和警告,事件调度器)
- 查询日志:-log
- 慢查询日志:-log-slow-queries
- 更新日志:log-update(一般都不会使用)
- 二进制日志:-log-bin(mysqlbinlog查看二进制文件)
mysql常用操作
1登录数据库
格式:mysql -h host -u user -p -P port -D database -e “sql”
2修改密码
格式:mysqladmin -u user -p oldp password newp
3添加用户
格式:grant select on db.table to user@host identified by \”密码\”
4创建数据库
create databasename;创建数据库
use databasename;选择数据库
5备份恢复数据库
shell>mysqldump -h host -u root -p dbname >dbname_backup.sql
shell>mysqladmin -h myhost -u root -p create dbname
shell>mysqldump -h host -u root -p dbname < dbname_backup.sql
shell>mysqladmin -u root -p -d databasename > a.sql//仅导出表结构
备份数据库staffer
mysqldump -uroot-proot staffer > staffer.sql
得到的staffer.sql是一个sql脚本,不包括建库的语句,所以你需要手工
创建数据库才可以导入
恢复数据库staffer,需要创建一个空库staffer
mysql -uroot-proot staffer
6导入.sql文件命令
mysql>use database;
mysql>source /home/mysql.sql
7用文本方式将数据装入数据库表中
mysql>load data local infile/mysql.txt into table mytable;
8删除数据库
drop databasename 直接删除数据库
mysqladmin drop database name 删除数据库前,有提示
9数据库授权
grant all privileges on dbname.* to user@’%’ indentified by ‘mima’ with grant option.
flush privileges;
10删除授权
mysql>revoke all privileges on . form user@’%’;
mysql>delete from user where user=”root” and hosr=”%”;
mysql>flush privileges;
11显示当前mysql版本和当前日期
select version(),current_date;
查询时间:select now();
查询当前用户:select user();
查询数据库版本:select version();
查询当前使用的数据库:select database();
12查询所有数据表
show tables;
13查询表的字段信息
desc 表名称;
14创建表
create table s_position
(
id int not null auto)increment,
name varchar(20) not null default “经理”,
description varchar(100),
primary key PK_position(id)
);
create table department
(
id int not null auto_increment,
name varchar(20) not null default “系统部”,
description varchar(100),
primary key PK_department (id)
)
create tabls depart_pos
(
department_id int not null,
position_id int not null,
primary key PK_depart_pos(department_id,postion_id)#设定复和主键
)
create table staffer
(
id int not null auto_increment primary key,
name varchar(20) not null default “无名氏”,#设定默认值
department_id int not null,
position_id int not null,
unique(department_id,position_id)#设定唯一值
)
15修改结构
mysql>
#表position增加列test
alter table position add(test char(10));
#表position修改列test
alter table position modify test char(20) not null;
#表position修改列test默认值
alter table position alter test set default ‘system’;
#表position去掉test默认值
alter table position alter test drop default;
#表position去掉列test
alter table position drop column test;
#删除主键
alter table depart_pos drop primary key;
#增加主键
alter table depart_pos add primary key PK_depart_pos(department_id,position_id);
16操作数据
#插入表department
insert into department(name,description)values(‘系统部’,’系统部’);
17查询及删除操作
#显示系统部的人员和职位
selecta.name,b.name department_name,c.name position_name
from staffera,department b,s_position c
wherea.department_id=b.id and a.position_id=c.id and b.name=’系统部’;
#显示系统部的人数
select count(*)from staffer a,department b
wherea.department_id=b.id and b.name=’系统部’
#显示各部门的人数
select count(*)cou,b.name from staffer a,department b
wherea.department_id=b.idgroup by b.name;
#删除客服部
delete fromdepartment where name=’客服部’;
#将财务部修改为财务一部
updatedepartment set name=’财务一部’ wherename=’财务部’;
18删除表
mysql>drop table mytable;
19清空表
mysql>delete from mytable;
20添加表字段
alter tabletable1 add transactor varchar(10) not Null;
alter tabletable1 add id int unsigned not Null auto_increment primary key
21修改某个表的字段类型及指定为空或非空
alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];alter table 表名称 modify 字段名称 字段类型 [是否允许非空];
22删除某一字段
ALTER TABLEmytable DROP 字段名;
23添加唯一键
ALTER TABLEtest2
ADD UNIQUE ( userid
)
24修改主键
ALTER TABLEtest2
DROP PRIMARY KEY ,ADD PRIMARY KEY ( id
)
alter tableversion_template drop primary key ,add primary key version,role,type);
25增加索引
ALTER TABLEtest2
ADD INDEX ( id
)
alter tabletable1 add index ind_id (id);
create indexind_id on table1 (id);
create uniqueindex ind_id on table1 (id);//建立唯一性索引
26删除索引
drop indexidx_id on table1;
alter tabletable1 drop index ind_id;
27重命名表:
alter table t1 rename t2;
28增加一个字段
alter tabletabelName add column fieldName dateType;
29增加多个字段
alter tabletabelName add column fieldName1 dateType,add columns fieldName2 dateType;
30创建临时表:
create temporarytable zengchao(name varchar(10));
31创建表时先判断表是否存在
create table ifnot exists students(….);
32从已近有的表中复制表的结构
create tabletable2 select * from table1 where 1\<>1;
33复制表
create tabletable2 select * from table1;
34对表重新命名
alter table table1 rename astable2;
35修改列的类型
alter table table1 modify id intunsigned;//修改列id的类型为int unsigned
alter table table1 change id sidint unsigned;//修改列id的名字为sid,而且把属性修改为int unsigned
36联合字符或者多个列(将列id与”:”和列name和”=”连接)
select concat(id,’:’,name,’=’)from students;
37limit(选出10到20条)<第一个记录集的编号是0>
select * from students order byid limit 9,10;
38MySQL会使用索引的操作符号
<,<=,>=,>,=,between,in,不带%或者_开头的like
39存储过程
select name from mysql.proc where db=’数据库名’;
或者
select routine_name frominformation_schema.routines where routine_schema=’数据库名’;
或者
show procedure status where db=’数据库名’;
40mysql存储过程修改
ALTER PROCEDURE
更改用CREATEPROCEDURE 建立的预先指定的存储过程,其不会影响相关存储过程或存储功能。
41mysql存储过程的删除
删除一个存储过程比较简单,和删除表一样:
DROP PROCEDURE
从MySQL的表格中删除一个或多个存储过程。
42存储过程基本函数
(1).字符串类
CHARSET(str) //返回字串字符集
CONCAT (string2 [,… ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string[,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注:mysql中处理字符串时,默认第一个字符下标为1,即参数position必须大于等于1
mysql> select substring(‘abcd’,0,2);
+———————–+
| substring(‘abcd’,0,2) |
+———————–+
| |
+———————–+
1 row in set (0.00 sec)
mysql> select substring(‘abcd’,1,2);
+———————–+
| substring(‘abcd’,1,2) |
+———————–+
| ab |
+———————–+
1 row in set (0.02 sec)
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格
(2).数学类
ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注:HEX()中可传入字符串,则返回其ASC-11码,如HEX(‘DEF’)返回4142143
也可以传入十进制整数,返回其十六进制编码,如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注:返回类型并非均为整数,如:
(1)默认变为整形值
mysql> select round(1.23);
+————-+
| round(1.23) |
+————-+
| 1 |
+————-+
1 row in set (0.00 sec)
mysql> select round(1.56);
+————-+
| round(1.56) |
+————-+
| 2 |
+————-+
1 row in set (0.00 sec)
(2)可以设定小数位数,返回浮点型数据
mysql> select round(1.567,2);
+—————-+
| round(1.567,2) |
+—————-+
| 1.57 |
+—————-+
1 row in set (0.00 sec)
SIGN (number2 ) //
(3).日期时间类
ADDTIME (date2 ,time_interval )//将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0
SQRT(number2) //开平方
43使用索引的缺点
1) 增删改数据的速度;
2)占用磁盘空间;
3)增加查询优化器的负担;
当查询优化器生成执行计划时,会考虑索引,太多的索引会给查询优化器增加
44分析索引效率
方法:在一般的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的建议;
45使用较短的定长列
1)尽可能使用较短的数据类型;
2)尽可能使用定长数据类型;
a)用char代替varchar,固定长度的数据处理比变长的快些;
b)对于频繁修改的表,磁盘容易形成碎片,从而影响数据库的整体性能;
c)万一出现数据表崩溃,使用固定长度数据行的表更容易重新构造。使用固定长度的数据行,每个记录的开始位置都是固定记录长度的倍数,可以很容易被检测到,但是使用可变长度的数据行就不一定了;
d)对于MyISAM类型的数据表,虽然转换成固定长度的数据列可以提高性能,但是占据的空间也大;
46使用not null和enum
尽量将列定义为notnull,这样可使数据的出来更快,所需的空间更少,而且在查询时,MySQL不需要检查是否存在特例,即null值,从而优化查询;
如果一列只含有有限数目的特定值,如性别,是否有效或者入学年份等,在这种情况下应该考虑将其转换为enum列的值,MySQL处理的更快,因为所有的enum值在系统内都是以标识数值来表示的;
47使用optimize table
对于经常修改的表,容易产生碎片,使在查询数据库时必须读取更多的磁盘块,降低查询性能。具有可变长的表都存在磁盘碎片问题,这个问题对blob数据类型更为突出,因为其尺寸变化非常大。可以通过使用optimize table来整理碎片,保证数据库性能不下降,优化那些受碎片影响的数据表。 optimize table可以用于MyISAM和BDB类型的数据表。实际上任何碎片整理方法都是用mysqldump来转存数据表,然后使用转存后的文件并重新建数据表;
48使用procedure analyse()
可以使用procedure analyse()显示最佳类型的建议,使用很简单,在select语句后面加上procedureanalyse()就可以了;例如:
select * from students procedureanalyse();
select * from students procedureanalyse(16,256);
第二条语句要求procedure analyse()不要建议含有多于16个值,或者含有多于256字节的enum类型,如果没有限制,输出可能会很长;
49使用查询缓存
1)查询缓存的工作方式:
第一次执行某条select语句时,服务器记住该查询的文本内容和查询结果,存储在缓存中,下次碰到这个语句时,直接从缓存中返回结果;当更新数据表后,该数据表的任何缓存查询都变成无效的,并且会被丢弃。
2)配置缓存参数:
变量:query_cache_type,查询缓存的操作模式。有3中模式,0:不缓存;1:缓存查询,除非与 select sql_no_cache开头;2:根据需要只缓存那些以select sql_cache开头的查询; query_cache_size:设置查询缓存的最大结果集的大小,比这个值大的不会被缓存。
50调整硬件
1)在机器上装更多的内存;
2)增加更快的硬盘以减少I/O等待时间;
寻道时间是决定性能的主要因素,逐字地移动磁头是最慢的,一旦磁头定位,从磁道读则很快;
3)在不同的物理硬盘设备上重新分配磁盘活动;
如果可能,应将最繁忙的数据库存放在不同的物理设备上,这跟使用同一物理设备的不同分区是不同的,因为它们将争用相同的物理资源(磁头)。