MySQL：MySQL的基本操作

1、数据库登录

格式：mysql -h主机地址 -u用户名 -p用户密码 -P端口 -D数据库 -e “SQL内容”

[root@wulaoer ~]# mysql -uroot -p

2、修改密码

格式：mysqladmin -u用户名 -p旧密码 password 新密码

[root@wulaoer ~]# mysqladmin -uroot password '123456'

注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。

例2：再将root的密码改为wulaoer。

[root@wulaoer ~]# mysqladmin -uroot  -p'123456' password 'wulaoer'
或
[root@wulaoer ~]# service mysqld start
[root@wulaoer ~]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
#如果是安装版的也可以在安装目录下执行 ./scripts/mysql_install_db --user=mysql
[root@wulaoer ~]# mysql -uroot -p #直接进数据库不用密码
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql; #切换到数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=PASSWORD("wulaoer")where user="root";#修改数据库的密码
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql> flush privileges;#更新权限
Query OK, 0 rows affected (0.00 sec)

mysql> quit #退出
Bye
[root@wulaoer ~]# service mysqld start
正在启动 mysqld：                                          [确定]
[root@wulaoer ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

View Code

3、添加用户

格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"

例1、增加一个用户root密码为wullaoer，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL，然后键入以下命令：

mysql> grant select,insert,update,deleteon on *.* to root@localhost identified by 'wulaoer';
Query OK, 0 rows affected (0.00 sec)
或
mysql> grant all privileges on *.* to root@localhost identified by 'wulaoer';
Query OK, 0 rows affected (0.00 sec)

如果你不想test2有密码，可以再打一个命令将密码消掉。

mysql> grant select,insert,update,delete on *.* to root@localhost identified by ' ';#localhos是本机登录
Query OK, 0 rows affected (0.00 sec)

4、创建数据库

格式：create database name; 创建数据库

实例

格式：use databasename; 选择数据库

mysql> use wulaoer;#切换数据库
Database changed

5、备份或恢复数据库

5.1格式：mysqldump -h 主机 -u 用户名 -p 数据库 > 备份地址以及备份名称

[root@wulaoer ~]# mysqldump -uroot -p wulaoer > wulaoer.sql
Enter password:
[root@wulaoer ~]# ls
anaconda-ks.cfg install.log install.log.syslog wulaoer.sql

5.2还原格式：mysqldump -h 主机 -u 用户名 -p 数据库 < 还原的数据库

这里先把wulaoer这个数据库删除掉

View Code

实例：

[root@wulaoer ~]# mysqladmin -h localhost -u root -p create wulaoer #新建要还原的数据库名
Enter password:
[root@wulaoer ~]# mysqldump -uroot -p wulaoer < wulaoer.sql
Enter password: #还原数据库
-- MySQL dump 10.13  Distrib 5.1.73, for redhat-linux-gnu (x86_64)
--
-- Host: localhost    Database: wulaoer
-- ------------------------------------------------------
-- Server version    5.1.73

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-01-20 21:46:13

View Code

注：如果要还原数据库，数据库中一定要有要还原的数据库的名字，

如果只想卸除建表指令，则可以如下：

[root@wulaoer ~]# mysqladmin -u root -p databasename > a.sql
Enter password:
[root@wulaoer ~]# mysqldump -uroot -p test mytable>./test.txt
Enter password:

将备份数据导入到数据：（导回test数据库）

5.3备份数据库wulaoer

[root@wulaoer ~]# mysqldump -uroot -p wulaoer > wulaoer.sql

得到的wulaoer.sql是一个sql脚本，不包括建库的语句，所以你需要手工创建数据库才可以导入恢复数据库wulaoer,需要创建一个空库wulaoer

[root@wulaoer ~]# mysql -uroot -p wulaoer < wulaoer.sql

如果不希望后来公共创建wulaoer，可以

[root@wulaoer ~]# mysqldump -uroot -p --databases wulaoer > wulaoer.sql
[root@wulaoer ~]# mysql -uroot -p > wulaoer.sql

但这样的话系统种就不能存在wulaoer库，且无法导入其他名字的数据库，当然你可以手工修改wulaoer.sql文件

如果只想卸出插入数据的sql命令，而不需要建表命令，则命令如下：

[root@wulaoer ~]# mysqladmin -uroot -p databasename > a.sql

那么如果我只想要数据，而不想要什么sql命令时，应该如何操作呢？

可将查询存储在一个文件中并告诉mysql从文件中读取查询而不是等待键盘输入。可利用外壳程序键入重定向实用程序来完成这项工作。例如，如果在文件my_file.sql 中存放有查

5.4完整备份：

给予mysql的完整恢复，因为bin-log记录着所有的事件的操作，当mysql发生灾难错误时，可以通过bin-log做完整恢复，给予时间点和位置的恢复。例如：每天定义执行一点用mysqldump备份数据库，但是在三点由于数据库出现了故障，数据库无法访问需要恢复，先把数据库恢复到一点的完整备份，在使用mysqlbinlog恢复从一点到mysqldump备份以来的binlog，这样数据库就能恢复到原来的状态

[root@wulaoer ~]# /usr/local/mysql/bin/mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > fullbackup_sunday_11_PM.sql

5.5增量备份数据库

用more查看备份的数据库：
[root@wulaoer ~]# more fullbackup_sunday_11_PM.sql
-- MySQL dump 10.13  Distrib 5.5.44, for Linux (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version    5.5.44-cll-lve

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000006', MASTER_LOG_POS=107;

--
-- Current Database: `mysql`
...............................省略部分................................................

View Code

在上面的信息里可以看到备份后所有的更改都保存在mysqld-bin.000006二进制文件中然后在完全备份的每天执行下面的命令做一次增量备份

[root@localhost ~]# mysqladmin -uroot -p flush-logs

这是需要把二进制文件保存到一个安全的地方，以方便恢复。

5.6恢复备份：

举例说明：

如果设定了每个星期日早上六点完整备份，每隔一个小时进行一次增量备份，在八点的时候数据库崩溃，这是就需要进行恢复数据库了。先进行完整恢复：

mysql -u root -p < fullbackup_sunday_11_PM.sql

在接七点、八点的增量备份

[root@wulaoer ~]# /var/lib/mysql/mysqld-bin.000006 mysqld-bin.000007 | mysql -uroot -p

恢复完备份之后，就可以找到最新的增量做最新的完整恢复

mysqlbinlog 后加 --database "具体库" 可恢复具体库的增量信息加 --stop-date="2010-12-20 13:15:00" 可恢复到该日志的具体时间点，如果恢复一个时间段可以--start-date="2010-12-20 13:15:00"--stop-date="2016-1-20 13:15:00"

flush privileges;

6、导入.sql文件命令（例如D:/mysql.sql）

mysql>use wulaoer;
mysql> source /home/wulaoer.sql;

UPDATE以新的值更新现存表中行的列。SET 子句指出要修改哪个列和他们应该给定的值。WHERE子句如果被给出，指定哪个记录行应该被更新。否则，所有的记录行被更新。如果 ORDER BY 子句被指定，记录行将被以指定的次序更新。如果你指定关键词LOW_PRIORITY，UPDATE 的执行将被延迟，直到没有其它的客户端正在读取表。如果你指定关键词IGNORE，该更新语句将不会异常中止，即使在更新过程中出现重复键错误。导致冲突的记录行将不会被更新。如果在一个表达式中从tbl_name 中访问一个列，UPDATE 使用列的当前值。举例来说，下面的语句设置 age 列值为它的当前值加 1 ：

mysql> UPDATE persondata SETage=age+1;

UPDATE赋值是从左到右计算的。举例来说，下列语句将 age 列设置为它的两倍，然后再加 1 ：

mysql> UPDATE persondata SETage=age*2, age=age+1;

如果你设置列为其当前的值，MySQL 注意到这点，并不更新它。UPDATE返回实际被改变的记录行数目。在 MySQL 3.22 或更新的版本中，C API 函数 mysql_info()返回被匹配并更新的记录行数目，以及在 UPDATE 期间发生的警告的数目。在 MySQL3.23 中，你可以使用 LIMIT # 来确保只有给定的记录行数目被更改。如果一个 ORDER BY 子句被使用(从 MySQL4.0.0 开始支持)，记录行将以指定的次序被更新。这实际上只有连同 LIMIT一起才有用。从 MySQL 4.0.4 开始，你也可以执行一个包含多个表的UPDATE 的操作：

UPDATE items,month SETitems.price=month.price
WHERE items.id=month.id;

注意：多表 UPDATE 不可以使用 ORDER BY 或LIMIT。
关键字: mysql
启动：net start mySql;
进入：mysql -u root -p/mysql -hlocalhost -u root -p databaseName;
列出数据库：show databases;
选择数据库：use databaseName;
列出表格：show tables；
显示表格列的属性：show columns fromtableName；
建立数据库：source fileName.txt;

匹配字符：可以用通配符_代表任何一个字符，％代表任何字符串;

7、用文本方式将数据装入数据库表中（例如D:/mysql.txt）

mysql> LOAD DATA LOCAL INFILE/mysql.txt" INTO TABLE MYTABLE;
删除数据库
drop database name 直接删除数据库
mysqladmin drop database name 删除数据库前，有提示
数据库授权
GRANT ALL PRIVILEGES ON bdc.* TObdc@'%' IDENTIFIEDBY 'bdc123' WITH GRANT OPTION;
flush privileges
授权访问bdc 的所有表用户为bdc 任何主机，密码为bdc@123
创建一个用户custom在特定客户端it363.com登录，可访问特定数据库fangchandb
mysql >grant select, insert,update, delete, create,drop on fangchandb.* to custom@it363.com identified by ‘passwd’
删除授权：
mysql> revoke all privilegeson *.* from root@”%”;
mysql> delete from user whereuser=”root” and host=”%”;
mysql> flush privileges;
显示当前mysql版本和当前日期
selectversion(),current_date;
查询时间：select now();
查询当前用户：select user();
查询数据库版本：select version();
查询当前使用的数据库：select database();
查询所有数据表
>show tables;
查询表的字段信息
>desc 表名称;
创建表
create table s_position
(
id int not null auto_increment,
name varchar(20) not nulldefault '经理', #设定默认值
description varchar(100),
primary key PK_positon (id) #设定主键
);
create table department
(
id int not null auto_increment,
name varchar(20) not nulldefault '系统部', #设定默认值
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_incrementprimary key, #设定主键
name varchar(20) not nulldefault '无名氏', #设定默认值
department_id int not null,
position_id int not null,
unique (department_id,position_id)#设定唯一值
);
修改结构
mysql>
#表position增加列test
alter tableposition add(test char(10));
#表position修改列test
alter tableposition modify test char(20) not null;
#表position修改列test默认值
alter tableposition alter test set default 'system';
#表position去掉test默认值
alter tableposition alter test drop default;
#表position去掉列test
alter tableposition drop column test;
#表depart_pos删除主键
alter tabledepart_pos drop primary key;
#表depart_pos增加主键
alter tabledepart_pos add primary key PK_depart_pos (department_id,position_id);

View Code

8、操作数据库

#插入表department
insert into department(name,description)values('系统部','系统部');
insert into department(name,description)values('公关部','公关部');
insert into department(name,description)values('客服部','客服部');
insert into department(name,description)values('财务部','财务部');
insert into department(name,description)values('测试部','测试部');
#插入表s_position
insert into s_position(name,description)values('总监','总监');
insert into s_position(name,description)values('经理','经理');
insert into s_position(name,description)values('普通员工','普通员工');
#插入表depart_pos
insert intodepart_pos(department_id,position_id)
select a.id department_id,b.id postion_id
from department a,s_position b;
#插入表staffer
insert intostaffer(name,department_id,position_id) values('陈达治',1,1);
insert into staffer(name,department_id,position_id)values('李文宾',1,2);
insert intostaffer(name,department_id,position_id) values('马佳',1,3);
insert intostaffer(name,department_id,position_id) values('亢志强',5,1);
insert intostaffer(name,department_id,position_id) values('杨玉茹',4,1);

9、查询及删除操作

#显示系统部的人员和职位
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='财务部';

10、清空表

mysql>delete from MYTABLE;

11、删除表：

mysql>drop TABLE MYTABLE;

12、添加表字段：

alter tabletable1 add transactor varchar(10) not Null;
alter tabletable1 add id int unsigned not Null auto_increment primary key
修改某个表的字段类型及指定为空或非空
>alter table 表名称 change 字段名称字段名称字段类型 [是否允许非空];
>alter table 表名称 modify 字段名称字段类型 [是否允许非空];
>alter table 表名称 modify 字段名称字段类型 [是否允许非空];
如果要删除某一字段，可用命令：
ALTER TABLEmytable DROP 字段名;

13、添加唯一键：

ALTER TABLE`test2` ADD UNIQUE ( `userid`)

14、修改主键：

ALTER TABLE`test2` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` )
alter tableversion_template drop primary key ,add primary key version,role,type);

15、增加索引

ALTER TABLE`test2` ADD INDEX ( `id` )
alter tabletable1 add index ind_id (id);
create indexind_id on table1 (id);
create uniqueindex ind_id on table1 (id);//建立唯一性索引

16、删除索引：

drop indexidx_id on table1;
alter tabletable1 drop index ind_id;

17、重命名表：

alter table t1 rename t2;

18、增加一个字段：

alter tabletabelName add column fieldName dateType;

19、增加多个字段

alter tabletabelName add column fieldName1 dateType,add columns fieldName2 dateType;

多行命令输入:注意不能将单词断开;当插入或更改数据时，不能将字段的字符串展开到多行里，否则硬回车将被储存到数据中;

增加一个管理员帐户：grant all on *.* to user@localhost identified by"password";

每条语句输入完毕后要在末尾填加分号';'，或者填加'\g'也可以；

20、创建临时表：

create temporarytable zengchao(name varchar(10));

21、创建表是先判断表是否存在

create table ifnot exists students(……);

22、从已经有的表中复制表的结构

create tabletable2 select * from table1 where 1<>1;

23、赋值表

create tabletable2 select * from table1;

24、对表重新命名

alter table table1 rename astable2;

25、修改列的类型

alter table table1 modify id intunsigned;//修改列id的类型为int unsigned
alter table table1 change id sidint unsigned;//修改列id的名字为sid，而且把属性修改为int unsigned

26、联合字符或者多个列(将列id与":"和列name和"="连接)

select concat(id,':',name,'=')from students;

27、limit(选出10到20条)<第一个记录集的编号是0>

select * from students order byid limit 9,10;

28、MySQL会使用索引的操作符号

<,<=,>=,>,=,between,in,不带%或者_开头的like

29、存储过程

1）查看存储过程

select name from mysql.proc where db=’数据库名’;

或

select routine_name frominformation_schema.routines where routine_schema='数据库名';

或

show procedure status where db='数据库名';

30、MySQL存储过程的修改

ALTER PROCEDURE

更改用CREATEPROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

31、MySQL存储过程的删除

删除一个存储过程比较简单，和删除表一样：

DROP PROCEDURE

从MySQL的表格中删除一个或多个存储过程。

32、存储过程基本函数

(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) //开平方

33、使用索引的缺点

1) 增删改数据的速度；

2）占用磁盘空间；

3）增加查询优化器的负担；

当查询优化器生成执行计划时，会考虑索引，太多的索引会给查询优化器增加

34、分析索引效率

方法：在一般的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的建议；

35、使用较短的定长列

1）尽可能使用较短的数据类型；

2）尽可能使用定长数据类型；

a）用char代替varchar，固定长度的数据处理比变长的快些；

b）对于频繁修改的表，磁盘容易形成碎片，从而影响数据库的整体性能；

c）万一出现数据表崩溃，使用固定长度数据行的表更容易重新构造。使用固定长度的数据行，每个记录的开始位置都是固定记录长度的倍数，可以很容易被检测到，但是使用可变长度的数据行就不一定了；

d）对于MyISAM类型的数据表，虽然转换成固定长度的数据列可以提高性能，但是占据的空间也大；

36、使用not null和enum

尽量将列定义为notnull，这样可使数据的出来更快，所需的空间更少，而且在查询时，MySQL不需要检查是否存在特例，即null值，从而优化查询；

如果一列只含有有限数目的特定值，如性别，是否有效或者入学年份等，在这种情况下应该考虑将其转换为enum列的值，MySQL处理的更快，因为所有的enum值在系统内都是以标识数值来表示的；

37、使用optimize table

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

38、使用procedure analyse()

可以使用procedure analyse()显示最佳类型的建议，使用很简单，在select语句后面加上procedureanalyse()就可以了；例如：

select * from students procedureanalyse();
select * from students procedureanalyse(16,256);

第二条语句要求procedure analyse()不要建议含有多于16个值，或者含有多于256字节的enum类型，如果没有限制，输出可能会很长；

39、使用查询缓存

1）查询缓存的工作方式：

第一次执行某条select语句时，服务器记住该查询的文本内容和查询结果，存储在缓存中，下次碰到这个语句时，直接从缓存中返回结果；当更新数据表后，该数据表的任何缓存查询都变成无效的，并且会被丢弃。

2）配置缓存参数：

变量：query_cache_type，查询缓存的操作模式。有3中模式，0：不缓存；1：缓存查询，除非与 select sql_no_cache开头；2：根据需要只缓存那些以select sql_cache开头的查询； query_cache_size：设置查询缓存的最大结果集的大小，比这个值大的不会被缓存。

40、调整硬件

1）在机器上装更多的内存；

2）增加更快的硬盘以减少I/O等待时间；

寻道时间是决定性能的主要因素，逐字地移动磁头是最慢的，一旦磁头定位，从磁道读则很快；

3）在不同的物理硬盘设备上重新分配磁盘活动；

如果可能，应将最繁忙的数据库存放在不同的物理设备上，这跟使用同一物理设备的不同分区是不同的，因为它们将争用相同的物理资源（磁头）。

41、MySQL版本查看：

[root@wulaoer ~]# mysql -V
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

或者

[root@wulaoer ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:        38
Current database:
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.1.73 Source distribution
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:        /var/lib/mysql/mysql.sock
Uptime:            2 hours 12 min 50 sec

Threads: 1  Questions: 186  Slow queries: 0  Opens: 28  Flush tables: 1  Open tables: 23  Queries per second avg: 0.23
--------------

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

View Code

42、二进制日志查看

开启二进制日志，在windows里my.ini或者linux里my.cnf里，在[mysqld]下加上log-bin=日志名，注{等号后面可以定义二进制日志文件名和位置}。如果只对某个库进行日志文件记录，那么再添加binlog-do-db=test，这样就只记录test数据库的日志，而放弃其他的，而binlog-ignore-db=sales 除sales库不记录，其他都记录。配置文件设置好需要重启一下mysql

43、mysql日志管理

设置my.cnf中的参数[mysqld]下的expire_logs_day = 天数。此参数是设置日志的过期天数，过期的日志将会被自动删除，

如果日志长度超过了max_binlog_size = (大小可以带单位，默认k)的上限（默认是1G）也会创建一个新的日志文件

①show variables like 'log_%';查看所有的log命令
②show variables like 'log_bin';查看具体的log命令

删除日志：

mysql> purge master logs to 'mysql-bin.000009' ;
Query OK, 0 rows affected (0.01 sec)
mysql> purge master logs before '2015-0-22 00:00:00′;
Query OK, 0 rows affected (0.05 sec)
RESET MASTER

#删除之前所有的mysqld-bin，并重新生成新的mysqld-bin后缀从000001开始。注：如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。

44、远程使用客户端连接数据库

（1）删除连接的空账户

mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec)

（2）建立客户端连接：

先在防火墙加如一条，防火墙允许3306端口通过，添加后重启防火墙。

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

（3）先看以下mysql的配置文件my.cnf或者my.conf,里面（[mysqld]）有没有bind_address=127.0.0.1，如果有就注释掉，没有的话就不用操作了，

（4）添加允许连接的IP和帐号密码.

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";

更多可以参考3、添加用户，这里的用户和密码是客户端连接的用户和密码。

（5）查看连接的客户端

（6）删除连接的客户端

关闭远程访问：

mysql> use mysql;
mysql> update user set host = "localhost" where user = "root" and host= "%";

（7）删除连接的IP：

45、查看连接MYSQL数据库的IP信息

我们通常情况下要统计数据库的连接数指的是统计总数，没有细分到每个IP上。现在要监控每个IP的连接数，实现方式如下：

方法一：

select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;

方法二：

mysql -u root -h127.0.0.1 -e "show processlist\G;"| egrep "Host\:" | awk -F: '{ print $2 }'| sort | uniq -c

方法三：

mysql -u root -h127.0.0.1 --skip-column-names -e "show processlist;"|awk '{print $3}'|awk -F":" '{print $1}'|sort|uniq –c

以上就是三种监控IP连接数的实现方式，希望对大家的学习有所帮助。

参考网址http://blog.csdn.net/lxh090821/article/details/9410943

posted @ 2016-01-20 00:00 吴老二阅读(2020) 评论(0) 编辑收藏举报

刷新页面返回顶部

MySQL：MySQL的基本操作

公告